This article helps admins and data teams understand how to query and calculate export metrics using the Customer Communities Data Lake.
Overview
The Exports query reference provides SQL logic used to generate each of the available community data exports, including post, topic, user, login, marked answer, and moderator actions. These queries align with the structure of Exports V2 and are intended for use with the Customer Communities Data Lake.
This reference enables administrators, analysts, and developers to validate exported data, troubleshoot discrepancies, and reproduce metrics in downstream tools. Each query reflects the latest data model and filtering logic used in the Exports V2 experience.
For more information on changes in Exports, refer to the Change Document for Exports V2 article.
KPI Queries
The following section outlines each Key Performance Indicator (KPI) available in the exports, along with the corresponding SQL query used to calculate it in the CC Data Lake.
Post Export
This export contains individual posts (both topics and replies).
with posts as (
select
t.topic_public_id as topic_id,
' ' as reply_id,
t.title as topic_title,
coalesce(t.category_id, t.group_id) as forum_id,
t.created_at as created_at,
t.author_id as author_id,
t.last_moderated_by as last_moderator_id,
t.last_moderated_at as last_moderated_at,
t.trashed_at as trashed_at,
t.marked_spam_at as marked_spam_at,
count(l.like_id) as likes,
null as prev_post_id,
null as prev_post_created_at,
'topic' as post_type,
t.pending_since as pending_since,
t.published_at as published_at,
t.content_type
from
topic t
left join likes l on t.topic_public_id = l.topic_public_id
and l.reply_public_id is null
group by
t.topic_public_id,
t.title,
t.category_id,
t.group_id,
t.created_at,
t.author_id,
t.last_moderated_by,
t.last_moderated_at,
t.trashed_at,
t.marked_spam_at,
t.pending_since,
t.published_at,
t.content_type
union all
select
t.topic_public_id as topic_id,
tr.reply_public_id as reply_id,
t.title as topic_title,
coalesce(t.category_id, t.group_id) as forum_id,
tr.replied_at as created_at,
tr.author_id as author_id,
t.last_moderated_by as last_moderator_id,
t.last_moderated_at as last_moderated_at,
tr.trashed_at as trashed_at,
tr.marked_spam_at as marked_spam_at,
count(l.like_id) as likes,
coalesce(
(
lag(
case when tr.trashed_at is null
and tr.marked_spam_at is null then tr.reply_public_id end
) ignore nulls over (
partition by tr.topic_public_id
order by
tr.replied_at
)
),
tr.topic_public_id
) as prev_post_id,
coalesce(
(
lag(
case when tr.trashed_at is null
and tr.marked_spam_at is null then tr.replied_at end
) ignore nulls over (
partition by tr.topic_public_id
order by
tr.replied_at
)
),
t.created_at
) as prev_post_created_at,
'reply' as post_type,
t.pending_since as pending_since,
null as published_at,
t.content_type
from
topic_reply tr
join topic t on t.topic_public_id = tr.topic_public_id
left join likes l on tr.reply_public_id = l.reply_public_id
group by
t.topic_public_id,
tr.reply_public_id,
t.title,
t.category_id,
t.group_id,
tr.replied_at,
tr.author_id,
t.last_moderated_by,
t.last_moderated_at,
tr.trashed_at,
tr.marked_spam_at,
tr.topic_public_id,
t.created_at,
t.pending_since,
t.content_type
),
forum as (
select
category_id as forum_id,
title as forum_title,
parent_id as parent_forum_id,
"module"
from
category
union all
select
group_id as forum_id,
name as forum_title,
null as parent_forum_id,
null as "module"
from
"group"
)
select
CAST(
(
CASE WHEN p.post_type = 'topic' THEN p.topic_id ELSE p.reply_id END
) AS VARCHAR
) as post_id,
CAST(p.topic_id AS VARCHAR) as topic_id,
CAST(p.post_type AS VARCHAR) as post_type,
CAST(p.topic_title AS VARCHAR) as topic_title,
CAST(f.forum_title AS VARCHAR) as subforum_title,
CAST(pf.forum_title AS VARCHAR) as category_title,
CAST(u.user_name AS VARCHAR) as post_creator,
CAST(r.name AS VARCHAR) as user_rank,
CAST(
array_join(
array_agg(role.label),
', '
) AS VARCHAR
) AS user_role,
CAST(
date_format(
p.created_at, '%Y-%m-%d %H:%i:%s'
) AS VARCHAR
) as post_creation_time,
CAST(
date_format(
p.last_moderated_at, '%Y-%m-%d %H:%i:%s'
) AS VARCHAR
) as moderate_timeread,
CAST(p.last_moderator_id AS VARCHAR) as moderator_id,
CAST(
COALESCE(p.likes, 0) AS VARCHAR
) as likes,
CAST(f.module AS VARCHAR) as module,
CAST(
date_diff(
'second', p.prev_post_created_at,
p.created_at
) AS VARCHAR
) as time_since_previous_post,
CAST(
case when p.trashed_at is not null then 'trashed' when p.marked_spam_at is not null then 'marked_spam' when p.pending_since is not null then 'pending' when p.post_type = 'topic'
and p.content_type in ('article', 'productUpdate')
and p.published_at is null then 'pending_create' else 'visible' end AS VARCHAR
) as status
from
posts p
left join forum f on p.forum_id = f.forum_id
left join forum pf on pf.forum_id = f.parent_forum_id
left join user u on p.author_id = u.user_id
left join rank r on u.rank_id = r.rank_id
left join user_role uro on u.user_id = uro.user_id
left join role on uro.role_id = role.role_id
where
cast(p.created_at as DATE) between DATE 'date_from'
and DATE 'date_to'
group by
p.topic_id,
p.reply_id,
p.topic_title,
f.forum_title,
pf.forum_title,
u.user_name,
r.name,
p.created_at,
p.last_moderator_id,
p.last_moderated_at,
p.likes,
f.module,
p.marked_spam_at,
p.trashed_at,
p.prev_post_id,
p.prev_post_created_at,
p.post_type,
p.pending_since,
p.content_type,
p.published_at
order by
p.created_at
Marked as Answers Export
Focuses on questions that have a reply marked as the Best Answer.
WITH topic as (
select
topic_public_id,
title,
category_id,
group_id,
created_at,
author_id,
answer_id,
moderation_label_id
from
topic
where
content_type = 'question'
),
topic_views as (
select
t.topic_public_id,
count(pv.topic_public_id) as views
from
pageview pv
right join topic t on t.topic_public_id = pv.topic_public_id
where
CAST(
pv.year || '-' || pv.month || '-01' AS DATE
) >= DATE_TRUNC('month', DATE 'date_from')
group by
t.topic_public_id
),
_topic_replies as (
select
tr.topic_public_id,
tr.reply_public_id,
tr.replied_at,
tr.author_id,
row_number() OVER (
PARTITION BY tr.topic_public_id
ORDER BY
tr.replied_at
) AS row_num,
count(tr.reply_public_id) over (PARTITION BY tr.topic_public_id) as replies_count
from
topic_reply tr
),
_moderators as (
select
u.user_id
from
user u
join user_role ur on u.user_id = ur.user_id
join role r on ur.role_id = r.role_id
where
r.name in (
'roles.moderator', 'roles.super-admin',
'roles.community-manager', 'roles.administrator'
)
),
_moderator_replies as (
select
tr.topic_public_id,
tr.reply_public_id,
tr.replied_at,
tr.author_id,
row_number() OVER (
PARTITION BY tr.topic_public_id
ORDER BY
tr.replied_at
) AS row_num
from
topic_reply tr
join _moderators m on tr.author_id = m.user_id
where
m.user_id is not null
),
topic_replies as (
select
t.topic_public_id,
tr.replies_count as replies,
tr.reply_public_id as first_reply_id,
tr.replied_at as first_reply_at,
tr.author_id as first_reply_author_id,
ta.reply_public_id as answer_id,
ta.replied_at as answered_at,
ta.author_id as answer_author_id,
mr.author_id as moderator_first_reply_author_id,
mr.replied_at as moderator_first_reply_at,
t.title as title,
t.category_id as category_id,
t.group_id as group_id,
t.created_at as topic_created_at,
t.author_id as topic_author_id,
t.moderation_label_id as moderation_label_id
from
topic t
join topic_reply ta on t.topic_public_id = ta.topic_public_id
and t.answer_id = ta.reply_public_id
join _topic_replies tr on t.topic_public_id = tr.topic_public_id
left join _moderator_replies mr on t.topic_public_id = mr.topic_public_id
and mr.row_num = 1
where
tr.row_num = 1
),
topics as (
select
tr.topic_public_id,
tr.title as topic_title,
coalesce(tr.category_id, tr.group_id) as forum_id,
tr.topic_created_at as created_at,
tr.topic_author_id as author_id,
tv.views,
tr.replies,
tr.answer_id,
tr.answered_at,
tr.first_reply_id,
tr.first_reply_author_id,
tr.answer_author_id,
tr.first_reply_at,
tr.moderator_first_reply_at,
tr.moderation_label_id
from
topic_replies tr
join topic_views tv on tr.topic_public_id = tv.topic_public_id
),
_forum as (
select
category_id as forum_id,
title as forum_title,
parent_id as parent_forum_id,
"module"
from
category
union all
select
group_id as forum_id,
name as forum_title,
null as parent_forum_id,
null as "module"
from
"group"
),
forum as (
select
f.forum_id,
f.forum_title,
pf.forum_title as parent_forum_title,
f.parent_forum_id,
f."module"
from
_forum f
left join _forum pf on f.parent_forum_id = pf.forum_id
),
user_stats as (
select
u.user_id,
u.user_name,
r.name as user_rank,
array_join(
array_agg(distinct role.label),
', '
) AS user_roles
from
user u
left join rank r on u.rank_id = r.rank_id
left join user_role uro on u.user_id = uro.user_id
left join role on uro.role_id = role.role_id
group by
u.user_id,
u.user_name,
r.name
),
topic_public_tags as (
select
tpt.topic_public_id,
array_join(
array_agg(distinct public_tag_name),
', '
) as public_tags
from
topic_public_tag tpt
join public_tag pt on tpt.public_tag_id = pt.public_tag_id
group by
tpt.topic_public_id
),
_cqa as (
select
tr.topic_public_id,
tr.reply_public_id,
qa.executed_by as marked_ans_by,
cast(
from_iso8601_timestamp(qa.occurred_at) as timestamp
) as marked_as_answer_at,
row_number() over (
partition by qa.topic_public_id,
qa.reply_private_id
order by
cast(
from_iso8601_timestamp(qa.occurred_at) as timestamp
) desc
) as rownum
from
content_question_answered qa
join topic_reply tr on qa.reply_private_id = tr.reply_private_id
where
CAST(
qa.year || '-' || qa.month || '-01' AS DATE
) between DATE_TRUNC('month', DATE 'date_from')
and DATE_TRUNC('month', DATE 'date_to')
and tr.content_type = 'question'
and qa.reply_private_id is not null
),
cqa as (
select
topic_public_id,
reply_public_id,
marked_ans_by,
marked_as_answer_at
from
_cqa
where
rownum = 1
)
select
t.topic_public_id as topic_id,
t.topic_title as topic_title,
f.forum_title as subforum_title,
f.parent_forum_title as category_title,
COALESCE(t.views, 0) as total_views,
COALESCE(t.replies, 0) as total_comments,
date_format(
t.created_at, '%Y-%m-%d %H:%i:%s'
) as post_1_creation_time,
u1.user_name as post_1_creator,
u1.user_roles as post_1_user_role,
u1.user_rank as post_1_user_rank,
date_format(
t.first_reply_at, '%Y-%m-%d %H:%i:%s'
) as post_2_creation_time,
u2.user_name as post_2_creator,
u2.user_roles as post_2_user_role,
u2.user_rank as post_2_user_rank,
DATE_DIFF(
'second', t.created_at, t.first_reply_at
) as post_1_post_2_time_difference,
t.answered_at as post_answered_creation_time,
u3.user_name as post_answered_user,
u3.user_roles as post_answered_user_role,
u3.user_rank as post_answered_user_rank,
DATE_DIFF(
'second', t.created_at, t.answered_at
) as post_1_post_answered_time_difference,
date_format(
cqa.marked_as_answer_at, '%Y-%m-%d %H:%i:%s'
) as post_answered_marked_time,
u4.user_name as post_answered_markedby_user_name,
u4.user_rank as post_answered_markedby_user_rank,
u4.user_roles as post_answered_markedby_user_role,
date_format(
t.moderator_first_reply_at, '%Y-%m-%d %H:%i:%s'
) as first_reply_by_a_moderator_time,
tpt.public_tags as public_tags,
m.name as moderator_label
from
topics t
join cqa on t.topic_public_id = cqa.topic_public_id
and t.answer_id = cqa.reply_public_id
left join forum f on t.forum_id = f.forum_id
left join user_stats u1 on t.author_id = u1.user_id
left join user_stats u2 on t.first_reply_author_id = u2.user_id
left join user_stats u3 on t.answer_author_id = u3.user_id
left join user_stats u4 on cqa.marked_ans_by = u4.user_id
left join topic_public_tags tpt on t.topic_public_id = tpt.topic_public_id
left join moderation_label m on m.moderation_label_id = t.moderation_label_id
where
cast(cqa.marked_as_answer_at as date) between date 'date_from'
and date 'date_to'
order by
cqa.marked_as_answer_at
Private Message Export
The private messages export provides an overview of all two-way private message communication.
with user_stats as (
select
u.user_id,
u.user_name,
r.name as rank,
array_join(
array_agg(distinct ro.label),
', '
) as roles
from
user u
left join rank r on r.rank_id = u.rank_id
left join user_role ur on ur.user_id = u.user_id
left join role ro on ro.role_id = ur.role_id
group by
u.user_id,
u.user_name,
r.name
)
select
sender.user_name as sender_username,
sender.rank as sender_user_rank,
sender.roles as sender_user_role,
receiver.user_name receiver_username,
receiver.rank as receiver_user_rank,
receiver.roles as receiver_user_role,
date_format(m.sent_at, '%Y-%m-%d %H:%i:%s') as "time"
from
pm_message m
join pm_conversation c on m.conversation_id = c.conversation_id
join user_stats sender on sender.user_id = c.sender_user_id
join user_stats receiver on receiver.user_id = c.receiver_user_id
where
cast(m.sent_at as DATE) between date 'date_from'
and date 'date_to'
order by
m.pm_message_id desc
Topic Export
This export focuses on the opening post.
WITH topic_likes as (
select
t.topic_public_id,
count(l.topic_public_id) as likes
from
topic t
left join likes l on t.topic_public_id = l.topic_public_id
and l.reply_public_id is null
group by
t.topic_public_id
),
topic_views as (
select
t.topic_public_id,
count(pv.topic_public_id) as views
from
pageview pv
right join topic t on t.topic_public_id = pv.topic_public_id
where
CAST(
pv.year || '-' || pv.month || '-01' AS DATE
) >= DATE_TRUNC('month', DATE 'date_from')
group by
t.topic_public_id
),
topic_public_tags as (
select
tpt.topic_public_id,
array_join(
array_agg(distinct public_tag_name),
', '
) as public_tags
from
topic_public_tag tpt
join public_tag pt on tpt.public_tag_id = pt.public_tag_id
group by
tpt.topic_public_id
),
topic_moderator_tags as (
select
tmt.topic_public_id,
array_join(
array_agg(distinct moderator_tag_name),
', '
) as moderator_tags
from
topic_moderator_tag tmt
join moderator_tag mt on tmt.moderator_tag_id = mt.moderator_tag_id
group by
tmt.topic_public_id
),
topic_replies as (
select
t.topic_public_id,
count(tr.reply_public_id) as replies
from
topic t
left join topic_reply tr on t.topic_public_id = tr.topic_public_id
group by
t.topic_public_id
),
topics as (
select
t.topic_public_id as topic_public_id,
t.title as topic_title,
coalesce(t.category_id, t.group_id) as forum_id,
t.created_at as created_at,
t.author_id as author_id,
t.last_moderated_by as last_moderator_id,
t.last_moderated_at as last_moderated_at,
t.trashed_at as trashed_at,
t.marked_spam_at as marked_spam_at,
t.content_type,
tv.views,
tl.likes,
tpt.public_tags,
tmt.moderator_tags,
tr.replies,
t.answer_id,
t.answered_at,
t.last_activity_at,
t.closed_at,
t.is_sticky,
t.moderation_label_id,
t.assigned_to,
u.user_name as assigned_to_user_name,
_is.name as idea_status,
he.helpful_count as helpful_votes,
he.not_helpful_count as not_helpful_votes,
he.neutral_count as neutral_votes,
t.pending_since as pending_since,
t.published_at
from
topic t
left join topic_views tv on t.topic_public_id = tv.topic_public_id
left join topic_likes tl on t.topic_public_id = tl.topic_public_id
left join topic_moderator_tags tmt on t.topic_public_id = tmt.topic_public_id
left join topic_public_tags tpt on t.topic_public_id = tpt.topic_public_id
left join topic_replies tr on t.topic_public_id = tr.topic_public_id
left join user u on t.assigned_to = u.user_id
left join idea_status _is on t.idea_status = _is.idea_status_id
left join helpfulness_score he on t.topic_public_id = he.topic_public_id
),
_forum as (
select
category_id as forum_id,
title as forum_title,
parent_id as parent_forum_id,
"module"
from
category
union all
select
group_id as forum_id,
name as forum_title,
null as parent_forum_id,
null as "module"
from
"group"
),
forum as (
select
f.forum_id,
f.forum_title,
pf.forum_title as parent_forum_title,
f.parent_forum_id,
f."module"
from
_forum f
left join _forum pf on f.parent_forum_id = pf.forum_id
),
user_stats as (
select
u.user_id,
u.user_name,
r.name as user_rank,
array_join(
array_agg(role.label),
', '
) AS user_roles
from
user u
left join rank r on u.rank_id = r.rank_id
left join user_role uro on u.user_id = uro.user_id
left join role on uro.role_id = role.role_id
group by
u.user_id,
u.user_name,
r.name
)
select
t.topic_public_id as topic_id,
t.topic_title as topic_title,
case when t.content_type != 'question' then t.content_type when t.answer_id is not null then 'Solved' else 'Question' end as topic_type,
cast(
case when t.trashed_at is not null then 'trashed' when t.marked_spam_at is not null then 'marked_spam' when t.pending_since is not null then 'pending' when t.content_type in ('article', 'productUpdate')
and t.published_at is null then 'pending_create' else 'visible' end AS VARCHAR
) as topic_status,
' ' as topic_url,
f.forum_title as subforum_title,
f.parent_forum_title as category_title,
t.public_tags,
t.moderator_tags,
COALESCE(t.views, 0) as total_views,
COALESCE(t.replies, 0) as total_comments,
u.user_name as topic_creator,
u.user_rank,
u.user_roles as user_role,
date_format(
t.created_at, '%Y-%m-%d %H:%i:%s'
) as date_creation,
date_format(
t.last_activity_at, '%Y-%m-%d %H:%i:%s'
) as date_last_active,
case when t.closed_at is null then 'Yes' else 'No' end as open,
case when t.is_sticky then 'Yes' else 'No' end as sticky,
m.name as label_id,
t.assigned_to_user_name as assigned_to,
COALESCE(t.likes, 0) as likes_opening_post,
t.idea_status as ideation_status,
COALESCE(t.helpful_votes, 0) as helpful_votes,
COALESCE(t.not_helpful_votes, 0) as not_helpful_votes,
COALESCE(t.neutral_votes, 0) as neutral_votes
from
topics t
left join forum f on t.forum_id = f.forum_id
left join user_stats u on t.author_id = u.user_id
left join moderation_label m on m.moderation_label_id = t.moderation_label_id
where
cast(t.created_at as date) between date 'date_from'
and date 'date_to'
order by
t.created_at
User Export
User Exports includes all activity data, even from hidden groups and categories excluded from statistics, providing a complete view of user interactions.
with topics_count as(
select
author_id,
count(1) as total_topics
from
topic
group by
author_id
),
topic_replies_count as (
select
author_id,
count(1) as total_comments
from
topic_reply
group by
author_id
),
answered_count as (
select
tr.author_id as user_id,
count(tr.author_id) as total_answeredcomments
from
topic_reply tr
join topic t on tr.reply_public_id = t.answer_id
and tr.topic_public_id = t.topic_public_id
group by
tr.author_id
),
likes_count as (
select
author_id,
count(1) as total_likes
from
likes
group by
author_id
),
ranked_login_records as (
select
user_id,
occurred_at,
row_number() over (
partition by user_id
order by
occurred_at desc
) as rn
from
identity_access_enduser_logged_in
),
last_login as (
select
user_id,
date_format(
from_iso8601_timestamp(occurred_at),
'%Y-%m-%d %H:%i:%s'
) as occurred_at
from
ranked_login_records
where
rn = 1
),
total_login_count as (
select
user_id,
count(user_id) as total_logins
from
identity_access_enduser_logged_in
group by
user_id
),
topic_helpful_count as (
select
user_id,
count(user_id) as helpfulness_positive_votes_given
from
helpfulness_topic_helpful
group by
user_id
),
topic_not_helpful_count as (
select
user_id,
count(user_id) as helpfulness_negative_votes_given
from
helpfulness_topic_not_helpful
group by
user_id
),
topic_neutral_count as (
select
user_id,
count(user_id) as helpfulness_neutral_votes_given
from
helpfulness_topic_neutral
group by
user_id
)
select
u.user_id,
u.user_name as username,
u.email,
rk.name as userRank,
ARRAY_JOIN(
ARRAY_AGG(r.label),
', '
) as userRole,
tc.total_topics,
trc.total_comments,
ac.total_answeredcomments,
lc.total_likes,
u.last_activity_at as last_activity,
ll.occurred_at as last_logged_in,
u.join_date as join_dates,
tlc.total_logins as login_count,
thc.helpfulness_positive_votes_given,
tnhc.helpfulness_negative_votes_given,
tnc.helpfulness_neutral_votes_given
from
user u
left join rank rk on u.rank_id = rk.rank_id
left join user_role ur on u.user_id = ur.user_id
left join role r on ur.role_id = r.role_id
left join topics_count tc on u.user_id = tc.author_id
left join topic_replies_count trc on u.user_id = trc.author_id
left join answered_count ac on u.user_id = ac.user_id
left join likes_count lc on u.user_id = lc.author_id
left join last_login ll on u.user_id = ll.user_id
left join total_login_count tlc on u.user_id = tlc.user_id
left join topic_helpful_count thc on u.user_id = thc.user_id
left join topic_not_helpful_count tnhc on u.user_id = tnhc.user_id
left join topic_neutral_count tnc on u.user_id = tnc.user_id
where
CAST(u.join_date AS DATE) between date 'date_from'
and date 'date_to'
group by
u.user_id,
u.user_name,
u.email,
rk.name,
u.join_date,
tc.total_topics,
trc.total_comments,
ac.total_answeredcomments,
lc.total_likes,
u.last_activity_at,
ll.occurred_at,
tlc.total_logins,
thc.helpfulness_positive_votes_given,
tnhc.helpfulness_negative_votes_given,
tnc.helpfulness_neutral_votes_given
order by
email
Login Export
Login Export provides the login details of all user login events, based on the identity_access_* models from data-lake.
select
u.user_name,
r.name as user_rank,
array_join(
array_agg(DISTINCT role.label),
', '
) AS user_role,
date_format(
from_iso8601_timestamp(ie.occurred_at),
'%Y-%m-%d %H:%i:%s'
) as time,
'user_login' as event_type
from
identity_access_enduser_logged_in ie
join user u on ie.user_id = u.user_id
left join rank r on u.rank_id = r.rank_id
join user_role ur on u.user_id = ur.user_id
join role on role.role_id = ur.role_id
where
from_iso8601_timestamp(ie.occurred_at) between date 'date_from'
and date 'date_to'
group by
u.user_name,
r.name,
date_format(
from_iso8601_timestamp(ie.occurred_at),
'%Y-%m-%d %H:%i:%s'
)
order by
user_name,
time