Building visual reporting in Google Sheets
In case you didn’t already know, Google Sheets has an incredible Google Analytics add-on that can be utilized to create customized studies in Google Sheets. For more info at the reporting features of this device, learn this blog post from 2016, which will even educate you the best way to obtain the add-on and arrange a customized record configuration. As an outline, this add-on permits you to:
- Temporarily pull any knowledge from Google Analytics (GA) accounts you have got get entry to to without delay right into a spreadsheet
- Simply examine ancient knowledge throughout customized time sessions
- Filter out and section your knowledge without delay inside of Google Sheets
- Automate common reporting
- Simply tweak your present studies (which might be stored to Google Pressure) to get new knowledge
Past the best way to use the device – we’ve got loose stuff!
The entire tons of information you’ll be able to pull with this device are helpful, however what if you wish to briefly have the ability to examine knowledge out of your customized record configurations? Wouldn’t it’s great to have a reporting view that visually presentations how your website online is appearing week-on-week (WoW) or year-on-year (YoY) by way of evaluating the choice of natural classes and orders (and is near-automated)?
I assumed so too.
Not too long ago, I constructed a record the usage of the Google Analytics add-on inside of Google Sheets. I’ve created a templated model of this report back to proportion with you. Be at liberty to keep a copy of it and use it as you please.
Get started developing your individual Google Sheets studies.
Listed below are one of the crucial insights this record will provide you with:
- Natural classes and orders WoW and YoY for all the website online
- Natural classes and orders WoW and YoY for various web page sorts together with class, content material, product and seasonal pages
- Natural classes and orders WoW and YoY for the homepage and a static most sensible 20 pages
- Natural classes and orders WoW and YoY to your cell website online
The usage of formulation, some common expressions, and conditional formatting, their weekly search engine optimization reporting procedure is now just about automatic the usage of knowledge from their Google Analytics.
Wait, can’t I do all of this within the GA interface already?
Now not fairly. Listed below are one of the crucial advantages of this add-on over the usual GA interface:
- Within the add-on, you’ll be able to filter out on dimensions or metrics that don’t seem to be already incorporated for your record. Within the GA interface in the event you’re taking a look at a record with touchdown pages as the one measurement, you’ll be able to’t use filters to filter out to simply natural site visitors. With the add-on, you’ll be able to
- You’ll do that in GA the usage of a section moderately than a filter out, however segments are extra vulnerable to inflicting problems with sampling than filters
- After getting loaded for your knowledge with the add-on, you’ll be able to manipulate it with no need to repeatedly export information
- You’ll do more than one comparisons with the add-on (which I do on this record), while in GA you’ll be able to best do two, i.e. both 12 months on 12 months or week on week comparisons, now not each concurrently
- The usage of the add-on can give you a unmarried supply of reality, moderately than having all of the further knowledge introduced by way of GA that you could now not wish to be diving into
- The usage of conditional formatting in Google Sheets implies that I’ve been in a position to flag various levels of certain or unfavorable adjustments by way of color
How you’ll be able to make this record your individual
This weblog submit will stroll you via why the record comes in handy, how you’ll be able to customize it, after which in the event you’re curious, I’ve additionally long past into additional element in an appendix under how the record works. This may also be helpful for any attainable de-bugging you’ll have to do.
With the next directions, even supposing you’re a amateur to such things as common expressions and Excel/Google Sheets formulation, you will have to nonetheless have the ability to customize the record and use it your self.
To construct the record I’ve used common expressions inside the reporting configuration to filter out to precise web page sorts that I sought after, after which within the “Comparisons” sheet, I’ve used formulation to tug the knowledge from the sheets to then get WoW and YoY comparisons. All of this takes position inside the sole Google Sheet.
For a one-time record, this might most likely now not be well worth the time invested, however in the event you or a consumer have a necessity for standardised, ongoing reporting – and you have got get entry to to the GA knowledge for the account – then this template will also be a good way to provide you with fast, simple perception into your natural site visitors traits.
It saves me as regards to an hour of time every week – or virtually 6 running days a 12 months.
What it is very important customize this record
To make this record your individual, you’ll want:
- To obtain the Google Analytics add-on for Google Sheets (directions are here)
- Get admission to to the GA account you wish to have to record on
- Your GA View ID (directions on the best way to in finding this here)
Different assets you might in finding helpful:
- GA’s Query Explorer – can be utilized to check the output of various mixtures of metric and measurement filters
- GA Reporting API – lists and describes all of the dimensions and metrics to be had during the Core Reporting API
Why this record comes in handy
This record makes use of conditional formatting to make any important certain or unfavorable adjustments stand out. It additionally makes use of each macro-scale perspectives of the website online traits and extra detailed perspectives. On the most sensible, it has the full classes and orders for all the website online, plus the WoW and YoY comparisons, and as you cross additional down the record it turns into extra granular.
I constructed this record so I may get a greater thought of ways explicit portions of a website online have been appearing. The main pages I’ve reported on are the class pages, content material pages, product pages and cell pages. Following that, I’ve put within the knowledge for a listing of the highest 20 pages, together with the house web page. On the finish, there’s a phase for seasonal pages.
The highest 20 pages that it studies on are static. Those have been pre-determined by way of taking a look at the ones pages that persistently had the easiest natural classes. We opted to make use of a static most sensible 20 moderately than the true most sensible 20 by way of classes a week as a result of the usage of the true will require updating the SUMIF formulation every time the record was once run.
This can be a record that I replace weekly. The date formulation are calculated in response to no matter as of late’s date is and are then used within the record configuration.
This permits the dates to replace on their very own with out me manually having to switch them every time I wish to run the record. I simply needed to then time table the report back to run weekly and BAM! – no fingers essential.
To set an auto-run to your record, cross to Upload-ons > Google Analytics > Time table Reviews, test “Allow studies to run robotically” after which set the time and frequency you wish to have your report back to run.
This has made my existence a lot more straightforward, and with a bit of luck sharing it’ll make your existence more straightforward too.
How one can customize this record
On this record you’re going to have to customize:
- Your record configurations
- The dates you wish to have this to run
- The main web page sorts you wish to have to match (we’ve got class pages, content material pages, product pages and cell)
- The highest 20 pages you need to record on (you may select to not use this)
- Your seasonal pages, if acceptable
Essential adjustments – Document Configurations
First degree customisation
To learn to arrange and run record configurations, the weblog submit I referenced originally will let you. For the aim of this submit, I’ll simply focal point on the place you’ll wish to tweak it to your website online or consumer.
It is very important put within the View ID you wish to have to record on (Row three), and you’ll have to edit the common expressions within the filters phase (Row nine) to lead them to distinctive in your consumer, which I can quilt under.
The dates I’m the usage of (Rows Four-Five) are references to these I’ve within the comparisons tab. If you wish to use other dates, you’ll be able to both manually exchange them right here, or within the subsequent phase, I provide an explanation for how the date formulation paintings. Right here’s a screenshot of the system in mobile B4 so you’ll be able to see what I’m speaking about:
You are also most likely going to wish to exchange the Document Identify (Row 2) for every column. In case you do, make sure that you obviously label every phase. The Document Identify turns into the title of the sheet this is generated when you run the record, and later the title used within the formulation within the Comparisons tab.
Observe that whilst you exchange the Document Identify, it received’t substitute the outdated one however will as an alternative simply create a brand new one. You’ll need to manually delete the outdated, undesirable sheets.
Updating the common expressions
There are two portions of the common expression which might be distinctive to the website online that you’re going to need to replace.
The primary phase that you just’ll need to replace is the place I needed to clear out PPC knowledge that was once being mistakenly reported as natural by way of GA. For this web site, PPC knowledge may well be recognized with any URL that contained both “gclid”, “cm_mmc”, “newsl”, or “google” – that is more likely to be other for you, so exchange what’s within the quotations to mirror this.
This was once being reported as natural on account of the tagging used for PPC knowledge was once to start with supposed for a special reporting platform, so it is probably not an issue for you. If that is so, you’ll be able to delete this phase.
If this isn’t an issue for you, then you’ll be able to cross forward and delete this a part of the Filters phase (the whole lot following ga:medium==natural in cells B9-D9 of the Document Configuration tab).
The second one replace you’ll need to make to the common expressions are to these used within the Filter out sections for all of the columns with the exception of the ‘The whole lot’ ones (cells E9-P9). Those are used to spot the a part of the URL trail you wish to have to filter out on.
Each and every filter out is separated by way of a semi-colon, so if you wish to upload the rest to those filters make sure to have that during there. Semi-colons imply “AND” within the Core Reporting API. For commas, you employ “OR”.
Here’s the ‘the whole lot’ phase:
Except for ga:medium==natural (which simply filters to natural classes best) this simply filters out PPC knowledge.
I’ve copied this expression throughout all of my sections, however for the sections on explicit web page sorts I’ve additionally incorporated any other common expression to get the precise URLs I’m searching for, highlighted under. For those sections, you’ll see diversifications of this:
For this situation, it was once filtering for URLs containing “/class/”. This filters that record down to simply our consumer’s class pages. Once more, you’ll be able to customize this common expression in your distinctive website online or consumer. Be sure you get away any slashes you employ on this phase with a backslash.
The cell sections (cells N9-P9) have been a little other, as this can be a outlined measurement in GA. You’ll see in the ones columns that I simply added in “ga:deviceCategory==cell” after the filter out for natural.
As soon as all this is executed you’ll be able to run your studies and transfer directly to customising your Comparisons tab.
Essential adjustments – Comparisons tab
The date formulation in cells M13:S18 additional automate the reporting. The record defines every week as Monday to Sunday as this was once how our consumer outlined theirs, so if that is other for you, you’ll have to switch it. In case you’re curious how those explicit formulation paintings, I’ve coated it in additional element within the appendix.
In case you do exchange this phase, be sure that the dates are formatted as YYYY-MM-DD. To do that, cross to Structure > Quantity > Extra codecs > Extra date and time codecs.
I’ve additionally left area to go into customized get started and finish dates. The particular consumer this was once constructed for sought after so that you can examine strange weeks for his or her YoY comparisons round explicit vacations. Those dates will best be used if cells N16-S16 don’t seem to be clean.
Google Sheets formulation – for number one, most sensible 20 and seasonal pages
While you’re pleased with the dates, the main factor you want to replace are formulation, particularly the names of the sheets being referenced and the factors that outline the pages you wish to have to record on.
If you’re getting mistakes whilst you customize the formulation, particularly #N/A! mistakes, take a look at re-running the cells within the comparisons sheet first by way of simply highlighting and urgent input.
For the main pages on the most sensible in cells B6:Okay10, when you have modified the Document Names from the former phase you best need to replace the sheet names being referenced. You’ll even have to try this for the next sections.
While you’re doing this, ensure to not combine up earlier week and former 12 months.
It is a lengthy and worsening procedure. Something I discovered that helped velocity it up was once any other Google Sheets add-on Advanced Find and Replace. This permits you to use the in finding and substitute serve as inside of formulation, which means that you’ll be able to merely in finding “The whole lot present week – UK” and substitute it with no matter selection you have got.
This plug in has a loose trial, and as soon as this is up you’ll be able to best use it as soon as an afternoon – so take advantage of it whilst you have it! If you recognize of some other loose choices, I’d love to listen to about them.
The formulation within the most sensible 20 pages, cells B13:Okay24, have relatively other formulation are other relying at the web page kind.
The place I’ve highlighted within the system under is the a part of this system you’ll have to switch to compare your explicit web page kind. That is from mobile B14:
=SUMIF(‘The whole lot present week – UK’!$A:$A,”*”&”/most sensible web page 2/“,’The whole lot present week – UK’!$B:$B)
The quantity you’re seeing is a sum of all of the pages with /most sensible web page 2/ within the URL from the Effects Breakdown in my The whole lot present week – UK tab, proven under.
For the seasonal phase in cells B34:Okay35, you’ll simply have to switch the place I’ve both “christmas” or “black-friday” to incorporate no matter explicit seasonal time period you wish to have to record on. Be mindful, this will have to be a reference this is incorporated within the URLs.
Different adjustments you’ll be able to make – Document Configuration
For metrics, I’ve used classes and transactions, however this will also be adjusted if there’s a other metric you need to record on. Simply make sure to exchange the headings within the comparability tab so that you keep in mind what you’re reporting on.
For dimensions, I’ve used the touchdown pages. Once more, you’ll be able to alter this if you want to, as an example, record on key phrases as an alternative.
I’ve set the order to be in descending moderately than ascending. This organises the knowledge but in addition helped to decide the highest 20 pages.
I’ve set the bounds on those to at least one,000. I did this as a result of I best truly cared in regards to the explicit knowledge for the highest pages. The prohibit does now not exchange the full quantity this is reported, it simply limits the choice of rows.
Sadly, this could also be the place I’ve to speak about sampling. In my record tabs in cells A6 and B6 it says “Accommodates Sampled Information, No”. In case your knowledge is being extremely sampled then you want to come to a decision if that might be a roadblock for you or now not.
Here is a useful resource with many ways to get round sampled knowledge.
It’s reporting time
In case you’ve made the above adjustments, when you run your studies with the updates in your Document Configuration, you will have a Google Sheet reporting to your explicit knowledge.
That was once a large number of data, so when you have any questions or want any assist on a selected a part of this procedure please remark under!
As promised, I’ve added an appendix to this submit under for the ones of you which might be curious to grasp in additional element the way it works.
Appendix: How this record works, in the event you’re curious
Major record formulation
Totals, WoW and YoY for most sensible record phase
Columns B and G for the highest phase merely pull out no matter quantity is reported for the full classes and general orders from every sheet. This comes in handy now not best as it brings all of the absolute numbers into one position, but in addition as a result of I will now reference those cells in formulation.
For WoW relative (Column C), I’ve once more referenced those self same cells, however created a proportion with a (Present – Earlier)/Earlier system.
Column D makes use of the odds generated in Column C to extract absolutely the quantity variations.
For YoY relative (Column E), I’ve adopted the similar precise approach, simply referencing the knowledge for the former 12 months moderately than the former week. Once more, I used those numbers to extract out absolutely the numbers observed in Column F.
The gray orders phase does the very same factor, however as an alternative references the mobile in every respective configuration with the order general, moderately than classes.
I’ve additionally wrapped those formulation in IFERRORs, to stop the sheet from having any error messages. This was once essentially for aesthetics, even if it’s price noting that now and again this may lend to it announcing there was once a nil% exchange, when perhaps there was once a 100% building up as that web page kind didn’t exist within the earlier 12 months.
Our consumer sought after weekly reporting evaluating weeks that run from Monday to Sunday as this was once how our consumer outlined theirs. Since GA weeks run from Sunday to Saturday, this needed to be customised.
Those dates are calculated based totally off the “=TODAY()” date in mobile M14, in addition to the primary day of closing 12 months calculated in M16, the primary Monday of closing 12 months in M18, and the week numbers in cells O12 and Q12.
As a result of those dates are calculated robotically right here, within the Document Configuration tab I will merely reference the precise cells from my Comparisons sheet, moderately than manually having to go into the dates every time I run the record. This additionally made it so I will set this report back to run robotically each Monday morning earlier than I am getting into the place of work.
You’ll additionally realize that under the dates I’ve left area to go into customized get started and finish dates, that is once more since the explicit consumer this was once constructed for now and again desires to match strange weeks for his or her YoY comparisons to account for explicit vacations.
Within the Document Configuration sheet, I’ve an IF system within the cells that claims, if the customized cells are clean then use the standard date, if they don’t seem to be, then use the ones. On the ones events, it does imply I’ve to manually run the studies, however I assume you’ll be able to’t have the whole lot.
Best 20-page reporting
The Best 20-page phase is the place the formulation get a little beastly, however this was once one thing the buyer particularly asked. We to start with sought after it to record at the most sensible 20 pages from every week, however that wasn’t conceivable the usage of formulation, as we would have liked one thing static to reference.
For those, I used a SUMIF system. For instance, in mobile C13 I’ve this system to record the WoW relative quantity for the house web page:
=IFERROR((SUMIF('The whole lot present week - UK'!A:A,"*"&".co.united kingdom/",'The whole lot present week - UK'!B:B)-SUMIF('The whole lot earlier week - UK'!A:A,"*"&".co.united kingdom/",'The whole lot earlier week - UK'!B:B))/SUMIF('The whole lot earlier week - UK'!A:A,"*"&".co.united kingdom/",'The whole lot earlier week - UK'!B:B),zero)
Once more, the IFERROR commentary wrapped round my system is simply to scrub issues up so we could drop that and smash down what the remainder of this system is doing.
=(SUMIF('The whole lot present week - UK'!A:A,"*"&".co.united kingdom/",'The whole lot present week - UK'!B:B)-SUMIF('The whole lot earlier week - UK'!A:A,"*"&".co.united kingdom/",'The whole lot earlier week - UK'!B:B))/SUMIF('The whole lot earlier week - UK'!A:A,"*"&".co.united kingdom/",'The whole lot earlier week - UK'!B:B)
The SUMIF system sums up cells in the event that they meet explicit standards. It really works by way of defining the variability, on this case ‘The whole lot present week – UK’!A:A (each row in column A of the sheet The whole lot present week – UK), after which the factors that you wish to have to be summed. Right here, it’s all cells which come with the rest and finish with “.co.united kingdom/”.
Finally, you outline the sum vary, which is the variability to be summed whether it is other from the unique vary outlined. We’ve used this right here as a result of we wish the sum of all of the classes, now not the touchdown web page paths. That complete factor spits out the sum of all of the classes at the homepage for the present week. I’ve then subtracted from that quantity the sum of all of the classes for the former week.
In any case, I’ve divided it by way of the sum of all of the classes of the former week to get the % exchange.I set formatting laws in those cells to layout the numbers as a proportion, however it’s worthwhile to additionally simply upload that the system to multiply by way of 100. So inside of those cells there are two issues you’re going to have to customize (1) the names of the sheets being referenced, and (2) the factors that outline the pages that you wish to have to record on. You’ll realize that within the most sensible 20 pages, those are other relying at the web page kind (they’ve been deliberately modified for discretion).