This article helps admins and data teams understand how to calculate the Key Performance Indicators (KPIs) displayed in the Audience dashboard using queries from the CC Data Lake.

Overview

The Audience dashboard KPIs – Query Reference outlines the SQL logic behind each KPI widget displayed in the Audience Dashboard. These queries are designed for use with the CC Data Lake, allowing community administrators, analysts, and developers to understand the KPIs.

For more information on Audience Dashboard, refer to the Audience Dashboard article.

KPI Query and Reference

The following section outlines each Key Performance Indicator (KPI) available in the Audience dashboard, along with the corresponding SQL query used to calculate it in the CC Data Lake.

• All Unique visitors
• Registered unique users
• Guest unique visitors
• All page views
• Page views of registered visitors
• Page views of guest visitors

Query to Generate Community Engagement Metrics

The following SQL query generates six key metrics related to audience activity on the platform. It pulls data from visit and page view logs, filters out incomplete or irrelevant sessions, and calculates statistics related to user access and content consumption.

All six metrics are derived from a single query using structured steps (Common Table Expressions) to ensure efficiency and maintain readability.

WITH _pageview AS (
     SELECT
            occurred_at,
            topic_public_id,
            reply_public_id,
            user_id,
            visitor_id,
            ROW_NUMBER() OVER (PARTITION BY visitor_id ORDER BY from_iso8601_timestamp(occurred_at) NULLS LAST, user_id, topic_public_id, reply_public_id) AS row_num
        FROM "community-id".pageview
),
_visitors AS (
            SELECT
                from_iso8601_timestamp(occurred_at) AS event_timestamp,
                topic_public_id,
                COALESCE (
                    user_id,
                    FIRST_VALUE(user_id) OVER (
                        PARTITION BY visitor_id ORDER BY CAST(user_id as INTEGER) NULLS LAST,
                        from_iso8601_timestamp(occurred_at) DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                    )
                ) AS user_id,
            'anonymous_' || CAST(visitor_id AS varchar) AS visitor_id,
            row_num
            FROM _pageview
        ),
visitors AS (
            SELECT
                CAST(event_timestamp AS DATE) AS activity_date,
                event_timestamp AS activity_timestamp,
                user_id AS user_id,
                'view' AS event_name,
                COALESCE(user_id, visitor_id) AS unique_visitor_id,
                CASE WHEN user_id IS NOT NULL then 'user' ELSE 'guest' END AS entity_type,
                visitor_id,
                row_num
            FROM
            _visitors
)
SELECT COUNT(DISTINCT(unique_visitor_id)) AS unique_visitors,
       COUNT(1) AS all_pageviews,
       COUNT(DISTINCT(user_id)) AS registered_unique_visitors,
       COUNT(CASE WHEN user_id IS NOT NULL THEN 1 ELSE NULL END) as pageviews_of_registered_visitors,
       COUNT(DISTINCT(CASE WHEN entity_type = 'guest' THEN unique_visitor_id ELSE NULL END)) AS guest_unique_visitors,
       COUNT(CASE WHEN user_id IS NULL THEN 1 ELSE NULL END) as pageviews_of_guest_visitors
FROM visitors
WHERE activity_date BETWEEN DATE('YYYY-MM-DD') AND DATE('YYYY-MM-DD')