Web DX Suite writeWired

会員ログの活用法 準備編

準備編：試すための5つの表とサンプルデータ

どの回も、この5つの表があれば試せる。まずサンプルデータで動かしてから、自社の表に読み替える。

この連載の使い方

各回は「観点 → よくある状況 → サンプルのグラフ → 試してみる」の順で、1つの見方を紹介しています。

試す流れは次の3つです。

  1. サンプルデータをPostgreSQLに入れる（試し方）
  2. 試したい回の「集計SQL」を実行する
  3. 出た数行の結果を、その回の「AIプロンプト」と一緒にAIへ貼る。グラフと読み取りが返ってくる

AIにデータベースを読ませるのではなく、SQLで集計した結果だけを渡します。自社では、集計SQLとAIプロンプトをシステム担当者に渡し、自社の表に読み替えるを確かめてもらってください。

5つの表

どの回も、次の5つの表を前提にしています。氏名・メールアドレスなど、個人を特定できる列は持たせていません。

member（会員）

1行が会員1人を表します。

列 内容
member_id 会員ID
registered_at 登録日時
withdrawn_at 退会日時（空なら在籍中）
occupation 職種（医師・薬剤師・看護師・その他）
specialty 診療科（医師以外は空）
prefecture 都道府県

content（コンテンツ）

1行がページ1つを表します。

列 内容
content_id コンテンツID
content_type 種別（製品・記事・申込・お知らせ）
title タイトル
published_at 公開日時

access_log（アクセスログ）

1行が、1ページの閲覧1回を表します。

列 内容
accessed_at 閲覧日時
member_id 会員ID（空ならログインしていない閲覧）
cookie_id クッキーID（同じブラウザなら同じ値）
session_id セッションID（ひと続きの閲覧＝1回の訪問）
url URL（メールのリンクから来た訪問は ?cmp=配信ID 付き）
content_id コンテンツID（トップ・マイページなどは空）
referrer 参照元（訪問の最初のページだけに入る）

mail_event（メール送信と反応）

1行が、メールの送信・開封・クリックのどれか1回を表します。

列 内容
campaign_id 配信ID
member_id 会員ID
event_type sent（送信）・open（開封）・click（クリック）
event_at 日時
url クリック先のURL（clickのときだけ）

form_submission（フォーム送信）

1行が、申し込み1件を表します。

列 内容
form_id フォームID
member_id 会員ID
submitted_at 送信日時
content_id 申し込んだページのコンテンツID

言葉の決まり

  • 利用した：ログインした状態で、ページを1回以上閲覧したこと
  • 閲覧：ページが開かれた回数（ページビュー）
  • 訪問：ひと続きの閲覧（セッション）
  • 会員でない訪問者：期間中に一度もログインしていないブラウザ
  • メール経由：リンクのURLに ?cmp=配信ID が付いた訪問
  • 延べ人数：同じ会員を、配信ごとなどに重ねて数えた人数

サンプルデータ

  • 架空の医療従事者向け会員サイトです。会員1,000人、2026年4〜9月の6か月分です（第29回だけ、10月の2配信を使います）
  • 各回の見どころが出るように、偏りを入れて作っています。実際のサイトの傾向を示すものではありません
  • 行数は、member 1,000行、content 68行、access_log 32,940行、mail_event 14,445行、form_submission 814行です

ダウンロード

試し方（PostgreSQL）

手順1：zipを展開したフォルダで、試すための専用のデータベースを作り、5つの表を作ります。

createdb member_log
psql -d member_log -f member_log_ddl.sql

手順2：サンプルのCSVを取り込みます。最後に、表ごとの行数が表示されます。

psql -d member_log -f setup_sample.sql

手順3：試したい回の「集計SQL」を、そのまま流します。第1回なら6行が返り、記事のグラフと同じ数字になります。

手順4：結果を、その回の「AIプロンプト」の「（ここに集計結果を貼る）」の位置に貼って、AIに渡します。グラフが表だけで返ってきたときは、「画像で描いて」と添えてください。

AIに貼るときの注意

  • 貼るのは、「集計SQL」で出た件数や割合だけにしてください
  • 「社内用SQL」は、会員IDの一覧が出ます。社外のサービスやAIには貼らないでください
  • 社内のルールで外部のAIが使えない場合も、数行の結果なら、Excelの「グラフの挿入」で同じグラフが作れます

自社の表に読み替える

SQLは、この5つの表の名前と列の名前で書いています。自社で試すときは、システム担当者と次の点を確かめてください。

  • 会員の名簿とアクセスの記録が、同じ会員IDでつながっているか。ここがつながっていないと、ほとんどの回は試せません
  • ログインしていないときの閲覧も、同じブラウザの記録として残っているか（第6回・第23回）
  • メールの送信・開封・クリックが、会員ごとに記録されているか（第12〜15回・第26〜29回）
  • フォームの送信が、会員IDと、申し込んだページと一緒に記録されているか（第14回・第17〜20回・第24回・第26〜28回）
  • メールのリンクに、配信を見分ける印（?cmp=配信ID など）が付いているか（第16回）
  • 表の名前・列の名前・区分の値（申込、sent など）が違えば、SQLの該当箇所を置き換える
  • 集計する期間（SQLの「期間。実際には集計する期間に差し替える」の行）を、自社の期間に合わせる

会員ログの活用法 一覧へ