The Pop Culture Information Society...

These are the messages that have been posted on inthe00s over the past few years.

Check out the messageboard archive index for a complete list of topic areas.

This archive is periodically refreshed with the latest messages from the current messageboard.

Check for new replies or respond here...

Subject: Track your songs' progress over time easily with a spreadsheet. No skill needed!

Written By: Tommy Turtle on 02/03/10 at 12:21 am

Most of our song posts get most of their hits in the first few days or a week after being posted, then continue to draw a few random hits as time goes by. Some do better than others, and regularly draw views for months or years after being posted. If you are curious about which of your songs have this kind of staying power (what showbiz calls "legs"), you can track them all, whether 10 or 1000, using a simple spreadsheet. A monthly snapshot can be compared to previous snapshots to see what's still moving. Sometimes, the answers are surprising.  ;)

No experience is required. This is written for the user with little or no experience in spreadsheets. Knowledgeable users will be able to speed-read or skip some instructions.
The instructions are Windows-oriented; if you use Mac, Linux, or whatever, adjust the commands appropriately.

I use Open Office, partly because it takes up only 1/3 as much space on your hard drive as MicrosoftTM OfficeTM, but mostly because it costs 100% less than MS Office. Free, nothing, nada, zip, zilch. No nag screens, no requests to donate, donations not even accepted. (So I hope this is regarded as a public service, not a spam link.) It will create, open, edit, and save MS files, including WordTM (".doc") and ExcelTM (".xls"), as well as its own open-source, publicly-available formats. Save your $400.00. But if you already have MS Office, these instructions work just as well. We'll use either the Open Office "Calc" (.ods), or MS Excel (.xls) spreadsheet.

The first step is to copy your song table from your author page. You'll probably first want to sort them by number of hits. After the page loads fully, click "H" once to sort from lowest to highest, then click again to sort from highest to lowest. (You're probably most interested in which ones are most popular, right?) NOTE: The second click, to sort to highest first, does *not* work if you have posted a song that day, or on the last update. You'll either have to go from low to high, or wait until the next update in which you have *not* posted a song. I've asked ChuckyG about this, and it just seems to be a strange glitch in the system. Easy enough to work around, but if you post *every* day, it looks like yours will have to be low-to-high.

To save bandwidth, the pics have been heavily cropped to show only the point being discussed.

To copy your song table: If you have a small number of songs, you can just hold and drag the mouse from the left side of the first title to the right side of the bottom of the "Hits" column. If you're in the triple- or quadruple-digit song total, it's probably easier to put the mouse pointer to the left of your first title, like this:
and click there. Then put the mouse pointer to the right of the "hits" number at the bottom of the table, like so: *hold down the Shift key*, and click. This should highlight the entire table in blue. (We don't want the captions at the top: Parody Title, Original Title, etc. No need.) Now, from the Edit menu or right-click menu, hit "Copy".

Now open a new spreadsheet document, whether from Excel or Open Office Calc. (These are the only two I've used. I don't know if/how other spreadsheet programs would work.)  Put the mouse pointer in Column A, Row 1, and click to select it, then right-click. Hit "Paste", and - voila!
Your complete song table is now a spreadsheet! ... If you have hundreds or thousands of songs, it will take a few seconds to fill in. I don't know what is the maximum number of rows, but for the sake of John Barry and Malcolm Higgins, I've seen that Open Office Calc will go to 3,000 rows (songs) with no problem.  8)

You'll probably want to widen Columns A, B, and C either before or after loading the data, so that parody and OS titles, and TOS artists, don't run more than one line. Just put the pointer on the line between A and B, click, and drag it to the right to widen Column A. Same thing with the line between B and C to widen Column B,  (OS title) and with the line between C and D to widen C.

Click "Save", choose a title -- might as well include the date of this snapshot, as above: "Tommy Song List 020110" (Feb 1, 2010) -- and save it wherever you like.

You'll notice that columns E, F, and G don't show. They are Pacing, How Funny, and Overall. They were cropped partially to keep the pics smaller, but even in my real spreadsheets, I'm not so concerned with those numbers or their changes over time, thanks to the many brave volunteer suicide Unabombers on the site. (It's *not* suicide? Dang!)
In Open Office, you'd drag the mouse across the letters E, F, and G, then choose the Format menu > Column > Hide. Presto - gone!

