The Lookback Trap in Marketing Attribution

1. The Hook

The euphoria from fixing the raw data schema lasted exactly 48 hours.

I was back on a Zoom call with the team at Ujvi Candles. Their Marketing Manager, Sarah, was sharing her screen, pointing accusingly at a row of data I had just engineered.

"Kamal," she said, her voice tight. "Why is Google Analytics claiming we got a sale from a 'Christmas Special' email... in July?"

She highlighted the row. Sure enough, a customer had clicked an email on December 24th, 2024. They disappeared for six months. Then, on July 15th, 2025, they came back directly and bought a $200 'Midnight Jasmine' set.

"According to your new database," the CEO chimed in, "that Christmas email gets credit for this summer sale. I am not paying a bonus to the Email team for a click that happened half a year ago."

They were right. We had successfully captured every touchpoint, but we had created a new monster: Zombie Clicks.

Clicks that died months ago were rising from the grave to claim credit for today's revenue. We needed a silver bullet. We needed a Lookback Window.

2. The "Aha!" Moment

I asked the CEO to pause. "Think of Marketing Attribution like dating," I said.

"Imagine you get married today. Who gets the credit for the wedding?"

• The person you're marrying? (The Conversion)
• The friend who introduced you last year? (The First Click)

Now, imagine an ex-girlfriend you had coffee with three years ago shows up at the wedding and says, "Technically, I dated him first, so I'm responsible for this marriage."

The CEO laughed. "That's ridiculous. That relationship ended years ago."

"Exactly," I nodded. "That's what your Christmas Email is doing. It's the 'Ex' showing up to the wedding. Just because a touchpoint happened doesn't mean it's relevant."

To fix this, we apply a Lookback Window—a strict time limit (usually 7, 30, or 90 days). If a click happens outside this window, it doesn't get invited to the wedding. It gets ghosted.

3. The Solution (The Code)

To kill the Zombie Clicks at Ujvi, I didn't need complex Python scripts yet. I just needed to refine our SQL Logic to exclude irrelevant history.

Here is the SQL architecture I deployed for Ujvi today:

/* LOGIC: FILTERING ZOMBIE CLICKS 
   We only count touches that occurred within 
   X days of the Conversion.
*/

WITH Orders AS (
    -- Step 1: Identify the Conversion Time (The "Wedding")
    SELECT 
        ORDER_ID,
        MAX(TOUCH_TIME) as CONVERSION_TIME,
        ORDER_VALUE
    FROM raw_touchpoints
    GROUP BY ORDER_ID, ORDER_VALUE
),

Valid_Touches AS (
    -- Step 2: The Lookback Logic
    SELECT 
        t.TRANS_ID,
        t.ORDER_ID,
        t.CHANNEL,
        t.TOUCH_TIME,
        o.CONVERSION_TIME,
        -- Calculate how many days before purchase the click happened
        DATEDIFF(day, t.TOUCH_TIME, o.CONVERSION_TIME) as DAYS_AGO
    FROM raw_touchpoints t
    JOIN Orders o ON t.ORDER_ID = o.ORDER_ID
    WHERE 
        -- THE KILL SWITCH:
        -- Only keep touches within 30 Days of purchase
        t.TOUCH_TIME >= DATEADD(day, -30, o.CONVERSION_TIME)
)

SELECT * FROM Valid_Touches;

The Fixer's Notes:

• The Anchor: We first establish CONVERSION_TIME. You can't look back if you don't know where you are standing.
• The Window: I set the interval to 30 Days (`DATEADD(day, -30...)`). This is standard for retail goods like candles. If you sell cars, use 90 days. If you sell pizza, use 1 day.
• The Purge: The `WHERE` clause ruthlessly filters out any interaction older than the window.

4. The Reality Check

The code looks clean, but how does it affect Ujvi's wallet? We ran this logic on Order #40808323 from our dataset.

The Data Profile (Order #40808323):

• Total Touches: 10 interactions.
• Journey Duration: 50 Days.
• Revenue: $150.00

The "After" Scenario (30-Day Window):

When we applied the code above, here is what happened:

"Touches 1, 2, and 3 (TikTok & Pinterest) happened >30 days ago. They were deleted."

Suddenly, the credit shifted entirely to Email and Direct, which occurred in the last week. The TikTok manager was furious. "I brought those people in!" she argued. "Just because they took 40 days to decide doesn't mean I didn't help!"

She has a point. Short windows favor "Closers" (Email/Retargeting). Long windows favor "Openers" (Social/Video).

There is no "Correct" window. There is only the window that matches your business reality. For Ujvi, we compromised on 30 days—long enough to consider a scent, short enough to ignore Christmas clicks in July.

5. Next Steps & Interaction

Recap: We have successfully cleaned the data timeline. We are no longer giving credit to "Ex-Girlfriends" (Zombie Clicks).

Teaser: Ujvi is safe from Zombies, but we have created a new problem. In Order #40808323, we still have 7 valid touches left in the 30-day window. Who gets the credit? Does the first one get 100%? The last one? Or do we split it?

Most tools default to "Last Click," which is the biggest lie in marketing. We’ll debunk that in Module 03: The First-Click Myth.