4 Replies Latest reply on May 6, 2016 3:37 PM by Jeff Huckaby

    Tagging Customers by Purchase behavior (digital, brick, multichannel)

    Claudine Bonnet

      Hi,

      I'm new to Tableau and I guess this is a classic one: I have a dataset with sales transactions by points of sales, each belonging to a channel (have 5-6 channels of which digital), and a variety of other dimensions (region, product, etc...).

      I'd like to cluster my customers dynamically, based on their transactions to date, as:

      - digitalOnly: those customers that have bought through the digital channel exclusively

      - nonDigital: those customers that have bought through any channel other than digital

      - multiChannel: Customers that have bought through digital and at least another channel

       

      By analogy if we were to use the superstore dataset, the question could be to cluster the Customers based on their state (California being analogous to digital in my own dataset):

      - CaliforniaOnly: customers that have sales records exclusively in California

      - nonCalifornia;: customers that have purchased in any state other than California

      - CaliforniaAndOther: customers that have purchased in California and at least another state

       

       

      Help appreciated on this question,

      Best,

      Claudine

        • 1. Re: Tagging Customers by Purchase behavior (digital, brick, multichannel)
          Charlie Archer

          Hi Claudine,

           

          Depending on how you're intending on using this cluster field, there are a couple of ways to do this. If you have your customers in the view (you have the dimension present on rows / columns / marks) then you can write an IF statement that looks something like this:

           

          IF COUNTD([State]) = 1 AND SUM(IF [State] = "California" THEN 1 ELSE 0 END) > 0 THEN "California Only"

          ELSEIF COUNTD([State]) > 1 AND SUM(IF [State] = "California" THEN 1 ELSE 0 END) > 0 THEN "California and Other"

          ELSEIF COUNTD([State]) > 1 AND SUM(IF [State] = "California" THEN 1 ELSE 0 END) = 0 THEN "Non California"

          ELSE "Non California"

          END

           

          However, if you're looking for a single dimension that has already pre-grouped all these customers into fixed groups i have used the following LOD calculation to create the clusters:

           

          IF {FIXED [Customer Name]: COUNTD([State])}  = 1 AND {FIXED [Customer Name]:SUM(IF [State] = "California" THEN 1 ELSE 0 END)} > 0 THEN "California Only"

          ELSEIF {FIXED [Customer Name]: COUNTD([State])}  > 1 AND {FIXED [Customer Name]:SUM(IF [State] = "California" THEN 1 ELSE 0 END)} > 0 THEN "California and Other"

          ELSEIF {FIXED [Customer Name]: COUNTD([State])}  > 1 AND {FIXED [Customer Name]:SUM(IF [State] = "California" THEN 1 ELSE 0 END)} = 0 THEN "Non California"

          ELSEIF {FIXED [Customer Name]: COUNTD([State])}  = 1 AND {FIXED [Customer Name]:SUM(IF [State] = "California" THEN 1 ELSE 0 END)} = 0 THEN "Non California"

          END

           

          This looks fairly messy and there may be a more efficient way of doing this that someone may be able to contribute - but this is how i approached this.

           

          If you want a hand understanding what these formulas mean / do then happy to run you through.

           

          Thanks,
          Charlie

          • 2. Re: Tagging Customers by Purchase behavior (digital, brick, multichannel)
            Claudine Bonnet

            Hi Charlie,

            I'm in the 2nd case (LOD) and implemented your suggestion on my dataset, it totally did the trick brilliant before messy if you ask me!

             

            Many thanks,

            Best

            Claudine

            1 of 1 people found this helpful
            • 3. Re: Tagging Customers by Purchase behavior (digital, brick, multichannel)
              Jeremy Poole

              Saw this shared over on the retail forum. Agree with Charlie Archer's solution. My first thought was to create the LOD's individually as 'flags', but his solution is definitely cleaner by combining the 2 into a single 'if' statement.

              Claudine Bonnet Thanks for sharing with our retail group.

              • 4. Re: Tagging Customers by Purchase behavior (digital, brick, multichannel)
                Jeff Huckaby

                Would be interesting for you to use the new clustering functionality in v10 Beta.  The clustering did not make the beta 1, but will be updated soon.