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

# Build the Metabase starter dashboard by hand

> Build SourceMedium's executive summary in your own Metabase from three SQL questions, without a coding agent or the command line.

This is the manual route for [your own Metabase](https://docs.sourcemedium.com/docs/onboarding/analytics-tools/connect-metabase.md). With a coding agent or the template's one command, the [foundation dashboards](https://docs.sourcemedium.com/docs/onboarding/analytics-tools/metabase-foundation-dashboard.md) install for you and you can skip this page.

Without an agent or command line, build the executive summary from three SQL questions on `rpt_executive_summary_daily`, the same table behind the Data 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 pieces through include lines: a line `-- @include periods` stands for [`include/periods.sql`](https://github.com/source-medium/sm-metabase-template/blob/main/foundation/sql/include/periods.sql), and any `-- @include name` stands for `include/name.sql` in the same [folder](https://github.com/source-medium/sm-metabase-template/tree/main/foundation/sql/include). Replace each such line with that file's contents, including any include lines inside it, 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` | `last_year` (same weekday last year for a single day, same period last year otherwise), `year` (same period last year), `weekday` (same weekdays last year), `off` |
| `compare2` | Period comparison, Net revenue by day | `last_year` | `previous`, `year`, `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.

The template's foundation dashboards default Date to the previous 28 days instead, so set the same range on both before you compare them. "Previous 30 days" covers the 30 complete days before today, in Metabase's report time zone. When no report time zone is configured, Metabase uses UTC. 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](https://docs.sourcemedium.com/docs/onboarding/analytics-tools/connect-metabase.md#what-the-numbers-mean) 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.


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