テック

BigQueryで位置情報を利用したアクセス元の可視化

BigQueryを活用することで地理情報解析が行えるようでしたので、その方法をまとめました。

最終的にはどの地域からアクセスが多いかマップ上で可視化できます。

IPアドレスでどの地域からアクセスされているか可視化する

画像7

利用するデータ

BigQuery一般データセットで一般公開されているWikipediaの改訂履歴データを利用します。

画像1
BigQuery の一般公開データセット

このテーブルには、2010年4月までの Wikipedia の全記事に関する改訂履歴が含まれています。改訂履歴には編集者のIPアドレスが含まれています。今回はこのIPアドレスを利用します。

IPアドレスから緯度経度をもとめる

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;

このクエリを元に地図上にプロットしていきます。

地図上にプロットする

BigQuery Geo Vizを利用します。

BigQuery Geo Viz は、Google Maps API を使用して BigQuery の地理空間データを可視化するためのウェブツールで、SQLクエリを実行しインタラクティブな地図に結果を表示できます。

前提

  • Googleアカウントを持っていること
  • 上記アカウントでGCPアカウントをお持ちであること
  • GCPアカウントでプロジェクトを作成していること
手順

1. BigQuery Geo Vizにアクセスする

2. 「Authorize」ボタンをクリックし、ご自身のGoogleアカウントで認証する

3. 「Query」に「IPアドレスからアクセス元のアクセス数を取得するクエリ」を入力する
Project IDはご自身のGCPアカウントから作成したものをご利用ください

画像5


4. 「Geometry column」カラムに「wkt」を選択する

画像5

5. 「Style」を入力する。

# 入力する値
fillColor: ff0000
fillOpacity: 0.5
strokeOpacity: 0.5
circleRadius:
    Function: linear
    Field: count
    Domain: 1000 - 13095
    Range: 6000 - 40000

6. 完成

位置情報の活用事例

以下のように国ごとのアクセス数をランキングで出すこともできます。

タクシー会社の活用事例だと、乗車位置とピックアップした時間を組み合わせ、どの時間帯にどこにお客さんが多いかを予測すれば配車の効率化が期待できルカと思われます。

他にも以下のような事例があるのでピックアップしてます。

● レジャー施設「来場に効果があるSNSはどれか」
● 大手スーパー「店舗への集客には、どこの範囲にいる人を狙うのが効果的か」
● 旅行会社「ユーザーの嗜好に合わせたターゲティングの細分化」

マイナビニュース

まとめ

  • 地域データがあれば、それを活用した新たなマーケティング施策を展開できる
  • BigQueryを使うと低コストかつカンタンに位置情報のデータを導入できる
  • 位置情報をデモグラフィック情報としてユーザーに紐づけてデータウェアハウスに蓄積しておくことで、データの利便性向上が見込める

注意

GDPRの対象となるサービスにおいてIPアドレスは個人情報とみなされており、取得することそのものにユーザーの同意が必要になるケースがあります。ご自身のサービスの特性をよくご確認の上ご活用することをおすすめします

ビッグデータ分析
created by Rinker
¥4,180
(2024/03/19 06:38:45時点 Amazon調べ-詳細)

created by Rinker
技術評論社
¥2,398
(2024/03/19 08:25:13時点 Amazon調べ-詳細)

この記事に関連する記事