Power BI Google Analytics Tutorial – Visualize GA Data

Power BI is a data visualisation and modelling tool from Microsoft. It is free to use (for up to 1 GB of data storage per user) and can be connected to various data sources including Google Analytics.

Through this tool, you can visualize your website data to gain insight.

The pro version of this tool is the paid version and can be used if you need more data storage, want an hourly data refresh (instead of daily), and more BI (business intelligence) tools.

The importance of data visualisation in web analytics

Data visualisation is the presentation of data in graphical format.

Data visualisation is not just about creating pretty reports and dashboards. It is about making sense of the data.

When you gave got a lot of data to analyze, you can’t spend days or weeks, analyzing thousands/millions of rows of data in excel spreadsheets. You need a system through which you can quickly make sense of data, determine patterns and anomalies, which are otherwise extremely hard to detect on time.

Here data visualisation comes in handy. Data visualisation not only helps in data interpretation but also helps in data retention and telling meaningful, emotional and engaging stories to key decision-makers.

If you wish to make your reports more meaningful and persuasive, then you need to learn the art of storytelling by visualising your data.

Get the E-Book (50 Pages)

Get the E-Book (40 Pages)

Power BI vs. Google Data Studio vs. Tableau

Power BI, Google Data Studio and Tableau, all are well-known data visualisation tools.

Following are the various pros and cons of using these tools:

#1 Free version

Both Power BI and Tableau provide a free version of their products. On the other hand, Google Data Studio is free to use.

Tableau’s free version is called Tableau Public but it won’t let you produce and save any private content, so pretty much useless for commercial purpose.

Tableau only provides a two-week free trial, unless you are a student or instructor at an accredited academic institution, who can use Tableau for free.

#2 Cost

Google Data Studio is free to use.

The paid version of Power BI will cost you just $9.99 per user per month.

The cheapest Tableau product is Tableau Desktop Personal edition which will cost you $420 per user per yearBut this edition is pretty much useless, as it does not directly connect with Google Analytics and provide just seven connectors (data sources).

Tableau Desktop Professional edition directly connects to Google Analytics, Google BigQuery, Google Sheets. But this edition will cost you $840 per user per year and it still does not directly connect with Google Ads.

Use Power BI for basic data visualisation.

If you have very specialized data visualisation needs, then consider using Tableau.

#3 Data connectors

Google Data Studio primarily integrates and visualize data across all GA products.

It primarily focuses on connecting data from other Google data sources (like Google Analytics, Google Ads, DBM, Google Sheets, YouTube, etc).

So if you are mainly using Google products then it is all well and good. But if you are using non-Google products then in many cases there is no direct integration. However, bear in mind that Google will introduce more data connectors in the near future. So this limitation won’t be there for long. 

Power BI, primary focus on connecting data from other Microsoft products (Microsoft Azure Enterprise, Dynamics AX cost management etc).

But, unlike Google Data Studio, it provides many more data connectors for both non-Microsoft and non-google data sources (Adobe Analytics, ComScore Digital Analytics, Github, MailChimp, Marketo, Salesforce, Stripe, etc)

However, the biggest downside of Power BI is that it provides only two connectors for Google products and i.e. for Google Analytics and Google BigQuery (still in beta). So for example, Power BI does not provide any connector for Google Ads or Google Sheets.

What that means is, you can’t download the Google Ads data directly into Power BI. But there is a workaround. You can still indirectly, download Google Ads data via your Google Analytics or via Excel or CSV file.

But then really, what more can you ask from one of the cheapest data visualisation tools on the market.

Following is a short video that compares Power BI and Tableau on four important factors:

Integrating Power BI with Google Analytics – online version

To integrate Power BI with Google Analytics, follow the steps below:

Step-1: Sign up for Power BI (free) using your work email. Use the online (cloud-based) version instead of the desktop version for now. Power BI desktop is more powerful but more suitable for advanced users.

Step-2: After you have signed up, click on ‘Apps’ in the left-hand side navigation menu.


Step-3
: Click on ‘Get Apps’.


Step-4:
Search for the Google Analytics app. You will see two applications that can connect to Google Analytics. One is from ‘Havens Consulting’ and the other one is from ‘Curbal AB’.

As an example, we are going to select the ‘Google Analytics App by Curbal’ by clicking on the application’s icon.


Step-5:
An overlay will appear like below, click on “Get it now”.


Step-6:
Fill in the required information, click on the checkbox and then click on ‘Continue’.

Step-7: Click on ‘Install’.

Step-8: This could take a few moments to install. Once installed you can see it in the ‘Apps’ section. Click on the installed app. 

