Example Queries

These example queries demonstrate how to retrieve user and content engagement information.

User Engagement Examples

User Details

The following query returns the following user details:

• User 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;

Selected Groups

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;

Session Metrics

The following query returns:

• the number of Sessions
• the 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;

Experience Metrics

The following query returns:

• the number of experience visits
• the Average Time Spent inside an experience
• Total Time Spent inside an experiernce

SELECT
  User.userId,

  COUNT(*) AS 'Experience visits',

  -- 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 = 'channel-used' -- Experience usage
  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 Views

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;

Share Information

The following query returns:

• the total number of Shares
• the number of Gmail Shares
• the number of 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 Information

The following query returns:

• the number of created Collections
• the number of active Collections

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 Per User

The following query returns the number of distinct assets each user has downloaded within a specified date range.

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 Per User

The following query returns the number of active Shared Spaces each user has within a given date range.

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;

Content Engagement Examples

Engagement Metrics

For the specified date range, the following query returns:

• Asset Id
• Asset Name
• the number of In-App Views per asset
• the number of In-App Viewers per asset
• In-App Average 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;

Asset Shares and Views

The following query returns:

• a count of how many times an asset was shared during a given date range
• how many recipient views an asset received inside those shares

WITH filtered_assets AS (
    SELECT *
    FROM Asset
    -- Optional filter on divisionId or tags can be applied here
),
​
filtered_events AS (
    SELECT *
    FROM Event
    WHERE
        Event.startTime >= :startTime
        AND Event.startTime <= :endTime
        AND Event.endTime IS NOT NULL
),
​
-- Asset shares metrics
-- Calculate how many times an asset was shared in the date range
asset_shares AS (
    SELECT
            assetId,
​
            -- Normally we can just count the share events.
            -- However, if someone adds single pages to a share (page 1,2,3,4,5,6) then we count the asset as shared 6 times.
            -- We squash these page events together to 1 by grouping on assetId-shareId-contactId to be 100% correct.
            COUNT(DISTINCT CONCAT(assetId, shareId, COALESCE(contactId, ''))) AS shares_count
    FROM filtered_events
    WHERE type = 'asset-shared'
    GROUP BY assetId
),
​
-- Asset recipient view metrics
-- Count how many views an asset received inside shares
asset_recipient_views AS (
    SELECT
            assetId,
            COUNT(*) AS recipient_views
    FROM filtered_events
    WHERE
        type = 'shared-asset-viewed' AND page IS NULL -- Exclude page-level views, we are only interested in the full asset views (page=null)
    GROUP BY assetId
)
​
SELECT
    a.assetId,
    a.displayName,
    a.type,
    COALESCE(asset_shares.shares_count, 0) AS shares_count,
    COALESCE(asset_recipient_views.recipient_views, 0) AS recipient_views
FROM filtered_assets AS a
LEFT JOIN asset_shares ON asset_shares.assetId = a.assetId
LEFT JOIN asset_recipient_views ON asset_recipient_views.assetId = a.assetId
GROUP BY 1, 2

Recipient Viewers

The following query returns details about the recipients that have viewed shared assets.

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;

Active Shared Spaces

The following query demonstrates how to calculate the number of active shared spaces for every asset in the dataset.

WITH filtered_assets AS (
    SELECT *
    FROM Asset
    -- Optional filter on divisionId or tags can be applied here
),

active_shared_space_items AS (
    SELECT * FROM SharedSpaceItem WHERE deletedAt IS NULL
),

active_shared_spaces AS (
    SELECT * FROM SharedSpace WHERE status = 'active'
)

SELECT
    a.assetId AS 'Asset ID',
    a.displayName AS 'Asset name',
    a.type AS 'Type',
    COUNT(DISTINCT ss.sharedSpaceId) AS 'Active Shared Spaces'
FROM filtered_assets AS a
LEFT JOIN active_shared_space_items AS ssi ON ssi.assetId = a.assetId
LEFT JOIN active_shared_spaces AS ss ON ss.sharedSpaceId = ssi.sharedSpaceId
GROUP BY 1;

caution

In the Showpad top-content table, only assets that have had event activity in the selected date range are displayed. This example query shows all assets. We recommend modifying the filtered_assets block to suit your specific use case.