an attempt to quantitatively measure song enjoyment, using itunes & excel, resulting in a numerical value: the "sire score" ("SS")
STEPS:
1. numerical tag number assigned to each song in itunes beginning with 00001, 00002, 00003, etc. (using 'comment' field in itunes)
2. itunes library exported to master excel spreadsheet
THE CHART:
3. once per week, itunes library exported to excel spreadsheet
4. simple formula utilized to calculate, for each song, the difference in playcount between current week and previous week, resulting in song playcounts for each week
[note: step #1 ensures each song retains same excel line position as the previous week, to counteract alphabetical shifting when new songs are added to itunes]
CALCULATIONS:
A. total appearances: 1 or more plays in a given reporting week = 1 appearance (eg. 4 appearances out of 6 weeks = 0.666)
B. plays/week: sum of total plays on chart divided by total weeks on the chart (eg. 6 total plays / 6 weeks = 1.00)
C. days on itunes: = difference between current date and date the song was added to itunes (eg. 4/6/2011 – 6/25/2008 = 1015 days)
D. plays per day: = total song plays divided by days on itunes (eg. 198 / 1015 = 0.195073892)
E. play count for previous week
F. play count for 2nd previous week
THE SIRE SCORE:
= IF(AND(E<1,F<1),(AVERAGE(A,B))*D*50,((AVERAGE(A,B))*D*100))
condition 1:
= IF song has had a least 1 play in the previous two weeks
= (AVERAGE(0.666,1.000))*0.195073282*100
= 16.26
condition 2:
= IF song has not had a least 1 play in the previous two weeks
= (AVERAGE(0.666,1.000))*0.195073282*50
= 8.13
RATIONALE:
1. averaging A and B factors in recent play count information and consistency of appearances
2. D factors in historical play information
3. 100 results in a visually nicer final value
4. the IF/AND condition adjusts the SS for songs that burn brightly but burn out quickly (ie. new songs that are played several times, but do not possess staying power). rather than letting time adjust the SS, the added condition speeds up the process, so to speak. that is, until the song is played again, rendering the condition not applicable
OTHER:
i understand there may be some statistically-minded folks here. my intent in posting this labor of love is to solicit any comments and/or suggestions for improvement.
i'm not too keen on posting the excel file in its entirely, however, a screenshot for reference purposes can be viewed here: http://postimage.org/image/2hxbf9s2s/
thanks in advance
-sire
I tried to do something like that when I was in high school.
I found that it was impossible to be objective that way because you start to alter your listening habits based on how you feel you want the list to turn out.
If you want to make your list solely based on number of listens, find a way to hide the numbers from yourself until you calculate them. It will only work if you make your listening decisions without knowing how it will affect the final tally.
Another small critique, if you use average listening frequency over several years, you do not allow your list to change when your opinion changes.
Now I have something to create album lists based on relative position instead of any objective measure.
Basically, you constantly rank albums relative to each other, and the algorithm looks for the ordering that makes the most sense given those ratings.
In other words, if you first enter A > B > C > D, then enter D > E > F > G, the program will tell you A > B > C > D > E > F > G. Only for me it does this for almost 600 of my favorite albums for the listening I've done over three years. It also weights comparisons more depending on recency and how long you've owned each album.
(I'm just as stats-nerdy as you are!)
thanks for the comments.
to your point about altered listening habits influencing the results, it's a valid consideration and I'm aware of that potential bias. i legitimately try to listen to songs based solely on whether or not i wish to hear them, and believe i'm successful. to manipulate my listening habits to suit the data would cheapen the whole endeavor, making it pointless.
and re: changing opinion, i've considered this as well and believe i've taken it into account by incorporating recent listening trends into the calculation. a song with 300 plays, for example, will not garner a high SS without any recent listening data. and vice versa. the two functions coexist equally; that's why i didn't assign a higher weight to one over the other.
at the end of the day, the point of this is an attempt at an objective measure. sometimes my own subjectivity feels unsubstantial.
I agree. When you try to just make a list out of thin air it's going to be biased toward what you can remember most easily at the time.
What I do to be objective is compare albums only against each other, right after I listen to them. I can't remember well enough which albums I enjoyed the most over a long period of time, but I can remember which I enjoyed more when I just listened to them.
Bill- Is your project ready to be shared? I know you tried once before, then removed it for tweaking. I'd love to test drive the database!