Product Unboxing Video Library

Objective

Create a system for managing product unboxing videos that showcase packaging, contents, and first impressions. This helps customers understand what they'll receive and builds purchase confidence.

Step 1: Create Unboxing Products Table

Define products with unboxing content.

CREATE TABLE unboxing_products (
    id INTEGER PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100),
    brand VARCHAR(100),
    price_range VARCHAR(50),
    has_unboxing_video BOOLEAN DEFAULT FALSE,
    unboxing_video_count INTEGER DEFAULT 0,
    total_views INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Unboxing Videos Table

Store unboxing video content.

CREATE TABLE unboxing_videos (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    video_file VIDEO(MP4),
    thumbnail IMAGE(JPEG),
    title VARCHAR(255) NOT NULL,
    description TEXT,
    creator_type VARCHAR(50) DEFAULT 'brand',
    creator_name VARCHAR(100),
    duration_seconds INTEGER,
    resolution VARCHAR(20),
    view_count INTEGER DEFAULT 0,
    like_count INTEGER DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    is_published BOOLEAN DEFAULT TRUE,
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES unboxing_products(id)
);

Step 3: Create Video Chapters Table

Define video sections.

CREATE TABLE unboxing_chapters (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    chapter_name VARCHAR(100) NOT NULL,
    start_seconds INTEGER NOT NULL,
    end_seconds INTEGER,
    description TEXT,
    chapter_thumbnail IMAGE(JPEG),
    sort_order INTEGER,
    FOREIGN KEY (video_id) REFERENCES unboxing_videos(id)
);

Step 4: Create Box Contents Table

Document what's in the box.

CREATE TABLE box_contents (
    id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    item_name VARCHAR(255) NOT NULL,
    item_type VARCHAR(50),
    quantity INTEGER DEFAULT 1,
    is_main_product BOOLEAN DEFAULT FALSE,
    description TEXT,
    item_image IMAGE(JPEG),
    sort_order INTEGER,
    FOREIGN KEY (product_id) REFERENCES unboxing_products(id)
);

Step 5: Create Viewer Feedback Table

Track viewer engagement.

CREATE TABLE unboxing_feedback (
    id INTEGER PRIMARY KEY,
    video_id INTEGER NOT NULL,
    session_id VARCHAR(100),
    watch_duration_seconds INTEGER,
    completion_rate DECIMAL(5, 2),
    liked BOOLEAN,
    shared BOOLEAN DEFAULT FALSE,
    led_to_purchase BOOLEAN DEFAULT FALSE,
    feedback_text TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES unboxing_videos(id)
);

Step 6: Insert Sample Products

Add products with unboxing content.

INSERT INTO unboxing_products (id, sku, name, category, brand, price_range, has_unboxing_video, unboxing_video_count, total_views) VALUES
    (1, 'LAPTOP-PRO-15', 'ProBook Pro 15 Laptop', 'Electronics', 'TechBrand', 'premium', TRUE, 3, 125000),
    (2, 'PHONE-MAX', 'SmartPhone Max Pro', 'Electronics', 'MobileMax', 'premium', TRUE, 4, 180000),
    (3, 'HEADPHONES-ELITE', 'Elite Noise-Cancel Headphones', 'Audio', 'AudioPro', 'mid-range', TRUE, 2, 65000),
    (4, 'WATCH-LUXURY', 'Luxury Swiss Chronograph', 'Watches', 'SwissTime', 'luxury', TRUE, 2, 45000),
    (5, 'CAMERA-PRO', 'Professional Camera Kit', 'Photography', 'PhotoMax', 'premium', TRUE, 3, 92000),
    (6, 'GAMING-CONSOLE', 'NextGen Gaming Console', 'Gaming', 'GameTech', 'mid-range', TRUE, 5, 250000),
    (7, 'DRONE-ADV', 'Advanced Photography Drone', 'Electronics', 'AerialTech', 'premium', TRUE, 2, 78000);

