How we used a Google spreadsheet to power our election app

Last Tuesday was election night in the Cedar Valley. While we didn’t have a huge number of contested races on the ballot, we did have a mayor’s race in Waterloo and Waverly, Iowa, as well as city council races in several area towns.

As NPR’s Jeremy Bowers proudly proclaimed, election nights are exciting times for news nerds. This election, we decided to do a little bit of experimenting.

Before the election, we posted biographical information for all the candidates running in a contested race in Waterloo, Cedar Falls and Waverly. We also promoted other races in smaller towns. This gave our readers a good overview of the races on the ballot and information on the candidates. And best of all, it was all in one place.

Three reporters were responsible for getting the biographical information for each candidate and entering it online. We used a Google spreadsheet to store the information. This allowed the reporters to enter the information online themselves. I could then go into the spreadsheet, edit the text and make sure it was formatted correctly. We then used Tabletop.js to import the data into our app and Handlebars.js to template it.

The basic setup for the app is available on my Github page. This is a very similar setup mentioned in my last blog post.

Before the app went live, I exported all the data in the Google spreadsheet into JSON format using a method mentioned here. I did this for two reasons: 1) It spend up the load time of the app because browsers didn’t have to connect to the spreadsheet, download the data, format the data into JSON (which is what Tabletop.js does with the data in this app) and then display it online using templates rendered in Handlebars.js. Instead, it’s already downloaded, formatted into JSON and ready to be templated. And: 2) Tabletop.js has a bug that may cause some readers not to see any of the data at all. I wanted to avoid this problem.

The night of the election, we wanted to update the election results live. And we wanted to use the same app to display the results. Fortunately, the process was easy to do: I added new columns in our spreadsheet for vote totals and precincts reported. The new data was then pulled into our app and displayed with simple bar charts.

To display the results live, we had to ditch the exported JSON data and use the Google spreadsheet to power the app. This allowed us to update the spreadsheet and have the results display live on our website. We had one reporter at the county courthouse who punched in numbers for our Waterloo and Cedar Falls races. We had another reporter in Waverly who punched in results for races in that town. And we had another reporter in the newsroom who was monitoring the races in rural towns and giving me updated election results to enter into the spreadsheet.

The workflow worked great. While most news outlets were waiting for the county websites to update with election results, we were able to display the results right away. Unfortunately, the Black Hawk County website never wound up working on election, making our app the only place readers could go to get election results.

Our effort paid off: The app received about 11,500 pageviews, with about 10,000 of those pageviews coming the night of election (about 7,000 pageviews) and the following day. The app was more popular than any single story on our website for the month of October and November.

Reporters at the party headquarters said many of the candidates first tuned into the local television station to get election results but quickly went to our webpage when they realized we were the only ones with updated results. In fact, Waterloo’s mayor found out he won his race by looking at our website. Here’s a photo of him checking out our website on election night.

Which leads me to my last point: You’ll notice how our mayor is checking out the results on a smartphone. Our app (like all of our apps) was responsively designed, which means it looks great on mobile phones. It’s critical that news producers make sure their apps work on mobile. It’s pretty much mandatory. Because as our mayor shows, people love checking the news on their phones.

Going mobile with our news apps

It’s been about two months since we launched our crime map for the city of Waterloo and so far, the response has been overwhelmingly positive.

I’ve been proud of our fans on Facebook, in particular, who are using the map to make informed opinions on crime in the area. One of our fans actually counted every dot on the map to counter another fan’s claim that crime only happens on the east side of town. He found crime was almost equally spread across the town.

In hopes that this data will be digested by an even larger audience, I launched the mobile/tablet equivalent of the map last week. The new app is fully responsive thanks to Twitter’s Bootstrap framework. If you haven’t check out Bootstrap, you really should. It makes designing websites for all platforms — desktops, tablets, mobile phones — about as easy as its going to get.

I also went in and modified  a lot of the CSS, changing things like the width of objects on the page from pixels to percentages. This ensures that the app looks great no matter how wide the screen you’re viewing it from is.

