Marketing Data Infrastructure: ETL, Analytics and CRM

Key takeaways

• Define the grain: an event, submission, contact and opportunity are different records.
• Choose ETL or ELT around data handling and operational constraints, not a universal recommendation.
• Reconcile source totals and test joins before trusting a dashboard.
• Separate a successful sync from a qualified commercial outcome.

What belongs in a marketing data stack?

Collection records interactions; ingestion moves approved data; the warehouse stores history; transformation applies documented definitions; activation sends an eligible result to an operational system. A CRM remains the operational home for contacts and sales follow-up. A warehouse is useful when historical joins and multiple sources exceed what a reliable export can answer.

For a small team, a versioned export and a reproducible reconciliation can be a better starting point than a new warehouse. Add infrastructure when repeatability, scale or access requirements justify its maintenance.

Marketing ETL versus ELT: where should transformation happen?

ETL transforms before loading into the destination. ELT loads first and transforms there. ELT can preserve replayable source history and make SQL transformations inspectable. It also means sensitive or unnecessary data may reach the warehouse unless it is filtered earlier. ETL can enforce minimization before storage, but changing the transformation may require another extraction.

For an aggregate campaign report, load only the required dimensions and metrics. For contact-level integration, agree permitted identifiers, access, retention and deletion propagation first. Do not place email addresses, form-message text or credentials into general analytics events.

Before selecting a managed connector or building an API integration, check pagination, rate limits, historical backfill, incremental cursor semantics, schema changes, removed records and recovery after partial failure. A green connector status confirms execution; it does not prove that every relevant source record arrived.

A minimal pipeline for analytics, campaigns and HubSpot

1. Define the question: which campaigns produced enquiries that the team accepted for follow-up?
2. Separate source tables: campaign spend at date/campaign/currency grain; form deliveries at submission grain; CRM contacts at contact grain; qualification history at status-change grain.
3. Store extraction context: source, extraction time, reporting timezone, coverage window and schema version. Preserve event time separately from ingestion time.
4. Build a permitted mapping: connect a submission to a CRM contact using the integration's acknowledgement. Do not infer person identity from an IP address or merge people because they share a company.
5. Publish a curated report: show eligible submissions, resolved mappings, qualification coverage and spend separately. Leave unresolved records visible.

A contact can submit twice; an existing contact can create a new enquiry. Counting newly created contacts as submissions therefore undercounts repeat enquiries. Browser analytics can also have a different observable population because of consent and blocking. Do not force these counts to match by silently dropping records.

Use the marketing data contract guide to document definitions before implementation. If tracking is being replaced, use the paired-event migration method to compare the two implementations.

Worked example: a delivered form is not a new contact

Synthetic example: 100 delivery attempts include 82 successful responses. After deduplicating retries by submission identifier, those responses represent 75 submissions. The integration maps them to 69 newly created contacts because some submissions reuse a contact. These are three different denominators, not contradictory dashboards.

The delivery-response success rate is 82/100 = 82%. It does not establish the share of distinct submissions delivered: that requires the count and final status of all distinct submitted identifiers, including failed ones. Nor does it establish a qualified-lead rate; qualification needs a separate observed outcome and an agreed follow-up window.

The Growth Measurement Lab provides a dependency-free, locally tested reconciliation example using synthetic records and explicit denominator rules. It does not call your CRM or upload customer data. Its tests cover duplicate exports, retries, conflicting identifiers and empty populations.

For advertising delivery rather than form delivery, use the offline-conversion reconciliation guide. A platform's acceptance of an uploaded conversion still does not prove attribution or incrementality.

Transformation tests that catch expensive reporting errors

Before a join, write down whether it is one-to-one, one-to-many or many-to-many. Joining contacts directly to both orders and support tickets can multiply rows and revenue. Aggregate each child table to the intended grain before joining, then compare row counts and totals against the source.

• Keys: check required identifiers and uniqueness at the intended grain.
• Relationships: quantify unmapped submissions and orphaned foreign keys.
• Accepted values: flag unexpected status codes rather than recoding them as success.
• Financial semantics: retain currency, refunds, tax treatment and the reporting window. Observed revenue is not predicted lifetime value.
• Missingness: a missing satisfaction score is unknown, not zero. A missing cost must not become a free acquisition.
• Late arrivals: rerun an explicit lookback window and document when a period becomes sufficiently complete for a decision.

dbt supports reusable data tests such as uniqueness, not-null, accepted values and relationships; custom tests can express additional business assertions. They need to be configured and run, rather than assumed to exist because a project uses dbt. See the official dbt data-test documentation.

Warehouse selection and cost controls

Evaluate BigQuery, Snowflake, Redshift or an existing platform against your actual workloads, access requirements, region and team skills. Compare the complete operating cost: storage, query or capacity usage, ingestion, connector charges, transformation jobs, egress and maintenance. There is no universal monthly price for a marketing team.

For BigQuery, estimate a query before execution and use appropriate cost controls. With on-demand queries, limiting returned rows does not necessarily limit the data read. Filter relevant partitions and select needed columns; assess the impact using your actual query plan and billing model. Google's cost-control guidance explains dry runs, maximum bytes billed and relevant limitations.

Benchmark a representative daily report, a historical backfill and a recovery run. Include failure recovery in the estimate. Moving a job to a different hour is not automatically a cost saving; the result depends on capacity, concurrency and pricing.

Activation, monitoring and a safe handover

Reverse ETL moves a curated result back to an operational system. Treat it as a write with consequences. Test an allowlisted cohort, check suppression at execution time, record what changed and prevent retries from creating duplicate actions. A segment label is not permission to send a campaign.

Monitor freshness, source-to-destination coverage, rejected records, unexpected schema changes, join expansion and business-total reconciliation. An alert needs an owner and a runbook: what was affected, whether outputs should be paused, and how to recover without duplicating writes.

Deliver a metric dictionary, source map, access register, test fixtures, release notes and recovery procedure. Agree how to handle revised source data and deleted records. Review system reliability separately from commercial impact: a perfectly scheduled pipeline may still answer the wrong question.

Choose the next step for your team

• Growth Bottleneck Diagnostic: establish which definitions or evidence are missing before commissioning a rebuild.
• Forward-deployed marketing implementation: connect the agreed data, APIs and workflows with evaluation and operating controls.
• Fractional CMO leadership: align the measurement roadmap with priorities, owners and commercial decisions.

Start with one decision and the smallest dependable implementation. No stack guarantees lower acquisition costs, better attribution or revenue growth simply by being installed.