Skip to content

Customer Experience

These functions provide with a centralized and standard transformation logic for classifications within the Customer Experience domain.

If an update it required, this logic should be updated in the specific UDF to maintain queries clean and consistent across the sources.

classify_appbot_group (UDF)

Classify a Zendesk ticket by its aggregated tag set into an Appbot reporting group. Returns a STRUCT. See README for usage and details.

classify_appbot_group

Classify a Zendesk ticket by its aggregated tags into an Appbot ticket group.

Returns STRUCT<ticket_group STRING, is_bot BOOL, is_english BOOL>:

  • ticket_group — 'Appbot - English' when 'bot' is present alongside any English-locale tag; 'Appbot - Non-English' when 'bot' is present without any English-locale tag; 'Other' when no 'bot' tag is present.
  • is_bot — TRUE when 'bot' is in the tag set, otherwise FALSE.
  • is_english — TRUE when at least one English-locale tag is in the set, otherwise FALSE.

The authoritative English-locale tag list lives in the UDF body — see udf.sql.

The whole STRUCT is NULL when the input array itself is NULL (input data missing) — distinct from an empty tag set, where is_bot and is_english are FALSE and ticket_group is 'Other'.

Usage

Per-ticket classification — caller aggregates tag rows per ticket_id and dots into the struct:

SELECT
  ticket_id,
  mozfun.customer_experience.classify_appbot_group(ARRAY_AGG(tag)).ticket_group
FROM `moz-fx-data-shared-prod.zendesk_syndicate.ticket_tag`
GROUP BY ticket_id

Note: ARRAY_AGG(tag) may include NULL elements; the UDF treats them as non-matching. A NULL input array (missing data) returns a NULL struct, while an empty array (no tags) returns is_bot = FALSE, is_english = FALSE, ticket_group = 'Other'.

If you want all details and want to avoid invoking the UDF three times per row, alias the struct once in a CTE and dot into it downstream:

WITH classified AS (
  SELECT
    ticket_id,
    mozfun.customer_experience.classify_appbot_group(ARRAY_AGG(tag)) AS appbot
  FROM `moz-fx-data-shared-prod.zendesk_syndicate.ticket_tag`
  GROUP BY ticket_id
)
SELECT
  ticket_id,
  appbot.ticket_group,
  appbot.is_bot,
  appbot.is_english
FROM classified

Parameters

INPUTS

tags ARRAY<STRING>

OUTPUTS

STRUCT<ticket_group STRING, is_bot BOOL, is_english BOOL>

Source | Edit

is_automated (UDF)

Returns TRUE when a tag set contains any of the automation / autosolve / experiment-macro tags maintained by the Zendesk / Customer Experience teams. FALSE when tags exist but none match (including the empty array). NULL when the input array itself is NULL (input data missing). See README for the full tag list and usage.

is_automated

Returns TRUE when the aggregated tag set contains any of the automation / autosolve / experiment-macro tags maintained by the Zendesk / Customer Experience teams.

  • TRUE — at least one automation tag is present.
  • FALSE — tags exist but none match (including the empty array).
  • NULL — the input array itself is NULL (input data missing) — distinct from an empty tag set.

Tag list

The tags captured cover Self-Service Automation (SSA) flows, Appbot autosolve, loginless autosolve, and the SSA experiment macro / star variants. Tickets matching any of these were resolved or rated through automation rather than agent handling.

The authoritative list lives in the UDF body — see udf.sql.

Usage

Per-ticket flag — caller aggregates tag rows per ticket_id:

SELECT
  ticket_id,
  mozfun.customer_experience.is_automated(ARRAY_AGG(tag)) AS is_automated
FROM `moz-fx-data-shared-prod.zendesk_syndicate.ticket_tag`
GROUP BY ticket_id

If a 1/0 column is needed downstream (matching the original automation_class CTE):

SELECT
  ticket_id,
  IF(mozfun.customer_experience.is_automated(ARRAY_AGG(tag)), 1, 0) AS automation_class
FROM `moz-fx-data-shared-prod.zendesk_syndicate.ticket_tag`
GROUP BY ticket_id

Parameters

INPUTS

tags ARRAY<STRING>

OUTPUTS

BOOL

Source | Edit

normalize_product (UDF)

