How much revenue can you earn from each of those customers?

You need to answer a range of questions about your revenue, because not all of your revenue is created equal.

The easy part: What types of Revenue do I have?

You need to establish a Mutually Exclusive and Exhaustive revenue hierarchy

(It should be able to describe 100% of your revenue without duplication)

This hierarchy definition will be unique for every business, but 99% of businesses should be able to cleanly define a 3-level hierarchy that will cover 100% of their revenue in a descriptive way.

example hierarchy for a business that offers hardware, software, and professional services.

Note:

Some blocks are noted with ** to denote that they can take on multiple values - in this case product / plan categories.

You will likely have to have a catch-all box called ‘other’. Be intentional about where you place this!

Before you go on - SHARE THIS WITH EVERYONE IN YOUR COMPANY

If you’ve created this alignment and are confident in it - celebrate it! Every member of your company should be able to have a clear understanding of how you generate revenue - and have a shared vocabulary with which to talk about it.

The difficult part: How do I track this?

date

customer_id

level_1

level_2

level_3

amount

end_date

Each row will represent the amount purchased by each customer, for each day, across every category. It will also contain information on when the long-term/recurring purchases conclude/reset

Example:

date	customer_id	level_1	level_2	level_3	amount	end_date
2024-03-02	aaa111	RECURRING	STARTUP_PLAN	NULL	1000	2025-03-02
2024-03-02	aaa111	NON-RECURRING	PRO_SERVICES	ONBOARDING	1500	NULL
2024-03-15	bbb222	RECURRING	ENTERPRISE_PLAN	NULL	10000	2025-03-15
2024-03-15	bbb222	NON-RECURRING	HARDWARE	ROBOT	5000	NULL
2024-03-15	bbb222	NON-RECURRING	PRO_SERVICES	ONBOARDING	3000	NULL
2024-03-15	bbb222	NON-RECURRING	PRO_SERVICES	OTHER	595	NULL

ok…How do I create that table?

Create a clean transaction line items table. This has proven to be the most difficult step for most companies.
1. The table is clean when you have a column with ‘amount’ that sums to a value you wholeheartedly trust, with each transaction broken down to its component line items, with all revenue accounted for.

You will need to assign each line item into a revenue category
1. Usually this will be 3 simple but lengthy CASE WHEN SQL statements (one per level) added to line_items table

convert Line Items table to REVENUE table format

select 
	date, 
	customer_id, 
	level_1,	
	level_2,
	level_3, 
	sum(amount) as amount, 
	end_date 
from line_items
group by 
	date, 
	customer_id, 
	category_1, 
	category_2, 
	category_3,  
	end_date