SQL Query Library

Copy/paste BigQuery SQL templates to answer common questions using SourceMedium tables

Overview

Use these queries as starting points for analysis in BigQuery. Replace your_project with your BigQuery project ID (e.g., sm-yourcompany) and your-sm_store_id with your store identifier.

Query Standards:

• Always include is_order_sm_valid = TRUE for order-based analyses
• If you have multiple stores, add sm_store_id = 'your-sm_store_id' to filter for a specific store’s data
• Use your_project.sm_transformed_v2.* for standard tables
• Use your_project.sm_experimental.* for MTA tables

If you’re not sure which table to use, start with: obt_orders and obt_order_lines.

---

Quick Links

Section	What’s inside
Marketing & Ads	CAC, ROAS by platform, ROAS trends
Messaging	Email/SMS performance, flows vs campaigns, list growth
Funnel	Funnel step counts, conversion rates, top converting pages
Journeys & Lead Capture	Lead capture → purchase timing, landing pages, first vs last touch (MTA)
Customers & Retention	First vs repeat orders, repeat rates by source, new vs repeat trends
Products	Top products by revenue/units, gateway products, product combos
Orders & Revenue	AOV by channel, subscription revenue, refund rates, sales channel mix
LTV & Retention	Cohort LTV, payback period, LTV:CAC, repeat purchase rates, 90-day LTV by product
Attribution & Data Health	Table freshness, UTM coverage, fallback signals, click-id coverage, tracking regressions
Customer Support	Ticket volume, one-touch rate, resolution time, CSAT

---

Most examples default to the last 30 days for performance and “current state” analysis. Adjust the timeframe and add sm_store_id scoping when needed.

Marketing & Ads

Average CAC (last 30 days)

What you’ll learn: How much you’re spending to acquire each new customer, broken down by channel. Use this to identify which channels are most cost-efficient and where you might be overspending on acquisition.

-- Assumptions: timeframe=last_30_days | metric=CAC=ad_spend/new_customer_count | grain=sm_channel | scope=all_channels
WITH channel_rollup AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(sm_channel)), ''), '(unknown)') AS sm_channel,
    SUM(ABS(ad_spend)) AS ad_spend,
    SUM(new_customer_count) AS new_customers
  FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily`
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND ad_spend IS NOT NULL
    AND new_customer_count IS NOT NULL
  GROUP BY 1
),
overall AS (
  SELECT
    '(all_channels)' AS sm_channel,
    SUM(ABS(ad_spend)) AS ad_spend,
    SUM(new_customer_count) AS new_customers
  FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily`
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND ad_spend IS NOT NULL
    AND new_customer_count IS NOT NULL
)
SELECT
  sm_channel,
  ad_spend,
  new_customers,
  SAFE_DIVIDE(ad_spend, NULLIF(new_customers, 0)) AS cac
FROM channel_rollup
WHERE ad_spend > 0

UNION ALL

SELECT
  sm_channel,
  ad_spend,
  new_customers,
  SAFE_DIVIDE(ad_spend, NULLIF(new_customers, 0)) AS cac
FROM overall
WHERE ad_spend > 0
ORDER BY cac ASC;

Highest ROAS by platform + campaign type (last 30 days)

What you’ll learn: Which ad platform and campaign type combinations are generating the best return on ad spend. Helps you decide where to allocate more budget and which underperforming campaigns to optimize or cut.

-- Assumptions: timeframe=last_30_days | metric=ROAS=platform_reported_revenue/ad_spend | grain=platform+campaign_type | scope=all_stores
SELECT
  sm_store_id,
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS platform,
  COALESCE(NULLIF(LOWER(TRIM(ad_campaign_type)), ''), '(unknown)') AS campaign_type,
  SUM(ad_platform_reported_revenue) AS platform_reported_revenue,
  SUM(ad_spend) AS ad_spend,
  SAFE_DIVIDE(SUM(ad_platform_reported_revenue), NULLIF(SUM(ad_spend), 0)) AS roas
FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND ad_spend > 0
GROUP BY 1, 2, 3
ORDER BY roas DESC
LIMIT 20;

ROAS trends over time (monthly, last 6 months)

What you’ll learn: How your ad efficiency has changed month-over-month by platform. Spot seasonal patterns, detect declining performance early, or confirm that recent optimizations are working.

