ダッシュボードレポート - サイトトラフィック

データとレポート
, ,
1

これはサイトトラフィックのダッシュボードレポートの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
「いいね！」 2