1 Reply Latest reply on Aug 2, 2017 6:37 AM by Jim Dehner

    Profiling Customers by Order Dates

    Derrick Lang

      I am running into a roadblock and can't find the specific answer I need by searching.


      My goal is to 'profile' based on first order date of a specific product and then then cadence of reorders (if they do). In the attached example workbook, I have Customers who are ordering binders from the Superstore data set. My business case is that we expect Customers to reorder this binder product yearly and to drive volume would like to identify customers who have not yet ordered their binder after a year of use.


      Using this logic I created a LOD equation to isolate first orders from the subsequent orders based on the filtered product sub category:


      //First Order by Customer by Sub Cat




      IF [Order Date] = {

          FIXED [Customer Name],[Sub-Category] : min([Order Date])



      THEN "First Order"



      ELSE "Repeat Order"




      Now I know the first order, but I don't know how to take the next step to classify all the customer's orders by year so I can say "their first order was in 2012, and they reordered in 2013, and 2014" without going line by line. I would like to be able to group customers who ordered in 2012 but perhaps did not reorder in subsequent years to target them for marketing contact when looking at current year sales.


      The above LOD might not even be the best way to isolate that. Thank you in advance!

        • 1. Re: Profiling Customers by Order Dates
          Jim Dehner


          See the attached


          it will produce this   - if this is what you want :



          You were very close

          you needed to determine the fixed min order date as -      { FIXED [Customer Name], [Sub-Category]: Min([Order Date])}


          And then do your labeling against that     



          IF [Order Date] = {    FIXED [Customer Name],[Sub-Category] : Min( [fixed min order date])    }

              THEN "First Order"ELSE "Repeat Order"END


          The fixed order date statement will determine the first order date for each combination of customer and subcategory - if subcategory is on in the viz you will get the first overall order (MIN)


          Let me know if this helps


          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.