How to create custom schemas for your marketing data warehouse in 3 simple steps

While you’ve always been able to use common data extraction schemas in Supermetrics for BigQuery, you might occasionally also want to pull metrics that aren’t covered by a standard schema.

To tackle this need, we’ve launched a new feature: the schema builder.

The schema builder offers an easy way for you to create custom data schemas for your data transfers. After reading this article, you’ll be able to build your own schemas and connect them to your data transfers.

But before we get started, let’s quickly make sure we’re all on the same page about data schemas.

What is a data warehouse schema?

A data warehouse schema guides data connectors (like Supermetrics) to extract specific data fields and tables from the selected data source (like Facebook, Instagram, or Google Analytics). Schemas contain the exact metrics and dimensions you want to analyze.

Before the schema builder, Supermetrics for BigQuery used standardized schemas only. The problem with standardized schemas, however, is just that: they’re standardized. While they do support the most common analytics use cases, that’s the only thing they do. And even though our standardized schemas are developed based on extensive research and customer feedback, they weren’t able to give our customers total freedom in their data extractions.

Cue: the schema builder.

Why would you need a custom schema?

When you’re creating a custom report that answers very specific questions, you might need a certain metric that isn’t available in a standard schema. For example, you might need some information about audience demographics in your dashboards. 

This is where the schema builder comes in. With it, you can include specific metrics and dimensions in your transfers. This way, you can take full control over what data is being transferred and stored.

All clear? Great. All you have to do now is to follow this three-step process to create a custom schema in BigQuery.

How to build a custom schema in 3 steps

To get started with the schema builder, log in to the query manager. 

The query manager is a universal user interface for managing all your Supermetrics data transfers, including those in Supermetrics for BigQuery. With the query manager, you can easily build your own schemas.

Step 1: Set up your schema

To start building a schema, the first thing you’ll want to do is select a data source.

After logging in to the query manager, you can simply select the connector you are building a schema for. If you haven’t used the selected connector before in the query manager, you might have to authenticate your connection first. 

After selecting the data source you want, click on the “Schema” tab and select “New schema”. Next, type in the name of your new schema and confirm.

Step 2: Set up the metrics

You’re now ready to start selecting the metrics and dimensions you want in your new schema. 

Make your way back to the “Query” tab and configure your query. You can freely select your metrics, dimensions, and timeframes. These can be selected from the query manager menu.

After setting up the query, test it by clicking on the “Run” button. If you’re happy with the result, click on the “Save as” button to add your query to a schema. Give a name to your query and select the schema you created.

Create as many queries for your schema as you need. Then pat yourself on the back, because you’ve just successfully configured your first custom schema! 

You’re now ready to add the schema to your data transfers in the BigQuery console.

Step 3: Add your custom schema to your data transfer

Scroll over to your “Transfers” tab in the BigQuery console. You can either choose to create a new transfer (if you need a refresher, check out how to create a new transfer to BigQuery) or reconfigure an existing transfer to use your new custom schema. 

When you have your transfer selected, click on the “Configure transfer” button. You will now see a dropdown menu with all schemas, including any custom schemas you’ve built. Pick the schema you just created, click “Ok” and save your changes. Your data is now transferred according to the new schema.

Need help in getting started?

With these three simple steps, creating a custom schema is quick and easy. You can now collect data in a way that fits the purpose of your reporting.

The schema builder is available as an add-on for all Supermetrics for BigQuery customers. To hear more about Supermetrics for BigQuery and building custom schemas, book a demo with our experts.