# Expected Lifetime Value (LTV) Analysis for Pro

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 will want to familiarize yourself with our cohort report builder.

## 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`

## Metrics

### Metric instructions

Metrics to create

• Distinct customers by first order date*
• If you enable guest orders, use `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

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

## Reports

### Report instructions

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, e.g. 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

• 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, contact support.