This article helps admins and data teams understand how to calculate the Key Performance Indicators (KPIs) displayed in the Engagement dashboard using queries from the CC Data Lake.
Overview
The Engagement dashboard KPIs – Query Reference provides the SQL logic behind each KPI widget in the Community Engagement dashboard. These queries are designed for use with the CC Data Lake, allowing community administrators, analysts, and developers to understand KPIs.
For more information on Audience Dashboard, refer to the Engagement Dashboard article.
Prerequisite: Salesforce Account Import integration is required.
The Engagement dashboard requires company data to show you customer analytics. To get this data into the Engagement dashboard, integrate Salesforce account to Customer Community.
For more information on how to import Salesforce Accounts, refer to How to Import Salesforce Account Data to CC article.
KPI Query and Reference
The following section outlines each Key Performance Indicator (KPI) available in the Engagement dashboard, along with the corresponding SQL query used to calculate it in the CC Data Lake.
| KPIs | Description |
|---|---|
| % Monthly active customers | The percentage of active customers that had at least one user perform any activity (such as a page view, post, reply, or like) in the community within a given calendar month. |
| Customer activity per month | The total number of companies where at least one user linked to the company had at least one page view in a given calendar month. |
| Active customers | A detailed table listing customers that had at least one user engage in the community during the selected period. |
| Inactive customers | Displays customers who had no user activity during the selected period. |
WITH months as (
SELECT
CAST(month AS TIMESTAMP WITH TIME ZONE) as month
FROM
UNNEST(
sequence (
timestamp 'YYYY-MM-DD', timestamp 'YYYY-MM-DD',
interval '1' month
)
) AS t(month)
),
_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_ans 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_ans' as event_name,
'question_ans' 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'
),
_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_temp 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,
user_id_2,
'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_temp
),
visitors AS (
SELECT
entity_type || '-' || COALESCE(
CAST(visitor_id as varchar),
''
) || '-' || format(
'%.0f',
1000 * to_unixtime(activity_timestamp)
) || '-' || CAST(row_num AS varchar) AS event_id,
CAST(
to_iso8601(activity_date) AS varchar
) AS activity_date,
activity_timestamp AS activity_timestamp,
CAST(user_id AS varchar) AS user_id,
CAST(event_name AS varchar) AS event_name,
CAST(unique_visitor_id AS varchar) AS unique_visitor_id,
CAST(entity_type AS varchar) AS entity_type,
CAST(0 AS varchar) AS points,
CAST(NULL AS varchar) AS subject
FROM
_visitors
),
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
),
_union_sf_user_event as (
SELECT
company_id,
from_iso8601_timestamp(occurred_at) as occurred_at,
user_id,
'user_added' as name,
ROW_NUMBER() over(
partition by company_id, user_id, occurred_at
) as row_num
FROM
"community-id".salesforce_company_user_added
UNION ALL
SELECT
company_id,
from_iso8601_timestamp(occurred_at) as occurred_at,
user_id,
'user_removed' as name,
ROW_NUMBER() over(
partition by company_id, user_id, occurred_at
) as row_num
FROM
"community-id".salesforce_company_user_removed
),
_union_sf_company_event as (
SELECT
company_name,
'company_processed' as event_name,
from_iso8601_timestamp(occurred_at) as occurred_at,
company_id,
status as company_status,
ROW_NUMBER() over(
partition by company_id, company_name,
status, occurred_at
) as row_num
FROM
"community-id".salesforce_company_processed
UNION ALL
SELECT
company_name,
'company_updated' as event_name,
from_iso8601_timestamp(occurred_at) as occurred_at,
company_id,
status as company_status,
ROW_NUMBER() over(
partition by company_id, company_name,
status, occurred_at
) as row_num
FROM
"community-id".salesforce_company_updated
UNION ALL
SELECT
'' as company_name,
'company_removed' as event_name,
from_iso8601_timestamp(occurred_at) as occurred_at,
company_id,
'' as company_status,
ROW_NUMBER() over(
partition by company_id, occurred_at
) as row_num
FROM
"community-id".salesforce_company_removed
),
_user_company as (
select
user_id,
company_id,
occurred_at,
name,
LEAD(occurred_at) OVER (
PARTITION BY user_id,
company_id
ORDER BY
occurred_at
) as lead
from
_union_sf_user_event
where
row_num = 1
),
user_company as (
select
user_id,
company_id,
occurred_at as added_date,
lead as removed_date
from
_user_company
where
name = 'user_added'
),
_company_event as (
select
company_name,
company_id,
occurred_at,
LEAD(occurred_at) OVER (
PARTITION BY company_id
ORDER BY
occurred_at
) as lead,
event_name,
company_status
from
_union_sf_company_event
where
row_num = 1
),
company as (
select
company_name,
company_id,
occurred_at as added_date,
lead as removed_date
from
_company_event
where
event_name in (
'company_processed', 'company_updated'
)
and company_status = 'Customer'
),
_monthly_company_events as (
SELECT
month,
company_name,
company_status,
company_id,
event_name,
occurred_at,
ROW_NUMBER() OVER (
PARTITION BY month,
company_id
ORDER BY
occurred_at DESC
) as rownum
FROM
months
LEFT JOIN _company_event ON (
(
occurred_at <= DATE_ADD('month', 1, month)
and event_name in (
'company_updated', 'company_processed'
)
and company_status = 'Customer'
)
OR (occurred_at <= month)
)
),
_monthly_existing_customers as (
SELECT
*
FROM
_monthly_company_events
WHERE
event_name in (
'company_updated', 'company_processed'
)
AND company_status = 'Customer'
AND rownum = 1
),
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_g' 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_g' 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_r' 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_r' 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
),
likes as (
SELECT
*
FROM
likes_given
UNION ALL
SELECT
*
FROM
likes_received
),
helpfulness as (
SELECT
'helpfulness-helpful-' || topic.topic_public_id || '-' || COALESCE(
"community-id".helpfulness_topic_helpful.user_id,
'guest'
) || '-' || format(
'%.0f',
1000 * to_unixtime(
from_iso8601_timestamp(occurred_at)
)
) || '-' || CAST(
ROW_NUMBER() OVER (
PARTITION BY topic.topic_public_id,
"community-id".helpfulness_topic_helpful.user_id
ORDER BY
from_iso8601_timestamp(occurred_at)
) AS VARCHAR
) AS event_id,
cast(
to_iso8601(
cast(
from_iso8601_timestamp(occurred_at) as date
)
) AS varchar
) AS activity_date,
'vote_helpful' AS event_name,
'topic' AS entity_type,
from_iso8601_timestamp(occurred_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
"community-id".helpfulness_topic_helpful
JOIN _topic_cache AS topic ON topic.topic_public_id = "community-id".helpfulness_topic_helpful.topic_public_id
UNION ALL
SELECT
'helpfulness-neutral-' || topic.topic_public_id || '-' || COALESCE(
"community-id".helpfulness_topic_neutral.user_id,
'guest'
) || '-' || format(
'%.0f',
1000 * to_unixtime(
from_iso8601_timestamp(occurred_at)
)
) || '-' || CAST(
ROW_NUMBER() OVER (
PARTITION BY topic.topic_public_id,
"community-id".helpfulness_topic_neutral.user_id
ORDER BY
from_iso8601_timestamp(occurred_at)
) AS VARCHAR
) AS event_id,
cast(
to_iso8601(
cast(
from_iso8601_timestamp(occurred_at) as date
)
) AS varchar
) AS activity_date,
'vote_neutral' AS event_name,
'topic' AS entity_type,
from_iso8601_timestamp(occurred_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
"community-id".helpfulness_topic_neutral
JOIN _topic_cache AS topic ON topic.topic_public_id = "community-id".helpfulness_topic_neutral.topic_public_id
UNION ALL
SELECT
'helpfulness-not_helpful-' || topic.topic_public_id || '-' || COALESCE(
"community-id".helpfulness_topic_not_helpful.user_id,
'guest'
) || '-' || format(
'%.0f',
1000 * to_unixtime(
from_iso8601_timestamp(occurred_at)
)
) || '-' || CAST(
ROW_NUMBER() OVER (
PARTITION BY topic.topic_public_id,
"community-id".helpfulness_topic_not_helpful.user_id
ORDER BY
from_iso8601_timestamp(occurred_at)
) AS VARCHAR
) AS event_id,
cast(
to_iso8601(
CAST(
from_iso8601_timestamp(occurred_at) AS DATE
)
) AS varchar
) AS activity_date,
'vote_not_helpful' AS event_name,
'topic' AS entity_type,
from_iso8601_timestamp(occurred_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
"community-id".helpfulness_topic_not_helpful
JOIN _topic_cache AS topic ON topic.topic_public_id = "community-id".helpfulness_topic_not_helpful.topic_public_id
),
community_activity as (
SELECT
activity_timestamp,
activity_date,
event_name,
entity_type,
user_id
FROM
likes
UNION ALL
SELECT
activity_timestamp,
activity_date,
event_name,
entity_type,
user_id
FROM
helpfulness
UNION ALL
SELECT
activity_timestamp,
activity_date,
event_name,
entity_type,
user_id
FROM
post_created
UNION ALL
SELECT
activity_timestamp,
activity_date,
event_name,
entity_type,
user_id
FROM
visitors
UNION ALL
SELECT
activity_timestamp,
activity_date,
event_name,
entity_type,
user_id
FROM
question_ans
),
company_activity as (
select
activity_timestamp,
activity_date,
event_name,
entity_type,
c.company_name,
a.user_id
from
community_activity a
join user_company uc on a.user_id = uc.user_id
and a.activity_timestamp > uc.added_date
and (
a.activity_timestamp < uc.removed_date
or uc.removed_date is null
)
join company c on uc.company_id = c.company_id
and a.activity_timestamp > c.added_date
and (
a.activity_timestamp < c.removed_date
or c.removed_date is null
)
where
a.activity_date < 'YYYY-MM-DD'
),
monthly_active_customers as (
SELECT
CAST(
to_iso8601(
_monthly_existing_customers.month
) AS VARCHAR
) as month,
COUNT (
DISTINCT (
_monthly_existing_customers.company_name
)
) AS total,
COUNT (
DISTINCT (company_activity.company_name)
) AS active
FROM
_monthly_existing_customers
LEFT JOIN company_activity ON _monthly_existing_customers.company_name = company_activity.company_name
AND company_activity.activity_timestamp BETWEEN _monthly_existing_customers.month
AND DATE_ADD(
'month', 1, _monthly_existing_customers.month
)
GROUP BY
_monthly_existing_customers.month
),
active_customers as (
select
company_name,
count(
distinct(user_id)
) as unique_visitors,
count(
case when event_name = 'view' then 1 else null end
) as page_views,
count(
case when event_name = 'post_created' then 1 else null end
) as posts,
count(
case when event_name = 'post_created'
and entity_type = 'topic' then 1 else null end
) as topics,
count(
case when event_name = 'post_created'
and entity_type = 'reply' then 1 else null end
) as replies,
count(
case when event_name = 'like_g' then 1 else null end
) as likes_given
from
company_activity
where
activity_date >= 'YYYY-MM-DD'
and activity_date <= 'YYYY-MM-DD'
group by
company_name
order by
3 desc
),
inactive_customers as (
select
company_name,
max(activity_timestamp) as last_seen
from
company_activity
where
activity_date < 'YYYY-MM-DD'
group by
company_name
having
max(activity_date) < 'YYYY-MM-DD'
order by
last_seen desc
)
select
*
from
monthly_active_customers
order by
month
select
*
from
active_customers
limit
10
select
*
from
inactive_customers
limit
10