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

by ruthpozuelo on ‎01-13-2016 08:41 AM

 

 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?

Page engagement power bi google analytics1.png

 

  • 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!! Smiley Happy

 

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”:

 

image002.png

Select Google Analytics and click “Connect”:

image003.png

Select your account, property, and view:

 

image004.png

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

 

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

 

image005.png

 

From the Event folder select:

Rename the table “Event Scroll”:

 

image006.png

 

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:

Rename the table “Page Unique”:

 

image007.png

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

 

image008.png

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. Smiley Frustrated

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:

image009.png

And click on the visualization called funnel:

 

image010.png

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:

 

image011.png

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

Enjoy!

Comments
by rejcom Frequent Visitor
on ‎02-03-2016 01:07 AM

Very detailed description. Thank you 

by ruthpozuelo
on ‎02-09-2016 12:55 PM

You welcome! Did you recreated the dashboard? You can also download it at our site, but free membership is required before the download:

http://curbal.com/blog/how-engaging-is-your-website-content-discover-it-with-power-bi-and-google-ana...

 

Let me know if you found any gold in there! Smiley Happy

/Ruth