In my not so humble opinion generating a graph like this from a database shouldn't require a 70 line ruby script!

Presentation created for June 30, 2011 Vancouver Ruby / Rails meetup (flickr set, slideshare, pdf)

See my Better Support Living through Software for the full details

And just for fun here are the flickr pics of the slides:

I. Better Living

II. Just Say No

III.We Can Do This

IV. Support Comes to You!

V. Fork this!

Manual Support Forum browsing is soooo 1990s and a waste of support folks' time aka my Get Satisfaction Thunderbird API ruby and Mongo DB side project finally has born fruit :-) ! Seriously if you are manually searching your support forums and don't have customized support querying and reporting  so vital, can't miss stuff comes to your support contributors via email (or some other semi-automated method like RSS or whatever) then you are doing support wrong.

I wrote a Ruby script to read via Get Satisfaction pseudo-REST API all the Thunderbird Support Topics into a MongoDB and wrote a ruby script to generate  a custom email support report  so cool, relevant, urgent stuff comes to me instead of me having to surf the forums.

Not only that I wrote a brute force search engine that allows searching the fulltext of all 40000 Get Satisfaction topics and tags via regexes.

Even cooler: Anybody who uses Get Satisfaction for support can use my code!!!!!!! All you have to do is:

1. change "mozilla_messaging" to your Get Satisfaction company name
2. change "mozilla_thunderbird" to your product name
3. configure a gmail account config file called email.conf with 3 lines:
1. from_address
2. to_address
3. p for your password

Finally, here's the README which serves as the documentation

(since this is a side project, the README is shall we say lacking :-); I hope to somehow get this into the SUMO project so that all support contributors not just me can use it; to this end I am writing a spec for this, fingers crossed)

Roland's utilities for Get Satisfaction Metrics and other GS fun stuff

• hardcoded for now for the Thunderbird product i.e. getsatisfaction.com/mozilla_message/products/thunderbird
• easily hackable for any other product
• forgive the barebones documentation for this project :-) !
• HELP WANTED: would love for a real developer to fix this code and enhance it
• email rtanglao AT mozilla.com if you are interested

Prerequisites:

• requires a MongoDB running on local host with a database called "gs"
• and with collection in the gs database called "topics"

Usage:

Update the Mongo - ALWAYS do this first before running anything else!

./mongoUpdateSlurpGS.rb 2011 6 16 2011 6 19 2>mongoslurp.16-19june2011.818pm.stderr.txt & 

Daily email metrics (I run this twice a day, typically 9a.m. and 4p.m. Pacific)

./emailDailyMetrics.rb & 

Weekly Metrics to CSV files and stdout (run once a week to generate support metrics for the Thunderbird Project Call)

./weeklyMetrics.rb 2011 6 13 2011 6 19 2>13-19june2011.stderr.txt 1>13-19june2011.stdout.txt 

Search the Thunderbird Get Satisfaction MongoDB for regexes in the fulltext (title+content+replies) & tags

e.g. for Thunderbird 5 here's a sample search ("-r" is a comma separated list of regexes to search the fulltext for and "-s" is a comma separated list of regexes to search tags for)

./bruteforceSearch.rb 2011 6 1 2011 6 30 2>19june2011.tb5b1.bfs.stderr.1020pm.txt 1>19june2011.tb5b1.bfs.stdout.1020pm.txt -r tb5, "tb 5","thunderbird5","thunderbird 5",beta -s "tb 5",tb5,thunderbird5,"thunderbird 5","beta feedback",50,tb5,"tb 5",beta 

I have a mongodb  database created by my mongoUpdateSlurpGS.rb Ruby script with Thunderbird Get Satisfaction support topics from July 20, 2009 until the present (i.e. roughly 40000 topics) and I would like to know for a given time period how many support topics were created or modified. To calculate this, I wrote a second ruby script called alltopicsCreatedOrUpdated.rb (and the code is embedded below). My question is: is there really a need for a ruby script here.? Could the nested for "each" loop in alltopicsCreatedOrUpdated.rb  be replaced by a couple of mongodb queries using the mongodb command line interface?

Since the Get Satisfaction developer docs are shall we say in need of some love, here's an ruby irb session (exercise for the reader, convert to a python or lisp REPL session :-) !) that illustrates how to find all Get Satisfaction topics tagged Android (to play along and find the get satisfaction topics tagged "android" for your company, make sure you have ruby installed on Linux or Mac OS X  along with the json ruby gem and substitute your company name for "mozilla_messaging"):

