cancel
Showing results for 
Search instead for 
Did you mean: 
ruthpozuelo

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

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

Hi Ruth, 
Very helpful thank you and I've watched your video too.  
I need to show the Sessions for the last Calendar month and then the same calendar month last year.  Any advice on how to do this?  

I'm not very familiar with Formula's and DAX.

Thank you

Jennifer

Hi,

When I am creating these measures 

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

 

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

After that still gives me full-year current month sales i.e same period last year sales but it s giving for full month

@ruthpozuelo