Video View Tracking

Objective

Implement comprehensive video view tracking with watch time, completion rates, and engagement analytics. This enables understanding viewer behavior and content performance.

Step 1: Create Videos Table

Create a table for trackable videos.

CREATE TABLE tracked_videos (
    id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    video_file VIDEO(MP4),
    duration_seconds INTEGER NOT NULL,
    category VARCHAR(50),
    total_views INTEGER DEFAULT 0,
    unique_viewers INTEGER DEFAULT 0,
    total_watch_time INTEGER DEFAULT 0,
    avg_completion_rate DECIMAL(5, 2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create View Events Table

Track individual view sessions.

CREATE TABLE video_views (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    viewer_id VARCHAR(100),
    session_id VARCHAR(100) NOT NULL,
    watch_start TIMESTAMP NOT NULL,
    watch_end TIMESTAMP,
    watch_duration_seconds INTEGER DEFAULT 0,
    completion_percent DECIMAL(5, 2) DEFAULT 0,
    playback_speed DECIMAL(3, 2) DEFAULT 1.00,
    device_type VARCHAR(50),
    browser VARCHAR(50),
    country VARCHAR(50),
    referrer VARCHAR(255),
    is_autoplay BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES tracked_videos(id)
);

Step 3: Create View Milestones Table

Track completion milestones.

CREATE TABLE view_milestones (
    id INTEGER PRIMARY KEY,
    view_id INTEGER NOT NULL,
    milestone_percent INTEGER NOT NULL,
    reached_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (view_id) REFERENCES video_views(id)
);

Step 4: Insert Sample Videos

Add videos to track.

INSERT INTO tracked_videos (id, title, duration_seconds, category) VALUES
    (1, 'Product Introduction', 300, 'marketing'),
    (2, 'Tutorial: Getting Started', 900, 'education'),
    (3, 'Feature Deep Dive', 1500, 'product'),
    (4, 'Quick Tips', 180, 'tips'),
    (5, 'Webinar Recording', 3600, 'webinar');

Step 5: Insert View Events

Add sample view data.

INSERT INTO video_views (id, video_id, viewer_id, session_id, watch_start, watch_end, watch_duration_seconds, completion_percent, device_type, browser, country, is_autoplay) VALUES
    -- Product Introduction views
    (1, 1, 'user_001', 'sess_001', '2024-01-15 10:00:00', '2024-01-15 10:05:00', 300, 100.00, 'desktop', 'Chrome', 'US', FALSE),
    (2, 1, 'user_002', 'sess_002', '2024-01-15 11:30:00', '2024-01-15 11:33:00', 180, 60.00, 'mobile', 'Safari', 'UK', TRUE),
    (3, 1, 'user_003', 'sess_003', '2024-01-15 14:00:00', '2024-01-15 14:04:30', 270, 90.00, 'tablet', 'Chrome', 'CA', FALSE),
    (4, 1, 'user_001', 'sess_004', '2024-01-16 09:00:00', '2024-01-16 09:05:00', 300, 100.00, 'desktop', 'Chrome', 'US', FALSE),
    -- Tutorial views
    (5, 2, 'user_004', 'sess_005', '2024-01-15 13:00:00', '2024-01-15 13:15:00', 900, 100.00, 'desktop', 'Firefox', 'DE', FALSE),
    (6, 2, 'user_005', 'sess_006', '2024-01-15 15:00:00', '2024-01-15 15:08:00', 480, 53.33, 'mobile', 'Chrome', 'US', FALSE),
    (7, 2, 'user_006', 'sess_007', '2024-01-16 10:00:00', '2024-01-16 10:12:00', 720, 80.00, 'desktop', 'Edge', 'US', FALSE),
    -- Feature Deep Dive views
    (8, 3, 'user_007', 'sess_008', '2024-01-15 16:00:00', '2024-01-15 16:25:00', 1500, 100.00, 'desktop', 'Chrome', 'US', FALSE),
    (9, 3, 'user_008', 'sess_009', '2024-01-16 11:00:00', '2024-01-16 11:10:00', 600, 40.00, 'mobile', 'Safari', 'AU', TRUE),
    -- Quick Tips views
    (10, 4, 'user_009', 'sess_010', '2024-01-15 09:00:00', '2024-01-15 09:03:00', 180, 100.00, 'mobile', 'Chrome', 'US', FALSE),
    (11, 4, 'user_010', 'sess_011', '2024-01-15 12:00:00', '2024-01-15 12:02:30', 150, 83.33, 'desktop', 'Chrome', 'UK', FALSE),
    (12, 4, 'user_011', 'sess_012', '2024-01-16 08:00:00', '2024-01-16 08:03:00', 180, 100.00, 'mobile', 'Safari', 'US', FALSE);

Step 6: Insert Milestones

Track viewing milestones.

INSERT INTO view_milestones (id, view_id, milestone_percent, reached_at) VALUES
    -- Full completion view
    (1, 1, 25, '2024-01-15 10:01:15'),
    (2, 1, 50, '2024-01-15 10:02:30'),
    (3, 1, 75, '2024-01-15 10:03:45'),
    (4, 1, 100, '2024-01-15 10:05:00'),
    -- Partial view
    (5, 2, 25, '2024-01-15 11:31:15'),
    (6, 2, 50, '2024-01-15 11:32:30'),
    -- Tutorial full view
    (7, 5, 25, '2024-01-15 13:03:45'),
    (8, 5, 50, '2024-01-15 13:07:30'),
    (9, 5, 75, '2024-01-15 13:11:15'),
    (10, 5, 100, '2024-01-15 13:15:00');

Step 7: Update Video Statistics

Calculate aggregate statistics.

UPDATE tracked_videos
SET total_views = (
    SELECT COUNT(*) FROM video_views WHERE video_views.video_id = tracked_videos.id
),
unique_viewers = (
    SELECT COUNT(DISTINCT viewer_id) FROM video_views WHERE video_views.video_id = tracked_videos.id
),
total_watch_time = (
    SELECT COALESCE(SUM(watch_duration_seconds), 0) FROM video_views WHERE video_views.video_id = tracked_videos.id
),
avg_completion_rate = (
    SELECT COALESCE(AVG(completion_percent), 0) FROM video_views WHERE video_views.video_id = tracked_videos.id
);

SELECT title, total_views, unique_viewers, avg_completion_rate FROM tracked_videos;

Step 8: Video Performance Overview

Get comprehensive video analytics.

SELECT
    tv.title,
    tv.duration_seconds / 60 as video_minutes,
    tv.total_views,
    tv.unique_viewers,
    tv.avg_completion_rate,
    tv.total_watch_time / 3600 as total_hours_watched
FROM tracked_videos tv
ORDER BY tv.total_views DESC;

Step 9: View Retention Analysis

Analyze where viewers drop off.

SELECT
    tv.title,
    vm.milestone_percent,
    COUNT(*) as reached_count,
    COUNT(*) * 100.0 / tv.total_views as retention_percent
FROM tracked_videos tv
INNER JOIN video_views vv ON tv.id = vv.video_id
LEFT JOIN view_milestones vm ON vv.id = vm.view_id
GROUP BY tv.id, tv.title, tv.total_views, vm.milestone_percent
ORDER BY tv.title, vm.milestone_percent;

Step 10: Device and Browser Analytics

Analyze viewing by device and browser.

SELECT
    device_type,
    browser,
    COUNT(*) as view_count,
    AVG(completion_percent) as avg_completion,
    AVG(watch_duration_seconds) / 60 as avg_watch_minutes
FROM video_views
GROUP BY device_type, browser
ORDER BY view_count DESC;

Step 11: Geographic Distribution

Analyze views by country.

SELECT
    country,
    COUNT(*) as total_views,
    COUNT(DISTINCT viewer_id) as unique_viewers,
    AVG(completion_percent) as avg_completion,
    SUM(watch_duration_seconds) / 3600 as total_hours
FROM video_views
GROUP BY country
ORDER BY total_views DESC;

Step 12: Engagement Quality Score

Calculate engagement quality per video.

SELECT
    tv.title,
    tv.total_views,
    tv.avg_completion_rate,
    tv.total_watch_time / NULLIF(tv.total_views, 0) as avg_watch_seconds,
    CASE
        WHEN tv.avg_completion_rate >= 75 THEN 'Excellent'
        WHEN tv.avg_completion_rate >= 50 THEN 'Good'
        WHEN tv.avg_completion_rate >= 25 THEN 'Fair'
        ELSE 'Needs Improvement'
    END as engagement_quality
FROM tracked_videos tv
ORDER BY tv.avg_completion_rate DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS view_milestones;
DROP TABLE IF EXISTS video_views;
DROP TABLE IF EXISTS tracked_videos;

Expected Outcomes

• View events tracked
• Completion rates calculated
• Retention milestones recorded
• Device analytics available
• Geographic distribution shown

View Metrics

Key Concepts Learned

• View event tracking
• Completion rate calculation
• Retention analysis
• Multi-dimensional analytics
• Engagement scoring