대시보드 보고서 - 사이트 트래픽

[SaraDev (Sara Devlaeminck)]

이것은 사이트 트래픽 대시보드 보고서의 SQL 버전입니다.

이 보고서는 날짜별로 그룹화된 사이트 트래픽 지표의 개요를 제공합니다. 페이지 뷰를 로그인한 브라우저 뷰, 익명 브라우저 뷰, 크롤러 뷰 및 기타 계산된 트래픽 유형 등 여러 범주로 구분합니다.

이 보고서에 대한 추가 세부 사항은 호스팅 플랜에서 페이지뷰 추적 이해하기를 참조하십시오.

-- [params]
-- date :start_date = 2025-01-01
-- date :end_date = 2025-02-01

WITH first_browser_pageview_date AS (
  SELECT 
    MIN(date) AS first_date
  FROM 
    application_requests
  WHERE 
    req_type IN (13, 15) -- 13: page_view_anon_browser, 15: page_view_logged_in_browser
)

SELECT 
  date,
  SUM(CASE WHEN req_type = 15 THEN count ELSE 0 END) AS page_view_logged_in_browser, -- 15: page_view_logged_in_browser
  SUM(CASE WHEN req_type = 13 THEN count ELSE 0 END) AS page_view_anon_browser, -- 13: page_view_anon_browser
  SUM(CASE WHEN req_type = 6 THEN count ELSE 0 END) AS page_view_crawler, -- 6: page_view_crawler
  SUM(
    CASE 
      WHEN req_type = 8 THEN count -- 8: page_view_anon
      WHEN req_type = 7 THEN count -- 7: page_view_logged_in
      WHEN req_type = 13 THEN -count -- 13: page_view_anon_browser
      WHEN req_type = 15 THEN -count -- 15: page_view_logged_in_browser
      ELSE 0
    END
  ) AS page_view_other
FROM 
  application_requests
WHERE 
  date >= :start_date 
  AND date <= :end_date
  AND date >= (SELECT first_date FROM first_browser_pageview_date)
GROUP BY 
  date
ORDER BY 
  date ASC

SQL 쿼리 설명

• 파라미터
  • :start_date: 보고서의 시작 날짜.
  • :end_date: 보고서의 종료 날짜.
• CTE: first_browser_pageview_date
  • 이 공통 테이블 표현식(CTE)은 브라우저 페이지 뷰(익명 또는 로그인)가 기록된 가장 이른 날짜를 식별합니다.
  • req_type을 값 13(익명 브라우저 뷰)과 15(로그인한 브라우저 뷰)로 필터링합니다.
• 주요 쿼리
  • application_requests 테이블의 데이터를 date별로 집계합니다.
  • 지정된 날짜 범위(:start_date부터 :end_date까지) 내이고 첫 번째 브라우저 페이지 뷰 날짜(first_browser_pageview_date) 이후인 행만 포함하도록 필터링합니다.
  • 다음 지표를 계산합니다:
    • page_view_logged_in_browser: 로그인한 브라우저 뷰의 총 개수(req_type = 15).
    • page_view_anon_browser: 익명 브라우저 뷰의 총 개수(req_type = 13).
    • page_view_crawler: 크롤러 뷰의 총 개수(req_type = 6).
    • page_view_other: 다음 방식으로 계산된 기타 페이지 뷰의 총 개수:
      • req_type = 8(익명 뷰) 및 req_type = 7(로그인한 뷰)의 개수를 더합니다.
      • req_type = 13(익명 브라우저 뷰) 및 req_type = 15(로그인한 브라우저 뷰)의 개수를 뺍니다.
• 결과
  • 로그인한 브라우저 뷰: 브라우저를 사용하여 로그인한 사용자에 의한 총 페이지 뷰 수.
  • 익명 브라우저 뷰: 브라우저를 사용하여 익명 사용자에 의한 총 페이지 뷰 수.
  • 크롤러 뷰: 크롤러(예: 검색 엔진 봇)에 의해 생성된 총 페이지 뷰 수.
  • 기타 페이지 뷰: 브라우저 뷰 또는 크롤러 뷰로 분류되지 않은 기타 유형의 페이지 뷰를 포함합니다.

결과 예시

날짜	로그인한 브라우저 뷰	익명 브라우저 뷰	크롤러 뷰	기타 페이지 뷰
2025-03-20	150	300	50	20
2025-03-21	200	400	60	30
2025-03-22	180	350	55	25