Vexnomics Office – Optimization Sprint Meeting

You’re talking about your great results and how you brought down the cost per lead, but your boss walks in and asks how many are qualified. You have no idea: sales haven't been through all the leads yet, and probably won’t get to it for weeks.

Waiting for sales to update the CRM is a path to madness
We need our own qualification system
Let me talk to sales and get an understanding of what historically converted
Then we’ll use those attributes to build a lead scoring system
That way we can optimize in near real-time


Lead scoring is a way for sales and marketing teams to prioritize their efforts and focus on the leads that are most likely to turn into paying customers. This is done by assigning each lead a score based on a set of criteria that are relevant to the organization's goals and target audience. For example, a lead's score might be determined by their demographic information, behavior data, and firmographic data.

Demographic information includes details about a lead's age, gender, and location, while behavior data includes information about how the lead has interacted with the organization in the past, such as which pages they have visited on the organization's website and how often they have opened and clicked on marketing emails. Firmographic data, on the other hand, includes information about the lead's company, such as its size and industry.

The goal of lead scoring is to help sales and marketing teams be more efficient and effective in their efforts. By prioritizing the leads that are most likely to convert, organizations can make the most of their time and resources. Lead scoring can also provide sales teams with a clear understanding of which leads are most valuable, allowing them to make more strategic decisions about how to pursue new business opportunities.

Overall, lead scoring is a valuable tool that can help organizations of all sizes and industries improve their sales and marketing efforts and drive more revenue. By prioritizing their leads, sales and marketing teams can focus on the ones that are most likely to become paying customers, ultimately helping the organization achieve its goals.

Lead Scoring

