Custom Dimensions and Metrics

-- Map access is bracket syntax. A key that was never sent returns the
-- type's default -- '' for a dimension, 0 for a metric -- so use
-- has() when "absent" and "zero" mean different things to you.
SELECT
    custom_dimensions['warehouse']            AS warehouse,
    custom_dimensions['carrier']              AS carrier,
    count()                                   AS shipments,
    sum(custom_metrics['parcel_weight_kg'])   AS total_kg,
    quantile(0.95)(custom_metrics['pick_duration_ms']) AS pick_p95_ms
FROM website_events
WHERE event_type = 'order_shipped'
  AND event_timestamp >= now() - INTERVAL 30 DAY
  AND has(custom_metrics, 'parcel_weight_kg')
GROUP BY warehouse, carrier
ORDER BY total_kg DESC
LIMIT 50;

-- The materialised keys are ordinary columns, so they index and read
-- like one. Prefer them where they apply.
SELECT search_term, count() AS searches
FROM website_events
WHERE event_type = 'site_search'
  AND event_timestamp >= now() - INTERVAL 7 DAY
  AND search_term != ''
GROUP BY search_term
ORDER BY searches DESC
LIMIT 25;

-- The query behind the discovery catalogue. Run it yourself when you
-- want to audit what your applications are actually emitting, as
-- opposed to what you believe they emit.
SELECT
    key,
    count()                        AS events,
    uniqExact(value)               AS distinct_values,
    min(event_timestamp)           AS first_seen,
    max(event_timestamp)           AS last_seen
FROM website_events
ARRAY JOIN
    mapKeys(custom_dimensions)   AS key,
    mapValues(custom_dimensions) AS value
WHERE event_timestamp >= now() - INTERVAL 14 DAY
GROUP BY key
ORDER BY distinct_values DESC;

-- Read the top of that result as a cardinality report. A key whose
-- distinct_values tracks its event count one-for-one is an
-- identifier that should not have been a dimension.

-- Some producers pack a list into one dimension. The AEM integrator
-- writes user_groups as a JSON array string, because a user is in
-- several groups and one event should not become several rows.
--
--   custom_dimensions['user_groups'] = '["dam-authors","marketing"]'
--
-- The report DSL exposes this as the jsonArray transform. In SQL you
-- expand it yourself:
SELECT
    grp,
    uniqExact(user_id) AS users,
    count()            AS events
FROM website_events
ARRAY JOIN
    JSONExtract(custom_dimensions['user_groups'], 'Array(String)') AS grp
WHERE event_type = 'asset_download'
  AND event_timestamp >= now() - INTERVAL 30 DAY
GROUP BY grp
ORDER BY events DESC;