Google Analytics is one of the most widely known and used website analytics platforms. BuiltWith, a website that tracks what software is used on websites across the internet, knows of 29.3 million websites using Google Analytics, which comes out to 8.4 percent of the 348 million websites tracked in its database. (Source). Every day, Google Analytic’s (GA) is pulling billions of pieces of data for site owners.

While many never even log in to see what is going on, the few that do are often overwhelmed by the amount of data and only look at service level metrics, such as overall site traffic. Learning GA takes time and patience. Just like any tool, the more you learn and use it, the more you get out of it. Google has a number of plugins that can help GA users extract their data and build powerful kusing Google Sheets. In this post, we will go over how to install the sheets add-on, run a report, and create a visualization that can bring your website data to life.

Installing the Add-On

  1. The first step is to go to Google Sheets.
  2. In order to use the Google Analytics add-on, you must add it to your spreadsheet. The next few steps will tell you how.
  3. First, create a new Google Spreadsheet (or open an existing one).

DATA Visualization.png

4. From the menu bar choose: Add-ons > Get Add-ons.

5. Find the Google Analytics Add-on from the add-ons gallery and select it.

6. From the add-on description page, click the “+” in the top right corner to add this add-on to your spreadsheet.

7. A dialog should pop up requesting your permission for the add-on to access your Google Analytics data. Click “Accept”.

8. The add-on is now installed. A “Google Analytics” submenu should now appear in the Add-ons menu.

Google Sheets-1.png

Creating Reports

Now that you have the GA add-on added, you can run reports. Reports can be created manually or by using the add-on’s report creation tool. To build a report with the tool itself, select “Add-ons” > “Google Analytics” > “Create a New Report” from the menu bar. This will display a sidebar on the right which allows you to 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 a sheet doesn’t exist in the report, one will be created.

Google Sheets 2-1.png

Now, it can be templating to try to pull all of your data at once, but I recommend doing it in chunks. This way you can still make sure you’re getting what you need and not having to sort through a ton of extra data. To create additional reports, simply repeat the steps above. The new reports 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 add-on 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 are not sure what to enter, review the configuration parameter reference page. This will help you know what to query and help ensure you get the data you want and need.

Running Reports

Once you have all your inputs into the “create a report” form, you will need to query the data. To run all the reports you’ve created select “Add-ons” > “Google Analytics” > “Run Reports” from the menu bar.

Automating Reports

In order to make sure you have the right data, you can run reports on a schedule. For instance, you may want a report to run once a month and to set the report to run every 30 days. To enable your reports to run automatically select “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 labeled “Enable reports to run automatically.” Once scheduling is enabled you can use the dropdown bar to control the time and frequency.

Creating Visualizations

While you can learn a lot from spreadsheets and the data they hold, adding some visualization can really help. Let’s say you run a report to compare this month’s traffic with last month’s traffic. To build the data source for this example, check out these videos. They will give you a really great walkthrough.

Introducing the Google Analytics Spreadsheet Add-on

Building a dashboard with the Google Analytics Spreadsheet Add-on

After you have pulled the data you will see “sheets” tabs at the bottom of the page where each individual page of the data is held. Create a new sheet and label it “Dashboard.” Pull the data you are interested in and click the “insert chart” button in the navigation. Choose the visualization the makes the most sense and customize as needed.

DATA Visualization 2-1.png

While these reports aren’t the fanciest, they do help make sense of more of your data. When you can see how your traffic is trending you can explore what could be the cause of it. This is just one simple way of using free tools to dig deeper into your data to help you make more informed decisions. For a detailed set-by-step process, check out this link and tutorial from the Google Developers site.