Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Ramesh Ramasubramanian
    Knowledge Advanced Questions and Sessions Count by Day -...
    Topic posted January 19, 2017 by Ramesh Ramasubramanian, last edited June 22, 2017 
    512 Views, 3 Comments
    Knowledge Advanced Questions and Sessions Count by Day - Analytics Cookbook Recipe

    Questions and Sessions

    Questions asked on a daily basis 

    (Knowledge Advanced)

    Author: Nivi Gopathi/Ramesh Manian


    Difficulty rating:  (Intermediate)

    Target persona: Managers, KB Administrator, Executive


    1 Tables (OKCS Questions)

    Columns using formulas (2 count(), 2 count distinct (), 1 date_format())


    Overall description/purpose: This report gives the executive or the knowledge manager a clear understanding of the questions that users have asked on a daily basis. This report is available only when Knowledge Advanced is enabled. 

    Use case being served: 

    A few questions that are often asked by senior managers are:

    1. How engaged are our users with our site?
    2. How is the response for our new campaign?
    3. How many searches do our customers perform at our website?
    4. What type of questions do our customers ask us?

    While there are many ways to answer the above questions using our existing reports, there is no simple way to answer question #3 using our existing knowledge reports. Further, answers to question #2 while may have to be detailed, an executive knows that there is likely to be an uptick in the number of questions asked during the early days of campaign launch.

    Using this custom report, an executive can look at the number of questions asked and looking at question details on the campaign days and learn whether users are engaged in the campaign. This report answers the general engagement of the users.  It also displays a trend and can help in planning hardware resources to support the changing nature of user engagement.

    An executive can look thru this report and glance at the actual questions asked. Existing Knowledge Advanced reports do not show the actual text of the questions asked. 

    Screen shot: Daily Question Sessions (split screen with drill down on bottom of image)

    Special functionality usage: The Questions and Sessions top level report is sorted in ascending order of date. There is a drill down on Total # of Questions column. Clicking on question count (Total # of Questions column) will open a second detailed report in the bottom panel. The detailed report is sorted in the ascending order of Phrase Stem. 

    High level flow for report creation: The Questions and Sessions top level report has a drill down. Clicking on question count (Total # of Questions column) will open a second detailed report in the bottom panel. Clicking on a different date in the top level will display appropriate details in the bottom report

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

    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.

    • Terms used in the report definition or column headers
      • Normalized text / Phrase stem: In a Natural Language Search such as the one in Knowledge Advanced, users ask plain text questions that include stop words. Stop words are ones that are very common in a sentence and many of them are used by our search engine to identify context, but are of little use as keywords for a user to know.
      • In Knowledge Advanced, we refer to the set of keywords, ones that are not stop words, as “Normalized Question Text”. This is very similar to the set compiled in Knowledge Foundation, which is referred to as “Phrase Stem”. The user will notice the “Normalized Question Text” in the reports under the heading “Phrase Stem” and will appear enclosed by curly brackets. The user will also notice that Normalized Question Text words are often followed by a colon followed by words within parenthesis.
      • The words inside curly braces are called “Concepts” and are related to questions and documents. Many of these concepts are inherited from the industry dictionary associated with your site and some are user defined. For additional information on concepts, please refer to “Configuring Search” section of the Knowledge Advanced Documentation. The words inside the parenthesis are words that are known to the search engine. If the executive sees a lot of these “unknown” words, she may want to employ search tuning and add new concepts to the dictionary. This action will result in better findability of documents.

    • If the manager can see in the report, the total number of unique questions asked.  This may differ from the total questions asked if there questions that are repeated by users. This metric will give a guide on the variations in the way users ask questions when they visit your site.


    The following details describe the OKCS Questions table, which is a relatively new table to Oracle Service Cloud.  This table is part of the Knowledge Advanced functionality. 

    Table: OKCS Questions (okcs_stg_question) – This table holds the questions asked by users

    Table fields used in the report:

    • okcs_stg_question.created – The date that questions were asked
    • okcs_stg_question.okcs_stg_question_id – The unique ID of questions that are asked
    • okcs_stg_question.session_ref – Unique session ID where the search was performed
    • okcs_stg_question.question – The actual question/search term entered by the user
    • okcs_stg_question.normalized_question - This represents the normalized question text/phrase stem. A normalized question text is the set of searchable keywords of the question


    The report is laid out as follows:

         Top Level Report:

    Date: This is a grouping of the dates in a MM-DD-YYYY format so that you can see the counts in the subsequent columns by day.

    date_format(okcs_stg_question.created, 'MM-DD-YYYY')

    Total # of Questions, Total # of unique Questions, Total # of unique Sessions columns:  These columns use count() and count(distinct ) functions to provide total counts for the number of questions asked and unique sessions.

    Total # of Questions: count(okcs_stg_question.okcs_stg_question_id)

    Total # of Unique Questions: count(distinct okcs_stg_question.normalized_question)

    Total # of Unique Sessions: count(distinct okcs_stg_question.sessoin_ref)


         Drill Down Report:   There is a drill down on the second column in the top report.

    The Question Text, Phrase Stem columns provide details on the question text that was entered into the search.

    Question Text: okcs_stg_question.question

    Phrase Stem: okcs_stg_question.normalized_question

    The Question Count column uses a count() function to provide the number of times the search text was entered on that day

    Question Count: count(okcs_stg_question.okcs_stg_question_id)


    There are filters on the Question Created (okcs_stg_question.created) and interface )(okcs_stg_question.interface) database fields.


    Aside from the above, there are no special techniques need to create the report. Standard report creation techniques that are available in Oracle Service Cloud will suffice.


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

    • Human readable PDF report definition
    • XML Report definition 
    • Screen shot 



    • 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 Ramesh'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

    • Danette Beal


      Thank you for this great report recipe for Knowledge Advanced!! That is great stuff and we definitely appreciate it!


      Danette, Community Manager

    • Sean FitzGerald

      Ramesh,  Nice addition to the community for KA enabled sites.  I am working to help my org get the most out of our KA enabled side and look forward to reviewing any and all KA enabled site analytics.