Grouping top 20% of customers by store

[BWitonsky]

Hello,

I am analyzing 1MM customers each tied to 1 of about 200 stores.  How can I set up a workflow to identify the top 20% in terms of revenue?  The business questions is who are the top 20% of customers who bring in about 80% of the revenue.

My data is straightforward; store ID, Cuistomer ID, customer revenue etc.

Thanks!

[s_pichaipillai]

i would suggest to use the below steps

1. Sort your revenue filed using SORT tool in Descending Order

2.Assign the Rank to your customers based on the Revenue. i think instead of RecordID use Multi Row Formula tool to create the rank . so that you wont miss the customers who has same Revenue

3.use the filter tool to get only top 20 Rank Customer using the Rank filed

does it help?

-->Saravanan

[jdunkerley79]

I would suggest you first use summarise tool to create CustomerID, SUM(Revenue)

You can then use a sort tool to order by SUM of Revenue descending.

Then finally use a sample tool to select just the first 20% records

[BWitonsky]

Thanks!  I'll check these out and let you know what works.

[MarqueeCrew · Accepted solution]

If you are looking for the customers that are in the top 20% of spend within each store then I'd likely approach this similarly to @jdunkerley79.  That isn't to say that @s_pichaipillai approach is anything but correct.  I would however modify the approach as:

Summarize by

groupby [store ID]

groupby [Cuistomer ID]

SUM [customer revenue]

SORT by

[Store ID] Ascending

[SUM of Customer Revenue] Descending

Then you could sample with a GROUPING on STORE ID and pick the first 20%

Now for your caveats:   

1) When you assign customer IDs have you done your best to prevent split files (an entity that has multiple customer IDs)?

2) When a single customer shops across multiple stores do you treat them based on their total value?

3) Are you looking for the number of customers that create 20% of the store's total sales versus finding the 20% of the customers with the greatest purchase history?

4) Does transaction frequency or recency have a role in your identification process?

Thanks,

Mark

[BWitonsky]

Hey all,

Sorry for such a late response.  By starting with yourrecomendations I found something that worked.  I now know I also did not explain what I was trying to do too well.  My issue out what % of customers generate 80% of revenue from almost 300 stores.

To do it, I summed revenue by store.

Joined this back to the customer data.

Sorted revenue across all stroes and put customer rev in a descending order.

I then ran a running total by store on customer revenue,.

Then added a formula to calculate run total/ store total.

Once the formula hit 80% I still had to determine the number of customers divided by total amt. of customers for that store.

Thanks!

[Newbie_1]

I have a similar question. What would be the right way to filter the top 50% contributor of the total.

To explain it clearly, I have the column "%Amount".After sorting the "%Amount",I need to filter out those "%Amount" whose summation is ~50% and thereby use only those  "part number" for further analysis. i.e. I need to filter for the top 50% contributor "part number" 

[trettelap]

I would do a running total on the percentage and then do a manual tile to split at 50%.