5 Replies Latest reply on May 16, 2018 3:02 PM by Carlos Arias Fernandez

    Sales/Profits of lost customers in previous years

    Carlos Arias Fernandez

      Hi,

       

      Goal

      I am trying to analyze several metrics (such as sales, profit margin, location etc) of lost customers when they were still customers.

      Lost customer is defined as :

      {FIXED [Customer], [Year] : SUM([Sales]) } >0 AND {FIXED [Customer], [Year]+1 : SUM([Sales]) } <=0

      I believe the syntax above is not right but I am using it conceptually

       

      The idea is to be able to analyze what were the sales, profits or locations of those customer before they were lost. Is this possible?

       

      Data explanation

      The data has a row for every item purchased by a customer.

      Each customer can purchase multiple items within a year

      In the original data there are more than 50K items and thousands of customer and there si no predictable order in the rows

       

      Expected Results

      Example 1: A graph/table that shows that Customer B was lost in 2014 and its sales in 2013 were $17

      Example 2: A graph/table that shows that Customer A and D were lost in 2016 and their profit margin in 2015 were (7+1)/(10+5) for customer A and (7+2)/(10+7) for customer B

       

      I would appreciate any help guidance

      Thanks

        • 1. Re: Sales/Profits of lost customers in previous years
          Zhouyi Zhang

          Hi, Carlos

           

          Is it something like this? If yes, please find my solution attached.

           

          Hope this helps and let me know if you have question

           

          ZZ

          • 2. Re: Sales/Profits of lost customers in previous years
            lei.chen.0

            Hello Carlos,

             

            I really appreciate your style of asking question!

            Clear and logical.

             

            My solution is similar but with more user interaction, by using filter and parameter.

            Please refer to the attached workbook for details.

            image.png

             

            Regards

            Lei

            1 of 1 people found this helpful
            • 3. Re: Sales/Profits of lost customers in previous years
              Carlos Arias Fernandez

              This is very helpful and exactly what I was looking for in my question. Thanks a lot.

               

              But Customer B was lost during 2014 and 2015. How could I show that in 2014 Customer B was lost and its Sales, profits, etc the years before?

              What if some of the sales entries are zero? I modified the data file to show that customer A has an entry in 2015 but the sum of the items is is zero. Therefore the last year Customer A was a customer is not 2015 but 2014.

               

              My goal is to be able to see for each year:

              - How many customers we lost

              - What were their profit margin, Sales, Profit the year before

              - Where they were located

               

              Thanks

              • 4. Re: Sales/Profits of lost customers in previous years
                lei.chen.0

                Hello Carlos,

                 

                Very interesting scenarios!

                 

                Let me call them,

                 

                Case 1

                Carlos Arias Fernandez wrote:

                 

                But Customer B was lost during 2014 and 2015. How could I show that in 2014 Customer B was lost and its Sales, profits, etc the years before?

                 

                Case 2

                Carlos Arias Fernandez wrote:

                 

                 

                What if some of the sales entries are zero? I modified the data file to show that customer A has an entry in 2015 but the sum of the items is is zero. Therefore the last year Customer A was a customer is not 2015 but 2014.

                 

                 

                Case 2 is simple, update the calculation and this can easily be achieved.

                See worksheet "Example".

                 

                Case 1 is tough to obtain in the style of worksheet "Example".

                 

                Carlos Arias Fernandez wrote:

                 

                My goal is to be able to see for each year:

                - How many customers we lost

                - What were their profit margin, Sales, Profit the year before

                - Where they were located

                 

                Instead, I created two dashboards.

                 

                Dashboard "Fact Check"

                 

                It is the list of customers and their status.

                If there's no CHECK mark, the customer is lost in this year.

                Click the CHECK mark, the sales details of this customer will be shown.

                 

                Fact Check.png

                 

                (click CHECK mark)

                Fact Check2.png

                 

                Dashboard "Summary Check"

                 

                It is the summary of each status (during the selected year and the previous year).

                Select the year, and see the customer count of each status.

                Click a status "Lost", and see who is lost in details.

                Summary Check.png

                (click "Lost")

                Summary Check_2.png

                Please check out the attached workbook for details.

                 

                 

                Regards

                Lei

                • 5. Re: Sales/Profits of lost customers in previous years
                  Carlos Arias Fernandez

                  Ok, you are a genius. I would have never been able to do this by myself.

                  I adapted the solution to my data and it works perfectly.

                  Thank you so much !!! You are amazing

                  2 of 2 people found this helpful