# 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

| Need                              | Preferred Surface                                       | Warehouse Source                           |
| --------------------------------- | ------------------------------------------------------- | ------------------------------------------ |
| Standard event names and payloads | `telemetry-sdk-event-catalog.md`                        | `GameEventTaxonomy_V2_Contract.json`       |
| Raw event investigation           | `@v2/telemetry-data-export-api`                         | `warehouse.v2_telemetry.events`            |
| Reconstructed sessions            | `@v2/telemetry-session-reconstruction`                  | `warehouse.v2_telemetry.player_sessions`   |
| Realtime player counts            | `@v2/realtime-player-count-dashboard`                   | latest aggregate samples                   |
| Funnels                           | `@v2/session-funnel-analysis`                           | session and purchase events                |
| Retention cohorts                 | `@v2/retention-cohort-analysis`                         | session events by cohort day               |
| Engagement metrics                | `@v2/engagement-metrics-dashboard`                      | session length and interval events         |
| Economy analytics                 | `@v2/economy-analytics`                                 | earn, spend, and purchase events           |
| Difficulty analytics              | `@v2/difficulty-analytics`                              | encounter attempts and outcomes            |
| Crash analytics                   | `@v2/crash-analytics-dashboard`                         | sanitized crash aggregates                 |
| Performance analytics             | `@v2/performance-analytics`                             | client performance samples                 |
| A/B experiments                   | `@v2/ab-test-analysis-pipeline`                         | experiment exposure and conversion events  |
| Churn and segments                | `@v2/churn-prediction-model`, `@v2/player-segmentation` | hashed-account behavioral events           |
| Alerting                          | `@v2/telemetry-alerting-rules`                          | aggregate crash/player/matchmaking samples |

## 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

```sql
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

```sql
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

```sql
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

```sql
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

```sql
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

```bash
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
```