注目の投稿

【kepler.gl】コロナ対策による人流の変化も地図上に可視化(各種メディアで報道)

kepler.glのサイト画面 kepler.glを使ってコロナ対策の効果を分析したところ、テレビ、新聞、ネットのメディアから問い合わせや報道依頼が殺到。今も、土日返上で都内や全国の人流変化を分析しています。この記事では人流変化の可視化に便利なkepler.glにつ...

2023年2月20日月曜日

【BigQuery 】緯度経度から50mメッシュコードを算出する方法

ぐぐっても出なかったので自分でSQL書きました。

ただ、未検証なのでご了承のほどよろしくお願いいたします。

BigQuery

WITH
  t0 AS (
  SELECT
    139.6917337 AS longitude,
    35.6895014 AS latitude ),
  t1 AS (
  SELECT
    latitude,
    longitude,
    /* FLOOR X 以下で最大の整数値を返します。*/ 
    /*round X のみが存在する場合、X を最も近い整数に丸めます。N が存在する場合、X を小数点以下の N 桁に丸めます。*/ 
    /*MOD(X, Y) Y による X の除算の剰余を返します。*/ 
    /* 1次メッシュ 辺の長さ:約80km 経度差:1度 緯度差:40分*/ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(latitude*60/40) AS INT64) AS STRING), SAFE_CAST(SAFE_CAST(FLOOR(longitude)-100 AS INT64) AS STRING) ) AS mesh1d,
    /* 2次メッシュ 辺の長さ:約10km 経度差:7分30秒 緯度差:5分*/ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC), 40.0)/5) AS INT64) AS STRING), SAFE_CAST(SAFE_CAST(FLOOR(MOD(SAFE_CAST(longitude-100 AS NUMERIC), 1.0)*60/7.5) AS INT64) AS STRING) ) AS mesh2d,
    /*3次メッシュ 辺の長さ:約1km 経度差:45秒 緯度差:30秒 */ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0)* 60/30) AS INT64) AS STRING), SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(SAFE_CAST(longitude AS NUMERIC),1.0)*60,7.5) * 60/45) AS INT64) AS STRING) ) AS mesh3d,
    /* 2分の1地域メッシュ 辺の長さ:約500m 経度差:22.5秒 緯度差:15秒 三次メッシュを緯線方向、経線方向に2等分してできる区域*/ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0),0.5)*4)*2+ FLOOR(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1.0)*60,7.5),0.75)* 60/22.5)+1 AS INT64) AS STRING) ) AS mesh4d,
    /* 4分の1地域メッシュ 辺の長さ:約250m 経度差:11.25秒 緯度差:7.5秒 */ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5),0.5),0.25)*8)*2+ FLOOR(MOD(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1)*60,7.5),0.75),0.375)*60/11.25)+1 AS INT64) AS STRING) ) AS mesh5d,
    /* 8分の1地域メッシュ 辺の長さ:約125m 経度差:5.625秒 緯度差:3.75秒 */
     CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0),0.5),0.25),0.125)*16)*2+ FLOOR(MOD(MOD(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1)*60,7.5),0.75),0.375),0.1875)* 60/5.625)+1 AS INT64) AS STRING) ) AS mesh6d,
    /* 10分の1地域メッシュ 辺の長さ:約100m 経度差:4.5秒 緯度差:3秒 三次メッシュを緯線方向、経線方向に10等分してできる区域*/ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0),0.1)*20)*2+ FLOOR(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1.0)*60,7.5),0.75)* 60/4.5)+1 AS INT64) AS STRING) ) AS mesh7d,
    /* 20分の1地域メッシュ 辺の長さ:約50m 経度差:2.25秒 緯度差:1.5秒 1/10メッシュを緯線方向、経線方向に2等分してできる区域*/ 
    CONCAT( SAFE_CAST(SAFE_CAST(FLOOR(MOD(MOD(MOD(MOD(SAFE_CAST(ROUND(latitude*60, 7) AS NUMERIC),40.0),5.0),0.1),0.05)*40)*2+ FLOOR(MOD(MOD(MOD(MOD(SAFE_CAST((longitude-100) AS NUMERIC),1.0)*60,7.5),0.75),0.075)* 60/2.25)+1 AS INT64) AS STRING) ) AS mesh8d
  FROM
    t0 )
SELECT
  latitude,
  longitude,
  CONCAT(mesh1d,mesh2d,mesh3d,mesh4d,mesh5d,mesh6d,mesh7d,mesh8d) AS mesh50m
FROM
  t1


参考


2020年7月29日水曜日

ECサイトの次の一手

ある程度成長したECサイトの次の一手とは?


1.WEBメディアを買収する

それはWEBメディアを買収することです。しかし、WEBメディアなら何でも良いかと言うとそうではありません。自社商品を購入する前段階に訪問するようなメディアである必要があります。

2.データ連携で潜在ユーザや新商品開発の種を発見

