From deal-diligence
calculate, interpret, and package b2b saas go-to-market metrics into a diagnostic excel workbook using the bundled metric catalog, formulas, and input requirements. use when a user uploads gtm tables, exports, board files, forecasts, or operating spreadsheets and wants a clean excel-based diagnostic analysis of growth drivers, arr funnel, pipeline, retention, efficiency, productivity, or forecast metrics. ask for missing uploaded inputs, distinguish provided values from derived calculations, and populate the bundled workbook tabs for inputs, metric calculations, and diagnostic output.
How this skill is triggered — by the user, by Claude, or both
Slash command
/deal-diligence:gtm-metrics-analyzerThe summary Claude sees in its skill listing — used to decide when to auto-load this skill
Build a stand-alone GTM diagnostic workbook from uploaded tables and files.
Build a stand-alone GTM diagnostic workbook from uploaded tables and files.
Use this skill when the user wants an Excel-based diagnostic analysis of GTM performance from uploaded source files.
The skill should:
This skill is built primarily for B2B SaaS companies with some sales-led motion.
Excel version note: Metric formulas use XLOOKUP, which requires Excel 365 or Excel 2019+. Earlier versions will show
#NAME?errors in Metric_Calcs and Diagnostic_Output. If the user is on an older version, replace XLOOKUP with INDEX/MATCH equivalents.
financial-model-builder ──┐
driver-tree ──┤──► gtm-metrics-analyzer ──► kpi-tree-builder (post-close)
ntb-diligence ──┘ ──► ic-memo (supporting metrics section)
| Task | Use |
|---|---|
| Quick metric definition or benchmark lookup | finance-metrics-quickref |
| Full GTM diagnostic workbook from uploaded files | this skill |
| Causal driver decomposition with owner assignment | kpi-tree-builder |
| Investment Committee memo with GTM section | ic-memo (calls this skill for metrics) |
| 3-tab operating model from P&L | financial-model-builder |
financial-model-builder: Load first if a 3-tab model exists. Pull Revenue, Gross Profit, S&M OpEx directly from the Output Tab rather than asking for re-entry.driver-tree: If a driver tree has been built, import node values directly as input fields rather than entering them manually.ntb-diligence: NTB outputs (new logo ARR, cohort retention) feed directly into the ARR funnel and retention sections of this workbook.finance-metrics-quickref: Use for quick metric definition lookups or benchmark checks. This skill handles calculation from uploaded data; finance-metrics-quickref handles definition and quick-reference.kpi-tree-builder: After GTM diagnostic is complete, hand off the derived metric outputs to kpi-tree-builder to assign owners, cadences, and thresholds for post-close tracking.ic-memo: The IC memo's GTM performance section pulls from this skill's Diagnostic_Output tab. Run gtm-metrics-analyzer first; reference the workbook in the memo.This skill is designed for uploaded tables and files.
Preferred inputs:
If the user asks for calculations without enough uploaded data, do not guess silently. Instead:
references/input-requirements.md to identify the missing fields,Map the request into one or more metric families using references/metric-catalog.md:
Before calculating, check the uploaded files against references/input-requirements.md.
Always separate:
If data is incomplete, tell the user exactly which uploaded tables or columns are still needed.
Use references/formulas.md as the human-readable source of truth for all metric formulas and methodology.
Rules:
Use the bundled asset workbook:
assets/gtm_metrics_template.xlsxTo regenerate the template from source, run:
assets/build_template.py (requires Python + openpyxl)The bundled workbook is organized into four tabs:
README = how to use the workbook, tab order, requirements, and metric family indexInput_Fields = user-provided values entered directly into column F (User Value); one row per input field, organized by sectionMetric_Calcs = 48 derived metrics; column G (Calculated Value) auto-populates via XLOOKUP from Input_FieldsDiagnostic_Output = 20-metric summary view; values pulled from Metric_Calcs via XLOOKUPThe Metric_Calcs tab columns are:
| Col | Content |
|---|---|
| A | Metric Family |
| B | Metric Key (XLOOKUP lookup key — do not modify) |
| C | Metric Name |
| D | Type (Derived) |
| E | Formula / Logic |
| F | Required Inputs |
| G | Calculated Value (formula; auto-populates when Input_Fields is populated) |
| H | Comments |
When completing work for the user, populate Input_Fields column F with values from uploaded source files, then review Metric_Calcs column G and Diagnostic_Output for the derived results. Preserve this structure unless the uploaded workbook already has a better one.
For multi-period time-series analysis (when the user provides monthly data across multiple fiscal years), see the advanced multi-period architecture section below.
Default output mode is diagnostic analysis in excel.
A good output should:
Use this section when the user provides monthly time-series data across multiple fiscal years and wants a full multi-period build instead of the single-period bundled template.
In a multi-period build, add a fifth tab — Raw_Monthly_Data — as the source-of-truth layer before Input_Fields. The formula chain becomes:
Raw_Monthly_Data ← source of truth for all monthly time-series data
↓
Input_Fields ← annual aggregates, rates, and context; formula-driven from Raw_Monthly_Data
↓
Metric_Calcs ← all metric calculations; formula-driven from Input_Fields
↓
Diagnostic_Output ← summary and findings; formula-driven from Metric_Calcs
Nothing flows backward — each layer only reads from the layer above it.
Never hardcode a value in Metric_Calcs or Diagnostic_Output that could be a formula referencing Input_Fields or Raw_Monthly_Data. The only hardcoded (blue) values permitted are context fields (e.g., business model, reporting cadence, benchmark references, source notes) and all cells in Raw_Monthly_Data.
Use named groups of 12 columns per fiscal year. For FY2024+FY2025: cols D–O = FY2024, cols P–AA = FY2025.
Organize rows into logical sections separated by blank rows. Suggested structure:
One value per cell. Never combine two values in one cell.
Wrong: "21 deals, €115K MRR" in a single cell
Right: separate rows — one for deal count, one for MRR
Months where data is not yet available should be left blank (None / empty), not zero.
When Input_Fields is formula-driven from Raw_Monthly_Data (multi-period build), use these patterns:
Flow metrics (accumulate): use SUM
=SUM('Raw_Monthly_Data'!D7:O7) # FY2024 New Logo MRR
=SUM('Raw_Monthly_Data'!P7:AA7) # FY2025 New Logo MRR
Rate / percentage metrics (average): use AVERAGEIF to skip null months
=AVERAGEIF('Raw_Monthly_Data'!D27:O27,"<>") # FY2024 Gross Margin avg
Do not use AVERAGE() for rate metrics — it counts blank months as zero, which distorts partially-available periods.
Stock metrics (point-in-time): reference a specific month cell
='Raw_Monthly_Data'!D6 # Jan-24 BOP MRR (start of FY2024)
Metrics with intermittent nulls: use IFERROR to default null months to zero
=IFERROR('Raw_Monthly_Data'!P10, 0) + 'Raw_Monthly_Data'!P11
The sheet reference must wrap the range, not the function:
Wrong: ='Raw_Monthly_Data'!SUM(D7:O7) — invalid Excel; produces #NAME?
Right: =SUM('Raw_Monthly_Data'!D7:O7) — the function wraps the full sheet-prefixed range
This applies to all functions: SUM, AVERAGE, AVERAGEIF, MIN, MAX, COUNT, etc.
For a multi-period build, expand Metric_Calcs to one column per fiscal year:
| Col | Content |
|---|---|
| B | Metric label |
| C | FY2024 value (formula from Input_Fields) |
| D | FY2025 value (formula from Input_Fields) |
| E | Unit |
| F | Delta (=D−C) |
| G | Delta % (=IF(C<>0,(D−C)/ABS(C),"—")) |
| H | Benchmark |
| I | Notes / methodology |
| J | Source |
| K | Commentary |
Magic numbers require prior-quarter S&M in the denominator. The correct syntax:
# Net Magic Number Q1 2025 = (Q1 Net New MRR × 4) / Q4 2024 S&M
=IF(SUM('Raw_Monthly_Data'!M24:O24)<>0, SUM('Raw_Monthly_Data'!P13:R13)*4 / SUM('Raw_Monthly_Data'!M24:O24), "—")
Note: the SUM function wraps the full 'Sheet'!range reference. Do not split it.
Apply consistently. A reviewer should be able to identify at a glance which cells are assumptions vs. calculations.
Deal cycle is a first-class GTM metric for sales-led SaaS. It must be calculated from CRM data, not estimated.
closeDate − createDate in calendar days, per individual closed-won deal recordUse the 1.5×IQR Tukey fence applied per deal type, not across all deal types combined:
Q1 = 25th percentile of deal cycle days for that type
Q3 = 75th percentile
IQR = Q3 - Q1
lower fence = Q1 - 1.5 × IQR
upper fence = Q3 + 1.5 × IQR
# Any deal outside [lower, upper] is flagged as an outlier
Applying one fence across all types masks genuine patterns: a 200-day new contract cycle is within-range for New Contracts but an extreme outlier for Renewals.
| Metric | Note |
|---|---|
| Avg deal cycle — All types | Include with caveat if outliers are present |
| Median deal cycle — All types | Primary figure |
| Avg deal cycle — New Contract | |
| Median deal cycle — New Contract | |
| Avg deal cycle — Renewal | |
| Median deal cycle — Renewal | |
| Avg deal cycle — PoC | Flag if n < 10 (thin sample) |
| Median deal cycle — PoC | |
| P25 / P75 per type | Signal predictability |
| Contract length — by type | endDate − startDate; typically 12 months for renewals, 3 months for PoCs |
Open pipeline stage age measures how long currently-open deals have been sitting in the pipeline, as a proxy for velocity and stall risk.
today − deal.createDate per open dealThis uses createDate as a proxy for stage entry date. The exact stage entry date (when a deal moved into its current stage) is not surfaced by most CRM APIs. If stage history is available via webhooks or audit log, use that instead — it is more accurate.
When the user provides data for multiple periods:
When building the workbook programmatically (recommended for repeatability):
#REF!, #NAME?, #DIV/0!, #VALUE! before delivering; fix at the source formula, not by overriding with values| Error | Cause | Fix |
|---|---|---|
#NAME? | Sheet reference before function: ='Sheet'!SUM(A1:A5) | Reverse: =SUM('Sheet'!A1:A5) |
#VALUE! | String in a cell starting with = being interpreted as a formula | Never start comment strings with =; rephrase as "Gross New = New Logo + Expansion" |
#REF! | Cell reference points to a deleted or out-of-bounds cell | Verify all row/column indices before saving |
#DIV/0! | Denominator is zero or blank | Wrap with IF(denominator<>0, formula, "—") |
Track row numbers as named variables during programmatic construction, not as magic numbers:
RD_BOP = 6 # BOP MRR row in Raw_Monthly_Data
RD_NL = 7 # New Logo MRR row
# etc.
# Use these to build cross-sheet formulas:
f"=SUM('Raw_Monthly_Data'!D{RD_NL}:O{RD_NL})" # FY2024 New Logo MRR
When the uploaded files are insufficient, prompt the user with this structure:
Use for a specific metric request. Output:
Output:
When the user wants a per-deal breakdown (rather than summary stats):
Use when the user explicitly asks for a board or operating review layout.
Use references/reporting-views.md for board deck and operating review section layouts.
When the bundled workbook is used:
Input_Fields as the data-entry layer — populate column F only; do not add formulas to columns A–EMetric_Calcs as the full metric calculation catalog; column G auto-populates from Input_FieldsDiagnostic_Output focused on the most decision-useful metricsRaw_Monthly_Data tab before Input_Fields and follow the multi-period architecture described abovekpi-tree-builder is the structural companion to this skill.
Use gtm-metrics-analyzer to:
Use kpi-tree-builder to:
This skill can support a KPI tree by supplying the metrics used in its nodes, but it should not replace the structural decomposition performed by kpi-tree-builder.
A good result should let the user answer:
npx claudepluginhub ian-lawrence423/claude-skills --plugin deal-diligenceProvides UI/UX resources: 50+ styles, color palettes, font pairings, guidelines, charts for web/mobile across React, Next.js, Vue, Svelte, Tailwind, React Native, Flutter. Aids planning, building, reviewing interfaces.
Searches MemPalace before answering questions about past work, people, projects, or prior decisions. Returns verbatim stored content instead of guessing from model memory.