Finally decided to map the whole beast, from the first ad impression to the renewal invoice. Not because I love pretty diagrams, but because our last attribution report tried to convince me a TikTok dance video was responsible for a $50k enterprise deal. Sure.
I used a mix of homegrown scripts and stitching together three different "solutions." The usual suspects were involved:
* **Platform A:** For capturing the initial ad touchpoints (because their UTM ingestion is tolerable).
* **Platform B:** For the mid-funnel web/app events (their session stitching is less of a black box than most).
* **Our own data warehouse:** Where all the pipelines dump raw events, and where the final "closed-won" and "renewal" signals live.
The real "fun" was building the connective tissue. Here's a sanitized snippet of the logic that tries to tie a lead to a campaign after a 90-day multi-touch window. It's not pretty, but it's honest.
```sql
-- The 'attribution logic' that makes our finance team weep
WITH touch_aggregation AS (
SELECT
anonymous_id,
-- Because first-touch gets all the glory
FIRST_VALUE(campaign_id) OVER (PARTITION BY anonymous_id ORDER BY timestamp ASC) AS first_touch,
-- But we all know last-touch before MQL takes the credit
LAST_VALUE(campaign_id) OVER (PARTITION BY anonymous_id ORDER BY timestamp ASC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_touch_mql,
-- Spread the love, I guess
ARRAY_AGG(DISTINCT campaign_id) WITHIN GROUP (ORDER BY timestamp ASC) AS all_touches
FROM
unified_event_stream
WHERE
timestamp >= DATEADD(day, -90, CURRENT_DATE())
AND campaign_id IS NOT NULL
)
-- ... and then the messy join to the CRM opportunity table
```
The takeaways were depressingly predictable:
* **Cross-device?** More like cross-your-fingers. The probabilistic models are basically fancy guesswork.
* **Cookieless measurement?** Every vendor claims they've solved it. The reality is a patchwork of brittle API integrations and modeled data that smells suspiciously like last year's numbers.
* The most accurate attribution point for renewal revenue was... the account executive's note in Salesforce about why the client stayed. Go figure.
So, what's everyone else using to connect these dots without buying into the full "360 Customer View" hype? Any tool that actually admits its own margin of error?
—mikep
Sounds like you just built a third, even worse attribution platform out of three bad ones. Now you get to maintain the "connective tissue" forever. I'll stick with last-touch and ignore the marketing reports.
If it ain't broke, don't 'upgrade' it.
Ah, the "connective tissue." That's where the real migration scars form. I bet you had to build a few custom id-mapping tables that live outside all three platforms. Those things always become the most critical piece of infrastructure, and also the most brittle.
Did your timestamp alignment cause any headaches? We had a fun one where Platform A used UTC, Platform B used the browser's local time (naively), and our warehouse used PST for business events. Matching sessions across a 90-day window turned into a timezone detective puzzle.
Ah, the classic "three bad platforms make one good one" strategy. I've seen this movie before, and the sequel is always a migration off the custom scripts when someone realizes the maintenance burden is now a full time job for two engineers.
Your 90 day multi-touch window is the real tell. That's a business rule that will change next quarter when the growth team decides "accelerated pipelines" need a 45 day window, and then finance will demand a 180 day view for enterprise. Now your clever SQL logic needs flags, config, and suddenly you're building a rule engine. You've graduated from stitching data to product managing an internal attribution platform nobody asked for.
And let me guess, the "sanitized snippet" leaves out the 20% of orphaned leads that fall outside the stitching logic, which become a monthly manual report someone has to reconcile. The pretty diagram always hides the spreadsheet someone is maintaining on the side.
monoliths are not evil
You're spot on about the hidden spreadsheet. Our orphan rate sits closer to 15%, but it's the same story. A PM asks for a cohort analysis on those "unmappable" users, and suddenly you're manually tagging lead sources in a shared Google Sheet that's linked from the official Looker dashboard.
The business rule churn is the real killer, though. We started with a 90-day window, but sales ops wanted to exclude weekends for "sales cycle days." That one-line config change turned into a branching logic monster to handle different timezones and holidays. Now we're basically running a shadow calendaring service.
Ship fast, measure faster.
That "connective tissue" is exactly what I'm worried about as we plan a migration. When you built those custom id-mapping tables, did you ever regret not putting them into one of the platforms, like your own warehouse, from the very start? I'm trying to avoid making something brittle right out of the gate.
How did you manage testing the logic against those orphaned leads everyone is mentioning? I'm nervous about building something that just silently drops data we don't understand.
One step at a time
That sanitized snippet is the entire problem in one CTE. You're letting a first_value window function decide who gets credit for a deal, but the partition key is an anonymous_id. How many of those actually resolve to a known user before the 90-day window closes? I'd bet the farm you're giving full credit to a ghost for a decent chunk of your "attributed" pipeline.
It's honest, I'll give you that. It's also a fantastic way to keep the attribution debate alive forever, because any lead that converts after their anonymous session expires breaks the model. Sales will love that - they can always blame the data.
Question everything.
> The real "fun" was building the connective tissue.
This is the part I'm stuck on in my own project! I'm trying to do something similar but for a smaller sales flow. When you stitch events from the ad platform and your own warehouse, how do you handle when the `anonymous_id` changes mid-session? I tried joining on email later, but that leaves a gap where the user is anonymous.
Do you have a simple rule for picking the final "real" user ID, or is that part of the brittle logic everyone's talking about?
Containers are magic, but I want to know how the magic works.
Ah, that snippet cuts off at the worst part! The window function ordering is critical. If you order by `timestamp ASC` for first-touch, you're at the mercy of your timestamp sync issues everyone mentioned. I'd love to see the full logic.
For the `anonymous_id` gap, our rule of thumb was to use the first known `user_id` linked to that anonymous session as the "real" ID. But, big caveat, you need a reliable `identified` event from your analytics platform to capture that link. It's still brittle - if a user clears cookies and comes back via a different ad, they're a new ghost.
We ended up with a mapping table that looks like this, just to make the joins explicit:
```sql
-- Simplifying a bit, but you get the idea
CREATE TABLE id_mappings (
anonymous_id VARCHAR,
user_id VARCHAR,
first_link_time TIMESTAMP,
last_seen_time TIMESTAMP
);
```
The "connective tissue" is basically maintaining this table's lifespan logic. It's not pretty either!
Clean code, happy life
> you need a reliable `identified` event from your analytics platform to capture that link
That's the whole game, isn't it? Our orphan rate dropped from 18% to under 5% when we started enforcing a mandatory `identify` call on the first form submission, not just on signup. It's a tiny product requirement with massive data implications.
Your mapping table is smart, but I'd add a `confidence_score` column. We flag links from a single session vs. multiple sessions over time. The multi-session links are the only ones finance lets us use for true multi-touch revenue attribution. The rest get bucketed into "assisted" for marketing reports only.
Show me the pipeline.