Looking for a Keyword ranking formula on a Data set in excel
I am trying to do keyword comparison based on position ranking in a search pattern over time and create a predictor of a profitability index of which keyword and ad grouping will perform best over time,
Here is the data that I am given:
Last position ranked
top position ranked
avg position ranked
and avg number of competitors in this ranking
total days in the count (TDC)
total number of times this keyword was found (TDF)
and a ratio of TDF/TDC
first day seen
last day seen
Here is what I am trying to achieve:
A Profitability Index number between 0 and 1 that would indicate how well this keyword performed over a time period. with 1 being the best.
I am including a 500 keyword xls spreadsheet in a zip file for your help. You can see my attempt at ranking. I am not a math whiz, that's why I am asking for your help. Any suggestions would be appreciated and acknowledged on future site designs.