In-depth Marketing Attribution in Salesforce

Hello, and welcome to another X Force virtual summit presentation. Today, I'm happy to introduce Kevin Deeney. He's a digital marketing app analyst at a company called CallSource, which does a whole bunch of interesting things with telephone numbers that I'd never heard of till I met Kevin, and he's gonna tell us about that. He's gonna tell us about attribution, which is like the big deal at CallSource, and I'm sure it's a big deal at a lot of other places. So here's Kevin to explain more of that to us.

Yes, I'm Kevin Deeney and you might be wondering who's this guy.

So I work for CallSource and a little bit about me I guess would be a good place to start here and that's figured this would be kind of a good way to do it. So who I kind of see myself as? I have four kids and it's busy, it keeps me very much on my toes and my lovely wife and my family, we live in Southern California, my company is based out of Westlake Village, very close to Thousand Oaks, which I guess if you don't know the area, it's Southern California, so we're Pacific time. And what my coworkers kind of see me as is they I usually get like two nicknames here, one of them is like a wizard because apparently I do magical things with data and the other one is that is like the Batman of analytic stuff because I have Batman stuff all over my desk and people here know that I love Batman.

It's actually like I put a little virtual background so you can see it right here. I love the animated series. So this kind of Gandalf on the computer is always great with the spinner hat of kind of how I'm seen here at my company. I sit in the marketing department but I'm sort of halfway between marketing and analytics data science y edge.

So I do a lot of marketing things, build email programs, I build and run our advertisement acquisition, I do a lot of our web analytics, I basically handle all of the analytics and reporting for our marketing department, and I've been involved with a lot of projects involving data, data governance and information monetization here at Call Source.

And so that kind of leads me to the last thing is like what my customers like what do they see at the end of the day that I do?

Because I do a lot of analytics, they don't necessarily see that, but I do run our acquisition and advertisements stuff and I've heard many times, especially from our own employees who just so happen to hit the right page and get pulled into my audience that they feel like I follow them everywhere with my ads and retargeting. So that Johnny Depp running away from all my ads is like a perfect sort of emotional capture of what it feels like for them.

So I thought that would be a good little snapshot of me and what I do.

Call Source, the company I work for, are, Laird kind of tipped it off there a little bit, we're like a very old one of the original call tracking companies, we actually have the original patent for call tracking, that's how old we are, that's how long we've been in business, and yet at conferences everywhere I go everyone's like, what is call tracking, what are you talking about? So to give a little background or give a little insight into what it is I'm actually talking about, because I'm not talking about tracking boyfriends and girlfriends, it's like a huge request we get, you'd be surprised, but it's actually done here in the lower left of the slide, you can see it says, okay this is what we do, we supply millions of tracking phone numbers to businesses and they put these phone numbers wherever they want customers to call them from.

So it could be their listings, it could be mailers, we do this on the web with digital visitors, so visitors that hit your website. And we also have numbers that spell things, so I'm sure there's a lot of common jingles that you can think of.

One-eight hundred FLOWERS is usually the most famous one out there that people have heard of, so we do things like that. And then since there's a unique number on all your marketing, that's where we get attribution from, is we could tell okay if it's on this campaign and they're calling you, we know which number they saw because that's the number they call. So because you have a unique number and all these different marketing channels or assets or even attached to keywords and stuff like that, we can see which marketing component asset, whatever, is driving calls for you.

And then the last thing we do, this third box along the right here, is we give you the analytics, the insights, we also listen to calls, score calls, and by that I mean we tell you if a lead is calling or if it was just grandma or her girlfriend calling. We tell you if an appointment ended up being set on those things or if you know, some kind of positive conversion result on the end of a call, we tell you how many you're getting and based on the sources you can see which calls, what places are sending you the best calls. So that's what CallSource has been in business for for a long time, and we do offer businesses all kinds of solutions to fit their needs, but we tend to be in that space of the phone call component of conversions in terms of all the things you're measuring. So that's call source.

Attribution, another take on an animated series, but attribution is like the ghost of our call source machine, it's like attribution is a lot of what we're about because a lot of the things we do is attributing. We listen to calls, we track calls, but the heart of it is we are all about attribution.

So I also wanted to kind of define how I see and we see attribution here, and that is that attribution is a lot less about what's better or worse, even though that's kind of what everyone uses it for, and it's more about tracking, let's say, the influence of things over time. So that's kind of how, to me a better way of looking at attribution is not to compete things against each other necessarily, but to see their influence over time because things have even small things can have big impacts. So attribution is a little dicey and that's why I think this presentation could be very helpful for those of you who are trying to tackle the marketing attribution hurdle. I want to talk about now let's get into it, Let's get into the meat, marketing attribution.

So this came out of I mean it's always been something in marketing you're always trying to do, but were tasked I was tasked with, okay Kevin, go get me tell me the revenue that's coming in.

So the actual specific ask was, okay, I need a monthly financial breakdown of all the marketing attributed revenue with a couple different dimensions. Meaning like, I want to see the revenue that came in from this department, division, vertical, grouping, so I could see how marketing is impacting revenue across our business sectors or our markets. So I put a little example here, it's not real information so don't worry I'm not late. This ask was, it was like okay tell me what how marketing is performing here.

So I guess the big takeaway I wanted to kind of get across was like, doesn't have to our ask was very simple, and it didn't require me to make insane business intelligence charts or interactive drill down reports or anything like that. Our ask was actually fairly simple, just tell me what you did, right? And even that has huge struggles and issues in trying to get it. What did we actually do at the end of the day, and how can we prove that?

So a little bit, I think, is what is important when you do any attribution, actually I feel like when you do any sort of database reporting, or you're preparing anything for anyone, is it's really important to know who's asking for it, like who are the stakeholders or who's going to be the person at the end of the day that's going to be looking at this information.

So I don't know if you should always prepare a little dossier like how I pictured it here, but over time I think you develop a pretty good understanding of the kind of person or people that you're going to be providing this information to, and one of the reasons why my ask and that single page report was the way it was, is who was asking for it? So my boss, which previously was the CFO and is now the president of our company, he has a very long history of being in the finance industry, so he's used to seeing income balance sheets, statements, things like that. So that's where he was like this is how I want to see it and why it is being presented that way, because that's how he wants to see it. If he wanted to see charts, things like that, we could make it, but that's not really what he wanted, and that's usually not what he wants to see.

