How We Built a Viewer Engagement Heatmap

We already had view counts and a completion funnel showing how many viewers reached 25%, 50%, 75%, and 100%. That tells you whether people finish your video, but not where they lose interest. A five-minute product walkthrough might have great completion rates while everyone skips the two-minute intro. The engagement heatmap fixes that. It splits the video timeline into 50 segments and shows how many viewers watched each one. Bright segments mean high engagement. Faint segments mean drop-off. The data model Each video gets up to 50 rows in a new segment_engagement table: CREATE TABLE segment_engagement ( video_id UUID NOT NULL REFERENCES videos ( id ), segment_index SMALLINT NOT NULL CHECK ( segment_index >= 0 AND segment_index < 50 ), watch_count INTEGER NOT NULL DEFAULT 0 , PRIMARY KEY ( video_id , segment_index ) ); The composite primary key means no separate ID column and no index to maintain — the primary key is the lookup path. Each segment covers roughly 2% of the video duration.