The Ultimate Tutorial on How to Import Reports From Google Analytics to Google Sheets

What is the pure value of Google Analytics? It is a know-it-all (or wise-ass :D) tool to learn about your website’s audience. With GA, you can get a detailed report on who, when, and how they navigated through your pages and, hence, understand their online behavior. So, the answer to the question “What do I need Google Analytics for?” is clear.

GA provides you with actionable data, which you can process using different tools such as Google Sheets, Excel, or even Data Studio. But first, you need to export the data. This is what we’re going to focus on below.

How to export Google Analytics to Google Sheets 

The native export function in Google Analytics is quite simple. Choose the report you need, click the “Export” button and select Google Sheets as the file format. Additionally, you can export GA reports as CSV, Excel, and PDF files. Check out our guide on how to export Google Analytics data to learn more.

Once you have selected Google Sheets, you will be asked whether you want to import data to Google Sheets.

Click Import the data and welcome your Google Analytics report in a new spreadsheet created on your Google Drive. For example, this is what the Audience Overview report looks like in Google Sheets:

Which reports from Google Analytics you can import to Google Sheets

You can export you custom reports, as well as the following ones:

Flow reports (Users Flow, Behavior Flow, etc.) can be exported as PDF only; Multi-Channel Funnels reports can be exported as PDF and CSV files. 

You can’t export any realtime reports, or Experiments (Behavior) or Lifetime Value (Audience). You can easily calculate the latter yourself – here is the customer lifetime value calculation guide.

How to automate Google Analytics reports importing into Google Sheets

The option above is good, but it’s manual. At the same time, you can automate import of Google Analytics to Google Sheets on a schedule without any coding or advanced manipulation. For this, you’ll need to complete three steps:

• Install the Google Analytics add-on from Google Workspace Marketplace.
• Create a report according to the parameters you want.
• Enable your report to run automatically.

Add-on to pull Google Analytics data into Google Sheets

On the Google Workspace Marketplace, you can find the Google Analytics Sheets Add-on. It’s a free tool to connect Google Sheets to GA and query specific report data automatically on a schedule. Install it and then open the spreadsheet you want to export Google Analytics data to.

How to create a Google Sheets report based on Google Analytics data

In your spreadsheet, go to the Add-ons menu, select Google Analytics and click Create new report.

The dashboard for creating a new GA report will open on the right of your spreadsheet. It contains fields you’ll need to fill out to configure your Google Analytics report.

1) Name your report

Enter the name of your report

2) Select a view

Select your Google Analytics account, property and view. If you are unsure if you know them, go back to your Google Analytics dashboard and click All accounts. You’ll see the list of these parameters in a single window:

 3) Choose configuration options

Google Analytics reports consist of dimensions and metrics.

• Metrics include specific measurements, such as New Users, Unique Events, Page Load Time, Sessions, etc. 
• Dimensions break down metrics by specific criteria. For example, the New Users metric can include the User Type dimension; the Sessions metric can include the Session duration dimension; and so on.

The third field to configure is Segments. A segment is a subset of data based on dimensions and metrics. If you don’t want to segment users, leave this field blank, and the Google Analytics add-on will import All users. 

Otherwise, you’ll have to specify a segment just like when you add a segment to your report in Google Analytics. 

For example, if your metric is Users, you can specify one segment for users from the USA, another segment for users who visited your FAQ page, and so on. 

Note: You can create a Google Analytics report with these fields empty and configure them later once the report is created.

Click Create Report and the add-on will add a Report Configuration sheet in your Google Sheets doc.

How to configure a Google Analytics report on the Report Configuration sheet

A Report Configuration sheet is not the report itself, but the sheet where you can change the configuration of your reports. It is used only to change the configuration of the created reports. To create additional reports, you’ll have to repeat the steps described above; or, just fill out the parameters in the respective cells in the next column to the right. The configuration of each new report will be added as a new column on the Report Configuration sheet. 

