Posted by tian_wang
With in the present day’s weblog publish I’m sharing every little thing one must find out about an underappreciated instrument: the Google Analytics add-on for Google Sheets. On this put up I’ll be protecting the next:
1. What’s the Google Analytics add-on?
2. The right way to set up and arrange the Google Analytics add-on.
three. How you can create a customized Report with the Google Analytics add-on.
four. A step-by-step labored instance of organising an automatic report.
5. Additional issues and pitfalls to keep away from.
Because of Moz for having me, and for giving me the possibility to jot down about this easy and highly effective instrument!
1. What’s the Google Analytics add-on and why ought to I care?
I’m glad I requested. Merely put, the Google Analytics add-on is an extension for Google Sheets that lets you create customized reviews inside Sheets. The add-on works by linking as much as an current Analytics account, utilizing Google’s Analytics API and Common Expressions to filter the information you need to pull, and at last gathering the information into a simple and intuitive format that’s ripe for reporting.
The Google Analytics add-on’s actual worth-add to a reporting workflow is that it’s extraordinarily versatile, dependable, and an actual time-saver. Your reporting will nonetheless be constrained by the constraints of Sheets itself (as in comparison with, say, Excel), however the Sheets framework has served virtually each reporting want I’ve come throughout so far and the identical will in all probability be true for many of you!
In a nutshell, the Add-On permits you to:
- Pull any knowledge that you simply’d have the ability to entry within the Analytics API (i.e analytics.google.com) instantly right into a spreadsheet
- Simply evaluate historic knowledge throughout time durations
- Filter and phase your knowledge
- Automate common reporting
- Make tweaks to present stories to get new information (no extra re-inventing wheels!)
If this all appears like you can use it, learn on!
2. Getting began: Easy methods to set up and arrange the Google Analytics add-on
2A. Putting in the Google Analytics add-on
- Go into Google Sheets.
- On the header bar, underneath your Workbook’s title, click on add-on.
- This opens a drop-down menu — click on “Get add-ons.”
- Within the following window, kind “Google Analytics” into the search bar on the highest proper and hit enter.
- The primary result’s the add-on we wish, so go forward and set up it.
- Refresh your web page and ensure the add-on is put in by clicking “Add-ons” once more. It’s best to see an choice for “Google Analytics.”
That’s all there’s to set up!
2B. Establishing the Google Analytics add-on
Now that now we have the Google Analytics add-on put in, we have to set it up by linking it to an Analytics account earlier than we are able to use it.
- Beneath the “Add-ons” tab in Sheets, hover “Google Analytics” to reveal a facet-bar as proven under.
- Click on “Create New Report.” You’ll see a menu seem on the best aspect of your display screen.
- On this menu, set the account info to the Analytics account you need to measure.
- Fill out the metrics and dimensions you need to analyze. You’ll be able to additional customise segmentation inside the report itself later, so simply select a easy set for now.
- Click on “Create Report.” The output will likely be a brand new sheet, with a report configuration that appears like this:
- Be aware: That is NOT your report. That is the setup configuration so that you can let the add-on know precisely what info you’d wish to see within the report.
When you’ve arrived at this step, your set-up part is finished!
Subsequent we’ll take a look at what these parameters imply, and the way to customise them to tailor the information you obtain.
three. Making a customized report with the Google Analytics add-on
So now you might have all these bizarre packing containers and also you’re most likely questioning what you could fill out and what you don’t.
Earlier than we get into that, let’s check out what occurs when you don’t fill out something further, and simply run the report from right here.
To run a configured report, click on again into the “Add-Ons” menu and go to Google Analytics. From there, click on “Run Reviews.” Be sure to have your configuration sheet open whenever you do that!
You’ll get a notification that the report was both efficiently created, or that one thing went unsuitable (this would possibly require some troubleshooting).
Following the instance above, your output will look one thing like this:
That is your precise report. Hooray! So what are we really seeing? Let’s return to the “Report Configuration” sheet to seek out out.
The report configuration:
Sort and View ID are defaults that don’t must be modified. Report Identify is what you need your report back to be referred to as, and would be the title generated for the report sheet created once you run your experiences.
So actually, within the report configuration above, all of the enter we’re seeing is:
- Final N Days = 7
- Metrics = ga:customers
In different phrases, this report reveals the full variety of periods within the specified View ID during the last week. Fascinating perhaps, however not that useful. Let’s see what occurs if we make a number of modifications.
I’ve modified Final N Days from 7 to 30, and added Date as a Dimension. Working the report once more yields the next output:
By growing the vary of knowledge pulled from final 7 to 30 days, we get an information from a bigger set of days. By including date as a dimension, we will see how a lot visitors the location registered every day.
That is solely scratching the floor of what the Google Analytics add-on can do. Right here’s a breakdown of the parameters, and the best way to use them:
|Parameter Title||Required?||Description & Notes||Instance Worth(s)|
|Report Title||No||The title of your report. This would be the title of the report sheet that is generated once you run experiences. When you’re working a number of reviews, and need to exclude one with out deleting its configuration setup, delete the report title and the column will likely be ignored subsequent time you run your stories.||“January Natural Site visitors”|
|Kind||No||Inputs are both “core” or “mcf,” consultant of Google’s Core Reporting API and Multi-Channel Funnels API respectively. Core is the default and can serve most of your wants!||“core”
|View (Profile) ID||Sure||The Analytics view that your report will pull information from. You could find your view ID within the Analytics interface, underneath the Admin tab.||ga:12345678|
|Begin / Finish Date||No||Used alternatively with Final N Days (i.e it’s essential to use precisely one), lets you specify a variety of information to tug from.||2/1/2016 – 2/31/2016|
|Final N Days||No||Used alternatively with Begin / Finish Date (i.e you could use precisely one), pulls knowledge from the final N days from the present date. Counts backwards from the present date.||Any integer|
|Metrics||Sure||Metrics you need to pull. You possibly can embrace a number of metrics per report. Documentation on Metrics and dimensions may be present in Google’s Metrics & Dimensions Explorer||“ga:classes”|
|Dimensions||No||Dimensions you need your metrics to be segmented by. You possibly can embrace a number of dimensions per report. Documentation on metrics and dimensions could be discovered right here.||“ga:date”|
|Type||No||Specifies an order to return your information by, can be utilized to prepare knowledge earlier than producing a report. Be aware: you’ll be able to solely type by metrics/dimensions which are included in your report.||“type=ga:browser,
|Filters||No||Filter the info included in your report based mostly on any dimension (not simply these included within the report).||“ga:nation==Japan;
|Phase||No||Use segments from the primary reporting interface.||“customers::situation::
|Sampling Degree||No||Directs the extent of sampling for the info you’re pulling. Analytics samples information by default, however the add-on can improve the precision of sampling utilization.||“HIGHER_PRECISION”|
|Begin Index||No||Exhibits outcomes ranging from the present index (default = 1, not zero). To be used with Max Outcomes, while you wish to retrieve paginated knowledge (e.g in case you’re pulling 2,000 outcomes, and wish to get outcomes 1,001 – 2,000).||Integer|
|Max Outcomes||No||Default is 1,000, could be raised to 10,000.||Integer as much as 10,000|
|Spreadsheet URL||No||Sends your information to a different spreadsheet.||URL for sheet the place you need knowledge to be despatched|
Through the use of these parameters in live performance, you’ll be able to arrive at a custom-made report detailing precisely what you need. The most effective half is, when you’ve arrange a report in your configuration sheet and confirmed the output is what you need, all it’s important to do to run it once more is run your reviews within the add-on! This makes common reporting a breeze, whereas nonetheless bringing all the advantages of Sheets to bear.
Some vital issues to notice and think about, whenever you’re establishing your configuration sheet:
- You’ll be able to embrace a number of report configurations within the the sheet (see under):
Within the picture above, operating the report configuration will produce 4 separate stories. It’s best to NOT have one configuration sheet per report.
- Though you possibly can have your experiences generated in the identical workbook as your configuration sheet, I like to recommend copying the info into one other workbook or utilizing the Spreadsheet URL parameter to do the identical factor. Loading a number of studies in a single workbook can create efficiency issues.
- You’ll be able to schedule your reporting to run robotically by enabling scheduled reporting throughout the Google Analytics add-on. Observe: that is solely useful if you’re utilizing “Final N Days” in your time parameter. Should you’re utilizing a date vary, your report will simply provide the identical knowledge for that vary each month.
The regularity choices are hourly, day by day, weekly, and month-to-month.
four. Creating an automatic report: A labored instance
So now that we’ve put in, arrange, and configured a report, subsequent up is the massive fish, the dream of anybody who’s needed to do common reporting: automation.
As an web optimization, I exploit the Google Analytics add-on for this actual goal for a lot of of my shoppers. I’ll begin by assuming you’ve put in and arrange the add-on, and are able to create a customized report configuration.
The first step: Define a framework
Earlier than we start creating our report, it’s necessary we perceive what we need to measure and the way we wish to measure it. For this instance, let’s say we wish to view natural site visitors to a particular set of pages on our website from Chrome browsers and that we need to analyze the site visitors month-over-month and yr-over-12 months.
Step two: Perceive your framework throughout the add-on
To get every thing we would like, we’ll use three separate studies: natural site visitors up to now month (January 2016), natural site visitors within the month earlier than that (December 2015), and natural site visitors up to now month, final 12 months (January 2015). It’s attainable to incorporate this multi functional report, however I like to recommend creating one report per date interval, because it makes organizing your knowledge and troubleshooting your configuration considerably simpler.
Step three: Map your key components so as to add-on parameters
Report One parameter breakdown:
Report Identify – 1/1/2016
- Make it simply distinguishable from the opposite stories we’ll be operating
Kind – core
- The GA API default
View (Profile) ID
- The account we need to pull knowledge from
Begin Date – 1/1/2016
- The start date we wish to pull knowledge from
Finish Date – 1/31/2016
- The cutoff date for the information we need to pull
Metrics – ga:classes
- We wish to analyze periods for this report
Dimensions – ga:date
- Permits us to see visitors the location obtained every day within the specified vary
Filters – ga:medium==natural;ga:[email protected]
- We’ve included two filters, one which specifies solely natural site visitors and one other that specifies periods that had a touchdown web page with “sources” within the URL (assets is the subdirectory on Distilled’s web site that homes our editorial content material)
- Correctly filling out filters and segments requires particular syntax, which you could find on Google’s Core Reporting API sources.
Segments – classes::situation::ga:browser==Chrome
- Specifies that we solely need session information from Chrome browsers
Sampling Stage – HIGHER_PRECISION
- Specifies that we wish to reduce sampling for this knowledge set
Report One output: Previous month’s classes
Now that we’ve arrange our report, it’s time to run it and examine the outcomes.
So, within the month of January 2016, the assets part on Distilled’s web site noticed 10,365 classes that glad the next circumstances:
- natural supply/medium
- touchdown web page containing “assets”
- Chrome browser
However how do we all know that is correct? It’s unattainable to inform at face worth, however you possibly can reliably verify accuracy of a report by wanting on the analogous view in Google Analytics itself.
Confirming Report One knowledge
Because the Google Analytics add-on is an analogue to what you discover on analytics.google.com, in your account, we will mix separate items in GA to realize the identical impact as our report:
Touchdown Web page Path & Browser
Now that we’ve confirmed our framework works, and is displaying us what we would like, creating our different two stories could be executed by merely copying the configuration and making minor changes to the parameters.
Since we would like a month-over-month comparability and a 12 months-over-yr comparability for the very same information, all now we have to do is change the date vary for the 2 stories.
One ought to element the month earlier than (December 2015) and the opposite ought to element the identical month within the earlier 12 months (January 2015). We are able to run these stories instantly.
Whole Classes In January 2015 (Reporting Month, Earlier 12 months: 2,608
Whole Periods In December 2015 (Earlier Month): 7,765
Whole Periods In January 2016 (Reporting Month): 10,365
We’re up 33% month-over-month and 297% 12 months-over-yr. Not unhealthy!
Each month, we will replace the dates within the configuration. For instance, subsequent month we’ll be analyzing February 2016, in comparison with January 2016 and February 2015. Setting up a dashboard could be achieved in Sheets, as properly, by creating an extra sheet that references the outputs out of your stories!
5. Closing observations and pitfalls to keep away from
The Google Analytics add-on in all probability isn’t the proper reporting resolution that each one digital entrepreneurs yearn for. Once I first found the Google Analytics add-on for Google Sheets, I used to be intimidated by its use of Common Expressions and thought that you simply wanted to be a syntax savant to make full use of the software. Since then, I haven’t turn into any higher at Common Expressions, however I’ve come to understand that the Google Analytics add-on is flexible sufficient that it may well add worth to most reporting processes, with out the necessity for deep technical fluency.
I used to be capable of cobble collectively every of the experiences I wanted by testing, breaking, and researching completely different combos of segments, filters, and frameworks and I encourage you to do the identical! You’ll most certainly be capable to arrive on the actual report you want, given sufficient time and persistence.
One last item to notice: the Google Analytics interface (i.e what you utilize once you entry your analytics account on-line) has constructed-in safeguards to make sure that the information you see matches the reporting stage you’ve chosen. For instance, if I click on right into a session-degree report (e.g touchdown pages), I’ll see largely session-stage metrics. Equally, clicking right into a web page-degree report will return web page-degree metrics. Within the Google Analytics add-on, nevertheless, this safeguard doesn’t exist because of the add-on being designed for higher versatility. It’s subsequently all of the extra essential that you simply’re thorough in outlining, designing, and constructing your reporting framework inside the add-on. After you’ve configured a customized report and efficiently run it, remember to examine your outcomes in opposition to the Google Analytics interface!
Abraham Lincoln famously stated, “Give me six hours to cut down a tree and I’ll spend the primary 4 sharpening the axe.” Good recommendation basically that additionally holds true for utilizing the Google Analytics add-on for Google Sheets.
Supplementary useful resource appendix:
- Kristi Hines’ Google Analytics for Learners information – See title.
- Google’s Analytics Core Reporting API Documentation – Dig round right here to learn how to construct reviews that offer you precisely what you’re searching for!
- Avinash Kaushik’s Analytics Useful resource – Discusses the Google Analytics reporting framework and the way to consider metrics and periods.
- RegExr – Basic Common Expressions useful resource.
- Debuggex – Visible Common Expressions debugging instrument.
Join The Moz High 10, a semimonthly mailer updating you on the highest ten hottest items of web optimization information, ideas, and rad hyperlinks uncovered by the Moz workforce. Consider it as your unique digest of stuff you do not have time to seek out however need to learn!
L'articolo The Google Analytics Add-On for Sheets: An Intro to an Underutilized Tool sembra essere il primo su SEO strategy.