例えば、スキンケア商品を販売している場合、肌の悩みに関するブログやサイトが対象となります。こうしたブログやサイトの訪問データとECサイトの購入データを連携させることで、潜在ユーザや新商品開発の種を発見することが可能になります。このような潜在ユーザ獲得や新商品の開発は将来のさらなる成長を実現するための大きな基礎になります。

3.データを独占して誰よりも先に潜在ユーザにアプローチ

また、単純にデータを連携するだけでなく、買収をお勧めするのも理由があります。それは、競合が同様のデータを取得した際に、競争優位性が相対的に低下するためです。もし、データを自社で囲い込みできなければ、競合に先を越されて潜在ユーザを特定・確保されてしまう可能性があります。一方、買収により自社でメディアを保有すれば、データを独占できるだけでなく、面の確保や記事の投稿も自由にできます。

4.データ連携ビジネスでビジネスをスケールさせる

さらに、自社が販売する商品に関係ない記事については、そのデータを他社に販売することでデータ連携ビジネスを新たに展開することもできます。ちなみに、広告は面に限りがあるのでスケールさせるのが難しいですが、データの連携には限度がないため販売先があるほどビジネスをスケールさせることができます。もし、WEBサイトで広告収入だけに頼っているものがあれば、データ連携ビジネスにより数倍に売上を伸ばすことも難しくないと考えられます。現状ではデータを積極的に販売するWEBメディアは多くないですが、今後は間違いなく増えると予想されます。そうなるとメディアの売り上げも上がり評価価値も上がってしまうので、この時期に買収することをお勧めします。

5.今後は技術だけでなく法令を特に注視する

最後に、メディアの訪問データとECサイトの購買データの連携については、改正個人情報保護法により扱いが厳しくなる見通しです。例えば、訪問データと購買データを連携する場合、ECサイトが保有する個人情報と紐づくので、訪問データを取得する際にはユーザに利用目的を明示した上で許諾が必要となります。ただ、これはあくまで法令をディフェンシブに解釈した場合なので、正確にはガイダンスを待つ必要があります。現在、データ連携の主なプレイヤーであるDMP各社が保有するほとんどのデータは、ユーザに対して利用目的を明示して許諾を得たものではありません。そのため、よりディフェンシブな解釈でガイダンスが発表された場合、DMP各社のデータが使えなくなる可能性もあります。そのため、データ連携を進める際は、各データの取得とその利用が法令に反していないか注視することが必要です。



2020年6月12日金曜日

【BigQuery】int型の数字を日付に変換する

グーグルのサイトを参考
標準 SQL のタイムスタンプ関数のページを参考

ーー以下引用部分ーー

TIMESTAMP_SECONDS
1970-01-01 00:00:00 UTC からの秒数

TIMESTAMP_MILLIS
1970-01-01 00:00:00 UTC からのミリ秒数

TIMESTAMP_MICROS
1970-01-01 00:00:00 UTC からのマイクロ秒数


2020年5月21日木曜日

【BigQuery】時間と分を抜き出す

タイムスタンプから時間として扱るかたちで時間と分のみを抜き出す方法

--%R=%H:%M 形式の時刻
    FORMAT_TIMESTAMP("%R",TIMESTAMP)

出力結果は、「02:30」というような形で出てくる。
もちろん、時間として扱えるので、

 FORMAT_TIMESTAMP("%R",TIMESTAMP) > ''12:30"

とすれば、12時30分以降のデータを抽出できる。

◇参考サイト





2020年5月6日水曜日

【トレジャーデータ】JSタグでサイト訪問・購入ログを取得

JSタグを使ってWEBサイトの訪問・購入ログなどを取得する手順


  1. WEBサイト用のJS SDKを準備する
  2. JS SDKを実装する
    • 参考サイト:Treasure Data JavaScript SDKの使い方
    • 補足:データはJSを仕込んだページのみ取得するため、サイト全体のデータを取得したい場合は全ページにJSを仕込む必要がある。また、購入ページにJSを仕込めば、分析の際に、そのページのURLのみ抽出すれば購入ログとみなすこともできる。
  3. SDKによって収集できたアクセスログを確認

◇下記のサイトで具体的な実装例があります。

◇参考リンク



2020年5月1日金曜日

【BigQuery】特定の文字を含むものだけを集計する

フィールド「x」の先頭の文字が「Z」である場合に「y」の合計を集計するSQL
「LIKE」はあいまい検索、「%」はワイルドカードで「Z%」はZの後ろに何があってもよいことを示す。


SELECT
  SUM(CASE
      WHEN x LIKE "Z%" THEN y
  END
    ) AS total
FROM
  data


2020年4月24日金曜日

【Google】意外と知らないグーグルスプレッドシートなどのファイルが保存されている場所を開く方法

矢印のところをクリックすることでファイルを保存している場所を開ける

グーグルスプレッドシートなどのファイルがどこに保存されているか分からない場合、上記の画像内の矢印の箇所をクリックすることで保存されているフォルダを開くことができます。

自分も最近知りました。。。