Serving up the tablet version of the map wasn’t particularly difficult. It’s basically the same map on our site without the header and footer, which seems to load slower on iPads. It’s also designed to be flexible regardless of how wide your tablet screen is.

The mobile version was more difficult. At this time, the mobile app does not have a map of the crimes. Instead, it’s just the color charts comparing different crimes and a table of all the crime data. I stripped out the map mostly because it’s difficult to click individual points on the map on small mobile screens. But screens continue to get bigger and nicer so hopefully this won’t be a problem in the future.

One pro tip: I set the padding-right CSS property on the table of crimes to 10 percent. This gives a nice cushion to the right of the table, making it easier for people to scroll past it on smartphones with touch screens.

For this project, I went about making the mobile version the completely wrong way: I opted to create just the desktop version at first and then go back and make one for tablets and phones.

Ideally I would have done both at the same time, which is easy to do with Bootstrap. And that’s exactly what I did for another project we launched this weekend on campaign finance reports. The project exams the finance reports for candidates running in four local races. The reports are made available by the Iowa Ethics and Campaign Disclosure Board, which fortunately allows users to export the data into CSV files.

We broke down the data and created bar charts using Bootstrap to compare the figures. The framework has several bar options to easily create the bar charts, which are worth checking out. The best part is they are designed to look good on all platforms.

We also have databases of all the contributions that are searchable. This allows readers to see exactly where the money is coming from.

For this project, I created the mobile and tablet equivalents of app as I was creating the desktop version. When viewing it on a desktop computer, the app is embedded on our website so it has the election header, footer and colors. The same app was created with responsive design in mind. So if you open it on a mobile phone, the app will look just as good on a tablet or smartphone as it does on a desktop computer.

Many studies show that more and more people are getting their news on smartphones. It is imperative that we keep those readers in mind when designed full-scale apps for our websites.

Hopefully we can continue this trend at the Courier and make sure our projects are reaching our full audience.

Pension reform; lieutenant governor showdown

Wow. It’s been a busy few weeks. I’ve neglected this blog for almost two weeks, so I feel obligated to finally update. So what has been happening at the Statehouse? The better question is what hasn’t been happening…

1. The biggest story of the past two weeks is probably the massive pension reform bill that Speaker Michael Madigan and Senate President John Cullerton rammed through the General Assembly in a mere ten hours last Wednesday. Many lawmakers complained to me that the whole process went too fast, which caused them to vote against the bill. But it still passed with overwhelming majorities in both chambers:

SPRINGFIELD — New Illinois schoolteachers, university workers and other state employees would have to work longer before they retire — and possibly receive fewer benefits when they do — under a plan lawmakers approved Wednesday.

In an effort to save the state billions of dollars in pension payments in the coming decades, both the Illinois House and Senate on Wednesday approved a plan to push the retirement age for state employees up to 67.

Link.

This was huge. Many critics, including the unions, didn’t even have time to react, which I think was one of the major reasons they pushed it through so quickly. A quick stab is much quicker and cleaner than dragging it out for months, or even years.

The next day, the governor said the move would save the state billions upon billions in the coming years:

SPRINGFIELD — Gov. Pat Quinn said state pension reforms approved by lawmakers Wednesday will save the state $300 million next year and more than $100 billion in the coming decades.

“That’s not chicken feed,” Quinn said at a Capitol news conference Thursday.

Indeed. But the blow back will be huge. We’ll see if it riles the governor’s race in November.

—–

2. Speaking of November, the still-vacant lieutenant governor seat on the Democratic side will finally be filled tomorrow. I’ll be there at 11 a.m. so expect more information later. But for now, everybody is kind of holding their breath.

Who the eventual winner will be is anybody’s guess. Earlier in the week, all signs were pointing towards a female senator from the suburbs:

SPRINGFIELD – A Lake County Democrat acknowledged Tuesday that a top aide to Gov. Pat Quinn contacted her about joining the race for Illinois lieutenant governor.Although Quinn has refused to indicate his choice for a running mate, state Sen. Susan Garrett said the governor’s chief of staff, Jerry Stermer, reached out to her before she applied to become the party’s nominee.

