canmove, Confirmed users
382
edits
Support/MetricsDashboardPRD/Metrics: Difference between revisions
Jump to navigation
Jump to search
Support/MetricsDashboardPRD/Metrics (view source)
Revision as of 15:16, 29 April 2009
, 29 April 2009The rest of the chat queries
mNo edit summary |
(The rest of the chat queries) |
||
| Line 70: | Line 70: | ||
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+Number of | * 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. | ** 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 | |||
* i+Number of | 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. | ** Collected from fpSession table. The transcript is null in cases where no agent accepts the offer. | ||
<pre>SELECT count(*) FROM of_dump.fpSession | |||
* i+Number of | 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) | ** 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=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. | ** 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 | ||
** 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. | ** 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 | |||
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+Number of chats | * 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. | ||
* i+Number of chats to follow up on | ** 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) | ||
| Line 97: | Line 147: | ||
** Query TBD, [https://bugzilla.mozilla.org/show_bug.cgi?id=490633 bug 490633] | ** 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''' | ||