Unified Marketing Intelligence

Blended performance

Revenue and media spend

Revenue Spend

Attributed revenue$1.84M

Media investment$436K

4.22× ROAS

Channel efficiency

Where growth comes from

Revenue influence

Top converting customer journeys

Unified view

Journey	Customers	Conv. rate	Revenue	Avg. days

Complete customer journey

One customer. Every interaction.

Ad clicks, website behavior, email engagement, and CRM revenue joined into a single timeline.

$1.84Mrevenue connected

Connected performance

Every growth channel in one view.

Compare customer, campaign, content, page, and social results using consistent revenue outcomes.

Revenue influenced$1.84M+18.4% this period

Automated BigQuery architecture

12 sources. One trusted model.

Every source is normalized, deduplicated, and stitched to a durable customer key before metrics reach the dashboard.

Reporting cycle3 days → 8 hours67% faster

Source systemsBigQuery transformationBusiness output

1.8M rows/day→

01

NormalizeCommon campaign + event schema

02

Resolve identityEmail, CRM ID + click IDs

03

Attribute revenuePosition + data-driven models

Every 30 min→

◆

Customer 360One row per customer

▥

Executive reportingConsistent KPIs

◉

Audience activationHigh-intent segments

✓Freshness8 min behind source

✓ConsistencyShared business definitions

✓Deduplication42K duplicate rows removed

✓AlignmentOne view across teams

Production pattern

From fragmented events to a unified customer view.

The query uses layered CTEs to standardize source schemas, create durable customer identities, sequence touchpoints, and allocate revenue.

01

Union channel events

Convert every platform into the same event grain.

02

Build identity graph

Link anonymous click IDs to known CRM contacts.

03

Sequence journeys

Order all touchpoints before each conversion.

04

Allocate value

Apply attribution weights and reconcile revenue.

Download full BigQuery SQL ↓

unified_customer_view.sql

WITH paid_media AS (
  SELECT
    event_timestamp,
    'google_ads' AS source,
    campaign_id,
    gclid AS click_id,
    cost,
    conversions
  FROM `demo.raw.google_ads`

  UNION ALL

  SELECT
    event_timestamp,
    'meta_ads' AS source,
    campaign_id,
    fbclid AS click_id,
    spend AS cost,
    purchases AS conversions
  FROM `demo.raw.meta_ads`
),

identity_map AS (
  SELECT
    COALESCE(crm.contact_id, web.user_id) AS customer_id,
    web.click_id,
    crm.email_hash
  FROM `demo.raw.web_events` web
  LEFT JOIN `demo.raw.crm_contacts` crm
    ON web.email_hash = crm.email_hash
)

SELECT * FROM unified_journeys;