Mail Merge From Customer Exports: Cleaning Names, Addresses, and Salutations at Scale

10 min readPipeSheets Team

To turn a customer export into a merge-ready mailing list, do five things in order: split the name column into First Name and Last Name, build a Salutation column with a fallback for blanks, normalize the address fields (unit numbers, casing, and country), remove rows with no deliverable address, and collapse duplicate households down to one recipient. A raw Shopify, Stripe, or CRM export is never ready to feed straight into Word or your postcard vendor. This guide walks each step with the exact column names these systems produce and the edge cases that ruin a print run.

This is the export-to-postal workflow, not the hand-maintained spreadsheet one. If you keep names and addresses in a tidy Excel sheet you built yourself, cleaning it for a Word or Gmail merge is a different, lighter job. Here we are dealing with the mess a system dumps out: a single FullName field, addresses in random casing, empty rows, and the same household appearing three times.

Why do customer exports break mail merges?

Mail merge assumes one clean field per merge tag. Customer platforms do not export that way. They store data for transactions and support, not for addressing an envelope, so the shapes rarely line up with what your letter template needs. The typical failures are predictable once you have seen a few.

The recurring problems in a customer export used for mail merge:

One name field. Many exports give you a combined name, so your "Dear {First Name}" tag has nothing to bind to.
Business names mixed in with people. "Northwind LLC" and "Dr. Alan Grant" live in the same column as "sarah jones".
Shouting addresses. Fields arrive ALL CAPS or all lowercase because a customer typed them that way at checkout.
Blank or partial recipients. Rows with an email but no street address, or a name with no city, that cannot be mailed.
Duplicate households. Two people at the same address, or the same person from two orders, becoming two pieces of mail.
Inconsistent country values. "US", "USA", "United States", and blank all in one Country column.

Start by learning your export's column names

You cannot map merge fields until you know exactly what headers the export uses. A Shopify customer CSV uses First Name, Last Name, Email, Company, Address1, Address2, City, Province, Country, and Zip. That is good news: Shopify already splits the name for you. Stripe customer and billing data uses name (a single field), email, and address components named line1, line2, city, state, postal_code, and country. A generic CRM export often gives you one FullName or Contact Name column and free-text address fields. Open the file and read the header row before you touch anything else, because the rest of the job depends on which shape you have.

Watch the ZIP and Province columns before Excel opens the file. Excel silently strips the leading zero from a 07030 ZIP and can reinterpret province or unit values. Clean and export the CSV with a tool that preserves text fields, then open the result, rather than editing the raw export in Excel first.

How to split FullName into First Name and Last Name

If your export already has separate First Name and Last Name columns, skip this section. If it hands you one FullName field, you need to split it, and a naive split on the first space will embarrass you. Real name data is full of traps: titles, suffixes, multi-word last names, and business names that are not people at all.

The edge cases a split has to survive:

Titles: "Dr. Alan Grant" should give First Name Alan, not Dr. Strip leading Dr., Mr., Ms., Mrs., and Prof.
Multi-word last names: "Maria Del Rosario Garcia" and "Vincent van Gogh" break a two-token assumption.
Suffixes: "Robert Downey Jr." should not put Jr. into Last Name on its own.
Business names: "Northwind Traders LLC" or "Acme, Inc." is not a person. Detect LLC, Inc, Corp, Ltd, and Co and route these rows to a company salutation instead of a first name.
Single-token names: "Cher" or a mononym leaves Last Name empty, which is fine as long as your salutation handles it.

A practical rule set: trim whitespace, remove a known leading title, treat everything up to the first space as First Name and the remainder as Last Name, and flag any value containing a business suffix so it never gets a "Dear FirstName" greeting. You will still want to eyeball the results, because no rule catches every human name.

Raw FullName            ->  First Name   Last Name        Is Company
Dr. Alan Grant          ->  Alan         Grant            no
sarah jones             ->  Sarah        Jones            no
Maria Del Rosario Garcia->  Maria        Del Rosario Garcia  no
Northwind Traders LLC   ->  (blank)      Northwind Traders LLC  yes
Cher                    ->  Cher         (blank)          no

How to build a Salutation column with a fallback

The greeting line is where merges go wrong in the most visible way. A blank First Name produces "Dear ," printed on 40 letters. The fix is a dedicated Salutation column you compute once, so your template only ever references one clean field. The logic is a fallback chain: use the first name if you have a real one, use the company name for business rows, and drop to a generic greeting for anything blank or uncertain.

First Name   Is Company   Company            ->  Salutation
Sarah        no           (blank)            ->  Dear Sarah
(blank)      yes          Northwind Traders  ->  Dear Northwind Traders
(blank)      no           (blank)            ->  Dear Valued Customer
Cher         no           (blank)            ->  Dear Cher

Decide the generic fallback wording before you build the column, because it will appear in public on real mail. "Dear Valued Customer", "Dear Neighbor", or "Hello there" all read better than a broken tag. The point is that no letter ships with an empty greeting, and you never patch salutations by hand across a 500-row file.

How to normalize addresses for postal delivery

Checkout forms let people type whatever they want, so address fields arrive in every casing and format imaginable. For a mailing you want them consistent and legible. There are four normalizations worth doing every time.