So he's very much a numbers guy, so that's why all along there is numbers, and they're used to seeing it that way, and used to reading through a sheet very quickly that way, and so when we have a conversation, I've understood the sheet because I've made it, and he can look at it and he knows that kind of sheet template already. So we have a very, it's very easy for us to get right into what matters instead of spending a whole bunch of time trying to explain okay what this number means, what that number means, why this is there or whatever, we can get like straight into it. So I think that was like a pretty decent format for getting straight to the meat of the ask.

So I think with a couple things I wanted to cover were the constraints and by that I mean like what were the issues, struggles, limitations and stuff like that that my team had in getting to marketing attribution. I think these may be common with some other departments and other teams out there and so these were the ones that were the most poignant for us that we really had to overcome.

And so the first one was operational constraint, this one might be fairly common for you guys, and it starts off with like we had problems but these were problems caused because of the processes that we had in place already. And in trying to obtain marketing attribution information, we needed a lot of information that was sort of in siloed areas of the company, and that also meant that when I needed something from another department that they had to figure out how to get it for me. So we, for one, we had a lot of non Salesforce people having to go into Salesforce for us because Salesforce is where we try to push everything, that's our CRM, where we kind of try to keep all our information together.

We try to limit how many different platforms and systems and silos basically our data is in. And then no one can I don't think I've ever heard of anyone having the perfect system out of the gate that has everything in one place, we just have different types of systems that are meant to hold different types of data? And a lot of marketing tools and stuff like that, you may have found, don't really integrate well with every other tool.

And then we also needed the final reports to come out in financial statement format, so that meant like, okay, our current processes aren't set up to do that, we can't just press a button in Salesforce and it's going to print out that exact template sheet. So there's a couple times where manual things had to come in and change it up, but basically we looked at it like okay, yeah we can do it, but man there's a lot of operational processes we'll have to put in place, like who's going to need to send what information to who and to where and in what order. So that was quite a lot of meetings where it felt like nothing necessarily got accomplished but we just planned out how we were going to do everything. I kind of mentioned this, data's in silos.

So looking at this as a macro, for us, our data, I think I've mapped out about twenty one marketing data sources we have, but not all that information is important for telling me revenue, right? So in this instance it may have just been ten or so that I needed information to get pulled in so that I could do this, but I can't just send all that data into Salesforce because Salesforce maps everything to records, Like people, the different objects like leads and contacts, accounts, opportunities, deals, things like that. Those are like kind of information about something very specific, whereas marketing data may just be like okay here's a visitor cookie ID, or here's a visit time, or here's an interaction with a specific email or something like that.

Sometimes they have the right information, sometimes they don't. Like with phone call data we have the phone number, with emails we have an email address. If they don't have a way to stitch them together that's sort of a problem. So we wanted Salesforce to be the center of truth, but we can only put stuff in there that was designed or is capable of being put in there, and we couldn't overload Salesforce, which is a ton of information.

So some of the data had to stay out, but we still had to be able to connect it, which is where a lot of Integrate.io's ingestion capabilities for being able to let systems and silos stay where they are in a sense, but only need to stitch together the data that you need for a specific query or a specific task.

And we love the unicorn example. This is like a joke we always say and tell everyone, especially we joke about it with sales teams, and if it's not in Salesforce it doesn't exist, just like a unicorn.

The next constraint we had was quality. So with marketing attribution, quality kind of refers to our ability to prove something happened.

The way we work here is if we send an email out, we don't just get revenue attribution for everyone that got the email, we track it based on the engagement. So if we send an email to ten thousand people and ten percent of them opened and then ten percent of those opens clicked, then it's not still we wouldn't count attribution yet, we count it when they we call it raise their hand.

So when someone says yes I want to talk to sales, which is like a very slim or kind of a tough mountain to climb sometimes because not every marketing channel, not every marketing thing is really designed to do that. So in the back end we also track, like on the marketing team we track influence, which let's say is like, okay if someone clicks, that's kind of like they're interested a little bit.

Sometimes teams use scoring models for this to kind of accrue like okay we're going to pass it over when we think they're ready, even before they've said yes, yes, call me, want to talk to your sales team, I'd love to talk to sales. That doesn't happen all that much, that's pretty rough, hard. So it came down to us because in so many conversations we were having we were like well how do we prove it? Marketing is always going to be denied attribution unless we can prove it, that we were told okay, just stop.

We are now going to have this rule where eighty percent accuracy is okay, and that might make a lot of people who are especially data scientists cringe a little bit. It makes me a little sad because I think at the end of the day how much we could be misled a little bit, but at the same time it takes a little bit of the burden off of trying to be so precise and so perfect with everything and prove everything done to a perfected little bit because it's really hard, especially with top of funnel marketing, to prove that anything's happening.

So eighty percent for us is kind of like our baseline, like let's get there, and a little bit of what that causes is it does reduce the manual stuff we sometimes have to do, because to get to ninety or ninety nine percent may actually take ten times more effort, but manual is still a huge cost, especially right now with all the things that are happening in the world, it's very important to be as productive and as efficient as you possibly can. So if there's a way to take something that's manual and make it automated, a lot of people are trying to do that right now, but we had to start everything manually.

So before we could prove marketing attribution we kind of had to lay it all out, do it all, but we had to do it manually. And so that meant guaranteed high cost of labor going into this project, information that like if information's in my head, it's not well documented or if I leave or something happens to me, we always joke here like what if I get hit by a bus, what are we going to do about it? So those are like real things that companies should be looking at and teams and managers should be considering is like, okay there's a lot going on right now, there's a lot of things happening, if I lose people what happens?

