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:
My site has grown 434% comparing to the same period last year. Well done!!
This month (December) up to yesteday’s date my site grew 18%.
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).
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.
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.
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.