top of page

Subscribe Form

Thanks for submitting!

Post: Subscribe

Vlookups can solve anything

Updated: Aug 30, 2022

Do you keep it simple? You should do, it sounds easy, right? The clue is in the word simple.


In my experience, some of the hardest things can be done with simple solutions. But yet, I have seen some of the most intelligent people overcomplicate solutions with straightforward answers. I call this ‘The burden of intelligence’.


This is something I fortunately lack.


I believe this is mainly because the more you know, the more options you have to solve something. This means making the right choice can be more challenging, particularly when you haven't experienced the issue.


The other reason this can happen is down to knowing the actual need or use for the work you are doing; often, we as humans like to jump straight into the solution without knowing what is needed from whoever is presenting the problem. Knowing what is needed always helps. You don't need a solution or even all the details to start with.


The things I look to understand before I dive in are:

  1. Impact of the problem/request

  2. What should the expected outcome be - Presentation for a QBR, Forecast for Sales from increased marketing spend etc.

  3. Who is the audience? This helps more with priority and style of presentation.

  4. When it is needed (I always work better to deadlines)


Having these things keeps focus and understanding of what is required to happen. This means we don't jump straight in without establishing the basics first, and it gives us time to think about the best way to solve the issue and map that out either physically, in Confluence or Jira, or just in our head.


As already mentioned, the aim here is to keep it simple. You can build complexity later. Most problems in Revenue Operations, Sales Ops, Billing or any area of work don't need a full statistical analysis to successfully complete. Some do, but most won't.

 

Data analysis and modelling are things I have learned as I have gone along in my career, I have fumbled through making many mistakes. I am not sure if there are mistakes I haven't made. Some have been minor, where only I have seen and have no impact; others have meant that I have sent some fundamentally wrong recommendations to the CFO. Or that one time when I thought I had updated one record in the Billing database, but it actually updated 160,000... I'm sure to cover this in another blog


I built my skills up slowly and primarily at TalkTalk, where I had some excellent foundational learning. I started with some basic payment matching; then, I learned to manage bank reconciliations to within 0.5% on more than £1B ledger. I found discrepancies in settled credit card payments to help recover hundreds of thousands of pounds.


Most of the work I did there was based on keeping it simple, transparent and easily handed to someone as we rotated work.

So let us look at how we can take an issue that could get complicated and keep to a simple solution:


Company A - let us call them Awesome Inc. - has acquired Company B - Blaze Ltd. - to compliment their product set and to allow for bigger cross-sell opportunities. They both only operate within the United States for now. We need to know what customers they have in common today to ensure we do not target them.


This is a fairly common type of request in Revenue Operations. Often in different guises. This should be pretty straightforward, but by now, you should know things aren't simple… we have different Account IDs, Names and addresses that don't match fully. So the simple Vlookup won't work for now.


Given my general opinion that you can do almost anything in Excel with a combination of a Vlookup, Concatenate, Pivot Table and IF. That's where I will start, but we have no (Simple) way to create a unique matching field directly in this scenario, so what do we do if Vlookups are out of the question?


