Marketing Attribution is Broken
Setting up the raw SQL schema. Ingesting transaction logs and defining the fundamental data structure for tracking user paths.
Read ModuleFrom Awareness to Conversion: Assigning Due Credit to Every Touchpoint.
The conference room was tense. Maya dimmed the lights and projected a single user’s path on the screen to ground the debate in reality.
"This is Chloe," Maya began. "She bought a $200 Leather Jacket yesterday. Before we fight over who gets the credit, let’s look at her actual path."
"Total Revenue: $200," Maya said. "Now, let's see how our different tools try to claim this money."
Maya switched the slide to the first set of models. "Most of our dashboard tools default to these because they are computationally cheap and easy to explain."
The Logic: This model gives 100% of the credit to the very last interaction before the sale.
The Result for Chloe: Direct Traffic gets $200.
Ben, the Performance Marketer, immediately objected. "That's absurd. She typed the URL because she just clicked my ad five minutes prior! Direct didn't 'sell' her."
"Correct," Maya nodded. "So, the industry standard is Last Non-Direct Click. In that case, Google Search gets the full $200."
Dr. Rao intervened for the first time.
"Maya, stop," he said from the back of the room. "Last-Click implies that Chloe's Propensity to Buy was zero until that specific click occurred. That is scientifically lazy. It suffers from Selection Bias—users who search for our brand name have often already decided to buy. The ad is capturing demand, not creating it. It is statistically equivalent to giving a waiter full credit for the meal just because he brought the check."
The Logic: This model gives 100% of the credit to the channel that introduced the customer to the brand.
The Result: TikTok gets $200.
Sarah, from Brand Marketing, smiled. "Finally, some justice. I found her. Without that video, she never searches."
Dr. Rao shook his head.
"This suffers from the 'Cookie Decay' problem," he countered. "If Chloe bought 30 days later, FTA assumes the video she saw a month ago is the sole reason she bought today, ignoring the Email and Search. It overvalues 'Click-bait' that brings traffic but doesn't convert."
Alex from CRM chimed in. "You are both missing the point. The most valuable event wasn't the view (TikTok) or the buy (Search). It was on Wednesday when we got her email address."
The Logic: Give 100% credit to the touchpoint where the user identifies themselves (Sign Up / Form Fill).
The Result: Email/Organic gets $200.
Dr. Rao nodded slightly. "Alex has a point. Once we have the email, our cost to market drops to near zero. That is a high-value event. But giving it 100% credit is still arbitrary."
"Since Single-Touch is biased," Maya continued, moving to the next chart, "We often look at Multi-Touch Attribution (MTA). But be warned: these are Heuristics—rules of thumb."
Maya’s Warning: "What all these models share is the same flaw: they observe who converted, not who would have converted anyway."
The Logic: Equality. 4 touchpoints = 25% credit each.
The Result: TikTok ($50), Email ($50), Google ($50), Direct ($50).
Dr. Rao circled the 'Direct' column on the slide.
"Why is a low-intent 'Direct Visit' worth the same as a high-intent Search? You are flattening the variance. This model has High Bias—it assumes all interactions are equal, which we know is false."
The Logic: Credit decays based on recency (usually a 7-day half-life).
The Result: Direct gets the most, TikTok gets the least.
Maya: "This punishes Brand marketing (Sarah) for planting seeds that take time to grow."
The Logic: 40% to the First touch, 40% to the Last touch, and 20% shared in the middle.
The Result: TikTok ($80), Direct ($80), Email ($20), Google ($20).
Sarah: "I can live with this. It recognizes the introduction."
Alex (CRM): "But it creates a 'Sagging Middle.' It tells me that nurturing the lead via Email is worth almost nothing (20%). That's why we have churn!"
Maya clicked to the next slide. "This is why many organizations prefer the **W-Shaped** model. It adds a third pillar for Lead Generation."
The Logic: 30% First Touch (TikTok), 30% Lead Conversion (Email), 30% Opportunity Creation/Last Touch (Search). The remaining 10% is shared.
The room went quiet. Dr. Rao finally asked the uncomfortable question. "Hold on," he said, pointing at the numbers. "Why 40% for U-Shape? Why 30% for W-Shape? Who decided these weights?"
Maya: "They are industry defaults, Dr. Rao. They aren't derived from our data."
Dr. Rao: "Exactly. We are managing a million-dollar budget on 'Industry Defaults.' This is unacceptable. Show us the Hypothesis."
Maya switched to the complex dashboard. Before she could speak, Ben raised a hand.
Ben: "Maya, honestly, this sounds like a Black Box. How do I know this isn't just generating random numbers? What is the logic?"
Maya smiled. "Fair question. We use two specific algorithms to remove human opinion: Markov Chains (Probability) and Shapley Values (Fairness)."
Attribution models estimate conditional probability, not causal truth. Only experiments reveal incrementality.
"Think of our funnel as a Relay Race," Maya explained. "We calculate the probability of the 'baton' passing from one runner to the next. If we remove a runner (TikTok), does the baton still reach the finish line?"
The Math: "The model looks at all 50,000 user paths. It sees that when 'TikTok' is present, the probability of reaching 'Email' jumps by 30%. When it is absent, that link breaks. This is the Removal Effect."
"Markov is about the path. Shapley is about the Team," Maya continued. "Imagine a basketball team. If Curry sits on the bench, how many points does the team score? If Lebron sits, how many? We calculate the marginal contribution of every player across every possible lineup."
Important Constraint: "Also," Maya added, looking at Dr. Rao, "Paths were constrained to a 30-day lookback window to reduce long-tail noise and cookie decay bias."
Dr. Rao walked to the whiteboard. "Markov answers 'How probable is the sale?' But it ignores 'How *fast* is the sale?'"
He drew a curve dropping over time. "We need Survival Analysis (Cox Proportional Hazards). We model the 'Time to Convert.' Does seeing a TikTok video shorten the sales cycle from 7 days to 2 days? If so, TikTok isn't just driving volume; it is improving cash flow velocity."
Dr. Rao: "If a channel speeds up conversion, it is more valuable than one that just assists. Survival analysis reveals the accelerators."
Sarah (Brand) was still looking at the whiteboard. She seemed happy she beat Last-Click, but she felt the model still undervalues the "lingering" power of high-impact video.
Sarah: "Maya, this model treats a 'View' on Monday as irrelevant if the user buys on Friday. But my TikToks are catchy. They stick in your head. Just because the click happened later doesn't mean the memory of my video wasn't doing the work."
Ben: "Here we go. You want credit for 'Vibes' again."
Maya: "Actually, Ben, she's talking about Adstock."
Ben: "I thought Adstock was for Dr. Rao's high-level MMM charts? This is user-level data."
Maya: "We can apply Adstock to user paths too. Instead of treating an ad impression as a binary 'Event', we treat it as a Decaying Signal."
Maya drew a curve on the whiteboard. "If Chloe sees a TikTok on Day 1, the 'Signal Strength' is 100%. On Day 2, it might be 50%. On Day 3, 25%. If she clicks a Search Ad on Day 3, the model sees two influences: The fresh Search Ad (100%) AND the lingering echo of the TikTok (25%)."
Dr. Rao's Condition:
"This is mathematically sound," Dr. Rao nodded. "But be warned: The Decay Rate (Half-Life) is a parameter. If you guess it, you are biased. We must use regression to solve for the Half-Life that best fits the conversion data. We do not 'pick' the decay; we let the data reveal how fast our customers forget."
The Result for Chloe (Algorithmic):
Ben (Performance Marketing): "Wait! My Search Ads are only worth $10? That destroys my ROAS! I'm turning them off."
Dr. Rao's Reality Check: "Ben raises a valid point. The model says Search is low impact because it assumes Organic Search would pick up the slack (Substitution Effect). But models are just simulations. We cannot trust the model blindly."
Dr. Rao stood up and walked to the whiteboard. "Maya, the Algorithmic model is a Hypothesis. We need to validate it with a Lift Test (Incrementality)."
The Experiment (Geo-Lift): We turn off Google Paid Search in Ohio (Treatment) and keep it on in Pennsylvania (Control).
The Result: In Ohio, Total Sales dropped by only 2%, even though Paid Search spend went to zero.
Crucially, this drop was statistically insignificant (p-value > 0.1).
The Conclusion: Dr. Rao was right. The 2% drop was just noise. Most people in Ohio just clicked the Organic link when the Ad disappeared.
The Verdict: The Algorithmic Model was correct. Google Search was claiming credit for users who were going to buy anyway (Selection Bias). TikTok was driving the true incremental lift.
Maya projected the final "Peace Treaty" slide.
| Methodology | Who Loves It | When We Use It |
|---|---|---|
| Last Non-Direct | Google/Ben | Daily Optimization. Good for tactical keyword bidding, but we apply a "haircut" to the targets. |
| W-Shaped | B2B / CRM | Lead Management. Ensures we value the "Sign Up" event, not just the "Sale." |
| Algorithmic (Markov/Survival) | Maya/Sarah | Quarterly Budgeting. Used to decide macro-allocation based on probability and velocity. |
| Incrementality (Lift) | Dr. Rao | The Gold Standard. Used once a year to calibrate the Algorithmic model. |
Governance Warning: No attribution model should be used for both Optimization and Financial Reporting without adjustment, as this creates incentive distortion.
The Decision:
The lift test told a different story the following quarter. TikTok’s incrementality had declined as frequency saturated the audience. Meanwhile, Search’s incrementality rose during the peak holiday season as competition drowned out organic listings.
Dr. Rao smiled as he looked at the new data.
"Good," he said. "If your model never changes, it isn't measuring reality. It's just memorizing last quarter."
100% credit goes to one event (First or Last Click). Simple, but ignores the journey.
Jump to Modules 1-3 ↓Splitting credit using static math (Linear, Time-Decay). Better, but arbitrary.
Jump to Modules 4-5 ↓Using Graph Theory (Markov) and Game Theory to calculate true conversion probability.
Jump to Modules 6-8 ↓Top-down econometrics to measure TV/Offline spend and optimize budgets.
Jump to Modules 9-12 ↓
Setting up the raw SQL schema. Ingesting transaction logs and defining the fundamental data structure for tracking user paths.
Read Module
Implementing Lookback Windows. Using SQL to define time constraints (30/60/90 days) and filter out irrelevant historical interactions.
Read Module
Single-Touch Attribution Models. Comparing First-Click vs. Last-Click logic in SQL to demonstrate why binary attribution fails.
Read Module
Heuristic Multi-Touch Attribution. Implementing Linear, Time-Decay, and Position-Based models to distribute revenue credit.
Read Module
Path Analysis & String Aggregation. Transforming raw event rows into readable visual user journeys using SQL string functions.
Read Module
Building transition matrices to calculate the "Removal Effect" and determining the true probability of conversion for each channel.
Read Module
Game Theory (Shapley Value). Applying cooperative game theory to assign fair value contribution based on marginal utility.
Read Module
Predictive Modeling (Logistic Regression). Using Machine Learning to predict conversion probabilities and score marketing channels dynamically.
Read Module
Marketing Mix Modeling. A top-down econometric approach to measure the impact of offline media, TV, and non-trackable spend.
Read Module
Unified Measurement. A strategic framework for integrating granular MTA data with broad MMM insights for holistic decision making.
Read Module
Budget Optimization. Translating attribution weights into actionable budget allocation to maximize Return on Ad Spend (ROAS).
Read Module
Data Storytelling. Techniques for visualizing complex attribution data and presenting actionable insights to C-Suite stakeholders.
Read Module