Maps a raw product identifier (GA4 page path, Zendesk custom_product, or Kitsune product slug) to the canonical CX product name. Replaces the static lookup table moz-fx-data-shared-prod.static.cx_product_mappings_v1. Returns 'Other' for unknown inputs (NULL for NULL input) so downstream consumers do not need a COALESCE fallback. See README for the rule list.

normalize_product

Maps a raw product identifier to the canonical CX product name. Replaces the static lookup table moz-fx-data-shared-prod.static.cx_product_mappings_v1.

mozfun.customer_experience.normalize_product(raw STRING, source STRING) -> STRING

source selects the rule set: 'GA4', 'Zendesk', or 'Kitsune'. Unknown source values fall through to 'Other'.

Return values

  • NULL when raw is NULL.
  • A canonical product name (e.g. 'Firefox', 'Mozilla VPN', 'Firefox Android') for recognized inputs.
  • 'Other' for any input that does not match a rule — downstream consumers do not need a COALESCE fallback. Treat 'Other' as the signal to investigate via the drift check (below).

The full canonical-name set is defined by the THEN clauses in udf.sql.

Rules by source

Zendesk and Kitsune

Exact-match only. Each raw slug maps to one canonical product; unknown slugs return 'Other'. New slugs are added by extending the WHEN source = '<…>' AND raw IN (…) arms in udf.sql.

GA4

Two layers, evaluated in order. Ordering is load-bearing — do not reorder without re-reading the test cases at the bottom of udf.sql.

  1. Exact-match path list. Preserves every mapping that existed in the static lookup table, so existing GA4 paths keep their historical bucketing.
  2. Substring fallback. Applied only when no exact-match fires, so newly-coined paths (e.g. /firefox/firefox-enterprise/mobile/) map automatically without a code change.

Substring precedence (top wins):

  1. /contributor/ → 'Other' — bucketed first so e.g. /contributor/thunderbird/ does not leak into Thunderbird.
  2. Deprecated/non-product brand markers (firefox-os, firefox-lite, firefox-reality, firefox-lockwise, firefox-fire-tv, firefox-amazon-devices, firefox-send, firefox-windows-8-touch, firefox-preview, webmaker, pocket, hubs, screenshot-go, open-badges) → 'Other'.
  3. /mozilla-account/ → 'Mozilla Account'.
  4. /mozilla-vpn/, /firefox-private-network/, /firefox-private-network-vpn/ → 'Mozilla VPN'.
  5. /relay/ → 'Firefox Relay'.
  6. /monitor/ → 'Mozilla Monitor'.
  7. /mdn-plus/ → 'Mdn Plus'.
  8. /focus-firefox/, /klar/ → 'Firefox Focus'.
  9. /thunderbird-android/ → 'Thunderbird Android' (more specific, fires before generic Thunderbird).
  10. /thunderbird/ → 'Thunderbird'.
  11. /firefox-enterprise/ → 'Firefox Enterprise'.
  12. /firefox-android-esr/, /mobile/, /ios/ (but not /firefox-ios/) → 'Firefox Android'.
  13. /firefox/ → 'Firefox'.
  14. Catch-all → 'Other'.

The most-specific brand markers always fire before broader ones (e.g. mozilla-vpn before any generic Firefox match) so paths like /firefox/mozilla-vpn/ resolve to 'Mozilla VPN', not 'Firefox'.

Adding a new slug

When the drift check (see below) surfaces a raw value mapping to 'Other':

  1. Decide the canonical product the slug belongs to.
  2. For Zendesk/Kitsune, add the slug to the relevant exact-match arm in udf.sql. For GA4, prefer extending the substring fallback if the slug shares a brand marker with existing paths; otherwise add it to the exact-match list.
  3. Add a test case at the bottom of udf.sql.
  4. If the slug is genuinely a non-product page that should stay 'Other', add it to the corresponding NOT IN exclusion list in the drift check (sql/moz-fx-data-shared-prod/sumo_metrics_derived/ga4_engagement_sessions_daily_v1/checks.sql) so it stops firing.

Drift check

ga4_engagement_sessions_daily_v1/checks.sql runs daily and emits a #warn when any raw custom_product / Kitsune product / GA4 products event_param maps to 'Other' and is not already in the intentional 'Other' exclusion list. New slugs surface as they first appear without needing a history re-scan.

Parameters

INPUTS

raw STRING, source STRING

OUTPUTS

STRING

Source | Edit