> ## Documentation Index
> Fetch the complete documentation index at: https://docs.sourcemedium.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# BigQuery Essentials for SourceMedium

> Practical SQL queries for analyzing your SourceMedium data in BigQuery

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

<Info>
  **Recommended starting point**: Most customer analytics should start in the `sm_transformed_v2` dataset within your Google Cloud project.
</Info>

<Note>
  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.
</Note>

<Tip>
  **New to BigQuery?** Start with [Google's How to Get Started with BigQuery video](https://www.youtube.com/watch?v=BH_7_zVk5oM\&t=2s) for a quick introduction.
</Tip>

***

## 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](/data-activation/data-tables/index).
* 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`](/data-activation/data-tables/sm_transformed_v2/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.

<Info>
  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.
</Info>

## Essential Queries

### 1. Daily Revenue Summary

```sql theme={null}
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
```

<Tip>
  **Always filter with `is_order_sm_valid = true`** to exclude test orders, cancelled orders, and other invalid transactions.
</Tip>

### 2. Channel Performance

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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](/data-activation/data-tables/sm_transformed_v2/index).

***

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

<AccordionGroup>
  <Accordion title="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](https://cloud.google.com/bigquery/docs/access-control).
  </Accordion>

  <Accordion title="Granting access to team members">
    Use [Google Groups](/onboarding/analytics-tools/google-groups-access-control) 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](https://cloud.google.com/bigquery/docs/control-access-to-resources-iam) for detailed instructions.
  </Accordion>

  <Accordion title="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](https://cloud.google.com/iam/docs/service-account-overview) for setup instructions.
  </Accordion>
</AccordionGroup>

***

## Computing Power & Workload

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

<Info>
  **SourceMedium Managed Data Warehouse customers** receive a managed compute allowance for normal analytical usage.
</Info>

For more details on managing compute resources, see the [Workload Management Documentation](https://cloud.google.com/bigquery/docs/reservations-intro).

***

## BigQuery Tips

<AccordionGroup>
  <Accordion title="Use Preview to avoid costs">
    Click **Preview** on any table to see sample data without running a query (free).
  </Accordion>

  <Accordion title="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.
  </Accordion>

  <Accordion title="Use LIMIT during development">
    Add `LIMIT 100` while building queries to reduce costs and speed up iteration.
  </Accordion>

  <Accordion title="Save frequently-used queries">
    Use **Save Query** to store queries you run often. Organize with folders for easy access.
  </Accordion>

  <Accordion title="Handle complex data types">
    BigQuery supports arrays and structs (nested data). Use `UNNEST()` to flatten arrays for analysis. See [Google BigQuery Data Types](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types) for details.
  </Accordion>
</AccordionGroup>

***

## Getting Help

* **Query errors**: Check column names against the [table documentation](/data-activation/data-tables/sm_transformed_v2/index)
* **Permission issues**: See [Google Groups Access Control](/onboarding/analytics-tools/google-groups-access-control)
* **Data questions**: Contact your SourceMedium support team

***

## Additional Resources

**Google Documentation:**

* [GoogleSQL Reference](https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql)
* [Query Syntax](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax)
* [Creating and Using Tables](https://cloud.google.com/bigquery/docs/tables)
* [Introduction to Views](https://cloud.google.com/bigquery/docs/views-intro)
* [Loading Data](https://cloud.google.com/bigquery/docs/loading-data)
* [BigQuery Pricing](https://cloud.google.com/bigquery/pricing)

**SourceMedium Resources:**

* [SQL Query Library](/data-activation/template-resources/sql-query-library) — Copy-paste SQL templates for common analyses
* [Data Tables Reference](/data-activation/data-tables/sm_transformed_v2/index)
* [Looker Studio Guide](/onboarding/analytics-tools/looker-studio-guide)
* [Google Groups Access Control](/onboarding/analytics-tools/google-groups-access-control)