10 Replies Latest reply: Jun 20, 2011 6:41 PM by Nikki Bussard RSS

    Analytics for Search History & Rating - Appear to be missing from Analytics DB

    john.tarbutton

      Hello All,

       

      I am attempting to retreive some specific reporting information regarding User Search history and Content Ratings.  I am currently working within SBS 3.0 (with plans to upgrade to 4) and I am aware of the analytics data model in version 4 (it appears to have added several new dimensions - community, sgroup, tag).

       

      Search History

      In regards to search history, I am attempting to build two reports that show the following:

      1. Report showing the ranking of the keywords searched over a period of time

      2. Reporting showing whether a search was abandoned or clicked-through

       

      In my analytics environment I can see activity fact records with the following activity types (SEARCH_CONTENT 170, SEARCH_USERS 180) but they are always associated to a single object record of type -1, not sure what -1 represents and in the JIVEDW_OBJECT table has an object name of CONTENT.

       

      Based on this information, as far as I can tell, I don't see anyway to rank keyword searches or discern whether a search was clicked-through or not.  Am I missing anything?

       

      Rating Total

      I am attempting to build a report that will provide a ranking of the highest rated content items per community.

       

      In my analytics environment I can tell how many times a particular content item was rated on (i.e. a document) by looking in the activity fact table for activity type RATE_OR_VOTE 100.  But I am not able to see anywhere where the actual rating value is stored.

       

      Is anyone aware of this type of information being available in some format in the analytics db?

       

      Thanks in advance!

       

      John

        • Re: Analytics for Search History & Rating - Appear to be missing from Analytics DB
          Mark Williams

          John,

           

          There are three types of search event we capture:

           

          SEARCH_CONTENT(170) - this occurs when the users executes a full search
          SEARCH_USERS(180) - this occurs when a search is executed against the user index (we maintain a different search index for the user store)
          SPOTLIGHT_SEARCH(190) - this occurs when the user starts typing text into the search box but does not hit enter. SBS will pro-actively execute a search based on the content typed so far and display a drop down beneath the text box.

           

          Search events do not correspond to any existing content object in the system since it's just a search string. Therefore there is no associated direct object from a dimension. Instead we capture the text string in the METADATA column of the base activity fact table. Since this is the lowest granularity this cannot be aggregated and so does not appear in any of the aggregate tables.

           

          Note also that the fix to capture this data was applied to 3.0.15 and 4.0.2, so unfortunately this information is not available in earlier patch releases.

           

          So I believe it should be possible to build reports to determine the popularity of certain keywords over time.

           

          Tracking whether a search was abandoned or whether a user performed a click through would be much harder. Unlike web site analytics tools, we do not capture actual web site links clicked, but rather the underlying event in the system. The system might report a search event followed by a read event (say on a document), but there is no way to know how that document had been reached. It may have been reached by the user clicking on the search result, but it may also have been reached simply by the user clicking on a bookmark, or possibly a link from a completely different web browser session which they had open. Web site analytics tools which monitor actual browser clicks are more appropriate for this kind of analysis.

           

          Unfortunately rating scores are not current available in analytics  - only the event that a rating occured. This data will be available in the next major release due out this year.

           

          Hope that helps.

           

          --Mark

            • Re: Analytics for Search History & Rating - Appear to be missing from Analytics DB
              john.tarbutton

              Mark,

               

              Thanks for the quick response - that helps a lot!

               

              A couple follow up questions:

               

              1. Regarding the click-through event tracking - Does the Jive App Database not provide some of this information in the JiveSearchClick table?  I understand this information is not in the analytics database currently but just curious if that information resides in the App Database.

               

              2. Great to hear the rating data will be available in a release this year.  Do you know specifically which release that is (mid or late year)?

               

              Thanks for all the help.

               

              John

                • Re: Analytics for Search History & Rating - Appear to be missing from Analytics DB
                  Mark Williams

                  John,

                   

                  Yes, there is a JiveSearchClick table which I believe would provide this information. Unfortunately this table is also not available in the analytics datamart and we had no plans scheduled to do so.

                  The Rating information (along with several other new data types and improvements) will be available in our upcoming release due to be scheduled some time mid this year (possibly June timeframe)

                    • Re: Analytics for Search History & Rating - Appear to be missing from Analytics DB
                      Nikki Bussard

                      Hi, was this rating information ever made available? We just started using Jive analytics, so i am assuming we are on the latest release?

                        • Re: Analytics for Search History & Rating - Appear to be missing from Analytics DB
                          RyanSe

                          Do you have access to the analytic and application databases? If so, I have a couple queries you could use. The only thing I haven't really investigated so far is the clickthrough reporting but I have SQL queries to pull search phrases over a specific time period and a query that will pull ratings at the document level and which space it is in.

                            • Re: Analytics for Search History & Rating - Appear to be missing from Analytics DB
                              Nikki Bussard

                              Hi Ryan,

                              We're on the SAAS version, so I am not sure if we have access to those databases or not. Any idea?

                               

                              We're using Google Analytics to track search terms, but our users are interested in geting average rating given to all content in a given container within a month and percent of all content rated in a month. The latter would be calculated as follows: the units which receive ratings will be numerator (X) and the total number of units will be the denominator (Y) and the metric score will be (X/Y)*100 for %. This will be applicable to new ratings in a calendar month only

                                • Re: Analytics for Search History & Rating - Appear to be missing from Analytics DB
                                  RyanSe

                                  A couple things up front:

                                   

                                  1. If you don't already do local development work (even though your solution is hosted) you can request that your database be put up on the Cloud Admin site so you can download and set up a local copy to play around with the data.
                                  2. If you just need the results of the query you might be able to work with your support folks at Jive to just run the query and export the results for you.

                                   

                                  Since I run a lot of queries and enjoy playing with the data and learning the schema, I have opted for #1 and it works out well for me.

                                   

                                  So down to the ratings now. Below you will find a section of code I am currently working on to get space level reporting on documents since it fits our current use case (we use spaces for "official" company content and groups are more "clubs"). I personally tend to just manipulate the tables via MS Access since I have some other data sources where I don't have direct database access and we publish reports vs. letting folks connect to the databases directly.

                                   

                                  This will give you the average rating of all documents within your spaces and allow you to change the date range to meet your specifications. Since groups and spaces are different container types, they live in their own tables. I haven't found a table that pulls them both together so far. Normally, if I need reporting for both, I just create a query for each then just stack them together.

                                   

                                  I haven't done a calculation for % rated per month yet. It is on my long list of queries I want to build in the future though. I am wrapping up my adoption/engagement metric reporting projects right then I will move on to finish up our content health/quality program as well which is when I will work on things like how often are objects being rated, avg. rating, interval usage patterns, content aging, etc.

                                   

                                  Tables Involved:

                                  • jiveacclaimvote - stores the individual's rating
                                  • jiveacclaim - stores the default "average" you see in the UI (so basically just does it own averaging based on all scores so far)
                                  • jivedocument - the master "inventory" table where most document properties live
                                  • jivecommunity - the list of all the spaces in the community
                                  select jivecommunity.name, avg(jiveacclaimvote.votevalue) as "Average Rating"
                                  from jiveacclaimvote
                                  left join jiveacclaim on jiveacclaim.acclaimid = jiveacclaimvote.acclaimid
                                  left join jivedocument on jivedocument.internaldocid = jiveacclaim.objectid
                                  left join jivecommunity on jivecommunity.communityid = jivedocument.containerid
                                  where jiveacclaim.acclaimtype = 'rate' and jiveacclaim.objecttype = 102 and jivedocument.containertype = 14 and to_timestamp(jiveacclaimvote.creationdate/1000) between '1/1/2010' and '7/1/2011'
                                  group by jivecommunity.name
                                  Order by "Average Rating" asc
                        • Re: Analytics for Search History & Rating - Appear to be missing from Analytics DB
                          Susan Fariss

                          Are SEARCH_CONTENT(170), SEARCH_USERS(180), and SPOTLIGHT_SEARCH(190) activity types or content types. Trying to find out how to build a report of search terms and wasn't sure where it resided.