How long did your viewers wait? Ask your own player data

Ask an AI assistant about your video player's QoE in plain language, straight from your own ClickHouse: questions, answers, MCP setup and the SQL behind each answer.

This is the hands-on companion to How long did your viewers wait? Just ask on Medium, which covers the same idea for product owners.
One question, one answer, from player events in your own database (demo data).

A streaming team's first questions about any week are the same. How long did viewers wait for the video to start? How often did it stall? How many gave up, and how many hit an error? The answers usually sit behind someone else's dashboard, with a history that ends where the retention window ends.

If your player events land in a database you own, you can simply ask: an AI assistant such as Claude Desktop or Cursor reads it over the Model Context Protocol (MCP) and answers in plain language. Below are our questions, the answers and the setup. It uses Open Analytics,
our player analytics service on Open Source Cloud, but works the same on a pipeline you run yourself.

Why the database matters more than the chat window

Several analytics products let you chat with your data. What matters is what the assistant reads.

With Open Analytics, every player event lands as one row in a ClickHouse database that belongs to you, in EPAS, the open Apache-2.0 Eyevinn Player Analytics Specification. So:

• You own the raw events. The assistant reads the same rows you can export or query in Grafana.
• There is no forced retention window. The table has no expiry, so nothing is deleted after a fixed period and comparing with the same week last year is a question, not a feature request. Each pipeline includes 30 GB of storage; more is available on request.
• The price does not grow with your audience. One flat monthly price, with no per-view billing.

The whole pipeline is open source, so you can also run it yourself.

Six questions, in plain language

We typed these questions into Claude Desktop as shown, against demo data: about 150 simulated sessions over one week on web, phones and TV. No real viewers. [...] marks shortened answers.

Before you start: tell the assistant what it is looking at. It can list your tables and columns, but it does not know what a player event means. We first asked three questions without an introduction, and two answers were built on guesses. Startup time ran from the session's first event, not from loading. And it said the failed starts could not be linked to a device or title, because it looked for those fields on the wrong events.

Then we pasted this, once, at the top of the conversation:

My player analytics are in the ClickHouse database default, one row per player event, in the table epas_default.
Columns: event, sessionId, timestamp (DateTime64, UTC, millisecond precision), deviceType, contentId, payload (JSON).
deviceType and contentId are only set on the session's metadata event. Events follow the EPAS specification: loading, loaded, playing, buffering, buffered, paused, seeking, seeked, heartbeat, error, stopped. Startup time is the time from a session's first loading to its first loaded. Use timestamp differences inside one session only, and compute durations with dateDiff.

Use your own table name.

The assistant recalculated startup time unasked and corrected itself: "I was wrong earlier that the failed starts couldn't be linked to devices or content: I had looked for deviceType and contentId on the wrong events."

1. "Which tables do I have, and what period do they cover?"

You have one table: default.epas_default, with 5,098 rows. It covers 2026-10-15 16:28:28 to 2026-10-22 12:56:37 UTC, just under 7 days. There are no other user tables, only ClickHouse's built-in system databases.

Start here every time. If the dates look wrong, every later answer is too.

2. "What was our video startup time over the last seven days? Give me the median and the 95th percentile per day."

