A reliable customer lifetime value calculation flourishes on a modular dbt schema structure. We build a highly organized layered architecture. We start with raw orders and finish with refined customer metrics.
Staging Raw Orders and Customers
We begin with a clean foundation at the staging layer. We standardize timestamps. We precisely rename ambiguous columns. We handle null values proactively early in the pipeline.
The staging layer vigorously protects our downstream models. We keep this layer dedicated exclusively to cleaning raw inputs. Reserving business logic for later stages ensures a pristine starting point. This best practice completely isolates our warehouse from any source system changes.
-- models/staging/stg_orders.sql
with source_data as (
select *
from {{ source('raw_data', 'sales_orders') }}
),
staged_orders as (
select
cast(order_id as string) as order_id,
cast(customer_id as string) as customer_id,
-- Standardize timestamps to UTC
convert_timezone('UTC', cast(order_created_at as timestamp)) as order_created_at,
-- Standardize monetary fields
cast(gross_revenue as numeric(10,2)) as gross_revenue,
cast(tax_amount as numeric(10,2)) as tax_amount,
cast(discount_amount as numeric(10,2)) as discount_amount,
-- Categorize order status
lower(cast(order_status as string)) as order_status
from source_data
where order_id is not null
)
select *
from staged_orders;
This first model establishes a solid baseline. We cast all monetary values to an explicit numeric type to maintain precision. This prevents any rounding errors in Snowflake. We convert all timestamps to UTC to guarantee accuracy across all global regions.
Defining the Canonical Transaction Model
We establish the explicit business grain to capture valid revenue accurately. Isolating orders that provide true business value ensures pristine data quality. Filtering out noise creates a clear path to precise calculations.
We carefully define the canonical transaction model for maximum clarity. We isolate the true net revenue by filtering out taxes, identified fraud, and full refunds. We calculate the exact net revenue for each valid transaction.
-- models/marts/fct_transactions.sql
with staged_orders as (
select *
from {{ ref('stg_orders') }}
),
valid_transactions as (
select
order_id,
customer_id,
order_created_at,
-- Calculate true net revenue
(gross_revenue - tax_amount - discount_amount) as net_transaction_revenue,
order_status
from staged_orders
-- Exclude invalid or non-revenue generating orders
where order_status in ('completed', 'shipped', 'delivered')
and gross_revenue > 0
)
select *
from valid_transactions;
This canonical model acts as our revenue source of truth. We focus entirely on successful transactions by removing canceled orders. We accurately deduct the tax and discount amounts. The resulting net_transaction_revenue column feeds our customer lifetime value calculation seamlessly.
Constructing the Customer Profile
We now aggregate our canonical transactions to unlock deep insights. We build the observed baseline perfectly for every individual buyer. We construct the customer profile model.
This model rolls up the transaction data to reveal key milestones. We determine the first order date. We identify the most recent order date clearly. We calculate the total lifetime spend per customer.
-- models/marts/dim_customer_profile.sql
with transactions as (
select *
from {{ ref('fct_transactions') }}
),
customer_aggregations as (
select
customer_id,
-- Identify customer lifecycle boundaries
min(order_created_at) as first_purchase_at,
max(order_created_at) as last_purchase_at,
-- Calculate historic frequency and monetary value
count(distinct order_id) as lifetime_order_count,
sum(net_transaction_revenue) as historic_clv_revenue
from transactions
group by customer_id
)
select *
from customer_aggregations;
This model delivers the immediate historic CLV. We logically group the records by customer_id. We calculate the exact distinct order count. We sum the net transaction revenue. Analytics engineers can seamlessly join this rich profile to marketing datasets.