Lookup Relations Tables

All actions of users and share recipients end up as events in the Events table. The table below details all of the available lookup tables and the fields we return when you call them.

Things to keep in mind

Aggregating Returned Data

It's important to distinguish between events when aggregating returned data. If you export details per asset page (pageBased=true), the events export will return:

• an event for every viewed page of a document
• an event for the whole document (the page field will display the value NULL).

Excluding Deleted Records

Reports in the Online Platform automatically filter out data for deleted users, assets, user groups, etc. In order for you to replicate this behavior, you must execute a filter based on the deletedAt field in the relevant tables.

Event Timestamps

Because Showpad tracks events that take place offline and sends the event data to the server when the device has an online connection, it's important to be aware that both the occurance of the event and the logging of the data is recorded.

An event has the following fields:

Field	Description
startTime	The actual start time of the event
endTime	The actual start time of the event
loggedAt	The time the event was received in the Showpad backend. Note: This can be significantly different from the startTime and endTime values in offline use cases.

It is therefore important to use the correct parameters when executing API calls:

• startedAt and endedAt can be used to filter events based on their actual time of occurance.
• loggedBefore and loggedAfter can be used to filter events based on the time they were received in the Showpad back-end.

See the Events reference for details about the available parameters.

Tables

Events

Field	Description
eventId	Identifier of an event. An event is an interaction of a user within the Showpad Web app, Windows Desktop app, or iOS/Android apps.
startTime	Timestamp of the start of an event.
endTime	Timestamp of the end of an event.
loggedAt	Timestamp of the data logged to the server. Note: This can differ significantly from startTime and endTime in offline use cases.
userId	Identifier of a user.
channelId	Identifier of a channel. A channel is the technical API name for an Experience (Classic, Advanced, or created from a Showpad App).
shareId	Identifier of a share. A share can be an email or link share sent via the Showpad Web app, Windows Desktop app, iOS/Android apps, or a plugin.
deviceId	Identifier of a user's device.
divisionId	Deprecated field. Do not use this for linking to the Divisions lookup table. The correct divisionId is found on the Assets lookup table.
assetId	Identifier of an asset.
contactId	Identifier of a contact.
type	Type of event.
page	Page of the asset (for view events only). NULL for all other cases or the "all pages" view event Only filled when an export is executed with pageBased=true Values are: non-HTML5 assets - numeric HTML5 assets- text/string
metadata	Some experiences have some extra metadata on usage data.
sharedSpaceId	Identifier of a Shared Space.
sharedSpaceParticipantId	Identifier of a Shared Space participant.

Users

Field	Description
userId	Identifier of a user.
createdAt	Timestamp when the user was created.
firstName	A user's first name.
lastName	A user's last name.
emailAddress	A user's email address. This is also the user's login.
deletedAt	Timestamp when the user is deleted. When used, any events after this date will be excluded from reporting.
isActive	Indicates the user has been assigned a license and is an active in Online Platform reporting.
isForgotten	Indicates a user has been anonymized for GDPR reasons (e.g., deleted users are anonymized after a specified amount of time according to the configuration in the Online Platform). For some geographical locations, the emailAddress, firstName and lastName of active users can be anonymized.
userRole	The role of a user within Showpad: Admin Manager Promoted Member User
managerId	The identifier of the Manager user.
externalId	The user's identifier in an external system.

Usergroups

Field	Description
userGroupId	Identifier of a user group.
name	Name of the user group.
deletedAt	Timestamp when the user group is deleted. When used, any events after this date will be excluded from reporting.

User-usergroups

Field	Description
userId	Identifier of a user.
usergroupId	Identifier of a user group.

Devices

Field	Description
deviceId	Identifier of a user's device.
appUsed	On a registered device record, we keep multiple fields to identify the device, such as: operating system device model or browser user agent type of device (web, browser, tablet, trackingPixel) app that was used For example, if you use the Showpad for Chrome extension, Showpad would recognize a browser with a specific user agent. The app field tells us which app was being used, i.e. Showpad for Chrome. This field was introduced in mid-2016, so data before then is not available. When the data is not available, we use the type of device (tablet). Possible values: Value Description web The Showpad Web app is used in a browser. android The Android app is used. ios The iOS app is used. windows The legacy Windows app is used. windows-wda The legacy Windows Desktop App is used. windows-wda-2.0 The current Windows Desktop App is used. app scv The Shared Content Viewer is used. This is what share recipients use for viewing assets. outlook The Showpad for Outlook plugin is used. gmail The Showpad for Chrome extension is used in Gmail. tablet This is a fallback value for device type (tablet, browser, phone). It's used when: a device doesn't send app data app data is unavailable (e.g., before creation of the app field). dealroom This is an alternative name for Shared Spaces. Used for external users. Si-app The Shares tab in the Showpad Web app is used. Crm-app The Showpad for Salesforce app is used.
lastActiveUserId	The identifier of the last user logged in on a device. If the event is not related to a Showpad user, this field is NULL.
model	The model of a device used (if available).
location	The last recorded location of a device (if available).
type	The type of device used. This mapping is based on the model and app. This facilitates ensuring reporting on the device is identical to the reporting in the Online Platform. Possible values: desktop tablet phone

