Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Answers Not Used - Analytics Cookbook Recipe
    Topic posted April 28, 2016 by Dietrik Black Diamond: 60,000+ Points, last edited June 22, 2017 by Dietrik Blue Ribbon: 750+ Points 
    764 Views, 1 Comment
    Answers Not Used - Analytics Cookbook Recipe

    Answers Not Used

    With Starters and desert

    Author: Dietrik

    KPN Consulting

    Difficulty rating:   (Intermediate)

    Target persona: Content Manager


    4 Total Reports (all included in this recipe)

    22 Columns using formulas (14 sum(),& date_group())

    1 Alert Schedule

    3 Outer Joins

    2 Group Filters

    1 Report Link

    Filter To Taste

    Overall description/purpose: Content Managers like to have a good overview of their popular, most used answers and what their customers are searching for. There are a lot of reports giving statistics on the answers usage especially using the Actionable Knowledge Foundation reports. Answers with bad ratings or a low numbers of hits can be easily seen. But what about the answers that are never viewed and do not show up in any report?  The courses in this recipe show a number of reporting features as well a number of different answers rating items.

    Use case being served: Answers that are never being viewed do not serve the information they contain. They answer no specific question to the customer, because they cannot be found easily, they don’t have a meaningful description, or simply are not useful answers.  It is not always easy to identify a cause, but it is difficult to fix answer content without knowing which answers fall into the “not used” classification.  Reporting is easy on actions that have happened but reporting around non-action requires a different setup.

    Screen shot: 

    High level flow for report creation:

    There are a number of courses (i.e. reports) being served in this recipe. As a starter, a look at the available answers field in the data dictionary shows the answer field “Score” with a description “The solved count for the answer. This is a measure of how helpful the answer is to the end-users”. This is shown in the report “Answers without Score” with filter “score = 0” presenting all public answers where the score value has been degraded to 0.

    Although this report shows unpopular answers it does not exactly show our initial reporting question. This report shows the availability of a “Score” field in the answer table and the use of the data dictionary to find the column description.  The methodology behind the score field can be found in the available documentation.

    A second course, “Answer Without Stats”, combines the “Answers” with the “Answer Stats” table.  The “Answer Stats” table provides helpful statistics on answers usage.  Because we are looking for answers that have no statistics reported, the join between these tables is set to “Outer”.  A group filter is added to the report to only show the answers that have no hits counted.  A filter is added to select a range of id’s. This is needed as this report runs through a large number of records which will quickly run up against maximum query limits.

    The main course makes use of the Actionable Knowledge Foundation statistics table “Answer Details Stats”. As this table reports on larger time frames we don’t run into query limits when setting the outer join.  There are no filters set except for status “Public”.  An alert schedule is added to send the report once a month but only when there’s data present. A linked report showing relevant answers details is added as desert.

    This report shows unused answers by using the Actionable Knowledge Foundation statistics table “Answer Details Stats”. It also demonstrates the “Alert Schedule” function.  To solve the initial reporting issue this is the reports that should be used.

    Master Chef Approved (a.k.a. Data Validated)? Not against a large English language site

    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.


      Answers without score:

      This report has a filter for “Score = 0”, the column score, therefore, showing answers with no recent use.

    2. Answers without stats:

      This report uses an outer join. A default inner join would show only answers with hits.

      As the report might run into query limits a filter on answers id’s is added which is set to a default of 1 to 10,000.

      A group filter is added to only show the items without hits. The expression adds a NVL function to replace no value items with 0 to get a valid value for the expression.

    3. Answers Not Used:

      The report only has a filter on status type “Public”. As the report will also show newly created answers, a filter on creation date might be added.

      The report uses an outer join to the Actionable Knowledge Foundation statistics table “Answer Details Stats”.

      A group filter is added to only show the items without hits. As there is no differentiation or filters on the records returned, the hit values shown when the filter is changed are not the correct values but can be used to identify unpopular answers.

      A report link to the “Answers Not Used Details” report is added to show answers details.  If this detail report would not show statistics data (column “Last viewed”, which was added for reference and should be empty), a standard drill down level can be setup in the report instead of a report link. When importing the report the link needs to be renewed or removed.

      An exception is added to allow an alert schedule to send the report to a content manager, it does not change the reports visually.


      The planning is setup to be send once a month, using the alert to only send the reports when there’s data available.  If checked regularly this does not need to be sent as often.


    4. Answers Not Used Details:

      This report is used by the report link. It show a number of content management dates as well as the columns “Last viewed” and “Score”, which are expected to be empty. 

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

    • Human readable PDF report definition (4x)
    • XML Report definition (4x)
    • Screen shot (4x)



    • Erica (Leep) Anderson

      Just wanted to give a BIG thanks to you, Dietrik, for contributing this great combination of dashboard recipes to cthe Analytics Cookbook launch! We are excited to have a reporting guru like you involved and supporting this new collective community resource!


      We are experimenting with 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 Dietrik'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