Share on facebook
Share on twitter
Share on linkedin
Share on email

Data Studio Tips and Tricks Series: Combining Google Analytics Properties

Creating a dashboard is sometimes like putting a puzzle together, specifically a data puzzle. Regardless of platform or data environment, I have found you will more often than not need to manipulate your data structures and reporting options to make everything fit perfectly into your dashboard. Perhaps your media agency is launching a campaign on a new platform, or there is a customer table not currently ETL'd into your data warehouse. Either way you're stuck without precisely what you need to build your dashboard, and of course, everyone wants to know how that new campaign is doing.

Data Studio is an incredibly powerful tool for creating a dashboard, but it does have limitations on which data sources it will natively work with and exactly how the data is visualized. The gap between what you have and what is being asked for will generally always exist and waiting on IT to engineer a solution could take months to a year to be implemented, which means we need to do a little work to put this puzzle together. To help with your creativity, I will share some of the tools I've used recently for Google Data Studio to overcome data limitations.

Help, my Google Analytics Data Lives in Different Accounts! 

Perhaps you just don't have a roll-up property for your sites, or maybe a new promotional site isn't integrated into your GA account. While Data Studio can connect to roughly 120 different types of data sources, I have found that a Google Sheet is sometimes a necessary buffer. It will help us in this example but could be used in many other use-cases. Perhaps you need to combine customer data sets to summarize data in a way that's not native to your existing reporting. In my example, I am combining multiple Google Analytics properties because data blending does not support this in Data Studio. To do this first pull in your data from GA using the sheets connector.

Image

My typical setup is to create a loading zone to capture all of my raw data. In case you're wondering, you can have up to 200 tabs in a single Google sheet. Depending on what you're summarizing that limit may be significant. You can see in the screenshot above I am grouping three distinct report sets across two different views, however scaling this solution out can leave you with a ton of tabs. I have reporting setup for a rolling 30-day period, which ensures it will always be loaded in exactly the same way when it is refreshed, which is essential. When combining data sets, think about how your data may change over time and build in plenty of room to grow, it's better to have it and not need it then to need it and have to create it 6 months from now.

That said you will then need to schedule your reports to run, below I am refreshing my data every hour.

Image

Lastly, create a summary tab to combine everything, there's not a lot of magic here just formulas that pull in and then summarize my data exactly how I need it. You could also consider using the ImportRange function in Google Sheets however I personally prefer to hard code cells to reference others.

Image

But Wait, What if You Don’t Need All that Data for Reporting that’s Being Loaded?

Sometimes you may need to be a little more specific about the data you're pulling in, rather than just grabbing everything that's loaded. Imagine you're picking a Device Category report with all of your metrics but really all you care about are the mobile metrics - pulling in everything would clearly be inefficient. This approach is also handy, as a back-end approach to applying segments to your reporting. To do this, I have successfully written a vlookup formula with multiple search conditions. You'll see below I make use of the Index and Match functions in google sheets.

In my example, I want to pull in data for a specific date and device from my ‘LoadingZone1' data tab

Image

First I reference all of the data in my loading zone, and then in my two match statements I reference the specific criteria I'm looking for. For instance, A1 could contain my current date while B1 is the ‘mobile' data I'm looking for. This formula can help you pare down the data you're loading into Data Studio into precisely what you need, making it far more responsive.

Ready to become a Data Studio wizard? Contact the Data Studio experts at InfoTrust today to discuss your data reporting needs.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn
Share on email
Email

Other Articles You Will Enjoy

InfoTrust Named One of Cincinnati’s Best Places to Work

InfoTrust Named One of Cincinnati’s Best Places to Work

For the seventh time in the analytics consulting and technology company’s history, InfoTrust has been named among Cincinnati’s Best Places to Work by the…

Fortune Names InfoTrust Among 50 Best Small Workplaces

Fortune Names InfoTrust Among 50 Best Small Workplaces

The entire analytics consulting and technology team at InfoTrust is thrilled to announce that we have been named one of the 50 Best Small…

Chicago Basket Brigade to Support Inpatient Families at Lurie Children’s Hospital

Chicago Basket Brigade to Support Inpatient Families at Lurie Children’s Hospital

We’re kicking off Basket Brigade 2019 in Chicago on Oct. 22! Once again, the InfoTrust Foundation is supporting Lurie Children’s Hospital and providing Thanksgiving…

Tips for Building an Effective Analytics Dashboard

Tips for Building an Effective Analytics Dashboard

Building an effective dashboard is tough work. I find most clients struggle when trying to build a dashboard internally due to a lack of…

InfoTrust and Tag Inspector Named Quanties Finalists

InfoTrust and Tag Inspector Named Quanties Finalists

The entire analytics consulting and technology team at InfoTrust is thrilled to be recognized as finalists in three categories at the upcoming DAA OneConference‘s…

Why Having a Data Layer is Critical During Black Friday Weekend

Why Having a Data Layer is Critical During Black Friday Weekend

Black Friday and Cyber Monday annually present marketers with ample eCommerce insights, as high traffic and large purchase volumes offer rich customer data for…

What Is Your Customer Data Doing Right Now?

What Is Your Customer Data Doing Right Now?

Do you or key stakeholders in your organization really know how or if your customer data is being collected, stored, and used? A discussion like…

InfoTrust Named to Inc. 5000 List for Fifth Straight Year

InfoTrust Named to Inc. 5000 List for Fifth Straight Year

InfoTrust ranks No. 1,855 on the 2019 Inc. 5000 list with a 3-year revenue growth of 218%. BLUE ASH, OH – Inc. Magazine yesterday…

Are You Getting the Most Out of Your Google Analytics 360 Partner?

Are You Getting the Most Out of Your Google Analytics 360 Partner?

At InfoTrust, we are extremely proud as an organization to be a Google Analytics Certified Partner and Google Analytics 360 Sales Partner. It’s a…

Leave Us A Review

Leave a review and let us know how we’re doing. Only actual clients, please.