Story courtesy of myself.

That quickly changed, however, largely because Garrett has been on record as opposing Quinn’s tax increase and has refused to concede her position. Her convictions, however, eventually led to her downfall. Now, the governor is looking at another female with a good name but little legislative experience:

Gov. Pat Quinn is expected to announce today his preference for a running mate in the general election, and sources told The Southern Illinoisan Sheila Simon is Quinn’s choice.

Link.

Sheila is the daughter of Democratic icon Paul Simon, so she has a good name. But she doesn’t have legislative experience, and her only noble political race was a failure. In 2007, she lost a bid for mayor to Brad Cole, even though she had statewide support.

But regardless of who he wants, Quinn doesn’t have an official say in the matter. Instead, that is left up to the Democratic central committee. And many of the members have indicated they would support state Rep. Art Turner, who finished second in the race for lieutenant governor. But, at the same time, he lost to a Chicago pawnbroker with a very, very ugly past.

If the committee doesn’t pick Turner, the black community will be pissed:

CHICAGO (AP) – Word that Gov. Pat Quinn planned to endorse Sheila Simon for lieutenant governor prompted warnings Friday that he risks angering black voters and weakening the Democratic ticket in November.

Rep. Art Turner, the second-place finisher in the Democratic primary for lieutenant governor, said he will continue fighting for the job even if Quinn prefers someone else.

Link.

All this controversy surrounding an office with few official responsibilities. What a mess.

The result will be announced tomorrow morning. But don’t expect smooth sailing from there…

Post-election analysis stories

While the Illinois primary season officially ended a week ago, neither party knows what their ticket is going to look like in the fall. For one, the Democrats are missing a lieutenant governor. The party is still hurting because their former candidate, Scott Lee Cohen, was engulfed in controversy due to revelations into his past, which included horrific tales of violence, sexual misconduct and steroid use. How much of it is true is unknown. But it was enough for him to bow out of the race on Super Bowl Sunday, just five days after he was nominated. The Dems are surely relieved he stepped down, but they now face a new problem: finding a replacement. Rumors are flying everywhere, but it may be a while before they officially name someone.

One would think this controversy would be the perfect battle cry for Republicans to rally behind their candidate for governor. Just one problem: they don’t have one. State Sen. Bill Brady, of Bloomington, is currently in the lead, but it’s so slim, his opponent, State Sen. Kirk Dillard, of Hinsdale, is refusing to give up before all of the provisional and absentee votes are counted. This leaves their ticket even more in limbo than their opponents on the left.

1. With that in mind, it was interesting to see former Gov. Jim Edgar and David Yepsen, director of the Paul Simon Public Policy Institute at Southern Illinois University in Carbondale, give their post-election analysis before a number of movers and shakers Thursday afternoon at a luncheon sponsored by UIS. It was an unprecedented election with unprecedented results. And both acknowledged these points.

The one thing Edgar said that I thought was most interesting was a quick line about how he thought Brady’s 400-vote lead would hold up. Of course, Dillard was Edgar’s chief of staff for a while and Dillard diligently touted his experience under the Edgar administration every day, it seemed, of the election. The fact that he was calling such a close race caught me off guard and eventually became my story:

“I’m saying this as if the 406 votes are going to hold up. I suspect it will,” Edgar said.

Edgar said the key to the Republican race was three candidates from the Chicago area — Dillard, former GOP chairman Andy McKenna and former Attorney General Jim Ryan — split the city vote. All three candidates finished within striking distance of Brady.

“The key factor was geography. It wasn’t ideology,” Edgar said.

A number of media outlets picked up on the story as well, including Capitol Fax, Progress Illinois, Illinois Observer and Illinois Review. It will be interesting to see if Edgar’s prediction comes true in the coming weeks.

