I'm trying to devise a way to rank accounts from best to worst based on their telephone duration and margin.
The data looks like this;
ID TIME_ON_PHONE MARGIN
1 1235 1256
2 12 124
3 1635 0
4 124 652
5 0 4566
Any suggestions on how to rank them from best to worst?
ID 5 = best as we have spent no time on the phone but their margin is the most.
ID 3 = worst as we've spend ages on the phone but no orders.
I've put it into excel to try and devise a solution but I can't get the ranking correct.
I would suggest creating a new metric like
New Metric = Margin / Time on phone
to compare each row.
To create a column with this metric just use:
dataframe["new_metric"] = dataframe["MARGIN"]/dataframe["TIME_ON_PHONE"]
Having 0 values in the TIME_ON_PHONE column will lead to an error, so I recommend replacing those values with a very small one, like 0.001 or something.
After that you can simply use this line of code to sort your rows:
dataframe = dataframe.sort_values("new_metric", ascending = False)
That way you would end up with the first ID being the best one, the second ID the second best one... etc.
Hope it helps.