3 Replies Latest reply on Nov 22, 2016 1:01 PM by David Li

# Determine Repeat Customers

I need to determine repeat customers on a given day vs first time customers.

Once a customer has has made their first visit/order to the store, any subsequent visit (order) is considered as a Repeat visit.

I have attached a sample data set and below are the expected results:

 Date Store Gust Cnt - First Time Visitors Guest Cnt - Repeat Visitors 10/1/2016 Store 1 3 0 10/1/2016 Store 2 2 1 10/1/2016 Store 3 2 0 10/2/2016 Store 1 1 1 10/2/2016 Store 2 1 1 10/2/2016 Store 3 2 1 10/3/2016 Store 1 1 1 10/3/2016 Store 2 1 0 10/3/2016 Store 3 0 2 10/4/2016 Store 1 0 0 10/4/2016 Store 2 2 0 10/4/2016 Store 3 0 2

Can somebody guide me towards a solution?

• ###### 1. Re: Determine Repeat Customers

Hi Maxx! Try this:

IIF([Date] = { FIXED [User ID]: MIN([Date]) }, "First Visit", "Repeat Visit")

This uses a LOD calc to figure out the first day each customer came in. Note that this will give you multiple first visits if a customer made multiple orders in a day. If you want just the first order, then change [Date] to [Order ID] (assuming the Order IDs are sequential increasing).

• ###### 2. Re: Determine Repeat Customers

Thanks David!

This is great. Although, as you pointed out, it gives multiple first visits for customers with multiple orders in a day. Again as you noted, Changing the [Date] to [Order ID] almost works but it seems the order IDs recycle every so often so it doesn't count the first visits correctly in some instances.

This is a great starting point though and helps a lot. Please let me know if you have any suggestion for overcoming the above scenario. Thanks!

• ###### 3. Re: Determine Repeat Customers

You're welcome!

If the Order ID is unlikely to be recycled on the same date, then you could match against both the minimum Order ID and the minimum Date.

IIF([Date] = { FIXED [User ID]: MIN([Date]) } AND [Order ID] = { FIXED [User ID]: MIN([Order ID]) }, "First Visit", "Repeat Visit")