A number of useful tips and tricks to standardise and spruce up your CSV and Excel contact data
Inevitably as a marketer you’ll receive spreadsheets full of contact data in a varying state of cleanliness. Whether be as output from a successful marketing campaign or a spreadsheet from a partner, everybody has differing data standards or formatting rules which makes merging and using the data often a long and tedious task.
It needn’t be. And nor do you need to be a super data scientist to apply a few simple techniques for getting that data fit for purpose.
This article will show you how to perform a few simple tricks in Excel to standardise your data.
Tip 1 – splitting Full Name into separate First Name and Last Name columns in Excel
A very common issue where you are presented with First and Last name data combined into a single Full Name field.
Firstly, next to your Full Name column, insert a column each for First Name and Last Name. We need to tackle the First and Last name columns separately. You could use the “Text to Columns” feature in Excel however this presents a problem where you have names with more than just a first and last name.
First Name
To extract the first name, enter the formula =LEFT(A2,FIND(" ",A2,1)-1) in a blank cell next to the Full Name value, i.e. B2, where A2 is the Full Name name value. Then drag the formula down (or double click on the corner) to apply the formula to the whole column.
Last Name
To extract the last name, enter the formula =RIGHT(A2,LEN(A2)-FIND(" ",A2,1)). As you’ll see below this grabs everything bar the first name which is great for when more than two names are involved.
Tip 2 – Proper Case, lower case and UPPER CASE in Excel
The formatting of data in contact spreadsheets you receive can be a bit hit and miss. Formatting and standardising data correctly is a must for several reasons. It aids with readability, helps with deduplication when the case of values is the same, it removes distraction and helps when importing and exporting data or integrating data between systems.
This section demonstrates why case is important and how to fix case issues in Excel.
Proper Case
Proper case is important in name and company fields in particular. When it comes to using your data, in an email campaign for example, who like to receive an email where the “Dear FirstName” shows up as “Dear samantha”?
How transform data into Proper case in Excel:
To turn a cells’ data in to proper case use the formula =PROPER(A2). It isn’t too complex this one.
Why not combine this with the technique with the extracting of the firstname and lastname in the exercise above:
Just add PROPER() around the outside of the previous formulae to split the first name and last names and sort the case out at the same time!
lower case
The use of LOWER() comes into play when formatting email addresses. The reason you’d want to both with this is for the purpose of deduplicating your data. If email addresses are presented with an assortment of case you might potentially miss a match. i.e. Dave@Company.com won’t match with dave@company.com depending on how you perform your dedupe. The other reason would be as part of your data standardisation. Email addresses formatted in lots of different ways can contribute to a loss of confidence in the data.
Once your email addresses are formatted correctly you can further improve confidence in your data by running them through an email verification tool to ensure you maintain your email domain reputation by removing any invalid or risky email addresses.
UPPER CASE
UPPER() is useful for formatting post codes (UK) or number plates.
Tip 3: Data Formatting
For readability and for taking data forward and being able to import into another system, data needs to be formatted consistently. For aesthetics yes, but you can expect errors in your import attempts if dates aren’t formatted as dates, numbers are text characters or not recognisable as currency if applicable.
Fortunately, Excel has some easy-to-use data type selectors you can use for this, simply select the cells you want to amend and select the relevant data type:
Excel has a number of options for Date formatting including Long and Short date format of including the Month name if you wish.
For Numbers, as you type, Excel will naturally allocate the General format and automatically detect what the data is. However, to make sure, particularly when pooling lots of data together, select the Number selector to fix the data as actual numbers rather than “text”. For two main reasons really, again you’ll experience errors importing your data in to another application, but the second reason – you won’t be able to perform calculations on numbers which are formatted as text.
Once you have converted the data to the number format you can add or remove the number of decimal points shown:
Tip 4: Improve your data through Categorisation
Opportunities to cleanse and clean your data don’t come around often due to the pressures of the day job. This is unfortunate as your data really is everything. Rather than just fixing and cleaning your data in Excel why not make some significant improvements and additions while you are at it.
Categorise your company data by industry
How well do you know the companies in your file? It might be there’s some industry or sector data in there already. There is huge value in being able to segment your data by industry to target certain groups with certain campaigns. Pick / decide on some core industries which are meaningful to your business and select one for each company in your data file. Often, the temptation is to try and use standard industry values – the real benefit comes from ignoring these and using ones which are more internally useful and will help your sales team to segment and target better and ultimately sell more.
Use job titles to categorise your contacts
If your excel contact data contains a populated column for job titles, using the Filter tool within Excel you can group the data together in stages and categorise each contact by:
- Job seniority
- Job function
- Decision-maker or not
For example you can select all contacts with a job title of “Head of ….” or “… Director …” and assign the a Job Seniority value of “Director”. The same principle for “Manager”. Continuing that process you could have all contacts assigned to one of the following 4 simple job seniority values:
- CXO
- Director
- Manager
- Officer
The same principle applies when it comes to job function, though job function is a bit more specific to your audience and how it is viewed by your business in a prospecting sense. Often, job function translates to department i.e.
- Operations
- IT
- Marketing
- Sales
- Administration
- Facilities
- etc
Lifecycle / Lead Status categorisation
In my experience, HubSpot present the best way to categorise contacts in their buying journey. They do this simply through the use of two fields:
Lifecycle Stage:
Lifecycle stage determines what stage the contact is to you in their buying journey using the following values:
- Subscriber / Unknown
- Lead – the contact has indicated interest
- Marketing Qualified Lead – the contact has been qualified by marketing to be contacted/targeted by the sales team
- Sales Qualified Lead – the sales team identifies a need
- Opportunity – the their is a sales opportunity with this contact
- Customer
- Evangelist – the customer is a willing case study and willing to refer you to others
- Other
Lead Status:
Lead status determines where the contact is, to you, in the buying journey using the following flexible values:
- New
- Open
- In progress
- Open deal
- Unqualified
- Attempted to contact
- Connected
- Bad timing
Data Summary
Data is your most important asset as a marketer and any opportunity to give it a good old clean, polish and tart-up should be taken. Whether you are combining data from different sources or just exporting your data into excel to look at it as a whole, the above tips should help you to make enhancements that will no doubt improve your chances of generating leads and progressing them through the buying journey.
Excel is still the number tool for making these improvements so take the time to get familiar with it’s capabilities, the tools available are now easier than ever to use.
