Vexnomics Office – Sales-Marketing Planning Meeting

In your quarterly sales and marketing planning meeting, somebody brings up the fact that we haven’t done any sort of analysis on the pipeline to see what leads are more or less likely to convert. Right now it’s entirely on sales to figure this out, but nobody sees the full picture.

Now we have a decent volume of leads, it makes sense to do a pipeline analysis
Each person on the sales team has a different idea of what converts
So it’d be good to zoom out and see what really leads to conversions at a macro level
What attributes and demographics, etc
Is that something you can handle for us?

Sales pipeline analysis is an essential part of the sales process, and marketing plays a crucial role in this process by generating leads and guiding potential customers through the sales funnel. One key aspect of pipeline analysis is segmenting the funnel into different stages and analyzing the attributes of leads at each stage to identify patterns and predict the likelihood of a sale.

By segmenting the sales funnel, marketers can gain a better understanding of the different stages of the sales process and the characteristics of leads that are most likely to convert into customers. This allows them to develop targeted campaigns and messaging that are tailored to the specific needs and interests of potential customers at each stage of the funnel.

One important factor in predicting the likelihood of a sale is the attributes of the lead, such as their demographics, interests, and behavior. By examining these attributes and comparing them to the close rate at each stage of the funnel, marketers can identify the characteristics of leads that are most likely to convert into customers.

For example, if a marketing team is analyzing the conversion rate of leads in the middle of the funnel, they may find that leads with a certain demographic profile or interest in a specific product are more likely to convert. By focusing their efforts on targeting leads with these attributes, the team can improve the efficiency of the sales process and increase the overall close rate.

Segmenting the sales funnel and analyzing the attributes of leads is an important part of sales pipeline analysis. By understanding the characteristics of leads that are most likely to convert, marketers can develop targeted campaigns and messaging that improve the efficiency of the sales process and increase the overall close rate.

Pipeline Analysis

