# Thread: Formula or Algorithm? find "average" shopping cart of 10 different items being sold

1. ## Formula or Algorithm? find "average" shopping cart of 10 different items being sold

Hi all!

I am trying to figure out the "average" shopping cart of 10 different items I sell. I can get info for around 100 shoppers, so I am trying to figure out how create a profile of my "typical shopper" who completes a transaction.

The shoppers each have made a purchase of some quantity of each of the 10 items I offer.

So for example, a shopper might have purchased 3 t-shirts, 2 books, and 5 pins, and none of the other 7 items.

I know I can look at my records and see the average number of each item that was purchased across all 100 shoppers and see what inventory was sold. But that doesn't tell me what the average shopping cart looked like. Right?

So for example, let's say I sold 50 books, 20 "combo sets", and 30 tote bags from the 100 shoppers. It doesn't make sense to me to take the individual average of each item, because people don't usually buy these particular items together (2 books and a totebag are included in the combo.)

Also, it doesn't make sense to me to say that out of 100 shoppers, my average shopper buys .5 books, .2 combo sets, and .3 tote bags. That's not a real purchase...

I'm trying to find out what combination of items and what quantity the shopper has in the cart when they make their purchase so I can say something like, "my typical shopper who makes a purchase is going to have 3 t-shirts and 2 pins in their cart."

Is there a way for me to calculate this with a formula on my spreadsheet or do I have to use some kind of algorithm?

Thanks!

2. You must overcome your attachment to integers. It's okay if the average number of books is 38.7 instead of 37 or 38. All that matters is the relationship. 38.7 is much greater than 5.2, for example. If you can't give that up, you may wish to switch to the MEDIAN, rather than the MEAN or Average.

3. ## Thanks! However...

Thanks for the reply! My issue is really less about the integer and more about the combination of items because I am trying to get to a "real world" answer. I was trying to use the decimal to illustrate that but I might not have been clear enough in my example.

Imagine if I was selling sunglasses and umbrellas. These purchases would probably be highly uncorrelated (it is usually either sunny or raining, not both).

So even if the "average" or "median" purchase was something I measured at the product level and my answer was .6 sunglasses and .5 umbrellas, I could round up or down and call it 1 pair of sunglasses and 1 umbrellas per shopper.

But reality is that this combination doesn't make sense as a combination for the average shopper.

How do I get away from just calculating the average item to calculating the average mix of items in the average shopper's shopping cart?

My data of purchases per shopper is basically something like this, where there are 100 shoppers and 10 items A, B, C, D, E, F, G H, I, and J:

Shopper 1: 2A, 3B
Shopper 2: 4B, 6F
Shopper 3: 1A, 2B, 3C, 4H
Shopper 4: 4B, 5F
Shopper 5: 2A, 4B, 3G
Shopper 6: 3A, 3D, 1F

and so on...

Thanks!

4. Figuring out answers to a real world problem frequently requires figuring out good questions to ask. Your "typical" customer may not look like any customer. When you deal with an "average," you are discarding information to get a "simple" answer. The problem is that the answer may be so simple as to be simple minded.

You might have groups of customers whose patterns are very different. If half your customers spend between 40 and 60 dollars each month, averaging 50 dollars, and another half spend between 12 and 20 dollars Each month, averaging 16 dollars, treating those two groups as a single group of customers and averaging will tell you that your average customer spent 33 dollars each month when no customer at all spent that amount. In other words, a single number is simple to grasp, but it may not reflect a complex reality. In fact, that simple answer may grossly distort that reality.

You seem to have a lot of data. You can ask various questions using a data base package and get more tailored answers to very specific questions. That may be more useful than any kind of average. In any case, I'd start by asking questions that would permit categorization of customers. Segment customers by frequency of purchase. Segment them by dollar value of purchase. How do the two segmentations relate. For example, if many customers who buy frequently per month also spend large amounts per purchase, I know which customers I'd immediately study in more detail.

5. Your seasonal example suggests that you cannot do this on an annual basis.

You can't get a decimal with a Median.

6. ## Aha - oversimplifying :)

Thanks Tkhunny and JeffM!

JeffM - Your response really resonated with me for the problem I am trying to solve!

I will think in reverse and start with a real world sample customer shopping cart that I observe in my data, which I believe might be a very typical combo of shopping cart items.

Then I will see how closely "fit" other carts are to the sample customer cart, instead of starting with an average.

And I will see if I can find a database program so I can quickly query the count of carts that have these similar "fit" characteristics.