Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Jeff Elser
    KCS Answer Citations By Group - Analytics Cookbook Recipe
    Topic posted January 5, 2017 by Jeff ElserSilver Medal: 2,000+ Points, last edited June 22, 2017 
    391 Views, 2 Comments
    KCS Answer Citations By Group - Analytics Cookbook Recipe


    Knowledge-Centered Service (KCS)


    Author: Jeff Elser


    Difficulty rating: (Basic)

    Target persona: Authors, Managers


    4 Tables (answers, user_trans, accounts, acct_groups). 0 Outer/ 3 Inner Joins

    4 Columns using formulas (1 sum() & decode())

    1 Filters

    Overall description/purpose: This report supports the KCS methodology Technique 7.3: Assessing the Creation of Value. Citations are a measure of the of an author's content by others. Specifically, it counts the number of times agents editing incidents have linked to answers written by a given author. This report aggregates citations by account group and drills down into individual citation counts.

    Screen shot: Citations by Group

    Master Chef Approved (a.k.a. Data Validated)? Yes/No

    Report Configuration Details

    The following section is intended to detail specific areas of the report that are not well described by the PDF report definition.

    1. This report lists authors with their respective citation counts and groups them by account group.

    2. Columns & variables are shown in the PDF definition.  These are additional comments about those.


      Column 1 is the account group. This column is hidden but allows for the roll-up on account group.


      Column 2 is author's full name, because authors are more than just a number!


      Column 3 is the account id and is hidden. This column is used to filter the Citations by Author linked report.


      Column 4 is the citation rate. This is the count of the number of times agents have used an answer when working incidents. We use the following formula to calulate citations: sum(decode(user_trans.type, 4, 1, 5, 1, 6, 0, 0)) Note that user transaction types 4 and 5 are given values of 1, but type 6 and other types are given 0. This means we are summing only answer link append and answer text append transactions. If you give type 6 a value of 1, you can include answer views. Measuring views can be useful for determining the usage of internal articles that are viewed by agents, but not linked in the incident (since customers wouldn't have access to the answer).

    3. Tables & Filters Used in this report: 

      a. Accounts (accounts)

      This report primarily list all authors with their citation rates, so the first table to use is the accoutns table.

      b. Answers (answers)

      Next, we need to find all the answers that belong to each author, so we join the accounts table to the answers table. (accounts.acct_id = answers.assgn_acct_id)

      c. User Transactions (user_trans)

      The user transactions table keeps track of actions that agents take in the agent desktop. In this case, we want to count the answer usage in incidents. Answer usage in incidents is stored as user transactions of type 4 (answer link append) and type 5 (answer text append). You will want to use an inner join on answers as shown in the screenshot above.

      d. Account Groups (acct_groups)

      The last table we need to use is the account groups table. This allows us to roll-up the authors by group. Since KCS permits all licensed agents to author content, there may be more authors than support agents and this rollup helps sort them into manageable groups.

    4. Report Filters

      a. User Transaction Date (user_trans.start_dtt... > -1 Weeks Exactly)

      The user transaction date filter allows us to define a time range when the linking took place. For example, we can choose to look at only citations that occured in the last month.

    5. Drill Down Levels: Citations by Author FIX LINK!!!

      a.  A drill down using report linking on the agent column was added to show citations on by author.

      b. The drilldown report is in a separate recipe. However, I selected the following options on the filter page of the report linking wizard:

    Included are the following (in a .ZIP file):

    • Human readable PDF report definition
    • XML Report definition 



    • Erica (Leep) Anderson

      We are leaving these report recipe posts open for comments, because we want report chefs to hear how their report recipes helped you. If you found this report recipe helpful, please give Jeff's post a thumbs up and leave a comment letting him know you appreciate his help, how this report helped you and your organization, etc.

      If you have a question about this report recipe, please start a new topic in the "Reporting and Analysis" forum board and link to this report recipe in your question. DO NOT post your reporting question here. We will remove any comments asking for help in the comments of this post.

      Erica, Community Manager

    • Barrilito van Dijk

      Hi Jeff, thanks for adding another cookbook! Good to see lots of information and examples on the community!