In: Operations Management
How many customers purchased more than 1 product? (Hint: Customer ID stays the same per customer when he/she buys several food boxes)
Please Tell me the steps on how this would be solved using Excel. Note that this is not the full excel data but just a small sample.
date_sign_up | customer_id | product_name | marketing_channel | City |
4/17/2015 13:11 | 71041 | Classic - 3 meals per week for 2 people | Search Engine Marketing | San Diego |
4/14/2015 11:32 | 103289 | Classic - 3 meals per week for 2 people | Newsletters (internal) | Los Angeles |
4/14/2015 9:53 | 107746 | Classic - 3 meals per week for 2 people | Newsletters (internal) | Los Angeles |
4/14/2015 16:08 | 157441 | Classic - 3 meals per week for 4 people | Newsletters (internal) | San Francisco |
4/18/2015 8:04 | 158646 | Classic - 3 meals per week for 2 people | Newsletters (internal) | San Diego |
4/18/2015 15:41 | 178843 | 3 Meals (vegetarian) for 2 people | TV / Radio Advertising | Miami |
4/15/2015 9:45 | 179297 | Classic - 3 meals per week for 2 people | Newsletters (internal) | Philadelphia |
4/15/2015 8:21 | 182439 | 3 Meals (vegetarian) for 2 people | Newsletters (internal) | San Francisco |
4/17/2015 6:57 | 186180 | Classic - 3 meals per week for 2 people | Partnership Marketing | San Diego |
4/19/2015 20:39 | 190396 | Classic - 3 meals per week for 2 people | TV / Radio Advertising | Miami |
4/14/2015 10:26 | 194229 | Classic - 3 meals per week for 2 people | Search Engine Marketing | Los Angeles |
4/13/2015 20:17 | 194353 | Classic - 3 meals per week for 2 people | Search Engine Marketing | Washington |
4/15/2015 14:43 | 200286 | Classic - 3 meals per week for 2 people | Search Engine Marketing | Philadelphia |
4/16/2015 7:35 | 201307 | Classic - 3 meals per week for 2 people | TV / Radio Advertising | 1San Diego |
4/15/2015 6:54 | 203319 | Classic - 3 meals per week for 2 people | Newsletters (internal) | San Francisco |
4/14/2015 8:35 | 205233 | Classic - 3 meals per week for 2 people | TV / Radio Advertising | Los Angeles |
4/13/2015 12:08 | 205322 | Classic - 3 meals per week for 2 people | Newsletters (internal) | Chicago |
4/16/2015 5:04 | 205611 | Classic - 3 meals per week for 2 people | TV / Radio Advertising | San Diego |
4/15/2015 12:17 | 208066 | 3 Meals (vegetarian) for 4 people | Newsletters (internal) | Philadelphia |
Answer: Hi following is the step to know that
select the table and insert a pivot table:
Place Customer ID in row and product name in column, then place the product name in value field and set it to the count
Now select the table and paste it in another sheet as a table only (not the pivot table)
Then remove the Grand total column and create a count of product column which will have the count function to count the number of entries under various product column against each of the customer Id.
This will tell us how many differnet type of product each customer has ordered in the data set.