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.
Open up Excel and log into the Blockspring Console. You should see the Analytics Report block in the sidebar. (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:
- Web Property
- Table View ID (also called “View” on the Google Analytics admin page)
- Start and End Reporting Dates
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.
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.
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.
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.
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.
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.