Am I still able to even complete this project? So that's a real concern, a real thing that's kind of been happening for us is like, well okay who's going to do this now? So at the end of the day too, amount of manual stuff we had to do to get this off the ground was a lot, so there's a lot of time, a lot of people have different projects they're working on that have deadlines, so it meant a high stress environment, it wasn't the funnest activity we've gone down, but we were still able to get there. So I kind of wanted to show you what I mean by manual process, Leonard and I talked about it a little bit beforehand, and it's thought it would be a good idea to have an example of what I mean, because I don't just mean like oh it's manual, like I'm turning a wrench in somewhere, it's that when information kind of crosses silos or something happens in between there that maybe let's say Xplanning would do for us, we start out by doing it manually.

So the output of let's say our attribution model kicks out like okay here's the opportunity ID and the attribution whether it was marketing or not marketing that gets credit. Well initially that was okay, know which ones get credit and which ones don't, we need to put that back into Salesforce now that we've, you know, sticked all this data into the warehouse and now we need to take it and put it into Salesforce. So initially that was manual and it's still a little bit manual right now because we're still ironing out all the kinks, but we have a field on opportunities in Salesforce, which is like a deal or something like that that's happening, a sale that's in progress, and how we track sales in Salesforce.

We have a little field for attribution and that would be marked yes or no basically, right?

And then that way you could run a report in Salesforce, which is in the lower right.

So that's kind of what my report looks like, I'm running a report to say okay which marketing which opportunities does marketing get credit for? Should they get credit for? And then that information is passed over to our accounting team. And the accounting team are the non Salesforce users because nothing they do really is in Salesforce.

And Salesforce for them is just like a big headache.

I jokingly put on here like an old computer because that's how it feels to me, their process, their things going on, but they're just ultra accurate, ultra precise with what they do, they're just not used to the same systems. Marketing is flying all over the place with new tools and innovative things they're just very much like, we're used to this and this is how we want. So we had like a clashing departmental culture there, but you know, at the end of the day, this got us the results we needed, went to them, they were able to confirm which contracts closed and which had receivables in them and stuff like that.

I kind of just mentioned it, right? We had cultural issues. So this is one of the best water cooler event conversations that I've had at events and conferences, is like oh man, what are the cultural constraints you're dealing with? What does your manager say?

What does your team look, how do they look at things? So one of the things that I don't think it's been solved, I think this is just like a struggle, is our culture here. So marketing attribution, it gets a little technical right, because a couple times it's been asking me why can't you just tell me, why can't you just go and look and tell me what the revenue from marketing is or what the impact of it is. And every time I'm like, oh it's not easy, it's not just there, I have to use a model or use something and confirm with everyone like yes, this is what we're going to consider is attribution, this is what we're gonna consider is credit for marketing, and it's not easy.

So at many companies that I've been with and that I've worked with, there's like a serious lack of trust in marketing. I think that that probably comes from the fact that it's almost been a cost center for a long time, so it's just been a place where we put money and we probably should emotionally get over the fact that maybe it's going to disappear forever, it's a sunk cost, we may never see it come back and you know marketing may do their dance and pretty thrills and make it look like it's impacting but generally I feel like marketing as a department lacks like a serious amount of trust. So when it comes to like proving revenue, marketing has to go to the moon to prove that this thing happened. But a lot of the other departments, they don't have to work that way, they just say, even just someone could say, Oh yeah, I think I did something there.

And they'll get credit for it or they'll be trusted in that. So something we were even told by people here was like, wow, marketing has to do all that to prove its attribution, that's a lot.

Then in a completely ironic opposite way, right, this is where it's hilarious, we'll like over prove like big huge manila folder full of like this is just metaphorically, right? Big huge manila folder of proof of look at this is all the deals we influenced and most of the time we hear is like, look, I don't care how the sausage is made, I don't care how you did it, it's fine, trust you right now. But then every once in a while there's that like, oh, I don't know about that, or I don't think so, and then we have to come back with the huge manila folder and have it. But if we always bring it, it seems like okay, you're doing too much, right? I don't need to know all the little details, just give me the final numbers.

But every once in a while we're asked to prove it or asked to go into it or drill down and so we kind of better have it around. So it's like a weird irony right? We don't really trust you and yet we trust you. So marketing attribution is like a double edged sword sometimes and I don't care what the sausage made is like a great quote a friend, a co worker here said and I've always just thought of it.

Okay so now let's get into what we actually did. So you understand what we were asked to do which is get monthly revenue for marketing and be able to put it into different dimensions and have proof of what happened, and then you also know, okay, what were the things that were keeping us from just doing that really easily? What were the things that were causing us issue? Which may be some things that are the same kinds of things that are causing you issues. So I want to go through how we were able to overcome the constraints and ultimately how we did our attribution. This is one of those Franklin Covey quotes, we actually have posters of this stuff all over our office, but keeping the end in mind is really important. So by that I mean, okay, we got to know what the end report, the end need is for the data and how it's going to be used before we even start.

We've gotten into a lot of issue and struggle and just wasted so much time making a lot of assumptions about what is needed, where it needs to go, how it's going to get used, how often it's going to get used, what its application is, and just completely misfired on that. So I would strongly suggest everyone who begins any sort of a project very much try to scope out what exactly the end is, otherwise you're going to waste a lot of time. And that happens with marketing attribution a lot because it's so much work, like doing it over again is just soul crushing.

So one of the ways to get around that is, at least the way I've figured out how to do this and it works for me pretty well, is if you can turn every request into a question back to them and try to restate or rephrase exactly what they want, then you can usually get clarity. That seems pretty simple, but it's very effective. So I put a little example conversation here and it would be like, okay this is my boss right? I would like to see how marketing is performing each month and then I say, oh so you want to see monthly recurring revenue of all marketing campaigns by month?

Because right there I'm basically saying, okay, want to see the monthly recurring revenue, you don't want to see revenue minus expenses, want see profit, you want to see if you want to see this monthly, you want see it annualized, I'm trying to very much like specify the kind of metric I need. In marketing campaigns that's a dimension, right? Because we have campaigns, we have them grouped up as channels, beneath campaigns there might be different types of things that they're doing like a specific email or a specific ad or whatever, so what level do you want this? And then by month, I'm just clarifying, okay where do I aggregate to, right?

So I have this big pile of data, if I'm scooping up each month at a time or what am I?

