Build the audience retention graph for your own videos

YouTube shows creators exactly where viewers drop off. If you run your own video platform, you probably don't have that graph. The data to build it, though, is already in your player.

This tutorial goes end to end: get view data flowing, export it, and build the graph yourself. The analysis is about 60 lines of ClickHouse SQL and Python, and it runs on your laptop.

Here's what you'll have by the end. It's live: hover any position, click a legend entry to isolate a video. Built from the sample export below.

How the data flows

Each step below is one hop in this pipeline. Already collecting views with FastPix? Skip ahead to Part 2. No traffic yet? Grab the sample dataset and run everything anyway.

Download the sample dataset → fastpix-video-data-sample-export.csv 80 real (anonymized) views from an online course catalog, exported from the FastPix Video Data API. 136 columns per view, including the full playback event stream.

Part 1: First, get data flowing from your player

If you're already a FastPix customer, this data is waiting in your dashboard and you can skip ahead. If not, the setup takes about ten minutes.

Step 1: Add the player (or add monitoring to the one you have)

If you use the FastPix Player, playback analytics are built in. Add the embed and views start flowing to your workspace:

<script src="https://unpkg.com/@fastpix/[email protected]/dist/player.js"></script>

<fastpix-player playback-id="YOUR_PLAYBACK_ID" stream-type="on-demand"></fastpix-player>

Keeping your existing player? Add the monitoring SDK for your stack instead: hls.js, video.js, or dash.js on web, plus native mobile SDKs. Same beacon, same data. The sample dataset in this article was collected exactly this way, from an hls.js player.

import fastpixMetrix from "@fastpix/data-core";

fastpixMetrix.tracker(videoElement, {
  hlsjs: hls,
  Hls: Hls,          // both are required
  data: {
    workspace_id: "YOUR_WORKSPACE_KEY",   // the only mandatory field
    video_title: video.title,
    player_name: "Academy Player",
  },
});

Step 2: Pass who's watching through custom dimensions

One thing worth adding before you move on: identity. Without it, every view in your export is an anonymous session. With it, every row carries your user ID, and retention becomes per-learner, per-cohort, per-account.

fastpixMetrix.tracker(videoElement, {
  hlsjs: hls,
  Hls: Hls,
  data: {
    workspace_id: "YOUR_WORKSPACE_KEY",
    custom_2: `${user.id}::${user.email}`,  // learner identity
    custom_5: lesson.slug,                  // used for deep links in Part 4
    custom_9: course.title,                 // course rollups
  },
});

You get ten free-form slots (custom_1 through custom_10). Part 4 uses two of them.

If custom_2 comes back null in your export, the usual cause is setting metadata after the tracker initialized. Pass it inside the tracker() call, not later.

Step 3: Play something, then export

Play a few videos. Open the Video Data dashboard and each playback session shows up as a row within seconds. Export from Views → Export CSV, or pull the same rows from the export API (see the export docs).

One row = one view. 136 columns. The ones this tutorial uses:

The events column is the one everything else in this article is built from: a JSON array of everything the player did, each event carrying a playhead position (pt, ms into the video) and a wall-clock timestamp (vt):

[
  {"pt": 0,     "e": "viewBegin", "vt": 1776318843241},
  {"pt": 1,     "e": "playing",   "vt": 1776318844833},
  {"pt": 48483, "e": "pause",     "vt": 1776318893415}
]

Part 2: Build the graph

The stack is chdb (embedded ClickHouse, pip install chdb) plus Plotly. Why ClickHouse for a CSV on a laptop? Because the queries you write here run unchanged on clickhouse-local, a self-hosted server, or ClickHouse Cloud. When you move from one CSV to months of streaming views later, the SQL comes with you.

Step 4: Load the export

import chdb

views = chdb.query("""
    SELECT view_id,
           video_title,
           CAST(video_duration AS Int64)              AS duration_ms,
           CAST(view_max_playhead_position AS Int64)  AS max_playhead_ms,
           events
    FROM file('fastpix-video-data-sample-export.csv', CSVWithNames)
    WHERE video_duration > 0
""", "DataFrame")

print(f"{len(views)} views across {views.video_title.nunique()} videos")
# 80 views across 6 videos

If the CAST errors, or your timestamp columns look like 02:37.6, the file has probably been opened and re-saved through Excel, which mangles several columns. Re-download the raw export and query that file directly.

Step 5: Turn each event stream into watched intervals

The question for each view: which stretches of the video did this person actually play? Here's the trick. Between two consecutive events, the viewer was watching if the playhead advanced in lockstep with the wall clock. A seek moves the playhead without wall-clock time passing. A pause moves the clock without the playhead. Real playback moves both together.

import json

def watched_intervals(events_json):
    """[(start_ms, end_ms), ...] of played stretches."""
    evs = [e for e in json.loads(events_json)
           if e.get("pt") is not None and e.get("vt")]
    intervals = []
    for a, b in zip(evs, evs[1:]):
        d_pt = b["pt"] - a["pt"]   # playhead movement (ms)
        d_vt = b["vt"] - a["vt"]   # wall-clock elapsed (ms)
        if d_pt > 0 and abs(d_vt - d_pt) < 0.25 * d_pt + 2000:
            intervals.append((a["pt"], b["pt"]))
    return intervals

views["intervals"] = views["events"].map(watched_intervals)

You could track play and pause events by name instead, but event taxonomies drift across players and SDK versions, and some sessions never emit a playing event at all. Comparing playhead movement to the clock sidesteps all of that.

Step 6: Count who was present in each segment

Split every video into twenty 5% segments. A viewer counts as present in a segment if any of their watched intervals overlaps it. This is the same shape as YouTube's absolute retention graph, and it's allowed to go up mid-video: when viewers skip to a popular moment, that segment's presence rises. You want that signal.

BUCKETS = 20  # 5% segments

def retention_curve(group):
    n = len(group)
    curve = []
    for b in range(1, BUCKETS + 1):
        present = sum(
            any(s < (b / BUCKETS) * d and e > ((b - 1) / BUCKETS) * d
                for (s, e) in iv)
            for iv, d in zip(group["intervals"], group["duration_ms"])
        )
        curve.append(present / n)
    return curve

curves = {title: retention_curve(g)
          for title, g in views.groupby("video_title")
          if len(g) >= 5}   # need enough views for a stable curve

Step 7: Plot it

import plotly.graph_objects as go

PALETTE = ["#2a78d6", "#eb6834", "#1baf7a", "#eda100", "#e87ba4", "#008300"]
order = sorted(curves, key=lambda t: -len(views[views.video_title == t]))

fig = go.Figure()
for i, title in enumerate(order):
    n = len(views[views.video_title == title])
    fig.add_trace(go.Scatter(
        x=[b * 100 / BUCKETS for b in range(1, BUCKETS + 1)],
        y=[v * 100 for v in curves[title]],
        name=f"{title} (n={n})",
        mode="lines",
        line=dict(color=PALETTE[i % len(PALETTE)], width=2),
    ))
fig.update_layout(
    title="Audience retention: % of viewers watching each part of the video",
    xaxis=dict(title="Position in video (%)", ticksuffix="%"),
    yaxis=dict(title="Viewers present", ticksuffix="%", rangemode="tozero"),
    hovermode="x unified",
)
fig.show()

That's the chart at the top of this page. If you've been running along, you now have your own.

Part 3: What the sample data says

Three things stand out in the sample, and each one points at a concrete next step:

The intro cliff. Every long-form video starts at 62 to 83% of viewers present, then drops to 15 to 20% by the 10% mark. On the 55-minute lecture, roughly three minutes of housekeeping cost four out of five viewers. The fix: cut a version that starts at the hook with instant clipping, or add auto-chapters so viewers can jump past the housekeeping themselves.

Short videos hold, long ones bleed. The 4-minute micro-lesson keeps at least half its audience through the end. The 55-minute lectures never see 25% again after the intro. If your catalog is mostly long recordings, the clip API can turn each one into a series of short segments.

A mid-video spike is worth investigating. The workshop recording climbs back to 40% presence around the 40 to 45% mark, which means viewers are skipping to and replaying one specific section. Clip that section, give it its own title, and let it earn its own retention curve.

Ship one change, give it a cohort of views, and rerun the notebook. If the curve moved, keep going.

Part 4: Let the graph trigger things

So far this is a chart you look at. The more useful version is one that acts: an email, a push, an in-app recommendation, a clip request. FastPix supplies the data and the video APIs; the wiring between them is your code, and there isn't much of it.

Here's the simplest loop, end to end: find identified learners who dropped mid-lesson, and nudge them back with a deep link to the second they left. This is why custom_2 (identity) and custom_5 (lesson slug) went into Step 2.

import chdb

dropped = chdb.query("""
    SELECT splitByString('::', custom_2)[2]                AS learner,
           video_title,
           argMax(custom_5, view_start)                    AS lesson_slug,
           max(CAST(view_max_playhead_position AS Int64))  AS deepest_ms,
           any(CAST(video_duration AS Int64))              AS duration_ms
    FROM file('fastpix-video-data-sample-export.csv', CSVWithNames)
    WHERE custom_2 LIKE '%@%'              -- identified learners only
    GROUP BY learner, video_title
    HAVING deepest_ms < 0.9 * duration_ms  -- never finished
       AND deepest_ms > 60000              -- but invested at least a minute
""", "DataFrame")

for _, row in dropped.iterrows():
    resume_s = row.deepest_ms // 1000
    link = f"https://your.app/lesson/{row.lesson_slug}?t={resume_s}"
    # swap print() for your email / push / LMS API call
    print(f"NUDGE {row.learner}: resume \"{row.video_title}\" "
          f"at {resume_s//60}:{resume_s%60:02d} -> {link}")

Run against the sample export, it finds two dropped learners:

NUDGE [email protected]: resume "Lecture 1: Course Foundations" at 12:49
  -> https://your.app/lesson/lecture-1-course-foundations?t=769
NUDGE [email protected]: resume "Lecture 2: Planning & Budgeting…" at 1:18
  -> https://your.app/lesson/lecture-2-planning-budgeting-fundamentals?t=78

Put it on a nightly cron and you have a re-engagement system driven by real watch behavior. The same pattern covers a lot of ground:

Each of these deserves its own writeup; the workflow cookbook is coming as a follow-up.

Running this on your own data

Point Step 4 at your own export and everything runs unchanged. From there, every one of the 136 columns is a GROUP BY away: retention by device, country, startup time, errored vs clean sessions. WHERE device_type = 'Mobile' is a one-line change. And when one CSV isn't enough anymore, the same SQL moves to a real ClickHouse over streaming exports.