SynapCores / Recipe

Media Analytics Dashboard

Build comprehensive analytics for media usage, engagement, and performance

All recipes· advanced-patterns· 15 minutesadvanced

Instance: localhost:8080

Opens your running SynapCores (Media Analytics Dashboard will be staged for a preview — nothing runs until you click Run). No instance yet? Install free in ~30s.

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

Metric Type	Examples
Engagement	Views, downloads, shares
Performance	Load time, processing time
Storage	Size, cost, growth
User Activity	Views, uploads, time spent

Key Concepts Learned

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