Zenn
Open1

Looker Studio で Zenn のアクセス解析ダッシュボードを作ってみた

Google Analytics
BigQuery
#
ga4
Looker Studio
畳屋民也畳屋民也

やったこと

Zenn に Google Analytics (GA4) を連携して取得したアクセスデータをもとに、Looker Studio でお手製ダッシュボードを作った。

なぜやったか？

Zenn の管理画面上で見られる統計ダッシュボードで日別・投稿別の表示数は見られるものの、ページ別の表示数推移や流入元別の集計値など、より詳細な情報を見たかった。

一応 GA4 のコンソール上でも見られるが、インタラクティブにフィルターを切り替えるなどしてより柔軟かつ手軽にデータを見たかったため、自分で作ってしまうことにした。

やり方

1. Google Analytics に Zenn を紐づけてトラッキングする

「Zenn Google Analytics」などで調べればいくらでも情報が出てくるので割愛。

2. Google Analytics のデータを BigQuery に転送する設定を有効にする

これも調べればすぐ出てくるはず

3. 集計クエリの作成

分析軸：

  • 日付
  • 投稿名 or ページ名
  • 流入元（direct, google, 旧twitter などの単位で）

集計軸：

  • ページ表示回数
  • ユニークユーザー数（日毎）
  • 初回ユーザー数

ページに関連する情報：

  • scrap, article の区別
  • 投稿日
    • 今回は、初回にアクセスのあった日で代用。
    • 原稿プレビュー段階での表示もログが残るため、実際の投稿日とは一致しない。
WITH import_events AS (
  SELECT
    *,
    (SELECT p.value.string_value FROM e.event_params AS p WHERE p.key="page_title") AS page_title,
    (SELECT p.value.string_value FROM e.event_params AS p WHERE p.key="page_path") AS page_path,
  FROM
    `<project_id>.analytics_<GA4_ID>.events_*` AS e
),

-- page_path を整形し、ページ内リンクやクエリパラメータ付きの path をまとめた。
-- なお、トップページだけはタブ別（記事一覧、scrap 一覧、コメント一覧）に見られるようにクエリパラメータを残した。
-- ついでに、scrap か article かそれ以外（トップページなど）の区別も取得した。
normalize_page_path AS (
  SELECT
    *,
    COALESCE(REGEXP_EXTRACT(page_path, r"^.*\/tatamiya\/([a-z]+)\/*"), "others") AS page_type,
    REGEXP_EXTRACT(page_path, r"^.*(\/tatamiya\/[a-z]+\/[^#?\/]+|\/tatamiya$|\/tatamiya\?.*)") AS page_path_normalized
  FROM
    import_events
),

-- ページ名の名寄せ。標準化した page_path に対して、ページ名を一意に紐づける。
-- 記事タイトルを変えたりすると、同じ path でも別の記事名になってしまうことがあるため。
-- 標準化した page_path ごとに最も新しいページ名を取ってくることにした
page_title_table AS (
  SELECT
    page_path_normalized,
    MAX_BY(page_title, event_timestamp) AS page_title,
    MIN(event_date) AS publish_date
  FROM normalize_page_path
  GROUP BY page_path_normalized
)

-- metrics の集計
SELECT
  event_date,
  pt.page_title,
  e.page_path_normalized,
  e.page_type,
  publish_date,
  traffic_source.source AS traffic_source,
  COUNTIF(event_name="page_view") AS count_pv,
  COUNTIF(event_name="first_visit") AS count_first_visit,
  COUNT(DISTINCT user_pseudo_id) AS count_uu
FROM
  normalize_page_path AS e
JOIN page_title_table AS pt ON e.page_path_normalized=pt.page_path_normalized
GROUP BY event_date, pt.page_title, e.page_path_normalized, e.page_type, publish_date, traffic_source

↑ 上記をテーブル化し、毎日洗い替えするようスケジュールクエリを設定[1]

Looker Studio でダッシュボードの作成

こんな感じ

  • （左上）表示回数の時系列（日別）
  • （右上）ページ別、直近7日間の表示回数・UU数[2]と前の週との比較
  • （下中央）ページ別、全期間の累計表示回数・UU数[3]

加えて、流入元、ページ種類でフィルタリング

Zenn 管理画面の統計ダッシュボードと数字が一致しないものの、傾向だけ分かればよいのでまーこれで十分だ。

脚注

  1. 増分だけ更新した方が経済的かもしれないが、現状1回のクエリで20MB程度だったので、一旦許容することにした。アクセスが増えたら差分更新を検討する。 ↩︎

  2. 正確には、「日別のアクセスUU数の合計」。別日に再訪したユーザーは重複カウントする。指定期間内の UU 数を重複カウントなしで出そうとするとテーブル設計が面倒なことになるので、このようにしている。 ↩︎

  3. 同上 ↩︎