Data you can trust.
Infrastructure you can keep.

                                  -- 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: return rate and net return cost by sm_channel

-- Assumptions: timeframe=last_30_days | metric=return_rate=returns/orders | grain=sm_channel | scope=matched_returns_only
SELECT
  o.sm_channel,
  COUNT(DISTINCT o.sm_order_key) AS orders,
  ROUND(
    SAFE_DIVIDE(COUNT(DISTINCT r.sm_order_key), COUNT(DISTINCT o.sm_order_key)) * 100,
    1
  ) AS return_rate_pct,
  COALESCE(SUM(r.net_return_cost), 0) AS net_return_cost
FROM `your_project.sm_transformed_v2.rpt_executive_summary_daily` o
LEFT JOIN `your_project.sm_transformed_v2.fct_returns` r
  ON o.sm_order_key = r.sm_order_key
  AND r.is_order_matched = TRUE
WHERE o.date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND o.is_customer_order = TRUE
GROUP BY 1
ORDER BY return_rate_pct 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;