I am absolutely convinced that the internet exists for people like me.
You know…the kind of people that when asked to do something will base whether or not they can do it on the availability of online instructions. I am 100% THAT person and so far, it hasn’t failed me yet.
It almost did though.
The other day, I was given my superintendent’s request for our district convocation surrounding integrating a social media experience for our staff. Her request seemed simple enough…
- Display tweets on the big screen with high quality visible images.
- Create a twitter leaderboard of original tweets so that the one that tweets the most gets the gift card.
Upon hearing these requests, of course I said, “sure thing”! After all, this wasn’t framed as an inquiry as to if it could be done but more like a “make this happen” especially considering that we had a branded twitter slide show last year that unbeknownst to everyone, I paid for…to the tune of $200 for a one day display.
That was last year and this year, with at least a day of notice, I believed that I could not only provide what I was asked but do so at no cost.
…and then I started confidently “internet searching”.
Twitter wall with image display? Check!!!!! (minus moderation but here’s a tool that’s free and allows moderation. It’s under $20 to remove their logo and insert your own. There’s also this one which includes moderation…also free.)
Now on to finding a way to track tweeters by hashtag and count them…minus retweets. A hashtag “leaderboard” shouldn’t be hard to find, right?
So yeah…this wasn’t so readily available unless I wanted to pay fees ranging from $20 – $800 for a one day event.
Also, marketing firms tend to value impressions and influence much more than who is tweeting the most so a “leaderboard” looked more like “who has the most follows” than what we actually needed or wanted. There was one tool, SocialMention.com, that just made me LOL at how terrible its snapshot data was…only after I actually used it for a time thinking that it was accurate. It wasn’t…unless accuracy can be measured by 100 tweets that seemed to update at unmeasurable intervals. On the bright side, our users were 66% passionate…so there’s that.
I finally attempted to create my 15th free social marketing account at Keyhole, a site that boasted yet another free trial which is honestly more like a “free snapshot of possibilities” because the good stuff is never free. At any rate, they had a leaderboard “per se” but it wasn’t real time, even though they said that it was. However, for $129 maybe it could’ve been?
Social Media EDU Lesson 101: Our version of “branding” isn’t the same thing as actual “branding”
I should also mention that this “leaderboard” search extended well into the midpoint of our convocation…about two hours before our superintendent was to announce the winner.
So, I had a dilemma and this is where it gets good. I could start hand counting tweets, as one of our specialist had already started to do or this self-proclaimed “learner” could brainstorm her way to a miracle!
Insert Rocky Theme Song…Brainstorm, an “aha” and also Google Sheets!
Twitter Archiver Add-On FTW!
Why pay $120 for a simple leaderboard when you can do it all at zero cost? I’m not measuring passion, influence or impressions people. I just need to know who tweeted the most original tweets!!
Using twitter archiver, I was able to create a thorough live spreadsheet of tweets for our hashtag which updated every hour. The premium version updated every 15 minutes, but at zero cost, I did manual refreshes as needed.
Even though I was running manual pulls, which were set to ignore “retweets”, I wanted to collect data and have it determine what I needed a little more automated which I knew could be done in excel, so surely it could be done in Google, right? A little Array Formula is all it took for one sheet to pull nothing but screen names from my raw data sheet!
From there, I needed to find a way to count number of tweets which equated to “number of times screen name appeared” and I needed two formulas for this, “UNIQUE” and “COUNTIF”.
The Unique formula checked my column for screen names, skipping duplicates. Each new name was added to a new column. The CountIF formula checked names on the “Unique” list, counted them on the “screen name” master list and assigned a numerical value.
And of course, fresh from my Adobe Education Exchange course on visual data literacy, the obvious next step was to create a chart. Without paying a fee, giving access to my twitter account or making a new account, I had my own method of creating a “useful this one-time a year only” tool that did exactly what we needed.
Nothing more and nothing less. (unless you consider that every tweet you send includes data about the phone you are using so whether I wanted to collect it or not, I know who our ratio of iphone:android and sticking this live chart into a published slide while my “tweet tracker” is capturing tweets is basically awesome.
And oh by the way, this entire process took me about 10 minutes of search and creation time to do while looking for a “pre-created product” took an entire day. How’s that for learning how to do something while doing it?!