This article is to help you get the Custom Power BI Connector setup to a point where we can start to make queries against the GA4 Data API.
If you haven’t read the first part of this series, you should go have a read. In summary, we are trying to get a custom GA4 data connector up and running for Power BI.
If you haven’t used a custom data connector before it can feel a little scary and a tiny bit sketchy. It will be fine, there shouldn’t be any issues if you act with reasonable caution and care. I’ll try and support fixing any issues, but you proceed at your own risk.
Steps To Set it Up
The Google Data API at time of writing is still in beta (mostly, some in alpha) and so will involve some work to getting I going, but it does work if you are willing to put in about 20 mins to set it up.
- Setup a project for our connector in Google Cloud (4 mins)
- Allow that project access to the Google Data API (6 mins)
- Setup some authentication stuff so Google recognises our connector (7 mins)
- Prepare the folder structure (1 min)
- Change a couple of settings in Power Bl (1 min)
- Add our authentication details to our connector (2 min)
- Fire up Power BI and get started with queries (as long as you want)
Let’s Get Ready to Query!
Essentially what we are creating is a client application that is going to connect to a resource (GA4 API) . To do so it’s going to authenticate you, the user (or anyone you share the connector with), with Google using OAuth. You’ve undoubtedly seen OAuth in action before when logging into websites using Google or Facebook credentials.
You don’t really need to know too much about OAuth as the connector is going to deal with that mostly for you. but we will have to tell Google that we are creating a new client app and that it will need access to the Google Data API. Then we’ll need to get a couple of bits of information that Google will need in order to recognise your connector and allow users to login and start querying the API. It’s a bit of a faff but it’s worth it and should only take a few mins.
Step 1 – Setup a Project For Our Connector in Google Cloud
First things first, if you haven’t previously used Google Clouds, you’ll need to register.
- Click console.cloud.google.com/ and follow the registration steps.
Once registered it should bring you to your dashboard. We will set up the project first.
- Create a new project by clicking in the ‘Select a project’ box at the top of the page and selecting ‘New Project’ on the resulting pop up.
- Set the project name; thinking up a name to call your project is the hard bit. I went for ‘PBI GA4 Connector‘. Your welcome to use that too
- Click ‘Create’ to initiate the project.
Well done, project created!
Step 2 – Allow That Project Access to The Google Data API
Annoyingly, by default it doesn’t take you into the project you just created so you need to select it.
- Either click ‘Select Project’ from the notification pop up, or select it from the projects list at the top again.
- If you’re still on a welcome screen, select ‘Dashboard’ to get to the project interface.
The next thing we need to do is tell Google what our project will need access to. That is, we need to enable the APIs we need.
- On the left hand navigation, click the ‘APIs and Services’ > ‘Enable APIs and Services’.
- Now on the API and Services page, select ‘+Enable APIs and Services’ from the top bar.
- Search for and select the ‘Google Analytics Data API‘
- There are a few analytics APIs so make sure you get this one specifically. The others are for UA.
- Click ‘Enable’ and give it a few seconds to set that up.
That’s step 2 done!
Step 3 – Setup Some Authentication Stuff So Google Recognises Our Connector
When your connector’s user logs into authenticate the connector they will be shown a screen to let them know what they are authorising. In our case this is just accessing the read only bits of GA4. So lets configure that content screen.
- On the left navigation, select ‘OAuth consent screen’.
- Set User Type to ‘External’, and hit ‘Create’.
- Give it an App name (same as the one you used before,’PBI GA4 Connector‘ is great) and a support email address to help your users.
- Everything else is optional except the developer email address at the bottom. You can use my email address but if you are using the connector for your colleagues then you might want to use yours so they know who to bother for help.
- Hit ‘Save and Continue’.
On the next page you need to add the ‘scopes’.
- Click ‘Add or Remove Scopes’.
- Using the filter, search for ‘Google Analytics Data API‘.
- This is the level of access you are looking for. We are only interested in reading the data, so select the API with the scope ‘.
. ./auth/analytics .readonly’
- This is the level of access you are looking for. We are only interested in reading the data, so select the API with the scope ‘.
- Hit ‘Update’ to add this API to the scope, then ‘Save and Continue’.
The next screen adds test users. Since we’re still setting up, we’ll only add a couple of users here to ensure you can test the connector is working.
- Click ‘+ ADD USERS’, adding yourself and any testers, the ‘Save and Continue’.
- Once its working you can come back in and publish your project and then anyone can use it.
Almost there, I promise!
The last bit is to get the client authentication credentials we need to identify our app.
- On the left navigation, click ‘Credentials’.
- Click ‘+ Create Credentials’ from the top of the page, then select ‘OAuth client id’.
- From the application type dropdown, you’ll want ‘Web Application’.
- Give it the same name you used before (I used ‘PBI GA4 Connector’)
- The important bit here is the ‘Authorized Redirect URIs’. Select ‘+Add URI’.
- Google needs to know once it’s logged you in to return back to Power BI, so copy in the URL ‘https://oauth.powerbi.com/views/oauthredirect.html‘, and hit ‘Create’.
The next page is going to have two pieces of important information; ‘client id’ and ‘client secret’.
- You can keep the dialog open so they’re accessible for now, we’re going to use them in step 6.
- If you need to close the window, you can find these again by opening the project’s dashboard > APIs & Services > Credentials > OAuth 2.0 Client IDs, then selecting the download action of the credentials.
If you have made it this far you’re almost there, I absolutely promise you.
Step 4 – Prepare the folder structure
You need to create the folder directory where Power BI looks for custom data connectors when it starts. We will move the connector in here later.
- Create C:\Users\[customer]\Documents\Power BI Desktop\’
- Then C:\Users\[customer]\Documents\Power BI Desktop\Custom Connectors‘
(The bit in square brackets will be different on your machine)
Step 5 – Change a Couple of Settings in Power Bl
If this is your first custom connectors in Power BI, you’ll need to adjust your setting to allow these.
- Select File > Options and settings > Options > Security
- Under ‘Data Extensions’ you need to “Allow any extension to load without validation or warning”.
- This isn’t recommended by Power BI as there is a risk this could expose you to malicious connections. Therefore, I strongly recommend that you look through the source of any extension before you install it. We will see the source in the next step. I encourage you to have a look at my code review it, and feel free to send me any recommendations.
Step 6 – Add Our Authentication Details to Our Connector
Almost there…
- Download this file GA4PowerBIConnector.zip and unzip it anywhere for now.
There are a couple of interesting files in zip file. If you want to review the source, as suggested in the last step, do that now.
- The ‘GA4PowerBIConnector.pq’ file is the file that activates the connections and can be read in any text editor. It’s actually the power query m code that makes our connector work. Every part is commented, so its fairly easy to read through it to see the different processes at play.
- ‘Recourses.resx’ can also be opened in a text editor, it describes our connector in technical terms and is pretty boring.
You need to edit the ‘client_id’ and ‘client_secret’ files adding the authentication details you noted from step 3 in place of the placeholder xxxs.
- Open each file in notepad and replace the ‘xxx…’ with your own credentials.
- Save the file. If you do this with notepad it’s going to try it’s hardest to add .txt extensions back onto your filenames so you might need to rename the files to remove the file type.
Finally, we’re going to repackage the files and turn the file into a ‘mez’ file, and drop it where Power BI can see it.
- Select all the files (not the folder), Zip them back up (right click, send to > compressed zipped folder)
- Rename the resulting zip file while also changing the file extension to ‘GA4PowerBIConnector.mez’.
- Copy the .mez file to the ‘Custom Connectors’ folder you set up in step 4, and we’re all done with the fiddly stuff.
Step 7 – Fire Up Power BI And Get Started With Queries
Ok, lets get cracking in Power BI.
- Restart or start up Power BI.
- Select the ‘Get Data’ > ‘More…’ dialog and search ‘GA4’ to find the ‘GA4PowerBIConnector (Beta)’.
- Connect, and we are ready to start querying!
Step 8 – Optional
leave a comment to encourage me. Share with friends. Be excellent to one another. If you want updates just message me via contact form and I’ll email you if a new version comes out.
Common Issues With Setup
A few people have got in contact when the connector does show up or you can’t login. Don’t give up! you are so close. It’s almost certainly one of the following issues.
- Restart Power BI. Any changes will need a restart of the app
- When you zipped the files together did you make sure that you only zip the files and not the whole folder. if you click on the zip file you should be able to see what’s in it. There shouldn’t be a folder, just the files.
- Double check the file extensions. On windows it doesn’t show file extensions by default. So within the file explorer panel you need to click on the view menu, then on the ribbon you need to tick the ‘show file extensions’ box, I’ve seen a couple of files called GA4PowerBIConnector.mez.zip so was easy fix to correct to GA4PowerBIConnector.mez
- An odd one that I had today was that the user had 2 C:\Users\[customer] folders. Double check that you are using the right one
- If you see the connector but the authentication bit fails just double check that you removed .txt from the ‘client_id’ and ‘client_secret’ files. These files shouldn’t have a file extension. within the file explorer panel you need to click on the view menu, then on the ribbon you need to tick the ‘show file extensions’ box
- Check step 5 above and restart Power BI.
- If its a Gateway refresh issue. I’m working on it. Message me on the contact form and I will let you know when there are updates.
Those seem to have fixed every problem I have seen so far. If you are still stuck message me on LinkedIn.
photo credit Graeme Brogan
Hello,
First off thank you for the great GA4 connector.
I am trying to make it work with a data gateway as the code seems to be missing a TestConnection handler that is needed for a gateway to work.
I’m unable to figure out the right right parameters to call the TestConnection. Could you help me please? The guide below goes over some examples.
https://learn.microsoft.com/en-us/power-query/handling-gateway-support
Best regards,
Sander Verbruggen
Hiya
I know you figured this out because you sent me the following code. I’ve not managed to test it myself but worth posting for anyone who wants to try it out
// Data Source Kind description
GA4PowerBIConnector = [
// TestConnection is required to enable the connector through the Gateway
TestConnection = (dataSourcePath) => { “NavigationTable.Simple” },
Authentication = [
OAuth = [
StartLogin = StartLogin,
FinishLogin = FinishLogin
]
],
Label = Extension.LoadString(“GA4 Data API”)];
Hi, I would like to publish the dataset created with the connector on the service. I can do it but then I can’t update the data because the service doesn’t know the connector. I also added the changes for the TestConnection method. can you help me?
Thank you
Hi Gavin,
thanks so much for writing this connector! I managed to connect it perfectly and all standard dimensions and metrics can be found easily.
Yet I cannot connect to any custom dimensions.
They seem to be configured correctly (I checked Tag Manager for correct tags and connections to events), as I am able to build reports (Explorations) with them in Analytics.
Any idea how I could also see these in Power BI using your connector?
Regards Michiel Welmers
I need to give it a try, I think it will just be the name being supplied. This might help in the mean time
https://developers.google.com/analytics/devguides/reporting/data/v1/api-schema#custom_dimensions
I got them working with syntax from the API documentation:
Example:
customEvent:timestamp
customUser:userId
Capitalisation needs to be exactly what you configured when you set up the customer dimension.
Hi Gavin,
I’m trying to connect my PowerBI Desktop to your connector, and followed all of the steps. However, PowerBI is in my case unable to find the connector. Do you have any idea what’s causing this? I tried researching and asked ChatGPT, but none of the answers I found, helped my with this problem.
Kind regards,
Robin
I’ll send you a wee email.
Hi Gavin,
I ran into a similar problem as did Robin. PowerBI does not find custom connector. I did everything according to the steps from the article.
I will email you too. Positive we can get it working without to much issue
Hi Gavin,
I’m also running into the same issue!
I think it might be where I’ve saved the file, but any help would be greatly appreciated.
Thanks for going to the effort of providing this solution.
Cheers
will ping you an email
Hi Gavin,
Thank you very much for the instruction with all details needed. I am running into the same issue. I cannot find the connector from Power BI. Could you please help? Thank you.
If it’s not one of the above you might need to check if you have more users on your machine. I place my file here C:\Users\customer\Documents\Power BI Desktop\Custom Connectors but you might have more users in that users folder.
Hi Gavin,
I am running into the same issue. For some reason I haven’t been able to identify yet, PowerBI can’t find the custom connector.
Just wondering if you guys were able to fix it?
Thank you!
Hi Gavin,
Thanks for this wonderful connector, but I am having the same issue of Power BI doesn’t find the custom connector.
Any help appreciated!
Thanks,
Hi Bindesh, can you double check that within the following directory: “C:\Users\{YourName}\Documents\Power BI Desktop\Custom Connectors” folder, you have a single file called “GA4PowerBIConnector.mez” which is a MEZ file? If not maybe recheck some of the earlier steps.
Hi Gavin,
I ran into a similar problem as did Robin and George. PowerBI does not find custom connector. I did everything according to the steps from the article.
Hi Fam – can you double check that within the following directory: “C:\Users\{YourName}\Documents\Power BI Desktop\Custom Connectors” folder, you have a single file called “GA4PowerBIConnector.mez” which is a MEZ file? If not maybe recheck some of the earlier steps.
Hello Gavin,
Thank you for your connector. The company for which I work doesn’t want to use the Oauth consent screen and ask me to change the connector so that it asks just for a usernam and password to connect to the data source. I tried changing the following part to adapt the connector but I didn’t manage
appKey=Text.FromBinary(Extension.Contents(“client_id”));
appSecret=Text.FromBinary(Extension.Contents(“client_secret”));
redirectUrl = “https://oauth.powerbi.com/views/oauthredirect.html”;
token_uri = “https://accounts.google.com/o/oauth2/token”;
authorize_uri = “https://accounts.google.com/o/oauth2/auth”;
logout_uri = “https://accounts.google.com/logout”
Thank you in advance for your help
Hi
The OAuth is there to authenticate the user on the GA4 server and ensure the user have access rights to retrieve the data from GA. Google offers another option where you could use a service account to gain access which would mean you didn’t need either username or password. This might be a solid option if you planned to put it on your on-prems gateway.
https://developers.google.com/analytics/devguides/reporting/data/v1/quickstart-client-libraries
If your company want to just use a username and password what would they be authenticating against?
Gavin
If I understand it correctly this will not work for ‘PowerBI SSRS’ as it does not support custom connectors. And so once you drop the file into the custom connectors folder, it just does not show up in the ‘Get Data’ dialog. Is there an easy way around this, or did I miss something?
Hi
Sorry if I get this response wrong, SSRS isn’t really my thing. My intention was that you could avoid SSRS entirely and that you load data directly into PBI. To get data into SSRS I think you would need to build something in SSIS but I’m not an 100% in that side of things.
A couple of people are struggling with the last stages so have added a new section above for common fixes.
I can now confirm that it will both load in PowerBI Desktop and PowerBI Desktop RS.
For me the problem was that company laptops use OneDrive as default Documents folder and not the standard documents folder. For example:
C:\Users\username\OneDrive – companyname\Documents\Power BI Desktop\Custom Connectors
Does the connector not show up for you? I’ve not seen that setup before. The on-prem gateway uses a different folder although you need the newer code from the github to get it working. Not sure, anyone else seen this?
Hi Gavin,
Thanks for the fantastic tutorial. I was completely lost before this.
T think I’m almost there but I have a problem. When I select the new connector in PowerBI I’m presented with a Sign in screen. I click the sign-in button which opens up a new Google window That says “Access Blocked: Authorization Error – xxxx@email.com The OAuth client was not found. Error 401: invalid_client
I was sure to paste the client and secret keys. the files dont have extensions.
I’m a bit stuck. Any chance you could offer any advice?
Much appreciated.
Sorry for the multiple posts. I dont know what happened.
Update on my issue. It looks like my code editor added carriage returns in the client_id and client_secret files. I opened up in notepad and removed these returns. connects perfectly now. Thanks
Hi, I had to create a ‘Power Bi Desktop’ Folder in my documents folder on my company one drive and then create the custom connectors folder within that for my PowerBi desktop to find the .mez file. A colleague had the same workaround. I was struggling to find the correct place to save the .mez file and attempted multiple locations within my C drive. Just incase this is of any use for anyone else. Thanks.
That’s great advise, glad its working for you.
Hi Gavin,
Thanks for the great work, this has been a real lifesaver.
I am, however, having a small issue with authentication. It seems that my login only persists for about 20 or 30 minutes, at which point I have to reenter my credentials to access GA.
This issue occurs both in desktop and online. Desktop gives the following error: “We couldn’t authenticate with the credentials provided. Please try again”. When refreshing online through the gateway, it gives a much longer error but the main point is “The credentials provided for the GA4PowerBIConnector source are invalid. (Source at GA4PowerBIConnector.)”
I’m not sure if this actually makes a difference, but I have the privacy level set to ‘Organizational’ both in desktop and online.
I didn’t see any other comments about this issue so I’m worried that this might have something more to do with the security settings of my actual google account, but I really don’t have a clue if that’s the case or not.
Thanks!
Hello,
I was able to do one initial query but when returning back to the project days later I am now met with this error and cannot create anymore queries – any idea on what is causing this?
I’ve removed the property ID in the sample below.
DataSource.Error: Web.Contents failed to get contents from ‘https://analyticsdata.googleapis.com/v1beta/properties/PROPERTYID:runReport’ (400): Bad Request
Details:
DataSourceKind=GA4PowerBIConnector
DataSourcePath=GA4PowerBIConnector
Url=https://analyticsdata.googleapis.com/v1beta/properties/PROPERTYID:runReport
Try deleting the permissions in data source settings. Sometimes PBI can be a bit of jerk. I had to do this once at the start
Hi. Can you please help me? I have followed all your steps but when I search for GA4PowerBIConnector using GET Data, it doesn’t show anything. I’m thinking maybe it’s because of client_id or client_secret file. The text inside client_id file is like this after replacing all the
x:’PBI GA4 Connectorapps.googleusercontent.com’. Should me client_id name be like this? Thanks
If it’s not showing up at all it’s almost certainly one of the zipping or folder steps. Check the common errors bit above. Ping me on Linkedin if your still stuck. It will be something easy
Hi !
Thanks for sharing your solution to connect GA4 to Power BI !
I managed to do all the steps but .. When I select the connector in PowerBI, I can’t find the data ! I just have a “GET Reports” and that’s all. How can I find my data ?
Thanks in advance
Sure, check the other article to get started. I would do this one next First Query
Thanks for creating this!
I’ve got a few different GA Properties I’d like to use this on, all tied to different emails/accounts (likely somewhat of a niche case since clients grant us access to a specific gmail account).
I tried to just create separate connectors for each one, but PowerBI only displays one connector at a time, and seems to want to connect to the first account I created even if I change the name in the resources file.
Do you know what fields I’d need to change to set up several versions of the connector for multiple properties
Hi Gavin,
we managed to follow all of your steps and the connection for the PowerBI desktop is working very good. Thank you for that!
But now we want to bring that connection to powerBI Online. Do you also have a guide how to do that?
Thanks for your help and keep up the good work!
It’s not fully tested but the code in the GitHub should work using the data gateway. Your existing OAuth details should still work so just download the files from the GitHub repo copy those 2 client files over and rezip up as before. let me know how you get on.
Hi Gavin
we managed to follow all of your steps and the connection for the PowerBI desktop is working very good. Thank you for that!
We have linked Goolge Search Console to GA4. Under https://ga-dev-tools.google I can only find metrics for the search console. Is there a way to connect the dimensions “Organic google search query”?
Thanks for your help and keep up the good work!
I’ve built a data connector to bring the Google Search Console data into PBI separately, then just join in PBI. It’s a bit to janky to publish just yet but ping me an email or get me on LinkedIn and I could maybe try to hook you up
Hi Gavin, I managed to install it fine, but when I open it in PBI, it doesn’t show any accounts or properties. There’s no data at all. Any ideas? Thanks, Stephen.
There is another couple of articles in this series to get your queries working. Check out GA4 Connector First Query to get you started
Hi Gavin,
First of all, thank you for setting this up. Amazing.
Everything works fine, however, when I publish my dashboard is not able to refresh anymore. I am not sure how I can get this working and refresh my dashboard (containing other datasources).
Thanks in advance.
Thank you for connector solution!
More than happy to help
Hello, I managed to get this connected on my own computer. However I can’t seem to get this working for my colleague. As far as I understand, all I need to do is add their google account as a test user and give them the .mez file with the same credentials as mine, right?
The error they’re getting is “Expression error: Access to the resource is forbidden.”
Have they got access to GA4 account?
Hi,
Great article.
I am able to see the custom connector and connected to it. After connecting to the custom connector, all I see is in the Navigator dialog is, GA4PowerBIConnector –> Get Reports. When I click on Load, I don’t see any data loaded.
Hi Krishna. Rather than selecting “load”, try “transform data” which will open the GA4 query editor.
Hello Gavin,
Thanks a lot for your job and for the amazing tutorial!
I managed to retrieve the GA4 data, and now I wonder whether there is a way to set up dynamic date range? For instance, I want to retrieve all the data from Jan 1, 2023 until yesterday. Is there any way to set up “yesterday” as a value for dateEnd parameter?
I appreciate your advice!
You should be able to get this working using the DateTime function, so your first line looks like this: #”GET Reports”(#date(2023, 4, 1), DateTime.Date(DateTime.LocalNow()), …
Hi Gavin, I hope all is well.
I managed to configure your connector but I can’t get the update to work with the gateway, have you tried anything for that?
Hi Gavin,
Thank you so much for helping with this!! I did follow all the steps, but when I go to the GET DATA option and look for the connector we created with the same name as you have for some reason I don’t see it. What I see is Google Big query, Google Big query (Azure AD) (beta) and the regular Google analytics. I thought I would see “GA4PowerBIConnector (Beta)” Can you please advise?
Hi Seema – can you double check that within the following directory: “C:\Users\{YourName}\Documents\Power BI Desktop\Custom Connectors” folder, you have a single file called “GA4PowerBIConnector.mez” which is a MEZ file?
Hello, Gavin! Thank you for the connector but I have an issue – Power BI can’t find the custom connector:(
I did everything as described in this article.
Folder path is “C:\Users\[my username]\Documents\Power BI Desktop\Custom Connectors”
Please help me
Hi Andriy – can you double check that within the following directory: “C:\Users\{YourName}\Documents\Power BI Desktop\Custom Connectors” folder, you have a single file called “GA4PowerBIConnector.mez” which is a MEZ file? If not maybe recheck some of the earlier steps.
Hi Gavin,
Which email do you add as a test account?
My PBI won’t work as it is not a google email so I used my google email but the connector is not appearing in Power BI.
Regards,
Paul
Hi Paul, the connector being present or not is not connected to the email adress in any way (that comes into play when you’re setting up the query). Therefore we can assume that there might be a problem with your .MEZ file setup – do you want to just run through the steps again and double check that within the following directory: “C:\Users\{YourName}\Documents\Power BI Desktop\Custom Connectors” folder, you have a single file called “GA4PowerBIConnector.mez” which is a MEZ file?
Hello, after I zip up the files and change the .zip folder to a .mez it is no longer a folder, but a file. Is that expected? Once I get this figured out, I should be able to connect. I would appreciate your assistance. Thanks@
Yup – thats right. The file should be a .mez file once you change the extension. This is the format that PBI requires to set up the connector.
Awesome! Thank you. I’m guessing that wasn’t my problem. I’m still having trouble connecting. Is it necessary to add a Scope as stated in Step 3? It was asking how I would use the sensitive scope and required a YouTube video of how I would use the restricted scope. I appreciate your help!
Hi Gavin,
Many thanks for the detailed article.
I have completed all the steps except the file conversion step. I am unable to convert the zip file into .mez file format. This being a naive question, I kindly request you to help me with the details on how to save the zip file into .mez format.
Kindly help me with this regard.
Thanks
Paramesh
Hi Paramesh – great question. You can convert the file in this instance by simply renaming the file to “GA4PowerBIConnector.mez”. Hope that works for you.
Hey there, I love all the points you made on that topic. There is definitely a great deal to know about this subject, and with that said, feel free to visit my blog Webemail24 to learn more about Sports.