canmove, Confirmed users
382
edits
Support/MetricsDashboardPRD/Metrics: Difference between revisions
Support/MetricsDashboardPRD/Metrics (view source)
Revision as of 14:30, 29 April 2009
, 29 April 2009no edit summary
(→Metrics: Chat queries, part 1) |
mNo edit summary |
||
| Line 72: | Line 72: | ||
* i+Number of chat requests answered | * i+Number of chat requests 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. | ||
** Query: | ** Query: '''SELECT count(*) FROM of_dump.fpSession WHERE (startTime > (unix_timestamp('YYYY-MM-DD') * 1000)) AND (startTime < (unix_timestamp('YYYY-MM-DD') * 1000)) AND transcript REGEXP "message";''' | ||
* i+Number of chat requests not accepted by an agent | * i+Number of chat requests 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. | ||
** Query: | ** Query: '''SELECT count(*) FROM of_dump.fpSession WHERE (startTime > (unix_timestamp('YYYY-MM-DD') * 1000)) AND (startTime < (unix_timestamp('YYYY-MM-DD') * 1000)) AND transcript IS NULL;''' | ||
* i+Number of chat requests dropped | * i+Number of chat requests 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) | ||
** Query: | ** Query: '''SELECT count(*) FROM of_dump.fpSession WHERE (startTime > (unix_timestamp('YYYY-MM-DD') * 1000)) AND (startTime < (unix_timestamp('YYYY-MM-DD') * 1000)) AND transcript NOT REGEXP "message" and transcript IS NOT NULL;''' | ||
* 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. (BEGIN and END are dates in YYYY-MM-DD format) The username column comes from the ofUser table and is the bare username (eg. "zzxc") of the user account. | ** 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. (BEGIN and END are dates in YYYY-MM-DD format) The username column comes from the ofUser table and is the bare username (eg. "zzxc") of the user account. | ||
**QUERY: | **QUERY: '''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));''' | ||
* 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. | ||
**QUERY: | **QUERY: '''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));''' | ||
* i+Number of chats answered | * i+Number of chats answered | ||