この記事はデータ活用 Advent Calendar 2018 - 16日目の記事です。
ダッシュボードに関する登壇をしたところ「自社でも地図データを活用したい」と相談いただいたのでTipsを共有します。
もくじ
はじめに
この記事のゴール
WEBメディア訪問者のIPアドレスを元に、エリア別のPV数を可視化します。
- (データが整備されていたら&リーガルチェックがOKなら&情シス部門に権限を縛られていなければ)10分で出来ます。
- (BQのストレージやクエリ料が多少掛かりますが)ほぼ無料です。
解決したい課題
- 運営中のニッチなメディアに、広告を掲載していただきたい!
- そのメディアの特徴的な情報(アクセスの曜日・時間帯・地域)を広告主にお伝えしたい!
- 「データの活用」や「レポーティングの徹底」で信頼構築して、相性の良さそうな広告を掲載していただこう!
実行方針
- BigQueryに「IPアドレスを含むアクセスログ」「ジオコーディング用の公開データ」「都道府県の公開マスタ」を連携します。
- 上記のデータをSQLでJoinして「地域別のアクセス数」を集計します。
- 集計したデータをDataStudio(名前がDataPortalに変わったらしい)で表示します。
完成イメージ
地域データを「都道府県」で持たせると以下のようになります。
地域データを「緯度」「経度」で持たせると以下のようになります。
このグラフの横に「東京都:10,000PV」みたいな数値を入れるとそれっぽくなります。
この記事では「都道府県」パターンを目標にします。 途中のSELECT句で取得項目を変えると「緯度」「経度」を実現できます。
注意点
作業手順
1: リーガルチェック
IPアドレスを利用して問題ないか、サービスの利用規約とユーザーの同意を確認しましょう。 「統計情報ならOKか」「社内に加えて他社(広告主)もOKか」といった点に注意してください。
2: データの準備(独自データ:アクセスログ)
以下のようなテーブル sample_table
をBigQueryの sample_project.sample_dataset
に連携します。
id | user_id | ip_type | ip_adress | created_at |
---|---|---|---|---|
1 | 010101 | v4 | 192.0.2.1 | 2019-01-01 10:00:00 |
2 | 010102 | v6 | 2001:db8::1 | 2019-01-01 10:01:00 |
3 | 010103 | v6 | 2001:db8::2 | 2019-01-01 10:02:00 |
ユーザーからのHTTPリクエスト(=アクセス)1つが、1行のレコード(ログ)に対応します。
プロジェクト名・データセット名・テーブル名・カラム名は、各自で読み替えてください。 手順5のSQLを変えると再現できます。
3: データの準備(公開データ:ジオコーディング用の変換テーブル)
BigQueryの公開データ fh-bigquery.geocode.geolite_city_bq_b2b.geolite_city_bq_b2b
テーブルを元に、新規テーブル geolite_city_bq_b2b_jp
を生成します。
「IPアドレスの範囲」「都道府県ID(数値)」「緯度・経度」といったデータが含まれています。
SELECT * FROM `fh-bigquery.geocode.geolite_city_bq_b2b` WHERE country = 'JP'
- 手順
- 意図
- IPアドレスと地域情報を繋ぐための関連テーブルです。
- 元データのレコード数が多いので日本地域に絞ります。
4: データの準備(公開データ:都道府県マスタ)
都道府県一覧CSVを元に新規テーブル jp_area_list
を生成します。
「都道府県の名前(漢字)」「都道府県の名前(ローマ字)」といったデータが含まれています。
- 手順
- 上記CSVをファイルとしてローカルにダウンロード→BQにアップロードします。
- 意図
5: SQLを書く
上記で用意したデータ郡を元に、DataStudioで表示できる形に集計します。 「どの都道府県で」「どれくらいのアクセスがあったか」をSQLで返します。
WITH logs AS ( SELECT IF(ip_type = 'v4', NET.IPV4_TO_INT64(NET.IP_FROM_STRING(ip_adress)), NULL) AS clientIpNum, TRUNC(IF(ip_type = 'v4', NET.IPV4_TO_INT64(NET.IP_FROM_STRING(ip_adress))/(256*256), NULL)) AS classB FROM `sample_project.sample_dataset.sample_table` ), logs_with_geo AS ( SELECT geo.region -- , geo.latitude -- , geo.longitude FROM logs LEFT JOIN `sample_project.sample_dataset.geolite_city_bq_b2b_jp` AS geo ON logs.classB = geo.classB AND logs.clientIpNum BETWEEN geo.startIpNum AND geo.endIpNum ), area AS ( SELECT DISTINCT REPLACE(REPLACE(REPLACE(string_field_0, '都', ''), '府', ''), '県', '') AS name, -- to display at datastudio map FORMAT("%02d", ROW_NUMBER() OVER(ORDER BY REPLACE(string_field_2, 'okinawa', 'z') ASC)) AS region FROM `sample_project.sample_dataset.jp_area_list` ) SELECT COUNT(*) AS count, area.name AS area FROM logs_with_geo LEFT JOIN area USING (region)
補足1:
REPLACE(REPLACE(REPLACE(string_field_0, '都', ''), '府', ''), '県', '')
- DataStudioは「都道府県の名前(漢字)」を使うと地図に表示できます。IDはダメらしい。
- 「東京都」ではなく「東京」、「京都府」ではなく「京都」、「奈良県」ではなく「奈良」です。
- ただし「北海道」は例外で「道」を含めないといけません。ややこしい。
補足2:
FORMAT("%02d", ROW_NUMBER() OVER(ORDER BY REPLACE(string_field_2, 'okinawa', 'z') ASC))
- 手順3で作った
geolite_city_bq_b2b
テーブルの「都道府県ID」(region)は独自採番です。 - 採番法則1:ローマ字読みにしたときのアルファベット順。
- 採番法則2:沖縄県のみ例外で47番目に固定。ややこしい。
- この採番ルールを再現するために「okinawa」だけ最初に「z」を付けてからソートします。
補足3:
logs_with_geo
geo.region
ではなくgeo.latitude
とgeo.longitude
をSELECTすると、緯度・経度ベースで可視化できます。
6: DataStudioでデータを読み込む
以下の手順でデータを読み込みます。
Resource
>
Manage Added Data Source
>
ADD A DATA SOURCE
>
BigQuery
>
SELECT
>
CUSTOM QUERY
>
sample_project
>
手順5のクエリを張る >
CONNECT
>
Field: area
に Type: Region
を指定する。
7: DataStudioで地図を描く
以下の手順でグラフを作成します。
INSERT
>Geo map
で地図を追加。DATA
に以下を指定。Dimension: area
Metric: AUT/ Record Count
Zoom Area: Japan
- あとはお好きにカスタマイズ。
これで冒頭の地図を再現することができます。
手順5のSQLで緯度・経度を集計したら、以下の地図を再現できます。
おわりに
このように逆ジオコーディング(IPアドレス→地図)は簡単に実現できます。 BigQueryやDataStudioといった便利なツールが台頭したおかげですね。 制約やハマりどころは多々ありますが、要件や運用を調整したほうが、ビジネス価値を出しやすいでしょう。
多くの現場において、真に解くべきイシューは、もはや技術面ではないのだと思います。 「このデータは使える状態なのか」「このデータを使って何を得られるのか」ではないでしょうか。 枯れた技術の水平思考によって企業のデータ活用を加速させ、事業開発を推進したいなぁと思う今日この頃です。
スケーラブルデータサイエンス データエンジニアのための実践Google Cloud Platform
- 作者:Valliappa Lakshmanan
- 出版社/メーカー: 翔泳社
- 発売日: 2019/06/05
- メディア: 単行本(ソフトカバー)