If you have previously worked with the Google Analytics API and Google Sheets integrations, you may have wondered how you can filter your data on an ad-hoc basis like you do in the interface with this search box:
Or using a more advanced search filter with multiple criteria like this:
Don’t worry - there is a way to do it! We’re going to use RegEx to accomplish this.
Like rainbows and unicorn farts, the Google Analytics add-on for Google Sheets is a truly magical thing. The GA API sheets integration can help automate your reporting, cutting back on hours of tedious data pulling. It’s quick, easy, and leaves you more time to interpret your data and use it to drive real conversations rather than unloading a full-blown data puke on your clients. (Tip: If you haven’t yet worked with the GA addon for Sheets and are a little intimidated -- don’t be!
Check out this post that demystifies the process and you’ll be on your way to using the GA API sheet to simplify your reporting life. Get a handle on the basics first and then come back to this post to really ramp up your GA reporting skills!)
NOTE: Just to be clear, we’re talking about filters applied to data within the UI, not filters applied to views (one of my colleagues discusses filters over here)!
What Are Some Use Cases Where RegEx Filters Can Help?
RegEx in the GA API Google Sheet add-on filter parameter works best in very focused use cases, particularly when you’re taking your data a step further and beyond “simple” aggregate metrics. Whenever you’re trying to pull highly customized data from GA, this is your best friend. It can help you easily obtain the data you need when:
- Doing a deep data dive over a long period of time
- Layering multiple inquiries, for instance: “I want to find organic sessions that landed on my blog on a Tuesday, from users outside the US, on mobile devices.”
You can string multiple requests for data together in the filter parameter by using commas and semicolons to include or exclude certain search parameters
- Semicolons ( ; ) = and
- Commas ( , ) = or
Depending upon what data you’re pulling (metrics, dimensions, and/or a combination of the two), that can define how your filters work. Continuing with the example above, here’s what that Regex filter would look like if we queried the sessions metric:
- ga:medium=~^organic$;ga:dayOfWeekName=~^Tuesday$;ga:country!~^United States$;ga:deviceCategory=~^mobile$
Relax! It’s just RegEx: Using Filters to Customize Data
Relax! It’s super easy! Using functionality in GSheets via the add-on it’s pretty much the same as using it in the GA interface, with a little added syntax. The hardest part (if you could call it that) is making sure you’re using the correct RegEx to filter to the data you need to make sure your data reporting is accurate and streamlined. Always be sure to check the data that’s being returned to make sure it’s what you were intending!
These filters can be based on metrics, dimensions, or a combination of the two.
If you want GA to return a specific subset of data, you'll want to use the add-on’s 'Filters' field (this is the same as using an ad-hoc filter in the UI) or use a segment (this post covers filtering API results, I’m working on another post that covers segments - stay tuned). Segments, while similar to filters, act different for now just know that you can save them and the lowest scope currently available is session.
Once you know what you want to filter on, you’ll use some operators to tell the request how you want to interact with the data.
RegEx Example & Cheat Sheets
So, let's say you were curious about sessions from organic traffic. To get this data, query the report for the metric “ga:sessions” (but you probably already knew that) and use "ga:medium" for the starting point for our filter, because that's the dimension you want to filter for.
Next, I'd recommend using RegEx for the operator "=~" (which means “contains a RegEx match” for the criteria I'm about to pass to you). It’ll look like this:
PS: the “^” and “$” are anchors. The “^” means the criteria must “start with”, and the the “$” means the criteria must “end with”. For more, check out this post on Regex in GA.
Alternatively, you could use exact match "==", which would look like this:
As mentioned before, the trickiest part is are the GA Sheets addon operators for the criteria you want to match. For easy reference, here's a table of the operators and what they mean. It can be kind of annoying to try to find online when you’re looking for it, so feel free to print this out or save it for easy access!
| =~ | contains a match for regex | ga:medium=~organic |
| !~ | doesn't contain a match for regex | ga:medium!~organic |
| You can do everything below using regex above, but for reference.... | ||
| == | exact match | ga:medium==organic |
| != | not an exact match | ga:medium!=organic |
| < | less than | ga:adCost<5 |
| <= | less than or equal to | ga:sessions<=5 |
| > | greater than | ga:pageviews>10 |
| >= | greater than or equal to | ga:bounceRate>=.5 |
| <> | between two values | ga:adCost<>5_10 |
| [] | in list | ga:medium[]organic|ppc|cpc |
| =@ | contains substring | ga:landingPagePath=@about |
| ~@ | doesn't contain substring | ga:landingPagePath~@about |
Using RegEx to Pull Data In Google Sheets
In conclusion, we learned how to use filters, operators and a little bit of RegEx to laser pull data from Google Analytics into Google Sheets. While there are a lot of operators, everything can be accomplished using:
- contains a match for RegEx - “=~”
- doesn’t contain a match for RegEx “!~”
in combination with the AND and OR string operators:
- AND - “;”
- OR - “,”
Give it a try for yourself and see just how easy it is to finely-tune your data. Also, check out our post on using RegEx to track SEO performance within Google Analytics itself.
