Customer analytics metrics

[adamjurak]

Hello,

i have a table with customer data like this:

UserID, OrderID, Date, OrderValue - primary sorted by UserID, secondary by Date

I would like to know these metrics for each customer (all time):

• first order date
• last order date
• count of orders and total revenue
• avarage order value

And these for the whole business based on date range (month, quarter, year - for example see these metrics for each month of the last year or last 12 months):

• new and returning customers
• total count of customers

Could someone help please me build these metrics?

Adam

[austinsense]

Let's call the table you have "Orders" - it's going to be a lot easier if you also create a table called "Customers" that has a one unqiue row per customer (per unique UserID). Create the Customers table and join it to the Orders table.  You should also always have a Calendar table that has one row per day, contiguous meaning don't skip any days.

On the Customer Table as Calculated Columns

First Order Date = CALCULATE( MIN( Orders[Date]))
Last Order Date = CALCULATE( MAX( Orders[Date]))

On the Orders Table as Measures

Order Count = DISTINCTCOUNT( Orders[OrderID])
Total Revenue = SUM( Orders[OrderValue])
Average Order Value = DIVIDE( [Total Revenue], [Order Count])

On the Customers Table as Measures

Customer Counter = COUNTROWS( Customers)

Customer Count - BEGINNING OF PERIOD = CALCULATE([Customer Counter], FILTER( Customers, Customers[First Order Date] < MIN( Calendar[Date]) && Customer[Last Order Date] > MIN (Calendar[Date])))

Customer Count - END OF PERIOD = CALCULATE([Customer Counter], FILTER( Customers, Customers[First Order Date] <= MAX( Calendar[Date]) && Customer[Last Order Date] > MAX(Calendar[Date])))

Customer Count - AVG ACROSS PERIOD = DIVIDE([Customer Count - BEGINNING OF PERIOD] + [Customer Count - END OF PERIOD],2)

New Customers = SUMX(Customers, IF([Customer Count - BEGINNING OF PERIOD] = 0 && [Customer Count - END OF PERIOD] = 1, 1, 0))

Returning Customers = SUMX(Customers, IF([Customer Count - BEGINNING OF PERIOD] = 1 && [Customer Count - END OF PERIOD] = 1, 1, 0))

Lost Customers = SUMX(Customers, IF([Customer Count - BEGINNING OF PERIOD] = 1 && [Customer Count - END OF PERIOD] = 0, 1, 0))

A couple of notes here

• Always use the dates from your date table in your reports.  Don't use the dates from the Orders table - you should just go ahead and hide the dates on the Orders table.
• The relationship between the Orders table and the Customers table is important.  If you set it to bi-directional then (when counting customers) you will see only customers that placed an order during the time period - meaning you'll only see customers that have a corresponding row on the Orders table.  If you set the relationship to only flow one way then you'll see all customers that exist at that time based on their first and last order date. I think you're going for the second option.
• You could also set up a metric called Active Customers that counts the customers with orders in the period ...

Active Customers = DISTINCTCOUNT( Orders[UserID])

This should get you started - good luck