Which pages are underperforming? Find out using Power BI and Google Analytics

[ruthpozuelo]

Running a blog on your business website enables you to attract customers that are searching for information related to your business and helps you connect with them. But, do you know how your blog pages are performing against your business pages? Are you getting more customer leads due to the blog?

In this post, I am going to show you how to categorize your content based on your Blog Pages and your Business Pages. I will add a third category as this site has also a Google Analytics Glossary, and I want to understand how that is performing against the other pages/categories.

Why not do it in Google Analytics?

As you probably know, Google Analytics allows you to group your content so you can better analyze the performance on different categories.

It is not complicated to do it in the Google Analytics Admin panel, but I prefer to do it using Power Bi as it allows me to make changes retroactively.

Insights when content is categorized

In this dashboard (see picture below), I can see:

1. How much traffic my Blog, Business and Glossary Pages are bringing to my site and looking at the data, the blog pages are by far the most popular. With the help of the blog, I can get quality customer leads to my business that hopefully lead to buying customers. Great, purposed achieved!

2. I can also see that even if the blog pages bring traffic, only a small portion of the visitors check out my Business Pages. Can I lead more blog readers in to my business pages? Further investigation should be done.

Page Category performance Dashboard

Now is your turn, Do you know how your pages are performing?

No? No worries, lets build the dashboard to find out.

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

Get Data

Select Google Analytics and click “Connect”:

Connect to google analytics

Select your account, property, and view:

Select account, property and view

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

• From the Time Category select “Date”
• From the Page Tracking Category select “ Page”
• And as we always need a metric, select “Unique Pageviews” from the Page Tracking category.

Why do I use Unique Pageviews instead of Users? Because “Users” is not a reliable metric.

Why do I use Sessions instead of Entrances? Because "Sessions" give a more complete hit view.

Your data should now look as below.

Import data from GA

Finally, change the Name of the table to (1) “Page Categories”.

Create Date fields

We are going to create a few date columns that we will use to slice our data. Of course it is possible to import that from Google Analytics Date category, but because google has limitations about how many metrics and dimensions you can download, I prefer to download only “Date” and create the others on Power BI.

We will create a Year, Year_Week and Month Number and Page Category column.

Year Column

It’s very easy, here is how:

1. Click on “Add Column” tab
2. Click on “Add Column” Button
3. Give your new column a name: “Year”
4. Insert the formula: Number.ToText(Date.Year([Date]))
5. Check that there are no errors
6. and save.

Year in Power Query

And you will see your new column:

YearWeek Column

Now we are going to create a column with Year-Week data based on our “Date column”:
Follow the same steps, but use this formula instead:

[Year]&"-"&Text.PadStart(Number.ToText(Date.WeekOfYear([Date])),2,"0")

A short explanation:
• Date.WeekOfYear converts the column “Date” to a week number, ie.34
• Number.ToText converts a number to text and
• Text.PadStart adds a leading zero to the week number,ie:02
• &”-“& concatenates the year and the week with a “-“ in the middle, ie:2015-02

Month No column

Formula to get the month number so we can sort later the month name.

Date.Month([Date])

Categorize your content

Now, to be able to categorize your content, you need to have some kind of structure on your URLS. Here is how mine are structured:

1. My Glossary pages follow this pattern: /blog/glossary
2. My Blog pages follow this pattern:/blog/
NOTE: If you don’t have /blog in your URL for your blog pages, follow this tutorial.
3. And my business pages follow this 😕

So, I need to Add a New column with the following formula:

if Text.Contains([Page],"/blog/glossary") then "Glossary" else if Text.Contains([Page],"/blog") then "Blog" else "Business"
Which says, if the Page Url contains “/blog/glossary” then call it Glossary, if it contains “/blog” then call it Blog otherwise call it Business.

Easy right?

Add Page category Power Query

lets close and apply:

Close& Apply

Before we start creating the dashboard we need to create one more column: Month Name. The reason I didn’t created on the steps before is because I am not aware that Power Query has that function, so we need to create it in Power Pivot.

Calculated column Month Name

Create a calculated column

MonthName = FORMAT('Page Categories'[Date],"MMM")
Click on the “Data” Tab

Power BI User interface

Click on the Month Name column and sort by Month No.

Building the dashboard

Now we can finally build the dashboard:
1. Drop the Page category
2. and Sessions fields
3. and choose tree map visualization:

Tree map visualization:

For the timeline visualization you need to:
1. Drop the Page category;
2. YearWeek
3. and Sessions
4. And choose 100% column stack visualization:

100% Stacked bar visualization;

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

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

Power BI custom visuals

And you are done!

Next post: Track engagement with page categories and scroll depth

Before you start leading more readers to your business pages, you should find out how your business pages are performing.
In the next post I will show you how to track user engagement using the page categories and scroll depth! Here is a snapshot of how the dashboard will look like

Engagement Report