6 Replies Latest reply on Aug 10, 2015 10:21 PM by rakhesh.nellikoppa

    How to Calculate Cross Shopping

    rakhesh.nellikoppa

      Hi All,

       

      I am stuck in finding Cross shopping formula in Tableau (Retail), I am not getting formula or proper definition that would help me to write formula.

       

      Simon you might be having better idea/formula to calculate this Cross shopping would be very helpful for me as I am new to Retail domain.

       

      I have created dummy data set with my best knowledge to explain the scenario, please correct if required. I have attached twbx file but haven't done any calculation

       

      Defination of “Cross Shopping: % of buyers also purchasing from other retailers among its own customers”

       

       

       

       

      Retailer Shopper

      Total customer

      Total Cross Shop Customer

      B Cross Shopper

      C Cross Shopper

      D Cross Shopper

      A

      5

      0

      0

      0

      0

      B

      5

      4

      0

      1

      3

      C

      4

      2

      1

       

      1

      D

      5

      5

      3

      2

      0

       

      At retailer level

      Cross Shopping % is

      Retailer A =  0/5 = 0

      Retailer B = 4/5  =80%

      Retailer C = 2/4 = 50%

      Retailer D = 5/5 = 100%

       

      Please help me to get the Cross Shopping if anyone have done this before or any helpful information would be much appreciated.

       

      Thank you.

       

      --Rakhesh

        • 1. Re: How to Calculate Cross Shopping
          rakhesh.nellikoppa

          Have attached the twbx

           

          Thank you.

           

          --Rakhesh

          • 2. Re: How to Calculate Cross Shopping
            pooja.gandhi

            Hey Rakhesh,

             

            This is pretty much similar to your SOW question. If you create 3 calculations, you can get it to working as expected.

             

            Calc 1: { fixed [Retailer]: countd([User id]) } - This is counting unique users who bought at a specific retailer.

            Calc 2: { fixed [User id] : IF count([Retailer]) > 1 THEN 1 else 0 END } - This willl count all the users whose retailer count is more than a 1, meaning if they bought in more than one retailer.

            Calc 3: [Calculation2]/[Calculation1] - simple a division to give you a percent of customers buying at multiple retailers.

             

            2015-08-07_1124.png

            So in the above, 5 users bought at retailer B, out of which 3 of them also bought at other retailers. So (3/5 which is 60% of retailer B's users are cross shoppers. I am not sure how you are getting a 80% in your mock up above.

             

            Capture.PNG

            Similarly for other retailers. You can move around your fields on sheet 2 to bring retailer before the user id field on rows to see how many users bought at a specific retailer. This is just an example of what could be accomplished with LOD expressions. You can also fix the purchases at a level of date if you need to find the ratio on a month level as well.

             

            Hope this helps!

            • 3. Re: How to Calculate Cross Shopping
              pooja.gandhi

              Rakhesh,

               

              Did this solution help? LOD is a pretty hot topic in the community. Can you mark the answer correct if you got the solution to your problem so that other users can benefit from this as well?

               

              Thanks,

              1 of 1 people found this helpful
              • 4. Re: How to Calculate Cross Shopping
                rakhesh.nellikoppa

                Yes Pooja, was checking with my data and no doubt its working wonderful .....

                 

                I am glad and really thankful to you ... Thank you very much.

                 

                Regards,

                Rakhesh

                • 5. Re: How to Calculate Cross Shopping
                  rakhesh.nellikoppa

                  Hi Pooja,

                   

                  Good Morning ...

                   

                  Can you help me to calculate the rivals of any retailers, example as per your solution book, Book8 – PG. Out of 4 users of “Retailer C” 2 users did cross shopping with Retailer B and Retailer D. Can we able to find the rivals of Ret A is Retailer B and D and count of my customers.

                   

                  Total Retailer of C – 4

                  Retailer C to B – 1 (userid 107)

                  Retailer C to D – 1 (Userid 112 )

                   

                  Thanks in advance ….

                   

                  -- Rakhesh

                  • 6. Re: How to Calculate Cross Shopping
                    rakhesh.nellikoppa

                    Hi ...

                     

                    If anyone have any idea about this please do share.

                     

                    Thank you.

                     

                    Regards,

                    Rakhesh