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. 

This analysis is available to customers on both the new and the legacy architecture. If your account has access to the Persistent Views feature under the Manage Data side bar, you are on the new architecture and can follow the instructions listed here to build this analysis yourself. If you are on the legacy architecture, 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.

Before getting started, you will want to familiarize yourself with our cohort report builder (new and legacy).

Calculated Columns

Columns to create on the orders table if using 30-day months:

    • Column name: Months between first order and this order
      • Column type: Same Table
      • Column equation: CALCULATION
      • Column input: A = Seconds between customer’s first order date and this order
      • Datatype: Integer
      • Definition: case when A is null then null when A <= 0 then '1'::int else (ceil(A)/2629800)::int end
    • Column name: Months since order
      • Column type: Same Table
      • Column equation: CALCULATION
      • Column input: A = created_at
      • Datatype: Integer
      • Definition: case when created_at is null then null else (ceil((extract(epoch from current_timestamp) - extract(epoch from created_at))/2629800))::int end

Columns to create on the orders table if using calendar months:

    • Column name: Calendar months between first order and this order
      • Column type: Same Table
      • Column equation: CALCULATION
      • Column inputs:
        • A = created_at
        • B = Customer's first order date
      • Datatype: Integer
      • Definition: case when (A::date is null) or (B::date is null) then null else ((date_part('year',A::date) - date_part('year',B::date))*12 + date_part('month',A::date) - date_part('month',B::date))::int end
    • Column name: Calendar months since order
      • Column type: Same Table
      • Column equation: CALCULATION
      • Column input: A = created_at
      • Datatype: Integer
      • Definition: case when A is null then null else ((date_part('year',current_timestamp::date) - date_part('year',A::date))*12 + date_part('month',current_timestamp::date) - date_part('month',A::date))::int end
    • Column name: Is in current month? (Yes/No)
      • Column type: Same Table
      • Column equation: CALCULATION
      • Column input: A = created_at
      • Datatype: String
      • Definition: case when A is null then null when (date_trunc('month', current_timestamp::date))::varchar = (date_trunc('month', A::date))::varchar then 'Yes' else 'No' end

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

 

Metrics

Metrics to create

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

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

 

Reports

Expected revenue per customer by month

  • Metric A: Revenue (hide)
    • Metric: Revenue
    • Filter:
      • Calendar months between first order and this order <= X (Pick some reasonable number for X, e.g. 24 months)
      • Is in current month? = No
  • Metric B: All time customers (hide)
    • Metric: New customers by first order date
    • Filter:
      • Is in current month? = No
  • Metric C: All time customers by month since first order (hide)
    • Metric: New customers by first order date
    • Filter:
      • Calendar months since order <= X
      • Is in current month? = No
  • Formula: Expected revenue
    • Formula: A / (B - C)
    • Format: Currency

Other chart details

  • Time period: All time
  • Time interval: None
  • Group by: Calendar months between first order and this order - show all
  • Change the goup by for the All time customers metric to Independent using the pencil icon next to the group by
  • Edit the Show top/bottom fields as follows:
    • Revenue: Top 24 sorted by Calendar months between first order and this order
    • All time customers: Top 24 sorted by All time customers
    • All time customers by month since first order: Top 24 sorted by All time customers by month since first order

Avg revenue per month by cohort

  • Metric A: Revenue
    • Metric view: Cohort
    • Cohort date: Customer's first order date
    • Perspective: Average value per cohort member

Cumulative avg revenue per month by cohort

  • Metric A: Revenue
    • Metric view: Cohort
    • Cohort date: Customer's first order date
    • Perspective: 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 1 found this helpful

Comments

  • Avatar
    Dan Mentzer

    How do you get current LTV for a certain group of customers.

    I do not want averages I want the exact lifetime value for the specific customers i put into the report each individual customers value needs to be separate not added together.

  • Avatar
    Khalil Manns

    Hi Dan,

    Thanks for reaching out. If you have a metric built upon a `customer's lifetime revenue` column, you can use that in a report and use a "group by" column such as Customer email or Customer name. The group by can be used to display all emails/names (which can be alot), or filtered for specific values. If you are still having issues setting up the report to your liking, you can reach our support team at support@rjmetrics.com.

Powered by Zendesk