Choosing a customer identifier in your database

The report shows a hundred and twenty customers. The boss says there are about eighty. Both are right: the database really does hold a hundred and twenty rows, and behind them there really are eighty people.

These are not duplicates in the usual sense. The records are different, the orders are real, nothing is broken. The problem is what the query uses to decide that two orders belong to the same person.

#Why name and phone are no good

Grouping by everything at once — email, name, phone — looks like the logical choice. The reasoning: if all of it matches, it is one person.

In practice it works out the other way round. The name and the phone in a checkout form are typed by the buyer, from scratch every time. Over a year, one and the same person will write:

ivan@mail.com   Ivan        +380671234567
ivan@mail.com   ivan        0671234567
ivan@mail.com   Ivan P.     +38 067 123 45 67

Grouping on three fields sees three different combinations here, and so three customers. Each of them has one order, each looks new, and no repeat-purchase report works.

#What to use as the identifier

The email. People type it to get a ticket, a receipt or access, so it is almost always the same one. And unlike a name, its format can be checked.

The rule is short: group by email and only by email, lower-cased.

SELECT LOWER(o.client_email) AS email,
       COUNT(*)              AS orders,
       SUM(o.total)          AS revenue
FROM orders o
GROUP BY LOWER(o.client_email)

Ivan@Mail.com and ivan@mail.com are the same person. Whether the database treats them as equal depends on the collation: typical MySQL settings ignore case, Postgres does not. LOWER() here is not a belt-and-braces measure but a way to make the query behave the same regardless of a setting you never thought about.

#Where to get the name and phone, then

From the latest order. Not from the grouping, not “whichever comes up first” — from the newest one, because that is where the freshest data is. Here is the column you add to the query above for that:

SUBSTRING_INDEX(
  GROUP_CONCAT(o.client_name ORDER BY o.created_at DESC SEPARATOR 0x1F),
  0x1F, 1
) AS client_name

The construction is unusual, but it does exactly what is needed: it joins all the names into one string, newest first, and takes the first of them. The separator is a control character rather than a comma, because a comma can perfectly well turn up inside a name.

#Why you must not accept an identifier from the front end

A rule of its own, and it has no exceptions.

If the page sends a user_id to the server, that identifier can become anything at all: someone opens the developer tools and swaps in another person’s. From there they see another person’s orders and another person’s documents.

The server has to work out who the customer is on its own — from a verified token or from the session. Anything that arrives from the browser is data to display, not data to select by.

#What to do when there is no email

It happens: orders come in over the phone or through a messenger. Then the phone number becomes the identifier — but not in the form it was typed in.

Strip it down to digits and take the last nine. The country code, the plus sign and the spaces stop mattering, and +380 67 123 45 67 and 0671234567 become one person. The same normalisation is needed when merging duplicates.

#Takeaways

Name and phone are form fields; they do not identify a person.
Group by email, lower-cased.
Take the name and phone for the record from the latest order.
Do not accept an identifier from the front end: the server decides who the customer is.
With no email, use the phone, normalised down to the last nine digits.

#Common questions

#What if one person orders from two different email addresses?

Then they show up as two customers in the report, and that is more honest than the alternative. You can bring them together by hand, or on an extra match — a normalised phone number, for instance.

#Wouldn’t it be better to have our own customer ID?

It would, and that is what systems with sign-up do. But it still has to be tied to something on the first order — and what it gets tied to is the email.

#What about a report that is already wrong?

Nothing in the data needs rewriting. The error lives in the query, not in the database: fix the grouping and the whole history recalculates correctly from day one.

#Sources

MySQL — GROUP_CONCAT — separator behaviour and sort order
From our own experience

The customer identifier: why it has to be the email