Analyze viewer drop-off with ClickHouse

When you build a video product, one question eventually reaches your desk: why are viewers leaving? At first the cause looks like content — maybe the intro runs long, the topic doesn’t land, or the video isn’t relevant. But after a few investigations, you find that viewers often leave for reasons that have nothing to do with content:

• Playback takes too long to start.
• Video quality drops unexpectedly.
• Buffering interrupts the experience.
• Network conditions degrade playback.
• A specific ISP delivers poor performance.

Most analytics dashboards tell you what happened, but not why it happened. This guide closes that gap. You pull playback and QoE metrics from FastPix, load them into ClickHouse alongside your application telemetry, and run custom queries that separate content problems from delivery problems.

NOTE: This guide assumes you already collect playback data through FastPix Video Data and have access to a ClickHouse instance (self-hosted or ClickHouse Cloud). You run all SQL in the ClickHouse SQL console or any compatible client.

What you’ll build

By the end of this guide, you’ll have a repeatable workflow that joins three data sources and answers a single question: are viewers dropping off because of the content or because of the playback experience?

FastPix Video Data

FastPix captures detailed playback and QoE metrics for every view, including watch time, buffering, startup time, bitrate, playback errors, QoE scores, and network provider information. The Video Data APIs return both raw and aggregated viewer analytics.

ClickHouse

You use ClickHouse to store FastPix metrics, combine them with application data, run custom investigations, and surface patterns that pre-built dashboards hide.

Application telemetry

Most applications also record context about each session. Assume yours collects the following:

Joining this telemetry with FastPix metrics is what lets you connect viewer behavior to application performance.

Pull time-series data from FastPix

FastPix provides a dedicated endpoint for retrieving metrics over time. The response includes three fields — intervalTime, metricValue, and numberOfViews so you can track how a playback metric evolves.

Request any of these metrics over your chosen interval:

• Views
• Watch time
• QoE
• Startup time
• Buffer ratio

Use time-series data when you want to identify trends, find peak engagement periods, and spot playback quality changes over time. For request and response details, see the Get timeseries data API reference.

A single interval in the response looks like this:

1
{
2
  "intervalTime": "2026-05-14T12:00:00Z",
3
  "metricValue": 603,
4
  "numberOfViews": 603
5
}

Load the data into ClickHouse

To load the FastPix time-series metrics into ClickHouse, do the following:

1. Create a table to hold the metrics:

1
CREATE TABLE fastpix
2
(
3
    intervalTime DateTime,
4
    metricValue  Float64,
5
    numberOfViews UInt64
6
)
7
ENGINE = MergeTree
8
ORDER BY intervalTime;

2. Import the API response into the table.

Warning When you import the time-series response, make sure the data is in JSONEachRow format, one JSON object per line. ClickHouse expects this format for line-delimited JSON ingestion.

Imported FastPix time-series rows in JSONEachRow format.

After the data loads, a quick aggregation by hour might look like this:

You can immediately spot the peak viewing period at noon, but you still don’t know why viewers might be leaving. The next steps add the context that explains it.

Create sample application data

To represent your application telemetry, create a second table and add sample rows:

1. Create the table:

1
CREATE TABLE Appdata
2
(
3
    created_at String,
4
    api_latency UInt32,
5
    page_load_time UInt32,
6
    user_tier String,
7
    recommendation_source String
8
)
9
ENGINE = MergeTree
10
ORDER BY created_at;

2. Insert a few sample rows:

1
INSERT INTO Appdata VALUES
2
('2026-05-14T07:15:00Z', 120, 900,  'free', 'search'),
3
('2026-05-14T08:20:00Z', 150, 850,  'free', 'recommendation'),
4
('2026-05-14T09:30:00Z', 110, 800,  'paid', 'direct'),
5
('2026-05-14T12:10:00Z', 850, 2400, 'free', 'recommendation');

Notice what happens at noon: API latency jumps and page load time increases. That’s exactly the kind of signal that can explain viewer dissatisfaction during the peak you found after loading the time-series data.

Use FastPix playback data

Your FastPix dataset already contains rich playback metrics. The following columns are the most useful for investigating playback quality directly:

FastPix playback columns queried in the ClickHouse SQL console.

Investigate viewer experience

Start by building an hourly timeline of the core experience metrics. This query groups views by hour and averages watch time, buffering, QoE, and startup time:

