Media Analytics Dashboard

Objective

Create a comprehensive media analytics system that tracks usage patterns, engagement metrics, storage costs, and performance indicators across all media types.

Step 1: Create Media Assets Summary Table

Store aggregated asset data.

CREATE TABLE media_assets_summary (
    id INTEGER PRIMARY KEY,
    asset_id VARCHAR(50) NOT NULL UNIQUE,
    asset_type VARCHAR(50),
    title VARCHAR(300),
    category VARCHAR(100),
    file_size_mb DECIMAL(10, 2),
    duration_seconds INTEGER,
    dimensions VARCHAR(50),
    upload_date DATE,
    owner_id VARCHAR(50),
    department VARCHAR(100),
    status VARCHAR(50) DEFAULT 'active'
);

Step 2: Create Daily Metrics Table

Track daily statistics.

CREATE TABLE daily_media_metrics (
    id INTEGER PRIMARY KEY,
    metric_date DATE NOT NULL,
    asset_type VARCHAR(50),
    category VARCHAR(100),
    total_views INTEGER DEFAULT 0,
    unique_viewers INTEGER DEFAULT 0,
    total_downloads INTEGER DEFAULT 0,
    total_shares INTEGER DEFAULT 0,
    avg_engagement_seconds DECIMAL(10, 2),
    total_storage_mb DECIMAL(15, 2),
    new_assets_count INTEGER DEFAULT 0,
    deleted_assets_count INTEGER DEFAULT 0
);

Step 3: Create Asset Engagement Table

Track individual asset performance.

CREATE TABLE asset_engagement (
    id INTEGER PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    engagement_date DATE,
    views INTEGER DEFAULT 0,
    unique_views INTEGER DEFAULT 0,
    downloads INTEGER DEFAULT 0,
    shares INTEGER DEFAULT 0,
    avg_view_duration_seconds DECIMAL(10, 2),
    completion_rate DECIMAL(5, 2),
    likes INTEGER DEFAULT 0,
    comments INTEGER DEFAULT 0,
    FOREIGN KEY (asset_id) REFERENCES media_assets_summary(id)
);

Step 4: Create User Activity Table

Track user behavior.

CREATE TABLE user_media_activity (
    id INTEGER PRIMARY KEY,
    user_id VARCHAR(50),
    activity_date DATE,
    assets_viewed INTEGER DEFAULT 0,
    total_view_time_seconds INTEGER DEFAULT 0,
    assets_downloaded INTEGER DEFAULT 0,
    assets_uploaded INTEGER DEFAULT 0,
    assets_shared INTEGER DEFAULT 0,
    storage_used_mb DECIMAL(10, 2)
);

Step 5: Create Storage Metrics Table

Track storage usage.

CREATE TABLE storage_metrics (
    id INTEGER PRIMARY KEY,
    metric_date DATE NOT NULL,
    asset_type VARCHAR(50),
    department VARCHAR(100),
    total_files INTEGER,
    total_size_mb DECIMAL(15, 2),
    avg_file_size_mb DECIMAL(10, 2),
    compressed_size_mb DECIMAL(15, 2),
    cost_estimate DECIMAL(10, 2)
);

Step 6: Create Performance Metrics Table

Track system performance.

CREATE TABLE performance_metrics (
    id INTEGER PRIMARY KEY,
    metric_date DATE NOT NULL,
    metric_hour INTEGER,
    avg_load_time_ms INTEGER,
    p95_load_time_ms INTEGER,
    avg_processing_time_ms INTEGER,
    error_count INTEGER DEFAULT 0,
    success_rate DECIMAL(5, 2),
    bandwidth_gb DECIMAL(10, 2),
    concurrent_users INTEGER
);

Step 7: Insert Sample Assets

Add media inventory.

INSERT INTO media_assets_summary (id, asset_id, asset_type, title, category, file_size_mb, duration_seconds, dimensions, upload_date, owner_id, department, status) VALUES
    (1, 'VID-001', 'video', 'Product Launch Keynote', 'corporate', 2850.5, 5400, '3840x2160', '2024-01-01', 'USR-001', 'Marketing', 'active'),
    (2, 'VID-002', 'video', 'Training Module 1', 'training', 580.2, 1800, '1920x1080', '2024-01-05', 'USR-002', 'HR', 'active'),
    (3, 'VID-003', 'video', 'Customer Testimonial', 'marketing', 420.8, 600, '1920x1080', '2024-01-10', 'USR-001', 'Marketing', 'active'),
    (4, 'IMG-001', 'image', 'Product Hero Shot', 'product', 15.2, NULL, '4000x3000', '2024-01-02', 'USR-003', 'Design', 'active'),
    (5, 'IMG-002', 'image', 'Team Photo 2024', 'corporate', 8.5, NULL, '5000x3333', '2024-01-08', 'USR-002', 'HR', 'active'),
    (6, 'DOC-001', 'document', 'Annual Report 2023', 'finance', 12.8, NULL, NULL, '2024-01-15', 'USR-004', 'Finance', 'active'),
    (7, 'AUD-001', 'audio', 'Podcast Episode 45', 'marketing', 52.4, 4200, NULL, '2024-01-12', 'USR-001', 'Marketing', 'active'),
    (8, 'VID-004', 'video', 'Product Demo', 'product', 320.5, 900, '1920x1080', '2024-01-18', 'USR-003', 'Product', 'active'),
    (9, 'IMG-003', 'image', 'Office Building', 'corporate', 6.2, NULL, '4000x2667', '2024-01-20', 'USR-002', 'HR', 'active'),
    (10, 'DOC-002', 'document', 'Employee Handbook', 'hr', 5.5, NULL, NULL, '2024-01-22', 'USR-002', 'HR', 'active');

