Orders & Revenue

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

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

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

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

-- Assumptions: source=Shopify | grain=month | metric=return_rate=return_orders/valid_orders | filter=is_order_matched
WITH all_orders AS (
  SELECT
    FORMAT_DATE('%Y-%m', DATE(order_processed_at_local_datetime)) AS month,
    COUNT(DISTINCT sm_order_key) AS total_valid_orders
  FROM `your_project.sm_transformed_v2.obt_orders`
  WHERE is_order_sm_valid = TRUE
    AND LOWER(source_system) = 'shopify'
  GROUP BY 1
),
returned_orders AS (
  SELECT
    FORMAT_DATE('%Y-%m', DATE(order_processed_at_local_datetime)) AS month,
    COUNT(DISTINCT sm_order_key) AS return_orders,
    COUNT(DISTINCT CASE WHEN has_exchange THEN sm_order_key END) AS exchange_orders
  FROM `your_project.sm_transformed_v2.fct_returns`
  WHERE is_order_matched = TRUE
  GROUP BY 1
)
SELECT
  a.month,
  a.total_valid_orders,
  COALESCE(r.return_orders, 0) AS return_orders,
  COALESCE(r.exchange_orders, 0) AS exchange_orders,
  SAFE_DIVIDE(COALESCE(r.return_orders, 0), a.total_valid_orders) AS return_rate
FROM all_orders AS a
LEFT JOIN returned_orders AS r USING (month)
ORDER BY 1 DESC;

-- Assumptions: source=Shopify | grain=sm_channel | metric=net_return_cost | filter=is_order_matched
SELECT
  sm_channel,
  COUNT(DISTINCT sm_order_key) AS return_orders,
  SUM(net_return_cost) AS total_net_return_cost,
  SUM(refund_amount) AS total_refund_amount,
  SUM(gift_card_amount) AS total_gift_card_amount,
  SUM(upsell_amount) AS total_upsell_amount,
  SAFE_DIVIDE(SUM(net_return_cost), NULLIF(COUNT(DISTINCT sm_order_key), 0)) AS avg_net_return_cost_per_order
FROM `your_project.sm_transformed_v2.fct_returns`
WHERE is_order_matched = TRUE
GROUP BY 1
ORDER BY total_net_return_cost DESC;

-- Assumptions: source=Shopify | grain=outcome_type | metric=return_count | filter=is_order_matched
SELECT
  CASE
    WHEN has_refund AND has_exchange THEN 'refund + exchange'
    WHEN has_refund THEN 'refund only'
    WHEN has_exchange THEN 'exchange only'
    WHEN has_credit THEN 'store credit'
    WHEN has_upsell THEN 'upsell'
    ELSE 'other'
  END AS return_outcome_bucket,
  COUNT(*) AS returns,
  COUNT(DISTINCT sm_order_key) AS orders,
  SUM(net_return_cost) AS total_net_return_cost
FROM `your_project.sm_transformed_v2.fct_returns`
WHERE is_order_matched = TRUE
GROUP BY 1
ORDER BY returns DESC;

-- Assumptions: source=Shopify | grain=product_variant | metric=return_order_rate | filter=valid_orders+is_order_matched
WITH valid_product_orders AS (
  SELECT
    sm_store_id,
    sm_product_variant_key,
    product_title,
    product_variant_title,
    sku,
    sm_order_key,
    SUM(order_line_net_quantity) AS units_sold
  FROM `your_project.sm_transformed_v2.obt_order_lines`
  WHERE is_order_sm_valid = TRUE
    AND LOWER(source_system) = 'shopify'
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
    AND sm_product_variant_key IS NOT NULL
  GROUP BY 1, 2, 3, 4, 5, 6
),
returned_orders AS (
  SELECT DISTINCT
    sm_store_id,
    sm_order_key
  FROM `your_project.sm_transformed_v2.fct_returns`
  WHERE is_order_matched = TRUE
    AND DATE(order_processed_at_local_datetime) >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
)
SELECT
  v.product_title,
  v.product_variant_title,
  v.sku,
  COUNT(DISTINCT v.sm_order_key) AS orders_containing_product,
  COUNT(DISTINCT r.sm_order_key) AS returned_orders_containing_product,
  SUM(v.units_sold) AS units_sold,
  SAFE_DIVIDE(COUNT(DISTINCT r.sm_order_key), NULLIF(COUNT(DISTINCT v.sm_order_key), 0)) AS return_order_rate
FROM valid_product_orders AS v
LEFT JOIN returned_orders AS r
  ON v.sm_order_key = r.sm_order_key
  AND v.sm_store_id = r.sm_store_id
GROUP BY 1, 2, 3
HAVING orders_containing_product >= 25
ORDER BY return_order_rate DESC, returned_orders_containing_product DESC;