Unlike the add-on dashboard with drop-down parameters, here you’ll have to insert parameters manually. So, if you want to add some metrics or change the time range of your report, the Report Configuration sheet is the only place where you can do this. Let’s check out which parameters you can configure.

Report Name [Required parameter]

You can change the name of your report. This parameter is also the name of the sheet (tab) where the Google Analytics data will be imported to.

View ID [Required parameter]

You can change the Google Analytics view of your report. For this, insert the View ID you want to report. You can find it by clicking All Accounts on the Google Analytics dashboard.

Start Date/End Date [Required parameter]

By default, the start and end dates in a new report have the following values:

• Start Date=30daysAgo
• End Date=yesterday

You can change these values using one of the following options:

• YYYY-MM-DD format, for example 2020-10-23
• Relative date such as today, yesterday, or NdaysAgo (N can be only a positive integer)
• One of the Google Sheets date functions to return the date value automatically. For example, this formula =EDATE(TODAY(), -1) will return the month ago date, and this formula =EOMONTH(TODAY(), -1) will return the last day of the previous month.

Metrics [Required parameter]

Enter the IDs of the metrics to query in your report. Separate them using commas; or enter each metric ID on a new line. For example,

ga:users, ga:sessions 

We advise you to use a Google Analytics tool called Dimensions & Metrics Explorer. It lists and describes all possible options, and excludes the dimensions and metrics that cannot be queried together. For example, if you choose the metric 7 Day Active User, such dimensions as User Type or User Bucket, as well as metrics Users and Number of Sessions per User become inactive. 

The following parameters are options, i.e. you can leave these fields blank.

Dimensions [Optional parameter]

You can enter the IDs of dimensions to query in your report. Separate them using commas, or enter each dimension ID on a new line. For example,

ga:userType, ga:sessionDurationBucket 

Dimensions & Metrics Explorer will help you here as well.

Order [Optional parameter]

You can specify the sorting order by column and direction. To sort by column, enter the metric/dimension IDs in the order you want them to return. For example, 

ga:sessions, ga:bounces – the Sessions metric goes first

ga:bounces, ga:sessions – the Bounces metric goes first 

The sort order direction is ascending by default. To change the direction to descending, use a minus sign (-) prefix on the field you want. For example,

ga:sessions, ga:bounces – the Sessions metric will return in ascending direction

-ga:sessions, ga:bounces – the Sessions metric will return in descending direction

Filters [Optional parameter]

You can specify conditions to filter out a dimension or metric in your Google Analytics report. This will let you exclude the rows that you don’t need in the report. Use the following syntax to tailor a filter query:

ga:{metric/dimension} {operator} {expression}

• {metric/dimension} – name of a metric or dimension to filter. For example, sessions, userType, etc.
• {operator} – a symbol that defines the type of filter to use. There are six operators for metrics and six operators for dimensions.
• {expression} – a case-insensitive regular expression that can not be longer than 128 characters. For example, ^New* is the regular expression for strings starting with New.

Operators for metrics 

Operators for dimensions

Multiple filters 

If you need to apply multiple filters using OR logic, separate the filters using a comma (,). For example, to filter out by countries either Germany or France, use

ga:country==Germany,ga:country==France

If you need to apply multiple filters using AND logic, separate the filters using a semicolon (;). For example, to filter out by countries Germany and France, use

ga:country==Germany;ga:country==France

Segments [Optional parameter]

Specify segments (set of users or sessions) to request. For example, the following segment string will select users from Berlin that have visited using the Firefox browser:  

users::condition::ga:city==Berlin;ga:browser==Firefox

For more on segment syntax and usage, read the Google Analytics API documentation. 

Limit [Optional parameter]

You can specify the maximum number of rows to return for your GA report. The default value is 1000. This parameter is recommended to use for reports that return thousands or millions of rows. If you leave this field blank, the Google Analytics add-on will make queries until all rows are returned (this can waste your API quota, which is 50,000 requests per project per day). 

Spreadsheet URL [Optional parameter]

You can import the GA report to an external Google Sheets document. To do this, specify the spreadsheet URL in this field and make sure that you have edit permissions for it. By default, the report will be imported to the current Google Sheets document. 

