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:

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

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

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:

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:

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="<competitor_name>",
  telemetry={intent: "Competitor intelligence brief for <competitor_name>"}
)

Confirm the verified LinkedIn URL with the user before proceeding. Store the firmographic block returned by match_company:

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:

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:

-- 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: <window cohort size>     // 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:

-- 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):

-- 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):

-- 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: <snapshot budget>
})

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=["<competitor_name>"],
  sentiment_subject="Positive or negative experience with <competitor_name>",
  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:

-- 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:

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: <distinct location groups>
})

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:

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: <captured-attendances budget>
})

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:

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:

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:

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:

The subtext on card 3 explains: "N joined − M le

…(truncated)