Medallia Blog

Medallia has, as part of its product portfolio, an advanced data analysis tool which is the main interface we provide our customers for looking at the data we collect for them. This tool is a web-based application and can answer questions such as “show me, for each question asked, the percentage of females who gave me a top 10% score grouped by income level for the past quarter, and show the percent change since the same quarter of last year” in less than a second even when the number of surveys are in the millions. “Yeah, yeah, am I sure it makes coffee as well” you are probably thinking, but this is actually important for what I am about to explain.

Since we provide a web application to our customers that we host ourselves we are naturally interested in how it is being used. A while ago one of our account managers asked me to compile a report for her on how many times people from a specific client had logged in over the last week, and I quickly realized that this would not be the last such request. As much as I enjoy grepping through logfiles (or even writing a small perl script to do it for me) I would much rather spend my time developing new features, so then I got an idea. Since we already have an advanced reporting application that churns through millions of records in less than a second while making coffee, why not try to use it to do our usage statistics as well? How much work would it take? Not much as it turns out!

First I had to decide what kind of statistics I wanted to collect, and came up with this list:

• Web browser
• Operating system
• Screen resolution
• Color Depth
• SSL used
• Cookie support
• JavaScript support
• Flash version
• IP address logged in from
• Number of views for each page
• Average and max time looked at each page
• Average and max time to generate each page

So how much work did it actually take? First I had to write a User-Agent parser since I could not find one, which required about 85 lines plus 50 lines for the unit test (which is basically just a list of lots of User-Agent strings taken from Wikipedia). The JavaScript for determining screen resolution, color depth and Flash version came out to about 15 lines, while the rest of the information was already available. But how much code did it take to integrate all this into the reporting application? About 200 lines – the whole thing took about half a day for the initial version (I later added asynchronous committing to the database since the user should not have to wait for this, which added about 100 lines).

So now I can produce reports such as:

• the number of logins using Firefox 1.5 or higher grouped by the percentage using each operating system over the last 6 months, and the change since the 6 months before that
• a graphical view of the browser distribution grouped by month over the last 12 months
• Who spent at least one minute looking at the Profiler report last week
• How much the different parts of our application are being used, and which type of user (e.g. corporate, region manager, individual property manager) are using the different reports

These reports can be quite useful in making business decisions. For example, does enough of our user base have Flash enabled that we could use it to play back voice files, or is a browser used enough that we need to have it be one of the browsers we QA our site with?

I can also produce nice graphs like this one, which shows the total number of logins (y-axis scale has been omitted to protect the innocent):

At the start of this post I mentioned that the system is designed to handle millions of records while still doing real-time calculations, and this quickly becomes important since we can expect thousands or even tens of thousands of logins as we scale the system up. Fortunately, even 50.000 logins per month over the next 5 years is only a total of 3m logins, so scaling should not be an issue.

So how come that a reporting application designed for people taking surveys could be made to do all kinds of statistics on login sessions in half a day with only a few hundred lines of code? Good abstractions and reusable code. We already had a unit test which created some questions, created the possible answers, created a record with some made-up answers, injected it into the OLAP engine, did some calculations and finally checked the result. Most of the time was thus spend on actually collecting the required information, and it was straight forward to adapt the code from the unit test to create the data structures on-the-fly and injecting the data. The reporting application itself is completely configurable (since we need to customize it for each industry vertical as well as different clients), and now the account managers can simply look at the usage statistics for themselves. Actually, even our clients can use it, thus offloading our account managers. And I had much more fun than I would have had tweaking regular expressions in perl (fun as that is).