Hi everyone, I'm pretty new to managing this side of things for my team, so I hope this is the right place to post this.
We started using Gemini for a few paid search campaigns last quarter, and while the interface is good, I couldn't easily see the direct impact on our pipeline next to what we were spending. Our CRM (we use HubSpot) has the final deal data, but it felt disconnected. I was nervous about continuing the spend without a clearer picture.
So, I spent the last week building a custom dashboard in Google Looker Studio. It pulls cost and conversion data from the Gemini API and combines it with closed-won revenue data from our HubSpot. Now I can see campaign ROI, cost per qualified lead, and pipeline value all on one page, segmented by campaign. It's already showing that two of our keyword groups are barely breaking even 😬.
My main question is: has anyone else tried something similar? I'm worried I might be missing something in my calculations, like attribution windows or how to properly handle multi-touch leads. Also, if there's a simpler way to do this that I've completely overlooked, I'd love to know before I present this to my manager next week.
Thanks for any advice you can offer. 🙏 jane
That's a solid foundational approach. The specific points you raised about attribution windows and multi-touch leads are precisely where these models break down. A single-touch, last-click attribution (which is what a simple join between Gemini conversions and HubSpot closed-won likely gives you) consistently overvalues bottom-funnel activities and can make upper-funnel campaigns appear to be underperforming.
You mentioned you're pulling from the Gemini API. Before you present, you need to audit what conversion event you're actually using. Is it a site visit, a form fill, a demo request? Each has a different typical latency. For B2B, the time between a form fill and a closed deal can be 90+ days. If your dashboard only looks at the last 30 days, your ROI on new campaigns will look artificially terrible.
A simpler way to start, though less precise, is to use a fixed attribution window for all campaigns, like 90 days, and apply it consistently in your Looker Studio date filters. It's a blunt instrument, but it's more defensible than assuming all conversions close instantly. For multi-touch, the simplest next step is to pull the entire lead's touchpoint history from HubSpot for any closed-won deal and see what percentage of them had a Gemini touch at any point. That's your influenced revenue, which is often a much larger number.
Really glad to see you thinking about attribution windows, that's the heart of the challenge. The user's point about conversion event latency is spot on, especially for B2B. Your Looker Studio setup is a great start, but it's probably assuming a direct 1:1 match.
You might want to build a "look-back window" into your data blend. Instead of joining conversion to deal date, try joining spend to deals closed within, say, a 90-day window *after* the conversion. That gives you a better shot at capturing the full lead cycle. The tricky part is you'll then have to decide how to allocate revenue if a lead interacted with multiple campaigns in that period. 😅
Also, check if the Gemini API has a field for `conversionType` or `conversionLag`. Sometimes the raw data has hints about whether it was a first-click or last-click conversion, which can help you weight things.
Prod is the only environment that matters.