agentleFS
risukisurisukisu/marketing-skills/skills/Marketing-Ops/pipeline-analysis/SKILL.md

pipeline-analysis

Skill6 starsChanged 49 days agoMITView on GitHub

Run a HubSpot RevOps pipeline and conversion analysis over a period (a quarter, a year, a custom or trailing range) and return a diagnosis-first report: verdict (binding constraint A–F with owner and value), reverse-funnel scorecard, New Business full diagnostic (flow plane, diagnosis plane, cohort conversion, velocity, sources), Account Management condensed, reconciliation, method note. Markdown by default; HTML on request. Use when the user says \"/pipeline-analysis\", \"run a pipeline analysis\", \"revops analysis\", \"conversion analysis for [period]\", or asks how leads, MQLs, and SQLs converted over a period. NOT the monthly board snapshot (that is marketing-monthly) and NOT a single campaign's report (that is campaign-report). Company and CRM ids come from a context pack in the workspace you launch from, chosen per run from the company and period you pass (e.g. /pipeline-analysis Q3 Acme); nothing client-specific lives in this skill.

3 other projects use a version of this.

Adopted by

Most recently taken up by

The file

We split this skill into the rules it gives an agent

5 rules. 0 are common, 2 are distinctive, and 3 are very specific to this project.

  • 0

    Common

    Most of the 3 edited versions we read keep these.

  • 2

    Distinctive

    This author's own choices. Worth a look.

  • 3

    Very specific

    Tied to their project's names, files or tools. Probably skip.

  • Common
  • Distinctive
  • Very specific
  1. Distinctive:

    No pack for this company yet?: Create one in the launch workspace, never in this skill's folder: next to the company's other context if the …

    line 35
  2. Distinctive:

    No fabrication: Every number traces to a query.

    line 49
  3. Very specific:

    Call mcp__claude_ai_HubSpot__tool_guidance(["query_crm_data"]) once first.

    line 80
  4. Very specific:

    Write pa_input.json to the scratchpad:

    line 123
  5. Very specific:

    Run the engine with the company's files from the reporting/ folder beside its pack:

    line 134

“Common” is measured: kept by at least 6 in 10 of the 3 edited versions of this file we could read. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

pipeline-analysis — RevOps funnel & conversion analysis

A deterministic engine (compute_funnel.py, stdlib only) plus a reporting workflow. Read definitions.md (same folder): it is the source of truth for every metric. campaign-report and marketing-monthly point at it rather than redefining metrics.

Paths in this file. compute_funnel.py, definitions.md, references/… are relative to this skill's folder. Output paths come from the client pack, never from the current directory.

Step 0 — Company and period (every run, one question)

