Build a Metabase foundation dashboard

node sourcemedium-metabase/sourcemedium-metabase.mjs \
  --url METABASE_URL --database-id DATABASE_ID \
  --dataset TRANSFORMED_DATASET --metadata-dataset METADATA_DATASET \
  --collection-id COLLECTION_ID --api-key-file TOKEN_FILE \
  --plan foundation-plan.json

WITH totals AS (
  SELECT
    SUM(order_gross_revenue) AS gross,
    SUM(order_discounts) AS discounts,
    SUM(order_refunds) AS refunds,
    SUM(order_net_revenue) AS net
  FROM `DATASET.rpt_executive_summary_daily`
  WHERE {{date}} [[AND {{channel}}]] [[AND {{store}}]]
)
SELECT item.measure, item.amount
FROM totals,
UNNEST([
  STRUCT(1 AS position, 'Gross revenue' AS measure, gross AS amount),
  STRUCT(2, 'Discounts', discounts),
  STRUCT(3, 'Refunds', refunds),
  STRUCT(4, 'Net revenue', net),
  STRUCT(5, 'Unreconciled difference', net - gross - discounts - refunds)
]) AS item
ORDER BY item.position

WITH totals AS (
  SELECT
    SUM(new_customer_order_net_revenue) AS new_revenue,
    SUM(new_customer_order_count) AS new_orders,
    SUM(repeat_customer_order_net_revenue) AS repeat_revenue,
    SUM(repeat_customer_order_count) AS repeat_orders
  FROM `DATASET.rpt_executive_summary_daily`
  WHERE {{date}} [[AND {{channel}}]] [[AND {{store}}]]
)
SELECT
  item.purchase_type,
  item.net_revenue,
  item.summary_orders,
  SAFE_DIVIDE(item.net_revenue, item.summary_orders) AS net_revenue_per_order
FROM totals,
UNNEST([
  STRUCT(1 AS position, 'New-customer orders' AS purchase_type,
         new_revenue AS net_revenue, new_orders AS summary_orders),
  STRUCT(2, 'Repeat-customer orders', repeat_revenue, repeat_orders)
]) AS item
ORDER BY item.position

SELECT
  DATE_TRUNC(date, WEEK(MONDAY)) AS week_start,
  MIN(date) AS first_selected_date,
  MAX(date) AS last_selected_date,
  COUNT(DISTINCT date) AS days_with_rows,
  SUM(order_net_revenue) AS net_revenue,
  SUM(order_count) AS summary_orders,
  SUM(ad_spend) AS ad_spend,
  SAFE_DIVIDE(SUM(order_net_revenue), SUM(order_count)) AS net_revenue_per_summary_order,
  SAFE_DIVIDE(SUM(order_net_revenue), SUM(ad_spend)) AS mer
FROM `DATASET.rpt_executive_summary_daily`
WHERE {{date}} [[AND {{channel}}]] [[AND {{store}}]]
GROUP BY week_start
ORDER BY week_start

SELECT
  source_system AS ad_platform,
  SUM(ad_spend) AS ad_spend,
  SUM(ad_impressions) AS impressions,
  SUM(ad_clicks) AS clicks,
  SUM(ad_platform_reported_conversions) AS platform_conversions,
  SUM(ad_platform_reported_revenue) AS platform_revenue,
  SAFE_DIVIDE(SUM(ad_platform_reported_revenue), SUM(ad_spend)) AS platform_roas,
  SAFE_DIVIDE(SUM(ad_spend), SUM(ad_platform_reported_conversions)) AS cost_per_platform_conversion,
  SAFE_DIVIDE(SUM(ad_clicks), SUM(ad_impressions)) AS click_through_rate
FROM `DATASET.rpt_ad_performance_daily`
WHERE {{date}} [[AND {{ad_platform}}]] [[AND {{store}}]]
GROUP BY source_system
ORDER BY ad_spend DESC, ad_platform

SELECT
  source_system AS ad_platform,
  ad_account_id,
  ad_campaign_id,
  MAX(ad_campaign_name) AS campaign_name,
  SUM(ad_spend) AS ad_spend,
  SUM(ad_platform_reported_conversions) AS platform_conversions,
  SUM(ad_platform_reported_revenue) AS platform_revenue,
  SAFE_DIVIDE(SUM(ad_platform_reported_revenue), SUM(ad_spend)) AS platform_roas,
  SAFE_DIVIDE(SUM(ad_spend), SUM(ad_platform_reported_conversions)) AS cost_per_platform_conversion
FROM `DATASET.rpt_ad_performance_daily`
WHERE {{date}} [[AND {{ad_platform}}]] [[AND {{store}}]]
GROUP BY source_system, ad_account_id, ad_campaign_id
ORDER BY ad_spend DESC, ad_platform, ad_account_id, ad_campaign_id

SELECT
  source_system,
  product_id,
  MAX(product_title) AS product_title,
  SUM(order_line_net_revenue) AS net_revenue,
  SUM(order_line_quantity) AS ordered_units,
  -SUM(order_line_refund_quantity) AS refunded_units,
  -SUM(order_line_refunds) AS refund_amount,
  SUM(order_line_product_gross_profit) AS product_gross_profit,
  COUNTIF(order_line_id IS NOT NULL AND order_line_product_cost IS NULL) AS missing_cost_lines,
  COUNTIF(order_line_id IS NOT NULL AND order_line_product_cost = 0) AS zero_cost_lines
FROM `DATASET.obt_order_lines`
WHERE is_order_sm_valid = TRUE
  AND {{date}} [[AND {{channel}}]] [[AND {{store}}]]
GROUP BY source_system, product_id
ORDER BY net_revenue DESC, source_system, product_id

SELECT
  sm_store_id,
  sm_channel,
  cohort_month,
  CONCAT(sm_store_id, ' / ', COALESCE(sm_channel, '(none)'), ' / ',
         CAST(cohort_month AS STRING)) AS cohort,
  months_since_first_order AS month_age,
  cohort_size,
  cumulative_second_order_count AS second_purchase_customers,
  SAFE_DIVIDE(cumulative_second_order_count, cohort_size) AS second_purchase_rate,
  SAFE_DIVIDE(cumulative_order_net_revenue, cohort_size) AS lifetime_revenue_per_customer,
  SAFE_DIVIDE(cumulative_order_gross_profit, cohort_size) AS gross_profit_lifetime_value
FROM `DATASET.rpt_cohort_ltv_by_first_valid_purchase_attribute_no_product_filters`
WHERE acquisition_order_filter_dimension = 'no_filters'
  AND sm_order_line_type = 'all_orders'
  AND cohort_month BETWEEN DATE_SUB(DATE_TRUNC({{as_of_month}}, MONTH), INTERVAL 11 MONTH)
                       AND DATE_TRUNC({{as_of_month}}, MONTH)
  AND months_since_first_order BETWEEN 0 AND 11
  AND DATE_ADD(cohort_month, INTERVAL months_since_first_order MONTH)
      <= DATE_TRUNC({{as_of_month}}, MONTH)
  [[AND {{channel}}]] [[AND {{store}}]]
ORDER BY sm_store_id, sm_channel, cohort_month, month_age