Well, frankly, I'm still doing a Vlookup. I love them, don't give me this, INDEX(MATCH Is better rubbish, I know it is. But I'm still doing a Vlookup - something is comforting in doing it for me, like when you lay on a beach, get a massage or when Liverpool loses… OK, that might be slightly too far, but you get the point; I like Vlookups.


When we have no matching columns but not enough to complete a full enough evaluation through the concatenation of different fields, we need to get more data!


This could be as simple as running both data sets through the same data enrichment tool like Lusha, ZoomInfo or Cognism. Or you could take a challenging route and analyse the data, do a fuzzy look up and can match on names of accounts with the country - that could work, but frankly, it's pretty time-consuming, and I don't generally go that route unless it's my last resort. Instead, I would take the data from within any of the tools.


For this example, I have put both Awesome Inc and Blaze Ltd into the lists and exported them to Excel for both. I will be given all the firmographic data they have; more importantly, there will be a unique ID. The ZoomInfo Company ID. We can do a VLookup to see what ZoomInfo ID matches on each data set.


We need to pull in the matches from both tables so we can do two Vlookups. One, to get the Awesome Inc vs Blaze Ltd matches and Vlookup two would be the same but reversed.

We can filter the matches, pull them into a single tab, and have what we need for the next stage.


We have our lovely unique list of customers, who we know are joint customers and already have both products. To get what we need, we will now need to understand the profile of these customers.


What makes these customers need both products?

This is a complicated question to answer. It could warrant a complete statistical analysis to establish all variables and their relationships to one another. Or we could get to an actionable list with little effort and look to refine it as we go.


So we have our customer data enriched with more data about who and what the businesses do.


So what else is the same?


Something I have found as a good starting point is to start with something that commonly differentiates businesses from one another, their employee count.


You can learn a lot from a company's employee size, and so we can look at the % breakdown in a straightforward Pivot Table, we can pull in the count and group by your company's segmentation. In this example, the segments are based on employee counts:

Segment Name

Employee Count Range

SMB

0-100

Mid-Market

101-1000

Enterprise

1001-10000

Strategic

10001+

The break out of the data from the pivot is below for you:










We can see here that Mid-Market appears to be the sweet spot for cross-selling.


This doesn't say much other than any business within an employee range of 100-1000 employees within the US, which is potentially a lot of companies; by my research, there are roughly 350,000 companies in that one segment. Let's see if we can get that down to a number that we can reasonably target and send a message that will resonate with that group.


Now we know it's Mid-Market - we can look at something else that could break that out more - Industry.


The industry is a great way to break out the data. It can be at the primary or secondary level, which can be pretty profoundly targeted if needed. Industry data from any source is not perfect, but it is helpful for all sorts of scenarios like this.


Once again, we can just pull the Primary and Secondary industries into the very same Pivot Table you have pulled in.


Adding in that data now shows us that we have 78% of all our joint customers in just two Primary Industries and four key Secondary Industries.

Primary Industry

Secondary Industries

Retail

Department Stores, Shopping Centers & Superstores

Retail

Flowers, Gifts & Specialty Stores

Retail

Jewelry & Watch Retails, Retail,

Manufacturing

Cosmetics, Beauty Supply & Personal Care Products

We can also see more than 40% of all the joint customers fit into the above industries. The Mid-Market Segment shows us a robust initial correlation between these industries, segments and common buyers.


This leads us to a pretty good and clear list now.


Mid-market companies that are within these specific industries. This brings the total market to around 2500 businesses in the US.


This is a much more manageable list to work with. Now it is best to start to check how many customers we have at Awesome Inc and Blaze Ltd. that fit this profile and do not appear on both lists.


We can finally get back to my favourite - Vlookup. As we have already done step one - we can confirm the following:


Step 1 - Filter to accounts that do not match from both lists - we can either work from this filter or copy the data to a new sheet. This is up to you, and I have no judgement either way… after all, I can't see your work in this fictional scenario anyway.


Step 2 - Now that we have the non-matches, we need to confirm if any industries match the ones listed above. We can make a quick List in a separate tab called parameters, and you can run a Vlookup in a new column on the primary data set on the industry against the list you have just created.


Step 3 - We have all accounts with those industries and therefore our initial cross-sell targets. We can now pull in any records around the contacts we wish to target based on the (hopefully already) known profiles from both Awesome Inc and Blaze Ltd.


This list can be directly worked on now, and a campaign to get cross-sells started without anything more than a Pivot table and a Vlookup.


Don't get me wrong; this is a pretty straightforward example. There will be times when it's not reasonable to do a lookup to solve a problem, but I would be pretty sure that in most cases, we can start with something simple before getting too complicated.


Never underestimate the good old Vlookup, and never overthink. That's what intelligent people do, don't be smart, be like me.





Recent Posts

See All

Comments


Post: Blog2 Post
bottom of page