Importing and Cleaning Data

Quick reference

• 00:04 As a marketer, it's incredibly important to have up-to-date clean data.
• 00:09 Clean data means that you have the right information about your prospects
• 00:12 and leads.
• 00:13 And you're not missing any relevant or
• 00:15 vital information that you can use in your marketing campaigns.
• 00:19 Like the country they're based in, their current email address, or
• 00:22 how they've been engaging with your marketing.
• 00:25 You can use this data to segment and nurture your leads to
• 00:28 help them through the buyer's journey and toward becoming paying customers.
• 00:33 In this video, we're going to look at how you can clean up your email marketing data
• 00:36 to make it more actionable for you and your business.
• 00:40 This can be quite common when you're importing data from different sources, or
• 00:44 copying information from different formats.
• 00:46 So let's see how we can clean up this data and turn it into something usable for
• 00:51 Excel.
• 00:52 If I look at the data a little closer, I can see the different columns or
• 00:56 what should have been different columns are imported into one column.
• 01:00 The data is separated.
• 01:02 The names of the data columns are separated by commas.
• 01:06 I can confirm that,
• 01:07 if I look at some of the data, there are commas in between the different fields.
• 01:11 Well, Excel has a function to help us sort this out, and it's called Text to Columns.
• 01:17 If I select my data Ctrl+Shift and down arrow and then go to the Data tab,
• 01:22 under the Data Tools groupings you'll find Text to Columns.
• 01:27 Click on it and we get a wizard that consists of three screens.
• 01:31 Now, the first choice we need to make is we need to choose whether the fields
• 01:36 are separated by a character, like a comma, or if it's a fixed-width field.
• 01:40 Ours are separated by a comma, so we'll check off that it's delimited data.
• 01:46 Now, on the next screen, we'll choose a comma as the delimiter.
• 01:50 Your default might be a tab.
• 01:51 And if you choose the wrong character,
• 01:53 you won't see any difference in the preview window.
• 01:56 But if you choose the right one, like a comma, in this case,
• 01:59 you'll see the preview makes a big difference.
• 02:02 The final screen in this wizard is used to define the data type in each field, but
• 02:06 we're going to skip that for now.
• 02:08 We'll do this later.
• 02:10 And we click Finish, and there we have our data.
• 02:13 We can adjust the column widths so that it looks a little better.
• 02:18 Now, if this is good data, there's a few characteristics it will comply with.
• 02:23 The first one is that there are row headings,
• 02:26 which we can see right across the top.
• 02:29 The second one is that there shouldn't be any blank lines.
• 02:34 But we can see there's a couple in here.
• 02:36 The third is that they are consistent data types.
• 02:39 Now, with data types, how do you recognize consistent data types?
• 02:43 Well, if this is a text field, for example, and if Excel imported the text
• 02:47 field as a text field, then it would auto align the data to the left.
• 02:53 If it imports a date or number, like in the date field,
• 02:56 it will automatically import the data to the right-hand side of the cell.
• 03:01 This seems like consistent data types.
• 03:04 Let's fix the blank lines first.
• 03:08 And I can fix that by sorting the data and
• 03:10 it will either sort the blank lines to the bottom of the data or to the top.
• 03:16 So I select my data by selecting the headings and
• 03:19 Cntrl+Shift+right arrow then Cntrl+Shift+down arrow.
• 03:23 This quick selection method will stop once it hits a break.
• 03:28 So I'll have to continue holding the Ctrl+Shift, press and
• 03:31 release the down arrow key until you select all the data.
• 03:34 And then on the data tab under the sort and
• 03:38 filter grouping there is a sort button.
• 03:42 Before I click it, you'll see I have selected my data, including the headings.
• 03:47 So if I click on the sort button, there's a tick box here that says my data has
• 03:52 headers, and I have selected the headers.
• 03:55 So I've checked that off and now Excel deselects the headings,
• 04:00 because you don't want the headings sorted into your data.
• 04:04 And I can choose to sort by name.
• 04:06 Any order really is good here.
• 04:09 So let's hit OK.
• 04:12 As you can see, it has sorted the data and
• 04:15 those blank lines have moved to the bottom of the range of data out of our way.
• 04:20 If they were at the top, I could just select the blank lines, right-click, and
• 04:24 delete but it's not a problem in this instance.
• 04:27 To fix the format of my date column, I'm going to select the date column,
• 04:32 Shift+Cntrl+down arrow, and I'm just going to change the format.
• 04:38 Excel sees the date as a serial number.
• 04:40 The number shows how many days have passed since the first of January 1900,
• 04:46 so this is 43,000 days since then, and so on.
• 04:50 So if the date imports as this type of number,
• 04:53 it's ideal because the regional settings of the computer and
• 04:57 possible differences in Excel versions does not corrupt your data.
• 05:02 On the Home tab under the number grouping, I can click on the drop-down and
• 05:07 I can select a short date format for my dates.
• 05:10 And that fixes the date column.
• 05:14 Now, the last thing that appears to be wrong here,
• 05:17 if you look at the name column, is that the alignment is not quite right.
• 05:21 And this often happens with text columns.
• 05:23 The problem is that text often imports extra spaces,
• 05:26 sometimes before a word, sometimes after, sometimes there's extra spaces in between.
• 05:32 To fix that, I'm going to insert a column and I'm going to use a trim function.
• 05:37 I'm going to get rid of this column again later.
• 05:39 But what the trim function does is it will remove all the spaces before the word, and
• 05:44 all the spaces after the word, and all the extra spaces in between words.
• 05:49 So in between words, there should only be one space, but
• 05:53 if there are two or three it will remove the extras.
• 05:57 So we'll use the trim function on the first record here in our data.
• 06:01 And once we've done that, we can copy the formula down by double-clicking
• 06:07 on this fill handle, the little square in the bottom right-hand corner.
• 06:12 And now we have all our names.
• 06:14 So now we want to copy the clean version of the names into the first column.
• 06:20 We do Ctrl+C or Cmd+C on the Mac to copy.
• 06:24 We move across to the correct column, but
• 06:27 we don't do a regular paste because we'd be copying and pasting the trim formulas.
• 06:33 Instead, let's do a paste special.
• 06:35 So let's go up to paste.
• 06:37 Paste special, and I want to paste values only.
• 06:42 And then we hit OK.
• 06:43 And that sorts out all the extra spaces.
• 06:47 And now we can select that second column, and we can delete it.
• 06:52 And that sorts out and cleans up all my email marketing data.
• 06:56 As a marketer, data is king.
• 06:59 Without knowing how to quickly and accurately clean large sums of data,
• 07:03 I'm left with two options, to manually clean it or not use it at all.
• 07:08 Cleaning it manually would take far longer and
• 07:11 may even result in more errors being made along the way.
• 07:14 Not using the data at all means missed insights and opportunities.
• 07:19 So, with these handy tools and tricks up your sleeve, you can have usable data
• 07:24 in a matter of minutes and can confidently use it knowing it's error-free.

Lesson notes are only available for subscribers.