Power BI and Google Analytics

One of the more underused features of Power BI is the Google Analytics data source.  Working closely with marketers and agencies I continue to see them struggle with getting and reporting on their data from Google Analytics and it should not be that difficult.   With Power BI or even with Power Query in Excel you can easily get, model and visualize this data with only a few clicks.

Let’s walk through how easy this is, first step is to get the data from Google Analytics which is done by selecting get data and choosing Google Analytics and logging in with your account.

image

Once logged in you will see a list of sites that are being managed under your account and from here you drill down into your site and select the data you need.  For this example lets say I want look at the number of hits on my site and be able to separate new users from existing users.  For this I will need to get hits from session, User Type from User and Date under time.

imageimageimage

 

Now that we have our data elements click load and the data will be loaded into Power BI.  To add a little depth to my report I did two things.  First I imported a date table, and if you don’t already have one here is a great blog to create one in power BI.  Next I created a custom column in my date table to specify records in three categories, “Last 30 days”, “Greater than 30 days” and “Future.

DAX : Last 30 Days = if(Now()>=[Date], if(DATEDIFF([Date],NOW(),DAY) <=30,”Last 30″, “Greater than 30″),”Future”)

Last I created a measure to show goal of hits by using hits from the previous year with 20% increase.

DAX: Hits Goal = CALCULATE(SUMX(‘I Predictus (2)’,[Hits]),SAMEPERIODLASTYEAR(‘G_Calendar'[Date]),all(G_Calendar)) * 1.2

Now I can create a couple simple visuals one showing overall hits in the last 30 days to goal and the other daily hits by user type against overall goal.

image

This is just a small example of the data available via the Google Analytics data source.  If you are using Google Analytics then I suggest you start using this data source and start developing your reports and dashboards.  Oh and how could I forget to mention that once you have these developed you can schedule the refreshes to be automatically done.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s