Using Regex formula for device detection in WooCommerce orders before export

[Anze]

Hello Make Support,

We’re using Make to export WooCommerce orders to Google Sheets. Currently, we include User Agent data in the exported details and use a regex formula in Sheets to extract/determine device type (Desktop, Mobile, Tablet) buyer used. However, when we filter the orders it disrupts this formula’s function.

To solve this, we would like to use regex formula in Make before export so that it will not be necessary to use any code in the Google sheet. Could you recommend a suitable module or a way to achieve this?
Or if you know any better solution to determine what type of device a person uses and export it to Sheets?

Attached is a screenshot of our desired outcome. We’d like to extract OS and device type from the user agent pre-export.

Thank you!

[Princy]

Use “match pattern” module with your regex you are using in sheets. It should work in make also.

If it doesnt work with regex, then you have to use switch function or module with contain function.

Princy Jain
Make consultant,
For consulting support: Follow up Automated

[samliew]

When reaching out for assistance with your regex pattern for a Text Parser module, it would be super helpful if you could share the actual text you’re trying to match. Screenshots of text can be a bit tricky, so if you could copy and paste the text directly here, that would be awesome! It ensures we can run it against test patterns effectively. If there’s any sensitive info, feel free to change it to something fictional yet still valid by keeping the format intact.

Providing clear text examples saves time on both ends and helps us give you the best possible solution. Without proper examples, we might end up playing a guessing game, and nobody wants that as it is a waste of time! You are more likely to get a correct answer faster. So, help us help you by sharing those text snippets. Thanks a bunch!

[Anze]

Thank you both for quick reply. I have some trouble using match pattern, it would be nice to find some example usage.

I will give you more info for my example.

List of a few User Agents:

Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Mobile Safari/537.36
Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/16.5.2 Safari/605.1.15
Mozilla/5.0 (Linux; Android 10; K) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/116.0.0.0 Mobile Safari/537.36
Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36 Edg/120.0.0.0
Mozilla/5.0 (Android 14; Mobile; rv:120.0) Gecko/120.0 Firefox/120.0
Mozilla/5.0 (Android 14; Mobile; rv:120.0) Gecko/120.0 Firefox/120.0
Mozilla/5.0 (Android 14; Mobile; rv:120.0) Gecko/120.0 Firefox/120.0
Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.1 Safari/605.1.15
Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36
Mozilla/5.0 (Linux; Android 13; SAMSUNG SM-F946B) AppleWebKit/537.36 (KHTML, like Gecko) SamsungBrowser/23.0 Chrome/115.0.0.0 Mobile Safari/537.36
Mozilla/5.0 (iPhone; CPU iPhone OS 17_1_2 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.1.2 Mobile/15E148 Safari/604.1
Mozilla/5.0 (iPhone; CPU iPhone OS 17_1_2 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.1.2 Mobile/15E148 Safari/604.1
Mozilla/5.0 (iPhone; CPU iPhone OS 17_1_2 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/17.1.2 Mobile/15E148 Safari/604.1
Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36 AVG/119.0.0.0
Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/119.0.0.0 Safari/537.36 AVG/119.0.0.0
Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/109.0.0.0 Safari/537.36 Edg/109.0.1518.61
Mozilla/5.0 (iPhone; CPU iPhone OS 16_5_1 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Mobile/20F75 Instagram 310.0.4.21.348 (iPhone15,2; iOS 16_5_1; pl_PL; pl; scale=3.00; 1179x2556; 542705233)
Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36

My current regex in Google Sheets for Operating system column:

=ARRAYFORMULA(IF(AI2:AI="", "", 
    IF(REGEXMATCH(LOWER(AI2:AI), "windows"), "Windows PC", 
    IF(REGEXMATCH(LOWER(AI2:AI), "macintosh"), "Apple PC", 
    IF(REGEXMATCH(LOWER(AI2:AI), "linux|x11|cros"), "Linux/ChromeOS PC", 
    IF(REGEXMATCH(LOWER(AI2:AI), "ipad"), "iPad", 
    IF(REGEXMATCH(LOWER(AI2:AI), "android.*(?:mobile|phone)"), "Mobile", 
    IF(REGEXMATCH(LOWER(AI2:AI), "android.*tablet"), "Android Tablet", 
    IF(REGEXMATCH(LOWER(AI2:AI), "iphone"), "iPhone", 
    IF(REGEXMATCH(LOWER(AI2:AI), "macintosh.*iphone"), "iPhone", 
    IF(REGEXMATCH(LOWER(AI2:AI), "ipod"), "iPod", 
    IF(REGEXMATCH(LOWER(AI2:AI), "windows.*phone"), "Windows Phone", 
    IF(REGEXMATCH(LOWER(AI2:AI), "windows.*mobile"), "Windows Phone", 
    IF(REGEXMATCH(LOWER(AI2:AI), "windows nt"), "Windows PC", 
    "Other"))))))))))))))

My current regex in Google sheets for Device column:

=ARRAYFORMULA(IF(AI2:AI="", "", IF(REGEXMATCH(LOWER(AI2:AI), "windows|macintosh|linux|x11|cros"), "Desktop", IF(REGEXMATCH(LOWER(AI2:AI), "ipad|android.+tablet"), "Tablet", "Mobile"))))

I use ARRAYFORMULA in both cases because I need it to also apply to new rows that are automatically added when orders are imported.

And the output in Google Sheets:

Now, I’m trying to achieve this in Make — extracting the device type from the User Agent

[Bjorn.drivn]

Hey @Anze , interesting case and good question!

So first of all I would say there is an easier option to do this: Create a new sheet or new tab and use the IMPORTRANGE() or the QUERY() function. This will import the data and you should be able to filter without messing up the formula.

RegEx in make

However since Make provides you with great power and more extensibility it would also be a good idea to use this. You can use a regex match pattern module to extract all information.

^(?<browser1>.+?) \((?<os>.+?)\)(?: (?<engine>\S+(?: \(.+?\))?)(?: Version\/(?<version>\S+(?: Mobile\/\S+)?))?(?: (?<browser2>\S+))?(?: \S+? (?<browser4>\S+\/\S+$))?)?

You can test and check the groups of this specific pattern in Regex101 for debugging and learning:

regex101: build, test, and debug regex

More information to learn about regex you can find here as well:

How to use Regex in Make?

Last updated: 2024/14/01 In regular cases it’s very usefull to use Regex (or Regular Expressions) if you want to extract or replace data in some text. When there always is a similar pattern in your text data, regex is ideal to use. But how can you use regex? Table of content 1 – What is regex 2 – How to begin with RegEx 3 – Starting development 4 – Basics - Extracting data from text 5 – Basics - Replacing data with the replace() function 6 – Basics - Named capturing groups 7 – Advanced -…

[Anze]

Hi Bjorn. I really like your Google sheets solution, I don’t know how I missed that. Importrange() does a perfect job for what I need.

Also, I’ve managed to make regex work in Make thanks to your example, so now I have two option to use. Thanks!