top of page
Search

Using Supermetrics Hub and BigQuery For Your Reporting and Analytics

Writer's picture: Katie WojciechowskiKatie Wojciechowski

What are the advantages of using Supermetrics Hub and BigQuery for your reporting and analytics?


If you’re a data analyst who works with Google data from GA4, GSC, etc., most likely you’re using a platform like Looker Studio or Tableau to do your reporting and analysis. But, as you know, it’s not just a matter of looking at your (or your client’s) data: how is it organized? How long does it take to process and update? And, perhaps most importantly, how long and how securely is it preserved?


To resolve these issues, my team uses a combination of Supermetrics Hub and BigQuery to transfer, query, and store our clients’ data. There are an array of helpful platforms and combinations of tools that can do the same thing, but this is the process we’ve found works with this particular pairing.


How to Use Supermetrics Hub to Store Data in BigQuery


Here’s how I have set up our clients’ data storage.


  1. Set up a dataset in BigQuery: make sure there is a destination table created before you send your data anywhere.

  2. Connect a data source in Supermetrics Hub: Choose Google Analytics 4 (GA4), Google Search Console (GSC), or any other supported platform as your data source, and authenticate the connection as prompted.

  3. Create a table with the metrics and dimensions you want to store: Below are two examples we use often.

    1. GA4 Traffic by Source

      1. Dimensions: Date, Session default channel grouping, Session campaign name, Session source/medium, Landing page + query string

      2. Metrics: Sessions, Revenue, Transactions

    2. GA4 Events

      1. Dimensions: Date, Event Name, Event source/medium, Page URL

      2. Metrics: Event Count, Conversions (Key Events)

Part of creating the table is selecting the properties that it has access to - make sure to add the GA4 (or other platform) property that you’re setting up. A single table can be used across multiple properties/accounts, given that the same dimensions, metrics, and date range is being used for all. For dates, you can set it to pull just for Yesterday - this table is what will be used in every daily query. To pull beyond Yesterday for initial setup of historical data, you can set a Backfill to run - see below.

  1. Configure BigQuery storage

    1. From the Supermetrics Hub dashboard, add BigQuery as your destination.

    2. Authenticate your Google Cloud account to establish the connection.

    3. Select the project and dataset you created in Step 1. For Service account key, you’ll need a piece of JSON code (the same JSON code will be used every time you set up a storage destination, as long as you are working within the same BigQuery project).

  2. Set up a Supermetrics Hub Transfer for your data source: Your destination will be the storage destination you just configured. We typically set the refresh schedule to run daily with a refresh window of 30 days. Under Schema, you’ll choose the table you set up in Step 3. Select the property you want to pull data from - make sure it’s the correct property in GA4 for the account you’re working with. Save the transfer, and configure the backfill back to the earliest date you want data for.

  3. Once the transfer is running, make sure to check in BigQuery using the Preview tab on your table to make sure that all the correct fields are present and starting to get populated. It’s normal for the initial backfill transfer to take hours or even a whole day.


Benefits of Using Supermetrics Hub

  • BigQuery Pricing - While connecting GA4 (for example) directly to BigQuery may end up being extremely costly, choosing only the metrics, dimensions, and date range that you need, and are relevant to your reporting, will drastically reduce storage costs.

  • Scheduling and Data Freshness - Supermetrics Hub makes it easy to pull data as often/infrequently as you need to.

  • Historical Data Backfills - Supermetrics supports extensive data backfills, enabling you to retrieve historical data when starting a new pipeline or analyzing past trends. We also used this feature to save UA data for clients before Google sunsetted it in July 2024.

  • Multi-Source Data Integration - while GA4 was the example I used above, this method can be used to store data from GSC, ad platforms, or other sources into the same BigQuery dataset.

  • Ease of Setup and Maintenance - Supermetrics is very user-friendly, in my experience.


Benefits of Using BigQuery

  • Massive Data Handling and Processing - BigQuery is widely considered to be among the best-in-class and most efficient options for data warehousing. It operates as a serverless, fully managed cloud data warehouse that can process massive amounts of data with high performance. Other platforms might struggle with extremely large datasets or require manual scaling. For querying, BigQuery uses a distributed processing engine optimized for analytical workloads, making it faster than querying directly from GA4 or many alternative platforms.

  • Custom Queries - With SQL support, BigQuery enables complex queries, joins, transformations, and custom calculations that aren't possible directly in GA4 or many BI tools.

  • Unlimited Storage and Historical Data - Unlike GA4’s limited data retention policies (e.g., 14 months for event data) or GSC’s 16-month window, BigQuery stores historical data indefinitely, enabling long-term trend analysis.

  • User-friendly - I’ve found BigQuery to be easy to use, with a simple interface.


For those who want to use BigQuery but are afraid of the costs, Supermetrics Hub might be a great choice. It works well for us as an agency because we can use one platform to route data from many sources into BigQuery where each property is stored in its own dataset. This combination of platforms can be a cost-effective, user-friendly, high-capacity solution for storing, manipulating, and accessing data that is crucial to you or your clients’ business.

6 views0 comments

Recent Posts

See All

Comments


©2020 by Katie Wojciechowski. Proudly created with Wix.com

bottom of page