Most businesses and independent bloggers still rely on Google Analytics for their content analytics. Google Analytics is an amazing product, especially considering that the standard version is available for free.
I get a lot of value from Google Analytics but I wanted to challenge myself to try and get more. I specifically wanted to try and analyze my content by post, something that isn't easy in GA.
Another goal of mine was to go from Google Analytics' standard reporting to a single, more inclusive visualization. To see how each individual piece of content was impacting traffic growth.
Well my friend, I managed to achieve my goal and built a content analytics dashboard which does exactly as planned. You can see the end product in the screenshot below, or by visiting the dashboard on Tableau Public.
In this post I'm going to share with you how I managed to build this content analytics dashboard and how you can use it to identify key insights about your content's performance, how your SEO efforts are impacting growth, and more.
How to analyze your content's performance using Google Analytics and Tableau
My strategy for this project was to try and move my Google Analytics data into a database which I could then plug into Tableau (check out my introduction to Tableau post).
Once I had the "raw" data in the database I could leverage Tableau's powerful formulas, parameter, filters and visualization options to build a useful dashboard.
Before we dive in I want to mention that it is important for you to understand the concepts covered in this post. You could use the same approach covered in this post to analyze your eCommerce business, SaaS business, or online advertising spend.
Step #1 - Sign up for a free Stitch account and connect Google Analytics to BigQuery
The first step is to connect your Google Analytics account to your BigQuery account via Stitch. I'm in no way affiliated with Stitch, I just like them as a service.
I've been experimenting with Stitch for a while and even use them with one of my clients to move Shopify and Facebook ads data into their data warehouse.
Stitch is an ETL tool which automatically moves data from dozens of different sources into your database of choice. The best part about Stitch is they have a very generous free plan. This makes Stitch ideal if you don't have too much data to move around.
Once you've signed up with Stitch you'll want to add BigQuery as a destination. You can read how to add BigQuery as a destination here.
The next step is to add Google Analytics as a source. This part can be tricky because you can't simply connect it and get access to everything. Google limits the number of dimensions and measures you can pull via their API.
Below is a screenshot of the fields I pulled in but in hindsight I would of changed this list to the list shown under the screenshot.
The fields I would have pulled in instead include:
Dimensions
- ga:date
- ga:userType
- ga:sourceMedium
- ga:campaign
- ga:landingPagePath
Measures
- ga:sessions
- ga:bounces
You can see a full list of the available dimensions and measures here.
Why this specific list? There is a crazy limitation in the Google Analytics API when it comes to unique users and pageviews. If you pull the data on a daily bases you will get daily unique users and daily unique pageviews. This limits you when you need to aggregate on a weekly, monthly or quarterly bases.
You'd typically have a user or visitor id to work with but Google doesn't provide that level of granularity so you're stuck. After a lot of unnecessary work I ended up focusing only on sessions which is good enough for my needs at this stage.
While adding Google Analytics as an source in Stitch you'll be asked to select a starting time and replication frequency. Go with what makes the most sense for you. I chose the start date of the blog and set the replication frequency to 6 hours.
Step #2 - Wait for the data to load and start cleanup
After everything is set up correctly Stitch will start loading the data. Stitch will load your data retroactively but note that they only do this once. This initial load is free.
You'll be able to know once data is moving from your Stitch dashboard.
If everything worked as expected you should now see aggregated Google Analytics data in your BigQuery account. Spend a bit of time exploring the data.
Unfortunately I had a ton of duplicates in y table which I removed by creating a custom view and grouping the values I cared about. I also removed all rows where the session count was zero.
The query I used to create my view can be found below.
SELECT
usertype,
medium,
_rjm_record_hash,
landingpagepath,
campaign,
source,
date,
max(sessions) as sessions,
max(bounces) as bounces,
max(users) as users,
max(pageviews) as Pageviews
FROM
`projectbi-211107.projectbi__google_analytics.report`
WHERE
(sessions) > 0
GROUP BY
1,2,3,4,5,6,7
Step #3 - Connect the view to Tableau and start building
If you've never used Tableau before then I recommend checking out the video below or this detailed Tableau guide that I published on this blog. These will get you on your way to learning one of the most powerful data visualization solutions on the market today.
You'll also want to download Tableau Desktop. You can take advantage of Tableau's 14 day free trial.
Even before creating my view in BigQuery I connected the Google Analytics table in BigQuery to Tableau. This way I could easily investigate the data and build the query for my view quicker.
Step 3.1 - Create a moving window function
The first major step that I took in Tableau was to build out a moving window function. A moving window is a special type of formula which groups values based on an operator and a time frame.
My goal was to create an area graph which represented the moving sum of sessions over time.
Let's look at an example to help explain this type of calculation.
In the screenshot above you have 2 columns. The blue column is a regular count of X. In the green column you have the moving sum of X in a window of 7 days.
If you open your calculator and sum up the numbers in the blue column it will equal the number in the green cell.
If we move back one cell we see that the window shifts over time. We can now use the green column to see the trend of the 7 day window for X.
Why is this type of grouping so powerful? The short answer is that it eliminates variance by taking a group of values and compares that group to subsequent groups. In web analytics you have common occurrences of one off spikes which can skew your reporting to send a misleading message. Moving window graphs eliminate that issue.
The window range should always be a multiple of 7. This way each point will have an equal number of weekends.
The formula I used to create my moving window is:
iif(FIRST() >= [Window Range] or LAST() >= [# of days],NULL,WINDOW_SUM(sum([Sessions]),-[Window Range]+1,0))
The green items are parameters which allow me to change the window size and control how many days are shown in the visualization.
Step 3.2 - Create a segmentation parameter and calculation field
There are many different dimensions which you'd want to look at when segmenting your traffic. To make it easy to switch between these dimensions you'll want to create a segmentation parameter.
My parameter included the following values:
- None - This will be the cleanest option, basically showing the total number of sessions with no breakdown.
- By Default Channel Grouping - This option will segment my graph by high-level traffic grouping.
- Source - A much more granular distribution, this time by traffic source.
- By Landing Page (Post) - This option segments the graph by actual blog post. More on how I built this out below.
- Post Age - Segment the graph by age groupings. More on how I managed to enrich my data with the post age below.
Once you've created the parameter you'll need to create a calculated field which switches out the segmentation option based on the value in the parameter. Below is a screenshot of my calculated formula.
Step 3.3 - Enrich your Google Analytics data with your post data
Since my main goal was to try and understand my traffic growth in terms of blog content, I needed to find a way to segment my traffic by blog post.
My solution was to take the ga:landingpagepath field and do a bit of manipulation to get the actual landing page path. The field is deceptive because it actually holds the landing page path and subsequent parameters and not simply the page path. This results in dozens or more variants of the same page which doesn't help us if we want to group by page.
The way I got around this was by using the formula below.
iif(CONTAINS([Landingpagepath],"?") = false, [Landingpagepath], LEFT( [Landingpagepath],FIND([Landingpagepath], '?')-1 )).
This formula checks if the field value has any parameters and if it does it takes everything before the starting "?" value.
Now that I could isolate the page path I went and created a custom Google sheet in Google Docs. This spreadsheet would act as my "posts table".
Yes, I created this spreadsheet manually but you could definitely set up a zap in Zapier or use a recipe in IFTTT to build out something more automated. You could also add it to BigQuery and do the join right in BigQuery. I covered this process in my previous post on how to track your life like an analyst. It took me about 10 minutes to build this spreadsheet. You can get a full list of your blog posts from your blog's sitemap.
The fields I added to my spreadsheet include:
- Post Count - A running count of posts. I could use this field to see how my earlier posts are performing compared to newer posts.
- Publication Date - The date the post was published.
- Post Title - The full title of the post. I didn't end up using this field since the path takes up less space and I can easily identify a post by it's path. This field could still be a useful filter in your dashboard.
- Post Path - This field will be used to join the spreadsheet to the data set.
- Post Age - A basic datedif calculation between publication date and today's date (using the today() function in Google Sheets).
Once the spreadsheet is built go a head and join it to your data set in Tableau. I used a standard left join and the formula I mentioned earlier to isolate the page path..
At this stage you have all the building blocks you need to build out your dashboard.
Some extras which I built out but are optional include:
- A option to filter between sessions which started on a post and sessions which started on a non-post page.
- I manually created the Default Traffic Grouping field using the formula below. I stated earlier you should save yourself the headache and choose this field when integrating Google Analytics in Stitch.
iif(source = "(direct)" or ISNULL([source]) = true, "Direct", iif([Campaign] != "(not set)", "Campaign",
iif(source = "google" or source = "bing" or source = "yahoo" or source="yandex.ru" or source="duckduckgo.com", "Organic",
iif([Medium] = "email", "Email",iif(source = "t.co" or source="quora.com" or source="reddit.com" or source="old.reddit.com" or source="m.facebook.com"
or source="lnkd.in" or source="lm.facebook.com" or source="linkedin.com" or source="twitter" or source="l.facebook.com","Social",
"Referrer")))))
What insights did I find from my content analytics dashboard?
Now that you've got your dashboard built the fun part can start. I was able to identify a number of useful insights from my dashboard which I've shared below.
It's important to reminder that all these views are moving windows of 28 days. That means that each point on the graph is the sum of sessions in the previous 28 days.
Insight #1 - Excel-related posts are driving more traffic over time
The first insight I noticed was the steady organic traffic growth I've seen from my two Excel-related posts. I had noticed in my Ahref's account that I was climbing up the rankings for "cohort + excel" keywords and this now helps validate this change.
Insight #2 - Growth in direct, organic and social
The next insight that stood out like a sore thumb was the big boost in direct, organic and social traffic in the last few weeks.
Since my dashboard is super dynamic I'm able to easily isolate the "Direct" traffic and break it down by post.
We can now easily see that the recent spike can be attributed to my recent post on how to track your life like a business analyst. We also see that my post, The complete guide to measuring product usage for startups, is driving a lot of direct traffic even a few weeks after publication.
Insight #3 - Twitter, LinkedIn and Reddit are driving social
Social media marketing is tough in my niche but I've had some good success recently on Reddit and Twitter.
The graph below shows my social traffic grouped by traffic source. I've highlighted the most prominent sources.
Insight #4 - Pareto distribution among old posts
The last insight I'd like to share with you is a breakdown of my traffic from older posts grouped by post. You can clearly see from posts I published more than 6 months ago only 2 of them are driving significant organic traffic in a 28 day window. 2 or 3 others are making smaller contributions but the majority of the traffic from this cohort is from only 2 posts.
I spent very little effort on SEO when I first started this blog and this graph helps to show that.
When comparing the screenshot above to the one below you can see a much better distribution among my newer posts, especially in terms of growth over time. The how to track your life post is an outlier.
I hope these 4 examples helped to demonstrate the power of this dashboard. Once you have the foundation in place you can get very creative and build a dashboard that can answer a very wide range of questions related to the growth of your blog.
Summary
Google Analytics is a powerful tool for the aspiring blogger. The ease of use and out of the box reporting is great but I hope this post has shown you how to get even more value from your traffic data and build a powerful content analytics dashboard with minimum effort. If you're serious about SEO and content analytics then this approach is much more beneficial to you than using the standard GA reporting.
Thanks for reading.
