How to Build a Multi-Touch Marketing Attribution Model

Consider a company spending $40,000 a month across Google Ads, Facebook, email campaigns, and organic content. The figures in this article are illustrative, but the reporting problem is common: the team allocates budget according to whichever channel Google Analytics credits with the conversion.

That sounds reasonable until you look at the attribution rule. A last-click model credits the final recorded interaction and leaves the earlier touchpoints out of the result.

In this example, Google Ads looks stronger because it captures the final click. Blog content and email sequences may have helped earlier in the journey, but the report gives them no credit.

The walkthrough below builds a position-based model from raw events and CRM data, then compares its output with other attribution rules.

The Problem with Last-Click Attribution

Last-click attribution is the default in most analytics platforms. It gives 100% of the credit for a conversion to the very last touchpoint before the customer converted. It is simple, easy to understand, and dangerously misleading.

Think about how people actually buy things. A typical B2B customer journey might look like this:

1. Sees a LinkedIn ad and clicks through to a blog post (awareness)
2. Reads two more blog posts over the next week (consideration)
3. Signs up for the email newsletter (engagement)
4. Receives a case study via email and reads it (trust-building)
5. Googles the brand name, clicks a Google Ad, and buys (conversion)

Under last-click attribution, Google Ads gets 100% of the credit. The LinkedIn ad that started the entire journey? Zero credit. The blog content? Zero. The email sequence that built enough trust to convert? Zero.

Last-click attribution is like giving the goalie all the credit for winning a soccer match. Sure, they were there at the end - but they did not score the goals.

This is not just an academic problem. It leads to real budget misallocation. Teams cut "underperforming" channels that are actually doing the heavy lifting at the top and middle of the funnel. Revenue drops, and nobody understands why.

The Common Attribution Models

Before building a custom model, it helps to understand what is out there. Here are the five most common attribution models:

• First-Touch: 100% credit to the first interaction. Great for understanding what drives awareness, but ignores everything that happens afterward.
• Last-Touch: 100% credit to the final interaction before conversion. The default in most tools. Overvalues bottom-of-funnel channels.
• Linear: Equal credit to every touchpoint in the journey. Fair, but treats a random blog visit the same as the demo request that sealed the deal.
• Time-Decay: More credit to touchpoints closer to the conversion. Better than linear, but still undervalues the initial discovery.
• Position-Based (U-Shaped): 40% credit to the first touch, 40% to the last touch, and the remaining 20% split evenly among everything in between. This is the model I recommend for most businesses.

Each model tells a different story about your marketing. The right choice depends on your sales cycle length, number of channels, and what decisions you are trying to make.

Why Most Businesses Are Doing Attribution Wrong

Beyond just using the wrong model, I see three recurring mistakes when businesses attempt attribution:

1. Incomplete data collection. If you are not tracking every touchpoint - including organic visits, email opens, and offline events - your model is working with partial information. Garbage in, garbage out.

2. No unified customer identity. A visitor on your website, a lead in your CRM, and a subscriber on your email list might all be the same person. Without stitching those identities together (usually via email or a user ID), you cannot build a complete journey.

3. Treating attribution as a one-time project. Customer behavior changes. Channels evolve. An attribution model you built six months ago might not reflect your current funnel. You need a system, not a spreadsheet.

Attribution is not about finding the "one true answer." It is about getting a more honest picture of how your channels work together, so you can make smarter budget decisions.

The Data You Need

Before writing any code, you need to get your data foundation right. Here is what a proper attribution dataset looks like:

• Touchpoint logs: Every interaction a user has with your brand - page views, ad clicks, email opens, form submissions, chat conversations.
• Conversion events: The moment a lead becomes a customer (or hits whatever goal you are measuring) - purchases, sign-ups, demo requests.
• Timestamps: Exact date and time of each touchpoint and conversion. You need this to sequence the journey and apply time-based models.
• Channel and source info: Where did the touchpoint come from? This is where UTM parameters become essential.
• User identity: A consistent identifier (email, user ID, or cookie ID) that ties all touchpoints to the same person.
• Revenue data: The monetary value of each conversion, pulled from your CRM or payment system.