Shares

Field	Description
shareId	The identifier of a share.
title	The title of a share.
type	The type of a share. Possible values: Value Description email An email share via Showpad. link A link share via Showpad. gmail A share via Gmail in the Showpad for Chrome extension. outlook A share via Showpad for the Outlook plugin
salesforce	Indicates if a share is logged to Salesforce or not. Possible values: TRUE FALSE
theme	The name of the sharing theme used for a share (simple shares only).
recipients	List of share recipients. This is typically recipient email address. If no email address is available, we store the name (if available). If data is anonymized, you will see hash values when: the security settings require anonymization after X months. a recipient doesn't give consent to store their data. the share is a reshare. We don't display reshare email addresses for privacy reasons until consent is given. the share is a link share. This does not facilitate to capture an address or name. the share is done via the Marketo integration or the Showpad for Chrome extension. These are technically link shares for which email addresses may or may not be available. When data is available, it's provided.
sharedAt	The timestamp of the share record creation. This should be close to the timestamp of the share event itself.
revokedAt	The timestamp of when a share is revoked (not deleted).
creatorId	The identifier of the user that created the share.

Sharedspaces

Field	Description
shareSpaceId	The identifier of a Shared Space.
title	The title of a Shared Space.
ownerId	The identifier of the owner of the shared space. This corresponds to the userID in the Users table.
sharedSpaceParticipantsId	An array of all participants in a Shared Space. This corresponds to the sharedSpaceParticipantsId in the Sharedspaceparticipantstable.
description	A description of the Shared Space.
status	The status of the Shared Space. Possible values: active archived deleted
creatorId	The identifier of the Shared Space creator. This corresponds to the userID in the Users table.
createdAt	The timestamp of the Shared Space creation. This should be close to the timestamp of the event.
lastActivityAt	The timestamp of the last activity in the Shared Space.
deletedAt	The timestamp of when a Shared Space is deleted.

Sharedspaceparticipants

Field	Description
sharedSpaceParticipantId	The identifier of a participant in a Shared Space. internal participant - The userID is used. external participant - If available, the firstName, lastName, emailAddress, organisation, or jobTitle is used.
firstName	A participant's first name.
lastName	A participant's last name.
emailAddress	A participant's email address. This is also their login.
organisation	The name of the participant's organization (company).
jobTitle	The participant's job title.
userId	The identifier of an internal Shared Space user. This corresponds to the userID in the Users table.
accessGrantedAt	The timestamp of when the participant was granted access to the Shared Space.
accessRevokedAt	The timestamp of when the participant's access to a Shared Space was revoked.
lastActivityAt	The timestamp of the participant's last activity in the Shared Space.
deletedAt	The timestamp of when a participant was removed from a Shared Space.
sharedSpaceId	The identifier of a Shared Space.
createdAt	The timestamp of the participant's creation in a Shared Space.

Sharedspace-items

Field	Description
sharedSpaceItemId	Identifier of an item in a Shared Space.
sharedSpaceId	Identifier of a Shared Space.
userId	Identifier of a user.
assetId	Identifier of an asset.
page	When adding specific page(s) of an asset (rather than the entire asset), the page number (zero-based) of the specific page(s) is used.
createdAt	Timestamp of an item's addition to the Shared Space.
updatedAt	Timestamp of an item's last modification in the Shared Space.
deletedAt	Timestamp of an item's deletion from the Shared Space.

Contacts

Field	Description
contactId	Identifier of a Share recipient.
name	The name of the contact.
userId	Identifier of the user that created the contact. Note, this can be different from the user that generates the Share.
deletedAt	Timestamp of the contact's deletion. In the Online Platform, any events after this date will be excluding from reporting.

Divisions

Field	Description
divisionId	Identifier of a Dvision.
divisionName	The name of the Division.
isGlobal	Indicates if the Division is a Global Division.
isPersonal	Indicates that the source of the assets is the user's My Files (a personal division).
ownerUserId	The identifier of the user that owns a personal division (My Files). This corresponds to the userID in the Users table.
deletedAt	Timestamp of the division's deletion. In the Online Platform, any events after this date will be excluding from reporting.

Division_usergroups

Field	Description
divisionId	Identifier of a Division.
usergroupId	Identifier of a user group.

Channels (Experiences)

Field	Description
channelId	Identifier of an Experience.
name	The name of an Experience.
status	Deprecated. Please do not use this field.
deletedAt	Timestamp of the deletion of the Experience (channel). In the Online Platform, any events after this date are not captured.

