Marketing Attribution

The often heard, and now quite hackneyed quote about advertising spend is:

“I know that half of my advertising budget is wasted, but I’m not sure which half”

As mentioned in my first post, this is quite a distressing position if you work in marketing today – it just doesn’t feel good enough to say to your boss “I know we spent a fortune on advertising/webinars/events/etc last year, but I really have no idea which of those made a blind bit of difference to our revenue!”. Also, of course, we are often in a position of quite tight budgetary constraints – should that last $50k go on an event, or on a new webinar series?

To start on a negative, I think there are certain types of spend for which you will never be able to measure any sort of accurate ROI. Banner advertising to drive brand awareness is one of them. At Red Gate we spend a decent amount of money on banner advertising, primarily to raise awareness of our company and our products (particularly new products…). We don’t get many click-throughs from these ads, but that’s not the point – their main value is eyeballs while potential customers are browsing their favourite websites. This is unmeasurable – even if you just counted “Number of times seen”, how do you know if people if noticed it or not, or liked it?

For this post I wanted to write about something that was more measurable. A good example is a conference – the long term benefits may be very hard to ascertain, but I think it is possible to measure short-medium return. NB: we use two different approaches to marketing attribution at Red Gate that come at the problem from two different angles:

  1. What revenue did activity X generate?
  2. What were the different activities which contributed to revenue Y?

There are some great posts about the 2nd problem, including:

…as well as some interesting technical articles about how to instrument your website (whether Google Analytics, Kissmetrics or whatever) to allow you to measure properly.

But I want to concentrate on the first approach. I went to a conference in Texas last week and we spoke to a lot of people. Was this just a jolly (a jolly involving a large number of mammoth steaks) or are we going to generate any revenue from it? Also note we go to conferences for a lot of different reasons, but generating leads is definitely one of them!

Below is the approach I have tried recently to tackle this problem. The logic behind it is that, though we speak to individuals at a conference, the revenue from that individual could come from anyone else in their company (I.e. Dave spoke to us, but it’s actually Pete in the purchasing department who orders from us). This means that you have to measure revenue from all of the companies represented at the event. The big danger here of course is that if you saw Dave from IBM, Texas at the conference and then someone from IBM in Malaysia (who has no link whatsoever with Dave) orders your product, then you can’t attribute that revenue to the conference – there is a danger of over-estimation.

The other part of the model though is looking at the history for an individual and company. Red Gate has a lot of existing customers – half-a-million end users, give or take, and there’s a pretty good chance that any company we talk to already has some of our software. But if we argue that “Company X already knew about Red Gate before we spoke to them, so we’re not making any difference at the conference” then we’d never go to a conference again – I.e. there’s a real risk of under-estimation.

My (best-attempt) approach to fixing these problems is to carry out the following process a few months after the event:

  1. First, and most obviously, you need to enter all lead details in to your CRM and tag them with the event
  2. Once this is done you need to write a horrible query to pull out the data required. If you have some sort of wonderful infrastructure that provides this data for you, then great, but if not, you’ll need to get your hands dirty.. The query I use, in embarrassing pseudo-SQL is:

    SELECT all of the individuals in the CRM who work for companies where someone from that company was tagged for the event

    For those individuals, pull out all revenue that has been generated afterthe event

    Also for those people, pull out the previous 12 months of "activity".

    This last item includes information such as invoices raised, downloads, money spent and so on – the purpose of this is to try and build up a context for the future revenue. If someone was about to buy something anyway, that’s a very different scenario to a brand new customer who’s never interacted with you before.

  3. I then apply a couple of filters to this list to make the job more manageable:
    • Remove companies where no money has been spent by that company at all
    • Remove individuals in companies where there has been no historical activity for the last 12 months – if someone bought something 3 years ago and hasn’t interacted with us since, I count that as a “new customer” for the purposes of this exercise
  4. This will give you a table that looks something like the following (click to enlarge):Marketing Attribution Spreadsheet
  5. The next, and most interesting/difficult step is to apply a percentage to each row in an attempt to figure out what proportion of the revenue generated post event can be attributed to the event (purple column above). For example:
    • If a customer didn’t exist on our CRM system before the event and we showed her product A at the stand and she, herself, then went on to buy 10 copies of product A straight after the event, then we would attribute 100% of that revenue to the event
    • In contrast, if one person already had a quote open for product A, and someone else from his/her company went to the conference and was demo-ed product B – and the first person then went on to accept that quote for product A, then it’s hard to argue the case that that revenue can be attributed to the conference demo. It’s not the same person, it’s for a different product and, most importantly, they were already convinced to buy, they just had to be closed by a sales person.
  6. To help validate these percentages, we also phone up a sample of the customers on the sheet and ask something like “How much did the demo at the event help you decide to purchase Red Gate products?”. It’s a hard question to answer, but if they say something like “It was instrumental – once I saw it, we had to have it!”, that’s very different to “Sorry, don’t remember any demo..”
  7. The next steps depend on the volumes of sales you’re talking about. Of course, for a high value/low volume business, you might only get one sale from a conference, months and months after the event (if you’re lucky!). In contrast, for Red Gate (high volume/low cost), we get higher numbers of sales. In the former case, you can probably apply percentages by hand for every item of revenue generated. For high volumes (for example, if there were 50 sales or more after an event), you might need to come up with some rules for calculating these percentages.

Finally, at the end of this process, you come out with a figure by multiplying all of your percentage figures by the revenue values and summing these up (i.e. a sum-product). In the example above, the total attributable revenue from the event would be:
$1000 x 25% + $2000 x %90 = $2,050

Of course, this is only approximate – how can we possibly apply a number to the complex process that goes through an evaluator/purchaser’s head before buying the tool? Was the demo really influential, or was it the follow-up work they did reading reviews and blogs? Would they have found the tool anyway? Did it help convert a pre-existing quote, or would that have gone through anyway? Impossible to know, but we’ve found the above process, to at least be a better stab at working out the return on certain types of activity –  particularly when that activity takes place at a specific point in time.

And hopefully, of course, once you have this number you can answer the question – “Was that conference really worth it?” with just a little more confidence than before.

Read More