YouTube Market Intelligence
YouTube Market Intelligence is a production-grade analytics platform for comparing public YouTube creator performance across Indonesian and global channels. It collects channel and video data from 170+ creators across gaming, tech, education, and music, then turns that raw data into dashboard-ready analytics.
The system covers the full data engineering path: yt-dlp extraction, BigQuery loading, dbt analytics engineering, and a Next.js dashboard with interactive views for overview metrics, channel leaderboards, video performance, and niche exploration. It also includes weekly scheduled updates with incremental scraping so the pipeline only processes new videos after the first full ingestion.
KEY CAPABILITIES
- Scrapes 170+ YouTube channels without API keys using yt-dlp
- Incremental ingestion that checks existing BigQuery video IDs and only scrapes new videos
- Prefect orchestration with batch loading, retry handling, and structured logging
- dbt transformation layer with staging, intermediate, and marts models
- Star schema with dim_channels, fct_video_performance, agg_channel_metrics, and agg_niche_overview
- Next.js dashboard with overview, channel leaderboard, video performance, and niche explorer views
- Weekly GitHub Actions workflow for scheduled pipeline refreshes
PROBLEM
- Creator performance analysis is hard to compare manually because channel metrics, video velocity, engagement, niche context, and regional benchmarks are spread across many public pages.
- A simple scrape is not enough for analytics work: the project needed repeatable ingestion, clean transformations, quality tests, and dashboard-friendly tables.
CONSTRAINTS
- The extraction layer had to work without YouTube API keys, handle a large channel list, and avoid reprocessing the same historical videos on every run.
- BigQuery Sandbox constraints made table expiration and refresh strategy part of the design, especially for keeping analytics marts available over time.
MY ROLE
- Owned the end-to-end platform design across scraping, orchestration, warehouse loading, dbt modeling, dashboard APIs, and visualization.
- Designed the star schema, incremental ingestion strategy, dashboard query layer, and weekly automation workflow.
ENGINEERING CALLS
- I used yt-dlp so the pipeline could collect public channel and video metadata without depending on API quotas or keys.
- I split transformations into staging, intermediate, and marts layers so cleansing, metrics, and serving tables each had a clear responsibility.
- I added aggregate marts for channel and niche rollups because the dashboard needed responsive filtering and stable query patterns.
- I built incremental ingestion around existing BigQuery video IDs to reduce weekly runtime from multi-hour full runs to new-video-only processing.
WHY THIS STACK
- Python fit the extraction and loading layer because yt-dlp, pandas, Prefect, and BigQuery clients integrate cleanly in one runtime.
- BigQuery gave the project a serverless warehouse that can handle analytical joins and dashboard queries without database operations overhead.
- dbt was the right layer for reproducible SQL modeling, tests, and a clear transformation contract between raw data and dashboard marts.
- Next.js API routes and Recharts made it practical to serve interactive analytics views directly from BigQuery-backed marts.
Flow from public YouTube creator data to BigQuery, dbt marts, and an interactive Next.js analytics dashboard
EXTRACT -> MODEL -> SERVE
- 1
Extract
yt-dlp collects channel metadata and video statistics from 170+ configured YouTube creators across 4 content niches.
- 2
Model
BigQuery stores raw data while dbt cleans, deduplicates, computes metrics, and publishes star-schema marts.
- 3
Serve
Next.js API routes query dashboard-ready marts for KPI cards, leaderboards, charts, filters, and niche comparisons.
DASHBOARD-READY DATASET
{
"coverage": "170+ channels across 4 niches",
"regions": ["ID", "GLOBAL"],
"warehouse": "BigQuery",
"models": ["dim_channels", "fct_video_performance", "agg_channel_metrics", "agg_niche_overview"],
"dashboard_views": ["overview", "channels", "videos", "niches"],
"scheduled_refresh": "weekly incremental ingestion"
}Representative summary of the platform output, based on the documented project coverage and refresh workflow.
This project proves I can build a full analytics platform, from messy public data extraction through warehouse modeling, quality tests, scheduled refreshes, and dashboard delivery.
End to end
Pipeline scope
Covers extraction, loading, transformation, serving APIs, and visualization in one connected system.
Star schema
Data model
Separates channel dimensions, video facts, and aggregate marts for reliable dashboard usage.
Weekly cron
Automation
GitHub Actions runs incremental updates so the platform can keep data fresh without manual reruns.
BEFORE
Creator comparison required manually checking many channels and videos, with no consistent benchmark for niche, region, or engagement quality.
AFTER
The platform centralizes public YouTube metrics in BigQuery and exposes them through tested models and interactive dashboard views.
IMPACT — It demonstrates practical data engineering across ingestion reliability, dimensional modeling, analytics SQL, automation, and frontend data storytelling.
RESULTS
- Delivered a complete data platform from public YouTube data collection through cloud warehouse, dbt marts, and interactive dashboard views.
- Reduced recurring ingestion work with incremental scraping, moving scheduled updates from full historical collection toward a weekly new-video workflow.
NEXT IMPROVEMENTS
- I would add stronger anomaly monitoring for sudden metric spikes, missing channel responses, and unusually slow scrape batches.
- I would add richer historical snapshots so the dashboard can compare creator growth trends across time, not only current channel and video performance.
HONEST TRADEOFFS
- Used yt-dlp instead of the official YouTube API to avoid API key and quota friction, accepting more responsibility for scrape reliability and error handling.
- Precomputed aggregate marts for dashboard speed, accepting extra dbt model maintenance in exchange for simpler and faster frontend queries.
- Kept the dashboard styling custom rather than adding a UI framework, which reduced frontend dependency overhead but required more deliberate CSS work.