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

# Connect Metabase to SourceMedium

> Connect your own Metabase to your SourceMedium warehouse with a key file, then install the foundation dashboards from the Metabase template.

## TL;DR

* Metabase connects to BigQuery with a **service account key file**. An editor or admin downloads one from **Dashboards & Apps** in your SourceMedium workspace. No Google Cloud setup is needed.
* In Metabase, add a BigQuery database with that file and your two dataset names. Leave both project ID fields blank.
* Install the [foundation dashboards](https://docs.sourcemedium.com/docs/onboarding/analytics-tools/metabase-foundation-dashboard.md) from the [Metabase template](https://github.com/source-medium/sm-metabase-template) with a coding agent or one command. Their numbers use SourceMedium's published metric definitions.
* No agent or command line? [Build the three starter questions by hand](#build-the-starter-by-hand).

You own your Metabase: where it runs, who can sign in, and what each person can see. SourceMedium supplies the key, the values to enter, and the starter content.

Prefer a Metabase that SourceMedium hosts for you? See [Which Metabase should I use?](https://docs.sourcemedium.com/docs/onboarding/analytics-tools/choose-metabase.md)

Workspace editors and admins can create app credentials on Foundation and Pro once the warehouse is active. Pro's direct access through a member's own Google account is a separate access path.

> [!NOTE]
>
> The template supports Metabase 0.64 and 0.65, tested with v0.64.1 (which includes security fixes) and v0.65.0-beta. Its installer checks the version before it changes anything.

***

## 1. Run Metabase

Any edition and any host works.

* **Fastest:** start a [Metabase Cloud](https://www.metabase.com/cloud) trial.
* **Free:** run the open-source edition yourself, for example with [Docker](https://www.metabase.com/docs/latest/installation-and-operation/running-metabase-on-docker).

## 2. Download a key file

1. In your SourceMedium workspace, an editor or admin opens **Dashboards & Apps** and clicks **Connect Metabase**.
2. Use `Metabase` or a name you will recognize, then press **Create key** and confirm.
3. When the one-time panel appears, click **Download key file**. The two dataset names you need below are listed beside it.
4. Confirm the file has saved, then check **I saved the key file somewhere safe** before clicking **Done**. Closing the panel loses access to this key.

Keep the file private. You will upload it in the next step, then delete it from your downloads. Later, **Manage → Copy dataset and project names** on the Metabase row lists the same values again.

## 3. Add the database in Metabase

In Metabase, go to **Admin → Databases → Add a database** and enter:

| Field | Value |
| - | - |
| Database type | BigQuery |
| Display name | `SourceMedium` |
| Service account JSON file | **Select a file** and choose the key file you downloaded |
| Billing project ID | Leave blank. It defaults to the key's own project, shown as `BIGQUERY_JOB_PROJECT_ID` in **Manage → Copy dataset and project names**. |
| Data project ID | Leave blank, for the same reason |
| Datasets | **Only these...**, then your `SM_TRANSFORMED_DATASET_ID`: `sm_transformed_v2` on a dedicated warehouse, or `<tenant>_sm_transformed_v2` on a shared warehouse. Copy the exact value from Dashboards & Apps. |

For the template's installer and reusable metrics, also add your `SM_METADATA_DATASET_ID` to the dataset list, separated by a comma, so the metric catalog and `dim_data_dictionary` are visible. This is optional for the three manual starter questions alone.

To explore models from other delivered datasets, add their exact `dataset_name` values from `dim_data_dictionary` to this list too. Preserve any existing entries and use only datasets your key can read. The template's installer creates models from this dictionary inventory and reports documented tables that are unavailable in the connection; metric support does not limit which models it creates.

![Metabase BigQuery form with blank billing and data project IDs, a key file selector and Only these datasets selected](https://mintcdn.com/sourcemedium/wTEuAS4KrHIcrNBa/images/onboarding/metabase/bigquery-connection.png?fit=max\&auto=format\&n=wTEuAS4KrHIcrNBa\&q=85\&s=fe494e5d0c22f5e4023ac950b8bcf2fb)

BigQuery connection fields in Metabase v0.64.1. Both project IDs are blank; copy your own dataset name from Dashboards & Apps.

Then open **Show advanced options**, turn on **Choose when syncs and scans happen**, and set **Scanning for Filter Values** to **Only when adding a new filter widget**. See [Cost](#cost) for why.

Leave **Periodically refingerprint tables** off. Metabase can still sample newly connected tables or views during their initial sync. On BigQuery, a sampled-row limit does not guarantee a small scan, especially for views over unpartitioned data. Adding more datasets can therefore cost more than a routine schema refresh. See [Metabase's sync and fingerprinting explanation](https://www.metabase.com/docs/latest/databases/sync-scan#how-database-fingerprinting-works).

![Advanced sync settings with Scanning for Filter Values set to Only when adding a new filter widget](https://mintcdn.com/sourcemedium/wTEuAS4KrHIcrNBa/images/onboarding/metabase/filter-scan-schedule.png?fit=max\&auto=format\&n=wTEuAS4KrHIcrNBa\&q=85\&s=884b1ebb72d26c025f21fc947028cead)

Scan filter values when adding a widget instead of on a daily schedule.

Save. Metabase syncs the dataset's tables, which usually takes a few minutes.

## 4. Install the foundation dashboards

The [Metabase template](https://github.com/source-medium/sm-metabase-template) installs the foundation: Overview with period comparisons, Business review, Performance tables, All stores, Paid marketing, Creatives, Products, Retention, Geography, Orders and Data, plus models, catalog metrics and shared SQL snippets. It runs on your computer with a Metabase API key and changes nothing until you approve its plan.

* **With a coding agent:** in **Dashboards & Apps**, open **Manage** on your Metabase row and copy a prompt into Codex or Claude Code. It clones the template and follows its instructions.
* **With one command:** follow [Install by hand](https://github.com/source-medium/sm-metabase-template#install-by-hand) in the template's README.

See [Metabase foundation dashboards](https://docs.sourcemedium.com/docs/onboarding/analytics-tools/metabase-foundation-dashboard.md) for what each tab shows.

### Build the starter by hand

Without an agent or command line, build the executive summary from three SQL questions on `rpt_executive_summary_daily`, the same table behind the Looker Studio Executive Summary. For each one, choose **New → SQL query**, pick the `SourceMedium` database, and paste the SQL from the template file. Replace `DATASET` with your transformed dataset name. Configure the variables below, run the question and format its columns, then save it with the name and visualization shown here.

> [!WARNING]
>
> Keep the table as `` `DATASET.rpt_executive_summary_daily` ``, without the project and without an alias. With the field-filter mappings below, Metabase refers to the table by that name, so a project-qualified table fails with "Unrecognized name".

| Save as | SQL | Visualization |
| - | - | - |
| **Period comparison** | [`comparison.sql`](https://github.com/source-medium/sm-metabase-template/blob/main/foundation/sql/comparison.sql) | Table |
| **Net revenue by day** | [`trend.sql`](https://github.com/source-medium/sm-metabase-template/blob/main/foundation/sql/trend.sql) | Line chart: `date` on the x-axis, `net_revenue` and `compare_net_revenue` as the series |
| **Business summary** | [`business.sql`](https://github.com/source-medium/sm-metabase-template/blob/main/foundation/sql/business.sql) | Table |

The template's SQL files share their comparison periods: a line `-- @include periods` stands for [`include/periods.sql`](https://github.com/source-medium/sm-metabase-template/blob/main/foundation/sql/include/periods.sql). Replace that line with the file's contents before you paste the query.

### Set up the variables

When you paste a query, Metabase opens a sidebar for each `{{variable}}`. Set **Variable type** to **Field Filter** for these three:

| Variable | Field to map to | Filter widget type | Required |
| - | - | - | - |
| `date` | Rpt Executive Summary Daily → Date | All Options | **Always require a value**, with **Default filter widget value** set to Previous 30 days |
| `channel` | Rpt Executive Summary Daily → Sm Channel | String | No; set **Default filter widget value** to `online_dtc` |
| `store` | Rpt Executive Summary Daily → Sm Store ID | String | No |

For Channel, click **Enter a default value…**, select `online_dtc`, then **Add filter**. Leave **Always require a value** off so you can clear Channel to see all delivered channels.

The other variables are values the SQL reads. Set their **Variable type** to **Text**, turn on **Always require a value**, and give each its default:

| Variable | Used by | Default | Other values |
| - | - | - | - |
| `compare` | Period comparison, Net revenue by day | `previous` | `year` (same period last year), `weekday` (52 weeks earlier), `off` |
| `compare2` | Period comparison, Net revenue by day | `year` | `previous`, `weekday`, `off` |
| `trend_values` | Net revenue by day | `daily` | `cumulative` |
| `grain` | Business summary | `day` | `week`, `month` |

> [tab] Date default
>
> ![SQL field-filter sidebar showing All Options, Previous 30 days and Always require a value enabled for Date](https://mintcdn.com/sourcemedium/wTEuAS4KrHIcrNBa/images/onboarding/metabase/date-variable.png?fit=max\&auto=format\&n=wTEuAS4KrHIcrNBa\&q=85\&s=2386d29a61356872b49a8ce449981d73)
>
> Date is required and defaults to Previous 30 days.

> [tab] Channel default
>
> ![SQL field-filter sidebar showing online\_dtc as the default channel with Always require a value disabled](https://mintcdn.com/sourcemedium/wTEuAS4KrHIcrNBa/images/onboarding/metabase/channel-variable.png?fit=max\&auto=format\&n=wTEuAS4KrHIcrNBa\&q=85\&s=8dc3fa295b363df86a4e035374340762)
>
> Channel defaults to online\_dtc and can be cleared.

"Previous 30 days" covers the 30 complete days before today, in Metabase's report time zone (UTC when no report time zone is configured). Check **Admin → Settings → Localization → Report timezone** before comparing date ranges with another tool.

### Format the columns

Before saving each table question, click the gear beside **Visualization**, then **Columns**. Open the gear beside a column and set:

* Money columns (`net_revenue`, `period_net_revenue`, `net_revenue_per_summary_order`, `ad_spend`, `period_ad_spend`): **Number of decimal places** 2. Leave the style as a number. Order money and ad spend are not always in one currency, so a currency symbol can label a mixed amount.
* `summary_orders`, `period_summary_orders` and `sessions`: **Number of decimal places** 0.
* `mer` and `period_mer`: **Number of decimal places** 2. Keep them plain numbers, rather than percentages or currency.
* The `_change` columns: **Style** Percent.

Money columns and `mer` follow the display units SourceMedium publishes. The template's installer applies those automatically.

Click **Done** and save the question. If you already saved it, choose **Replace original question** when Metabase asks whether to replace or save as new.

### Assemble the dashboard

1. Create a dashboard named **SourceMedium executive summary** and add the three questions, top to bottom.
2. Click **Edit dashboard → Add a filter or parameter** to add a date filter, and text filters named Channel, Store, Compare with, Second comparison, Trend values and Period.
3. Wire each filter to the matching variable on every card that has it. Give the date filter the default **Previous 30 days**, Channel the default `online_dtc`, and the other filters the defaults above. Leave Store blank, or select one store when comparing numbers.
4. Paste the methodology note below into the dashboard description.

Every other foundation question has its SQL in the template's [`foundation/sql`](https://github.com/source-medium/sm-metabase-template/tree/main/foundation/sql) folder. Paid marketing questions use an `ad_platform` variable instead of `channel`, and retention uses an `as_of_month` date variable instead of `date`. Every variable and its default is in the template's [`src/filters.mjs`](https://github.com/source-medium/sm-metabase-template/blob/main/src/filters.mjs), and [Customize the foundation](https://github.com/source-medium/sm-metabase-template/blob/main/docs/customize.md) explains them.

## What the numbers mean

The starter uses SourceMedium's executive summary formulas over the selected channels, stores and dates. Its default Channel is **Online DTC** (`online_dtc`), matching the Looker Studio dashboards' [default channel filter](https://docs.sourcemedium.com/docs/data-activation/managed-bi-v1/core-dashboard-features.md). Compare tools with the same store, absolute date range, time zone and channel selection.

Order measures count SourceMedium's valid sales channels: `online_dtc`, `amazon`, `retail`, `wholesale` and `tiktok_shop`. Clear Channel to sum all five. Rows under any other channel value, such as `excluded`, `draft_orders`, `exchanged` or `*_via_shopify`, add nothing to revenue or order counts. Ad spend and sessions count every row. These totals are not guaranteed to match Shopify's Sales report, whose source exclusions and reporting settings can differ.

| Column | Definition | Note |
| - | - | - |
| Net revenue | `SUM(order_net_revenue)` on valid sales channels | Sales-report basis: refunds and exchanges count on the day they happen |
| Gross revenue | `SUM(order_gross_revenue)` on valid sales channels | |
| Summary orders | `SUM(order_count)` on valid sales channels | Placed-order count in this report, not valid-order count. It can include later cancelled or refunded orders; Amazon voided orders and configured exclusions are removed |
| Net revenue per summary order | Net revenue ÷ summary orders | Not the same as valid-order AOV |
| Ad spend | `SUM(ad_spend)` | All spend in the selection, including negative credits |
| MER | Net revenue ÷ ad spend | Blended, not attributed ROAS |
| Sessions | `SUM(website_sessions)` | Online DTC and Amazon only |

Ratios are computed from totals, never by averaging daily ratios. When the denominator total is zero, the ratio shows blank. A zero numerator with a nonzero denominator shows zero. An empty date range shows blanks, not zeros.

Methodology note for the dashboard description:

> Online DTC by default; clearing Channel sums the valid sales channels (online\_dtc, amazon, retail, wholesale, tiktok\_shop), and other channel rows add no revenue or orders. Revenue follows the sales report: refunds and exchanges count on the day they happen. Summary orders count placed orders under source-specific and configured exclusions; later cancelled or refunded orders can remain. MER is net revenue divided by all ad spend in the selection, not attributed ROAS. Sales events use store reporting dates; sessions and ad spend can follow their source reporting calendars. Some ad platforms report spend in the ad account's currency, so totals across stores or channels with different currencies are not meaningful. Days that only carry targets show zero.

## Who can see what

> [!WARNING]
>
> On a **dedicated warehouse**, the key can read every dataset in the warehouse project, including datasets you created or add later. On a **shared warehouse**, it can read the datasets granted to your organization and shared utility datasets. The Datasets setting only controls which tables Metabase lists; it does not restrict SQL access.

* Anyone you allow to write SQL against this database in Metabase can query all of that data.
* Store and channel filters are for navigation, not security.
* Metabase's paid row and column security does not apply to SQL questions.
* Set Metabase's own permissions to match who should see your data.

## Cost

Jobs run in the `BIGQUERY_JOB_PROJECT_ID` shown by **Copy dataset and project names**. Dedicated warehouses use the tenant project; shared warehouses use SourceMedium's shared query project. Jobs share that project's billing, reservations and applicable query limits. Dashboards & Apps does not set a separate per-app spending cap.

With on-demand billing, query cost depends on bytes processed. With capacity billing, queries consume the project's assigned slots and can compete with other workloads or increase autoscaling use. Byte counts alone do not estimate capacity charges, and [daily byte quotas apply only to on-demand queries](https://docs.cloud.google.com/bigquery/docs/custom-quotas).

* On the small demo warehouse, each uncached starter query billed 10 MiB (10,485,760 bytes), so three queries billed 30 MiB. Larger scans can bill more; cached queries can bill zero. Sync and filter-value jobs add to those query costs.
* Scheduled filter-value scans can add costs that grow with the fields and tables scanned. Setting **Scanning for Filter Values** to **Only when adding a new filter widget** avoids the daily rescan.

## Replace or revoke the key

* **Replace:** in **Dashboards & Apps**, open **Manage** on the Metabase row and click **Replace key**, download the new key file, and upload it in the Metabase database settings. The old key can no longer create access tokens. Tokens already issued from it may keep working until they expire; replacement reuses the app's identity.
* **Revoke:** **Revoke access** disables the app identity, removes its keys and grants, then deletes the identity, preventing new warehouse queries. Pausing or offboarding your organization also disables app identities. SourceMedium does not cancel a query that is already running.
* Removing someone from your SourceMedium workspace does not revoke the key or remove their Metabase permissions. Remove their access in Metabase separately. For routine rotation, replace the key. For a compromised key, **revoke the credential and create a new one** for the connection; revoking deletes the old identity.

Google distinguishes [key deletion](https://docs.cloud.google.com/iam/docs/keys-create-delete) from disabling the service account: deleting a key does not revoke tokens already issued from it, and [re-enabling an account](https://docs.cloud.google.com/iam/docs/reference/rest/v1/projects.serviceAccounts/disable) accepts its existing tokens again.

## Troubleshooting

* **Access denied right after issuing the key:** Google can take a minute or more to activate a new key. Wait, then sync again.
* **"Unrecognized name" on a starter question:** with this guide's mappings, use `` `DATASET.rpt_executive_summary_daily` `` exactly. For custom SQL with an alias, configure Metabase's [Table and field alias](https://www.metabase.com/docs/latest/questions/native-editor/field-filters) to match it.
* **"Not found: Dataset":** the dataset name is wrong. Copy it from the app's **Manage → Copy dataset and project names**.

## Related pages

> [card] [Other BI tools](https://docs.sourcemedium.com/docs/onboarding/analytics-tools/connect-to-other-bi-tools.md)
>
> Connect Tableau, Power BI, and other tools that read BigQuery.

> [card] [Executive summary table](https://docs.sourcemedium.com/docs/data-activation/data-tables/sm_transformed_v2/rpt_executive_summary_daily.md)
>
> Every column in `rpt_executive_summary_daily`.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.