canmove, Confirmed users
382
edits
Support/MetricsDashboardPRD/Metrics: Difference between revisions
Jump to navigation
Jump to search
update csat queries
(The rest of the chat queries) |
(update csat queries) |
||
| Line 109: | Line 109: | ||
** 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. | ** 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. | ** 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 | <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.fpSession ON (fpSession.sessionID = trim(leading 'feedback' from tiki_user_votings.id)) | |||
WHERE feedbackObjectType = "livechat" | |||
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> | |||
<pre> | |||
(for data prior to 2009-06-16, this alternative query is needed) | |||
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 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.fpSessionMetadata ON (tiki_user_votings.id regexp "feedback" and fpSessionMetadata.metadataValue regexp trim(leading 'feedback' from tiki_user_votings.id)) | ||
| Line 121: | Line 131: | ||
* i+(CSAT survey) Number of chats to follow up on | * 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. | ** 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 | <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.fpSession ON (fpSession.sessionID = trim(leading 'feedback' from tiki_user_votings.id)) | |||
WHERE feedbackObjectType = "livechat" | |||
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> | |||
<pre> | |||
(for data prior to 2009-06-16, this alternative query is needed) | |||
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 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.fpSessionMetadata ON (tiki_user_votings.id regexp "feedback" and fpSessionMetadata.metadataValue regexp trim(leading 'feedback' from tiki_user_votings.id)) | ||
| Line 132: | Line 152: | ||
* i+(CSAT survey) Number of chats unsolved | * 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. | ** 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 | <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.fpSession ON (fpSession.sessionID = trim(leading 'feedback' from tiki_user_votings.id)) | |||
WHERE feedbackObjectType = "livechat" | |||
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> | |||
<pre> | |||
(for data prior to 2009-06-16, this alternative query is needed) | |||
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 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.fpSessionMetadata ON (tiki_user_votings.id regexp "feedback" and fpSessionMetadata.metadataValue regexp trim(leading 'feedback' from tiki_user_votings.id)) | ||
| Line 147: | Line 179: | ||
** 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.'' | ||
<pre> | |||
SELECT avg(title) as chatcsatscore 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.fpSession ON (fpSession.sessionID = trim(leading 'feedback' from tiki_user_votings.id)) | |||
WHERE feedbackObjectType = "livechat" | |||
AND tiki_user_votings.id regexp "feedback" | |||
AND (title regexp "[1-5]") | |||
AND startTime > (unix_timestamp('START') * 1000) and startTime < (unix_timestamp('END') * 1000); | |||
</pre> | |||
* i=Number of CSAT votes (calculated from CSAT poll counts above) | * i=Number of CSAT votes (calculated from CSAT poll counts above) | ||