← Measured AI

Production

Marketing ledger

  • Next.js
  • Vercel
  • n8n
  • Google Sheets
  • Salesforce

The problem

A colleague built the first version of this dashboard. It reads a Google Sheet that an n8n workflow fills from Salesforce every day. In September 2026 a manager flagged 49 opportunities it called "Unlinked", meaning no known person behind them. I audited it, and I wrote nearly all the code after that first version.

  • It had only two ways to link an opp to a person. A lead converted straight into that opp, or a same-domain lead created up to 160 days before it. It never looked at contacts.
  • The first route almost never fired. Reps converted leads without creating an opp, so only 46 of 5,551 converted leads pointed at one.
  • The people were there all along. Of the 50 flagged opps, 32 had contact roles and 45 had contacts on the account. Only 1 had nobody.
  • The sheet was incomplete. It held only leads created since mid-2025 (5,750 of 15,969), and records deleted in Salesforce stayed in it.

Constraints

  • It was someone else's live tool. I confirmed my copy matched the deployed code byte for byte and kept the old deploy as a rollback.
  • The warehouse is a Google Sheet, and Vercel caps a function response at about 4.5 MB.
  • n8n holds a run's data in memory until the run ends.

How it works

  1. Daily sync. At 6:00 IST, n8n pulls all opps, leads, contacts and domain aliases. It counts each object in Salesforce first. If a pull comes up short, it stops before touching the sheet. The first live run matched Salesforce exactly: 663 opps, 15,971 leads, 10,521 contacts, in 67 seconds.

  2. One tab per request. Each tab is trimmed to the columns the dashboard uses and cached for 10 minutes. The largest is 1.86 MB.

  3. A 5-route source-person linker. For each opp, it tries these routes in order and stops at the first hit:

    1. A lead converted into this opp.
    2. Someone in a contact role on the opp.
    3. A contact on the opp's account.
    4. A lead attached to that account.
    5. A same-domain lead created 0–160 days before the opp.

    Routes 1–2 count as Direct and routes 3–5 as Account/domain. If no route hits, the opp is Unlinked. When a route finds several people, it picks the most recent one created on or before the opp.

  4. Attribution. First-touch gives all of an opp's value to the earliest marketing touch before the opp was created. Last-touch gives it all to the latest. Even-split shares it equally among every touch. The first version offered those three views, matching touches to opps by domain. In November I built an Attribution tab where credit follows people instead. Each linked person brings their first lead source within a 3-, 6-, 12- or 24-month window. The opp's value is split evenly across lead sources, then origins, then campaigns, so totals add up at every level. It matched the analysis sheet on 1,847 rows across 154 opps with 0 differences.

  5. Inbound SLA and cohorts. The clock starts at the lead's first demo or product form fill. It's due one business hour into the next 9–5 ET window. It counts as met if a BDR calls, emails or messages them on LinkedIn, or books a meeting, by then. The Pipeline tab compares each month's cohort with the 6-month average and with plan.

  6. Deploys. Every merge to main runs self-checks on the attribution and inbound logic before production deploys. The checks include 7 worked clock examples copied from the Salesforce cadence tests.

n8n pulls Salesforce daily into a Google Sheet after a count check; a per-tab API serves the ledger, which shows Pipeline, Inbound and Attribution tabs; merges to main pass self-checks before deploying.

From Salesforce to the dashboard

Receipts

Before
49 of 105 opps Unlinked · 7 Direct
Action
5-route linker, per-tab API, sync rebuilt
After
1 of 104 Unlinked · 71 Direct
Verified by
Live dashboard after a sync that matched Salesforce counts

What broke, and what I changed

  • The page showed stale numbers. The old API returned every tab in one 15.6 MB response, over Vercel's limit, so the page never refreshed after its first build. Splitting it into one tab per request fixed that.
  • An old copy overwrote production. A later deploy built from an old local copy removed the linker. I moved that deploy's one real change onto main and redeployed. Then I added CI, so production always runs main.
  • The sync crashed twice in November and sent no alert. One run held about 92 MB of data. n8n marked it "crashed" rather than "error", and only errors trigger the alert. Now leads and contacts sync in batches of 2,000, each in its own sub-workflow. The main run uses 0.8 MB, and the biggest sub-workflow 24.5 MB. Old and new code gave 0 differences on the same data. A failed batch now fails the whole run, which does alert.

What I'd do next

  • Keep one copy of the shared rules. Batching left the test-record filter and channel map copied into three n8n steps. Today, a rule change has to touch all three and then pass the replay check.
  • Fix the 17 opps with no contact role found in the audit. That's a Salesforce data fix, not code.

Want systems like these at your org?

Message me on LinkedIn