Skip to main content

​
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

TableDescriptionKey Columns
obt_ordersOne row per ordersm_order_key, order_net_revenue, sm_channel
obt_order_linesOne row per line itemsm_order_line_key, product_title, order_line_quantity
obt_customersOne row per customersm_customer_key, customer_email, source_system
rpt_ad_performance_dailyDaily ad metrics by addate, 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.

​
Permissions & Sharing

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.
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.
Use Google Groups to manage BigQuery access efficiently:
  1. Create a Google Group for your analytics team
  2. Grant the group BigQuery Data Viewer or Editor role
  3. Add team members to the group as needed
See Control Access to Resources with IAM for detailed instructions.
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

Click Preview on any table to see sample data without running a query (free).
BigQuery shows estimated data scanned in the top right corner of the query editor. This determines cost—always check before running large queries.
Add LIMIT 100 while building queries to reduce costs and speed up iteration.
Use Save Query to store queries you run often. Organize with folders for easy access.
BigQuery supports arrays and structs (nested data). Use UNNEST() to flatten arrays for analysis. See Google BigQuery Data Types for details.

​
Getting Help

​
Additional Resources

Google Documentation: SourceMedium Resources: