This article will help you learn about the fields available in the Lifetime Value view, the field types, descriptions and how the fields are calculated
Key Topics
Click on the links below to take you to the section where you can learn more about this View and where it is used and how it is created
View Definition
This view is linked to the [drp.ltv_time_series] table which generated as part of the Daasity transformation process and has the following fields available/visible for use.
Field Name | Field Type | Description | Source / Calculation |
Customer Ever Repurchased Flag | Dimension | Flag that identifies if a customer ever repurchased (i.e. made a 2nd purchase) | CASE WHEN customer_ever_repurchased = 1 THEN TRUE ELSE FALSE END |
Months Since First Purchase | Dimension | Indicates the number of 30 day buckets from the first purchase - Time on Books (TOB). 0 is the day of first purchase, 1 covers the next 30 days, 2 covers the 30 days after that, etc... | tob |
Count Customer Repurchased in TOB | Measure | Number of Customers that made at least their 2nd purchase in the TOB | COUNT(DISTINCT(CASE WHEN customer_ever_repurchased = 1 THEN unique_customer_id ELSE NULL END)) |
Count Customers | Measure | Number of Customers in the cohort | COUNT(DISTINCT(unique_customer_id) |
Count Customers Ordering | Measure | Number of Customers that placed an Order in the TOB | SUM(customer_purchased) |
Count Orders | Measure | Number of orders in the TOB | SUM(total_orders) |
Count Units | Measure | Number of items purchased in the TOB | SUM(total_items) |
Total Gross Margin | Measure | Total Gross Margin of orders: Net Sales less SKU Cost | SUM(gross_margin) |
Total Gross Margin per Customers | Measure | Running Total of Gross Margin / Count Customers (must use Months Since Purchase in Query) | RUNNING_TOTAL(CASE WHEN COUNT(DISTINCT(unique_customer_id)) IS NULL THEN NULL ELSE SUM(gross_margin) / NULLIF(COUNT(DISTINCT(unique_customer_id)),0)) |
Total Gross Sales | Measure | Total Gross Sales of orders | SUM(gross_sales) |
Total Net Contribution | Measure | Total Contribution Margin of orders: Gross Margin less Marketing Cost | SUM(contribution_margin) |
Total Net Sales | Measure | Total Net Sales of orders | SUM(net_sales) |
Explores
This view is part of the following explores:
- Lifetime Value
Transformation Code
This view is a Daasity derived table generated by the following code:
- [5110_LTV_BAS_ltv_time_series.sql]