BigQueryを活用することで地理情報解析が行えるようでしたので、その方法をまとめました。
最終的にはどの地域からアクセスが多いかマップ上で可視化できます。
![](https://tokukichi.com/wp-content/uploads/2021/05/picture_pc_c9d504ea90c21b4deb17a9f2cd5286e3.gif)
IPアドレスでどの地域からアクセスされているか可視化する
![画像7](https://assets.st-note.com/production/uploads/images/31217987/picture_pc_9d099802ee12d244626bd9a2c499c86b.png?width=800)
利用するデータ
BigQuery一般データセットで一般公開されているWikipediaの改訂履歴データを利用します。
![画像1](https://assets.st-note.com/production/uploads/images/31151030/picture_pc_2c653addb04f1ae91d7d38613c862f7b.png?width=800)
このテーブルには、2010年4月までの Wikipedia の全記事に関する改訂履歴が含まれています。改訂履歴には編集者のIPアドレスが含まれています。今回はこのIPアドレスを利用します。
IPアドレスから緯度経度をもとめる
![](https://tokukichi.com/wp-content/uploads/2021/05/picture_pc_b168d6db5a529794c7f0c4fe2f5aeb16-1024x698.jpeg)
GeoLite2から経度緯度を取得します。MAXMIND社が無償で公開しており、これもBigQueryに公開されています。
BigQuery GIS で アクセス元を可視化
BigQuery GIS を利用します。地理情報システム(GIS:Geographic Information System) を利用した位置情報を扱うことのできるBigQueryの機能で、地理空間データを分析・可視化できるようになります。BigQueryでは地理関数として提供されており、ST_GeogPointがそれに当たります
IPアドレスからアクセス元のアクセス数を取得するクエリ
クエリ
WITH wiki_ips AS (
-- wikipediaの編集者IPアドレスを取得
SELECT
NET.SAFE_IP_FROM_STRING(contributor_ip) AS ip,
LENGTH(NET.SAFE_IP_FROM_STRING(contributor_ip)) AS len
FROM
`publicdata.samples.wikipedia` -- wikipedia改訂履歴
WHERE
contributor_ip IS NOT NULL
AND LENGTH(NET.SAFE_IP_FROM_STRING(contributor_ip)) = 4
),
area as (
-- wikipediaの編集者IPアドレスから経度・緯度情報を紐付け
SELECT
country_name, -- 国
city_name, -- 都市
latitude, -- 経度
longitude -- 緯度
FROM
-- geolite2 経度緯度
`fh-bigquery.geocode.201806_geolite2_city_ipv4_locs` l
INNER JOIN
-- IP(class B)で地域紐付け
wiki_ips wiki ON l.network_bin = wiki.ip & NET.IP_NET_MASK(4, 24)
),
address AS (
SELECT
MAX(longitude) AS longitude,
MAX(latitude) AS latitude,
city_name,
COUNT(*) AS count -- アクセス数
FROM
area
GROUP BY
city_name
HAVING
MAX(country_name) = 'Japan' -- 日本に限定
)
SELECT
ST_GeogPoint(SAFE_CAST(longitude AS FLOAT64), -- 地理関数
SAFE_CAST(latitude AS FLOAT64)) AS wkt,
count
FROM
address
ORDER BY
count DESC;
![](https://tokukichi.com/wp-content/uploads/2021/05/picture_pc_4e34c7b5a1c893916e470e8c72494897.png)
このクエリを元に地図上にプロットしていきます。
地図上にプロットする
BigQuery Geo Vizを利用します。
BigQuery Geo Viz は、Google Maps API を使用して BigQuery の地理空間データを可視化するためのウェブツールで、SQLクエリを実行しインタラクティブな地図に結果を表示できます。
前提
- Googleアカウントを持っていること
- 上記アカウントでGCPアカウントをお持ちであること
- GCPアカウントでプロジェクトを作成していること
手順
1. BigQuery Geo Vizにアクセスする
2. 「Authorize」ボタンをクリックし、ご自身のGoogleアカウントで認証する
![](https://tokukichi.com/wp-content/uploads/2021/05/スクリーンショット-2021-05-12-23.36.14-1024x548.png)
3. 「Query」に「IPアドレスからアクセス元のアクセス数を取得するクエリ」を入力する
Project IDはご自身のGCPアカウントから作成したものをご利用ください
![画像5](https://assets.st-note.com/production/uploads/images/31155073/picture_pc_5afb97f75d54788b1e8d6bdb90c02afa.png?width=800)
4. 「Geometry column」カラムに「wkt」を選択する
![画像5](https://assets.st-note.com/production/uploads/images/31155384/picture_pc_d7a1527bbb0d0dd0dc64e901d28d82b8.png?width=800)
5. 「Style」を入力する。
![](https://tokukichi.com/wp-content/uploads/2021/05/スクリーンショット-2021-05-12-23.39.09-1024x532.png)
# 入力する値
fillColor: ff0000
fillOpacity: 0.5
strokeOpacity: 0.5
circleRadius:
Function: linear
Field: count
Domain: 1000 - 13095
Range: 6000 - 40000
6. 完成
位置情報の活用事例
以下のように国ごとのアクセス数をランキングで出すこともできます。
![](https://tokukichi.com/wp-content/uploads/2021/05/picture_pc_ed3140e5df52517200b19580ee52d540.png)
タクシー会社の活用事例だと、乗車位置とピックアップした時間を組み合わせ、どの時間帯にどこにお客さんが多いかを予測すれば配車の効率化が期待できルカと思われます。
他にも以下のような事例があるのでピックアップしてます。
● レジャー施設「来場に効果があるSNSはどれか」
マイナビニュース
● 大手スーパー「店舗への集客には、どこの範囲にいる人を狙うのが効果的か」
● 旅行会社「ユーザーの嗜好に合わせたターゲティングの細分化」
まとめ
- 地域データがあれば、それを活用した新たなマーケティング施策を展開できる
- BigQueryを使うと低コストかつカンタンに位置情報のデータを導入できる
- 位置情報をデモグラフィック情報としてユーザーに紐づけてデータウェアハウスに蓄積しておくことで、データの利便性向上が見込める
注意
GDPRの対象となるサービスにおいてIPアドレスは個人情報とみなされており、取得することそのものにユーザーの同意が必要になるケースがあります。ご自身のサービスの特性をよくご確認の上ご活用することをおすすめします