BigQuery Essentials for SourceMedium

BigQuery is Google’s fully managed, serverless data warehouse where your SourceMedium data lives. It handles large-scale analysis and complex data types, and controls access through IAM. This guide provides practical queries you can run immediately against the standard SourceMedium modeled datasets in your BigQuery project.

Recommended starting point: Most customer analytics should start in the sm_transformed_v2 dataset within your Google Cloud project.

Some workspaces also have access to raw source datasets or platform-native exports in BigQuery. Those datasets are organization-specific and are not the canonical SourceMedium modeled layer unless SourceMedium has documented them for your workspace.

New to BigQuery? Start with Google’s How to Get Started with BigQuery video for a quick introduction.

Finding tables and schemas

Use BigQuery Explorer to find your project, then start with the sm_transformed_v2 dataset for modeled SourceMedium analytics tables. For SourceMedium-modeled data:

Table docs live in the Data Tables Reference.
The most common order table is your_project.sm_transformed_v2.obt_orders.
The most common order-line table is your_project.sm_transformed_v2.obt_order_lines.
The most common ad performance table is your_project.sm_transformed_v2.rpt_ad_performance_daily.
Product and SKU attributes are documented in dim_product_variants.
Metric and schema metadata live in your_project.sm_metadata.
Experimental attribution tables can live in your_project.sm_experimental.
Some report views live in your_project.sm_views.

If you are looking for raw platform data, such as direct GA4 export tables or a connector-specific dataset, confirm the dataset name in your BigQuery project. Raw source dataset names and availability can vary by organization and integration.

If a table exists in BigQuery but is not documented in the SourceMedium table reference, treat it as source-specific or organization-specific until your SourceMedium team confirms how it should be used.

Essential Queries

1. Daily Revenue Summary

select
  date(order_processed_at_local_datetime) as order_date,
  count(distinct sm_order_key) as orders,
  count(distinct sm_customer_key) as customers,
  sum(order_gross_revenue) as gross_revenue,
  sum(order_total_discounts) as discounts,
  sum(order_net_revenue) as net_revenue
