@FIRST_LIGHT

I have three sons in Scouting and I serve as a Scoutmaster. Every year we raise money for the organization by hauling discarded Christmas trees to the local transfer station for $10 per tree. Over two weekends, we pick up about 300 trees, raising about $3000. That’s enough to take some of the sting out of summer camp expenses for cash-strapped families.

I’ve helped the project for several years and I’m always amazed at the complexity of pulling it off. We have to advertise through email, phone, newspapers, flyers, and sandwich boards. Then we have to accurately collect addresses of people who want their trees removed, line up trucks and trailers, make arrangements with the transfer station, generate route/maps, and safely pickup the trees. About 40 Scouts and their parents are involved. It’s a whole enterprise that we set up and shut down over six week period starting in early December and ending the second week of January.

One of the hairiest aspects of the project has been IT to support it. We need information technology that is free of charge, usable by everyone in our organization, and does not require custom programming. This year we used a variety of free Google tools to do the job, and I think it worked out quite well. Here’s the run down.

We used Blogger to set up a simple blog for the project. You can see the blog here. The blog allowed us to have a simple web presence that we could reference in our flyers, sandwich boards, and email blasts to customers from prior years.

We set up a Gmail account to send out our email blasts to customers from prior years and to receive requests for pick up. We’ve used Yahoo before, but this year we opted for Gmail in order to get integration with Google Docs.

We used Google GrandCentral, currently in beta, to provide a voicemail box that people could call to ask for their tree to be picked up. This account was connected to the Gmail account so that we got email notifications whenever a voicemail was received. GrandCentral also provided the “Call Me” button that we used on our blog. I don’t know if anyone actually used it or not, but I thought it was cool.

We used Google Docs spreadsheets as the central repository of customer requests for pick up. Several volunteers had the login credentials for the inbox. They would log in daily to transcribe requests from email or voicemail into the spreadsheet. We had separate tabs for each pick up day. This worked really well as many of our volunteers were traveling for the holidays and were able to log in and keep on top of the inbox from any browser. In prior years we had to have multiple spreadsheets and then merge them, an error-prone task.

Once all the requests were received, one of our volunteers used Delorme mapping software to import the addresses in the spreadsheets and plot them on printed maps. We didn’t take the extra step of producing actual routing directions. I’m not aware of an online tool that can do multi-point routing from uploaded data and produce a large format (11X17) printable map at then end. If you know of a free online service that does this, please leave a comment.

Overall, I’m very happy with how we were able to cobble together a useful system from all these free online tools. We didn’t have the time or budget to get much more sophisticated than this, but we wanted to be more efficient than using pen and paper. Mission accomplished.