Modeling with SourceMedium

-- Example: Custom cohort analysis
WITH first_valid_order AS (
  SELECT
    sm_customer_key,
    MIN(order_processed_at) AS first_valid_order_processed_at
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
  GROUP BY 1
)
SELECT
  DATE_TRUNC(DATE(first_valid_order_processed_at), MONTH) AS cohort_month,
  COUNT(DISTINCT o.sm_customer_key) AS customers,
  SUM(o.order_net_revenue) AS net_revenue
FROM `your_project.sm_transformed_v2.obt_orders` o
JOIN first_valid_order f
  ON o.sm_customer_key = f.sm_customer_key
WHERE o.is_order_sm_valid = TRUE
GROUP BY 1
ORDER BY 1

-- Example: Order line-level analysis with product details
SELECT
  order_id,
  product_title,
  order_line_net_revenue,
  product_type
FROM `your_project.sm_transformed_v2.obt_order_lines`
WHERE is_order_sm_valid = TRUE

-- Example: Daily ad performance already aggregated
SELECT
  date,
  sm_channel,
  ad_spend,
  ad_platform_reported_revenue,
  SAFE_DIVIDE(ad_platform_reported_revenue, ad_spend) AS roas
FROM `your_project.sm_transformed_v2.rpt_ad_performance_daily`
WHERE date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)

SELECT COUNT(*) AS valid_orders
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE;

CREATE TABLE `your_project.your_dataset.custom_model`
PARTITION BY DATE(order_created_at)
CLUSTER BY sm_store_id, sm_channel
AS (
  SELECT ...
)

SELECT
  sm_customer_key,
  MIN(order_created_at) AS first_order_date,
  MAX(order_created_at) AS last_order_date,
  COUNT(DISTINCT sm_order_key) AS order_count,
  SUM(order_net_revenue) AS lifetime_revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
GROUP BY 1

SELECT
  sm_channel,
  sm_sub_channel,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(order_net_revenue) AS revenue
FROM `your_project.sm_transformed_v2.obt_orders`
WHERE is_order_sm_valid = TRUE
  AND DATE(order_created_at) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY 1, 2
ORDER BY revenue DESC

SELECT
  a.product_title AS product_a,
  b.product_title AS product_b,
  COUNT(DISTINCT a.sm_order_key) AS co_purchase_count
FROM `your_project.sm_transformed_v2.obt_order_lines` a
JOIN `your_project.sm_transformed_v2.obt_order_lines` b
  ON a.sm_order_key = b.sm_order_key
  AND a.sm_order_line_key < b.sm_order_line_key
WHERE a.is_order_sm_valid = TRUE
GROUP BY 1, 2
HAVING co_purchase_count >= 10
ORDER BY co_purchase_count DESC