Churn Rate
This topic demonstrates how to calculate a churn rate for your commerce customers. Unlike SaaS or traditional subscription companies, commerce customers typically do not have a concrete “churn event” to show you that they should no longer count toward your active customers. For this reason, the below instructions allow you to define a customer as “churned” based on a determined amount of time passing since their last order.
Many customers want assistance in starting to conceptualize what timeframe they should use based on their data. If you want to use historical customer behavior to define this churn timeframe, you may want to familiarize yourself with the defining churn topic. Then, you can use the results in the formula for churn rate in the below instructions.
Calculated Columns
Columns to create
-
customer_entitytable -
Customer's last order date- Select a definition:
Max - Select table:
sales_flat_order - Select column:
created_at sales_flat_order.customer_id = customer_entity.entity_id- Filter:
Orders we count
- Select a definition:
-
Seconds since customer's last order date- Select a definition:
Age - Select column:
Customer's last order date
- Select a definition:
Metrics
- New customers (by first order date)
- Customers that are counted
-
In the
customer_entitytable -
This metric performs a Count
-
On the
entity_idcolumn -
Ordered by the
Customer's first order datetimestamp -
Filter:
-
New customers (by last order date)
- Customers that are counted
note note NOTE This metric may exist on your account. -
In the
customer_entitytable -
This metric performs a Count
-
On the
entity_idcolumn -
Ordered by the
Customer's last order datetimestamp -
Filter:
Reports
-
Churn Rate
-
Metric: New customers (by first order date)
-
Filter:
Lifetime number of orders Greater Than 0 -
Perspective:
Cumulative -
Metric:
New customers (by last order date) -
Filter:
-
Seconds since customer’s last order date >= [Your self-defined cutoff for churned customers]
^ -
Lifetime number of orders Greater Than 0 -
Metric:
New customers (by last order date) -
Filter:
Lifetime number of orders Greater Than 0 -
Perspective: Cumulative
-
Formula:
(B / ((A + B) - C) -
Format: Percentage
-
-
Metric
A:New customers cumulative -
Metric
B:Churned customers by last order date -
Metric
C:Customers by last order date cumulative -
Formula:Repeat order probability -
Time period:All time (or custom range) -
Group by:Customer's order number -
Chart Type:Column
Below are some common month > second conversions, but google provides other values, including week > seconds conversions for any custom values you may be looking for.
After compiling all the reports, you can organize them on the dashboard as you desire. The result may look like the above sample dashboard.