Article content and detailed guides remain in English. The selected language applies to controls and quick instructions.

Back to articles

List management

Using Data Warehouses for Email Marketing Analytics: BigQuery, Snowflake, Redshift and Databricks

By Email ExtractorPublished 6 min read
On this page

Why Email Analytics Belongs in a Data Warehouse

Email service providers (ESPs) report on what happens inside the email channel: opens, clicks, bounces, unsubscribes. But the questions marketers actually need to answer require combining email data with data from other systems:

Question Data needed ESP alone? Warehouse needed?
What is the open rate for Campaign X? Email events Yes No
Which email campaign drove the most revenue? Email events + orders No Yes
What is the lifetime value of subscribers acquired via email vs paid? Email events + orders + acquisition source No Yes
Which email content type correlates with higher retention? Email events + product usage + churn data No Yes
How does email engagement predict deal close rate? Email events + CRM pipeline No Yes
What is the incremental lift of email vs control group? Email events + holdout data + orders No Yes
Which customer segments should receive which email frequency? Email events + demographics + orders + product usage No Yes

Data sources to combine

Source Data How it connects to email
ESP (Mailchimp, Klaviyo, Brevo, etc.) Sends, opens, clicks, bounces, unsubscribes Primary email event data
CRM (Salesforce, HubSpot, etc.) Contacts, deals, pipeline stages, revenue Email-to-revenue attribution
E-commerce platform (Shopify, WooCommerce) Orders, products, revenue, returns Email-to-purchase attribution
Product analytics (Mixpanel, Amplitude, Segment) Feature usage, activation, retention Email impact on product engagement
Website analytics (GA4, Snowplow) Page views, conversions, attribution Email-driven website behaviour
Customer support (Zendesk, Freshdesk) Tickets, satisfaction, resolution Email impact on support volume
Subscription billing (Stripe, Chargebee) MRR, churn, upgrades, downgrades Email impact on revenue retention

Data Warehouse Options

Warehouse Best for Pricing model Email analytics strengths
BigQuery (Google Cloud) Google ecosystem users; event-level analytics Per-query (pay for data scanned) Native GA4 integration; good for event data; generous free tier
Snowflake Multi-cloud; data sharing; high concurrency Compute + storage (separated) Data marketplace; easy data sharing; strong for B2B
Amazon Redshift AWS ecosystem users; traditional BI Per-node (provisioned) or serverless Native AWS integrations; Redshift Spectrum for S3 data
Databricks ML-heavy analytics; streaming data Compute + storage Best for ML models on email data; real-time processing

Architecture overview

Layer Purpose Tools
Data extraction Pull data from ESPs, CRMs, e-commerce Fivetran, Airbyte, Stitch, custom APIs
Data warehouse Store and query combined data BigQuery, Snowflake, Redshift, Databricks
Data transformation Clean, model, join data sources dbt (data build tool)
BI / visualisation Dashboards, reports, ad hoc analysis Looker, Tableau, Metabase, Power BI
Reverse ETL Push insights back to ESP, CRM Census, Hightouch, RudderStack

Key Email Analytics Models

Email-to-revenue attribution

Attribution model How it works Best for Limitation
Last-touch email Revenue credited to the last email clicked before purchase Simple reporting Ignores email nurture; overcredits final email
First-touch email Revenue credited to the first email engagement Understanding acquisition Ignores conversion emails
Linear (multi-touch) Revenue split evenly across all email touches Fair distribution Treats all touches as equal
Time-decay More recent emails get more credit Balancing recency and history Arbitrary decay rate
Incremental (holdout) Compare emailed group vs control group True causal impact Requires holdout group (lost revenue)

Engagement scoring in the warehouse

Signal Points Decay Rationale
Email open 1 Halves every 30 days Low-intent signal; privacy issues with open tracking
Email click 5 Halves every 30 days Medium-intent signal
Email reply (if tracked) 10 Halves every 30 days High-intent signal
Purchase after email click 20 Halves every 60 days Revenue-generating action
Unsubscribe from category -10 No decay Negative signal for that category
Spam complaint -50 No decay Strong negative signal

Cohort analysis queries

Analysis What it answers SQL approach
Signup cohort retention "Do subscribers acquired in January engage longer than March?" Group by signup month; measure engagement by month since signup
Email frequency cohort "Do subscribers who receive 2 emails/week engage more than 4/week?" Group by email frequency bucket; compare click and purchase rates
Content type preference "Which content types drive higher LTV by segment?" Group by primary content type engaged; join with revenue data
Channel comparison "How does email LTV compare to paid, organic, referral?" Group by acquisition channel; join with all revenue data
Reactivation effectiveness "What percentage of reactivated subscribers become active long-term?" Group by reactivation campaign; measure sustained engagement

Common Transformations

dbt model examples

Model Purpose Key logic
stg_email_events Standardise raw ESP event data Deduplicate events; standardise event types; parse UTM parameters
stg_orders Standardise e-commerce order data Parse order line items; handle refunds and cancellations
int_email_attribution Join email events to orders Match email clicks to subsequent orders within attribution window
int_subscriber_engagement Calculate engagement scores Rolling engagement score per subscriber; segment classification
mart_email_campaign_performance Campaign-level metrics Revenue, ROI, engagement by campaign; comparison to benchmarks
mart_subscriber_lifetime Subscriber-level lifetime metrics LTV, engagement trend, predicted churn, optimal frequency

Metrics Dashboard

Campaign-level metrics (warehouse-powered)

Metric Formula ESP provides? Warehouse adds
Send volume Count of sends Yes Historical trend; comparison
Delivery rate Delivered / sent Yes By segment, domain, content type
Open rate Opens / delivered Yes Adjusted for privacy (MPP); by segment
Click rate Clicks / delivered Yes By content type; by link position
Click-to-open rate Clicks / opens Yes Trend analysis; A/B comparisons
Conversion rate Conversions / clicks Partial Full attribution with revenue data
Revenue per email Total revenue / emails sent Partial Multi-touch attribution; incremental
Revenue per subscriber Total revenue / active subscribers No Subscriber-level LTV
Unsubscribe rate Unsubscribes / delivered Yes By send frequency; by tenure
List growth rate (New - unsubscribed - bounced) / total Partial Net growth with churn analysis

Subscriber-level metrics (warehouse-only)

Metric What it measures Business use
Email-attributed LTV Lifetime revenue from email-influenced purchases Justify email investment
Engagement velocity Rate of engagement change (accelerating or decelerating) Predict churn; trigger re-engagement
Optimal send frequency Frequency that maximises engagement without increasing unsubscribes Personalise send cadence
Cross-channel influence How email engagement correlates with other channel engagement Multi-channel strategy
Time-to-first-purchase (email) Days from first email engagement to first purchase Benchmark nurture effectiveness

Preparing Email Data for Warehouse Ingestion

When consolidating email data from multiple ESPs (during migrations or multi-brand operations), CRM exports, e-commerce platform exports and legacy email system archives, you may need to extract and standardise email addresses across all data sources before loading into your warehouse. Upload exports (CSV, JSON, XML) to Email Extractor to extract and deduplicate email addresses across all systems. A clean, deduplicated subscriber identifier is the foundation of accurate warehouse analytics: duplicate email records across systems create inflated counts and incorrect attribution.

Extract emails

Explore tools

Verify emails

Check address validity before using your list.

ZeroBounce

Email Verification

Verifies email lists and provides tools for monitoring deliverability.

Useful when list cleaning and sender health belong in one workflow.

Explore ZeroBounce (opens in a new tab)