관리자/모더이터 검색 제외된 인기 검색어?

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

사이트 데이터 주간 보고서 작성 초기 단계에 있습니다. 현재 적극적으로 추적하고자 하는 몇 가지 지표들을 검토하고 있습니다. 검색어 트렌드 보고서에 관심이 있지만, 그 이력을 살펴보니 관리자/모더레이터의 검색으로 인해 데이터가 심각하게 왜곡되어 있을 가능성이 있어 보입니다.

이 포럼은 우리 회사가 제조하는 제품의 지원 지식 베이스 역할을 부분적으로 수행하고 있으며, 티켓 응답 시 관련 트러블슈팅 게시물을 검색하는 경우가 많습니다. 이 보고서에서 관리자/모더레이터의 검색을 쉽게 제외하는 방법이 있을까요?

2

안녕하세요 @Kieran_Berryman :wave:

hmmm 이걸 한번 시도해 볼까요? :thinking:

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

WITH admin_moderator_users AS (
    SELECT 
        gu.user_id
    FROM 
        group_users gu
    JOIN 
        groups g ON gu.group_id = g.id
    WHERE 
        g.name IN ('admins', 'moderators')
)

SELECT 
    lower(sl.term) AS search_term,
    count(sl.id) AS term_count,
    (COALESCE(SUM(CASE WHEN sl.search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(sl.id)::float) * 100 AS click_through_rate,
    SUM(CASE WHEN sl.search_result_type IS NULL THEN 0 ELSE 1 END) as click_count
FROM 
    search_logs sl
LEFT JOIN 
    admin_moderator_users amu ON sl.user_id = amu.user_id
WHERE 
    sl.created_at::date BETWEEN :start_date AND :end_date
    AND amu.user_id IS NULL
GROUP BY 
    lower(sl.term)
ORDER BY 
    term_count DESC

다음은 트렌딩 검색어 대시보드 리포트용 SQL입니다:

3

감사합니다! 확인해 보겠습니다.