GA4 Connector First Query

Img 20230128 Wa0016 1240x600

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
All going well this will open Power Query and you will have the UI for the GET Reports function.
  • 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)

Those 3 records that are empty in the query are for ‘dimension filters’, ‘metric filters’ and ‘order bys’, respectively. The third one of those it what we are interested in here.
Creating a record inline is a bit of a pain so we will put it together as a new Query and then use it as a parameter above.
  • Create a new blank query
  • Name it OrderBy or something useful
  • In the ‘Advanced Editor’ paste in the following  to create orderBy record
orderBy

let
    orderBys = [
        dimension = [
            dimensionName = “pageLocation”,
            orderType = “CASE_INSENSITIVE_ALPHANUMERIC”
        ]
    ]
in
    orderBys

(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 

Comments ( 6 )

  • Louise
    Louise

    If you’d make some videos on these instructions that would be so helpful! I’m struggling a bit as a new PBI user. Your connector is amazing by the way! Just having a hard time figuring the reporting out.

    • Gavin Brogan
      Gavin Brogan

      Thanks, I totally agree with the video idea it’s just getting time free to do them. Have you seen the other articles too?

  • Ela Mocanu
    Ela Mocanu

    Hi Gavin,
    Is there a limit in this connector on now many queries we can run, say once a day? I mean in the scenario where I have multiple queries for different dimension / measure combination, given function limitations.
    Thank you!

    • Gavin Brogan
      Gavin Brogan

      Hi, yes there is an hourly limit. It depends on the complexity of the query. I’ve only really run into it as a limitation on Looker where every time you manipulate the viz it can send a new query. You should be ok in power BI but check the token limits page in the Google docs for more info

  • Balazs
    Balazs

    Hey Gavin, just found this fantastic tool. Sadly I have a weird error message when I try to connect to a report with “”GET Reports”: “Expression.Error: Access to the resource is forbidden.”

    I use the exact same query you do above. What can cause this?

    • Sam Carter
      Sam Carter

      Hi Balazs, maybe recheck step 2 of the tutorial where you allow the Api to access your data:
      “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.”

Leave a Comment