Customer Lifetime Value Segmentation: Beyond ROAS

"Which of our customer cohorts are actually worth the most over time, and which look profitable but aren't?" — that was my Slack question on a Tuesday, and I handed it to our Amazon Agent Atlas with a strict brief: no fluffy summaries, just a query-first proof of concept. I already tried three obvious approaches: a ChatGPT-style single-shot which ignored AMC's 12-month cohort window, the AMC docs alone that described CLTV but didn't surface the exact AVG(... ) OVER () CASE pattern we needed, and an agency pitch deck that happily swapped segment labels and ignored activation timing. I asked the agent to return a per-user labeling of High Value / Growth Potential / Revenue Potential / Low Value ready for a 48-hour DSP activation window for resulting audiences.

What a model without Atlas gets wrong

I ran the same prompt through a frontier model with no retrieval. The output read confidently. It was wrong in five places, each subtle enough that an operator would only catch the failures at activation time, when the DSP audience comes back empty or the segment labels don't match the deck.

Warning: five failure modes I saw in a single un-grounded response:

• used conversions rather than conversions_for_audiences (the audience-build table variant), which produces the wrong user set for RBA exports.
• omitted AVG(lifetime_score) OVER () and AVG(value_score) OVER () semantics, producing non-comparable thresholds across the two axes.
• failed to include the 48-hour DSP activation window, so audiences can't be scheduled correctly in DSP.
• returned generic tiers instead of the four AMC segments: High Value, Growth Potential, Revenue Potential, Low Value.
• omitted the year-over-year diagnostic dimension for cohort migration.

This is the differentiator. The model isn't lying. It is producing a plausible-looking artifact that fails against the AMC playbook's actual mechanics: the audience-build table variant, the composite window-function semantics, the activation timing, the named segment taxonomy, and the diagnostic dimension that catches drift over time.

What Atlas retrieves

• "Understanding Customer Long-Term Value (CLTV)" playbook: the canonical AMC CLTV reference that explains CLTV versus ROAS and reminds us the AMC long-term window is bounded to 12 months. (source: Customer long-term value (CLTV): Amazon Marketing Cloud)
• "Lifetime * Value CLTV SQL Export Logic": the composite-score template that defines the Lifetime Ratio and the overall Lifetime × Value construct. This includes the formula Lifetime Ratio = Purchase Frequency × Purchase Quantity × User Lifetime ratios. (source: Lifetime * Value CLTV SQL Export Logic)
• "Audience Labeling" CASE statement for the four segments: the exact AVG(...) OVER () CASE pattern you need to convert scores into High Value, Growth Potential, Revenue Potential, and Low Value labels. (source: Audience segmentation (based on CLTV))
• "Identify high value customer segments": a data-interpretation guide with percentile examples and audience-size guidance so you can pick seed thresholds and respect RBA/LAL guardrails. (source: Identifying High Value Customer Segments: Flexible Amazon shopping insights)
• "CLTV Audience Segmentation (Year-Over-Year)": the diagnostic view and recommended recurring check to detect cohort migration between segments over time. (source: Customer long-term value (CLTV): Year-over-year guidance)

The agent's output

-- Per-user CLTV segmentation skeleton for AMC
WITH
-- 1) base events: filter to the 12-month AMC window and to the audience build table
events AS (
    SELECT
        user_id,
        event_time,
        tracked_asin, -- optional: later join to ASIN->category mapping for descriptive stats
        quantity,
        sale_price,   -- revenue per event (use Paid Features mappings if available)
        ad_spend      -- spend attributed to the event (nullable; a proxy may be filled for off-Amazon)
    FROM conversions_for_audiences -- use conversions_all for sizing/coverage experiments
    WHERE event_time BETWEEN DATE_ADD('month', -12, CURRENT_DATE) AND CURRENT_DATE
),

-- 2) per-user purchase aggregates
user_purchases AS (
    SELECT
        user_id,
        COUNT(DISTINCT event_time)                             AS purchase_count,
        SUM(quantity)                                         AS total_units,
        SUM(COALESCE(sale_price,0) * COALESCE(quantity,0))    AS total_revenue,
        SUM(COALESCE(ad_spend,0))                             AS total_ad_spend,
        MIN(event_time)                                       AS first_purchase_ts,
        MAX(event_time)                                       AS last_purchase_ts
    FROM events
    GROUP BY user_id
),

-- 3) derived per-user metrics required by the playbook
user_metrics AS (
    SELECT
        user_id,
        purchase_count,
        total_units,
        total_revenue,
        total_ad_spend,
        -- user lifetime in days within the 12-month window (inclusive)
        DATE_DIFF('day', first_purchase_ts, last_purchase_ts) + 1 AS user_lifetime_days,
        -- purchase frequency: purchases per active 30-day period (approx)
        CASE WHEN DATE_DIFF('day', first_purchase_ts, last_purchase_ts) >= 30
            THEN purchase_count / (GREATEST(DATE_DIFF('day', first_purchase_ts, last_purchase_ts),1) / 30.0)
            ELSE purchase_count
        END AS purchase_frequency,
        -- purchase quantity per purchase
        CASE WHEN purchase_count > 0 THEN total_units / CAST(purchase_count AS DOUBLE) ELSE 0 END AS purchase_quantity,
        -- customer cost: currently only ad spend; retention_cost can be added to this field
        (total_ad_spend) AS customer_cost
    FROM user_purchases
),

-- 4) cohort selection: cohort_month as a simple acquisition cohort; replace with channel or category as needed
cohorted AS (
    SELECT
        um.*,
        DATE_FORMAT(first_purchase_ts, '%Y-%m') AS cohort_month
    FROM user_metrics AS um
),

