Financial Reporting Went From Weeks to Minutes
Samuel Martin built a unified attribution warehouse in BigQuery that fused four source systems into one closed-loop model, and cut financial reporting latency from weeks to minutes. Instrumenting the pipeline join by join also settled a two-quarter argument: the finance reconciliation everyone blamed was losing 2% of conversion events, and the CRM join nobody had looked at was losing 38%.
Published 2026-07-25. Last updated 2026-07-25. Roughly an eight minute read.
In one paragraph
Samuel Martin replaced a weeks-long manual attribution close with a unified warehouse in BigQuery, fusing marketing platform streams with clinical and CRM records into one closed-loop model. Reporting latency fell from weeks to minutes. The rebuild made every join countable, and the counts moved the work: the finance reconciliation the organization had been blaming lost 2% of conversion events, while the CRM join lost 38%. Engineering effort moved from the step that was visible to the step that was expensive.
- Minutes
- reporting latency, down from weeks
- 38%
- of events lost at the CRM join
- 2%
- lost at finance reconciliation
- 4
- source systems fused
- 482,000
- conversion events per quarter
01 / The spine
Four steps, in order.
Finance closed each period from a spreadsheet assembled by hand out of four systems, and marketing planned spend against a different number. Leadership was being asked to approve channel budgets on figures that took weeks to produce and disagreed with each other on arrival. The question was not which number was right. It was why nobody could say.
The organization believed the loss was at the finance reconciliation, because that was the only step that produced a visible disagreement. It was the step with a human in it, so it was the step that got blamed. Nothing upstream was counted, so nothing upstream could be suspected.
Rebuilt attribution as an explicit sequence of joins in BigQuery, fusing Marketing API streams with CRM and Electronic Medical Record data into one closed loop. Every join emits a survival count: an event is counted at a stage only if it carries the keys the next stage requires. That turned an opaque pipeline into four numbers anyone could read.
Reporting latency went from weeks to minutes, because the joins became incremental and scheduled rather than assembled by hand. The counts named the real leak. Two of every five conversion events were losing their source at the CRM join, and the reconciliation step everyone had been fixing was losing almost nothing.
02 / The result
Two of every five conversion events lost their source at the CRM join.
Conversion events surviving each join, one quarter, four source systems. Common zero baseline.
Two of every five conversion events lost their source at the CRM join, and the finance reconciliation everyone blamed lost almost nothing. Counts are conversion events over one quarter across four source systems, measured after the unified warehouse was in place.
An event is counted at a stage only if it carries the keys the next join requires, so the bars are survival counts and not a tapered funnel. Figures redrawn on synthetic data. No client named. Method and reasoning are exact.
The bars are drawn as survival counts rather than a tapered funnel on purpose. A funnel graphic implies each stage is a decision someone made; these stages are joins, and an event drops out because it is missing a key. Reading it as attrition would have kept the team optimizing conversion when the problem was identity resolution.
“The reconciliation was never the problem. It was the only step anyone had instrumented, so it was the only step anyone could blame.”
03 / The method, in full
The depth is on this page, not behind a link.
Each heading states its own conclusion, so nothing below requires opening to be understood. Open one when you want the mechanics.
Stage counts are survival counts, which is why the bars do not taper like a funnel.
Each stage counts an event only if it carries the keys the next join requires. An ad platform event with no resolvable session identifier is present at stage one and absent at stage two, and it is absent because of a missing key rather than a lost customer.
A funnel chart would have been the conventional choice and the wrong one. It implies sequential human decisions and invites the reader to optimize conversion between stages. These drops are identity resolution failures, and the fix is upstream key hygiene, not persuasion.
Every stage sits on one common zero baseline so the drops are comparable by length. That is the only way the 38% step reads as larger than the 15% step without the reader doing arithmetic.
Fusing marketing streams with clinical records is a key problem, not a volume problem.
The warehouse joins Marketing API event streams to CRM leads and to Electronic Medical Record encounters. Volume was never the constraint; BigQuery absorbs the counts without comment. The constraint is that each system identifies a person differently, and only some of those identifiers survive into the next system.
Samuel Martin built the join keys as an explicit resolution layer rather than burying them in the query that needed them. That layer is the reason a stage count exists at all: if the key logic lives inside one report, no other report can measure how often it fails.
The closed loop matters for the same reason. Spend enters from the platform side and value returns from the clinical and finance side, so a channel decision can be evaluated against outcomes rather than against clicks. That loop is what made the latency number worth chasing.
Latency fell from weeks to minutes because the joins became incremental, not because the warehouse was faster.
The old close was a person assembling four extracts, reconciling them by hand, and explaining the differences in a meeting. Elapsed time was dominated by coordination, not computation, which is why buying more compute had not helped in two previous attempts.
The rebuild made each join incremental and scheduled, so the expensive work happens continuously on new events instead of all at once at period end. The reported number is available minutes after the source systems land rather than weeks after the period closes.
The honest framing is that minutes is the pipeline latency, not the governance latency. Numbers still get reviewed before they are used for a spend decision, and that review is now the slowest step. Samuel Martin reported it that way rather than claiming the review had disappeared.
04 / Limits
What this does not show, and what I would do differently.
Limits
The stage counts describe one quarter and four source systems. A different quarter with a different platform mix will move the drops, and the 38% is a fact about this pipeline at this time rather than a benchmark.
The warehouse measures where events lose their keys. It does not establish that a recovered event would have been attributed correctly, only that it could have been attributed at all.
What I would do differently
Instrument the joins before rebuilding them, not during. The counts were the most valuable output of the project and they arrived late enough that a quarter of engineering effort had already gone to the wrong step.
Publish the stage counts as a standing dashboard rather than a project artifact. Identity resolution degrades quietly as source systems change, and a number nobody watches is the failure mode this project existed to fix.