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 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…

It’s a Great Time To Be a Woman in Technology and Analytics

It’s a Great Time To Be a Woman in Technology and Analytics

I still remember my first job as a systems engineer in a bank. I was the only woman in the entire floor of the…

InfoTrust Earns Great Place to Work Certification for Third Straight Year

InfoTrust Earns Great Place to Work Certification for Third Straight Year

The InfoTrust team is thrilled to announce that our organization has once again been certified as a “Great Place to Work.” This is the…

Inc. Magazine: Why Entrepreneurs are Uniquely Positioned to Transform Our World

Inc. Magazine: Why Entrepreneurs are Uniquely Positioned to Transform Our World

InfoTrust’s CEO and co-founder, Alex Yastrebenetsky had the honor to attend the International Entrepreneurial Summit at the United Nations. This blog article was originally…

See How ITP, ETP and Other Browser Cookie Changes Affect Your Analytics

See How ITP, ETP and Other Browser Cookie Changes Affect Your Analytics

You’ve likely seen the abbreviation for Intelligent Tracking Prevention, ITP, strewn across the headlines of tech and marketing blogs over the past year. The…

Former P&G Digital Transformation Executive Joins InfoTrust as Head of Data Governance

Former P&G Digital Transformation Executive Joins InfoTrust as Head of Data Governance

BLUE ASH, OH — The digital analytics consulting and technology team at InfoTrust is pleased to announce that long-time Procter & Gamble executive Kent…

Black Friday Is Coming — Is Your Digital Analytics Team Ready?

Black Friday Is Coming — Is Your Digital Analytics Team Ready?

Though summer’s not yet over, major retail organizations around the globe are already developing strategic marketing plans for that oh-so-special day of the fiscal…

It’s Time to Move: What the New Version of Google Tag Manager Means for Your Business

It’s Time to Move: What the New Version of Google Tag Manager Means for Your Business

Is your business ready for the changes coming to Google’s data measurement infrastructure? You probably rely on Google Analytics as a marvelous tool to…

Leave Us A Review

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