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.
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.
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)
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.
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
A couple things up front:
- 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.
- 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.
- 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"
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