2. The governor’s race wasn’t the only one addressed at the luncheon, however. The U.S. Senate race also dominated the conversation and for good reason. This year’s race between Treasurer Alexi Giannoulias and Congressman Mark Kirk should be a barn burner. National Republicans are giddy at the chance of winning Obama’s old seat, while Democrats are poised not to be embarrassed in a state that has been predominately blue for quite some time.

Anways, a few quotes from Edgar and Yepsen made it into another story my colleague Mike was working on. Here is a quick look:

“The Republicans have probably one of the best chances in the United States of picking up a Democratic-held Senate seat,” Edgar said of the Illinois race. “Kind of the icing on the cake is this is a seat that Barack Obama held.”

This race will be watched closely, not only in Illinois but across the nation. Stay tuned…

3. Lastly, one other quick point about the primary election: voter turnout was dismal. Officials pinned the voting public at less than 30 percent of registered voters, which is lower than usual. Primary turnout is usually horrendous but many lawmakers blamed the February primary (the earliest in the nation) for this year’s low numbers. I talked to a number of local Lee lawmakers and did a quick story:

State Rep. Dan Brady, R-Bloomington, agrees the primary is too early and also is co-sponsoring its move to June. In his district, McLean County reported 22 percent of registered voters made their voices heard Tuesday night, which is a slight increase from the 2006 primary when 20 percent voted. In 2002, turnout was 30 percent.

“I think a big part of it was people not being prepared and ready to a deal with a primary election at this point in time,” he said. “It caught them off guard.”

Moving the primary date is going to be a hot issue this session, especially for the minority party who feel the early primary unfairly gives incumbents an edge. I’ll keep you posted.

Well I’m running out of wind once again, so I’m going to stop this post now. What a doozy! I hope you enjoyed every last word…

Image posted by Flickr user myoldpostcards. It is used under a Creative Commons license.

Election night coverage

I kind of all ready gave a run down of my election night last Tuesday….But here are links to the two stories I wrote:

1. Shimkus wins GOP primary in 19th Congressional District
2. Hirner claims win in Democratic primary, will face Schock in November

These stories were all about speed. Because we were on such tight deadlines, we had to pump these out fast. So I made a few quick calls, put the stories up and updated them as new result numbers come in.

These races may not have gotten the attention that many of the other nes did but I glad I got some real-time election experience under my belt.

What a night

Wow. Tonight’s primaries were unprecedented, unpredictable and pretty much everything in between. As it stands, Bill Brady is leading in the GOP governor primary, while Pat Quinn leads for the Democrats. A total of 99 percent of the precincts are in. But Republican Kirk Dillard and Democrat Dan Hynes are not far behind and both vow to fight, fight, fight. This could be settled in court and take months. The race for Democratic comptroller is also coming down to the wire. State Rep. David Miller is in the lead at the moment, but Raja Krishnamoorthi also vows to keep the battle going. The races for lieutenant governor are also–you guessed it–strikingly close. While it’s possible these races could be decided in the next few hours (it’s all ready 1:15 a.m. as it is), I highly doubt it.

Interestingly enough, the Republican party has a unity breakfast tomorrow. That should be interesting…

Anyways, I spent the night covering two other races: the 18th Congressional District and the 19th Congressional District. Things really picked up around 8:30 p.m. because our first deadline was an hour later. We got our stories up and then had to quickly update them for our 10:30 p.m. deadline. After that, we had to update the stories as the final results came in. Fortunately, my races were decided fairly early, at least compared to the governor races.

Between phone calls and frenzied typing, I helped tweet on behalf of Lee Enterprises (@Illinois_Stage). Between Mike and I, we put up a ton of tweets, got in some political conversations with locals and picked up 15 new followers! And many of our newspapers did a great job promoting the feed on their web page. The Pantagraph, for instance, put a nice, colorful teaser on the front page, under the main articles:

The Southern embeded the feed on their front page, just off to the right of the lead articles:

The Herald & Review also embeded our feed on their election page, but that has since been taken down.

We followed the race closely by basically clicking refresh on the result pages for hours on end. My eyes were in pain and honestly, it’s a miracle I’m staring at the computer right now. We finally left at 12:30 p.m., a few hours after our deadlines. Much uncertainty, however, still remains in some of the night’s most important races.

