V2 Analytics Self-Service Query Guide

Phase 72.5.2.15 defines the self-service analytics documentation path for approved analysts, researchers, and operators.

The default path is to use governed analytics surfaces first, then @v2/telemetry-data-export-api for scoped raw-event investigation, and finally @v2/telemetry-bi-export-connectors for Grafana, Tableau, and Looker delivery. Direct warehouse queries are allowed only for principals with an approved purpose and must preserve the privacy guardrails below.

Access Model#

• Roles: researcher, analyst, or admin
• Required approval: a non-empty approved purpose attached to the query, dashboard, notebook, or incident
• Governed API: GET /v2/telemetry/export/events
• BI delivery: Grafana JSON data frames, Tableau Web Data Connector rows, and Looker LookML/rows from @v2/telemetry-bi-export-connectors
• Audit: every export records principal subject, approved purpose, source table, row count, and topic set

Data Map#

Privacy Guardrails#

• Never select, store, or join raw account IDs, email, IP address, platform tokens, device identifiers, or other forbidden payload fields.
• Preserve exposesRawAccountIds: false for every self-service query, dashboard, notebook, and BI connector output.
• Use accountIdHash only when the approved purpose requires account-level grouping.
• Prefer cohort, segment, session, region, platform, game mode, and build aggregates over player-level extracts.
• Keep raw-event exports within the approved topic, account hash, and time scopes enforced by queryTelemetryDataExport.
• Do not bypass @v2/telemetry-privacy-compliance, @v2/telemetry-data-export-api, or retention policy for ad hoc notebooks.
• Redact payloadJson before sharing screenshots or external BI exports unless every field is already approved for that audience.

Query Workflow#

1. Start from telemetry-sdk-event-catalog.md and confirm the topic and payload fields are standard V2 telemetry.
2. Check whether a governed surface already answers the question.
3. If raw rows are required, request an approved purpose and scope topics, account hashes, and time windows before using GET /v2/telemetry/export/events.
4. Use cursor pagination for exports larger than one page.
5. Validate the row count, topics, and source table in the export audit record.
6. Publish durable dashboards through Grafana, Tableau, or Looker connectors rather than unmanaged CSV extracts.
7. Attach the query, approval ticket, and verification notes to the dashboard or incident record.

SQL Templates#

The examples below are ClickHouse-flavored and use only approved V2 warehouse columns. Adapt JSON extraction syntax for BigQuery or Redshift without changing the privacy model.

Daily Active Sessions#

SELECT
  toDate(toDateTime(eventUnixSeconds)) AS event_day,
  uniqExact(sessionId) AS sessions
FROM warehouse.v2_telemetry.events
WHERE topic = 'v2.player.session.started'
  AND eventUnixSeconds >= {from_unix_seconds:UInt32}
  AND eventUnixSeconds < {to_unix_seconds:UInt32}
GROUP BY event_day
ORDER BY event_day;

New Player Funnel#

WITH funnel_events AS (
  SELECT
    sessionId,
    topic,
    min(eventUnixSeconds) AS first_seen
  FROM warehouse.v2_telemetry.events
  WHERE topic IN (
    'v2.player.session.started',
    'v2.tutorial.completed',
    'v2.match.completed',
    'v2.cosmetic.purchased'
  )
    AND eventUnixSeconds >= {from_unix_seconds:UInt32}
    AND eventUnixSeconds < {to_unix_seconds:UInt32}
  GROUP BY sessionId, topic
)
SELECT
  topic,
  uniqExact(sessionId) AS sessions
FROM funnel_events
GROUP BY topic
ORDER BY sessions DESC;

Retention Cohort Sketch#

SELECT
  cohort_day,
  active_day,
  uniqExact(accountIdHash) AS retained_accounts
FROM warehouse.v2_telemetry.player_sessions
WHERE accountIdHash IS NOT NULL
  AND cohort_day >= {cohort_start:Date}
  AND cohort_day <= {cohort_end:Date}
GROUP BY cohort_day, active_day
ORDER BY cohort_day, active_day;

Crash Rate By Version#

SELECT
  JSON_VALUE(payloadJson, '$.build_version') AS build_version,
  countIf(topic = 'v2.player.error.reported') AS crashes,
  uniqExact(sessionId) AS sessions,
  crashes / greatest(sessions, 1) AS crash_rate
FROM warehouse.v2_telemetry.events
WHERE topic IN ('v2.player.error.reported', 'v2.player.session.started')
  AND eventUnixSeconds >= {from_unix_seconds:UInt32}
  AND eventUnixSeconds < {to_unix_seconds:UInt32}
GROUP BY build_version
ORDER BY crash_rate DESC;

Matchmaking Wait P95#

SELECT
  JSON_VALUE(payloadJson, '$.region') AS region,
  JSON_VALUE(payloadJson, '$.game_mode') AS game_mode,
  quantileExact(0.95)(
    toFloat64(JSON_VALUE(payloadJson, '$.wait_seconds'))
  ) AS p95_wait_seconds
FROM warehouse.v2_telemetry.events
WHERE topic = 'v2.matchmaking.wait_sampled'
  AND eventUnixSeconds >= {from_unix_seconds:UInt32}
  AND eventUnixSeconds < {to_unix_seconds:UInt32}
GROUP BY region, game_mode
HAVING p95_wait_seconds >= 180
ORDER BY p95_wait_seconds DESC;

BI Connector Recipes#

Grafana#

Use @v2/telemetry-bi-export-connectors with connector grafana. The output is a grafana-json-dataframe payload with time, topic, event ID, session ID, sequence, critical flag, account hash, and payload JSON fields.

Tableau#

Use connector tableau. The output is a tableau-web-data-connector payload with stable columns, rows, and eventUnixSeconds as the incremental key.

Looker#

Use connector looker. The output includes the oshun_v2_telemetry model name, the v2_telemetry_events explore, and a LookML view with topic, session_id, account_id_hash, and event_count definitions.

Review Checklist#

• The question maps to a documented surface or approved event topics.
• The query includes an approved purpose and bounded time window.
• Topic and account hash scopes are explicit when raw rows are exported.
• Row counts and topic lists match the export audit.
• Results avoid raw identifiers and comply with retention.
• Dashboards include owner, refresh cadence, source table, and incident or research ticket.
• BI connector outputs are regenerated from governed exports, not manually edited extracts.

Verification#

python V2/ue/Tools/check-v2-analytics-self-service-query-guide.py
python -m json.tool V2/ue/Build/Telemetry/v2-analytics-self-service-query-guide.json