Sales Development Hacks For Google Sheets

Sales development professionals invariably spend a fair portion of their time massaging prospect lists and prospect data in spreadsheets. We certainly do more than our fair share of spreadsheet hackery at RevBoss.

Here are a few of our favorite Google Sheets hacks that make life simpler for sales development pros.  Some are pretty simple, others require a bit of lightweight web programming. I’ve explained everything in super simple terms to make these hacks useful to a broad audience.  Skip to the end if you want to get right to the good stuff.

Enjoy!

Find & Replace

This one should be pretty obvious, particular for cleaning up company names for email personalization.  Find & Replace makes it easy to get rid of all of the abbreviations that come after business names like Inc, LLC, Ltd, etc.

It is actually pretty important to clean these up because it makes your personalization more effective.  For example, no one would ever send an email that said “How are things at Acme Inc?” — you would say “How are things at Acme?”  Luckily this is a 15-second fix with Find & Replace.

Your searches might include strings like:

• “, Inc”
• “, LLC”
• ” LLC”
• “, Ltd”

…all of which you can replace with “”. Make sure to include the comma and the space to catch the extra characters and to ensure that you don’t accidentally delete matches found in email addresses or names.

Split() / &

Split() and & provide a great way to clean up data and also to combine disparate data points into a single data point.

For example, you might have access to a list of raw data that includes a lot of extraneous data that you don’t want or not.  If the data is well structured, you can often find a pattern, split the text with a specified delimiter, and then put the pieces back together as necessary.

I encountered this with a recent client engagement.  We found a workable list of the target companies but the list included the stock ticker right after the company name, in the same cell as the company name.  So I did this:

=split(A2," ")

Which split the cell by spaces such that “Akamai Technologies AKAM” in one cell become three separate cells with values “Akamai”, “Technologies”, and “AKAM”.

I then did this:

=if(E2=""," ",D2)

Which is an If() statement that determined which cell included the ticker symbol — if E2 is blank then D2 is the ticker symbol from my split, which is what I want to delete. The cells to the right of the split were all blank, so I know that the first blank cell in a row is to the immediate right of the cell that I want to delete.

I drug this formula across the columns resulting from the split and got a clean set of company name components in separate cells, which I joined together like this:

=J2&" "&K2&" "&L2&" "&M2&" "&N2

The & function simply threads together the content from select cells with whatever copy you like, which were simply spaces in this case. Some of the company names were long and some where short, so I ended up joining up some blank spaces — which I cleaned up by using…

Trim()

Trim() is pretty straightforward — it cleans up all of the extra spaces include in a cell, before and after the first and last character.  Very useful for cleaning up your data so that spacing looks right around personalized merge fields.

Proper()

Much like Trim(), Proper() is also pretty simple — it corrects all capitalization to the proper case by capitalizing the first letter of each word in the cell.  This is great for scrubbing out all caps, all lowercase, and whatnot.

ImportHTML()

ImportHTML() can save TONS of time when you’re copying large data sets from tables online, i.e. list of something from Wikipedia.  Copy/Paste works in some cases, but you’re almost always left with lots of clean-up.  ImportHTML() essentially recreates the online table in a spreadsheet so that it is ready to go right away and much easier to manipulate.

This code will grab the list of North Carolina Tar Heels inducted into the Naismith Basketball Hall of Fame from Wikipedia and dump the data into a table in your Google Spreadsheet:

=importhtml("http://en.wikipedia.org/wiki/North_Carolina_Tar_Heels_men's_basketball",
"table",5)

Simply tell the formula the URL that contains the data you want, the type of data structure (usually “table” or “list”) you want to scrape, and the sequence number of the table or list you want — the 5th table on the page in my example. Changing the “5” in my example to a “6” will get you a list of Tar Heels that played or coached in the Olympics.

ImportXML()

I definitely saved the best for last.  ImportXML() is amazingly useful.  In short, it can turn Google Sheets into a massive, free web scraper.  You’ve just got to be comfortable mucking around with XPath.

The feature works great and pretty easily with XML.  I’ve been able to make it work with well-structured HTML, but it can be a real pain to dive deep inside a page to pluck that single important data point. In a few cases, I’ve stopped myself 20 or so minutes into an XPath boondoggle and simply tasked out the “scrape” to Mechanical Turk.

As a quick HTML example, here is some code I used to scrape the homepage URL from a list of Crunchbase URLs:

=IMPORTXML(A1,"//a[@class='homepage']/@href")

I used the URLs to back into a best-guess email domain, which I used to brute force guess (via MX validation) the email addresses of the CEOs at the companies…with surprisingly effective results.

As another example, Here is some code I frequently use to get the Alexa rank from a URL:

=value(ImportXML(CONCATENATE("http://data.alexa.com/data?cli=10&dat=snbamz&url=",E344),"//@rank[1]"))

This is useful for prioritizing and pre-qualifying companies based on site traffic estimates.

There are many, many other ways that ImportXML() can be enormously useful — just use your imagination, don’t be afraid of trial/error, and make use of resources like StackOverflow to find quick shortcuts.

So those are some of RevBoss’ tricks, though I’m constantly testing and learning more. Got anything I should add to the list?