1 2 Previous Next 25 Replies Latest reply on Mar 2, 2016 11:49 AM by Santanu Mukherjee

    Number of customers for paired products

    Santanu Mukherjee

      Here is my sample data from the analytics table

      StartFragment

      customer id

      product

      12

      Bread

      12

      Pizza

      12

      Pretzel

      34

      Pizza

      34

      Smoothie

      45

      Bread

      45

      Pizza

      45

      Smoothie

      45

      Candy

      67

      Candy

      89

      Bread

      89

      Pizza

      89

      Pretzel

      89

      Smoothie

      89

      Candy

      89

      Energy Drink

      89

      Soda

      EndFragment

       

       

       

      We have got the customer ids in the database and offer names / offr desc.

      I use SQL and some keywords from the string of names to identify whether the product is bread, pizza etc..

      Now the problem is one customer can have multiple products – we do not know how many products we have , although we know from lets say Jan – Dec 2015, how many customers we had and how many transactions. From running a SQL I can get a result set which says customer 123 bought Pizza, etc….( as I had send you a sample)

       

      But what I want is

      Total customers who bought (bread and Pizza) – in this example it will be 3 (customer id 12,45 and 89)

      Total customers who bought pizza and pretzel -  in this example it will be 2 (customer id 12 and 89)

      Total customers who bought pizza and smoothie – in this example it will be 3 (customer id 34,45 and 89)

      And so on….

      So total number of customers for each pair.

        1 2 Previous Next