Ask. Act. Accelerate.

                                  -- Analysis: top platform/campaign-type ROAS combinations

-- Assumptions: timeframe=last_30_days | metric=roas=platform_reported_revenue/ad_spend | grain=platform+campaign_type | scope=rows_with_spend_and_revenue
SELECT
  COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS platform,
  COALESCE(NULLIF(LOWER(TRIM(ad_campaign_type)), ''), '(unknown)') AS campaign_type,
  SUM(ad_spend) AS ad_spend,
  SUM(ad_platform_reported_revenue) AS platform_reported_revenue,
  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
HAVING ad_spend >= 5000
  AND platform_reported_revenue > 0
ORDER BY roas DESC
LIMIT 20;
                                

                                  -- Analysis: top product SKUs by net revenue from order lines

-- 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
GROUP BY 1
ORDER BY order_line_net_revenue DESC
LIMIT 10;
                                

                                  -- Analysis: weekly attribution trend using order-level UTM coverage

-- Assumptions: timeframe=last_26_weeks_full | metric=utm_coverage+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)
    AND DATE(order_processed_at_local_datetime) < DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY))
),
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 != '(none) / (none)') AS orders_with_utm_source_medium
  FROM base
  GROUP BY 1
)
SELECT
  week_start,
  orders,
  ROUND(SAFE_MULTIPLY(SAFE_DIVIDE(orders_with_utm_source_medium, NULLIF(orders, 0)), 100), 2) AS pct_orders_with_utm_source_medium,
  ROUND(SAFE_MULTIPLY(SAFE_DIVIDE(unattributed_orders, NULLIF(orders, 0)), 100), 2) AS pct_orders_unattributed,
  ROUND(SAFE_MULTIPLY(SAFE_DIVIDE(unattributed_revenue, NULLIF(order_net_revenue, 0)), 100), 2) AS pct_revenue_unattributed
FROM weekly
ORDER BY week_start;
                                

                                  -- Analysis synthesis: combine ROAS, spend scale, and recent momentum into a platform priority score

-- Assumptions: timeframe=last_12_weeks | metric=priority_score_from_roas+scale+momentum | grain=source_system | scope=platforms_with_spend_and_revenue
WITH weekly AS (
  SELECT
    DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
    COALESCE(NULLIF(LOWER(TRIM(source_system)), ''), '(unknown)') AS platform,
    SUM(ad_spend) AS ad_spend,
    SUM(ad_platform_reported_revenue) AS platform_revenue
  FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily`
  WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 84 DAY)
  GROUP BY 1, 2
),
platform_rollup AS (
  SELECT
    platform,
    SUM(ad_spend) AS spend_12w,
    SUM(platform_revenue) AS revenue_12w,
    SAFE_DIVIDE(SUM(platform_revenue), NULLIF(SUM(ad_spend), 0)) AS roas_12w,
    SAFE_DIVIDE(
      SUM(CASE WHEN week_start >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) THEN platform_revenue ELSE 0 END),
      NULLIF(SUM(CASE WHEN week_start < DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) THEN platform_revenue ELSE 0 END), 0)
    ) AS recent_momentum
  FROM weekly
  WHERE ad_spend > 0
  GROUP BY 1
  HAVING spend_12w >= 50000
    AND revenue_12w > 0
),
scored AS (
  SELECT
    platform,
    spend_12w,
    revenue_12w,
    roas_12w,
    recent_momentum,
    ROUND(
      SAFE_DIVIDE(roas_12w, NULLIF(MAX(roas_12w) OVER (), 0)) * 0.50 +
      SAFE_DIVIDE(spend_12w, NULLIF(MAX(spend_12w) OVER (), 0)) * 0.20 +
      SAFE_DIVIDE(recent_momentum, NULLIF(MAX(recent_momentum) OVER (), 0)) * 0.30,
      3
    ) AS priority_score
  FROM platform_rollup
)
SELECT
  platform,
  spend_12w,
  revenue_12w,
  roas_12w,
  recent_momentum,
  priority_score
FROM scored
ORDER BY priority_score DESC
LIMIT 10;
                                

                                  -- Analysis: weekly flow vs campaign performance

-- Assumptions: timeframe=last_8_weeks_full | metric=click_rate+platform_attributed_revenue | 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_clicks) AS clicks,
  SUM(platform_reported_order_revenue) AS platform_reported_order_revenue,
  SAFE_MULTIPLY(SAFE_DIVIDE(SUM(message_unique_clicks), NULLIF(SUM(message_unique_receives), 0)), 100) AS click_rate_pct
FROM `your_project.sm_transformed_v2.rpt_outbound_message_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 56 DAY)
  AND date < DATE_TRUNC(CURRENT_DATE(), WEEK(MONDAY))
  AND LOWER(message_type) IN ('flow', 'campaign')
GROUP BY 1, 2
ORDER BY week_start, message_type;
                                

                                  -- Analysis: lead event to first purchase latency

-- Assumptions: timeframe=last_90_days | metric=lead_to_purchase_timing_hours | grain=lead_utm_source_medium | scope=event_user_id_non_null+top_sources
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,
    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
WHERE lead_utm_source_medium != '(none) / (none)'
GROUP BY 1
HAVING purchasers_with_lead_event >= 150
ORDER BY purchasers_with_lead_event DESC
LIMIT 50;
                                

                                  -- Analysis: cohort-based LTV:CAC ratio by acquisition source/medium

-- Assumptions: timeframe=last_12_cohort_months | metric=ltv_to_cac_ratio_6m | grain=source_medium | scope=cohort_table_all_orders+scaled_sources
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
    AND acquisition_order_filter_dimension_value != '(none) / (none)'
  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 >= 500
ORDER BY cohort_customers DESC
LIMIT 25;