Get Data from Google Analytics into Excel

Learn to connect to Google Analytics and do basic reporting with metrics, dimensions, filters, sorting, and segments.

All Tutorials


Requirements Not Met

Before continuing, you'll want to or with Blockspring.
Otherwise none of this is going to be any fun. Let us know if you need help in the chat window below.

Overview

In this tutorial, we'll show you how to get data from Google Analytics into Excel for basic reporting. You will go through the following three steps:

  • Connect Excel to Google Analytics
  • Setup a simple report using metrics and dimensions
  • Customize your report using filters, sorting, and segments

Once you've gone through these steps, you'll know how to start building your own Google Analytics reports right from Excel.


Step 1: Connect to Google Analytics

Go to the Google Analytics Report page on Blockspring. This page gives you step-by-step instructions for connecting your Google Analytics account to Excel. In the process you'll need to install Blockspring's plugin for Excel.

When you've finished the instructions on the Blockspring page, click the "Use in Excel" button. You should see the "Success!" banner. This means you're ready to switch back into Excel. Analytics report excel completed


Open up Excel and log into the Blockspring Console. You should see the Analytics Report block in the sidebar. Gar excel login1 Gar excel login2 (Psst. If you ran into a roadblock or just got a little lost during any of these steps, let us know using the chat window below. We like to help!)


Step 2: Setup a Report

Google Analytics requires you to provide a few pieces of information to get data from their system:

  • Account
  • Web Property
  • Table View ID (also called “View” on the Google Analytics admin page)
  • Start and End Reporting Dates
  • Metrics
  • Dimensions

Blockspring provides a form to make it easy for you to get this data to Google Analytics and setup your report.

Select an account, web property, and then table view ID using the first three dropdowns in Blockspring. Then provide any relevant date range to your account, select Users, Sessions, and Bounce Rate as metrics, and Source as dimensions. You can hold the Ctrl key while clicking Metrics and Dimensions to select multiple items at the same time.

Excel block in console

Now click “Insert into a New Sheet” to create your Google Analytics report. You'll see a new worksheet tab appear in Google Sheets prepopulated with your report.

The cells in column D (colored yellow) are referenced by the Blockspring formula "=BLOCKSPRING()" in the Results section cell A15 (colored green) Editing the yellow cells alters the Blockspring formula, giving you back a different dataset from Google Analytics.

If you'd like to see data for a different time period, edit the Start Date and End Date in column D to instantly change the date range for your report.


Gar input explanation excel

Limitations

One thing you'll notice right away is that you can't sort, edit, or delete any of the data returned from Google Analytics like a normal spreadsheet table. That's because Blockspring is returning a spreadsheet array rather than static values. We do that so as you change the inputs to your report (the yellow cells), the data you get back can automatically refresh.


Step 3: Customize Your Report

Often, you'll want to set up reports that filter out specific pages, sort your data in a specific way, or only include a specific user segment. Let's explore how.

Click "Optional Parameters" in the Blockspring form. These are pieces of data you can optionally provide to Google Analytics to customize your report.

Excel optional params

To filter: add the Dimension, logical operator (see the Filter Operators section of the documentation for details) and value to be applied. The Dimension names follow Google Analytics variable naming convention starting with "ga:". You can filters on multiple Dimensions by adding more rules using the "Add Filter" button. Multiple filters are linked by "AND" logic.


Excel filters

Click back to "Optional Parameters" and click Sort. Sorting is configured with a comma-separate list of Metric or Dimension names, which are applied in left-to-right order. The names follow Google Analytics variable naming conventions starting with "ga:". Sort is ascending by default, so if you want values to be descending, prepend the name with "-". Type -ga:sessions into the sort box to sort your report largest to smallest by sessions.

Now click "Insert into New Sheet" one more time and you'll see a new worksheet with your new report pop up, now including filters and sorting as well.

Excel sort

Next Steps

You've learned how to build a very simple report using Google Analytics and Excel. Given these fundamentals, you can build just about any Google Analytics spreadsheet report you dream up. Here are some ideas on what to do now:

  • If you have any segments on your account, set up a report that uses them just like you set up filtering and sorting.
  • If you have Google Analytics setup on multiple webpages, create a spreadsheet report across them - for example, to see which landing page has the most traffic, or lowest exit rate. Here are some details on how to report on multiple webpages to get you started.

Otherwise, get ready to jump to our next lesson to learn how to cement these skills and learn how to build a Landing Page Report.

Next tutorial: Landing Page Tracking Report