So my boss would say, you know, in this example he'd be like, yes, just the one number. Okay, that tells me I don't need a huge chart for this, just need the number, and let's review it each time we meet. So that's how that's actually how this simple request in the beginning went, and I didn't need to make I realized I needed to make a chart, I just needed the straight number. If you needed to ask more, could you know, the data behind it will be sitting there, and then I just I could aggregate all my campaigns, it made it so I knew exactly what I needed to get to.

So if you by using like a question and answer rule, you can really figure out, okay, what metrics, what dimensions, what everything I need, and sort of like we call it here like a menu of what's requested, and then you can serve them the final, here you go, final result. So that's kind of how I overcome this issue and how it kind of started out. I've talked about the cultural issues, the operational silos, so working together.

This sort of sounds like my kid's teacher telling my kid at school, Come on everybody, let's work together. But it's a little more complicated than that, right, in the work environment.

All these departments we have here are measured differently, so if one department spends a lot of time on this project with you that might completely mess them up versus you may be measured totally differently and it doesn't matter if there's a little bit of waste of time here or there.

And then in this project we had someone who was sort of in like a line, so this person had to finish their job before this person could start and then this person had to wait for that person to finish their job before they could start. So because there's dependencies right in getting things done, it meant that we had to figure out okay, who do I go to when I have an issue and who's in charge of what and what are the limitations the other teams have? So figuring that all out was very crucial and required a little bit of empathy or maybe a lot of empathy.

There was ego clashing, there was authority headbutting, and by that I mean like, okay, we're in charge of this or we're going to be doing this and this is how it's going to go. And it wasn't really like a we're on an even playing field here trying to figure this out together. And there's a lot of emotional roller coaster because sometimes we'd be like, oh yeah, everything's working well, and then a couple days later we'd come back to them and be like, oh man, all that information is all wrong. And then there would be such a high to such a low. So any sort of project that works like this with a lot of data and different teams that are used to data requires a lot of understanding, at least putting it up front, we may not this may not be easy and that could be okay. We may struggle or we may fail at this a lot and that should be okay.

And I need to know or it would be helpful to know the things, the priority that this is in my lab or the different types of things I'm doing. We had teams tell us, look we can't work on this the fourth week because we have this huge project, we can work on it the week after. Okay, that helps everyone understand what's going on. Because at the end of the day, the big picture right is like everyone wants everything to work, everyone wants everything to be successful.

They're not trying to stab each other in the back here. So I think it's helpful to know that everyone's trying their best. It might be a good attitude to have when going about something like this. The gigo is like a data scientist, probably one of the most common phrases I've heard.

We have an analytics team here that crunches all of our call analytics data, so like hundreds and millions of phone calls all the time, and garbage in garbage out is one of the most common things I've ever heard them say. So making sure that the highest quality of data on the out is happening, right, requires the right amount of information coming on the in. So we had to work with we have a Salesforce admin, maybe not everyone has that, maybe like the CRM is kind of just like a shared thing that everyone manages, but for us there was like someone kind of in charge of the CRM and its architecture, and that meant like okay, what are the fields, objects, the values, the taxonomies and formats that everything needed to be in?

With marketing attribution it's critical because a lot of marketing attribution relies on the text or the date or the information, the ID numbers and stuff like that to be case sensitive, so you really can't mess around with oh I typed email instead of e m a I l I typed it I a l, I've mistyped it or whatever, that can't really happen, so how can you control the inputs to kind of make it so that it's really hard to mess up. In that way people will feel better too, they're like oh man did I mistype that? They worry and then could the whole thing fall apart if one little bolt falls out of place?

Those are like pretty serious questions for figuring this out because when you stitch data together, so let's say you're going to stitch like an email to an email, they have to be exactly matching and so sometimes someone will put an extra space accidentally or they'll forget the dot or whatever, so we have, right, we have like the process for connecting the data, but then we also have processes like above and below that validate and check on every point if can do that, right? And that may take extra work just to validate, but it would save time and if you get to the end and you're like wow, this thing only has like five values in it and that's totally off and wrong.

So you can set this up validation things like that. I actually would recommend setting it up with X Plenty or setting up reports in Salesforce where literally their whole purpose is just to validate, right? So we have ones where they're looking for invalid emails, invalid phone numbers, invalid or missing identification or unique identifiers.

All these different things are part of the attribution model process that we have so that at any point along the way I can catch an error and I can figure it out. And I don't need to look at the reports all the time, but at this point if I go in and something doesn't really match the result that I kind of expected I may go check them to see if there's any major flaws.

You may not be able to control all the garbage that goes in, but you can put up some validation stuff in place to kind of check it, see if it's working.

That we had an idea of how, and this is like a year, right, I'm crunching a year down into this example here, we knew how the model was going to work, how everything was going to go.

So my personal expertise is not in data science, I'm a marketing analyst, So I have learned, picked up some SQL and picked up the things I need to do as I do them.

And so I am really comfortable taking things in Excel and stitching them together and modeling how it will work there before I go into Xplanar, before I go into Salesforce and build or into our automation tools and build it all out there. We even have a tool we call Lucidchart where we have boxes and arrows and it figures out how we're going to plan and how the processes are going to happen. We whiteboard, we do all those types of things. And so initially it was like okay, what's important to know for attribution, which is basically all the stitch points and joins that need to happen, the values that represent revenue or that denote attribution or campaigns, things like that, that basically just say okay marketing did this, those things are vital to have right?

And ultimately too, alright, when we're working with functions and formulas we can't have like divide by zero errors, we can't have issues missing or no values or things don't match up and go, we end up like okay, what does all the formulas and everything look like? So I did that all in Excel actually first, which maybe to some is kind of crude, but I'm so much more comfortable sometimes breaking something in Excel where I know it's not going to affect our nodes or anything else that I can kind of keep it in a safer environment for me.

So this is actually my attribution model, you can see the boxes and the arrows, that is our visual process planning tool we use, sort of our way of doing whiteboarding, but my handwriting is terrible so this tool allows us to work this way and everyone can see, okay this is actually like the rules it's going to go through and so each tier or level here represents like an if then statement that's happening that's going through and each if then statement actually I created like a field and a value for. So at the first one it would be like okay was it created by SDR or not created by SDR, right? So created by SDR could be a true false and so all the way down this it's going to have just like it's going to be building like a logic of okay, is yes, this is yes, this is yes, this is yes, and that way I could data would flow the right way, and this is the data flow path to getting to okay, does marketing deserve the attribution or not?

