Two reasons journalists should learn to love Excel

By Mack D. Male · November 9, 2010 at 10:00am

I love Microsoft Excel, I really do. It’s currently the second highest item in my Start Menu, that’s how frequently I use it (now that I think about it, I should just pin it). I use it for all kinds of things – calculations, cleaning up data, and yes, generating graphs. It’s a really versatile tool, and it’s really easy to use (especially the latest version).

I often talk about changes I’d like to see in the mainstream media, and two important ones are context and presentation. There are so many stories that seem like they’re written in a vacuum. A story about housing starts is a good example, like this one from the Edmonton Journal yesterday. There’s 560 words there, words about numbers. Is that the best way to present that information? And even if you think it is, where’s the context? How do the housing starts this month relate to averages and historical numbers?

That’s the first reason that journalists should learn to love Excel – it can make providing context and better presentation easy. Here are three simple graphs, created with Excel, that tell you about housing starts in Edmonton.

This data comes from a PDF provided by the City of Edmonton. It shows annual housing starts since 1970. Copy and paste into Excel and you’re done!

This graph shows monthly housing starts from October 2008 until now. It uses data from the CMHC’s Reports & Publications section. Took maybe 10 minutes of copying and pasting.

This graph compares housing starts for this time of year from 2006 until now. Also comes from the CMHC.

Imagine if the article included graphs like these. The journalist could then focus on telling a more interesting story.

So, what’s the second reason journalists should learn to love Excel? Well, it can help them get their story right. Here’s what the Journal article starts with:

Despite a strong spring, the slowing trend in new-home construction became clear in October with housing starts dropping to their lowest level since June 2009 in the Edmonton region.

As you can see from the second graph above, that’s just not true. Is there a slowing trend? Maybe, if you just look from the spring to now. Was October the lowest level since June 2009? No. There were just 690 starts in August 2010. In fact, there were six months with lower housing starts since June 2009. I’m not sure what data the Journal was looking at, but it doesn’t appear to be CMHC data.

Add Excel to your toolkit. You won’t regret it.

UPDATE: Here’s the Journal story on August housing starts. Maybe if finding archived stories was easier, Dave Cooper, who wrote the story on October housing starts, could have consulted previous Journal articles to see that the lowest level was much more recent than June 2009.

How Friday’s AMBER Alert unfolded on Twitter

By Mack D. Male · November 1, 2010 at 10:30am

On Friday afternoon, RCMP issued an AMBER Alert for 12-year-old Jacob Telford. Thankfully, he was found “in good condition” after just a few hours. For a while that evening, it seemed like every tweet in Edmonton was related to the incident. At one point during the evening, @tkoriordan said “So I’ve been away from Twitter all day. Somebody want to fill me in on what I missed?” to which @Kiri_W replied “About a billion amber alert retweets.” Curiosity got the better of me, so I looked at the data to see how the AMBER Alert unfolded on Twitter.

Here are the first four tweets that appeared:

Just heard about amber alert, young boy from #shpk, check out alert and key an eye outMatthew Ross
Oct 29 at 2:11 PM via ÜberTwitter

1st time I have hear the emerg broadcast have a real emerg. Its ano amber alert for a little boy missing from Sherwood ParkLesley S
Oct 29 at 2:11 PM via Twitter for BlackBerry®

Amber Alert in #YEG child abducted in #sherwoodpark. #Edmonton #AMBERALERT!trevortye
Oct 29 at 2:15 PM via Echofon

AMBER ALERT: 12-year-old Jacob Telford taken from Sherwood Park last night – White 2002 Ford Taurus – BC Plate: V2P4J3CTV Edmonton
Oct 29 at 2:17 PM via Seesmic Desktop

Actually, Rob McAnally had one up before the main CTV Edmonton account did, but it seems that tweet has since been deleted. CTV posted the first link to a new story at 2:29 PM (that link has since been clicked over 1000 times).

From Matthew’s first tweet at 2:11:17 PM until 7:23:43 PM when Jeremy Lye’s tweet appeared declaring that the AMBER Alert had been cancelled (the first to do so), a total of 958 tweets were posted (mentioning either the AMBER Alert or Jacob Telford). That works out to just over 3 tweets per minute (in the first hour, it was nearly 7 tweets per minute). By 5 PM the next day, the total number of related tweets had grown slightly to 1126.