Data Collection Setup

For this project, I used three data sources:

UTM parameters on every link the team controlled. Every ad, every email link, every social post got tagged with utm_source, utm_medium, utm_campaign, and utm_content. I created a UTM naming convention document so the marketing team stayed consistent.

Google Analytics 4 event data exported to BigQuery through the native GA4 integration. This provides event-level timestamps, session details, and traffic-source fields.

CRM data from HubSpot - specifically the contact timeline and deal records. This showed which leads converted and for how much revenue. I exported it to BigQuery on a nightly schedule using a Python ETL script.

The key step was identity resolution. I matched GA4 user pseudo IDs to HubSpot contact records using the email address captured at form submission. This made it possible to stitch pre-conversion anonymous browsing sessions to known contacts.

-- Identity mapping: link GA4 users to CRM contacts
CREATE TABLE attribution.identity_map AS
SELECT DISTINCT
    ga.user_pseudo_id,
    crm.contact_id,
    crm.email,
    ga.event_timestamp AS first_identified
FROM `project.analytics.events_*` ga
JOIN `project.crm.contacts` crm
    ON LOWER(ga.event_params.value.string_value) = LOWER(crm.email)
WHERE ga.event_name = 'form_submit'
    AND ga.event_params.key = 'email';

Building the Position-Based (U-Shaped) Attribution Model

With the data unified in BigQuery, the model has three stages: touchpoint sequencing, credit assignment, and revenue allocation.

Step 1: Build the Touchpoint Journey

First, I constructed a complete journey for every converted user - every touchpoint from first visit to conversion, ordered by timestamp.

-- Build ordered touchpoint journeys for converted users
CREATE TABLE attribution.touchpoint_journeys AS
WITH conversions AS (
    SELECT
        im.contact_id,
        im.user_pseudo_id,
        d.deal_id,
        d.deal_amount,
        d.close_date AS conversion_date
    FROM attribution.identity_map im
    JOIN `project.crm.deals` d
        ON im.contact_id = d.contact_id
    WHERE d.deal_stage = 'closed_won'
),
touchpoints AS (
    SELECT
        c.contact_id,
        c.deal_id,
        c.deal_amount,
        c.conversion_date,
        ga.event_timestamp,
        ga.traffic_source.source AS source,
        ga.traffic_source.medium AS medium,
        ga.traffic_source.name AS campaign,
        ROW_NUMBER() OVER (
            PARTITION BY c.deal_id
            ORDER BY ga.event_timestamp ASC
        ) AS touch_position,
        COUNT(*) OVER (
            PARTITION BY c.deal_id
        ) AS total_touches
    FROM conversions c
    JOIN `project.analytics.events_*` ga
        ON c.user_pseudo_id = ga.user_pseudo_id
    WHERE ga.event_timestamp < c.conversion_date
        AND ga.event_name IN (
            'page_view', 'session_start',
            'form_submit', 'email_click',
            'ad_click'
        )
)
SELECT * FROM touchpoints;

Step 2: Apply Position-Based Credit Weights

This is where the attribution logic lives. In a position-based model, the first and last touchpoints each receive 40% of the credit. Everything in between splits the remaining 20% equally.

-- Assign position-based (U-shaped) attribution weights
CREATE TABLE attribution.weighted_touchpoints AS
SELECT
    contact_id,
    deal_id,
    deal_amount,
    source,
    medium,
    campaign,
    touch_position,
    total_touches,
    CASE
        -- Single-touch journey: 100% credit
        WHEN total_touches = 1 THEN 1.0
        -- Two-touch journey: 50/50 split
        WHEN total_touches = 2 THEN 0.5
        -- Multi-touch: U-shaped weights
        WHEN touch_position = 1 THEN 0.4
        WHEN touch_position = total_touches THEN 0.4
        ELSE 0.2 / (total_touches - 2)
    END AS attribution_weight,
    CASE
        WHEN total_touches = 1 THEN deal_amount * 1.0
        WHEN total_touches = 2 THEN deal_amount * 0.5
        WHEN touch_position = 1 THEN deal_amount * 0.4
        WHEN touch_position = total_touches THEN deal_amount * 0.4
        ELSE deal_amount * (0.2 / (total_touches - 2))
    END AS attributed_revenue
