월별 상위 트래픽 소스 리포트는 어떻게 하나요?

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

안녕하세요,

월별 상위 트래픽 소스 보고서를 생성하려고 합니다. 각 월별로 소스가 어떻게 변화했는지 하나의 그래프/표 안에서 한눈에 볼 수 있으면 좋겠습니다. 이 데이터를 가져올 수 있는 방법이 있을까요?

현재는 관심 있는 5개월 전체 기간의 합계만 확인할 수 있습니다:

image
image1231×691 36.5 KB

감사합니다.

나초

2 
WITH traffic_sources AS (
  SELECT
    EXTRACT(MONTH FROM user_visits.visited_at) AS month,
    incoming_referers.incoming_domain_id,
    COUNT(*) AS visit_count,
    LAG(COUNT(*)) OVER (PARTITION BY incoming_referers.incoming_domain_id ORDER BY EXTRACT(MONTH FROM user_visits.visited_at)) AS prev_month_visit_count
  FROM  user_visits 
  JOIN  incoming_referers	 
  ON	user_visits.user_id = incoming_referers.id
  WHERE user_visits.visited_at IS NOT NULL
  GROUP BY EXTRACT(MONTH FROM user_visits.visited_at), incoming_referers.incoming_domain_id
)
SELECT
  month,
  incoming_domain_id,
  visit_count,
  prev_month_visit_count,
  visit_count - COALESCE(prev_month_visit_count, 0) AS change
FROM traffic_sources
ORDER BY month, incoming_domain_id

저녁 식사 후 돌아와서 쿼리를 업데이트했습니다. 한번 시도해 보시고, 진전이 있다면 알려주세요 :smiley:. 제 디스커스(Discourse) 서버는 새로 생성한 것이어서 쿼리할 데이터가 충분하지 않아, SQL Server에 더미 데이터를 생성한 뒤 PGSQL로 이전했습니다.

image
image1101×998 76.5 KB

image
image1686×856 65.8 KB

5

확인차 물어보는데, 이번에는 테스트를 해보셨나요? :slight_smile:

나쁜 소식을 전해드릴 것 같습니다… :frowning: date_trunc이 의도하신 대로 작동하지 않는 것 같습니다:

image
image1402×330 10.1 KB

7

이 작업을 시도하는 분들에게 도움이 될 수 있도록, Top Traffic Sources 리포트의 SQL 버전을 공유합니다:


-- [params]
-- date :start_date = 04/05/2023
-- date :end_date = 05/06/2023

WITH count_links AS (
  
SELECT COUNT(*) AS clicks,
       ind.name AS domain
FROM incoming_links il
INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at > :start_date
  AND il.created_at < :end_date
GROUP BY ind.name
ORDER BY clicks DESC
), 

count_topics AS (
  
SELECT COUNT(DISTINCT p.topic_id) AS topics,
       ind.name AS domain
FROM incoming_links il
INNER JOIN posts p ON p.deleted_at ISNULL AND p.id = il.post_id
INNER JOIN topics t ON t.deleted_at ISNULL AND t.id = p.topic_id
INNER JOIN incoming_referers ir ON ir.id = il.incoming_referer_id
INNER JOIN incoming_domains ind ON ind.id = ir.incoming_domain_id
WHERE t.archetype = 'regular'
  AND il.created_at > (CURRENT_TIMESTAMP - INTERVAL '30 DAYS')
GROUP BY ind.name
) 

SELECT cl.domain AS "Domain", 
       cl.clicks AS "Clicks", 
       ct.topics AS "Topics"
FROM count_links cl
JOIN count_topics ct ON cl.domain = ct.domain
LIMIT 10