Aka "How to Countdown 913 awesome songs and tweet about, with photos, from a spreadsheet."
Our listeners voted on the top 913 songs, which we then scheduled to play throughout the week as a countdown.
'Here's my thinking for the countdown tweets: '#374 The Flaming Lips "Race For The Prize" || #913Countdown'
Simple request...
The thing is, our twitterbot is a hacked together mashup. A prototype really. It pulls information from an NPR Digital Composer playlist feed. Our Drupal CMS ingests the feed, and posts to Twitter.
Unfortunately, the place in the countdown isn't included anywhere in that pipeline. What to do?
When I got the countdown list from our Programming Director, it looked like this:
I have pretty much all the info I need. I added "for the song title in column F, and added a concat function:
=CONCATENATE("#",E2," ",C2," ", F2, D2,F2," || #913Countdown")
Which gives me the output:
#913 Grateful Dead "Casey Jones" || #913Countdown
How do I get these tweets scheduled for Twitter from my spreadsheet?
As it turns out, there is a great tool called
Zapier, which can connect a Google Spreadsheet to your
Buffer account.
The gist of our Zapier setup:
The Trigger: When a row is updated in the Google Spreadsheet
The Action: Add to Buffer Schedule
The text source is the column containing the results of my concatenate function above. For the schedule template I used the two columns to build a human readable date: Wed 04/13/2016 10:01am -1h
The -1h (minus 1 hour) was necessary to get the time right... not sure why since the timezone was set to East Coast. (don't forget to keep a space before "-1h")
And presto... 913 songs scheduled to tweet! Only... our level of Buffer account only allows 200 scheduled at a time. Also, anything over 99 sends you an email to confirm that you want to do such a large job.
But how did you get all those photos in there?
I later noticed that you can add photos based on a column in your spreadsheet. There was a quirk though... and I don't quite remember what it was. For some reason, just adding a URL in the column wasn't working. Instead, I wound up building the url in the Zapier template. We used: http://bit.ly/(google sheet column).
Our awesome interns then proceeded to curate photos and enter appropriate bitly codes into the spreadsheet.
This mostly worked!
What didn't work: Tweets with photos larger than 3mb would fail. There was no email notification that they failed, so I didn't notice until I checked into Buffer. It would have been really great to get a notification when the tweet was scheduled, instead of waiting for it to fail.
Also, nearing the end of our countdown we got the sad news that Prince had died. There was no way we could carry on counting down without taking a moment to celebrate the musical force that Prince was.
It was the last day of the NAB Show, and I was just getting ready to check out of our hotel. But first, I thought I would check in on the countdown, and finish up the scheduling.
Our marketing coordinator mentioned something in Hipchat about preparing messaging about Prince, just in case. What? Eeek. Not Prince! I looked up the info... AP had verified that Prince had died.
I tuned into our stream, and we were playing Purple Rain. Odd... Purple Rain was indeed on our countdown, but it wasn't at this spot. Oh no... we are going wall to wall Prince. I mean awesome, I love Prince. But...
Must. Stop. The. Tweet. Train.
Deleting, editing, or rescheduling a tweet in Buffer is relatively easy. Deleting 200 tweets is not. It requires a hover, click, confirm for each. Man, would checkboxes be nice. I found a bit of a shortcut, where I could tap my touchscreen where the delete button would be, and it would jump to the confirm dialog which was always in the same spot. It was like a Twisted Candy Crush.
We managed to only publish a couple of countdown songs ahead of their actual playtime, and were able to resume the countdown without too much trouble.
All in all, the tweet scheduling was a success. There was a big increase in impressions and engagement through the countdown. This was a good way to provide an additional level of digital content to WYEP listeners, allowing them to share in a sense of community and conversation celebrating music.