One of the things Leonard asked was like what is this one hundred and eighty three nonsense going on here?

If it's the third level down, right? So at this point it's either SDR or not, there's a campaign attached to it or not, that's what I mean by campaign responded, and then it's like okay what's this created responded less than or equal to one hundred and eighty three? So it depends on that comes out of our product like our pipeline life cycle, I think is what it's actually called. So that is like okay, the moment that someone comes in to the moment some the larger deals close, what is that window? What's the window of credit for something it did like four years ago? And that didn't make sense?

I can't feel confident suggesting that we should get credit for something that happened so long ago. So where is like that comfort point and where does your company figure out, right? A lot of these are company top down decisions that come from up above that come down to you, is okay, what is fair, what's right for marketing attribution? Because on small deals we've had, we have a phrase here called one call close, it could be like a deal happens within twenty minutes. And then on the other end we have deals that take months if not years to close.

What's fair, right? There's a small amount that happened really really long small amount that happened really fast, but where is it fair? So one hundred and eighty days is sort of representing six months roughly, right? So we kind of came up with one hundred and eighty three is three days extra on six months because we were kind of told and suggested from our sales force reps like okay, whatever model you decide with, just add a few extra days. So we decided six months was fair, so we added three extra days. So that's why you see this weird, I don't know if anyone else ever has had a one hundred and eighty three attribution window, but that's ours.

We also have a higher confidence ninety day window that's not represented on this, but that's just an extra thing, that's mostly for me to see what stuff were impacting within a shorter window of time. So windows are almost like different attribution models all to themselves. But finally the colors here, they represent the different, almost like our channels, at least the way marketing internal channels look. So for us there's SDR which is like our sales development rep team, they get credit for things they work and they do, but marketing as like the digital side of marketing or the campaign stuff side, it has to have a campaign attached to it with some pretty good hand raising and stuff like that to prove it, and each of our campaigns falls into different, we call them channel buckets.

So every campaign has what's called a campaign type, which we just use as a soft version of channel, and that tells us okay, an email campaign thing affected this deal and that should get credit, or in yellow here right, ads. So if an ad campaign did it, it should get credit. But what do we do, right, because I think most attribution models, the big question is okay, what if you have multiple?

What if the plinko here falls down and you have email and ads should get credit, then what? So that's where the thing at the bottom came in.

After many, many deliberative, I don't know, slightly tense hours of discussion with our teams, it kind of fell to the equal attribution weighted model, and that's because we kind of want to see, this is sort of our baseline, We want to see how everything behaves in an equal environment, even if it was the first thing that happened or the last thing that happened. So we do actually have marketing across, and this is simplistic, the top, middle and the bottom of the funnel, we actually have marketing at all stages.

We know it's going to be completely tilting to do the stuff with the last attribution for the top stuff because it's never going to be represented.

And so just to see what everything was equally is sort of our eighty percent, to get back to that, way of knowing what revenue is coming from where. So this is how it looks, and by equal I guess I should clarify. By equal I mean if a one hundred dollars deal happened and two channels, two campaigns or whatever are responsible, they will get fifty percent, right? So they'll take whatever the number is and divide it by the number of attributing campaign or channel and it would split it. So if we're looking at it at channel level and there's two channels that get credit, then it's fiftyfifty. If you're looking at campaigns and there's like five, then each one gets twenty percent.

So it's always split evenly, it's not like one gets more, the polls get more credit or whatever. That's our current attribution model and the rules and the data flow for how it works. This is probably one of the more important slides, so that's why I want to make sure it makes sense.

So getting the first draft together, we had our model, we have the visual, we know what it looks like, we know the important fields, the values, I'm going to be modeling it in, I've modeled it or I'm working on modeling it in Excel, so I know like, okay, this is kind of what I should expect along the way, what kind of data I should be looking at.

And that's where it's like, okay, now we're ready to put this in X Plenty, in our ingestion tool, so that all the data can come in, it can be formulas, expressions, everything can be outputted the right way, so at the end of the day I can take it and I can actually compare it to what I started with in my Excel and see if they match.

So a couple things about the first draft is that to make things run a little bit smoothly, at least for those non XPlanning experts, Excel is a great place to start but not everything actually translates really easily over to XPlanning expressions. So it's like okay, if I'm doing an index match, which is like Excel's version of a join, at least the way I use it, how does a join how am going to join data in XPlanning? Well actually they have a module for that and that's really easy to do, but not everything, especially with some expressions, like I know that one of them I love using is index of, which is basically like being able to search for a word or a phrase or something like that in like a field, so whereas in Excel I might just use like a find or a search or something like that.

So also don't put a whole bunch of data in there you don't need, That's one of the things I love to do, is I'm like, oh what if I need this later? Like no, I have to continually tell myself to start with the very basics of what's needed.

You don't need that many dimensions, normally it's the dimensions I end up trying to cram in there. So, and then the last thing is, from someone who is not a data scientist, I absolutely, even if you're a data scientist, even if you know what you're doing, is I would not hesitate to ask for help. One of the best things I know about X Plenty and Salesforce and a lot of tools, but man they have an amazing support. So you can just hit the chat and you can ask them complex things, right?

I started out not knowing a thing about SQL when I got Integrate.io, and now I'm building some pretty crazy stuff. And that's because like I asked for help, how do I do this, or I have an idea of how I want it to work, and they've given me like three solutions, three options like almost every time, so I can be like okay, and then I can ask questions like how is this working or what is this doing or what's different about this, why is this the best solution for this, or how many nodes do I want or need, all those kinds of things are amazing uses of going to the help, to say help, it's a little bit of like a pride chop to be like oh man I can't do it myself because I want to do it myself all the time.

But I really recommend you use the support because you'll save a lot of time.

Even if it's like oh you have to wait twenty minutes or ten minutes or whatever, anything like that ever happens, you're still like going to get there faster almost all the time.

