5 Replies Latest reply: Jun 19, 2017 5:32 AM by Alan Huddart RSS

    Pareto Analysis

    Alan Huddart

      I want to be able to show a Pareto-style analysis of our customers, but at a high-level rather than having to show every customer as we would have to using Henric's solution here: Recipe for a Pareto Analysis – Revisited

       

      I would simply like a metric showing what % of our revenue comes from the Top X% of our customers (say 20%, for example). The only solution I can think of is to rank each customer in the data load, however we want this metric to respond to user selections so don't want to pre-calculate anything.

       

      Any suggestions?

       

      Alan

        • Re: Pareto Analysis
          vikas mahajan

          you can use input box to take % from user store it into variable and use this variable in Top x%

           

          Vikas

          • Re: Pareto Analysis
            Sunny Talwar

            Would you be able to share some data with the output you are expecting out of it? Also, I hope you are working with QV12.1 or above?

              • Re: Pareto Analysis
                Alan Huddart

                This is a (very) simplified version of the dataset:

                 

                StoreCustomer IDOrder IDRevenue
                London1ABC1£190
                London2ABC2£87
                London3ABC3£163
                London1ABC4£93
                London2ABC5£91
                London1ABC6£58
                London3ABC7£99
                London4ABC8£218
                London5ABC9£195
                London6ABC10£176
                London4ABC11£76
                London7ABC12£204
                London8ABC13£56
                London9ABC14£64
                New York10ABC15£173
                New York10ABC16£117
                New York8ABC17£90
                New York10ABC18£176
                New York8ABC19£58
                New York9ABC20£132
                New York10ABC21£87
                New York8ABC22£140
                New York9ABC23£193
                New York10ABC24£157

                 

                The steps to go through are basically rank the customers in order of their total revenue, then calculate the contribution each on makes to the overall revenue:

                 

                Customer IDTotal Revenue% ContributionCumulative Contribution
                10£71023.0%23.0%
                9£38912.6%35.5%
                8£34411.1%46.7%
                1£34111.0%57.7%
                4£2949.5%67.2%
                3£2628.5%75.7%
                7£2046.6%82.3%
                5£1956.3%88.6%
                2£1785.8%94.3%
                6£1765.7%100.0%
                Grand Total£3,093

                 

                From there, I can now say the top 20% of customers (in this case just customers 9 and 10) contribute 35.5% of our revenue.

                 

                But, I also want this to react to user selections, so if we filter to just London orders, the data would look like this:

                 

                Customer IDTotal Revenue% ContributionCumulative Contribution
                1£34119.3%19.3%
                4£29416.6%35.9%
                3£26214.8%50.7%
                7£20411.5%62.2%
                5£19511.0%73.2%
                2£17810.1%83.3%
                6£1769.9%93.2%
                9£643.6%96.8%
                8£563.2%100.0%
                Grand Total£1,770

                 

                And, as we have over 300,000 customers, I don't want to show as a table/chart with a record for individual customers, but just a high level metric to say the top 20% of customers are worth Y% of total revenue.

                 

                Hope that helps!

                 

                Alan