GTM Funnel Analytics
Four dashboards showed four different 'pipeline created' numbers. A dbt layer reconciles them to one canonical figure — and a test fails the build if any gap goes unexplained.
Reconciliation of the "Pipeline Created" metric across five sources, shown as horizontal bars against a canonical reference line at $6.88M. Sales (Salesforce) reports $13.4M, 95% high, applying no exclusions — renewals, partner deals, $0 and duplicate opportunities are all counted. Finance reports $8.3M, 20% high, bucketing by close date and including renewals. The canonical value from this repo is $6.88M — net-new opportunities at Stage 1 or higher with exclusions applied (KPI #5). The data team (Looker) reports $6.3M, 8% low, from inner-joining a broken id crosswalk. Marketing (HubSpot) reports $4.5M, 35% low, counting marketing-sourced only at an MQL threshold of 40 instead of 50. A dbt test fails the build if any gap is left unexplained.
The problem
Ask any two teams for “pipeline created” and you get two numbers. At Arcline, four dashboards showed four — Sales said $13.4M, Finance $8.3M, the data team $6.3M, Marketing $4.5M. Each was computed a slightly different, slightly wrong way, so the metric surfaced in pipeline reviews as an argument instead of an answer. The reconciliation lived in spreadsheets and meetings, never in code, so it never stayed fixed.
The decision
Define each metric once — in a governed catalog tied to the KPI dictionary — implement it once in dbt, and lock the number with a test. One file carries the definition, the filters, and the owner; the same artifact the model computes against. The catalog is the contract; the test is the enforcement. Reconciliation stops being a quarterly debate and becomes something the build checks on every push.
What I built
A dbt project over synthetic Salesforce + HubSpot funnel data, seeded with deliberate defects — duplicate opps, null owners, UTC-vs-local timestamps, an ~8% broken id crosswalk — so the staging models and tests have real work to do. The marts compute the canonical metrics; a reconciliation report recomputes the headline number every team’s way, quantifies each gap, and names the owner of the fix. Around it: multi-touch attribution with a credit-conservation test, golden-value tests that fail the build if a number drifts, and a code-first Evidence.dev dashboard that CI ships to GitHub Pages.
Closing the loop
Every gap has a reason and an owner, and a dbt test fails the build if any gap is left unexplained. Reconciliation becomes a build-time regression test, not a quarterly fire drill — the canonical number can’t quietly drift without someone deciding that it should.
# defined once · locked by a test - name: pipeline_created # KPI #5 definition: net-new · Stage ≥ 1 filters: - opp_type = 'New Business' - is_partner_sourced = false - opp_amount > 0 owner: Sales Ops expected: 6884381.39 # golden value