Community

Product DocsMenu

Usage Analytics Database Content

You may want to take advantage of data collected by the Usage Analytics module by directly querying the Usage Analytics database. This section presents detailed information on the content of the Usage Analytics database.

This topic contains the following sections:

Usage Analytics database schema

The following schema presents all the tables and the relations between the tables of the Usage Analytics database.

Content of Usage Analytics database tables

The following table describes the content of each Usage Analytics database table.

Table Description
Users Represents a user that accesses one of the .NET search interfaces monitored by the Usage Analytics module.

  • userId: (PK) The unique identifier of the user.

  • userCreatedDate: The date at which the user was created.

  • userName: The full name of the user.
UserMetadata Represents metadata related to a specific user. This entity is used to store additional information (see Customizing the Collected Usage Analytics Data).

  • usermetadataId: (PK) The unique identifier of the user metadata.

  • usermetadataName: The name of the metadata.

  • usermetadataValue: The value of the metadata.

  • userId: (FK) The user related to this metadata.
Sessions Represents a sequence of interactions between a user and a .NET search interface. Looking at the sequence of actions in a session allows administrators to determine if a search session was a success or not.

  • sessionId: (PK) The unique identifier of the session.

  • sessionCreatedDate: The date at which the session was created.

  • sessionGuid: The globally unique identifier (GUID) of the session. This value is required by the .NET search interface as there is a strong distinction between the search user interface and the mechanism responsible for writing in the database.

  • userId: (FK) The user related to the session.
SessionMetadata Represents the metadata related to a specific session. This entity is used to store additional information (see Customizing the Collected Usage Analytics Data).

  • sessionmetadataId: (PK) The unique identifier of the session metadata.

  • sessionmetadataName: The name of the metadata.

  • sessionmetadataValue: The value of the metadata.

  • sessionId: (FK) The session related to the metadata.
SearchInterfaces Represents the .NET search interfaces on which data is collected.

  • searchInterfaceId: (PK) The unique identifier of the .NET search interface.

  • searchInterfaceCreatedDate: The date at which the .NET search interface was created.

  • searchIntercafeName: The name of the .NET search interface.
Actions Represents any action that can be performed on a .NET search interface. This table is the central part of the Usage Analytics database design.

  • actionId: (PK) The unique identifier of the action.

  • actionCreatedDate: The date the action was performed.

  • actionGuid: The globally unique identifier (GUID) of the action. This value is required by the .NET search interface as there is a strong distinction between the search UI and the mechanism responsible for writing in the database.

  • actionType: The type of the action (see Logged actions).

  • actionOutputType: The type of output the action has generated (ex.: web, RSS, xml, etc.).

  • sessionId: (FK) The session in which the action was performed.

  • searchInterfaceId: (FK) The .NET search interface the action was performed on.

  • searchHubId: (FK) The search hub the action was performed from.

  • parentActionId: (FK) The action ID of the parent of the action.

  • actionResponseTime: The time required to perform the action.

  • rootActionId: (FK) The action ID of the root of the action.

  • queryId: (FK) The query ID related to this action.
ActionValues Represents the various values related to an action.

  • actionId: (PK) (FK) The action ID the action value is related to.

  • actionValueName: (PK) The name of the action value.

  • actionValueStringValue: The string representation of the action value.

  • actionValueIntegerValue: The integer representation of the action value.

  • actionValueDoubleValue: The double representation of the action value.

  • actionValueDateTimeValue: The date and time representation of the action value.
Queries Represents the various queries entered by users.

  • queryId: (FK) The unique identifier for the query.

  • queryExpression: The expression of the query
SearchHubs Represents the various search hubs.

  • searchHubId: (PK) The unique identifier of the search hub.

  • searchHubCreatedDate: The date at which the search hub was created.

  • searchHubName: The name of the search hub.
Results Represents the results on which an action is performed. This entity is a bridge between an action and documents.

  • actionId: (PK) (FK) The action that targets the result.

  • documentId: (PK) (FK) The document targeted by the result.

  • resultIndex: The order in which the result was displayed.

  • resultIsTopResult: Boolean indicating when the result is a Top Result.
Documents Represents documents that are available for the search. Basically, it holds information to find this document in the index.

  • documentId: (PK) The unique identifier of the document.

  • documentCreatedDate: The date at which the document was created.

  • documentAggregatedMirrorId: The ID of the aggregated mirror the document came from.

  • documentPhysicalIndexName: The name of the physical index the document came from.

  • collectionId: (FK) The ID of the collection the document came from.

  • sourceId: (FK) The ID of the source the document came from.

  • documentUrl: The URL of the document.
DocumentMetadata Represents metadata related to a specific document. This identity is used to store additional information (see Customizing the Collected Usage Analytics Data).

  • documentmetadataId: (PK) The unique identifier of this document metadata.

  • documentmetadataIndexedDate: The date this document was indexed.

  • documentmetadataName: The name of the metadata.

  • documentmetadataValue: The value of the metadata.

  • documentId: (FK) The document related to this metadata.
Collections Represents collections from which sources come from.

  • collectionId: (PK) The unique identifier of this collection (@syscollection from CES).

  • collectionIndexedDate: The date at which the collection name was indexed.

  • collectionName: The name of the collection.
Sources Represents sources from which documents come from.

  • sourceId: (PK) The unique identifier of this source (@syssource from CES).

  • sourceIndexedDate: The date the source name was indexed.

  • sourceName: The name of the source.
