Product Movement Algorithm Ranking (Based on Units Sold and Net Income)

Question

I am not totally sure if this is the right place to ask this question. Hopefully someone will have an answer or could give some advice.

I run a small shop that sells commodities. I have a program / spreadsheet that will list the top moving items.

I can sort this information by total number of units sold:

Product 1, 15  
Product 2, 12  
Product 3, 7

... or by total value in sales:

Product 2, $250.00   
Product 1, $75.00  
Product 3, $30.00

I would like to create a combined rank between these two values. I have programming (PHP, Python, Perl, etc.) and database experience. I was wondering if there is some way to identify the top movers with a combined value of income and units. For example, Product 1 sells more units, but brings in less money. Perhaps product 2 is more important. Maybe a points based system where 1.5 points might be given for each unit sold and 2 points for each dollar. (monetary being slightly more important)

Any ideas?

The first thing you need to think about is "what exactly you want these ranks to mean". A simple approach would be to sort them in both of the above ways (in increasing manner) and assign a score like `units_sold_rank * 1.5 + total_money_gathered_rank * 2.0` and sort them based on that. In your case that will push Product 2 on the top with a score of `1.5` (or `4.0` depending if you index from `0` or from `1`), while Product 1 will have a score of `2.0` (or `5.5`) — dmg, Mar 02 '13 at 14:24
Are you are asking for business algorithm rather than code? If yes, try googling or binging "ABC pareto analysis inventory", common technique to rank as you ask. — rlb, Mar 02 '13 at 20:47

score 0 · Answer 1 · answered Mar 02 '13 at 14:24

Assuming you are using MySQL, then this query should be pretty close for what you're looking for:

SELECT Product, 
  CAST(1.5*UnitsSold as DECIMAL) + 2*DollarsSold OverallRank
FROM YourTable
ORDER BY OverallRank DESC

SQL Fiddle Demo

If not, the same concept should work in a spreadsheet.

score 0 · Answer 2 · answered Mar 02 '13 at 14:51

I'm not also sure if this is the right place where ask this kind of questions. It seems your are asking for algorithm or some standard business rules but for final code.

Well, I post an approach to raise new ideas. I construct a list of algorithms and test for top values:

import math

fs = [
 ('Plain coef',    lambda m: math.log(m[1]* units_coef)  
                           + math.log(m[2]* money_coef)  ),
 ('Coef into log', lambda m: m[1]* units_coef 
                           + m[2]* money_coef                       ),
 ('Coef out log',  lambda m: math.log(m[1])* units_coef  
                           + math.log(m[2]) * money_coef ),
 ('Coef out log2',  lambda m: math.log(m[1],2)* units_coef  
                           + math.log(m[2],2) * money_coef ),
]

movings = [ ( 'product1', 1500,    75.00 ),
            ( 'product2',    2, 90000.00 ),
            ( 'product3', 1200,  8000.00 ),
            ( 'product4',    6,  4000.00 ),
            ( 'product5',  500,  1000.00 ),
            ( 'product6',  800,  1200.00 ),
            ( 'product7',  300,   800.00 ),
           ]

units_coef = 1.1
money_coef = 0.04

for (n,f) in fs:
    print ''
    print n
    print '==============================================='
    for i in sorted( movings, 
                     key = lambda m: f(m) , 
                     reverse=True)[:3]:
        print i, f(i)

Results:

$ python solds.py 

Plain coef
===============================================
('product3', 1200, 8000.0) 12.9537080114
('product6', 800, 1200.0) 10.6511229184
('product5', 500, 1000.0) 9.99879773234

Coef into log
===============================================
('product2', 2, 90000.0) 3602.2
('product1', 1500, 75.0) 1653.0
('product3', 1200, 8000.0) 1640.0

Coef out log
===============================================
('product1', 1500, 75.0) 8.21724195034
('product3', 1200, 8000.0) 8.15857239218
('product6', 800, 1200.0) 7.63667597387

Coef out log2
===============================================
('product1', 1500, 75.0) 11.8549742115
('product3', 1200, 8000.0) 11.7703319309
('product6', 800, 1200.0) 11.0173945564

Product Movement Algorithm Ranking (Based on Units Sold and Net Income)

2 Answers2