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.

SFTP data covers usage of Classic Moveworks bot and does not include data on usage of Copilot.

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:

ColumnDescription
timestamp_utcTime 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_addressEmail address of the user who experienced the resolution or interaction.
initiatorThe 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_categoryThe high-level skill category for this event. The possible values are 'Access Software', 'Access Group', 'Access Account', 'AskMe', 'Lookups', 'Form Filing', and 'Interaction'.
skill_detailMore 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_idThis 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:

ColumnDescription
dtThe day's date in UTC (Coordinated Universal Time)
email_addressUser's email address
user_idUnique 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:

ColumnDescription
dtThe day's date in UTC (Coordinated Universal Time) when ticket is polled by bot
ticket_idID of the ITSM ticket corresponding to the request
domainThe 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:

ColumnDescription
email_addressThe email address of the user
geoThe 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)
departmentThe 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_codeCountry code of the user
is_active_in_rosterWhether 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_botWhether 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_idUnique 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:

ColumnDescription
timestamp_utcTime stamp in UTC (Coordinated Universal Time) when ticket is polled by bot
ticket_idID of the ITSM ticket corresponding to the request
predicted_fieldThe 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_valueThe field value when the ticket was CREATED (before the model makes a prediction)
predicted_valueThe model prediction of the field value
final_valueThe field value when the ticket was CLOSED. The final value can either match the predicted value or be updated by agents.
short_descriptionThe short description of the ticket id
triage_resultThis 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_typeThis 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:

ColumnDescription
timestamp_utcTimestamp (UTC) indicating when the question was asked in bot or when bot reached out with suggestion on a ticket
article_added_dateDate when the knowledge article was added
user_idUnique user ID generated internally by Moveworks
user_nameUser name (will be redacted for Enterprise Answers)
departmentUser department (will be redacted for Enterprise Answers)
languageLanguage of the request (may be empty in case of feature not enabled)
user_textText of the user request or ticket
knowledge_titleTitle of the knowledge snippet served to user
article_idKnowledge article ID
doc_sourceSource of the knowledge snippet (Internal Knowledge Base/Moveworks FAQ Sheet/Moveworks External Knowledge)
doc_domainDomain of the knowledge article, as set when the article was added
issue_topicEntity detected in user request (Email/Outlook/Zoom/VPN/...)
resultResult 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.
sourceOrigin of the request

"DM": direct message to bot

"Ticket route": intercepted ticket

"Group channel":message intercepted in a group channel
article_urlURL of the knowledge article served
ticket_idID of the ITSM ticket corresponding to the request
ticket_urlURL 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.

ColumnDescription
timestamp_utcTime 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_idID of the ITSM ticket corresponding to the request
domainDomain 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.

ColumnDescription
channel_idThe ID of the Slack channel
full_nameThe full name of the user who posted in the channel
resolution_skill_typeThe skill that the bot reached out to the user with
issue_feedback_typeThe user feedback on the bot reach-out
is_issue_resolvedResolution outcome of the issue
dtTimestamp

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:

MetricDescriptionSQL Query or Arithmetic Calculation
Active UsersCount unique number of users (by email address) in the daily active users tableselect count(distinct email_address) from {{date}}_daily_active_users
ResolutionCount 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')
InteractionsCount the number of unique tickets that users interacted with through the bot, from the main events tableselect 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_categoryskill_detailIncluded in Resolution?Conditions for addition to denominator (i.e. no ticket filed)
Access AccountChange PW promptedYesIf 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 AccountChange PW unpromptedYesIf 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 AccountReset MFAYesIf 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 AccountUnlock AccountYesIf 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 GroupsAdd toYesIf the bot provides instructions to the user or redirects them to a self-service group management portal, without performing the addition automatically
Access GroupsCreateYesIf the bot provides instructions to the user or redirects them to a self-service group management portal, without performing the creation automatically
Access GroupsRemove fromYesIf the bot provides instructions to the user or redirects them to a self-service group management portal, without performing the creation automatically
Access Softwareapp nameYesIf 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
AskmeAskMe answer served, not ratedYesIf the conversation was initiated by the user (not from an existing ticket)
AskmeAskMe rated HelpfulYesIf the conversation was initiated by the user (not from an existing ticket)
AskmeAskMe rated Not HelpfulNoIf the conversation was initiated by the user (not from an existing ticket)
Form Filingform nameYesSince 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 breakdownDescriptionSQL Query
Resolution by skillCount 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 operationCount Access Software resolutions by software applicationselect 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 caseCount Access Account resolutions by use caseselect skill_detail, count(*) from {{date}}_daily_detailed_report where skill_category = 'Access Account'

group by skill_detail
Access Software by AppCount Access Software resolutions by software applicationselect skill_detail, count(*) from {{date}}_daily_detailed_report where skill_category = 'Access Software'

group by skill_detail
AskMe by feedbackCount 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 filedCount 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 feedbackCount successful Lookup events by type of lookupselect 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 typeCount 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.

MetricDescriptionSQL Query
Number of active users by departmentCount unique users (by email address), grouping by department, after joining the active user table to the user roster tableselect {{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 geoCount unique users (by email address), grouping by geographic location, after joining the active user table to the user roster tableselect {{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.

MetricDescriptionSQL Query
Triage Result by FieldCount 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 ValuesCount 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.

MetricDescriptionSQL Query
Articles ServedTotal number of times an article was foundselect 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 ProvidedTotal number of issues when a solution was suggested to userselect count(distinct user_text||timestamp)

from {{date}}_answers_insights_user_interactions

where result in ('Rated Positive', 'Not Rated', 'Rated Negative', 'Agent Handoff')
Resolutions ProvidedTotal 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 ResolutionsNumber 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 ArticleRatio of the number of issues with resolution in Answers (not rated or rated positive) to the number of issues with Answers suggestion served to userselect 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 TopicRatio of the number of issues with a knowledge article found to the number of issues with an attempt to find an articleselect 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.