BigQuery Essentials for SourceMedium

Overview

BigQuery is Google’s fully-managed, serverless data warehouse where your SourceMedium data lives. It excels at large-scale data analysis, handling complex data types, and provides granular access control through IAM integration. 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 tenants may also have access to raw source datasets or platform-native exports in BigQuery. Those datasets are tenant-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 tenant and integration.

If a table exists in BigQuery but is not documented in the SourceMedium table reference, treat it as source-specific or tenant-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 integrates with Identity and Access Management (IAM) to provide granular control over who has access to your data. You can assign specific roles to users and control their permissions on a project-wide or dataset-wide level.

SourceMedium Managed Data Warehouse Admin Permissions

The SM Managed WH - Admin user role is intended for teams that need to manage warehouse access, run queries, and administer BigQuery resources in the managed project.For permission details, review the role in Google Cloud IAM or see IAM Permissions Documentation.

Granting access to team members

Use Google Groups to manage BigQuery access efficiently:

Create a Google Group for your analytics team
Grant the group BigQuery Data Viewer or Editor role
Add team members to the group as needed

See Control Access to Resources with IAM for detailed instructions.

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 Google Groups Access Control
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
Google Groups Access Control

Home

Workspace guides

Product Overview

Access & Permissions

Analytics Tools

Resources

BigQuery Essentials for SourceMedium

Overview

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

​Overview

​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

Overview

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