As an avid Google Analytics user, I’m constantly leveraging Looker Studio’s exceptional visualisation capabilities to gain insights from my GA4 data. However, sometimes I run into API quota limits, which can be challenging to navigate. Nevertheless, there are ways to overcome this bottleneck.
Instead of exploring the solutions for navigating GA4 API quota limits in Looker Studio, we will focus on importing our GA4 data into another business intelligence tool for data visualization.
My recent attempt and learning exploit with Microsoft’s business intelligence tool, Power BI, prompted me to write this blog post. This article will explore various options for importing your GA4 data into Power BI, their pros and cons, and a brief overview of visualising it in the tool.
If this piques your interest, let’s delve into the details.
Getting Google Analytics (GA4) data into Power BI
Power BI now offers a native Google Analytics connector (GA4). In this guide, I will demonstrate how to utilize this connector and the additional options available, which are;
- Using the Google Analytics 4 Native Power BI Connector
- Manual CSV export of your GA4 data
- Pulling GA4 data into Google Sheets/Excel Sheets and then to Power BI
- Using GA4 & Big Query
- Using Data Connectors query API
- Using Data Hubs platforms
- GA4 Data API
Using the Google Analytics 4 Native Power BI Connector:
To access the GA4 native connector in Power BI, you will utilize the same Google Analytics connector previously used to connect and import data from Universal Analytics (discontinued for standard users on July 1st, 2023).
It is worth noting that, as of the current time of writing, this connector is still in beta and relies on the Google Analytics Data API, which may be subject to API quota limits. With that in mind, let’s explore establishing a connection to GA4 data using this method.
To begin, click on «Get Data» in the Home ribbon. Then, select «More» to view additional connectors, including the Google Analytics connector.
You can either use the search field to locate the connector or navigate to the «Online Services» data connector category and choose the «Google Analytics» connector. Once found, click on the «Connect» button.
You can skip this optional step in the connection process by clicking the «Continue» button. However, a third-party notice will appear if this is your first time retrieving data using the Google Analytics connector. If you prefer not to see this message again, choose «Don’t warn me again with this connector.» Then, select «Continue» to proceed.
In this subsequent step of the connection process, you will need to choose a version of the native Google Analytics connector you wish to use. Our goal is to create reports in Power BI using GA4 data, and we will opt for the «2.0» version and then click the «Ok» button.
Since this is your first time using this connector, you will need to authenticate your Google Analytics account on the following screen by signing in with your Google Account. To begin the process, simply click the «Sign In» button.
On the authentication screen, you can select the Google Account with access to your Google Analytics data or add another account by choosing the «Use Another Account» option.
Upon completing the Google Account authentication on the following screen, you will need to grant Power BI permission to access and download your Google Analytics data. Possible by clicking the «Allow» button.
Once the authentication is successful, a confirmation message will appear in your browser. You must then return to the Power BI interface to continue the integration process.
In Power BI, you should notice that the message changes from «Sign In» to «Sign In as a different user,» indicating that you are already signed in. The next step is to click the «Connect» button.
If you encounter an error message, it may be because the connector version is still in beta. In such a case, simply click the «Retry» option and follow the connection wizard again. This should resolve the issue.
On the next screen, you will see a list of all the Google Analytics accounts your Google Account has access to. You will need to select the specific account you intend to use.
For illustrative purposes, I’ll choose the «Google demo store.» To proceed, you will need to navigate through the Google Analytics account until you reach the GA4 property. Here, you can select the dimensions and metrics you want for your visualization.
* Take caution as you may encounter scoping issues and limitations in selecting Google Analytics data dimension fields.
After selecting the necessary dimensions and metrics, you have the option to either load the data as it is or transform it before loading it into your Power BI dashboard interface.
Congratulations! You have now successfully connected your Google Analytics to Power BI and loaded your GA4 data using the Native Google Analytics connector. You are all set to begin working with your data in Power BI.
What are the benefits of using the native GA4 connector?
- User-Friendly: The native GA4 connector is designed to be easy to use, making it accessible even to non-technical users.
- Speedy Connection: It offers a swift and efficient way to connect GA4 data to Power BI.
- Cost-Free: Using this connector comes at no expense, making it a budget-friendly option.
- Automatic Data Refresh: Your data gets refreshed automatically without requiring manual intervention, ensuring up-to-date information.
- Suitable for Sample Dashboards: This connector works well for projects where you don’t need an extensive number of dimensions and metrics, making it ideal for creating sample dashboards.
What are the disadvantages of this method?
- API Quota Limits: The direct integration with the GA4 data API may lead to limitations on the number of queries per day due to API Quota limits, impacting data retrieval.
- Scoping and Cardinality Issues: You may encounter scoping and cardinality challenges while using this native GA4 data connector. However, the article discusses an alternative BigQuery method to overcome these limitations.
- Beta Version: As the GA4 version of the connector is still in beta, it is possible to encounter errors and connection issues during its use.
- Dimension Limitation: Importing more than nine dimensions at a time could trigger a GA4 data API error, which might restrict data importation capabilities.
Some Of The Issues Of This Method
Here is an example of the scoping error message.
Here is an example of a dimension limit error message
Doing a Manual CSV Export To Power BI
This method is relatively easy and doesn’t require deep technical expertise. But first, you must generate a CSV format of your GA4 data and import it into Power BI.
So, how do you obtain your analytics data in CSV format?
One way to do this is by exporting the data from the GA4 user interface.
Exporting From The GA4 UI
While this approach may seem simple, it also has its drawbacks. In Google Analytics 4, there are several ways to export data, including from the reports, explore and advertising tabs.
To export data from the standard report tab, navigate to your desired «standard» report in GA4, click the share icon, select «Download File,» and choose «CSV» as the file format. However, not all chart data will get exported, and you have no control over what gets downloaded to the CSV file.
Select the «Download File» export option.
Choose «Download CSV» to download the report as a CSV.
Alternatively, a more refined approach is to create an exploration «Free Form» data table with desired dimensions and metrics in the exploration report. Once completed, you can export your data by clicking the «Download» icon and selecting «CSV» as the file format.
Select CSV as the file format.
It’s worth noting that when exporting your GA4 data, you must clean up the file before importing it into Power BI.
How Do You Import Your CSV File To Power BI
To import your CSV file to Power BI, follow these steps:
- Open the Power BI visualisation interface.
- Click on the «Get Data Source» menu widget.
- Select «Text/CSV» from the list of options.
- Choose the Google Analytics 4 file that you exported as CSV.
- You can either further transform your data in Power Query or Click «Load» to import the data into Power BI.
What are the benefits of using the manual CSV export approach?
- Any Google Analytics 4 user can do this.
- It’s a fast and efficient way to obtain your data in CSV format.
- This method also provides an easy way to generate sample data for dashboard prototyping when building a GA4 dashboard.
What are the downsides of this method?
- The data source is locally stored on your PC and isn’t accessible by other collaborators.
- This method works best for analysts working with small to medium-sized datasets, as there is a threshold for what is exportable.
- This approach is not ideal for projects where data needs to be refreshed at certain time intervals, as it requires manual refreshes.
- A manual data update is necessary when new dimensions and metrics get added or removed from the exported GA4 data set.
Exporting Data from GA4 to Google Sheets/Online Excel Sheets and Importing to Power BI
To use this methodology, you must take an extra step by getting the data to a Google Spreadsheet or an online Excel Spreadsheet. This approach is more advanced than the first one but straightforward.
To simplify the process, I will break down this section into two parts:
- Importing GA4 data into a Google Sheet.
- Importing GA4 data into an Excel Spreadsheet.
Importing GA4 data into Google Spreadsheet
From within the GA4 user interface, you can export your data to Google Sheets. However, there are more efficient options available that can automate the import process and data updates, which cannot be achieved through the manual export process from the GA4 UI.
Third-party data connectors can help with this task, allowing you to import and update your Google Analytics 4 data into Google Sheets automatically.
Some examples of these connectors include DataSlayer.ai, Supermetrics, and Mixed Analytics. While they have similar processes and interfaces, Mixed Analytics can be a bit more complex.
An alternative option is the «GA4 Magic Reports» tool, which was recommended to me by my good friend Brais Calvo. As of the time of writing this article, the tool is free to use and does not require any sign-up. The tool can be accessed with this link. (One downside of this tool is modifying your query; it’s not straightforward).
Generally, the process involves selecting your data source (GA4), authenticating your account, and connecting by choosing your preferred GA4 account. Then, you can select the metrics and dimensions to include in your report table.
For this demo report, I used DataSlayer.ai as the data connector to import my analytics data into Google Sheets, as shown in the image below.
After installing the DataSlayer extension, navigate to the «Extensions» menu to find the DataSlayer Sheets extension.
Select your connector data source, authenticate and select your Google Analytics property.
Select your GA4 property, build your report query and click the «Generate Table» button to get your data and schedule report updates.
Supermetrics Users
After installing the «Supermetrics» sheet extension, launch it by going to the extension menu.
It will launch the Supermetrics widget, select your data source (GA4), authenticate and choose your GA4 account and property.
Build your report query, and hit the «Get Data to Table» to generate the report and configure schedule settings.
Using «GA4 Magic Reports»
After you’ve installed the extension, click on the extensions tab to find the «GA4 Magic Reports» extension and click the «Create New Report» option.
To configure your query, you need to provide a name for your report, which will be used by «GA4 Magic Reports» to name the new tab for displaying the report data. Additionally, you’ll need to select your Google Analytics account, GA4 property, report date range, metrics, dimensions, and filtering and sorting arrangements.
Here is what my final setup looks like.
«GA4 Magic Reports» extension has a few limitations, which are;
- No flexible date range options.
- Report query modification isn’t straightforward.
- The data is presented with additional information about the query report and some summary aggregations that may not be compatible with Power BI. Therefore, you will need to clean it up manually.
Here is an example of the report displayed when queried.
How to Connect Google Sheets to Power BI
There are so many ways of doing this, but I’ll go over just two methods, which are;
- Using the built-in Google Sheets connector
- Using the native Power BI web connection
Using the built-in Google Sheets connector
Importing GA4 data into Power BI through Google Sheets is relatively straightforward. Follow these simple steps to do so:
- Click «Get Data» and select «More» to view all available connections.
- Search for «Google Sheet» in the search bar or navigate to the «Other» connector type and scroll to the «Google Sheets» connector.
- Follow the integration wizard, and you’ll get to a screen with a field to add your sheet URL and click «connect«.
- If this is your first time, you get asked to sign into your Google account for user authentication.
- Select the sheet containing your GA4 data and select «Transform Data«.
- You may need to transform your data in «Power Query» because the sheet headers get added as an extra cell. To correct this, select the «Use First Row as Headers» setting in the transform section of Power Query.
- Once you complete all your transformations, click «Close and Apply» to build your visualisations using the metrics and dimensions in your Google Sheets import.
Search for the «Google Sheets» native connector.
Alternatively, find the Google Sheets connector in the «other» connector types.
Click «Continue«.
In this screen, you’re to add your spreadsheet URL.
Add your Google spreadsheet URL
In the navigator screen, select your worksheet and hit «Transform Data» to make the first row the table headers, and any additional data manipulation in «Power Query«.
Using the native Power BI web connection
To utilise this approach, you must first publish your spreadsheet online.
Start by clicking the «File» menu option, and select «Publish to the Web«.
You can select the whole document or a specific sheet to publish and click «Publish.»
Ensure you’ve checked the «Automatically republish when changes are made» option.
Next, copy the published link and go to Power BI.
Select «Web» as your connection under Power BI’s «Get Data» menu.
Enter the sheet’s URL in the URL field and click «OK» to proceed. (Ensure «basic» is your selected conenction type)
Once the spreadsheet appears, hit «connect» to import your data
After establishing the web page connection, you can select the preferred table you want to import.
Finally, as usual, you can transform your data in Power Query or directly load it into your data visualisation project.
Importing GA4 Data Into An Online Excel Spreadsheet
Many organisations using Microsoft business solutions favour Excel over Google Sheets. One of the data connectors that can import marketing data into an online Excel spreadsheet is «Supermetrics.«
After importing your GA4 data into your online Excel sheets, you can import it into Power BI. The easiest way is using the built-in «Web» connector, which we covered earlier.
However, this time your web URL is your Excel spreadsheet file path.
What Good Does Using Google Sheets & Excel Have To Offer
- The process is automated, and a refresh happens in one click
- The data source and connection are easy to setup
- Enables team collaboration by providing a shared data source not confined to a single desktop.
The Downsides Of Using Online Spreadsheets Like Excel & Google Sheets
- Every time changes get made to your Google Sheets data, you need to manually click refresh in Power BI for the changes to take effect.
- When working with large GA4 data sets, you may encounter sheet limits, which can cause issues with data importation.
Using GA4 & Big Query
The easy integration of Google Analytics 4 with Big Query, available for both standard and 360 customers, is an exciting feature. In addition, its data schema is less complicated than Universal Analytics, making it even more appealing.
If you are searching for ways to connect GA4 to Google Big Query, the Datola article in Spanish is a helpful resource. This linked article can be useful for assistance with analysing and writing SQL queries.
Furthermore, a free worksheet is available for you to use as a checklist and document for integrating Google Analytics and Big Query.
Google BigQuery is also one way to overcome the famous GA4 API quota limit in Looker Studio. For example, here is an article offering a use case for visualising Google Analytics file download events in Looker Studio or how to build a GA4 real-time report in Looker Studio using BigQuery with the help of Google Big Query and its native connector.
Prepping Google Analytics 4 Data In Google BigQuery
You need to follow some prerequisite steps before establishing the Big Query connection. This step involves creating the Big Query data table that you’ll use for your Power BI visualisations.
To begin, you need to determine the metrics and dimensions required to answer the business questions in your dashboard. Once you figure that out, you can create the SQL query and save it as a «BigQuery view» or «scheduled table,» which will automatically update when new data is collected.
Here is a simple approach to follow, click the «Schedule» menu option with a «clock icon» and select the «Create new schedule query» option.
Configure your schedule table settings by giving it a name, repeat frequency, time, a start and end date.
Next, set the destination for your scheduled query result table.
Lastly, ensure the destination table write preference is «Overwrite table«.
Configure your Big Query table location, and click the save button.
Here is to create a view in Big Query;
After creating and executing the SQL query, you used in generating your desired GA4 dimensions and metrics in a table.
Click the «Save» workspace option and select «Save View«.
In the next screen, select the project, and dataset, give the view a name, and hit save.
The view should appear under the list of tables under your GA4 export data set.
How To Connect Big Query To Power BI
Connecting Power BI to Google Big Query is a relatively straightforward process, and there is already a native connection available.
To begin, click «Get Data» and select «More» in Power BI to view all data connection types.
To find the connector, enter «Google BigQuery» in the search bar or select «Database» Type connectors and scroll down to find «Google BigQuery«.
Search for «Google BigQuery«
Alternatively, navigate to «Database» and scroll to «Google BigQuery«.
If you want to connect to a single Google Cloud project using SQL commands to create the GA4 data table imported into Power BI, use the «Advance Options.» However, in most cases, when working with a GA4 data set, you can skip the «Advance Options» part and click «OK.»
If you’re working with a GA4 data set, skip the next two steps.
Since you’re working with Google Analytics 4 (GA4) data, skip the last two steps in the image above and click the «OK» button.
Next, you must authenticate your Google service account on the following screen. Once completed, click the «Connect» button to complete the integration process.
Select «Organizational Account» as the authentication method.
Select the Google account to be used for the authentication.
Authenticate your BigQuery Google account.
You’ll get this message once it’s successful.
You should see this screen in the Power BI interface and click «Connect.»
A Navigator window will display the data available under your Big Query account after a successful connection. You can select your data with the option to either «Transform Data» to transform the data in Power Query or «Load» to load the data in Power BI Desktop.
Note each day GA4 table gets represented as a separate table.
What Benefits Comes With Using GA4 & BigQuery For Data Import To Power BI
- No limit to the size of data you can work with.
- Your data set is always up to date, thanks to automatic updates as new data is collected.
- No data sampling, making it suitable for working with large data sets.
- It enables long-term analysis as you can create a table with over 14 months of data, exceeding the GA4 retention period, only if your linking has exported data for over 14 months.
- Access to all event parameters sent in a GA4 event, whether registered as a custom dimension/metric or not.
- Data incompatibility due to scopes won’t be a problem.
Downsides To This Approach
- Querying data in the data warehouse requires SQL knowledge.
- There are additional business costs associated with using BigQuery.
- It’s important to note that data collection starts from the day the BigQuery link with GA4 was established, meaning historical data won’t be available.
Using Data Connectors query API
The term «API» may seem intimidating, but it’s not much different from the connectors you’re used to in Looker Studio. However, API connectors may differ slightly from your usual Looker Studio connector.
The good side of this approach is that with zero knowledge of SQL or data engineering, you can generate GA4 data with third-party connectors like Supermetrics and DataSlayer.ai. In addition, these connectors offer an intuitive interface for building your query and obtaining GA4 data.
If you choose to work with DataSlayer.ai, the initial stage will be creating a new query (kind of a workspace) and giving it a name. Then, like Google sheet data connectors, you’ll select your GA4 data source.
After authenticating your account and selecting the GA4 account and property to pull data from, you’ll choose the metrics and dimensions you’ll use for your data visualisation project. Other data configuration settings, such as date and filters, may also be available.
Once you’ve selected your metrics and dimensions, save the query, and your data table will be available for preview. To connect with your BI tool, ensure the query data format you’ve selected is Power BI.
After generating your preview data table, copy the report link and go straight to your visualisation project.
Using Supermetrics API Query Manager
To use Supermetrics, select your data source from the Query Manager product. Then, authenticate your Google Analytics 4 (GA4) account.
In the next phase, you will need to select your GA4 account and property. Additionally, you will select the metrics and dimensions that will be used for building the query and configure your report settings. You will also have the option to split your table and add the query to your schema.
Make sure to choose «Power BI» as your data format and copy the generated query URL, either long or short format.
Here is an example where the «Power BI» format is selected
Importing The Data Into Power BI
Importing the data is a straightforward process. Start by accessing the «Get Data» menu option from the «Home» section, then select «More» to reveal additional connector types.
Once on the new screen, look for the «Web» connector or choose «Other» and click «Web«.
Next, insert the URL you previously copied from the third-party connector API query manager into the designated field.
In the following navigator screen, choose your Google Analytics (GA4) data table and decide whether to transform the data or load it directly into your visualisation project.
What are the benefits of using this approach?
- The GA4 data table is automatically updated when new data is collected, eliminating the need for manual updates.
- No SQL expertise or knowledge is required to import the data, making it accessible to users without technical skills.
- It can handle extensive data sets too big for Google Sheets, providing more flexibility.
- Data sampling is not an issue, allowing you to access complete and accurate data for analysis.
What is the downside of taking this route?
- GA4 standard users cannot visualise data older than 14 months due to data retention settings.
- Data incompatibility can arise from scopes in GA4.
Data Hubs Platforms To The Rescue
If you have a Funnel.io business subscription, you can use its data hub platform’s native Power BI connector.
Data hubs differ from data warehouses like Big Query and Azure.
Funnel.io’s data hub lets you import GA4 data and perform calculations and transformations before importing it into Power BI. In addition, their user interface is user-friendly and easy to use.
It’s important to note that Funnel.io is just one of many data hub software available and that data warehouses and data hubs are two different types of data collection and storage systems with critical differences in purpose, data structure, processing, and target users.
Here is the Funnel.io Power BI connector
Benefits Of Using Data Hubs Solutions
- Automated GA4 data set updates on new data collection.
- Capable of handling large data sets.
- Extracting data from GA4 to the data hub and importing it to the Power BI desktop is straightforward.
- No technical expertise is required, just familiarity with the data hub platform.
Some Things To Consider With This Approach
- Adequate knowledge of the data hub platform (Funnel.io) is necessary.
GA4 Data API
While it may be the most advanced method for retrieving GA4 data and loading it into Power BI, it does demand expertise in programming and a significant time commitment to write the necessary code.
Although unfamiliar with this procedure, resources are available for those interested in exploring it further.
Data Visualisation In Power BI
Due to how long this blog post has become, I won’t be delving into the detailed step by step of doing the actual visualisation in Power BI, but if you’re interested, here are some standard visualisation charts that are available by default;
- Column Chart
- Line Chart
- Bar Chart
- Area Chart
- Stacked Area Chart
- Scatter Chart
- Bubble Chart
- Pie Chart
- Donut Chart
- Tree Map
- Heat Map
- Gauge Chart
- Waterfall Chart
- Funnel Chart
- Radar Chart
Power BI offers a range of custom visualisations that can be downloaded and embodied into your reports for a more tailored data visualisation experience.
It’s Time For Some Goodbyes
The read may be lengthy, but it covers the different options to connect GA4 as a data source to Mircosoft’s Power BI. These options vary in difficulty, and some have their advantages and disadvantages.
The comment section is excellent for sharing general ideas or questions regarding GA4 and Power BI. In the meantime, happy measuring!
Jude Nwachukwu
I Love watching magic videos and documentaries and googling tourism destinations I end up not visiting. I write about measurement topics in my free time and love helping non-technical marketers succeed in the ever-changing measurement space. I'm a marketing analytics specialist with Hedy and Hopp (a Healthcare marketing agency based in St Louis, US) and DumbData’s co-founder.