Now let’s add the Google Analytics view to the app.

Step-9: Click on ‘Workspaces’ under the left-hand side navigation.

Step-10: Select the Google Analytics app.

Step-11: A configuration console will appear like below. Click on the three vertical dots in front of the application and select ‘Settings’.

Step-12: An overlay will appear like below. Click on ‘Edit Credentials’ under ‘Data Source credentials’.

Step-13: You will now see a dialog box that asks you to select the ‘authentication method’.

However, you don’t have the option to select any other authentication method. So, leave this selection the way it is and click on the ‘Sign in’ button.

Step-14: Choose your Google account by clicking on it.

Step-15: Allow Power BI to access your Google Analytics data by clicking on the ‘Allow’ button:

Note: It may take up to a minute or more for Power BI to connect with your Google Analytics. So you need to be patient and do not automatically assume that something has gone wrong. If the integration does not work the first time then try it again. Remember, after all, it is a Microsoft product.

Step-16: Now you need to select the Google Analytics account, property and view whose data you want to visualise and then click on the ‘Parameters’ drop-down:

You need to define all the parameters asked by the application.

Step-17: To begin with, enter the following:

  1. Google Analytics Account ID under ‘AccountID’.
  2. Title of your contact us page under ‘ContactPage’, for example ‘Contact Us’ or ‘Contact’.
  3. A domain such as ‘com’ or ‘net’, without the dot.

Step-18: Now add the below parameters:

  1. EndDate: Specify the date from when you want to stop loading data in Power BI (Generally, Power BI will stop automatic refreshing data after this date).
  2. Language: Enter the language for the month and day name which will appear in reports and dashboard.
  3. PropertyID: Your Google Analytics property ID for which you want to generate reports.

Step-19: Finally add the below parameters and click on ‘Apply’.

  1. StartDate: Date from when you want to add data to Power BI reports (Generally it should be the first date you started capturing data in Google Analytics).
  2. StartWeekDay: Start of the week that you define, if you put ‘1’ then your week will start on Monday, if you put ‘0’ then your week will start on Sunday.
  3. ViewID: Your Google Analytics view ID for which you want to generate reports.
  4. WebsiteName: Your website name for example ‘optimizesmart’.

Note: Importing data from your GA property could take some time.

Introduction to workspaces in Power BI

Workspaces are the navigation areas used for interacting with Power BI:

The workspace contains the following main areas:

  1. Reports: Normal reports that can be shared.
  2. Paginated reports: Designed to be printed or shared. They’re called paginated because they’re formatted to fit well on a page.
  3. Dashboards: Visualization.
  4. Datasets.
  5. Data flow: Used for data joining and data modelling.
  6. Streaming dataset: Build real-time reports.
  7. Upload a file: Offline File uploads.

A dataset is simply a data source like Google Analytics, Excel file, Salesforce, etc. The more data source you integrate with Power BI, the more datasets you can use and the more powerful your marketing and analytics reports become.

You need to have at least one dataset available before you can create reports and/or dashboards in Power BI.

You can create visualisations by adding data points to your dataset and then save the visualisation as a new report.

From your reports, you can create a new dashboard.

Creating reports in Power BI

In the context of Power BI, a report is one or more visualisation tilesThese visualisation tiles can be on one page or multiple pages.

This is what a visualisation tile looks like in Power BI:

Note: A single visualisation tile is also known as a single visualisation.

There are two methods through which you can create one or more visualisation tiles. One is through the ‘Datasets’ area and the other is through the ‘Reports’ area.

To create a visualisation tile via ‘Datasets’ follow the steps below:

Step-1: Click on the ‘Dataset’ which you want to use, for creating your visualisation. For example, you can select ‘Google Analytics’.:

 

Step-2: A new console will appear like below. Click on ‘Create from scratch’.

Step-3: Select the metric you want to use for creating a data visualisation tile. For example, I selected ‘sessions’ by typing ‘sessions’ in the search box:

Step-4: Click on the checkbox in front of the ‘Sessions’ metric.

After clicking on the checkbox, you will see a new data visualization tile based on the ‘Sessions’ metric:

Step-5: Click on the ‘Reading View’ button at the top:

Step-6: A pop up will appear, as below, Click on the ‘Save’ button:

Step-7: Give your new report a name and click on the ‘Save’ button:

You will now be automatically redirected to the ‘Reports’ section of your workspace.

Step-8: Click on the ‘Edit report’ link to add more data visualisation tiles to your report:

Step-9: Select the metric you want to use for creating a new data visualisation tile by typing that metric in the search box (as explained earlier).

Step-10Click on the checkbox in front of the selected metric.

