Integrating Google Analytics with Microsoft Power Bi (Quick How-to)
By Trent Y
In this post, I’ll discuss how to integrate Google Analytics with Power Bi. There is already a pretty cool dashboarding tool by Google called Data Studio. We love Data Studio at twentysix and use it regularly. In fact, we’ve written a really neat post on how to create your own custom Google data studio connector.
But Data Studio is not the only dashboarding platform we use. For those of you who still prefer working with data in Excel and want something that stays close to that with visualisations that are cleaner, then Power Bi might be the tool for you. For this article, we are using the Pro version of Power Bi.
Summary: Free vs Pro
Power Bi is an interactive dashboarding tool developed by Microsoft. Most new windows computers will already have the tool installed and you can use the tool for free. If you don’t have it, you can download the desktop application here. You can also activate a free 60-day trial of the Pro version to try out all the features.
The free version of Power Bi allows you to connect to more than 70 data sources, publish your dashboard to the web and export your wok to powerpoint, excel or csv. What you will not be able to do on the free version is the ability to collaborate with other users, use Apps and embed APIs. For a comparison table between Free and Pro go here.
Ready to start the integration between Google Analytics and Power Bi? Let’s go! We'll need to enable Apps in Power Bi and there are two methods to do this.
Method 1: The first method requires us to login to https://app.powerbi.com/ . You’ll need a Microsoft account to do so. Once you’re in, click on the apps link on the left navigation and then select “Get Apps”. Then choose Google Analytics.
PowerBi will connect to Google Analytics using the OAuth2 authentication method. Sign in using your Google Account and select the analytics property you wish to visualise. The next time you login to Power Bi and click on the Google Analytics app icon, Power BI will generate a quick app dashboard like this one:
Method 2: The second method is our preferred method because we get to dive straight into preparing and customising how we want the data to be structured once we connect to analytics. To do this, we need to use Power Bi for Desktop:
It kinda looks like a blank spreadsheet with loads of options similar to the excel layout. Just ignore the visualisations panel on the right for now. We’ll go through that later.
To connect to analytics, click on Get Data > More > select Google Analytics from the list of apps presented. This is where it gets interesting. Within the Navigator Panel, you can select precisely the metrics and dimensions you want to visualise. It also allows you to preview the data beforehand.
For example, here, we’ve selected “Default Channel Grouping” with “Sessions”. (Note: if you do not select a relevant measure, Power BI will display an error because there is not enough information to enable the preview). You can of course select multiple metrics if you so wish.
Click Load when you're done.
Cleaning & Preparing The Data
You’ll need to wrangle with the data abit if you need it to display in exactly the format you want. This is probably the most time consuming part.
For example, the Google Analytics connection has a “Month of the year” dimension which outputs the month as a two digit integer from 01 to 12.
You can imagine what the charts are going to look like with 01, 02, 03 instead of Jan, Feb, Mar. Not very friendly to read. There is no selectable dimension that spits out the month name so to get around this, here’s what I did:
In the Navigator:
- Select the dimension “Year”
- Select the dimension “Month of the year”
- And then select whatever else you want to output for example, “Default Channel Grouping” and “Sessions”.
Instead of clicking the Load button to load the data, select Edit instead:
When you Edit the data before it is loaded, this means, the data will be loaded in the manner you want before it is retrieved. This will open up the query editor. Once you're in it, ignore everything else and select “Add Column” on the top navigation.
Then click on Custom Column, name your new column with something like “New Date Column” and input the following formula.
This is essentially concatenation.
Power BI will check that your syntax is correct. Click OK.
Your new column will now appear in the query table. The final step you’ll need to do is select the new column, select the Transform tab and then transform the Data Type into Date. This means the data in the new column will be correctly read as dates. Then click Save and select Apply when prompted.
If you don’t like the format of the dates, Save, Apply then exit the query editor. You'll return to Data, then under the Modelling tab, select the newly created column again and just change the format of the dates to the format you want:
This should enable you to visualize your data nicely, especially those ones where you are monitoring performance month on month.
When you pull in metrics such as conversion rate from Google Analytics and then try to visualise it, sometimes Power BI will not display the percentages in the manner you’d expect. The best approach to standardise percentages (eg. for metrics like Bounce Rates, Conversion Rates etc) is to create custom columns much like the date example shown previously, and use formulas to calculate the percentage.
The below is a screenshot of getting the conversion rate for each channel using a new column called CVR with the formula:
Ensure the Data Type for your column is set to Decimal and the format is Percentage:
Visualising the data
Finally, the fun bit! Once the data is cleaned and formatted in the way you want, you can start visualising it. The visualising part is probably the simplest part.
In this example I am visualizing month on month traffic. After connecting Power BI with Google Analytics, you can save each set of data and give it a name and these will be available for you to select when visualising.
In this example we have a data set named “Acquisition”, another one called “Behaviour” and a data set for “Conversions”. You can also just have one large dataset – it really depends on your workflow.
To visualise traffic, I select the type of visualisation I want (in this case, the bar chart).
- I select the data set I want under “Fields”
- Drag and drop “Month” into the Axis field
- Drag and drop “Sessions” into the Value field
There is a paintbrush icon indicated under the arrow in the screenshot above – this allows you to customise your chart further for example, colours, trendlines, borders, titles etc. Play around with the different visualisations available – there’re some pretty neat ones in Power BI that are not available in Data Studio for example, funnel charts, filled maps etc.
You can add multiple charts onto one sheet in Power Bi and just like Excel, you can add additional tabs and name them.
Saving & Sharing Your Creation
When you’re happy with your work, hit Save, then Publish.
This will publish your report to the online app which you can access by going to https://app.powerbi.com. Your report will be accessible under My Workspace > Reports.
You can share the report with other colleagues or clients who have a Pro Power Bi account.
To share it, simply click the Share button from the online report and add their email address when prompted.
Alternatively, the report can be exported into a .pbix file (File > Download Report) which can then be opened by another user in Power Bi.
And that's it!
There is obviously more to Power Bi and Google Analytics than what we have gone through in this basic how-to article.
Here are some further reading to get more of an understanding and extend the capabilities even further:
Understanding available Google Analytics metrics and Dimensions:
Quick Measures in Power Bi:
Running R with Power BI:
Maximize business opportunity with data-driven decision making. Find out more about our data team.
Trent Y. is the Analytics & Conversion Director at twentysix. Analytics, usability and conversion strategies. Loves coffee, interested in how data and technology can positively impact our lives. "Learn fast, learn often."
Image by Edouard Ki, Unsplash