# Competitor Report > Generate a full client-grade Competitor Intelligence Brief for a single competitor company. The window is configurable - the last completed calendar quarter (default) or the trailing 12 months. Produces an A4 HTML and optional PDF in a McKinsey-style consulting layout. The brief covers org reshape, hires, leavers, open job postings, customer acquisition motion by industry, market sentiment, GitHub footprint, vendor-trust events, an evidence wall of verbatim third-party quotes, and a Company Reference Card. Use this skill whenever the user asks to "build a competitor brief", "competitive intelligence on X", "deep dive on competitor X", "Nexagon-style report on X", "scan competitor X for the last quarter", "12-month view of competitor Y", "what's happening at competitor Y", or any phrasing that mixes a vendor name with a request for an analytical multi-section report on their org and market posture. - Skill: `onfire-ai/competitor-report` (Agent Skill, multi-file: 5 files) - Install (CLI): `npx skillmds@latest add onfire-ai/competitor-report` - Raw SKILL.md: https://api.skillmd.com/api/skills/onfire-ai/competitor-report/raw - Safety review: pending - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Web & Frontend - Author: onfire-ai (https://skillmd.com/u/onfire-ai) - Updated: 2026-09-21 - Page: https://skillmd.com/skills/onfire-ai/competitor-report --- # Competitor Intelligence Brief ## What this skill does Given a **competitor company name** (e.g. `Nexagon`, `Codeshield`, `Artifex`), this skill produces a **13-page A4 PDF analytical brief** by default. The page count can flex to 14 when Complication 1B is split into a geographic page (page 5) and a departmental-change page (new page 6) - opt in to the split when the joiner book is rich enough to warrant a function-bar visualisation and the internal-promotion list deserves its own card. Briefs without vendor-trust events run 12 pages (Complication 3 is omitted). The deliverable is internal-facing (prepared FOR the caller's tenant, ABOUT a non-customer vendor) and written in a consulting-firm idiom - Pyramid Principle, SCR (Situation / Complication / Resolution) narrative arc, McKinsey-style action titles. The brief is **analytical only**. No GTM plays, no recommendations, no revenue estimates. The reader synthesises the implications themselves. ### Editorial policy: how the tenant appears The brief is prepared for the **requesting tenant** (the company who ran the report) and is about the **target competitor**. They are treated asymmetrically in the deliverable: | Subject | Appears named | Does not appear named | |---|---|---| | **Target competitor** | Cover, Exhibit A, action titles, findings, all analytical sections. The target is the subject. | The evidence wall — verbatim quotes from target employees are excluded (a competitor founder's rebuttal of a public critique is still a target voice). | | **Prepared-for tenant** | Internal-only file metadata. The default cover line reads "Prepared for the requesting tenant" - explicitly opt in via `brand_cover: true` to name them. | Anywhere in the customer-facing copy. If a tenant CEO or employee publicly engaged with the target in-window, describe the event by category descriptor ("a category-incumbent CEO", "a public artifact-management vendor") - never by name. | The rule formalises that the artefact stays distributable even if it ends up outside the requesting tenant. The target is the subject; the tenant is the reader. --- ## Inputs | Input | Required | Example | Default | |-------|----------|---------|---------| | `competitor_name` | Yes | `Nexagon` | - | | `company_linkedin_url` | Optional | `linkedin.com/company/nexagon` | resolved via `match_company` | | `window_mode` | Optional | `quarter` or `12_month` | `quarter` | | `quarter` | Optional | `Q1 2026` | the last fully completed calendar quarter | | `prepared_for_tenant` | Optional | `artifex` | from `get_current_tenant` | | `brand_cover` | Optional | `true` | `false` - cover line reads "Prepared for the requesting tenant" by default; opt in to name the tenant on the cover | If `quarter` is not supplied, the skill computes it from today's date. Today is in Q2 2026 → last completed quarter is Q1 2026 (Jan 1 - Mar 31). ### Window modes | Mode | Window | Use when | |---|---|---| | `quarter` (default) | The single calendar quarter chosen (e.g. Q1 2026 = Jan 1 - Mar 31). Acquisition motion still uses a trailing 12-month series for context but every other section is bound to the quarter. | Quarterly tracking brief; what changed in the last 90 days. | | `12_month` | The trailing 12 months ending at the last completed calendar quarter (e.g. Apr 1, 2025 - Mar 31, 2026). Every section - org reshape, hires, leavers, sentiment, vendor-trust - is bound to the full window. | Annual review brief; deeper analytical read; capturing a leadership-rebuild story that a single quarter would miss. | The data slices are largely the same shape under either window - the difference is the time predicate and the headline language. Switching mode propagates to every page footer, the running headers, the Assumptions block, and all action titles ("Q1 hiring tilt" vs "12-month hiring tilt"). --- ## The 6 phases The skill works through **six phases in strict order**. Each phase has mandatory inputs from the previous phase. Do not skip ahead; the analysis phase depends on every data slice being persisted as a dataset. ``` Phase 0 Scope and identity (~1 minute) Phase 1 Data gathering (~5-8 minutes) Phase 2 Analysis (~3-5 minutes) Phase 3 Report assembly (~3-5 minutes) Phase 3.5 Self-validation pass (~3-5 minutes) — MANDATORY Phase 4 Pre-delivery checklist (~1 minute) ``` **Phase 3.5 is non-negotiable.** Every brief that has shipped without this pass has contained at least one self-contradiction a careful reader catches in five minutes. Triple-check every number, every percentage, every cross-page reference. The brief is one argument across all pages — if any two pages disagree on the same fact, the brief is wrong. --- ## Phase 0 - Scope and identity ### 0.1 Compute the window In bash: ```bash TODAY=$(date +%Y-%m-%d) # Compute last completed full quarter from today. # Example: today 2026-05-24 → Q1 2026 = 2026-01-01 .. 2026-03-31 ``` Persist these values for the rest of the run: | Value | `quarter` mode | `12_month` mode | |---|---|---| | `window_start` | First day of the chosen quarter (e.g. `2026-01-01`) | `q_end - 12 months` (e.g. `2025-04-01`) | | `window_end` | Last day of the chosen quarter (e.g. `2026-03-31`) | Last day of the most recent completed quarter (e.g. `2026-03-31`) | | `window_label` | `Q1 2026` | `12 months ending Mar 2026` | | `q_start` / `q_end` | Same as `window_start` / `window_end` | Used only for the recent-quarter bracket in the acquisition chart | The customer-acquisition motion **always** uses a trailing 12-month series ending at `q_end`. The other sections honour `window_mode`. ### 0.2 Resolve the competitor identity ``` Onfire MCP: match_company( name="", telemetry={intent: "Competitor intelligence brief for "} ) ``` Confirm the verified LinkedIn URL with the user before proceeding. **Store** the firmographic block returned by `match_company`: | Field | Used for | |---|---| | `linkedin_url` | every subsequent query (lowercased) | | `name`, `display_name` | brief cover + exhibit + running headers | | `hq`, `country`, `founded_year` | Exhibit A reference card | | `revenue_band`, `ownership`, `last_funding_round` | Exhibit A timeline | | `employee_count`, `size_band` | Exhibit A + headline framing | | `industry`, `description` | Exhibit A positioning paragraph | | `subsidiary_linkedin_urls[]` | fold into headcount aggregates (don't double-count) | ### 0.3 Resolve the "prepared for" tenant ``` Onfire MCP: get_current_tenant(telemetry={intent: "..."}) ``` The cover line by default reads **"Prepared for the requesting tenant"**. The tenant's brand name only lands on the cover when the caller passes `brand_cover: true`. Resolve the tenant's `company_linkedin_url` via `match_company` and store it (lowercased) as `{tenant_linkedin_url}`. Two downstream exclusions use it: 1. Phase 1.10 - exclude biased sentiment authors from `ds_authors_resolved` 2. Phase 3 - scrub any analytical mention of the tenant in customer-facing copy; replace with the category descriptor (see editorial policy above) --- ## Phase 1 - Data gathering Each warehouse pull runs through **`ask_onfire`** — a structured **QueryIR** (`query={entity, select, filters, insight_filters, joins, order_by, distinct_by, limit, confirmed}`), **not** raw SQL. (The removed `query_onfire` raw-SQL tool no longer exists.) Each pull persists its result as a dataset; track the dataset IDs - the analysis phase joins them with `query_datasets`. See `references/snowflake-queries.md` for the per-slice QueryIR recipes and the table→entity map, and `account-research/references/ask-onfire-signals.md` for the worked patterns. **Important — billing + capability:** `ask_onfire` **bills 1 credit per row returned**, so set a small explicit `limit` on every call; an unset or over-threshold budget returns `needs_confirmation` (`stage: "row_budget"` — a free COUNT, nothing billed) so you can settle the count with the user and resubmit with `confirmed: true`. Several brief slices are full-cohort pulls feeding a downstream `query_datasets` analysis — expect to hit and deliberately clear the confirmation gate. Two capabilities the brief now uses (read snowflake-queries.md for the per-slice recipes): - **Aggregate mode in a QueryIR** — `group_by`, `aggregations` (`count` / `count_distinct` / `sum` / `avg` / `min` / `max`), date `buckets` (`month` / `quarter` / `year`, works on `'YYYY-MM'` TEXT), and `having`. In aggregate mode `select` MUST be empty; result columns are the `group_by` keys plus the aggregations, and it bills 1 credit per **group** — set a `limit`. This now expresses the per-distribution, per-month, distinct-count cuts the brief used to compute client-side: hires/leavers per month by title, modal-country, location distribution, open-posting / event distributions. - **Extended-pool entities** — the full employment-history pool (`experiences_pool`, ~5x `people_experiences`, one row per stint — the source for hires / leavers / title movement / roster) and the full people pool (`people_pool`, ~5x `contact`). Both are **gated** and have **no insight search**: query them with `allow_extended_pool: true` and the entity named explicitly. These supersede the old `PEOPLE_GRAND` / `PEOPLE_GRAND_EXPERIENCES` "tool-managed, no entity" tables. ask_onfire **still cannot** express window functions, self-joins (prior / next employer), first-ever-across-all-time cohort logic, true month-over-month deltas in SQL, multi-field free-text OR-scans, `PAYLOAD:` JSON, or multi-hop joins; for those slices (title-string net-new / now-gone + paired-swap classification, acquisition first-ever cohort, departed-no-backfill free-text scan, leaver destinations) pull the constrained rows with a QueryIR (aggregate-or-row, `allow_extended_pool: true` where the pool is needed), persist, and do the window / cohort / self-join logic client-side (see snowflake- queries.md for which is which). ### 1.1 Headcount trend + employee roster (single call — primary source for org-growth changes) See `references/snowflake-queries.md` → query 01. A single `get_company_headcount` call is the **primary source for all org-growth changes** in the brief — monthly headcount trend, joiners, leavers, joiner origins, and leaver destinations. It powers Phase 1.1 (headcount), Phase 1.3 (Q-hires), and Phase 1.12 (leavers + their destinations). No separate `ask_onfire` pull is needed for any of those. Headcount is derived from tenure intervals in the extended employment- history pool; the employee roster joins each tenure to the extended people pool for location and to **symmetric self-joins** on the employment-history pool for both the immediately-prior company (joiner origin) and the immediately-next company (leaver destination). Those pools ARE now queryable as the gated `experiences_pool` / `people_pool` entities (set `allow_extended_pool: true`), but `get_company_headcount` stays the canonical path **for this slice** because it bundles the monthly headcount, the roster, AND the prior / next self-joins (window functions a standalone QueryIR cannot express) into one call. Reach for the pool entities directly only for a cut the tool does not pre-compute — e.g. the aggregate-mode reshape counts in Phase 1.2. ``` Onfire MCP: get_company_headcount( company_linkedin_urls=["{company_linkedin_url}"], months=12, telemetry={intent: "Competitor brief headcount + joiners + leavers"} ) ``` The roster is always returned alongside the monthly counts — no opt-in flag needed. Each roster row covers joiners (`start_date IN window`), still-active (`end_date IS NULL`), **and** leavers (`end_date IN window` — incl. long-tenured departures whose start is outside the window). `prior_*` columns are on every row; `next_*` columns are populated for stints that ended. Persist the two datasets returned: | Dataset | From | Used by | |---|---|---| | `ds_headcount` | `response.headcount.dataset` | Complication 1B headcount % bars; exec-summary "Q net" callout | | `ds_employees` | `response.employees.dataset` | Phase 1.3 (joiners filter via `start_date`), Phase 1.12 (leavers + destinations via `end_date` + `next_*`), and the talent-flow strategic-thread synthesis (joiner origins via `prior_*`, leaver destinations via `next_*`) | Pull in-quarter rows from `ds_headcount` for the exec-summary "+X% Q1 net" callout. Each row carries `time_period` (first day of month), `employee_count`, and `growth_pct` (MoM %; NULL for the oldest row). Because joiners and leavers are derived from the same `ds_employees` source as the headcount counts, the joiner / leaver math reconciles with the MoM headcount delta within snapshot-lag tolerance — this is the canonical pattern; do not re-derive these slices from `people_experiences` via `ask_onfire`. ### 1.2 Title movement (window-bound) See `references/snowflake-queries.md` → query 02. Sourced from the gated `experiences_pool` entity (the whole-pool employment history; `allow_extended_pool: true`). Two complementary cuts: **Title-string classification (client-side).** Pull every stint at the company from `experiences_pool`, then classify every in-window event in `query_datasets`: - **Net-new title** - title string had no prior holder - **Now-gone title** - last remaining holder of an existing title departed The `NOT EXISTS` "no remaining active holder" guard and the paired-swap pairing are self-referential, so this stays client-side. Persists as `ds_title_movement`. **Strictly bounded to the window** - any event outside `window_start` / `window_end` is excluded. **Per-month reshape counts (aggregate mode).** The hires-per-month and leavers-per-month by `title_role` counts ARE now expressible directly: a date `bucket` on `start_date` (hires) / `end_date` (leavers), grouped with `title_role`, `count_distinct` of `person_url`. Run those as aggregate-mode `experiences_pool` queries (query 02b) instead of re-deriving them from the row pull. These feed the page 3 / page 6 by-function reshape counts; they count person-stints per month, which is a different unit from the title-string classification (see §2.5.b). For `12_month` mode the same queries run against the 12-month predicate; expect ~5x more events than a single quarter. ### 1.3 Quarter hires with geo + function (derived from `ds_employees`) No second tool call — Phase 1.1 already pulled the employee roster. Derive `ds_q_hires` from `ds_employees` via `query_datasets`: ```sql -- datasets: {"e": "ds_employees"} SELECT person_linkedin_url, full_name, start_date, title_name, title_role, title_sub_role, title_levels, location_country, location_region, location_continent, prior_company_name, prior_company_linkedin_url, prior_title FROM e WHERE start_date >= '{rolling_12mo_start_yyyymm}' -- '2025-04', match the YYYY-MM form AND start_date <= '{q_end_yyyymm}' -- '2026-03' AND end_date IS NULL -- still in role ``` Persists as `ds_q_hires`. The roster also carries `prior_company_name` / `prior_company_linkedin_url` for every row — feed this into the Phase 2 strategic-thread synthesis ("which talent pools are they pulling from"). Persist it as `ds_q_hires_priors` if you intend to surface it as its own exhibit. ### 1.4 Open job postings See `references/snowflake-queries.md` → query 04. `SILVER.JOB_POST.STG_JOB_POSTS` rows for the target quarter and currently-active postings. Persists as `ds_open_jobs_quarter` (in-quarter) and `ds_open_jobs_active` (currently open snapshot). Pitfall: `SILVER.JOB_POST.STG_JOB_POSTS` does not have a `DELETED_AT` column. Use `APPLICATION_ACTIVE = 1` to scope to currently open roles. ### 1.5 Persona / department trends See `references/snowflake-queries.md` → query 05. `GROWTH_INSIGHT_MONTHLY` for 12 months of persona-level adoption (e.g. `growth-Data Engineering`, `growth-Product`). Persists as `ds_persona`. ### 1.6 GitHub footprint See `references/snowflake-queries.md` → query 06. `EVIDENCES` rows where `EVIDENCE_TYPE_ID = 6` and the `PAYLOAD:REPO_OWNER` matches the competitor (or its open-source repo naming convention). Persists as `ds_github`. **Critical pitfall.** GitHub `EVIDENCES` rows carry the **data-collection date** in the date field, **not** the actual star or fork timestamp. Treat the GitHub section as a footprint snapshot only. Never construct a time-trend. Always carry a methodology caveat into the assembled report. ### 1.7 Customer acquisition motion (last 12 months) See `references/snowflake-queries.md` → query 07. **Methodology — first-ever, not any-mention.** The cohort is companies whose **first-ever** Packmint mention in the insights pipeline (across all time, not just the window) falls inside the 12-month window. Companies with any prior mention before the window start are excluded. This is the canonical "true new mentioner" cohort. The naive "any mention in window" query over-counts dramatically: on the canonical Packmint run the naive cut returned 87 companies, of which only **63** were truly first-ever-in-window; 24 of the 87 had earlier mentions and should not have been counted as "new". **The first-ever-in-window cohort is NOT expressible in `ask_onfire`** — it needs `MIN(start_date)` per company **across all time**, then a keep-if-first-ever-date-in-window filter, then `COUNT(DISTINCT person)` per company **inside** the window, plus a same-named-consultancy URL guard. That is multi-stage GROUP-BY + `COUNT(DISTINCT)` keyed by company + a `NOT ILIKE` exclusion, none of which a QueryIR can express. So **pull the constrained `insight_evidence` rows with a bounded `ask_onfire` QueryIR, persist them, then run all of the cohort logic client-side in `query_datasets` (DuckDB).** `insight_value` is bound and resolved server-side (`resolve_insights` shares the persona/technology vocabulary), so the old `ILIKE` casing workaround is gone — pass the competitor name and the resolver canonicalises it. `insight_evidence` is ~1B rows, so the `insight_value` + `start_date` window is what keeps the pull bounded; the row budget bills 1 credit per row, so set `limit` to the cohort size and confirm against the free COUNT when the `needs_confirmation` gate fires. ``` ask_onfire(query={ entity: "insight_evidence", select: ["person_url", "company_url", "evidence_type", "start_date"], filters: [ {dimension: "insight_value", op: "eq", value: "{competitor_name}"}, // resolved server-side {dimension: "start_date", op: "gte", value: "{rolling_12mo_start}"}, {dimension: "start_date", op: "lte", value: "{q_end}"} ], limit: // large — hits the row-budget gate; confirm against the COUNT }) ``` The QueryIR above fetches in-window rows only. The first-ever cohort also needs each company's earliest mention **across all time** to exclude companies that had pre-window mentions. Get that either with a second per-candidate-company pull (`insight_value` + `company_url` eq, selecting the `first_seen` measure = `MIN(start_date)`), or pull a wider date range and compute `MIN` client-side. Persist the rows, then in `query_datasets` (DuckDB) build the cohort: ```sql -- runs in query_datasets over the persisted insight_evidence rows, NOT in Snowflake WITH external_mentions AS ( SELECT company_url, person_url, start_date FROM ds_evidence_rows WHERE company_url IS NOT NULL AND company_url NOT ILIKE '%/company/{competitor_slug}' AND company_url NOT ILIKE '%/company/{competitor_slug}s' -- guard against same-named consultancies (Packmints) AND start_date IS NOT NULL ), first_ever AS ( -- MIN across ALL time (from the all-time pull) SELECT company_url, MIN(start_date) AS first_ever_date FROM external_mentions GROUP BY company_url ), first_in_window AS ( SELECT * FROM first_ever WHERE first_ever_date BETWEEN '{window_start}' AND '{window_end}' ), mentioner_counts AS ( SELECT fiw.company_url, fiw.first_ever_date, COUNT(DISTINCT em.person_url) AS distinct_mentioners_in_window FROM first_in_window fiw JOIN external_mentions em ON em.company_url = fiw.company_url AND em.start_date BETWEEN '{window_start}' AND '{window_end}' GROUP BY 1, 2 ) SELECT * FROM mentioner_counts ``` Persists as `ds_acquisition`. **Production-depth = `distinct_mentioners_in_window >= 2`; evaluator = 1.** **Validation check.** Compare the result against the naive "any mention in window" count. Run this client-side over the same persisted rows (the in-window pull alone is enough — no all-time `MIN` needed): ```sql -- naive (over-counts; informational only) — query_datasets, not Snowflake SELECT COUNT(DISTINCT company_url) FROM ds_evidence_rows WHERE start_date BETWEEN '{window_start}' AND '{window_end}' AND company_url NOT ILIKE '%/company/{competitor_slug}' AND company_url NOT ILIKE '%/company/{competitor_slug}s'; ``` If `naive_count > first_ever_count`, the gap is companies with prior mentions that the brief MUST NOT count. Surface the gap in the brief as a footnote so the reader trusts the methodology. Legacy template (any-mention in window, raw Snowflake — **DO NOT USE for the brief**; the `query_onfire` raw-SQL tool that ran it no longer exists; kept here only as a reference for what the brief is *not* doing): ```sql -- WRONG / over-counting / removed-tool syntax — kept for diff reference only SELECT PERSON_LINKEDIN_URL, COMPANY_LINKEDIN_URL, MIN(START_DATE) AS first_seen FROM ONFIRE.INSIGHTS_2_EVIDENCES WHERE INSIGHT_VALUE = '{competitor_name}' AND START_DATE BETWEEN DATEADD(MONTH, -12, '{q_end}') AND '{q_end}' GROUP BY PERSON_LINKEDIN_URL, COMPANY_LINKEDIN_URL ``` If `ask_onfire` rejects the `insight_evidence` entity (it is gated per-tenant), fall back to a `contact` static snapshot — current employees-of-other-companies who carry the competitor as a derived **technology insight** (the curated catalog that superseded the raw `PEOPLE` / `JOB_SUMMARY` free-text scan; ask_onfire has no regex/word-boundary op): ``` ask_onfire(query={ entity: "contact", select: ["full_name", "job_title", "current_company_name", "current_company_url"], insight_filters: [{kind: "technology", value: "{competitor_name}"}], // resolved server-side limit: }) ``` This is a footprint snapshot, not a dated motion (no `first_seen`), so label the section as "snapshot, not motion" in the brief — the Customer Acquisition page becomes a Customer Footprint snapshot. The fallback **must** be flagged in the Assumptions and Definitions block. If the caller uploads a CSV with `(person_linkedin_url, company_linkedin_url, first_seen)`, use that instead of the live query - this is the explicit user-supplied data path. Persists as `ds_acquisition`. ### 1.8 Customer firmographics See `references/snowflake-queries.md` → query 08. Join the distinct `company_linkedin_url` values from `ds_acquisition` to `ONFIRE.COMPANIES` for `INDUSTRY`, `SIZE`, `LOCATION_COUNTRY`, `EMPLOYEE_COUNT`. Persists as `ds_acq_firmo`. ### 1.9 Sentiment scoring (target quarter, full window) Use **`mode="date_range"`** — it scores *every* message that mentions the competitor in the quarter (the window is the scope), so the brief keeps its "all community sentiment about this quarter" claim. ``` # First call (no `confirmed`) returns needs_confirmation with `matched_in_window` # = the quarter's universe size. Surface that volume, then resubmit confirmed=true. Onfire MCP: community_messages_sentiment( keywords=[""], sentiment_subject="Positive or negative experience with ", date_from="{q_start}", date_to="{q_end}", mode="date_range", confirmed=true, # set on the resubmit after the count gate ) ``` Persists as `ds_sentiment`. **`date_range` scores the full quarter window**, so claims like "every Nexagon-mentioning public message in Q_" are legitimate — *with one caveat*: the run is capped at `max_range_messages` (10k) per call. If `matched_in_window` exceeds the cap, the run analyzes the most recent 10k and says so in `note`; for very high-volume competitors, run `date_range` **per month** and concatenate the `ds_sentiment` datasets for true full coverage. The per-message LLM scorer drops off-topic messages (`discarded_off_topic`); `total_returned` is the scored, on-topic set persisted to `ds_sentiment`. ### 1.10 Resolve sentiment authors See `references/snowflake-queries.md` → query 09. For every opinionated (positive + negative) external author in `ds_sentiment`, look up their LinkedIn URL in `ONFIRE.PEOPLE` to get their current employer + country. Persists as `ds_authors_resolved`. Authors that don't match a `PEOPLE` row, OR whose `PEOPLE` row has a null `JOB_COMPANY_NAME`, are the "Unresolved" bucket - shown muted in the cross-tab, not hidden. **Bias exclusion.** After joining to `ONFIRE.PEOPLE`, discard any author whose `JOB_COMPANY_LINKEDIN_URL` matches either `{company_linkedin_url}` (the competitor being analysed) or `{tenant_linkedin_url}` (the origin tenant). These authors have a direct stake in the outcome and must not appear in `ds_authors_resolved`, the sentiment cross-tabs, or the evidence wall. ### 1.11 Geo fallback for unresolved companies See `references/snowflake-queries.md` → query 10. For any company in `ds_acq_firmo` with NULL `LOCATION_COUNTRY`, get the per-(company, country) employee distribution via an **aggregate-mode** `contact` query (`group_by: ["current_company_url", "location_country"]`, `count_distinct` of people) and take the **modal country** per company as the inferred country. Persists as `ds_geo_fallback`. This bills per (company × country) group, not per employee, so the budget is small. ### 1.12 Leavers and their destinations (derived from `ds_employees`) No second tool call — Phase 1.1 already pulled the employee roster, which now covers leavers (incl. long-tenured departures) and attaches the immediately-next company on each row. Derive `ds_leavers` from `ds_employees` via `query_datasets`: ```sql -- datasets: {"e": "ds_employees"} SELECT person_linkedin_url, person_linkedin_id, full_name, title_name, title_role, title_sub_role, title_levels, start_date, end_date, location_country, location_region, location_continent, location_name, current_job_title, current_company_name, current_company_linkedin_url, -- destination on the same row, no second join needed: next_company_name, next_company_linkedin_url, next_title, next_start_date, next_end_date FROM e WHERE end_date IS NOT NULL -- they left AND end_date >= '{window_start_yyyymm}' -- in the window AND end_date <= '{window_end_yyyymm}' ORDER BY end_date, location_country ``` `start_date` / `end_date` are stored as `YYYY-MM` strings, not full dates — compare against `YYYY-MM` literals. Tenure-at-target is derivable per row as `end_date - start_date` (in months). Interns surface naturally as 4-6-month tenures with `intern` in the title — call those out separately from real departures. `ds_leavers` is a single dataset; destination columns live on the same row. If a destination size / industry / country breakdown is needed, pull the distinct `next_company_linkedin_url` values from the `company` entity via a follow-up `ask_onfire` call (the Query 08 shape: `linkedin_url op=in`, batch in groups of 30-50, `limit` = batch size). Many leavers will not have a captured next role — call the gap out explicitly ("6 of 18 destinations captured"). Used in: Complication 1B leavers card; Phase 2 strategic-thread synthesis ("where are senior people going?"). ### 1.13 Departed-no-backfill recoverability (only if Phase 2 detects any) See `references/snowflake-queries.md` → query 13. For each title classified as `departed-no-backfill` in Phase 2.1, run two checks: 1. **Current-holder scan.** ask_onfire cannot OR-scan the free-text `SUMMARY` / `HEADLINE` / `JOB_SUMMARY` columns (they are returnable attributes, not filterable dimensions). Use the insight-native substitute instead: map the departed function to a curated persona (via `resolve_insights`) and scan current `contact` employees of the target with that `insight_filter` (see snowflake-queries.md → query 13a). If the function does not resolve to any curated persona, the free-text "doing the work under a different title" scan has **no ask_onfire expression** — flag it and lean on check 2 alone. 2. **Open-job-posting check.** Filter `ds_open_jobs_active` for the same function keywords. Three outcomes per departed-no-backfill title: | Read | Definition | |---|---| | **Parallel hold by ...** | The function is still held by 1+ current employees whose role summary describes the work. Often pre-existed the departed person's tenure. | | **Open posting in flight** | No current holder but an active posting targets the function. | | **Function lapsed** | No current holder AND no open posting. The dedicated seat experiment ended and the work isn't visible elsewhere. | This becomes the Departed-no-backfill detail table on page 4 - a post-Phase-2 enrichment, not a Phase 1 data slice. ### 1.14 Office segmentation (geographic footprint for page 2) Powers the **Geographic footprint** card on the executive summary (page 2). Three calls in sequence: ``` Onfire MCP: search_offices( company_name="{competitor_name}", company_website="{competitor_website}", telemetry={intent: "Competitor brief office segmentation"} ) ``` Returns `offices` (list of `{city, country, state, region, is_hq}`) and `total_offices`. Persist as `ds_offices`. Then pull the company's employee locations. The per-location `COUNT(*)` roll-up across `(country, region, name)` IS now an **aggregate-mode** query — `group_by` the three location dimensions and `count` people, server-side, instead of pulling every employee row and rolling up in `query_datasets`. This bills one credit per location group, not per employee: ``` ask_onfire(query={ entity: "contact", filters: [{dimension: "current_company_url", op: "eq", value: "{company_linkedin_url}"}], group_by: ["location_country", "location_region", "location_name"], aggregations: [{name: "employee_count", fn: "count_distinct", field: "linkedin_url"}], order_by: [{field: "employee_count", direction: "desc"}], limit: }) ``` Persist the result directly as `ds_location_distribution` (the group-by keys plus `employee_count` are exactly the columns the old client-side roll-up produced — no `query_datasets` aggregation needed). The old numeric `JOB_COMPANY_LINKEDIN_ID` disambiguation guard (needed when the slug was ambiguous, e.g. `packmint` vs `packmints`) is no longer available — ask_onfire has no numeric-ID dimension on `contact`. Pass the **verified `company_linkedin_url`** from Phase 0 `match_company` as `current_company_url`; it is normalized server-side and resolves to the one canonical company, so it does not pollute with a same-named neighbour the way a bare slug `ILIKE` did. **Use the headcount snapshot (from Phase 1.1) as the denominator** for the % column, not the sum of the distribution rows. People with a null `LOCATION_COUNTRY` are excluded from the group-by but are still part of the headcount — surface this as a "Remote / location not disclosed" row equal to `headcount_total − sum(employee_count)`. #### Assignment rules (location cluster → office) Per the `office-segmentation` skill: 1. **City match** — an office `city` (case-insensitive) appears inside `LOCATION_NAME` → assign cluster to that office. 2. **Country match** — only when no city match exists AND a single office exists in that country → assign to that office. 3. **Multiple offices in same country, no city hit** → assign to `Remote / Unknown` rather than guess. 4. **No match** → assign to `Remote / Unknown`. For competitors with only 1 official office (common for sub-200-person scale-ups), most clusters will only match by country. Surface the distinction in the card narrative — Belfast HQ catchment vs distributed hubs without an official site. Persist the rolled-up table as `ds_office_segmentation`: | Column | Notes | |---|---| | `bucket_label` | E.g. "Belfast HQ", "Jaipur cluster", "Remote / location not disclosed" | | `bucket_type` | `hq` / `office` / `distributed_hub` / `other` / `remote_unknown` | | `employee_count` | Sum of matching `LOCATION_NAME` clusters | | `pct_of_total` | `employee_count / headcount_total` | | `notes` | Cities included in this bucket | ### 1.15 Event attendance signals (conditional Complication 2D) Powers the **Event attendance** page (Complication 2D). Source entity: `event_contact` (`ONFIRE.EVENTS_CONTACTS`) — every captured signal of a competitor person attending a conference, summit, webinar or industry event. The brief wants the attendees **with names + titles**, which the raw query got by joining `EVENTS_CONTACTS` to `PEOPLE`. In ask_onfire that is the documented inverse: query the `contact` entity (so you can SELECT profile fields) and apply `event_contact` as a **filter-only join** (`event_contact` exposes no profile fields to select): ``` ask_onfire(query={ entity: "contact", select: ["full_name", "job_title", "location_name", "location_country"], filters: [{dimension: "current_company_url", op: "eq", value: "{company_linkedin_url}"}], joins: [{entity: "event_contact", filters: [ // optionally pin one event: {dimension: "event", op: "eq", value: "RSAC 2026"} {dimension: "active_employment", op: "eq", value: true} // attendee still at the company ]}], limit: }) ``` Two capability notes vs the raw query: - **No numeric-ID disambiguation.** The raw query keyed on `company_linkedin_id` (numeric) to avoid same-named-company pollution. ask_onfire has no numeric-ID dimension — pass the **verified `company_linkedin_url`** from Phase 0 `match_company` (normalized server-side, resolves to the one canonical company). - **Per-event grouping.** The named-attendee pull above cannot also return a per-event `COUNT` in the same call (aggregate mode requires an empty `select`, so you can't have both the named rows and the group counts at once). Two ways to get the per-event distribution: read the pre-aggregated `event_company.attendee_count` (`select: ["event", "attendee_count"]`, `company_url eq`, read directly), or run an **aggregate-mode** query over the same `contact` + `event_contact` join (`group_by: ["event"]`, `aggregations: [{name: "attendees", fn: "count_distinct", field: "linkedin_url"}]`). Either is server-side now; grouping `ds_events_contacts` in `query_datasets` is only needed if you already hold the named rows and want to avoid a second call. The stored event names are prefixed `Event - ` and resolved server-side from the human wording. Persist as `ds_events_contacts`. **Include the Complication 2D page only if at least one attendance signal is captured.** Below 5 captured attendances, the sowhat MUST frame the absolute count as a floor and focus on surface concentration (which events, which roles) rather than counts. --- ## Phase 2 - Analysis Read `references/analysis-patterns.md` for the canonical transforms. The short version: ### 2.1 Title movement → 3 buckets Classify every in-window event in `ds_title_movement`: | Bucket | Definition | |---|---| | Paired swaps | Old title departed AND a new title arrived in the same function in-window | | Departed-no-backfill | Title departed, no in-window replacement → trigger Phase 1.13 recoverability check | | Net-new functions | Title arrived with no prior holder, no departure pair | For each event, also tag **seniority**: leadership (VP / Director / Manager+) vs department (IC, individual contributor, even Senior / Principal / Staff). Then synthesise the bets into **3-5 strategic threads** that explain the cluster (e.g. "broaden public-sector GTM" or "build AI ops bench"). **Important:** the departed-no-backfill bucket is NOT a Q+1 watch list. Most departed-no-backfill titles are short-tenure dedicated seats where the function continues elsewhere - confirm via Phase 1.13 before characterising a function as "lost." ### 2.2 Sentiment owned vs external Split `ds_sentiment` opinionated rows by whether `community_name` matches the competitor name (owned brand surface) or not (external developer community). Compute the positive-share-of-opinionated for each pool: ``` owned_pos_share = owned_positive_count / (owned_positive_count + owned_negative_count) external_pos_share = external_positive_count / (external_positive_count + external_negative_count) ``` Report as percentages. The brief uses percentages, not raw counts, everywhere in opinion sections. ### 2.3 Resolved vs Unresolved authors Join `ds_sentiment` to `ds_authors_resolved` by `linkedin_url`: | Tag | Definition | |---|---| | `resolved` | LinkedIn URL has a `PEOPLE` row AND that row has a current `JOB_COMPANY_NAME` | | `unresolved-no-employer` | `PEOPLE` row exists but `JOB_COMPANY_NAME IS NULL` | | `unresolved-no-profile` | LinkedIn URL not in `PEOPLE` at all | Compute % shares of positive / neutral / negative for each continent and employer-size bucket from the resolved set. Show the unresolved bucket muted at the bottom of each cross-tab. ### 2.4 Customer acquisition cohort For every distinct `company_linkedin_url` in `ds_acquisition`, compute the company-level `first_seen` (earliest first_seen across all that company's employees). Then: 1. Filter to companies with `first_seen` in the last 12 months. 2. Join to `ds_acq_firmo` for industry, size, country. 3. For any company with NULL `LOCATION_COUNTRY`, take the modal employee country from `ds_geo_fallback`. 4. Bucket by industry into 5-8 categories (see `references/analysis-patterns.md` → §4). 5. Bucket by size (SMB 1-200, Mid-market 201-1000, Enterprise 1001+). 6. Bucket by region (North America / EMEA / Asia Pacific / South America). 7. Build a monthly stacked-bar histogram: rows = months, stacks = 4-5 industry buckets. ### 2.5 People movement — joiner / leaver / promotion semantics `ds_employees` returned by the headcount tool contains all the person-stint events the brief needs. Three derived buckets feed pages 5, 6 and the exec-summary findings: | Bucket | Rule | Use on | |---|---|---| | **External hires** | `start_date IN window AND end_date IS NULL` AND the person has no other stint at the target whose `end_date` is in window | Page 5 hero stat: "People joined"; page 5 Joiners card (region + seniority breakdown) | | **Internal promotion** | One person has BOTH a stint ending in window AND a stint starting in window (`b.start_date >= a.end_date`, same `person_linkedin_url`) | Page 6 promotions card only — does NOT appear as a page 5 hero stat AND does NOT appear in either the Joiners or the Leavers card on page 5 | | **Real departure** | `end_date IN window` AND the person has no active stint at the target (`end_date IS NULL` somewhere else for this person) | Page 5 hero stat: "People left"; page 5 Leavers card (region + seniority breakdown — real departures only) | The net headcount growth is computed directly from movements — it is NOT taken from `ds_headcount` snapshot delta: ``` net_headcount_growth = external_hires − real_departures ``` This is the only number shown as the Net stat on page 5. The three page 5 hero stats are therefore: | Card | Label | Value | |---|---|---| | 1 | People joined | `+{external_hires}` (green) | | 2 | People left | `−{real_departures}` (red) | | 3 | Net headcount growth | `+{external_hires − real_departures}` (green) | The subtext on card 3 explains: "N joined − M le …(truncated)