Orange Marketing | Blog

💰 How to Create Marketing Revenue Attribution Reports Using HubSpot

Written by Rebecca Gonzalez | May 01, 2023

Finally, the holy grail. Our perspective on exactly how to create 💰revenue attribution reports using HubSpot. Yes, you can NOW answer the annoyingly simple question from your CEO, "What marketing efforts resulted in revenue?" But we have to warn you; it isn't going to be easy. 

[WARNING] Years of wrangling with marketing attribution data in HubSpot culminated in this article. It is detailed, long, and strictly one team's perspective. You have to be an Advanced HubSpotter to absorb this nonsense.

Posts in This Series

What is marketing revenue attribution?

Revenue or marketing attribution is a data analysis method that connects different data sets (typically marketing contacts and sales deals) to determine which marketing channels and assets result in revenue. 

  • CEOs and CFOs want to know: Does our marketing expense result in incremental revenue? 
  • CMOs want to know: Which marketing channels and campaigns generate the most revenue?

We have had our fill of complex revenue attribution models and marketer cocktail party debates around direct and influenced attribution. Which channel generates the most revenue? Why do we continue to do B2B retargeting when we can't prove ROI?

Sometimes we wax philosophical and say, “A rising tide lifts all boats.”

Meaning the whole is greater than the sum of the parts. At Orange Marketing, we argue that every bit of spending and marketing effort, if done thoughtfully, from social media to content, to paid media works together in a combined fashion to bring in your leads. 

Our analysis is: the pancake stack.

What we explain in our eBook, The Truth About B2B Marketing, “is that each marketing activity does not, cannot, solve your revenue problem on its own. You will not get that lucky. You need a variety of interconnected infrastructure components and activities.”

What Is Marketing revenue attribution at its most basic level?

When our backs are pushed against a wall, and our customers want to know exactly what marketing activities generated new contacts, which in turn generated new deals, and what percentage of those deals resulted in closed/won revenue, we will resort to a standard, simple marketing revenue attribution model.

Fundamental attribution metrics:

  1. Did our campaign create incremental new Contacts?
  2. Did any of these Contacts become Deals? From what campaign/source?
  3. Did any of those Deals result in Close/Won Revenue? From what campaign/source?

And the shocking bit: HubSpot Marketing Pro out-of-the-box metrics cannot concretely answer these questions. We are sorry to disparage the only Marketing CRM we promote. But the truth is the truth. And we thought you would want to understand what gets in the way.

In our previous post, “HubSpot Marketing Campaigns: The Good, The Bad, And The Ridiculous,” we show you the flaws with HubSpot’s campaign reporting and marketing revenue attribution. Now, we will demonstrate the gymnastics we go through to attribute marketing revenue decisively and finitely for executive reporting. 

Marketing revenue attribution  Reporting Using MQLs

Okay, throwing away campaigns in general, is there an easy way to get pure marketing revenue attribution utilizing simply MQLs created and then running a HubSpot Custom Report combining Deals with MQLs (Contacts).

The short answer is NO.


The report that our CEOs and CMOs have asked us to create and that we cannot create automatically using HubSpot is pulling a list of MQLs, and then pulling the associated Deals created AFTER the MQL was created (associated with that particular contact).

The reasons you can't do it are two-fold:

  1. When pulling a Custom Report, you will not get a unique deal record if multiple contacts are associated with a Closed/Won Deal. You will get multiple lines and double reporting. No way around this, and we have the HubSpot Help Desk Tickets to prove it.
  2. There is no easy way to add date logic, such as Deal Create Date has to be AFTER MQL Create Date (because HubSpot can’t easily compare dates in reporting). 
How did we get around this problem? Again two-steps. And, any brainiac who can code and write apps, who does tricky things with reporting software, could do this much more straightforwardly than us. Here are our caveman tactics:

  1. Create the joined Contacts/Deal table using a Custom Report in HubSpot and export the table to a Google Sheet or Excel. 
  2. Either manually or with code, check that
        1) There is only one record per Deal (e.g. deduplicate rows) and
        2) MQL Date is <= Deal Create Date