-- Assumptions: timeframe=last_6_months | metric=ROAS=platform_reported_revenue/ad_spend | grain=month+platform | scope=all_stores
WITH monthly AS (
  SELECT
    DATE_TRUNC(date, MONTH) AS month_start,
    COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS platform,
    SUM(ad_platform_reported_revenue) AS platform_reported_revenue,
    SUM(ad_spend) AS ad_spend
  FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily`
  WHERE date >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 6 MONTH)
    AND date < DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH)
    AND ad_spend > 0
  GROUP BY 1, 2
)
SELECT
  platform,
  month_start,
  ad_spend,
  platform_reported_revenue,
  SAFE_DIVIDE(platform_reported_revenue, NULLIF(ad_spend, 0)) AS roas
FROM monthly
ORDER BY platform, month_start;

Messaging

Messaging performance by channel + message type (last 30 days)

What you’ll learn: How email/SMS/push performance differs between campaigns and flows, including deliverability, engagement, list growth, and platform-attributed orders/revenue. Use this to quickly identify which message types are driving the most value (and where engagement or unsubscribes are trending poorly).

-- Assumptions: timeframe=last_30_days | metric=engagement+platform_attributed_orders_revenue | grain=channel+message_type | scope=all_messages
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  COALESCE(NULLIF(LOWER(TRIM(message_type)), ''), '(unknown)') AS message_type,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_opens) AS opens,
  SUM(message_unique_clicks) AS clicks,
  SUM(message_unique_bounces) AS bounces,
  SUM(message_unique_drops) AS drops,
  SUM(list_subscribes) AS list_subscribes,
  SUM(list_unsubscribes) AS list_unsubscribes,
  SUM(platform_reported_orders) AS platform_reported_orders,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_DIVIDE(SUM(message_unique_opens), NULLIF(SUM(message_unique_receives), 0)) AS open_rate,
  SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)) AS click_rate,
  SAFE_DIVIDE(SUM(list_unsubscribes), NULLIF(SUM(message_unique_receives), 0)) AS unsubscribe_rate_per_receive
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2
ORDER BY platform_reported_order_revenue DESC;

platform_reported_* metrics are platform-attributed, not incremental lift. Use them for directional comparisons and monitoring, not causal claims.

Top campaigns by platform-attributed order revenue (last 30 days)

What you’ll learn: Which campaigns are driving the most platform-attributed revenue and orders in the last 30 days. Use this to spot your best-performing sends and quickly triage underperformers.

-- Assumptions: timeframe=last_30_days | metric=platform_attributed_orders_revenue | grain=campaign | scope=campaigns_only
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  campaign_id,
  ANY_VALUE(campaign_name) AS campaign_name,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_opens) AS opens,
  SUM(message_unique_clicks) AS clicks,
  SUM(platform_reported_orders) AS platform_reported_orders,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_DIVIDE(SUM(message_unique_opens), NULLIF(SUM(message_unique_receives), 0)) AS open_rate,
  SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)) AS click_rate
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND LOWER(message_type) = 'campaign'
GROUP BY 1, 2, 3
ORDER BY platform_reported_order_revenue DESC
LIMIT 25;

List subscribes vs unsubscribes trend by channel (weekly, last 12 weeks)

What you’ll learn: How list growth is trending week-over-week by channel using message-attributed subscribes and unsubscribes. Use this to detect periods of churn (high unsubscribes) and measure whether list acquisition is keeping up.

-- Assumptions: timeframe=last_12_weeks | metric=list_subscribes_unsubscribes_net | grain=week+channel | scope=all_messages
SELECT
  DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  SUM(list_subscribes) AS list_subscribes,
  SUM(list_unsubscribes) AS list_unsubscribes,
  SUM(list_subscribes) - SUM(list_unsubscribes) AS net_list_growth,
  SAFE_DIVIDE(SUM(list_unsubscribes), NULLIF(SUM(list_subscribes), 0)) AS unsubscribe_per_subscribe
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 84 DAY)
GROUP BY 1, 2
ORDER BY week_start, sm_message_channel;

Messaging performance by provider + channel + message type (last 30 days)

What you’ll learn: Which messaging providers (Klaviyo, Postscript, Attentive, etc.) are driving the best engagement and platform-attributed outcomes, broken out by channel and message type. Useful when you run multiple providers or want to audit performance differences across tools.

-- Assumptions: timeframe=last_30_days | metric=engagement+platform_attributed_orders_revenue | grain=provider+channel+message_type | scope=message_sends_only
SELECT
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  COALESCE(NULLIF(LOWER(TRIM(message_type)), ''), '(unknown)') AS message_type,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_opens) AS opens,
  SUM(message_unique_clicks) AS clicks,
  SUM(platform_reported_orders) AS platform_reported_orders,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_DIVIDE(SUM(message_unique_opens), NULLIF(SUM(message_unique_receives), 0)) AS open_rate,
  SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)) AS click_rate
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND message_type IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY platform_reported_order_revenue DESC
LIMIT 50;

Flow vs campaign performance trend (weekly, last 12 weeks)

What you’ll learn: Whether your weekly performance is being driven by flows or campaigns, including engagement and unsubscribes per receive. Helpful for diagnosing list fatigue or “campaign heavy” weeks that spike churn.

-- Assumptions: timeframe=last_12_weeks | metric=engagement+platform_attributed_revenue+unsubscribe_rate | grain=week+message_type | scope=flow_vs_campaign_only
SELECT
  DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
  LOWER(message_type) AS message_type,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_opens) AS opens,
  SUM(message_unique_clicks) AS clicks,
  SUM(list_unsubscribes) AS list_unsubscribes,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_DIVIDE(SUM(message_unique_opens), NULLIF(SUM(message_unique_receives), 0)) AS open_rate,
  SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)) AS click_rate,
  SAFE_DIVIDE(SUM(list_unsubscribes), NULLIF(SUM(message_unique_receives), 0)) AS unsubscribe_rate_per_receive
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 84 DAY)
  AND LOWER(message_type) IN ('flow', 'campaign')
GROUP BY 1, 2
ORDER BY week_start, message_type;

This is a directional trend view. If your providers attribute list subscribes/unsubscribes separately from message sends, keep using the dedicated “List subscribes vs unsubscribes” template for net list growth.

Deliverability health (bounce + drop rates) by provider and channel (weekly, last 12 weeks)

What you’ll learn: Whether bounces or suppressed sends (“drops”) are trending up for a specific provider/channel/message type. Useful for deliverability monitoring and troubleshooting.

-- Assumptions: timeframe=last_12_weeks | metric=bounce_rate+drop_rate | grain=week+provider+channel+message_type | scope=message_sends_only
SELECT
  DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  COALESCE(NULLIF(LOWER(TRIM(message_type)), ''), '(unknown)') AS message_type,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_bounces) AS bounces,
  SUM(message_unique_drops) AS drops,
  SAFE_DIVIDE(SUM(message_unique_bounces), NULLIF(SUM(message_unique_receives), 0)) AS bounce_rate_per_receive,
  SAFE_DIVIDE(SUM(message_unique_drops), NULLIF(SUM(message_unique_receives), 0)) AS drop_rate_per_receive
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 84 DAY)
  AND message_type IS NOT NULL
GROUP BY 1, 2, 3, 4
HAVING receives >= 1000
ORDER BY week_start, source_system, sm_message_channel, message_type;

Highest click-rate messages (last 30 days, minimum receives threshold)

What you’ll learn: Which messages have unusually high click rates, after applying a minimum receives threshold to avoid small-sample noise. Useful for creative analysis and identifying “winner” templates to reuse.

-- Assumptions: timeframe=last_30_days | metric=click_rate+platform_attributed_outcomes | grain=message | scope=min_receives_threshold
SELECT
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  COALESCE(NULLIF(LOWER(TRIM(sm_message_channel)), ''), '(unknown)') AS sm_message_channel,
  COALESCE(NULLIF(LOWER(TRIM(message_type)), ''), '(unknown)') AS message_type,
  message_id,
  ANY_VALUE(message_name) AS message_name,
  ANY_VALUE(message_subject) AS message_subject,
  campaign_id,
  ANY_VALUE(campaign_name) AS campaign_name,
  SUM(message_unique_receives) AS receives,
  SUM(message_unique_opens) AS opens,
  SUM(message_unique_clicks) AS clicks,
  SUM(platform_reported_orders) AS platform_reported_orders,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)) AS click_rate
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND message_id IS NOT NULL
GROUP BY 1, 2, 3, 4, 7
HAVING receives >= 10000
ORDER BY click_rate DESC, receives DESC
LIMIT 25;

Funnel

Daily session-based funnel conversion (last 30 days)

What you’ll learn: Daily session-level funnel conversion rates (view item → add to cart → begin checkout → purchase) using distinct-session denominators. Use this for “conversion rate” questions.

-- Assumptions: timeframe=last_30_days | metric=session_funnel_conversion_rates | grain=date | scope=sessions_with_any_funnel_activity
WITH events AS (
  SELECT
    DATE(event_local_datetime) AS date,
    COALESCE(event_user_session_id, event_session_id) AS session_id,
    sm_event_name
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND DATE(event_local_datetime) < CURRENT_DATE()
    AND (event_user_session_id IS NOT NULL OR event_session_id IS NOT NULL)
    AND sm_event_name IN ('view_item', 'add_to_cart', 'begin_checkout', 'purchase')
),
session_daily AS (
  SELECT
    date,
    session_id,
    MAX(IF(sm_event_name = 'view_item', 1, 0)) AS has_view_item,
    MAX(IF(sm_event_name = 'add_to_cart', 1, 0)) AS has_add_to_cart,
    MAX(IF(sm_event_name = 'begin_checkout', 1, 0)) AS has_begin_checkout,
    MAX(IF(sm_event_name = 'purchase', 1, 0)) AS has_purchase
  FROM events
  GROUP BY 1, 2
)
SELECT
  date,
  COUNT(*) AS sessions,
  SUM(has_view_item) AS sessions_with_view_item,
  SUM(has_add_to_cart) AS sessions_with_add_to_cart,
  SUM(has_begin_checkout) AS sessions_with_begin_checkout,
  SUM(has_purchase) AS sessions_with_purchase,
  SAFE_DIVIDE(SUM(has_add_to_cart), NULLIF(SUM(has_view_item), 0)) AS view_to_cart_rate,
  SAFE_DIVIDE(SUM(has_begin_checkout), NULLIF(SUM(has_add_to_cart), 0)) AS cart_to_checkout_rate,
  SAFE_DIVIDE(SUM(has_purchase), NULLIF(SUM(has_begin_checkout), 0)) AS checkout_to_purchase_rate,
  SAFE_DIVIDE(SUM(has_purchase), NULLIF(COUNT(*), 0)) AS session_conversion_rate
FROM session_daily
ORDER BY date;

Top pages by on-page add-to-cart session rate (last 7 days)

What you’ll learn: Which pages have the highest share of sessions that triggered an add-to-cart event on that same page path. Useful for identifying strong product pages/collections and debugging low-performing pages.

-- Assumptions: timeframe=last_7_days | metric=on_page_add_to_cart_session_rate | grain=event_page_path | scope=sessions_with_page_path
WITH base AS (
  SELECT
    event_page_path,
    COALESCE(event_user_session_id, event_session_id) AS session_id,
    sm_event_name
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    AND DATE(event_local_datetime) < CURRENT_DATE()
    AND event_page_path IS NOT NULL
    AND TRIM(event_page_path) != ''
    AND (event_user_session_id IS NOT NULL OR event_session_id IS NOT NULL)
    AND sm_event_name IN ('page_view', 'add_to_cart')
)
SELECT
  event_page_path,
  COUNT(DISTINCT IF(sm_event_name = 'page_view', session_id, NULL)) AS sessions_with_page_view,
  COUNT(DISTINCT IF(sm_event_name = 'add_to_cart', session_id, NULL)) AS sessions_with_add_to_cart_on_page,
  SAFE_DIVIDE(
    COUNT(DISTINCT IF(sm_event_name = 'add_to_cart', session_id, NULL)),
    NULLIF(COUNT(DISTINCT IF(sm_event_name = 'page_view', session_id, NULL)), 0)
  ) AS add_to_cart_session_rate
FROM base
GROUP BY 1
HAVING sessions_with_page_view >= 500
ORDER BY add_to_cart_session_rate DESC
LIMIT 25;

Funnel conversion by UTM source/medium (last 30 days)

What you’ll learn: How different acquisition sources/mediums perform through a session-based funnel (distinct-session denominators). This is the recommended pattern for “conversion rate by channel” questions.

-- Assumptions: timeframe=last_30_days | metric=session_funnel_conversion_rates | grain=utm_source_medium | scope=sessions_with_any_funnel_activity
-- Notes:
-- - Uses DISTINCT sessions (not event-count ratios)
-- - UTM source/medium is attributed to the session using the earliest event in that session
WITH events AS (
  SELECT
    event_local_datetime,
    COALESCE(event_user_session_id, event_session_id) AS session_id,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_source)), ''), '(none)') AS utm_source,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_medium)), ''), '(none)') AS utm_medium,
    sm_event_name,
    event_order_revenue
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND DATE(event_local_datetime) < CURRENT_DATE()
    AND (event_user_session_id IS NOT NULL OR event_session_id IS NOT NULL)
),
session_utm AS (
  SELECT
    session_id,
    ARRAY_AGG(utm_source ORDER BY event_local_datetime ASC LIMIT 1)[OFFSET(0)] AS utm_source,
    ARRAY_AGG(utm_medium ORDER BY event_local_datetime ASC LIMIT 1)[OFFSET(0)] AS utm_medium
  FROM events
  GROUP BY 1
),
session_steps AS (
  SELECT
    session_id,
    MAX(IF(sm_event_name = 'view_item', 1, 0)) AS has_view_item,
    MAX(IF(sm_event_name = 'add_to_cart', 1, 0)) AS has_add_to_cart,
    MAX(IF(sm_event_name = 'begin_checkout', 1, 0)) AS has_begin_checkout,
    MAX(IF(sm_event_name = 'purchase', 1, 0)) AS has_purchase,
    SUM(IF(sm_event_name = 'purchase', COALESCE(event_order_revenue, 0), 0)) AS purchase_revenue
  FROM events
  GROUP BY 1
),
by_channel AS (
  SELECT
    CONCAT(u.utm_source, ' / ', u.utm_medium) AS utm_source_medium,
    s.has_view_item,
    s.has_add_to_cart,
    s.has_begin_checkout,
    s.has_purchase,
    s.purchase_revenue
  FROM session_steps s
  JOIN session_utm u USING (session_id)
)
SELECT
  utm_source_medium,
  COUNT(*) AS sessions,
  SUM(has_view_item) AS sessions_with_view_item,
  SUM(has_add_to_cart) AS sessions_with_add_to_cart,
  SUM(has_begin_checkout) AS sessions_with_begin_checkout,
  SUM(has_purchase) AS sessions_with_purchase,
  SAFE_DIVIDE(SUM(has_add_to_cart), NULLIF(SUM(has_view_item), 0)) AS view_to_cart_rate,
  SAFE_DIVIDE(SUM(has_begin_checkout), NULLIF(SUM(has_add_to_cart), 0)) AS cart_to_checkout_rate,
  SAFE_DIVIDE(SUM(has_purchase), NULLIF(SUM(has_begin_checkout), 0)) AS checkout_to_purchase_rate,
  SAFE_DIVIDE(SUM(has_purchase), NULLIF(COUNT(*), 0)) AS session_conversion_rate,
  SUM(purchase_revenue) AS total_revenue
FROM by_channel
WHERE has_view_item = 1 OR has_add_to_cart = 1 OR has_begin_checkout = 1 OR has_purchase = 1
GROUP BY 1
HAVING sessions >= 100
ORDER BY sessions_with_purchase DESC
LIMIT 25;

This is a session-based funnel. If you want near-real-time monitoring (hourly/daily step volumes and event-based ratios), use rpt_funnel_events_performance_hourly.

Funnel tracking health by event source system (last 30 days)

What you’ll learn: Whether one tracking source (source_system) appears to be missing critical steps (e.g., begin checkout) relative to other sources. This is a fast “do we have tracking regressions?” check.

-- Assumptions: timeframe=last_30_days | metric=funnel_step_event_counts | grain=source_system | scope=monitoring
SELECT
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
  SUM(page_view_event_count) AS page_views,
  SUM(view_item_event_count) AS view_item_events,
  SUM(add_to_cart_event_count) AS add_to_cart_events,
  SUM(begin_checkout_event_count) AS begin_checkout_events,
  SUM(purchase_event_count) AS purchase_events
FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
HAVING page_views >= 5000
ORDER BY page_views DESC;

Hourly funnel anomaly detector (hour-over-hour deltas, last 7 days)

What you’ll learn: Which tracking sources have unusually large hour-over-hour spikes/drops in purchases. Useful for catching instrumentation outages, batch backfills, or sudden traffic changes.

-- Assumptions: timeframe=last_7_days | metric=hour_over_hour_purchase_deltas | grain=hour+source_system | scope=anomaly_triage
WITH hourly AS (
  SELECT
    event_local_datetime AS hour_start,
    COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
    SUM(purchase_event_count) AS purchase_events,
    SUM(event_order_revenue) AS event_order_revenue
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
  GROUP BY 1, 2
),
scored AS (
  SELECT
    source_system,
    hour_start,
    purchase_events,
    LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start) AS prev_purchase_events,
    purchase_events - LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start) AS delta_purchase_events,
    SAFE_DIVIDE(
      purchase_events - LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start),
      NULLIF(LAG(purchase_events) OVER (PARTITION BY source_system ORDER BY hour_start), 0)
    ) AS pct_change_purchase_events,
    event_order_revenue
  FROM hourly
)
SELECT
  source_system,
  hour_start,
  prev_purchase_events,
  purchase_events,
  delta_purchase_events,
  pct_change_purchase_events,
  event_order_revenue
FROM scored
WHERE prev_purchase_events >= 10
ORDER BY ABS(pct_change_purchase_events) DESC
LIMIT 50;

Lead-gen to purchase (email signups vs purchases) by UTM source/medium (last 30 days)

What you’ll learn: Which UTMs drive email signups and purchases (event-based). Useful for diagnosing “lots of leads, few purchases” vs “low leads, high purchases” sources.

-- Assumptions: timeframe=last_30_days | metric=email_signups+purchases | grain=utm_source_medium | scope=event_based
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(event_utm_source)), ''), '(none)') AS utm_source,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_medium)), ''), '(none)') AS utm_medium,
    SUM(email_sign_up_event_count) AS email_signups,
    SUM(purchase_event_count) AS purchase_events,
    SUM(event_order_revenue) AS event_order_revenue
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY 1, 2
)
SELECT
  CONCAT(utm_source, ' / ', utm_medium) AS utm_source_medium,
  email_signups,
  purchase_events,
  event_order_revenue,
  SAFE_DIVIDE(event_order_revenue, NULLIF(purchase_events, 0)) AS revenue_per_purchase_event
FROM base
WHERE email_signups >= 100
ORDER BY email_signups DESC
LIMIT 50;

These are event-based counts and ratios (not user-based). Treat them as directional monitoring signals, not conversion attribution.

Cart drop-off signals (add-to-cart vs remove-from-cart vs checkout) trend (daily, last 30 days)

What you’ll learn: Whether remove-from-cart events are spiking relative to add-to-cart, and whether checkout initiation is dropping. Useful for diagnosing UX issues, tracking regressions, or promo-related cart behavior changes.

-- Assumptions: timeframe=last_30_days | metric=cart_drop_off_signals | grain=date | scope=event_based
WITH daily AS (
  SELECT
    DATE(event_local_datetime) AS date,
    SUM(add_to_cart_event_count) AS add_to_cart_events,
    SUM(remove_from_cart_event_count) AS remove_from_cart_events,
    SUM(begin_checkout_event_count) AS begin_checkout_events,
    SUM(purchase_event_count) AS purchase_events
  FROM `your_project.sm_transformed_v2.rpt_funnel_events_performance_hourly`
  WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  GROUP BY 1
)
SELECT
  date,
  add_to_cart_events,
  remove_from_cart_events,
  begin_checkout_events,
  purchase_events
FROM daily
ORDER BY date;

Remove-from-cart can exceed add-to-cart in event terms (multi-item carts, repeated events). Focus on trend changes, not absolute levels.

Journeys & Lead Capture

These templates use:

• sm_transformed_v2.obt_funnel_event_history for event-level lead capture + timing analysis, and
• sm_experimental.obt_purchase_journeys_with_mta_models for purchase-journey first-touch vs last-touch analysis (MTA).

The MTA tables are experimental: treat results as directional and validate against your owned analytics + business context.

Lead capture event discovery (top event names, last 30 days)

What you’ll learn: Which normalized funnel events are present in your tenant so you can pick the correct lead-capture event names (email signup, subscribe, generate lead, etc.) without guessing.

-- Assumptions: timeframe=last_30_days | metric=event_counts | grain=event_name | scope=discovery
SELECT
  COALESCE(sm_event_name, '(null)') AS sm_event_name,
  COUNT(*) AS events
FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
WHERE DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY events DESC
LIMIT 50;

Lead capture → first purchase timing (hours) by lead UTM source/medium (last 90 days)

What you’ll learn: For users who have a lead capture event and later a purchase event, how long it takes to convert (p50/p90 hours), broken out by the UTM source/medium at the lead event.

-- Assumptions: timeframe=last_90_days | metric=lead_to_purchase_timing_hours | grain=lead_utm_source_medium | scope=event_user_id_non_null
-- Update this list after running the discovery query above:
--   lead_event_names = ('generate_lead', 'sign_up')
WITH lead_events AS (
  SELECT
    event_user_id,
    event_local_datetime,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_source)), ''), '(none)') AS event_utm_source,
    COALESCE(NULLIF(LOWER(TRIM(event_utm_medium)), ''), '(none)') AS event_utm_medium
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE event_user_id IS NOT NULL
    AND DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sm_event_name IN ('generate_lead', 'sign_up')
),
first_lead AS (
  SELECT
    event_user_id,
    ARRAY_AGG(STRUCT(event_local_datetime, event_utm_source, event_utm_medium) ORDER BY event_local_datetime ASC LIMIT 1)[OFFSET(0)] AS lead
  FROM lead_events
  GROUP BY 1
),
first_purchase AS (
  SELECT
    event_user_id,
    MIN(event_local_datetime) AS first_purchase_at
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE event_user_id IS NOT NULL
    AND DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sm_event_name = 'purchase'
  GROUP BY 1
),
joined AS (
  SELECT
    CONCAT(lead.event_utm_source, ' / ', lead.event_utm_medium) AS lead_utm_source_medium,
    lead.event_local_datetime AS first_lead_at,
    p.first_purchase_at,
    DATETIME_DIFF(p.first_purchase_at, lead.event_local_datetime, HOUR) AS hours_to_first_purchase
  FROM first_lead l
  JOIN first_purchase p
    USING (event_user_id)
  WHERE p.first_purchase_at >= l.lead.event_local_datetime
)
SELECT
  lead_utm_source_medium,
  COUNT(*) AS purchasers_with_lead_event,
  APPROX_QUANTILES(hours_to_first_purchase, 101)[OFFSET(50)] AS p50_hours_to_first_purchase,
  APPROX_QUANTILES(hours_to_first_purchase, 101)[OFFSET(90)] AS p90_hours_to_first_purchase
FROM joined
GROUP BY 1
HAVING purchasers_with_lead_event >= 50
ORDER BY purchasers_with_lead_event DESC
LIMIT 50;

Lead capture → purchase conversion rate (last 90 days)

What you’ll learn: What share of tracked users with a lead capture event later have a purchase event (identity-based, using DISTINCT event_user_id). Useful for directional lead-to-purchase monitoring.

-- Assumptions: timeframe=last_90_days | metric=lead_to_purchase_rate | grain=all_leads | scope=event_user_id_non_null
-- Update this list after running the discovery query above:
--   lead_event_names = ('generate_lead', 'sign_up')
WITH lead_users AS (
  SELECT DISTINCT
    event_user_id
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE event_user_id IS NOT NULL
    AND DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sm_event_name IN ('generate_lead', 'sign_up')
),
purchase_users AS (
  SELECT DISTINCT
    event_user_id
  FROM `your_project.sm_transformed_v2.obt_funnel_event_history`
  WHERE event_user_id IS NOT NULL
    AND DATE(event_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sm_event_name = 'purchase'
)
SELECT
  COUNT(DISTINCT l.event_user_id) AS lead_users,
  COUNT(DISTINCT p.event_user_id) AS purchasers_from_leads,
  SAFE_DIVIDE(COUNT(DISTINCT p.event_user_id), NULLIF(COUNT(DISTINCT l.event_user_id), 0)) AS lead_to_purchase_rate
FROM lead_users l
LEFT JOIN purchase_users p
  USING (event_user_id);

This is event-identity based (tracking-user-based), not customer-based. Coverage depends on your tracking setup and identity stitching.

MTA: First-touch vs last-touch marketing channel mix (purchases, last 30 days)

What you’ll learn: For purchases, what the first-touch vs last-touch marketing channels were (journey-level). Useful for quantifying “what brings users in” vs “what closes”.

-- Assumptions: timeframe=last_30_days | metric=purchase_revenue_by_first_last_touch_channel | grain=first_touch+last_touch | scope=mta_purchase_rows_only
SELECT
  COALESCE(first_touch_dimension_value.marketing_channel, '(unknown)') AS first_touch_marketing_channel,
  COALESCE(last_touch_dimension_value.marketing_channel, '(unknown)') AS last_touch_marketing_channel,
  COUNT(DISTINCT purchase_order_id) AS orders,
  SUM(purchase_order_revenue) AS purchase_order_revenue
FROM `your_project.sm_experimental.obt_purchase_journeys_with_mta_models`
WHERE sm_event_name = 'purchase'
  AND DATE(purchase_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2
ORDER BY purchase_order_revenue DESC
LIMIT 50;

MTA: Time to conversion (days) by first-touch marketing channel (purchases, last 30 days)

What you’ll learn: How long it takes to convert by acquisition channel, using MTA-derived days-to-conversion (journey-level).

-- Assumptions: timeframe=last_30_days | metric=days_to_conversion | grain=first_touch_marketing_channel | scope=mta_purchase_rows_only
WITH purchases AS (
  SELECT
    COALESCE(first_touch_dimension_value.marketing_channel, '(unknown)') AS first_touch_marketing_channel,
    CAST(days_to_conversion.marketing_channel AS INT64) AS days_to_conversion_marketing_channel
  FROM `your_project.sm_experimental.obt_purchase_journeys_with_mta_models`
  WHERE sm_event_name = 'purchase'
    AND DATE(purchase_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND days_to_conversion.marketing_channel IS NOT NULL
)
SELECT
  first_touch_marketing_channel,
  COUNT(*) AS purchases,
  AVG(days_to_conversion_marketing_channel) AS avg_days_to_conversion,
  APPROX_QUANTILES(days_to_conversion_marketing_channel, 101)[OFFSET(50)] AS p50_days_to_conversion,
  APPROX_QUANTILES(days_to_conversion_marketing_channel, 101)[OFFSET(90)] AS p90_days_to_conversion
FROM purchases
GROUP BY 1
HAVING purchases >= 100
ORDER BY purchases DESC;

MTA landing pages: Top first-touch landing pages by attributed revenue (purchases, last 30 days)

What you’ll learn: Which landing pages most often appear as the first-touch landing page for purchases, and the associated revenue impact (directional).

-- Assumptions: timeframe=last_30_days | metric=first_touch_landing_page_revenue | grain=landing_page | scope=mta_purchase_rows_only
SELECT
  COALESCE(NULLIF(dimension_value.landing_page, ''), '(unknown)') AS first_touch_landing_page,
  COUNT(DISTINCT purchase_order_id) AS orders,
  SUM(purchase_order_revenue) AS purchase_order_revenue,
  SUM(first_touch_revenue_impact.landing_page) AS first_touch_attributed_revenue_landing_page
FROM `your_project.sm_experimental.obt_purchase_journeys_with_mta_models`
WHERE DATE(purchase_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND first_touch_revenue_impact.landing_page > 0
GROUP BY 1
ORDER BY first_touch_attributed_revenue_landing_page DESC
LIMIT 50;

Zero-party attribution: Revenue by post-purchase survey source (new vs repeat, last 90 days)

What you’ll learn: What customers say drove their purchase (post‑purchase survey tags), and how it differs for new vs repeat orders and subscription orders.

-- Assumptions: timeframe=last_90_days | metric=orders+net_revenue | grain=zero_party_source+new_repeat+subscription_sequence | scope=valid_orders_only
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_zero_party_attribution_source)), ''), '(none)') AS sm_zero_party_attribution_source,
  sm_valid_order_sequence,
  subscription_order_sequence,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_cancelled_at IS NULL
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1, 2, 3
ORDER BY order_net_revenue DESC
LIMIT 100;

Last-touch Klaviyo orders: New vs repeat × subscription sequence (last 90 days)

What you’ll learn: How last-click orders attributed to Klaviyo perform, segmented by new vs repeat and subscription sequence. This uses sm_utm_source/sm_utm_medium (last-click) from the order attribution hierarchy.

-- Assumptions: timeframe=last_90_days | metric=orders+net_revenue | grain=utm_medium+new_repeat+subscription_sequence | scope=valid_orders_only_last_click_utm
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_utm_medium)), ''), '(none)') AS sm_utm_medium,
  sm_valid_order_sequence,
  subscription_order_sequence,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_cancelled_at IS NULL
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND LOWER(TRIM(sm_utm_source)) = 'klaviyo'
  AND LOWER(TRIM(sm_utm_medium)) IN ('email', 'sms')
GROUP BY 1, 2, 3
ORDER BY order_net_revenue DESC;

If you don’t see sm_utm_source = 'klaviyo' in your tenant, run the “UTM source/medium discovery” template and choose the exact source values for your messaging stack.

Customers & Retention

First valid vs repeat orders (last 30 days)

What you’ll learn: The split between customers’ first valid order vs repeat valid orders in terms of order count, unique customers, and net revenue. Track this over time to understand how much revenue comes from retention vs new customer acquisition.

-- Assumptions: timeframe=last_30_days | metric=orders+customers+net_revenue | grain=first_vs_repeat | scope=valid_orders_only
SELECT
  CASE WHEN sm_valid_order_index = 1 THEN 'first_valid_order' ELSE 'repeat_valid_order' END AS order_type,
  COUNT(DISTINCT sm_order_key) AS orders,
  COUNT(DISTINCT sm_customer_key) AS customers,
  SUM(order_net_revenue) AS order_net_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_cancelled_at IS NULL
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY orders DESC;

Which source/mediums drive repeat purchases? (cohorted on first order in last 12 months)

What you’ll learn: Which acquisition channels bring in customers who come back to buy again. Identify your best sources for long-term customer value versus one-time buyers.

-- Assumptions: timeframe=first_orders_last_12_months | metric=repeat_rate=customers_with_2+_orders/customers | grain=first_order_source_medium | scope=valid_orders_only
WITH valid_orders AS (
  SELECT
    sm_customer_key,
    sm_order_key,
    order_processed_at_local_datetime,
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium,
    ROW_NUMBER() OVER (
      PARTITION BY sm_customer_key
      ORDER BY order_processed_at_local_datetime
    ) AS rn
  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
),
customer_summary AS (
  SELECT
    sm_customer_key,
    MAX(CASE WHEN rn = 1 THEN source_medium END) AS first_order_source_medium,
    MIN(CASE WHEN rn = 1 THEN DATE(order_processed_at_local_datetime) END) AS first_order_date,
    COUNT(DISTINCT sm_order_key) AS valid_order_count
  FROM valid_orders
  GROUP BY 1
)
SELECT
  first_order_source_medium AS source_medium,
  COUNT(*) AS customers,
  COUNTIF(valid_order_count >= 2) AS repeat_customers,
  SAFE_DIVIDE(COUNTIF(valid_order_count >= 2), COUNT(*)) AS repeat_rate,
  AVG(valid_order_count - 1) AS avg_subsequent_orders
FROM customer_summary
WHERE first_order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 365 DAY)
GROUP BY 1
HAVING customers >= 100
ORDER BY repeat_rate DESC
LIMIT 25;

New vs repeat customer ratio trend (weekly, YTD)

What you’ll learn: How your balance between new and returning customers has shifted week-over-week this year. Use this to see whether acquisition is outpacing repeat purchasing (or vice versa), and to spot meaningful shifts after campaigns or seasonality.

-- Assumptions: timeframe=year_to_date | metric=new_to_repeat_ratio=new_customer_count/repeat_customer_count | grain=week | scope=all_channels
WITH weekly AS (
  SELECT
    DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
    SUM(new_customer_count) AS new_customers,
    SUM(repeat_customer_count) AS repeat_customers
  FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily`
  WHERE date >= DATE_TRUNC(CURRENT_DATE(), YEAR)
  GROUP BY 1
)
SELECT
  week_start,
  new_customers,
  repeat_customers,
  SAFE_DIVIDE(new_customers, NULLIF(repeat_customers, 0)) AS new_to_repeat_ratio
