This article helps admins and data teams understand how to calculate the Key Performance Indicators (KPIs) displayed in the User dashboard using queries from the CC Data Lake.
Overview
The User dashboard KPIs – Query Reference provides the SQL logic behind each KPI widget in the Community User 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 User dashboard, refer to the User Dashboard article.
KPI Query and Reference
The following section outlines each Key Performance Indicator (KPI) available in the User dashboard, along with the corresponding SQL query used to calculate it in the CC Data Lake.
Total users
Displays the total number of valid, registered users in the community up to the selected end date.
with _user as (
select user.user_id, user_role.role_id, user.user_name, user.join_date
from "community-id".user
join "community-id".user_role on user.user_id = user_role.user_id
join "community-id".role on user_role.role_id = role.role_id and primary_role = 1
where role.name not in ('roles.requires-approval', 'roles.not-activated', 'roles.super-admin', 'roles.banned', 'roles.guest', 'roles.deleted')
group by user.user_id, user_role.role_id, user.user_name, user.join_date
)
select count(1) from _user where date(join_date) <= date('YYYY-MM-DD')New Registrations
Shows the number of users who joined the community within the selected date range.
with _user as (
select user.user_id, user_role.role_id, user.user_name, user.join_date
from "community-id".user
join "community-id".user_role on user.user_id = user_role.user_id
join "community-id".role on user_role.role_id = role.role_id and primary_role = 1
where role.name not in ('roles.requires-approval', 'roles.not-activated', 'roles.super-admin', 'roles.banned', 'roles.guest', 'roles.deleted')
group by user.user_id, user_role.role_id, user.user_name, user.join_date
)
select count(1) from _user where date(join_date) >= date('YYYY-MM-DD') and date(join_date) <= date('YYYY-MM-DD')Users With At Least One Post
Counts users who have created at least one topic or reply during the selected period.
with _user as (
select user.user_id, user_role.role_id, user.user_name, user.join_date
from "community-id".user
join "community-id".user_role on user.user_id = user_role.user_id
join "community-id".role on user_role.role_id = role.role_id and primary_role = 1
where role.name not in ('roles.requires-approval', 'roles.not-activated', 'roles.super-admin', 'roles.banned', 'roles.guest', 'roles.deleted')
group by user.user_id, user_role.role_id, user.user_name, user.join_date
),
_topic_cache as (
SELECT topic_public_id, author_id, published_at AS created_at, content_type, topic_private_id, title,
category_id
FROM "community-id".topic
WHERE content_type IN ('productUpdate', 'article')
AND published_at IS NOT NULL
AND trashed_at IS NULL
UNION ALL
SELECT topic_public_id, author_id, created_at, content_type, topic_private_id, title, category_id
FROM "community-id".topic
WHERE content_type IN ('question', 'discussion', 'idea')
AND trashed_at IS NULL
AND created_at IS NOT NULL
),
_topic_reply_cache as (
SELECT
"community-id".topic_reply.replied_at AS created_at,
"community-id".topic_reply.topic_public_id,
"community-id".topic_reply.topic_private_id,
"community-id".topic_reply.reply_public_id,
"community-id".topic_reply.reply_private_id,
"community-id".topic_reply.author_id,
"community-id".topic_reply.author_id AS user_id
FROM "community-id".topic_reply
JOIN _topic_cache ON "community-id".topic_reply.topic_public_id = _topic_cache.topic_public_id AND trashed_at IS NULL
),
post_created as (
SELECT
'post_created-topic-' || topic.topic_public_id || '-' || COALESCE(topic.author_id, 'anonymous') AS event_id,
cast(to_iso8601(CAST(topic.created_at AS DATE)) AS varchar) AS activity_date,
'post_created' AS event_name,
'topic' AS entity_type,
CAST(to_iso8601(topic.created_at AT TIME ZONE 'UTC') AS varchar) AS activity_timestamp,
CAST(topic.author_id AS varchar) AS author_id,
CAST(topic.author_id AS varchar) AS user_id,
CAST(topic.topic_public_id AS varchar) AS topic_public_id,
CAST(topic.topic_private_id AS varchar) AS topic_private_id,
CAST(NULL AS varchar) AS reply_public_id,
CAST(NULL AS varchar) AS reply_private_id
FROM _topic_cache AS topic
UNION ALL
SELECT
'post_created-reply-' || replies.topic_public_id || '-' || replies.reply_public_id || '-' || COALESCE(replies.author_id, 'anonymous') AS event_id,
cast(to_iso8601(CAST(replies.created_at AS DATE)) AS varchar) AS activity_date,
'post_created' AS event_name,
'reply' AS entity_type,
CAST(to_iso8601(replies.created_at AT TIME ZONE 'UTC') AS varchar) AS activity_timestamp,
CAST(replies.author_id AS varchar) AS author_id,
CAST(replies.author_id AS varchar) AS user_id,
CAST(replies.topic_public_id AS varchar) AS topic_public_id,
CAST(replies.topic_private_id AS varchar) AS topic_private_id,
CAST(replies.reply_public_id AS varchar) AS reply_public_id,
CAST(replies.reply_private_id AS varchar) AS reply_private_id
FROM _topic_reply_cache AS replies
)
SELECT COUNT(DISTINCT(_user.user_id))
FROM _user
JOIN post_created ON post_created.author_id = _user.user_id
WHERE activity_date >= 'YYYY-MM-DD' and activity_date <= 'YYYY-MM-DD'Top Users
Lists the most active users based on various contribution metrics within the selected time frame.
- Posts
- Replies
- Answers Given
- Likes Given and Received
- Points Earned
with _user as (
select user.user_id, user_role.role_id, user.user_name, user.join_date
from "community-id".user
join "community-id".user_role on user.user_id = user_role.user_id
join "community-id".role on user_role.role_id = role.role_id and primary_role = 1
where role.name not in ('roles.requires-approval', 'roles.not-activated', 'roles.super-admin', 'roles.banned', 'roles.guest', 'roles.deleted')
group by user.user_id, user_role.role_id, user.user_name, user.join_date
),
user_ids as (
select distinct(_user.user_id)
from _user
),
user_points as (
select _user.user_id,
_user.user_name,
_user.join_date,
COALESCE(SUM(CAST("community-id".gamification_points_earned.points as int)), 0) as points
from _user
left join "community-id".gamification_points_earned on _user.user_id = "community-id".gamification_points_earned.user_id
and date(from_iso8601_timestamp("community-id".gamification_points_earned.occurred_at)) between date '2024-08-01' and date '2024-08-31'
group by _user.user_id, _user.user_name, _user.join_date
),
_topic_cache as (
SELECT topic_public_id, author_id, published_at AS created_at, content_type, topic_private_id, title,
category_id
FROM "community-id".topic
WHERE content_type IN ('productUpdate', 'article')
AND published_at IS NOT NULL
AND trashed_at IS NULL
UNION ALL
SELECT topic_public_id, author_id, created_at, content_type, topic_private_id, title, category_id
FROM "community-id".topic
WHERE content_type IN ('question', 'discussion', 'idea')
AND trashed_at IS NULL
AND created_at IS NOT NULL
),
_topic_reply_cache as (
SELECT
"community-id".topic_reply.replied_at AS created_at,
"community-id".topic_reply.topic_public_id,
"community-id".topic_reply.topic_private_id,
"community-id".topic_reply.reply_public_id,
"community-id".topic_reply.reply_private_id,
"community-id".topic_reply.author_id,
"community-id".topic_reply.author_id AS user_id
FROM "community-id".topic_reply
JOIN _topic_cache ON "community-id".topic_reply.topic_public_id = _topic_cache.topic_public_id
AND trashed_at IS NULL
),
question_answered as (
SELECT
'q-ans-' || CAST(r.topic_public_id as varchar) || '-' || CAST(r.user_id as varchar) as event_id,
CAST(to_iso8601(CAST(r.created_at AS DATE)) AS varchar) AS activity_date,
'question_answered' as event_name,
'question_answered' AS entity_type,
r.created_at AS activity_timestamp,
CAST(r.author_id AS varchar) AS author_id,
CAST(r.user_id AS varchar) AS user_id,
CAST(r.topic_public_id AS varchar) AS topic_public_id,
CAST(r.topic_private_id AS varchar) AS topic_private_id,
CAST(r.reply_public_id AS varchar) AS reply_public_id,
CAST(r.reply_private_id AS varchar) AS reply_private_id
FROM _topic_reply_cache r
JOIN "community-id".topic as t on t.topic_public_id = r.topic_public_id and t.answer_id = r.reply_public_id
and t.content_type = 'question'
),
post_created as (
SELECT
'post_created-topic-' || topic.topic_public_id || '-' || COALESCE(topic.author_id, 'anonymous') AS event_id,
cast(to_iso8601(CAST(topic.created_at AS DATE)) AS varchar) AS activity_date,
'post_created' AS event_name,
'topic' AS entity_type,
topic.created_at AS activity_timestamp,
CAST(topic.author_id AS varchar) AS author_id,
CAST(topic.author_id AS varchar) AS user_id,
CAST(topic.topic_public_id AS varchar) AS topic_public_id,
CAST(topic.topic_private_id AS varchar) AS topic_private_id,
CAST(NULL AS varchar) AS reply_public_id,
CAST(NULL AS varchar) AS reply_private_id
FROM _topic_cache AS topic
UNION ALL
SELECT
'post_created-reply-' || replies.topic_public_id || '-' || replies.reply_public_id || '-' || COALESCE(replies.author_id, 'anonymous') AS event_id,
cast(to_iso8601(CAST(replies.created_at AS DATE)) AS varchar) AS activity_date,
'post_created' AS event_name,
'reply' AS entity_type,
replies.created_at AS activity_timestamp,
CAST(replies.author_id AS varchar) AS author_id,
CAST(replies.author_id AS varchar) AS user_id,
CAST(replies.topic_public_id AS varchar) AS topic_public_id,
CAST(replies.topic_private_id AS varchar) AS topic_private_id,
CAST(replies.reply_public_id AS varchar) AS reply_public_id,
CAST(replies.reply_private_id AS varchar) AS reply_private_id
FROM _topic_reply_cache AS replies
),
likes_given as (
SELECT
'like-given-topic-' || CAST(topic_public_id as varchar) || '-' || CAST(liked_by as varchar) AS event_id,
CAST(to_iso8601(CAST(liked_at AS DATE)) AS varchar) AS activity_date,
'like_given' AS event_name,
'topic' AS entity_type,
liked_at AT TIME ZONE 'UTC' AS activity_timestamp,
CAST(author_id AS varchar) AS author_id,
CAST(liked_by AS varchar) AS user_id,
CAST(topic_public_id AS varchar) AS topic_public_id,
CAST(topic_private_id AS varchar) AS topic_private_id,
CAST(NULL AS varchar) AS reply_public_id,
CAST(NULL AS varchar) AS reply_private_id
FROM "community-id".likes
WHERE reply_public_id IS NULL
UNION ALL
SELECT
'like-given-reply-' || '-' || CAST(topic_public_id as varchar) || '-' || CAST(reply_public_id as varchar) || '-' || CAST(liked_by as varchar) AS event_id,
CAST(to_iso8601(CAST(liked_at AS DATE)) AS varchar) AS activity_date,
'like_given' AS event_name,
'reply' AS entity_type,
liked_at AT TIME ZONE 'UTC' AS activity_timestamp,
CAST(author_id AS varchar) AS author_id,
CAST(liked_by AS varchar) AS user_id,
CAST(topic_public_id AS varchar) AS topic_public_id,
CAST(topic_private_id AS varchar) AS topic_private_id,
CAST(reply_public_id AS varchar) AS reply_public_id,
CAST(reply_private_id AS varchar) AS reply_private_id
FROM "community-id".likes
WHERE reply_public_id IS NOT NULL
),
likes_received as (
SELECT
'like-recv-topic-' || CAST(topic_public_id as varchar) || '-' || CAST(author_id as varchar) AS event_id,
CAST(to_iso8601(CAST(liked_at AS DATE)) AS varchar) AS activity_date,
'like_received' AS event_name,
'topic' AS entity_type,
liked_at AT TIME ZONE 'UTC' AS activity_timestamp,
CAST(author_id AS varchar) AS author_id,
CAST(author_id AS varchar) AS user_id,
CAST(topic_public_id AS varchar) AS topic_public_id,
CAST(topic_private_id AS varchar) AS topic_private_id,
CAST(NULL AS varchar) AS reply_public_id,
CAST(NULL AS varchar) AS reply_private_id
FROM "community-id".likes
WHERE reply_public_id IS NULL
UNION ALL
SELECT
'like-recv-reply-' || '-' || CAST(topic_public_id as varchar) || '-' || CAST(reply_public_id as varchar) || '-' || CAST(author_id as varchar) AS event_id,
CAST(to_iso8601(CAST(liked_at AS DATE)) AS varchar) AS activity_date,
'like_received' AS event_name,
'reply' AS entity_type,
liked_at AT TIME ZONE 'UTC' AS activity_timestamp,
CAST(author_id AS varchar) AS author_id,
CAST(author_id AS varchar) AS user_id,
CAST(topic_public_id AS varchar) AS topic_public_id,
CAST(topic_private_id AS varchar) AS topic_private_id,
CAST(reply_public_id AS varchar) AS reply_public_id,
CAST(reply_private_id AS varchar) AS reply_private_id
FROM "community-id".likes
WHERE reply_public_id IS NOT NULL
),
activities as (
select *
from post_created
union all
select *
from likes_received
union all
select *
from question_answered
union all
select *
from likes_given
),
top_users as (
select
user_points.user_id,
user_points.user_name,
MAX(points) as points,
COUNT(CASE WHEN activities.event_name = 'post_created' and activities.entity_type = 'topic' THEN 1 ELSE NULL END) as topics,
COUNT(CASE WHEN activities.event_name = 'post_created' and activities.entity_type = 'reply' THEN 1 ELSE NULL END) as replies,
COUNT(CASE WHEN activities.event_name = 'post_created' THEN 1 ELSE NULL END) as posts,
COUNT(CASE WHEN activities.event_name = 'question_answered' THEN 1 ELSE NULL END) as best_answer_given,
COUNT(CASE WHEN activities.event_name = 'like_received' THEN 1 ELSE NULL END) as likes_received,
COUNT(CASE WHEN activities.event_name = 'like_given' THEN 1 ELSE NULL END) as likes_given
from user_points
left join activities on user_points.user_id = activities.user_id
and activities.activity_date >= 'YYYY-MM-DD' and activities.activity_date <= 'YYYY-MM-DD'
where user_points.join_date <= YYYY-MM-DD'
group by user_points.user_id, user_points.user_name
)
select *
from top_users
order by posts desc
limit 100;
