Custom Measures: Duplicate Customers

josephmckeownjosephmckeown Moderator, Lightspeed Staff Posts: 73 moderator
edited July 7 in Reporting & Analytics

There are lots of ways that customers may be listed with multiple profiles. Sometimes they inadvertently use different email addresses, sometimes an employee may record them with slight variance in their name. Whatever the source of the duplication, it becomes a challenge to manage.

So, can we identify customers who may have multiple profiles in Lightspeed Retail using Lightspeed Analytics? Glad you asked!

Option One: Duplicate emails

Finding Duplicate Emails

Using a new empty Marketing report, let's start by adding a new Custom Dimension...

...to gather results together, I'm going to use the Lower() function that we discussed in Cell-Based Functions

and inside the function, I'm going to search for the "email" field...

There it is! So upon, saving and running the report, we get:

When I save my Custom Dimension, and run...


....I get a list of emails that looks like this...


Now of course, the list itself is not what I'm interested in. I want to count how many customers are associated with that email.

Counting Duplicate Emails

So, the next thing we want to do is count the customers. To do this, I'm going to open the Dimensions and Measures for "Customers"


and add the measure of "# of Customers"

When I run the report, I should get a count of how many customers are identified with each email address.


So now I can sort on the # of Customers measure...


Highlighting for me the customers needing the most actions.

Identifying customers with the duplicates

Finally, we may want some indicators as to which customers are being surfaced within the duplicate emails.

An important first step is to filter on our Custom Dimension "lower email"...


...to not include instances of the lower email being null.

Otherwise, all the customers without an email address will be listed in the first cell, which will be of no value in this analysis.


Next, let's go back to the Dimension of Customer, clicking on the gear icon next to Name: Full Name:

From the gear icon, we're going to select: "Add Custom Measure> List"

Which will add this new measure to our data:

When we run the report, it will list all the names associated with that email address:


Option Two: Duplicate names

Finding Duplicate Names

We could use a similar approach to search for duplicate names:

Opening a new empty Marketing report, and adding a new Custom Dimension, using the Lower Full Name:



Upon Saving:


Counting Customers:

Next we'll go the Dimensions and Measures for customers, and add the measure of "# of Customers"...


...again, sorting from highest to lowest...


Special Bonus

Now here's a trick that may come in handy. Sometimes, you may get customers who are identified by only one name.

This is a custom filter calculation I've put together to identify if these are one-name only customers, who you may wish to filter out from your actions. Up to you.


if((length(${customers.first_name})+1=length(${customers.full_name}))=yes,yes,if((length(${customers.last_name})+1=length(${customers.full_name}))=yes,yes,no))


Showing Customers

Finally, for each duplicate name found, we could add a list of identifiers: their emails or phone numbers for example.

Just navigate to Customer, Email, (Gear Icon)> List


And run....


...and we'll get our list of associated emails!


Spoiler Alert, these are the Custom Dimension formulae we used today

Lower email: lower(${contact_emails.address})

Lower name: lower(${customers.full_name})

One Name Custom Dimension: if((length(${customers.first_name})+1=length(${customers.full_name}))=yes,yes,if((length(${customers.last_name})+1=length(${customers.full_name}))=yes,yes,no))

Calculations Home

Post edited by Heather on
Joseph McKeown
Lightspeed Analytics and reporting consultant
Lightspeed HQ
Sign In or Register to comment.