Assets

Field	Description
assetId	Identifier of an asset.
originalAssetId	When assets are added to a collection, a new (duplicate) asset is created in the background. As long as the asset in the collection remains unaltered, this field points to the original assetId it was based upon. This enables reports to group the asset analytics together, as the two assets are essentially the same. Note: When the asset in the collection is changed, the edited asset no longer has an originalAssetId that links it to the original asset, because the two assets are now different.
divisionId	Identifier of the Division an asset belongs to.
displayName	Name of an asset (included the extension).
slug	The unique identifying part of the asset's URL.
status	Status of an asset. During upload, an asset can have multiple statuses. In order to be used by a user, the status must active. Possible values: Value Description active The file has finished processing and is available in the library. inactive An issue with the file during processing and the user has canceled the upload in the processing tab. failed The file was unable to be processed. processing The file is being processed and optimized. queued The file is waiting to be processed. prepared The asset's metadata is created and persisted, but the binary file has either not yet been uploaded or is waiting to be processed. Tip: If a file is in the library but can't be used, it may have failed to process and does not have an active status.
source	Indicates if an asset was uploaded by an admin or as a personal user upload. Possible values: user generated marketing
description	Description of the asset given in the Online Platform.
type	The type of the asset. Possible values: 3d-model - audio - document - page - personal-video-recording - photo - raw - scorm - video - url - webapp (HTML5)
isSensitive	Indicates if asset can be shared. Possible values: TRUE FALSE
isDownloadable	Indicates if asset can be downloaded. Possible values: TRUE FALSE
isDivisionShared	Indicates if asset has global property so it can be shared across several divisions. Possible values: TRUE FALSE
isAnnotatable	Indicates if an asset can be annotated by user. Possible values: TRUE FALSE
isShareable	Indicates if an asset can be shared.
expiresAt	Expiration date of the asset.
releasedAt	Released date set for the asset.
deletedAt	Timestamp of the asset's deletion. In the Online Platform, all reporting will exclude events after this date.
uploadedAt	Timestamp of the asset's upload.
archivedAt	Timestamp of the asset's archival. In the Online Platform, the asset is in the Trash but not yet deleted.
createdAt	Timestamp of the asset's creation.
updatedAt	Timestamp of the asset's last update.
externalId	Assets uploaded via an integration often have a ID from the external system.
commentsCount	The number of an asset's comments.
likesCount	The number of an asset's likes.
optimizedFileSize	The size of the asset file in bytes.
originalFileSize	The size of the original asset file in bytes.

Asset-authors

Field	Description
assetId	Identifier of an asset.
authorId	Identifier of an author. Authors are also users, so this corresponds to the userID in the Users table.

Asset-countries

Field	Description
assetId	Identifier of an asset.
countryCode	Two-letter country code using the ISO 3166-1 alpha-2 standard.

Asset-languages

Field	Description
assetId	Identifier of an asset.
languageCode	Two-letter language code using the ISO 639-1 standard.

Tags

Field	Description
tagId	Identifier of a tag.
name	The tag.

Asset-tags

Field	Description
assetId	Identifier of an asset.
tagId	Identifier of a tag.

Tagcategories

Field	Description
tagCategoryId	Identifier of a tag category.
name	Name of the tag category.
path	Path to the tag category. Because a category can be a child to another category, the full parent path (based on tagCategoryId) is presented in "materialized path" format. Example Child is a child category of the Parent category, so Child has the tagCategoryId of Parent for path: /9c73f297c697c94e9afbfbe246230a16/. A Grandchild category as a child of Child would have path /9c73f297c697c94e9afbfbe246230a16/0ed2cfaa83e798a60d0490dc9578204a
publishedAt	Timestamp of the tag category's publication. If empty, the tag category is created for internal use, but is not available searching or browsing Experiences.

Tagcategory-tags

Field	Description
tagCategoryId	Identifier of a tag category.
tagId	Identifier of a tag.

Collections

Field	Description
collectionId	Identifier of a personal collection.
userId	Identifier of the user that owns the collection.
name	Name of the collection.
createdAt	Timestamp of the collection's creation.
updatedAt	Timestamp of the collection's last update.
deletedAt	Timestamp of the collection's deletion.

Collection-items

Field	Description
collectionItemId	Identifier of a collection item. All pages/assets in a collection are considered collection items.
collectionId	Identifier of a personal collection.
assetId	Identifier of an asset.
page	The page of the asset added. If the complete asset is added, the page field will be empty (NULL). If the original asset is updated, it is still the absolute page number which will be part of the collection.
position	The position in the collection of the asset.
createdAt	Timestamp of the collection item's creation.
updatedAt	Timestamp of the collection item's last update.
deletedAt	Timestamp of the collection item's deletion.