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