SFTP Data Dictionary
What is the daily data format?
SFTP data shows every single resolution and interaction event, which will allow customers more flexibility in filtering/aggregating the data to build custom reports.
This document provides information on the data dictionary (the tables and fields involved), and directions on how it can be aggregated to build the views shown on your daily dashboard that Moveworks provides in Tableau. With this knowledge, you'll have the power to take the data and use it in any internal views/reports/dashboards you may need to build.
Data Dictionary
The daily SFTP data format contains multiple files, while some are used more extensively than others to derive the key metrics, all are necessary to build the complete daily dashboard that Moveworks provides. The files are as follows, and are all prefixed with today's date:
- {{today's date}}_daily_detailed_report.csv
- {{today's date}}_daily_active_users.csv
- {{today's date}}_daily_concierge_tickets.csv
- {{today's date}}_daily_user_roster.csv
- {{today's date}}_daily_triage_record.csv
- {{today's date}}_daily_answers_insights_user_interactions.csv
- {{today's date}}_daily_new_tickets.csv
The date format will be as follows: year-month-day e.g: 2023-12-13_daily_active_users.csv
File 1: Resolution and Interaction Events
File name: {{today's date}}_daily_detailed_report.csv
This is the main file that contains a table of all of the resolution and significant interaction events for the day. Each row in this table represents a resolution or interaction. Below are the descriptions of each column in the table:
| Column | Description |
|---|---|
| timestamp_utc | Time stamp in UTC (Coordinated Universal Time). Quick conversion tips: PST is 7 hours earlier than UTC time; EST is 4 hours earlier than UTC. |
| email_address | Email address of the user who experienced the resolution or interaction. |
| initiator | The value in this column delineates whether it was the User or the Bot that initiated the conversation that led to this event. Thus, the only possible values are 'User' and 'Bot'. |
| skill_category | The high-level skill category for this event. The possible values are 'Access Software', 'Access Group', 'Access Account', 'AskMe', 'Lookups', 'Form Filing', and 'Interaction'. |
| skill_detail | More precise information on the use case and/or entity involved in this resolution. The type of information provided here depends on the skill category (the previous column). If the skill_category is 'Access Software', this column will contain the name of the app that was provisioned or for which self-service instructions were given to the user. If the skill_category is 'Access Group', this column will contain an indicator of whether the resolution was a create operation ('Create'), an add operation ('Add to'), or remove operation ('Remove from'). If the skill_category is 'Access Account', this column will contain the precise use case. The possible values are: 'Change PW prompted' (upon a password expiration reminder), 'Change PW unprompted' (the user asking to reset their password), 'Unlock Account', 'Reset MFA', or 'Account Expiry' (if the user took action on a contractor/contingent worker account expiry reminder) If the skill_category is 'AskMe', there are two possible values for this column: 'AskMe answer Served' (if the bot provided a possible answer to a user's question and the user did not rate it as "not helpful") or 'AskMe rated Not Helpful' (if the bot provided a possible answer, but the user clicked the button to say it was not helpful or filed a ticket) If the skill_category is 'Form Filing', this column will contain the name of the catalog item that the user submitted If the skill_category is 'Lookups', there are several possible values for this column. If the lookup was considered successful (i.e. the user did not rate it as "not helpful"), the value of this column will correspond to the type of lookup it was: 'people', 'rooms', 'finance', or 'partner'. If the bot provided more than one potential matches, the column will be empty. If the skill_category is 'Interaction', this column will contain the precise use case for the user's interaction with the ticketing system. The possible values are: 'Nudge Agent', 'Comment added', 'Ticket closed in bot', 'Ticket weblink clicked', 'Check Status', 'Click on Got it Thanks' (when provided an update on the ticket), 'Ticket submitted in bot', 'Tickets approved' (for ServiceNow RITM only), 'Live Chat clicked', 'Other interaction' (ticket reopening or interactions with tickets that were opened due to resolution events), 'Campaign response'. |
| ticket_id | This column contains the ID number of the ticket that is associated with this event, if one exists. A ticket is associated with an event in two cases: (1) if it is responsible for the bot initiating the conversation (i.e., the bot "reached out" to the user upon reading their ticket), or (2) if the bot files a ticket in the process of automatically resolving the user's issue (to create a log of the event for audit purposes). |
| user_id* | Numeric ID of the user who experienced the resolution or interaction, generated internally by Moveworks |
| minutes_to_resolution* | Time between when an issue was created and when it was resolved in the bot, in minutes |
| domain* | Domain of enterprise service related to the issue described, as detected by ML models per user utterance |
| issue_origin* | Origin of issue indicates where user described their issue. It can be user utterance in the bot chat or in IT chat channel, or ticket intercepted by bot. |
*available in some versions of SFTP data
File 2: List of active users
File name: {{today's date}}_daily_active_users.csv
This file contains a table that shows all of the users who were active during the day. A user is considered to have been "active" if they sent a message to the bot OR if they clicked a button/link in a message from the bot. Below are descriptions of each column in the table:
| Column | Description |
|---|---|
| dt | The day's date in UTC (Coordinated Universal Time) |
| email_address | User's email address |
| user_id | Unique user ID generated internally by Moveworks |
File 3: List of tickets that were updated in the ITSM system
File name: {{today's date}}_daily_concierge_tickets.csv
This file contains a table that shows the list of tickets that were available to the bot for Concierge use cases (eligible for checking status, receiving updates, adding comments, etc.). It is used in calculating the percentage of eligible tickets that users interacted with through the bot. Below are descriptions of each column in the table:
| Column | Description |
|---|---|
| dt | The day's date in UTC (Coordinated Universal Time) when ticket is polled by bot |
| ticket_id | ID of the ITSM ticket corresponding to the request |
| domain | The domain of the issue described in ticket |
File 4: Full roster of eligible bot users
File name: {{today's date}}_daily_user_roster.csv
This file contains a table that shows the full roster of users in your organization that are (or were previously) available to the Moveworks system. It is used to produce the breakdowns of active users by geographic location and department. Below are descriptions of each column in the table:
| Column | Description |
|---|---|
| email_address | The email address of the user |
| geo | The user's geographic location, as stored in Moveworks' user roster. This comes from one of your systems of record, such as ServiceNow or Active Directory (you can ask your Customer Success Manager if the values don't look accurate) |
| department | The user's department, as stored in Moveworks' user roster. This comes from one of your systems of record, such as ServiceNow or Active Directory (you can ask your Customer Success Manager if the values don't look accurate) |
| country_code | Country code of the user |
| is_active_in_roster | Whether the user is active in the roster. Either True or False, shows if the user is present in the current user identity roster that we pull from either your ITSM or AD roster |
| has_access_to_bot | Whether the user has access to the Moveworks Bot based on whitelisting. If is_active_in_roster is False, then has_access_to_bot will be False |
| user_id | Unique user ID generated internally by Moveworks |
File 5: List of tickets triaged by the bot
File name: {{today's date}}_daily_triage_record.csv
This file contains a table that shows all the tickets triaged by the model (it will only be present if the Triage skill is active in your environment). If models are being used to set multiple ticket fields (e.g. Call Type, Assignment Group, Category, etc.), predictions and results for all fields will be in this single file. Below are descriptions of each column in the table:
| Column | Description |
|---|---|
| timestamp_utc | Time stamp in UTC (Coordinated Universal Time) when ticket is polled by bot |
| ticket_id | ID of the ITSM ticket corresponding to the request |
| predicted_field | The specific field that is being predicted by the model. These fields include but are not limited to assignment_group, business_service, category, subcategory, and components. |
| start_value | The field value when the ticket was CREATED (before the model makes a prediction) |
| predicted_value | The model prediction of the field value |
| final_value | The field value when the ticket was CLOSED. The final value can either match the predicted value or be updated by agents. |
| short_description | The short description of the ticket id |
| triage_result | This field is populated once the ticket is CLOSED. The type of information provided here depends on the predicted field: If the predicted field is "call type", a successful prediction would result in a ticket "Predicted" (incident, service request, etc), "No Prediction" (no prediction of field value), or "Out of Scope" (an agent updated the ticket before the bot). For other predicted fields such as "Assignment Group", a prediction can be "Correct" or "Incorrect" (comparing predicted value to final value), "No Prediction" (no prediction of field value) or "Out of Scope" (an agent updated the ticket before the bot). |
| predicted_field_type | This field is populated once the ticket is CREATED. The type of action the model is performing to the specific field. For CALL tickets, a model would transfer a ticket "Transferred". For other tickets, such as INC or ITHELP types, they can be "Routed" (routed the ticket to the appropriate support group), "Categorized" (labeled the ticket for reporting purposes), or "No Change" (predicted value is the same as the start value). |
File 6: List of User Interactions in scope of Answers
The data in this file corresponds to the current edition of the Answers Insights customer-facing Tableau dashboard.
File name: {{today's date}}_daily_answers_insights_user_interactions.csv
This file contains a table that shows all issues (utterances and tickets) that have the potential to be resolved by Answers skill, indicating the outcome of the bot attempt to solve the issue and context around it.
Data uniqueness is defined by the combination of the issue, knowledge article, and issue topic. For example, if 3 articles were served for an issue where 2 issue topics were detected, there will be 6 rows in the data.
Below are descriptions of each column in the table:
| Column | Description |
|---|---|
| timestamp_utc | Timestamp (UTC) indicating when the question was asked in bot or when bot reached out with suggestion on a ticket |
| article_added_date | Date when the knowledge article was added |
| user_id | Unique user ID generated internally by Moveworks |
| user_name | User name (will be redacted for Enterprise Answers) |
| department | User department (will be redacted for Enterprise Answers) |
| language | Language of the request (may be empty in case of feature not enabled) |
| user_text | Text of the user request or ticket |
| knowledge_title | Title of the knowledge snippet served to user |
| article_id | Knowledge article ID |
| doc_source | Source of the knowledge snippet (Internal Knowledge Base/Moveworks FAQ Sheet/Moveworks External Knowledge) |
| doc_domain | Domain of the knowledge article, as set when the article was added |
| issue_topic | Entity detected in user request (Email/Outlook/Zoom/VPN/...) |
| result | Result of the bot actions on the request "Not Served": No solution is offered to the user, instead bot suggests user to hand the issue off to an agent. "No Engagement: Bot is able to find a relevant answer, however user does not engage to see the suggestion. This can happen in Ticket route if the user does not click on any article links. Another case is when the bot suggests solutions from multiple skills but user does not choose to see results from Answers. "Not Rated": Bot offers an answer to the user; the user does not give any explicit feedback and does not hand the issue off to an agent. (Agent handoff includes filing a ticket, click on a Live Agent link or redirection to another domain portal.) "Agent Handoff": Bot offers an answer to the user but the user proceeds to an agent handoff. "Rated Negative": Bot offers an answer to the user but the user rates the answer as not helpful. "Rated Positive": Bot offers an answer to the user and the user rated the answer as helpful. |
| source | Origin of the request "DM": direct message to bot "Ticket route": intercepted ticket "Group channel":message intercepted in a group channel |
| article_url | URL of the knowledge article served |
| ticket_id | ID of the ITSM ticket corresponding to the request |
| ticket_url | URL of the ITSM ticket corresponding to the request |
File 7: List of daily new tickets
File name {{today's date}}_daily_new_tickets.csv
This file contains the list of new tickets ingested daily from ITSM system.
| Column | Description |
|---|---|
| timestamp_utc | Time stamp in UTC (Coordinated Universal Time). Quick conversion tips: PST is 7 hours earlier than UTC time; EST is 4 hours earlier than UTC. |
| ticket_id | ID of the ITSM ticket corresponding to the request |
| domain | Domain of the issue described in ticket |
File 8: List of Channel Resolver issues
File name {{today's date}}_daily_channel_issues.csv
This file contains the list of issues in the Slack channels where Channel Resolver is active in.
| Column | Description |
|---|---|
| channel_id | The ID of the Slack channel |
| full_name | The full name of the user who posted in the channel |
| resolution_skill_type | The skill that the bot reached out to the user with |
| issue_feedback_type | The user feedback on the bot reach-out |
| is_issue_resolved | Resolution outcome of the issue |
| dt | Timestamp |
Description of metrics derived from the data
In the following section, we go through commonly used metrics shown in Performance Insights dashboards that Moveworks provides in Tableau, providing a description and the corresponding SQL query or arithmetic calculation you can use to derive these metrics from the data in the files discussed above.
HIGH-LEVEL METRICS
Absolute Number Metrics
These metrics are counts of users, resolution, interactions, and forms filed. Below is a table that outlines how each of these metrics are calculated:
| Metric | Description | SQL Query or Arithmetic Calculation |
|---|---|---|
| Active Users | Count unique number of users (by email address) in the daily active users table | select count(distinct email_address) from {{date}}_daily_active_users |
| Resolution | Count all resolution events from the main events table, excluding Interactions and negative feedback (i.e. where the user rated the bot's response "not helpful). | select count(*) from {{date}}_daily_detailed_report where skill_category not in ( 'Interaction', 'Lookups') and skill_detail not in ('AskMe rated Not Helpful') |
| Interactions | Count the number of unique tickets that users interacted with through the bot, from the main events table | select count(distinct ticket_id) from {{date}}_daily_detailed_report where skill_category='Interaction' |
Percentage Metrics
There are two percentage numbers in this section - resolution percentage and interactions percentage. The key to understanding these metrics is to understand what is included in the numerator of the percentage calculation and what is included in the denominator.
Resolution Percentages
This metric calculates a percentage of users' issues resolved by the bot. All relevant resolution events from the main events table are counted in the numerator (only events that are rated "not helpful" are excluded).
To obtain the correct denominator, the starting point is the number of new tickets considered for resolution on the day (using SQL, this can be found with the following query: select count(distinct ticket_it) from {{date}}_daily_new_tickets). But this number is not the complete denominator, because many of users' issues are addressed through chatting with the bot and no corresponding ticket is filed. Thus, we need to add these events to the denominator, whether they were resolved or not.
At a high level, the events that need to be added to the denominator are those that do not originate from a ticket, and for which no new ticket is filed. These can be found by choosing the events that do not have the ticket_id column populated (using SQL, this can be found with the following query: select count(*) from {{date}}_daily_detailed_report where skill_category <> 'Interaction' and ticket_id is null). Since the logic behind whether a ticket is filed depends on the skill_category, it is best to explain the details with the following table that looks at all the types of resolution events (i.e. combinations of skill_category and skill_detail):
| skill_category | skill_detail | Included in Resolution? | Conditions for addition to denominator (i.e. no ticket filed) |
|---|---|---|---|
| Access Account | Change PW prompted | Yes | If the conversation was initiated by the user and the bot provides instructions to the user or redirects them to a self-service password reset link without taking action on the identity system |
| Access Account | Change PW unprompted | Yes | If the bot provides instructions to the user or redirects them to a self-service password change link, without taking action on the identity system |
| Access Account | Reset MFA | Yes | If the conversation was initiated by the user and the bot provides instructions to the user or redirects them to a self-service MFA reset link, without taking action on the identity system |
| Access Account | Unlock Account | Yes | If the bot provides instructions to the user or redirects them to a self-service account unlock link, without taking action on the identity system |
| Access Groups | Add to | Yes | If the bot provides instructions to the user or redirects them to a self-service group management portal, without performing the addition automatically |
| Access Groups | Create | Yes | If the bot provides instructions to the user or redirects them to a self-service group management portal, without performing the creation automatically |
| Access Groups | Remove from | Yes | If the bot provides instructions to the user or redirects them to a self-service group management portal, without performing the creation automatically |
| Access Software | app name | Yes | If the conversation was initiated by the user and the bot provides instructions to the user or redirects them to a self-service portal, without provisioning software automatically |
| Askme | AskMe answer served, not rated | Yes | If the conversation was initiated by the user (not from an existing ticket) |
| Askme | AskMe rated Helpful | Yes | If the conversation was initiated by the user (not from an existing ticket) |
| Askme | AskMe rated Not Helpful | No | If the conversation was initiated by the user (not from an existing ticket) |
| Form Filing | form name | Yes | Since there is always a ticket associated with a form, no addition is ever necessary |
Interactions Percentage
The goal here is to calculate the percentage of tickets that users interacted with through the bot. Thus, the numerator is the count of unique tickets that had interaction events in the main events table (using SQL, the query would be select count(distinct ticket_id) from {{date}}_daily_detailed_report where skill_category='Interaction').
The denominator is the total count of unique tickets that were eligible for Concierge interactions that day, from the daily concierge tickets table (using SQL, the query would be select count(distinct ticket_id) from {{date}}_daily_concierge_tickets).
EVENTS BREAKDOWN
The table below provides descriptions and SQL queries for the Resolution breakdown.
| Type of breakdown | Description | SQL Query |
|---|---|---|
| Resolution by skill | Count rows in the main event table by respective resolution skill_category, excluding Interactions and negative feedback (i.e. where the user rated the bot's response "not helpful). | select skill_category, count(*) from {{date}}_daily_detailed_report where skill_category not in ( 'Interaction', 'Lookups') and skill_detail<>'AskMe rated Not Helpful' order by count desc |
| Access Group by type of operation | Count Access Software resolutions by software application | select SPLIT_PART(skill_detail, ' ', 1) as type, count(*) from {{date}}_daily_detailed_report where skill_category = 'Access Group' group by type |
| Access Account by use case | Count Access Account resolutions by use case | select skill_detail, count(*) from {{date}}_daily_detailed_report where skill_category = 'Access Account' group by skill_detail |
| Access Software by App | Count Access Software resolutions by software application | select skill_detail, count(*) from {{date}}_daily_detailed_report where skill_category = 'Access Software' group by skill_detail |
| AskMe by feedback | Count AskMe events by those resolved ('AskMe answer served') and those not resolved ('AskMe rated Not Helpful') | select skill_detail, count(*) from {{date}}_daily_detailed_report where skill_category = 'AskMe' group by skill_detail |
| Forms filed | Count the number of forms filed, grouping by the catalog item name. | select skill_detail, count(*) from {{date}}_daily_detailed_report where skill_category='Form Filing' group by skill_detail |
| Lookups by feedback | Count successful Lookup events by type of lookup | select skill_detail, count(*) from {{date}}_daily_detailed_report where skill_category = 'Lookups' and skill_detail <> 'Lookup rated Not Helpful' group by skill_detail |
| Interactions by type | Count the interaction events in the main events table, grouping by the type of interaction. It is a simple count for all types of interaction. | select skill_detail, count(*) from {{date}}_daily_detailed_report where skill_category ='Interaction' group by skill_detail |
ACTIVE USERS
The table below provides descriptions and SQL queries to count number of users engaged with Moveworks bot.
| Metric | Description | SQL Query |
|---|---|---|
| Number of active users by department | Count unique users (by email address), grouping by department, after joining the active user table to the user roster table | select {{date}}_daily_user_roster.department, count(distinct {{date}}_daily_active_users.email_address) from {{date}}_daily_active_users join {{date}}_daily_user_roster on {{date}}_daily_active_users.email_address={{date}}_daily_user_roster.email_address group by {{date}}_daily_user_roster.department |
| Number of active users by geo | Count unique users (by email address), grouping by geographic location, after joining the active user table to the user roster table | select {{date}}_daily_user_roster.geo, count(distinct {{date}}_daily_active_users.email_address) from {{date}}_daily_active_users join {{date}}_daily_user_roster on {{date}}_daily_active_users.email_address={{date}}_daily_user_roster.email_address group by {{date}}_daily_user_roster.geo |
TRIAGE PERFORMANCE
The table below provides descriptions and SQL queries for Triage metrics.
| Metric | Description | SQL Query |
|---|---|---|
| Triage Result by Field | Count the number of triage_result_type, grouped by the predicted_field and triage_result. | select predicted_field, triage_result, count(*) from {{date}}_daily_triage_record group by predicted_field, triage_result |
| Breakdown of Triage Result by Predicted Values | Count the number of predicted_value, grouped by the predicted_field, triage_result, and predicted_value. | select predicted_field, triage_result, predicted_value, count(*) from {{date}}_daily_triage_record group by predicted_field, triage_result, predicted_value |
KNOWLEDGE
The table below provides descriptions and SQL queries for Knowledge metrics.
| Metric | Description | SQL Query |
|---|---|---|
| Articles Served | Total number of times an article was found | select count(distinct article_id||timestamp) from {{date}}_answers_insights_user_interactions where result in ('Rated Positive', 'Not Rated', 'Rated Negative', 'Agent Handoff', 'No Engagement') |
| Answers Provided | Total number of issues when a solution was suggested to user | select count(distinct user_text||timestamp) from {{date}}_answers_insights_user_interactions where result in ('Rated Positive', 'Not Rated', 'Rated Negative', 'Agent Handoff') |
| Resolutions Provided | Total number of issues when a solution was suggested to user and there is no negative feedback or a hand-off to an agent *In Channel route, only issues with positive feedback are counted | select count(distinct user_id||timestamp) from {{date}}_answers_insights_user_interactions where source='Group channel' and result='Rated Positive' or source='Ticket route' and (result='Rated Positive' or result='Not Rated') or source='DM' and (result='Rated Positive' or result='Not Rated') |
| Employees with Resolutions | Number of unique users with a solution suggested and no negative feedback or a hand-off to an agent *If a user reported an issue in Channel route, only resolutions with positive feedback are taken into account | select count(distinct user_id) from {{date}}_answers_insights_user_interactions where source='Group channel' and result='Rated Positive' or source='Ticket route' and (result='Rated Positive' or result='Not Rated') or source='DM' and (result='Rated Positive' or result='Not Rated') |
| Knowledge Effectiveness by Article | Ratio of the number of issues with resolution in Answers (not rated or rated positive) to the number of issues with Answers suggestion served to user | select knowledge_title, 1.0*count(distinct case when result not in ('Rated Negative', 'Agent Handoff', 'Not Served', 'No Engagement') then timestamp else null end)/count(distinct case when result not in ('Not Served', 'No Engagement') then timestamp else null end) as knowledge_effectiveness_rate from {{date}}_answers_insights_user_interactions group by knowledge_title |
| Knowledge Coverage by Issue Topic | Ratio of the number of issues with a knowledge article found to the number of issues with an attempt to find an article | select issue_topic, 1.0*count(distinct case when result not in ('Not Served') then timestamp else null end)/count(distinct timestamp) as knowledge_coverage_rate from {{date}}_answers_insights_user_interactions group by issue_topic |
FAQ
Q: When can I expect SFTP data to land?
A: SFTP reports will be produced the following day by 11:00 PM UTC Time. For example, the data for the 24 hours of January 1st will land by 11PM UTC on January 2nd. Note: data from each file will have no overlap of data.
Q: Do the files grow in size?
A: No, the files are sent for the days data, and a new file will be added for the next day.
Updated 3 months ago