Segment AMC Customers by CLTV

"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 Amazon Marketing Cloud's 12-month customer lifetime value (CLTV) 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 Amazon demand-side platform (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.

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 return on ad spend (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 rule-based audience (RBA) and lookalike (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

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 Amazon Standard Identification Number (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.

The full workflow runs through the Amazon Agent Data layer: Amazon Ads MCP brings AMC and campaign signals, the Selling Partner MCP can add catalog context for ASIN grouping, Atlas grounds the CLTV taxonomy, and Skills turn the segment export into a reviewed recurring automation.

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.

{"workflow":"amc_cltv_segmentation","segments":["High Value","Growth Potential","Revenue Potential","Low Value"],"activation_wait_hours":48}