The cult of real-time metrics (in addition to big data) in Silicon Valley-style (Silicon Valley is now a state of mind and no longer just a place) startups now. Graph crucial and key metrics all the time and all in real-time ! I jest but it is valuable! Heretofore real-time metrics graphing has just been too hard unless you have the talent and experience of firms like Etsy to put the necessary infrastructure (graphing software, metrics collecting software, etc) in place.

However that difficulty is an opportunity that will be rapidly addressed (because real-time graphing is an itch that can be rapidly scratched by developers and it's helpful to companies' bottom line) and you can start to see this being addressed in projects like Simulchart.

In 1 hour I was able to get SimulChart graphing every 10 minutes the number of new Get Satisfaction Thunderbird  topics using some node.js code (this is not a business critical real-time metric; i have plans for other real-time Thunderbird metrics, stay tuned :-) !). That's the future! 1 hour to get a business critical metric graphed in real time without having to stand up home grown infrastructure like Etsy and flickr did.

If anybody knows of any other real time open source and/or free graphing toolkits or services, let me know!

Got some data that you want to persist and manipulate but you are not sure what to do with it i.e. you are in exploratory or "side project" mode? Then MongoDB is a fine choice in 2011. Easy to get the data in and out and then when you have figured out what you want to do when you grow up :-) you can store it in a "real" database as well e.g. PostgreSQL. e.g. If you want to get your data out of Get Satisfaction or any other system with an API for backup and other purposes, check out my "hacked in an hour or two but I think it works", mongoUpdateSlurpGS ruby script. I used it to slurp in 20000 Get Satisfaction Thunderbird Topics and their associated replies and tags. If you want to do the same for your Get Satisfaction community, it's trivial to do: all you need is about 1 hour of a ruby (or other language,the code is pretty elementary) developer's time and some time to run the script.

Jonathan aka "protz" built on top of my idea of a real-time support dashboard for Thunderbird powered by the GetSatisfaction API (tech: JavaScript, CSS, HTML, JQUERY, protvis) and made it awesome (merci beaucoup Jonathan!) with some fab styling from Andy.

It's so 1.0 :-) i.e. it's early days but it delivers on the promise of showing what's happening with the Get Satisfaction forum. Check it on gitbhub or if you running Firefox 3.6 or above click on the following link to run it from my dropbox (yay for dropbox deployment!):

• Mozilla Thunderbird Real Time Support Dashboard

Help Wanted

The code is on github. Fork away, send us a pull request, you know the drill :-)! Please make it better e.g. improve the Javascript and improve the CSS,  add more cool visualizations, adapt it to your getsatisfaction community etc

Current Features as I write this blog post:

