Black Friday customer email list clean-up sheet for small shops: fix gmial.com typos and drop dead and throwaway addresses before you send

Updated 26 Oct 2026 · Disclosure: the email checker linked below is an Unadhi product on the Apify Store. This guide is free, and the sheet works with a CSV from any checker that gives one verdict per address. Current Apify pricing is only on the Store page. This page has no affiliate links and nobody paid to be mentioned.

The finished sheet

list tab (example rows are made up)

The first row is the most common problem in a shop's customer list: a customer typed the address on a phone at checkout, so the order confirmation never arrived and every later email goes nowhere too.

Who this is for

Small online shops (Shopify, WooCommerce or a hand-kept list from a counter or a stall) that are about to send a Black Friday, Diwali sale or Christmas email to their own customers. Addresses with dead domains and typos bounce, and Google's sender guidelines ask senders to remove recipients who bounce repeatedly. Cleaning the list once before the big send costs a few minutes; this sheet does it in one pass. It is not for bought or scraped lists: send only to people who agreed to hear from you.

Step 1: put your customers in a tab named customers

Export your customers from your shop (for example the customer CSV export in your shop admin) and import it with File → Import → Upload, "Insert new sheet". Rename the tab customers and arrange it so that:

• column A holds the email address, starting in row 2;
• column B holds yes or no for "agreed to receive marketing email". Use the consent column from your shop's export if it has one; if your list has no consent record, type no until you know.

Step 2: check the addresses

With the email checker

Add a tab named input and put this in A1. It builds the run input from column A:

="{ ""emails"": [" & TEXTJOIN(",", TRUE, ARRAYFORMULA(IF(customers!A2:A="", "", """" & TRIM(customers!A2:A) & """"))) & "], ""checkDns"": true }"

Copy the cell's value, open the Email Verifier and Indian Validators (Half Price) on the Apify Store, switch the input to JSON and paste it. A Google Sheets cell holds about 50,000 characters, so for more than about 1,500 addresses build the input in batches (rows 2 to 1501, then 1502 onward) and run once per batch.

• Put only emails in this run. If you add phones, GSTINs or PANs too, their rows land in the same file with different columns.
• checkDns stays true: it is the check that finds dead domains. Exact duplicate addresses are removed before the check.
• The checker looks at each address's syntax, whether its domain exists and accepts mail (MX records), throwaway domains, role mailboxes such as info@, and likely typos of common providers. It sends only the domain to public DNS, and it does not contact any mail server.

In the run, export the dataset as CSV and import it into the Sheet as a new tab named verify. Do not rename the headers. The columns this sheet uses are input (the address as you gave it), verdict (valid, risky, invalid or unknown), reason, didYouMean, role and disposable.

Free routes

For a short list, the bounce report from your last campaign in your email tool is free and is the best evidence there is: an address that hard-bounced is gone. Free online checkers usually take a handful of addresses at a time. Any tool works with this sheet if you paste its results into a verify tab with the headers input, verdict, reason and didYouMean.

Step 3: build the list tab

Add a tab named list. Row 1 holds the headers below; row 2 holds the formulas, which you copy down as far as your customer list goes.

Notes on the formulas:

• The lookups in C to F find each column by its header name, so the column order in the CSV does not matter. They match your address against the input column, which holds the address exactly as you sent it.
• MATCH ignores upper and lower case, so Asha@Gmail.com and asha@gmail.com find the same row.
• Consent comes first on purpose: a perfectly valid address without marketing consent is still a "Don't send". Order and delivery emails are a different matter from a sale email.

Step 4: the summary tab

Add a tab named summary. In A1: =QUERY(list!G2:G, "select G, count(G) where G is not null group by G order by count(G) desc label G 'Action', count(G) 'Customers'", 0). Because the Action text carries the reason, you see how many addresses are dead domains, how many look like typos and how many lack consent.

For the send itself, filter list to Send and export column A, or use your email tool's own unsubscribe or suppression list for the removed addresses so they are not re-imported next time.

What to do with each group

1. Remove (domain-does-not-exist, no-mail-server, domain-refuses-mail, bad-syntax): these addresses cannot receive mail. Keep the customer record for orders, but take the address out of the marketing send.
2. Fix typo?: do not change the address yourself. Ask the customer on their next order, in your shop's account page or by phone if they gave one, and update it only when they confirm.
3. Hold (disposable-domain, no-mx-implicit-a-record-only): throwaway addresses often stop working within days. Leave them out of the big send.
4. Re-check later (unknown): the domain's DNS did not answer in time. Run those addresses again the next day before deciding.
5. Send: the domain accepts mail. Include a clear one-click unsubscribe, which Google's guidelines require for marketing email.

Check before you act on it

• "valid" does not prove the mailbox exists. The checker confirms the domain accepts mail; it does not ask the mail server about the person, so a closed mailbox at a working domain can still bounce. After the send, remove addresses that bounce.
• The number of rows in verify is close to the number of unique addresses in customers. If it is much lower, a batch is missing.
• Role addresses such as accounts@ or info@ are normal for business customers; column F is there for information, not as a reason to remove.
• Customer email addresses are personal data. Process only your own customers' list, for a purpose they agreed to, under the data protection law that applies to you (for example India's DPDP Act 2023 or the GDPR). This is not legal advice.

Partner programme opening soon

Partner programme opening soon: join the free waitlist. Planned terms are 50% of each referred sale (75% once you have 5+ paying customers), single level, paid through Gumroad only from revenue received.

Sources

• Email checker inputs and outputs (emails, checkDns; per-address input, verdict valid/risky/invalid/unknown, reason, didYouMean, role, disposable, freeProvider; duplicates removed; no SMTP probing; only the domain sent to public DNS): Apify Store listing.
• Removing recipients who bounce repeatedly, one-click unsubscribe for marketing messages, spam rate below 0.3%: Google, Email sender guidelines.
• Google Sheets INDEX/MATCH, TEXTJOIN and QUERY functions: Google Docs Editors Help.

Related

• Black Friday real discount check sheet
• Clean a contact CSV into a call sheet
• Google Shopping product listing check sheet

← All guides

Unadhi Guides are free, independent explainers. We correct errors as soon as we find them: write to mininglife7@gmail.com.