I need to regularly update Google Analytics data in Google Sheets, so I have been looking for a method to automate the process. It turned out to be easy. If you also want to pull data from Google Analytics to Google Sheets, keep reading to learn how.
As you know, Google Analytics allows you to manually export reports to Google Sheets. Why not automate it when you can? Google Analytics sends to Google Sheets through an API on a schedule.
In addition, you can use a few Google Sheets formulas to extract your Google Analytics data into your Google Sheet automatically and keep data updated on a regular basis. All you need is the Google Analytics spreadsheet add-on. I will show you how to do it step by step.
Install Google Analytics Add-On for Google Sheets
The Google Analytics spreadsheet add-on allows you to create custom reports from multiple views through Google Analytics API and schedule your reports to run automatically. It is quite easy to get any data with this add-on. You can follow the steps below to install it.
Step 1. Go to Google Sheet and create a new spreadsheet. From the top menu, click Add-ons and select the Get add-ons to get started.
Step 2. In new pop-up window, search to find Google Analytics add-on. Click on it to install this add-on for your Google Account.
Step 3. It will ask you for some permissions to continue. This Add-on is provided by Google, so feel free to click Continue to install this add-on.
Create New Report in Google Sheets
Needless to say, you should install this add-on for the account that has access to your Google Analytics reports from which you want to pull data. Once that is done, you can create a new report in Google Sheets.
Step 1. Within the Spreadsheet, click on Add-ons > Google Analytics > Create a new report. Then you will see a sidebar that allows you to configure the report.
Step 2. First type a name for your report. Then you can select the Account, the Property, and the View from which you want to export data.
Step 3. Select the Metrics, Dimensions, and Segments that you want to extract. You can add up to 5 dimensions. We can select dimensions from the various report types in Google Analytics. You always edit your report configuration whenever you want.
Step 4. Once you have filled out the sidebar, click Create Report. It will create a new sheet for all your report configuration. From there, you can adjust more options.
Step 5. In the Report Configuration sheet, you can review and modify the report settings before running. By default, the date range is set to the last 30 days. You can specify the start date and end date in YYYY-MM-DD format, or use today, yesterday, or #daysAgo.
The sorting order of the results by column (metric or dimension ID) and direction (ascending or descending). For example, “-ga:sessions,ga:eventLabel” means sort Sessions in Descending order and EventLabel in Ascending order.
You can apply filters just like in Google Analytics. For example, if you use ga:source==media, then the API will only return data sourced from Media. Limit is for defining how many rows of data to extract. You can also generate the report data to another spreadsheet by adding the spreadsheet URL in there. You can leave it blank if you want generate the report in this spreadsheet.
Run Report Manually Or Automatically
Once you done with the report configuration, you can go to Add-ons > Google Analytics > Run reports. In a short while, a new sheet will be created with add the data that you want to extract from Google Ananlytics.
If you don’t want to manually run the report, you automate this whole process. You just go to Add-ons > Google Analytics > Schedule reports. In the popup, check the option Enable reports to run automatically. Then you can enable the reports to run automatically every hour, every day, every week, or every month.
When you save this, the add-on will run the report at the your specified interval. You can also see the updated data in Google Sheets without open Google Analytics.
Automatically Fill Google Analytics Data to Your Sheet
In this video below, I will show you how to use Vlookup and a few other formulas to get Google Analytics values to the corresponding cells.
Formulas Used in the sample sheet.
Get relative path from absolute path:
=IFERROR(ARRAYFORMULA(RIGHT(B2:B,len(B2:B)-39)),)
Get Sessions:
=IFERROR(ARRAYFORMULA(RIGHT(B2:B,len(B2:B)-39)),)
Get gols:
=IFERROR(ARRAYFORMULA(VLOOKUP(C2:C,’demo report’!A2:C,3,false)),)
Conclusion
That’s how you can extract Google Analytics data to Google Sheets. For marketers. Google Sheets can help you automate many things like mail merge, finding YouTube influencers, etc.
