Prompted by a tweet
— Ryan Praskievicz (@ryanpraski) February 16, 2017
I set myself a challenge of utilsing Google Sheets and the Google Analytics API to show scroll depth for a group of pages. Here is my step by step guide.
Step 1: Track Scroll
This then fires events into google analytics as follows:
- Event Category: Scroll Depth
- Event Action: Percentage
- Event Label: Baseline, 25%, 50%, 75% or 100%
Step 2: Create a report in google sheets
Using the Google Analytics API add on, I have to create a 5 reports for each possible event label. Each consists of the same configuration options, with just a tweak to the filters.
|View (Profile) ID / ids||ENTER YOUR ID|
|Start Date||PICK START DATE|
|End Date||PICK END DATE|
|Last N Days|
You will see I have 2 extra filters. One to show just news pages based on a “regex match” operator (ga:pagePath=~^/about-us/news/) and also a do not show substring operator containing “id=” (ga:pagePath!@id=). This gets me just the right pages to compare. For more info on filter operators check out the google developer site.
Also worth a mention to GDS blog for helpful tips on selecting a date using relative date formulas.
Now repeat this report, changing the last filter and also give it a new report name.
NB I originally used 25%… as a report name, but that played havoc with the tab name in google sheets. So used 25percent…
Run the report
Step 3: Bring the data together
Create a new sheet and give it a meaningful name. Add headings Page path, Baseline, 25%, 50%, 75%, 100%
In cell A2, simply use an array to pull in the baseline data
NB 500 was used to cap the storys in my report but you can add more
Repeat for B2
This will pull in the corresponding unique events
For C2, D2, E2 and F2 you need to use a vlookup to match the url from a2 and pull in the matching unique event.
=iferror(VLOOKUP(A2, ’25percent’!A$15:C$500, 3, 0))
=iferror(VLOOKUP(A2, ’50percent’!A$15:C$500, 3, 0))
=iferror(VLOOKUP(A2, ’75percent’!A$15:C$500, 3, 0))
=iferror(VLOOKUP(A2, ‘100percent’!A$15:C$500, 3, 0))
NB I wrap the vlookup with an iferror to clear the N/A to a blank.
Select the cells with rules and pull the cells down to fill the rest of the rows.
What you are left with is a table of urls based on diminishing unique events. This you can take to Step4 or if your data is like mine (biased by one page) you can add some more cells that just give you a percentage drop out.
So in columns H-L i recreate the titles (Baseline, 25%, 50%, 75%, 100%) and then do some simple sums in row 2, formatting them to a percentage.
Step 4: Conditional formatting
Google sheets makes this a very simple task. Select the data to format. Go to Format and Conditional Formatting in the menu. A new rule box appears on the right. I chose the 2nd tab “color scale”. Picked the white to red, which gives the smallest value white and the largest red. And click done.
Below are my 2 examples (one just based on unique events, the other on a percentage calculation.
Here is my test google sheet if people want to look.
Now I release there are more visual heat map tools, which I also advocate. These are great for research workshops or senior stakeholder presentations. There visual, they connect instantly. Please use them.
The reason I have used google sheets is for a different audience. The beauty of google sheets is the fact it relays data both timely (auto schedules) but also in a format that you control (the language, the look…) The audience doesn’t need to understand google analytics or its terminology, they just need to understand your google sheets report.
By providing heat maps in this format for highly valuable pages (news pages, product pages, list pages) you can see instant insight. Now “read” is a strong word, just because someone scrolls does not mean they read content, but knowing that someone scrolled, even past the baseline means someone was vaguely interested.
Now to crank this up a level, and what I do for LJMU is swap the scroll event funnel for engagement funnel.
So rather than the use of a filter per report, I use segments, one for all traffic (baseline), one for no bounce, one for engagement (a collection of various events) and one for the main CTA.
Please let me know your thoughts.