00:01 Hi, I am gonna walk you through how to do pipeline analysis. So this is a useful piece of analysis to do regardless, like it's always useful to know what types of leads are more likely to close versus others.
00:15 But typically you would do this ahead of trying to define like what makes a marketing qualified lead. So if you're about to do some lead scoring this would be the preceding analysis.
00:26 And we do have a, a course of lead scoring as well. So we have here, you know, a list of leads and these have already been run through by sales.
00:35 Actually, let me just, you know, if I select this and I go format conditional formatting and then I want the text contains closed one.
00:51 Okay. So you can kind of see visually here, like very few of the leads actually close.  So you know, it's very hard to do analysis you know, very hard to optimize your campaigns.
01:04 And, and, you know, it takes some time for these leads to be kind of, you know, eventually filtered to the system.
01:10 So in this case, if it's a new lead that means that basically nobody picked up on it from sales you know, it hasn't been processed, so you can treat that as non-qualified.
01:22 Then the next stage is not accepted. So this is where you know, sales team have had a call, a fit call, like maybe 15 minutes, half an hour and then they've agreed that like there's actually not a good fit for that customer.
01:36 So that would be not accepted. So that, that is a lead that sales have touched. Opportunity comes before not accepted.
01:43 So opportunity is where sales are happy, that is a qualified lead but they couldn't get them on the phone. So if you kind of think about it you know, comes through as a new lead and then sales looks at it and says, yeah, that's an opportunity.
01:57 They're trying and contact them, and then they have a call and they either are not accepted or they're closed one.
02:03 So close to one is we actually kind of close the contract and there might be, you know, multiple stages. There might be more complex in this or less complex in your crm.
02:14 But this is just really an illustrative example. So what we want is close one, but we can't always get that right and we don't have enough data all the time.
02:22 So what we're gonna do is like pivot analysis just to see, you know, what, what are the different situations. Cool.
02:29 So so here we have the funnel and we have you know, how many people kind of get through each stage of the funnel, what the average value is.
02:40 Just in general, you can see that it really drops off around like opportunity in close one. So we, we don't have enough data really just to look at those, right?
02:51 Because, you know, if there's only seven close one out of 107 you know, it's gonna be really hard to do any analysis based on that, right?
03:02 Because you know, if we look at different categories, there might just be like one closed one in each category. So what we're gonna do is we're gonna treat this as like, this is some indication that there is quality there, right?
03:14 So like, if the sales market is an opportunity, then they thought there was quality there.  You know, if the sales even if they said not accepted, it was still worth getting on the phone with them, right?
03:26 So that's a good sign. And then close one obviously is the ultimate good sign. So what we're gonna do is kind of treat, you know, treat this as you know, the, the, this is quality lead, and then anything this new lead is, is not a quality lead.
03:39 And you can see here in terms of average value the just getting a lead is worth $300 to us, but getting a quality lead is worth three times as much $946.
03:51 So if we can kind of predict this, like what are the leads that are qualified, then we can optimize the cost per quality lead and then hopefully, you know, get more people through the funnel.
04:00 Cool. So we're not gonna cover like how to actually kind of lead score afterwards, but really it's just, just kind of understand what criteria are likely to make a lead.
04:09 Good. Cool. So what we've done is we've just done a pivot table. And, you know, the way you can do that is if you just click anywhere in here and press control A or command A, and then we go insert and then pivot table and then do it in a new sheet.
04:25 And then that's going to bring up something like this. So if you wanna edit the pivot table, just click in here.
04:32 So you can see here we put rows on the left hand side, that's the rows we put country and you know, each one of these pivot tables is a different segment.
04:42 So this is segment by the UTM content. So like, what asset did they come through on as a lead? Did they come from a webinar or, or an event or an ebook?
04:52 And then we also did like job roles here as well. So you know what the, what we're trying to do is segment the data by these different properties and then just see, you know, which ones are indicative of there being a good or bad fit.
05:09 So what we've done then is put the columns leads status, and then for values it would just counted how many they created that field.
05:16 So that's just a good way to get unique account. Cause they won't have the same created app. Each, each one is a come through a different second of the day.
05:27 Cool. So so once you have your pivot table, then the qualified column is just made up by selecting, you know, summing up any, anything that got into not accepted option to your close one, and then divided by the grand total.
05:43 So here we had 47% of the leads that came from the United Kingdom got into one of these buckets. Obviously this is cumulative in a way, right?
05:52 So you know, all of the not accepted were also leads at some point, right? So it's not right to say like eight divided by 12.
06:03 Actually you have to do like say eight divided by 12 plus eight, if that makes sense. So that's why we're divided by the grand total, because, you know, this is the current status of the leads, not their past status.
06:13 The past status would've been new lead for all of them, right? We had 23 of total leads and 12 of them were not qualified, if you think about it that way.
06:21 Cool. And one thing you can see straight away is like we get enough of, you know, ordered this descending by count of created leads.
06:32 So you can see straight away that u UK is our top country, right? It is drove 23 leads out of 107, so about 20%.
06:41 And then France is the next, and then we have Singapore. And a lot of the rest of the companies, countries are like relatively long tail.
06:49 We don't have that many leads. So so that's something to keep in mind because, you know, when we're looking at the score, like we can't just look at United States and say, yeah, we had like, you know, six leads from the United States altogether, two of them converted into close one.
07:06 That's a good thing, I guess. But like, that's not enough to like really, you know be confident. And you can work out the statistical significance of that.
07:15 But usually if this, you know, you can just kind of eyeball it to be honest. And, you know, you can make a rule of thumb and say like, you know, okay, well, you know, UK and France, we have like plenty of data here.
07:27 But then, you know, Singapore, we have nine leads, but they're actually just at the average qualified, right? So the average here is 34% and they're at 35%.
07:36 Same with Netherlands, they're at 33% and the average is 34%. So you can treat them as average, you don't need to worry too much about them.
07:44 So what you're looking for is like what categories, what, you know, attributes in the categories have a high number of leads and also a really different qualified score.
07:57 So you can see here France seems to perform terribly for us. Only 17% of those leads are qualified. So if we wanted to give it a score, what we can do is we divide the qualified by the average and then just multiply that by a hundred.
08:11 So this is basically an index. So it's saying, you know, that UK indexes 138 over a hundred, right? So it's 38% better than average, and France is half as good as average.
08:25 For the rest I'll just put a hundred because, you know, these two are average, so you don't need to really assign a score for them.
08:31 This one is like borderline, right? Like, but we only got, you know, we got nine leads and it's like pretty high which is pretty good.
08:41 So that's kind of like a judgment call. You know, you could, if you wanted to do this, you could put that here and then kind of give that an index.
08:49 The you know, the, but like, still kind of remains to be seen like, you know, we, we have twice as much data for the UK as we do Italy.
08:59 So we might have to kind of think about, you know, keep an eye on that and make sure that the score is correct.
09:05 Cool. So now, you know, we can break it down by other things as well. So let's say like, you know is there a difference in lead quality by the, the asset they come through in?
09:15 And this is something I've found most people forget about, but you know, different assets perform differently, right? So someone read an e-book, are they more or less likely to convert versus like a webinar or an event you know, et cetera.
09:29 So you can see the top two categories. We have a lot of leads for you know, the eBooks and you know, the score here is pretty low, right?
09:42 So they, they're actually less likely to convert.  There is something interesting here, the future, you know, the webinar, right? This this webinar and then this you know, this here, the e guide seems to perform pretty good.
09:55 1 0 1 guide of being a marketing scientist. So, you know, you might wanna kind of create scores for these. Yeah, and here we go.
10:02 So you can see like if it's a webinar or an e guide, you know, if it's this webinar specifically or if it's this e guide, then it seems to, you know, perform better than average and this one is average.
10:13 So just gonna set it at a hundred. Cool. And then final one here is job role. So you know, this is a, rather than the job title, which you know, there's tons and tons of job titles.
10:26 Let me kind of show you how many job titles we have.  Let me see. So if I get rid of job role and then I bring in the job title, there's a lot, right?
10:41 And you know, we do have, okay, I have 10 leads from social media interns, <laugh>, right? But, but like, that's the biggest category.
10:49 And, and then most of these don't, don't repeat. So what you could do is you could kind of group these and you could say like, I dunno, these are the VPs yeah, pivot group.
11:01 You go. So now you can kind of see yeah, this is like a terrible ui, but you can see the VP group you know, conversion rate.
11:13 So this is seven divided by 17. So like, that's one thing you could do. But in general, like job title is you know, it's usually like a free text field, so you end up with lots and lots of values.
11:26 So I'm just gonna get rid of that. And we're not gonna use that for for these at least right now.
11:34 But you could, you could make up like a grouping to automatically group them. What we did here instead, which I find is a little bit more useful, is when they fill in the lead form one thing we ask is like, you know, what, like, do you have responsibility over budget?
11:52 Right? Or so like, do you have budget oversight or are you a strategic decision maker, like a senior, senior leader?
11:59 Or are you in more in an operational role or a managerial role? Or are you none of the above? And obviously a lot of people don't fill this in, so I guess like 30% don't fill it in, but 60% are managerial roles and like you know, so that's kind of interesting.
12:15 And it, and it tells me here as well that like, not that many people are in operational roles, which is good because it doesn't look like we can convert anyone from operational roles.
12:25 And, and that is something that we can see, you know, see in just a small amount of data we have.
12:29 But it's also, you know, something that is like, just makes sense, right? Like if they don't have budget oversight, then they're probably not gonna be a good lead.
12:36 So you can come at this from, based on what the salespeople think and what they've found in, in terms of, you know, based on their logic as well.
12:44 And then and then also based on the data, so interesting, and this is something I find a lot like people who don't fill in that, that role, right?
12:54 Like, they're instantly worth less, right? They're worth half as much you know, than than if they do fill it in.
13:01 And if they say they're a man manager, then that's really, really good for us. Okay, cool. So, and we don't have enough detail on the rest.
13:09 Cool. Okay. So you know, he here actually as well, this is like converting at zero, right? So we might wanna say, you know, I might wanna just give this like a 50 or whatever.
13:21 So we don't have enough data, as much data as we do here, but like, let's just say that it's definitely like if they say none of the above, then you know, that's probably a bad sign as well, right?
13:31 So we can highlight that there. Cool. Let's practice taking another one. So what I'm gonna do is I'm just gonna copy this and then paste, and then we'll just go into edit and then we're gonna get rid of, so click out of the row, like get rid of that.
13:48 And then that just gives us a full funnel. And then we're gonna bring in let me see a number of employees, right?
13:55 Because this is usually like a, a pretty good indicator. So I just copied another thing here. So number of employees is really important you know, usually, right?
14:07 So let me just do descending by number of employees. And you can kind of see here that like most of the time, like most of our leads, 60 60% of our leads have at least 10,000 employees.
14:24 And it looks like that is a really good sign for us. It's 46% of them are qualified. And then even with like 5,000, it looks like 62% are qualified.
14:37 So this, you know, for this, I'm just gonna, I'm just gonna paste that in there.  So it looks like the score is pretty good if they have at least 5,000 employees.
14:47 We don't have any data for 35,000, but we could assume that the score is gonna be good there as well.
14:53 So, you know one thing you could do is just put this as like one 50, right? And then if they have a thousand employees, the quality goes way down, right?
15:05 So when I set that there, cool. And then, you know, for, for these guys you know, we could, we could say like, actually, like if they, you know, we could be restricted with this.
15:18 And so, like, especially if few sales people back you up on it, say like, look, they don't have at least a thousand employees, it's not even worth talking to you.
15:26 And then some people don't fill it in. It's actually a fewer people that don't fill it in. So I think we're, you know, we can kind of ignore that category.
15:34 Cool. So now what you would do with this analysis is you would take it and you kind of agree the scores based on sales and maybe you would update those scores every quarter based on historical data.
15:46 Like obviously the more historical data you have, the better you know, and then you would use this in your lead scoring.
15:52 So one simple way you could do it is you could just do a points based system and you can say like, if they have more than 5,000 employees, that's one point, you know, if they're a manager, that's another point.
16:05 And then if they're, if they come through ebook that we're deducting a point <laugh>, right? And then if they come from UK or adding a point, if they come from France for a deducting a point so that would be the simplest implementation and that would get you like 80% of the way there.
16:21 I know it's not very scientific, but you know, practical kind of application of this to like kind of steer your lead qualification in the right direction is an important thing.
16:33 Because there's a few reasons, right? One is right now, you know, the salespeople are having to go through every single lead.
16:40 So if you give them some sort of scoring, you go like uk good France, bad, like that's enough. At least like they're gonna go for the UK leads first, right?
16:48 And they're gonna leave the French leads to the end, and that is gonna help them prioritize. It's also gonna help you optimize, right?
16:55 So you're gonna look at your performance and you go like, huh, I'm gonna just really jack up the spend on my UK campaign, right?
17:02 I'm gonna decrease the spend on my friends campaign. You know, same thing here. Like, if eBooks aren't working, like why are we spending, you know, five grand on content writers to create the next ebook, right?
17:13 Like, let's produce more webinars and this is giving us like a little hint, like a hypothesis that this type of webinar seems to be working right?
17:21 And then like, maybe we'd do another e guide, cuz maybe they prefer that to eBooks, I'm not sure. Cool. So this is gonna give you kind of strategic analysis and it could also give you like an automated lead score in the future as well.
17:35 So like you know, you could do this statistically as well, but like you could also just do it like we've done it here in the spreadsheet and kind of you know, update these values over time.
17:46 You could turn this into a formula, so like you could do v lookups And say like, if the leads from the UK give it the score of 1 38, and then you can kind of like multiply the different scores together to get the final score.
17:56 So that's something you could do. But you know, we won't cover this cover that in this video. Just wanted to kind of show you how you kind of dig into these different segments and, and understand like what types of customers are really good for you.
18:11 Okay? Hopefully that was helpful.

Why is it important to do a pipeline analysis?

What countries drove the highest quality leads?

What percentage of leads aren’t qualified?

Why is it important to have enough leads in a category in order to assign it a score?

Looking historically at your lead data and segmenting by attributes gives you a good idea of what types of leads are more or less likely to close. This can feed into a storing system to automatically qualify leads, or just be used to align marketing better to sales.

Vexpower Learning Certificate of Completion

Congratulations envious-robot-49 💪