This was like the very first thing I worked on, It was alright, I need to get my Salesforce data and all those multitude of objects, I want them in my warehouse, we use a Redshift warehouse. And I don't want them all in just one table because it's insane, I probably won't need it like that. So I figured okay, I'll take the common objects some of the objects have actually five other objects all kind of related to them, so I want to roll them up into each master object and save that as its own table in Salesforce. So for instance leads, right? There's lead, lead history, lead status, all these lead fields, lead objects I mean, sorry, in the Salesforce API. I want most or all of them that have relevant information to be just one lead object and have all that information in there. So this is what it ended up looking like for me, was massive, there's a lot of joins and duplication and and all kinds of stuff going on here.

And I put this here because I want you to know like try to keep it simple.

Obviously this is me not keeping it simple, but learn from what I've done. Maybe it does require this, but a lot of times I don't think it does, right? I don't think at the end of the day you need all this information in there, I think you could pretty much keep it simpler than this. But because initially I had no idea what I wanted, right? I had no idea at the beginning of any kind of data ingestion, I'm kind of like I don't know what need down the road, so maybe I'll stick it all in there.

But again that's like not keeping the end in mind, so this is what it looks like when you're like okay I want to put everything in and it's fairly overwhelming, this is like a very, I don't know, maybe some of you have way more complicated branching and stuff than this, but this is definitely the one that normal I don't think it's possible for anybody to have anything more complicated than this, but keep going.

So there's a lot going on here, there's expressions filled like in a lot of the select statement stuff in here, it's all over the place.

Just try to keep it simple, you know, you do what you have to do, but I would recommend just trying to keep things very simple at first so if something wrong happens you'll know, okay, is where it's going wrong.

Okay, so now that the embarrassing part's over.

Okay, what were the initial results? So we had the model, we had the data, we knew the flow, I mapped it out in Excel, I transformed that and built it out in Integrate.io.

Okay, I ran the query, okay, what are the top errors I ran into in just getting my initial results right? Because a bit of that was like okay I'm using the wrong expressions, I'm using the wrong modules, I'm using the wrong stuff in explaining, or I totally scoped this out wrong. Once those kind of issues are kind of dealt with, the other kinds of issues that I always run into is like okay, I'm using the wrong data type, or when I pull it out of Salesforce or when I pull it out, normally Salesforce is pretty good but not in every platform it is, where everything will default to like stream.

And I'm like okay, I got to cast this to a date or I got to put a date time or how am I going to work with this? So date or data type, data formatting is a big deal and when it gets to the end, like when you just take it out and you're going throw it in Excel anyway, may not necessarily be a big problem, but when you do take information and data and you put it into like any sort of a charting or business intelligence use case or any kind of thing like that, they usually, the data type and the data format makes a big deal in how those tools will segment, filter and give you drill down options into the information.

So just be like, if you're in a place where formatting doesn't matter, just be kind of cautious that at some point it may matter, and it's always one of those things where once you, it makes plenty anyway, once you go in and set up and make sure everything's in the right data type, you don't really need to do it over and over again because you're done. And once you've figured out how to cast or change or transform or convert one type to another, you can go in and you can even create a document. I have a document where it's like, what are all my expressions again, that free format stuff? So I have a reference, right? Like if I'm doing this to that, I should use this expression. There's a lot of stuff in there and I don't memorize all of them.

So data formatting is I think a big thing where I run into issues and errors all the time. I've talked about how I include too many columns, that's like just an obsession I have, and then asking for help is where this third check comes from, there's an expression for that, right?

I will try to get it right myself, sometimes I'll spend a few hours, and then I'll ask for help and they'll be like oh yeah, that expression isn't right, like the index of, being able to search for text, text contains kind of thing, I didn't even know that existed. And then they told me oh you're doing a lot of extra work here, use this. And that's why I ended up using it. It was like, oh wow, I wish I had asked for help earlier, right?

So don't try not to do that. The snowman example here is kind of like how any bright eyed marketing analyst goes in expecting this amazing thing, and then when you actually really try you're like, gosh this is hard, or this is cold, or this is not what I expected at the end, and you're like, well it kind of looks like it, we're kind of at the point and it looks kind of right and I think it's kind of valuable also to remember like it's okay, everyone's going to really mess up on this stuff at first but you can do it, at least I think, I believe in you.

And that comes out of not trusting results, or feeling like okay this is off, maybe there's something wrong. So when you're at that point where you've run it manually, you've tested it, maybe you're running experimental or test or just sandbox nodes, and then you're ready, okay I'm going to put this into production basically, I'm going to put this into maybe a workflow where there's an order, this has to happen before this, before that, you can really get things cooking or automated. And so some questions to make sure you're aware of is frequency requirement. So that initial example, need marketing revenue monthly.

To me that kind of sounds like it's a monthly frequency, so maybe I don't need to run it that often. But I actually have to keep track of it weekly so that I can stay ahead of it, so that when at the end of the month, the accounting team isn't just waiting for me to give them all the attribution. By the end of the month I'm only working on a few days of attribution by the time that it gets to there. Because each week I've kind of broken it up and I'm kind of chopping the big project down each week so that it's smaller by the time at the end of the month.

The end of the month is a crazy time for accounting, so that's kind of how I figured out my frequency for this. And also like your nodes, there's a lot around nodes, don't think I can get too much into it, but like how many you need, how many is safe and good and the quantity and large volume of data, the amount of that insanely long thing that's going on, there's a lot there around like what's an efficient way to get this done and how many concurrent programs or projects or whatever are happening at the same time that may overload it. So that's like kind of an important thing to consider.

And then I think maybe the most important thing you can take away from this entire presentation has got to be actionable results are best.

When you scope out any project at the beginning, step one, and then at the end when it's being delivered and for maybe weeks or months after that, there needs to be evaluation over how whatever you're doing is being used.

So at some point, maybe like a couple years ago, like one in maybe ten projects actually got used, acted on, did anything with, otherwise they were like oh that's nice and they put down, right? So that's terrible, at least it feels terrible, maybe that's like a pretty good average.

It's so critically important that the things you work on and use, there's a plan for how it's going to be acted upon. So marketing attribution, Oh marketing improved its revenue, okay marketing's doing well.

