Modeling with SourceMedium

This guide covers how to extend SourceMedium’s data models by building your own tables, views, or queries in your Managed Data Warehouse.

Before you start

Understand our table types:

obt_* (One Big Tables) — Best starting point for most analyses. Pre-joined, business-ready.
fct_* / dim_* — Granular building blocks for custom joins.
rpt_* — Pre-aggregated for specific reporting use cases.

Start with obt_ tables when possible. They’re designed as a semantic layer and handle most common joins for you.

Recommended approach

1. Start with OBT tables

For most custom analyses, obt_orders and obt_customers provide everything you need:

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

2. Join fact + dimension tables for granular needs

When OBTs don’t have the grain you need:

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

3. Use report tables for pre-aggregated metrics

Don’t re-aggregate what’s already computed:

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

Common join keys

Table Type	Primary Key	Common Join Keys
Orders	`sm_order_key`	`sm_customer_key`, `sm_store_id`, `source_system`
Order Lines	`sm_order_line_key`	`sm_order_key`, `sm_product_variant_key`
Customers	`sm_customer_key`	`sm_store_id`, `source_system`
Products	`sm_product_variant_key`	`product_id`, `variant_id`

Always filter by sm_store_id (SourceMedium store identifier) when working across brands or stores to avoid cross-contamination.

Best practices

Filter to valid orders

Always include is_order_sm_valid = TRUE to exclude test orders, cancelled orders, and other invalid transactions:

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

Use consistent revenue definitions

Pick one and stick with it across your models:

order_net_revenue — After discounts and refunds; excludes shipping, taxes, and shipping taxes
order_total_revenue — Net revenue plus net shipping, net taxes, and net shipping taxes
order_gross_revenue — Before discounts and refunds

Partition and cluster your models

If creating persistent tables, optimize for query performance:

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

Document your models

Add descriptions so others (and future you) understand the logic:

-- Model: custom_weekly_cohort_summary
-- Purpose: Weekly cohort LTV for finance reporting
-- Owner: analytics@yourcompany.com
-- Last updated: 2026-01-15
SELECT 1 AS ok;

Common patterns

Customer-level aggregations

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

Channel attribution analysis

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

Product affinity (what’s bought together)

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

Next steps

Browse available tables: Data Tables Reference
Connect your BI tool: BI Tools Setup
Learn naming conventions: Column Naming Standards

Managed BI (Dashboards)

Managed Data Warehouse

Templates

Common Analyses

Modeling with SourceMedium

Before you start

Recommended approach

1. Start with OBT tables

2. Join fact + dimension tables for granular needs

3. Use report tables for pre-aggregated metrics

Common join keys

Best practices

Filter to valid orders

Use consistent revenue definitions

Partition and cluster your models

Document your models

Common patterns

Customer-level aggregations

Channel attribution analysis

Product affinity (what’s bought together)

Next steps

​Before you start

​Recommended approach

​1. Start with OBT tables

​2. Join fact + dimension tables for granular needs

​3. Use report tables for pre-aggregated metrics

​Common join keys

​Best practices

​Filter to valid orders

​Use consistent revenue definitions

​Partition and cluster your models

​Document your models

​Common patterns

​Customer-level aggregations

​Channel attribution analysis

​Product affinity (what’s bought together)

​Next steps

Before you start

Recommended approach

1. Start with OBT tables

2. Join fact + dimension tables for granular needs

3. Use report tables for pre-aggregated metrics

Common join keys

Best practices

Filter to valid orders

Use consistent revenue definitions

Partition and cluster your models

Document your models

Common patterns

Customer-level aggregations

Channel attribution analysis

Product affinity (what’s bought together)

Next steps