This article shows example queries you can use to display user and content engagement.
User engagement examples
Id, Status, Email Address, First Name, Last Name
SELECT User.userId AS 'Id', IF(User.isActive = 1, 'Active', 'Inactive') as 'Status', User.emailAddress, User.firstName, User.lastName, FROM Event LEFT JOIN User ON Event.userId = User.userId WHERE Event.type = 'app-opened' -- Session AND Event.startTime >= [startDate] AND Event.startTime <= [endDate] AND Event.endTime IS NOT NULL -- Exclude events with no end-time GROUP BY User.userId;
Groups (selected)
SELECT User.userId AS 'Id', userGroup.name as 'Groups' FROM User LEFT JOIN User-usergroups ON User-usergroups.userId = User.userId LEFT JOIN Usergroups ON Usergroups.userGroupId = User-usergroups.userGroupId WHERE UserGroup IN [SelectedUserGroups]
Sessions, Average Time Spent, Total Time Spent
SELECT User.userId, COUNT(*) as 'Sessions', -- OP caps sessions to one hour. We also avoid negative durations too by setting them to 0. ROUND(AVG(LEAST(GREATEST(TIMESTAMPDIFF(SECOND, startTime, endTime), 0), 3600))) AS "Average Time Spent", -- OP caps sessions to one hour. We also avoid negative durations by setting them to 0. SUM(LEAST(GREATEST(TIMESTAMPDIFF(SECOND, startTime, endTime), 0), 3600)) AS "Total Time Spent" FROM Event LEFT JOIN User ON Event.userId = User.userId WHERE Event.type = 'app-opened' -- Session AND Event.startTime >= [startDate] AND Event.startTime <= [endDate] AND Event.endTime IS NOT NULL -- Exclude events with no end-time GROUP BY User.userId;
In-App View
SELECT User.userId, -- user.* for user details count(*) AS 'In-app Views', count(DISTINCT Event.assetId) AS 'Unique assets viewed' FROM Event LEFT JOIN User ON Event.userId = User.userId WHERE Event.type = 'asset-in-app-viewed' -- Asset viewed AND Event.page IS NULL -- Exclude page level details AND Event.startTime >= [startDate] AND Event.startTime <= [endDate] GROUP BY User.userId;
Shares, Gmail Shares, Outlook Shares
SELECT User.userId, count(*) AS 'Total Shares', SUM( IF(Share.type = 'gmail', 1, 0)) AS 'Gmail Shares', SUM( IF(Share.type = 'outlook', 1, 0)) AS 'Outlook Shares' FROM Event LEFT JOIN User ON Event.userId = User.userId LEFT JOIN Share ON Event.shareId = Share.shareId WHERE Event.type = 'share-created' -- Shares created AND Event.startTime >= [startDate] AND Event.startTime <= [endDate] GROUP BY User.userId;
Collections Created
SELECT User.userId, -- user.* for user details COUNT(collectionId) as 'Collections Created' FROM Collections LEFT JOIN User ON Event.userId = User.userId WHERE Collections.createdDate >= [startDate] AND Collections.createdDate <= [endDate] GROUP BY User.userId;
Downloads
SELECT User.userId, -- user.* for user details count(DISTINCT Event.assetId) AS 'Downloads' FROM Event LEFT JOIN User ON Event.userId = User.userId WHERE Event.type = 'asset-in-app-downloaded' -- Asset downloaded AND Event.startTime >= [startDate] AND Event.startTime <= [endDate] GROUP BY User.userId;
Active Shared Spaces
SELECT User.userId, -- user.* for user details count(DISTINCT Sharedspaces.sharedSpaceId) AS 'Active Shared Spaces' FROM SharedSpaces LEFT JOIN User ON SharedSpaces.ownerId = User.userId WHERE SharedSpaces.createdAt <= [startDate] AND (SharedSpaces.deletedAt IS NULL OR SharedSpaces.deletedAt >= [endDate]) GROUP BY User.userId;
Shared Spaces Created
SELECT User.userId, -- user.* for user details count(DISTINCT Sharedspaces.sharedSpaceId) AS 'Shared Spaces Created' FROM SharedSpaces LEFT JOIN User ON SharedSpaces.ownerId = User.userId WHERE SharedSpaces.createdAt <= [startDate] GROUP BY User.userId;
Active Shared Spaces Names
SELECT User.userId, -- user.* for user details SharedSpaces.title FROM SharedSpaces LEFT JOIN User ON SharedSpaces.ownerId = User.userId WHERE SharedSpaces.createdAt <= [startDate] AND (SharedSpaces.deletedAt IS NULL OR SharedSpaces.deletedAt >= [endDate]) GROUP BY User.userId;
Content engagement examples
In-App Views, Id, Asset Name, In-App Viewers, In-App Avg. Time Per View
-- In-app views and viewers (per asset) SELECT Asset.assetId, Asset.displayName, count(*) as 'In-app Views', count(DISTINCT `Event`.userId) as 'In-app Viewers' ROUND(AVG(LEAST(GREATEST(TIMESTAMPDIFF(SECOND, startTime, endTime), 0), 3600))) AS "In-App Avg. Time Per View" FROM `Event` LEFT JOIN `User` ON `Event`.userId = `User`.userId LEFT JOIN Asset ON `Event`.assetId = Asset.assetId WHERE `Event`.`type` = 'asset-in-app-viewed' -- Asset viewed AND `Event`.page IS NULL -- Exclude page level details AND `Event`.startTime >= [startDate] AND `Event`.startTime <= [endDate] GROUP BY Asset.assetId, Asset.displayName;
Division (selected)
SELECT Asset.assetId, Asset.displayName, GROUP_CONCAT(Divisions.divisionName) AS "Divisions" FROM Asset INNER JOIN Divisions ON Divisions.divisionId = Asset.divisionId GROUP BY Asset.assetId, Asset.displayName;
Recipient Views, Recipient Viewers, Recipient Avg. Time Per View
-- Shared Asset - Recipient Views and Viewers SELECT Asset.assetId, Asset.displayName, count(*) as 'Recipient Views', count(DISTINCT Event.contactId)+count(DISTINCT(IF(Event.contactId IS NULL,IFNULL(Event.deviceId,'X'),NULL))) as 'Recipient Viewers', count(DISTINCT Event.contactId) as 'Known contacts', count(DISTINCT(IF(Event.contactId IS NULL,IFNULL(Event.deviceId,'X'),NULL))) as 'Unknown contacts', ROUND(AVG(LEAST(GREATEST(TIMESTAMPDIFF(SECOND, startTime, endTime), 0), 3600))) AS "Recipient Avg. Time Per View" FROM Event LEFT JOIN User ON Event.userId = User.userId LEFT JOIN Asset ON Event.assetId = Asset.assetId WHERE Event.type = 'shared-asset-viewed' -- Shared Assets viewed AND Event.page IS NULL -- Exclude page level details AND Event.startTime >= [startDate] AND Event.startTime <= [endDate] GROUP BY Asset.assetId;
Shares, Gmail Shares, Outlook Shares
# Shares/gmail/outlook per asset SELECT Asset.assetId, Asset.displayName, COUNT(AssetShares.assetId) AS "Shares", SUM(IF(Share.type = 'gmail',1,0)) as 'Gmail Shares', SUM(IF(Share.type = 'outlook',1,0)) as 'Outlook Shares' FROM ( SELECT assetId, shareId FROM Event WHERE type = 'asset-shared' AND startTime >= '2020-01-01 00:00:00' AND startTime <= '2020-06-01 00:00:00' GROUP BY assetId, shareId, contactId ) AS AssetShares LEFT JOIN Asset ON Asset.assetId = AssetShares.assetId LEFT JOIN Share ON Share.shareId = AssetShares.shareId GROUP BY AssetShares.assetId;
In-App Downloads
-- In-app views and viewers (per asset) SELECT Asset.assetId, Asset.displayName, count(*) as 'In-app Downloads', FROM `Event` LEFT JOIN Asset ON `Event`.assetId = Asset.assetId WHERE `Event`.`type` = 'asset-in-app-downloaded' AND `Event`.page IS NULL -- Exclude page level details AND `Event`.startTime >= [startDate] AND `Event`.startTime <= [endDate] GROUP BY Asset.assetId, Asset.displayName;
Recipient Downloads
-- In-app views and viewers (per asset) SELECT Asset.assetId, Asset.displayName, count(*) as 'In-app Downloads', FROM `Event` LEFT JOIN Asset ON `Event`.assetId = Asset.assetId WHERE `Event`.`type` = 'shared-asset-downloaded' AND `Event`.page IS NULL -- Exclude page level details AND `Event`.startTime >= [startDate] AND `Event`.startTime <= [endDate] GROUP BY Asset.assetId, Asset.displayName;
Active Shared Spaces, Active Shared Spaces Names
# Note, this is not the actual state of the shared spaces. # You could filter out events that have "sharedspace-removed-asset" events, but still someone can the asset again etc. Kinda complicates the query. # Analytics queries the items inside the shared space instead of events. SELECT Asset.assetId, Asset.displayName, COUNT(SharedSpaces.*) AS 'Active Shared Spaces', GROUP_CONCAT(SharedSpaces.title) AS 'Active Shared Spaces Names' FROM Event INNER JOIN Asset ON Event.assetId = Asset.assetId INNER JOIN SharedSpaces ON Event.shareId = SharedSpaces.sharedSpaceId WHERE Event.type = "sharedspace-added-asset" AND Event.startTime >= [startDate] AND Event.startTime <= [endDate] GROUP BY Asset.assetId, Asset.displayName;
Content Type
Add Asset.type to an existing query
Deleted Asset
Add IF(Asset.deletedAt IS NOT NULL, 'Yes', 'No') to an existing query
In-App Avg. Time Per Day
See In-App Avg. Time Per View but then aggregate by day
Recipient Avg. Time Per Day
See Recipient Avg. Time Per View but then aggregate by day
Tags, Tags (selected)
SELECT Asset.assetId, Asset.displayName, GROUP_CONCAT(Tag.name) AS "Tags" FROM Asset LEFT JOIN AssetTags ON AssetTags.assetId = Asset.assetId LEFT JOIN Tags ON Tags.tagId = AssetTags.tagId GROUP BY Asset.assetId, Asset.displayName;
Type
Add Asset.source to an existing query