This topic demonstrates how to set up a dashboard that helps you understand customer lifetime value growth and expected lifetime value of your customers.
This analysis is only available to Pro account customers on the new 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.
Before getting started, you want to familiarize yourself with the cohort report builder.
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
Metrics to create
Distinct customers by first order date
customer_email
In the orders
table
This metric performs a Count Distinct Values
On the customer_id
column
Ordered by the Customer's first order date
timestamp
Make sure to add all new columns as dimensions to metrics before building new reports.
Expected revenue per customer by month
Metric A
: Revenue (hide)
Calendar months between first order and this order
<= X
(Pick some reasonable number for X, for example, 24 months)Is in current month?
= No
Metric: Revenue
Filter:
Metric B
: All time customers (hide)
Is in current month?
= No
Metric: New customers by first order date
Filter:
Metric C
: All time customers by month since first order (hide)
Calendar months since order
<= X
Is in current month?
= No
Metric: New customers by first order date
Filter:
Formula: Expected revenue
Formula: A / (B - C)
Format: Currency
Other chart details
All time
None
Calendar months between first order and this order
- show allgroup by
for the All time customers
metric to Independent using the pencil icon next to the group by
Show top/bottom
fields as follows:
Top 24 sorted by Calendar months between first order and this order
Top 24 sorted by All time customers
Top 24 sorted by All time customers by month since first order
Avg revenue per month by cohort
A
: Revenue
Cohort
Customer's first order date
Average value per cohort member
Cumulative avg revenue per month by cohort
A
: Revenue
Cohort
Customer's first order date
Cumulative average value per cohort member
After compiling all the reports, you can organize them on the dashboard as you desire. The 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 the Professional Services team, contact support.