1. Today's hot keywords (a bit mislabelled: should be today's hot email providers)
2. Today's support pattern, a bar graph showing when folks have filed support topics and replies over the last calendar day
3. 5 most active topics in the last 24 hours
4. 5 latest created support topics
5. 5 random topics from today
6. 5 most recently solved topics
7. 5 latest active solved topics (since we can't really close a topic, people can still comment on a solved topic so it's good to know this; this is a major Get Satisfaction gap IMHO, we need a a *real* close feature
8. Real-time tag cloud - neat but not really helpful since most of the tags users use are not helpful
9. applause feature when a support topic is marked close i.e. it plays an applause sound!!!!!
10. Real-time - updated every two minutes!

Current bugs / feature requests

1. make it work in Safari, Chrome and Opera (sorry IE :-) actually I am not sorry LOL!) - this is a small matter of javascript modification to make it work on the major browser JavaScript implementations
2. improve the styling
3. Add avatars of the folks who solved support topics
4. The Get Satisfaction API could be improved with better documentation as well as support for real-time stats. Would be great for example to have an API call for the current calendar's day's solved topics, current contributors who solved topics etc instead of having to compute this (this is thinking out loud, haven't thought of this too much will blog about it more in the future)
5. Deploy to github static pages instead of dropbox!

UPDATE November 2011:

The code has been dramatically re-factored so i deleted it from the gist. Please use the revised code at:
https://github.com/rtanglao/momogs/blob/master/emailReport-m.rb

For my technical support lead job at Mozilla Messaging, I continue to try to find the best way to sort and sift and help out folks who need support and identify the hot issues. We currently use Get Satisfaction for Thunderbird support and I subscribe via email to everything but it's really difficult to read through the over 200 emails a day that Get Satisfaction sends

So I developed over the last 9 months a series of Ruby scripts to see the most active support topics (getactive.rb),  the contributors who are actually being marked by the community of users as solving a support topic (getcontributors.rb), who's doing the most replies (gethappykludged.rb) and pick out some random support topics (getrandom.rb)

Up until last week I have been using these scripts on a manual basis but not as consistently as I would like since it's pain to run all four manually.

To make a single email report that ties together everything in one nice summary, last week I finally took 3 hours and wrote emailReport.rb (code after the jump). It's crude but it works and is a useful report that I run every day now for the previous day (e.g. on Monday morning, I run it for Sunday and Saturday). It runs all the above mentioned scripts and then emails a summary email:

Here's an example of how you invoke it:

(for August 2, 2010)

./emailReport.rb 2010 8 2 2010 8 2 2>emailreport.20100802.20100802.stderr 1>emailreport.2010082002.20100802.stdout

And here's a sample email:

From: xxxx@gmail.com
To: yyyy@zzzzzo.com
subject: MoMo Support Report FROM: 2010.8.2 TO: 2010.8.2
Date: Mon, 02 Aug 2010 23:31:16 -0700

Get Satisfaction Top 5 active:
5,http://getsatisfaction.com/mozilla_messaging/topics/how_do_i_export_folders_as_mbox_files_thanks_chris
5,http://getsatisfaction.com/mozilla_messaging/topics/migration_of_thunderbird_data_from_old_to_new_computer
4,http://getsatisfaction.com/mozilla_messaging/topics/delivering_mail_98_thunderbird_problem
4,http://getsatisfaction.com/mozilla_messaging/topics/from_thunderbird_to_outlook_how
4,http://getsatisfaction.com/mozilla_messaging/topics/cannot_start_thunderbird_already_runnning

Get Satisfaction Contributors:


Top 10 Get Satisfaction Repliers:
2, cam_1319937
3, simonpaquet
3, ankitgupta1980
3, davro
3, pat_drummond
3, kent_james
4, bhuvnesh_thakar
5, wayne_mery
9, archaeopteryx
30, michael_a_pasek


5 Random Get Satisfaction Topics:
http://getsatisfaction.com/mozilla_messaging/topics/crashing_after_start_up
http://getsatisfaction.com/mozilla_messaging/topics/problems_editing_an_email_to_be_forwarded
http://getsatisfaction.com/mozilla_messaging/topics/dmg_file_whats_this
http://getsatisfaction.com/mozilla_messaging/topics/want_to_use_thunderbird_for_mass_mailing
http://getsatisfaction.com/mozilla_messaging/topics/pasting_images_takes_minutes

What I did well

• It works and when it doesn't I can check stderr for diagnostics and usually quickly figure out what went wrong (Get Satisfaction has a known bug on topics that have URLS that begin with numbers and also JSON can't cope when folks paste binary data into support topics; fortunately both cases are rare -:-) !)

What could be improved

• The JSON API is called on the same data four times, once for each script (i.e. I don't store any of the GS data) so it takes 5-10 minutes to run emailReport.rb!
• It would be more efficient to just read the data into a database, e.g. couchdb and then have the four scripts read from the database
• Ruby is not an excellent choice for Windows users; would be better to write this in JavaScript and then anybody (i.e. other support contributors not just folks on Mac OS X or Linux) could use it
• Badly need to way to extract the GS conversations I am involved in and highlight the top 5
• Although a separate can of worms :-), I would love to have a really great search engine that works on the GS support data; unfortunately the GS management view search doesn't work so well.

Help Wanted

I'd love some help :-) (what's in it for you! Open Source Whuffie, fame and adventure :-) !!!)   in the following areas:

• Convert to JavaScript and to a web app so all can use it :-) !
• Put the GS support data into a JSON friendly database like CouchDB

The Code

The code has been dramatically re-factored so i deleted it from the gist. Please use the revised code at:
https://github.com/rtanglao/momogs/blob/master/emailReport-m.rb

A belated gig change post: Since July 21, 2009, I have been Technical Support Lead at Mozilla Messaging. It's been quite the wild ride. I have been immersed in Mozilla Messaging's product, the open source Thunderbird  Email client (in particular getting ready for Thunderbird 3 currently scheduled for November which has a plethora of improvements including the super spiffy global search) as well as tweaking the Mozilla Messaging Implementation of Get Satisfaction for Thunderbird support as well as starting the deployment of a Knowledge Base for Thunderbird. Busy times! Good times!

Sidenote: I'd completely forgotten the intensity of focus that's needed to ship a big product like Thunderbird; couple that with the knowledge that millions of people will be using it and with the history of Mozilla makes for a unique experience for me because the Unix network management software I worked on at Nortel was big but only used by 100s of users and even then by all reports most of the features were unused, contrast that with Thunderbird where seemingly every feature no matter how obscure is used by lots of folks!