The ultimate dashboard to track your website growth with Google Analytics and Power BI (supercharged Excel)

 

I have been using Google Analytics for a few years now and it has been really useful to modify my websites so they are more useful to my readers.

So, all my efforts modifying my sites, are they paying off? Do I get new readers? Is my site growing?

Google Analytics helps you with the technical details, (screen size, speed, etc) with the behavioral details (pageviews, time on page, etc) and even with your traffic sources (social, organic, etc). Everything is geared so you can grow your site, but to see how much growth you are getting on a daily/ weekly/monthly or year basis, you need to do it manually.

I want to find out in one single click, how much my sites are growing,  and I can finally do it using Power BI. My site growth in one click. Excellent! I will show you what you will create, so you can decide right now if this for you or not already now.

Here is the dashboard (see picture below):

• The  column to the left represents the actual number of sessions this year, month, week  up to yesterday’s date, yesterday and today’s date
• The middle column represents the number of sessions last year at the exact same Week Day. For example, if today is 2015-12-02, this column will give the number of sessions up to 2014-12-01 (the day before)
• The column to the right represents the growth in percent.

NOTE: Why I am using Yesterday’s date instead of Today’s? Check the end of the post for the answer.

What insights can I get from the dashboard?

Here are the insights, when the date was 2015-12-02:

1. My site has grown 447% comparing to the same period last year. Well done!!
2. The growth on December month, only one day worth of data, the site grew 340%.
3. I am having a slow start on the week with a -2% growth, probably caused by yesterday’s performance of -19%.

Note: If you are wondering why the week’s data is not equal as the month’s data is because the week includes 2 days (Mon 2015-11-30 and Tue 2015-12-01).

Truly useful, in my opinion!

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.

Watch it on  

If you would like to see a live version of the dashboard, you can watch a demo in our YouTube Channel, otherwise you can follow a step by step guide below.

 

Don’t want to re-create the Power BI file?

If you are a member on our site (it’s free), you can download it below.

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

Members Download center:

This download is for Curbal Members only. If you have a membership, please login and download the files from our download center *HERE*, if not, register!

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 dates.

Time/ Date Tables for Power BI

To make Time/Date calculations work in Power BI, a date table is needed. This is because this functions need a continuous set of dates to work properly. Google Analytics will always give you a continuous date range, even when you have zero visitors, but I still think is good practice to have a date table so everything is properly set up when you import data from other sources.

The good news is that you can use the Date field in Google Analytics to create the date table instead of building one yourself.

To build your date table, from the Time category, select “Date” and “Year“.

You will see an yellow message (3) indicating that a measure has to be specified. What it means is that a metric, like sessions or users need to be selected to be able to export the data.

Power BI has given different symbols to Google Analytics Dimensions and Metrics, so you can easily identify them:

So, go to the Sessions group and select “Sessions“. Once you have done that, you will see a data preview instead of the yellow message. Click “Edit” so we can transform our data the way we need to.

In the query editor that will appear, we are going to do two things, change the table name to DimDate:

and remove the sessions column (we don’t need that in our date table):

And now click Home and then “close and apply” to import the data in Power BI:

Now we are going to import session data, so click again on “Get Data” and import:

1. From the Time Category: Date
2. From the Session Category: Sessions

and click ” Edit”.

Change the table name to “Sessions” and click on “Close and Load”.

Power BI User interface

Before continuing, some words about the Power BI user interface:

• The Report tab: will take you to the visualization panel where you create your graphs. You can also create formulas here.
• The Data tab: here you import data, modify it and can also create column and measures
• The Relationships report: you connect tables like in access.

Back to our task at hand, if you click on the Report Tab you will see both tables Sessions and DimTable:

Create relationship between both tables

Now, we need to connect the date table with the Sessions table. Click on the “Relationships” tab and drag and drop the Date field from one table to the other:

Now, we need to change the direction of the relationship, as Power Bi has “guessed it” wrong. Double click on the arrow and change to “One to Many”:

Create formulas and columns in Power BI

Now that we have imported the data i Power BI, we need to create a calculated column and some measures (formulas).

To create a measure or a formula, on the Report tab, you click on “New Measure” in the Home Ribbon:

We will create a formula or measure that calculates the sum of all sessions:

Total No of Sessions:

Total Sessions = SUM(Sessions[Sessions])

Copy the formula and paste it on Power BI:

NOTE:This formulas can be copied in your report as they are if you use the same table names. If you use a European version of Power BI change the “,” to “;” in the formula.

Per default, all new columns and measures will be added to the first table in the list. To change that, click on the measure you want to move and change the Home Table to Sessions:

I have chosen to use Sessions instead of Users, because Users is not a reliable metric. But you can import users instead and change the first formula.

When was the Google Analytics data last refreshed?

I always have a Last Refreshed measure to see right away how old my data is. The formula below will return the last available data on the Data table we are creating from Google Analytics Date Dimension.

Last Refreshed= LASTDATE(DimDate[Date])

Dashboard formulas/ measures

_________________________________________________________________________

For an in-depth explanation on how the formulas work, read my post on the Power BI community Blog.

_________________________________________________________________________

Now, let’s create the measures that will be used in our Growth Dashboard.