Of those, 983 were retweets. Here’s what the retweets looked like:

@ctvedmonton was retweeted 674 times
@SimonOstler was retweeted 126 times
@sonic1029 was retweeted 70 times
Taken together, everyone else was retweeted 113 times

They weren’t quite the first to tweet the start or end of the AMBER Alert (they were about six minutes behind both times), but CTV Edmonton was definitely the most visible account to do so, and that is reflected in the number of retweets they received.

In total, 710 different Twitter users tweeted (or retweeted) about the incident. Just less than half were located in the Edmonton area, while some were as far away as Brazil, Russia, and Kuwait (based on the location in their profiles). I suppose there’s always a chance that their retweets could have helped (maybe they have followers in Canada) but I still have to wonder why someone who is so far away would tweet about something like this. Is it because it’s easy to click the retweet button? Is it because we all like to feel as though we’re helping? I’m not sure.

If you add up the number of followers those 710 users have, you get a potential reach of more than 288,000 users. That’s not accounting for overlap though, so the actual number is probably quite a bit less. And not all of those users will have been online to see the tweets. Still, there’s no question that the AMBER Alert was seen far and wide on Friday.

Here’s what I found interesting about all of this:

85% of all the tweets posted about the incident appeared before we knew the boy was safe
60% of all the tweets posted about the incident were simply retweets of CTV Edmonton’s tweets
It took less than 20 minutes for the first news story link to appear
After 24 hours, almost no one was tweeting about it anymore

You can learn more about the AMBER Alert Program here.

State of the Edmonton Twittersphere – August 2010

By Mack D. Male · October 29, 2010 at 10:00am

Welcome to the eighth State of the Edmonton Twittersphere of 2010, my look at the intersection of Twitter and Edmonton, AB. You can see last month’s stats here.

For information on the data, definitions, and other background, click here.

For August 2010:

# of local users: 8488 (an increase of 261 from July)
# of tweets by local users: 481605
# of tweets by local users containing #yeg: 35965 (7.5%)
# of tweets by local users that were replies: 167831 (34.8%)
# of tweets by local users containing links: 144532 (30.0%)
# of tweets by local users that were retweets: 34282 (7.1%)
# of tweets by local users that were twooshes: 16012 (3.3%)

Here are the numbers above in graphic form:

Here are the top clients used by local users for posting updates – BlackBerry apps really dropped off this month for some reason:

Some other interesting stats for the month:

Just under 51% of all local tweets were posted between 9 AM and 5 PM.
Local users posted roughly 10.8 tweets per minute in August (compared to 10.0 tweets per minute in July).
The day with the most local tweets posted was August 26 at 18232. On average, 15536 local tweets were posted each day (compared to 14390 in July).
Of the 167831 replies posted by local users this month, 65767 or 39.2% were to other local users.
A total of 1727 users posted 50 times or more in August. In comparison, 1199 users posted just once.

Here are the top ten most active local users (not including bots):

Here are the top ten most active local users using #yeg (not including bots):

Here are the top ten most replied to local users:

Here are the top ten most retweeted local users (by other local users):

Final Thoughts

Better late than never right? It has been a long time since I’ve posted any Twitter stats! A vacation, the election, all kinds of stuff has kept me busy. I think the August stats represent the start of a big increase in election-related tweets. You can see a number of #ecca users in the lists above, and there were 580 tweets posted in August using the #yegvote hashtag.

For August, the number of retweets jumped quite a bit because of changes I made to my analysis code. Previously I only counted tweets that started with “RT @” or tweets that were new-style retweets (using the retweet button). This time, I counted retweets that contained additional text as well. It’s common for users to post a retweet like “LOL! RT @…” and now those are included. The number of retweets therefore jumped from 21528 (old way of calculating) to 34282 (new way of calculating).

Considering it was the end of summer, I’m a little surprised that the average number of tweets posted per day went up!

UPDATE: @burnstand pointed out that the day with the most tweets, August 26, was the day Lady Gaga was in town.

Edmonton Election 2010: Election Result Statistics

By Mack D. Male · October 21, 2010 at 11:30am

By now I’m sure you’ve seen the unofficial election results (official results should be released tomorrow). I thought it would be interesting to look at those numbers in more detail, and with a little bit of context.

There were 14 data updates throughout the night. The first voting stations reported in at 8:31 PM, and the final one reported at 11:35 PM. Here’s what the updates looked like:

