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

    Determine Repeat Customers

    maxx patel

      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:



      DateStoreGust Cnt - First Time VisitorsGuest Cnt - Repeat Visitors
      10/1/2016Store 130
      10/1/2016Store 221
      10/1/2016Store 320
      10/2/2016Store 111
      10/2/2016Store 211
      10/2/2016Store 321
      10/3/2016Store 111
      10/3/2016Store 210
      10/3/2016Store 302
      10/4/2016Store 100
      10/4/2016Store 220
      10/4/2016Store 302


      Can somebody guide me towards a solution?

        • 1. Re: Determine Repeat Customers
          David Li

          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
            maxx patel

            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
              David Li

              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")