Sorting results, metric and dimension filters

HomeGA4Sorting results, metric and dimension filters
Img 20230128 Wa0010 1240x600

Sorting and filtering our queries is super powerful and is often a faster and more efficient alternative to doing it in Power Query after all our data has been returned.

The GA4 API gives us quite a few powerful options to do this, but it will require some work up front from us to get them working. Below are some working examples to get us started.

My starting query for the following will be:

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

Ordering

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 in the 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 dimensions

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

Go ahead and update and run your invoked function to include our new record query in the second to last argument spot in the query:

= #”GET Reports”(#date(2023, 2, 1), #date(2023, 2, 3), “35xxxxx62”, “pageLocation, pageTitle”, “eventCount,eventCountPerUser”, [], [], OrderBy, 20)

That’s cool, lets step it up and do a metric instead. This time lets sort by eventCountPerUser. Note the desc = true; without that it would still sort but in an ascending fashion which probably isn’t what you want.

orderBy metrics

let
    orderBys = [
        desc = true,
        metric = [
            metricName = “eventCountPerUser”
        ]
    ]
in
    orderBys

desc = true, works for dimensions too.

There are a few order types you can go for depending on your tastes.

  • ALPHANUMERIC
  • CASE_INSENSITIVE_ALPHANUMERIC
  • NUMERIC (dimension values are converted to numbers before sorting)

Dimension Filters

The first of those empty records in our query is where we will put out dimension filters.
  • Create a new blank query
  • Name it DimensionFilter or something useful
  • In the ‘Advanced Editor’ paste in the following to create dimensionFilter record
Example dimensions filter record

let
    dimensionFilter = [
        filter = [
            fieldName = “pageTitle”,
            stringFilter = [
                matchType = “CONTAINS”,
                value = “login”,
                caseSensitive = false
            ]
        ]
    ]
in
    dimensionFilter

As with the Order By you can now use that in your query

= #”GET Reports”(#date(2023, 2, 1), #date(2023, 2, 3), “35xxxxx62”, “pageLocation, pageTitle”, “eventCount,eventCountPerUser”, DimensionFilter, [], OrderBy, 20)

There are few ‘matchTypes’ to pick from depending on your desired flavour of query.

  • EXACT
  • BEGINS_WITH
  • ENDS_WITH
  • CONTAINS
  • FULL_REGEXP
  • PARTIAL_REGEXP

Metric Filters

Ok, same as with the above create a new query for metric filters and use that within your GA4 query

Example metric filter record

let
    metricFilter = [
        filter = [
            fieldName = “eventCount”,
            numericFilter = [
                operation = “GREATER_THAN”,
                value = [
                    int64Value = “6000”
                ]
            ]
        ]
    ]
in
    metricFilter

You can now inject that into your query as well:

= #”GET Reports”(#date(2023, 2, 1), #date(2023, 2, 3), “35xxxxx62”, “pageLocation, pageTitle”, “eventCount,eventCountPerUser”, DimensionFilter, MetricFilter, OrderBy, 20)

Again, there are some operations you probably want to be aware of:

  • EQUAL
  • LESS_THAN
  • LESS_THAN_OR_EQUAL
  • GREATER_THAN
  • REATER_THAN_OR_EQUAL

Additionally, there are of couple numeric types that you can mess about with:

  • int64Value
  • doubleValue

That’s the basics!  Should be enough to get you started. Basically you need to pass in records that represent the filters and order bys. You can do some pretty fancy stuff with the filters; its documented in the GA4 API reference if you have the patience to read it. A little tip is that JSON and power query m are basically the other way round when it come to curly and square braces.

Check this to take your filters to the next level using logic conditions

photo credit Graeme Brogan 

Comments ( 8 )

  • Maxim
    Maxim February 4, 2023

    Thank you, Gavin.
    Question: when I upload report to Power BI Service it tells me that this source doesn’t support automatic refreshes – “You can’t schedule refresh for this dataset because the following data sources currently don’t support refresh”.
    Is it possible to update the code somehow to be able to update directly from Power BI Service?

    Reply
    • Gavin Brogan
      Gavin Brogan February 4, 2023

      Yeah, slight limitation at the moment. I’ll look into it

      Reply
  • Rick Waanders
    Rick Waanders February 4, 2023

    Hi,

    I’m running the following query:

    = #”GET Reports”(#date(2023, 1, 1), #date(2023, 12, 31), “********”, “date”, “sessions, totalUsers, eventCountPerUser, eventCount”, [], [], [], null)

    Then I divide the EventCount by the eventCountPerUser, so i get the total views of our website per date.

    However when querying I only retrieve data from the 3th of february till yesterday. Even if I change the date parameters, it won’t show data before the 3th of february. If i look in Google Analytics itself, i do see data from before this date. Any idea on how this is possible?

    Thanks in advance!

    Kind regards,
    Rick

    Reply
    • Gavin Brogan
      Gavin Brogan February 4, 2023

      The connector should pull back everything that’s there. Maybe try changing the second date to today rather than the 31st of Dec.

      Reply
  • Veronika
    Veronika February 4, 2023

    Hi!
    I need to get data about the number of users who have 10 or more sessions. How do I do this? I can’t find a way yet(

    Reply
    • Sam Carter
      Sam Carter February 4, 2023

      Hi Veronika. You can acheive this by creating a “userID” custom dimension (in tag manager create a 1st party cookie variable from cookie name “_ga”) and pass that into the config as a user property. You’ll also need to add it as a custom dimension within the GA4 interface. Once thats all set up you can create a query that has dimensions : user ID, metrics: sessions – then add a filter that sessions > 10. Bit rough but should get you the data that you’re looking for.

      Reply
  • Eric
    Eric February 4, 2023

    Thanks for the great plugin, it’s working great!

    We added custom parameters to GA4 events in Tag Manager, which show up when reporting in Analytics itself – Is there a way to reference and pull in these related event parameters when reporting on event names and counts using the plugin?

    Thanks,
    Eric

    Reply
    • Sam Carter
      Sam Carter February 4, 2023

      Hi Eric, absolutely, You can add custom dimensions using the syntax “customEvent:” or “customUser:” before the customer parameter. This reference guide should help: GA4 Dimensions and Metrics

      Reply

Leave a Comment