Lookup Relations Table

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.

Events

Field	Explanation
`eventId`	Identifier of an event; an event is an interaction of a user in the Showpad apps.
`startTime`	Timestamp of the event (start).
`endTime`	Timestamp of the event (end).
`loggedAt`	Timestamp of the logging of the event to the database (can be later in offline use case).
`userId`	Identifier of a user.
`channelId`	Identifier of a channel; A channel is an experience (classic, advances or experience app).
`shareId`	Identifier of a share; A share can be a shared space or simple share, a simple share can be sent via the mobile app, web app or a plugin.
`deviceId`	Identifier of a device.
`divisionId`	Deprecated field, please do not use this for linking division lookup table. The correct divisionId is found on the asset 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; page is also only filled when the export is executed with `pageBased=true`; Values are numeric for all non-HTML5 assets and can be text/string for HTML5 assets.
`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	Explanation
`userId`	Identifier of a user.
`createdAt`	Timestamp when the user was created.
`firstname`	First name.
`lastname`	Last name.
`emailaddress`	Email Address - also the login for the user.
`deletedAt`	Timestamp when the user is deleted; In the OP, all reporting will exclude events after this date.
`isActive`	Indicates the user has assigned a license (is active in user OP reporting).
`isForgotten`	Indicates the user has been anonymized for GDPR reasons; i.e. deleted users are anonymized after x months according to configuration in the OP. Also for some geographies active users can be anonymized; `emailAddress`, `firstname` and `lastname` will also be anonymized.
`userRole`	The role of the user inside Showpad. Can be one of: `Admin`, `Manager`, `Promoted Member`, `User`.
`managerId`	The identifier of the manager user.
`externalId`	The external id of the user.

Usergroups

Field	Explanation
`userGroupId`	Identifier of a user group.
`name`	Name of the group.
`deletedAt`	Timestamp when the user group is deleted; In the OP, all reporting will exclude events after this date.

User-usergroups

Field	Explanation
`userId`	Identifier of a user.
`userGroupId`	Identifier of a user group.

Devices

Field	Explanation
`deviceId`	Identifier of a device.
`appused`	On the device record that is registered, we keep multiple fields to identify the device, e.g. the operating system, the device model (device model or browser user agent), the type of device (`web`, `browser`, `tablet`, `trackingPixel`), and the app that was used. If you use the gmail or chrome extension, Showpad would recognize a browser with a certain user agent. We have the app field that tells us which Showpad app was being used, e.g. Showpad for Gmail. The app field was introduced somewhere mid-2016, so before that it is not filled in. Therefore, in case it is missing we just mention the type of device (`tablet`). Possible values: `web`: Showpad webapp is used (in the browser) `android`: Android app is used `ios`: iPhone or iPad app is used `windows`: old legacy windows app `windows-wda`: the legacy windows desktop app `windows-wda-2.0`: new windows desktop app `app scv`: Showcase Viewer, aka the share mini-website the recipient uses for viewing assets `outlook`: Outlook plugin `gmail`: Gmail plugin `tablet`: we fall back to show the device "type" (`tablet`, `browser`, `phone`) in case the device did not send any app (exception fallback case). `dealroom`: alternative name for Shared Spaces `Si-app`: shares tab in front-end `Crm-app`: SFDC app.
`lastActiveUserId`	Last user ID which logged on to this device. If the event is not related to a Showpad user, this field is `NULL`.
`model`	Model of the device (if available).
`location`	(last) location of the device (if available).
`type`	The mapping is based on model and app used. This is done to facilitate recreating identical reporting to that used in our Online Platform. Possible values: `desktop` `tablet` `phone`

Field	Explanation
`shareId`	Identifier of a share.
`title`	Title of the share.
`type`	Type of the share. Possible values: `email`: simple email share via Showpad. `link`: simple link share via Showpad. `gmail`: share via gmail plugin. `outlook`: share via Outlook plugin.
`salesforce`	Indicator whether logged to Salesforce or not.
`theme`	Name of the theme used for sharing (simple shares only).
`recipients`	List of recipients of the share. Typically recipient email address. If we don't have email but we have the name we store the name. If data is anonymized you will see numerical values (hash value): After x months data is anonymized according to account security settings. When recipients don't give consent. With reshares we are not allowed to store email addresses. With the use of links this is often not filled in (since we have neither address nor name). Marketo plugin and chrome extension are technically links but depending on the use case we sometimes have info. If we have info we store it here.
`sharedAt`	Timestamp of creation of share record (should be close to timestamp of event itself).
`revokedAt`	Shares can be revoked (not deleted). This is the timestamp.
`creatorId`	The user ID that created the share.

Sharedspaces

Field	Explanation
`shareSpaceId`	Identifier of a share.
`title`	Title of the shared space.
`ownerId`	Owner of the shared space - lookup to users.
`sharedSpaceParticipantsId`	Array with all participants - lookup to sharedspaceparticipants.
`description`	Description.
`status`	Status of the shared space. Can be one of: `active`, `archived`, `deleted`
`creatorld`	Create of the shared space - lookup to users.
`createdAt`	Timestamp of creation of shared space (should be close to timestamp of event itself).
`lastActivityAt`	Timestamp of last activity on the shared space.
`deletedAt`	Shared Spaces can be deleted; this is the timestamp.

Sharedspaceparticipants

Field	Explanation
`sharedSpaceParticipantsId`	Participant ID.
`userId`	Lookup to the user table.
`accessGrantedAt`	Timestamp this person was granted access.
`accessRevokedAt`	Timestamp this person was revoked access.
`lastActivityAt`	Timestamp of last activity of this participant.
`deletedAt`	Timestamp of removal of this participant.
`sharedSpaceId`	Identifier of a Shared Space.
`createdAt`	Timestap when the participant was created.

Sharedspace-items

Field	Explanation
`sharedSpaceItemId`	Item ID.
`sharedSpaceId`	Identifier of a shared space.
`userId`	Identifier of a user.
`assetId`	Identifier of an asset.
`page`	Page number (zero-based) in case a single page was inserted in the shared space.
`createdAt`	Timestamp when the item was added to the shared space.
`updatedAt`	Timestamp when the item was last modified in the shared space.
`deletedAt`	Timestamp when the item was deleted in the shared space.

Contacts

Field	Explanation
`contactId`	Identifier of a share recipient.
`name`	The name of the contact.
`userId`	User ID that created the contact (can be different than the user that generates the share).
`deletedAt`	Timestamp when the contact is deleted. In the OP, all reporting will exclude events after this date.

Divisions

Field	Explanation
`divisionId`	Identifier of a division.
`name`	The name of the division.
`isGlobal`	Indicates the division is a global division.
`isPersonal`	Indicates the source of assets is the user's My Files, which is in fact a personal division.
`ownerUserId`	In case of a personal division (My Files) this field contains the related `userId`.
`deletedAt`	Timestamp when the division is deleted. In the Online Platform, all reporting will exclude events after this date.

Division_usergroups

Field	Explanation
`divisionId`	Identifier of a division.
`usergroupId`	Identifier of a user group.

Channels

Field	Explanation
`channelId`	Identifier of an experience.
`name`	The name of the experience.
`status`	Deprecated field. Please ignore.
`deletedAt`	Timestamp when the channel is deleted. In the Online Platform, all reporting will exclude events after this date.

Assets

Field	Explanation
`assetId`	Identifier of an asset.
`originalAssetId`	When adding an asset to a collection, a new asset is created in the background. As long as the asset is unaltered from a content perspective, the originalAssetId will point to the original `assetId` it was based upon. This enables reports to group the asset analytics together, as the "different" assets are essentially the same from a business perspective. When an asset is changed, the edited asset no longer has an `originalAssetId` that links it to the original ID, as the two are also from business perspective different.
`divisionId`	Identifier of the division this asset belongs to.
`displayName`	Name of the asset (included the extension).
`status`	Status of the asset. During upload, an asset can have multiple statuses but to be used by a user it has to be "active". This can indicate, however, why a file is in the library but not used, i.e. it failed to process. Possible values: `active` - the file has finished processing and is available in the library. `inactive` - file processing identified an issue with this file, and the user has canceled this file in the processing tab. `failed` - file processing was unable to process the file. `processing` - the file is being processed & optimized for various platforms. `queued` - the file is in the queue, waiting to be processed. `prepared` - the asset metadata is created and persisted, but the binary file has either not been uploaded yet or is waiting to be processed.
`source`	An indication of whether the asset was uploaded by an admin (marketing-owned) or as a personal user upload.
`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` `url` `video` `webapp` (HTML5)
`isSensitive`	Indicator whether asset can be shared or not.
`isDownloadable`	Indicator whether asset can be downloaded or not.
`isDivisionShared`	Indicator whether asset has "global" property. This means it is shared across several divisions.
`isAnnotatable`	Indicator whether asset can be annotated by user.
`expiresAt`	Expiry date on the asset.
`releasedAt`	Release date of the asset.
`deletedAt`	Timestamp when the asset is deleted. In the Online Platform, all reporting will exclude events after this date.
`uploadedAt`	Timestamp when the asset is uploaded.
`archivedAt`	Timestamp when the asset is archived. In the Online Platform, that means the asset is in the Trash but not yet deleted.
`createdAt`	Timestamp when the asset was created.
`updatedAt`	Timestamp when the asset is last updated.
`externalId`	Asset uploaded via an integration often have a ID from the source system.
`commentsCount`	Number of comments.
`likesCount`	Number of likes.
`fileSize`	Filesize of the asset in bytes.

Asset-authors

Field	Explanation
`AssetId`	Identifier of an asset.
`AuthorId`	Identifier of an author. Authors are also users so the reference field is `users.userId`.

Asset-countries

Field	Explanation
`AssetId`	Identifier of an asset.
`CountryCode`	Country Code.

Asset-languages

Field	Explanation
`AssetId`	Identifier of an asset.
`LanguageCode`	Language Code.

Field	Explanation
`tagId`	Identifier of a tag.
`name`	Tag.

Asset-tags

Field	Explanation
`assetId`	Identifier of an asset.
`tagId`	Identifier of a tag.

Tagcategories

Field	Explanation
`tagCategoryId`	Identifier of a tag category.
`name`	Name of the tag category.
`path`	Path above the tag category. A category can be a child to another category. The full parent path (based on `tagCategoryId`) is presented here in "materialized path" format.
`publishedAt`	Timestamp when the tag category was published. If empty, the tag category is created for internal usage but not for use on the search or browse experiences.

Tagcategory-tags

Field	Explanation
`tagCategoryId`	Identifier of a tag category.
`tagId`	Identifier of a tag.

Collections

Field	Explanation
`collectionId`	Identifier of a personal collection.
`userId`	Identifier of the user who owns the collection.
`name`	Name of the collection.
`createdAt`	Timestamp when the collection was created.
`updatedAt`	Timestamp when the collection was last updated.
`deletedAt`	Timestamp when the collection was deleted.

Collection-items

Field	Explanation
`collectionItemId`	Identifier of a collection item. All pages/assets in a collection are collection items.
`collectionId`	Identifier of a personal collection.
`assetId`	Identifier of an asset. Can be personal or marketing-owned.
`page`	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`	Position in the collection of the asset (page).
`createdAt`	Timestamp when the item in collection was created.
`updatedAt`	Timestamp when the item in collection was last updated.
`deletedAt`	Timestamp when the item in collection was deleted.

Important remarks

You can export details per page by setting the pageBased parameter to true. In that case, the events export will return an event for every page of a document, as well as an event for the whole document. Be mindful of this when aggregating the returned data. The event that refers to the whole document can be recognized by the page field which will display the value NULL.
The Reports section of our Online Platform automatically filters out data on deleted users, assets, user groups, etc. If you want the data returned by the events call to do the same, you can filter out the unwanted users, groups and assets by executing a filter based on the deletedAt field in the relevant tables.
Showpad apps also track events when offline, and send data to the server when back online. These events will have the original timestamp as the event timestamp. If you want to detect when the data was received by the server, use the loggedAt timestamp instead. Because of this, the exact same call with the exact same date/time may yield different results when executed at different points in time when using the startedAt and endedAt parameters.

Events​

Users​

Usergroups​

User-usergroups​

Devices​

Shares​

Sharedspaces​

Sharedspaceparticipants​

Sharedspace-items​

Contacts​

Divisions​

Division_usergroups​

Channels​

Assets​

Asset-authors​

Asset-countries​

Asset-languages​

Tags​

Asset-tags​

Tagcategories​

Tagcategory-tags​

Collections​

Collection-items​