2 Replies Latest reply on May 6, 2020 6:43 AM by Chris Maertz

    Customer Quintiles

    Chris Maertz

      Hi all,

       

      I am trying to put together a customer quintile analysis that is dynamic with the filters. I believe i have gotten 90% of the way there (see attached superstore example), however, when i breakdown the data by region, it changes when i filter out certain regions (ie. if i just filter to central, the quintiles are one data set, but when i add in west, central's sales #'s change.)

       

      The goal is to see the quintiles by regions, while also being able to remove regions and see overall quintiles for the entire company.

       

      Appreciate any help!!

      Chris

        • 1. Re: Customer Quintiles
          Fearghal Gunning

          Hey Chris,

          What you're seeing here is more an issue with how Superstore works than it is with your calcs.  In Superstore, a customer isn't actually tied to a region, a purchase is tied to a region.  Therefore when you're looking at customers and how they compare to other customers in their region, you need to decide how you want to count a customer's sales.  If a customer bought $10k in Central, but then bought $5k in West, do we count them as a $15k customer for both regions?  Or do we count them as independent customers for each one?  In most systems, [Region] will be an attribute of [Customer], so this won't be an issue.  That said, Tableau does have an easy fix.

           

          Assuming you want to treat your customers as "the combination of customer and region" (to avoid counting a customer as top 20% in Central even if all he's bought there is one paperclip), simply add Region into your LODs.  In your Customer Worth calc, fix the sales to Customer Name and Region level.

          {FIXED [Customer Name], [Region]: Sum([Sales])}

          For customer quintiles, fix the PERCENTILE calc at the Region level.

          IF [05.04Customer Worth]<={FIXED [Region]:PERCENTILE([05.04Customer Worth],.2)} THEN "Bottom 20%" .........

           

          I've attached a functioning example here, but let me know if there are any questions!  If you're curious about exactly why LODs work this way and want to see some of the SQL behind them, take a look here.

          https://gunningfortableau.com/2018/12/17/devil-in-the-details-understanding-lod-equations-and-filtering/

          2 of 2 people found this helpful
          • 2. Re: Customer Quintiles
            Chris Maertz

            Thank you Fearghal! This is exactly what i was looking for. Appreciate your help!