Analyze repeat purchases when customer identity changes

A repeat-purchase metric depends on what the system considers one customer. If an anonymous shopper later creates an account, two observed identifiers may represent one person. Counting them separately can hide repeat behavior. Merging identifiers without reliable evidence can create repeat behavior that never occurred.

Build the identity contract before calculating the rate, and report how much of the order population remains unresolved.

Follow three orders through an identity map

The original synthetic identity orders contain:

The accompanying identity map links anonA and accountC01 to canonical customer C01. For this exercise, those links are assumed to come from verified account evidence. anonB remains unresolved.

The raw identifiers each have one order. After resolution, C01 has two. The unresolved order must remain visible; it cannot safely be assigned to C01 or treated as evidence of a different known person.

Preserve order grain during the join

SELECT o.order_id,o.observed_id,m.canonical_id,o.amount_paise,
 CASE WHEN m.canonical_id IS NULL THEN 'unresolved' ELSE 'resolved' END AS identity_status
FROM identity_orders o
LEFT JOIN identity_map m USING(observed_id)
ORDER BY o.order_id;

The result must contain exactly three orders and 6,000 paise. A many-to-many identity map could duplicate orders during the join, inflating both frequency and value. The fixture enforces one canonical mapping per observed identifier.

from build_and_verify import database

db = database()
rows = db.execute('''SELECT o.order_id,m.canonical_id,o.amount_paise
 FROM identity_orders o LEFT JOIN identity_map m USING(observed_id)''').fetchall()
assert len(rows) == 3 and sum(r[2] for r in rows) == 6000
assert len({r[0] for r in rows}) == len(rows)
counts = {}
for order, canonical, amount in rows:
    if canonical is not None:
        counts[canonical] = counts.get(canonical, 0) + 1
assert counts == {'C01': 2}
assert sum(r[1] is None for r in rows) == 1
assert sum(r[2] for r in rows if r[1] is None) == 3000
db.close()
print({'resolved_customers': 1, 'resolved_repeat_customers': 1,
       'unresolved_orders': 1, 'unresolved_value_paise': 3000})

Among resolved customers, the repeat-customer rate is 1/1, or 100%. That is a conditional result for one known customer, not a credible estimate for the whole business. Half of the order value and one-third of the orders remain unresolved.

Do not turn missing identity into a shared customer

Replacing every unknown canonical ID with the same string such as unknown makes unrelated orders look like purchases by one recurring customer. Conversely, assigning a different synthetic customer to each unresolved order assumes none belong to the same person.

Neither assumption is established here. Keep order-level unresolved counts and value, and restrict customer-level measures to the explicitly described resolved population. If you use bounds or sensitivity scenarios, label their assumptions rather than presenting them as corrected truth.

Decide whether history should be restated

A newly verified link can change historical customer counts. Store the identity-map version and the report's as-of time. Otherwise a past dashboard may change without any new purchase, leaving stakeholders unable to reproduce the earlier number.

Some analyses need the best currently known identity; others need the identity known at a historical decision time. The second requires dated identity evidence and an as-of join. Using future identity knowledge in a historical targeting evaluation can introduce information that was unavailable when the decision was made.

Shared devices, account transfers and business accounts also challenge the assumption that one account equals one person. Choose the unit appropriate to the decision and avoid speculative matching based only on similar names or shared network addresses.

Exercise: add a second unresolved order and show how a shared unknown customer would falsely create a repeat customer. Then demonstrate that the order count and value remain unchanged under your corrected treatment.

NeuraPath's Data Analytics with Generative AI course connects joins and data quality with customer reporting. Identity coverage is part of the metric, not a cleanup detail to hide after calculation.

Continue learning

This article is part of the Customer and product analytics sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Market-basket analysis without confusing popularity with affinity.
• Continue with Measure feature adoption without counting internal users.