1 2 Previous Next 19 Replies Latest reply on Sep 5, 2017 6:15 AM by Leanne Vermeulen

    Customers that have NOT ordered a product?

    Bryan Heenan

      Hello everyone !!!

       

      It's my first time here so please go easy on me.

       

      I'm trying to work out which customers have NOT purchased a certain product. We could look at the Superstore dataset to imagine this scenario.

       

      Here we have multiple orders, each of which contains one or more products.

       

      If we look across all the orders we can see for instance that the 'Logitech 910-002974 M325 Wireless Mouse for Web Scrolling' is a popular product and appears on 15 orders.

       

      What I essentially want to know is what customers who have placed orders have not ordered this product.

       

      I'm sure there's an easy way to do this - but can't seem to work it out. For extra thanks and appreciation the next step would be to allow my user to dynamically select which product they want to include on the NOT ordered by customers master list. Also if they could select a date range which would list the customers who haven't ordered the product within a specific range.

       

      Thanks in advance

        • 1. Re: Customers that have NOT ordered a product?
          Ken Patton

          Let's make sure we understand your specifications. Are you saying:

           

          For any arbitrary Product in the data set, within a selected date range of Order Date  -- return a "list" of specific Customers that have ordered something but have not ordered the specific Product selected?

           

          In other words, you want to exclude any customers that have ordered nothing whatsoever, and exclude customers that have ordered the selected product among other things?

           

          Would that be correct ?

          • 2. Re: Customers that have NOT ordered a product?
            Bryan Heenan

            Thanks for the response.

             

            You seem to have understood the requirement but the sample provided doesn't produce the expected results.

             

            Take for example the product 'Avery Binder Labels' in your sample. When this item is selected in your filter the list contains 16 Customers.

             

            Looking at your dataset we see the following:

            • There are 2858 distinct customers
            • Across the entire date range 6 distinct customers ordered 'Avery Binder Labels'
            • This leaves us with 2852 distinct customers that did NOT order 'Avery Binder Labels'


            It is the 2852 customers that I am trying to identify.


            Note: The sample set is probably not a real world example for me. I work in the B2B space were the vast majority of customers order the top products. I am trying to identify those customers who didn't order these products.

             

            To help clarify further, it the data were stored in database the following query would give me the results I need assuming I know the filters are : 'Avery Binder Labels' and '01/07/2010' AND '30/07/2010'

             

            SELECT DISTINCT [Customer Name]

              FROM superstore A

              WHERE NOT EXISTS(

                                SELECT [Customer Name]

                                  FROM superstore B

                                  WHERE B.[Customer Name] = A.[Customer Name]

                                    AND B.Item = 'Avery Binder Labels'

                                    AND B.[Order Date] BETWEEN '01/07/2010' AND '30/07/2010'

                              );

             

            Thanks again for your response, if you can provide more feedback i'd be stoked.

             

            In a nutshell. Using filters as you provided I want to work out - Of the customers that placed orders in this selected time frame who did not include the selected product on any of their orders.

             

            Cheers

            • 3. Re: Customers that have NOT ordered a product?
              Ken Patton

              Right. I had already deleted my post with the sample workbook because I knew it didn't do what you wanted.

              • 4. Re: Customers that have NOT ordered a product?
                Bryan Heenan

                Sorry Mate, I was so excited about the reply that I went and responded without realising you had removed it.

                 

                Awesome feedback though. Appreciated.

                • 5. Re: Customers that have NOT ordered a product?
                  kettan

                  Without reading all details of this question I can say that data blending is a way to imitate SQL EXISTS.

                   

                  See example of this in  Re: how to filter one item and view all matches.

                  • 6. Re: Customers that have NOT ordered a product?
                    Simon Runc

                    hi Bryan,

                     

                    It depends how your final viz will work, but here's a technique (...you can find a better explanation that mine if you watch the excellent Think Data Thursday's Let's Talk About Sets webinar...definitely an hour well spent!) The specified item was not found.

                     

                    The basic trick is to use a parameter and a set.

                     

                    The Parameter is set up to have all the product names in (now this means it won;t update dynamically...although this feature might be on the way, and you can only select product by product)

                     

                    I've then created a set, on the customer name, with the condition

                     

                    MAX( IIF([Product Name] = [Didn't Puchase?], 1, 0 ) ) = 0

                     

                    If puts a 1 next to all rows where Product Name = Selected Product Name from the Parameter, and then takes the Max. If this equals zero then they didn't purchase.

                     

                    I've then used this as a filter, so by changing the selected product in the parameter the list of non-buying customers updates

                     

                    Hope this helps (this is a very handy trick!)

                    1 of 1 people found this helpful
                    • 7. Re: Customers that have NOT ordered a product?
                      Bryan Heenan

                      Simon - i've not yet decided whether this is Genius or just Devillishly Brilliant

                       

                      Either way it works a treat !!! Tis indeed a very handy trick.

                       

                      Thanks for this and thanks to the others for their responses also.

                      • 8. Re: Customers that have NOT ordered a product?
                        Simon Runc

                        hi Bryan,

                         

                        ...let's say it's a bit of both!! I wish I could take credit for thinking of it, but as you'll see when/if you watch the Let's Talk about Set's TDT Bethany covers a very similar example (in fact 80% of the presentation are different use cases of the same basic set conditional formula).

                         

                        It's a very handy technique which can be used in so many different ways. The very handy thing with sets is also that they can be combined. So in one of the examples in the TDT, one set was created on the condition that a customer spent over X amount (set by a parameter), and another set for customers who hadn't bought anything from a given category. The intersection of these 2 sets was created (using combine sets and selecting the intersection), and this set (in) was put on the filter. The user could then see all customers who had spend more than X, but hadn't bought anything from Category Y. As you can imagine this is very handy for Customer/Store segmentation/clustering.

                        • 9. Re: Customers that have NOT ordered a product?
                          Bryan Heenan

                          Cheers Simon, I will most certainly check out the Sets TDT. What you have mentioned is exactly the type of information I will be after.

                          • 10. Re: Customers that have NOT ordered a product?
                            Leanne Vermeulen

                            Simon is there a way to do this without having to select a specific product in the parameter? I've created a similar post here and someone posted the same solution as yours: Show Attributes Of A Field That Are NOT Associated With A Customer  What we're trying to figure out is how to show all of the products a customer did not purchase, not just a single product as specified by a parameter?

                             

                            We tried adding the "All" value but I've found that just places all customers inside the "In" set.

                            • 11. Re: Customers that have NOT ordered a product?
                              Simon Runc

                              hi Leanne,

                               

                              Although the data source is Excel, we can still use the technique that Ray gave here Re: Show Attributes Of A Field That Are NOT Associated With A Customer

                               

                              We can use Custom SQL to generate the List of All Products, and then again to get a list of all customers...and then cross-join these (as per Ray's answer) and then Left Join the original data back on. This will give us a row for every possible Product/Customer combination, where only the rows with sales against them will have a sales value. As you can probably already see, once we get to that the Tableau is very easy.

                               

                              Is this is an approach that's feasible? if so let me know and I can work up an example from superstore.

                              • 12. Re: Customers that have NOT ordered a product?
                                Leanne Vermeulen

                                I didn't even know Custom SQL for Excel from Tableau was an option!!! Feeling a little behind but also really relieved; an example would be great, thanks!

                                • 13. Re: Customers that have NOT ordered a product?
                                  Simon Runc

                                  hi Leanne,

                                   

                                  No shame in that at all...prior to Tableau 9 whenever we connected to Excel we got the customSQL option, but since T 9.0 it's been hidden away! When you connect to Excel, you need to use the Legacy Connection (hidden away in the open dialogue box)

                                   

                                   

                                  So once we have this we can create a CustomSQL query that will create a row for every possible Customer/Product combination...Hopefully you have less products than superstore! as this generated 1.5M rows!! (not that big in Tableau TDE terms, but from a dataset of 9k rows, once rolled up to Customer/Product level that's quite an expansion!)

                                   

                                  Here's the Custom SQL for that....

                                  SELECT [c].[Customer Name] AS [Customer Name],

                                    [p].[Product Name] AS [Product Name]

                                  FROM (

                                    SELECT [Orders$].[Product Name] AS [Product Name],

                                    1 AS [Dummy]

                                    FROM [Orders$]

                                    GROUP BY [Orders$].[Product Name]

                                  ) [p]

                                    INNER JOIN (

                                    SELECT [Orders$].[Customer Name] AS [Customer Name],

                                    1 AS [Dummy]

                                    FROM [Orders$]

                                    GROUP BY [Orders$].[Customer Name]

                                  ) [c] ON [p].[Dummy] = [c].[Dummy]

                                  GROUP BY [c].[Customer Name],

                                    [p].[Product Name]

                                   

                                  So once we have this, I can then do another bit of custom (which you might not need) to get an aggregated table of all customer/product purchases (i.e. taking the Transaction level out)

                                  SELECT [Orders$].[Customer Name] AS [Customer Name],

                                  [Orders$].[Product Name] AS [Product Name],

                                  SUM([Orders$].[Sales]) AS [Sales]

                                  FROM [Orders$]

                                  GROUP BY [Orders$].[Customer Name],

                                  [Orders$].[Product Name]

                                   

                                  We can then do a LEFT JOIN (where we can just use the Tableau Join UI)

                                   

                                  I also removed (hide) the 2nd set of Customer Name, Product Name. btw you will (most likely) need to use an Extract as 1.5 Million rows live against Excel isn't fast!!

                                   

                                  Once we've done this the flag for bought/not bought is really simple (I've added the NOT so if someone has bought it the flag goes to true!)

                                   

                                  [Customer Bought/Not Bought Flag]

                                  NOT(ISNULL([Sales]))

                                   

                                  Hope that helps

                                  • 14. Re: Customers that have NOT ordered a product?
                                    Leanne Vermeulen

                                    Thanks for all this Simon - apparently I can't do this on a Mac. Maybe I can find a way to do this inside Excel using Macros. Wouldn't happen to know of any alternatives, would you?

                                    1 2 Previous Next