THE CHEATSHEET
18 things YouTube media buyers do
in one terminal session.
Each of these is a real workflow someone is currently doing in 11 browser tabs and a Slack thread. Each one collapses to a handful of tl db pg|fb|es queries you can hand to your agent. None require a TL employee. None require leaving the terminal.
The pattern is always the same: narrow on the server, stitch in the shell. One SQL query replaces a paginated walk plus a Python reduce. One ES body replaces a transcript scrape. One Firebolt slice replaces a year of weekly snapshots. These queries are the kind of thing your AI agent should be making.
RAW DB · NO PAGINATION TAX
PUBLIC SCHEMA · OPEN TO BUILD ON
YOUR AGENT, YOUR TERMINAL, YOUR SCRIPT
01
The non-compete check
You bought a $14k integration on a tech channel last month. Did the same channel run your competitor in the 90 days before — or after — you went live?
- 01Pull every sponsorship that channel ran in a ±90-day window around your publish_date
- 02Filter to brands in your category (or your explicit competitor list)
- 03Surface a clean list with brand, publish date, and days from your ad
$ tl db pg "
SELECT a.id, b.name AS brand, a.publish_date,
a.publish_date - DATE '2026-02-15' AS days_from_yours
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
JOIN thoughtleaders_profile_brands pb ON p.id = pb.profile_id
JOIN thoughtleaders_brand b ON pb.brand_id = b.id
WHERE s.channel_id = 12465
AND a.publish_status = 3
AND a.publish_date BETWEEN '2025-11-15' AND '2026-05-15'
AND b.name IN ('NordVPN','ExpressVPN','ProtonVPN')
ORDER BY a.publish_date" 02
Vet an incoming proposal in 30 seconds
An AM forwards a proposal: $9,500 for a tech channel. You have no idea if it's fair, who else they've run, or whether their views hold up over time.
- 01Pull the channel's last 24 months of sold deals: brand, price, repeat flag
- 02Compare proposed price to median for that channel
- 03Pull the view curve for their last 5 sponsored videos to confirm the ad doesn't die in week one
$ # 1. price + brand history
tl db pg "
SELECT b.name, a.price, a.publish_date,
COUNT(*) OVER (PARTITION BY b.id) AS times_run
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
JOIN thoughtleaders_profile_brands pb ON p.id = pb.profile_id
JOIN thoughtleaders_brand b ON pb.brand_id = b.id
WHERE s.channel_id = 12465 AND a.publish_status = 3
AND a.purchase_date >= NOW() - INTERVAL '24 months'
ORDER BY a.purchase_date DESC"
# 2. view curves for the last sold ads
tl db fb "SELECT id, age, view_count
FROM article_metrics
WHERE channel_id = 12465 AND age <= 180
ORDER BY id, age" 03
Verify your ads actually went live
You sold a flight of 8 deals for Q1. Three send dates have passed. Did the videos publish? Did the creator forget? Did your AM forget to tell you?
- 01Pull all your sold deals with send_date in the past
- 02Flag any without a publish_date (or without an article_id)
- 03Cross-check the channel's actual recent uploads in ES for an unflagged drop
$ tl db pg "
SELECT a.id, b.name AS brand, c.channel_name, a.send_date,
a.publish_date, a.article_id
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_channel c ON s.channel_id = c.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
JOIN thoughtleaders_profile_brands pb ON p.id = pb.profile_id
JOIN thoughtleaders_brand b ON pb.brand_id = b.id
WHERE p.organization_id = <my_org_id>
AND a.publish_status = 3
AND a.send_date < NOW()
AND (a.publish_date IS NULL OR a.article_id IS NULL)
ORDER BY a.send_date" 04
Build a target list for a new campaign
Marketing wants 25 channels for a Q3 finance launch — creator-led, US-leaning, mobile-skewed, last upload in the past 60 days, available in the MSN.
- 01Use the recommender to rank channels on the right topic tag
- 02Hand the IDs to a SQL filter for the demographic/recency cutoffs
- 03Export to CSV for the AM to outreach against
$ tl recommender top-channels "Personal Finance" msn:yes --limit 200 --json \
| jq -r '.results[].channel_id' | paste -sd, - \
| xargs -I {} tl db pg "
SELECT id, channel_name, reach, demographic_usa_share,
demographic_device_primary, last_published, sponsorship_score
FROM thoughtleaders_channel
WHERE id IN ({})
AND demographic_usa_share >= 55
AND demographic_device_primary = 'mobile'
AND last_published >= CURRENT_DATE - INTERVAL '60 days'
AND is_active = TRUE
ORDER BY sponsorship_score DESC NULLS LAST
LIMIT 25 OFFSET 0" --csv > targets.csv 05
Catch a competitor poaching your channels
Your competitor just sponsored 4 of the channels you've been running with for 18 months. You'd rather find out today than 4 months from now.
- 01Pull the channels you've sold deals on in the past 12 months
- 02Find any sold sponsorship from your competitor on those channels in the past 30 days
- 03Sort by recency, ping the AM
$ tl db pg "
WITH my_channels AS (
SELECT DISTINCT s.channel_id
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
WHERE p.organization_id = <my_org_id>
AND a.publish_status = 3
AND a.purchase_date >= NOW() - INTERVAL '12 months'
)
SELECT c.channel_name, b.name AS competitor, a.publish_date, a.price
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_channel c ON s.channel_id = c.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
JOIN thoughtleaders_profile_brands pb ON p.id = pb.profile_id
JOIN thoughtleaders_brand b ON pb.brand_id = b.id
WHERE s.channel_id IN (SELECT channel_id FROM my_channels)
AND b.name IN ('NordVPN','ExpressVPN')
AND a.publish_status = 3
AND a.publish_date >= NOW() - INTERVAL '30 days'
ORDER BY a.publish_date DESC" 06
Pre-negotiation evidence pack
A talent manager is asking $18k. You want a counter that's defensible — backed by what the channel has actually closed at, plus comparable channels.
- 01Pull the channel's median, p25, p75 prices over the last 18 months
- 02Pull the same percentiles for 10 lookalike channels
- 03Build a one-pager showing fair-market with realised view performance
$ tl db pg "
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY a.price) AS p25,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY a.price) AS median,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY a.price) AS p75,
COUNT(*) AS n_deals
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
WHERE s.channel_id = 12465
AND a.publish_status = 3
AND a.purchase_date >= NOW() - INTERVAL '18 months'"
tl channels similar 12465 --limit 10 --json \
| jq -r '.results[].channel_id' \
| xargs -I {} tl db pg "<same percentile query, channel_id IN ({})>" 07
Renewal candidates report
Renewal season. You want every channel that ran your brand 2+ times AND delivered above-median views, sorted by repeat probability.
- 01Pull deals where your brand published, grouped by channel
- 02Filter to channels with ≥2 sold deals
- 03Join to ES for actual published view counts and rank
$ tl db pg "
SELECT c.id, c.channel_name,
COUNT(*) AS deals_with_us,
AVG(a.price)::int AS avg_price,
MIN(a.publish_date) AS first_run,
MAX(a.publish_date) AS last_run
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_channel c ON s.channel_id = c.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
JOIN thoughtleaders_profile_brands pb ON p.id = pb.profile_id
JOIN thoughtleaders_brand b ON pb.brand_id = b.id
WHERE b.id = <my_brand_id>
AND a.publish_status = 3
AND a.publish_date IS NOT NULL
GROUP BY c.id, c.channel_name
HAVING COUNT(*) >= 2
ORDER BY deals_with_us DESC, avg_price DESC
LIMIT 50 OFFSET 0" 08
Honest evergreen tail audit
Your attribution tool reports 7-day performance. The 85% of views that arrive in months 2–12 are invisible to you. Calculate the real evergreen multiplier for every ad you ran last year.
- 01List your sold ads from last year with article_id present
- 02For each, pull view_count at age=30 and age=180 from Firebolt
- 03Compute (views_180d − views_30d) ÷ views_30d per ad — the official evergreenness formula
$ tl db pg "SELECT a.id, a.article_id, s.channel_id
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
WHERE p.organization_id = <my_org_id>
AND a.publish_status = 3
AND a.publish_date BETWEEN '2025-01-01' AND '2025-12-31'
AND a.article_id IS NOT NULL" --json > ads.json
# Then per-ad in Firebolt (composite ID required):
tl db fb "SELECT id, age, view_count
FROM article_metrics
WHERE channel_id = 12465
AND id = '12465:dQw4w9WgXcQ'
AND age IN (30, 180)" 09
Pipeline aging — what's stalling?
Your pending pipeline has 31 deals. Some have been pending for 2 weeks; some for 4 months. You have no idea which are alive.
- 01Pull all pending/proposal-approved deals for your org
- 02Compute days since last touch (updated_at)
- 03Sort by oldest, flag anything > 21 days
$ tl db pg "
SELECT a.id, c.channel_name, b.name AS brand, a.price,
a.publish_status,
(NOW() - a.updated_at)::interval AS stale_for
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_channel c ON s.channel_id = c.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
JOIN thoughtleaders_profile_brands pb ON p.id = pb.profile_id
JOIN thoughtleaders_brand b ON pb.brand_id = b.id
WHERE p.organization_id = <my_org_id>
AND a.publish_status IN (2, 6, 8)
ORDER BY a.updated_at ASC
LIMIT 100 OFFSET 0" 10
Brand-safety transcript scan
Before you commit to a 30-channel campaign, you want to know which of those creators have ever said the things your legal team flags.
- 01Hand the channel IDs to ES, restrict to longform of theirs in the past 12 months
- 02Match transcript text against your blocklist terms
- 03Return one row per offending video with timestamp + URL
$ tl db es '{
"size": 50,
"query": {
"bool": {
"must": [
{"terms": {"channel.id": [12465, 9342, 18293, 33001]}},
{"range": {"publication_date": {"gte": "now-12M"}}},
{"match": {"transcript": "crypto rugpull lawsuit indictment"}}
]
}
},
"_source": ["id", "channel.id", "publication_date", "title"]
}' 11
Find lookalikes of your best-performing channel
One channel out of last quarter's flight crushed it. You want 20 more like it — same vibe, same audience shape, in the MSN, not yet pitched by you.
- 01Use channels similar for the seed channel
- 02Drop anyone you've already proposed/sold to
- 03Keep MSN-only, sort by similarity score
$ tl channels similar 29834 msn:yes --limit 50 --json \
| jq -r '.results[].channel_id' > candidates.txt
tl db pg "
SELECT DISTINCT s.channel_id
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
WHERE p.organization_id = <my_org_id>" --json > already.json
# diff and you have your fresh outreach list 12
Category penetration report
Your CMO asks: of the 600+ active US-leaning Cooking channels with 100k+ subs, how many has our brand actually reached?
- 01Use the recommender to enumerate the universe
- 02Cross with your own deal history
- 03Spit out reached / not-reached counts and the gap list
$ tl recommender top-channels "Cooking" msn:yes --limit 500 --json > universe.json
tl db pg "
SELECT DISTINCT s.channel_id
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
JOIN thoughtleaders_profile_brands pb ON p.id = pb.profile_id
WHERE pb.brand_id = <my_brand_id>
AND a.publish_status = 3" --json > reached.json
duckdb -c "
SELECT u.* EXCLUDE (reached)
FROM (SELECT *, channel_id IN (SELECT channel_id FROM 'reached.json')
AS reached
FROM 'universe.json') u
WHERE NOT reached" 13
Discover MSN joiners this week
11k+ channels in the MSN, 6,173 added in the last 90 days. You want a Monday-morning email of every new joiner that matches your category.
- 01Filter channels by media_selling_network_join_date in the last 7 days
- 02Match against your topic tags via the recommender
- 03Pipe into your existing outreach queue
$ tl db pg "
SELECT id, channel_name, content_category, reach, country
FROM thoughtleaders_channel
WHERE media_selling_network_join_date >= CURRENT_DATE - INTERVAL '7 days'
AND content_category IN (10, 15, 16) -- Business, Technology, Gaming
AND reach >= 50000
AND is_active = TRUE
ORDER BY reach DESC
LIMIT 100 OFFSET 0" 14
Ghost / no-respond detector
Some channels accept proposals and then disappear. You want to flag any channel where multiple proposals have been sent and zero have published.
- 01Group adlinks by channel; count proposals vs publishes
- 02Filter to ratio = 0 with N ≥ 3
- 03Add to your do-not-pitch list
$ tl db pg "
SELECT s.channel_id, c.channel_name,
COUNT(*) FILTER (WHERE a.publish_status IN (0, 6, 8)) AS proposals,
COUNT(*) FILTER (WHERE a.publish_status = 3
AND a.publish_date IS NOT NULL) AS published
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_channel c ON s.channel_id = c.id
WHERE a.created_at >= NOW() - INTERVAL '12 months'
GROUP BY s.channel_id, c.channel_name
HAVING COUNT(*) FILTER (WHERE a.publish_status IN (0, 6, 8)) >= 3
AND COUNT(*) FILTER (WHERE a.publish_status = 3
AND a.publish_date IS NOT NULL) = 0
ORDER BY proposals DESC" 15
CPM benchmark for your category
You want to know the real CPM distribution paid by similar brands on similar channels — not the rate-card fiction.
- 01Pull all sold deals in your category in the past 12 months with article_id present
- 02Join to current channel impression projections
- 03Compute price ÷ impression × 1000 and bucket the distribution
$ tl db pg "
SELECT a.id, a.price, c.impression,
(a.price / NULLIF(c.impression,0) * 1000)::numeric(10,2) AS projected_cpm
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_channel c ON s.channel_id = c.id
WHERE c.content_category = 15
AND a.publish_status = 3
AND a.purchase_date >= NOW() - INTERVAL '12 months'
AND c.impression IS NOT NULL
AND c.impression > 0
ORDER BY projected_cpm" --csv > cpm.csv
duckdb -c "SELECT
PERCENTILE_CONT(0.10) WITHIN GROUP (ORDER BY projected_cpm) AS p10,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY projected_cpm) AS median,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY projected_cpm) AS p90
FROM 'cpm.csv'" 16
Auto-place a matched deal from your agent
Your agent just finished the vetting workflow and decided to book the deal. You don't want to context-switch into a UI.
- 01Hand the channel ID and brand to tl sponsorships create
- 02Server-side guardrails enforce MSN, integration, persona, plan
- 03Ad lands in the same TL pipeline your AM works out of
$ tl sponsorships create --channel 12465 --brand <brand_id>
# 0 credits — deal creation is always free.
# Returns the new adlink ID + a tracking URL. 17
Post-campaign report you can hand to the CMO
Quarter end. You ran 22 deals across 3 brands. You need a single sheet with: spend, realised views, evergreen multiplier, repeat % into next quarter.
- 01Pull your quarter's sold deals with brand, channel, price
- 02Hand article_ids to ES for realised view counts
- 03Hand the same IDs to Firebolt for the evergreen multiplier
- 04Roll up by brand and ship as a CSV
$ # Step 1: deal list
tl db pg "
SELECT a.id, a.article_id, s.channel_id, b.name AS brand, a.price
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
JOIN thoughtleaders_profile p ON a.creator_profile_id = p.id
JOIN thoughtleaders_profile_brands pb ON p.id = pb.profile_id
JOIN thoughtleaders_brand b ON pb.brand_id = b.id
WHERE p.organization_id = <my_org_id>
AND a.publish_status = 3
AND a.publish_date BETWEEN '2026-01-01' AND '2026-03-31'
AND a.article_id IS NOT NULL" --json > q1.json
# Step 2 + 3: views and evergreen for each ad — agent stitches them. 18
Outreach prep — who do I email?
You found the channel. You don't have an inbox match. Pull the publisher contact + their last 3 sponsorship emails on file in one go.
- 01tl sponsorships show on a recent adlink for that channel
- 02Detail payload includes nested publisher block (first/last name, email)
- 03Cross-reference with their other recent sold deals to confirm contact is live
$ tl sponsorships show <recent_adlink_id> --json \
| jq '.publisher, .outreach_email, .common_name'
# To confirm contact is current, find the latest sold deal on the channel:
tl db pg "
SELECT id, outreach_date, updated_at
FROM thoughtleaders_adlink a
JOIN thoughtleaders_adspot s ON a.ad_spot_id = s.id
WHERE s.channel_id = 12465 AND a.publish_status = 3
ORDER BY a.purchase_date DESC LIMIT 1" Every workflow above shipped today. No private endpoints used.
Every query in this page runs against the same public CLI. Same auth. Same credit rates. Same response envelope. If your workflow isn't here, write it. If it's good, send it back to us.