SEOs and marketers always have a lot on their plates.
That's why we care so much about tools that save us time.
Any chance you get to automate some of your work is one that you should take.
That's why when I first came across the Google Analytics add-on for Google Sheets, I knew I had to share it with you in a post.
What exactly is this add-on, and why is it useful? I'm going to assume you know what Google Analytics is.
But you may not know what Google Sheets is. It's essentially the free spreadsheet competitor to Excel that Google has developed over the years.
The best part is your spreadsheets can live in the cloud and be worked on by multiple people at the same time.
The add-on I'll show you how to use allows you to pull data from your Google Analytics account using the API and create reports with it.
Not only that, you can re-run these reports at any time.
That's really powerful because once you create a report, you don't have to spend time remaking it.
Whether you work for clients or do marketing for an internal team, you can generate these reports on a regular basis for your meetings and progress reviews.
Why would you want this? If it's not clear yet, it will be soon. Playing with data in Google Analytics is fine, but it's not the most usable interface.
Compare that to a spreadsheet, where you can use a ton of different functions (like filtering, custom graphing, etc.) on the data you retrieve.
Additionally, it's really easy to generate those reports on a regular basis and make improvements whenever you'd like.
At this point, you should know if this add-on is going to make your life easier or not. If you know it will, keep reading on, and I'll show you the ins and outs of it.
Step 1: Install the add-on
Installing the add-on is easy.
Start by opening a new Google Sheet.
Then, click on the “add-ons” menu option at the top, and choose “get add-ons.”
A new window will pop up. Type in “Google analytics” into the search box at the top right side, then press “Enter.”
There should be one obvious add-on with the Google Analytics name and symbol. Click it, then press the “+free” button on the next window to install it.
The add-on should now be installed for use with all your future sheets.
Click on the “add-ons” menu again, and you should see a new listing for “Google Analytics.”
If you don't see it there, you may have to refresh the page.
Finally, you should get a pop-up at some point, telling you the link to the support forum, but if you didn't get it, here's the link. If the add-on is not working correctly, that's where you should post your questions.
Step 2: Create your first report
This add-on, while it should simplify your life, can actually be a little overwhelming if you dive right in.
In this section, we'll create an example report and go over the basic settings and options you have.
Start by going back to the Google Analytics option in the “add-ons” menu, and this time, click on “Create new report.”
Once you do that, a menu like the one below should show up on the right hand side of your screen:
In order for this to work, you need to be signed in (in Sheets) to the same Google account that you use for Google Analytics.
The first few settings are obvious: give your report a title, and choose the website (property) that you want to analyze.
The metrics and dimensions are where things get interesting.
Metrics, or key performance indicators (KPIs), are the heart of most marketing reports. I wrote a detailed post on the 14 most common metrics for SEOs that you might want to refer to now.
Many of those metrics can be found in Google Analytics:
- Traffic
- Average time on page
- Pages per visitor
- Bounce rate
When you click on the “metrics” field, a list will appear with a huge variety of metrics. You can choose any metric you'd like for now, but I'm going to start with “users.”
While you'll probably want to choose more than one metric for your actual reports later on, one is fine for now.
The last field is the dimension field. In Google Analytics, you can filter data based on things like source, referral path, keyword, and so on. That's what dimensions are here-they allow you to segment your reported data.
For our example, pick any dimension you want, or leave it blank.
Then, finish off by clicking “create report.”
After a few seconds, you should see something like this:
Here's the confusing part: This didn't actually create the report that most people would expect. Instead, it just created the instructions that the add-on needs to run the report and pull data from your Analytics account.
Let's actually run the report: Now go back to the “add-ons” menu, but this time, click on “run reports.”
This will run all the reports you set up in the active spreadsheet, but since we only have one for now, it'll do just that one.
A few seconds later, you'll get a confirmation box, saying the report was run. And at the bottom, a new tab will appear:
Click the tab, and you should see the data in the report, as expected:
This will match your Google Analytics data, but feel free to double check.
You can create as many reports as you'd like. The settings will all be stored in the main tab. When you run your reports, you'll get a tab for each report (you won't get a new tab if the report has already been run before).
Editing reports: On the original “report configuration” tab, your report settings will always be available to be edited.
You can change dates, add and remove metrics or dimensions, and even add things like filters, which I'll go into next.
To add more than one metric to a report, you'll need to select the metric box, put the cursor at the end, and then press “alt + enter” to create a new line. Then type in the new metric as usual.
Step 3: Understand all the different options
Congratulations, you've run your first report!
But that's just the tip of the iceberg because there are a ton of different combinations and options in this add-on that you should be aware of.
Let's go through all the fields in the main report configuration tab, one by one. You need to know what each of them does and how you can use them:
- Report name – Just a quick note: if you delete the name in this cell, the report will not be run when you run your reports. This name will show up at the top of each report, but it will also be the label of the report sheet at the bottom of your spreadsheet.
- View (profile) ID - That's the ID of your Analytics property that data is being pulled from. It will be pulled automatically when you create the report. However, you could duplicate reports for multiple sites by copy/pasting the rest of the cells and changing this value.
- Start date/End date – You can specify the date range that the data is pulled from.
- Last N days - You can also specify to just pull data from the last “N” number of days, where N is any number you input into the cell. Note that you can use either this option or the start/end date option-not both.
- Metrics – You can add multiple metrics for each report. You can get a full list of the different metric labels here so that you can just edit the configuration instead of creating a new report every single time.
- Dimensions – You use these to segment your traffic to get metrics separated for each type of user. However, dimensions need to be compatible with the metrics in your report; otherwise, they won't work. If you're just typing in dimensions, go to that list of metrics, and select either a dimension or metric to see which ones are compatible.
- Sort - You can set up the report to automatically sort the results if you find yourself wasting time doing that manually. You'll have to manually input the metric or dimension here that you want to sort by (e.g., “ga:sessions”). You can sort in reverse by putting a minus sign in front (e.g., “-ga:sessions”).
- Filters – You can use filters to remove certain parts of your traffic that you don't want to see. For example, if you didn't want to include referral traffic in your report, you'd enter “ga:medium%3D%3Dreferral” in this box. Refer to the “filter syntax” and “filter operators” on this page to see what's available.
- Segment – This is true segmenting, allowing you to look at a specific section of data. To use this field, you'll need to enter a value like “sessions::condition::ga:medium%3D%3Dreferral.” You can find more examples here.
- Sampling level – There are three acceptable values here: “DEFAULT,” “FASTER,” or “HIGHER_PRECISION.” For most metrics, the default value (of “DEFAULT”) is fine. If the report is taking too long, choose “FASTER” to sacrifice accuracy for speed.
- Start index – If for some reason you want to ignore the first “X” results, you can do so by specifying a start index. For example, if you type in 5 here, the first 4 results will not be shown.
- Max results – You can choose the number of results to be returned in your reports, up to 10,000. By default, you'll get 1,000.
- Spreadsheet URL - If you want your report data to be sent to a different spreadsheet for any reason (e.g., if you have a sheet for a specific client already), you can just enter the URL of the file where the report should go.
I know that was a lot, but struggle through it, and you'll have everything you need to get going.
When you consider all these different fields, you can create just about any custom report you want. Be prepared for reports to fail if you're adding many values to them. Just add them one at a time, and tweak them until they work (test each time you add one).
Step 4: Create reports that are actually useful
At this point, you have a pretty solid understanding of what the add-on is all about and how to use it.
It's time to create reports that you'll actually use on a regular basis-that's the whole purpose of this exercise.
Although you might be good from here, let's outline the general steps:
- Decide which metrics you want to measure
- Decide which segments you want to analyze
- Create the report
- *Run the report periodically
- Manipulate the results (sort, filter, graph) as needed
I put a star by #4 because there's an alternative. If you haven't noticed yet, you have a third option when you go to the add-on in the menu called “Schedule reports.”
With the scheduling feature, you can have reports run automatically every hour, day, month, etc.-basically, whenever you want.
Conclusion
Saving time and being able to create consistent reports from your analytics data are both important things for marketers.
If you create reports from Google Analytics on a regular basis, you'll likely benefit from giving this add-on for Google Sheets a try.
Once you've created a report, you can then make charts from the data or share the data directly with your client (if you don't want them messing around in Google Analytics).
If you have any questions about this add-on or tips on using it more effectively, please share them in the comment section below.
