How to format a customer list in Excel

Published by: Erica Sanford    |   Category: "How To" Articles, Blog, Email List    

Understanding How to Move Customer Data from your CRM to your ESP

With such a large variety of business software options on the market to choose from, sometimes these platforms don’t interact as well as most of us would like them to. There are numerous Customer Relationship Managers (CRM’s) and Email Service Providers (ESP’s) on the market to choose from. But what happens if your CRM and your ESP clash – Don’t play nice together? Your ESP may tell you that your list was not formatted correctly or it needs to be in a different column order to be able to upload it into their platform. Or, when you get to the personalization stage, you don’t know why it is showing your customer’s full name instead of just their first name.

Below I’m going to explain how to reorganize or move data within an Excel Spreadsheet.


In the example above, you can see that I have the customer’s full name in column B. To split the column so that you have one column for first name and one column for last name, the next step is to go to the click the header of column C so that the whole column is selected. Then go to the CELLS section in the HOME tab and click the INSERT button.

Sorting Customer List

This will insert a new column and shift the original column C to column D.

Customer Email List Manipulation

The next step is to select Column B just like you did with Column C earlier and click the column header to highlight the whole tab. You will then go to the DATA tab and under DATA TOOLS and click TEXT TO COLUMNS.

Insert Email in List Excel

This will open up a “Wizard” box as shown below. The Wizard will detect what type of data you have. Click Next to proceed.

Email Import Wizard Excel

In this example, a single space separates the first and last names. So in the next screen of the Wizard below, only check “Space” in the DELIMITERS section. The Wizard will show you an example of how your new columns will look.  Then click NEXT.

Text to Columns Excel

The Wizard will ask you what type of data you’ll be formatting. In this example, it is Text. Choose GENERAL or TEXT in the COLUMN DATA FORMAT section by highlighting the new columns in the DATA PREVIEW and then click FINISH.

Clean Email List Import Excel

A pop-up will appear asking you if you want to replace the contents of the destination cells. Since we already cleared a new column for the data to go into in column C, and there is currently no data in those cells, click OK.

Excel Replace Cell Data

And Voila! Your data is now split into First and Last Name in two separate columns.

Moving Customer Data Excel

You can also perform the same steps to split a column that is separated by a comma such as my column D in this example. When you get to the step in the wizard that asks you which DELIMITERS you want to sort by, just ensure that you have selected the check-box for comma.


Internet Marketing Terms You Should Know

Published by: Erica Sanford    |   Category: "How To" Articles, Blog, Marketing    

On the Email Answers blog, we’ve written quite a few articles that cover some of the core components of online marketing. We’ve covered email marketing, email validation, business reputation as well as really bad marketing ideas and the list goes on. I thought it was important to compile a list of the most commonly used marketing terms, so I put together a short list of terms that are imperative to anyone learning about online marketing.


Marketing Terms and Abbreviations

  • A/B Testing – a method in marketing research where variables in a control scenario are changed and the ensuing alternate strategies tested, in order to improve the effectiveness of the final marketing strategy. (Testing 2 versions)
  • Above The Fold – the section of a Web page that is visible without scrolling.
  • Affiliate Marketing – revenue sharing between online advertisers/merchants and online publishers/salespeople, whereby compensation is based on performance measures, typically in the form of sales, clicks, registrations, or a hybrid model.
  • ALT Text – HTML attribute that provides alternative text when non-textual elements, typically images, cannot be displayed.
  • B2B – business that sells products or provides services to other businesses.
  • B2C – business that sells products or provides services to end-user consumers.
  • Bounce Rate (web) – In web analytics, the percentage of visitors who leave after viewing a single page.
  • Bounce Rate (email) – In email marketing, the percentage of emails in a campaign that are returned as undeliverable.
  • Call To Action (CTA) – the part of a marketing message that attempts to persuade a person to perform a desired action.
  • Cascading Style Sheets (CSS) – a data format used to separate style from structure on Web pages.
  • CDN (content delivery network) – a network of computer servers geographically distributed and designed to accelerate the delivery of web pages, images and files by routing user requests to the server that’s in the best or closest proximity or position to serve them.
  • Click-Through – the process of clicking through an online advertisement or email advertisement to the advertiser’s destination.
  • Click-Through Rate (CTR) – The average number of click-through per hundred ad impressions, expressed as a percentage.
  • Conversion Rate (CR) – the percentage of visitors who take a desired action.
  • Cost Per Lead (CPL) – online advertising payment model in which payment is based on the number of qualifying leads generated.
  • Cost-Per-Action (CPA) – online advertising payment model in which payment is based solely on qualifying actions such as sales or registrations.
  • Cost-Per-Click (CPC) – the cost or cost-equivalent paid per click-through.
  • CPM – cost per thousand impressions.
  • Customer Acquisition Cost – the cost associated with acquiring a new customer.
  • Customer Relationship Management (CRM) – a system for managing a company’s interactions with current and future customers. It often involves using technology to organize, automate, and synchronize sales, marketing, customer service, and technical support.
  • Dedicated Hosting – hosting option whereby the host provides and is responsible for the equipment, dedicating an entire server to the client’s websites.
  • Dedicated IP – an IP address dedicated to a single website or customer.
  • Email List Cleaning – a comparative process to remove bad or known undeliverable email addresses from a list.  See details of Email List Cleaning.
  • Email Marketing – directly marketing a commercial message to a group of people using email. See details of Email Marketing.
  • Email Marketing Agency – a company that manages your email marketing campaigns, creative designs, data quality, opt-outs and deployments. See details of Email Marketing Agency.
  • Email Validation – testing the validity and deliverability of an email address. See details of Email Validation.
  • Geo-Targeting – a method of detecting a website visitor’s location to serve location-based content or advertisements.
  • HTML Email – email that is formatted using Hypertext Markup Language, as opposed to plain text email. See examples of email designs.
  • Inbound Link – a link from a website outside of your website.
  • Inbound Marketing – a marketing model whose sales performance relies on the initiative of its client base to find and purchase a product.
  • Key Performance Indicator (KPI) – a measurable value that demonstrates how effectively a company is achieving key business objectives.
  • Landing Page  sometimes referred to as a “lead capture page” is a single web page that appears in response to clicking on a search result or an online advertisement. The landing page will usually display directed sales copy that is a logical extension of the advertisement, search result or link.
  • Link Building – the process of increasing the number of inbound links to a website or web page in a way that will increase search engine rankings.
  • List Segmentation-The ability to group contacts based on similarities. For example, you can group people based on similar contact or companies properties, form submissions, email actions, pages views, and more.
  • Log File – file that records the activity on a web server.
  • Marketing Automation – the use of software to automate repetitive tasks related to marketing activities and connect different parts of the marketing funnel.
  • Opt-In Email – email that is explicitly requested by the recipient.
  • Organic Search– When a visitor originates from a search engine from a non-paid ad or link.
  • Outbound Link – a link to a website outside of your website.
  • Pay Per Click (PPC) – online advertising payment model in which payment is based solely on qualifying click-throughs.
  • Pay Per Lead (PPL) – online advertising payment model in which payment is based solely on qualifying leads.
  • Pay Per Sale (PPS) – online advertising payment model in which payment is based solely on qualifying sales.
  • Permission Marketing – marketing centered on obtaining customer consent to receive information from a company. Usually related to email marketing.
  • Return On Investment (ROI) – the ratio of profits (or losses) to the amount invested in your advertising.

