Viral Video Analyzer: 15,000 cached records and what they teach us
A walkthrough of the nightly job that turns cached data into a Viral video analyzer, complete with regional slices and action items for SEO.
Every night we snapshot video_cache and channels_cache so product, SEO, and partnerships get a self-serve Viral video analyzer. This post shows the queries and charts we share internally.
Because we store region and category with every record, we can publish slices for YouTube trending videos US, YouTube trending videos UK, and YouTube trending videos Japan without reprocessing raw API responses.
Localization teams compare Viral videos in US versus Viral videos in UK and Viral videos in Japan to decide which scripts get translated first. All of it funnels into Regional YouTube analytics decks that guide sales calls.
Methodology
A cron job copies fresh cache rows into a warehouse table with normalized metrics, then Looker dashboards reference it for reporting.
Regional comparisons
Region + category filters expose how certain hooks play differently in finance vs. lifestyle, or how Japan rewards shorter runtimes than the US.
Runtime and hook signals
We keep tracking the 8–12 minute sweet spot, CTRs inside the first 48 hours, and per-channel view velocity so teams can cite hard numbers.
Action items for SEO and sales
Download the CSV, pull thumbnails, highlight copy that worked, and drop it straight into briefs or account plans.
More articles
All postsHow to Find Viral YouTube Videos in 2024: Complete Guide for Content Creators
Follow the actual path a query takes inside ShortsMonkey: normalize, score, hit the database, and trigger refresh jobs so your brief already includes proof.
How our stack behaves like the YouTube analytics tool we needed
Peek into the instrumentation that makes our dashboards double as a YouTube channel monitor and YouTube video tracker.
Use our stack to go from 0 to 10K subscribers in 90 days
The exact cadence we tested: research with cache presets, publish with guardrails, and let monitors dictate the next sprint.