I've encountered a persistent and critical discrepancy in our data stack that I believe warrants a thorough community discussion. Our primary attribution platform (Platform A) and our enterprise CRM (Salesforce) are reporting fundamentally different numbers for what should be the same core pipeline metrics. The delta isn't a rounding error—we're observing a consistent 22-27% variance in Marketing Qualified Lead (MQL) volume when comparing the weekly aggregates. This is a foundational data integrity issue that undermines budget allocation and ROI calculations.
My initial hypothesis pointed to timing differences in status updates or definitional misalignment. However, after a methodical audit of the data flow and transformation logic, the issue appears more systemic. I've broken down the potential failure points in our current architecture, which is a fairly standard ELT setup:
1. **Source Systems:** Attribution tool (REST API) -> Snowflake (raw). CRM (via Fivetran) -> Snowflake (raw).
2. **Transformation Layer:** dbt models running in Snowflake, applying business logic to unify the datasets into a single `marketing_performance_fact` table.
3. **Key Logic Applied:**
* De-duplication on `contact_id` and `touchpoint_timestamp`.
* Alignment of `opportunity_stage` based on CRM event timestamps.
* Filtering for `MQL_status = TRUE` based on a shared definition (form fill, webinar attendance, etc.).
Despite this, the divergence persists. I've isolated the primary discrepancies to the following areas, ordered by probable impact based on my root-cause analysis:
* **Attribution Window Configuration:** The attribution tool is configured with a 30-day look-back window from the MQL creation date. Our CRM reporting simply sums all touches associated with the contact record, regardless of a time-bound window. This is the most likely source of volume inflation in the CRM numbers.
* **Cross-Device Tracking Gaps:** The attribution tool attempts to stitch sessions, but we have noted a ~15% drop-off in user-level stitching in our analytics warehouse logs. These "unstitched" touchpoints may be credited as net-new leads in the CRM.
* **Data Latency & State Management:** The CRM's `Lead` object updates (e.g., from `Contact` to `MQL`) are reflected in our warehouse with a 4-hour lag. The attribution platform updates in near-real-time. Our dbt model uses a `snapshot` for the CRM data, but there may be a misalignment in the effective observation timestamp.
* **API Extraction Filters:** I suspect a misconfigured filter in the API pull from the attribution platform, potentially excluding touchpoints where `channel = 'Direct'` or `campaign_id IS NULL`.
```sql
-- Example of the diagnostic query I'm running to compare counts
WITH attribution_counts AS (
SELECT
DATE_TRUNC('week', mql_created_date) AS report_week,
COUNT(DISTINCT contact_id) AS attributed_mqls
FROM
refined.attribution_touchpoints
WHERE
mql_created_date >= '2024-01-01'
AND attribution_model = 'first_touch'
GROUP BY 1
),
crm_counts AS (
SELECT
DATE_TRUNC('week', converted_date) AS report_week,
COUNT(DISTINCT id) AS crm_mqls
FROM
snapshots.crm_leads_snapshot
WHERE
status = 'MQL'
AND converted_date >= '2024-01-01'
AND is_deleted = FALSE
GROUP BY 1
)
SELECT
a.report_week,
a.attributed_mqls,
c.crm_mqls,
c.crm_mqls - a.attributed_mqls AS absolute_delta,
ROUND((c.crm_mqls - a.attributed_mqls) / NULLIF(a.attributed_mqls, 0) * 100, 2) AS percent_variance
FROM
attribution_counts a
FULL OUTER JOIN
crm_counts c
ON a.report_week = c.report_week
ORDER BY 1;
```
My immediate next step is to run a deterministic match on a sample of contact IDs that appear in one system but not the other. However, I am keen to learn from the community: **what have been your most effective diagnostic procedures or reconciliation frameworks when facing a similar rift between attribution and system-of-record data?** Specifically, I'm interested in approaches for validating attribution window logic and managing state synchronization in near-real-time pipelines.
-- elliot
Data first, decisions later.