Data Visualization with Google Sheets

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 visualizations using 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 Google Sheets 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. Next, create a new Google Spreadsheet (or open an existing one).

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.

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. 

Now, it can be tempting 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. Don’t worry, this is intentional. The add-on is meant to 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 Google’s very helpful configuration parameter reference page. This will help you better understand 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, it’s time to run the report. To do this, 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 up to date 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 set up an automated report select “Add-ons” > “Google Analytics” > “Schedule Reports” from the menu bar. 

This opens a report scheduling box 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 drop-down bar to control the time and frequency. 

For a great tutorial, check out the video below.

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.   

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.

As you can see, adding a visualization to the data helps you more easily see trends. There are a number of different chart types available that you can use to dig deeper and find out what is working and what isn’t. Another great feature is if you have your report on a schedule, the visualizations will update when the data updates. This is a very convenient feature in a fast paced business world.

Want to learn more about data visualization with Google Sheets?  Check out the video below.

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. If you are looking for to do even more, sites like Simple Sheets offer dashboards and slicers to help your data come to life.  For a detailed set-by-step process, check out this link and tutorial from the Google Developers site.