So let’s quickly break it down

HubSpot Custom Report

Here is the custom report with all the fields you’ll need to analyze your MQL to Deal flow

This gives you a report that you can export to Excel or google sheets, and it has every element you are curious about.

Excel or Google Sheets and Manual Manipulation

You can then open the file in Excel and 

  1. Ensure that the Deal create date is AFTER the MQL Create Date
  2. Data must be de-duplicated, only one deal per row. You can pick which MQL to keep (haha keep the one that improves your metrics - duh)

Holy Grail Charts

At that point, you have data that allows you to create these types of charts, which are the holy grail for marketing spend justification:

Total Deals $$$ - Creation Preceded by an MQL

Won Deals $$$ - Creation Preceded by an MQL

And we can show Deals $$$ by Source or by Campaign

We can manually bang out these charts for a desperate executive in an hour. 

HubSpot may point to the Funnel report and say it gets you there. It simply does not get you there. It is a contact-related report. The problem with MQLs to Deals is it correlates the HubSpot Contact object to the HubSpot Deal Object, and the two do not compute.

MQLs to Opportunities to Customers is all CONTACT data. What the marketers want is MQL correlated to DEAL data. 

Out-of-the-box Attribution Reporting, which you get with Marketing Pro is all Contact related, with no Deal correlation.

HubSpot might point to the fact that the deals have limited Source data and could be used for summary Deal data. We agree, and we use this information in high-level dashboard reports. But honestly, it is not exact, and we lack the data behind it, which invariably executives will ask for. From HubSpot:

Analytics History

  • Original source type: the original source of the associated contact, or associated company if there is no contact, with the earliest activity for the deal. 
  • Original source data 1: additional information about the original source for the associated contact, or associated company if there is no contact, with the oldest value for the Time first seen property.
  • Original source data 2: additional information about the original source for the associated contact, or associated company if there is no contact, with the oldest value for the Time first seen property.

This deal-level source data does not include any campaign information at all.

And for many of our clients, the data looks something like this. And tells them nothing.


HubSpot Marketing Enterprise Revenue Attribution reports

And, if our marketer upgraded to Marketing Enterprise? Would they see the Revenue reporting of their dreams? Let's check it out! We have access to just one HubSpot Marketing Enterprise account. We remain unconvinced that it gives you what you need. 

Here’s that “Influence” word again.

This report came close because when you click on this bar, you can see contacts associated with deals, like our manual pull. This particular client had no deals related to any campaigns.

This is still a contact report. When you click on the bar, you see a list of contacts. Again, this client not using campaigns (just getting started).

Our Conclusion

We have shown you what we do to give our B2B clients Marketing Revenue Attribution reporting so they can objectively analyze their marketing spend and show ROI on campaigns.

But how we do this is somewhat painful and convoluted. Not to mention manual and error-prone. Sure, our process could be improved with automation and a BI tool. But show pity on us, HubSpot. All we need is a marketing revenue attribution process and reporting stack that would satisfy the basic needs of marketers. If marketers want to get fancy and play around with different revenue attribution models, let them. Out of the box at the Pro level,  it would be lovely to have something that we can all agree on. 

HubSpot Product Development Ask

Here is hoping that these two blog posts of real-life marketing measurement challenges and our somewhat painful solutions inspire further conversations and possible product changes to HubSpot Campaigns and Revenue Attribution at the HubSpot Marketing Pro Subscription Level. And maybe even the Enterprise Subscription level because we honestly doubt Enterprise solves the problems we have described. But we could be wrong. And we realize HubSpot's strategy is to bury more functionality at the Enterprise Marketing level. Unfortunately, this is out of reach for the clients we serve. And as marketers, we believe marketing attribution is table stakes for all. 

Posts in This Series

How Marketing Revenue Attribution Reports Make Us Feel

 

Additional Resources