With today’s blog post I’m pity all one needs to know about an underappreciated tool: a Google Analytics add-on for Google Sheets. In this post I’ll be covering a following:
1. What is a Google Analytics add-on?
2. How to implement and set adult a Google Analytics add-on.
3. How to emanate a tradition news with a Google Analytics add-on.
4. A step-by-step worked instance of environment adult an programmed report.
5. Further considerations and pitfalls to avoid.
Thanks to Moz for carrying me, and for giving me a possibility to write about this elementary and absolute tool!
1. What is a Google Analytics appendage and because should we care?
I’m blissful we asked. Simply put, a Google Analytics appendage is an prolongation for Google Sheets that allows we to emanate tradition reports within Sheets. The appendage works by joining adult to an existent Analytics account, regulating Google’s Analytics API and Regular Expressions to filter a information we wish to pull, and finally entertainment a information into an easy and discerning format that’s developed for reporting.
The Google Analytics add-on’s genuine value-add to a stating workflow is that it’s intensely flexible, reliable, and a genuine time-saver. Your stating will still be compelled by a stipulations of Sheets itself (as compared to, say, Excel), though a Sheets horizon has served roughly any stating need I’ve come opposite to date and a same will substantially be loyal for many of you!
In a nutshell, a Add-On allows we to:
- Pull any information that you’d be means to entrance in a Analytics API (i.e analytics.google.com) directly into a spreadsheet
- Easily review chronological information opposite time periods
- Filter and shred your data
- Automate unchanging stating
- Make tweaks to existent reports to get new information (no some-more re-inventing wheels!)
If this all sounds like we could use it, review on!
2. Getting started: How to implement and set adult a Google Analytics add-on
2A. Installing a Google Analytics add-on
- Go into Google Sheets.
- On a header bar, underneath your Workbook’s title, click add-on.
- This opens a drop-down menu — click “Get add-ons.”
- In a following window, form “Google Analytics” into a hunt bar on a tip right and strike enter.
- The initial outcome is a appendage we want, so go forward and implement it.
- Refresh your page and endorse a appendage is commissioned by clicking “Add-ons” again. You should see an choice for “Google Analytics.”
That’s all there is to installation!
2B. Setting adult a Google Analytics add-on
Now that we have a Google Analytics appendage installed, we need to set it adult by joining it to an Analytics comment before we can use it.
- Under a “Add-ons” appendage in Sheets, float “Google Analytics” to display a side-bar as shown below.
- Click “Create New Report.” You’ll see a menu seem on a right side of your screen.
- In this menu, set a comment information to a Analytics comment we wish to measure.
- Fill out a metrics and dimensions we wish to analyze. You can offer customize segmentation within a news itself later, so usually select a elementary set for now.
- Click “Create Report.” The outlay will be a new sheet, with a news pattern that looks like this:
- Note: This is NOT your report. This is a setup pattern for we to let a appendage know accurately what information you’d like to see in a report.
Once you’ve arrived during this step, your set-up proviso is done!
Next we’ll demeanour during what these parameters mean, and how to customize them to tailor a information we receive.
3. Creating a tradition news with a Google Analytics add-on
So now we have all these uncanny boxes and you’re substantially wondering what we need to fill out and what we don’t.
Before we get into that, let’s take a demeanour during what happens if we don’t fill out anything additional, and usually run a news from here.
To run a configured report, click behind into a “Add-Ons” menu and go to Google Analytics. From there, click “Run Reports.” Make certain we have your pattern piece open when we do this!
You’ll get a presentation that a news was possibly successfully created, or that something went wrong (this competence need some troubleshooting).
Following a instance above, your outlay will demeanour something like this:
This is your tangible report. Hooray! So what are we indeed seeing? Let’s go behind to a “Report Configuration” piece to find out.
The news configuration:
Type and View ID are defaults that don’t need to be changed. Report Name is what we wish your news to be called, and will be a name generated for a news piece combined when we run your reports.
So really, in a news pattern above, all a submit we’re saying is:
- Last N Days = 7
- Metrics = ga:users
In other words, this news shows a sum series of sessions in a specified View ID over a final week. Interesting maybe, though not that helpful. Let’s see what happens if we make a few changes.
I’ve altered Last N Days from 7 to 30, and combined Date as a Dimension. Running a news again yields a following output:
By augmenting a operation of information pulled from final 7 to 30 days, we get a information from a incomparable set of days. By adding date as a dimension, we can see how many trade a site purebred any day.
This is usually scratching a aspect of what a Google Analytics appendage can do. Here’s a relapse of a parameters, and how to use them:
The name of your report. This will be a name of a news piece that’s generated when we run reports. If you’re regulating mixed reports, and wish to bar one though deletion a pattern setup, undo a news name and a mainstay will be abandoned subsequent time we run your reports.
“January Organic Traffic”
Inputs are possibly “core” or “mcf,” deputy of Google’s Core Reporting API and Multi-Channel Funnels API respectively. Core is a default and will offer many of your needs!
View (Profile) ID
The Analytics perspective that your news will lift information from. You can find your perspective ID in a Analytics interface, underneath a Admin tab.
Start / End Date
Used otherwise with Last N Days (i.e we contingency use accurately one), allows we to mention a operation of information to lift from.
2/1/2016 – 2/31/2016
Last N Days
Used otherwise with Start / End Date (i.e we contingency use accurately one), pulls information from a final N days from a stream date. Counts retrograde from a stream date.
Metrics we wish to pull. You can embody mixed metrics per report. Documentation on Metrics and magnitude can be found in Google’s Metrics Dimensions Explorer
Dimensions we wish your metrics to be segmented by. You can embody mixed magnitude per report. Documentation on metrics and magnitude can be found here.
Specifies an sequence to lapse your information by, can be used to classify information before generating a report. Note: we can usually arrange by metrics/dimensions that are enclosed in your report.
Filter a information enclosed in your news formed on any dimension (not usually those enclosed in a report).
Use segments from a categorical stating interface.
Directs a turn of sampling for a information you’re pulling. Analytics samples information by default, though a appendage can boost a pointing of sampling usage.
Shows formula starting from a stream index (default = 1, not 0). For use with Max Results, when we wish to collect paginated information (e.g if you’re pulling 2,000 results, and wish to get formula 1,001 – 2,000).
Default is 1,000, can be lifted to 10,000.
Integer adult to 10,000
Sends your information to another spreadsheet.
URL for piece where we wish information to be sent
By regulating these parameters in concert, we can arrive during a customized news detailing accurately what we want. The best partial is, once you’ve set adult a news in your pattern piece and reliable a outlay is what we want, all we have to do to run it again is run your reports in a add-on! This creates unchanging stating a breeze, while still bringing all a advantages of Sheets to bear.
Some critical things to note and consider, when you’re environment adult your pattern sheet:
- You can embody mixed news configurations in a a piece (see below):
In a picture above, regulating a news pattern will furnish 4 apart reports. You should NOT have one pattern piece per report.
- Although we can have your reports generated in a same workbook as your pattern sheet, we suggest duplicating a information into another workbook or regulating a Spreadsheet URL parameter to do a same thing. Loading mixed reports in one workbook can emanate opening problems.
- You can news your stating to run automatically by enabling scheduled stating within a Google Analytics add-on. Note: this is usually useful if we are regulating “Last N Days” for your time parameter. If you’re regulating a date range, your news will usually give we a same information for that operation any month.
The rule options are hourly, daily, weekly, and monthly.
4. Creating an programmed report: A worked example
So now that we’ve installed, set up, and configured a report, subsequent adult is a large fish, a dream of anyone who’s had to do unchanging reporting: automation.
As an SEO, we use a Google Analytics appendage for this accurate purpose for many of my clients. I’ll start by presumption you’ve commissioned and set adult a add-on, and are prepared to emanate a tradition news configuration.
Step one: Outline a framework
Before we start formulating a report, it’s critical we know what we wish to magnitude and how we wish to magnitude it. For this example, let’s contend we wish to perspective organic trade to a specific set of pages on a site from Chrome browsers and that we wish to investigate a trade month-over-month and year-over-year.
Step two: Understand your horizon within a add-on
To get all we want, we’ll use 3 apart reports: organic trade in a past month (January 2016), organic trade in a month before that (December 2015), and organic trade in a past month, final year (January 2015). It’s probable to embody this all in one report, though we suggest formulating one news per date period, as it creates organizing your information and troubleshooting your pattern significantly easier.
Step three: Map your pivotal elements to appendage parameters
Report One parameter breakdown:
Report Name – 1/1/2016
- Make it simply discernible from a other reports we’ll be running
Type – core
View (Profile) ID
- The comment we wish to lift information from
Start Date – 1/1/2016
- The commencement date we wish to lift information from
End Date – 1/31/2016
- The cutoff date for a information we wish to pull
Metrics – ga:sessions
- We wish to investigate sessions for this report
Dimensions – ga:date
- Allows us to see trade a site perceived any day in a specified range
Filters – ga:medium==organic;ga:landingpagepath=@resources
- We’ve enclosed dual filters, one that specifies usually organic trade and another that specifies sessions that had a alighting page with “resources” in a URL (resources is a subdirectory on Distilled’s website that houses a editorial content)
- Properly stuffing out filters and segments requires specific syntax, that we can find on Google’s Core Reporting API resources.
Segments – sessions::condition::ga:browser==Chrome
- Specifies that we usually wish event information from Chrome browsers
Sampling Level – HIGHER_PRECISION
- Specifies that we wish to minimize sampling for this information set
Report One output: Past month’s sessions
Now that we’ve set adult a report, it’s time to run it and check a results.
So, in a month of Jan 2016, a resources territory on Distilled’s website saw 10,365 sessions that confident a following conditions:
- organic source/medium
- landing page containing “resources”
- Chrome browser
But how do we know this is accurate? It’s unfit to tell during face value, though we can reliably check correctness of a news by looking during a equivalent perspective in Google Analytics itself.
Confirming Report One data
Since a Google Analytics appendage is an analogue to what we find on analytics.google.com, in your account, we can mix apart pieces in GA to grasp a same outcome as a report:
Landing Page Path Browser
Now that we’ve reliable a horizon works, and is display us what we want, formulating a other dual reports can be finished by simply duplicating a pattern and creation teenager adjustments to a parameters.
Since we wish a month-over-month comparison and a year-over-year comparison for a accurate same data, all we have to do is change a date operation for a dual reports.
One should fact a month before (December 2015) and a other should fact a same month in a prior year (January 2015). We can run these reports immediately.
Total Sessions In Jan 2015 (Reporting Month, Previous Year: 2,608
Total Sessions In Dec 2015 (Previous Month): 7,765
Total Sessions In Jan 2016 (Reporting Month): 10,365
We’re adult 33% month-over-month and 297% year-over-year. Not bad!
Every month, we can refurbish a dates in a configuration. For example, subsequent month we’ll be examining Feb 2016, compared to Jan 2016 and Feb 2015. Constructing a dashboard can be finished in Sheets, as well, by formulating an additional piece that references a outputs from your reports!
5. Closing observations and pitfalls to avoid
The Google Analytics appendage substantially isn’t a ideal stating resolution that all digital marketers crave for. When we initial detected a Google Analytics appendage for Google Sheets, we was intimidated by a use of Regular Expressions and suspicion that we indispensable to be a syntax theorist to make full use of a tool. Since then, we haven’t turn any improved during Regular Expressions, though I’ve come to comprehend that a Google Analytics appendage is versatile adequate that it can supplement value to many stating processes, though a need for low technical fluency.
I was means to cobble together any of a reports we indispensable by testing, breaking, and researching opposite combinations of segments, filters, and frameworks and we inspire we to do a same! You’ll many expected be means to arrive during a accurate news we need, given adequate time and patience.
One final thing to note: a Google Analytics interface (i.e what we use when we entrance your analytics comment online) has built-in safeguards to guarantee that a information we see matches a stating turn you’ve chosen. For example, if we click into a session-level news (e.g alighting pages), I’ll see mostly session-level metrics. Similarly, clicking into a page-level news will lapse page-level metrics. In a Google Analytics add-on, however, this guarantee doesn’t exist due to a appendage being designed for larger versatility. It’s therefore all a some-more critical that you’re consummate in outlining, designing, and building your stating horizon within a add-on. After you’ve configured a tradition news and successfully run it, be certain to check your formula opposite a Google Analytics interface!
Abraham Lincoln famously said, “Give me 6 hours to clout down a tree and we will spend a initial 4 heightening a axe.” Good recommendation in ubiquitous that also binds loyal for regulating a Google Analytics appendage for Google Sheets.
Supplementary apparatus appendix:
- RegExr – General Regular Expressions resource.
- Debuggex – Visual Regular Expressions debugging tool.