What Is the Database Schema of the Different Tables That I See When Doing an Export?

By default, any exported analytics database contains the five following tables: clicks, custom events, groups, keywords, and searches (see Managing Usage Analytics Data Exports).

Each search event can involve many clicks, groups and keywords (a query can be followed by 0 to n clicks, a query can contain 0 to n keywords and the user submitting a query can belong to 0 to n groups). The Search ID is thus the link between clicks and searches, between keywords and searches, and between groups and searches.

Custom events is a special table that contains the non-search events in the system. Two different IDs can be used to create a relation between the custom events and the other tables. The Visit ID can be used to relate a custom event with all queries and clicks in the same visit (for instance, all the queries that preceded a Case Deflection event in a user visit on the community site). The Last Search ID contains the query (if any) that immediately preceded the custom event. It is thus possible to do a relation between a custom event and the last query done by the user who triggered the custom event.

The five tables in the CSV files contain columns titled with the API names of the dimensions you use in reports. Therefore, knowing which API name refers to which dimension can be difficult. The following table lists in alphabetical order the API names and the actual dimensions they refer to.

In addition to these default columns, any created dimension will have its own column (see Managing Dimensions on Custom Metadata).

API name Dimension
Clicks
anonymous Is Anonymous
cause Click Cause
c facettitle Facet Title
c facetvalue Facet Value
city City
clickId

N/A

This column contains the unique ID for each click event.

clickRank Click Rank
collectionName Collection Name
country Country
customDatas

N/A

This column contains all custom dimensions and their value, displayed in the following format and separated by comma: "customDimensionName":"customDimensionValue"

datetime Click Date Time
device Device
documentTitle Document Title
documentUrl Document URL
language Language
mobile Is Mobile
originContext Origin Context
queryPipeline Query Pipeline
region Region
searchId

N/A

The Search ID is used in tables as the link between clicks and searches, keywords and searches, and groups and searches.

sourceName Source Name
systemUriHash Search Result (see Usage Analytics Dimensions
userId User ID
username User Name
visitId Visit ID
visitorId

Visitor ID

Searches
advancedQueryExpression Advanced Query Expression
anonymous Is Anonymous
batchId

N/A

Values in this column are destined for future use.

cause Search Cause
city City
country Country
datetime Search Date Time
device Device
c facettitle Facet Title
c facetvalue Facet Value
id

N/A

The Search ID is used in tables as the link between clicks and searches, keywords and searches, and groups and searches.

language Language
mobile Is Mobile
numberOfResults Number of Results
originContext Origin Context
pageNumber Page Number
pageSize

N/A

Values in this column are destined for future use.

queryExpression Query
queryPipeline Query Pipeline
region Region
responseTimeMS

N/A

This column contains the time taken by each query to return.

searchOriginLevel1 Origin 1 (Page/Hub)
searchOriginLevel2 Origin 2 (Tab)
searchOriginLevel3 Origin 3 (Referrer)
userAgent User Agent
userId User ID
username User Name
visitId Visit ID
visitorId

Visitor ID

withClicks Has Clicks
withResults Has Results
Custom Events
anonymous Is Anonymous
city City
country Country
customEventId

N/A.

This column contains the unique ID for each custom event.

dateTime Custom Event Date Time
device Device
eventType Event Type
customEventValue Event Value
c facettitle Facet Title
c facetvalue Facet Value
language Language
lastSearchId

N/A

The Last Search ID contains the query (if any) that immediately preceded the custom event and. Last Search ID is used as a link between a custom event and the last query done by the user who triggered the custom event.

mobile Is Mobile
originContext Origin Context
region Region
userId User ID
userName User Name
visitId Visit ID
visitorId

Visitor ID

Keywords
keyword Keyword
searchId

N/A

The Search ID is used in tables as the link between clicks and searches, keywords and searches, and groups and searches.

Groups
groupName Group
searchId

N/A

The Search ID is used in tables as the link between clicks and searches, keywords and searches, and groups and searches.