Step-11: Select the type of visualisation you want to apply to your visualization tile from the visualisation toolbox:

Step-12: Format the text in your data visualisation tile by clicking on the ‘Format’ button:

Step-13: If you want to add values to your visualisation tile, then drag and drop data fields to the ‘Values’ area:

Step-14: If you want to apply filters to your visualisation title, then Power BI provides three types of filters:  ‘Filters on this visual, ‘Filters on this page (page-level filters), and ‘Filters on all pages’ (report-level filters):

To get the desired data in your visualisation title, you need to use these filters.

However, do not change the ‘visual level filter’, unless you want to use a different metric for your visualisation.

For example, if you want to get visualisation data for the date say ‘Sept 16, 2017’ then drag and drop the ‘Date’ field under the ‘Filters on all pages’ column and then select the date:

Step-15: Click on the ‘Reading view’ button once you have done editing your report and then save it.

Creating a dashboard in Power BI

In the context of Power BI, a dashboard is a collection of data visualization tiles from one or more reports and/or datasets.

When you first create a Power BI account, one dashboard is automatically set up for you.

You can create additional dashboards by navigating to your workspace and then clicking on the ‘+ New’ followed by the ‘Dashboard’ link:

Once you have created the dashboard, then you need to add content to it.

For example, let us suppose I created a dashboard named ‘Marketing Dashboard’. Now I want to add content to it.

What I can do, is navigate to one of my existing reports and then click on the ‘Pin to a dashboard’ button:

Then select my dashboard from the drop-down menu and then click on the ‘Pin live’ button:

Now when I navigate to my dashboard, I can see this report there.

You can also pin an individual data visualisation tile (instead of the entire report) to your dashboard from one or more connected datasets.

That’s how you can create dashboards in Power BI. You can also share your dashboards with others.

Following is an example of a dashboard in Power BI:

You can click on one of these reports to see a detailed report.

For example, the following is the data visualization for a system usage report in the last 180 days:

If you wish to see, for example, ‘system usage’ data for the last 30 days, then click on the ‘Edit’ button at the top of the report:

Type ’30 days’ in the box as shown below:

Click on the ‘Reading View’ button:

Click on the ‘Save’ button:

You will now see the ‘system usage’ data report for the last 30 days:

If you want to ‘zoom in’ a particular report, then click on the ‘Focus mode‘ button:

If you want to see the data for a particular chart, then right-click on the chart and select ‘Show as a table’:

The data that is reported is quite easy to understand:

Ask Questions About Your Data

This is a very cool feature of Power BI. You can literally ask questions in plain English about your data from a dashboard and this tool with try to answer it.

I used the word ‘try’ because it does not always answer all of the questions you may ask, but it does a very decent job of answering questions.

Google Analytics provides similar functionality via Analytics Intelligence.

Just like in GA, in the case of Power BI, you don’t need to know, how to drill down data.

Just type your question like ‘total sessions by mobile browser’ in the ‘Ask a question about your data’ text box on your dashboard:

As soon as you type your question, you will see the report for ‘total sessions by mobile browser’ (provided there is data available to answer your question):

Check out this cool feature on your own and see how many answers you can get from this powerful ‘Q & A’ feature of the Power BI tool.

There are a ton more things you can do with Power BI. But it is impossible to cover them all in one just one article. However, I hope, you got a basic understanding of what Power BI is and how it can be used.

To learn more about Power BI, check out the documentation here: Get started with Power BI.

There is a big Power BI users community out there and tons of tutorials are available online for further reading and support.

Integrating Power BI with Google Analytics – Desktop Version

Power Bi Desktop is free visualisation software that you can install on your machine.

With Power BI desktop You can connect to more data sets using the ‘Query Editor’ and combine data. This is often called data modelling.

You can download the desktop version on your local windows machine and install it. Use this link to download the latest version of Power BI Desktop “https://powerbi.microsoft.com/en-us/downloads/

Note: Power BI Desktop software is updated monthly and released. Only the latest version of the software is supported.

Power BI Desktop has three views available in the console for reporting and data modelling:

  • Report: In Report View you can create and reports and visualisations.
  • Data: In Data View you can see the tables and other data which are associated with your reports
  • Model: In Model View, you can define and manage the relationship between the different data sets.

To integrate Power BI Desktop with Google Analytics, follow the below steps:

Step-1: When you open Power Bi Desktop, click on ‘Get data’ in the menu option available in the ribbon.

Step-2: An overlay will appear, as below. Click on ‘Online Services’.

Step-3: Select Google Analytics and click on ‘Connect’.

Step-4: A pop up will come like below, click on ‘Sign In’.

