4 Replies Latest reply on Oct 18, 2018 10:15 AM by Roger Pouly

    Identify customers that belongs in several groups

    Roger Pouly

      Hello everyone,

       

      I want to be able to identfy customers that :

       

      Exclusively ordered in the summer.

      Exclusively ordered in the winter.

      Ordered both in summer and winter (in at least two different orders out of their XX orders).

       

      As well as being able to identify customers that :

       

      Exclusively ordered from the channel "WEB"

      Exclusively ordered from the channel "STORE"

      Ordered both in the channel "WEB" and "STORE"

       

      I am having trouble creating a calculating field that is able to get those informations.

       

      The field I have : [Order_number], [Client_number], [Order_channel], [Date_Order] and [Summer_or_Winter] (based on the date).

       

      I tried to do something like : {Fixed [Client_number] : IF Order_channel = "WEB" AND Order_channel = "STORE" THEN 'Client Store and Web' END} but it obviously doesn't work.

       

      Thanks a lot,

       

      Roger

        • 1. Re: Identify customers that belongs in several groups
          Zach Leber

          You can build up and debug your LoD expressions as follows:

           

          1. Client Orders from Store

          { FIXED [Client Number]: ZN(SUM(IF [Order Channel]="STORE" THEN 1 END)) > 0 }

          2. Client Orders from Web
          { FIXED [Client Number]: ZN(SUM(IF [Order Channel]="WEB" THEN 1 END)) > 0 }

          3. Client Type
          IF [Client Orders from Store] AND [Client Orders from Web] THEN "Both"
          ELSEIF [Client Orders from Store] THEN "Store"
          ELSE "Web"
          END

          client channels.png

          Repeat/combine for summer/winter analysis.

          10.4 TWBX attached.

          1 of 1 people found this helpful
          • 2. Re: Identify customers that belongs in several groups
            Roger Pouly

            Thanks a bunch Zach Leber, that's actually a great solution.

             

            Would you know if there is a way if I want to check specifically if the first order from a customer is from web, and the second (or third, or fourth) from store ? Meaning that I would know how many customers have been "converted" into using the store after discovering us via the web (or vice-versa).

             

            Roger

             

            EDIT : Thinking about it, I think I got it. I do not have access to Tableau until tomorrow so I cannot test right now, but something like :

             

            1. First date Client Orders from Store

            { FIXED [Client Number]: IF [Order Channel]="STORE" THEN min([Date_Order]   END}

            2. First date Client Orders from Web
            { FIXED [Client Number]: IF [Order Channel]="WEB" THEN min([Date_Order] END}

            3. Web to Store conversion
            IF [Client Orders from Store] AND [Client Orders from Web] THEN

            IF [First date Client Orders from Store] > [First date Client Orders from Web]
            THEN 1 END END

             

            • 3. Re: Identify customers that belongs in several groups
              Zach Leber

              Once you get the hang of LoD expressions you can start to ask and answer lots of these questions. Your logic is correct but your syntax needs to be tweaked. Remember that a FIXED LoD expression looks at all the records for the fixed dimension so needs to apply an aggregation.

              1. First Store Order

              { FIXED [Client Number]:
              MIN(IF [Order Channel]="STORE" THEN [Order Number] END) }

              2. First Web Order

              { FIXED [Client Number]:
              MIN(IF [Order Channel]="WEB" THEN [Order Number] END) }

              3. Conversion

              // for clients with both types of orders, check what the first order type was

              IF [Client Type]="Both" THEN
                IF [First Web Order]<[First Store Order]
                THEN "WEB > STORE"
                ELSE "STORE > WEB"
                END
              END

              Updated TWBX attached (version 10.4 for real this time).

              1 of 1 people found this helpful
              • 4. Re: Identify customers that belongs in several groups
                Roger Pouly

                Thanks a lot for your advice, and for the calculations. I still have a lot to learn and that was super helpful.

                 

                The main thing I need to stop doing is that every time I try to do a calculation, to try to do it in a single calculated field instead of doing it step by step in multiple calculated fields, the right way.

                 

                Anyway, thank you again for the calculations and the workbook.

                 

                Roger