Spreading Credit in Marketing Attribution

1. The Hook

I was back in the boardroom with Ujvi Candles. The coffee was cold, and the argument was heating up.

The Brand Manager (Team First Click) was still insisting that her Instagram campaigns were the reason the company existed. The PPC Manager (Team Last Click) was rolling his eyes, pointing at his conversion charts.

"We are at a stalemate," the CEO said, rubbing his temples. "One model says my social team is a genius. The other says they are useless. I can't run a business on 'Pick a Card, Any Card'."

"Then don't pick one," I said. "Pick them all."

The room went quiet.

"Why are we fighting over who gets the dollar?" I asked. "It's a digital dollar. We can cut it into as many pieces as we want. We don't have to choose a winner. We can give a 'Participation Trophy' to everyone who showed up."

This is the birth of Multi-Touch Attribution (MTA). Specifically, Heuristic MTA. It’s not perfect math—it’s arbitrary rules—but it’s a hell of a lot better than ignoring 90% of your customer's journey.

2. The Menu of Models

Once you accept that you can split the dollar, you have to decide how to split it. Here are the three most common shapes of the attribution pie.

⚠️ Image Not Found
Please ensure the file is named exactly: blog 4.1.png

1. The Linear Model (The Egalitarian)

Philosophy: "Everyone played a part."
The Logic: If there are 3 touches, everyone gets 33%.
The Visual:

Pros: Simple, fair. The middle-funnel (Email, SEO) finally gets paid.
Cons: It's lazy. Does a random banner ad really deserve the same credit as the final "Buy Now" click?

2. The Time Decay Model (The Realist)

Philosophy: "What have you done for me lately?"
The Logic: Credit increases as you get closer to the conversion.
The Visual:

Pros: great for short sales cycles (impulse buys like candles).
Cons: Punishes the "Sparks" that started the fire weeks ago.

3. The U-Shape / Position-Based (The Strategist)

Philosophy: "The Intro and the Close matter most."
The Logic: 40% to First Touch, 40% to Last Touch, 20% spread in the middle.
The Visual:

Pros: The industry favorite for Growth teams. It respects the acquisition (Social) and the conversion (Search).

3. The Technical Solution (SQL Logic)

"Let's try the Linear Model," I told the CEO. "Let's see what happens when we stop fighting and start sharing."

We don't need complex Python scripts for this. SQL Window Functions are perfect here.

/* Model: Linear Attribution
   Logic: Total Value / Count of Touches = Value per Touch
*/
SELECT 
    ORDER_ID,
    CHANNEL,
    TOUCH_SEQUENCE,
    ORDER_VALUE,
    -- Step 1: Count how many touches in this journey
    COUNT(*) OVER (PARTITION BY ORDER_ID) as total_touches,
    
    -- Step 2: Divide the Pie
    ORDER_VALUE / COUNT(*) OVER (PARTITION BY ORDER_ID) as linear_credit_revenue
FROM marketing_touches;

Why this works:

• COUNT(*) OVER...: This counts the participants in the journey without collapsing the rows.
• The Math: If an order is $100 and there are 5 touches, this instantly calculates $20 per touch. Finally, that forgotten Email in Step 3 gets a value on the board.

4. The Real Data Scenario

We ran this on Order #40808323. This was a $25.00 order with a long, nagging history (10 touchpoints).

⚠️ Image Not Found
Please ensure the file is named exactly: blog 4.2.png

The Journey of Order #40808323

Let's compare the scorecards.

The Implication:

Suddenly, the SEO Manager has something to show. $2.50 isn't much, but across 10,000 orders, it adds up to a justification for their budget. We have mathematically proven that SEO participated in the sale.

5. The Reality Check

The CEO looked at the numbers. "I like it," he said. "It feels nicer."

"Careful," I warned. "Linear is fair, but it's also kind of dumb."

"What do you mean?"

"Look at that journey again," I said. "The user came back via Direct nine times. Nine! But we gave the exact same credit ($2.50) to the 9th Direct visit as we did to the 1st SEO visit. Does that reflect reality? Probably not. The SEO visit started the whole thing. Maybe it deserves more?"

This is the flaw of Heuristic Models (Linear, Time Decay, U-Shape). They are just rules of thumb. We are guessing that "All touches are equal" or "Recent touches are better."

But we aren't using data to prove it. We are just imposing our will on the data.

"So how do we stop guessing?" the CEO asked.

"Next time," I grinned. "We stop using simple division. We start using Graph Theory. We enter the world of Markov Chains."

6. Next Steps & Interaction

Ujvi is now spreading the wealth, but they are starting to ask harder questions about the true value of each touch.

Over to you: If you had to choose one model for your life choices, would you choose Linear (everyone matters) or Time Decay (only the latest matters)? Vote below!