The skill holds no company or CRM data, and its folder never stores any: this library is a public repository. Context packs live in the workspace you launch from. /marketing-monthly and /campaign-report use the same packs and the same procedure.

  1. Read the arguments, in any order: a company and a period, e.g. /pipeline-analysis Q3 Acme or /pipeline-analysis Acme 2026. Either may be missing.
  2. Find the packs in the launch directory: markdown files whose front-matter says pack: reporting (grep ^pack: reporting over **/*.md; skip *.TEMPLATE.md). Read only their front-matter (company, aliases), nothing else yet.
  3. Ask one question, always, even when the arguments look complete: the company (matched against company and aliases) and the period, pre-filled from the arguments. For example: "Acme · Q3 2026 (1 Jul–30 Sep) · pack companies/acme/context/reporting.md — go?" No company given → list the companies found. No period given → propose the last full quarter. Other answers: point me at another file / paste the ids / start with none. Wait.
  4. Read only the approved pack. Relative paths in it (output root, templates, engine files) resolve from the pack's folder. A pack supplies portal id, pipeline→funnel mapping, stage ids, ticket stages, AM sources, targets, reason map, output root, house style. "None" means the ids are collected by hand and the run stops at markdown with generic labels.
  5. No pack for this company yet? Create one in the launch workspace, never in this skill's folder: next to the company's other context if the workspace has such a place (ask; e.g. companies/<company>/context/reporting.md), otherwise reporting-packs/<company>.md. Start from references/context-pack.TEMPLATE.md, fill it with the user verifying every id against the live portal (SELECT pipeline, dealstage, COUNT(*) FROM DEAL GROUP BY pipeline, dealstage is the fastest way), and create the three engine files it describes in a reporting/ folder beside the pack. Then continue.
  6. Legacy packs (transition only). Older installs kept packs as references/<client>-context.local.md inside the skill folders. Offer one only when <client> appears in the launch directory's path, so a workspace never sees another workspace's pack. Say it is the old location and offer to move it into the workspace. Its engine files keep their old references/<client>-*.local.json paths until moved.

Critical rules

  1. No fabrication. Every number traces to a query. Empty result → say so.
  2. MQL month = deal createdate, never the stage-entry property (it re-stamps on re-entry).
  3. Two-plane model: flow (where volume drops or slows) and diagnosis (which failure mode A–F, whose, how much). Always computed and reported separately.
  4. NB primary, AM condensed. New Business gets the full diagnostic; Account Management gets top-line + realized revenue + its own target when provided. No goal-seek, no verdict for AM.
  5. Loss reason from the structured picklist, mapped to A–F via the client's reason map. Free-text reason is context only.
  6. Two-lens conversion, never blended. Throughput (events by month vs targets) and cohort (of deals born in month M, the % that ever progressed). Never divide this month's SQLs by this month's MQLs.
  7. Targets are versioned, append-only, effective-dated. The resolved revision is stamped in the report.
  8. Reverse-funnel derives lead volume from the revenue target and measured cohort rates. No external lead target exists by design.
  9. Two funnels, never blended. NB excludes renewals; AM keeps them. Archive pipelines out.
  10. Won/Lost across all pipelines by per-pipeline stage ids. SQL uses first entry; re-entry flagged, not modeled.
  11. Home currency only (amount_in_home_currency).
  12. Flag, don't hide. Renewals, sub-deal candidates, prior-period re-entries, cross-period leads, ticket-only MQLs surface in a reconciliation block; sub-deals get human confirmation before exclusion.
  13. CRM numbers are directional, not absolute. Say so in the method note.

Workflow

Step 1 — Period

Resolve the period confirmed in Step 0 (a quarter, a year, a custom range, or trailing-to-today) to start/end ISO dates. Both funnels are always produced.

Step 2 — Pull data (HubSpot MCP)

Call mcp__claude_ai_HubSpot__tool_guidance(["query_crm_data"]) once first. Substitute every id from the pack.

Deals created in period, both funnels' pipelines, filtered by createdate. NB uses the main pipeline's stage-entry properties; AM uses its own (hs_v2_date_entered_<stageId>). Normalize both into generic mql_entry / sql_entry / proposal_entry / nego_entry / won_entry in Step 3.

SELECT hs_object_id, dealname, pipeline, dealtype, createdate,
  <stage-entry properties for this funnel>, closedate, dealstage,
  amount_in_home_currency, deal_currency_code,
  <loss-reason picklist>, <loss-reason free text>
FROM DEAL WHERE pipeline IN (<funnel pipelines>) AND createdate BETWEEN '<start>' AND '<end-of-month>'

⚠️ Data-pull gotchas, verified on live portals:

  1. Raw record queries silently drop same-day upper-bound rows and can truncate at ~20–25 rows with no pagination note. Always reconcile against SELECT DATE_TRUNC(createdate,'MONTH'), pipeline, COUNT(*) FROM DEAL ... GROUP BY ... (aggregates include same-day) and use end-of-month bounds; fetch any deal the GROUP BY shows but the raw pull missed.
  2. Won/Lost stage ids are per pipeline. Confirm with SELECT pipeline, dealstage, COUNT(*) FROM DEAL GROUP BY pipeline, dealstage.
  3. Some stage-entry properties (stage ids that are UUIDs) are rejected by the SQL layer. Set that entry to null and note the gate undercount in the report; won deals still pass every gate.
  4. The reporting API allows at most two GROUP BY dimensions.

Also pull deals whose stage entry is in-period but whose createdate is before it: prior-period re-entries the engine flags.

Trailing-window won deals for avg deal size when in-period wins are sparse (n < 5): widen to 18–24 months; flag low confidence if still n < 5.

Tickets created in period from the lead-inbox pipeline: hs_object_id, createdate, closed_date, source_type, hs_pipeline_stage, subject, ORDER BY createdate ASC LIMIT 500 (stable, complete). Reconcile against month×stage and source×stage GROUP BYs before trusting the enumeration. Split NB vs AM tickets by the pack's AM sources.

Ticket→MQL velocity needs each deal's originating-ticket createdate: pull deal↔ticket links via the cross-object query (... FROM DEAL WHERE ... AND associations.TICKET IS NOT NULL) and carry the earliest ticket createdate onto each deal as ticket_createdate. AM deals usually have none; that is legitimate.

Step 3 — Normalize → JSON → compute

Write pa_input.json to the scratchpad:

  • deals: id (string), name, pipeline, createdate, stage, amount_home, currency, dealtype, the five generic entry dates, ticket_createdate (or null), loss_reason (picklist value), loss_desc (free text).
  • tickets: the reconciled enumeration (id, createdate, closed_date, source_type, stage, subject). Fallback [] only if it will not reconcile; the ticket-side tables then come straight from the GROUP BYs.
  • top level: period, generated, manual_excludes (filled in Step 4). Optional: targets_revision, avg_deal_override, sql_reentry. The crm and funnels blocks may be inlined here or supplied with --config.

Run the engine with the company's files from the reporting/ folder beside its pack:

python compute_funnel.py <scratch>/pa_input.json <scratch>/pa_out.json \
  --config     <pack folder>/reporting/config.json \
  --targets    <pack folder>/reporting/targets.json \
  --reason-map <pack folder>/reporting/reason-map.json

A legacy pack (Step 0.6) still points at references/<client>-{config,targets,reason-map}.local.json. Output: {period, generated, targets_revision, funnels: {nb: {...verdict}, am: {...}}}. NB carries flow_gates / accept_gate / mql_cohorts / funnel / leak_events / leak_pnl / execution_flags / conversions / throughput / throughput_vs_target / velocity / ticket_cohorts / sources / flags / reverse_funnel / verdict; AM the non-diagnostic subset. Tests: python test_compute_funnel.py (run them if you touch the engine).

Step 4 — Confirm candidate exclusions

Show flags: renewals, sub-deal candidates, prior-period re-entries, ticket-only MQLs. Sub-deal exclusion is human-confirmed: ask which ids go into manual_excludes, re-run Step 3.

Step 5 — Write the markdown report

Save to <output root from pack>/<period>-pipeline-conversion-analysis.md. Every number from pa_out.json (deal side) or the Step-2 GROUP BYs (ticket side). Diagnosis-first structure:

  1. Verdict header — on/off track for the revenue and MQL/SQL targets; the binding constraint (A–F) with owner and value; targets-revision label. NB only.
  2. Reverse-funnel scorecard — required vs actual per stage + year-end projection. Low-confidence flag on avg deal size when n < 5. Derived requirements labeled directional.
  3. New Business (full) — flow plane (spine with per-gate in/through/leaked, velocity, time-in-stage, throughput vs target); diagnosis plane (leak P&L by A–F with owner; loss-reason breakdown with free text as context; C-inference flags labeled hypothesis; a qualitative callout slot for sales-retro findings); conversion (cohort rates to maturity, immature cohorts flagged); velocity (median/mean/range, outliers flagged); sources (volume, accept rate, MQL conversion by source).
  4. Account Management (condensed) — top-line, realized won revenue vs AM target if any. No verdict. Note that AM enters at Opportunity.
  5. Reconciliation & flags — exclusions applied; ambiguous reason values; Unattributed bucket; SQL re-entries; cross-period leads; ticket-only MQLs; zero-amount deals; targets stamp.
  6. Method note — deal side = engine over the full set; ticket side = GROUP BYs; stage→pipeline mapping verified empirically; inference caveats; CRM-is-directional statement.

De-identify to company level unless the pack's house style says otherwise.

Step 6 — HTML (only when asked)

Render the markdown with the pack's style template. The leads→client master funnel has two views behind a pill toggle, default Conversions, second + Drop-out, never a third:

  • Conversions: six stages (Leads → Accepted → MQL → SQL → Opportunity → Client), sequential bars deepening down the funnel with the won terminal in the pack's success colour, counts in ink beside bars (never inside coloured bars), faint ribbons between stages, per-boundary chevrons for next-step and cumulative rates, each header with a "?" hover tip (definition + example).
  • + Drop-out: same bars plus a thin secondary row splitting exits into still-live (light accent, can still convert) and terminally lost (red), each with an inline label. Legend in this view only. Still-live + lost + won must reconcile to the top-of-funnel count. Toggle is vanilla JS on the hidden attribute. Reuse the block across periods; don't redesign.

Notes

  • The five calibration decisions (two funnels, all-pipeline won/lost, SQL first-entry, full-lifecycle cohort conversion, AM-deal handling of ticket-only captures) and the two-plane diagnostic model are baked into the engine and definitions.md.
  • marketing-monthly stays independent; the pack owner watches definition drift by hand.

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.