Time is along the bottom, the vertical axis represents the number of ballots cast, and the size of the balloon represents the size of the update (the data labels are the number of votes after the update). You can see that there was one very large update, at 9:48 PM.

Here’s what Stephen Mandel’s win looked like throughout the night – the difference in votes between him and nearest competitor David Dorward:

When all was said and done, Mandel had won re-election by 49,533 votes.

A total of 196,661 ballots were cast. Here are the number of votes per ward:

You can see that Ward 8 had the most votes. Because the wards changed this year from six to twelve, it doesn’t make sense to try to compare them to 2007. We can compare the winning candidates however. To get elected in 2007, a candidate on average had 12724 votes. To get elected in 2010, a candidate on average had just 8640 votes.

Here is the difference between first and second place for each ward:

You can see that the two closest races were in Ward 2 and Ward 3. Those two wards were among the busiest in terms of the number of candidates, along with Ward 6 and Ward 11. The biggest wins were in Ward 5, Ward 9, and Ward 10, all of which had strong incumbents and few competitors.

Here are the number of votes per Catholic School Ward:

And finally, here are the number of votes per Public School Ward, compared with 2007:

There were two acclamations this year, versus just one in 2007. In every other ward, the number of votes in 2010 was higher than in 2007. This isn’t surprising, given the increased interest in schools due to the closures.

Just 1217 ballots did not indicate a choice for mayor (compared to 2491 in 2007), where as 4456 ballots did not indicate a choice for councillor. A total of 44,121 ballots did not indicate a choice for school trustee (keep in mind there were two acclamations, but still).

I’ll leave you with this:

UPDATE: Official election results are now available.

Edmonton Election 2010: Nomination Day Statistics

By Mack D. Male · September 21, 2010 at 10:30am

Yesterday was nomination day, the day that all candidates in the upcoming municipal election needed to file their paperwork and pay their fees. Dave was at City Hall and has a nice overview of how things went.

Now that we know who’s running (though some may still drop out) let’s look at some stats. In total, there are 113 candidates vying for the role of mayor, councillor, public school trustee, or catholic school trustee. Here’s a breakdown of the various candidates:

I wondered about gender, since that is often a hot issue in politics. Here’s the gender breakdown for the candidates:

The web is going to play a very important role in this election. It’s the first place people turn to when they want to know more – they search. How many of the candidates could be found online as of last night? And which details did they make available?

As you can see, candidates have some work to do.

Quite a few people, myself included, followed along yesterday on Twitter. Kudos to Dave and John for live-tweeting and posting lots of photos! Here’s what yesterday’s #yegvote activity looked like:

Candidates could file their paperwork between 9am and noon, which explains the big spike in the morning.

I hope Edmontonians take the time to learn about their candidates, and to voice their concerns and thoughts on the issues facing our city. I have updated ShareEdmonton to (hopefully) help make that easier. Here’s what’s new at the Election 2010 site:

You can see a list of upcoming election-related events. You can subscribe to the iCal so you don’t miss anything.
You can see recent election-related tweets.
You can browse all candidates, or just those running for Mayor, Councillor, Public School Trustee, or Catholic School Trustee (you can see them all by ward too).
For each candidate, you can see their photo, the position they’re running for, and any details I have been able to find so far, like website, Twitter, Facebook, etc. For example, here’s Brendan Van Alstine, candidate for Councillor in Ward 7.
You can type in your address to find out what wards you’re in and who your candidates are!

I’d love your feedback on how I can improve the Election 2010 site at ShareEdmonton. And if you find additional candidate information that I’m missing, let me know. I’ve got a few planned improvements on the way, so stay tuned!

State of the Calgary Twittersphere – July 2010

By Mack D. Male · September 2, 2010 at 10:00am

Welcome to the seventh State of the Calgary Twittersphere of 2010, my look at the intersection of Twitter and Calgary, AB. You can see last month’s stats here.

For information on the data, definitions, and other background, click here.

For July 2010:

# of local users: 10501 (a decrease of 125 from June)
# of tweets by local users: 501941
# of tweets by local users containing #yyc: 22956 (4.6%)
# of tweets by local users that were replies: 153421 (30.6%)
# of tweets by local users containing links: 148567 (29.6%)
# of tweets by local users that were retweets: 21291 (4.2%)
# of tweets by local users that were twooshes: 16440 (3.3%)

