逆ジオコーディングな地図ダッシュボードを10分で作る

この記事はデータ活用 Advent Calendar 2018 - 16日目の記事です。

ダッシュボードに関する登壇をしたところ「自社でも地図データを活用したい」と相談いただいたのでTipsを共有します。

もくじ

はじめに

この記事のゴール

WEBメディア訪問者のIPアドレスを元に、エリア別のPV数を可視化します。

• （データが整備されていたら＆リーガルチェックがOKなら＆情シス部門に権限を縛られていなければ）10分で出来ます。
• （BQのストレージやクエリ料が多少掛かりますが）ほぼ無料です。

解決したい課題

• 運営中のニッチなメディアに、広告を掲載していただきたい！
• そのメディアの特徴的な情報（アクセスの曜日・時間帯・地域）を広告主にお伝えしたい！
• 「データの活用」や「レポーティングの徹底」で信頼構築して、相性の良さそうな広告を掲載していただこう！

実行方針

• BigQueryに「IPアドレスを含むアクセスログ」「ジオコーディング用の公開データ」「都道府県の公開マスタ」を連携します。
• 上記のデータをSQLでJoinして「地域別のアクセス数」を集計します。
• 集計したデータをDataStudio（名前がDataPortalに変わったらしい）で表示します。

完成イメージ

地域データを「都道府県」で持たせると以下のようになります。

地域データを「緯度」「経度」で持たせると以下のようになります。

このグラフの横に「東京都:10,000PV」みたいな数値を入れるとそれっぽくなります。

この記事では「都道府県」パターンを目標にします。 途中のSELECT句で取得項目を変えると「緯度」「経度」を実現できます。

注意点

• 現時点のデータではIPv4のみ対応しています。
• IPアドレスによっては住所を特定できないケースもあります。

作業手順

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' 

• 手順
  • USリージョンの場合：
    • 上記SQL実行結果をテーブルとして保存します。
  • USリージョン以外の場合：
    • 上記SQL実行結果をCSVでダウンロード→BQにアップロードします。
    • 公開データがUSリージョンなのでそのまま移せません。
• 意図
  • IPアドレスと地域情報を繋ぐための関連テーブルです。
  • 元データのレコード数が多いので日本地域に絞ります。

4: データの準備（公開データ：都道府県マスタ）

都道府県一覧CSVを元に新規テーブル jp_area_list を生成します。 「都道府県の名前（漢字）」「都道府県の名前（ローマ字）」といったデータが含まれています。

• 手順
  • 上記CSVをファイルとしてローカルにダウンロード→BQにアップロードします。
• 意図
  • 「都道府県ID」を元に「都道府県の名前」を集計するためのテーブルです。
  • 人間が見るためだけでなく、DataStudioで地図を表示するために必要です。

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