Hello there !
When you work with Google Analytics and want to dig into your live data to extract precious insights, you can sometimes feel limited by the tools offered by their user interface. You may want to calculate specific conversions, regressions or cross those stats with offline data. Sometimes you just don’t have the same freedom you would have on a spreadsheet. But this is over !
Create live and custom analytics reports in Google Spreadsheet
With Google Analytics Spreadsheet Add-on, you can run Analytics queries directly in a Spreadsheet. And once you have the results in your spreadsheet, you are completely free to dig into them the exact way you would want to. Here are a few useful key points and advantages of using this add-on :
- Run multiple queries and get all the results in the same spreadsheet
- Run queries on different views
- Schedule your reports to update the data
- Create automated reports working with multiple and different queries
- Automatically draw up to date data visualizations (dashboards)
- Easily share your reports using Google Drive sharing system
Basically, what you get is a spreadsheet as user interface for Google Analytics. What I like the most about that is that I can have an updated report to play around with and draft some researches. I highly encourage you to try this approach. Since you can build easy reports as well as complicated ones, there will be a solution for you ! Let’s see how to get the add-on.
Download Google Analytics Spreadsheet Add-on
The name speaks for itself, this is a Google Spreadsheet add-on. Follow these 3 easy steps to install it:
- 1. Go on Google Drive and open a new Spreadsheet
- 2. Open the Add-ons menu and click on Get Add-ons
- 3. Search for Google Analytics and install the add-on
Plugin to install from the Add-ons library
Now that the add-on is installed, you want to create a report to run Google Analytics queries.
Create and run Google Analytics queries
Open the Add-ons menu and select Google Analytics > Create a new report.
A new window opens on the right side of the spreadsheet. It’s a form asking for details about your Google Analytics query.
Widget to create a new report
I advise you to pick a short name for your query because you’ll reference it from another spreadsheet later. Concerning the property and the view, select the ones you want to base your analysis on. Finally, enter the metrics and dimensions that you want to work with. If you’re searching for custom metrics or dimensions, leave the fields blank, you’ll have the opportunity to specify them during the next step.
After validating, a sheet containing the details for the query is created.
Example of queries sheet content (with 2 queries)
The fields are pretty explicit and it should be an immediate reminder if you use to work with the GA Query Explorer. You can use max-results and start-index to paginate your results if they don’t fit in the 10.000 entries limit.
Note that you can create multiple reports on the same Spreadsheet by entering the different queries on the next columns (or using the same “Create a new report” method). You can customize your requests by changing the values of each field and following the Core Reporting API.
Once your queries are ready, open the Add-ons menu and select Google Analytics > Run reports. After a few seconds, a new sheet (or multiple sheets if you asked more than one query) named after your query and containing the results will be created.
Example of results sheet content
Manipulate the results
You can manipulate the results directly in the results sheet, but if you do that, you’ll loose all your calculations during the results update because the results sheets are completely recreated every time you run the reports.
The solution is close though. You just need to create a new calculation sheet and reference the results sheets to access the data. This way, as the results update, our calculations will be automatically applied to our updated results. In that calculation sheet, you can gather the results from all the results sheet by referencing them then base your analysis from your gathered columns.
Example of calculations sheet
Feel free to use the multiple and different charts and visualizations of Google Spreadsheet to visualize your data. It’s important to explore all the opportunities that Google Spreadsheet gives you to make sure that you pick the perfect fit to your needs. The best thing: updating your reports will automatically update the charts.
If you’re playing with multiple results sheets, you can do lookups (I use and abuse of the Vlookup function) to gather the results by dimension (or any key).
Schedule the report update
A wonderful thing is the ability to schedule your reports. You can choose to run your reports every hour, day, week or month. Like that, you can create charts or heat maps that will be up to date every time you take a look at them. This is basically a way to create live dashboards and reports.
Popup to schedule reports
You now have a custom and up to date report that you can easily modify and play around with, using all the calculation power of a spreadsheet.
Share a same calculation for different queries
One last cool thing is that you can share your calculation and apply it instantly to different dimensions. Let’s take an example.
Let’s say you’re searching to analyse a weekly evolution :
- Difference = ( This week pageviews – Last week pageviews )
- Progress (%) = ( Difference / Last week pageviews )
This is a very classic pattern, you may want to use it to calculate the weekly evolution of your visits (or sales or goals) by country (or OS or language). Imagine all the possibilities and combinations you can create. Guess what ? It will always be the same calculation.
That said, you can create a “Weekly evolution calculation” sheet that will use the results of your query, whatever it is, by referencing the results sheet. Changing the query (dimensions and metrics) will change the results set but not its format. This means that your calculation will still be applied.
With one sheet and a few formulas, you have access to a complete set of evolution analysis by switching from a combination of dimensions and metrics to another (ex: sessions/country, pageviews/os, transactions/currency, pageviews/currency …). You can push that further with other common or custom calculations like conversion rates, abandonment or any other key indicator.
And this is how you dig into your Google Analytics data, unlocking all the Spreadsheet features for your analysis, live.