Order line unit economics and why it’s crucial for the data team
One thing I've come across again and again over my 25 years in analytics and reporting that teams just get wrong is putting calculations in the wrong place. This nearly always reflects badly on the data team.
One of the worst culprits for consumer products data teams failing when implementing data initiatives and dashboards is that people simply calculate things about sales orders at different parts of the data flow, creating uncertainty.
What usually happens - have you seen this? You probably have. The head of the business unit comes to a meeting saying sales for the month are 1M. Finance are saying 900k. Marketing is saying 600k. Seem typical? This is because we’re not defininig and calculating sales consistently.
What’s happening here? Sales - probably going on orders by order date. Finance - they’re going by invoices. Marketing? They have just missed out a whole channel as they don’t have responsibility for it…
What should you do as a consumer products or materials data team?
Make sure you have clear rules for precedence of systems and agreement on rule definitions. Be clear on what you are calling an order or order line - and what date and value you are using. Pro tip: don't confuse sales from orders with revenues from invoices.
Calculate and enrich as much as you can at order line level and store it in your warehouse. This includes:
Assigning clearly what date you're using
FX translations
COGS and margin calculations
Discount calculations
Returns processing
Price waterfall assignments
Handling cancellations or changes
This is silver layer/business vault work. Make sure these are reconciling to any analytics in the source system. All your unit economics should be mapped here. You can either retain or rebuild history as you want. I like to use data vault, which allows me to retain history while keeping the data for as-was comparisons.
If there are properties per SKU or bundle, store them at that level then call them at the order line level.
3. Enable and enforce these rules with all your teams.
So there you go:
Define your rules clearly
Shift the calculations and enrichments to the right part of the data warehouse
Enforce it
What's your view? - comment in the below , follow me here, on www.nathanjones.online for more of this, or get in touch via our website www.calonanalytics.com for more of this
