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
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.
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
- Create a new blank query
- Name it DimensionFilter or something useful
- In the ‘Advanced Editor’ paste in the following to create dimensionFilter record
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
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
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?
Yeah, slight limitation at the moment. I’ll look into it
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
The connector should pull back everything that’s there. Maybe try changing the second date to today rather than the 31st of Dec.
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(
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.
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
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