Help Center

Using Export to Excel to Compute Team Performance Statistics

Last Updated: Dec 14, 2016 08:43AM EST
When exporting mentions from CX Social, you can choose to have any actions on those mentions included in the export as well. In other words, the export contains when mentions were resolved, replied to, assigned, tagged, etc.

The action details contain the type of action and which user took the action. Next to that, you are seeing when the action was done and how many seconds after the publish date of the mention this was (handle time). 

The combination of action and mention details allows you to compute any relevant team performance statistics yourself, in case you want to build custom graphs in Excel. In this Support Article we explain how.

How to Export Actions

When exporting a list of mentions from the Inbox, you can click "Advanced" to select which fields of the mentions you want to have included in the export. To have actions included, make sure to check the column "Actions".

Note that this only works for exporting to Excel (since other formats don't support this feature). Your export preferences are saved for the next time you export.

When you download the exported Excel file, you'll notice it has two sheets: one with mention data and one with the actions on those mentions. Actions and mentions are exported to two separate sheets because mentions can have zero or more actions on them.

Connecting Mention & Action Data

The “Permalink” field is the key to connect action data to mention data, which are located in different tabs of the Excel file. This “Permalink” field is a unique identifier for each mention. Thus, if you have e.g. a resolve action by "user x" in the Actions tab, using the column "Permalink" you can find the corresponding mention and its text, publish date or source in the Mentions tab of the Excel file. 

Relevant Excel Functionalities

The following two Excel features are specifically interesting for computing your own team performance metrics from these exports:
  • Pivot Tables: Excel's Pivot Table feature allows you to compute aggregations of selected data sets. Pivot Tables summarize data and reveal patterns and trends. This can e.g. be used to compute the average reply time per agent (see example 1), or the amount of messages per source.
    More details on Pivot Tables in the Office documentation.
  • VLOOKUP Function: By using Excel's "Vertical Lookup" function you can look for a certain value in a selected data-set, based on a search parameter. Or, as Excel documents it: VLOOKUP looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. This is particularly useful to couple information from the Actions tab with information in the Mentions tab (using the “Permalink” as lookup value). With a vertical lookup function you can e.g. add a column to the Mentions tab, that contains the name of the agent that replied to the mention and/or how fast. This is explained in detail in example (2).
    More details on VLOOKUP in the Office documentation.



1. Average (First) Reply Time Per Agent.

In this example we'll create a Pivot table that, for each agent in your account, shows the average amount of seconds it took him/her between a mention's publish date and the timestamp of the action, for all replies he/she did.
  • The metric we want to take an average of is the "Action Delay" column in the Actions tab (or "Action Delay in BH" if you only want to take the business hours of the agent into account). This column shows the amount of seconds between the action and the publish date of the mention it happened on.
  • For this Pivot table we want to make, we only need data from the Actions tab.
  • First we need to filter by the complete list of actions, so that we only take replies into account. We can do this by sorting/filtering on the "Type" column, and selecting only actions of type "Replied".
  • Optionally, if you want to compute the average time to first reply, you can further trim down the table to those records where "Index Per Type" has value 1. (This will leave out any follow-up replies that were sent as replies to the same mention.)
  • From the selection, we create a pivot table. The fields we want to include in the pivot table are "User Name" and "Action Delay". As "Rows" for the pivot table we select "User Name" (so each of your users gets their own row), and we drag the "Action Delay" field to the "Values" configuration of the pivot table. For the values that should be computed in the Delay column, we choose to show the average (instead of the default "sum").

2. A list of replied mentions with their replies and person who replied.

In this example, we want to get a list of all mentions that were replied to and, next to that, show who replied, and with what text, all in the same table.
  • First, we need to decide what happens with mentions that have had multiple replies. Do we duplicate the mention's text and details in a 2nd row to show the 2nd reply next to it, or do we leave out the 2nd reply. In this example we're choosing the second strategy (*).
  • Next, we need to couple information from the Mentions tab (mention's text, author, etc.) to information in the Actions tab (reply text, who did the reply). We'll couple these sheets using vertical lookups.
  • Then, remove all actions that aren't "first reply" actions. We can do this by using Excel's filter or sorting functionality. Make sure to only leave actions where "Type" is "replied" and where "Index Per Type" is 1.
  • What we will do now is create a new column in the Mentions tab where we want the name of the user that replied to show up. For this, we'll add a vertical lookup function to the cells in this column that do a lookup on the "permalink" property in the filtered dataset of actions, and displays the "User Name" column. (Note: Make sure your actions are sorted in ascending order by the permalink column for VLOOKUP to work well.)
  • We can repeat this to do a lookup for the "reply text" (which, for actions of type "replied", will be available in the "Details" column of an action).
  • We can now extend these function lookups to the other cells in the mention tab to display reply user and reply text for the other mentions as well.
  • You can finish of by filtering or sorting the mentions tab by these new columns, so mentions that weren't replied to don't show up.

(*) If you would prefer the strategy where mention details are duplicated in the resulting table so multiple replies are also shown, you'd create VLOOKUPs from the Actions tab into the Mentions tab, instead of the other way around as explained in the example above.

Contact Us
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
Invalid characters found