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