Magento Business Intelligence Help Center

Expected customer LTV

In this article, we demonstrate how to set up a dashboard that will help you understand customer lifetime value growth and expected lifetime value of your customers.

Before getting started, you will want to familiarize yourself with our cohort report builder.

This analysis contains advanced calculated columns that can only be created by a Magento BI analyst, so file a support request with the subject [EXPECTED LTV ANALYSIS], before getting started with this analysis.

Columns to create

  • orders table
    • IF using 30-day months:
      • Months between first order and this order will be created by an analyst as part of your [EXPECTED LTV ANALYSIS] ticket
      • Months since order will be created by an analyst as part of your [EXPECTED LTV ANALYSIS] ticket
    • IF using calendar months:
      • Calendar months between first order and this order will be created by an analyst as part of your [EXPECTED LTV ANALYSIS] ticket
      • Calendar months since order will be created by an analyst as part of your [EXPECTED LTV ANALYSIS] ticket
    • Is in current month? (Yes/No) will be created by an analyst as part of your [EXPECTED LTV ANALYSIS] ticket
  • New customers by first order date*
    • In the orders table
    • This metric performs a Count Distinct Values
    • On the customer_id column
      • If you enable guest orders, use customer_email
    • Ordered by the Customer's first order date timestamp

*This metrtic may already exist

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

  • Expected revenue by month
    • Metric A: Revenue (hide)
      • Metric: Revenue
      • Filter:
        • Calendar months between this order and customer’s first order <= x (Pick some reasonable number for X, e.g., 24 months)
        • Is in current month? = No
    • Metric B: All time customers (hide)
      • Metric: Customers by first order date
      • Filter:
        • Is in current month? (Yes/No) = No
      • Group by: Independent
    • Metric C: All time customers by month since first order (hide)
      • Metric: New customers by first order date
      • Filter:
        • Calendar months since first order<= x
        • Is in current month? (Yes/No) = No
    • Formula: Expected revenue
      • Formula: A / (B - C)
      • Format: Currency
    • Time period: All time
    • Interval: None
    • Group by: Calendar months between this order and customer's first order
    • Chart Type: Line
  • Avg revenue per month by cohort
    • Cohort analysis - use the cohort report builder!
    • Metric A: Revenue
    • Cohort date: Customer's first order date
    • Counting members in the sales_flat_order table
    • Perspectinve: Average value per cohort member
  • Avg revenue per month by cohort
    • Cohort analysis - use the cohort report builder!
    • Metric A: Revenue
    • Cohort date: Customer's first order date
    • Counting members in the sales_flat_order table
    • Perspectinve: Cumulative average value per cohort member

After compiling all the reports, you can organize them on the dashboard as you desire. The end result may look like the image at the top of the page

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