HubSpot multi-touch attribution: native reports vs. a warehouse model

HubSpot’s built-in attribution reports are a useful starting point for which efforts create contacts and deals. They get harder to rely on when the question depends on data HubSpot cannot see, or on rules your team needs to read and change.

What HubSpot attribution reports do

HubSpot has three attribution reports. Each answers a different question about the same set of tracked interactions.

The reports count these interaction types:

• Ad clicks on Facebook, Instagram, LinkedIn, or Google. The ad accounts must be connected.
• Marketing event registration and attendance.
• Connected calls. The calls must be logged on the contact.
• CTA clicks.
• Form submissions.
• Marketing email clicks. Only clicks to the contact’s primary email count.
• Page views on pages that carry the HubSpot tracking code.
• Social post clicks. The social accounts must be connected.

Each interaction is also placed at one of four positions:

• First interaction.
• Lead create: the last interaction before the person became a contact.
• Deal create: the last interaction before a deal was associated with the contact.
• Deal closed-won: the last interaction before the associated deal moved to closed-won.

Every interaction counts separately. Three clicks on one email are three interactions, not one.

The attribution models HubSpot offers

The report builder offers five models:

Where native HubSpot attribution falls short

• It only sees what HubSpot tracks. Pages without the tracking code, unconnected ad platforms, untracked offline activity, and data in other systems such as a separate CRM, POS, or billing tool all sit outside the model. Ad spend from platforms you have not connected is missing too.
• Deals need associated contacts. A deal only gets credit from contacts associated with it.
• Sampling applies at volume. Reports include up to 20 million interactions after sampling. In very high-volume accounts, high-frequency types such as email interactions and page views are sampled, and up to 100,000 interactions per deal are used. HubSpot recommends non-attribution reports for exact activity counts.

When to build multi-touch attribution in a warehouse

A native report is enough when it answers the question and the data behind it is complete. A warehouse model is worth building when:

• The attribution rules need to be readable and auditable by people outside the CRM team.
• The same rules must apply across HubSpot, ad platforms, and GA4.
• Ad spend and GA4 sessions need to sit next to CRM revenue.
• You want revenue attribution without depending on a specific HubSpot tier.
• You want to own the history of the data.

Building HubSpot attribution in BigQuery

The model has five steps. The table names in the SQL are illustrative. Map them to your own dataset before running anything.

1. Sync HubSpot into BigQuery

Land these HubSpot objects in a BigQuery dataset: contacts, deals, deal–contact associations, form submissions, and marketing email events. Maven can sync these into BigQuery, and any connector that lands them in a dataset will work.

2. Capture source on every form

Store UTM parameters and click IDs on the contact as hidden form fields or contact properties, so the source survives until the contact reaches HubSpot. Capture the GA4 client ID the same way, because step 3 joins web sessions on it. The beginner’s guide to offline conversions covers the capture basics.

3. Build a touchpoints table

The touchpoints table has one row per marketing interaction, tied to a HubSpot contact. Save this query as a table named touchpoints.

SELECT contact_id, submitted_at AS touch_ts, 'form_submission' AS touch_type,
       utm_source AS source, utm_medium AS medium, utm_campaign AS campaign
FROM hubspot_form_submissions
UNION ALL
SELECT contact_id, clicked_at, 'email_click',
       'hubspot', 'email', email_campaign_name
FROM hubspot_email_clicks
UNION ALL
SELECT c.contact_id, s.session_start, 'web_session',
       s.source, s.medium, s.campaign
FROM ga4_sessions AS s
JOIN hubspot_contacts AS c
  ON c.ga_client_id = s.client_id

The GA4 join only works if the GA4 client ID is captured on the form and stored on the contact. Without it, the web sessions in this query have no path to a contact.

4. Apply attribution models to closed-won deals

This query joins closed-won deals to the touchpoints of every contact on the deal, up to the close date. Touches from all associated contacts count, which suits buying committees. Three columns apply three models:

• first_touch_revenue gives the full deal amount to the earliest touchpoint.
• last_touch_revenue gives the full deal amount to the latest touchpoint before close.
• linear_revenue splits the deal amount equally across all of its touchpoints.

WITH deal_paths AS (
  SELECT
    d.deal_id,
    d.amount,
    t.source,
    t.medium,
    t.campaign,
    ROW_NUMBER() OVER (PARTITION BY d.deal_id ORDER BY t.touch_ts, t.touch_type) AS touch_n,
    COUNT(*)     OVER (PARTITION BY d.deal_id)                                   AS touches
  FROM hubspot_deals AS d
  JOIN hubspot_deal_contacts AS dc
    ON dc.deal_id = d.deal_id
  JOIN touchpoints AS t
    ON t.contact_id = dc.contact_id
   AND t.touch_ts <= d.closed_at
  WHERE d.is_closed_won
)
SELECT
  source,
  medium,
  campaign,
  SUM(IF(touch_n = 1, amount, 0))       AS first_touch_revenue,
  SUM(IF(touch_n = touches, amount, 0)) AS last_touch_revenue,
  SUM(amount / touches)                 AS linear_revenue
FROM deal_paths
GROUP BY source, medium, campaign
ORDER BY linear_revenue DESC

Each model column sums to the same total: the closed-won revenue of deals with at least one touchpoint. Deals with no touchpoint on or before the close date drop out of the join, so compare that total against all closed-won revenue and document the difference.

5. Report and sanity-check

Point Looker Studio or a Maven report at the output of step 4. Before you rely on it, compare the totals to HubSpot’s native attribution report for the same period and models. Expect differences. The native report uses its own sampling and rules, and this model adds GA4 sessions. Write each difference down with its cause rather than forcing the totals to match.

Before you trust the numbers

1. 1Every form captures UTMs, click IDs, and the GA4 client ID, and stores them on the contact.
2. 2Each deal is associated with every contact who should get credit, so buying committees are counted.
3. 3Closed-won deals with no matching touchpoint are counted and reviewed, since the join drops them.
4. 4Each model total is compared with all closed-won revenue, and the gap is explained.
5. 5Totals are compared with HubSpot’s native report, and each difference is documented with its cause.

For the web side of the model, read the GA4 multi-touch attribution guide for BigQuery. To understand the gaps you will see between the two systems, read why HubSpot and GA4 numbers don’t match. The MTA technical guide covers the underlying methods. If you want help scoping this for your accounts, book a tracking audit.