GA4 Connector First Query

If you have been playing along at home, we should have our GA4 data connector installed and ready to go.

Maybe someone configured this for you and has just given you the connector but If you would like to understand the process go do that first; we’ll wait.

Before we begin, you’re going to need your GA4 Property ID for the property that you want query. You can find that under property settings in your GA admin screen.

First Query

Fire up Power BI and go ahead and click ‘Get Data’ and search for our GA4PowerBIConnector. It’s going to want you sign in and authenticate against your google analytics account. Click sign in.

If you did the setup for the connector, do you recognise that permissions screen? It’s the one that you created when setting up the connector before.

• Sign in using your usual Google Analytics credentials
• Select the ‘GET Reports’ function
• Hit the ‘Transform Data’ button

• Start Date – should be self explanatory is the first date you want to include in your data
• End Date – this is the last date you want in your report
• GA4 property id – your GA4 Property ID for the property that you want query. You can find that under property settings in your GA admin screen
• Dimensions – The dimensions you want in your query, you will need at least one. For now we’ll use pageLocation (no spaces, camelCase)
• Metrics – is the metrics, for now we will use eventCount 
• max rows – if you leave this as null or 0 by default it will return all the data it can, I am going to recommend the first time you run a query you probably want to set this to something low like 20  

Once you are happy, go ahead and hit Invoke to run your query. Assuming that you have events data in your GA4 property and that you have the correct property id, you should have just competed your first query.

If you have used the UA API before you might be thinking pageLocation, what the heck? That’s right has a new schema and the dimension and metric names are slightly different. You can sometimes guess what they are and are typically one word, fomatted in camelCase, to work with the GA4 API. The easy way just to look them up on Google’s API Dimensions & Metrics reference page.

Multiple dimensions and metrics can be added, just separate them with a comma. Try these:

pageTitle,pageLocation

eventCount,eventCountPerUser

First Query Done! Let’s improve it.

Quick ‘Order By’ In The Query

The data you get back might not be what you had hoped for. You might want to have sorted pageLocation alphabetically. It’s often quicker to get Google to do some sorting for you rather than returning all your rows and then sorting in Power Query. To do so is pretty easy, the in-depth stuff will have to be a post by itself but this should get you started.

Your query so far should look some like this:

= #”GET Reports”(#date(2023, 2, 1), #date(2023, 2, 3), “678xxxx22”, “pageLocation”, “eventCount”, [], [], [], 20)

• Create a new blank query
• Name it OrderBy or something useful
• In the ‘Advanced Editor’ paste in the following  to create orderBy record

(If you get an ‘Invalid identifier’ error once you’ve copyied in the above, you might need to overwirte the quote marks with a fresh set.)

Cool, go ahead and update and run your invoked function to

= #”GET Reports”(#date(2023, 2, 1), #date(2023, 2, 3), “351866362”, “pageLocation”, “eventCount”, [], [], orderBy, 20)

Hopefully you should see some sorting magic happen.

Filters are done in a similar manor but that will be for next time.

photo credit Graeme Brogan