FROM attribution.touchpoint_journeys;

Notice the edge cases: single-touch journeys give all credit to one touchpoint, while two-touch journeys split it evenly. Handle these cases explicitly so the weights still add up correctly.

Step 3: Aggregate Attributed Revenue by Channel

Now the model rolls everything up to show how much revenue each channel actually drove.

-- Final attribution report: revenue per channel
SELECT
    CONCAT(source, ' / ', medium) AS channel,
    COUNT(DISTINCT deal_id) AS conversions_touched,
    ROUND(SUM(attribution_weight), 1) AS weighted_conversions,
    ROUND(SUM(attributed_revenue), 2) AS attributed_revenue,
    ROUND(SUM(attributed_revenue) /
        NULLIF(COUNT(DISTINCT deal_id), 0), 2) AS revenue_per_conversion
FROM attribution.weighted_touchpoints
GROUP BY channel
ORDER BY attributed_revenue DESC;

This single query gives you the full picture: which channels are driving the most attributed revenue, how many conversion journeys they participate in, and what the average value per conversion looks like.

Validating with Python

SQL works well for building the model inside a warehouse. A Python version is also useful for validation, testing different weights, and comparing the results side by side.

import pandas as pd

def apply_position_based_attribution(journeys_df):
    """
    Apply U-shaped (position-based) attribution to a
    DataFrame of touchpoint journeys.

    Expects columns: deal_id, deal_amount, source,
    medium, touch_position, total_touches
    """
    def calculate_weight(row):
        total = row['total_touches']
        pos = row['touch_position']

        if total == 1:
            return 1.0
        elif total == 2:
            return 0.5
        elif pos == 1:
            return 0.4
        elif pos == total:
            return 0.4
        else:
            return 0.2 / (total - 2)

    df = journeys_df.copy()
    df['weight'] = df.apply(calculate_weight, axis=1)
    df['attributed_revenue'] = df['deal_amount'] * df['weight']

    # Aggregate by channel
    channel_attribution = (
        df.groupby(['source', 'medium'])
        .agg(
            conversions_touched=('deal_id', 'nunique'),
            weighted_conversions=('weight', 'sum'),
            attributed_revenue=('attributed_revenue', 'sum')
        )
        .sort_values('attributed_revenue', ascending=False)
        .reset_index()
    )

    channel_attribution['channel'] = (
        channel_attribution['source']
        + ' / '
        + channel_attribution['medium']
    )

    return channel_attribution

# Compare multiple models side by side
def compare_models(journeys_df):
    """Run all five attribution models and compare."""
    results = {}
    df = journeys_df.copy()

    # Last-touch
    last_touch = df[df['touch_position'] == df['total_touches']].copy()
    last_touch['attributed_revenue'] = last_touch['deal_amount']
    results['last_touch'] = (
        last_touch.groupby('source')['attributed_revenue'].sum()
    )

    # First-touch
    first_touch = df[df['touch_position'] == 1].copy()
    first_touch['attributed_revenue'] = first_touch['deal_amount']
    results['first_touch'] = (
        first_touch.groupby('source')['attributed_revenue'].sum()
    )

    # Linear
    df_linear = df.copy()
    df_linear['attributed_revenue'] = (
        df_linear['deal_amount'] / df_linear['total_touches']
    )
    results['linear'] = (
        df_linear.groupby('source')['attributed_revenue'].sum()
    )

    # Position-based
    position_based = apply_position_based_attribution(df)
    results['position_based'] = (
        position_based.set_index('source')['attributed_revenue']
    )

    comparison = pd.DataFrame(results).fillna(0)
    comparison.columns = [
        'Last Touch', 'First Touch',
        'Linear', 'Position-Based'
    ]
    return comparison

