Example queries for user and content engagement

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