Fictional B2B lead list for data cleaning and SQL

1. Size

156 rows. 141 different websites as written, 140 after removing "www." (Fabrikam Couriers is in the list once as fabrikam-couriers.example and once as www.fabrikam-couriers.example). So there are 140 companies but 156 rows: some companies have two different contacts, and some people are in the list twice.

• Spreadsheet: =COUNTA(A2:A157), =COUNTA(UNIQUE(H2:H157)) and =COUNTA(UNIQUE(SUBSTITUTE(H2:H157,"www.",""))).

SELECT COUNT(*) AS rows_total,
       COUNT(DISTINCT website) AS sites_raw,
       COUNT(DISTINCT REPLACE(LOWER(website), 'www.', '')) AS sites_clean
FROM leads

2. Country spellings

27 different values for 12 countries. Germany is written five ways: Germany (39), DE (3), Deutschland (3), germany (2) and "Germany " with a space at the end (1). Counted together that is 48 leads in Germany. The other variants: United Kingdom, UK, Great Britain and England; Netherlands, The Netherlands, Holland and NL; Austria, AT and Österreich; Switzerland, CH and Schweiz; France and FR.

After cleaning: Germany 48, United Kingdom 22, France 11, Sweden 11, Switzerland 11, Netherlands 10, Austria 9, Italy 9, Belgium 8, Spain 8, Poland 5, Denmark 4. Together 156.

• SQL GROUP BY shows all 27 values. Some spreadsheet programs ignore capital letters in UNIQUE and pivot tables and show 26, because Germany and germany merge. The trailing space still makes "Germany " a value of its own.

• Fix: make a small mapping table (raw value, standard name), then add a column with VLOOKUP or XLOOKUP on the trimmed value.

• Note: England and Great Britain are not the same as the United Kingdom. For a country column they all point to the same country, but the example shows why a fixed list of allowed values (or ISO codes such as DE, GB, NL) beats free text.

SELECT country, COUNT(*) AS leads
FROM leads
GROUP BY country
ORDER BY leads DESC;

SELECT COUNT(*) AS germany
FROM leads
WHERE LOWER(TRIM(country)) IN ('germany', 'de', 'deutschland')

Run the two queries one at a time.

3. Missing values

13 rows have no email, 5 have no employee count. For an email campaign the missing emails matter more: those rows cannot be used at all. A missing employee count only makes a size filter less precise. Do not guess emails such as firstname.lastname@ to fill the gap: a wrong guess bounces, and an address the person never gave you raises data protection questions.

• Spreadsheet: =COUNTBLANK(L2:L157) and =COUNTBLANK(F2:F157).

SELECT COUNT(*) AS no_email FROM leads WHERE email IS NULL

4. Role addresses

12 rows: 5 info@, 2 office@, 2 sales@, 2 contact@ and 1 hello@. None of these rows has a first name, last name or role, because nobody knows who reads the mailbox. They are useful to reach a company, but not for a personal email, and you cannot tell whether one person or ten will read it.

• Spreadsheet: =COUNTIF(L2:L157,"info@*") and the same for the other four, added together. COUNTIF ignores capital letters.

SELECT lead_id, company, email
FROM leads
WHERE email LIKE 'info@%' OR email LIKE 'office@%' OR email LIKE 'sales@%'
   OR email LIKE 'contact@%' OR email LIKE 'hello@%'

5. Duplicate emails

7 addresses appear twice, so there are 7 extra rows. Each pair teaches something different:

When you merge a pair, keep the newest status, the newest contact date and the newest job title, and keep any opt-out.

• Spreadsheet: add a helper column with =LOWER(L2), then a second one with =COUNTIF(R$2:R$157,R2)>1 and filter on TRUE: 14 rows, 7 pairs.

SELECT LOWER(email) AS email_clean, COUNT(*) AS copies
FROM leads
WHERE email IS NOT NULL
GROUP BY LOWER(email)
HAVING copies > 1

6. Duplicates without an email

8 people appear twice: the 7 from task 5 plus Lotte Moreau at Fabrikam Physio (L029 and L042). Her row L029 has no email, so matching on email could not find it. Altogether the file holds 148 different contacts (156 rows minus 8 copies) at 140 companies.

