What is nice about the Google software ecosystem is that all of their products have been developed with the acceptance in the demand for collaboration and api tools. They recognize that users of Google Analytics face challenges analyzing their data within the product itself and need to extract and transform their data in a spreadsheet to answer a question. With this understanding, they also know that it would be beneficial to the user if they were able to create a simple way to export this data to a Google Sheet and even automate this extraction task on a time-based schedule.
The days of having to create complex macros repeating the extract and transform process are over as Google Analytics has a free Google Sheet plugin that allows you to configure reports within your Google Sheet and then schedule the report to run on a time-based schedule. What makes this process even more rewarding is that it uses the Google Analytics API to run the report, which gives you slightly more reporting capabilities than what a custom report within the Google Analytics interface can provide.
In order to use this plugin and sync your Google Analytics data to a Google Sheet, follow the steps below.
1. Install Google Analytics from the Google Sheet marketplace. You will only need to do this once. Once it is installed to your account, then you will notice a “Google Analytics” item in the “Add-Ons” plugin for all of your Google Sheets
2. Hover over the “Google Analytics” item in the “Add-Ons” dropdown and click on “Create a new report”, which will generate a new Google Analytics report configuration sheet, which is where the Google Sheet will look for every time that “Run reports” is triggered.
3. Set up your report based on your reporting criteria. Note that you can add more than one report to run by adding a new report to the next available column. While I won’t go into detail on formatting for filters and segments, you must understand that the combination of conditions and strictness of filters must be in a specific format. The links below provide you with more information on what that format looks like.
4. Once your report is configured, click “Run report”. After triggering the run, you will notice a window appear providing you with the status of the run and if this is your first run, a new tab appear with the name provided for that report.
5. If your report was successfully run, then you should see a report like the screenshot below. If there was an error message then you need to fix the changes mentioned in the error message.
6. If you would like this data pulled on a frequent basis, then click on the “Add-Ons” dropdown, hover over “Google Analytics” and then click on “Schedule reports”. This will trigger a modal window that will ask you to confirm that you want to automate the report and on accepting, the date cadence and time of day for when the report should be run.