INTRODUCTION
I have been involved with a number of B2B demand generation marketing campaigns lately that put my Google Sheet skills to the test. I am proficient at Microsoft Excel from using it for classes at the University of Colorado Boulder Leeds MBA program so the transition to learning Google Sheets has been pretty straightforward. In my opinion (and if you check out this Reddit thread, sounds like my opinion is shared), Excel is still superior in terms of features, functionality and heavy duty number crunching. That being stated, Google Sheets has come a long way since it was introduced and can tackle the majority of problems in the B2B demand generation marketing data world. I would be remiss if I didn’t thank a few friends whom shall remain anonymous, but have always been extremely helpful when I have had a vexing Google Sheet question. If it was not for your assistance, I could not even begin to put this post together. Let’s jump in…
PIVOT TABLE
One of our publicly traded enterprise technology vendor clients executed a creative direct mail demand generation campaign to prospects combined with digital touches to book appointments. The demand generation marketing and sales development representative (SDR) teams started off with a 5,400 lead list that was pulled from their Salesforce.com CRM. From that initial list, they had to analyze and append a number of items to execute the campaign correctly. As the saying goes, “good data in, good data out… bad data in, bad data out..”. In the prospect list of 5,400 leads, there were a number of companies with multiple leads from the same company. For example, COMPANY A may have 15 different “leads”. The first lead may be an IT Director, second a Director of IT Infrastructure, the third be the IT Manager in a different division of the same company, etc. You get the idea. One of the requirements of the campaign was that our client wanted to limit the amount of mailers going out to a single prospect account and be strategic to who received the mailer at each particular prospect account. Therefore, they wanted to quickly look at this list of 5,400 leads and get a sense of the number of companies represented out of the 5,400 and how many leads were associated with each company. In this scenario, we used a PIVOT TABLE to present this information. I built a dummy data set with 200 leads to show how this is accomplished.
SPLIT COLUMNS
Another problem that we faced with the direct mail list project was that when the original lead list was pulled from the CRM, first name and last name of the lead was in one column represented as full name. In order for the direct mailer to be executed properly, the full name had to be broken out to execute the campaign. Now, perhaps the simplest way to solve this problem immediately would have been to pull the same list again but ensuring first name and last name are pulled into two columns versus one column, full name. Well, as we know, in a publicly traded fast moving enterprise IT company, that ship had sailed as data had already been manipulated a fair amount before we uncovered this nuance. Have no fear, Google Sheet’s SPLIT COLUMN feature is here! One detail that I learned quickly is that full names are not always two words (ex. John Smith). For example, some leads have three, four or five worded names (John Smith III, Fred Van Slatten, Tom James PHd. And, again, you have to remember the data is coming from Salesforce.com. You could argue that some of these pieces of data shouldn’t be included in the name, but, whomever entered in the data in the first place entered it in so we have to work with it. So, you need to be cognizant of this when you use the SPLIT COLUMN feature. If not, your data can get messed up rather quickly. Here is a short video using dummy data to explain how to use the SPLIT COLUMN feature in Google Sheets.
FILTER VIEW
The beauty and curse of Google Sheets is that it is built natively in the cloud and is fantastic for collaboration. In other words, you can have literally people from around the world making updates to a Google Sheet at the same time. Google is an expert at collaboration and you can see this in their recent advertisement highlighting animals collaborating making music using the Shared Piano. On one hand, this is helpful as it eliminates files being sent through email, versions being overwritten inadvertently, etc. That being stated, I have seen Google Sheets get messed up very quickly if you have a number of different people working on the same sheet. Let’s call it the “too many cooks in the kitchen scenario”.
The FILTER VIEW is a tool that I’ve come to love that helps to create and SAVE certain views showcasing different components of the data. In the direct mail project use case, we wanted to create views that highlights the leads specifically for different sales people so they could review their prospects and make determinations on which leads would be receiving the direct mail. In a spreadsheet of 5,400 leads, this may seem daunting for some. Here is a short video using dummy data to explain how to use the FILTER VIEW feature in Google Sheets.
VLOOKUP
And finally, we’ll end this post with a fairly advanced yet incredibly useful function, the Vertical Lookup, or VLOOKUP for short. In this particular use case, a virtual trade show had been sponsored and attended by our enterprise technology vendor client. They also had a speaking slot. The deliverable of the event to our client from the virtual trade show organizer was a couple of spreadsheets with contact information of who attended their presentation. We had to append data from two different spreadsheets in order for the data to be complete before being uploaded into our client’s CRM system for their sales team to follow up with the attendees.
Here are the four different formulas I was using in the video. Feel free to copy, paste and make the appropriate edits for your own projects.
=VLOOKUP($D2, 'Virtual Trade Show Registration List'!$E:$I, 2, FALSE) =VLOOKUP($D2, 'Virtual Trade Show Registration List'!$E:$I, 3, FALSE) =VLOOKUP($D2, 'Virtual Trade Show Registration List'!$E:$I, 4, FALSE) =VLOOKUP($D2, 'Virtual Trade Show Registration List'!$E:$I, 5, FALSE)
SUMMARY
Hopefully you have found some value in this post and videos to take on your own Google Sheets projects. It is an amazing tool that can help a B2B demand generation marketer solve many problems. It certainly is not as sexy as the latest and greatest data analytics SAAS tool, but, based on my experience, it has a ton of features and functionality to handle most B2B marketing data projects. And, to state the obvious, I’d guess 95% of the features and formulas that are available in Google sheets are not touched by the average user. So, I’d encourage you to explore some of the trainings that are available online to brush up on your skills. It’s amazing what you can find out there. If you have a formula or tip you’d like to share, feel free to leave it in the comments. And, as always, feel free to reach out to me @ dave@colliver.io with questions and I’d be happy to try and help. Also, check out our Data Management Services section to learn more of how we can help you and your B2B marketing and sales teams.