Skip Report [Optional parameter]

You can skip the import of a certain report or reports if you enter TRUE for this parameter. This option is useful when you have multiple reports and do not need to import all of them. 

You can also apply Google Sheets formulas to set up this field. For example, the following expression will return TRUE (skip the report), if the value in B5 cell is less than 10:

=IF(B5 < 10, TRUE, FALSE)

Hidden parameters

The following parameters are hidden, so you need to click on the arrows to expand them. These include

• Report Type – if you enter mcf in this field, you can query the Multi-Channel Funnels Reporting API instead of the Analytics Reporting API v4 which is set by default. Why would you do this? MCF Reporting API provides a different set of metrics and dimensions that are not available in Analytics Reporting API v4; for example, mcf:adwordsPlacementUrl (URLs where your ads on the content network were placed), and many others.
• Sampling Level – you can change the sampling level (the number of sessions used to calculate the result) for a reporting query. Available options are:
  • DEFAULT — the default sampling level that uses balanced speed and accuracy to return the response.
  • FASTER — the sampling level that uses a smaller sample size to return a fast response.
  • HIGHER_PRECISION — the sampling level that uses a large sample size to return a more accurate response. This slows down the response.
• Use Resource Quotas – the field to enable the Resource Based Quota system – a feature available for Analytics 360 customers. If you’re one of them, set the parameter to TRUE.
• Exclude Empty Rows – set the parameter to TRUE if you need to exclude empty rows from your report. 

That’s it for configuration!

Get data from Google Analytics to Sheets

Once you’ve configured your GA report, you can run it manually. Go to the Add-ons menu => Google Analytics => Run reports

Your Google Analytics report will be imported into a new sheet named accordingly.

How to automate Google Analytics reports into Google Sheets

The best thing about the Google Analytics add-on is that you can automate data importing. For this, navigate to your usual path, go to the Add-ons menu => Google Analytics, and select Schedule Reports.

Then check “Enable reports to run automatically” and choose the schedule.

Click Save and that’s it. All your configured reports will be imported according to the specified schedule! 

How to remove reports from the schedule

If you need to ignore some of the created reports, you can either use the Skip Report parameter, or simply delete all the data from the respective columns of your reports. After that they won’t be available. 

Google Analytics in Google Sheets: Combine your reports with other imported data

The Google Analytics add-on only imports data from GA to Google Sheets. But you can enhance your spreadsheet with other add-ons and solutions to import a variety of other data. 

With Coupler.io, you can import data to Google Sheets from Airtable, WordPress, Clockify, ProfitWell and many other sources. This will let you accumulate data in one place and manipulate it much more efficiently. 

Additionally, Coupler.io provides a Google Sheets importer – an advanced alternative to the native IMPORTRANGE function in Sheets. The Google Sheets importer lets you automate import of data from one sheet or spreadsheet to another on a schedule, without any of the errors associated with IMPORTRANGE. 

How to use Google Sheets for analytics

Having your GA data in spreadsheets opens wide opportunities for processing. You can monitor website performance, create custom dashboards, sales trackers or monitors, as well as visualize data in the way you need. For example, our team uses the Website Traffic Monitor built atop the report imported from Google Analytics. Here is the report configuration we use:

The traffic data from GA is then filtered using the Google Sheets FILTER function. The final view of the monitor is based on a pivot table that lets you see how many views each page had every week historically. 

This is only one use case of how you can analyze your GA data in Sheets and there are many more!

Are there other options to connect Sheets to Google Analytics? 

Actually, the Google Analytics add-on is not a single option to import data. You can go to Google Workspace Marketplace, enter “Google Analytics” into the search bar and check out the alternatives. Are they good enough? To answer this question, just pay attention to the number of installs compared to the GA add-on.

Moreover, the Google Analytics add-on is free, while its alternatives that provide import from multiple sources, including Google Analytics, are paid options. Anyway, it’s up to you to decide. Good luck with your data!