이것은 상위 트래픽 소스에 대한 대시보드 리포트의 SQL 버전입니다.
이 대시보드 리포트는 지정된 날짜 범위 내에서, 그리고 선택적으로 특정 카테고리 또는 그 하위 카테고리에 속하는 포스트로 연결되는 상위 외부 소스를 분석합니다.
외부 도메인 중 어떤 것이 커뮤니티로 연결되는지를 파악함으로써 관리자는 마케팅 활동의 효과를 평가하고, 더 넓은 독자층을 유치하기 위해 콘텐츠 전략을 개선하며, 매력적인 사이트와의 협업 기회를 탐색하고, SEO를 최적화하며, 원치 않는 트래픽 소스로 인한 잠재적 위기를 관리할 수 있습니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date= 2025-01-01
-- null int :category_id
-- int :limit = 10
-- boolean :include_subcategories = true
SELECT
ind.name AS domain,
COUNT(*) AS clicks,
COUNT(DISTINCT p.topic_id) AS topics
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 categories c ON c.id = t.category_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::date BETWEEN :start_date AND :end_date
AND (:category_id IS NULL
OR t.category_id = :category_id
OR (:include_subcategories AND c.parent_category_id = :category_id))
GROUP BY ind.name
ORDER BY clicks DESC
LIMIT :limit
매개변수
- 날짜 매개변수:
- 쿼리는 리포트의 날짜 범위를 정의하는 두 개의 날짜 매개변수인
:start_date와:end_date를 받아들입니다. 두 날짜 매개변수 모두YYYY-MM-DD형식의 날짜를 지원합니다.
- 쿼리는 리포트의 날짜 범위를 정의하는 두 개의 날짜 매개변수인
- 카테고리 매개변수:
:category_id: 분석 범위를 특정 카테고리의 포스트로 좁히기 위해 해당 카테고리의 ID로 설정할 수 있는 정수 매개변수입니다. null로 설정되거나 제공되지 않으면 모든 카테고리의 포스트가 고려됩니다.:include_subcategories: 지정된:category_id의 하위 카테고리 포스트를 포함할지를 제어하는 불리언 매개변수입니다. true로 설정하면 리포트에 지정된 카테고리 및 그 하위 카테고리의 포스트로 연결되는 링크가 모두 포함됩니다. false인 경우 지정된 카테고리만 고려됩니다.
- 결과 제한:
:limit: 클릭 수를 기준으로 상위N개의 결과만 반환하도록 쿼리의 행 수를 제한하는 정수 매개변수입니다. 쿼리에 제공된 기본값은 10입니다.
SQL 쿼리 설명
- SELECT: 리포트는 세 가지 정보를 선택하도록 구성됩니다:
ind.name AS domain: 이 열은 포럼으로 사용자를 유도한 각 도메인의 이름을 표시합니다.COUNT(*) AS clicks: 이 열은 각 도메인에서 발생한 인바운드 링크 클릭의 총 수를 보여줍니다.COUNT(DISTINCT p.topic_id) AS topics: 이 열은 해당 인바운드 링크를 통해 액세스된 고유 토픽의 수를 나타냅니다.
- FROM AND JOIN: 쿼리는 여러 테이블의 데이터를 집계합니다:
incoming_links il: 인바운드 링크 데이터가 저장되는 주요 테이블입니다.posts p: 포럼 포스트와 관련된 테이블입니다.topics t: 토픽 데이터를 포함하는 테이블입니다.categories c: 카테고리에 대한 정보를 포함하는 테이블입니다.incoming_referers ir: 리퍼 링크에 대한 정보를 저장하는 테이블입니다.incoming_domains ind: 포럼으로 연결되는 링크의 출처가 되는 도메인을 분류하는 테이블입니다.INNER JOINs는 삭제되지 않은 포스트와 토픽만 고려하도록 하고, 인바운드 링크 데이터를 올바른 포스트, 토픽, 리퍼 도메인과 연결합니다.
- WHERE: 쿼리의 이 부분은 결과 집합을 좁히기 위해 필터를 적용합니다:
- 레코드를 ‘regular’ 아키타입(즉, 개인 메시지나 기타 특수 유형이 아닌 일반 포럼 스레드)에 속하는 항목으로만 제한합니다.
- 지정된 기간(
:start_date부터:end_date까지) 내에 생성된 링크만 포함하도록 날짜를 필터링합니다. - 제공된
:category_id매개변수에 기반하여 카테고리 필터링을 적용합니다.:category_id가 null이면 모든 카테고리가 고려됩니다.:include_subcategories가 true이고:category_id가 null이 아니면 지정된 카테고리의 하위 카테고리도 포함됩니다.
- GROUP BY: 쿼리는 도메인 이름(
ind.name)별로 데이터를 그룹화하여, 카운트가 각 고유한 리퍼 도메인별로 분할되도록 보장합니다. - ORDER BY and LIMIT: 최종 데이터셋은 클릭 수를 기준으로 내림차순으로 정렬되어, 가장 높은 추천 트래픽을 가진 도메인이 우선시됩니다.
LIMIT :limit절은 리포트를 간결하게 유지하고 상위 도메인에 초점을 맞추기 위해 결과의 수를 제한합니다.
예시 결과
| 도메인 | 클릭 수 | 토픽 수 |
|---|---|---|
www.examplesite1.com |
1234 | 123 |
www.examplesite2.com |
56 | 67 |
www.examplesite3.com |
40 | 95 |
| … | … |