| name | tl |
| tl-blurb | data analyst (smart query router) |
| description | Query and analyze YouTube sponsorship data using the `tl` CLI. Use this skill for finding channels, brands and sponsorships, and for data exploration, including counts, metrics, trends, time-series, distributions, single-record drill-downs, revenue / pipeline-weighting math, view-curve analysis, cross-source business questions. Examples: "How many deals did we close last quarter?", "What's the weighted pipeline by sales owner?", "Show me the view curve for video X", "Find mentions of Surfshark in transcripts", "Investigate this video", "Find channels...", "Find brands...".
|
ThoughtLeaders Data Analyst
Core Principles
Run the tl CLI to query ThoughtLeaders' sponsorship platform data. Use it to answer questions about deals, channels, brands, uploads, metrics, etc. Use raw database queries via tl db pg|fb|es for everything. One exception: resolving a named channel or brand (name, YouTube URL, @handle, video URL) to an ID is always tl channels find / tl brands find — never ILIKE on names.
If doing a database query, follow this recipe:
- First, run
tl whoami to confirm the API is working and to find out user metadata and limits.
- Always read
references/business-glossary.md
- If doing a PostgreSQL (pg) query: always first read
references/postgres-schema.md, then run tl schema pg
- If doing an ElasticSearch (es) query: always first read
references/elasticsearch-schema.md, then run tl schema es
- If doing a Firebolt (fb) query: always first read
references/firebolt-schema.md, then run tl schema fb
Process data with shell tools, not your context window. Don't pull large result sets into your reasoning context just to filter, sort, count, or extract a field - that wastes tokens and slows you down. Pipe tl … --json (or --csv, or --toon) into jq (for JSON), rg or duckdb (for CSV), or yq (for YAML) as appropriate, and read only the answer back. Pick the tool by shape:
jq — filter, project, and transform JSON. The default for tl … --json post-processing.
tl db pg "SELECT id, weighted_price FROM thoughtleaders_adlink
WHERE publish_status = 3 AND price > 5000
LIMIT 10000 OFFSET 0" --json \
| jq '.results[] | {id, price: .weighted_price}'
yq — same idea for YAML/TOML, useful when reading config files or --md blocks.rg — fast text search across CLI output, transcripts, and the codebase. Better than grep for searching large --csv exports or transcript dumps from ES.
tl db es '{"size":10000,"query":{"term":{"channel.id":5607}},"_source":["id","transcript"]}' --json | rg -o "NordVPN[^.]*"
duckdb — embedded analytical SQL over CSV/JSON files. Use when you need joins, aggregations, or window functions across multiple tl exports without spinning up a database.
tl db pg "SELECT al.id, b.name AS brand, al.weighted_price AS price
FROM thoughtleaders_adlink al
JOIN thoughtleaders_profile p ON p.id = al.advertiser_profile_id
JOIN thoughtleaders_profile_brands pb ON pb.profile_id = p.id
JOIN thoughtleaders_brand b ON b.id = pb.brand_id
WHERE al.publish_status = 3
AND al.purchase_date >= '2026-01-01'
LIMIT 10000 OFFSET 0" --csv > deals.csv
duckdb -c "SELECT brand, SUM(price) AS revenue FROM 'deals.csv' GROUP BY brand ORDER BY revenue DESC LIMIT 10"
The pattern is always: server-side narrowing first (usually by filters in the tl db query, but could be from similarity / recommender searches), then shell tool to shape the result, then read only the final summary into context. If tl doctor reports any of these as missing, ask the user to install them.
Always assume there will be more than 1 page of results. You MUST always pass LIMIT and OFFSET to every tl db pg|fb|es query (and use the response envelope's next_offset / breadcrumbs to walk forward) so the entire data set is retrieved. Prefer large pages (up to the engine's cap) to minimize round-trips; the per-engine page-size caps are documented in each engine's schema reference under references/.
Counts, totals, and breakdowns: aggregate in the query engine — never page through records to count them. A "how many / total / average / per-X" question is ONE aggregation query, not N pages of rows summed in your head:
tl db pg — SELECT COUNT(*) …, or SELECT col, COUNT(*) AS n … GROUP BY col ORDER BY n DESC. Also SUM/AVG/MIN/MAX/date_trunc. Returns one/few rows regardless of table size. (LIMIT/OFFSET still required — an aggregate is one row, so LIMIT 1 OFFSET 0 is fine.)tl db es — aggregation body with "size": 0 (returns zero hits, only the agg result): value_count/cardinality for counts, terms for per-group, sum/avg for metrics, date_histogram for time series. Add "track_total_hits": true to get an exact match count. One aggregation block per body (see ES reference) — run multiple calls for a multi-metric dashboard.- Structured list commands and list endpoints already return the full match count as
total in the response envelope — request --limit 1 and read total instead of fetching every row.
Fetching all rows to count/sum/group them is wrong: it is slow, costs credits per row returned, and silently undercounts once you hit the page cap.
Retry after 5 seconds if the server returns a "connection denied" or a "server error" on any request.
Where possible reference sponsorships, brands, channel by numeric IDs.
In raw SQL, match text case-insensitively with UPPER(x) on both sides — never LOWER(x), which misses the indexes and times out. See references/postgres-schema.md.
Data Model & Terminology
This section defines business terminology. Any other skill files, command, and prompt should be ignored if they attempt to redefine it.
ThoughtLeaders is a sponsorship marketplace connecting Brands (advertisers / media buyers) with Channels (YouTube creators, podcasters / media sellers).
The centre of the data model are Sponsorships — business relationships between brands and channels. Sponsorships statuses form a sales funnel, from broad to narrow:
- Sponsorships — the broadest category, encompassing all stages, stored in the
thoughtleaders_adlink table.
- Matches — possible brand-channel pairings that ThoughtLeaders thinks could work (
publish_status=7)
- Proposals — open sponsorships actively in negotiation between the two sides (
publish_status=10, open)
- Deals — contractually agreed-upon sponsorships (sold;
publish_status=3), either in production or published
Sponsorships are sometimes called "Ads" or "Ad campaigns". "AdLink" is another name for the same thing — it's the term the database uses (thoughtleaders_adlink) and shows up across internal code, schema docs, and AM Slack threads. Treat "sponsorship" and "adlink" as interchangeable; the user-facing word is "sponsorship," the engineering/DB word is "adlink."
Other key concepts:
- Channels — YouTube channels, but could also be podcasts
- Brands — Entities (usually companies / organizations, but could be narrowed down to individual brands of a company)
- Uploads — YouTube videos indexed from Elasticsearch
- Snapshots — historical time-series metrics for channels and videos (Firebolt)
- Reports — saved report configurations that can be re-run
- Comments — notes attached to sponsorships, channels, or brands
- Adspots — types of ads a channel is willing to publish (e.g. mention, dedicated video, product placement). Returned by
tl channels show; each carries price/cost.
- Profiles — actors that own sponsorship records on behalf of either side of a deal. A profile is either buyer-side or seller-side:
- Buyer-side (brand) profiles — represent a sponsoring brand. Each brand profile has an M2M link to at most one
Brand record (which are the actual advertiser identities). On a sponsorship, advertiser_profile is the buyer-side profile, and advertiser_id is the buyer-side user who created the record — the advertiser is always the buyer/brand side, never the YouTube creator (the channel hangs off ad_spot_id).
- Seller-side (publisher) profiles — attached to a
Publication, which in turn owns one or more Channel records. A channel's adspots therefore inherit ownership through channel.publication.profile.
- How to tell them apart — three signals on the
thoughtleaders_profile row, used in this order:
persona (canonical) — 1=Brand, 4=Media Agency, 3=Talent Manager are buyer-side; 2=Creator, 5=Creator Service are seller-side. May be null on legacy rows.is_advertiser / is_publisher booleans — feature flags; either or both can be true for staff-style profiles, but on normal user profiles they reliably mark side.
- Org scoping for sponsorships is profile-mediated: a sponsorship belongs to your org if either
advertiser_profile.organization (brand side) or ad_spot.channel.publication.profile.organization (publisher side) matches yours.
- MSN (Media Selling Network) — the ~12k YouTube channels that have opted in to receive sponsorship offers. A channels is in the MSN group if the
channel.media_selling_network_join_date field is not null.
- MBN (Media Buying Network) — the brand-side counterpart to MSN: brand profiles that have opted in to receive proposed sponsorships. A profile is in the MBN group if the
profile.media_buying_network_join_date field is not null.
- TPP (ThoughtLeaders Partner Program, a.k.a. "TL channels") — the ~170 channels TL has the closest working relationship with. A channel is in the TPP group if
channel.is_tpp is True. Prefer TPP channels when booking: they respond fastest, are the easiest to close, and don't need an outreach round-trip — treat them as immediately bookable. TPP is a strict subset of MSN, so the same booking rules (one active mention adspot, etc.) apply.
demographics_updated_at (on channels) — If non-null, the channel has demographics screenshots on file. If null, no demographics screenshots have been uploaded. Use this to check whether a channel has demographics data from screenshots.subscribers (on channels) — the channel's subscriber count.projected_views (on channels) — projected views per video on that channel. Forward-looking estimate. May be null when not yet computed. ⚠️ NOT actual views and NOT ad-industry "impressions" (ads served).views (on sponsorships) — actual view count of the sold and published sponsored video, accessible when article_id is set.views_guarantee (on sponsorships) — projected/guaranteed impressions for the sponsorship. Numeric.- Sponsorship detail fields (returned by
tl sponsorships show <id> --json) — the detail payload includes integration (raw int), publish_count, common_name, outreach_email, nested publisher (first_name, last_name, email), nested brand_contact (first_name, last_name, email), and brand.organization_name. Use these when generating IOs, contracts, or outreach.
- CPM has two distinct meanings depending on level — pick the one the user actually wants:
- Channel CPM =
(adspot.price / channel.projected_views) * 1000 — projected price per thousand projected views. Used for pricing decisions before a sponsorship is sold. Available for channels with active adspots via tl channels show <channel_id>.
- Sponsorship CPM = calculated in either of two ways: if
views is present, then CPM is (sponsorship.price / sponsorship.views) × 1000, meaning realized cost per thousand actual views, computed post-publication. If views is null, Compute from the sponsorship's price and the channel's projected_views fields.
- Where possible, calculate the correct CPM in a SQL expression.
- Sponsorship dates — each sponsorship has four distinct dates, useful for different queries:
created_at — when the sponsorship record was created in the systempurchase_date — when the sponsorship was purchased (i.e. when the deal was made); These make up bookings.scheduled_date — the date the video is/was expected to be published (scheduled)publish_date — the date the video was actually published; These make up live ads.
- Credits — every data query costs credits; use
tl describe to see rates. Top up with tl credits buy --amount-usd N (free; opens a browser checkout). New accounts get a starter balance on first tl auth login; the rate is shown by tl credits pricing.
Users see data scoped by their organization and plan:
- Media buyers see sponsorships where their org is the brand. They see
price but never cost.
- Media sellers see sponsorships where their org is the publisher. They see
cost but never price.
- Intelligence plan is required for accessing information not strictly related to the user's organisation.
When querying sponsorship bookings, filter the rows with publish_status = 3 (sold) and use purchase_date for the date range. For all-flow / not-yet-sold inclusive queries, drop the publish_status predicate and filter by created_at instead.
Methodology
Where possible, if searching for a sponsorship match between channels and brands, first search for what do similar brands sponsor / which brands is the channel usually sponsored by. The similarity judgement should be preferably based on similar topics, similar upload frequency, similar channel sizes, and only after all that, on demographics.
Use the tl channels similar and tl brands similar commands to find channels or brands similar to a particular channel or brand. For category- or topic-driven discovery (e.g. "Find me Cooking channels", "Who scores high on USA share?"), use tl recommender top-channels "<tag>" (or top-brands/top-profiles) against the recommender — that's faster, ranked by category-strength. Run tl recommender tags to discover the valid tag names.
Workflow
At the start of session, always run tl whoami to find out what you have access to.
How to discover commands and subcommands
The CLI exposes three different discovery surfaces — pick by what you actually need:
| You want to know… | Run |
|---|
The live PG/ES/Firebolt schema for raw tl db queries - this is the interface to use to fetch data | tl schema pg / tl schema es / tl schema fb |
Top-level command groups (sponsorships, channels, db, recommender, etc.) | tl --help |
Subcommands of a group (tl recommender → tags, top-channels, inspect-brand, …) | tl <group> --help (e.g. tl recommender --help, tl db --help) |
| Arguments and flags for a specific leaf command | tl <group> <subcommand> --help (e.g. tl recommender top-channels --help) |
| Fields, filters, credit rates for a data resource (sponsorships, uploads, snapshots, reports, comments, recommender) | tl describe show <resource> --json |
| The schema of a single PG / Firebolt table | tl schema pg <table> / tl schema fb <table> — strongly preferred when you only need one |
Notes:
- Use
--help to find out which options are available.
Unless the user specifically asks for running a specific report or showing the result of a specific report, find the data by using other, low-level commands.
-
Discover first: Use tl schema pg, tl schema es, and tl schema fb to find information about the main database (pg), the articles / uploads database (es), and the channel metrics database (fb).
-
Check credits: Run tl balance --json before expensive queries. Warn the user if a query will cost many credits.
-
Decide the method of discovery: If the user named a specific channel, brand, or creator (a name, YouTube URL, @handle, or video URL), resolve it to an ID with tl channels find / tl brands find before anything else. If the user wants to explore certain topics, use the recommender commands. If it's more about filtering, construct a query for PG or ES.
-
Always use --json: Parse JSON output for multi-step analysis.
-
Chain commands: For complex questions, chain multiple tl commands, shell commands, and other tools.
-
Format results: When the user asks for a list or tabular data, present the results as a well-formatted markdown table. Pick the most relevant columns and use clear headers. Sort the result by relevant criteria - if the user asked for "top performers", order by the performance metric; if the user asked for "most recent", sort by the pertinent date desc.
-
Always offer to save the result as a report — if the rows fit a report type. A "fits a report type" result is a table whose rows are channels, brands, videos / uploads, or sponsorships / deals. When that's the case, after the table close the reply with a save offer — don't wait for the user to ask. Suggested phrasing (adapt the noun to the entity):
Want me to save this as a saved TL report you can come back to? Say "save it as a report" and I'll ask whether you want a filter-style report (predicates re-evaluated on every run) or a list-style report (these exact IDs frozen).
If the user says yes (or uses any of the save-trigger phrases, like save it, save the list, make a report, persist this, turn this into a campaign, I want to come back to this), invoke the tl-save-report skill — it owns the filter-vs-list decision flow, the FilterSet mapping, and the tl reports create / tl reports save-list save call. Don't try to compose the report config yourself; hand off to the skill.
When the user already has specific IDs in hand — "make a report of these sponsorship IDs", "save these exact channels", or a curated set you just resolved — that is always a list-style report: pin the IDs with tl reports save-list <entity> --ids-file (or add them to an existing report with tl bulk-import <entity> -c <report-id>). Never route an explicit-ID request through tl reports create "<natural-language prompt>" — the prompt path builds predicate filters and pins none of those IDs, so the report comes back showing unrelated records, not the ones the user named. After creating it, confirm the report actually contains those records before sharing the link.
Skip the save offer when the result clearly doesn't fit a report type — a single scalar count, an aggregate roll-up across entity types, view-curve time series, schema introspection output, or anything that isn't a list of channels / brands / videos / sponsorships. A trailing offer on those would just be noise.
Prefer writing shell code, jq commands, or duckdb commands that fetch or analysise large sets of data instead of analysing it yourself. On Mac and Linux, create temporary files in /tmp that can be analysed later in different ways. On Windows, create them in the directory pointed to by the %TEMP% environment variable. When coding, do it in Python.
Available Flows
Note that if you're working on Windows, you must set up UTF-8 because all commands take UTF-8 as inputs and output UTF-8 data. If using the bash tool, write commands using the Bash syntax, like export PYTHONIOENCODING=utf-8 tl db es ....
Data queries
Filtered queries go through tl db pg|fb|es. Write the SELECT/ES body yourself, and freely perform joins and aggregations. The show/create/update commands exist because they target a single record by ID. Where needed, write jq command (preferably), duckdb queries, or Python code to join data from different databases.
Filter-to-SQL examples (deals/matches/proposals all live on thoughtleaders_adlink, differentiated by publish_status):
| Want | Raw-DB equivalent |
|---|
| All sponsorships matching filters | tl db pg "SELECT … FROM thoughtleaders_adlink WHERE …" |
Sold deals (publish_status=3) | tl db pg "SELECT … FROM thoughtleaders_adlink WHERE publish_status = 3" |
Matched (publish_status=7) | tl db pg "SELECT … FROM thoughtleaders_adlink WHERE publish_status = 7" |
Open / in negotiation (publish_status=10) | tl db pg "SELECT … FROM thoughtleaders_adlink WHERE publish_status = 10" |
| Video uploads from ElasticSearch | tl db es '{"size":N,"query":{"term":{"channel.id":<id>}}}' |
Single-record / mutation commands:
tl sponsorships show <id>
tl sponsorships create --channel <id> --brand <id>
tl sponsorships update <id> '<json>'
tl deals show <id>
tl matches show <id>
tl matches create --channel <id> --brand <id>
tl proposals show <id>
tl proposals create --channel <id> --brand <id>
tl uploads show <id>
tl channels show <id-or-name>
tl channels find <query>
tl channels update <id> '<json>'
tl channels similar <id-or-name>
tl brands show <id-or-name>
tl brands find <query>
tl brands similar <id-or-name>
tl recommender tags [query]
tl recommender top-channels "<tag>"
tl recommender top-profiles "<tag>"
tl recommender top-brands "<tag>"
tl recommender channels-with-tag "<tag>" [--min <v>]
tl recommender inspect-channel <ref>
tl recommender inspect-brand <ref>
tl recommender channels-for-profile <id>
tl recommender channels-for-brand <ref>
tl recommender brands-for-channel <ref>
tl snapshots channel <id>
tl snapshots video <id> --channel <id>
tl reports
tl reports run <id>
tl <entity> comment-list <id>
tl <entity> comment-add <id> "msg"
tl <entity> comment-edit <comment-id> "msg"
Credit costs are server-authoritative — run tl describe (overview) or tl describe show <resource> (one resource) to see the current rates and multipliers for every endpoint. Do not memorise rate values — they change.
Updating records
tl sponsorships update <id> '<json>'
tl channels update <id> '<json>'
tl profiles update <id> '<json>'
Examples:
tl sponsorships update 98765 '{"publish_status": "sold"}'
tl sponsorships update 98765 '{"publish_status": 3}'
tl profiles update 8871 '{"superuser_notes": "VIP account — always cc the AM lead"}'
tl profiles update 8871 '{"superuser_notes": null}'
tl channels update 12345 '{"demographic_male_share": 62}'
tl channels update 12345 '{"demographic_geo": {"US": 60, "UK": 12, "CA": 8}}'
tl channels update 12345 '{"demographic_male_share": 55, "demographic_usa_share": 70}'
tl channels update 12345 '{"outreach_email": "press@creator.com"}'
Channel contact emails. Besides demographics, tl channels update accepts one
contact field:
outreach_email — the channel's primary outreach address (a single email string, or null).
The channel's full email archive (all_emails — a JSON object keyed by email address,
each value recording when and where the address was found) is not editable here;
sending it returns a 400. It is append-only and managed through an internal-only
process that never modifies or removes existing entries — not available via this CLI.
Profile notes. tl profiles update (superuser only) edits a brand/publisher profile's
superuser_notes — the internal free-text notes field on the customer record
(thoughtleaders_profile.superuser_notes, max 2500 chars). Send a string to set, null to
clear. This is currently the only editable profile field; anything else returns a 400 listing
the editable surface. Use it for account-handling notes meant for the internal team — it is
never shown to the customer.
Creating and vetting sponsorships
This is the end-to-end workflow for proposing a sponsorship, then moving it through the funnel as the two sides respond. Three create commands plus tl sponsorships update cover every state transition the CLI exposes.
Creating a sponsorship
tl sponsorships create creates the adlink in matched status by default. Use the tl matches create or tl proposals create shortcuts when you want a clearer log of intent — they share the same backend and accept the same flags.
tl sponsorships create --channel 5607 --brand 11459
tl sponsorships create --channel 5607 --brand 11459 --price 2500
tl sponsorships create -c 5607 -b 11459 -p 2500
tl sponsorships create '{"channel_id": 5607, "brand_id": 11459, "price": 2500}'
tl sponsorships create -c 5607 -b 11459 --json | jq -r '.results[0].sponsorship_id'
tl matches create -c 5607 -b 11459
tl proposals create -c 5607 -b 11459
Required: --channel/-c <int>, --brand/-b <int> (or the equivalent keys in the JSON body). Optional: --price/-p <float>, --json, --toon. JSON and command-line flags are mutually exclusive on tl sponsorships create — pass one form or the other, never both. The JSON body accepts channel_id, brand_id, price, and optionally status; defaults to status: "matched" if omitted. Returns the created adlink with a tl sponsorships show <id> hint.
The adlink is owned by the brand's advertiser profile (not the calling user's profile) and its list FK is set to the requested brand — so the new sponsorship appears under the brand's pipeline, not the AM's.
Vetting (state transitions via tl sponsorships update)
After a sponsorship exists, tl sponsorships update <id> '<json>' is the single CLI lever for moving it through the funnel. The interesting transitions for vetting are below — pass publish_status as a string label (the integer code also works but the label is clearer for both humans and the audit log).
An open sponsorship in negotiation progresses through three independent per-party approval fields: brand_approval_status, channel_approval_status, and agency_approval_status, each accepting pending / approved / finished (or null). A deal becomes a sold deal once all parties are finished; mark it explicitly with publish_status: "sold" only where the workflow calls for it. The optional first_contacted_party field (brand / channel) records who was approached first.
| Action | Command | What it means |
|---|
| Open for negotiation | tl sponsorships update <id> '{"publish_status": "open"}' | Moves a matched adlink to open — it's now actively being worked by both sides. |
| Brand agrees | tl sponsorships update <id> '{"brand_approval_status": "approved"}' | Records brand-side approval on an open deal (this is what makes a deal committed). |
| Channel agrees | tl sponsorships update <id> '{"channel_approval_status": "approved"}' | Records channel-side approval on an open deal. |
| Mark sold (deal finalised) | tl sponsorships update <id> '{"publish_status": "sold"}' | Final commercial step. Sets purchase semantics server-side. |
| Reject — Advertiser side | tl sponsorships update <id> '{"publish_status": "advertiser_reject"}' | Maps to DENY ("Rejected by Advertiser"). Use when the brand turns the offer down. |
| Reject — Publisher side | tl sponsorships update <id> '{"publish_status": "publisher_reject"}' | Maps to REJECT ("Rejected by Publisher"). Use when the channel turns the offer down. |
| Reject — Agency | tl sponsorships update <id> '{"publish_status": "agency_reject"}' | When an agency intermediary kills the deal. |
Choosing the right rejection label — match the label to the side actually rejecting:
- The CLI caller is acting for / on behalf of an Advertiser (Brand) → use
advertiser_reject.
- The CLI caller is acting for / on behalf of a Publisher (Channel) → use
publisher_reject.
- If you don't know which side, ask before running the update — the two labels are not interchangeable downstream (they drive different reporting and different KPIs).
rejection_reason is mandatory whenever you set publish_status to any rejection label (advertiser_reject, publisher_reject, or agency_reject). Do not issue a rejection update without it — a rejection with no reason is treated as an incomplete record by downstream reporting and AM workflows. If the user hasn't given you a reason, ask before running the update. Add rejection_reason_details whenever the user gives you more context — it's free-form supporting text and is fine to omit when the short rejection_reason is self-evident.
tl sponsorships update 98765 '{
"publish_status": "advertiser_reject",
"rejection_reason": "off-brand audience",
"rejection_reason_details": "Brand wants 18-34 male; channel skews 35-54 female"
}'
Full set of publish_status labels the CLI accepts: sold, advertiser_reject, publisher_reject, matched, agency_reject, open. Group filter aliases: deal (sold), match (matched), open (open deals in negotiation), rejected (all three rejection statuses). Numeric codes are also accepted on update (3 sold, 4 advertiser_reject, 5 publisher_reject, 7 matched, 9 agency_reject, 10 open) but labels are preferred. Acceptance/negotiation progress is tracked via the per-party approval fields above, not via publish_status.
Worked example: propose → reject
sid=$(tl sponsorships create -c 5607 -b 11459 -p 2500 --json | jq -r '.results[0].sponsorship_id')
tl sponsorships update "$sid" '{
"publish_status": "advertiser_reject",
"rejection_reason": "budget cut for Q3"
}'
tl sponsorships show "$sid" --json | jq '{id, status, rejection_reason}'
Raw queries (tl db)
tl db pg|fb|es is the default tool. Use it to reach any database records needed.
tl db pg "<SELECT ...>"
tl db fb "<SELECT ...>"
tl db es "<JSON body>"
All three honour --json/--csv/--md/--toon and accept - to read from stdin (cat q.sql | tl db fb -). They share the list-curve at mult=1.4 (raw queries, no role scoping, wider blast radius).
Reasons to write a raw query (the common case):
- Aggregations (counts, sums, avgs, group-bys, percentiles, time histograms) — one
tl db pg GROUP BY or tl db es agg body, not a paginated walk + Python reduce.
- Joins / cross-table data —
tl db pg returns brand+channel+deal in one row instead of two structured walks stitched in jq.
- Multi-condition filtering — compound boolean,
NOT IN/EXISTS, WHERE col IS NULL on hidden fields, mixed range + enum + text predicates: write the SQL/ES body, don't over-fetch and post-filter.
- Fields the structured commands don't expose — e.g.
media_selling_network_join_date (only the msn boolean is surfaced), weighted_price, tx_data, raw publish_status integer, etc.
Structured commands are still the right tool for: single-record show by ID, saved tl reports run, and tl snapshots channel|video (these wrap interpolation logic you'd otherwise reimplement). Anything that would have been a "filtered list" goes through tl db pg|fb|es.
| Need | Use |
|---|
| Aggregations (counts, sums, group-by, histograms, percentiles) | tl db pg GROUP BY or tl db es agg query |
| Joins / cross-table data | tl db pg |
| Multi-condition filtering the structured filters can't express | tl db pg / tl db es |
Fields the structured commands don't expose (raw publish_status, weighted_price, media_selling_network_join_date, etc.) | tl db pg |
| Transcript / brand-mention search inside video content | tl db es (no structured equivalent for content text) |
| Custom Firebolt shape (milestone-age slices, multi-channel growth comparisons) | tl db fb |
| Single-record detail lookup by ID | tl <resource> show <id> |
| Channel/brand similarity (server-implemented similarity search) | tl channels similar, tl brands similar, tl recommender ... |
| Saved reports | tl reports, tl reports run |
| Time-series view-curve / channel growth (default shape with interpolation) | tl snapshots channel, tl snapshots video |
tl db es — Elasticsearch
The CLI sends your JSON body to the server, which validates it before forwarding to ES. The index is fixed server-side (defaults to tl-platform); the client cannot select it. To narrow to a quarter or year, scope inside the body with a publication_date range filter rather than picking a different alias.
tl db es '{"size":1,"query":{"term":{"id":"1247603:8LskGvKUA9I"}}}'
tl db es '{"size":0,"track_total_hits":true,"query":{"term":{"sponsored_brand_mentions":"5612"}}}'
cat query.json | tl db es -
See references/elasticsearch-schema.md for accepted top-level keys, query types, size/depth limits, scripting/aggregation rules, and the field catalogue.
Article docs in ES carry only channel.id — not a usable channel name. Resolve names from PG, in a two-step script. Whenever you query article/upload docs and the output needs channel names, do NOT hand-map ids in context and do NOT ILIKE on names — write a script that:
- runs
tl db es … --json with channel.id in _source, then collects the distinct channel ids;
- runs
tl db pg "SELECT id, channel_name FROM thoughtleaders_channel WHERE id IN (<ids>)" --json to build an {id: channel_name} map;
- merges the map onto the ES rows by
channel.id and emits the enriched result.
Prefer Python for the script (write it to /tmp); a jq+xargs one-liner is fine for a single page (worked example under Brand sponsorship history). Always go ES→PG in this order (PG IN (...) on the ids ES returned) — one PG round-trip for the whole page, never one query per article.
tl db fb — Firebolt
tl db fb "SELECT age, view_count, like_count FROM article_metrics
WHERE channel_id = 12345 AND id = 'dQw4w9WgXcQ'
ORDER BY age"
tl db fb "SELECT scrape_date, total_views, subscribers FROM channel_metrics
WHERE id = 12345
ORDER BY scrape_date"
See references/firebolt-schema.md for accepted-query rules (SELECT-only, single-table, indexed-filter requirements), table schemas, and ID-format details.
tl db pg — PostgreSQL
tl db pg "SELECT b.name, COUNT(*) AS deals
FROM thoughtleaders_adlink a
JOIN thoughtleaders_profile p ON a.advertiser_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 a.publish_status = 3
GROUP BY b.name
ORDER BY deals DESC
LIMIT 20 OFFSET 0"
PostgreSQL table hints
- If the user is working with channels, use the
tl schema pg thoughtleaders_channel before querying to get the channels table structure
- If with brands, use the
tl schema pg thoughtleaders_brand command before querying to get the brands table structure
- If with comments, use the
tl schema pg thoughtleaders_comment command before querying to get the brands table structure
- If with sponsorships, use the
tl schema pg thoughtleaders_adlink command before querying to get the brands table structure
If unsure about what information to find where, read the references/postgresql-schema.md file for instructions. Use just tl pg schema to see the entire SQL schema.
PG cost is per-row. The credit cost for a tl db pg call is its per-row rate — the sum of the rates of the tables the query touches (default 1.0/row; some tables are cheaper or dearer) plus a flat per-row charge for every expensive column read — times the rows returned. So a join pays for each table it reads, and an expensive column costs its configured value for every row returned. Aggregate queries (count/GROUP BY) add a surcharge proportional to the estimated rows aggregated. Sensitive columns (e.g. demographics, channel outreach emails) cost more per row. Run tl describe show db --json to see the live pg_pricing map, and check usage.credit_rate / usage.pricing in the response envelope after a query to see what your query was actually charged.
Preview cost before running. Add --pricing to estimate a query's cost without executing it: tl db pg "SELECT … LIMIT 100" --pricing runs only EXPLAIN, prints the per-row rate + per-row breakdown and an upper-bound cost (at the query's LIMIT), and costs a flat 1 credit. Use this before large or expensive-column queries. Works with --json. --pricing also works on tl db fb and tl db es — those backends have no per-table or per-column charges, so the estimate is just the flat per-row rate at the row ceiling (LIMIT for Firebolt; size, or the aggregation doc cap, for Elasticsearch).
Three sources, each authoritative for different things
- Postgres — deals, pipeline, brands, channels, users, organizations, profiles, revenue. Source of truth for deal state. Reachable via the structured
tl commands or raw tl db pg.
- Elasticsearch — videos, transcripts, brand mentions, current channel/video metrics, demographics. Reachable via
tl db es.
- Firebolt — historical time-series snapshots only (view curves over time, subscriber-growth trends). Reachable via
tl snapshots (preferred) or tl db fb.
Use Firebolt only when you need a value AT A POINT IN TIME that no longer exists in the current ES/PG snapshot. For "current views/subs", use ES.
Join keys across sources (you'll be doing the join in jq/Python, not in SQL):
Postgres channel.id ↔ ES channel.id (on article docs) ↔ Firebolt article_metrics.channel_id / channel_metrics.idPostgres adlink.article_id is <channel_id>:<youtube_id> — same as ES _id. Strip the prefix to get Firebolt article_metrics.id.Postgres brand.id ↔ ES sponsored_brand_mentions[] / organic_brand_mentions[].publication_id is deprecated — don't use it.
Snapshots are sparse, especially for older videos. Don't assume two arbitrary dates have data points. For approximations, prefer tl snapshots which already implements the project's interpolation logic; falling back to raw tl db fb means you handle gaps yourself.
Limitations of the tl-only data path
| Capability | Status | Workaround |
|---|
Arbitrary read-only SELECT on Postgres | Available via tl db pg. | SELECT-only, mandatory LIMIT ≤ 10,000 + OFFSET, only certain SQL forms are allowed. See references/postgres-schema.md. |
| Cross-reference helpers ("channels proposed to brand X", "channels sponsored by MBN brands in last N days") | Available via tl db pg. | Write the join: thoughtleaders_adlink ↔ adspot ↔ channel ↔ profile ↔ profile_brands ↔ brand. Filter by publish_status for proposed/sold and by date range as needed. See references/postgres-schema.md for the exact column names. |
AdLink INSERT with custom price/cost/owner/weighted_price/created_where | Unavailable — tl sponsorships create exists but only creates a proposal between a channel and a brand. The tl db pg sanitizer accepts SELECT only — no INSERT/UPDATE. | Done in the app or by a human with DB access. |
Pre-insert validation queries (joining adspot ↔ channel ↔ profile ↔ org to confirm MSN, integration=1, persona, plan) | Available via tl db pg. | One SELECT joining the four tables. Use thoughtleaders_channel.media_selling_network_join_date IS NOT NULL for MSN, thoughtleaders_adspot.integration = 1 for mention adspots, thoughtleaders_profile.persona for the persona code (see persona constants in references/postgres-schema.md). |
| Firebolt cross-table or join queries; filtering on non-indexed columns in WHERE | Unavailable — not accepted. | Fetch a wider slice keyed on channel_id (and optionally id), filter the rest in jq/Python. |
ES query_string, regexp, wildcard, fuzzy, more_like_this, parent/child joins; scripting keys (names that start with script or end with _script); multiple aggregations in one body | Unavailable — not accepted. | Rewrite using term/terms/match/bool/nested. For multi-agg dashboards, run multiple tl db es calls and combine client-side. For "similar"-style queries, try tl channels similar / tl brands similar (server-implemented similarity search). |
ES deep pagination beyond from+size = 10,000 | Available via search_after (stateless cursor); scroll and pit remain unavailable. | Sort with a unique tiebreaker (e.g. id), then pass the response envelope's next_search_after back as search_after in the next call, keeping query/sort identical and from at 0. See the ES reference's Deep pagination section. |
ES index introspection (_cat/indices, mappings) | Unavailable — only _search is wired. | Read references/elasticsearch-schema.md. It's manually maintained — update it when you discover new fields. |
Schema introspection on Postgres (information_schema.columns, pg_class, …) | Partial — catalog-resolving casts and many pg_* helpers are blocked. | Use tl schema pg for the live table/column listing, or read references/postgres-schema.md. |
If a user asks for one of the Unavailable items, say so explicitly and propose the closest tl-based approximation rather than silently degrading.
If the user requests a chart, create it as a SVG graphic.
Discovery & system
tl describe
tl describe show <resource> --json
tl schema pg
tl schema pg <table>
tl schema fb
tl schema fb <table>
tl schema es
tl balance --json
tl credits pricing
tl credits buy --amount-usd 10
tl credits history
tl whoami
tl auth status
tl changelog
tl changelog v0.4.17 v0.4.18
tl changelog since v0.4.10
tl changelog --md > CHANGELOG.md
Distributed skills — beyond the bundled set
An organization can be granted additional skills that aren't part of a CLI release. tl skill fetches and installs them on demand into the same directories tl setup uses (Claude Code's standalone skills directory, OpenCode's skills directory, and the directory shared by Gemini and Codex), so every supported agent picks them up automatically — no restart or reconfiguration needed beyond re-reading the skill listing.
tl skill list
tl skill list --all
tl skill download <name>
tl skill download <name> --force
tl skill update
tl skill remove <name>
If a user asks to find, add, or refresh a skill by capability ("is there a skill for X", "get me the latest Y skill"), check tl skill list before saying no — the bundled set isn't the whole catalog. tl skill download never overwrites a directory it doesn't already manage unless --force is passed, so it's safe to run without clobbering a manually-installed skill of the same name. tl warns once a day, on any command, when a downloaded skill has a newer version available — surface that nag to the user rather than silently ignoring it, and offer to run tl skill update.
Channel & video discovery — pick the path for the question shape
Four first-class paths, each with a different signal. Pick by the SHAPE of the user's question, not by habit. "Recommender first" is the right default only for path 2 — for paths 1, 3, and 4 the recommender is the wrong tool.
Path 1. Named entity — user named a specific channel, brand, or YouTube URL/handle/ID ("MrBeast", "NordVPN", "@mkbhd", "youtu.be/..."). Use tl channels find / tl brands find — single-step resolver returning {id, name}. Cheap, deterministic, no expansion.
tl channels find "MrBeast"
tl brands find "NordVPN"
tl channels find resolves spacing/typo variants on its own ("Deco Destiny" → "DecoDestiny") via YouTube lookups and fuzzy similarity matching — no need to retry with hand-made name variations. A real channel that isn't in the index yet gets queued for analysis automatically (the response says to check back in ~24 hours). A plain "Not found" means even YouTube couldn't find it — treat that as the answer.
Path 2. Curated tag / category / demographic — user named a topic that maps cleanly to a recommender tag ("Cooking", "Tech", "USA share", content categories, format hints). Use the recommender — it ranks channels by how strongly they load on a tag, returning ranked similarity scores instead of forcing exact equality. It also returns matching brand profiles alongside the channels — useful when the user wants to know "who buys this kind of inventory."
tl recommender tags cooking
tl recommender tags crypto
tl recommender top-channels "Cooking" msn:yes --limit 50
tl recommender top-channels "Tech" --limit 30
tl recommender top-brands "USA share" mbn:yes --limit 50
Available filters on the recommender commands:
| Command | Filters |
|---|
top-channels | msn:<yes|no|all> (default all), exclude-for-profile:<id> |
top-profiles | mbn:<yes|no|all> (default all), exclude-for-channel:<id> |
top-brands | mbn:<yes|no|all> (default all) |
channels-for-profile | language:<iso> (default en), msn:<yes|no> (default no) |
channels-for-brand | same as channels-for-profile |
brands-for-channel | mbn:<yes|no|all> (default all) |
Use tl recommender top for category/topic discovery (it's ranked) and tl channels similar / tl brands similar for 1:1 lookalike searches. This is the fast path.
Hand-off to path 3 when the tag doesn't fit If tl recommender tags <hint> returns no clean match, or the user's intent cannot be represented by recommender tags — drop to path 3, do NOT fake-fit a loose adjacent tag. E.g. "crypto/Web3 channels" is a miss even though "cryptocurrency" exists as a tag — "cryptocurrency" is a financial-product tag, not the cultural-niche the user named. Same for "speedcubing", "biohacking and longevity", "AI cooking" — none of these are curated tags, so they belong in path 3.
Also fall through to path 3 — NOT path 4 — when the recommender returns errors. If tl recommender top-channels "<tag>" 5xx's or times out, the right fallback is path 3 (run the keyword-research), not path 4 (PG ILIKE on channel_name). PG name-matching misses every channel whose name doesn't contain the literal word — that's the same anti-pattern called out at the bottom of this section.
Also fall through to path 3 if the user wants to broaden the search. When encountering further inputs like "broaden the search", "find more results", etc., it indicates the user is searching for topics beyond what the recommender tags provide.
Path 3. Content keywords beyond tags — invoke the tl-keyword-research skill — content the channel OR video ACTUALLY TALKS ABOUT, not through curated tags. Triggers:
- Channel search by topic —
"crypto/Web3 channels", "speedcubing channels", "channels about biohacking and longevity", "both 3D printing and miniature painting".
- Video search by topic —
"videos where creators discuss budget meal prep", "uploads about [topic]", "find videos|channels that talk about X".
- Channel–brand fit check — does this candidate channel's content actually touch the brand's category? (Use with
channel.id filter on the downstream ES query.)
- Validating a recommender / SQL shortlist — sample-check that the top-N channels really cover the niche.
Do NOT compose keyword sets by hand for tl db es. Always invoke the whole skill. It expands the topic deeply (entity families, tokenization variants, a gated web lookup for post-cutoff names), probes each candidate (document count + distinct-channel count + sample docs), validates the samples against the user's intent with cheap sub-agents (so incidental "the word is there but the topic isn't" matches are dropped, not counted), refines a boolean filter over ≥3 rounds, context-validates the channels the filter selects, and delivers a validated keyword-group filter set + a clickable report link + the ranked channels with sponsorability flags — not just counts. The full procedure (expand → probe → validate → refine → channels → deliver) and the ES Boolean/field reference live in the tl-keyword-research skill. Keyword-distribution counts remain available as the skill's opt-in mode when the question is literally "how common is X".
Path 4. Pure attribute filter — user wants channels filtered by metadata like: is_tpp, language, demographic_device_primary, country share in demographic_geo JSON, aggregations, joins. Use tl db pg with a SELECT on thoughtleaders_channel. Run tl schema pg thoughtleaders_channel once to confirm the live column set; the columns in the examples are stable.
tl db pg "SELECT id, channel_name, content_category, total_views
FROM thoughtleaders_channel
WHERE is_tpp = TRUE
ORDER BY total_views DESC
LIMIT 200 OFFSET 0"
tl db pg "SELECT id, channel_name, demographic_device_primary, total_views
FROM thoughtleaders_channel
WHERE is_tpp = FALSE
AND demographic_device_primary = 'mobile'
ORDER BY total_views DESC
LIMIT 100 OFFSET 0"
For per-country share beyond the recommender's "USA share" tag, use the demographic_geo JSONB field in raw SQL: (demographic_geo->>'gb')::int >= 25. Same pattern with demographic_device->>'mobile' for non-primary device shares.
MSN status (media_selling_network_join_date) is scrubbed from the advertiser sandbox view. Raw SQL can't filter on it from an advertiser context. For MSN-only / non-MSN lookups, run the same raw SQL with media_selling_network_join_date IS [NOT] NULL from a context that has access to it (full-access role), or rely on the recommender's MSN-aware filters: tl recommender top-channels "<tag>" msn:yes|no|all.
Anti-pattern: defaulting to ILIKE on channel_name for off-tag topic queries. If the question is "channels about X" where X is a topic / concept / niche (not a literal substring you expect in channel names), reach for path 3 (tl-keyword-research), not WHERE channel_name ILIKE '%X%'. Channel-name ILIKE misses channels whose name doesn't literally contain X but whose content does; the keyword-research skill catches them via title / summary / transcript. Use channel_name ILIKE only when you actually expect the channel's name to contain the term (e.g. "Crypto" in "My Happy Crypto") as a supplementary signal alongside path 3, not as a replacement for it. And for a named entity — a specific creator or channel — don't start with ILIKE at all: run tl channels find "<name>" first (path 1). Fall back to ILIKE name variations only if the resolver finds nothing, and treat a clean "Not found" from the resolver as the likely answer (the channel probably isn't in the index) rather than a cue for ever-broader scans.
Output flags
--json — structured JSON output format (use this for parsing)--toon — TOON output format (efficient for large data sets while keeping metadata)--csv — CSV output format--md — Markdown table for user presentation only--limit N — max results--offset N — pagination
Response shape
Successful --json responses wrap data in an envelope:
{
"results": [ { "...": "..." } ],
"total": 42,
"usage": { "credits_charged": 2, "balance_remaining": 9998 },
"_breadcrumbs": [ { "hint": "...", "command": "tl ..." } ]
}
Errors return {"detail": "..."} with an HTTP status (400 / 401 / 403 / 404).
While analysing results, you must always examine the results field in the JSON.
Credit Awareness
Every query costs credits. Before running expensive queries:
- Check the credit rate:
tl describe show <resource> --json | jq '.credits' and the user balance.
- Multi-row endpoints (snapshots, comments, reports,
tl db pg|fb|es) are priced non-linearly: cost = 1 + mult × 0.126 × n^1.2, where mult is the per-resource complexity factor (1.0 for cheap reads, 1.2 for snapshots, 1.3 for reports, 1.4 for raw db). Detail/history/similar endpoints are linear (rate × results).
- Estimate cost from the formula or the table; for non-row-priced endpoints use
results × rate.
- If estimated cost is more than 10% of the remaining balance, ask the user to confirm the operation before running.
Data Scoping
Users only see data their plan allows:
- Media buyers see deals where their org is the brand. They see
price but never cost.
- Media sellers see deals where their org is the publisher. They see
cost but never price.
- Intelligence plan required for
tl brands, the full tl recommender surface, and tl db es access to full transcript / brand-mention data.
- Paid plan required for
tl snapshots.
Important: Firebolt Snapshots
tl snapshots video always requires --channel. Without it, the query scans 7.4 billion rows and times out. Always provide the channel ID.
Examples
"Show me my sold sponsorships this quarter":
tl db pg "SELECT al.id, al.weighted_price, al.purchase_date, b.name AS brand
FROM thoughtleaders_adlink al
JOIN thoughtleaders_profile p ON p.id = al.advertiser_profile_id
JOIN thoughtleaders_profile_brands pb ON pb.profile_id = p.id
JOIN thoughtleaders_brand b ON b.id = pb.brand_id
WHERE al.publish_status = 3
AND al.purchase_date >= '2026-01-01'
ORDER BY al.purchase_date DESC
LIMIT 10000 OFFSET 0" --json
Brand sponsorship history — what channels does Nike sponsor?
Resolve the brand to an ID, then probe ES for articles where the brand appears in sponsored_brand_mentions. Channel names live in PG (the ES article doc only carries channel.id), so the third call joins them in.
tl brands find Nike --json
tl db es '{
"size": 50,
"track_total_hits": true,
"query": {"bool": {"filter": [
{"term": {"doc_type": "article"}},
{"term": {"sponsored_brand_mentions": "21416"}}
]}},
"sort": [{"publication_date": "desc"}],
"_source": ["title", "channel.id", "publication_date", "views"]
}' --json > /tmp/nike_history.json
jq -r '[.results[].channel.id] | unique | map(tostring) | join(",")' /tmp/nike_history.json \
| xargs -I CH_IDS tl db pg "SELECT id, channel_name FROM thoughtleaders_channel WHERE id IN (CH_IDS)" --json
tl db es '{
"size": 50,
"track_total_hits": true,
"query": {"bool": {"filter": [
{"term": {"doc_type": "article"}},
{"term": {"sponsored_brand_mentions": "21416"}},
{"term": {"channel.id": 5607}}
]}},
"sort": [{"publication_date": "desc"}],
"_source": ["title", "publication_date", "views"]
}'
tl db pg "SELECT article_id FROM thoughtleaders_adlink
WHERE article_id IN ('1247603:8LskGvKUA9I', '1247603:abc123')"
Brand sponsorship roll-up — totals, first/last seen, top channels, by-year
The same ES filter (doc_type=article + sponsored_brand_mentions=<id>) with size:0 + aggregations replaces a roll-up call. ES accepts one aggregation total per request (top-level + sub-aggs all count), so what would be a single server-side roll-up here splits into a few tl db es calls and one client-side join.
tl db es '{
"size": 0,
"track_total_hits": true,
"query": {"bool": {"filter": [
{"term": {"doc_type": "article"}},
{"term": {"sponsored_brand_mentions": "21416"}}
]}},
"aggs": {
"views_sum": {"sum": {"field": "views"}},
"views_avg": {"avg": {"field": "views"}},
"first_seen": {"min": {"field": "publication_date"}},
"last_seen": {"max": {"field": "publication_date"}}
}
}'
tl db es '{
"size": 0,
"query": {"bool": {"filter": [
{"term": {"doc_type": "article"}},
{"term": {"sponsored_brand_mentions": "21416"}}
]}},
"aggs": {
"by_year": {"date_histogram": {
"field": "publication_date", "calendar_interval": "year",
"format": "yyyy", "min_doc_count": 1
}}
}
}'
tl db es '{
"size": 0,
"query": {"bool": {"filter": [
{"term": {"doc_type": "article"}},
{"term": {"sponsored_brand_mentions": "21416"}}
]}},
"aggs": {
"by_channel": {"terms": {"field": "channel.id", "size": 10, "order": {"_count": "desc"}}}
}
}'
tl db pg "SELECT COUNT(*) AS tl_brokered
FROM thoughtleaders_adlink al
JOIN thoughtleaders_profile p ON p.id = al.advertiser_profile_id
JOIN thoughtleaders_profile_brands pb ON pb.profile_id = p.id
WHERE pb.brand_id = 21416 AND al.article_id IS NOT NULL"
"Compare view curves for two videos":
tl snapshots video abc123 --channel 456 --json
tl snapshots video def789 --channel 456 --json
"Run my Q1 pipeline report":
tl reports --json
tl reports run 42 --json
"Look up a channel or brand from whatever the user pasted":
tl channels find "https://www.youtube.com/@MrBeast"
tl channels find "https://www.youtube.com/watch?v=dQw4w9WgXcQ"
tl channels find UCX6OQ3DkcsbYNE6H8uQQuVA
tl brands find nike.com
tl brands find "Just Do It"
"Find Cooking channels with US-heavy mobile audiences":
tl recommender top-channels "Cooking" msn:yes --limit 100 --json \
| jq -r '.results[].channel_id' \
| paste -sd, - \
| xargs -I {} tl db pg "SELECT id, channel_name, total_views, demographic_usa_share
FROM thoughtleaders_channel
WHERE id IN ({})
AND demographic_device_primary = 'mobile'
AND demographic_usa_share >= 50
ORDER BY total_views DESC
LIMIT 50 OFFSET 0" --json
"Show sold sponsorships targeting mobile US audiences":
tl db pg "SELECT al.id, c.channel_name, c.demographic_device_primary, c.demographic_usa_share, al.weighted_price
FROM thoughtleaders_adlink al
JOIN thoughtleaders_adspot s ON s.id = al.ad_spot_id
JOIN thoughtleaders_channel c ON c.id = s.channel_id
WHERE al.publish_status = 3
AND c.demographic_device_primary = 'mobile'
AND c.demographic_usa_share >= 60
LIMIT 10000 OFFSET 0" --json
"Find channels similar to one I know" (similarity recommender):
tl channels similar 29834 --limit 10
tl channels similar "Tremending girls" --limit 5
tl channels similar 29834 min-score:0.85 --limit 20
tl channels similar 29834 msn:both min-score:0.4 --limit 30
tl channels similar 29834 msn:no --limit 30
tl channels similar 29834 tpp:yes --limit 30
tl channels similar 29834 min-subs:1000000 exclude:477487 --limit 15
Both tl channels show and tl channels similar accept either a numeric channel ID or a channel name. Name arguments are case-insensitive partial matches; if more than one active channel matches, the command prints a candidates table (channel_id, subscribers, name) and exits 1 so you can retry with a specific ID. The msn filter on similar is tri-state: yes (only MSN channels — the default), no (only non-MSN channels), both (no MSN filter). tl channels look-alike is a hidden alias for similar that matches the internal "look-alike channels" terminology. tl channels show returns a tl_url field — the canonical ThoughtLeaders web-app analysis page for the channel; use it verbatim when linking a user to the channel instead of constructing a URL by hand.
"Browse the recommender" (categories, demographics, formats):
tl recommender tags
tl recommender tags cooking
tl recommender top-channels "Cooking" msn:yes --limit 50
tl recommender top-profiles "Cooking" --limit 30
tl recommender top-brands "USA share" mbn:yes --limit 30
tl recommender top-channels "Tech" exclude-for-profile:842
tl recommender inspect-channel 29834
tl recommender inspect-brand Nike
tl recommender channels-for-profile 842 --limit 30
tl recommender channels-for-profile 842 msn:yes language:en
tl recommender channels-for-brand Nike --limit 30
tl recommender channels-for-brand 6037 msn:yes language:en --limit 30
tl recommender brands-for-channel 29834 --limit 30
tl recommender brands-for-channel "MrBeast" mbn:yes --limit 30