The comparison table makes disagreements between models visible. Those differences are where the team needs to examine assumptions before changing a budget.

Building the Attribution Dashboard

Query output is useful for analysts, but marketing and finance teams often need a dashboard. Looker Studio fits teams working in Google Workspace, while Power BI may suit teams already using Microsoft tools.

Dashboard Components

The dashboard included four main views:

1. Channel Attribution Summary: A horizontal bar chart showing attributed revenue per channel under the position-based model, with a toggle to switch between models for comparison.
2. Model Comparison Table: A side-by-side table showing how each channel's attributed revenue changes across all five models. This was the most important view for decision-making - it showed where the models agreed (high confidence) and where they diverged (needs investigation).
3. Journey Length Distribution: A histogram showing how many touchpoints the average conversion journey has. This validates whether a multi-touch model is even necessary. If most journeys are single-touch, the model choice matters less.
4. Attribution Over Time: A line chart showing attributed revenue by channel per month. This surfaces seasonal trends and lets the team see how channel performance evolves over time.

Illustrative Scenario: How Attribution Can Change a Budget Discussion

The figures below are illustrative. They show how a position-based model can challenge a last-click budget, not a reported client result.

Start with this example budget:

• Google Ads: 55% ($22,000/mo)
• Facebook Ads: 25% ($10,000/mo)
• Email Marketing: 10% ($4,000/mo)
• Content/SEO: 10% ($4,000/mo)

Now compare it with this example attribution breakdown:

• Google Ads: 30% of attributed revenue (was getting 55% of budget)
• Facebook Ads: 18% of attributed revenue (was getting 25% of budget)
• Email Marketing: 22% of attributed revenue (was getting 10% of budget)
• Content/SEO: 25% of attributed revenue (was getting 10% of budget)
• LinkedIn Organic: 5% of attributed revenue (was getting 0% of dedicated budget)

In this scenario, content and email receive less budget than their attributed contribution suggests, while paid channels receive more. That is a reason to test a new allocation, not proof that the model has found the one correct answer.

A practical next step would be a limited budget test with clear conversion and acquisition-cost targets. Compare the result with the original allocation before making a larger change.

Key Takeaways and Best Practices

These practices make the model easier to test and explain:

1. Consider position-based attribution as a starting point. It gives weight to the first and last interactions while still crediting the middle of the journey. Compare it with the current model before using it for budget decisions.

2. Get your UTM hygiene right first. No attribution model can save you if your tracking is inconsistent. Create a UTM naming convention, enforce it across the team, and audit your parameters monthly. One person tagging a source as "facebook" and another as "Facebook" or "fb" will fragment your data.

3. Do not ignore the middle of the funnel. The touchpoints between first click and final conversion are where trust is built. Email nurture sequences, blog content, case studies, and webinars rarely get credit under last-click models, but they are often the reason someone converts.

4. Run models in parallel before making decisions. Keep the current reporting while you test the new model. Compare where they agree, where they differ, and whether either result matches observed conversions.

5. Update the model regularly. Customer behavior shifts. New channels emerge. Review your attribution model quarterly. Check if the average journey length is changing, if new touchpoint types need to be added, and if the weights still make sense.

6. Use attribution for direction, not precision. No attribution model is perfectly right. Use it to form and test budget hypotheses, not to claim an exact value for every touchpoint.

Want an Attribution Model for Your Business?

If last-click attribution is driving budget decisions, it is worth comparing that view with another model before changing spend. The work depends on the quality of the event, CRM, cost, and revenue data available.

I can adapt this approach to BigQuery, or build a smaller version in Google Sheets and Looker Studio when the data volume and reporting needs are simpler.

Book a 15-min fit call for a free consultation, or hire me on Upwork to get started right away.