Call 0131 370 4737

>

>

Sorting results, metric and dimension filters

Table of Contents

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

[gdlr_code title=”orderBy dimensions” active=”yes”]

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

[/gdlr_code]

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.

[gdlr_code title=”orderBy metrics” active=”yes”]

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

[/gdlr_code]

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

[gdlr_code title=”Example dimensions filter record” active=”yes”]

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

[/gdlr_code]

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

[gdlr_code title=”Example metric filter record” active=”yes”]

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

[/gdlr_code]

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