Send Ad Data to BigQuery with Supermetrics – Easy Setup
If you want to streamline your marketing analytics by sending your advertising data directly into BigQuery, you’re in the right place. This guide will walk you through the process of connecting Supermetrics with BigQuery from scratch. I’ll cover everything from setting up your BigQuery dataset to creating the necessary service account and finally linking it all within Supermetrics. By the end, you’ll have a solid foundation to start automating your marketing data transfers and unlock powerful query capabilities for your ad campaigns.
For a full visual walk-through, you can watch the step-by-step video tutorial here:
Send Your Ad Data to BigQuery with Supermetrics — Fast & Easy Setup
Step 1: Prepare Your BigQuery Environment
Before diving into Supermetrics, make sure your Google Cloud environment is ready. This means you should already have a Google Cloud account, BigQuery enabled, and a project created where you want to store your marketing data. If you haven’t done this yet, I recommend setting up your BigQuery project first.
In your BigQuery project, you will create a new dataset. This dataset acts as a container where all your imported marketing data will reside.
Here’s how to create your dataset:
- Go to the BigQuery console in Google Cloud.
- Locate your project in the left-hand navigation pane.
- Click the three dots next to your project name and select Create dataset.
- Name your dataset something descriptive, like supermetrics.
- Choose the data location (multi-region or region). For example, I’m based in Europe, so I selected a European multi-region location.
- Leave other options like table expiration disabled for now.
- Click Create dataset to finalize.
Your dataset is now ready to receive data from Supermetrics.
Step 2: Set Up Supermetrics Data Storage Destination
With your BigQuery dataset created, it’s time to connect it to Supermetrics. First, log into your Supermetrics account and navigate to the Data Storage section. This option allows you to configure where your marketing data will be sent.
Keep in mind that data storage is not included in the cheapest Supermetrics plans, so make sure your subscription supports this feature.
Follow these steps:
- Click on Storage Destination and then select Connect.
- Choose BigQuery as your storage destination.
- Give your connection a clear, descriptive name like query_reports to keep things organized.
- Use the assistant that Supermetrics provides to guide you through the setup process. It’s very helpful, especially if you’re doing this for the first time.
After naming your connection, you’ll need to provide two key pieces of information: the dataset name you just created in BigQuery and a service account JSON key file. This setup allows Supermetrics to securely connect and write data to your BigQuery dataset.
Step 3: Enable BigQuery API and Create a Service Account
To allow Supermetrics to access your BigQuery dataset programmatically, you need to enable the BigQuery API and create a service account with the right permissions.
- Open the Google Cloud Console and navigate to the APIs & Services dashboard.
- Search for BigQuery API and enable it if it’s not already enabled.
- Next, go to the IAM & Admin > Service Accounts section.
- Create a new service account. Name it something like supermetrics to identify its purpose.
- Add a description if you want, though it’s optional.
- Assign the role BigQuery Admin to this service account. This role grants full access to BigQuery resources your project contains, which Supermetrics needs to manage your data.
- Skip any optional user permissions for now and finalize the service account creation.
After creating the service account, you must generate a key that Supermetrics will use for authentication.
- Click on your newly created service account.
- Go to the Keys tab.
- Select Add Key > Create new key.
- Choose the JSON key type and download the key file to your computer.
This JSON file is crucial for the next step, so keep it handy.
Step 4: Connect Supermetrics to BigQuery Using the Service Account Key
Now that you have your dataset name and service account JSON key, return to Supermetrics to complete the connection.
Here’s what to do:
- Paste the dataset name exactly as it appears in BigQuery into the corresponding field in Supermetrics.
- Open your downloaded JSON key file using a plain text editor (avoid using rich text editors like Visual Studio Code for this).
- Copy the entire content of the JSON key file.
- Paste the content into the service account key JSON field in Supermetrics.
- Hit the Connect button.
If the connection is successful, Supermetrics will confirm it. If you encounter any errors, double-check that the dataset name matches exactly and that the JSON key is valid.
Step 5: Save Your Connection and Start Creating Data Transfers
Once connected, save your configuration in Supermetrics. You’re now ready to create data transfers that will automatically pull your advertising data into BigQuery.
To create a data transfer:
- Go to the Transfers section in Supermetrics.
- Click Create a transfer.
- Choose the advertising data source you want to pull from (e.g., Facebook Ads, Google Ads).
- Configure your transfer settings such as frequency, metrics, and dimensions.
- Start the transfer and watch your data flow into BigQuery.
Automating these data transfers lets you focus more on analyzing your marketing performance rather than manually exporting and importing data.
Final Thoughts and Next Steps
Connecting Supermetrics to BigQuery might seem intimidating at first, but following these steps makes the process straightforward and manageable. Once your data is flowing into BigQuery, you unlock immense potential to analyze your advertising campaigns using SQL and build powerful custom dashboards.
Stay tuned for my next tutorial, where I will show you how to configure actual data transfers in detail and make the most out of your marketing data in BigQuery.