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 article. Then, you can use the results in the formula for churn rate in the below instructions.

Calculated Columns

Columns to create

• customer_entity table

• 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

• Seconds since customer's last order date

  • Select a definition: Age
  • Select column: Customer's last order date

Metrics

• New customers (by first order date)
  • Customers that are counted

• 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 that are counted

• 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:

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.

Business.Adobe.com resources