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
- 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”]
[/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”]
[/gdlr_code]
desc = true, works for dimensions too.
There are a few order types you can go for depending on your tastes.
ALPHANUMERICCASE_INSENSITIVE_ ALPHANUMERIC NUMERIC (dimension values are converted to numbers before sorting)
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”]
[/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.
EXACTBEGINS_WITH ENDS_WITH CONTAINSFULL_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”]
[/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:
EQUALLESS_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