Using your definition (a session's first loading to its first loaded), the median startup time over the last seven days (16-22 Oct, UTC days) was 0.9-1.2 s. The 95th percentile was 1.4-2.6 s, except on 17 Oct when it reached 4.3 s.

Day (UTC)	Sessions	Median	95th percentile
Fri 16 Oct	18	1.18 s	2.61 s
Sat 17 Oct	17	1.04 s	4.34 s
Sun 18 Oct	21	0.87 s	1.42 s
Mon 19 Oct	23	1.05 s	1.80 s
Tue 20 Oct	25	0.94 s	1.51 s
Wed 21 Oct	18	1.16 s	2.48 s
Thu 22 Oct	11	0.90 s	2.62 s

Look at the 95th percentile, not the average. An average of about a second can hide one viewer in twenty waiting four.

3. "Which device types have the slowest startup? Show the 95th percentile per device, slowest first."

Smart TVs have the slowest startup, with a 95th percentile of 4.34 s. TV devices in general are slower than phones and web.

Device type	Sessions	95th percentile	Median
smarttv	7	4.34 s	1.84 s
appletv	7	2.48 s	1.52 s
androidtv	3	2.40 s	1.21 s
android	35	2.06 s	1.21 s
ios	28	1.47 s	0.85 s
web	53	1.46 s	0.91 s

It linked this to the previous answer on its own: the slow day was one smart TV start. Many "no device" sessions would mean the player is not reporting metadata: an integration fix, not a quality problem.

Startup time per device type, slowest first (demo data).

4. "How much rebuffering did viewers see this week? I want the share of sessions with at least one stall, stalls per session, and the rebuffering ratio: stall time divided by stall time plus play time, ignoring pauses, seeks and startup."

This week (19-22 Oct UTC), viewers saw little rebuffering in total, but it was common. About one in three sessions stalled at least once, and only 0.19% of watch time was spent rebuffering.

Metric	Value
Sessions that started playing	77
Sessions with at least one stall	28 (36.4%)
Stalls per session (all started sessions)	0.64
Rebuffering ratio	0.19% (121 s stalled vs 64,548 s playing)

5. "How many sessions this week never started playing? Split them into viewers who left before the video started and sessions that failed with an error before the first frame."

This week (19-22 Oct UTC), 5 of 82 sessions never started playing: 3 viewers left before the video started, and 2 sessions failed with an error before the first frame.

Outcome	Sessions	Detail
Left before the video started	3	no error; waited 2.1 s, 4.6 s and 8.0 s before leaving
Failed with an error before the first frame	2	both Android TV, both error 4004 "Manifest request failed"

They look the same in a play count. One viewer gave up waiting; the other hit a problem you can fix.

6. "What are the most common error codes behind those failed starts?"

There is only one error code behind the failed starts: 4004 (NETWORK), "Manifest request failed." [...]

Code	Category	Message	Failed starts this week	All data (15-22 Oct)
4004	NETWORK	Manifest request failed	2	3

Here a conversation beats a fixed report: "those failed starts" builds on the previous answer, and the assistant found the code inside the event payload on its own, then pointed at manifest delivery rather than the player.

The error code behind every failed start, found in the event payload (demo data).

Setup

You need an Open Analytics pipeline (or your own EPAS pipeline), a player using one of the SDKs, and an MCP client.

1. Install uv from docs.astral.sh/uv. It runs the open-source mcp-clickhouse server on your own machine; nothing to deploy.
2. Copy your credentials. In Open Analytics, open SQL Access and pick your pipeline to see its host, username and password.
3. Add the server to your assistant's MCP configuration (Claude Desktop and Cursor use the same format), with your own values in braces:

{
  "mcpServers": {
    "clickhouse": {
      "command": "uv",
      "args": ["run", "--with", "mcp-clickhouse", "--python", "3.10", "mcp-clickhouse"],
      "env": {
        "CLICKHOUSE_HOST":     "{host}",
        "CLICKHOUSE_PORT":     "443",
        "CLICKHOUSE_USER":     "{username}",
        "CLICKHOUSE_PASSWORD": "{password}",
        "CLICKHOUSE_DATABASE": "default",
        "CLICKHOUSE_SECURE":   "true"
      }
    }
  }
}

1. Restart the assistant and ask question 1. If it lists your epas_ tables, you are done.

The SQL Access user can write, but the MCP server runs read-only unless you enable writes (our test insert was refused). Keep it that way, and give other tools their own read-only user. The rows the assistant reads go to its AI provider, so treat it like any tool with access to your data.

What to watch for

• Say which period you mean. "This week" became Monday to Thursday, and "the last seven days" became seven whole UTC days. Our rolling seven-day query gave a higher stall share (41.7% against 36.4%). Neither is wrong, but a weekly report needs one fixed definition.
• The assistant writes SQL, and it can get a definition wrong. Ask it to show the query when a number surprises you, and compare it with the appendix below. Answers can also vary between runs, so for a weekly number, save the query and rerun that in Grafana or as a scheduled report.
• On small samples, the percentile method decides the number. With 17 sessions on one day, the assistant's 95th percentile was the slowest session (4.34 s); our interpolating query gave 2.34 s. Tell it which function to use.
• Not every metric is ready yet. Watch time, seek distance and completion rate need playhead and duration values, which the SDKs do not yet send in the same unit. Until a fix at ingest ships, keep those per platform.

Where to go from here

• Open Analytics, with SQL Access and the assistant setup: analytics.apps.osaas.io
• The event model: EPAS on GitHub
• The MCP server: mcp-clickhouse on GitHub
• Player SDKs: web (npm install @eyevinn/player-analytics-client-sdk-web), Android, iOS and tvOS

Appendix: what the assistant ran

These are the tested definitions behind questions 2 to 5, so you can check an answer or save it as a fixed report.
Replace epas_default with your table name. They were tested on ClickHouse 24.10 with known-answer data and a real
web SDK session.

How the assistant's answers compared with these queries. We ran the queries below on the same demo data the same morning. Where the numbers differ, it is because the assistant chose a different period or a different way to compute a percentile, not because it misread the data:

• Question 1: same table, 5,098 rows, same first and last timestamp.
  • Question 2: most days match to within a few hundredths of a second. On days with few sessions, its 95th percentile is the day's slowest session (4.34 s on 17 Oct), while the query below interpolates between the two slowest (2.34 s).
• Question 3: same order at the top (smart TV, then Apple TV); small device groups move around with the period.
• Question 4: it used Monday to Thursday; the query below uses a rolling seven days, so it reports 41.7% of sessions with a stall, 0.76 stalls per session and a 0.22% ratio.
• Questions 5 and 6: same error code, 4004, and the same two failed starts this week.

Startup time, median and 95th percentile per day (question 2)

SELECT
  day,
  count()                            AS sessions,
  round(quantile(0.5)(startup_ms))   AS p50_ms,
  round(quantile(0.95)(startup_ms))  AS p95_ms
FROM (
  SELECT
    sessionId,
    toDate(minIf(timestamp, event = 'loading')) AS day,
    dateDiff('millisecond',
      minIf(timestamp, event = 'loading'),
      minIf(timestamp, event = 'loaded'))       AS startup_ms
  FROM epas_default
  WHERE timestamp > now() - INTERVAL 7 DAY
  GROUP BY sessionId
  HAVING countIf(event = 'loading') > 0 AND countIf(event = 'loaded') > 0
)
GROUP BY day
ORDER BY day;

Startup time by device type (question 3)

SELECT
  if(device = '', '(no metadata)', device)  AS device,
  count()                                   AS sessions,
  round(quantile(0.5)(startup_ms))          AS p50_ms,
  round(quantile(0.95)(startup_ms))         AS p95_ms
FROM (
  SELECT
    sessionId,
    anyIf(deviceType, event = 'metadata' AND deviceType != '') AS device,
    dateDiff('millisecond',
      minIf(timestamp, event = 'loading'),
      minIf(timestamp, event = 'loaded'))                      AS startup_ms
  FROM epas_default
  WHERE timestamp > now() - INTERVAL 7 DAY
  GROUP BY sessionId
  HAVING countIf(event = 'loading') > 0 AND countIf(event = 'loaded') > 0
)
GROUP BY device
ORDER BY p95_ms DESC;

Rebuffering: share of sessions, stalls per session and ratio (question 4)

WITH ordered AS (
  SELECT
    sessionId, event, timestamp,
    multiIf(event IN ('playing', 'buffered'), 'playing',
            event = 'buffering',              'rebuffering',
            event IN ('init', 'loading', 'loaded', 'paused',
                      'seeking', 'seeked', 'stopped', 'error'), 'other',
            NULL) AS new_state,
    -- order for events that share a millisecond
    indexOf(['init', 'metadata', 'loading', 'loaded', 'seeking', 'seeked',
             'buffering', 'buffered', 'paused', 'playing', 'heartbeat',
             'bitrate_changed', 'warning', 'error', 'stopped'], event) AS seq
  FROM epas_default
  WHERE timestamp > now() - INTERVAL 7 DAY
),
intervals AS (
  SELECT
    sessionId, event,
    anyLast(new_state) OVER w_upto                      AS state,
    dateDiff('millisecond', timestamp,
      leadInFrame(timestamp, 1, timestamp) OVER w_next) AS ms
  FROM ordered
  WINDOW
    w_upto AS (PARTITION BY sessionId ORDER BY timestamp, seq
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    w_next AS (PARTITION BY sessionId ORDER BY timestamp, seq
               ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
),
sessions AS (
  SELECT
    sessionId,
    countIf(event = 'buffering')      AS rebuffers,
    sumIf(ms, state = 'rebuffering')  AS rebuffer_ms,
    sumIf(ms, state = 'playing')      AS playing_ms
  FROM intervals
  GROUP BY sessionId
  HAVING playing_ms > 0
)
SELECT
  count()                                           AS played_sessions,
  round(100 * countIf(rebuffers > 0) / count(), 1)  AS pct_sessions_rebuffered,
  round(sum(rebuffers) / count(), 2)                AS rebuffers_per_session,
  round(sum(rebuffer_ms) / 1000, 1)                 AS rebuffer_seconds,
  round(100 * sum(rebuffer_ms)
        / (sum(rebuffer_ms) + sum(playing_ms)), 2)  AS rebuffer_ratio_pct
FROM sessions;

Exits before start and start failures (question 5)

SELECT
  count()                                          AS sessions,
  countIf(played)                                  AS started,
  countIf(NOT played AND NOT failed)               AS exits_before_start,
  countIf(failed)                                  AS start_failures,
  round(100 * countIf(NOT played AND NOT failed) / count(), 1) AS ebvs_pct,
  round(100 * countIf(failed) / count(), 1)        AS vsf_pct
FROM (
  SELECT
    sessionId,
    countIf(event = 'playing') > 0 AS played,
    countIf(event = 'error') > 0
      AND (countIf(event = 'playing') = 0
           OR minIf(timestamp, event = 'error') < minIf(timestamp, event = 'playing')) AS failed
  FROM epas_default
  WHERE timestamp > now() - INTERVAL 7 DAY
  GROUP BY sessionId
  HAVING countIf(event = 'loading') > 0
)

A start failure is an error event before the first playing. For question 6, read the code from the payload with JSONExtractString(payload, 'code').

---