Step-5: Enter the email to which your Google Analytics account belongs and complete the sign-in process.

Step-6: Once you are signed in, click on ‘Connect’.

Step-7: You will get an overlay, like below, where you can see the number of views you have access to, click on any view for which you want to create a report.

Step-8: You get all the reporting options available. You can select any of them based on your needs. As an example, I am selecting ‘Event tracking’.

Step-9: Select the metrics and dimensions you are interested in, like below.

Step-10: You can see the preview of the data selected and click on ‘Load’.

It will now load all the selected metrics and dimensions.

Congratulations! You have successfully integrated Power Bi Desktop with Google Analytics.

Creating reports and dashboards – desktop version

Once you have loaded all of your required Google Analytics metrics and dimensions, you can use them to create reports.

You can select the visualisation type and drag it to reporting canvas.

The next step is to add fields to the visualisation type. This can be done by selecting the required metrics and dimensions available in the “Fields” option on the right-hand side.

Once you have selected the metrics and dimensions, the visualisation chart will show data in the reporting canvas.

You can also apply filters to your report. Just simply drag and drop the required metric or dimension from ‘Fields’ into the ‘Filters’ section.

So, that is how you can use Power Bi Desktop for creating reports and visualisation with your Google Analytics data.

Limitations of Power BI

There are some limitations of using Power BI for Google Analytics, as follows.

  1. API restrictions: Power BI has restrictions on the number of Google Analytics dimensions and metrics values for a single API call. Currently, you can call a total of seven dimensions and ten metrics from Google Analytics in a single API call. This limit will unnecessarily force you to create multiple API calls to Google Analytics.
  1. Sampled data for high traffic pages: Google Analytics automatically samples data for high traffic web pages and hence you might get incorrect values in Power BI for such pages.
  1. Sampling check: Power Bi doesn’t let you check the data in your report is sampled or unsampled.
  1. Aggregation and averages: The data in Power BI is already aggregated and averaged and because of this you cannot use already aggregated or averaged data for further calculations. Also, some of the aggregation metrics are useless. For example, you can not count the daily user and then sum up to a weekly or monthly number of users

Frequently Asked Questions About Power BI Google Analytics Tutorial – Visualize GA Data

What is Power BI?

Power BI is a reporting and visualisation tool from Microsoft. You can connect this tool with different data sets, combine them and create an appealing report and visualisation. There are two versions of Power Bi,
·        Power BI Services (online)
·        Power BI Desktop (software) which can be installed on a Windows machine.

What is data visualisation?

Data visualisation is the presentation of data in graphical format. Data visualisation not only helps in data interpretation but also help in data retention and telling meaningful, emotional and engaging stories to key decision-makers.

What is a Power BI dashboard?

In the context of Power BI, a dashboard is a collection of data visualisation tiles from one or more reports and/or datasets. You can also pin an individual data visualisation tile (instead of the entire report) to your dashboard from one or more connected datasets. When you first create a Power BI account, one dashboard is automatically set up for you.

What is a Power BI tile?

In the context of Power BI, a report is one or more visualisation tiles. These visualisation tiles can be on one page or multiple pages. A single visualisation title is also known as a single visualisation. This is what a visualisation tile looks like in Power BI:

visualization-tile

Can I Get all metrics and dimensions from Google Analytics into Power BI?

Yes, you can get all the metrics and dimensions from Google Analytics into Power BI. But in a single API call, you can get seven dimensions and ten metrics. So, you will have to create multiple API calls to get all the metrics and dimensions from Google Analytics.

Register for the FREE TRAINING...

"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."



Here’s what we’re going to cover in this training…

#1 Why digital analytics is the key to online business success.

​#2 The number 1 reason why most marketers are not able to scale their advertising and maximize sales.

#3 Why Google and Facebook ads don’t work for most businesses & how to make them work.

#4 ​Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

#5 The number 1 reason why conversion optimization is not working for your business.

#6 How to advertise on any marketing platform for FREE with an unlimited budget.

​#7 How to learn and master digital analytics and conversion optimization in record time.



   

My best selling books on Digital Analytics and Conversion Optimization

Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.

Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.

Attribution Modelling in Google Analytics and Beyond
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.

Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.

About the Author

Himanshu Sharma

  • Founder, OptimizeSmart.com
  • Over 15 years of experience in digital analytics and marketing
  • Author of four best-selling books on digital analytics and conversion optimization
  • Nominated for Digital Analytics Association Awards for Excellence
  • Runs one of the most popular blogs in the world on digital analytics
  • Consultant to countless small and big businesses over the decade
error: Alert: Content is protected !!