2 Replies Latest reply on Aug 16, 2018 10:13 AM by Fred Triest

    Month and Day Parameter Calculation

    Fred Triest

      WORKBOOK ATTACHED (version 2018.2 and Global Superstore Orders 2016 sample data)

       

      Issue: I am having trouble figuring out how to utilize a month and day parameter to see where New, Lapsed, and Renewed customers are in a dynamic date range where the parameter is day and month, but the rows are years. I have an existing month and day parameter that works perfectly for finding current active customers and the sales they bring in YOY (see “Active Customers and Sales” worksheet with the "Sales and Customer Date" parameter).

       

      Use case: This would help answer the question, “I see we have x number of new customers, x number of returning customers, and x number of customers who haven’t ordered anything so far this year, but where were we at this same month and day last year, the year before, etc?”. Then the user can select one of the three groups and focusing on ramping up efforts of getting new customers, or getting old customers, etc.

       

      Below are the definitions and examples of New, Lapsed, and Renewed customers.

       

      New

       

      Definition: A New customer is the date they make their first order and they will be counted as a New customer for the rest of that year.

       

      Example:  Customer Aaron Bergman from Global Superstore’s first order was on 2/19/2012 which makes him a New customer from 2/19/2012 to 12/31/2012. If the user interacts with the parameter and places the day and month anywhere before 2/19, Aaron Bergman is not counted as a new customer for the 2012 row. If they place the parameter on or anywhere after 2/19, they will be counted as a new customer for the 2012 row.

       

      Lapsed

       

      Definition: A Lapsed customer is when the following year after their first order date from any prior year, they are considered lapsed until they make their next order for that year.

       

      Example:  Customer Aaron Bergman’s first order ever was on 2/19/2012 so he is considered a New customer from 2/19/2012 to 12/31/2012. His next order for the following year was on 1/10/2013. If the user interacts with the parameter and places the day and month anywhere before 1/10 then he is considered a Lapsed customer for the 2013 row.

       

      Renewed

       

      Definition: A Renewed customer is when they make any follow up orders the following year(s) after their first order date or subsequent years for follow up orders.

       

      Example:  Customer Aaron Bergman’s first order ever was on 2/19/2012 so he is considered a New customer from 2/19/2012 to 12/31/2012 for the 2012 row. His next order for the following year was on 1/10/2013. If the user interacts with the parameter and places the day and month anywhere before 1/10 then he is considered a Lapsed customer for the 2013 row. If the user slides the parameter to 1/10 or any other day and month after, the customer is considered a Renewed customer for the rest of the 2013 row. For the 2014 row, Aaron Bergman’s first order for 2014 was on 5/31. If the user slides the parameter on or after 5/31, then he is labeled a Renewed customer for the 2014 row.

       

      Below is a rough image to show how one customer (Aaron Bergman) would contribute to the entire superstore for the three definitions listed above if a user selected 3/25 for the parameter. When 3/25 is selected, Aaron Bergman would contribute 1 new customer in 2012, 1 renewed customer in 2013, 1 lapsed customer in 2014, and 1 renewed customer in 2015.

       

      Aaron Bergman Example (New Lapsed Renewed).png

       

      Attempted Solutions:

       

      I have tried the Find new/lost customers by month  and Calculating YOY customer retention solutions, however I haven’t had luck integrating my current month and day parameter in the calculations.

       

      Any help with this is greatly appreciated!!

        • 1. Re: Month and Day Parameter Calculation
          swaroop.gantela

          Fred,

           

          Apologies for the delayed response.

           

          Your use of the parameter is quite ingenious.

           

          I don't think I quite got there, but maybe this can give ideas.

          I made attempt at using your LODs and MONTH, DAY comparisons.

           

          First Order Date Ever:

          { FIXED [Customer Name]: MIN([Order Date])}

           

          First Order Date each Year:

          { FIXED [Customer Name], [Order Date (Years)]: MIN([Order Date])}

           

          New Flag:

          { FIXED [Customer Name],[Order Date (Years)]:MAX(      // fix for customer and each year

          IF YEAR([Order Date])=YEAR([First Order Date Ever])      // if this is the first year

          AND MONTH([Sales and Customers Date])>MONTH([First Order Date Ever])      // if greater than first order month ever

          OR (MONTH([Sales and Customers Date])=MONTH([First Order Date Ever])      // or if same month but

              AND DAY([Sales and Customers Date])>=DAY([First Order Date Ever]))      //  greater day

          THEN 1 ELSE 0            // return 1 which can be summed up for aggregations

          END)}

           

          Lapsed Flag:

          { FIXED [Customer Name],[Order Date (Years)]:MAX(

          IF YEAR([Order Date])>YEAR([First Order Date Ever])

          AND (MONTH([Sales and Customers Date]) < MONTH([First Order Date each Year])  // compared to first order of the year

          OR (MONTH([Sales and Customers Date]) = MONTH([First Order Date each Year])

              AND DAY([Sales and Customers Date]) < DAY([First Order Date each Year])))

          THEN 1 ELSE 0

          END)}

           

          Renewed Flag:

          { FIXED [Customer Name],[Order Date (Years)]:MAX(

          IF YEAR([Order Date])>YEAR([First Order Date Ever])

          AND (MONTH([Sales and Customers Date]) > MONTH([First Order Date each Year])

          OR (MONTH([Sales and Customers Date]) = MONTH([First Order Date each Year])

              AND DAY([Sales and Customers Date]) > DAY([First Order Date each Year])))

          THEN 1 ELSE 0

          END)}

           

           

          Please see workbook v2018.1 attached in the Forum Thread.

          • 2. Re: Month and Day Parameter Calculation
            Fred Triest

            Thank you so much for your response! This definitely gets me on the right track!!