How a Marketing Agency Built Cross-Channel Attribution in 4 Weeks with Hevo and Snowflake

The Challenge

This agency manages paid media for 23 clients across Google Ads, Meta Ads, LinkedIn Ads, TikTok Ads, and Bing Ads. That's 115 active ad accounts minimum, and most clients run campaigns on at least 3 platforms simultaneously. Each platform has its own attribution window, its own conversion definition, and its own way of counting the same user.

The agency's "reporting" process was a 4-person team pulling CSVs from each platform every Monday morning and pasting them into client-specific Google Sheets. Google Ads said it drove 40 conversions. Meta said it drove 35 for the same campaign period. LinkedIn claimed 12. Add them up and you'd get more conversions than actually happened - because every platform takes full credit for every conversion it touched.

Attribution was last-click only because nobody could connect the cross-channel journey. A prospect might click a LinkedIn ad on Tuesday, a Google search ad on Thursday, and convert through a Meta retargeting ad on Saturday. Meta got 100% of the credit. LinkedIn and Google got nothing. Clients kept asking "which channel is actually driving revenue?" and the honest answer was "we don't know." That's a bad answer when you're managing $2.8M/month in combined ad spend.

The Monday reporting ritual consumed 16 hours per week across the 4 analysts. That's 16 hours of copying, pasting, formatting, and checking that the numbers looked reasonable. Not analyzing. Not optimizing. Just assembling data.

What We Built

We used Hevo Data's pre-built connectors for all 5 ad platforms - Google Ads, Meta Ads (via the Marketing API), LinkedIn Ads, TikTok Ads, and Bing Ads - plus Google Analytics 4. Hevo handles the API pagination, rate limiting, and schema changes that make ad platform integrations painful to maintain in-house. Each connector syncs every 6 hours, pulling campaign-level metrics, ad set performance, and conversion events into Snowflake's raw layer.

Inside Snowflake, we built a unified touchpoint model. Every ad click, impression, and conversion event gets mapped to a common schema with standardized fields: client_id, platform, campaign_name, touchpoint_timestamp, event_type, cost, and revenue. This normalization layer is where the real work happens - Google Ads calls it a "campaign," Meta calls it an "ad set," LinkedIn calls it a "campaign group." We mapped them all to the same grain.

The multi-touch attribution model runs nightly as a Python/Snowpark job. It uses Shapley values (a game-theory approach) to distribute conversion credit across all touchpoints in the customer journey. If a customer clicked 3 ads before converting, the model calculates each touchpoint's marginal contribution to the conversion rather than giving 100% credit to the last click. This is mathematically sound and doesn't require the arbitrary weights that position-based models use.

For client-facing reporting, we built a Looker Studio dashboard template that auto-populates for each client using parameterized data sources. Each client gets their own dashboard URL showing attributed revenue by channel, cost per attributed conversion, and ROAS by platform. The reports auto-generate Sunday night and email to clients before Monday morning. The Monday reporting ritual is dead.

Results

Tech Stack

What We Learned

• Hevo's pre-built connectors saved weeks of API plumbing. Ad platform APIs are notoriously finicky - rate limits change, pagination tokens expire, and Meta's Marketing API in particular has breaking changes every few months. Hevo abstracts that away. We had all 5 platforms syncing into Snowflake within 2 days, including Google Analytics 4. Building those connectors from scratch would've taken 3-4 weeks alone.
• Shapley values beat position-based attribution, but you need to explain them. The math is solid, but most marketing managers don't know what a Shapley value is. We built a simplified "contribution score" visualization in Looker Studio that shows each channel's credit as a percentage. Under the hood it's Shapley values. On the dashboard it's a bar chart that says "Google drove 42% of this client's conversions." That's what stakeholders actually need.
• The 18% ROAS improvement came from shifting budget, not spending more. Once the agency could see that TikTok was getting last-click credit for conversions that LinkedIn actually initiated, they shifted 15-20% of budget from overattributed channels to underattributed ones. Total spend stayed the same. Conversions went up. That's the entire point of multi-touch attribution.
• Client retention jumped because the agency could finally answer "why." Before this project, client QBRs were awkward. "We spent $120K and got roughly this many conversions." Now the QBR shows exactly which channels contributed what, which creative assets drove the highest-quality conversions, and where the next dollar should go. Clients renewed because they could see the agency was actually optimizing, not just spending.