Model help - determining cross shopping

[boston99]

I have loyalty data and am trying to analyze the willingness to switch between similar products (e.g. if we have three brands of sweatshirts what % solely purchase Puma, Nike, and Adidas, what % cross shop between Puma and Nike, Puma and Adidas, and Nike and Adidas, and what % cross shop between all three).

In our data, we have household #, UPC number, and transaction number of all purchases.

This is what I've built so far, but have a couple questions:

1) Does this look right so far?

2) Is there an easy way at the end to spit out the data we are looking for (venn-diagram like) or would I need to separately calculate each?

3) If I wanted to filter the number of times a household has purchased anything at the store, how would I do this (e.g. don't want a household that has come 1000 times a year – likely the free loyalty card at the check-out).

4) Similarly, if I wanted to filter so that a household has had to make two purchases in our subgroup (e.g. two sweaters from Puma Nike or Adidas) how would I do this? We are trying to avoid the case where someone has bought only one item and appears brand loyal when they in fact are not.

[boston99]

If anyone can help, even with a portion of the asks, it would be much appreciated! 

[danrh]

I'm sure there's a good way to do this, but I'm having trouble wrapping my head around it without seeing the data.  Could you post a sample?  And if you want it in a particular format, how you want it to look on the other end?

[boston99]

Absolutely - a sample of the data how it is in the excel / csv?

[danrh]

Yep, an excel would be great.

[boston99]

Attached a dummy Excel with some dummy data and the same columns. Say it's 10016 as the Puma shirt and we want to know of people who bought 10016, 10017, or 10018, what % are completely brand loyal, what percent switch between 16 and 17 (say Nike) and 18 (Adidas), and what percent switch with all three.

As far as the output, something like total households that bought item more than 1 time, # of those that bought 17 as well, # 18 as well, # that bought all 3. Can definitely be flexible on the output though. Goal is to make a ven-diagram of sorts that shows cross shopping. I'd love to then get a view by spend (e.g. 20% of spend is with Puma, 40% with Nike, 40% with Adidas), but can play around that once the initial logic is built.

[danrh · Accepted solution]

Apologies for the delay.  You can accomplish most of this through the Summarize tool, or the Transpose/Cross Tab tools.  Here's a simple workflow that will give you what percentages of customers have purchased between which brands, along with the percentage of total spend:

Hopefully this gives you a jump start!

[boston99]

Thank you so much! 

[Yakobepenguin]

Excel would be a better one and it's easy to filter. Telltims Ca Survey