The Lost Traffic Chronicles: Rescuing Marketing from the “(direct)” Swamp with Identity Stitching

In modern Data Warehousing (DWH) architecture, web analytics logs are often a disjointed mess. Standard, out-of-the-box solutions (like GA4) operate on an append-only basis. They record events in the moment: if they don’t see a UTM tag, they log the source as (direct) and move on.

The algorithm known as Retroactive User-Centric Last Non-Direct Click Attribution (more commonly referred to in the industry as Identity Stitching with custom attribution) solves this exact problem. It shifts the responsibility of building sessions and attributing value from the user’s browser directly to the database level (BigQuery, Snowflake).

Where This Algorithm Fits in Modern Data Engineering (ELT)

Within the Modern Data Stack, this algorithm lives in the Transform stage (the “T” in ELT). Raw logs are dumped into the warehouse, and nightly batch scripts (usually orchestrated via dbt or Airflow) assemble a coherent graph out of this chaos.

From a systems analysis perspective, the algorithm solves the problem of finding connected components within a graph. We start with a fragmented graph of devices, sessions, and internal user IDs. The goal is to stitch it all together into a unified profile and redistribute the attribution correctly.

Industries and Business Goals

If you sell $5 phone cases with a three-minute sales cycle, you don’t need this algorithm. However, it is mandatory for businesses where the path to conversion is long and spans multiple devices:

  • FinTech and Banking: A user sees a mortgage ad on their smartphone $\rightarrow$ thinks about it for a week $\rightarrow$ types the bank’s URL on their work laptop $\rightarrow$ submits an application.

  • Complex E-commerce / Real Estate: Long periods of comparing prices across different gadgets before the final purchase.

  • B2B and SaaS: A user registers for a free trial from one IP address $\rightarrow$ pays for an annual subscription a month later from another.

The Goal: Calculating accurate CAC (Customer Acquisition Cost) and LTV (Lifetime Value). Without this algorithm, you’ll end up firing marketers for “ineffective” mobile ads that are actually generating delayed desktop conversions.

How the Logic Works

The pipeline runs on a schedule (usually once a day) and consists of three mathematical stages:

1. Sessionization

Session IDs provided by Google (ga_session_id) are ignored because they break easily due to frontend timeouts. The database calculates sessions independently using TIMESTAMP_DIFF window functions. Events are sorted chronologically. If the gap between the current and previous hit is $> 1800$ seconds (30 minutes) OR the UTM tag changes, the algorithm cuts the timeline and starts a new session.

2. Retroactive Identity Stitching

This is the core of the system. A Mapping Table is built. Let’s say an anonymous user visited our site from their phone for three days (user_pseudo_id = A). On the fourth day, they log in and are assigned user_id = 999 in the database.

The script goes back in time and permanently attaches user_id = 999 to all historical logs from that phone. Now, all devices and cookies are linked to a real person.

3. Source Redistribution (Last Non-Direct)

Data is grouped by the stitched user_id. The algorithm arranges all of this person’s sessions chronologically. If a session that resulted in a conversion has an empty source (direct) / (none), the LAST_VALUE() IGNORE NULLS window function is applied. It grabs the last known paid source from this profile’s history and overwrites the empty current source. Justice is restored.

Companies and Platforms Implementing This Pattern

This isn’t a guarded corporate secret; it’s a classic engineering pattern:

  1. OWOX BI: They built their BigQuery pipelines entirely around the daily recalculation of logs using first-party cookies.

  2. Segment (Twilio) & RudderStack: CDP platforms. Their identity resolution modules generate the profile-stitching tables that are later used in analytics.

  3. Snowplow Analytics: A raw data collection platform. It provides the perfect underlying structure (including parsing User-Agent Client Hints) that makes implementing this algorithm straightforward.

Bottlenecks and Trade-offs

Any powerful architecture comes with a cost. Here, there are two main trade-offs:

  • Data Drift (Mutating History): Because the script changes the past retroactively, your reports will “drift.” If you look at a May report today and see 100 sales from ads, you might check the same report two weeks later and see 105 sales (because the algorithm stitched new logins to old mobile clicks). The business and finance teams must be strictly prepared for the fact that historical numbers are dynamic.

  • FinOps and Compute Load: Churning through 22 million rows daily using heavy LAG() and LAST_VALUE() window functions is expensive. If you don’t set up incremental updates (only recalculating users who had new data in the last 3 days), BigQuery billing will destroy the project’s unit economics.

Similar Posts