Google Analytics may not always be the best way to interpret data. On the other hand, spreadsheets allow you to work with data easily, interlink them and make various graphs, calculations or predictions. Fortunately, Google created an add-on that enables you to download data directly from the Google Analytics API without any knowledge of the code.
This add-on is the most stable solution in the long term if you do not need data from other platforms. Basically, you are able to get any data you see in Google Analytics into Google Sheets – standard metrics and dimensions, defined goals, events or custom dimensions and metrics.
First, there are three basic steps you need to do to create a new report (all of them can be changed or added later):
After that, “Report Configuration” sheet is created automatically, in which you can modify, add or remove metrics and dimensions, set the date range, add filters or sort data.
See an example of a product data report below (source/medium: google / cpc, date range: last 7 days, device category: mobile – all sorted by unique purchases in descending order).
First ten parameters are the most important ones.
Report name: the name of the report and the sheet (where the report data is written)
View ID: the ID for your Google Analytics View
Start date and End date: the date range for fetching Google Analytics data; it is formatted as YYYY-MM-DD, or as a relative date (today, yesterday, XdaysAgo where X is a positive integer)
Metrics: a list of metrics to download, e.g. “ga:sessions”, “ga:transactions”, “ga:transactionRevenue”
Dimensions: a list of dimensions to download, e.g. “ga:sourceMedium”, “ga:dimensionN (where N is a number of custom dimension in Google Analytics”, “ga:productCategory”
Order: how the data in the report will be sorted, e.g. “-ga:transactionRevenue” (data sorted by revenue in descending order) or “ga:costPerConversion” (data sorted by cost per conversion in ascending order)
Filters: conditions that let you restrict the set of results that are returned (the principle is the same as in Google Analytics), e.g. “ga:sourceMedium=~facebook” (any kind of Facebook visits), “ga:campaign=~search” (all campaigns containing the word “search”)
Segments: concrete segment you want to download data for – you can use either segments defined in Google Analytics or create your own right in the configuration sheet, e.g. “sessions::condition::ga:userGender==female” (segment for women)
Limit: the maximum number of results that will be returned for a report – keep in mind that the more data you have, the more time-consuming the update will be. What is more, there are also limits and quotas on API requests
Spreadsheet URL: if you want to output the result to a different sheet
Skip Report: the report in the corresponding column will not be run if the value is set to “TRUE” (for example in case you run more reports and do not want to update this one)
Report Type: by default (or when left blank) this report will query the Analytics Reporting API v4; to query the Multi-Channel Funnels Reporting API, you can set the value to “mcf” (just keep in mind that there are different definitions of dimensions and metrics)
Sampling Level: the desired report sample size – “DEFAULT” (the same as leaving it blank) with no sampling, “SMALL” or “LARGE” to determine the sampling level (in case you have a lot of data and want to speed the report up)
Use Resource Quotas: if you have an Analytics 360 account, set this parameter to TRUE to have higher sampling thresholds
Exclude Empty Rows: all rows will be displayed when it is left blank (those rows where the value is set to “0”)
After everything is set up, run the report selecting “Add-ons > Google Analytics > Run reports” from the menu bar – all defined reports in the configuration section will be run. A separate sheet is created for each defined report with its name and the resulting report.
To update the report automatically, select “Add-ons > Google Analytics > Schedule Reports” from the menu bar, check the box labelled “Enable reports to run automatically” and use the select dropdown to control the time and frequency.
The biggest advantage of the add-on is its connection with Google Sheets. You can add any additional data to the tables and link them to the updated Google Analytics report. For example, if you don’t want to pull data directly into Google Analytics, you can easily add margin data, make calculations in your document, plan for the next period, or make sales predictions and pull real data to them every day. If you have more Google Analytics “Views”, you can easily connect them using the add-on. You can also generate a report of the best-selling products in the last few days, add the necessary data (product ID, image url, target url, price, etc.) using the csv feed, connect the created feed to Google Ads account and run an experiment in the Google Display Network.