Here are the numbers above in graphic form:

Here are the top clients used by local users for posting updates:

Some other interesting stats for the month:

Just over 50% of all local tweets were posted between 9 AM and 5 PM.
Local users posted roughly 11.3 tweets per minute in July (compared to 11.7 tweets per minute in June).
The day with the most local tweets posted was July 30 at 19863. On average, 16821 local tweets were posted each day (compared to 16821 in June).
Of the 153421 replies posted by local users this month, 47364 or 30.9% were to other local users.
A total of 1960 users posted 50 times or more in July. In comparison, 1587 users posted just once.

Here are the top ten most followed local users:

Here are the top ten most listed local users:

Here are the top ten most active local users (not including bots):

Here are the top ten most active local users using #yyc (not including bots):

Here are the top ten most replied to local users:

Here are the top ten most retweeted local users (by other local users):

Final Thoughts

A small decrease in users in July, maybe because everyone was outside doing things! No surprise that the Calgary Stampede was among the most retweeted for the month. Chinook Centre celebrated its 50th birthday on August 1, and made a big push for followers during the month of July which likely explains why it was retweeted so much. With the Calgary election heating up, it’s interesting to see that mayoral candidate Naheed Nenshi was the 6th most retweeted user. I wonder if we’ll see more election-related stats as October 18 draws near.

State of the Calgary Twittersphere – June 2010

By Mack D. Male · August 27, 2010 at 11:30am

Welcome to the sixth State of the Calgary Twittersphere of 2010, my look at the intersection of Twitter and Calgary, AB. You can see last month’s stats here.

For information on the data, definitions, and other background, click here.

For June 2010:

# of local users: 10626 (an increase of 79 from May)
# of tweets by local users: 504633
# of tweets by local users containing #yyc: 22382 (4.4%)
# of tweets by local users that were replies: 156128 (30.9%)
# of tweets by local users containing links: 148101 (29.3%)
# of tweets by local users that were retweets: 22445 (4.4%)
# of tweets by local users that were twooshes: 17296 (3.4%)

Here are the numbers above in graphic form:

Here are the top clients used by local users for posting updates:

Some other interesting stats for the month:

Just under 50% of all local tweets were posted between 9 AM and 5 PM.
Local users posted roughly 11.7 tweets per minute in June (compared to 11.4 tweets per minute in May).
The day with the most local tweets posted was June 17 at 20079. On average, 16821 local tweets were posted each day (compared to 16355 in May).
Of the 156128 replies posted by local users this month, 49139 or 31.5% were to other local users.
A total of 1978 users posted 50 times or more in June. In comparison, 1600 users posted just once.

Here are the top ten most active local users (not including bots):

Here are the top ten most active local users using #yyc (not including bots):

Here are the top ten most replied to local users:

Here are the top ten most retweeted local users (by other local users):

Final Thoughts

First off, my apologies to those of you who have been waiting for a stats update! I’ve been a little behind this summer.

There was a slight increase in the number of users in June, and a slight decrease in the number of tweets posted. I think that decrease is due to two things: one less day than in May, and the large amount of downtime that Twitter experienced in June (it was their worst month since August 2009).

State of the Edmonton Twittersphere – July 2010

By Mack D. Male · August 26, 2010 at 10:00am

Welcome to the seventh State of the Edmonton Twittersphere of 2010, my look at the intersection of Twitter and Edmonton, AB. You can see last month’s stats here.

For information on the data, definitions, and other background, click here.

For July 2010:

# of local users: 8227 (an increase of 49 from June)
# of tweets by local users: 446078
# of tweets by local users containing #yeg: 32290 (7.2%)
# of tweets by local users that were replies: 155793 (34.9%)
# of tweets by local users containing links: 121874 (27.3%)
# of tweets by local users that were retweets: 19864 (4.5%)
# of tweets by local users that were twooshes: 15646 (3.5%)

Here are the numbers above in graphic form:

Here are the top clients used by local users for posting updates – UberTwitter and Twitter for iPhone continue to duke it out.

Some other interesting stats for the month:

