The Traffic-to-Revenue Question Every Executive Asks, and How I Built the Model to Answer It
How I answered the question most marketing teams avoid without waiting for a data pipeline that was a year away.
This article covers how to build a traffic-to-revenue model without a data engineering project, including the three questions every executive asks, what the model includes and excludes, and why a Google Sheets calculator was the right tool for the job.
Leadership asks marketing three questions that sound simple and are not. How many visitors do we need to generate a specific number of business opportunities? How much will that cost? And how long before it shows up as revenue? Most marketing teams can report how much traffic they got. Very few can answer any of those three with a straight face.
The honest reason is that people assume answering them requires a data engineering project connecting the website to the customer relationship management system. It does not. I answered the first question with a model built in a spreadsheet, and I was deliberate about which of the three questions that model could honestly answer.
The question behind the questions
When leadership asks about traffic, they are rarely asking about traffic. They are asking whether the money spent to acquire visitors produces real business opportunities and what it would take to hit a target. That is a question about the relationship between two numbers that most teams never connect.
On the website side, I could see, in our behavioral analytics, the rate at which general traffic produced high-intent conversions. On the customer relationship management side, in a Salesforce report, I could see the rate at which website submissions became qualified opportunities. Both numbers existed. Nobody had connected the two ratios into a single model.
What I built, and what I did not
I want to be precise, because the difference matters in an interview and on the job. I did not build a live integration between the two systems. There was no automated pipeline and no real-time data flow. What I built was simpler and more useful:
- A dashboard in Data Studio, formerly named Looker Studio, presenting the behavioral data from the website side
- A Salesforce report whose results I added manually, on a regular cadence
- A calculator in Google Sheets where a stakeholder enters a target, for example a number of opportunities they want, and the model returns how much traffic that requires, working backward through both conversion ratios
I designed all of the logic: the structure of the model, the order of the calculations, and the inputs a non-technical stakeholder would actually have. I then used artificial intelligence tools to handle the mechanical build of the sheet. The thinking was mine. The assembly was automated.
Why I refused to put a single cost number in it
The model answered the volume question precisely. It did not compute cost, and that was a decision, not an omission. Cost was not one number. Traffic came from different channels, each with its own budget, its own acquisition cost, and its own pricing model. A single cost figure would have been a confident lie. So the model gave leadership the volume target they needed, and cost and timing could be applied per channel by whoever owned that channel. Knowing what not to put in a model is part of building one that people can trust.
I did scope the automated version. Then I made a call.
We did look at building the automated, integrated version. It would have taken roughly a year and a significant budget. Rather than let the answer wait a year for funding to clear, I built the simpler model and delivered the answer that quarter. The funded automation could come later. Given the choice between a perfect system in a year and a useful one this week, I chose useful, with the full cost of the alternative in front of me.
Choosing the simpler tool was not a limitation. It was a resourcing decision made with the full cost of the alternative in front of me.
It was half spreadsheet, half relationship
The part that made the model possible was not the math. It was the operations team that owned Salesforce and ran the analytics on their side. They shared their reporting with me, and over time they came to treat me as the analytics resource they checked their own thinking against. The cross-system view existed because that relationship existed. A connector would have given me their data. Their trust gave me their context, which is the part that made the model right rather than just complete.
It also meant the model did not depend on a system only one person could run. Anyone who understood the two ratios could open the sheet, change the inputs, and get an answer. Simplicity made it durable.
The takeaway
If your leadership keeps asking how traffic connects to revenue, resist the urge to scope a year-long project before you answer. Find the two ratios you already have, one from your behavioral analytics and one from your customer relationship management system, and connect them into a model a stakeholder can use without you in the room. Be honest about the questions the model cannot answer, like cost across mixed channels, instead of faking a number. You will answer the real question this quarter, and you will build the relationships that make the next model possible.
This article is a summary of Case Study 2, available in full inside the Work section upon request.
The question nobody could answer
At an enterprise life sciences platform, leadership regularly asked marketing to connect website traffic to revenue outcomes. The website analytics and the CRM were separate, with no automated integration. A full engineering project had been scoped at roughly one year and significant budget. The answer to leadership’s question was being deferred indefinitely.
The gap was not a data gap
Both sides of the equation existed: the website conversion ratio from GA4, and the opportunity conversion ratio from Salesforce. Nobody had connected them into a single model. The question was not technically hard. It was organizationally unresolved, because the systems were owned by different teams and the integration was positioned as an engineering problem.
What I built instead
Rather than wait for the integration, I built a practical working-backward model using tools available immediately. A Data Studio dashboard presenting web behavioral data connected to manually updated Salesforce figures. A Google Sheets calculator where a stakeholder enters a target number of opportunities and receives the traffic required, working backward through both conversion ratios in sequence.
The deliberate omission
I left cost out of the model deliberately. Cost varies by channel and pricing model, and a single figure would have been a confident misrepresentation. The rationale was documented inside the sheet so each channel owner could apply their own cost and timeline to the volume output. A model that is honest about what it does not know is more useful than one that fakes completeness.
What made it work
The model worked in part because of a relationship, not a system. The operations team that owned Salesforce trusted me, shared their reporting, and came to treat me as their analytics resource. The cross-system view existed because that relationship existed. Knowing what not to build is as important as knowing what to build. The right model is not the most sophisticated one. It is the one that answers the real question honestly, ships this quarter, and works without you in the room.
This article is a summary of Case Study 2, available in full inside the Work section upon request.
Request Access to the Full Case Study