FROM weekly
ORDER BY week_start;

Customer acquisition trend (monthly new customers, last 12 months)

What you’ll learn: Your month-over-month new customer growth including order count, revenue, and average order value from first-time buyers. Use this to track whether your acquisition efforts are scaling.

-- Assumptions: timeframe=last_12_months | metric=new_customers | grain=month | scope=all_channels
WITH monthly AS (
  SELECT
    DATE_TRUNC(date, MONTH) AS month_start,
    SUM(new_customer_count) AS new_customers,
    SUM(new_customer_order_count) AS new_customer_orders,
    SUM(new_customer_order_net_revenue) AS new_customer_order_net_revenue
  FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily`
  WHERE date >= DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 12 MONTH)
  GROUP BY 1
)
SELECT
  month_start,
  new_customers,
  new_customer_orders,
  new_customer_order_net_revenue,
  SAFE_DIVIDE(new_customer_order_net_revenue, NULLIF(new_customer_orders, 0)) AS new_customer_aov
FROM monthly
ORDER BY month_start;

Products

Top 10 products by net revenue (last 30 days)

What you’ll learn: Your highest revenue-generating products with units sold and order counts. Use this to identify your cash cows and prioritize inventory, marketing, and merchandising decisions.

-- Assumptions: timeframe=last_30_days | metric=net_revenue=SUM(order_line_net_revenue) | grain=sku | scope=valid_orders_only
SELECT
  sku,
  ANY_VALUE(product_title) AS product_title,
  SUM(order_line_net_revenue) AS order_line_net_revenue,
  SUM(order_line_quantity) AS units_sold,
  COUNT(DISTINCT sm_order_key) AS orders
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND sku IS NOT NULL
  AND NOT REGEXP_CONTAINS(product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
GROUP BY 1
ORDER BY order_line_net_revenue DESC
LIMIT 10;

Top products by units sold (last 30 days)

What you’ll learn: Your most popular products by volume, which may differ from your top revenue generators. Useful for forecasting demand, managing inventory levels, and identifying viral or gateway products.

-- Assumptions: timeframe=last_30_days | metric=units_sold=SUM(order_line_quantity) | grain=sku | scope=valid_orders_only
SELECT
  sku,
  ANY_VALUE(product_title) AS product_title,
  SUM(order_line_quantity) AS units_sold,
  SUM(order_line_net_revenue) AS order_line_net_revenue,
  COUNT(DISTINCT sm_order_key) AS orders
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND sku IS NOT NULL
  AND NOT REGEXP_CONTAINS(product_title, r'(?i)(shipping protection|not a product|service fee|processing fee)')
GROUP BY 1
ORDER BY units_sold DESC
LIMIT 20;

Products most common with new customers (first valid orders, last 90 days)

What you’ll learn: Which products are most often the entry point for new customers. These “gateway products” are key to acquisition strategy—consider featuring them in ads, bundles, or welcome offers.

-- Assumptions: timeframe=first_valid_orders_last_90_days | metric=units_sold=SUM(order_line_quantity) | grain=product_title | scope=new_customers_valid_orders_only
WITH first_valid_orders AS (
  SELECT
    sm_order_key
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND sm_valid_order_index = 1
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
SELECT
  ol.product_title,
  SUM(ol.order_line_quantity) AS units_sold,
  COUNT(DISTINCT ol.sm_order_key) AS orders,
  COUNT(DISTINCT ol.sku) AS skus
FROM `your_project.sm_transformed_v2.obt_order_lines` ol
INNER JOIN first_valid_orders fvo
  ON ol.sm_order_key = fvo.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
ORDER BY units_sold DESC
LIMIT 25;

Most commonly ordered product combinations

What you’ll learn: Which products are frequently purchased together in the same order. Use this for bundle recommendations, cross-sell strategies, and merchandising decisions.

-- Assumptions: timeframe=all_time | metric=order_frequency | grain=product_combination | scope=valid_orders_only
WITH RECURSIVE product_combos AS (
    -- Anchor: Start with individual products per order
    SELECT
        ol.sm_store_id,
        ol.sm_order_key,
        1 AS combo_length,
        CONCAT(ol.product_title, ' - ', ol.product_variant_title) AS combo,
        CONCAT(ol.product_title, ' - ', ol.product_variant_title) AS last_item
    FROM `your_project.sm_transformed_v2.obt_order_lines` AS ol
    WHERE ol.sm_store_id = 'your-sm_store_id'
      AND 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|order specific)')

    UNION ALL

    -- Recursive: Build combinations up to 5 products
    SELECT
        ol.sm_store_id,
        ol.sm_order_key,
        pc.combo_length + 1,
        CONCAT(pc.combo, ', ', CONCAT(ol.product_title, ' - ', ol.product_variant_title)),
        CONCAT(ol.product_title, ' - ', ol.product_variant_title)
    FROM product_combos AS pc
    INNER JOIN `your_project.sm_transformed_v2.obt_order_lines` AS ol
        ON ol.sm_order_key = pc.sm_order_key
        AND CONCAT(ol.product_title, ' - ', ol.product_variant_title) > pc.last_item
    WHERE pc.combo_length < 5
      AND 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|order specific)')
)

SELECT
    combo AS product_combinations,
    COUNT(DISTINCT sm_order_key) AS order_frequency,
    combo_length AS num_products
FROM product_combos
WHERE combo_length >= 2
GROUP BY combo, combo_length
HAVING order_frequency >= 100
ORDER BY order_frequency DESC, combo ASC
LIMIT 100;

Orders & Revenue

Average order value (AOV) by marketing channel (last 30 days)

What you’ll learn: Which marketing channels drive higher-value orders. Channels with high AOV may warrant more budget even if volume is lower; low-AOV channels might need different offer strategies.

-- Assumptions: timeframe=last_30_days | metric=AOV=SUM(order_net_revenue)/orders | grain=sm_utm_source_medium | scope=valid_orders_only
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS marketing_channel,
    sm_order_key,
    sm_customer_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
  marketing_channel,
  COUNT(DISTINCT sm_order_key) AS orders,
  COUNT(DISTINCT sm_customer_key) AS customers,
  SUM(order_net_revenue) AS order_net_revenue,
  SAFE_DIVIDE(SUM(order_net_revenue), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS aov
FROM base
GROUP BY 1
HAVING orders >= 50
ORDER BY aov DESC
LIMIT 50;

Revenue in the last 30 days from customers who have ever had a subscription

What you’ll learn: How much of your recent revenue comes from customers with subscription history (even if they’re not currently subscribed). Helps quantify the long-term value of your subscription program.

-- Assumptions: timeframe=last_30_days | metric=net_revenue=SUM(order_net_revenue) | grain=overall | scope=customers_with_any_subscription_history
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
),
last_30_valid_orders AS (
  SELECT
    sm_order_key,
    sm_customer_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND sm_customer_key IS NOT NULL
)
SELECT
  SUM(CASE WHEN sc.sm_customer_key IS NOT NULL THEN o.order_net_revenue ELSE 0 END) AS revenue_from_customers_with_subscription_history,
  SUM(o.order_net_revenue) AS total_revenue_last_30_days,
  SAFE_DIVIDE(
    SUM(CASE WHEN sc.sm_customer_key IS NOT NULL THEN o.order_net_revenue ELSE 0 END),
    NULLIF(SUM(o.order_net_revenue), 0)
  ) AS pct_of_revenue_from_subscription_history_customers
FROM last_30_valid_orders o
LEFT JOIN subscription_customers sc
  ON o.sm_customer_key = sc.sm_customer_key;

Refund rate by marketing channel (last 90 days)

What you’ll learn: Which marketing channels have higher refund rates—by order count and by revenue. High refund rates may indicate mismatched expectations from certain ad campaigns or audiences.

-- Assumptions: timeframe=last_90_days | metric=refund_rate | grain=sm_utm_source_medium | scope=valid_orders_only
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS marketing_channel,
    order_total_refunds,
    order_net_revenue_before_refunds,
    sm_order_key
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
SELECT
  marketing_channel,
  COUNT(DISTINCT sm_order_key) AS orders,
  COUNTIF(ABS(order_total_refunds) > 0) AS refunded_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 base
GROUP BY 1
HAVING orders >= 50
ORDER BY refund_rate_revenue DESC
LIMIT 50;

Distribution of orders and revenue by sales channel (last 30 days)

What you’ll learn: How your orders and revenue are distributed across different sales channels (online, POS, wholesale, etc.). Useful for understanding channel mix and identifying growth opportunities.

-- Assumptions: timeframe=last_30_days | metric=orders+net_revenue+share | grain=sm_channel | scope=valid_orders_only
SELECT
  COALESCE(NULLIF(LOWER(TRIM(sm_channel)), ''), '(unknown)') AS sm_channel,
  COUNT(*) AS orders,
  SUM(order_net_revenue) AS order_net_revenue,
  SAFE_DIVIDE(COUNT(*), NULLIF(SUM(COUNT(*)) OVER (), 0)) AS pct_orders,
  SAFE_DIVIDE(SUM(order_net_revenue), NULLIF(SUM(SUM(order_net_revenue)) OVER (), 0)) AS pct_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND order_cancelled_at IS NULL
  AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY orders DESC;

LTV & Retention

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

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

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)).

-- 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;

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.

-- 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;

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.

-- 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;

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.

-- 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;

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.

-- 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;

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.

-- 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;

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).

-- 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;

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.

-- 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;

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.

-- 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;

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.

-- 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;

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.

-- 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;

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.

-- 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;

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.

-- 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;

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).

-- 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;

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.

-- 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;

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.

-- 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;

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.

-- 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;

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.

-- 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;

Attribution & Data Health

These templates help you assess attribution coverage and basic data health before doing deeper analysis.

If you want table-level freshness/coverage metadata, start with: dim_data_dictionary.

Which tables are stale or missing data?

What you’ll learn: Which tables in your data warehouse haven’t been updated recently or are missing data entirely. Run this first to identify pipeline issues before diving into analysis.

-- Assumptions: timeframe=all_time | metric=table_freshness | grain=dataset+table | scope=sm_metadata
SELECT
  dataset_name,
  table_name,
  MAX(CAST(table_has_data AS INT64)) > 0 AS table_has_data,
  MAX(CAST(table_has_fresh_data_14d AS INT64)) > 0 AS table_has_fresh_data_14d,
  MAX(table_last_data_date) AS table_last_data_date,
  ANY_VALUE(table_description) AS table_description
FROM `your_project.sm_metadata.dim_data_dictionary`
WHERE dataset_name IN ('sm_transformed_v2', 'sm_experimental')
  AND dataset_name IS NOT NULL
  AND table_name IS NOT NULL
GROUP BY 1, 2
ORDER BY table_has_fresh_data_14d ASC, table_has_data ASC, table_last_data_date ASC, dataset_name, table_name
LIMIT 200;

Attribution column coverage on orders

What you’ll learn: How complete your attribution data is—what percentage of orders have UTM source, zero-party attribution, discount codes, landing pages, and referrer domains. Low coverage in key columns signals tracking gaps.

-- Assumptions: timeframe=all_time | metric=column_coverage | grain=column | scope=sm_metadata_obt_orders
WITH cols AS (
  SELECT 'sm_utm_source' AS column_name UNION ALL
  SELECT 'sm_utm_medium' UNION ALL
  SELECT 'sm_utm_source_medium' UNION ALL
  SELECT 'sm_zero_party_attribution_source' UNION ALL
  SELECT 'order_discount_codes_csv' UNION ALL
  SELECT 'sm_order_landing_page' UNION ALL
  SELECT 'sm_order_referrer_domain'
)
SELECT
  d.table_name,
  d.column_name,
  ROUND(100 - d.column_null_percentage, 1) AS non_null_pct,
  d.column_distinct_count,
  (
    SELECT STRING_AGG(
      CONCAT(v.value, ' (', FORMAT('%.1f', v.pct), '%)'),
      ', '
      ORDER BY v.pct DESC
      LIMIT 8
    )
    FROM UNNEST(IFNULL(d.categorical_value_distribution, [])) AS v
    WHERE v.value IS NOT NULL AND v.pct IS NOT NULL
  ) AS top_values
FROM `your_project.sm_metadata.dim_data_dictionary` d
INNER JOIN cols c
  ON d.column_name = c.column_name
WHERE d.dataset_name = 'sm_transformed_v2'
  AND d.table_name = 'obt_orders'
ORDER BY non_null_pct DESC, d.column_distinct_count DESC, d.column_name;

When UTMs are missing, what other attribution signals exist?

What you’ll learn: For orders without UTM tracking, what fallback attribution data is available (zero-party surveys, discount codes, landing pages, referrer domains). Helps you understand how much attribution you can recover.

-- Assumptions: timeframe=last_90_days | metric=fallback_coverage | grain=overall | scope=valid_orders_only_missing_utms
WITH missing_utms AS (
  SELECT
    sm_order_key,
    order_net_revenue,
    sm_zero_party_attribution_source,
    order_discount_codes_csv,
    sm_order_landing_page,
    sm_order_referrer_domain
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') = '(none) / (none)'
)
SELECT
  COUNT(DISTINCT sm_order_key) AS orders_missing_utms,
  SUM(order_net_revenue) AS order_net_revenue_missing_utms,
  COUNTIF(sm_zero_party_attribution_source IS NOT NULL AND TRIM(sm_zero_party_attribution_source) NOT IN ('', '(none)')) AS orders_with_zero_party,
  SAFE_DIVIDE(
    COUNTIF(sm_zero_party_attribution_source IS NOT NULL AND TRIM(sm_zero_party_attribution_source) NOT IN ('', '(none)')),
    NULLIF(COUNT(DISTINCT sm_order_key), 0)
  ) AS pct_with_zero_party,
  COUNTIF(order_discount_codes_csv IS NOT NULL AND TRIM(order_discount_codes_csv) NOT IN ('', '(none)')) AS orders_with_discount_code,
  SAFE_DIVIDE(
    COUNTIF(order_discount_codes_csv IS NOT NULL AND TRIM(order_discount_codes_csv) NOT IN ('', '(none)')),
    NULLIF(COUNT(DISTINCT sm_order_key), 0)
  ) AS pct_with_discount_code,
  COUNTIF(sm_order_landing_page IS NOT NULL AND TRIM(sm_order_landing_page) NOT IN ('', '(none)')) AS orders_with_landing_page,
  SAFE_DIVIDE(
    COUNTIF(sm_order_landing_page IS NOT NULL AND TRIM(sm_order_landing_page) NOT IN ('', '(none)')),
    NULLIF(COUNT(DISTINCT sm_order_key), 0)
  ) AS pct_with_landing_page,
  COUNTIF(sm_order_referrer_domain IS NOT NULL AND TRIM(sm_order_referrer_domain) NOT IN ('', '(none)')) AS orders_with_referrer_domain,
  SAFE_DIVIDE(
    COUNTIF(sm_order_referrer_domain IS NOT NULL AND TRIM(sm_order_referrer_domain) NOT IN ('', '(none)')),
    NULLIF(COUNT(DISTINCT sm_order_key), 0)
  ) AS pct_with_referrer_domain
FROM missing_utms;

Top referrer domains for orders missing UTMs

What you’ll learn: Which external sites are sending you traffic that isn’t tagged with UTMs. Use this to identify partners, affiliates, or other untracked sources that need proper tracking or attribution rules.

-- Assumptions: timeframe=last_90_days | metric=orders+net_revenue | grain=referrer_domain | scope=valid_orders_only_missing_utms
WITH base AS (
  SELECT
    LOWER(TRIM(sm_order_referrer_domain)) AS referrer_domain,
    sm_order_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') = '(none) / (none)'
    AND sm_order_referrer_domain IS NOT NULL
    AND TRIM(sm_order_referrer_domain) NOT IN ('', '(none)')
)
SELECT
  referrer_domain,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue
FROM base
GROUP BY 1
HAVING orders >= 25
ORDER BY order_net_revenue DESC
LIMIT 50;

Key join-key completeness (customers + SKU coverage)

What you’ll learn: The percentage of orders missing customer keys and order lines missing SKUs. Critical for data integrity—high null rates here break customer-level analysis and product reporting.

-- Assumptions: timeframe=last_30_days | metric=null_rate_checks | grain=overall | scope=valid_orders_only
WITH orders AS (
  SELECT
    COUNT(*) AS orders_total,
    COUNTIF(sm_customer_key IS NULL) AS orders_missing_customer_key
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
),
lines AS (
  SELECT
    COUNT(*) AS lines_total,
    COUNTIF(sku IS NULL OR TRIM(sku) = '' OR LOWER(sku) = 'missing sku') AS lines_missing_sku
  FROM `your_project.sm_transformed_v2.obt_order_lines`
  WHERE is_order_sm_valid = TRUE
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
)
SELECT
  orders_total,
  orders_missing_customer_key,
  SAFE_DIVIDE(orders_missing_customer_key, NULLIF(orders_total, 0)) AS pct_orders_missing_customer_key,
  lines_total,
  lines_missing_sku,
  SAFE_DIVIDE(lines_missing_sku, NULLIF(lines_total, 0)) AS pct_lines_missing_sku
FROM orders
CROSS JOIN lines;

Attribution health trend (weekly)

What you’ll learn: How your attribution coverage has changed week-over-week—UTM coverage, unattributed orders, and direct traffic share. Spot tracking regressions or improvements over time.

-- Assumptions: timeframe=last_26_weeks | metric=utm_coverage+direct_share+unattributed_share | grain=week | scope=valid_orders_only
WITH base AS (
  SELECT
    DATE_TRUNC(DATE(order_processed_at_local_datetime), WEEK(MONDAY)) AS week_start,
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium,
    sm_order_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 182 DAY)
),
weekly AS (
  SELECT
    week_start,
    COUNT(DISTINCT sm_order_key) AS orders,
    SUM(order_net_revenue) AS order_net_revenue,
    COUNTIF(source_medium = '(none) / (none)') AS unattributed_orders,
    SUM(CASE WHEN source_medium = '(none) / (none)' THEN order_net_revenue ELSE 0 END) AS unattributed_revenue,
    COUNTIF(source_medium IN ('(direct) / (none)', 'direct / (none)')) AS direct_orders,
    SUM(CASE WHEN source_medium IN ('(direct) / (none)', 'direct / (none)') THEN order_net_revenue ELSE 0 END) AS direct_revenue,
    COUNTIF(source_medium != '(none) / (none)') AS orders_with_utm_source_medium
  FROM base
  GROUP BY 1
)
SELECT
  week_start,
  orders,
  order_net_revenue,
  SAFE_DIVIDE(orders_with_utm_source_medium, NULLIF(orders, 0)) AS pct_orders_with_utm_source_medium,
  SAFE_DIVIDE(unattributed_orders, NULLIF(orders, 0)) AS pct_orders_unattributed,
  SAFE_DIVIDE(unattributed_revenue, NULLIF(order_net_revenue, 0)) AS pct_revenue_unattributed,
  SAFE_DIVIDE(direct_orders, NULLIF(orders, 0)) AS pct_orders_direct,
  SAFE_DIVIDE(direct_revenue, NULLIF(order_net_revenue, 0)) AS pct_revenue_direct
FROM weekly
ORDER BY week_start;

Attribution health by store and sales channel

What you’ll learn: How attribution coverage varies across your stores and sales channels (online vs. POS vs. wholesale). Some channels naturally have lower attribution—this helps set expectations.

-- Assumptions: timeframe=last_30_days | metric=unattributed_share | grain=sm_store_id+sm_channel | scope=valid_orders_only
WITH base AS (
  SELECT
    sm_store_id,
    COALESCE(NULLIF(LOWER(TRIM(sm_channel)), ''), '(unknown)') AS sm_channel,
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium,
    sm_order_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
    AND NULLIF(LOWER(TRIM(sm_channel)), '') IS NOT NULL
)
SELECT
  sm_store_id,
  sm_channel,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue,
  SAFE_DIVIDE(COUNTIF(source_medium = '(none) / (none)'), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS pct_orders_unattributed,
  SAFE_DIVIDE(
    SUM(CASE WHEN source_medium = '(none) / (none)' THEN order_net_revenue ELSE 0 END),
    NULLIF(SUM(order_net_revenue), 0)
  ) AS pct_revenue_unattributed
FROM base
GROUP BY 1, 2
HAVING orders >= 50
ORDER BY pct_revenue_unattributed DESC, orders DESC
LIMIT 100;

Discount code parsing (top codes by revenue)

What you’ll learn: Your top discount codes ranked by revenue, with order counts and AOV. Use this to evaluate promo effectiveness and identify codes that might be over-used or under-attributed.

-- Assumptions: timeframe=last_90_days | metric=orders+net_revenue | grain=discount_code | scope=valid_orders_only
-- Note: If an order has multiple discount codes, its revenue will be counted under each code (this is a code-usage view, not strict attribution).
WITH orders_with_codes AS (
  SELECT
    sm_order_key,
    order_net_revenue,
    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 DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND order_discount_codes_csv IS NOT NULL
    AND TRIM(order_discount_codes_csv) != ''
),
exploded AS (
  SELECT
    sm_order_key,
    order_net_revenue,
    TRIM(code_raw) AS discount_code
  FROM orders_with_codes,
  UNNEST(SPLIT(order_discount_codes_csv, ',')) AS code_raw
  WHERE TRIM(code_raw) != ''
)
SELECT
  discount_code,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue,
  SAFE_DIVIDE(SUM(order_net_revenue), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS aov
FROM exploded
GROUP BY 1
HAVING orders >= 25
ORDER BY order_net_revenue DESC
LIMIT 50;

Top landing pages for orders missing UTMs

What you’ll learn: Which pages customers land on when they arrive without UTM tracking. Useful for identifying untracked entry points and pages that need better tracking implementation.

-- Assumptions: timeframe=last_90_days | metric=orders+net_revenue | grain=landing_host+landing_path | scope=valid_orders_only_missing_utms
WITH base AS (
  SELECT
    sm_order_key,
    order_net_revenue,
    sm_order_landing_page
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') = '(none) / (none)'
    AND sm_order_landing_page IS NOT NULL
    AND TRIM(sm_order_landing_page) NOT IN ('', '(none)')
),
parsed AS (
  SELECT
    sm_order_key,
    order_net_revenue,
    REGEXP_EXTRACT(sm_order_landing_page, r'^(?:https?://)?([^/?#]+)') AS landing_host,
    REGEXP_EXTRACT(sm_order_landing_page, r'^(?:https?://)?[^/?#]+(/[^?#]*)') AS landing_path
  FROM base
)
SELECT
  COALESCE(NULLIF(LOWER(TRIM(landing_host)), ''), '(unknown)') AS landing_host,
  COALESCE(NULLIF(landing_path, ''), '/') AS landing_path,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue
FROM parsed
GROUP BY 1, 2
HAVING orders >= 25
ORDER BY order_net_revenue DESC
LIMIT 50;

Click-id coverage vs UTM coverage (gclid/fbclid)

What you’ll learn: The overlap between UTM tracking and ad-platform click IDs (gclid for Google, fbclid for Meta). Reveals orders where click IDs exist but UTMs don’t—potential attribution recovery opportunities.

-- Assumptions: timeframe=last_90_days | metric=utm_coverage+click_id_coverage | grain=week | scope=valid_orders_only
WITH base AS (
  SELECT
    DATE_TRUNC(DATE(order_processed_at_local_datetime), WEEK(MONDAY)) AS week_start,
    sm_order_key,
    order_net_revenue,
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium,
    sm_gclid,
    sm_fbclid
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
SELECT
  week_start,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue,
  COUNTIF(source_medium != '(none) / (none)') AS orders_with_utm_source_medium,
  SAFE_DIVIDE(COUNTIF(source_medium != '(none) / (none)'), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS pct_orders_with_utm_source_medium,
  COUNTIF(sm_gclid IS NOT NULL AND sm_gclid NOT IN ('', '(none)')) AS orders_with_gclid,
  SAFE_DIVIDE(COUNTIF(sm_gclid IS NOT NULL AND sm_gclid NOT IN ('', '(none)')), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS pct_orders_with_gclid,
  COUNTIF(sm_fbclid IS NOT NULL AND sm_fbclid NOT IN ('', '(none)')) AS orders_with_fbclid,
  SAFE_DIVIDE(COUNTIF(sm_fbclid IS NOT NULL AND sm_fbclid NOT IN ('', '(none)')), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS pct_orders_with_fbclid,
  COUNTIF(source_medium = '(none) / (none)' AND sm_gclid IS NOT NULL AND sm_gclid NOT IN ('', '(none)')) AS utm_missing_but_gclid_orders,
  COUNTIF(source_medium = '(none) / (none)' AND sm_fbclid IS NOT NULL AND sm_fbclid NOT IN ('', '(none)')) AS utm_missing_but_fbclid_orders
FROM base
GROUP BY 1
ORDER BY week_start;

These are deeper-dive investigations for when attribution looks “weird” (too much direct/unattributed), or when downstream metrics are being skewed by edge-case orders.

$0 / negative net-revenue order share by source/medium (last 90 days)

What you’ll learn: Which source/mediums have an unusually high share of valid orders with order_net_revenue = 0 (or negative). This often indicates replacements/comp orders or heavy discounts that can skew repeat/retention metrics.

-- Assumptions: timeframe=last_90_days | metric=paid_vs_zero_vs_negative_order_share | grain=source_medium | scope=valid_orders_only
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium,
    sm_order_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
SELECT
  source_medium,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue,
  COUNTIF(order_net_revenue > 0) AS paid_orders,
  SAFE_DIVIDE(COUNTIF(order_net_revenue > 0), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS pct_paid_orders,
  COUNTIF(order_net_revenue = 0) AS zero_net_revenue_orders,
  SAFE_DIVIDE(COUNTIF(order_net_revenue = 0), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS pct_zero_net_revenue_orders,
  COUNTIF(order_net_revenue < 0) AS negative_net_revenue_orders,
  SAFE_DIVIDE(COUNTIF(order_net_revenue < 0), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS pct_negative_net_revenue_orders
FROM base
GROUP BY 1
HAVING orders >= 200
ORDER BY pct_zero_net_revenue_orders DESC, orders DESC
LIMIT 50;

Unattributed share by source system and sales channel (last 90 days)

What you’ll learn: Where unattributed orders are coming from by commerce platform (source_system) and sales channel (sm_channel). Some channels (e.g., marketplaces or POS) naturally have lower UTM coverage—this helps separate “expected” vs “broken tracking.”

-- Assumptions: timeframe=last_90_days | metric=unattributed_share | grain=source_system+sm_channel | scope=valid_orders_only
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS source_system,
    COALESCE(NULLIF(LOWER(TRIM(sm_channel)), ''), '(unknown)') AS sm_channel,
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium,
    sm_order_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND NULLIF(LOWER(TRIM(source_system)), '') IS NOT NULL
    AND NULLIF(LOWER(TRIM(sm_channel)), '') IS NOT NULL
)
SELECT
  source_system,
  sm_channel,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue,
  COUNTIF(source_medium = '(none) / (none)') AS unattributed_orders,
  SAFE_DIVIDE(COUNTIF(source_medium = '(none) / (none)'), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS pct_orders_unattributed,
  SAFE_DIVIDE(
    SUM(CASE WHEN source_medium = '(none) / (none)' THEN order_net_revenue ELSE 0 END),
    NULLIF(SUM(order_net_revenue), 0)
  ) AS pct_revenue_unattributed
FROM base
GROUP BY 1, 2
HAVING orders >= 200
ORDER BY pct_revenue_unattributed DESC, orders DESC
LIMIT 100;

Top landing pages for direct traffic orders (last 90 days)

What you’ll learn: Which landing pages are most associated with “direct” orders (based on sm_utm_source_medium)—and whether landing page capture is missing. This helps diagnose tracking gaps (e.g., missing UTMs or missing landing-page capture on key entry flows).

-- Assumptions: timeframe=last_90_days | metric=orders+net_revenue | grain=landing_host+landing_path | scope=valid_orders_only_direct
WITH base AS (
  SELECT
    sm_order_key,
    order_net_revenue,
    NULLIF(TRIM(sm_order_landing_page), '') AS sm_order_landing_page
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') IN ('(direct) / (none)', 'direct / (none)')
),
parsed AS (
  SELECT
    sm_order_key,
    order_net_revenue,
    REGEXP_EXTRACT(sm_order_landing_page, r'^(?:https?://)?([^/?#]+)') AS landing_host,
    REGEXP_EXTRACT(sm_order_landing_page, r'^(?:https?://)?[^/?#]+(/[^?#]*)') AS landing_path
  FROM base
)
SELECT
  COALESCE(NULLIF(LOWER(TRIM(landing_host)), ''), '(missing_landing_page)') AS landing_host,
  COALESCE(NULLIF(landing_path, ''), '(missing_landing_page)') AS landing_path,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue
FROM parsed
GROUP BY 1, 2
HAVING orders >= 25
ORDER BY order_net_revenue DESC
LIMIT 50;

Attribution health trend with week-over-week deltas (weekly)

What you’ll learn: A “tracking regression detector”: week-over-week changes in unattributed/direct order share and revenue share. Sudden jumps typically indicate tagging/measurement changes.

-- Assumptions: timeframe=last_26_weeks | metric=utm_coverage+direct_share+unattributed_share+wow_deltas | grain=week | scope=valid_orders_only
WITH base AS (
  SELECT
    DATE_TRUNC(DATE(order_processed_at_local_datetime), WEEK(MONDAY)) AS week_start,
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium,
    sm_order_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 182 DAY)
),
weekly AS (
  SELECT
    week_start,
    COUNT(DISTINCT sm_order_key) AS orders,
    SUM(order_net_revenue) AS order_net_revenue,
    COUNTIF(source_medium = '(none) / (none)') AS unattributed_orders,
    SUM(CASE WHEN source_medium = '(none) / (none)' THEN order_net_revenue ELSE 0 END) AS unattributed_revenue,
    COUNTIF(source_medium IN ('(direct) / (none)', 'direct / (none)')) AS direct_orders,
    SUM(CASE WHEN source_medium IN ('(direct) / (none)', 'direct / (none)') THEN order_net_revenue ELSE 0 END) AS direct_revenue,
    COUNTIF(source_medium != '(none) / (none)') AS orders_with_utm_source_medium
  FROM base
  GROUP BY 1
),
metrics AS (
  SELECT
    week_start,
    orders,
    order_net_revenue,
    SAFE_DIVIDE(orders_with_utm_source_medium, NULLIF(orders, 0)) AS pct_orders_with_utm_source_medium,
    SAFE_DIVIDE(unattributed_orders, NULLIF(orders, 0)) AS pct_orders_unattributed,
    SAFE_DIVIDE(unattributed_revenue, NULLIF(order_net_revenue, 0)) AS pct_revenue_unattributed,
    SAFE_DIVIDE(direct_orders, NULLIF(orders, 0)) AS pct_orders_direct,
    SAFE_DIVIDE(direct_revenue, NULLIF(order_net_revenue, 0)) AS pct_revenue_direct
  FROM weekly
)
SELECT
  week_start,
  orders,
  order_net_revenue,
  pct_orders_with_utm_source_medium,
  pct_orders_unattributed,
  pct_orders_unattributed - LAG(pct_orders_unattributed) OVER (ORDER BY week_start) AS delta_pct_orders_unattributed,
  pct_revenue_unattributed,
  pct_revenue_unattributed - LAG(pct_revenue_unattributed) OVER (ORDER BY week_start) AS delta_pct_revenue_unattributed,
  pct_orders_direct,
  pct_orders_direct - LAG(pct_orders_direct) OVER (ORDER BY week_start) AS delta_pct_orders_direct,
  pct_revenue_direct,
  pct_revenue_direct - LAG(pct_revenue_direct) OVER (ORDER BY week_start) AS delta_pct_revenue_direct
FROM metrics
ORDER BY week_start;

UTM source/medium discovery (top normalized values, last 90 days)

What you’ll learn: What your UTM source/medium values actually look like in practice (normalized with LOWER(TRIM())). Use this to discover the exact strings you should filter on—without guessing.

-- Assumptions: timeframe=last_90_days | metric=orders+net_revenue | grain=source_medium | scope=valid_orders_only
WITH base AS (
  SELECT
    COALESCE(NULLIF(LOWER(TRIM(sm_utm_source_medium)), ''), '(none) / (none)') AS source_medium,
    sm_order_key,
    order_net_revenue
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
SELECT
  source_medium,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS order_net_revenue,
  SAFE_DIVIDE(COUNT(DISTINCT sm_order_key), NULLIF(SUM(COUNT(DISTINCT sm_order_key)) OVER (), 0)) AS pct_orders,
  SAFE_DIVIDE(SUM(order_net_revenue), NULLIF(SUM(SUM(order_net_revenue)) OVER (), 0)) AS pct_revenue
FROM base
GROUP BY 1
ORDER BY orders DESC
LIMIT 50;

Join-key coverage trend (weekly missing customer keys + missing SKUs)

What you’ll learn: Whether key join fields are getting worse over time. Spikes in missing sm_customer_key (orders) or missing sku (order lines) will break customer-level and product-level analysis.

-- Assumptions: timeframe=last_26_weeks | metric=missing_key_trends | grain=week | scope=valid_orders_only
WITH orders_weekly AS (
  SELECT
    DATE_TRUNC(DATE(order_processed_at_local_datetime), WEEK(MONDAY)) AS week_start,
    COUNT(*) AS orders_total,
    COUNTIF(sm_customer_key IS NULL) AS orders_missing_customer_key
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND order_cancelled_at IS NULL
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 182 DAY)
  GROUP BY 1
),
lines_weekly AS (
  SELECT
    DATE_TRUNC(DATE(order_processed_at_local_datetime), WEEK(MONDAY)) AS week_start,
    COUNT(*) AS lines_total,
    COUNTIF(sku IS NULL OR TRIM(sku) = '' OR LOWER(sku) = 'missing sku') AS lines_missing_sku
  FROM `your_project.sm_transformed_v2.obt_order_lines`
  WHERE is_order_sm_valid = TRUE
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 182 DAY)
  GROUP BY 1
)
SELECT
  o.week_start,
  o.orders_total,
  o.orders_missing_customer_key,
  SAFE_DIVIDE(o.orders_missing_customer_key, NULLIF(o.orders_total, 0)) AS pct_orders_missing_customer_key,
  l.lines_total,
  l.lines_missing_sku,
  SAFE_DIVIDE(l.lines_missing_sku, NULLIF(l.lines_total, 0)) AS pct_lines_missing_sku
FROM orders_weekly o
LEFT JOIN lines_weekly l
  USING (week_start)
ORDER BY o.week_start;

Multiple discount codes prevalence (double-counting risk, last 90 days)

What you’ll learn: How often orders have multiple discount codes applied. This matters because any “revenue by discount code” view will double-count revenue across codes when multiple codes exist.

-- Assumptions: timeframe=last_90_days | metric=discount_code_multiplicity | grain=code_count_bucket | scope=valid_orders_only
WITH base AS (
  SELECT
    sm_order_key,
    order_net_revenue,
    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 DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND order_discount_codes_csv IS NOT NULL
    AND TRIM(order_discount_codes_csv) NOT IN ('', '(none)')
),
code_counts AS (
  SELECT
    sm_order_key,
    order_net_revenue,
    ARRAY_LENGTH(
      ARRAY(
        SELECT TRIM(code_raw)
        FROM UNNEST(SPLIT(order_discount_codes_csv, ',')) AS code_raw
        WHERE TRIM(code_raw) != ''
      )
    ) AS code_count
  FROM base
),
bucketed AS (
  SELECT
    sm_order_key,
    order_net_revenue,
    LEAST(code_count, 5) AS code_count_bucket
  FROM code_counts
)
SELECT
  CASE code_count_bucket
    WHEN 5 THEN '5+'
    ELSE CAST(code_count_bucket AS STRING)
  END AS code_count_bucket,
  COUNT(DISTINCT sm_order_key) AS orders,
  SAFE_DIVIDE(COUNT(DISTINCT sm_order_key), NULLIF(SUM(COUNT(DISTINCT sm_order_key)) OVER (), 0)) AS pct_orders,
  SUM(order_net_revenue) AS order_net_revenue,
  SAFE_DIVIDE(SUM(order_net_revenue), NULLIF(SUM(SUM(order_net_revenue)) OVER (), 0)) AS pct_revenue
FROM bucketed
GROUP BY 1, code_count_bucket
ORDER BY code_count_bucket;

Customer Support

Ticket volume + one-touch rate by communication channel (last 30 days)

What you’ll learn: How ticket volume and one-touch resolution varies by support channel (email, chat, Instagram DM, etc.). Use this to identify which channels are driving the most workload and where your team is resolving issues efficiently.

-- Assumptions: timeframe=last_30_days | metric=ticket_volume+one_touch_rate | grain=communication_channel | scope=exclude_spam
SELECT
  COALESCE(NULLIF(LOWER(TRIM(ticket_communication_channel)), ''), '(unknown)') AS ticket_communication_channel,
  COUNT(DISTINCT sm_ticket_key) AS tickets,
  COUNTIF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'open') AS open_tickets,
  COUNTIF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'closed') AS closed_tickets,
  COUNTIF(is_ticket_one_touch = TRUE) AS one_touch_tickets,
  SAFE_DIVIDE(COUNTIF(is_ticket_one_touch = TRUE), NULLIF(COUNT(DISTINCT sm_ticket_key), 0)) AS one_touch_rate
FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
WHERE is_ticket_spam = FALSE
  AND DATE(ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1
ORDER BY tickets DESC;

Resolution time + CSAT coverage by assignee team (last 90 days)

What you’ll learn: Which teams are closing tickets fastest and how complete your CSAT data is by team. Use this for staffing, training, and process improvement.

-- Assumptions: timeframe=last_90_days | metric=resolution_time_hours+csat_coverage | grain=assignee_team | scope=closed_tickets_exclude_spam
SELECT
  COALESCE(NULLIF(TRIM(ticket_assignee_team_name), ''), '(unassigned)') AS ticket_assignee_team_name,
  COUNT(DISTINCT sm_ticket_key) AS closed_tickets,
  AVG(ticket_resolution_time_hours) AS avg_resolution_time_hours,
  APPROX_QUANTILES(ticket_resolution_time_hours, 101)[OFFSET(50)] AS p50_resolution_time_hours,
  APPROX_QUANTILES(ticket_resolution_time_hours, 101)[OFFSET(90)] AS p90_resolution_time_hours,
  COUNTIF(ticket_csat_score IS NOT NULL) AS csat_responses,
  SAFE_DIVIDE(COUNTIF(ticket_csat_score IS NOT NULL), NULLIF(COUNT(DISTINCT sm_ticket_key), 0)) AS csat_response_rate,
  AVG(ticket_csat_score) AS avg_csat_score
FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
WHERE is_ticket_spam = FALSE
  AND ticket_resolution_time_hours IS NOT NULL
  AND ticket_status IS NOT NULL
  AND LOWER(ticket_status) = 'closed'
  AND DATE(ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY 1
HAVING closed_tickets >= 25
ORDER BY avg_resolution_time_hours ASC;

Support backlog aging by team and channel (open tickets)

What you’ll learn: How old your open ticket backlog is (age buckets + p50/p90) broken out by team and channel. Useful for backlog management and escalation.

-- Assumptions: timeframe=current_state | metric=open_ticket_age_buckets | grain=team+channel | scope=open_tickets_exclude_spam
WITH open_tickets AS (
  SELECT
    COALESCE(NULLIF(TRIM(ticket_assignee_team_name), ''), '(unassigned)') AS ticket_assignee_team_name,
    COALESCE(NULLIF(LOWER(TRIM(ticket_communication_channel)), ''), '(unknown)') AS ticket_communication_channel,
    DATE_DIFF(CURRENT_DATE(), DATE(ticket_created_at_local_datetime), DAY) AS ticket_age_days
  FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
  WHERE is_ticket_spam = FALSE
    AND ticket_status IS NOT NULL
    AND LOWER(ticket_status) = 'open'
    AND ticket_created_at_local_datetime IS NOT NULL
)
SELECT
  ticket_assignee_team_name,
  ticket_communication_channel,
  COUNT(*) AS open_tickets,
  COUNTIF(ticket_age_days = 0) AS age_0d,
  COUNTIF(ticket_age_days BETWEEN 1 AND 2) AS age_1_2d,
  COUNTIF(ticket_age_days BETWEEN 3 AND 6) AS age_3_6d,
  COUNTIF(ticket_age_days BETWEEN 7 AND 13) AS age_7_13d,
  COUNTIF(ticket_age_days >= 14) AS age_14d_plus,
  APPROX_QUANTILES(ticket_age_days, 101)[OFFSET(50)] AS p50_ticket_age_days,
  APPROX_QUANTILES(ticket_age_days, 101)[OFFSET(90)] AS p90_ticket_age_days
FROM open_tickets
GROUP BY 1, 2
HAVING COUNT(*) >= 25
ORDER BY open_tickets DESC;

Unread open-ticket share by team and channel (ops triage)

What you’ll learn: Which teams/channels have the highest unread share of open tickets. Useful for triage and staffing.

-- Assumptions: timeframe=current_state | metric=unread_open_ticket_share | grain=team+channel | scope=open_tickets_exclude_spam
SELECT
  COALESCE(NULLIF(TRIM(ticket_assignee_team_name), ''), '(unassigned)') AS ticket_assignee_team_name,
  COALESCE(NULLIF(LOWER(TRIM(ticket_communication_channel)), ''), '(unknown)') AS ticket_communication_channel,
  COUNT(DISTINCT sm_ticket_key) AS open_tickets,
  COUNTIF(is_ticket_unread = TRUE) AS unread_open_tickets,
  SAFE_DIVIDE(COUNTIF(is_ticket_unread = TRUE), NULLIF(COUNT(DISTINCT sm_ticket_key), 0)) AS unread_open_ticket_rate
FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
WHERE is_ticket_spam = FALSE
  AND ticket_status IS NOT NULL
  AND LOWER(ticket_status) = 'open'
GROUP BY 1, 2
HAVING COUNT(DISTINCT sm_ticket_key) >= 25
ORDER BY unread_open_ticket_rate DESC, open_tickets DESC;

Top support tags by ticket volume + one-touch + resolution time (last 90 days)

What you’ll learn: Which tagged issue types generate the most tickets, and whether they tend to be one-touch or slow to resolve. Useful for product feedback loops, macro coverage, and staffing.

-- Assumptions: timeframe=last_90_days | metric=ticket_volume+one_touch_rate+resolution_time | grain=tag | scope=exclude_spam
WITH base AS (
  SELECT
    sm_ticket_key,
    ticket_status,
    is_ticket_one_touch,
    ticket_resolution_time_hours,
    ticket_tag_names_csv
  FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
  WHERE is_ticket_spam = FALSE
    AND ticket_created_at_local_datetime IS NOT NULL
    AND DATE(ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND ticket_tag_names_csv IS NOT NULL
    AND TRIM(ticket_tag_names_csv) != ''
),
exploded AS (
  SELECT DISTINCT
    sm_ticket_key,
    ticket_status,
    is_ticket_one_touch,
    ticket_resolution_time_hours,
    LOWER(TRIM(tag_raw)) AS tag
  FROM base
  CROSS JOIN UNNEST(SPLIT(ticket_tag_names_csv, ',')) AS tag_raw
  WHERE TRIM(tag_raw) != ''
)
SELECT
  tag,
  COUNT(DISTINCT sm_ticket_key) AS tickets,
  COUNTIF(is_ticket_one_touch = TRUE) AS one_touch_tickets,
  SAFE_DIVIDE(COUNTIF(is_ticket_one_touch = TRUE), NULLIF(COUNT(DISTINCT sm_ticket_key), 0)) AS one_touch_rate,
  COUNTIF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'closed') AS closed_tickets,
  AVG(IF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'closed', ticket_resolution_time_hours, NULL)) AS avg_resolution_time_hours_closed
FROM exploded
GROUP BY 1
HAVING tickets >= 50
ORDER BY tickets DESC
LIMIT 50;

One ticket can have multiple tags, so a single ticket may appear in multiple tag rows. Use this for per-tag diagnostics, not for global totals.

Support workload by priority × channel × team (last 30 days)

What you’ll learn: Which priority/channel/team combinations generate the most tickets, and whether they are being resolved quickly. Useful for triage rules and staffing.

-- Assumptions: timeframe=last_30_days | metric=ticket_volume+resolution_time | grain=priority+channel+team | scope=exclude_spam
SELECT
  COALESCE(NULLIF(LOWER(TRIM(ticket_priority)), ''), '(unknown)') AS ticket_priority,
  COALESCE(NULLIF(LOWER(TRIM(ticket_communication_channel)), ''), '(unknown)') AS ticket_communication_channel,
  COALESCE(NULLIF(TRIM(ticket_assignee_team_name), ''), '(unassigned)') AS ticket_assignee_team_name,
  COUNT(DISTINCT sm_ticket_key) AS tickets,
  COUNTIF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'open') AS open_tickets,
  COUNTIF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'closed') AS closed_tickets,
  COUNTIF(is_ticket_one_touch = TRUE) AS one_touch_tickets,
  SAFE_DIVIDE(COUNTIF(is_ticket_one_touch = TRUE), NULLIF(COUNT(DISTINCT sm_ticket_key), 0)) AS one_touch_rate,
  AVG(IF(ticket_status IS NOT NULL AND LOWER(ticket_status) = 'closed', ticket_resolution_time_hours, NULL)) AS avg_resolution_time_hours_closed
FROM `your_project.sm_transformed_v2.obt_customer_support_tickets`
WHERE is_ticket_spam = FALSE
  AND ticket_created_at_local_datetime IS NOT NULL
  AND DATE(ticket_created_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2, 3
HAVING tickets >= 25
ORDER BY tickets DESC;

---

Request a Query

Have a question that’s not covered here? We regularly add new queries. If there’s a template you’d like added (subscription churn, cohort LTV curves, creative performance, etc.), reach out to your SourceMedium team and include the business question and the table(s) you’re using.