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 を含めることを忘れないでください。私の 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")
)
次の部分を理解する鍵は、pageTitles がこの一般的な形式に適合することを確認することです。
Topic title - Category - Site title
トピックがカテゴリ化されていない場合、Category は欠落しています。また、カテゴリがないユーティリティページ（「最新トピック」、「新規トピック」など）も多数あります。（「最新 [カテゴリ] トピック」はカテゴリの一部とは見なしていませんが、含めた方が良いかもしれません。）最後に、ホームページでは Site title - short site description がページタイトルとして使用されます。しかし、これらのいずれにも関心はありません。したがって、私が使用している正規表現は次のとおりです。
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 データを抽出する方法はわかりません。
