Media & Entertainment Data Visualisation Automation

Creating an automated YouTube analytics dashboard

12 YouTube channels reported by hand in Excel. Replaced it with a fully automated, interactive dashboard on a unified data model.

channels unified into one data model

100%

of manual Excel reporting eliminated

Switchable

daily, weekly or monthly aggregation

History

built from snapshot-only API data

The client

A digital agency that manages and grows the YouTube presence of a large media and entertainment content portfolio - 12 channels spanning long-form shows, clips, and curated playlists, with a substantial monthly audience.

The problem

Channel and video performance was tracked by hand in Excel. Numbers were copied in manually, the tables looked dated, and there was no stored history beyond what the platform exposed at any given moment. There was no way to look at the same metric daily, weekly, or monthly without rebuilding it. Reporting was slow, repetitive, and easy to get wrong.

Architecture

flowchart LR A["12 YouTube channels
videos, playlists,
demographics"] -->|YouTube Data API + Fivetran| B[("BigQuery
warehouse")] B -->|Unified data model
+ history layer| C["SQL aggregation
daily / weekly / monthly"] C --> D["Looker Studio"] D --> E["Channel & video stats"] D --> F["Playlist stats"] D --> G["Audience & demographics"] style A fill:#f0e8db,stroke:#1a1a2e style B fill:#d8c8ed,stroke:#1a1a2e style C fill:#b6e0c2,stroke:#1a1a2e style D fill:#7551c3,stroke:#1a1a2e,color:#fff style E fill:#f4c8a8,stroke:#1a1a2e style F fill:#f4c8a8,stroke:#1a1a2e style G fill:#f4c8a8,stroke:#1a1a2e

What I did

01.Replaced the manual Excel reporting with an automated pipeline, pulling channel, video, playlist, and demographic data into BigQuery using both the YouTube Data API and Fivetran in parallel.
02.Built a unified data model so all 12 channels share consistent metric definitions and can be reported together or one at a time.
03.Added a history layer that stores point-in-time snapshots over time, so trends are analysable for metrics the API only exposes as a current value.
04.Built SQL aggregation so every metric can be viewed at daily, weekly, or monthly granularity from the same model.
05.Designed an interactive Looker Studio dashboard with three views: periodic channel and video statistics, playlist statistics, and audience demographics, geography, traffic sources, and device split.
06.Added filtering and drill-downs - date range, channel, content type, country - so the team can answer ad-hoc questions without exporting anything.
07.Introduced new ways to present the data - lifetime vs. period growth, search-term analysis, and retention-style breakdowns - that were never part of the original Excel reports.

InteractiveWhat it looks like

The dashboard I built, recreated with sample data

Client figures are confidential - layout and components mirror the live report

YouTube performance - all channels

Jan - Oct

Views

1.24M

▲ 4.1% vs. prev.

Watch time

171K hrs

▲ 2.6% vs. prev.

New subscribers

+1,845

▲ 3.1% vs. prev.

Likes

24.8K

▲ 5.0% vs. prev.

Views trend

Watch time (hrs)

Result

What the automated dashboard changed for the team.

Reporting on autopilot

The team stopped maintaining Excel. The dashboard refreshes itself from the API into BigQuery; no one copies a number by hand any more.

One model, 12 channels

A single data model means every channel uses the same metric definitions, and the whole portfolio can be compared or rolled up without reconciliation.

Trends over time

The history layer turns data the platform only shows as a current value into month-over-month and lifetime trends the team can actually act on.

Self-serve depth

Filters and drill-downs across channels, playlists, audience and geography let stakeholders explore the data themselves instead of requesting a new export.

Tools used

Looker Studio BigQuery YouTube Data API Fivetran SQL Python

"We reached out to Berni for a data visualization solution to give our old statistics tables, still managed in Excel, a fresh, modern look. Berni not only provided a direct API and BigQuery solution to fully automate the process but also introduced new ways to showcase our data that we hadn't considered before. The final dashboard exceeded our expectations, offering a visually engaging and intuitive interface that has significantly improved our efficiency."

Olivér Portik

IT Development

A similar problem in your stack?

Send me the rough shape of it. I'll figure out scope on a 30-min call.

Book a call