대시보드 보고서 - 일일 활성 사용자

커뮤니티 구축 데이터 및 보고
, ,
1

이것은 일일 참여 사용자(Daily Engaged Users) 대시보드 보고서의 SQL 버전입니다.

해당 SQL 보고서는 매일 사이트에서 '좋아요’를 누르거나 게시물을 작성한 고유 사용자의 수를 집계합니다.

--[params]
-- date :start_date
-- date :end_date

SELECT 
  ua.created_at::date AS day,
  COUNT(DISTINCT ua.user_id) AS engaged_users
FROM user_actions ua
INNER JOIN topics t on t.id = ua.target_topic_id
WHERE ua.created_at::date BETWEEN :start_date AND :end_date
    AND ua.action_type IN (1, 4, 5, 12)
GROUP BY ua.created_at::date
ORDER BY ua.created_at::date ASC

쿼리 분석:

  • 날짜 파라미터: 쿼리는 :start_date와 :end_date라는 두 개의 날짜 파라미터를 받아 보고서의 범위를 지정할 수 있습니다.
  • 선택(Selection): 액션의 날짜(ua.created_at::date)를 선택하고, 해당 액션을 수행한 고유 사용자 ID(COUNT(DISTINCT ua.user_id))를 집계하여 이를 engaged_users로 별칭을 부여합니다.
  • 데이터 소스: 쿼리는 user_actions 테이블(ua)에서 데이터를 가져오고, 토픽과 관련된 액션에 초점을 맞추기 위해 topics 테이블(t)과 조인(joins)합니다.
  • 액션 유형: 액션은 유형 1, 4, 5, 12로만 필터링됩니다. 이러한 액션 유형은 다음 특정 활동에 해당합니다:
    • 1: Like(좋아요)
    • 4: New Topic(새 토픽)
    • 15: Reply(답글)
    • 12: New Private Message(새 개인 메시지)
  • 그룹화 및 정렬: 결과는 액션 날짜별로 그룹화되고 오름차순으로 정렬되어 활동의 시간 순서대로 확인할 수 있습니다.

결과 예시

day engaged_users
2023-01-01 136
2023-01-02 124
2023-01-03 187
2023-01-04 287
2023-01-05 110
2023-01-06 95
2023-01-07 73
2

이것은 정리해서 살펴보면 꽤 흥미롭습니다. 반응(Reactions)과 투표(폴, 주제 투표, 게시글 투표 - 심지어 게시글 투표에 대한 댓글까지 포함) 기능이 포함된 버전이 있으면 좋겠습니다. :thinking: