View: Lifetime Value

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]