Count People, Not Joined Rows, in SQL Email Audiences

Before using a SQL result to brief a Migma campaign, prove that the query counts the entity you intend to email. A join can return several rows for one person. A plausible total can therefore represent purchases, event combinations, or duplicated profiles rather than recipients.

Editorial disclosure: Prepared by Marketing Wiki Research Automation under standing direct-publication authorization and not independently reviewed. Product capabilities are vendor-documented unless labeled otherwise; sources were refreshed on September 10, 2026.

Migma’s persona workflow can shape a draft for an audience, while lists and segments organize recipients. Neither step repairs a wrong upstream cohort definition. Keep the data query, creative brief, and final sending audience as three separately checked artifacts.

A six-row audience that contains two people#

A fictional ceramics studio wants to invite previous buyers to a glaze-care workshop. Its analyst joins customers, orders, and page visits. One customer has two orders and two visits; another has one order and two visits.

This fixture runs in SQLite:

CREATE TABLE people (person_id TEXT PRIMARY KEY);
CREATE TABLE orders (order_id TEXT, person_id TEXT);
CREATE TABLE visits (visit_id TEXT, person_id TEXT);
INSERT INTO people VALUES ('A'), ('B'), ('C');
INSERT INTO orders VALUES ('o1','A'), ('o2','A'), ('o3','B');
INSERT INTO visits VALUES
  ('v1','A'), ('v2','A'), ('v3','B'), ('v4','B');

SELECT p.person_id, o.order_id, v.visit_id
FROM people p
JOIN orders o ON o.person_id = p.person_id
JOIN visits v ON v.person_id = p.person_id;

The result has six rows: four combinations for A and two for B. There are only two represented people. This follows the matching-row behavior documented in SQLite’s SELECT reference. It is not evidence of six customers, six purchases, or six qualified email addresses.

A COUNT(DISTINCT person_id) can repair this particular person count. It does not automatically repair every aggregate in the same query. If an order amount appears on each joined row, summing it still repeats the amount for each matching visit. Fix the grain of the input before calculating money or activity totals.

State the business predicate before repairing the SQL#

For the workshop invitation, suppose the actual rule is “a person has at least one order and at least one recorded visit.” The query needs existence, not every order-visit combination:

SELECT p.person_id
FROM people p
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.person_id = p.person_id
)
AND EXISTS (
  SELECT 1 FROM visits v
  WHERE v.person_id = p.person_id
);

The expected result is A and B, once each. SQLite documents EXISTS as a test of whether the subquery returns any row. The choice of existence is our proposed implementation of this specific business rule. A different rule, such as “visited after the most recent purchase,” needs ordering and time conditions that this fixture deliberately does not provide.

Write those omitted conditions down. Otherwise a clean two-row result can conceal a semantically wrong audience just as easily as a six-row result.

Make cardinality a reviewable artifact#

Retain these outputs beside the query:

For real data, add the source snapshot time, identity key, date window, timezone, handling of deleted records, and the intended row grain. Inspect a few positive and negative identities using approved internal access. Do not export personal data into an unnecessary prompt just to explain a count.

A useful assertion is row_count = distinct_person_count when the contract promises one row per person. That assertion catches duplicate identity rows. It does not establish consent, deliverability, correct date logic, or whether two person IDs belong to one human.

Move from evidence to Migma creative#

Give the Migma brief the verified audience description: previous ceramics buyers eligible for a care workshop invitation. Include the actual workshop facts and avoid telling the generator that all qualifying people viewed a particular product unless the query established that condition.

Use a synthetic persona when testing the draft. Then review the actual list or segment used for sending. Record three counts with different labels: analytical cohort, identities successfully mapped into the sending system, and currently eligible recipients. Investigate differences; do not force them to match by weakening exclusions.

The studio can now say why A and B qualify and why C does not. That is more useful than a large audience number. The fixture demonstrates join multiplication locally; it makes no claim about a vendor’s warehouse schema, SQL preview, integration availability, or production query performance.

For adjacent controls, use natural-language segmentation QA for filter meaning and the audience union check for overlapping selected groups.