The action isn't like, okay, is either going to stick around or you're going to get fired. That's not what I'm talking about. It's more like, okay, what positive things do we want does marketing want to come out of providing these results? Well, we want more budget, we want more people, we want more tools, we want to be able to have more freedom, we want to do whatever, right?

If those are the things we want, how can maybe this project get us there? And having that in mind, or if you're a different team, and you're like okay I'm responsible for their thing, how do you know and understand and get some feedback, right, that the things you're delivering are actually being used and that there's some good feedback coming in that will help you next time, or if they don't use it then they don't see it as valuable or important or whatever so why isn't it that way? So going back to the drawing board and being like okay we've been doing this for a couple months but I'm not seeing anything come out of it, what are you guys using it for?

If they don't have anything they're using it for maybe there's something wrong and you should reevaluate it so that they will start using it.

We did a whole project and how we used it was we changed our operations in the beginning, the garbage in part. We did this whole long project to tell us something, we didn't use it for the something, we used it to realize we didn't have enough to tell us the something, right? So if it was like okay we want know how much revenue we had and we couldn't even get there because we had terrible processes, well the project actually let us change the processes so that's okay, And is that alright?

That might be where a lot of things go with data, is like wow, we don't have quality data so now we have to go back and figure out how to get that. That happens a lot and that has to be like a, I don't know, considered I think, is it being acted upon or used in some way, because that's so important and it's tragic for information to not be used or to keep going, to keep moving and building on its value. The moment the data stops moving or transforming or being used, its value starts to die, at least the way I see it.

Okay, we're near the end of this.

If you've been holding your breath.

Murphy's Law is one of my favorite, I don't know, ironic sarcastic philosophical ideas, and that's that something's always going to go wrong or assume it will.

So basically what to consider if that is true, right? You may not believe in Murphy's Law happening and maybe you're fairly immune to things like that, but what I think all teams should consider if you're doing marketing attribution, if you're doing any sort of data ingestion, with the constraint right of information leaving and people leaving, like we have this huge crisis going on, is to keep functions, go to projects, expressions, sources, everything to keep it flexible. And by that I mean if, you know, with case sensitivity of stuff right, can you write an expression or formula or whatever algorithm that allows for things to be incorrect? So for instance, the easiest way to get around the case sensitivity problem, and it's plenty, is just to put lower.

Lowercase everything, right? And if you lowercase everything as it comes in, then you don't have to worry about necessarily how it's typed perfectly. So there's little things you can do, very little things you can do to make your stuff more flexible so that when you're reading it, interpreting the information when it's happening in an automated way, that your stuff is more flexible. Or if team members leave, right, and you're doing stuff based on user names and user emails, that can cause havoc and every time a staff change happens you have to go in and rewrite all your stuff, that's not really scalable and that's not really, that's more manual again.

So it's something like you start manual but is there a way to make your stuff more flexible to things happening? And that is trying to it's sort of avoiding Murphy's law that there's going to be issues there. And then the last thing here, if anyone knows Nassim Taleb, is that even though you may consider an event to be rare, like oh black swans don't exist, we found out they do in Australia.

And they didn't know that for thousands of years. So basically even though rare events are rare, they may have large sweeping consequences if they happen. A year ago I would never have guessed we'd be in a pandemic right now and that's happening, There's stay at home stuff going on and that's big deal. So how could anyone have foreseen that necessarily, like it's really hard, right? But if, and you may not be able to foresee everything, knowing you can't have a plan for all these volcanoes and earthquakes and meteors and stuff that are extremely rare, but just within your scope, are there any kinds of rare things or things you've seen along the way that have happened? People with experience probably have like a lot of things that have happened, and so it may take them a little longer or it may take them down a different path when they end up doing the work they do, but they're building it so that it's more flexible.

So making yourself, raising up your career, raising up your abilities here is knowing a lot of times how to be a little more flexible with the things you're making so they don't break it as often, so people aren't always coming to you and they're like, Oh, this data's wrong. Because if that happens over and over again, why are they going to trust the data? Are they going to trust information? So the more flexible you can be I think with your results at the end, the more it'll allow you to like come in, tweak, change and get it right or fix it and try to reduce the amount of errors that happen.

So let's go through some concluding thoughts.

Alright, this is almost basically the last slide here, in case you're checking for time.

This is just a complete summary of all my suggestions for the process that it took us to get to marketing attribution and being able to prove in the simplest way, right, monthly revenue from marketing with some dimensions in a monthly format that came out in a financial statement basically. So you may not have anywhere near the same request or ask that we did, you may just generally want to, you know, you're curious about how marketing gets attributed. So basically you could follow these six steps here, and that is to start with the end in mind and model the end result with the stakeholders so you know exactly what's required, exactly what you're going to need from the beginning of the day.

And then the second one here is you're going to have to work with other teams possibly, other departments, other people to get the information you need to prove marketing. For us accounting, marketing are different silos here, different silos of even information, different cultural silos all together. So that required us crossing over. But you may have like many more silos than that you're going have to work with.

So just try to avoid being the bottleneck or the squeaky wheel, and I guess it depends on which kind of way you want to look at it, but try to be one that's not holding everybody else up.

For us, we use Salesforce, that's why I put it here. But basically keep your CRM, keep your data sources as clean or as high quality as you can.

A lot of times when a project starts in something new, there won't be much consideration over what information goes into fields or what information we need because we don't really know maybe six months from now what we're going to do with anything. So when we start out a project we may start it out fairly dirty, but if you have some idea, okay we may need these two things or this is kind of like we're going use these two things to base it off, base other actions off of, then those are really important to make sure that they're gathered appropriately and accurately. So not just knowing what's required at the end, but knowing what information is going into a system is going to help you to have that data in the future so you won't have to rewrite it or I don't know, have to realize it's all garbage and you'll have to start all over again.

The fourth one here is sort of just my personal way, but modeling the data on paper, on whiteboards, excel, we use Lucidchart or wherever you're comfortable first, it's really helpful and especially if you can take something really complex and you can explain it to someone who has no idea and they can kind of get it, that means you're really onto something. If you can put and model what you're trying to do in a very simple way, just initially basics at first, then you're really onto being able to take that and turn it into something like use more complicated tools to pull it off or make it more automated. And that way you can always refer back to it and it's not all up in your head, there's actually documented process of what's happening.