1
SELECT
2
    toStartOfHour(
3
        parseDateTimeBestEffort(created_at)
4
    ) AS hour,
5
    count(*) AS views,
6
    round(avg(watch_time), 2) AS avg_watch_time,
7
    round(avg(buffer_ratio), 4) AS avg_buffer_ratio,
8
    round(avg(quality_of_experience_score), 2) AS avg_qoe,
9
    round(avg(video_startup_time), 2) AS avg_startup_time
10
FROM Appdata
11
GROUP BY hour
12
ORDER BY hour;

The result gives you a timeline of views, watch time, buffering, startup performance, and QoE so you can see what viewers actually experienced hour by hour.

Hourly viewer-experience timeline produced by the query above.

Find network providers causing poor experiences

One of the most valuable dimensions in the FastPix dataset is asn_name, the viewer’s network provider. Grouping by ISP reveals whether a specific network is delivering a worse experience.

The HAVING views > 10 clause filters out low-traffic providers so you compare only ISPs with a meaningful sample:

1
SELECT
2
    asn_name,
3
    count(*) AS views,
4
    round(avg(average_bitrate), 2) AS avg_bitrate,
5
    round(avg(avg_request_latency), 2) AS avg_latency,
6
    round(avg(buffer_ratio), 4) AS avg_buffering,
7
    round(avg(quality_of_experience_score), 2) AS avg_qoe
8
FROM Appdata
9
GROUP BY asn_name
10
HAVING views > 10
11
ORDER BY avg_qoe ASC;

Are viewers on a particular ISP having a worse experience? Sorting by avg_qoe ascending puts the worst-performing providers at the top. This breakdown is valuable for video engineers, platform teams, and customer success teams investigating regional or provider-specific complaints.

Quality of Experience compared across network providers (ASNs).

Check whether latency affects QoE

Next, compare request latency and playback quality on the same timeline. If QoE drops when latency rises, infrastructure performance rather than content is likely affecting viewers.

1
SELECT
2
    toStartOfHour(
3
        parseDateTimeBestEffort(created_at)
4
    ) AS hour,
5
    round(avg(avg_request_latency), 2) AS latency,
6
    round(avg(quality_of_experience_score), 2) AS qoe
7
FROM Appdata
8
GROUP BY hour
9
ORDER BY hour;

Plot the two series together. Where the latency line spikes and the QoE line dips at the same hour, infrastructure is the more likely cause of drop-off.

Hourly request latency plotted against QoE score.

Identify videos with potential drop-off problems

Finally, investigate engagement at the individual video level. This query ranks videos by average watch time and surfaces the playback metrics alongside it:

1
SELECT
2
    video_title,
3
    count(*) AS views,
4
    round(avg(watch_time), 2) AS avg_watch_time,
5
    round(avg(view_playing_time), 2) AS avg_play_time,
6
    round(avg(buffer_ratio), 4) AS avg_buffering,
7
    round(avg(avg_request_latency), 2) AS avg_latency,
8
    round(avg(quality_of_experience_score), 2) AS avg_qoe
9
FROM Appdata
10
GROUP BY video_title
11
ORDER BY avg_watch_time ASC;

A result set might look like this:

This is where the investigation gets interesting. For Product Demo A, watch time is low, buffering is high, and QoE is poor. Taken together, those signals suggest the problem isn’t the content, the playback experience is likely driving viewers away.

Per-video performance metrics, sorted by average watch time.

When to use FastPix vs. ClickHouse

A common question is why you wouldn’t just use the FastPix dashboard. The answer is that the two tools answer different questions, and they’re strongest together.

FastPix answers what are viewers experiencing? ClickHouse answers why are they experiencing it? Used together, they give you a far more complete picture than either alone.

Summary

Viewer drop-offs are rarely caused by a single factor. A viewer might leave because the content isn’t engaging, startup times are too high, buffering interrupts playback, network conditions degrade quality, or backend latency slows delivery.

FastPix Video Data APIs provide the playback and QoE signals you need to understand viewer experience. ClickHouse gives you the flexibility to investigate deeper and to join video analytics with your application telemetry. Together they move you beyond simple reporting toward answering the question every video team eventually asks: why did viewers leave?

Next steps

Schedule the FastPix time-series export to refresh your ClickHouse tables on a regular interval, then save these queries as views so any team can re-run the drop-off investigation on demand.