Today we will talk about automating your reports using Google Sheet’s Google Analytics add-on. This nifty tool was formerly known as Google Magic Sheets. The add-on is very simple to implement and will definitely save you time on reporting metrics from Google Analytics.
Getting The Google Analytics Add-On
- Start a brand new Google Sheet
- Select the Add-ons option
- Search for the Google Analytics Add-On
- Install it to your Google Sheets
Now your Google Sheets have access to your Google Analytics API and can begin pulling data in an organized way for your reporting purposes. Lets try it out.
Using the Google Analytics Add-On
Under your Add-ons, the Google Analytics add-on should be an option now, after you installed it. It should come with 3 dropdown options
- Create new report
- Run report
- Schedule report
Click “create report” to get started with using the add-on. You should see options pop up on the right-hand side. This is where you will begin to create your reporting configuration sheet, this sheet will be your hub for telling Google Sheets what exactly you want it to pull from Google Analytics.
To get started you will be told to select the appropriate
- A Metric from a Dimension you want to pull
Click the “Create Report” blue button on the bottom to start having your reporting configuration sheet.
Now you have set up Google Analytics automatic reporting on your Google Sheet. On the Report Configuration sheet, you can customize what each sheet will show you based on metrics and dimensions you want. Remember, this sheet can only show you what is possible to show you in Google Analytics, meaning, do not try to see two secondary dimensions on one report, you will have to create a new report using the next column over.
Helpful Points of References for Creating Reports
- Query Explorer – This is extremely useful because you can test out the configurations here first, and there will be recommendations on what exactly you will need to input in your configurations.
- Core Reporting API Guide – This will tell you specific functions you can put in to drill down the data to exactly what you need. For example, you can find how to include and exclude just like filters in GA.
It is also helpful to note that the add-on will tell you if you inputting the configuration incorrectly. So, after you have put in the data you want Google Sheets to fetch, go back to the add-ons option, go to Google Analytics option, now click the second option “Run Report.”
If you have configured your settings incorrectly you will see a reporting error. Fortunately, the report will also tell you exactly which metric was inputted incorrectly. Ex: “unknown metric:” “unknown dimension”
The coolest thing about this feature is that you can schedule your reports to run at a certain time. This means you can have your data the instant you need it, maybe at the beginning of the month you need the data for the last 30 days. You can set the add-on to run all the reports the night before the 1st of the month, every month. So, when you come in the morning, everything will be ready for you.
Advice: Creating a Master Reporting Sheet For Consolidation
You should create a master sheet for viewing purposes, or else you will end up going from sheet to sheet to see what you need. So, create a master view sheet and fill in the cells using formulas. For example, if you created a table that will tell you the organic traffic for the month from a specific campaign or the organic traffic for a specific month, just make that cell equal the sheet that is specifically pulling that data for you. The sheets are great because they compile what you needed on the same cell every time you run a new report, so the consolidated master data will always show you what you need from any specific reporting configuration sheet.