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 4helps 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 your site is growing, you can finally do it using Power BI. Your site growth in one click.
Check the report live here:
About the report
The following Power BI report app will allow you to connect to Google Analytics 4 to analyze your website performance.
The report contains 7 tabs to analyze different aspects of your website:
- The User Acquisition Page, will allow you to get a deeper understanding on how your new users reach your site.
- The Traffic Acquisition Page, will help you understand where your traffic comes from for new and return users.
- The Pages page, lets you analyze your content so you can improve it or create more of what is working already.
- The Landing Page , will help you understand which pages are driving traffic to your site.
- The Tech Page, will help you optimize your site to the technology your audience uses. and finally,
- The Demographic Page, will help you learn more about your audience.
Important note:
This report uses Windsor.ai connector for Google Analytics 4.
They have a free tier you can use. If you need a pair tier you can get 15% off with this code:
CURBAL
Is the template using the built in Power BI connector in Power BI?
No, because it is not possible to send filters back to Google Analytics 4 at the time of the query and therefore the data will be incorrectly aggregated.
Can I get the report?
If you tested the app and you want to personalize it, you can buy the report here:
-
Google Analytics 4 Template App by Curbal449 € (Price excl. VAT: 359 €)
For more information about this report check this blog post.
Hi Ruth, thank you for a quick response. Unfortunately, the link is still not working. I get the message “Sorry, the page you are looking for is not found”.
Hi,
I updated the link, Please try again!
/Ruth
Hi, I am unable to download the Power BI file. Thanks.
Based off your formula below, how do I calculate % change vs. the previous month (.e.g. April ’17 vs. March ’17)?
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])
Not able to download the file
Hi Teena,
Please use Chrome to download the file, it works for me.
/Ruth
Unable to download the file 🙁
So sorry Fadel,
The link stopped working for some reason.
I have fixed it now, please try again with Google Chrome. (Having issues with firefox)
/Ruth
Awesome tutorial Ruth 🙂
I was wondering if you could share the snippet for
Last 90 days vs Previous 90 Days
Last 30 days vs Previous 30 days(Not the one where it starts from “start of the month”, it should be last 30 days and same period before the last 30 days”
I was able to do that for last 365 days and same period previous 365days, but I am having difficulty getting the quarter and month done
Can you please help me out here?
Let me know 🙂
P.S: As you can see, I am not soo good indax!
Sure I can help you Sumit, though next week, I am on vacation till then.
In the meantime check out my DAX Fridays DATEADD video:
https://youtu.be/iw-WfZM9L3U
That is the way to solve your issue 😉
/Ruth
Hi Sumit,
Do you still need help?
/Ruth
Hey Ruth.I’m stuck. I need to calculate session %change from sept.2015 to sept.2016. I know how to do this in SQL but not in DAX. Would you know how to do this?
Hi Brandon,
In my growth file, I am measuring that already. Can you please specify a bit more what you need?
/Ruth
Hi,
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)))
I have modified the download file, so you can either change the measures manually or download the file again.
Let me know if you have questions.
/Ruth
Hola Ruth,
Jörgen here from Sweden, I saw your post in Power BI community. I am sitting and trying out your solutions and I run into some problems, starting when I tried to relate one-to-many in “Create relationship between both tables”, it says I am not allowed to do that.
I am an ex Microsoft from Microsoft Latam during the 90’s and I am very impressed with the possibilities of Power BI. Specially combining it with Google Analytics that I am using a lot.
Please let me know if you have time some day to go through your solution and maybe we can explore some synergies.
BTW, I speak Spanish…
Saludos,
Jörgen
Hola Jörgen :),
You have to create a “one-to-many” from the date table to the Sessions table. Make sure it is in that order.
If when you open the dialogue you see the sessions table first, then you have to choose many to one as you see on the picture below:
Absolutely, I will send you a private message and we can arrange something,
/Ruth
Hi Ruth,
I have the same problem : Power BI will not let me set the relationship in the direction you suggest, no matter if I go from Dates to sessions (one to many) or from sessions to date (many to one). Can I let it be as power BI initially suggest it?
Hi Vallee,
Not sure what the issue is? I checked the download and the relationships are ok. You mean that it breaks when you load your own data?
/Ruth
Hi Ruth,
No I didn’t use the download, I built the data model from scratch importing my google analytics data.
Can’t that work this way?
Marie
Hi Ruth, no I didn’t use the download, I built the data model from scratch following the steps you describe in the tutorial. When U click on the relationship to edit it, the system doesn’t let me set up anything else than many to many. Marie
Hi,
That means that you have duplicates , blanks or empties on the date table. Please check that.
/Ruth