Reporting and Analytics for Service

Get Involved. Join the Conversation.


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


    Knowledge-Centered Service (KCS)


    Author: Jeff Elser


    Difficulty rating: (Basic)

    Target persona: Authors, Managers


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

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

    2 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.

    Screen shot: Citations by Author

    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 answers authored by a given accout and then count of citations for each answer.

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


      Column 1 is just the answer id.


      Column 2 is the summary of the answer. This allows provides quick insite into what the answer is about without opening the answer.


      Column 3 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. Answers (answers)

      This report needs to show citations for each answer that an author has created, so the first table to use is answers. This table will provide the answer ID and summary.

      b. 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.

      c. Accounts (accounts)

      The last table we need to use is the accoutns table. This allows us to filter down to only ansers that were edited by a particular author. We'll leverage this table in the report filters. Join with an inner join on answers where answers.assin_acct_id = accounts.acct_id.

    4. Report Filters

      a. Account (accounts.acct_id = Logged In)

      The account fitler allows us to show only answers that were authored by a given account.

      b. 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.

    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!