No of Sessions Today:

Sessions Today =
CALCULATE ( [Total Sessions], LASTDATE ( DimDate[Date] ) )

No of Sessions same day last week:

You probably are wondering why choose same day last week and not yesterday. Most websites follow a pattern, for example they have a day where they have most visitors or lower no of visitors on the weekends. For this reason I prefer to compare Saturdays with Saturday last week, it is more relevant.

Previous Week =
CALCULATE ( [Total Sessions], DATEADD ( LASTDATE ( DimDate[Date] ), -7, DAY ) )

No of sessions Yesterday

Sessions Yesterday = CALCULATE([Total Sessions];DimDate[Date]=TODAY()-1)

No of sessions same Week Day last week:

Previous Week (yesterday) = CALCULATE([Total Sessions];DATEADD(LASTDATE(DimDate[Date]);-8;day))

Day over day growth:

DOD % = DIVIDE(([Sessions Yesterday]-[Previous Week (yesterday)]);[Previous Week (yesterday)])

Week-To-Yesterday Sessions:

WTY Sessions = CALCULATE([Total Sessions];DATESBETWEEN(DimDate[Date]; LASTDATE(DimDate[Date])-WEEKDAY(LASTDATE(DimDate[Date]);2)+1;LASTDATE(DimDate[Date])-1))

Same Period Last Week Sessions:

SPLW Sessions = CALCULATE([Total Sessions];DATESBETWEEN(DimDate[Date]; LASTDATE(DimDate[Date])-WEEKDAY(LASTDATE(DimDate[Date]);2)-6;LASTDATE(DimDate[Date])-8))

Week over weeek growth:

WOW % = DIVIDE(([WTY Sessions]-[SPLW Sessions]);[SPLW Sessions])

No of sessions Month-To-Yesterday:

MTY Sessions = CALCULATE([Total Sessions];DATESBETWEEN(DimDate[Date];EOMONTH(TODAY();-1)+1;LASTDATE(DimDate[Date])-1))

No of sessions Same Period last Month:

SPLM Sessions = CALCULATE([Total Sessions];DATESBETWEEN(DimDate[Date];EOMONTH(TODAY();-2)+1;EDATE(TODAY();-1)-1))

Month over month growth:

MOM % = DIVIDE(([MTY Sessions]-[SPLM Sessions]);[SPLM Sessions])

No of sessions Year-To-Yesterday:

YTY Sessions = CALCULATE([Total Sessions];DATESBETWEEN(DimDate[Date];DATE(YEAR(TODAY());1;1);LASTDATE(DimDate[Date])-1))

To calculate the Same period last year, we need to create a calculated column in the DimDate table first. To create a calculated column, on the Report tab,  select the table DimDate and  click on “New Column” in the Home Ribbon:

Copy the following formula:

DatePY = DATE(YEAR(DimDate[Date])-1,MONTH(DimDate[Date]),DAY(DimDate[Date]))

Now, we can create our last measure:

No of sessions same period last year:

SPLY Sessions = CALCULATE([Total Sessions];DATESBETWEEN(DimDate[Date];DATE(YEAR(TODAY())-1;1;1);MAX(DimDate[DatePY])))

Year over year growth:

YOY % = DIVIDE(([YTY Sessions]-[SPLY Sessions]);[SPLY Sessions])

If you have done everything correctly, your will see your new column and measures in the Power BI Fields section:

Create the Dashboard

To create the dashboard, you need to drag the measure into your white canvas in the Report tab. When you do that, Power Bi chooses a bar graph, by default:

To create our dashboard, we will change the bar chart for a card visualization. While the chart is selected, click on the Card icon:

and the visualization will change to a card:

When you add the Year over year growth, it will give you the data in decimal. You need to change that to percentage:

Drop in all other measures, to create the final dashboard:

Refresh the dashboard with new data

Now that you have created your brand new dashboard, the only thing you need to do is to click on Refresh to get new data:

About the dashboard

About the formulas

For an explanation on how the formulas work, read my post on the Power BI community Blog.

Why I am using Yesterday’s date instead of Today’s?

If I use today’s date, the calculations wont be relevant until the day is over, and I wont be able to see what see how I am doing weekly either.

For example, these are the numbers for a site on the first day of the month: Month-to-Date, Week-To-Date and Today

As the day is not over yet, (half days worth of data), all the month-over-month and week-over-week calculations are negative, not very insightful right?

Moreover, if you perform badly the first day of the month, you won’t be able to see how much you underperformed, until the second day is over and by then a new calculation with minus digits will start (if you are still underperforming).

Here are the same calculations but from yesterday dates. Truly much more useful!

I can see how I am performing so far today: I am -11% behind from yesterday’s date and the same for the week, as this is the first day of the week. I can see last months performance +18% increase. My site is growing but I have a slow start this week. So much better!!

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 below.

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

Members Download center:

This download is for Curbal Members only. If you have a membership, please login and download the files from our download center *HERE*, if not, register!

Google Analytics Glossary

If you are unsure or unfamiliar of how the data is categorized in Google Analytics, use our Google Analytics Glossary (requires Free membership) to  search for your dimensions and metrics easily!