In this video tutorial, our very own Mark and Jess discuss common obstacles that B2B marketers face in the digital era and give proven strategies for tackling these challenges head on.
By default, Google Data Studio only allows you to blend up to five data sources at a time. While this is sufficient for smaller reporting ventures, it just doesn’t make the cut when you’re looking for aggregations across more than five Google Analytics accounts. This is a common issue for companies managing several brands or locations when it’s time to report on how the company is doing overall.
So how can you easily aggregate data from more than five Google Analytics accounts? Enter the Google Analytics add-on for Google Sheets! This is a free tool that pulls data straight from the Google Analytics API. You can pull in the data you need from each analytics account, set up calculated fields to aggregate it, and then push it into Data Studio. You can even set a schedule for the data to update so you really shouldn’t have to revisit your Google Sheet too often.
Here are the steps we’ll follow to accomplish this:
1. Install the Google Analytics add-on for Google Sheets
2. Create reports for each Google Analytics view that defines the metrics and dimensions that should pull in
3. Run the reports to pull in the data into Google Sheets
4. Create a separate sheet for aggregations, formatted to import into Data Studio properly
5. Connect the spreadsheet to Data Studio to start creating visuals
6. Automate reporting!
Correction: At 6:32 in the video, an average is taken of goal conversion rate by adding all of the goal conversion rates together and dividing by 6 (the total number of goal conversion rates in this example). The correct way that this should be done is by taking a weighted average – this can be accomplished by pulling in the sessions column and goal completions column and creating a calculated field for goal conversion rate that looks something like this:
=(Site1Sessions * Site1GoalConvRate) + (Site2Sessions * Site2GoalConvRate) … (Site6Sessions * Site6GoalConvRate) / Total Sessions
Core Reporting API Reference Guide: https://developers.google.com/analytics/devguides/reporting/core/v3/reference
Formatting a Google Sheet for Data Studio: https://support.google.com/datastudio/answer/6295223?hl=en