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

# Number of customers for paired products

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. Re: Number of customers for paired products

If you are Ok with parameter solution, here is my approach.

Put two parameters added from "Product".

Then create calculated field by LOD Overview: Level of Detail Expressions

[Param Combination]  // using multiply brings the result of "and" conditions

{fixed [Customer Id]:sum(if [Product]=[Parameter 1] then 1 else 0 end)}

*

{fixed [Customer Id]:sum(if [Product]=[Parameter 2] then 1 else 0 end)}

Put this field in filter and filter out "0"

I tired to show whole combination as table, but not succeeded that yet for now.

Thanks,

Shin

9.0 attached

1 of 1 people found this helpful
• ###### 2. Re: Number of customers for paired products

Created all the formula, not sure this approach works for your case or not.

re-attached 9.0

• ###### 3. Re: Number of customers for paired products

Hi ,

This is the table I want but the way it has been done is a problem:

1.  1. Although I will have a list of customers (may be 1000s of rows with product names) and the products they bought, but I do not know that there are 7 products or 10 products or 50 products , so creating measures of each is difficult.

2.  2. So, I need a generic approach as to how I can group the products.

3.

Thanks,

Santanu Mukherjee | Manager-Data Analytics

Koupon Media

C. 410.790.4104

@KouponMedia

1. Relentless Execution

• ###### 4. Re: Number of customers for paired products

FYI – continuing from our precious conversation

Thanks,

Santanu Mukherjee | Manager-Data Analytics

Koupon Media

C. 410.790.4104

@KouponMedia

1. Relentless Execution

• ###### 5. Re: Number of customers for paired products

Hi Ann,

I need this in a different way though. How do I Get there?

customer ID     Product

34                              Pizza           Smoothie

67              Candy

89      Bread   Candy   Energy Drink    Pizza   Pretzel Smoothie        Soda

Thanks,

Santanu Mukherjee | Manager-Data Analytics

Koupon Media

C. 410.790.4104

@KouponMedia

1. Relentless Execution

• ###### 6. Re: Number of customers for paired products

Hey!

1 of 1 people found this helpful
• ###### 7. Re: Number of customers for paired products

Hi Pooja,

The problem with that is that when I export that to excel it takes only the first column values.

I want the products to be separate columns which I can export to excel and do my analysis.

Thanks,

Santanu Mukherjee | Manager-Data Analytics

Koupon Media

C. 410.790.4104

@KouponMedia

1. Relentless Execution

• ###### 8. Re: Number of customers for paired products

Well then have product placed on columns as well. Then do Worksheet > copy ? crosstab or worksheet > export > cross tab to excel. Columns would be separated upon export. This is what it looks like in excel post export:

1 of 1 people found this helpful
• ###### 9. Re: Number of customers for paired products

Hi Pooja,

I am not seeing the same thing in Tableau. This is what I see

Thanks,

Santanu Mukherjee | Manager-Data Analytics

Koupon Media

C. 410.790.4104

@KouponMedia

1. Relentless Execution

• ###### 10. Re: Number of customers for paired products

Place product on 'text' also on the marks card.

1 of 1 people found this helpful
• ###### 11. Re: Number of customers for paired products

Thanks,

But is there a way that I can have the data in this format in Tableau

customer ID     Product

34      Pizza   Smoothie

67      Candy

89      Bread   Candy   Energy Drink    Pizza   Pretzel Smoothie        Soda

Thanks,

Santanu Mukherjee | Manager-Data Analytics

Koupon Media

C. 410.790.4104

@KouponMedia

1. Relentless Execution

• ###### 12. Re: Number of customers for paired products

Hi Pooja / Ann,

Just making sure you guys got this email. This is what I want

customer ID     Product

34      Pizza   Smoothie

67      Candy

89      Bread   Candy   Energy Drink    Pizza   Pretzel Smoothie        Soda

Thanks,

Santanu Mukherjee | Manager-Data Analytics

Koupon Media

C. 410.790.4104

@KouponMedia

1. Relentless Execution

• ###### 13. Re: Number of customers for paired products

Santanu!

I already told you what you can do to get there. Customer ID on rows and product on columns and text on marks card. What is the issue with that solution? Are you looking for something else? I am not sure I understand if thats the case.

Pooja.

1 of 1 people found this helpful
• ###### 14. Re: Number of customers for paired products

Pooja,

This is what I want – understand that I want for each customer only those columns that has products. Look very carefully and you will see the difference.

customer ID     Product

34      Pizza   Smoothie

67      Candy

89      Bread   Candy   Energy Drink    Pizza   Pretzel Smoothie        Soda

What you have proposed gives me this below.

Product

customer ID     Bread   Candy   Energy Drink    Pizza   Pretzel Smoothie        Soda

34                              Pizza           Smoothie

67              Candy

89      Bread   Candy   Energy Drink    Pizza   Pretzel Smoothie        Soda

Thanks,

Santanu Mukherjee | Manager-Data Analytics

Koupon Media

C. 410.790.4104

@KouponMedia

1. Relentless Execution

1 2 Previous Next