Monitor your Website Growth with one click with Google Analytics and Power BI

by ruthpozuelo on ‎12-07-2015 03:46 PM - last edited on ‎12-11-2015 09:45 AM by Power BI Admin

I use Google analytics to optimize my site, to make it useful and usable for my readers. Doing that, I am also hoping that my site will grow. It is the ultimate confirmation that I am doing it right.

 

Measuring growth in Google Analytics itself is possible, but it takes too many steps. I want to be able to monitor my site growth with one dashboard and one click so I have created my favorite and most used Site Growth Dashboard in Power BI Desktop.

 

 google analytics growth dasboard_powerbi3.png

 

What insights can I get from the dashboard?

Here are the insights, on 2015-12-06:

  1. My site has grown 434% comparing to the same period last year. Well done!!
  2. This month (December) up to yesteday’s date my site grew 18%.
  3. This week, up to yestesterday’s date, my site grew 12% and yesterday 260%.

Note: If you are wondering why the week's data is not equal as the month's data is because the week includes 6 days (Mon 2015-11-30 to Su 2015-12-06) and the month only 5 (Tue 2015-12-01 to Su 2015-12-06).

 

Truly useful, in my opinion!

 

For a step-by-step guide on how to create this dashboard, follow this link.

 

A few words about the calculations

 

When I started creating the dashboard, I thought I could use the Year-to-Date function minus previous period to create the calculations until I realized that YTD will give you the entire month if the entire date range is represented in the calendar table.

Also, in my calculations, I am not using Year to Date, but year to Yesterday.

 

Why I am calculating up to yesterday’s date?

 

If I use today’s date, the calculations won’t be relevant until the day is over, and I won’t 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.

2015-12-01_12-41-31.png

 

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!

 yesterday to date.png

 

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 month’s performance +18% increase. My site is growing but I have a slow start this week. So much better!!

 

Let’s go through how the measures are built

 

The first measure is the sum of all sessions. This will be use on all calculations.

Total Sessions = SUM(Sessions[Sessions])

 

When was the Google Analytics data last refreshed?

 

Last Refreshed= LASTDATE(DimDate[Date])

 

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

 

No of sessions Today

 

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

 

No of sessions same Week Day as today last week

 

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

 

Why am I using same week day last week and not yesterday? Most websites follow a pattern, for example they have a day where they have most visitors or lower number of visitors on the weekends. For this reason I prefer to compare Saturdays with Saturday last week, it is more relevant.

 

No of sessions Yesterday

 

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

 

This measures calculates the number of sessions on Today-1, which is yesterday.

 

No of sessions same Week Day last week:

 

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

 

This measures calculates the number of sessions on the last date of the date table -8. In other words, today – 8days.

 

No of sessions Week-To-Yesterday:

 

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

 

This is how I calculated it. I need to calculate the number of sessions between:

  • Fist week day of current week: LASTDATE(DimDate[Date])-WEEKDAY(LASTDATE(DimDate[Date])-1;2)
  • Yesterday’s date: LASTDATE(DimDate[Date])-1

WEEKDAY will give us the number of days from the beginning of the week to last date of the date table -1 (in other words: yesterday). So, 2015-12-01 (last date of the date table)-1 returns 2015-11-30, which is the first date on that week.

 

LASTDATE(DimDate[Date])-1 will give us the last date on the date table -1 => Yesterday

 

And voilà we have the dates (2015-11-30 and 2015-11-30) which in this case are the same as the first date of the week is the same as yesterday’s date.

 

No of sessions same period last week:

 

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

 

No of sessions Month-To-Yesterday:

 

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

 

This is how I calculated it. I need to calculate the number of sessions between:

  • First day of current month: EOMONTH(TODAY();-1)+1
  • Yesterday’s date: LASTDATE(DimDate[Date])-1

EOMONTH (Today(), -2) gives us the last day of the month, one month back from current month, that is 2015-10-30 by adding +1 we get 2015-11-01. The beginning of the month.

 

No of sessions Same Period last Month:

 

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

 

EDATE(TODAY();-1) will give us today’s date one month back. We need yesterday’s date, so we subtract one day -1.      

 

No of sessions Year-To-Yesterday:

 

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

 

No of sessions same period last year:

 

SPLY Sessions = CALCULATE([Total Sessions];DATESBETWEEN(DimDate[Date];FIRSTDATE(DATEADD(DimDate[Date];-12;MONTH));MAX(DimDate[DatePY])))

 

This is how I calculated it. I need to calculate the number of sessions between:

  • First day of last year: FIRSTDATE(DATEADD(DimDate[Date];-12;MONTH))
  • “Yesterday’s date” of last year: MAX(DimDate[DatePY])