Well I’m running out of steam, so I’m going to end while I’m ahead. It was a fun night but also stressful. But I made it out alive and will live to see another day!

Be on the look out for more updates to come in the next few days…

Today is the Illinois primary!

Today is Super Tuesday in Illinois, meaning 20 some-odd percent of eligible Illinois voters will be deciding which candidates for governor, lieutenant governor, treasurer, comptroller, U.S. Senate and other races will be representing each side of the isle in November’s general election. Across the board, this is going to be one of the most contested series of races in recent history, in part because the list of open races and vying candidates is so long. But at the same time, turnout has been very low across the state since the polling places opened at 6 a.m. It should be interesting to see if things pick up around 5 p.m. when most Illinoisans are getting off work.

My day hasn’t really started yet because the polls don’t close until 7 p.m. I’m not scheduled to work until 5 p.m. As we get closer to 7 p.m., be sure to follow Lee Enterprises on Twitter @Illinois_Stage. Both Mike and I will be tweeting throughout the night as the results come in. For tomorrow’s papers, I will be reporting on 18th Congressional District and 19th Congressional District, which means things will probably get real busy around 9 p.m. when races are being called. I will have a full update in the next day or two.

A few other Public Affairs Reporting interns are going up to Chicago to cover the races, including Tim Magaw and Chase Castle from the Daily Herald and Steve Contorno from the Chicago Sun-Times. I’m sure they are going to have some stories to tell when they get back tomorrow but for now it’s all (or mostly, at least) business.

While this isn’t my first time covering an election (I covered city elections while working at the Daily Eastern News at EIU), it will be my first Congressional election. Hopefully everything goes according to plan, and I’m not working too late…The House is scheduled to be in session tomorrow so there will be no rest for the weary.

And, finally: go out and vote! With so many contested elections, you could make a difference.

Money continues to flow in as primary nears

With less than 48 hours to go before Tuesday’s primary, candidates from across the political spectrum are putting big bucks into their campaigns in hopes for a last minute surge to victory. Jason Plummer, GOP candidate for lieutenant governor, is both pumping his own money into his campaign and relying on a lot of help from his family-owned business. Their combined investment is worth more than $1 million. Andy McKenna, Republican candidate for governor, received another loan last week from his significant other, raising their personal financial commitment to more than $2.5 million. Take a look:

– Image taken from the State Board of Election’s web site.

Even Justin Oberman, Democratic candidate for Treasurer, is getting in on the act by pitching in more than $250, 000 in the last week and a half. Here is the 411:

Democratic candidate Justin Oberman has loaned his campaign $258,000 in the past week and a half as he tries to beat Robin Kelly, a top aide in the treasurer’s office.

Oberman, president of a Chicago investment firm, has been using the money for a new television ad and a number of radio spots, which are airing across the state.

Oberman said he loaned his campaign more than $200,000 to make sure his message reaches a statewide audience, noting that campaigning is an “expensive undertaking.”

His opponent, Kelly, is relying more heavily on small donations. Her campaign noted that she has received donations from more than 1,100 contributors, which beats out Oberman. This shows they have power in numbers. It also ensures greater independence, they contend, if she is elected.

We’ll see in a few days which strategy works. For now, take a look at Oberman’s big media buy-in:

Kelly doesn’t have a television commercial but does have this radio ad. Fortunately, her staff put it on YouTube with corresponding pictures. Take a look:

Tuesday should be interesting on all fronts. A number of races are coming down to the wire, and the time between now and then is going to be very hectic. Don’t be surprised if you get an automated phone call from one of the candidates listed above or any other lawmaker for that matter. It may be best to let the phone ring…

Regardless, be sure get out the vote on Tuesday! In most of Illinois’ big elections, your vote will truly matter.

More election coverage

On Friday, our Statehouse bureau unleashed seven (!) election stories for the weekend. I had my hand in two of them, both related to Congressional elections. And both feature long-shots, either in the general or primary election. Let’s take a quick look:

