Data Explorer에서 카테고리 하위 모든 토픽의 조회수를 합산하여 카테고리 전체 조회수를 확인할 수 있습니다:
SELECT
c.id as category_id,
SUM(views) as "total views"
FROM categories c
JOIN topics t ON t.category_id = c.id
WHERE read_restricted is false
GROUP BY c.id
order by sum(views) desc
태그별 전체 조회수를 가져오는 방법도 비슷합니다:
SELECT tags.name,
sum(views)
from topics t
join topic_tags tt on t.id = topic_id
join tags on tags.id = tt.tag_id
group by tags.name
order by sum(views) desc
이론적으로 사용자와 날짜별로 조회수를 나눌 수 있는 topic_views 테이블도 있습니다. 하지만 토픽의 조회수가 많으면 쿼리가 타임아웃이 발생하여 이 테이블은 특별히 유용하지 않다는 것을 발견했습니다. 또한, 제 이해가 맞다면 익명 조회수를 집계하지 않기 때문에 토픽당 조회수가 훨씬 적게 표시됩니다. topic.views에는 익명 조회수뿐만 아니라 봇 트래픽도 포함된 것 같습니다. GA에서 보는 수치보다 훨씬 많습니다.
GA에 대해 말하자면, 필요한 모든 데이터가 있지만 카테고리나 태그별로 그룹화하기가 쉽지 않습니다. 제가 할 수 있는 최선은 pageTitle을 파싱하여 카테고리를 찾는 것입니다. 저는 R을 사용하여 googleAnalyticsR 패키지로 이를 수행하고 있습니다. 매뉴얼의 지침에 따라 Google 계정을 인증하고 원하는 지표를 가져오세요. pageTitle을 차원(dimension)으로 포함하는 것을 잊지 마세요. 제 API 호출은 다음과 유사합니다:
ga_this_year <- ga_data(ga_id,
date_range = c("2023-01-01", "2023-05-30" ),
metrics=c("screenPageViews","averageSessionDuration", "sessions"),
dimensions = c("pageTitle", "deviceCategory")
)
다음 부분을 이해하는 핵심은 pageTitle이 다음과 같은 일반적인 형식을 따른다는 것입니다:
토픽 제목 - 카테고리 - 사이트 제목
토픽에 카테고리가 없으면 Category 부분이 누락됩니다. 또한 카테고리가 없는 유틸리티 페이지(“Latest topics”, “New topics” 등)도 여러 개 있습니다. ("Latest [category] topics"는 카테고리 일부로 집계하지 않지만, 포함하는 것이 더 나을 수도 있습니다.) 마지막으로 홈페이지는 페이지 제목으로 사이트 제목 - 짧은 사이트 설명을 사용합니다. 하지만 우리는 이들에 대해 신경 쓸 필요가 없습니다. 따라서 제가 사용하는 정규식은 다음과 같습니다:
str_extract(pageTitle,
str_glue(".*? - (.*) - {sitename}"),
group=1)
카테고리로 그룹화하는 함수로 이를 조합하면 다음과 같은 결과가 나옵니다:
category_views <- function(data, sitename = "Meta Jon") {
data %>%
mutate(category = str_extract(
pageTitle,
str_glue(".*? - (.*) - {sitename}"),
group=1
)) %>%
group_by(category) %>%
summarise(views = sum(screenPageViews)) %>%
arrange(desc(views)) #%>% head(20)
}
category_views(ga_this_year)
(물론 "Meta Jon"은 자신의 사이트 이름으로 변경해야 합니다.)
다만, 현재 태그를 기준으로 GA 데이터를 추출하는 방법은 모릅니다.
