# Report pack

The reports that prove the workflow works, the counting rules that keep teams on one number, SQL patterns over the normalized files, and the reconciliation gate.

## Definition template

Every report in the pack is written like this before anyone builds it:

```yaml
report_id: R01
name: MQL to SQL conversion by entry cohort
question: Of the people who first became MQL in a month, how many reached SQL within 30 days?
unit: person
cohort_timestamp: first entry into marketingqualifiedlead, UTC month
numerator: cohort members whose first salesqualifiedlead entry after the MQL entry is within 30 days
denominator: every cohort member
exclusions: test, internal and spam records (the one shared exclusion rule)
maturity_window: 30 days after the cohort month ends; immature cohorts show "immature", never a rate
owner: revops_lead
source_fields: lifecyclestage history (transitions.csv or the warehouse history table)
native_build: notes on how the CRM report is built, its builder and filters
reconciles_to: the record ID list exported with every published number
```

## The ten reports

- R01 Conversion by stage-entry cohort. Unit person (or request for repeats). Numerator: reached the next stage inside the window. Denominator: entered the stage in the cohort month. Mature only after the window closes.
- R02 Time between stages. Median and p90 days between first entries, with n, plus the count still open and their age. Unit as R01.
- R03 SLA compliance per handoff. On time / (eligible - not yet due), with late, overdue, not yet due and excluded counts beside it. Unit: request or handoff.
- R04 Buyer clock decomposition. Received to CRM and CRM to assigned in wall-clock minutes; assigned to first response in business minutes; median and p90 for each.
- R05 Stage aging and fallback queue age. Records over the stage dwell limit, oldest first; unowned requests by age. Unit: record or request at the as-of time.
- R06 Rejection and lost reasons, and re-entry loops. MQL rejections and closed-lost deals by reason per month, each with its "other" share against the 10% limit (at most 12 values per list), and records with more than 2 entries into a stage in 90 days.
- R07 Writer conflicts and backward moves. Changes by writer per workflow field, unexpected writers, ping-pong events and backward moves by source (the audit's T03, T05 and T06).
- R08 Pipeline created and won by source snapshot. Deal count and amount by the source recorded at deal creation; each deal ID counted once. This report keeps the snapshot; it does not choose an attribution model.
- R09 Closed-won to customer success acceptance. Accepted inside the SLA, returned with missing packet items, still waiting; packet gate failures by field.
- R10 Renewal coverage. Negotiated or annual cycles inside the review window with an owner, a next action and a due date, against all cycles due; decisions recorded after the notice deadline; self-serve exceptions (failed payments, scheduled cancellations, downgrades) with no owner, counted separately.

## Counting rules

- Count stage entries, not current occupancy. "Currently MQL" and "entered MQL in September" answer different questions and both can be right.
- First entry by default. A report that counts every entry says so, and repeat requests are counted on request records.
- Skipped stages: state whether a record that skipped MQL counts as reaching SQL from Lead. Both answers are valid for different questions; neither may be relabelled as the other.
- Maturity: report a cohort only once cohort end plus the window has passed; otherwise print "immature until" the date it matures.
- Keep an explicit unknown group (unknown source, unknown segment). Never drop it to make shares add up.
- One exclusion rule for test, internal and spam records, applied in every report.
- Sum deal amounts once per deal ID, in one currency with the conversion date stated. Joins to contacts or campaigns multiply rows.
- Every published number comes with the record ID list that produces it.

## SQL patterns

Snowflake syntax over two tables loaded from the normalized files: `workflow_transitions(record_id, object, field, value, changed_at, source)` and `workflow_requests` with the requests file columns. BigQuery equivalents: `TIMESTAMP_TRUNC(ts, MONTH)`, `TIMESTAMP_ADD(ts, INTERVAL 30 DAY)`, `TIMESTAMP_DIFF(b, a, SECOND)`, `COUNTIF`, `IF`, `GENERATE_DATE_ARRAY`. Translated to SQLite and run on the Acme fixtures on 2026-11-01, this logic returned the script's numbers: the R01 cohorts, the three ping-pong events and the R03 counts and overdue ages. On 2026-11-02 the per-calendar R03 join was run the same way with added closed-won rows on cs_us, one of them with its stop before its start, and matched the sla run for all three classes. The Snowflake syntax itself was not run, so load the two fixture files into your warehouse and check the same numbers come back before trusting a query on real data. These queries do not collapse clear-then-set pairs or repeated values the way the script does.

First entry per stage:

```sql
SELECT record_id, field, value AS stage, MIN(changed_at) AS first_entered_at
FROM workflow_transitions
WHERE value <> ''
GROUP BY record_id, field, value;
```

Cohort conversion with maturity (R01):

```sql
WITH params AS (SELECT '2026-09-30 20:00:00'::TIMESTAMP_NTZ AS as_of),
src AS (
  SELECT record_id, MIN(changed_at) AS from_at
  FROM workflow_transitions
  WHERE field = 'lifecyclestage' AND value = 'marketingqualifiedlead'
  GROUP BY record_id
),
reach AS (
  SELECT s.record_id, s.from_at, MIN(t.changed_at) AS to_at
  FROM src s
  LEFT JOIN workflow_transitions t
    ON t.record_id = s.record_id AND t.field = 'lifecyclestage'
   AND t.value = 'salesqualifiedlead' AND t.changed_at > s.from_at
  GROUP BY s.record_id, s.from_at
),
cohorts AS (
  SELECT DATE_TRUNC('month', from_at) AS cohort_month,
         IFF(to_at <= DATEADD(day, 30, from_at), 1, 0) AS reached
  FROM reach
)
SELECT c.cohort_month,
       COUNT(*) AS entered,
       SUM(c.reached) AS reached,
       DATEADD(day, 30, DATEADD(month, 1, c.cohort_month)) AS mature_at,
       IFF(DATEADD(day, 30, DATEADD(month, 1, c.cohort_month)) <= MAX(p.as_of),
           SUM(c.reached) / COUNT(*), NULL) AS rate
FROM cohorts c CROSS JOIN params p
GROUP BY c.cohort_month
ORDER BY c.cohort_month;
```

Velocity (R02): `MEDIAN(days)` and `PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY days)` over `DATEDIFF('second', from_at, to_at) / 86400.0` for records with a `to_at`. PERCENTILE_DISC is the nearest-rank method the script uses.

Ping-pong between writers (R07):

```sql
WITH seq AS (
  SELECT record_id, field, value, changed_at, source,
         LAG(value, 1)      OVER (PARTITION BY record_id, field ORDER BY changed_at) AS v2,
         LAG(value, 2)      OVER (PARTITION BY record_id, field ORDER BY changed_at) AS v1,
         LAG(changed_at, 1) OVER (PARTITION BY record_id, field ORDER BY changed_at) AS v2_at,
         LAG(source, 1)     OVER (PARTITION BY record_id, field ORDER BY changed_at) AS v2_source
  FROM workflow_transitions
  WHERE value <> ''
)
SELECT record_id, field, v1, v2, value AS v3, v2_source, source AS back_source
FROM seq
WHERE value = v1 AND value <> v2 AND source <> v2_source
  AND changed_at <= DATEADD(hour, 24, v2_at);
```

Coverage intervals, one row per calendar and open day in UTC. Write one SELECT per calendar in workflow-config.json (and one per span where a day has several), each with its own hours and holidays, combined with UNION ALL. A span that closes at 24:00 ends at the next day's 00:00:

```sql
CREATE OR REPLACE TABLE coverage_intervals AS
WITH days AS (
  SELECT DATEADD(day, ROW_NUMBER() OVER (ORDER BY SEQ4()) - 1, '2026-01-01'::DATE) AS d
  FROM TABLE(GENERATOR(ROWCOUNT => 731))
)
SELECT 'sdr_us' AS calendar,
       CONVERT_TIMEZONE('America/New_York', 'UTC', TIMESTAMP_NTZ_FROM_PARTS(d, '09:00:00'::TIME)) AS open_at,
       CONVERT_TIMEZONE('America/New_York', 'UTC', TIMESTAMP_NTZ_FROM_PARTS(d, '17:00:00'::TIME)) AS close_at
FROM days
WHERE DAYOFWEEKISO(d) <= 5
  AND d NOT IN ('2026-09-07'::DATE, '2026-11-26'::DATE, '2026-12-25'::DATE)
UNION ALL
SELECT 'cs_us',
       CONVERT_TIMEZONE('America/Chicago', 'UTC', TIMESTAMP_NTZ_FROM_PARTS(d, '08:00:00'::TIME)),
       CONVERT_TIMEZONE('America/Chicago', 'UTC', TIMESTAMP_NTZ_FROM_PARTS(d, '16:00:00'::TIME))
FROM days
WHERE DAYOFWEEKISO(d) <= 5
  AND d NOT IN ('2026-09-07'::DATE, '2026-11-26'::DATE, '2026-12-25'::DATE);
```

SLA targets, one row per class, mirroring `classes` in workflow-config.json:

```sql
CREATE OR REPLACE TABLE sla_targets (
  request_class STRING, stop_field STRING, target_business_minutes NUMBER, calendar STRING);
INSERT INTO sla_targets VALUES
  ('hand_raise', 'first_response_at', 15, 'sdr_us'),
  ('mql', 'disposition_at', 480, 'sdr_us'),
  ('closed_won_to_cs', 'disposition_at', 960, 'cs_us');
```

SLA categories with business minutes (R03). Each request counts only the intervals of its own class's calendar; without `c.calendar = s.calendar`, a second calendar with overlapping hours counts the same minute twice. Requests whose stop comes before their start are left out, as the script's S01 does:

```sql
WITH params AS (SELECT '2026-09-30 20:00:00'::TIMESTAMP_NTZ AS as_of),
spans AS (
  SELECT r.request_id, r.request_class, r.received_at, t.calendar, t.target_business_minutes AS target,
         IFF(t.stop_field = 'first_response_at', r.first_response_at, r.disposition_at) AS stop_raw,
         p.as_of
  FROM workflow_requests r
  JOIN sla_targets t ON t.request_class = r.request_class
  CROSS JOIN params p
  WHERE COALESCE(r.excluded_reason, '') = '' AND r.received_at IS NOT NULL AND r.received_at <= p.as_of
),
s2 AS (
  SELECT request_id, request_class, received_at, calendar, target,
         stop_raw IS NOT NULL AND stop_raw <= as_of AS answered,
         IFF(stop_raw IS NOT NULL AND stop_raw <= as_of, stop_raw, as_of) AS stop_at
  FROM spans
  WHERE NOT (stop_raw IS NOT NULL AND stop_raw <= as_of AND stop_raw < received_at)
),
bm AS (
  SELECT s.request_id, s.request_class, s.answered, s.target,
         COALESCE(SUM(GREATEST(0, DATEDIFF('second', GREATEST(s.received_at, c.open_at),
                                           LEAST(s.stop_at, c.close_at)))), 0) / 60.0 AS business_minutes
  FROM s2 s
  LEFT JOIN coverage_intervals c
    ON c.calendar = s.calendar AND c.close_at > s.received_at AND c.open_at < s.stop_at
  GROUP BY s.request_id, s.request_class, s.answered, s.target
)
SELECT request_class,
       COUNT(*) AS eligible,
       COUNT_IF(answered AND business_minutes <= target) AS on_time,
       COUNT_IF(answered AND business_minutes > target) AS late,
       COUNT_IF(NOT answered AND business_minutes > target) AS overdue,
       COUNT_IF(NOT answered AND business_minutes <= target) AS not_yet_due,
       COUNT_IF(answered AND business_minutes <= target)
         / NULLIF(COUNT(*) - COUNT_IF(NOT answered AND business_minutes <= target), 0) AS compliance
FROM bm
GROUP BY request_class;
```

Use seconds divided by 60, not minute differences: Snowflake's DATEDIFF compares the named part only and ignores smaller units, so 10:00:59 to 10:01:00 is one minute.

## Warehouse sources

- HubSpot through Fivetran: contact_property_history (contact_id, name, value, timestamp, source, source_id), and the dbt_hubspot models hubspot__contact_history (contact_id, field_name, new_value, valid_from, valid_to, change_source, change_source_id) and hubspot__deal_history. Map them to workflow_transitions with record_id = contact_id, field = field_name, value = new_value and changed_at = valid_from. For source, export the rows to CSV with one raw source column (`change_source || ':' || COALESCE(change_source_id, '')`) and run them through `normalize_history.py long` with `--source-map`, adding `--value-map` for owner fields so owner IDs or names become the same role labels; load workflow_transitions from its output. Never use a raw change_source_id as the source: it can hold a user email.
- Salesforce: the replicated LeadHistory, OpportunityFieldHistory and OpportunityHistory tables. Confirm column names in the replica before mapping.
- Once definitions are agreed, Fivetran's sales-pipeline-analysis skill can compute HubSpot deal metrics from the dbt package models. Give it the definitions from this pack, not its defaults.

## Native report notes

- HubSpot funnels: the legacy builder distinguishes All and Any stage membership; the newer builder (beta) allows optional stages and filters by activity date. Record the builder and filters next to the number.
- HubSpot Date entered properties feed velocity reports only for stages with stage calculated properties turned on; new stages start with them off.
- HubSpot saved views on "Date entered current stage" give aging lists without code.
- Salesforce report types: Leads with converted lead information (filter Converted equals True for converted Leads), Opportunity History, Lead History. A list of converted Leads cannot supply the denominator of a conversion rate; keep the full cohort. ConvertedDate is a date, so elapsed hours need a separate timestamp.

## Reconciliation gate and the classic dispute

Marketing says 500 MQLs for September. Sales says 410.

- Marketing's query: contacts whose first MQL entry falls in September. 500.
- Sales' query: contacts whose lifecycle stage is MQL today and whose MQL date is in September. 410, because 90 of the 500 have already moved on to SQL or beyond.
- Both queries are correct for their question. The pack picks the one that answers the business question (entries, for "how many did marketing produce"), records it in `martech-context/metrics.md` with an effective date, and labels the other "current occupancy".
- The gate: a number is published only with its record ID list, and a reviewer recomputes one number from that list before the pack is accepted.