Classifying Device Categories from User Agent String in SQL with Regex

So you have a User Agent string field to transform into Device category, but you want to do it using least resource on query time as possible.

The assumption we have made is that for mobile device categories below we are looking for three letter characters and then a hyphen and then a combo of numbers and or letters for the model numbers. These are usually present in the User string for mobile phones. Therefore we have used a regexp_like operator with regex

\s[A-Z]{3}-[0-9a-zA-Z]+

e.g. would match ABC-12GF

The SQL plus the regex we are using for device category is below. So matches for items such as SamsungBrowser and Mobi are naturally put inside the ‘Mobile’ part of the case statement.

CASE
WHEN (regexp_like(UAtext, '.*\s[A-Z]{3}-[0-9a-zA-Z]+|Opera mini|SamsungBrowser|CFNetwork|Nokia|Nexus|Phone|Mobi|CPH1823|IN2023|KB2003|M2007J3SG|M2012K11AG|moto g|P40|Pixel [0-9]{1}|Q10|Redmi|SM-(?:A|G|J|F|S)[0-9a-zA-Z]+|VOG-(?:A|L)[0-9a-zA-Z]+.*') AND (UAtext NOT LIKE '%iPad%')) THEN 'Mobile'
ELSE 'Non-Mobile'
END AS Device_Category

Ipads and Tablets, we are classifying as Non-Mobile, for simplicity in this statement however you could adapt the statement to include tablets if resource in running the query on the database is not so much an issue.

There is another way to look at classification and that is on screen resolutions instead of device categories. So if you have screen resolutions or viewport sizes available or can use code to output these in your data, you could try using these to classify the devices in a SQL case statement as another dimension segment.

We would recommend possibly creating extra dimensions with screen sizes + screen orientation as some devices can share the same screen resolution.

There are lots of limitations to using the User Agent string and the example we provide above is a basic example of a quick dimension segmentation. This article on how Adobe Analytics uses different dimensions for mobile device classification is a good look at how can get even more detailed if you want to start to classify many more dimensions. –>>
https://experienceleague.adobe.com/docs/analytics/components/dimensions/mobile-dimensions.html?lang=en