From Metric Chaos to Marketing Clarity

The problem was not that data was missing. The company was running approximately 400 active campaigns across Meta and Google, spending $280K per month, with data flowing from four source systems: Meta Marketing API, Google Ads API, Salesforce, and Google Analytics 4. Every platform was generating numbers. The problem was that every platform was also taking full credit for the same revenue.

Finance calculated CAC as total ad spend divided by deals closed that month: $11,200 per deal. Sales calculated it as total ad spend divided by pipeline generated: $890 per opportunity. Marketing calculated it as ad spend divided by form submissions: $72 per lead. All three were arithmetically correct. All three were measuring different things. When a budget cycle arrived and the allocation decision needed to be defensible to a board, nobody in the room trusted the figure they were being asked to optimise toward.

The discrepancies ran deeper than CAC. Meta reported 15,240 leads for one month; Salesforce showed 8,600 leads created in the same period. Google Ads reported 8,430 conversions; the CRM showed 2,190 completed form submissions. Forty percent of opportunities in Salesforce had no campaign source data recorded at all. And 42% of leads had no UTM parameters captured, meaning nearly half of all acquisition activity was invisible to any attribution model.

We started where every attribution project should start — by agreeing the model before writing any code. What counts as a touch. How credit distributes across a multi-touch journey. What the sales cycle lag means for attribution windows. For a B2B SaaS company with a 47–89 day sales cycle, attributing spend only to same-month closes severely undercounts true CAC. We agreed on a two-month lag window: spend in January attributed to closes through March. These were not technical decisions. They were business decisions the technical implementation had to reflect.

Once the model was agreed on, we built the pipeline. Source ingestion from Meta, Google Ads, Salesforce, and GA4 into BigQuery on GCP, with daily refresh schedules calibrated to each API's realistic latency — Meta and Google at T+1 to T+2, Salesforce at T+0 to T+1. The attribution logic ran from the unified dataset, not from each platform's own reporting. We implemented confidence scoring for lead matching: 95% for exact UTM match, 68% for Salesforce campaign mapping, 60% for source-based inference, 0% for unmatched. We did not hide the unmatched volume — we surfaced it as a known gap the business needed to close.

Three data quality problems surfaced during integration that changed the shape of the solution. Meta had retroactively applied iOS 14.5+ privacy adjustments to 18 months of historical data, creating a 28% variance in historical conversion counts. We re-ingested all 18 months after the platform stabilised; variance reduced to under 2%. Salesforce's lead count discrepancy traced partly to bot traffic and spam submissions triggering the Meta pixel from non-campaign forms — we implemented an allowlist of valid form IDs, reducing false positive CPL by 22%. And UTM capture was at 58% because sales was not required to capture UTMs at lead creation. Adding a mandatory field to the Salesforce lead creation form improved capture to 72% within 30 days.

The dashboards gave each team the view they needed from the same underlying source of truth. An operational dashboard refreshing hourly from Salesforce for intraday visibility. A reporting dashboard refreshing daily at 4 AM once ad platform data settled. An executive dashboard updating weekly for trend analysis. Real-time was not technically possible given API latency constraints — we designed around the actual data freshness available rather than pretending otherwise.

The results that emerged from a single source of truth changed decisions immediately. Google Search had a 4.2x ROAS versus Meta's 1.8x ROAS for this company's product and audience. $50K per month was reallocated from Meta to Google in month one. Three campaigns that had appeared to perform well in platform reporting showed negative contribution once unified attribution was applied. Budget was reallocated within the first reporting cycle. Finance and marketing aligned on a shared CAC definition that held through two subsequent planning cycles.

What we delivered: Cloud-native marketing analytics pipeline on GCP connecting Meta Ads API, Google Ads API, Salesforce, and GA4 into BigQuery. Unified attribution model agreed across marketing, sales, and finance with documented assumptions and confidence scoring. Three-tier dashboard suite in Power BI — operational, reporting, and executive — with daily automated refresh and data quality monitoring. UTM capture improvement from 58% to 72% within 30 days.

The outcome: Monthly reporting cycle reduced from 3–4 weeks to 2–3 days after month-end. Campaign optimisation cycle from two weeks to 48 hours. $50K per month reallocated in month one based on unified data. One agreed CAC definition replacing three conflicting calculations. 16 weeks from engagement start to production deployment.