00:01 Hey, I'm just gonna walk you through how to qualify your leads more programmatically. And we're gonna do this in a Google sheet.
00:10 So you know, you can kind of understand how it works but once you see the principles, you'll be able to apply it really you know, wherever you need to set it up.
00:19 Cool. So this is a typical, you know, spreadsheet you might get from sales output, from the crm based on a lead generation campaign.
00:30 So if you look you know, we've been producing these eBooks and e guides and webinars and different events, and they're driving leads.
00:39 And some of those leads are qualified and some aren't. So what we have is a few different leads. Status. One is a new lead, which means it hasn't been seen yet.
00:51 It hasn't been looked at by sales not accepted, means it's been looked at by sales, and it's not a good fit.
00:57 So this is usually an override, meaning it doesn't matter how qualified the lead looks. We can't, you know we can't count it as a qualified lead if sales have said no.
01:09 Although sometimes if you see that they're being mis qualified, you can you know, go back over it. And then there's some situations where sales have already looked at lead.
01:20 Maybe they've already had a call and it says there's an opportunity. So opportunity is when sales accepts the lead, and they're, you know, following up with the proposal.
01:32 They've had a, had a call. Cool. So we basically what we want to do is, like, we know that not all of these leads are qualified and we need to figure out you know, what a cost per qualified lead was.
01:46 In order to do that, we need to qualify these leads. And you don't want to be doing what the sales team is doing, right?
01:51 And going through each one of these manually, like looking up the company, looking up the job title, et cetera. So you just need some more programmatic way of figuring it out.
02:00 Cool. So the way that I find is useful is you know, first there's gonna be, you know, specific countries which are more likely to create good quality leads than others.
02:17 So I'm just gonna say country. And then gonna do data, data cleanup and then gonna remove duplicates. So I just copied and pasted the countries, and then now I have a list of all the countries.
02:31 And then I'm just gonna put a score here. So for United States, America, I'm gonna put 10, you know, one of richest countries in the world.
02:43 So, you know, like much more likely to be high quality. And then we have, you know, Sweden, Luxembourg also, you know, pretty rich.
02:52 Let's say just let's make Europe eight Western Europe eight middleland keep in France. Philippines gonna say three. It's a lower income country, Malaysia.
03:08 And, you know, you, you could actually actually, so Singapore is, you know, much more like Norway, right? In terms of demographics.
03:16 So you know, you're gonna be able to derive these numbers properly by looking at your past data and seeing, you know, leads from Belgium are pretty good leads from, you know, other countries are not as good.
03:31 You know, but the but the main point is really that like some countries will provide more qualified leads than others, and you kind of wanna weight the score, but you don't want to like, completely dismiss a lead from the Philippines.
03:45 Cause say for example, they're you know, actually like a senior VP or something, right? Like maybe they're actually qualified. Cool.
03:53 So country is just one score. And then we also have number of employees. And that one's a little bit easier, I guess.
04:07 Cause you know, this is b2b, you know, company. And if they have like a good number of employees that's gonna be that's gonna be really influential, right?
04:22 So I'm gonna put this score there. So this, you know, if like, if it's a really big company, like 10,000 people plus then I'm gonna give that look at score 20.
04:32 Cause I think that's really important, much more important than the country. And then if it's like 5,000, let's give 10, and then this is a five.
04:43 And then if it's missing just don't give any score.  And then actually, let's make these no score as well. Unknown, actually, yeah, let's just make these five because I would just assume that they're average.
05:06 If it's a one employee or 10 employees, we know that they're not gonna attend to customers. They're a lot less likely to tend to customers.
05:15 Cool. So again, these scores, I'm just making them up, but you would use historical data, you would say, say like, you know maybe, you know, companies that have 10,000 employees are twice as likely to become valuable customers than you know, companies that have 5,000 employees.
05:34 And then we're gonna do it based on one more thing, which is job role. So let's grab all the job roles, okay?
05:46 Right. And then should put this, that here and again, when I'm doing this, I'm just, you know, control shift down or command shift down to grab the whole column.
05:56 And then control shift v adds paste without formatting. Cool. So I'm just gonna remove duplicates here. Okay. So it looks like we have a few different job roles and a blank job role.
06:20 Just gonna put the scores. Sorry, let's give this a five again. Then. Managerial role is going to be a 10 about five for none of the above.
06:31 Operational role is a one because that's not good for us. And you know, they need to be in a managerial role to be the right type of a client for us.
06:41 Budget oversight, let's give them let's give them a seven, right? Cause they have some influence. And then strategic decision maker.
06:52 Let's give that a 20. So they're the, the big hot show, right? Cool. So you know, you could keep, you could keep going with this.
06:59 Like you could do it based on job title or, you know, whatever it is. But I think this is gonna be enough to demonstrate for us.
07:07 Cool. So now we just need to put together a lead score. And then what we're gonna do is the look up we're gonna look up the country first, and then that first F four to grab that group.
07:28 So then it doesn't change as we move down. I'm gonna return the second thing here and then and then oppose zero at the end to make not a fuzzy match.
07:39 Cool. So let's say, okay, let's actually make a different thing for each of, so this is country score. This just makes it cleaner.
07:48 And then we're gonna do the same thing here. But this is gonna be please score. And here, instead of looking at this, gonna look at this table, right?
08:08 And remember to press F four, or you can put the dollar signs in yourself. And here we're looking at the number of employees, which is this field here.
08:18 And the rest is fine. Cool. And then down here we've got some nas so did not find value blank. Oh, yeah, yeah.
08:37 So I found this as an issue. Sometimes you just wrap it in an if error. That's, if you don't find the lookup, then you know, then it's just gonna return blank.
08:51 In this case, we gave a a value of five if there's an error. So let's try that. Yeah, there we go.
08:59 It's filled it in with a five. Cool. Here we go. All right, employee score. And then one more, which is the job roll score, roll score.
09:22 Cool. So if you look up again, and then the job roll, I'll look it up over here at four. It's The second column we want, and we don't wanna fuzzy match.
09:36 Okay, here we go. So that looks like it's good. All right, cool. So now we can do the total lead score, right?
09:48 And that is that plus that, that, okay. And let's just c just gonna press control A and then what's the best way to look at this?
10:11 Let's do, actually, let me just do some sort of chart. So not a pie chart columns. Cool. So this isn't like, particularly easy to see.
10:44 Yeah, let's do that. Then we can sort this. So let's create a filter and then sort by lead score. Cool.
10:56 All right, here we go. So this is more useful visualization. All right, cool. So it looks like, you know, most of our leads, you know, are around this 38 lead score, which is pretty good, right?
11:09 So that's these guys, right? Like You know, they come through here like a VP of growth in there, VP of finance, so that's, you know, interesting.
11:20 Cool. So we have a lot of 30 eights, and then we have like, kind of lower amounts down here, and we have a, you know, a bunch kind of in the lower bracket.
11:29 So it really depends. And again, you know, you kind of have to set this as a negotiation with sales. But let's say that the cutoff is 20, right?
11:40 So if anything below a 20 is gonna be not qualified. So let's make another column and yeah, this is going to, you know, get rid of the Worst offenders, right?
11:58 But then you know, but then it's gonna like, you know, it is gonna make sure that we like at least looking at some of the leads that, that have potential, right?
12:08 So if that is you know, more than 20, then it's an mql. Cool. So there we go. Now we have false and true.
12:18 Cool. So so this is how I do it. The benefit of doing it programmatically this way is that a, it saves you a lot of time because now you have this spreadsheet, you could literally just paste in more leads and then it'll update.
12:32 You could also just write some code. You know, you could give this to a developer to automate, so it could actually push straight into HubSpot or whatever CRM you're using.
12:41 Just as soon as they fill in the form. That's great. That's like a ideal situation. Then you don't even have to do this spreadsheet.
12:48 The other reason is that you can't wait for sales to say what leads are qualified. You have to optimize. And, you know, we've seen here that, you know, of our leads, like of all of our leads, we've got 77 that are qualified, and And 107 leads total.
13:12 So, you know, our MQL rate is 27 times seven, so 71% cost per qualified. Yeah, 71% of our leads are qualified.
13:25 So it's pretty high. But like, you know, that's not always gonna be the case. You know, and you know, when you're optimizing your campaigns, you can kind of keep an eye on this figure and say, are we getting high quality leads?
13:37 You know, are there some you know are there some types of you know, campaign that are getting low quality leads?
13:46 So if you do a pivot table, you can see that. So just gonna put here, like the campaign. And then I'm just gonna bring in values like nql, and then just gonna do a count of like created app.
14:06 Cool. So count of nql, and this one is just gonna be some, Yeah You know what? Here we go. Let's make this If It's true, then it's gonna be one other is gonna be a zero.
14:36 So now that's gonna be a number, then that's a lot easier. Okay, so here we go. So now we have the mql.
14:44 So, you know, we could, we could make a, you know, custom formula here in the pivot table or, you know, just for speed and bring here like this.
14:53 So we can see that, you know, our qualification rate. We're just gonna do additional forming. So you can see this you know, our qualification rate here is you know here we go.
15:05 It's it's, it's much higher for some channels than others, like for some campaigns than others. So we'd wanna look into that.
15:13 You know, for some of the campaigns who are targeting different countries might have higher rates than others. So this is really, really key to doing any type of analysis.
15:21 And doing it programmatically means that like, there's no arguments. It doesn't really matter what sales says. Like if you've agreed on the criteria then you can, you know, what to optimize to.
15:31 You don't have to wait for them to come back. You know, where you do see conflicts like you know, maybe if like, we can filter down here.
15:41 So if I go data and then I you know, create the filter, let me filter to anyone that I think is qualified but they think is not qualified right?
15:54 So now you can dig into these and say, Hey why did you disqualify this guy? Like, he's a VP of marketing and this one's a VP of finance, and they're saying lead fit, right?
16:02 I need more information, right? And you can maybe win some of these back and say, Hey you know do we get the qualification criteria wrong?
16:10 Or you know, did we, were we too hasty to reject this one? Or maybe there's an additional criteria that we can add, you know, something else in the form that we can, we can ask.
16:19 Cool. Okay. So hopefully you understand that. And you know, you get the concept it's you know, incredibly useful. It really just depends on how complex or how easy you want to go with the filtering requirements.
16:33 But I would start simple and just kind of build on from there.


Why is it important for marketing to own lead scoring?

How many qualified leads were rejected by Sales?

What percentage of the leads would have been qualified if the required minimum lead quality score was 30 not 20?

How do you determine the criteria for lead scoring and know the value of each attribute?

Working out the cost per qualified lead oftentimes requires a long-winded manual process, involving waiting for sales to follow up in the CRM. In practice it’s better to do this programmatically based on specific attributes you both agree are indicators of lead quality. That way you can optimize in near-real-time.

Vexpower Learning Certificate of Completion

Congratulations Dan Perach 💪