How can we be better Orwellian taskmasters?
November 1, 2010 4:09 PM Subscribe
How do websites or CRM tools track and report on user metrics? What are the best methods for summarizing this sort of data?
I work for a company that hosts a website where a certain number of users login to provide services for a certain number of customers. It's probably best to think of it as something like an online tech support call center. Anywhere between 10 to 50 techs login and can either be available for a call, occupied by a call, logged in but unavailable (ie. on reserve, break), or logged out. Any number of users can login, create an account and chat with one of the techs that specializes in their problem.
The underlying database has a log table that tracks events like when individuals log in, log out, change their availability state and/or get into and out of a call. It's fine for getting a transcript of a particular individual's history and our clients, logically, are very interested in this data, especially with respect to how it can be used to predict capacity (ie. are enough techs available at 11am? are too many techs online at 3pm?). However it's surprisingly hard to summarize such state transitions since techs don't have a specific shift per se and can drop in and out of availability at a moment's notice. The data also just tracks when events happened, and we'd have to do math to identify duration between events.
I keep thinking that there has to be an easy way to summarize this stuff in a materialized database view, but get bogged down on the details of what that view should be; while simultaneously worrying about writing something that is sufficiently detailed but doesn't crush the database server whenever it recompiles.
Should we be slicing time in five minute increments and create a view that just reports on who was available at 11:00 am, 11:05, etc? Should we just be doing session based entries, with just three columns of user_id, availability_start, availability_end?
This isn't a new problem, so pointers to sites that basically say, "these are the approaches that have worked for some folks" are also appreciated.
I work for a company that hosts a website where a certain number of users login to provide services for a certain number of customers. It's probably best to think of it as something like an online tech support call center. Anywhere between 10 to 50 techs login and can either be available for a call, occupied by a call, logged in but unavailable (ie. on reserve, break), or logged out. Any number of users can login, create an account and chat with one of the techs that specializes in their problem.
The underlying database has a log table that tracks events like when individuals log in, log out, change their availability state and/or get into and out of a call. It's fine for getting a transcript of a particular individual's history and our clients, logically, are very interested in this data, especially with respect to how it can be used to predict capacity (ie. are enough techs available at 11am? are too many techs online at 3pm?). However it's surprisingly hard to summarize such state transitions since techs don't have a specific shift per se and can drop in and out of availability at a moment's notice. The data also just tracks when events happened, and we'd have to do math to identify duration between events.
I keep thinking that there has to be an easy way to summarize this stuff in a materialized database view, but get bogged down on the details of what that view should be; while simultaneously worrying about writing something that is sufficiently detailed but doesn't crush the database server whenever it recompiles.
Should we be slicing time in five minute increments and create a view that just reports on who was available at 11:00 am, 11:05, etc? Should we just be doing session based entries, with just three columns of user_id, availability_start, availability_end?
This isn't a new problem, so pointers to sites that basically say, "these are the approaches that have worked for some folks" are also appreciated.
« Older Travel to Paris, France as a Bahamian citizen: How... | "You can have any color telephone you want, Ma'am... Newer »
This thread is closed to new comments.