Just over 51% of all local tweets were posted between 9 AM and 5 PM.
Local users posted roughly 10.0 tweets per minute in July (compared to 9.8 tweets per minute in June).
The day with the most local tweets posted was July 30 at 19129. On average, 14390 local tweets were posted each day (compared to 14068 in June).
Of the 155793 replies posted by local users this month, 61444 or 39.4% were to other local users.
A total of 1658 users posted 50 times or more in July. In comparison, 1164 users posted just once.

Here are the top ten most followed local users:

Here are the top ten most listed local users:

Here are the top ten most active local users (not including bots):

Here are the top ten most active local users using #yeg (not including bots):

Here are the top ten most replied to local users:

Here are the top ten most retweeted local users (by other local users):

Final Thoughts

Even though July is one of the busiest months of the year here in Edmonton, local Twitter users were still pretty active! Not many new accounts, but there was a noticeable jump in the number of updates posted. The busiest day of the month, July 30, was the last Friday of Capital EX and Taste of Edmonton, and was also the day the Eskimos snapped a four-game losing streak to win their first game of the regular season.

State of the Edmonton Twittersphere – June 2010

By Mack D. Male · August 10, 2010 at 10:00am

Welcome to the sixth State of the Edmonton Twittersphere of 2010, my look at the intersection of Twitter and Edmonton, AB. You can see last month’s stats here.

For information on the data, definitions, and other background, click here.

For June 2010:

# of local users: 8178 (an increase of 127 from May)
# of tweets by local users: 422018
# of tweets by local users containing #yeg: 29861 (7.1%)
# of tweets by local users that were replies: 148809 (35.2%)
# of tweets by local users containing links: 108225 (25.6%)
# of tweets by local users that were retweets: 21092 (5.0%)
# of tweets by local users that were twooshes: 15355 (3.6%)

Here are the numbers above in graphic form:

Here are the top clients used by local users for posting updates – UberTwitter has been overtaken!

Some other interesting stats for the month:

Just over 51% of all local tweets were posted between 9 AM and 5 PM.
Local users posted roughly 9.8 tweets per minute in June (compared to 9.6 tweets per minute in May).
The day with the most local tweets posted was June 25 at 17106. On average, 14068 local tweets were posted each day (compared to 13894 in May).
Of the 148809 replies posted by local users this month, 56284 or 37.8% were to other local users.
A total of 1631 users posted 50 times or more in June. In comparison, 1229 users posted just once.

Here are the top ten most active local users (not including bots):

Here are the top ten most active local users using #yeg (not including bots):

Here are the top ten most replied to local users:

Here are the top ten most retweeted local users (by other local users):

Final Thoughts

First off, my apologies to those of you who have been waiting for a stats update! I’ll be getting caught up over the next week or two.

State of the Calgary Twittersphere – May 2010

By Mack D. Male · June 23, 2010 at 10:30am

Welcome to the fifth State of the Calgary Twittersphere of 2010, my look at the intersection of Twitter and Calgary, AB. You can see last month’s stats here.

For information on the data, definitions, and other background, click here.

For May 2010:

# of local users: 10547 (an increase of 439 from April)
# of tweets by local users: 507017
# of tweets by local users containing #yyc: 21053 (4.1%)
# of tweets by local users that were replies: 152349 (30.0%)
# of tweets by local users containing links: 153152 (30.2%)
# of tweets by local users that were retweets: 22158 (4.4%)
# of tweets by local users that were twooshes: 16966 (3.3%)

Here are the numbers above in graphic form:

Here are the top clients used by local users for posting updates:

Some other interesting stats for the month:

Just over 48% of all local tweets were posted between 9 AM and 5 PM.
Local users posted roughly 11.4 tweets per minute in May (compared to 11.6 tweets per minute in April).
The day with the most local tweets posted was May 27 at 19816. On average, 16355 local tweets were posted each day (compared to 16661 in April).
Of the 152349 replies posted by local users this month, 47020 or 30.9% were to other local users.
A total of 1959 users posted 50 times or more in May. In comparison, 1610 users posted just once.

Here are the top ten most followed local users:

Here are the top ten most listed local users:

Here are the top ten most active local users:

Here are the top ten most active local users using #yyc:

Three are clearly bots so here are the next three:

Here are the top ten most replied to local users:

Here are the top ten most retweeted local users (by other local users):

Final Thoughts

A decent jump in users and Calgary has finally broken the 500,000 tweets per month mark! There was a noticeable increase in the number of tagged tweets this month too. As with Edmonton, the official Twitter clients are climbing the charts.