Step 7: Insert Unboxing Videos

Add video content.

INSERT INTO unboxing_videos (id, product_id, title, description, creator_type, creator_name, duration_seconds, resolution, view_count, like_count, is_featured, published_at) VALUES
    -- Laptop videos
    (1, 1, 'ProBook Pro 15 Official Unboxing', 'Official unboxing and first look at the new ProBook Pro 15', 'brand', 'TechBrand', 420, '4K', 75000, 4500, TRUE, '2024-01-15 10:00:00'),
    (2, 1, 'ProBook Pro 15 - What is in the Box', 'Complete contents overview and accessory guide', 'brand', 'TechBrand', 180, '1080p', 35000, 1800, FALSE, '2024-01-16 10:00:00'),
    (3, 1, 'ProBook Pro 15 Unboxing Review', 'Independent reviewer unboxing and impressions', 'influencer', 'TechReviewer', 600, '4K', 15000, 1200, FALSE, '2024-01-20 14:00:00'),
    -- Phone videos
    (4, 2, 'SmartPhone Max Pro - Full Unboxing Experience', 'Premium unboxing of the flagship phone', 'brand', 'MobileMax', 360, '4K', 95000, 7200, TRUE, '2024-02-01 09:00:00'),
    (5, 2, 'SmartPhone Max Pro Accessories Unboxing', 'All accessories and add-ons revealed', 'brand', 'MobileMax', 240, '1080p', 42000, 2100, FALSE, '2024-02-02 09:00:00'),
    -- Headphones
    (6, 3, 'Elite Headphones Premium Unboxing', 'Luxurious packaging and first listen', 'brand', 'AudioPro', 300, '4K', 45000, 3800, TRUE, '2024-01-25 12:00:00'),
    -- Watch
    (7, 4, 'Swiss Chronograph Luxury Unboxing', 'Exquisite packaging of a luxury timepiece', 'brand', 'SwissTime', 480, '4K', 28000, 3200, TRUE, '2024-02-10 11:00:00'),
    -- Gaming Console
    (8, 6, 'NextGen Console Day One Unboxing', 'Launch day unboxing and setup', 'brand', 'GameTech', 540, '4K', 150000, 12000, TRUE, '2024-03-01 00:01:00'),
    (9, 6, 'NextGen Console Bundle Unboxing', 'Special edition bundle contents', 'brand', 'GameTech', 420, '4K', 75000, 5500, FALSE, '2024-03-05 10:00:00');

Step 8: Insert Video Chapters

Define chapter markers.

INSERT INTO unboxing_chapters (id, video_id, chapter_name, start_seconds, end_seconds, description, sort_order) VALUES
    -- Laptop official unboxing chapters
    (1, 1, 'Intro', 0, 30, 'Introduction and package overview', 1),
    (2, 1, 'Outer Box', 30, 90, 'Examining the outer packaging', 2),
    (3, 1, 'Opening', 90, 150, 'First look inside the box', 3),
    (4, 1, 'Main Product', 150, 240, 'Revealing the laptop', 4),
    (5, 1, 'Accessories', 240, 330, 'Charger, cables, and extras', 5),
    (6, 1, 'First Power On', 330, 390, 'Booting up for the first time', 6),
    (7, 1, 'Summary', 390, 420, 'Final thoughts and whats included', 7),
    -- Phone unboxing chapters
    (8, 4, 'Package Arrival', 0, 45, 'Delivery and first impressions', 1),
    (9, 4, 'Box Design', 45, 100, 'Premium packaging details', 2),
    (10, 4, 'Phone Reveal', 100, 180, 'Uncovering the device', 3),
    (11, 4, 'In-Box Items', 180, 280, 'All included accessories', 4),
    (12, 4, 'Setup Guide', 280, 360, 'Quick setup walkthrough', 5),
    -- Gaming console chapters
    (13, 8, 'Launch Day Excitement', 0, 60, 'The wait is over', 1),
    (14, 8, 'Premium Packaging', 60, 150, 'Next-gen presentation', 2),
    (15, 8, 'Console Reveal', 150, 270, 'First look at the hardware', 3),
    (16, 8, 'Controller', 270, 360, 'New controller features', 4),
    (17, 8, 'Cables and Accessories', 360, 450, 'Everything in the box', 5),
    (18, 8, 'First Boot', 450, 540, 'Powering on and initial setup', 6);

