How to Connect CRM Data to Google BigQuery for Marketing Reports

Connecting your CRM to Google BigQuery closes the gap between marketing activity and business results. Instead of reporting on clicks and conversions, you report on leads, pipeline, and revenue. You see which campaigns generate customers, not just form submissions.

Here is how to connect HubSpot or Salesforce to BigQuery and build a full-funnel marketing report.

Why CRM Data Changes Your Marketing Reports

Ad platforms report conversions based on their own attribution models. Meta counts a conversion if someone saw your ad and converted within 7 days. Google counts a conversion if someone clicked your ad and converted within 30 days. These numbers overlap, and they do not tell you what happened after the conversion.

Your CRM knows what happened after the conversion. It tells you which leads became qualified opportunities, which opportunities became customers, and how much revenue each customer generated.

When you join your CRM data with your ad data in BigQuery, you see the full picture: which campaigns generate the leads that actually close, and at what cost.

What CRM Data to Send to BigQuery

From HubSpot or Salesforce, the most useful data for marketing analysis includes:

Contacts: lead source, creation date, lifecycle stage, owner.

Deals: deal value, stage, close date, associated contact.

Activities: emails sent, calls made, meetings booked.

UTM parameters: the campaign, source, and medium that brought each lead in (if you capture UTMs in your forms).

For marketing attribution, the most critical fields are lead source, UTM campaign, creation date, and deal value.

Step 1: Connect Your CRM to BigQuery With Porter Metrics

In Porter Metrics, create a new query and select HubSpot (or Salesforce) as your data source. Authenticate with your CRM credentials.

Select the objects and fields you want to sync. For a marketing attribution report, select contacts with their lead source, UTM fields, creation date, and lifecycle stage. Also select deals with their value, stage, close date, and associated contact ID.

Set your write mode to “update” and schedule daily syncs. Porter loads your CRM data into BigQuery automatically.

Step 2: Join CRM Data With Ad Performance Data

Once both your CRM data and your ad data are in BigQuery, you join them on the UTM campaign field.
SELECT
ads.campaign_name,
SUM(ads.spend) AS total_spend,
COUNT(crm.contact_id) AS total_leads,
COUNT(CASE WHEN crm.lifecycle_stage = ‘customer’ THEN 1 END) AS total_customers,
SUM(crm.deal_value) AS total_revenue,
ROUND(SUM(ads.spend) / NULLIF(COUNT(crm.contact_id), 0), 2) AS cost_per_lead,
ROUND(SUM(ads.spend) / NULLIF(SUM(crm.deal_value), 0), 2) AS true_roas
FROM your_dataset.meta_ads AS ads
LEFT JOIN your_dataset.hubspot_contacts AS crm
ON ads.campaign_name = crm.utm_campaign
WHERE ads.date BETWEEN ‘2026-01-01’ AND ‘2026-01-31’
GROUP BY ads.campaign_name
ORDER BY total_spend DESC
This query shows you true ROAS (total revenue from CRM divided by total ad spend) for each campaign. It also shows cost per lead and how many leads from each campaign became customers.

Step 3: Build a Full-Funnel Marketing Report

A full-funnel marketing report shows every stage from ad impression to closed revenue. In BigQuery, you build this by joining multiple tables.

The funnel stages:

Ad impressions and clicks (from your ad platform tables).

Leads created (from your CRM contacts table).

Qualified leads (leads that reached the MQL or SQL lifecycle stage).

Opportunities (deals created in your CRM).

Customers (deals closed as won).

Revenue (deal value from closed-won deals).

With all of this data in BigQuery, you write a single query that shows every stage of the funnel, broken down by campaign or channel. You see where leads drop off and which campaigns drive the most revenue, not just the most leads.

Step 4: Build Your CRM Marketing Dashboard in Looker Studio

Connect Looker Studio to your BigQuery tables. Build a report with:

Scorecards for total leads, total customers, cost per lead, and true ROAS.

A funnel visualization showing the conversion rate at each stage.

A table showing campaign performance from lead to revenue.

A date range filter to adjust the reporting period.

This dashboard gives your marketing team and leadership a single view of marketing performance from first touch to closed revenue.

Why This Analysis Matters

Platform-reported ROAS is a starting point, not a conclusion. It tells you what the platform thinks happened. CRM data tells you what actually happened in your business.

When you connect your CRM to BigQuery alongside your ad data, you make budget decisions based on actual revenue, not estimated conversion values. That is the difference between marketing reporting and marketing intelligence.