I could not figure out how to filter last year’s table to get the same day as today-1 (yesterday), so I created a calculated column in the Date Table:

 

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

 

Date Over Date calculations

 

All the “Date over Date” calculations are written in the same way:

Current period – Previous period / Previous Period, for example,

 

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

 

Any suggestions about how to improve the calculations? I would love to hear your thoughts.

Comments
by jorgene Visitor
on ‎12-11-2015 09:09 AM

Nice post Ruth!

I have played around a bit with Google Analytics in Power BI and what you have done is something in the line I like to depict.

 

I will try out your solution and I get back to you if I find anything worth sharing.

 

One thing though, that you maybe agree on is that it would be good to have the possibility to enter comments with insights and recommendations in Power BI. That is according to my opinion one of the most important things to share with people in the organisation to help them interpret the data.

 

Best regards,

Jörgen Ekelund

by ruthpozuelo
on ‎12-11-2015 12:15 PM

Thanks Jörgen,

 

Glad you liked it. I have some new dashboards on the way that hopefully you will find useful too.

 

I am not really sure what you mean with comments, but if you are referring to the functionality that Google Analytics call annotations, I really agree with you. I use annotations all the time and they are a life saver!

 

Perhaps that could be done in a separate table that is linked to the facts table....Mmmm good idea for a future post.

 

Let me know if you find better ways to do what I did, or something didnt work and specially if you find the dashboard useful or insightful or neither Smiley Happy.

 

/Ruth

by ruthpozuelo
on ‎01-11-2016 06:12 AM

First of all, happy new year to you all,

 

After the new year started, I discovered a mistake in one of my calculations. This is how the measure was written:
No of sessions same period last year:


SPLY Sessions = CALCULATE([Total Sessions];DATESBETWEEN(DimDate[Date];FIRSTDATE(DATEADD(DimDate[Date];-12;MONTH));MAX(DimDate[DatePY])))


• First day of last year: FIRSTDATE(DATEADD(DimDate[Date];-12;MONTH))


The problem here is that the firstdate(DimDate[Date]) will give us the first date of the data table and that was ok when I had only two years of data, but now that I am entering the third year, the measure will still give us the first date of the data table instead of the first date of the previous year.


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


I have also made the change in the Power BI Desktop file that is available for you to download here:
http://curbal.com/blog/the-ultimate-dashboard-to-track-your-website-growth-with-google-analytics-and...


Note: You need to be a member to download the file, but the membership is free.

 

Cheers,Ruth

 

by ruthpozuelo
on ‎06-02-2016 03:48 AM

If you downloaded this power bi file or created a copy, you might have already noticed that the first day of every month, the “month to yesterday” and “previous month to yesterday” measures return a blank but the next day everything starts working again.

 

The problem lies on how the first date of the month is calculated. In both measures, we calculate the last day of the month and then add a day to get the first day of the following month. Example:

 

If today is 2016-06-12, the function EOMONTH will give us 2016-05-31 and by adding one day we get 2016-06-01 which is exactly what we want. To calculate yesterday’s date, we subtract -1 day to today’s date, so following the same example, 2016-06-12 minus 1 day is 2016-06-11.

 

This method works perfectly except for the first date of the month. Let’s do the calculations to see what happens. If today is 2016-06-01:

 

First day of the month=2016-05-31 plus 1 day = 2016-06-01.

Yesterday’s date= 2016-06-01 minus 1 day = 2016-05-31

 

And of course power bi cannot calculate the number of sessions between 2016-06-01 and 2016-05-31 and returns a blank.

 

To solve this, I have changed the measures, so when today’s date is equal to the first day of the month, it will go back two months to get the last day of the month and then add one day:

 

First day of the month= 2016-04-30 plus 1 = 2016-05-01.

 

Here are the new measures:

 

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

 

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

 

Perhaps there is a more elegant way to solve this, but for know it works. Any ideas, let me know J

/Ruth

by onlinetimesheet Visitor
on ‎09-26-2017 12:56 AM

very useful post with great information, i have started monitoring my traffic of my website Online timesheet , thankyou for great post

by ravsha85 Member
on ‎10-05-2017 09:11 AM

Hello Ruth,

Thanks for the Detailed Explanation(you are awesome), Is it possible to Include the Quarter wise Information to this

 

Quarter to Yesterday

Previous Quarter

Same Quarter last year

 

Thanks,

 

by Betoclass Visitor
on ‎11-13-2018 10:08 AM

Ruth, muchas gracias por tú aportación. Los calculos de MTD PY con Datesbetween me ayudaron bastante.

 

saludos

 

Alberto Guajardo