How to Remove Duplicates in your Email List using Excel

Published by: Erica Sanford    |   Category: "How To" Articles, Blog, Email List    


How to Dedupe or Remove Duplicate Emails using Excel

Deduping an email list using Excel is easier than you think.

Since we are asked quite often about removing duplicates in an email list, we thought it might be time to post a simple “How To” guide explaining how easy it really is to dedupe an email list using Excel.

Using Excel to remove, view or hide duplicates in 3 steps or less.

  1. The simplest and quickest way of removing duplicates in Excel is clicking the Remove Duplicates button in the Data Tab

De-Duping Emails using Excel

  1. After clicking Remove Duplicates, a selection box pops up. You will need to choose the column(s) you want to filter by. I chose every column, so only one entry from each email remains.

Deduping emails selecting column

3. Click OK in the selection box. As you can see in the image below, Excel will tell you how many duplicates it found as well as how many unique values remain. The duplicates will be removed with only the first instance of the records remaining.

This will treat the first instance of the records as the master value and delete all others.

Finding Duplicate Emails

What if you want the first instance of the duplicate record to not be in your list either? Or, you are interested in viewing each duplicate instance and determining for yourself if you want to keep it? Luckily this is made simple with conditional formatting.

  1. Again with your data selected go to Home → Conditional Formatting → Highlight Cell Rules → Duplicate Values.

Email Conditional Formatting

  1. The box below will appear. You can leave the selection in the box as is with “Duplicate” chosen and click OK. All of the duplicate values will now be highlighted in red for you to see. If your list is short like this one you can go through and manually delete them.

Selecting Duplicate Emails

If your email list is larger, follow these steps to sort your formatted list for easy viewing.

  1. Go to Data → Sort. The box shown in the image below will appear.

Sort Duplicate Emails

  1. Choose the appropriate Column, Sort On, and Order then click OK. You will now see that all your highlighted duplicates have moved to the top of the list grouped together for easy deletion!

The previous two options were easily done, but maybe you don’t want to delete the duplicates from your list – Maybe you just want to hide them. Here is way to quickly dedupe your data by hiding the rows that contain duplicate data.

  1. Go to Data → Filter → Advanced Filter. Another dialogue box will appear.

Filter Email Addresses

  1. With your cursor in the “List range” text box, highlight the range you want to hide duplicates from. In my case I selected B2-D17. Also, make sure you have the“Unique records only” box checked. Then click OK. Only unique values will remain visible, but remember since this was only a filter,  the values have not been deleted; they are just hidden in rows.

Now that was pretty easy – Wasn’t it?

Remember, even if you have a list of unique emails, you should consider using Purelist to scrub and clean your emails or have your email list cleaned and validated before sending to it. Having undeliverable emails in your list or sending to complainers, spam seeds or dead domains will only cause your IP Reputation to be damaged. Keeping your email list clean, valid, deliverable and free from spam seeds or BOTS will help you deliver your marketing emails to your users inbox on a more consistent basis.