If you run a website, you need to connect Google Analytics with a visualization tool. Power BI and Google Analytics are better together. Users can quickly and easily explore Google Analytics information using one of the most popular business intelligence tools on the market today.
Establishing a connection between Power BI and Google Analytics is really just the beginning. We’ll take a look at some ways to find the right metrics to put the treasure trove of website data to use in a dashboard.
Connect Power BI to Google Analytics in Power BI Desktop by going to Get Data > More > Online Services > Google Analytics. Log into your Google account when prompted. Next, select an implementation method. 1.0 has predefined metrics while 2.0 gives provides more precise control.
Table of Contents
We’ll also look at some alternatives to Power BI for the budget constrained or if you prefer to work within the Google ecosystem.
Let’s take a look!
Connecting Power BI to Google Analytics
Getting Power BI and Google Analytics connected together is often the easy part. Microsoft’s built in connector is very user friendly and easy to use.
Go to the Power BI Ribbon. Select Get Data > More > Online Services > Google Analytics
Get data is found when first opening Power BI Desktop, or from the Home section of the Power BI Desktop Ribbon.
The Google Analytics connector is available under Online Services and comes pre-installed with Power BI Desktop as a built-in connector provided by Microsoft.
The Google Analytics connector comes in two different versions.
While both connectors will allow you to pull Google Analytics data into Power BI, there are a few important things to keep in mind about using version 2.0 over 1.0
- 1.0 is easier to use for most users – The 1.0 implementation is organizes measures and metrics into folders and provides a more curated experience. It is easier to put together a report highlighting the most popular and common calculations.
- 2.0 Has better Performance – Data pulls in faster in the 2.0 version of the Google Analytics Connector. Data pulls can take a long time depending on how detailed the data is and how large the Google Analytics Property or website is.
- 2.0 Has more metrics and measures – Power BI developers will find the exhaustive list of possible calculations and data points fairly extensive. The list of metrics and measures can be almost too much at times and it can be a challenge to find the right combination of data.
NOTE: Google Analytics GA4 is only compatible with Implementation 2.0
In the screenshots below, we used the 2.0 implementation of the Google Analytics connector, which is currently in beta.
The next screen gives an extensive list of measures and metrics.
Users will need to select a combination of them to pull into Power BI
When pulling data into Power BI, keep in mind that you can build relational models within Power BI. This means that you can bring in multiple queries, but will need to be able to relate them based on a commonality.
Many times the commonality is PagePath but could really be anything.
The more granular and detailed the data is, the longer it will take to import.
Working with Google Analytics Measures and Metrics
Google Analytics is multi-dimensional. It allows you to analyze data across multiple dimensions simultaneously, such as time, geography, device type, user behavior, etc.
These dimensions can be paired with various metrics to provide a comprehensive view of website performance and user interaction.
The example below is a screenshot from the 1.0 implementation.
You have to select both a dimension and a metric for the query to work.
The difference between a Matric and a dimension can be somewhat confusing. For a query to work, it will need both pieces.
Metrics
- Definition – A metric is a quantitative measurement that provides numerical data.
- Example – Pageviews, Bounce Rate, Active Users.
Dimensions
- Definition – A dimension is a qualitative attribute that can be paired with metrics to segment data.
- Example – Country, Device Category, Browser.
It may take some experimentation to figure out which combination of Dimensions and Metrics are needed. Google will not let you run a query that is not valid. So, if you get an error you won’t have to wait for a bad query to run.
Finding the Right GA4 Dimensions and Metrics
One of the most difficult parts of using Google Analytics is determining which dimensions and metrics to use. Google provides a tool to view different possible combinations in the form of a query builder.
There is a Universal Analytics (UA) version along with a GA4 version you can find here: GA4 Dimensions & Metrics Explorer
Editing a Google Analytics Query in Power Query
One of the challenges with connecting to Google Analytics is that it connects to data as a datacube. This means that you cannot interact with the data in the same way that you would when working with an Excel file or a SQL database.
Go to the Power Query Editor in Power BI.
Instead of clicking on the source settings like you would on most data sets, click on Added Items.
The Power Query Ribbon will have a Cube Tools Manage option.
From here you can Add Columns to choose different combinations of metrics and dimensions to work with.
It’s not an intuitive way to modify a query, but it is easier than starting completely over from scratch.
Why Does Power BI Take a Long Time to Import Google Analytics Data?
If it is taking a long time to pull in a dataset, you may need to limit the query. For example, if you want to bring in something like Page Views by Page by Country by Day and you have a lot of content it can take a long time for Power BI to download all of the data.
You basically have two options for brining in large datasets.
Option 1 – Modify the query to bring in less data at once. For example, use 28 day active users instead of daily active users.
Option 2. – Schedule Power BI to pull the data for you during off hours
Part of the Power BI architecture is to download data, and store it into a Power BI .pbix file. Even though the data structure it self is very efficient, it can still take a long time to download. We imagine that Google also has some limitations on how much data can be downloaded at one time or throttles the API to prevent people from using up too much compute power.
Looker Studio as a Free Alternative
Power BI is a great option for visualizing data, especially if you already use it for business intelligence use cases. Once you bring in the Google Analytics data, you can work with like you would any other data source.
Looker Studio is produced by Google, and integrates very well with Google Analytics and Google Search Console “GSC”. There is no native connector in Power BI to Google Search Console, though you could establish a connection by API. A number of GSC metrics are also available in Google Analytics but there’s not a 100% overlap.
Look Studio also costs $0
This makes it one of the best free options on the market today for data vis. Power BI Desktop is also free, but licensing costs come into play as soon as you publish and want to share a dashboard.
To learn more about Look Studio and Google Analytics integration, check out the video below!
There are a number of great YouTube videos to help people get up to speed with Looker Studio as well. In our experience it’s not as full featured as Power BI or Tableau but Google continues to improve and invest in the product over time.
Conclusion
While establishing a connection between Google Analytics and Power BI is relatively straightforward, determining the right combination of metrics and dimensions can be time consuming.
For efficient building of dashboards, try to bring in a small dataset at first, and expand it later. For example, use 28 day active users then expand to individual users by day. The datasets can get quite large, and you can choose to either limit the dataset or schedule it on the Power BI Service to load data during off hours.
For a free alternative to Power BI, try using Google Looker Studio. It integrates easily with other Google products and is completely free to use.