Step 9: Insert Box Contents

Document package contents.

INSERT INTO box_contents (id, product_id, item_name, item_type, quantity, is_main_product, description, sort_order) VALUES
    -- Laptop contents
    (1, 1, 'ProBook Pro 15 Laptop', 'main_product', 1, TRUE, '15-inch laptop with M-series chip', 1),
    (2, 1, '140W USB-C Power Adapter', 'power', 1, FALSE, 'Fast-charging power adapter', 2),
    (3, 1, 'USB-C to USB-C Cable', 'cable', 1, FALSE, '2-meter charging cable', 3),
    (4, 1, 'Quick Start Guide', 'documentation', 1, FALSE, 'Setup and basics guide', 4),
    (5, 1, 'Warranty Information', 'documentation', 1, FALSE, 'Warranty and support details', 5),
    (6, 1, 'Cleaning Cloth', 'accessory', 1, FALSE, 'Microfiber display cloth', 6),
    -- Phone contents
    (7, 2, 'SmartPhone Max Pro', 'main_product', 1, TRUE, 'Flagship smartphone', 1),
    (8, 2, 'USB-C to USB-C Cable', 'cable', 1, FALSE, 'High-speed data cable', 2),
    (9, 2, 'SIM Ejector Tool', 'tool', 1, FALSE, 'For SIM card installation', 3),
    (10, 2, 'Quick Start Guide', 'documentation', 1, FALSE, 'Setup instructions', 4),
    (11, 2, 'Safety Information', 'documentation', 1, FALSE, 'Safety and regulatory info', 5),
    -- Gaming Console contents
    (12, 6, 'NextGen Gaming Console', 'main_product', 1, TRUE, 'Next-generation gaming console', 1),
    (13, 6, 'Wireless Controller', 'controller', 1, FALSE, 'Next-gen wireless controller', 2),
    (14, 6, 'HDMI 2.1 Cable', 'cable', 1, FALSE, 'Ultra high-speed HDMI cable', 3),
    (15, 6, 'Power Cable', 'power', 1, FALSE, 'AC power cable', 4),
    (16, 6, 'USB-C Cable', 'cable', 1, FALSE, 'Controller charging cable', 5),
    (17, 6, 'Quick Start Guide', 'documentation', 1, FALSE, 'Setup guide', 6),
    (18, 6, 'Console Stand', 'accessory', 1, FALSE, 'Vertical stand for display', 7);

Step 10: Insert Viewer Feedback

Track engagement data.

INSERT INTO unboxing_feedback (id, video_id, session_id, watch_duration_seconds, completion_rate, liked, shared, led_to_purchase, created_at) VALUES
    (1, 1, 'sess_001', 420, 100.00, TRUE, TRUE, TRUE, '2024-01-16 10:30:00'),
    (2, 1, 'sess_002', 280, 66.67, TRUE, FALSE, FALSE, '2024-01-16 14:15:00'),
    (3, 1, 'sess_003', 420, 100.00, TRUE, FALSE, TRUE, '2024-01-17 09:00:00'),
    (4, 4, 'sess_004', 360, 100.00, TRUE, TRUE, TRUE, '2024-02-02 11:00:00'),
    (5, 4, 'sess_005', 180, 50.00, FALSE, FALSE, FALSE, '2024-02-02 15:30:00'),
    (6, 8, 'sess_006', 540, 100.00, TRUE, TRUE, TRUE, '2024-03-01 08:00:00'),
    (7, 8, 'sess_007', 540, 100.00, TRUE, TRUE, FALSE, '2024-03-01 12:00:00'),
    (8, 6, 'sess_008', 300, 100.00, TRUE, FALSE, TRUE, '2024-01-26 16:00:00');