-- 5) cohort-level means used to normalize into ratios
cohort_stats AS (
    SELECT
        cohort_month,
        AVG(purchase_frequency)     AS avg_purchase_frequency,
        AVG(purchase_quantity)      AS avg_purchase_quantity,
        AVG(user_lifetime_days)     AS avg_user_lifetime_days,
        AVG(total_revenue)          AS avg_revenue,
        AVG(customer_cost)          AS avg_cost,
        COUNT(*)                    AS cohort_size
    FROM cohorted
    GROUP BY cohort_month
),

-- 6) per-user ratios (Lifetime and Value components)
ratios AS (
    SELECT
        c.user_id,
        c.cohort_month,
        -- Lifetime Ratio = Purchase Frequency Ratio × Purchase Quantity Ratio × User Lifetime Ratio
        (COALESCE(c.purchase_frequency,0) / NULLIF(s.avg_purchase_frequency,0)) *
        (COALESCE(c.purchase_quantity,0)  / NULLIF(s.avg_purchase_quantity,0)) *
        (COALESCE(c.user_lifetime_days,0) / NULLIF(s.avg_user_lifetime_days,0)) AS lifetime_ratio,
        -- Value side: revenue vs cost ratios (higher revenue and lower cost should increase value)
        (COALESCE(c.total_revenue,0) / NULLIF(s.avg_revenue,0)) AS revenue_ratio,
        (COALESCE(c.customer_cost,0) / NULLIF(s.avg_cost,1)) AS cost_ratio
    FROM cohorted c
    JOIN cohort_stats s USING (cohort_month)
),

-- 7) per-user scores: lifetime_score (product) and value_score (revenue - cost composite)
scores AS (
    SELECT
        r.user_id,
        r.cohort_month,
        r.lifetime_ratio AS lifetime_score,
        (r.revenue_ratio - r.cost_ratio) AS value_score
    FROM ratios r
)

-- Final: label users using global AVG(...) OVER () thresholds so both legs compare to the same population
SELECT
    s.user_id,
    s.cohort_month,
    s.lifetime_score,
    s.value_score,
    CASE
        WHEN s.lifetime_score >= AVG(s.lifetime_score) OVER () AND s.value_score >= AVG(s.value_score) OVER () THEN 'High Value'
        WHEN s.lifetime_score >= AVG(s.lifetime_score) OVER () AND s.value_score <  AVG(s.value_score) OVER () THEN 'Growth Potential'
        WHEN s.lifetime_score <  AVG(s.lifetime_score) OVER () AND s.value_score >= AVG(s.value_score) OVER () THEN 'Revenue Potential'
        WHEN s.lifetime_score <  AVG(s.lifetime_score) OVER () AND s.value_score <  AVG(s.value_score) OVER () THEN 'Low Value'
        ELSE 'Unlabeled'
    END AS customer_segment
FROM scores s;

I kept the SQL intentionally modular so the parts are pluggable into our pipeline. The agent used the composite formula from the "Lifetime * Value CLTV SQL Export Logic": Lifetime Ratio = Purchase Frequency × Purchase Quantity × User Lifetime ratios, and modeled value_score as a revenue-minus-cost composite so that higher revenue and lower cost increase value. Cohorting is done by first_purchase month as a placeholder; replace cohort_month with acquisition_channel or tracked_asin category if that's more meaningful for your ops.

I also made two operational choices: use conversions_for_audiences for the canonical audience build exports and run the same query against conversions_all only when you need a sizing or coverage experiment. Finally, ensure the AVG(...) OVER () comparisons are computed over the same population so the thresholds are comparable.

The footnotes the agent surfaced unprompted

Agent footnotes added without asking:

• audiences ready for DSP activation have a 48-hour activation window.
• AMC long-term = one-year window; cohorts are bounded to 12 months.
• use percentile examples (e.g., top 71-100, ~748k out of 2.5M) and audience-size guardrails when creating RBA/LAL; avoid seed sizes below the platform minimum.
• AVG(lifetime_score) OVER () and AVG(value_score) OVER () must use the SAME population to produce comparable thresholds.
• run the year-over-year cohort migration check to spot cohorts moving from Growth Potential into High Value or sliding to Low Value.
• include non-ad-attributed conversions (Paid Features shopping insights) for a fuller revenue picture when available.

What happens next

• Export the four labeled audiences as rule-based audience manifests and activate into Amazon DSP with differentiated bidding: Higher bid for High Value, conversion-first creatives for Growth Potential, revenue-focused bids for Revenue Potential while excluding low-margin ASINs, and exclude or suppress Low Value audiences from high-cost buys. (See Swim Lane #3 and #4 from the Off-Amazon Conversions Playbook.)
• Schedule this query as a recurring AMC job monthly, aligned to cohort_month granularity, and push audiences after respecting the 48-hour DSP activation window for resulting audiences.
• Run the year-over-year diagnostic at the same cadence and produce cohort-migration charts that bucket users by percentile so you can detect movement between Growth Potential and High Value or slides toward Low Value.
• For ops handoff: publish an audience manifest with ASIN→category mappings, provide audience sizes and seed counts, and filter out cohorts under minimum seed thresholds before requesting DSP activation. Consider running overlap scoring against Persona Builder API outputs to avoid audience saturation.

Why this matters

A foundation model can write CLTV-shaped SQL. What it cannot reliably do is produce a query that compiles against the AMC table variants Audiences actually accepts, uses the exact composite window-function pattern the CLTV playbook prescribes, labels users with the four-segment taxonomy operators downstream are already wired for, and bakes in the 48-hour activation window plus the year-over-year diagnostic. The agent didn't have to remember any of that. It had to know where to look.

Composite Lifetime × Value cohorting.

Next guide in the series: moving from segmentation to actual bidding rules. What amazon_rules says you should do once you have a High Value cohort: Path to conversion sankey.