Reporting and Analytics for Service

Get Involved. Join the Conversation.


    Bastiaan van der Kooij
    Top Keywords Trend - Analytics Cookbook Recipe
    Topic posted January 9, 2017 by Bastiaan van der KooijGold Crown: 30,000+ Points, last edited June 22, 2017 
    669 Views, 1 Comment
    Top Keywords Trend - Analytics Cookbook Recipe

    Top Keywords Trend

    Show the trend of todays most used keywords


    Author: Bastiaan van der Kooij

    Independent Oracle Service Cloud Consultant


    Difficulty rating: (Advanced)

    Target persona: KB Administrator


    This report shows the Keywords that are most used today and how much they were used in the previous days. It gives an intuitive, real-time overview of what your customers are currently looking for and whether current keyword searching is out of the ordinary. Suppose a keyword that is used many times today has not been used the last couple of days, this could be an indication that there is a problem with your service or products and people suddenly start searching for solutions. Time to either make sure people find the right solution by adding answers or notify technical departments of a potential situation that needs to be looked into. By using the predictive feature the report will show you how many the times the keywords might be used by the end of the day, based on the usage of the previous days. This is particularly useful to show the potential impact of a keyword and determine whether action is required or not. The prediction gets more reliable during the day when more data is recorded from end user activity. This report gives a summary...

    7 Columns using formulas (date_trunc, count, sum, if / then / else, date_format)

    2 Filters

    5 Variable

    1 Group Filter

    1 Custom Script

    Overall description/purpose: This report uses a custom script to deal with the situation whereby you want to show the trend data of only those records that have certain aggregate result. This sentence sounds a bit cryptic, but for this situation it comes down to only wanting to show data for the top records for today. Suppose you create a report that shows the top 10 keywords for today, how would you get the data for those keywords from the last couple of days? To do this in a SQL query you would create a subquery to first get the top 10 keywords and use those keywords to get the data for all days. This is not possible in Analytics, therefore, the custom script does all the heavy lifting and the report itself simply provides all keyword data.

    User Case Being Served: 

    As stated above the report simply gets all keywords that were used since the selected ‘From Date’ filter value and displays the count per keyword per day. Once the report returns the data, the custom script kicks in and performs the following actions:

    1. Store all the data from the top keywords and sort it by date
    2. Remove all existing rows in the report
    3. Determine the prediction for today if this is switched on
    4. Output the data from the top keywords

    One bar graph shows the usage of the keywords today and a line graph shows the predicted trend for the keywords. The data itself is displayed using a cross tab.


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

    Attachments included are the following (in a .ZIP file)

    • Human readable PDF report definition
    • Complete Report Recipe and Report Configuration Details (PDF)
    • 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 Bastiaan'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. and consider sharing details on if/how you changed this report to fit your unique requirements.

      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