Skip to main content

Example Queries

||

Copy-paste SQL queries for common Content Reporting use cases. These queries work with exported Showpad data in your data warehouse.

What you'll learn:

  • How to query user engagement metrics (sessions, views, shares)
  • How to analyze content performance (views, shares, time spent)
  • How to join Events with lookup tables for richer insights
TL;DR
  • User sessions? Filter by type = 'app-opened' and join with Users
  • Content views? Filter by type = 'asset-in-app-viewed' with page IS NULL
  • Share analytics? Filter by type = 'share-created' and join with Shares
  • Cap durations? Use LEAST(TIMESTAMPDIFF(...), 3600) to cap at 1 hour
Prerequisites
  • Plan: Ultimate
  • Data: Content Reporting data exported and loaded into your data warehouse
  • Knowledge: Basic SQL and familiarity with Lookup Tables

Query Parameters​

All queries use placeholder parameters that you'll need to replace:

ParameterDescriptionExample
:startDateStart of date range'2024-01-01'
:endDateEnd of date range'2024-01-31'
:selectedUsergroupsList of user group names('Sales', 'Marketing')

User Engagement Examples​

User Details​

Returns user info for users with session activity in a date range.

Returns: User ID, status, email, first name, last name

Filter by User Groups​

Returns users belonging to specific user groups.

Returns: User ID, group names

Session Metrics​

Calculates session counts and time spent per user. Sessions are capped at 1 hour to handle edge cases.

Returns: User ID, session count, average time spent, total time spent

Experience Metrics​

Tracks Experience (channel) usage per user. Useful for measuring engagement with interactive content.

Returns: User ID, visit count, average time spent, total time spent

In-App Views​

Counts asset views within the Showpad app per user. Uses page IS NULL to count full asset views only (not individual page views).

Returns: User ID, view count, unique assets viewed

Share Information​

Breaks down sharing activity by type (Gmail, Outlook, etc.) per user.

Returns: User ID, total shares, Gmail shares, Outlook shares

Collections Information​

Calculates collections created and active collections per user. Uses a UNION to combine creation and active counts.

Returns: User ID, collections created, collections active

Downloads Per User​

Counts unique assets downloaded per user in a date range.

Returns: User ID, download count (unique assets)

Active Shared Spaces Per User​

Lists active Shared Spaces per user, including titles. Handles soft-deleted records via COALESCE.

Returns: User ID, email, active Shared Space count, Shared Space titles

Content Engagement Examples​

Engagement Metrics​

Calculates per-asset engagement: views, unique viewers, and average time per view.

Returns: Asset ID, display name, view count, viewer count, average time per view

Asset Shares and Views​

Advanced CTE query that calculates per-asset share counts and recipient views. Handles edge case where individual pages are shared separately.

Returns: Asset ID, display name, type, share count, recipient views

Recipient Viewers​

Analyzes external recipient engagement with shared assets. Distinguishes between known contacts (with contactId) and anonymous viewers (tracked by deviceId).

Returns: Asset ID, display name, view count, viewer count, known/unknown contacts, average time per view

Active Shared Spaces Per Asset​

Counts active Shared Spaces containing each asset. Filters out deleted items and inactive spaces.

Returns: Asset ID, display name, type, active Shared Space count

This query returns all assets. In the Admin App's top-content table, only assets with event activity in the

selected date range are shown. Modify the filtered_assets CTE to match your use case. :::

Was this page helpful?