Audience overlap analysis: compare shared customers

Audience overlap analysis counts the customers shared by two campaign audience exports and separates them from customers exclusive to either list. Use permissioned identifiers already approved for this comparison, deduplicate each audience, and name the denominator beside every rate. The result supports campaign coverage planning without assuming that list membership proves exposure or response.

Establish what an identifier represents

A US distributor wants to compare a replacement-parts email audience with a maintenance-service email audience. Both exports contain the same company customer ID from an approved customer register. The team is comparing customer accounts, so several contacts at one company must collapse to one account before counting.

Record the export date, eligibility period, suppression rules, and customer level. Compare contact IDs with contact IDs or account IDs with account IDs. Do not mix the two. A reliable common key is a condition of this method; the analysis does not resolve identities across platforms.

Retain identifiers as text so meaningful leading zeros survive. Exclude blank IDs from the measured population and report their presence separately. Review differences in spacing and letter case against the register's rules rather than changing keys simply to increase the match count.

Deduplicate each approved list

Here are the distributor's distinct, nonblank audience IDs. The two columns are separate lists, not customer pairs.

Place A in A2:A7 and B in B2:B6. In Google Sheets, create the clean A list in D2:

=UNIQUE(A2:A7)

Create the clean B list in E2:

=UNIQUE(B2:B6)

Google's UNIQUE documentation explains that the function returns unique rows and discards duplicates. For a longer export, use the occupied source range after removing blank identifiers. Including entire empty columns complicates the counts.

Keep the raw exports and the clean lists available for review. A campaign audience with repeated contacts or repeated export rows should not receive extra weight merely because the source file has more lines.

Flag shared customers and calculate overlap

In F2, flag whether the clean A identifier is present anywhere in clean B, then fill through F7:

=IF(D2="","",IF(COUNTIF($E$2:$E$6,D2)>0,1,0))

In G2, perform the reverse test and fill through G6:

=IF(E2="","",IF(COUNTIF($D$2:$D$7,E2)>0,1,0))

The COUNTIF documentation describes conditional counting and notes that matching is not case sensitive. These formulas suit the plain numeric-text IDs shown here. Identifiers whose case matters, or which contain wildcard characters, require a reviewed matching method before using this approach. Covirage is not affiliated with Google.

The shared count is =SUM(F2:F7), which must equal =SUM(G2:G6). Audience sizes are =COUNTA(D2:D7) and =COUNTA(E2:E6): six and five customers respectively.

Union = Distinct A + Distinct B − Shared

A overlap rate = Shared / Distinct A

B overlap rate = Shared / Distinct B

Union overlap rate = Shared / Union

Percentages are rounded to one decimal place. The union measure is also called Jaccard similarity. None of these denominators is interchangeable: a campaign owner asking whether B adds customers beyond A needs the exclusive count, while someone comparing list similarity may prefer the union measure.

For aggregated traffic composition rather than customer matching, use website audience analysis. Session totals cannot supply these shared-ID counts.

Reconcile the counts and test the decision

The checks are small enough to inspect directly. A only plus shared is 3 + 3 = 6, which matches A. B only plus shared is 2 + 3 = 5, which matches B. The union is 6 + 5 − 3 = 8, also equal to 3 + 3 + 2.

Simply adding the two audience sizes gives eleven list memberships and double counts the three shared customers. A campaign plan should state eight unique eligible accounts if these lists are combined under the same eligibility rules. It should not describe those accounts as confirmed message recipients.

Now change one input: add customer 1009 to B. B becomes six customers, its exclusive group becomes three, and the union becomes nine. The shared count stays three. B's overlap falls to 3 / 6 = 50.0%, and union overlap falls to 3 / 9 = 33.3%.

That movement came entirely from broader B coverage. No existing customer changed membership. Save the export definitions with the results so a later reviewer can tell list expansion from a change in matching rules.

Use overlap as a planning input

The campaign manager owns the coverage question; customer operations owns the approved identifier and eligibility rules. Shared membership can prompt a review of coordinated messages or contact frequency. Exclusive membership can reveal accounts absent from the other campaign's approved list.

It cannot establish competitor reach or audience quality. Competitor audience analysis needs its own evidence, while influencer audience analysis asks a different question about an audience's relevance and observed response. Do not substitute a list similarity percentage for either assessment.

Where it goes wrong

• Counting source rows. Duplicate contacts inflate list size and distort the overlap denominator.
• Mixing identity levels. Account and contact identifiers measure different populations.
• Comparing different snapshots. Changed eligibility or suppression rules can move overlap without a campaign change.
• Treating missing IDs as nonmatches. Unknown identity is a coverage exception, not proof of exclusivity.
• Equating membership with reach. An eligible account is not necessarily a delivered message, an engaged recipient, or an incremental sale.

Compare your approved audience files in Covirage

Bring an approved audience file or Google Sheet with a common identifier and audience membership; Covirage maps the columns, and its tools compute and check the shared and exclusive counts and rates. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account, then set up your business and bring a sample of your own data.

See self-service analytics for the supporting approach to comparisons from business files.