Step 8: Insert Daily Metrics

Add aggregated metrics.

INSERT INTO daily_media_metrics (id, metric_date, asset_type, category, total_views, unique_viewers, total_downloads, total_shares, avg_engagement_seconds, total_storage_mb, new_assets_count) VALUES
    (1, '2024-01-25', 'video', 'corporate', 1250, 890, 145, 52, 1800, 3851.5, 2),
    (2, '2024-01-25', 'video', 'training', 420, 380, 28, 12, 1200, 580.2, 0),
    (3, '2024-01-25', 'video', 'marketing', 580, 450, 65, 38, 450, 741.3, 1),
    (4, '2024-01-25', 'image', 'product', 2100, 1580, 520, 180, 45, 21.4, 0),
    (5, '2024-01-25', 'image', 'corporate', 890, 620, 125, 42, 30, 14.7, 1),
    (6, '2024-01-25', 'document', 'finance', 320, 280, 185, 25, 180, 12.8, 0),
    (7, '2024-01-25', 'audio', 'marketing', 245, 210, 85, 32, 2100, 52.4, 0),
    (8, '2024-01-24', 'video', 'corporate', 1180, 850, 138, 48, 1750, 3851.5, 1),
    (9, '2024-01-24', 'video', 'training', 395, 360, 25, 10, 1150, 580.2, 0),
    (10, '2024-01-24', 'image', 'product', 1980, 1490, 485, 165, 42, 21.4, 2);

Step 9: Insert Asset Engagement

Add individual asset data.

INSERT INTO asset_engagement (id, asset_id, engagement_date, views, unique_views, downloads, shares, avg_view_duration_seconds, completion_rate, likes, comments) VALUES
    (1, 1, '2024-01-25', 450, 380, 45, 18, 2400, 44.4, 125, 32),
    (2, 1, '2024-01-24', 420, 365, 42, 15, 2350, 43.5, 118, 28),
    (3, 2, '2024-01-25', 185, 165, 12, 5, 1450, 80.5, 45, 8),
    (4, 3, '2024-01-25', 320, 285, 38, 22, 480, 80.0, 85, 15),
    (5, 4, '2024-01-25', 890, 720, 245, 85, 35, NULL, 320, 45),
    (6, 4, '2024-01-24', 845, 695, 228, 78, 32, NULL, 298, 42),
    (7, 6, '2024-01-25', 145, 128, 95, 12, 240, 100.0, 28, 5),
    (8, 7, '2024-01-25', 125, 108, 42, 18, 2800, 66.7, 52, 12),
    (9, 8, '2024-01-25', 285, 248, 32, 14, 720, 80.0, 78, 18);

Step 10: Insert User Activity

Add user behavior data.

INSERT INTO user_media_activity (id, user_id, activity_date, assets_viewed, total_view_time_seconds, assets_downloaded, assets_uploaded, assets_shared, storage_used_mb) VALUES
    (1, 'USR-001', '2024-01-25', 12, 3600, 5, 2, 3, 2865.7),
    (2, 'USR-002', '2024-01-25', 8, 2400, 3, 1, 2, 594.2),
    (3, 'USR-003', '2024-01-25', 15, 4200, 8, 3, 5, 341.9),
    (4, 'USR-004', '2024-01-25', 6, 1800, 4, 0, 1, 12.8),
    (5, 'USR-005', '2024-01-25', 22, 5400, 12, 0, 8, 0),
    (6, 'USR-001', '2024-01-24', 10, 3200, 4, 1, 2, 2865.7),
    (7, 'USR-002', '2024-01-24', 7, 2100, 2, 0, 1, 594.2),
    (8, 'USR-003', '2024-01-24', 14, 3800, 6, 2, 4, 336.5);

Step 11: Insert Storage and Performance

Add infrastructure metrics.

