Magento Business Intelligence Help Center

Commerce Churn

In this article, we demonstrate 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 upon a determined amount of time passing since their last order.

Many customers want assistance in starting to conceptualize what timeframe they should use based upon 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 Analysis Library article. Then, you can use the results in the formula for churn rate in the below instructions.

Columns to create

  • customer_entity table
    • 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: Age
    • Select column: Customer's last order date
    • Customer's last order date
    • Seconds since customer's last order date

Note: Make sure to add all new columns as dimensions to metrics before building new reports.

  • New customers (by first order date)
    • Customers we count
    • NOTE: this metric may already exist on your account
    • In the customer_entity table
    • This metric performs a Count
    • On the entity_id column
    • Ordered by the Customer's first order date timestamp
    • Filter:
  • New customers (by last order date)
    • Customers we count
    • NOTE: this metric may already exist on your account
    • In the customer_entity table
    • This metric performs a Count
    • On the entity_id column
    • Ordered by the Customer's last order date timestamp
    • Filter:

Note: Make sure to add all new columns as dimensions to metrics before building new 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.

Months Seconds
3 7,776,000
6 15,552,000
9 23,328,000
12 31,104,000

After compiling all the reports, you can organize them on the dashboard as you desire. The end result may look like the above sample dashboard.

If you run into any questions while building this analysis, or simply want to engage our Professional Services Team to build the analysis for you, simply open a support ticket here.

Was this article helpful?
0 out of 0 found this helpful

Comments

Powered by Zendesk