1. In the 19th Congressional District, incumbent John Shimkus faces his first primary opponent in 14 years. That is quite an impressive run. Veterinarian Mike Firsching of Moro is hoping to do the unpredictable and unseat Shimkus for the first time since he was elected. However, Shimkus is severely out-fundraising him. According to federal records released last week, Shimkus has raised $211,000 between October and mid-January. Firsching hasn’t raised close to that, only saying he’s pitched in $3,000 of his own money.

Here is a quick look at the story:

Firsching said he ran, in part, because he believes the federal government has too much power. He pointed to the Department of Education as an example and said Washington shouldn’t be control of local school districts. As a student of the Constitution for the past four years, he said he would vote against proposals that expand the government’s powers beyond those laid out in the Constitution.

He also cited dysfunction in Congress and a lack of personal freedoms as reasons for running against Shimkus in the southern Illinois district.

And here is the link.

2. In the 18th Congressional District, incumbent Aaron Schock does not face a primary candidate. So instead, I covered the Democratic race, which features Deirdre “DK” Hirner of Springfield and Carl Ray of Washington. While the election could be interesting, the winner will likely have an uphill battle because the central Illinois district hasn’t elected a Democrat for more than 90 years. Think about that for a second: 90 years! Wow. Now that is a strongly GOP district.

But getting back to the Democratic candidates, here is a look at the story I put together for the weekend:

Hirner was critical of Schock for voting against the stimulus package, health care legislation and the Lilly Ledbetter Fair Play Act, which gives workers more time to take cases concerning pay discrimination to court.

“The 18th district deserves someone who will work for them in Washington,” she said.

Ray was upset Schock voted against the State Children’s Health Insurance Program, known as SCHIP, which provides federal matching funds to states for health care coverage of children. The measure was vetoed by President George W. Bush twice, although it eventually passed and later was expanded by President Obama.

In fact, Schock’s “no” vote was the tipping point for Ray to enter the race. He said the vote hit home because he has an autistic son. He was born around the same time his wife became very sick, which raised the cost of health care for the family.

And the link.

And that is it for this election update. Expect coverage from these races and more as the election nears.

And remember, get out the vote (in just a week)!

Scott Brown’s victory and Groundhog Day (the movie)

It should be no surprise that the Illinois GOP has been overflowing with excitement after Scott Brown’s unlikely victory in Massachusetts. The state, after all, hasn’t elected a Republican to Senate for more than 35 years. Here in Illinois, many candidates and party affiliates are hoping the seating of Brown will signal a national Democratic dethroning come November. As a result, the list of local GOPers lining up to congratulate Brown grew long last night as the election results came in.

Mark Kirk, a candidate for U.S. Senate, for instance, used the opportunity to solicit supporters. Fellow Senate candidate Patrick Hughes tweeted about the victory, as did Bill Brady and Jim Ryan, candidates for governor. Republicans Kirk Dillard and Dan Proft went a step further and extended their congratulations in an official statement. Illinois Republican Party Chairman Pat Brady did as well, proclaiming loud and clear that “Illinois is next.” My bureau chief, consequently, wrote a story on the press release, as many media outlets did.

Adam Andrzejewski, the fifth candidate in the governor’s race to sound off on the Massachusetts’ election, went the furthest by actually releasing a video on YouTube congratulating Brown. Check it out:

It adds a more personal touch, I guess.

But the most bizarre response I saw–by far–came from John Arrington, a little-know candidate for U.S. Senate. In his press release congratulating Brown, he compared the movie Groundhog Day to Illinois. You’ll have to see this to believe it:

If you recall the movie Ground Hog day, Bill Murray had to relive that day over and over again until he got his life right.  Are we going to continue to relive the past here Illinois by electing the same corrupt, self serving, or unprincipled politicians? Or are we finally going to elect a principled servant of the people, who will go to Washington and defend the Constitution and move us past the reliving of Ground Hog day?

Groundhog Day, by the way, is two words, not three. But all kidding aside, what the hell?

Very usual…