ActionKeywords Represents the various keywords related to an action.

  • actionId: (FK) The ID of the action related to this keyword.

  • keywordId: (FK) The unique identifier of the keyword
Keywords Represents the various query keywords.

  • keywordId: (FK) The unique identifier of the keyword

  • keywordValue: The keyword itself.
Versions (Internal Coveo Table) Represents the version of this database. This is a utility table used by CES to determine if the database is up-to-date.

  • versionId: (PK) The unique identifier of the version.

  • versionCreatedDate: The date the version was installed.

  • versionNumber: The main version number (for example "4" in 4.0).

  • versionData: The data version number (for example "0" in 4.0).

Relations between tables in the Usage Analytics database

The following table provides a description of the relations between Usage Analytics database tables.

Table Relation Description
User – UserMetadata Users can have zero or more metadata. By default, CES does not store metadata on users. This table is filled by custom data (see Customizing the Collected Usage Analytics Data).
User – Session A session must be initiated by a user. If a .NET search interface allows anonymous connections, then the anonymous user initiates all the sessions on that .NET search interface.
Session – SessionMetadata Sessions can have zero or more metadata. By default, CES does not store metadata on sessions. This table is filled by custom data (see Customizing the Collected Usage Analytics Data).
Session – Action An action must be performed on a specific session. This relation allows administrators to link every action performed to a specific user.
SearchHub – Action An action can be performed on a specific search hub. This allows administrators to track statistics on specific search hubs.
Search Interface – Action An action must be performed on a specific .NET search interface. This allows administrators to track statistics on specific .NET search interfaces.
Parent Action – Action A series of actions can be performed to complete a search session. This relation allows administrators to follow the search path of a user through the .NET search interface.
Root Action – Action Actions are based on a root action. A root action is the first action of a search path of a user through the .NET search interface.
Action – ActionValue An action contains one or more action values (see Logged values).
Action – Result An action contains zero or more search results depending on the action type.
Document – Result A document is linked to one or more search results across the actions.
Document – DocumentMetadata Documents can have zero or more metadata. By default, CES does not store metadata on documents. This table is filled by custom data (see Customizing the Collected Usage Analytics Data).
Source – Document Documents must be located in a source.
Collection– Document Documents must be located in a collection.
Action – ActionKeyword An action can have zero or more action keywords depending on the action type.
Keyword – ActionKeyword A keyword is linked to one or more action keyword across the actions.
Query – Action A query is linked to one or more action.

Logged actions

The following table lists the action types logged by the Usage Analytics module. You can overwrite these actions in ASP.net (see Customizing the Collected Usage Analytics Data).

Logged action type Logged action name
CHANGE_INTERFACE "ChangeInterface"
CLEAR_REFINE_BY_FIELD "ClearRefineByField"
DID_YOU_MEAN "DidYouMean"
EXPAND_QUERY "ExpandQuery"
EXPORT_TO_EXCEL "ExportToExcel"
FILTER_REFINE_BY_FIELD "FilterRefineByField"
MANUAL_RATING "ManualRating"
OPEN_CACHED_VERSION "OpenCachedVersion"
OPEN_DOCUMENT "OpenDocument"
PAGE_CHANGE "PageChange"
PERFORM_SEARCH "PerformSearch"
RATE_THIS_SEARCH "RateThisSearch"
REFINE_BY_CLUSTER "RefineByCluster"
REFINE_BY_FIELD "RefineByField"
REFINE_BY_SCOPE "RefineByScope"
REMOVE_REFINE_BY_CLUSTER "RemoveRefineByCluster"
REMOVE_REFINE_BY_FIELD "RemoveRefineByField"
REMOVE_REFINE_BY_SCOPE "RemoveRefineByScope"
RSS_LINK "RSSLink"
SEARCH_WITHIN_RESULTS "SearchWithinResults"
SHOW_DETAILS "ShowDetails"
SHOW_IN_DETAIL_VIEW "ShowInDetailView"
SORT_BY_FIELD "SortByField"

Logged values

The following table lists the value types logged by the Usage Analytics module.

Logged value type Logged value name
CLUSTER_NAME "ClusterName"
DOCUMENT_SCORE "DocumentScore"
FACET_TYPE_NAME "FacetTypeName"
FIELD_LOOKUP "FieldLookup"
FIELD_NAME "FieldName"
FIELD_VALUE "FieldValue"
OUTPUT_TYPE "OutputType"
PREVIOUS_INTERFACE "PreviousInterface"
PREVIOUS_PAGE "PreviousPage"
QUERY_ADVANCED_EXPRESSION "QueryAdvancedExpression"
QUERY_AFTER "QueryAfter"
QUERY_BEFORE "QueryBefore"
QUERY_EXECUTION_TIME "QueryExecutionTime"
QUERY_MODE "QueryMode"
QUERY_SORT_CRITERIA "QuerySortCriteria"
QUERY_SOURCE
"QuerySource"
QUERY_SUPER_USER_TOKEN
"QuerySuperUserToken"
QUERY_SUPER_USER_TOKEN_NAME
 "QuerySuperUserTokenName"
QUERY_TOTAL_COUNT "QueryTotalCount"
QUERY_WAS_CHANGED
"QueryWasChanged"
REFINE_TYPE "RefineType"
REPORT_COMMENT "ReportComment"
REPORT_SCORE "ReportScore"
REQUESTED_PAGE "RequestedPage"
SCOPE_CAPTION "ScopeCaption"
SCOPE_EXPR "ScopeExpr”
SORT_ORDER "SortOrder"
People who viewed this topic also viewed