Matching on names has its own risk: two different people can share a name. That is why the key here combines name and website. Eight companies, for example Coho Building and Trey Hotels, have two different contacts on purpose: same company, different person, so they are not duplicates. At Proseware Home and Garden, Hugo Jansen and Noah Jansen share a last name but are two people.

• Spreadsheet: a helper column =I2&" "&J2&" "&SUBSTITUTE(H2,"www.",""), then COUNTIF as in task 5. Ignore rows with an empty last name.

SELECT contact_first_name, contact_last_name,
       REPLACE(LOWER(website), 'www.', '') AS site,
       COUNT(*) AS copies
FROM leads
WHERE contact_last_name IS NOT NULL
GROUP BY contact_first_name, contact_last_name, REPLACE(LOWER(website), 'www.', '')
HAVING copies > 1

7. Opt-outs

10 rows say yes. The conflict is Sofia Steiner at Lamna Care: row L083 says she replied and opted out (last contact 22 September 2026), row L030 says no, and that row was contacted again on 25 October 2026. Because the opt-out was only stored on one copy, she was contacted after she had asked to stop.

Under the GDPR, a person can object to the use of their data for direct marketing at any time, and the data must then no longer be used for that purpose (Article 21(2) and (3)). In practice: an opt-out belongs to the person, not to one row, so it must survive every merge and every new import. Keep a separate suppression list of opted-out addresses and check every list against it.

• Spreadsheet: =COUNTIF(Q2:Q157,"yes"). Then filter the duplicate pairs from task 5 and compare column Q.

SELECT LOWER(email) AS email_clean, COUNT(*) AS copies,
       SUM(opted_out = 'yes') AS opt_outs
FROM leads
WHERE email IS NOT NULL
GROUP BY LOWER(email)
HAVING copies > 1 AND opt_outs > 0

opted_out = 'yes' is 1 when true and 0 when false, so SUM counts the yes rows in each group.

8. Lead sources

Counted on the raw file, with meeting or customer as "reached a meeting":

Website forms have the highest rate. Be careful: the groups are small (one more meeting for webinars would raise their rate from 9.1 to 18.2 %), the file still contains duplicates, and many leads are still open. The data is fictional, so it says nothing about real sources. A fair comparison also needs the cost per source and enough leads in each group.

SELECT source, COUNT(*) AS leads,
       SUM(lead_status IN ('meeting', 'customer')) AS meetings,
       ROUND(100.0 * SUM(lead_status IN ('meeting', 'customer')) / COUNT(*), 1) AS meeting_rate
FROM leads
GROUP BY source
ORDER BY meeting_rate DESC

9. Stale leads

43 leads: 30 contacted and 13 replied, all last contacted before 1 May 2026. 29 leads have the status new and have never been contacted (their last_contacted is empty). Contact data ages: people change jobs and companies move, so stale leads should be checked again before anyone writes to them.

• Spreadsheet: =COUNTIFS(O2:O157,"contacted",P2:P157,"<"&DATE(2026,5,1)) plus the same for "replied". If your program keeps the dates as text, filter the column instead.

SELECT COUNT(*) AS stale
FROM leads
WHERE lead_status IN ('contacted', 'replied')
  AND last_contacted < '2026-05-01'

Dates written as YYYY-MM-DD sort correctly even as text, which is one reason this format is used in data files.

10. A send-ready list

90 addresses. Step by step:

The SQL below returns 90 rows. Without subqueries the playground cannot count them in the same query; read the number of rows it shows.

SELECT LOWER(email) AS email_clean, COUNT(*) AS copies
FROM leads
WHERE email IS NOT NULL
  AND email NOT LIKE 'info@%' AND email NOT LIKE 'office@%'
  AND email NOT LIKE 'sales@%' AND email NOT LIKE 'contact@%'
  AND email NOT LIKE 'hello@%'
  AND lead_status NOT IN ('customer', 'disqualified')
GROUP BY LOWER(email)
HAVING SUM(opted_out = 'yes') = 0

The opt-out check sits in HAVING on purpose. A WHERE condition opted_out = 'no' would drop row L083 but keep row L030, and Sofia Steiner would be back on the list.

A clean list is not yet a lawful one. Whether you may email these people at all depends on where they are and how the data was collected: see where B2B leads come from.