-1
$\begingroup$

I have the following data:

Item        Search_Vol   Competition    Bid    
item 1      10,000       17%            $1.03
item 2      20,000       25%            $2.7
item 3       5,000        5%            $0.8
item 4      55,000        5%            $2.8
item 5     100,000       15%            $0.1
item 6     200,000        7%            $0.7
... etc

The Search Volume and Bid columns need to be sorted in descending order (bigger the better), while the Competition column needs be sorted in ascending order (least competition is best)

Now I need to rank/sort the items according to the criteria above. (so that the keyword with the largest search volume + bid amount along with minimal competition ranks first)

What would be the best way of achieving this?

PS: I'll be using a general purpose programming language and not SQL/database.

UPDATE #1: The criteria is a bit more complex than I'd assumed at first... If the search volume or bid is significantly higher than the rest, the corresponding item will get some boost. Similarly, if the competition is significantly higher than the nearest neighbor, it should be relegated...

UPDATE#2: CSV formatted example data:

Item,Search_Vol,Bid,Competition
item_1,10000,1.03,17
item_2,20000,2.7,25
item_3,5000,0.8,5
item_4,55000,2.8,5
item_5,100000,0.1,15
item_6,200000,0.7,7
$\endgroup$
  • 1
    $\begingroup$ Search-Vol & Bid are not commensurate. You need to specify how you are going to equate them. Ie, how many searches are worth $1? $\endgroup$ – gung Sep 11 '15 at 17:22
  • $\begingroup$ There's no relationship between any of the columns. The values are arbitrary. All I need to do is rank the most profitable items first using this criteria: most searched + most expensive + with least competition. $\endgroup$ – masroore Sep 11 '15 at 17:25
0
$\begingroup$

The python code here will read a file formatted similar to this and write it to a file named "sorted_items.csv".

You just have to edit the function get_item_val using the correct formula you want to calulate a value to rank the items by.

$\endgroup$
  • $\begingroup$ An optimal get_item_val() is what I'm after. Unfortunately, this code doesn't generate the output I'm looking for. I've updated the original post's example table to include a couple of more rows. According to the criteria, "item 5" should rank higher since it has significantly more search volume (but but less bid amount and high competition), In your python output it's positioned at #5. IMO, it should be near the top (perhaps at #3 spot)... $\endgroup$ – masroore Sep 11 '15 at 19:36
  • $\begingroup$ I guess the criteria is a bit more complex than I'd realized at first... If the search volume or bid are significantly higher than the rest, then the corresponding item should get some boost. Similarly, if the competition is significantly higher than the nearest neighbor, it should be relegated... $\endgroup$ – masroore Sep 11 '15 at 19:37
  • $\begingroup$ Are you expected to create a function to generate a value to rank items by using: Search_Vol, Bid, and Competition based only on the 6 ranked items you have provided? Did the instructor not give you a larger dataset of ranked items? $\endgroup$ – toinetoine Sep 11 '15 at 20:19
  • $\begingroup$ Alright, here's a real-world dataset freshly scraped from my google adwords account. Now we need to sort this table according to the criteria laid down above. $\endgroup$ – masroore Sep 12 '15 at 9:47
  • $\begingroup$ Listen, there is something your misunderstanding. I need the criteria with which to rank them. You can't just say: "rank them where ____ is high and _____ is low but if _____ is abnormally high then give it..." To rank them you need one of two things: 1. a formula to generate a value to rank them by. 2. a set of ranked items which you can create a formula with. $\endgroup$ – toinetoine Sep 12 '15 at 17:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.