Outreach campaign dataset for spreadsheet and SQL tasks

The story behind the data

Fabrikam, a fictional maker of warehouse shelving, had a stand at a trade fair in late August 2026. 90 visitors left their business card and agreed to receive information. Fabrikam then sent them a sequence of up to three emails:

• Step 1 on Tuesday 8 September 2026,

• Step 2 on Monday 14 September,

• Step 3 on Tuesday 22 September.

Each contact was randomly given subject line A or B for all of their emails, 45 contacts each. The sequence stopped for a contact after a reply, an unsubscribe or a hard bounce. A soft bounce did not stop it: the next step was still sent.

The file fictional-outreach-campaign.csv (in the Downloads on this page) has one row per email sent. Every company, domain and figure is invented; all domains end in .example, which can never be a real address.

Data dictionary

Rules the data keeps

• Every row is either delivered or bounced: delivered + bounced = 1.

• A bounced email has no opens, replies, unsubscribes or meetings, and its bounce_type is hard or soft. A delivered email has bounce_type none.

• reply_sentiment is none exactly when replied is 0.

• A meeting is only booked after a positive reply.

• Each contact replies at most once, because a reply ends the sequence.

• variant, recipient_domain, industry and country stay the same for all emails to one contact. Some contacts share a company domain.

Facts to check against

• 238 emails to 90 contacts at 81 company domains: 90 in step 1, 78 in step 2, 70 in step 3.

• 227 delivered and 11 bounced (6 hard, 5 soft).

• 13 replies: 4 positive, 5 neutral and 4 negative. 3 meetings, 3 unsubscribes.

How to work with it

• SQL: open the SQL Query Playground, load the CSV and query the table data. Nothing is uploaded. In other SQL tools, import the file as a table called data and the queries below work unchanged.

• Spreadsheets: open the CSV, select all, insert a pivot table. The 0 and 1 columns can simply be summed: the sum of replied is the number of replies.

• Each contact has exactly one step 1 row, so filtering on step = 1 gives one row per contact. That is the easy way to count contacts in a pivot table.

Tasks

1. Get to know the data. How many emails, contacts and company domains are in the file? How many emails were sent in each step?

2. Delivery and bounces. Calculate the delivery rate and the bounce rate of all emails. How many bounces were hard and how many soft, and in which steps? Why are all hard bounces in step 1?

3. Reply rate per step. For each step, calculate replies ÷ delivered emails. What does the result say about sending step 2 and step 3?

4. Reply rate per variant. What share of contacts replied, for subject line A and for B?

5. What did people say? Count the replies by variant and sentiment, and the meetings and unsubscribes by variant. Does the picture from task 4 still hold?

6. Opens and replies. Calculate the open rate (opened ÷ delivered) per variant. Find the emails that got a reply but were never counted as opened. How is that possible?

7. Industries. Calculate the share of contacts who replied for each industry, highest first. Which industry would you target next, and how much do you trust this ranking?

8. Countries. Build a pivot table of emails, bounces and the bounce rate per country. What could explain the differences, and what should Fabrikam check?

9. Who left the sequence early? Find every contact who got fewer than three emails, and explain why for each group.

10. Is B really better? Use your numbers from task 4 for a two-proportion z-test, as in the lesson on A/B tests. Is the difference significant at the 5 percent level?

Solutions

The results below were calculated from the file with a script and checked in the SQL Query Playground and in SQLite.

SELECT COUNT(*) AS emails, COUNT(DISTINCT contact_id) AS contacts, COUNT(DISTINCT recipient_domain) AS domains
FROM data

SELECT step, COUNT(*) AS emails
FROM data
GROUP BY step
ORDER BY step

SELECT COUNT(*) AS sent, SUM(delivered) AS delivered, SUM(bounced) AS bounced,
  ROUND(100.0 * SUM(delivered) / COUNT(*), 1) AS delivery_rate,
  ROUND(100.0 * SUM(bounced) / COUNT(*), 1) AS bounce_rate
FROM data

SELECT step, bounce_type, COUNT(*) AS emails
FROM data
WHERE bounced = 1
GROUP BY step, bounce_type
ORDER BY step, bounce_type

SELECT step, COUNT(*) AS sent, SUM(delivered) AS delivered, SUM(replied) AS replies,
  ROUND(100.0 * SUM(replied) / SUM(delivered), 1) AS reply_rate
FROM data
GROUP BY step
ORDER BY step

SELECT variant, COUNT(DISTINCT contact_id) AS contacts, SUM(replied) AS replies,
  ROUND(100.0 * SUM(replied) / COUNT(DISTINCT contact_id), 1) AS reply_rate
FROM data
GROUP BY variant
ORDER BY variant

SELECT variant, reply_sentiment, COUNT(*) AS replies
FROM data
WHERE replied = 1
GROUP BY variant, reply_sentiment
ORDER BY variant, reply_sentiment

SELECT variant, SUM(meeting_booked) AS meetings, SUM(unsubscribed) AS unsubscribes
FROM data
GROUP BY variant
ORDER BY variant

SELECT variant, SUM(delivered) AS delivered, SUM(opened) AS opened,
  ROUND(100.0 * SUM(opened) / SUM(delivered), 1) AS open_rate
FROM data
GROUP BY variant
ORDER BY variant

SELECT send_id, contact_id, step, variant, reply_sentiment, meeting_booked
FROM data
WHERE replied = 1 AND opened = 0

SELECT industry, COUNT(DISTINCT contact_id) AS contacts, SUM(replied) AS replies,
  ROUND(100.0 * SUM(replied) / COUNT(DISTINCT contact_id), 1) AS reply_rate
FROM data
GROUP BY industry
ORDER BY reply_rate DESC

SELECT country, COUNT(*) AS emails, SUM(bounced) AS bounced,
  ROUND(100.0 * SUM(bounced) / COUNT(*), 1) AS bounce_rate
FROM data
GROUP BY country
ORDER BY bounce_rate DESC

SELECT contact_id, COUNT(*) AS emails, MAX(bounced) AS bounced, MAX(replied) AS replied, MAX(unsubscribed) AS unsubscribed
FROM data
GROUP BY contact_id
HAVING emails < 3
ORDER BY emails, contact_id

SELECT step, SUM(bounce_type = 'hard') AS hard_bounces, SUM(replied) AS replies, SUM(unsubscribed) AS unsubscribes
FROM data
GROUP BY step
ORDER BY step

License

The data is released into the public domain under CC0 1.0. Use it however you like, no credit needed.