I think the last two here are kind of like getting it past the finish line, so building everything out and testing it thoroughly, I think everyone hits it and goes and is like, Okay, it ran successfully, that's a good sign. And then it's, Okay, what was the output of that information?

And if you have a way, if you've set up a way to compare if it's right or not, that's huge. That's why I do it in Excel first and then I do it in xPutney and then I compare the two, right, they match in my off. If this says one hundred and this says five thousand, there's some serious issue there that happened along the way.

And then again, Murphy's Law, try to make things as flexible as possible, but at the same time your information and things at the end of the day have to be actionable. So you want to spend a bit of time making them flexible, but you may want to start with, okay, are they actually using this? Is this actually being used for some, you know, to make change or for some big decision to happen?

You may not always have the clarity of that all the time, but asking for some feedback and stuff on what you're providing and if there's different ways or ways of making it better, or at least at some irregular intervals, good ways to follow-up to make sure, get some feedback, okay, they're at least using the information I'm providing.

Marketing attribution is one of those things where it's like you may start providing it weekly or monthly, then it may be like, I'm fine with it right now, maybe I'll wait six months to see it. And then you wait six months and they're like holy cow, things are crazy, I need to go back to monthly. Things can like ebb and flow with how whoever the stakeholder is adapting to the information they're given. They're digesting information from a lot of sources, some of them may just be looking for fires and some of them may actually be looking for opportunities. So those are important things to separate.

That's it, I'm probably going to turn orange here, yeah, I appreciate it, I'm really grateful for the opportunity to present on the topic of marketing attribution with Xplanning and Xforce, this is great.

Thanks Kevin. I've got a question for you. How long did this project take from the minute you got to go from your management till you had pretty much a good a good output? It looks it sounds like it was years.

Yeah, I'm glad it sounded like it was years because emotionally that's how it felt.

Okay.

Alright so leading up to this specific ask, as the analyst on the team, and foreseeing like, okay I absolutely need to track this stuff, but not necessarily knowing the ask of what it's going be, because we had KPIs, we knew the stuff we had to measure to, but I would say getting UTM parameters set up, getting taxonomies well established, and then figuring out like okay how are we going to report on our revenue, that took like a couple years. I think every marketing department's always like okay how are we doing this and how are we reporting this and what's working? But this specific project, with this specific ask, this took two months.

Okay. Maybe that sounds kind of fast, but it was kind of during a down time for us and also during a time where like we had people come in and we're like look we're kind of flattening and cutting some red tape, Marketing we need to know what your attribution revenue is monthly. Your team you need to know this, this team we need know that. So everyone kind of knew okay everyone's going to be asking everybody else.

Before this things were drawn out long because we had meetings but no one knew the priority of this. We were given the priority, we were given like high priority from the top down to figure out this marketing attribution problem that we have. And it's sort of being like a heart of call source, right? We're an attribution company, why can't we figure this out?

This should be easy or whatever. And yeah, we have our phone call stuff, but we have all these other marketing channels so how do we get them all in one place and stuff like that. And one other little thing I would add is when you have executive level changes happen or departmental operational changes, like right now maybe a lot of companies are changing their dynamics, And that couldn't be something for us that changed like every week, like a whim.

Have a joke here where someone had a dream last night and they came in and they're like, well this is how it's going be or they went to some event came back and they're like, no this is it's going to be. We kind of had to establish like for us to even do our job we need to be able to pivot on this in a slow way because it takes a while to see if something's working and then it takes a while to act on that and improve it and then a while to see if that improvement worked, we can't just keep changing and iterating so fast. The pace of iteration had to be a little longer for us and so that's why initially the first couple years that was all we did. And then this last project was okay, we've decided all these things and we were like oh thank you, it's so nice that we have someone has actually told us, drawn a line in the sand for like how things are going to be and that was like a huge struggle for us up to that point.

So just so I'm understanding it took two months to do this specific project but underneath the whole thing you already had Salesforce as a single source of truth, you had done some data cleansing, you've done a lot of laying the groundwork that allowed you to do this one specific project, is that right?

Yeah, like for instance not everyone, I mean this is like a Salesforce thing, not everyone uses campaigns for one, we do heavily and not everyone uses UTM parameters, some people use maybe an attribution tool like a visible or something.

Some people use different ways to say this is credit, this is not, or whatever, maybe they tag or they look at lead source or something like that. We use campaigns because that's sort of what Salesforce has built to measure and track campaigns. Marketing is the only one using campaigns so it's a little easier, sales doesn't use them even though they could for us. But that way we could track multiple campaign influence on opportunity deals and sales force has a report for campaign influenced opportunities or opportunities influenced by campaigns. So out of the box Sorry I need some water for a second.

Getting much better. So out of the box we could kind of get our attribution reporting almost straight out of Salesforce, Salesforce says this is the opportunities influenced from campaigns. And we had some processes and stuff like that for data inputs, we actually this is just a side note here, my colleague and I, Matt Widmeyer here, he's in charge of our SDR team. And he and I made an entire Salesforce training course. We filmed the videos in this, I'm in a green room right now that's why you see Batman in the background.

We filmed an entire course for our own company to train them on what they should do because it was such a problem for marketing.

We spent last summer, three months filming an entire Salesforce training course on how to do it and how to do it right and why to try to help them. And we got our sales and our executive leaders to buy into that and to set some punitive reasons why they should do it and also some rewards over who does it best. So we set up that whole thing and that helped us get some better information in and also helped the sales leaders know, okay their teams are trained, they can actually tell their teams, like no, if you don't know how to do this go watch that video that's in the intranet that our own people made for you.

Cool. Well, Kevin, thank you so much for your for presenting and for sharing all your knowledge about your attribution project and for telling us how X20 helped you achieve your goals at your company. We appreciate it very much.

Thank you. Yeah, if you need to connect, I'm on I'm on LinkedIn. Great. Thanks, Leonard.

Yeah. Thank you.

‍