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

Overview

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

Self-Service Score Metrics

The Self-Service score is the core metric of the Self-Service Dashboard. It measures how well your help content supports users in solving issues without contacting your support team.

A strong self-service score means users are:

• Finding answers quickly
• Having a smoother experience
• Less reliant on support agents

The self-service score is calculated by dividing the Total unique visitors who viewed help content by the Total Zendesk unique users who submitted support tickets.

For example, if 1,000 users viewed help content and 250 created tickets, your self-service score is 4.0.

KPI Query and Reference

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

Unique Visitors Who Viewed Help Content

The number of unique visitors who viewed an article, a question with a best answer, or a conversation on the community in the selected timeframe.

WITH topics AS (
    SELECT
        tp.topic_public_id,
        tp.author_id,
        tp.published_at AS created_at,
        tp.content_type,
        tp.topic_private_id,
        tp.title,
        tp.category_id,
        tp.answer_id
    FROM "community_id".topic AS tp
    WHERE tp.content_type IN ('productUpdate', 'article') AND tp.trashed_at IS NULL AND tp.created_at IS NOT NULL

    UNION ALL

    SELECT
        tp.topic_public_id,
        tp.author_id,
        tp.created_at,
        tp.content_type,
        tp.topic_private_id,
        tp.title,
        tp.category_id,
        tp.answer_id
    FROM "community_id".topic AS tp
    WHERE tp.content_type IN ('question', 'discussion', 'idea') AND tp.trashed_at IS NULL AND tp.created_at IS NOT NULL
),
replies AS (
    SELECT
        tr.replied_at AS created_at,
        tr.topic_public_id,
        tr.topic_private_id,
        tr.reply_public_id,
        tr.reply_private_id,
        tr.author_id,
        tr.author_id AS user_id
    FROM "community_id".topic_reply AS tr
    WHERE tr.trashed_at IS NULL AND tr.replied_at IS NOT NULL
),
questions_with_best_answers AS (
    SELECT
        tp.topic_public_id,
        tp.content_type,
        tp.author_id,
        tp.created_at,
        tp.topic_private_id,
        tp.title,
        tp.category_id,
        rp.created_at AS answered_at
    FROM replies AS rp
    join topics AS tp
    ON rp.topic_public_id = tp.topic_public_id
    WHERE tp.content_type = 'question' AND tp.answer_id = rp.reply_public_id
),
helpful_content AS (
    SELECT
        tp.topic_public_id,
        tp.author_id,
        tp.created_at,
        tp.content_type,
        tp.topic_private_id,
        tp.title,
        tp.category_id,
        ct."module" AS category_module,
        ct.status AS category_status,
        NULL AS answered_at
    FROM topics AS tp
    join "community_id".category AS ct
    ON tp.category_id = ct.category_id
    WHERE tp.content_type IN ('article', 'discussion')

    UNION ALL

    SELECT
        qwba.topic_public_id,
        qwba.author_id,
        qwba.created_at,
        qwba.content_type,
        qwba.topic_private_id,
        qwba.title,
        qwba.category_id,
        ct."module" AS category_module,
        ct.status AS category_status,
        qwba.answered_at
    FROM questions_with_best_answers AS qwba
    join "community_id".category AS ct
    ON qwba.category_id = ct.category_id
),
topic_visitors AS (
    SELECT *
    FROM "community_id".pageview AS pv
    WHERE pv.topic_public_id IS not NULL AND pv.visitor_id IS NOT NULL
)

SELECT COUNT(DISTINCT tv.visitor_id)
FROM helpful_content AS hc
join topic_visitors AS tv
on hc.topic_public_id = tv.topic_public_id
WHERE
    (
        hc.content_type IN ('article', 'discussion') OR
        (
            hc.content_type = 'question' AND
            hc.answered_at < FROM_ISO8601_TIMESTAMP(tv.occurred_at)
        )
    ) AND
    CAST(DATE(FROM_ISO8601_TIMESTAMP(tv.occurred_at)) AS VARCHAR) BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' AND
    hc.category_status = 'visible' AND
    hc.category_module IN ('community', 'knowledge_base');

Unique Users Who Created Tickets

Number of distinct Zendesk users who submitted support tickets in a selected timeframe.

SELECT COUNT(DISTINCT zt.zendesk_user_id)
FROM   "community_id".zendesk_ticket AS zt
WHERE  CAST(DATE(FROM_ISO8601_TIMESTAMP(zt.created_at)) AS VARCHAR)
 BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD';