Support/MetricsDashboardPRD/Metrics

From MozillaWiki
Jump to: navigation, search

Here are all the sections. You'll note that some metrics are in more than one section.

Legend

Unless specified, metrics are range specific. This means they are collected weekly, displayed for the last week, and cannot be displayed for other date ranges. For example, the "number of KB contributors" metric cannot be collected daily and accumulated to display the weekly metric, since the same contributors might have been active during several days, making the weekly metric incorrect.

Unless specified, metrics are coming from the TikiWiki db (using SQL queries).

  • + : metric is not range specific and should be collected daily even though we will display the metric with the weekly date range by default. Metrics that are not range specific will support a custom date range if/when we add UI for it.
  • @ : this range specific metric should be collected monthly and weekly.
  • -: this is a range specific metric and is only collected weekly.
  • =: this metric is calculated from other metrics
  • (Om) : metric is coming from Omniture
  • List based: metric is NOT a number but will be presented as a list. How this will work (ideally) is explained in the section below.
  • i, %, f: integer, percentage or floating point number.

Date ranges

  • Last week -- this is the default range and is supported by all metrics
  • Last month -- only supported by + and @ metrics, as explained above
  • Custom date range -- only supported by + metrics, as explained above

List based metrics

There are three special types of item: search terms, articles, locales. For each, we'll be collecting a variety of metrics for EACH article/search term/locale over time. On the dashboard the presentation is a ranked list of the top 5 for a given metric. For example:

For articles, we'll collect: page hits, hits from search engine and votes daily. Then when we generate the dashboard, we just have a list of the five articles with the most page hits (for example) as a metric.

Metrics

Overview

  • i@Number of unique visitors (Om)
  • i+Number of new forum threads
  • i+Number of new live chats
  • i+Number of new KB articles
  • i-Number of active contributors
  • i-Number of new contributors
  • i+Number of new registered accounts
  •  %@Combined total CSAT score (for KB, forum, chat)

Knowledge base (All tiki queries)

  • i@Number of modified articles
  • i+Number of new articles
  • i-Number of active KB contributors
  • i-Number of new KB contributors
  •  %=ratio of votes that say articles are helpful (calculated from:)
    • i+number of yes votes
    • i+number of no votes
  •  %=ratio of votes that say articles are easy to understand (calculated from:)
    • i+number of yes votes
    • i+number of no votes
  •  %@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+Number of CSAT votes

Forums (All tiki queries)

  • i+New forum threads
  • i+Number of replies by contributors (total)
  • i-Number of threads in each status category (after 1 week)
    • Request for more info
    • Solved
    • Proposed solution
    • Locked
    • None of the above
  • i-Number of threads with no replies for over 1 week
  • i-Number of active contributors
  • i-Number of new contributors
  •  %@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+Number of CSAT votes

Live Chat Many of these aren't done with queries so there will be some work with zzxc here.

  • 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)
SELECT count(*) FROM of_dump.fpSession
WHERE (startTime > (unix_timestamp('BEGIN') * 1000))
AND (startTime < (unix_timestamp('END') * 1000))
AND transcript REGEXP "message";
  • 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.
SELECT count(*) FROM of_dump.fpSession
WHERE (startTime > (unix_timestamp('BEGIN') * 1000))
AND (startTime < (unix_timestamp('END') * 1000))
AND transcript IS NULL;
  • 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)
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;
  • i=Total number of chat requests (calculated from summing chat request totals from above)
  • 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.
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
    • 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.
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+(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 bug 490638 is fixed.
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);
(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 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);
  • 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.
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);
(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 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);
  • 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.
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);

(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 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);
  •  %=chats answered % (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)
  • f+Number of hours open
  •  %@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.
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);
  • i=Number of CSAT votes (calculated from CSAT poll counts above)

Traffic and usage

All of these come from Omniture unless otherwise noted

  • i+Total pages hits
  • i@Number of unique visitors
  • i+Start page # entries
  •  %=Start page bounce rate (calculated from...)
    • i+Start page number of bounces
  • i+Inproduct start page # entries
  • i+Inproduct start page bounce rate (calculated from...)
    • i+Inproduct start page number of bounces
  • i+Number of searches
  • i+Number of people who go to Ask a question
    • from a KB article,
    • from a search results page,
    • directly.
  • i+Number of people who go to Ask a question and then go to forums, live chat (Om) -- This is a traffic question: Of the people who land on Ask a question how many try to go to forums and how many try to go to live chat, it lets us know (for example) that everyone is trying to do LiveChat and only doing forums if LC is closed.
  • =Top search terms (from search plugin ideally) List based For each search term, we're collecting:
    • i-Number of searches per search term.

KB article metrics

This will ideally be ALL of the KB articles with metrics for each. Then presentation would be in the form of lists based on ranking based on these metrics. As such, these are all List based

  • i+Number of page hits (Om)
  • i+Number of search-hits (Om) Number of times people select the page in search results
  • i+Number of front page hits/inproduct front page hits (Om)
  • f-Time spent on page (should be on Om) [djst: not possible to get other than a distribution average. i think we might give this one a miss]
  •  %=Percentage of people who said yes to "was this helpful?" calculated from:
    • i+Number of yes votes
    • i+Number of no votes
  •  %= Was this understandable votes % calculated from:
    • i+Number of yes votes
    • i+Number of no votes
  •  %@ CSAT score and vote count 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.
  • Calculated score
  • Calculated helpfulness

Locale-based metrics -- Gather each of the below metrics for EACH locale. List based

  • i+Number of KB edits/translations/approvals to indicate activity
  •  %-Percent of KB articles in each group:
    • Up to date
    • Needs updating
    • Needs translation
    • Needs approval
  •  %-Combined completeness score
  • i+Number of visitors (Om)
Retrieved from "https://wiki.mozilla.org/index.php?title=Support/MetricsDashboardPRD/Metrics&oldid=1236663"