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 UserUsergroup ON UserUsergroup.userId = User.userId
    LEFT JOIN Usergroup ON Usergroup.userGroupId = UserUsergroup.userGroupId
WHERE
    Usergroup.name 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
    AND Event.endTime IS NOT NULL -- Exclude events with no end-time
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/Active

SELECT
    result.userId,
    MAX(result.collectionsCreatedCount) AS 'Collections Created',
    MAX(result.collectionsActiveCount) AS 'Collections Active'
FROM (
    SELECT
        Collection.userId,
        COUNT(*) AS collectionsCreatedCount,
        0 AS collectionsActiveCount
    FROM Collection
    WHERE Collection.createdAt BETWEEN :startDate AND :endDate AND COALESCE(Collection.deletedAt, :startDate) >= :startDate
    GROUP BY Collection.userId
    UNION
    SELECT
        Collection.userId,
        0 AS collectionsCreatedCount,
        COUNT(*) AS collectionsActiveCount
    FROM Collection
    WHERE Collection.createdAt <= :endDate AND COALESCE(Collection.deletedAt, :startDate) >= :startDate
    GROUP BY Collection.userId
) AS result
GROUP BY result.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
    u.userId,
    u.emailAddress,
    COUNT(ssp.sharedSpaceId) AS 'Active Shared Spaces',
    GROUP_CONCAT(sp.title) AS 'Shared Spaces titles'
FROM SharedSpaceParticipant AS ssp
INNER JOIN User AS u ON u.userId = ssp.userId
INNER JOIN SharedSpace AS sp ON sp.sharedSpaceId = ssp.sharedSpaceId
WHERE
    ssp.createdAt <= :endDate
    AND COALESCE(ssp.accessRevokedAt, ssp.deletedAt, sp.deletedAt, :startDate) >= :startDate
GROUP BY u.userId;

Shared Spaces Created

SELECT
    u.userId,
    u.emailAddress,
    COUNT(ssp.sharedSpaceId) AS 'Shared Spaces Created',
    GROUP_CONCAT(sp.title) AS 'Shared Spaces titles'
FROM SharedSpaceParticipant AS ssp
INNER JOIN User AS u ON u.userId = ssp.userId
INNER JOIN SharedSpace AS sp ON sp.sharedSpaceId = ssp.sharedSpaceId AND ssp.userId = sp.creatorId
WHERE
    ssp.createdAt BETWEEN :startDate AND :endDate
    AND COALESCE(ssp.accessRevokedAt, ssp.deletedAt, sp.deletedAt, :startDate) >= :startDate
GROUP BY u.userId;

Content engagement examples

In-App Views, Id, Asset Name, In-App Viewers, In-App Avg. Time Per View

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
    AND Event.endTime IS NOT NULL -- Exclude events with no end-time
GROUP BY
    Asset.assetId,
    Asset.displayName;

Division (selected)

SELECT
    Asset.assetId,
    Asset.displayName,
    GROUP_CONCAT(Division.divisionName) AS "Divisions"
FROM
    Asset
    INNER JOIN Division ON Division.divisionId = Asset.divisionId
GROUP BY
    Asset.assetId,
    Asset.displayName;

Recipient Views, Recipient Viewers, Recipient Avg. Time Per View

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
    AND Event.endTime IS NOT NULL -- Exclude events with no end-time
GROUP BY
    Asset.assetId;

Shares, Gmail Shares, Outlook Shares

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

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

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 add the asset again.
-- It would complicate the query a lot
-- Showpad reporting queries the items inside the shared space instead of events.
SELECT
    Asset.assetId,
    Asset.displayName,
    COUNT(SharedSpace.sharedSpaceId) AS 'Active Shared Spaces',
    GROUP_CONCAT(SharedSpace.title) AS 'Active Shared Spaces Names'
FROM
    Event
    INNER JOIN Asset ON Event.assetId = Asset.assetId
    INNER JOIN SharedSpace ON Event.shareId = SharedSpace.sharedSpaceId
WHERE
    Event.type = "sharedspace-added-asset"
    AND Event.startTime >= :startDate
    AND Event.startTime <= :endDate
GROUP BY
    Asset.assetId,
    Asset.displayName;

Content Type

Filtering using "Asset.type" in 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 AssetTag ON AssetTag.assetId = Asset.assetId
    LEFT JOIN Tag ON Tag.tagId = AssetTag.tagId
GROUP BY
    Asset.assetId,
    Asset.displayName;

Type

Add "Asset.source" to an existing query.