> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# LTV & Retention

> Cohort LTV, payback period, and long-term value analysis queries.

<Tip>
  Use the same query standards from the [SQL Query Library overview](/data-activation/template-resources/sql-query-library): `is_order_sm_valid = TRUE` for order analyses, `sm_store_id` scoping for multi-store setups, and `your_project` placeholders.
</Tip>

These templates cover cohort analysis, payback periods, and repeat purchase behavior. Some use the pre-aggregated cohort table for efficiency.

<Info>
  If you use the cohort LTV table (`rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`):

  * Always filter **one** cohort dimension (e.g., `acquisition_order_filter_dimension = 'source/medium'`)
  * Always include `sm_order_line_type = 'all_orders'` unless you explicitly want a subset
</Info>

<AccordionGroup>
  <Accordion title="Cohort table: available dimensions">
    **What you'll learn:** Which cohort dimensions are available in the LTV table. Run this first to see what you can filter by (e.g., `source/medium`, `discount_code`, `order_type_(sub_vs._one_time)`).

    ```sql theme={null}
    -- Assumptions: timeframe=all_time | metric=discovery | grain=acquisition_order_filter_dimension | scope=cohort_table_only
    SELECT DISTINCT
      acquisition_order_filter_dimension
    FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
    ORDER BY 1;
    ```
  </Accordion>

  <Accordion title="3m/6m retention + 6m LTV by acquisition source/medium (last 12 cohort months)">
    **What you'll learn:** Which acquisition sources produce customers with the best retention and lifetime value at the 3 and 6 month marks. Use this to optimize ad spend toward channels that deliver long-term value, not just initial conversions.

    ```sql theme={null}
    -- Assumptions: timeframe=last_12_cohort_months | metric=retention_pct+ltv_6m | grain=source_medium | scope=cohort_table_all_orders
    WITH pivoted AS (
      SELECT
        acquisition_order_filter_dimension_value AS source_medium,
        cohort_month,
        ANY_VALUE(cohort_size) AS cohort_size,
        MAX(IF(months_since_first_order = 3, customer_count, NULL)) AS customers_m3,
        MAX(IF(months_since_first_order = 6, customer_count, NULL)) AS customers_m6,
        MAX(IF(months_since_first_order = 6, cumulative_order_net_revenue, NULL)) AS cumulative_order_net_revenue_m6
      FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
      WHERE acquisition_order_filter_dimension = 'source/medium'
        AND sm_order_line_type = 'all_orders'
        AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
        AND months_since_first_order IN (3, 6)
      GROUP BY 1, 2
    )
    SELECT
      source_medium,
      SUM(cohort_size) AS cohort_customers,
      SAFE_DIVIDE(SUM(customers_m3), NULLIF(SUM(cohort_size), 0)) AS retention_m3,
      SAFE_DIVIDE(SUM(customers_m6), NULLIF(SUM(cohort_size), 0)) AS retention_m6,
      SAFE_DIVIDE(SUM(cumulative_order_net_revenue_m6), NULLIF(SUM(cohort_size), 0)) AS ltv_net_per_customer_m6
    FROM pivoted
    GROUP BY 1
    HAVING cohort_customers >= 200
    ORDER BY retention_m6 DESC
    LIMIT 25;
    ```
  </Accordion>

  <Accordion title="Payback period by acquisition source/medium (cohort table, last 12 cohort months)">
    **What you'll learn:** Roughly how many **months** it takes for each acquisition source/medium cohort to “pay back” its CAC (when cohort cumulative net revenue per customer first exceeds `cost_per_acquisition`). Only interpret rows where your cohort model populates CAC for that cohort.

    ```sql theme={null}
    -- Assumptions: timeframe=last_12_cohort_months | metric=payback_months | grain=source_medium | scope=cohort_table_all_orders
    WITH base AS (
      SELECT
        acquisition_order_filter_dimension_value AS source_medium,
        cohort_month,
        months_since_first_order,
        cohort_size,
        cost_per_acquisition,
        cumulative_order_net_revenue
      FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
      WHERE acquisition_order_filter_dimension = 'source/medium'
        AND sm_order_line_type = 'all_orders'
        AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
        AND months_since_first_order BETWEEN 0 AND 12
        AND cost_per_acquisition IS NOT NULL
        AND cost_per_acquisition > 0
    ),
    per_cohort AS (
      SELECT
        source_medium,
        cohort_month,
        ANY_VALUE(cohort_size) AS cohort_size,
        ANY_VALUE(cost_per_acquisition) AS cac_per_customer,
        MIN(
          IF(
            SAFE_DIVIDE(cumulative_order_net_revenue, NULLIF(cohort_size, 0)) >= cost_per_acquisition,
            months_since_first_order,
            NULL
          )
        ) AS payback_months
      FROM base
      GROUP BY 1, 2
    )
    SELECT
      source_medium,
      SUM(cohort_size) AS cohort_customers,
      SAFE_DIVIDE(
        SUM(CAST(payback_months AS FLOAT64) * cohort_size),
        NULLIF(SUM(CASE WHEN payback_months IS NOT NULL THEN cohort_size ELSE 0 END), 0)
      ) AS avg_payback_months_weighted,
      SAFE_DIVIDE(
        SUM(CASE WHEN payback_months IS NOT NULL THEN cohort_size ELSE 0 END),
        NULLIF(SUM(cohort_size), 0)
      ) AS pct_customers_in_cohorts_with_payback_within_12m
    FROM per_cohort
    GROUP BY 1
    HAVING cohort_customers >= 200
    ORDER BY avg_payback_months_weighted ASC;
    ```
  </Accordion>

  <Accordion title="LTV:CAC ratio by acquisition source/medium (6m net LTV vs CAC, last 12 cohort months)">
    **What you'll learn:** Whether you’re getting enough 6‑month net revenue per acquired customer relative to CAC (a simple **LTV:CAC** sanity check). Only interpret rows where your cohort model populates CAC for that cohort.

    ```sql theme={null}
    -- Assumptions: timeframe=last_12_cohort_months | metric=ltv_to_cac_ratio_6m | grain=source_medium | scope=cohort_table_all_orders
    WITH per_cohort AS (
      SELECT
        acquisition_order_filter_dimension_value AS source_medium,
        cohort_month,
        ANY_VALUE(cohort_size) AS cohort_size,
        ANY_VALUE(cost_per_acquisition) AS cac_per_customer,
        MAX(IF(months_since_first_order = 6, cumulative_order_net_revenue, NULL)) AS cumulative_order_net_revenue_m6
      FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
      WHERE acquisition_order_filter_dimension = 'source/medium'
        AND sm_order_line_type = 'all_orders'
        AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
        AND months_since_first_order = 6
        AND cost_per_acquisition IS NOT NULL
        AND cost_per_acquisition > 0
      GROUP BY 1, 2
    )
    SELECT
      source_medium,
      SUM(cohort_size) AS cohort_customers,
      SAFE_DIVIDE(SUM(cumulative_order_net_revenue_m6), NULLIF(SUM(cohort_size), 0)) AS ltv_net_per_customer_m6,
      SAFE_DIVIDE(SUM(cac_per_customer * cohort_size), NULLIF(SUM(cohort_size), 0)) AS cac_per_customer_weighted,
      SAFE_DIVIDE(
        SAFE_DIVIDE(SUM(cumulative_order_net_revenue_m6), NULLIF(SUM(cohort_size), 0)),
        NULLIF(SAFE_DIVIDE(SUM(cac_per_customer * cohort_size), NULLIF(SUM(cohort_size), 0)), 0)
      ) AS ltv_to_cac_ratio_m6
    FROM per_cohort
    GROUP BY 1
    HAVING cohort_customers >= 200
    ORDER BY ltv_to_cac_ratio_m6 DESC
    LIMIT 25;
    ```
  </Accordion>

  <Accordion title="Top discount-code cohorts by 6m retention + 12m LTV (last 12 cohort months)">
    **What you'll learn:** Which discount codes attract customers who stick around and spend more over time. Identify promo codes that bring loyal buyers vs. one-time bargain hunters.

    ```sql theme={null}
    -- Assumptions: timeframe=last_12_cohort_months | metric=retention_m6+ltv_12m | grain=discount_code | scope=cohort_table_all_orders
    WITH pivoted AS (
      SELECT
        acquisition_order_filter_dimension_value AS discount_code,
        cohort_month,
        ANY_VALUE(cohort_size) AS cohort_size,
        MAX(IF(months_since_first_order = 6, customer_count, NULL)) AS customers_m6,
        MAX(IF(months_since_first_order = 12, cumulative_order_net_revenue, NULL)) AS cumulative_order_net_revenue_m12
      FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
      WHERE acquisition_order_filter_dimension = 'discount_code'
        AND sm_order_line_type = 'all_orders'
        AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
        AND months_since_first_order IN (6, 12)
        AND acquisition_order_filter_dimension_value IS NOT NULL
        AND acquisition_order_filter_dimension_value != ''
      GROUP BY 1, 2
    ),
    aggregated AS (
      SELECT
        discount_code,
        SUM(cohort_size) AS cohort_customers,
        SAFE_DIVIDE(SUM(customers_m6), NULLIF(SUM(cohort_size), 0)) AS retention_m6,
        SAFE_DIVIDE(SUM(cumulative_order_net_revenue_m12), NULLIF(SUM(cohort_size), 0)) AS ltv_net_per_customer_m12
      FROM pivoted
      GROUP BY 1
      HAVING cohort_customers >= 100
    )
    SELECT
      discount_code,
      cohort_customers,
      retention_m6,
      ltv_net_per_customer_m12
    FROM aggregated
    ORDER BY ltv_net_per_customer_m12 DESC
    LIMIT 25;
    ```
  </Accordion>

  <Accordion title="Subscription vs one-time cohorts: 6m retention + 12m LTV (last 12 cohort months)">
    **What you'll learn:** How customers who start with a subscription compare to one-time buyers in terms of retention and lifetime value. Quantify the LTV advantage (or disadvantage) of your subscription program.

    ```sql theme={null}
    -- Assumptions: timeframe=last_12_cohort_months | metric=retention_m6+ltv_12m | grain=first_order_type | scope=cohort_table_all_orders
    WITH pivoted AS (
      SELECT
        acquisition_order_filter_dimension_value AS first_order_type,
        cohort_month,
        ANY_VALUE(cohort_size) AS cohort_size,
        MAX(IF(months_since_first_order = 6, customer_count, NULL)) AS customers_m6,
        MAX(IF(months_since_first_order = 12, cumulative_order_net_revenue, NULL)) AS cumulative_order_net_revenue_m12
      FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
      WHERE acquisition_order_filter_dimension = 'order_type_(sub_vs._one_time)'
        AND sm_order_line_type = 'all_orders'
        AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
        AND months_since_first_order IN (6, 12)
      GROUP BY 1, 2
    )
    SELECT
      first_order_type,
      SUM(cohort_size) AS cohort_customers,
      SAFE_DIVIDE(SUM(customers_m6), NULLIF(SUM(cohort_size), 0)) AS retention_m6,
      SAFE_DIVIDE(SUM(cumulative_order_net_revenue_m12), NULLIF(SUM(cohort_size), 0)) AS ltv_net_per_customer_m12
    FROM pivoted
    GROUP BY 1
    HAVING cohort_customers >= 50
    ORDER BY ltv_net_per_customer_m12 DESC;
    ```
  </Accordion>

  <Accordion title="Repeat purchase rate (paid orders only) within 30/60/90 days by acquisition source/medium (first valid orders in last 12 months)">
    **What you'll learn:** How quickly customers come back to buy again after their first valid purchase, broken out by acquisition source/medium. This version counts only repeat orders with `order_net_revenue > 0` (so \$0 replacements/comp orders don’t inflate “purchase” rates).

    ```sql theme={null}
    -- Assumptions: timeframe=first_valid_orders_last_12_months | metric=repeat_rate_30_60_90 | grain=source_medium | scope=valid_paid_orders_only
    WITH first_valid_orders AS (
      SELECT
        sm_customer_key,
        order_processed_at_local_datetime AS first_order_at_local_datetime,
        COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    ),
    per_customer AS (
      SELECT
        fo.source_medium,
        fo.sm_customer_key,
        MIN(DATETIME_DIFF(o.order_processed_at_local_datetime, fo.first_order_at_local_datetime, DAY)) AS first_repeat_day,
        COUNT(DISTINCT o.sm_order_key) AS repeat_orders_90d,
        COALESCE(SUM(o.order_net_revenue), 0) AS repeat_revenue_90d
      FROM first_valid_orders fo
      LEFT JOIN `your_project.sm_transformed_v2.obt_orders` o
        ON o.sm_customer_key = fo.sm_customer_key
        AND o.is_order_sm_valid = TRUE
        AND o.order_cancelled_at IS NULL
        AND o.order_net_revenue > 0
        AND o.order_processed_at_local_datetime > fo.first_order_at_local_datetime
        AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 90 DAY)
      GROUP BY 1, 2
    )
    SELECT
      source_medium,
      COUNT(*) AS customers,
      COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30) AS customers_repeat_30d,
      SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30), NULLIF(COUNT(*), 0)) AS repeat_rate_30d,
      COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60) AS customers_repeat_60d,
      SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60), NULLIF(COUNT(*), 0)) AS repeat_rate_60d,
      COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90) AS customers_repeat_90d,
      SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90), NULLIF(COUNT(*), 0)) AS repeat_rate_90d,
      AVG(repeat_orders_90d) AS avg_repeat_orders_90d,
      AVG(repeat_revenue_90d) AS avg_repeat_revenue_90d
    FROM per_customer
    GROUP BY 1
    HAVING customers >= 200
    ORDER BY repeat_rate_90d DESC
    LIMIT 25;
    ```
  </Accordion>

  <Accordion title="Repeat purchase rate (paid orders only) within 30/60/90 days by subscription vs one-time first order (first valid orders in last 12 months)">
    **What you'll learn:** A practical retention proxy for subscription programs: customers whose **first valid order** was subscription vs one-time, and how quickly they return to buy again. This version counts only repeat orders with `order_net_revenue > 0`.

    ```sql theme={null}
    -- Assumptions: timeframe=first_valid_orders_last_12_months | metric=repeat_rate_30_60_90 | grain=first_order_type | scope=valid_paid_orders_only
    WITH first_valid_orders AS (
      SELECT
        sm_customer_key,
        order_processed_at_local_datetime AS first_order_at_local_datetime,
        CASE
          WHEN is_subscription_order = TRUE THEN 'subscription_first_order'
          ELSE 'one_time_first_order'
        END AS first_order_type
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    ),
    per_customer AS (
      SELECT
        fo.first_order_type,
        fo.sm_customer_key,
        MIN(DATETIME_DIFF(o.order_processed_at_local_datetime, fo.first_order_at_local_datetime, DAY)) AS first_repeat_day,
        COUNT(DISTINCT o.sm_order_key) AS repeat_orders_90d,
        COALESCE(SUM(o.order_net_revenue), 0) AS repeat_revenue_90d
      FROM first_valid_orders fo
      LEFT JOIN `your_project.sm_transformed_v2.obt_orders` o
        ON o.sm_customer_key = fo.sm_customer_key
        AND o.is_order_sm_valid = TRUE
        AND o.order_cancelled_at IS NULL
        AND o.order_net_revenue > 0
        AND o.order_processed_at_local_datetime > fo.first_order_at_local_datetime
        AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 90 DAY)
      GROUP BY 1, 2
    )
    SELECT
      first_order_type,
      COUNT(*) AS customers,
      COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30) AS customers_repeat_30d,
      SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30), NULLIF(COUNT(*), 0)) AS repeat_rate_30d,
      COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60) AS customers_repeat_60d,
      SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60), NULLIF(COUNT(*), 0)) AS repeat_rate_60d,
      COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90) AS customers_repeat_90d,
      SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90), NULLIF(COUNT(*), 0)) AS repeat_rate_90d,
      AVG(repeat_orders_90d) AS avg_repeat_orders_90d,
      AVG(repeat_revenue_90d) AS avg_repeat_revenue_90d
    FROM per_customer
    GROUP BY 1
    HAVING customers >= 200
    ORDER BY repeat_rate_90d DESC;
    ```
  </Accordion>

  <Accordion title="Repeat purchase rate (paid orders only) within 30/60/90 days by first-order AOV bucket (first valid orders in last 12 months)">
    **What you'll learn:** Whether high-AOV first purchases actually translate into better short-term retention. This version counts only repeat orders with `order_net_revenue > 0` so that \$0 orders don’t inflate “purchase” rates.

    ```sql theme={null}
    -- Assumptions: timeframe=first_valid_orders_last_12_months | metric=repeat_rate_30_60_90 | grain=first_order_aov_bucket | scope=valid_paid_orders_only
    WITH first_valid_orders AS (
      SELECT
        sm_customer_key,
        order_processed_at_local_datetime AS first_order_at_local_datetime,
        order_net_revenue AS first_order_net_revenue
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    ),
    bucketed AS (
      SELECT
        sm_customer_key,
        first_order_at_local_datetime,
        CASE
          WHEN first_order_net_revenue < 25 THEN '$0–$25'
          WHEN first_order_net_revenue < 50 THEN '$25–$50'
          WHEN first_order_net_revenue < 100 THEN '$50–$100'
          WHEN first_order_net_revenue < 200 THEN '$100–$200'
          ELSE '$200+'
        END AS first_order_aov_bucket
      FROM first_valid_orders
    ),
    per_customer AS (
      SELECT
        b.first_order_aov_bucket,
        b.sm_customer_key,
        MIN(DATETIME_DIFF(o.order_processed_at_local_datetime, b.first_order_at_local_datetime, DAY)) AS first_repeat_day,
        COUNT(DISTINCT o.sm_order_key) AS repeat_orders_90d,
        COALESCE(SUM(o.order_net_revenue), 0) AS repeat_revenue_90d
      FROM bucketed b
      LEFT JOIN `your_project.sm_transformed_v2.obt_orders` o
        ON o.sm_customer_key = b.sm_customer_key
        AND o.is_order_sm_valid = TRUE
        AND o.order_cancelled_at IS NULL
        AND o.order_net_revenue > 0
        AND o.order_processed_at_local_datetime > b.first_order_at_local_datetime
        AND o.order_processed_at_local_datetime < DATETIME_ADD(b.first_order_at_local_datetime, INTERVAL 90 DAY)
      GROUP BY 1, 2
    )
    SELECT
      first_order_aov_bucket,
      COUNT(*) AS customers,
      COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30) AS customers_repeat_30d,
      SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 30), NULLIF(COUNT(*), 0)) AS repeat_rate_30d,
      COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60) AS customers_repeat_60d,
      SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 60), NULLIF(COUNT(*), 0)) AS repeat_rate_60d,
      COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90) AS customers_repeat_90d,
      SAFE_DIVIDE(COUNTIF(first_repeat_day IS NOT NULL AND first_repeat_day <= 90), NULLIF(COUNT(*), 0)) AS repeat_rate_90d,
      AVG(repeat_orders_90d) AS avg_repeat_orders_90d,
      AVG(repeat_revenue_90d) AS avg_repeat_revenue_90d
    FROM per_customer
    GROUP BY 1
    ORDER BY CASE first_order_aov_bucket
      WHEN '$0–$25' THEN 1
      WHEN '$25–$50' THEN 2
      WHEN '$50–$100' THEN 3
      WHEN '$100–$200' THEN 4
      WHEN '$200+' THEN 5
      ELSE 99
    END;
    ```
  </Accordion>

  <Accordion title="90‑day LTV by first-order source/medium (dynamic, last 12 months)">
    **What you'll learn:** Which acquisition source/mediums produce higher 90‑day LTV (including the first order). This is a dynamic alternative to cohort tables when you want a strict “first purchase → next 90 days” window.

    ```sql theme={null}
    -- Assumptions: timeframe=first_valid_orders_last_12_months | metric=90d_LTV | grain=first_order_source_medium | scope=valid_orders_only
    WITH first_valid_orders AS (
      SELECT
        sm_customer_key,
        order_processed_at_local_datetime AS first_order_at_local_datetime,
        COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS first_order_source_medium
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    ),
    customer_90d_ltv AS (
      SELECT
        fo.first_order_source_medium,
        fo.sm_customer_key,
        SUM(o.order_net_revenue) AS ltv_90d
      FROM first_valid_orders fo
      INNER JOIN `your_project.sm_transformed_v2.obt_orders` o
        ON o.sm_customer_key = fo.sm_customer_key
      WHERE o.is_order_sm_valid = TRUE
        AND o.order_cancelled_at IS NULL
        AND o.order_processed_at_local_datetime >= fo.first_order_at_local_datetime
        AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 90 DAY)
      GROUP BY 1, 2
    )
    SELECT
      first_order_source_medium AS source_medium,
      COUNT(*) AS customers,
      AVG(ltv_90d) AS avg_ltv_90d,
      SUM(ltv_90d) AS total_ltv_90d
    FROM customer_90d_ltv
    GROUP BY 1
    HAVING customers >= 200
    ORDER BY avg_ltv_90d DESC
    LIMIT 25;
    ```
  </Accordion>

  <Accordion title="90‑day LTV by first-order discount code (single-code only + no-code baseline, last 12 months)">
    **What you'll learn:** How 90‑day LTV differs for customers whose first valid order used **exactly one** discount code vs no code. Customers whose first order has multiple codes are excluded to avoid ambiguity/double counting. Use the “Multiple discount codes prevalence” diagnostic before interpreting results.

    ```sql theme={null}
    -- Assumptions: timeframe=first_valid_orders_last_12_months | metric=90d_LTV | grain=first_order_discount_code | scope=valid_orders_only_single_or_no_code
    WITH first_valid_orders AS (
      SELECT
        sm_customer_key,
        order_processed_at_local_datetime AS first_order_at_local_datetime,
        order_discount_codes_csv
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    ),
    parsed_codes AS (
      SELECT
        sm_customer_key,
        first_order_at_local_datetime,
        ARRAY(
          SELECT LOWER(TRIM(code_raw))
          FROM UNNEST(SPLIT(COALESCE(order_discount_codes_csv, ''), ',')) AS code_raw
          WHERE TRIM(code_raw) != ''
            AND LOWER(TRIM(code_raw)) NOT IN ('(none)')
        ) AS codes
      FROM first_valid_orders
    ),
    cohort AS (
      SELECT
        sm_customer_key,
        first_order_at_local_datetime,
        ARRAY_LENGTH(codes) AS code_count,
        IF(ARRAY_LENGTH(codes) = 1, codes[OFFSET(0)], '(no_code)') AS first_order_discount_code
      FROM parsed_codes
      WHERE ARRAY_LENGTH(codes) IN (0, 1)
    ),
    customer_90d_ltv AS (
      SELECT
        c.first_order_discount_code,
        c.sm_customer_key,
        SUM(o.order_net_revenue) AS ltv_90d
      FROM cohort c
      INNER JOIN `your_project.sm_transformed_v2.obt_orders` o
        ON o.sm_customer_key = c.sm_customer_key
      WHERE o.is_order_sm_valid = TRUE
        AND o.order_cancelled_at IS NULL
        AND o.order_processed_at_local_datetime >= c.first_order_at_local_datetime
        AND o.order_processed_at_local_datetime < DATETIME_ADD(c.first_order_at_local_datetime, INTERVAL 90 DAY)
      GROUP BY 1, 2
    )
    SELECT
      first_order_discount_code AS discount_code,
      COUNT(*) AS customers,
      AVG(ltv_90d) AS avg_ltv_90d,
      SUM(ltv_90d) AS total_ltv_90d
    FROM customer_90d_ltv
    GROUP BY 1
    HAVING customers >= 50
    ORDER BY avg_ltv_90d DESC
    LIMIT 25;
    ```
  </Accordion>

  <Accordion title="First-order refund rate by acquisition source/medium (first valid orders in last 12 months)">
    **What you'll learn:** Which acquisition source/mediums produce higher refund rates on the **first valid order** (by order count and by revenue). This is a common “traffic quality” question and helps spot channels driving mismatched expectations.

    ```sql theme={null}
    -- Assumptions: timeframe=first_valid_orders_last_12_months | metric=first_order_refund_rate | grain=first_order_source_medium | scope=valid_orders_only
    WITH first_valid_orders AS (
      SELECT
        sm_customer_key,
        sm_order_key,
        COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS first_order_source_medium,
        order_total_refunds,
        order_net_revenue_before_refunds
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    )
    SELECT
      first_order_source_medium AS source_medium,
      COUNT(DISTINCT sm_order_key) AS first_orders,
      COUNTIF(ABS(order_total_refunds) > 0) AS refunded_first_orders,
      SAFE_DIVIDE(COUNTIF(ABS(order_total_refunds) > 0), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS refund_rate_orders,
      ABS(SUM(order_total_refunds)) AS refund_amount,
      SUM(order_net_revenue_before_refunds) AS revenue_before_refunds,
      SAFE_DIVIDE(ABS(SUM(order_total_refunds)), NULLIF(SUM(order_net_revenue_before_refunds), 0)) AS refund_rate_revenue
    FROM first_valid_orders
    GROUP BY 1
    HAVING first_orders >= 200
    ORDER BY refund_rate_revenue DESC
    LIMIT 25;
    ```
  </Accordion>

  <Accordion title="90‑day LTV by first-order source system and sales channel (last 12 months)">
    **What you'll learn:** How 90‑day LTV differs by the commerce platform (`source_system`) and sales channel (`sm_channel`) of the first valid order. This helps separate marketplace/POS behavior from online DTC without mixing attribution concepts.

    ```sql theme={null}
    -- Assumptions: timeframe=first_valid_orders_last_12_months | metric=90d_LTV | grain=first_order_source_system+sm_channel | scope=valid_orders_only
    WITH first_valid_orders AS (
      SELECT
        sm_customer_key,
        order_processed_at_local_datetime AS first_order_at_local_datetime,
        COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS first_order_source_system,
        COALESCE(NULLIF(LOWER(TRIM(sm_channel)), ''), '(unknown)') AS first_order_sm_channel
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    ),
    customer_90d_ltv AS (
      SELECT
        fo.first_order_source_system,
        fo.first_order_sm_channel,
        fo.sm_customer_key,
        SUM(o.order_net_revenue) AS ltv_90d
      FROM first_valid_orders fo
      INNER JOIN `your_project.sm_transformed_v2.obt_orders` o
        ON o.sm_customer_key = fo.sm_customer_key
      WHERE o.is_order_sm_valid = TRUE
        AND o.order_cancelled_at IS NULL
        AND o.order_processed_at_local_datetime >= fo.first_order_at_local_datetime
        AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 90 DAY)
      GROUP BY 1, 2, 3
    )
    SELECT
      first_order_source_system AS source_system,
      first_order_sm_channel AS sm_channel,
      COUNT(*) AS customers,
      AVG(ltv_90d) AS avg_ltv_90d,
      SUM(ltv_90d) AS total_ltv_90d
    FROM customer_90d_ltv
    GROUP BY 1, 2
    HAVING customers >= 200
    ORDER BY avg_ltv_90d DESC
    LIMIT 50;
    ```
  </Accordion>

  <Accordion title="Cohort-table vs dynamic reconciliation (6m vs 180d) for source/medium (last 6 cohort months)">
    **What you'll learn:** A sanity check to reconcile precomputed cohort-table LTV (month-offset based) with a dynamic 180-day LTV window from `obt_orders`. Differences can indicate mismatched cohort definitions or expectation gaps (month buckets vs day windows).

    ```sql theme={null}
    -- Assumptions: timeframe=last_6_cohort_months | metric=ltv_reconciliation | grain=cohort_month+source_medium | scope=cohort_table_vs_dynamic
    WITH cohort_table AS (
      SELECT
        cohort_month,
        COALESCE(NULLIF(LOWER(TRIM(acquisition_order_filter_dimension_value)), ''), '(none) / (none)') AS source_medium,
        ANY_VALUE(cohort_size) AS cohort_size,
        MAX(IF(months_since_first_order = 6, cumulative_order_net_revenue, NULL)) AS cumulative_order_net_revenue_m6
      FROM `your_project.sm_transformed_v2.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
      WHERE acquisition_order_filter_dimension = 'source/medium'
        AND sm_order_line_type = 'all_orders'
        AND cohort_month >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 6 MONTH)
        AND months_since_first_order = 6
      GROUP BY 1, 2
    ),
    first_valid_orders AS (
      SELECT
        sm_customer_key,
        order_processed_at_local_datetime AS first_order_at_local_datetime,
        DATE_TRUNC(DATE(order_processed_at_local_datetime), MONTH) AS cohort_month,
        COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 6 MONTH)
    ),
    dynamic_180d AS (
      SELECT
        fo.cohort_month,
        fo.source_medium,
        fo.sm_customer_key,
        SUM(o.order_net_revenue) AS ltv_180d
      FROM first_valid_orders fo
      INNER JOIN `your_project.sm_transformed_v2.obt_orders` o
        ON o.sm_customer_key = fo.sm_customer_key
      WHERE o.is_order_sm_valid = TRUE
        AND o.order_cancelled_at IS NULL
        AND o.order_processed_at_local_datetime >= fo.first_order_at_local_datetime
        AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 180 DAY)
      GROUP BY 1, 2, 3
    ),
    dynamic_rollup AS (
      SELECT
        cohort_month,
        source_medium,
        COUNT(*) AS cohort_customers_dynamic,
        AVG(ltv_180d) AS ltv_net_per_customer_180d
      FROM dynamic_180d
      GROUP BY 1, 2
    )
    SELECT
      d.cohort_month,
      d.source_medium,
      d.cohort_customers_dynamic,
      d.ltv_net_per_customer_180d,
      c.cohort_size AS cohort_customers_table,
      SAFE_DIVIDE(c.cumulative_order_net_revenue_m6, NULLIF(c.cohort_size, 0)) AS ltv_net_per_customer_m6,
      d.ltv_net_per_customer_180d - SAFE_DIVIDE(c.cumulative_order_net_revenue_m6, NULLIF(c.cohort_size, 0)) AS diff_dynamic_minus_table,
      SAFE_DIVIDE(
        d.ltv_net_per_customer_180d - SAFE_DIVIDE(c.cumulative_order_net_revenue_m6, NULLIF(c.cohort_size, 0)),
        NULLIF(SAFE_DIVIDE(c.cumulative_order_net_revenue_m6, NULLIF(c.cohort_size, 0)), 0)
      ) AS pct_diff_dynamic_vs_table
    FROM dynamic_rollup d
    LEFT JOIN cohort_table c
      ON d.cohort_month = c.cohort_month
      AND d.source_medium = c.source_medium
    WHERE d.cohort_customers_dynamic >= 200
    ORDER BY ABS(pct_diff_dynamic_vs_table) DESC
    LIMIT 50;
    ```
  </Accordion>

  <Accordion title="Which initial products lead to the highest 90‑day LTV? (primary first‑order SKU, last 12 months)">
    **What you'll learn:** Which **primary first‑order SKU** (one SKU per customer, chosen as the highest net‑revenue line item on the first valid order) is associated with higher 90‑day LTV. Use this to identify “starter products” to feature in acquisition campaigns and new customer bundles.

    ```sql theme={null}
    -- Assumptions: timeframe=first_valid_orders_last_12_months | metric=90d_LTV=SUM(order_net_revenue_90d) | grain=primary_first_sku | scope=valid_orders_only
    WITH first_valid_orders AS (
      SELECT
        sm_customer_key,
        sm_order_key,
        order_processed_at_local_datetime AS first_order_at_local_datetime
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    ),
    first_order_primary_sku AS (
      SELECT
        fo.sm_customer_key,
        ol.sku,
        ANY_VALUE(ol.product_title) AS product_title,
        SUM(ol.order_line_net_revenue) AS first_order_sku_net_revenue
      FROM first_valid_orders fo
      INNER JOIN `your_project.sm_transformed_v2.obt_order_lines` ol
        ON ol.sm_order_key = fo.sm_order_key
      WHERE ol.is_order_sm_valid = TRUE
        AND ol.sku IS NOT NULL
        AND NOT REGEXP_CONTAINS(ol.product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
      GROUP BY 1, 2
      QUALIFY ROW_NUMBER() OVER (
        PARTITION BY fo.sm_customer_key
        ORDER BY first_order_sku_net_revenue DESC
      ) = 1
    ),
    customer_90d_ltv AS (
      SELECT
        fo.sm_customer_key,
        SUM(o.order_net_revenue) AS ltv_90d
      FROM first_valid_orders fo
      INNER JOIN `your_project.sm_transformed_v2.obt_orders` o
        ON o.sm_customer_key = fo.sm_customer_key
      WHERE o.is_order_sm_valid = TRUE
        AND o.order_cancelled_at IS NULL
        AND o.order_processed_at_local_datetime >= fo.first_order_at_local_datetime
        AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 90 DAY)
      GROUP BY 1
    )
    SELECT
      pos.sku,
      pos.product_title,
      COUNT(*) AS customers,
      AVG(ltv_90d) AS avg_ltv_90d,
      SUM(ltv_90d) AS total_ltv_90d
    FROM first_order_primary_sku pos
    INNER JOIN customer_90d_ltv ltv
      ON pos.sm_customer_key = ltv.sm_customer_key
    GROUP BY 1, 2
    HAVING customers >= 20
    ORDER BY avg_ltv_90d DESC
    LIMIT 25;
    ```
  </Accordion>

  <Accordion title="90‑day LTV by first-order product type (primary first‑order attribute, last 12 months)">
    **What you'll learn:** Which **product types** tend to create higher 90‑day customer value when they appear as the “primary” first-order item (one product type per customer, selected by highest first-order net revenue). This is a scalable alternative to SKU-level LTV.

    ```sql theme={null}
    -- Assumptions: timeframe=first_valid_orders_last_12_months | metric=90d_LTV | grain=primary_first_product_type | scope=valid_orders_only
    WITH first_valid_orders AS (
      SELECT
        sm_customer_key,
        sm_order_key,
        order_processed_at_local_datetime AS first_order_at_local_datetime
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    ),
    first_order_primary_product_type AS (
      SELECT
        fo.sm_customer_key,
        COALESCE(NULLIF(LOWER(TRIM(ol.product_type)), ''), '(unknown)') AS product_type,
        SUM(ol.order_line_net_revenue) AS first_order_type_net_revenue
      FROM first_valid_orders fo
      INNER JOIN `your_project.sm_transformed_v2.obt_order_lines` ol
        ON ol.sm_order_key = fo.sm_order_key
      WHERE ol.is_order_sm_valid = TRUE
        AND ol.product_type IS NOT NULL
        AND NOT REGEXP_CONTAINS(ol.product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
      GROUP BY 1, 2
      QUALIFY ROW_NUMBER() OVER (
        PARTITION BY fo.sm_customer_key
        ORDER BY first_order_type_net_revenue DESC
      ) = 1
    ),
    customer_90d_ltv AS (
      SELECT
        fo.sm_customer_key,
        SUM(o.order_net_revenue) AS ltv_90d
      FROM first_valid_orders fo
      INNER JOIN `your_project.sm_transformed_v2.obt_orders` o
        ON o.sm_customer_key = fo.sm_customer_key
      WHERE o.is_order_sm_valid = TRUE
        AND o.order_cancelled_at IS NULL
        AND o.order_processed_at_local_datetime >= fo.first_order_at_local_datetime
        AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 90 DAY)
      GROUP BY 1
    )
    SELECT
      pt.product_type,
      COUNT(*) AS customers,
      AVG(ltv_90d) AS avg_ltv_90d,
      SUM(ltv_90d) AS total_ltv_90d
    FROM first_order_primary_product_type pt
    INNER JOIN customer_90d_ltv ltv
      ON pt.sm_customer_key = ltv.sm_customer_key
    GROUP BY 1
    HAVING customers >= 50
    ORDER BY avg_ltv_90d DESC
    LIMIT 25;
    ```
  </Accordion>

  <Accordion title="90‑day LTV by first-order product vendor (primary first‑order attribute, last 12 months)">
    **What you'll learn:** Which **vendors** tend to create higher 90‑day customer value when they appear as the “primary” first-order item (one vendor per customer, selected by highest first-order net revenue). Useful for wholesale/brand partnerships and merchandising.

    ```sql theme={null}
    -- Assumptions: timeframe=first_valid_orders_last_12_months | metric=90d_LTV | grain=primary_first_product_vendor | scope=valid_orders_only
    WITH first_valid_orders AS (
      SELECT
        sm_customer_key,
        sm_order_key,
        order_processed_at_local_datetime AS first_order_at_local_datetime
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_valid_order_index = 1
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    ),
    first_order_primary_vendor AS (
      SELECT
        fo.sm_customer_key,
        COALESCE(NULLIF(LOWER(TRIM(ol.product_vendor)), ''), '(unknown)') AS product_vendor,
        SUM(ol.order_line_net_revenue) AS first_order_vendor_net_revenue
      FROM first_valid_orders fo
      INNER JOIN `your_project.sm_transformed_v2.obt_order_lines` ol
        ON ol.sm_order_key = fo.sm_order_key
      WHERE ol.is_order_sm_valid = TRUE
        AND ol.product_vendor IS NOT NULL
        AND NOT REGEXP_CONTAINS(ol.product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
      GROUP BY 1, 2
      QUALIFY ROW_NUMBER() OVER (
        PARTITION BY fo.sm_customer_key
        ORDER BY first_order_vendor_net_revenue DESC
      ) = 1
    ),
    customer_90d_ltv AS (
      SELECT
        fo.sm_customer_key,
        SUM(o.order_net_revenue) AS ltv_90d
      FROM first_valid_orders fo
      INNER JOIN `your_project.sm_transformed_v2.obt_orders` o
        ON o.sm_customer_key = fo.sm_customer_key
      WHERE o.is_order_sm_valid = TRUE
        AND o.order_cancelled_at IS NULL
        AND o.order_processed_at_local_datetime >= fo.first_order_at_local_datetime
        AND o.order_processed_at_local_datetime < DATETIME_ADD(fo.first_order_at_local_datetime, INTERVAL 90 DAY)
      GROUP BY 1
    )
    SELECT
      v.product_vendor,
      COUNT(*) AS customers,
      AVG(ltv_90d) AS avg_ltv_90d,
      SUM(ltv_90d) AS total_ltv_90d
    FROM first_order_primary_vendor v
    INNER JOIN customer_90d_ltv ltv
      ON v.sm_customer_key = ltv.sm_customer_key
    GROUP BY 1
    HAVING customers >= 50
    ORDER BY avg_ltv_90d DESC
    LIMIT 25;
    ```
  </Accordion>

  <Accordion title="Typical time between orders for non-subscription customers (last 12 months)">
    **What you'll learn:** The distribution of days between repeat purchases for non-subscription customers. Use this to time your re-engagement emails and identify the optimal window for replenishment reminders.

    ```sql theme={null}
    -- Assumptions: timeframe=last_12_months | metric=days_between_orders_distribution | grain=days_between_orders | scope=non_subscription_customers_only
    WITH subscription_customers AS (
      SELECT DISTINCT
        sm_customer_key
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND is_subscription_order = TRUE
        AND sm_customer_key IS NOT NULL
    ),
    non_subscription_orders AS (
      SELECT
        sm_customer_key,
        DATE(order_processed_at_local_datetime) AS order_date
      FROM `your_project.sm_transformed_v2.obt_orders`
      WHERE is_order_sm_valid = TRUE
        AND order_cancelled_at IS NULL
        AND sm_customer_key IS NOT NULL
        AND sm_customer_key NOT IN (SELECT sm_customer_key FROM subscription_customers)
        AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
    ),
    per_customer_gaps AS (
      SELECT
        sm_customer_key,
        order_date,
        DATE_DIFF(
          order_date,
          LAG(order_date) OVER (PARTITION BY sm_customer_key ORDER BY order_date),
          DAY
        ) AS days_since_prior_order
      FROM non_subscription_orders
    )
    SELECT
      days_since_prior_order,
      COUNT(*) AS repeat_order_pairs
    FROM per_customer_gaps
    WHERE days_since_prior_order IS NOT NULL
      AND days_since_prior_order BETWEEN 1 AND 365
    GROUP BY 1
    ORDER BY days_since_prior_order;
    ```
  </Accordion>
</AccordionGroup>

***

## Related Categories

Back to the [SQL Query Library](/data-activation/template-resources/sql-query-library).

<CardGroup cols={2}>
  <Card title="Customers & Retention" icon="users" href="/data-activation/template-resources/sql-query-library/customers-and-retention">
    Start with customer mix and repeat behavior before cohort deep dives.
  </Card>

  <Card title="Orders & Revenue" icon="dollar-sign" href="/data-activation/template-resources/sql-query-library/orders-and-revenue">
    Connect long-term value to current order economics.
  </Card>

  <Card title="Marketing & Ads" icon="chart-line" href="/data-activation/template-resources/sql-query-library/marketing-and-ads">
    Compare cohort value and payback against acquisition performance.
  </Card>
</CardGroup>