Q&A Dashboard KPIs – Query Reference for CC Data Lake

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

Overview

The Q&A Dashboard KPIs – Query Reference provides the SQL logic behind each KPI widget in the Community Q&A 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 the Q&A dashboard, refer to the Q&A Dashboard article.

KPI Query and Reference

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

• Questions
• Answers

Query to Generate Question Engagement Metrics

The SQL query generates eight key metrics related to question engagement in the community forum.

It pulls data from question posts and their accepted replies, filters out deleted or incomplete entries, and calculates statistics about how often questions are asked and answered and how quickly they receive responses.

The following eight metrics are derived from a single query using structured steps (Common Table Expressions) for efficiency, role-based filtering, and clarity:

WITH filtered_question_user as (
  SELECT DISTINCT("community-id".user.user_id) as user_id
    FROM "community-id".user
    join "community-id".user_role on "user".user_id = "community-id".user_role.user_id
    join "community-id".role on "community-id".user_role.role_id = "community-id".role.role_id
    where role.label IN ('Registered Users')
),
filtered_answer_user as (
  SELECT DISTINCT("community-id".user.user_id) as user_id
    FROM "community-id".user
    join "community-id".user_role on "user".user_id = "community-id".user_role.user_id
    join "community-id".role on "community-id".user_role.role_id = "community-id".role.role_id
    where role.label IN ('Registered Users')
),
_question AS (
        SELECT topic_public_id, author_id, created_at, content_type, topic_private_id, topic.title, category.category_id, answer_id, category.title as category
        FROM "community-id".topic
        LEFT JOIN "community-id".category ON topic.category_id = category.category_id
        WHERE content_type = 'question'
        AND trashed_at IS NULL
        AND created_at IS NOT NULL
),
question AS (
        SELECT
            replied_at as answered_at,
            _question.created_at,
            _question.topic_public_id,
            topic_reply.reply_public_id,
            _question.author_id as asked_by,
            topic_reply.author_id AS answered_by,
            _question.title,
            _question.category_id,
            _question.category
        FROM _question
        LEFT JOIN "community-id".topic_reply ON topic_reply.topic_public_id = _question.topic_public_id
            AND topic_reply.trashed_at IS NULL
            AND _question.answer_id =  topic_reply.reply_public_id
)

SELECT
    COUNT(1) AS question_asked,
    COUNT(CASE WHEN asked_by IN (SELECT DISTINCT(user_id) FROM filtered_question_user) THEN 1 ELSE NULL END) as filtered_question_asked,
    COUNT(CASE WHEN answered_by IS NOT NULL THEN 1 ELSE NULL END) as question_answered,
    COUNT(
            CASE WHEN (
                    asked_by IN (SELECT DISTINCT(user_id) FROM filtered_question_user)
                         AND
                    answered_by IN (SELECT DISTINCT(user_id) FROM filtered_answer_user)
                ) THEN 1
            ELSE NULL END
    )  AS filtered_questions_answered,
    AVG(answered_at - created_at) as average_answer_time,
    AVG(
         CASE WHEN (
                    asked_by IN (SELECT DISTINCT(user_id) FROM filtered_question_user)
                         AND
                    answered_by IN (SELECT DISTINCT(user_id) FROM filtered_answer_user)
                ) THEN answered_at - created_at
            ELSE NULL END
    ) AS filtered_average_answer_time
FROM question
WHERE DATE(created_at) BETWEEN DATE('YYYY-MM-DD') AND DATE('YYYY-MM-DD')

Query to Generate Answer Engagement Metrics

The SQL query below generates metrics related to answer engagement in the community forum. It analyzes how many answers were posted to questions during a selected timeframe, including a breakdown by users with specific roles (for example, Registered Users).

WITH filtered_question_user as (
  SELECT DISTINCT("community-id".user.user_id) as user_id
    FROM "community-id".user
    join "community-id".user_role on "user".user_id = "community-id".user_role.user_id
    join "community-id".role on "community-id".user_role.role_id = "community-id".role.role_id
    where role.label IN ('Registered Users')
),
filtered_answer_user as (
  SELECT DISTINCT("community-id".user.user_id) as user_id
    FROM "community-id".user
    join "community-id".user_role on "user".user_id = "community-id".user_role.user_id
    join "community-id".role on "community-id".user_role.role_id = "community-id".role.role_id
    where role.label IN ('Registered Users')
),
_question AS (
        SELECT topic_public_id, author_id, created_at, content_type, topic_private_id, topic.title, category.category_id, answer_id, category.title as category
        FROM "community-id".topic
        LEFT JOIN "community-id".category ON topic.category_id = category.category_id
        WHERE content_type = 'question'
        AND trashed_at IS NULL
        AND created_at IS NOT NULL
),
question AS (
        SELECT
            replied_at as answered_at,
            _question.created_at,
            _question.topic_public_id,
            topic_reply.reply_public_id,
            _question.author_id as asked_by,
            topic_reply.author_id AS answered_by,
            _question.title,
            _question.category_id,
            _question.category
        FROM _question
        LEFT JOIN "community-id".topic_reply ON topic_reply.topic_public_id = _question.topic_public_id
            AND topic_reply.trashed_at IS NULL
            AND _question.answer_id =  topic_reply.reply_public_id
)
SELECT
    COUNT(answered_by) AS answers,
    COUNT(
            CASE WHEN (
                    asked_by IN (SELECT DISTINCT(user_id) FROM filtered_question_user)
                         AND
                    answered_by IN (SELECT DISTINCT(user_id) FROM filtered_answer_user)
                ) THEN 1
            ELSE NULL END
    )  as answeres_filtered
FROM question
WHERE DATE(answered_at) BETWEEN DATE('YYYY-MM-DD') AND DATE('YYYY-MM-DD')