Step 11: Get Product Unboxing Gallery

List unboxing videos for a product.

SELECT
    uv.title,
    uv.creator_type,
    uv.creator_name,
    uv.duration_seconds / 60 as minutes,
    uv.resolution,
    uv.view_count,
    uv.like_count,
    uv.is_featured,
    COUNT(uc.id) as chapters
FROM unboxing_videos uv
LEFT JOIN unboxing_chapters uc ON uv.id = uc.video_id
WHERE uv.product_id = 1
  AND uv.is_published = TRUE
GROUP BY uv.id, uv.title, uv.creator_type, uv.creator_name, uv.duration_seconds, uv.resolution, uv.view_count, uv.like_count, uv.is_featured
ORDER BY uv.is_featured DESC, uv.view_count DESC;

Step 12: Get Video with Chapters

View video and chapter markers.

SELECT
    uv.title,
    uc.chapter_name,
    uc.start_seconds,
    uc.end_seconds,
    uc.description
FROM unboxing_videos uv
INNER JOIN unboxing_chapters uc ON uv.id = uc.video_id
WHERE uv.id = 1
ORDER BY uc.sort_order;

Step 13: What is in the Box Report

Display complete box contents.

SELECT
    up.name as product,
    bc.item_name,
    bc.item_type,
    bc.quantity,
    bc.description,
    CASE WHEN bc.is_main_product THEN 'Main Product' ELSE 'Included' END as status
FROM unboxing_products up
INNER JOIN box_contents bc ON up.id = bc.product_id
WHERE up.id = 1
ORDER BY bc.sort_order;

Step 14: Video Performance Analytics

Analyze unboxing video engagement.

SELECT
    up.name as product,
    uv.title,
    uv.view_count,
    uv.like_count,
    COUNT(uf.id) as feedback_count,
    AVG(uf.completion_rate) as avg_completion,
    CAST(COUNT(CASE WHEN uf.led_to_purchase THEN 1 END) AS DECIMAL) / NULLIF(COUNT(uf.id), 0) * 100 as purchase_influence_rate
FROM unboxing_videos uv
INNER JOIN unboxing_products up ON uv.product_id = up.id
LEFT JOIN unboxing_feedback uf ON uv.id = uf.video_id
WHERE uv.is_published = TRUE
GROUP BY uv.id, up.name, uv.title, uv.view_count, uv.like_count
ORDER BY uv.view_count DESC;

Step 15: Top Performing Unboxing Content

Identify best content by engagement.

SELECT
    up.name as product,
    up.category,
    SUM(uv.view_count) as total_views,
    SUM(uv.like_count) as total_likes,
    CAST(SUM(uv.like_count) AS DECIMAL) / NULLIF(SUM(uv.view_count), 0) * 100 as like_rate,
    AVG(uf.completion_rate) as avg_completion
FROM unboxing_products up
INNER JOIN unboxing_videos uv ON up.id = uv.product_id
LEFT JOIN unboxing_feedback uf ON uv.id = uf.video_id
WHERE uv.is_published = TRUE
GROUP BY up.id, up.name, up.category
ORDER BY total_views DESC
LIMIT 10;

Cleanup (Optional)

DROP TABLE IF EXISTS unboxing_feedback;
DROP TABLE IF EXISTS box_contents;
DROP TABLE IF EXISTS unboxing_chapters;
DROP TABLE IF EXISTS unboxing_videos;
DROP TABLE IF EXISTS unboxing_products;

Expected Outcomes

• Unboxing videos organized
• Chapters defined
• Box contents documented
• Engagement tracked
• Purchase influence measured

Content Types

Key Concepts Learned

• Video content management
• Chapter-based navigation
• Box contents documentation
• Engagement analytics
• Purchase attribution