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

[ruthpozuelo]

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.

 

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.

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