Philip Walton, Google Analytics Developer Programs Engineer – March 2014
The Google Analytics Spreadsheet add-on makes it easier for Google Analytics users to access, visualize, share, and manipulate their data in Google Spreadsheets.
Introduction
The Google Analytics spreadsheet add-on brings you the power of the Google Analytics API combined with the power of data manipulation in Google Spreadsheets. With this tool, you can:
- Query and report data from multiple views.
- Compute and display custom calculations.
- Create visualizations and embed those visualizations on third-party websites.
- Schedule your reports to run and update automatically.
- Control who can see your data and visualizations by using Google Sheets' sharing and privacy features.
Video tutorials
The following two videos explain how to download and install the add-on as well as build a dashboard that updates automatically and can be embedded on a third-party website.
Introducing the Google Analytics Spreadsheet Add-on
Building a dashboard with the Google Analytics Spreadsheet Add-on
Installing the add-on
To use the Google Analytics add-on you must first add it to your spreadsheet. The following instructions will walk you through the process:
- Create a new Google Spreadsheet (or open an existing one).
- From the menu bar choose: Add-ons > Get Add-ons…
- Find the Google Analytics Add-on from the add-ons gallery and select it.
- From the add-on description page, click the "+" in the top right corner to add this add-on to your spreadsheet.
- A dialog should pop up requesting your permission for the add-on to access your Google Analytics data. Click "Accept".
- The add-on is now installed. A "Google Analytics" submenu should now appear in the Add-ons menu.
Creating reports
Reports can be created manually or with the help of the add-on's report creation tool. To use the tool, select "Add-ons" > "Google Analytics" > "Create a New Report" from the menu bar. This will display a sidebar on the right that allows you to easily look up your Google Analytics account information as well as choose what dimensions and metrics to query. When you're finished, click the "Create Report" button and your report information will appear in a sheet named "Report Configuration" (if the sheet doesn't already exist, it will be created).
To create additional reports, just repeat the steps above. Each additional report you create will add a new column of data to the right of the previous report.
If you look at the values the report creation tool enters into the report configuration sheet, you'll notice that many of the cells are left blank. This is intentional. The tool is meant to help get you started and provide you with the information you might not know off the top of your head. The rest of the fields will need to be entered by you. If you have any questions about what to put in each field, see the reference at the bottom of this page.
Note: While the report creation tool is helpful, it's not required to create new reports. Once you get the hang of how it works, you can skip the tool and simply copy and paste the information you want directly into the report configuration sheet.
Running reports
To run all the reports you've created select "Add-ons" > "Google Analytics" > "Run Reports" from the menu bar.
Excluding a Report
By default, the add-on will run every report with a name, so if you want to temporarily exclude a report without deleting it, you can simply remove all text from the cell where the name value goes.
Choosing an Output Sheet
Running the reports will make a request to the Google Analytics API and print the results to a sheet of your choosing. It can be a sheet in the spreadsheet you're currently in, or a different spreadsheet altogether (as long as you have edit access to that spreadsheet). To print the results to a different spreadsheet copy the spreadsheet URL and paste it into the cell to the right of the "spreadsheet-url" parameter.
Scheduling reports to run automatically
You can enable your reports to run automatically by selecting "Add-ons" > "Google Analytics" > "Schedule Reports" from the menu bar. This opens a report scheduling dialog where you can turn scheduling on and off, and set how frequently your report will run.
To turn scheduling on, check the box labelled "Enable reports to run automatically." Once scheduling is enabled you can use the select dropdown to control the time and frequency. Reports can be scheduled to run every hour, day, week, or month.
To turn scheduling off, uncheck the box labelled "Enable reports to run automatically."
Important: The add-ons platform has certain limitations on scheduling. Please read the FAQs if scheduled reports don't run as expected.
Common use cases
Querying data from multiple views
Through the Google Analytics web interface you can only see reports and visualization data from a single view at a time. With this add-on you can run reports on any number of views and use the generated data in whatever way you like. For example, if you have a view that tracks your iOS traffic and a separate view to track your Android traffic, you could use this add-on to run reports on both views and easily compare the results side by side, in the same spreadsheet.
Creating custom calculations with your Google Analytics data
The Google Analytics API collects a lot of different types of data and gives you a great deal of choice for how you want it reported but sometimes you require a specific calculation. When your data is in a Google Spreadsheet you can leverage the spreadsheet's built-in data manipulation features.
Creating dashboards with embedded data visualizations
When your data is in a Google Spreadsheet you can take advantage of all the visualization tools that Google gives you out of the box. Furthermore, Google Spreadsheet charts and graphs can be embedded into external sites. This means you can easily create your own dashboards with visualizations of your Google Analytics data, host them wherever you want, and they will automatically update as often as you run your reports.
Easily controlling who can see your Google Analytics data
The Google Analytics website provides great insight into your data at a glance, but sometimes you want to share your data with stakeholders without giving them access to your Google Analytics account.
With the Google Analytics Spreadsheet add-on you can easily solve this problem because Google spreadsheets have their own, built-in permissions system. All you have to do is create a new spreadsheet, grant access to whoever you want, and then have your reports output data to that spreadsheet.
Automatically updating your data and visualizations
Once you've created reports or embedded a visualization on a third party website, you can ensure your data is always current by scheduling your reports to run automatically.
FAQs
How do I know when my scheduled reports will run next?
The first run of your scheduled reports will occur randomly during the time period you specify. Subsequent runs will occur predictably at that same time, offset by the interval you've chosen. For example, if you schedule your reports to run daily between 4 a.m. and 5 a.m., and the first run occurs at 4:12 a.m., all subsequent runs will also occur at 4:12 a.m.
My scheduled reports didn't run when they were supposed to, is it broken?
When scheduling reports, make sure there is plenty of time between when you create the schedule and when the schedule is supposed to run. If it's too close to the first occurrence of the scheduled time, there's a chance those reports will be postponed until the next occurrence. It's usually best to leave at least a one-hour buffer.
Can I set different schedules for different reports?
Scheduling either applies to all reports or no reports. If you need to schedule different reports at different intervals, you can use multiple spreadsheets.
Why is my report data in the wrong format?
The Google Analytics Reporting APIs return all values in U.S. format. If your spreadsheet is set to a different locale, Google Sheets will attempt to do a conversion for you, which will sometimes yield unexpected results.
To avoid this issue, make sure the locale of your report spreadsheet is set to "United States" (instructions for changing your spreadsheet's locale can be found in the Docs editors Help.) To then convert your data into the format of your preferred locale, you can create a second spreadsheet with your preferred locale set and use the IMPORTRANGE
function to import your report data into that new spreadsheet. Google Sheets will correctly convert formats between locales when importing from one spreadsheet to another.
Getting help
To ask a question about the add-on or to report a bug, post a message the google-analytics-spreadsheet-add-on Google group. Search the group before posting as your question may have already been answered.
Configuration parameter reference
The sections below outline the required, optional, and hidden parameters:
- Required parameters must be present for each report or the report will error.
- Optional parameters are not required but can be specified.
- Hidden parameters are advanced options that are not needed for most reports and are hidden by default. You can use these parameters by un-hiding the rows 14-16 in the Report Configuration sheet.
Required parameters
Name | Description |
---|---|
Report Name | This is the report name. It will also be the name of the sheet where the report data is written. |
View ID | The ID for your Google Analytics view. You can get this value using the report creation tool or find it in the Google Analytics account admin. |
Start Date | The start date for fetching Analytics data. Requests can specify a start date formatted as YYYY-MM-DD, or as a relative date (e.g., today , yesterday , or NdaysAgo where N is a positive integer).You can also use Sheets date functions to specify this value programmatically. For example, the following expression returns the last day of the previous month:=EOMONTH(TODAY(), -1) |
End Date | The end date for fetching Analytics data. Requests can specify an end date formatted as YYYY-MM-DD, or as a relative date (e.g., today , yesterday , or NdaysAgo where N is a positive integer).You can also use Sheets date functions to specify this value programmatically. For example, the following expression returns the last day of the previous month:=EOMONTH(TODAY(), -1) |
Metrics | A list of metrics to query. Metrics can be specified in one of two formats:A list of comma or new-line separated metric IDsAs a JSON value in the format required for the metrics parameter of the Analytics Reporting API v4 .For example, all of the following are valid values for the Metrics parameter.ga:sessions,ga:bounces``ga:sessions ga:bounces``[{ "expression": "ga:sessions/ga:users", "alias": "Sessions per User", "formattingType": "FLOAT"}, { "expression": "ga:totalEvents/ga:pageviews", "alias": "Events per Pageview", "formattingType": "FLOAT"}] For most use cases, a list of metric IDs is the easiest way to specify the Metrics parameter. However, if you want to leverage advanced features of the Analytics Reporting API such as metric expressions in the third example above, you have to specify the value as JSON.The full list of dimensions and metrics and their valid combinations is available using the Dimensions and Metrics Explorer. |
Optional parameters
Name | Description |
---|---|
Dimensions | A list of dimensions to query. Dimensions can be specified in one of two formats:A list of comma or new-line separated dimension IDsAs a JSON value in the format required for the dimensions parameter of the Analytics Reporting API.For example, all of the following are valid values for the Metrics parameter.ga:source,ga:deviceCategory``ga:source ga:deviceCategory``[{ "name": "ga:source"}, { "name": "ga:deviceCategory"}] For most use cases, a list of dimension IDs is the easiest way to specify the Dimensions parameter.The full list of dimensions and metrics and their valid combinations is available using the Dimensions and Metrics Explorer. |
Order | The sorting order of the results by column (metric or dimension ID) and direction (ascending or descending). Order can be specified in one of two formats:Using the legacy sort format from Core Reporting API v3.As a JSON value in the format required for the orderBys parameter of the Analytics Reporting API v4.For example, all of the following are valid values for the Order parameter.-ga:sessions,ga:eventLabel``-ga:sessions ga:eventLabel``[{ "fieldName": "ga:sessions", "sortOrder": "DESCENDING"}, { "fieldName": "ga:eventLabel", "sortOrder": "ASCENDING", "orderType": "DIMENSION_AS_INTEGER"}] For most use cases, the legacy format is the easiest way to specify the Order parameter. However, if you want to use the orderType parameter, you must specify the value as JSON. |
Filters | Conditions that let you restrict the set of results that are returned. Filters can be specified in one of two formats:Using the legacy filters format from Core Reporting API v3.As a JSON value in the format required for the dimensionFilterClauses and/or the metricFilterClauses parameters of the Analytics Reporting API v4.For example, any of the following are valid values for the Filters parameter.ga:sessions>10;ga:country==United States``{ "metricFilterClauses": [{ "filters": [ { "metricName": "ga:totalEvents", "operator": "GREATER_THAN", "comparisonValue": "100" } ] }], "dimensionFilterClauses": [{ "operator": "AND", "filters": [ { "dimensionName": "ga:eventCategory", "operator": "EXACT", "expressions": ["Outbound Link"] } ], "filters": [ { "dimensionName": "ga:eventAction", "operator": "EXACT", "expressions": ["click"] } ] }]} For most use cases, the legacy format is the easiest way to specify the Filters parameter. However, if you need to do advanced filtering (e.g. use comparison operators on dimensions) then you must specify the value as JSON. |
Segments | Conditions that let you restrict the set of sessions or users your query applies to. Segments can be specified in one of two formats:A list of comma or new-line separated segment IDs or definitions using the legacy segment format from Core Reporting API v3.As a JSON value in the format required for the segments parameter of the Analytics Reporting API v4.For example, any of the following are valid values for the segments parameter.gaid::-1,sessions::condition::ga:city==New York``gaid::-1, sessions::condition::ga:city==New York``[{ "dynamicSegment": { "name": "New York Sessions", "sessionSegment": { "segmentFilters": [{ "simpleSegment": { "orFiltersForSegment": [{ "segmentFilterClauses":[{ "dimensionFilter": { "dimensionName": "ga:city", "expressions": ["New York"] } }] }] } }] } }}, { "dynamicSegment": { "name": "Non-referral Sessions", "sessionSegment": { "segmentFilters": [{ "simpleSegment": { "orFiltersForSegment": [{ "segmentFilterClauses": [{ "dimensionFilter": { "dimensionName": "ga:medium", "operator": "EXACT", "expressions": [ "referral" ] } }] }] }, "not": "True" }] } }}] For most use cases, it's easiest to specify segment IDs or use the legacy definition format. However, if you need to do advanced segmentation (e.g. use comparison operators on dimensions) then you must specify the value as JSON. |
Limit | The maximum number of results that will be returned for a report. If this parameter is left blank, all results are returned.Caution: For reports that you expect to return thousands or millions or results, it's advisable to set a limit. When no limit is set, the add-on will perform additional queries until all rows are returned, which will take more time and can use up your reporting quota. |
Spreadsheet URL | The URL of an external Spreadsheet to output the result. By default, results are outputted to a sheet in the current spreadsheet. Note, you must have edit permissions on the spreadsheet to use this feature. |
Skip Report | When TRUE the report in the corresponding column will not be run. This option is useful if experimenting with a new report and you don't want to run all your reports until you're finished. It can also be useful to if you want to stop running reports that already exist and the data isn't going to change.Note, this field can be set programmatically using a formula. For example, the following expression will skip the report if its End Date value (cell "B5") is in the past:=IF(B5 < TODAY(), TRUE, FALSE) |
Hidden parameters
Name | Summary |
---|---|
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".Note that when using the MCF API, you must also use MCF dimensions and metrics. In addition, some of the other features (e.g. segments) are not available in MCF reports. |
Sampling Level | The desired report sample size. Possible values are "DEFAULT" (the same as leaving it blank), "SMALL", or "LARGE". |
Use Resource Quotas | The resource based quota system is a new quota system that will enable certain Analytics 360 accounts to have higher sampling thresholds while using the Analytics Reporting API. If you have access to this feature, you can enable it by setting this parameter to TRUE . |
Exclude Empty Rows | This parameter allows you to exclude rows from the report if all the metric values in the row are zero. The default behavior (or when left blank) is to display all rows (recommended).Note, this parameter corresponds to the includeEmptyRows parameter of the Analytics Reporting API v4 but has been renamed from "include" to "exclude" to make its intended behavior easier to understand. |