How engaging is your website content? Discover it with Power BI and Google Analytics

[ruthpozuelo]

 Measuring content engagement is probably one of the most common actions performed by website owners.

There are a lot of indicators that can help you understand how engaged your readers are with your content, but I will show in this post, the one I use the most: scroll tracking and content grouping.

To be able to  to build this website you need:

1. Categorize your content (You can skip this if you don't have categories in your site)
2. Implement Scroll Depth on your site (YouTube video)

One you have this done, you can start creating the dashboard.

What insights can you get from this dashboard?

• Are your visitors reading your content in full?
• If not, which category of pages are performing well and which ones do you need to improve?
• How are your business pages performing? Make sure your blog pages lead to your best performing business pages.
• Should you rewrite a specific post or business page?

This dashboard will give your work for weeks!! 

Download the Power BI file

Don't want to create the Power BI file? If you are a member on our site (it's free), you can download it by following this link.

You will need also to change the data source from my account to your Google account.

If you are new to Power BI, this guide will take some time to complete, otherwise, it is just a few formulas and you are good to go. Skip to the formulas if that is all you need.

Creating the dashboard: Get Data

Before you can start creating this dashboard you need to have Power BI Desktop installed (it’s FREE) and connect to Google Analytics. Once you have done that, we can start importing the data in Power BI.

Let's do it, open Power Bi Desktop and click on “Get Data”:

Select Google Analytics and click “Connect”:

Select your account, property, and view:

and now we will export a table with the following parameters:

Follow the post “Categorize your website content” to create the Page Categories table:

From the Event folder select:

• Date,
• Event Action,
• Event Category,
• Event Label,
• Unique Events
• Page Title

Rename the table “Event Scroll”:

Unique Events vs Total Events

Why do I use “Unique Events” instead of “Total Events”? First lets understand the difference between the two of them. I prefer to use Unique events as I am looking to understand how interesting the post is the first time it is read.

Relationship between tables

To make our dashboard filter correctly, we will need to join the Event and the Page table using a column that at least on one of the tables has only unique values. We are not going to get that on either of the tables we have downloaded, so we need to download a third table with unique Page Titles.

So, form the Page Tracking folder select:

• Page Title
• Pageviews

Rename the table “Page Unique”:

Now we can join all three tables. Go to the relationships tab and joint all three Page titles:

Building the Dashboard

Now that we have all the data we need, lets start building the dashboard.

We need to create some measures first:

Formulas

Our first measure will give us the number of events where the blog post was loaded. Following the script, we need to filter by all events where the Event Label is equal to baseline:

Article Loaded = CALCULATE(SUM('Event Scroll'[Unique Events]);FILTER('Event Scroll';'Event Scroll'[Event Label]="Baseline"))

Our second measure will give us the number of events where the visitors started reading the post (reached 25% of scroll):

Started Reading = CALCULATE(SUM('Event Scroll'[Unique Events]);FILTER('Event Scroll';'Event Scroll'[Event Label]="25%"))

When they read half way, or scrolled up to 50%:

Read half way = CALCULATE(SUM('Event Scroll'[Unique Events]);FILTER('Event Scroll';'Event Scroll'[Event Label]="50%"))

When they read the entire post, or scrolled up to 75%:

Read end of Content = CALCULATE(SUM('Event Scroll'[Unique Events]);FILTER('Event Scroll';'Event Scroll'[Event Label]="75%"))

And finally when they reached the Button of the Page, or scrolled up to 100%. You are going to be surprised how few people scroll to the end of your pages. I was. :S

Reached Button Page = CALCULATE(SUM('Event Scroll'[Unique Events]);FILTER('Event Scroll';'Event Scroll'[Event Label]="100%"))

I have also created a measure to understand the percentage of people that do not read the posts at all:

Not read = DIVIDE(([Article Loaded]-[Started Reading]);[Article Loaded])

And finally a measure to understand how many (percentage) of your pages were read to the end.

Read to the end % = DIVIDE('Event Scroll'[Read end of Content],'Event Scroll'[Article Loaded])

Create the visualizations

Now that you have all the measures and data in place is time to create the visualizations.

Add all the following fields in a table:

And click on the visualization called funnel:

Drop “Page Title” and “Read to the end %” into a table visualization.

Power BI Visualization Gallery

Now, as slicers, I am using a custom visualization called chiclet slicer that you need to download from Power Bi custom Gallery if you want to use it.

Click on the three dots to import it and select that visualization for Year and Month Name:

Give the visualizations some color, change font sizes and format as you like.

Enjoy!