Now you can make a new spreadsheet each month, or however often you like, compare them, and see which songs are continuing to get the most hits, and, like the Billboard Hot 100, which are moving up the chart. (I hate to say what my results tell about the readership here, nudge, wink  ;D )

Suppose you'd like to know the "total" number of page hits you've had. That would take a while to add up if you have hundreds of songs. There should be a "Sum" function bar, like this (same shot, but with "Sum" outlined in red) ... or instead of the word "Sum", you might see the Greek symbol, Σ (sigma), the standard math symbol for "sum". Put the cursor in the first empty cell below your last row, in column H (Hits), click it, then click the "Sum" function. It will probably fill in the formula for you, and you just hit "Enter" or "Accept". If not, in the blank bar to the right of the Functions, type =Sum(H1:H332) (Enter) ... because at the time this was made, I had 332 songs in the chart. It really "should" prompt you to auto-complete this, anyway. Instantly, the total number of hits of all songs appears:
Note that the value in the left box, H333, is telling you that it is equal to the value in the right box, the Sum of H1 through H332. Namely, 68,817 total hits.

Suppose you wanted to know the average number of hits per song? It would be easy enough to grab a pocket calculator, or use the calculator built into the computer (In Windows: Start > Run > Calc (enter). But if you'd like the spreadsheet to do that for you, especially as you add songs over time, you can do that too. Click on the next cell down in Column H, or several cells down if you want to leave room for future songs (same with "Sum"), and either select "Average" from the Function drop-down menu, or just start typing
=AV. By now, it will probably auto-complete for you, but if not, type what's in the below diagram:
=AVERAGE(H1:H332). In other words, "Machine, find the average of all of the numbers in column H, rows 1 - 332." Like so:
... And there we are. An average of 207.28 hits per song.
Be sure to "Save" as you go along, and when you're done. Fun!

Next: (Strictly optional) story about an incident that started me doing this, other than just liking to play with numbers. (BTW, I use this only to compare my own songs among themselves, and not to "compete" with anyone else. Page hit numbers are affected by many factors: if you're on the site every single day and v/c-ing every other song vs. intermittent or taking long breaks; if you live in a college dorm, have an extended family, or a humongous e-mail address list where you can spam-link your songs; participation in SOTM, where others *have* to read your songs; vs. living inside a shell on an underwater coral reef where almost none of the other occupants has Internet access.  :D FWIW.)

I hope some of you find this interesting and useful!  ;)

Subject: Re: Track your songs' progress over time easily with a spreadsheet. No skill nee

Written By: Tommy Turtle on 02/03/10 at 12:57 am

What brought up this idea: (OPTIONAL)

The same song has pretty much been at the top of the Tommy Chart for several years. However, some time around the summer of 2009, I think, it started growing at a rate rather unusual for a three-year-old song. Curious, I Googled it (I use Scroogle now -- no ads, no "cookies", no tracking of your searches and profiling you, and a secure, encrypted connection, just like with your bank.) The parody came up, not only here at AmIRight, but also at the web site of a British rugby club. Seems someone had asked, "What was that song... ?" and someone else posted mine. No author, no copyright, no link .... well, at least they didn't claim that *they* wrote it. The actual song they were looking for was an OS by Stevie Wonder, but as the context was rather crude, it won't be printed here.

I sent the webmaster a polite e-mail, saying that I was flattered, not upset, but I'd just like to have my authorship and ownership (copyright) acknowledged, with a link to the page, to invite visitors here. They did so; I bonded with the boys for a bit; we had a couple of pints of Guinness, and all was well.

Since I wouldn't be getting page view credit here for views at their site, that wasn't what was pushing the song. However, the double listing in Google -- AIR and their site -- might have brought some extra visitors searching any similar keywords. (They've since dropped off the top Google listings for it.) That's when it struck me that it might be interesting to watch all of the songs -- which ones continued to grow months or years later, or which ones started out slowly, then surprisingly picked up steam later. For example, I figured that all of the Sarah Palin parodies written during the 2008 POTUS campaign were dead in the water when she lost. But when she came out with her book recently, started hitting the talk-show circuit, became a contributor to a cable news network, etc., they started moving again.

End of story. :)

Check for new replies or respond here...