canmove, Confirmed users
382
edits
Support/MetricsDashboardPRD/MetricsCurrent: Difference between revisions
Support/MetricsDashboardPRD/MetricsCurrent (view source)
Revision as of 06:34, 30 April 2009
, 30 April 2009live chat metrics
(Created page with 'This is a copy of the metrics section from Metrics with notes about how each metric is currently collected and a quick overview of the query. '''Overview''' sumo_dump is a rep...') |
(live chat metrics) |
||
| Line 5: | Line 5: | ||
sumo_dump is a replica of our production database that's made nightly. $startdate is a string in this perl script of the form YYYY-MM-DD and is the START of the previous week. | sumo_dump is a replica of our production database that's made nightly. $startdate is a string in this perl script of the form YYYY-MM-DD and is the START of the previous week. | ||
of_dump is nightly copy of the production Openfire database, which contains chat logs and live chat user accounts. | |||
* i@Number of unique visitors (Om) | * i@Number of unique visitors (Om) | ||
| Line 12: | Line 14: | ||
** A record is made in tiki_actionlog every time certain actions are taken. The Posted action on the forum objectType refers to making a new thread. object=1 is the Firefox support forum (we have multiple forums -- some are contributor specific but we care mainly about the Firefox support forum. | ** A record is made in tiki_actionlog every time certain actions are taken. The Posted action on the forum objectType refers to making a new thread. object=1 is the Firefox support forum (we have multiple forums -- some are contributor specific but we care mainly about the Firefox support forum. | ||
* i+Number of new live chats | * i+Number of new live chats | ||
** Collected | ** Collected using live chat queries below. | ||
* i+Number of new KB articles | * i+Number of new KB articles | ||
** Collected by cilias. I think he uses the web interface to tiki_actionlog to get this info. | ** Collected by cilias. I think he uses the web interface to tiki_actionlog to get this info. | ||
| Line 91: | Line 93: | ||
Many of these aren't done with queries so there will be some work with zzxc here. | Many of these aren't done with queries so there will be some work with zzxc here. | ||
* i+ | |||
* i+Number of chats answered | |||
** Collected from Openfire fpSession table, which contains one row for each chat session. The start and end dates for the metric are compared with startTime to select data from a range. The transcript must match "message", indicating that at least one message stanza was sent. (BEGIN and END are dates in YYYY-MM-DD format) | |||
<pre>SELECT count(*) FROM of_dump.fpSession | |||
WHERE (startTime > (unix_timestamp('BEGIN') * 1000)) | |||
AND (startTime < (unix_timestamp('END') * 1000)) | |||
AND transcript REGEXP "message";</pre> | |||
* i+Number of chats not accepted by an agent | |||
** Collected from fpSession table. The transcript is null in cases where no agent accepts the offer. | |||
<pre>SELECT count(*) FROM of_dump.fpSession | |||
WHERE (startTime > (unix_timestamp('BEGIN') * 1000)) | |||
AND (startTime < (unix_timestamp('END') * 1000)) | |||
AND transcript IS NULL;</pre> | |||
* i+Number of chats dropped | |||
** This is the number of requests dropped before any messages were sent. (Includes cases where users close the chat window, helpers get disconnected before answering, or live chat bugs resulting in disconnections) | |||
<pre>SELECT count(*) FROM of_dump.fpSession | |||
WHERE (startTime > (unix_timestamp('BEGIN') * 1000)) | |||
AND (startTime < (unix_timestamp('END') * 1000)) | |||
AND transcript NOT REGEXP "message" | |||
AND transcript IS NOT NULL;</pre> | |||
* i=Total number of chat requests (calculated from summing chat request totals from above) | |||
* i-Number of active contributors | * i-Number of active contributors | ||
** Collected from Openfire ofUser and fpSession tables. Pattern matching is used to determine the total number of helpers in chat sessions during a given week. The username column comes from the ofUser table and is the bare username (eg. "zzxc") of the user account. | |||
<pre>SELECT COUNT(DISTINCT username) FROM of_dump.ofUser | |||
LEFT JOIN of_dump.fpSession ON transcript REGEXP CONCAT("\"", username,"[.@.]chat-support.mozilla.com[./.]") | |||
WHERE (startTime > (unix_timestamp('BEGIN') * 1000)) | |||
AND (startTime < (unix_timestamp('END') * 1000));</pre> | |||
* i-Number of new contributors | * i-Number of new contributors | ||
* i+Number of chats | ** Collected using the same method as the last query, adding a condition for accounts created during this time range. The creationDate column is from the ofUser table and specifies the unixtime (in milliseconds) that the account was created. | ||
* | <pre>SELECT COUNT(DISTINCT username) FROM of_dump.ofUser | ||
* i+Number of chats to follow up on | LEFT JOIN of_dump.fpSession ON transcript REGEXP CONCAT("\"", username,"[.@.]chat-support.mozilla.com[./.]") | ||
WHERE (startTime > (unix_timestamp('BEGIN') * 1000)) | |||
AND (startTime < (unix_timestamp('END') * 1000)) | |||
AND (creationDate > (unix_timestamp('BEGIN') * 1000));</pre> | |||
* i+(CSAT survey) Number of chats with problem solved | |||
** Collected from Tikiwiki poll results combined with Openfire session data. The Webchat ID used in Tikiwiki is part of the referer metadata associated with each chat. Each row in fpSession can have one or more metadata rows (from fpSessionMetadata) associated with it. Metadata includes the question text, plugins, extensions, referer, and nickname. | |||
** For problems solved, a survey response of "yes" or a score from 1-5 is counted. A response of 1-5 indicates that "yes" was previously selected, since we only ask people who get their problems solved for a rating. | |||
** Note: These queries will become faster and simpler after [https://bugzilla.mozilla.org/show_bug.cgi?id=490638 bug 490638] is fixed. | |||
<pre>SELECT count(*) as yesresults FROM sumo_dump.tiki_user_votings | |||
LEFT JOIN sumo_dump.tiki_poll_options ON tiki_user_votings.optionId=tiki_poll_options.optionId | |||
LEFT JOIN of_dump.fpSessionMetadata ON (tiki_user_votings.id regexp "feedback" and fpSessionMetadata.metadataValue regexp trim(leading 'feedback' from tiki_user_votings.id)) | |||
LEFT JOIN of_dump.fpSession ON (fpSession.sessionID = fpSessionMetadata.sessionID) | |||
WHERE feedbackObjectType = "livechat" | |||
AND fpSessionMetadata.metadataName = "referer" | |||
AND tiki_user_votings.id regexp "feedback" | |||
AND (title regexp "[1-5]" OR title regexp "yes") | |||
AND startTime > (unix_timestamp('START') * 1000) and startTime < (unix_timestamp('END') * 1000);</pre> | |||
* i+(CSAT survey) Number of chats to follow up on | |||
** This is based on the above query, instead counting the number of people who answered that their issue was unsolved but that they are planning on following up. | |||
<pre>SELECT count(*) as followupresults FROM sumo_dump.tiki_user_votings | |||
LEFT JOIN sumo_dump.tiki_poll_options ON tiki_user_votings.optionId=tiki_poll_options.optionId | |||
LEFT JOIN of_dump.fpSessionMetadata ON (tiki_user_votings.id regexp "feedback" and fpSessionMetadata.metadataValue regexp trim(leading 'feedback' from tiki_user_votings.id)) | |||
LEFT JOIN of_dump.fpSession ON (fpSession.sessionID = fpSessionMetadata.sessionID) | |||
WHERE feedbackObjectType = "livechat" | |||
AND fpSessionMetadata.metadataName = "referer" | |||
AND tiki_user_votings.id regexp "feedback" | |||
AND (title = "I will follow up later to continue solving this issue") | |||
AND startTime > (unix_timestamp('START') * 1000) and startTime < (unix_timestamp('END') * 1000);</pre> | |||
* i+(CSAT survey) Number of chats unsolved | |||
** Based on the above queries, collecting users who have selected "No" and who are not planning on following up. | |||
<pre>SELECT count(*) as noresults FROM sumo_dump.tiki_user_votings | |||
LEFT JOIN sumo_dump.tiki_poll_options ON tiki_user_votings.optionId=tiki_poll_options.optionId | |||
LEFT JOIN of_dump.fpSessionMetadata ON (tiki_user_votings.id regexp "feedback" and fpSessionMetadata.metadataValue regexp trim(leading 'feedback' from tiki_user_votings.id)) | |||
LEFT JOIN of_dump.fpSession ON (fpSession.sessionID = fpSessionMetadata.sessionID) | |||
WHERE feedbackObjectType = "livechat" | |||
AND fpSessionMetadata.metadataName = "referer" | |||
AND tiki_user_votings.id regexp "feedback" | |||
AND ((title = "No") OR (title = "The chat ended before it was finished") OR (title = "The helper wasn't responding") OR (title = "The helper was unable to solve my problem") OR (title = "The chat was taking too much time")) | |||
AND startTime > (unix_timestamp('START') * 1000) and startTime < (unix_timestamp('END') * 1000);</pre> | |||
* %=chats answered % (calculated from the "number of chats" metrics above) | * %=chats answered % (calculated from the "number of chats" metrics above) | ||
* %=chats solved % (calculated from the "number of chats" metrics above) | * %=chats solved % (calculated from the "number of chats" metrics above) | ||
* %=chats to follow up on % (calculated from the "number of chats" metrics above) | * %=chats to follow up on % (calculated from the "number of chats" metrics above) | ||
* f+Number of hours open | * f+Number of hours open | ||
** Query TBD, [https://bugzilla.mozilla.org/show_bug.cgi?id=490633 bug 490633] | |||
* %@CSAT score ''This can also be + in that it can be collected daily and calculated for other date ranges using a weighted average rather than a sum.'' | * %@CSAT score ''This can also be + in that it can be collected daily and calculated for other date ranges using a weighted average rather than a sum.'' | ||
* i | * i=Number of CSAT votes (calculated from CSAT poll counts above) | ||
'''Traffic and usage''' | '''Traffic and usage''' | ||