INSERT INTO storage_metrics (id, metric_date, asset_type, department, total_files, total_size_mb, avg_file_size_mb, compressed_size_mb, cost_estimate) VALUES
    (1, '2024-01-25', 'video', 'Marketing', 45, 48520.5, 1078.2, 38816.4, 485.21),
    (2, '2024-01-25', 'video', 'HR', 18, 10850.2, 602.8, 8680.2, 108.50),
    (3, '2024-01-25', 'image', 'Design', 250, 1850.8, 7.4, 1480.6, 18.51),
    (4, '2024-01-25', 'image', 'Marketing', 180, 1250.4, 6.9, 1000.3, 12.50),
    (5, '2024-01-25', 'document', 'Finance', 85, 425.6, 5.0, 340.5, 4.26),
    (6, '2024-01-25', 'audio', 'Marketing', 32, 1680.2, 52.5, 1344.2, 16.80);

INSERT INTO performance_metrics (id, metric_date, metric_hour, avg_load_time_ms, p95_load_time_ms, avg_processing_time_ms, error_count, success_rate, bandwidth_gb, concurrent_users) VALUES
    (1, '2024-01-25', 9, 245, 580, 1250, 2, 99.8, 45.2, 125),
    (2, '2024-01-25', 10, 268, 620, 1380, 3, 99.7, 52.8, 148),
    (3, '2024-01-25', 11, 312, 750, 1520, 5, 99.5, 68.4, 185),
    (4, '2024-01-25', 12, 285, 680, 1420, 2, 99.8, 58.2, 162),
    (5, '2024-01-25', 13, 258, 610, 1320, 1, 99.9, 48.5, 138),
    (6, '2024-01-25', 14, 275, 645, 1380, 2, 99.8, 52.4, 145);

Step 12: Overall Dashboard Summary

View key metrics.

SELECT
    'Total Assets' as metric,
    COUNT(*) as value
FROM media_assets_summary
WHERE status = 'active'
UNION ALL
SELECT
    'Total Storage (GB)',
    ROUND(SUM(file_size_mb) / 1024, 2)
FROM media_assets_summary
UNION ALL
SELECT
    'Today Views',
    SUM(total_views)
FROM daily_media_metrics
WHERE metric_date = CURRENT_DATE
UNION ALL
SELECT
    'Today Downloads',
    SUM(total_downloads)
FROM daily_media_metrics
WHERE metric_date = CURRENT_DATE;

Step 13: Asset Type Distribution

Analyze by media type.

SELECT
    asset_type,
    COUNT(*) as asset_count,
    SUM(file_size_mb) as total_size_mb,
    ROUND(SUM(file_size_mb) / 1024, 2) as total_size_gb,
    AVG(file_size_mb) as avg_size_mb,
    ROUND(SUM(file_size_mb) * 100.0 / (SELECT SUM(file_size_mb) FROM media_assets_summary), 2) as storage_percent
FROM media_assets_summary
WHERE status = 'active'
GROUP BY asset_type
ORDER BY total_size_mb DESC;

Step 14: Top Performing Assets

Identify popular content.

SELECT
    mas.asset_id,
    mas.title,
    mas.asset_type,
    mas.category,
    SUM(ae.views) as total_views,
    SUM(ae.downloads) as total_downloads,
    SUM(ae.shares) as total_shares,
    AVG(ae.avg_view_duration_seconds) as avg_engagement_sec,
    AVG(ae.completion_rate) as avg_completion
FROM media_assets_summary mas
INNER JOIN asset_engagement ae ON mas.id = ae.asset_id
WHERE ae.engagement_date >= CURRENT_DATE - 7
GROUP BY mas.id, mas.asset_id, mas.title, mas.asset_type, mas.category
ORDER BY total_views DESC
LIMIT 10;

Step 15: Department Analytics

View by department.

SELECT
    mas.department,
    COUNT(DISTINCT mas.id) as total_assets,
    SUM(mas.file_size_mb) as storage_mb,
    COALESCE(SUM(ae.views), 0) as total_views,
    COALESCE(SUM(ae.downloads), 0) as total_downloads,
    COALESCE(AVG(ae.completion_rate), 0) as avg_completion
FROM media_assets_summary mas
LEFT JOIN asset_engagement ae ON mas.id = ae.asset_id
    AND ae.engagement_date >= CURRENT_DATE - 30
WHERE mas.status = 'active'
GROUP BY mas.department
ORDER BY total_views DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS performance_metrics;
DROP TABLE IF EXISTS storage_metrics;
DROP TABLE IF EXISTS user_media_activity;
DROP TABLE IF EXISTS asset_engagement;
DROP TABLE IF EXISTS daily_media_metrics;
DROP TABLE IF EXISTS media_assets_summary;

Expected Outcomes

• Assets tracked with metadata
• Daily metrics aggregated
• Engagement measured
• Storage monitored
• Performance tracked

Key Metrics Tracked

Key Concepts Learned

• Metrics aggregation
• Time-series data
• Performance monitoring
• Storage analytics
• User behavior tracking