Address normalizations for a print run:

Casing: convert "123 MAPLE AVE" or "123 maple ave" to title case, "123 Maple Ave", so it reads well on a letter. Laser-printed personal mail looks better in mixed case than in all caps.
Unit numbers: keep Address2 (or line2) intact and consistent. USPS recognizes secondary designators like APT, STE, and UNIT, and prefers them at the end of the delivery address line, for example "123 Maple Ave Apt 4B".
Country consistency: map the free-text mess in the Country field to one value. Turn "US", "USA", and "United States" into a single standard so your vendor's presort or international logic does not choke.
Whitespace and nulls: trim stray spaces and standardize placeholder text like "N/A", "none", or "-" to a true blank so it does not print literally.

One casing caveat for machine sorting. USPS Publication 28 recommends uppercase for automated OCR scanning of high-volume, presorted mail. For a few hundred personal postcards or holiday cards printed on your own laser printer, title case is accepted and reads far better. If your mail house requires uppercase for a presort discount, apply an upper-case transform as the final step instead.

How to remove undeliverable and blank recipient rows

Every blank or half-filled row is a wasted stamp and a returned piece. A customer who only ever gave you an email address cannot receive a postcard. Before you merge, drop any row missing the parts a carrier needs: no street address, or no city, or a name and email but nothing else. Removing empty rows and columns also cleans up the trailing blank lines that exports love to append, which otherwise show up as ghost recipients at the end of your merge.

Be deliberate about the threshold. A row missing Address2 is still deliverable; a row missing Address1 and City is not. Filter on the fields that actually determine delivery, not on completeness for its own sake, or you will throw away good recipients who simply have no apartment number.

How to collapse duplicate households

Sending three identical letters to one address is the fastest way to look sloppy and burn budget. Duplicates come from two sources: the same person appearing in multiple orders, and different people sharing an address, like two members of the same household or two contacts at one company. For a household mailing you usually want one piece per address.

A workable dedupe key is last name plus normalized street address. Normalize casing and whitespace first so "123 Maple Ave" and "123 MAPLE AVE" collapse together, then keep one row per key. If you would rather mail both members of a household separately, dedupe on full name plus address instead. Decide the rule based on whether the letter is addressed to a person or to a home.

Before dedupe (last name + address key):
Sarah  Jones   123 Maple Ave   Apt 4B
Mark   Jones   123 MAPLE AVE   apt 4b
Sarah  Jones   123 Maple Ave   Apt 4B   (repeat order)

After collapsing on last name + normalized address:
Jones  household   123 Maple Ave Apt 4B   -> one mailing

Preview edge cases before printing 500 letters

The cheapest insurance in this whole process is looking at the hard rows before you commit to print. Sort or filter to surface the ones most likely to be wrong: rows flagged as companies, rows with an empty First Name, single-token names, addresses that were ALL CAPS, and anything that landed on the generic salutation. If those handful of rows look right, the routine ones almost certainly are. A before-and-after view of the same records makes the whole transformation auditable in one glance.

RAW EXPORT ROWS
FullName,Address1,Address2,City,Country
DR. ALAN GRANT,45 PARK LANE,,BOSTON,US
sarah jones,123 maple ave,apt 4b,newark,USA
Northwind Traders LLC,,,, 
,,,, 

MERGE-READY ROWS
Salutation,First Name,Last Name,Address1,Address2,City,Country
Dear Alan,Alan,Grant,45 Park Lane,,Boston,United States
Dear Sarah,Sarah,Jones,123 Maple Ave,Apt 4B,Newark,United States
Dear Northwind Traders,,Northwind Traders LLC,,,, (no address -> removed)
(fully empty row -> removed)

In that sample, four raw rows become two mailable letters: the titled name is fixed, the lowercase address is title-cased, the company routes to a company greeting but drops out for having no address, and the empty row disappears. That is the shape you want feeding your merge.

The merge-ready cleanup checklist

Before you run the merge, confirm every item:

Name is split into First Name and Last Name, with titles and suffixes handled.
Business rows are flagged so they never get a first-name greeting.
A Salutation column exists with a real fallback for blanks ("Dear Valued Customer").
Addresses are consistent in casing, with unit numbers preserved in Address2.
Country values are collapsed to one standard.
Rows with no deliverable street address are removed.
Duplicate households are collapsed to one recipient per your chosen key.
You previewed the flagged edge-case rows and they look correct.

You can do all of this by hand for 20 rows. At a few hundred, it is worth building a repeatable pipeline so the next export cleans itself the same way. PipeSheets is built for exactly the prep steps here: run Quick Clean to trim whitespace, standardize placeholder nulls, and drop empty rows and columns in one click, then add steps like title-case transforms, find and replace for country values, and column renames to shape the file. You preview the before-and-after with detected column types, then download a clean CSV or XLSX that your merge template or postcard vendor can read without mangling ZIP codes.

The merge itself still happens in Word, Gmail, or your print vendor's tool, and choosing the household-versus-person rule is a judgment call only you can make. But the cleaning that stands between a raw export and a stack of correctly addressed letters is mechanical and repeatable. Do it once as a pipeline and every future mailing starts from a merge-ready file.