from `your_project.sm_transformed_v2.obt_orders`
where is_order_sm_valid = true
  and date(order_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
group by 1
order by 1 desc

Always filter with is_order_sm_valid = true to exclude test orders, cancelled orders, and other invalid transactions.

2. Channel Performance

select
  sm_channel,
  count(distinct sm_order_key) as orders,
  sum(order_net_revenue) as revenue,
  round(sum(order_net_revenue) / count(distinct sm_order_key), 2) as aov
from `your_project.sm_transformed_v2.obt_orders`
where is_order_sm_valid = true
  and date(order_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
group by 1
order by revenue desc

3. New vs Returning Customers

select
  date(order_processed_at_local_datetime) as order_date,
  order_sequence as customer_type,
  count(distinct sm_order_key) as orders,
  count(distinct sm_customer_key) as customers,
  sum(order_net_revenue) as revenue
from `your_project.sm_transformed_v2.obt_orders`
where is_order_sm_valid = true
  and date(order_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
group by 1, 2
order by 1 desc, 2

4. Top Products by Revenue

select
  product_title,
  sum(order_line_quantity) as units_sold,
  count(distinct sm_order_key) as orders,
  sum(order_line_net_revenue) as revenue
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)
group by 1
order by revenue desc
limit 20

5. Attribution by Source/Medium

select
  coalesce(sm_utm_source, '(direct)') as source,
  coalesce(sm_utm_medium, '(none)') as medium,
  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_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
group by 1, 2
order by revenue desc
limit 20

6. Marketing Spend vs Revenue by Channel

with daily_spend as (
  select
    date as report_date,
    sm_channel,
    sum(ad_spend) as spend,
    sum(ad_impressions) as impressions,
    sum(ad_clicks) as clicks
  from `your_project.sm_transformed_v2.rpt_ad_performance_daily`
  where date >= date_sub(current_date(), interval 30 day)
  group by 1, 2
),
daily_revenue as (
  select
    date(order_processed_at_local_datetime) as order_date,
    sm_channel,
    sum(order_net_revenue) as revenue,
    count(distinct sm_order_key) as orders
  from `your_project.sm_transformed_v2.obt_orders`
  where is_order_sm_valid = true
    and date(order_processed_at_local_datetime) >= date_sub(current_date(), interval 30 day)
  group by 1, 2
)
select
  coalesce(s.report_date, r.order_date) as date,
  coalesce(s.sm_channel, r.sm_channel) as channel,
  s.spend,
  r.revenue,
  r.orders,
  round(safe_divide(r.revenue, s.spend), 2) as roas
from daily_spend s
full outer join daily_revenue r
  on s.report_date = r.order_date
  and s.sm_channel = r.sm_channel
order by 1 desc, 2

Key Tables Reference

Table	Description	Key Columns
`obt_orders`	One row per order	`sm_order_key`, `order_net_revenue`, `sm_channel`
`obt_order_lines`	One row per line item	`sm_order_line_key`, `product_title`, `order_line_quantity`
`obt_customers`	One row per customer	`sm_customer_key`, `customer_email`, `source_system`
`rpt_ad_performance_daily`	Daily ad metrics by ad	`date`, `sm_channel`, `ad_spend`, `ad_impressions`

For complete table documentation, see the Data Tables Reference.

Important Filters

Valid Orders Only

Always include is_order_sm_valid = true to exclude:

Test orders
Cancelled orders
Fully refunded orders
Draft orders

Date Ranges

Use date_sub(current_date(), interval N day) for rolling windows:

Last 7 days: interval 7 day
Last 30 days: interval 30 day
Last 90 days: interval 90 day

Primary Date Field

Use order_processed_at_local_datetime as the primary date field for order analytics. This is the order processed timestamp converted to your reporting timezone.

BigQuery controls access through Identity and Access Management (IAM). In the warehouse SourceMedium manages for you, those permissions are set from your workspace rather than in Google Cloud, so you manage who can query by managing your team.

What your workspace role gets you

Warehouse access follows your SourceMedium workspace role.

Everyone with workspace access can query the sm_* datasets.
Nobody can write to the sm_* datasets. SourceMedium builds those.
Admins and Editors can also create datasets of their own alongside them, where your organization has its own BigQuery project. Where the warehouse is shared, every role has the same query-only access.

Granting access to team members

Invite them from Team in your SourceMedium workspace and pick a role. Access is granted when they accept, and it is removed when you remove them.You do not set this up in Google Cloud IAM, and a Google Group will not grant it. See Giving dashboard access to a teammate.

Service accounts for Looker Studio

For Looker Studio data sources, we recommend using a service account as the Data Credential:

Ensures consistent access regardless of who created the data source
Simplifies permission management
Avoids issues when employees leave

See Provisioning Service Accounts for setup instructions.

Computing Power & Workload

BigQuery uses a reservation model with “slots” representing computational capacity for running queries.

SourceMedium Managed Data Warehouse customers receive a managed compute allowance for normal analytical usage.

For more details on managing compute resources, see the Workload Management Documentation.

BigQuery Tips

Use Preview to avoid costs

Click Preview on any table to see sample data without running a query (free).

Check query cost before running

BigQuery shows estimated data scanned in the top right corner of the query editor. This determines cost. Always check before running large queries.

Use LIMIT during development

Add LIMIT 100 while building queries to reduce costs and speed up iteration.

Save frequently-used queries

Use Save Query to store queries you run often. Organize with folders for easy access.

Handle complex data types

BigQuery supports arrays and structs (nested data). Use UNNEST() to flatten arrays for analysis. See Google BigQuery Data Types for details.

Getting Help

Query errors: Check column names against the table documentation
Permission issues: See Giving dashboard access to a teammate
Data questions: Contact your SourceMedium support team

Additional Resources

Google Documentation:

SourceMedium Resources:

SQL Query Library: Copy-paste SQL templates for common analyses
Data Tables Reference
Looker Studio Guide
Giving dashboard access to a teammate

Get into BigQuery

Activate

SQL Query Library

Common analyses (SQL)

Tables

Schema Standards

BigQuery Essentials for SourceMedium

Finding tables and schemas

Essential Queries

1. Daily Revenue Summary

2. Channel Performance

3. New vs Returning Customers

4. Top Products by Revenue

5. Attribution by Source/Medium

6. Marketing Spend vs Revenue by Channel

Key Tables Reference

Important Filters

Valid Orders Only

Date Ranges

Primary Date Field

Computing Power & Workload

BigQuery Tips

Getting Help

Additional Resources

​Finding tables and schemas

​Essential Queries

​1. Daily Revenue Summary

​2. Channel Performance

​3. New vs Returning Customers

​4. Top Products by Revenue

​5. Attribution by Source/Medium

​6. Marketing Spend vs Revenue by Channel

​Key Tables Reference

​Important Filters

​Valid Orders Only

​Date Ranges

​Primary Date Field

​Permissions & Sharing

​Computing Power & Workload

​BigQuery Tips

​Getting Help

​Additional Resources

Finding tables and schemas

Essential Queries

1. Daily Revenue Summary

2. Channel Performance

3. New vs Returning Customers

4. Top Products by Revenue

5. Attribution by Source/Medium

6. Marketing Spend vs Revenue by Channel

Key Tables Reference

Important Filters

Valid Orders Only

Date Ranges

Primary Date Field

Permissions & Sharing

Computing Power & Workload

BigQuery Tips

Getting Help

Additional Resources