ダッシュボードレポート - トレンド検索用語

データとレポート
, ,
1

これは、トレンド検索語のダッシュボードレポートのSQLバージョンです。

このレポートは、指定された期間内にサイトで使用された検索語に関する洞察を提供し、どの用語が最も頻繁に検索され、ユーザーが検索結果をどれだけクリックしているかを理解するのに役立ちます。

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

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

SQLクエリの説明

クエリの内訳は次のとおりです。

  • パラメータ:
    • :start_date - レポートの期間の開始日。
    • :end_date - レポートの期間の終了日。
      • 両方のdateパラメータはYYYY-MM-DD形式を受け入れます。
  • データ選択:
    • lower(term) AS search_term: 検索語を小文字に変換し、一貫したグループ化を行い、結果の列をsearch_termと名付けます。
    • count(id) AS term_count: 各用語の検索の総数をカウントし、この列をterm_countとラベル付けします。
    • (COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 100 AS click_through_rate: クリックにつながった検索の割合を決定することにより、各用語のクリック率（CTR）を計算します。search_result_typeがNULL（クリックなしを示す）の場合をゼロとして処理し、それ以外の場合は1とします。次に、結果をその用語の検索の総数で割って率を取得し、それに100を掛けてパーセンテージを取得します。
    • SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count: 各用語で検索結果がクリックされた回数を合計します。
  • フィルタリング:
    • WHERE sl.created_at::date BETWEEN :start_date AND :end_date: created_atの日付が指定された開始日と終了日の間にある検索ログエントリのみを含めるようにフィルタリングします。
  • グループ化と並べ替え:
    • GROUP BY lower(term): 結果を小文字の検索語でグループ化し、各一意の用語のカウントとクリックを集計します。
    • ORDER BY term_count DESC: 結果をterm_countで降順に並べ替え、最も頻繁に検索された用語を一番上に表示します。

結果の例

search_term term_count click_through_rate click_count
example search term 756 0 0
#category search term 545 0.3669724770642202 2
in:personal order:latest 70 0 0
test 59 5.084745762711865 3
search 57 14.03508771929824 8
… … … …
「いいね！」 5