Every query in this cookbook answers a question a media, ad tech, brand safety, competitive intel, agency, alt-data, or creator-economy team asks every week but cannot answer with existing tools. The dataset joins signals no other vendor publishes together: VidScore's 20-slug industry classification with per-slug confidence (every tier), IAB Content + Ad Product Taxonomy (Growth+), brand and product mentions with sentiment and prominence (every tier), full sponsorship detail (Growth+), and statement-level intelligence and per-brand industry claims (Scale).
All queries assume Parquet tables loaded into a warehouse (Snowflake, BigQuery, Databricks, Redshift, DuckDB). Growth and Scale customers receive 4 normalized Parquet tables joinable on video_id: videos, people, brands, products. Scale customers receive two more tables, industry_claims and statements, for six in total. JSONL-only customers (Starter) can rewrite the same logic in pandas/polars by flattening the same shape.
Where IAB and statements live. IAB Content Taxonomy 3.1 hierarchy and is_sensitive_content live nested inside the JSONL iab block. The Parquet schemas expose flat columns for the most common joins, but for tier-3 IAB hierarchy queries and statement-level fields you may need to flatten the JSONL once with json_each (DuckDB), LATERAL FLATTEN (Snowflake), or UNNEST (BigQuery) into a side table. Each query below notes which path it uses.
Ad tech / programmatic
1. Contextual inventory by industry, with sponsor density
Who asks this
Trading desk lead at a DSP (Microsoft Invest subscribers looking for post-sunset alternatives, Q1 2026).
What they do today
Filter by IAB tier-1 in a DMP, then cross-reference manually against sponsorship vendors. Never end up with a defensible pre-bid list.
Why this is hard
Grapeshot and Peer39 are page-level with no creator history. DV/IAS pre-bid flags are tier-1 binary. Nobody joins industry classification to per-channel sponsor behavior.
-- Inventory for an automotive advertiser: 'automotive' industry
-- content where the channel runs a low sponsorship rate (cleaner inventory).
-- Uses videos.industries (every tier) and brands.is_sponsor (every tier).
SELECT
v.channel_id,
v.channel_name,
COUNT(DISTINCT v.video_id) AS available_videos,
SUM(v.view_count) AS total_reach,
ROUND(
1.0 * COUNT(DISTINCT b.video_id) FILTER (WHERE b.is_sponsor = TRUE)
/ NULLIF(COUNT(DISTINCT v.video_id), 0),
3
) AS sponsor_rate
FROM videos v
LEFT JOIN brands b ON b.video_id = v.video_id
WHERE v.industries LIKE '%automotive%' -- Parquet stores comma-joined
AND v.published_at >= current_date - INTERVAL '30 days'
GROUP BY v.channel_id, v.channel_name
HAVING COUNT(DISTINCT v.video_id) >= 3
AND COALESCE(
1.0 * COUNT(DISTINCT b.video_id) FILTER (WHERE b.is_sponsor = TRUE)
/ NULLIF(COUNT(DISTINCT v.video_id), 0),
0
) <= 0.20
ORDER BY total_reach DESC
LIMIT 50;Read the result. Each row is a channel where the industry tag matches your campaign and <= 20% of their last-30-day output is sponsored. Feed channel_id into your DSP allowlist.
For tier-3 IAB precision (Growth+). Flatten the JSONL iab.content array first:
-- Snowflake example. Replace your_jsonl_table with whatever you ingested into.
SELECT v.video_id, c.value:tier3::STRING AS iab_tier3
FROM your_jsonl_table v,
LATERAL FLATTEN(input => v.iab:content) c
WHERE c.value:tier3::STRING = 'Basketball'
AND v.published_at::DATE >= current_date - INTERVAL '30 days';2. Sponsorship density by industry
Who asks this
Programmatic analyst building a pre-bid safety floor.
What they do today
Nothing systematic. GARM rate is a single number per vendor.
Why this is hard
No vendor publishes per-category sponsorship rates that you can refresh weekly.
-- Industries where 30-day sponsorship rate is highest. Each row is a
-- VidScore industry slug; high rates mean the inventory is mostly
-- paid placements (less attractive for unbiased contextual buys).
WITH sponsor_videos AS (
SELECT DISTINCT video_id FROM brands WHERE is_sponsor = TRUE
)
SELECT
TRIM(industry_slug.value) AS industry,
COUNT(DISTINCT v.video_id) AS videos,
COUNT(DISTINCT v.video_id) FILTER (WHERE sv.video_id IS NOT NULL)
AS sponsored_videos,
ROUND(
100.0 * COUNT(DISTINCT v.video_id) FILTER (WHERE sv.video_id IS NOT NULL)
/ NULLIF(COUNT(DISTINCT v.video_id), 0),
1
) AS sponsor_pct
FROM videos v
CROSS JOIN UNNEST(SPLIT(v.industries, ', ')) AS industry_slug
LEFT JOIN sponsor_videos sv ON sv.video_id = v.video_id
WHERE v.published_at >= current_date - INTERVAL '30 days'
AND v.industries IS NOT NULL
GROUP BY industry_slug.value
HAVING COUNT(DISTINCT v.video_id) >= 50
ORDER BY sponsor_pct DESC;Read the result. Highest rows have heavy paid-placement saturation; lowest rows are clean organic inventory.
Brand safety
3. Independent second-opinion via VidScore signals
Who asks this
Brand safety director at a Fortune 500 after the February 2025 Adalytics report / Blackburn-Blumenthal letters to DV and IAS.
What they do today
Trust a vendor score. Get burned when Adalytics shows ads ran next to risky content.
Why this is hard
Vendor scores are black boxes. Buyers can't audit the decision.
-- Videos on your bought channels where VidScore detected sponsored
-- mentions but no FTC sponsorship_type was identified — review for
-- disclosure adequacy. Uses brands Parquet (every tier) plus videos.
-- Note: the JSONL also exposes iab.is_sensitive_content for tier-1
-- safety flags (Growth+); see the LATERAL FLATTEN snippet at end of
-- query 1 for that pattern.
SELECT
v.video_id,
v.channel_name,
v.title,
v.title_translated,
v.language,
v.view_count,
v.published_at,
COUNT(*) FILTER (WHERE b.is_sponsor = TRUE) AS sponsor_mentions,
COUNT(*) FILTER (WHERE b.is_sponsor = TRUE AND b.sponsorship_type IS NULL)
AS sponsorship_type_unidentified
FROM videos v
LEFT JOIN brands b ON b.video_id = v.video_id
WHERE v.channel_id IN ('UCxxx', 'UCyyy') -- paste your bought channels
AND v.published_at >= current_date - INTERVAL '90 days'
GROUP BY v.video_id, v.channel_name, v.title, v.title_translated,
v.language, v.view_count, v.published_at
HAVING COUNT(*) FILTER (WHERE b.is_sponsor = TRUE
AND b.sponsorship_type IS NULL) > 0
ORDER BY v.view_count DESC;Read the result. Each row is a video on your roster with at least one sponsored brand mention where VidScore couldn't extract a clear sponsorship type — these warrant a manual disclosure-quality review.
4. FTC disclosure audit: 90-day exposure report
Who asks this
CMO whose brand was named in a Fashion Nova-style FTC enforcement.
What they do today
Pray. Or pay an agency $25K for a manual audit.
Why this is hard
FTC fines hit $53,088 per violation in 2025. Brands are liable for undisclosed creator posts. No tool audits YouTube disclosure at video level.
-- Every video in the last 90 days where your brand appears as a paid
-- sponsor. Cross-reference `sponsorship_type` against your contracts;
-- nulls or 'product_placement' on what should be a labeled integration
-- are red flags. The disclosure_present boolean is nested inside the
-- JSONL sponsorship block on Growth+ — use LATERAL FLATTEN if you need it.
SELECT
b.video_id,
v.channel_name,
v.title,
v.published_at,
v.view_count,
b.sponsorship_type,
b.prominence_level
FROM brands b
JOIN videos v ON v.video_id = b.video_id
WHERE b.brand_name = 'YourBrand'
AND b.is_sponsor = TRUE
AND v.published_at >= current_date - INTERVAL '90 days'
ORDER BY v.view_count DESC;Read the result. Match each row against your influencer contracts. Anything not in your roster but tagged as your sponsor is a creator running paid content without a contract — investigate before the FTC does.
Competitive intel / brand
5. Dark share-of-voice recovery (on-camera mentions without text tags)
Who asks this
Brand manager at a CPG using Brandwatch or Sprinklr.
What they do today
Social listening tools read text. They miss what happens on camera in long-form video.
Why this is hard
"50,000 creators can drink your beverage on camera without tagging the brand or using a hashtag." No text-based tool catches this.
-- Find videos where your brand was mentioned on camera as a primary
-- or secondary subject (not a passing reference) without being a paid
-- sponsorship. These are missed by text-based listening tools.
SELECT
v.video_id,
v.channel_name,
v.title,
v.title_translated,
v.language,
b.sentiment,
b.sentiment_label,
b.prominence_level,
b.context_role,
v.view_count,
v.published_at
FROM brands b
JOIN videos v ON v.video_id = b.video_id
WHERE b.brand_name = 'YourBrand'
AND b.is_sponsor = FALSE
AND b.prominence_level IN ('primary_subject', 'secondary_subject')
AND v.published_at >= current_date - INTERVAL '30 days'
ORDER BY b.sentiment DESC, v.view_count DESC
LIMIT 100;Read the result. Dark share of voice you were not counting. Positive rows are earned-media opportunities; negative rows are crisis-management leads.
6. Head-to-head: organic vs sponsored mention ratio for three brands
Who asks this
Competitive intel lead at Nike tracking Adidas and On Running.
What they do today
Two Brandwatch dashboards and a spreadsheet.
Why this is hard
Social listening can't cleanly distinguish organic praise from paid placements on video.
-- 90-day share-of-voice comparison across three brands, split by
-- organic vs sponsored and weighted by creator reach.
SELECT
b.brand_name,
SUM(CASE WHEN b.is_sponsor = FALSE THEN 1 ELSE 0 END) AS organic_mentions,
SUM(CASE WHEN b.is_sponsor = TRUE THEN 1 ELSE 0 END) AS sponsored_mentions,
ROUND(AVG(b.sentiment) FILTER (WHERE b.is_sponsor = FALSE), 3) AS avg_organic_sentiment,
ROUND(AVG(b.sentiment) FILTER (WHERE b.is_sponsor = TRUE), 3) AS avg_sponsored_sentiment,
SUM(v.view_count) AS reach_weighted_impressions
FROM brands b
JOIN videos v ON v.video_id = b.video_id
WHERE b.brand_name IN ('Nike', 'Adidas', 'On Running')
AND v.published_at >= current_date - INTERVAL '90 days'
GROUP BY b.brand_name
ORDER BY reach_weighted_impressions DESC;Read the result. Organic-to-sponsored ratio is the earned-media health metric. Brands with high organic sentiment are winning culture; brands relying on sponsorships are buying presence.
Media planners / agencies
7. RFP-ready creator landscape for an industry vertical
Who asks this
Strategist at WPP with 48 hours to build an automotive EV pitch.
What they do today
Two weeks of manual Tubular + YouTube search + spreadsheet. Viral Nation says a 30-min vetting session covers 0.01% of a creator's history.
Why this is hard
Requires joining reach, vertical, sponsor ratio, and content density. No tool does all four.
-- Top 50 creators in EV-focused automotive content, past 90 days,
-- with sponsor density. Uses videos Parquet (industries is comma-joined),
-- brands Parquet for sponsor counts, and videos.summary text for EV match.
SELECT
v.channel_id,
v.channel_name,
v.channel_handle,
MAX(v.channel_subscribers) AS subscribers,
COUNT(DISTINCT v.video_id) AS videos_published,
SUM(v.view_count) AS total_views,
ROUND(AVG(COALESCE(sponsor_count.n, 0)), 2) AS avg_sponsors_per_video
FROM videos v
LEFT JOIN (
SELECT video_id, COUNT(*) AS n FROM brands WHERE is_sponsor = TRUE GROUP BY video_id
) sponsor_count ON sponsor_count.video_id = v.video_id
WHERE v.industries LIKE '%automotive%'
AND v.content_type = 'review'
AND LOWER(v.title || ' ' || COALESCE(v.summary, '')) LIKE '%ev%'
AND v.published_at >= current_date - INTERVAL '90 days'
GROUP BY v.channel_id, v.channel_name, v.channel_handle
HAVING COUNT(DISTINCT v.video_id) >= 3
ORDER BY total_views DESC
LIMIT 50;Read the result. Drop this result straight into an RFP appendix. Subscribers, views, and sponsor density per creator — the three things every agency deck needs.
Brand intelligence / BI
8. Industry claim trajectory: how a brand is perceived on a dimension
Who asks this
Product marketing at Apple tracking build-quality sentiment vs Samsung.
What they do today
Read Reddit. Manually watch reviews.
Why this is hard
No tool structures claims by dimension.
-- Scale tier (industry_claims is the 5th Parquet table, Scale-only).
-- Rolling sentiment on build_quality for two brands, last 90 days,
-- showing direction of travel.
SELECT
ic.brand_name,
DATE_TRUNC('week', v.published_at) AS week,
COUNT(*) AS claim_count,
ROUND(AVG(ic.sentiment_score), 3) AS avg_sentiment,
COUNT(*) FILTER (WHERE ic.sentiment = 'positive') AS positive,
COUNT(*) FILTER (WHERE ic.sentiment = 'negative') AS negative
FROM industry_claims ic
JOIN videos v ON v.video_id = ic.video_id
WHERE ic.brand_name IN ('Apple', 'Samsung')
AND ic.industry = 'consumer_electronics'
AND ic.dimension = 'build_quality'
AND v.published_at >= current_date - INTERVAL '90 days'
GROUP BY ic.brand_name, DATE_TRUNC('week', v.published_at)
ORDER BY week DESC, ic.brand_name;Read the result. Week-over-week sentiment delta on a specific perceptual dimension. This is what a Nielsen brand tracker costs $50K/year for.
9. Statement-level intelligence on a product
Who asks this
Product marketing tracking how a specific product is being framed by creators.
What they do today
Manually watch reviews.
Why this is hard
Statement-level extraction across thousands of videos is impossible by hand.
Important. video_statements ships in the JSONL feed only — there is no video_statements Parquet table. The JSONL fields per statement are text, kind, subject (object with type and name), object (same shape), stance, sentiment, strength, topics, risk_flags. Speaker attribution is intentionally not present — YouTube auto-captions do not support diarization. Use these for narrative tracking, not for attributing quotes to specific people. Flatten the nested array once on ingest with the pattern that suits your warehouse:
-- Snowflake (assumes JSONL ingested into your_jsonl table with iab/etc as VARIANT)
CREATE OR REPLACE TABLE video_statements_flat AS
SELECT
v.video_id,
v.published_at,
s.value:text::STRING AS text,
s.value:kind::STRING AS kind,
s.value:stance::STRING AS stance,
s.value:sentiment::STRING AS sentiment,
s.value:strength::FLOAT AS strength,
s.value:subject.name::STRING AS subject_name,
s.value:subject.type::STRING AS subject_type
FROM your_jsonl_table v,
LATERAL FLATTEN(input => v.video_statements) s;Then run the aggregate:
-- Scale tier. Statements where the product is the SUBJECT,
-- grouped by stance and kind.
SELECT
vs.kind,
vs.stance,
vs.sentiment,
COUNT(*) AS occurrences,
ROUND(AVG(vs.strength), 2) AS avg_strength
FROM video_statements_flat vs
WHERE LOWER(vs.subject_name) = 'galaxy s26 ultra'
AND vs.published_at >= current_date - INTERVAL '60 days'
GROUP BY vs.kind, vs.stance, vs.sentiment
ORDER BY occurrences DESC;Read the result. Aggregate creator narrative on the product. Comparison + critical + negative cluster identifies the dominant counter-positioning frame; opinion + supportive + positive identifies the consensus pitch.
Creator economy
10. Creator roster drift: rolling industry mix and sponsor density
Who asks this
CreatorIQ / Grin customer-success lead.
What they do today
Score a creator once at onboarding; hope nothing changes.
Why this is hard
Creator roster needs continuous monitoring, not a one-shot vetting session.
-- 3-month industry-mix and sponsor-density trajectory for the creators
-- on your roster. Detects creators whose content is drifting outside
-- the industry you signed them for, OR whose sponsor density is
-- climbing (saturation risk).
SELECT
v.channel_id,
v.channel_name,
DATE_TRUNC('month', v.published_at) AS month,
COUNT(DISTINCT v.video_id) AS videos,
ROUND(
100.0 * COUNT(DISTINCT v.video_id) FILTER (WHERE v.industries LIKE '%consumer_electronics%')
/ NULLIF(COUNT(DISTINCT v.video_id), 0),
1
) AS pct_in_signed_industry,
ROUND(
100.0 * COUNT(DISTINCT b.video_id) FILTER (WHERE b.is_sponsor = TRUE)
/ NULLIF(COUNT(DISTINCT v.video_id), 0),
1
) AS pct_sponsored
FROM videos v
LEFT JOIN brands b ON b.video_id = v.video_id
WHERE v.channel_id IN ('UCaaa', 'UCbbb', 'UCccc') -- roster
AND v.published_at >= current_date - INTERVAL '90 days'
GROUP BY v.channel_id, v.channel_name, DATE_TRUNC('month', v.published_at)
ORDER BY v.channel_name, month DESC;Read the result. Any creator whose pct_in_signed_industry is dropping or pct_sponsored is climbing month over month needs a roster review before their next campaign.
Schema 3.1.0 recipes
11. International coverage with confidence-gated industry filtering — schema 3.1.0+
Who asks this
Global brand manager wanting Asia-Pacific creator coverage at high classifier confidence.
What they do today
Filter on US-only English content because non-English titles are unreadable in their tools.
Why this is hard
Most vendors don't translate titles or expose classifier confidence — buyers can't combine "give me Korean and Japanese coverage" with "only high-confidence consumer_electronics tags."
-- Schema 3.1.0+ (every tier). Top non-English videos in
-- consumer_electronics where the classifier was >= 0.8 confident,
-- with the English-translated title and channel name for readability.
-- Note: industries_detail is JSON-stringified in Parquet; parse it
-- once per row.
SELECT
v.video_id,
v.language,
v.title,
v.title_translated,
v.channel_name,
v.channel_name_translated,
v.view_count,
v.published_at
FROM videos v,
LATERAL FLATTEN(input => PARSE_JSON(v.industries_detail)) id
WHERE v.language IN ('ko', 'ja', 'zh-CN', 'zh-TW', 'th', 'id')
AND id.value:industry::STRING = 'consumer_electronics'
AND id.value:confidence::FLOAT >= 0.80
AND v.published_at >= current_date - INTERVAL '30 days'
ORDER BY v.view_count DESC
LIMIT 100;Read the result. Asia-Pacific consumer-electronics inventory at 0.8+ classifier confidence with readable English labels. Drop into an international media plan.
Notes on portability
Warehouse syntax. Examples use Snowflake / BigQuery dialects where they differ. The patterns to watch: LATERAL FLATTEN(input =>) is Snowflake; BigQuery uses UNNEST() directly; DuckDB has json_each and UNNEST(). SPLIT(string, ', ') works in BigQuery and Snowflake (returns array); use STRING_TO_ARRAY(string, ', ') in Postgres/Redshift. FILTER (WHERE …) works in Postgres, Snowflake, BigQuery; use COUNT_IF(...) in Snowflake-only dialects. If a query fails in your warehouse, send the error to [email protected] with the warehouse name and we will ship a translated version.
File naming. The Scale-tier 5th Parquet table is delivered as claims.parquet; load it as whatever table name your pipeline prefers (the queries above use industry_claims for clarity).
Schema version. All queries are written against feed schema 3.2.0. Older deliveries may not have title_translated, language, channel.name_translated, industries_detail, the People Graph attributes, affiliate_url, or the statements Parquet table populated.
Detailed reference
What you can expect
- Ad tech / programmatic: tier-3 contextual with disclosure verification, sponsorship-density heatmap
- Brand safety: independent second-opinion on vendor scores, FTC disclosure audit
- Competitive intel: dark SOV recovery, organic vs sponsored ratio across three brands
- Media planners: RFP-ready creator landscape with sponsor density and safety distribution
- Alt data: CEO forward-looking statements before earnings windows
- Brand intelligence: industry claim trajectory on a specific dimension
- Creator economy: roster drift detection across rolling windows
Limits
- Queries that require data we do not publish (full transcripts, PII, speaker diarization)
- Platform-specific optimizations (sub Snowflake/BigQuery/Databricks syntax as needed)
- Real customer data (all channel IDs shown are placeholders)
If something looks off
If a query does not return expected results, first confirm your tier covers the required fields (Scale for video_statements and industry_claims). If the syntax fails in your warehouse, send the error to [email protected] with the query and your warehouse (Snowflake, BigQuery, Databricks, Redshift) and we will ship a translated version.
Important context
These queries reflect research on 2025-2026 buyer pain points: the February 2025 Adalytics report on DV/IAS failures, the Senate letters from Blackburn and Blumenthal, GARM framework gaps, the Microsoft Invest sunset driving DSP fragmentation, FTC enforcement escalation, and the 75% data deficit in text-based social listening. Every query title is a real question buyers are asking vendors and getting inadequate answers to.
Last updated 2026-05-05