1 Reply Latest reply on Jun 20, 2018 1:21 PM by Jim Dehner

    Calculating Purchase Share in a Market of a Customer

    Ross Helenius

      I am sure I am overlooking something here, but here goes. I am looking to calculate how much of a customer's purchasing a given retailer owns in the market. A simplified version of this can be seen in the attached workbook.

       

      So in the attached example if we put the retailer Fris in context they have had 3 customers Sarah, Jim and Dave. Sarah has made 2 purchases in the overall Market if we assume the other retailer data is our entire market. So I want to know that Sarah made one purchase at Fris and and one at Big Buy, essentially controlling 50 percent of that customers market share (1/2 total purchases). See the No Category sheet and this looks good. So far so good. Now if I add in the Category to figure out which segment the purchases are made, I am losing the categories in which the store selected by the parameter does not have a corresponding entry which may be found in the market, ie Sarah only has appliance purchases at Fris so I lose the electronics purchase at Big Buy.See the With Category sheet. I just added category to the fixed calc and the dimensions on the row. We pick up all of Dave's Electronics purchases in contrast because he made an electronics purchase at Fris. In the end I would like to aggregate for the set of customers that made a purchase at a retailer what is the number of purchases they made at that retailer vs other retailers in the market and compute share (essentially loyalty), broken down by category.

       

      Hopefully that isn't too confusing to follow. Ideally I would like to use only the existing dataset without duplication, blends etc as this is a much smaller representative set than the total.

       

      Any insight much appreciated.

       

      Thanks!

       

      Sample 2018.1 packaged workbook attached.