The Problem

Sometimes analytics datasets include the raw user-agent string but not a clean device category. That creates a reporting problem if you want to compare mobile and non-mobile journeys.

Full user-agent parsing can be heavy. It may require external libraries, lookup tables or extra processing. For many service analytics use cases, a simpler SQL regex approach is good enough.

The Simple Version

The query below creates a three-way device category directly from the user-agent string.

CASE
  WHEN UAString LIKE '%iPad%' THEN 'Tablet'
  WHEN regexp_like(
    UAString,
    '.*\s[A-Z]{3}-[0-9a-zA-Z]+|Mozilla\/4\.0|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]+.*'
  ) THEN 'Mobile'
  ELSE 'Desktop / Other'
END AS Device_Category

Why This Is Useful

For dashboarding, you often do not need a full browser and device breakdown. You may only need to know whether a user was likely on a mobile device.

• Are mobile users giving lower CSAT scores?
• Are mobile users more likely to hit session-expired pages?
• Are form errors more common on mobile?
• Does a journey perform worse on mobile than desktop?

Breaking Down the Regex

Why Check iPad First?

The tablet condition comes first:

WHEN UAString LIKE '%iPad%' THEN 'Tablet'

The order matters. If tablets are checked after the mobile condition, some tablets may already have been classified as mobile.

Reducing Query Load

Regex can be relatively expensive over very large event tables. A simple optimisation is to filter the dataset first, then classify the user agent.

WHERE tags.path NOT LIKE '%/assets/%'
AND tags.path NOT LIKE '%/images/%'
AND tags.path NOT LIKE '%/fonts/%'
AND detail.status_code = '200'

This removes irrelevant asset, image and font requests before running device classification.

Classify Once, Reuse Later

If the same user-agent strings appear repeatedly, classify the distinct user-agent strings once and join them back to page views.

WITH ua_classification AS (
  SELECT DISTINCT
    UAString,
    CASE
      WHEN UAString LIKE '%iPad%' THEN 'Tablet'
      WHEN regexp_like(UAString, 'Phone|Mobi|SamsungBrowser|Pixel|Redmi|SM-(?:A|G|J|F|S)[0-9a-zA-Z]+') THEN 'Mobile'
      ELSE 'Desktop / Other'
    END AS Device_Category
  FROM source_table
)

SELECT
  p.date,
  p.session_id,
  p.page_path,
  u.Device_Category
FROM page_views p
LEFT JOIN ua_classification u
ON p.UAString = u.UAString

Limitations

This approach is not perfect. It may misclassify unusual Android devices, embedded web views, tablets that do not identify clearly, bots using mobile-like strings, and future devices not included in the regex.

Conclusion

Device categorisation does not always need a full parsing library. For operational analytics, a lightweight regex can provide a useful mobile, tablet and desktop split directly in SQL.