2016年9月30日金曜日

【トレジャーデータ】SQL:複数のテーブル間で重複するユーザ数の日時の推移を集計する

前回、【トレジャーデータ】WITH句を使って複雑なSQLの可読性・効率性を改善
前々回、【トレジャーデータ】JOINを使って複数のテーブルからデータ抽出する複雑なSQLを書く
では、記事別に自社サイトUU、自社サイト×他社サイト重複UU、自社サイト×購買者重複UUを集計するSQLを書いた。

今回は、下記のような結果をイメージしてSQLを書いてみる。

日にち自社サイトUU自社サイトと他社サイトの重複UU自社サイトと購買者の重複UU
2016-06-011000012020
2016-06-021200013030
2016-06-031500020015

テーブルとしては、
  • 自社サイトログ:mysite_log(time, article_name, user_id)
  • 他社サイトログ:othersite_log(time, article_name, user_id)
  • 販売ログ:order_log(time, item_id, order_user_id)
  • 商品ログ:item_info(time, item_id, brand_name)
があるとする。
ここで、販売ログは、order_user_idという独自のuser_idを持つ。そのため、
  • id照合テーブル:userid_matching(user_id, order_user_id)
をうまく用いることが必要になる。
また、今回、自社サイトと購買者の重複UUは特定のブランドのみで抽出することも条件に入れて集計している。

SQLのコードは下記の通り、


WITH mysite_with AS(
  -- 自社サイトUU
  SELECT
    td_time_format(time,
                 'yyyy-MM-dd',
                 'jst') AS days,
    user_id
  FROM
    log_db.mysite_log
  WHERE
    AND TD_TIME_RANGE(time,
                    '2016-06-01',
                    '2016-09-01',
                    'JST')
  GROUP BY
    td_time_format(time,
                 'yyyy-MM-dd',
                 'jst'),
    user_id
),
othersite_with AS(
  -- 自社サイトと他サイトの重複UU
  SELECT
    td_time_format(time,
                 'yyyy-MM-dd',
                 'jst') AS days,
    user_id
  FROM
    log_db.othersite_log
  WHERE
    TD_TIME_RANGE(time,
                '2016-06-01',
                '2016-09-01',
                'jst')
  GROUP BY
    td_time_format(time,
                 'yyyy-MM-dd',
                 'jst'),
    user_id
),
order_with AS(
  -- 自社サイトと販売データの重複uu
  SELECT
    order_log.days,
    match.user_id
  FROM (
    SELECT
      user_id,
      order_user_id
    FROM
      log_db.userid_matching--サイト側user_idと販売側order_user_idを称号するテーブル
  ) match
  JOIN (
    SELECT
      td_time_format(time,
                   'yyyy-MM-dd',
                   'jst') AS days,
      order_user_id
    FROM
      log_db.order_log
    WHERE
      TD_TIME_RANGE(time,
                  '2016-06-01',
                  '2016-09-01',
                  'jst')
      AND item_id IN(
        SELECT
          item_id
        FROM
          info_db.item_info -- ブランド名などのアイテム情報が格納されたテーブル
        WHERE
          brand_name = 'xxx' -- ブランド名を指定する場合
    )
    GROUP BY
      td_time_format(time,
                   'yyyy-MM-dd',
                   'jst'),
      order_user_id
  ) order_log
  ON (
    match.order_user_id = order_log.order_user_id
  )
) SELECT
  mysite.days,
  MAX(mysite.uu) AS mysite_uu,
  MAX(wp.wp_uu) AS wp_uu,
  MAX(ord.ord_uu) AS order_uu
FROM (
  SELECT
    days,
    COUNT(DISTINCT imid) AS uu
  FROM
    mysite_with
  GROUP BY
    days
) mysite LEFT
JOIN (
  SELECT
    mysite_with_othersite.days,
    COUNT(DISTINCT mysite_with_othersite.user_id) AS othersite_uu
  FROM (
    SELECT
      days,
      user_id
    FROM
      mysite_with
  ) mysite_with_othersite
  JOIN (
    SELECT
      days,
      user_id
    FROM
      othersite_with
  ) othersite_with_1
  ON (
    mysite_with_othersite.days = othersite_with_1.days
    AND mysite_with_othersite.user_id = othersite_with_1.user_id
  )
  GROUP BY
    mysite_with_othersite.days
) othersite
ON (
  mysite.days = othersite.days
) LEFT
JOIN (
  SELECT
    mysite_with_order.days,
    COUNT(DISTINCT mysite_with_order.user_id) AS ord_uu
  FROM (
    SELECT
      days,
      user_id
    FROM
      mysite_with
  ) mysite_with_order
  JOIN (
    SELECT
      days,
      user_id
    FROM
      order_with
  ) order_with_order
  ON (
    mysite_with_order.days = order_with_order.days
    AND mysite_with_order.imid = order_with_order.imid
  )
  GROUP BY
    mysite_with_order.days
) order
ON (
  mysite.days = order.days
)
GROUP BY
  mysite.days
ORDER BY
  mysite.days


以前、WITH句を使わないで複数のテーブルをJOINさせた無駄に複雑なSQLを書いていたのだが、もし、今回もWITH句を使わずに書いていたらものすごく可読性の悪いSQLになっていただろう。


2016年9月29日木曜日

【トレジャーデータ】任意の期間のデータを消す

トレジャーデータは、例えば、「user_idカラムにxxxが含むレコードを削除」ということが仕様上できない。では、データを削除したいときはどうするのか?できないのか?

もちろん、可能である。データ作成日(time)を指定して、任意の期間のレコード削除することができる。ただし、削除の最小単位は1時間。

具体的な方法は以下の通り

1.まずはTreasure Dataにログイン

$ td -e https://api.treasuredata.com account -f

でログインする。
Emailとパスワードを入力すればOK。

2.partial_deleteを使って削除

$ td table:partial_delete example_db table1 --from '2016-01-01 JST' --to '2016-01-02 JST'

以上

  • 参照サイト
TreasureData上の過去データを削除する


2016年9月28日水曜日

【マーケティング】TVに取り上げられてもバズらなかった事例

今日はソーシャルメディアマーケティングの勉強会に参加。その中で、興味深かったのが「バズらせる」ためのプロセス。一例としては、SNS等で公開された記事等がTVで取り上げられることによって、さらに世の中に広まるというもの。そして、それをただほって置くのではなく、それを活かすアクション・フォローをすることでより効果的になるということだ。これについて、実体験から「たしかに」と思った。

以前、「【格闘】カラスとツバメ  [FIGHTING]Crow and Swallows」という動画をyoutubeに投稿したことがあるのだが、それが偶然TV局の目に留まり、2012年11月29日のスーパーニュース(フジテレビ)で取り上げられ全国に放送された。しかし、視聴数は伸びることはなかった。一つの原因としては、全くその後のフォローをしなかったこと。もし、取り上げられたことを活かした次のアクションをしていれば、あるいは「バズる」こともあったかもしれない。残念。。


【格闘】カラスとツバメ  [FIGHTING]Crow and Swallows



  •  2012年11月29日のスーパーニュース(フジテレビ)で放送

【Cytoscape】ネットワークを可視化する最強ツール

Cytoscape を使ってネットワークを可視化してみた


Cytoscapeで可視化したネットワーク

なかなかキャッチーな綺麗な図が描けてる
ただ、この全体図だけでは個々の繋がりがよく把握できない
そこで、拡大してみたのが下記の図

拡大図

Cytoscapeの便利機能の一つは、このようにどんどん図を拡大していき個々のノード間の繋がりを簡単に把握できる点にある。いちいち、作図時に拡大設定などをしなくても良いので作業効率が非常に良い。

今後、積極的に使いたいツールの代表格!

2016年9月27日火曜日

【新居】遠くの夜景はきれいだけど…(分析と関係ないです)

東京に来て早2、3年くらい
一度はタワマンに住んでみたいという思いがやっと実現できた
写真にはよく写ってないが、スカリツリー、東京タワー、六本木ヒルズを一望できる
ただ、周りは車や人通りが少なく寂しい感じ


部屋からの眺望

思えば、研究員も含めれば約6年間で4回ほど転職し、
周りの環境もその都度大きく変わっていった

まさに「光陰矢の如し」で時が過ぎたように思える

博士課程を退学し坂の上の雲を目指してきたが、

はたして雲との距離は縮まっているのだろうか


2016年9月26日月曜日

【トレジャーデータ】WITH句を使って複雑なSQLの可読性・効率性を改善

前回書いた複雑なSQL
をWITH句を用いて効率性を改善!ただ、どのくらい改善したか計測してない。。。


-- WITH句を使うことで効率性改善
WITH my_with AS(
  SELECT
    article_name,
    user_id
  FROM
    データベース名.mysite_log
  WHERE
-- NOT LIKE '%Safari%'を使っていたが遅くなると聞き下記に変更
    td_browser != 'Safari'
    AND TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-07-01',
      'JST')
  GROUP BY
    article_name,
    user_id
) SELECT
  mysite.article_name,
  MAX(mysite.my_uu) AS my_uu,
  MAX(othersite.other_uu) AS other_uu,
  MAX(order.order_uu) AS order_uu
FROM (
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS my_uu
    FROM
      my_with
    GROUP BY
      article_name
  ) mysite LEFT
JOIN (
    SELECT
      article_name,
      COUNT(DISTINCT imid) AS other_uu
    FROM
      my_with
    WHERE
      user_id IN(
        SELECT
          user_id
        FROM
          データベース名.log_web_wp
        WHERE
          TD_TIME_RANGE(time,
            '2016-06-01',
            '2016-07-01',
            'jst')
      )
    GROUP BY
      article_name
  ) othersite
  ON (
    mysite.td_title = othersite.td_title
  ) LEFT
JOIN (
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS order_uu
    FROM
      my_with
    WHERE
      user_id IN(
        SELECT
          user_id
        FROM (
            SELECT
              user_id,
              order_id
            FROM
              データベース名.userid_orderid_matching
          ) matching
        JOIN (
            SELECT
              order_id
            FROM
              データベース名.order_log
            WHERE
              TD_TIME_RANGE(time,
                '2016-06-01',
                '2016-07-01',
                'jst')
              )
          ) order_log
          ON (
            matching.order_id = order_log.order_id
          )
      )
    GROUP BY
      article_name
  ) order
  ON (
    mysite.article_name = order.article_name
  )
GROUP BY
  mysite.article_name
ORDER BY
  mysite_uu DESC

2016年9月23日金曜日

【比較】Treasure Data と Big Query の違い

先日、TDの人とTDとBig Queryの違いについて話す機会があったのでまとめてみました。


Big QueryTreasure Data
機械学習不可可能(Hivemallを利用)
データ構造変更drop & create table が必要Schema less のためdrop table が不要
Jobスケジュール機能
ログ欠損率

参考:BigQueryのstreaming insertでログが欠損する http://qiita.com/shibacow/items/199a17f07c525d3dbb2d

Fluentdのchunk毎にID管理を行い重複制御を実装

クエリ安定性中程度高い
他システム連携

GCP製品TDユーザ間でのデータ連携
Google Analytics Premiumからのデータフィード (3回/1日)

GCP/AWS/SFDC
DB(MySQL, PostgreSQLなど)
その他、FTP、BI、SDK、SMP、外部データ等
サポート

エンタープライズ向けサポートが不足
標準サービスに含む 
→ チャットでインタラクティブに対応
→ SQL構文/性能改善にも対応

追加費用が発生
参考:ドリコムを支えるデータ分析基盤がTD+AWSに移行した話 http://ka-nipan.hatenablog.com/entry/2015/12/07/002702

料金体系

従量課金(ストリーミングインサート量、ストレージ量、クエリ数)月額固定
参考:BigQueryで150万円溶かした人の顔 http://qiita.com/itkr/items/745d54c781badc148bb9

スタンダード利用で月$3300
保存レコード上限:150億レコード、クエリ数等は制限無し
使い方を誤らなければ安い高いが大規模データを扱う際に料金を気にしなくても良い

機能面は、TDに軍配!
価格面は、BQに軍配!

という感じです。

自分の好みとしては、他社とのデータ連携を主要目的として利用したいので、現状TDの方が使いやすいです。
一方、GAP(Google Analytics Premium)をすでに利用しているとBQの月額料金が$500免除されるらしいので、それが事実であり、かつGAPユーザならBQ利用も検討の余地ありと思います。


【役立ちリンク】バイト変換

バイト変換は下記のサイトが便利


バイト変換 ビット、キロ、メガ、ギガ、テラ計算
http://byte.yonelabo.com/


2016年9月22日木曜日

【本棚】1年前からダンボールに入りっぱなしの本をやっと収納できた(データ分析とは関係ないです)

東京に引っ越してきてからダンボールから出すことができなかった本をやっと収納できた。ダンボール2箱分あった本をほぼすべて1つの本棚に収納できて安堵。一番上の次の棚から貨幣金融関係、経済学関係、理論・分析関係、プログラミング・その他、そして、一番下の棚に学会誌とナウシカなどを収納。まだちゃんと整理されていないが、それはおいおいやっていこう。

本棚の使用状況

本棚自体の感想だが。。。思った以上によかった!素材も軽く組み立ても簡単にできた。また、8千円くらいと安いため質感等は期待していなかったが、なかなか良い。つくりもしっかりしている。本棚は、東洋事務器工業のクールラック(大)で高さは180cmもある。「大学で使っていた本がたくさんあるけど収納場所がない!」っていう人には特にオススメ。実際、研究室で使っていた本棚にも似てるような。。。



・商品情報(広告ではないです)
amazon クールラックへのリンク



【Research】Network Approach to Inducing Coordinative Structures of Skillful Movements

Abstract

Even though coordination is the key to explaining skillful movement, as advocated by Bernstein, analyzing the coordinative structure of body parts remains yet to be fully addressed. A correlation network is useful for identifying the most influential factor in the web of correlations among factors. The correlation network is thus thought to be effective in analyzing coordinative structures because it enables us to identify the body part most influential in skillful movement.




  • Masanori Tsujino, Tsutomu Fujinami and Keisuke Nagai, Network Approach to Inducing Coordinative Structures of Skillful Movements, Journal of Advanced Computational Intelligence and Intelligent Informatics, Vol.15, No.8, 988-996, 2011.

【主成分分析】マクロミルの説明が簡潔明瞭で分かりやすい

主成分分析については、マクロミルの図が非常に分かりやすい。特に、各主成分が変数と主成分負荷量によって構成されることが簡潔明瞭に示されているところが良い。

主成分は変数と負荷量によって構成される

また、最後にある

各主成分の主成分係数に着目して各主成分の意味を解釈します。第1主成分は総合指標になることが多く、

という説明分も「確かに第1主成分は総合指標になる可能性が高いな」と考えさせられる。

参考サイト

【計量経済学】一般的なデータ分析の流れと計量経済分析の流れの違い

データ分析の流れは、

  1. あるべき姿の把握
  2. 現状とあるべき姿のギャップから問題発見
  3. データ収集
  4. データ分析
  5. 解決策立案
  6. 検証
が一般的。

では、計量経済分析の流れどうなのだろうか?

秋山(2009)によれば下記のようになるとのこと
  1. 経済モデルの提示
  2. データの収集
  3. モデルの特定化
  4. モデルの推定
  5. 仮説検定
  6. 理論を用いての検討(予測、政策の立案)
*1〜5までは各段階で不十分だと判断された場合、前段階に戻る
*1〜5まで合格した場合はモデル(経済理論)を用いて政策評価や予測に用いる

一般的なデータ分析も計量経済分析もデータ収集・分析を通して何らかの問題を解決するという基本的な流れは似ている。大きな違いは、一般的なデータ分析は現状の問題解決が目的でありモデル提示は考慮されないが、計量経済分析ではモデル提示が必須となる点だと言えそうだ。


参考文献
  • 秋山(2009)『Rによる計量経済学』,  オーム社

2016年9月15日木曜日

【トレジャーデータ】JOINを使って複数のテーブルからデータ抽出する複雑なSQLを書く

目的:自社サイトの記事別UUを抽出し、かつ、他のサイトも閲覧している重複UU及び自社サイトを閲覧して実際に商品を購入しているUUの抽出

結果のイメージ 
article_name自社サイトUU自社サイトと他社サイトの重複UU自社サイトと購買者の重複UU
山とは?1000405
川とは?100202
海とは?1011


*後に下記のコードはWITH句を使って改善した
【トレジャーデータ】WITH句を使って複雑なSQLの可読性・効率性を改善

SELECT
  mysite.article_name,
  MAX(mysite.my_uu) AS my_uu,
  MAX(othersite.other_uu) AS other_uu,
  MAX(order.order_uu) AS oder_uu
FROM (
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS my_uu
    FROM
      データベース名.mysite_log
    WHERE
-- Sfariを除外したいときに記述
      browser NOT LIKE '%Safari%'
-- 期間を指定
      AND TD_TIME_RANGE(time,
        '2016-06-01',
        '2016-07-01',
        'JST')
    GROUP BY
      article_name
  ) mysite
JOIN (
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS othersite_uu
    FROM
      データベース名.mysite_log
    WHERE
      browser NOT LIKE '%Safari%'
      AND TD_TIME_RANGE(time,
        '2016-06-01',
        '2016-07-01',
        'JST')
      AND user_id IN(
        SELECT
          user_id
        FROM
          othersite_log
        WHERE
          TD_TIME_RANGE(time,
            '2016-06-01',
            '2016-07-01',
            'jst')
      )
    GROUP BY
      article_name
  ) othersite
  ON (
    mysite.article_name = othersite.td_article
  )
JOIN
  (
-- 下記はサイト閲覧ユーザID(user_id)と商品購入者ID(order_id)が異なる場合を想定
-- そのためuser_idとorder_idが紐付けられたテーブル(userid_orderid_matching)を利用
    SELECT
      article_name,
      COUNT(DISTINCT user_id) AS order_uu
    FROM
      データベース名.mysite_log
    WHERE
      browser NOT LIKE '%Safari%'
      AND TD_TIME_RANGE(time,
        '2016-06-01',
        '2016-07-01',
        'JST')
      AND user_id IN(
        SELECT
          user_id
        FROM (
            SELECT
              user_id,
              order_id     -- 商品購入者のID
            FROM
--商品購入者IDをサイト閲覧ユーザIDの照合テーブル
              データベース名.userid_orderid_matching
          ) matching
        JOIN (
            SELECT
              order_id
            FROM
              データベース名.order_log
            WHERE
              TD_TIME_RANGE(time,
                '2016-06-01',
                '2016-07-01',
                'jst')
          ) order_log
          ON (
            matching.order_id = order_log.order_id
          )
      )
    GROUP BY
      article_name
  ) order
  ON (
    mysite.article_name = order.article_name
  )
GROUP BY
  mysite.article_name
ORDER BY
  mysite_uu DESC


 ◇補足
期間設定を下記に変更することで、
過去30日間のデータを毎日自動集計することも可能。
*Scheduleの設定は別途必要

WHERE
  TD_TIME_RANGE(time,
    TD_TIME_ADD(TD_SCHEDULED_TIME(),
      '-30d'),
    TD_SCHEDULED_TIME(),
    'JST')


◇参照サイト
 「組合せ計算」から出発する,データ分析のための「JOIN」
*トレジャーデータのアカウントがないと見れない
https://support.treasuredata.com/hc/ja/articles/215646908

SELECT構文:JOINを使ってテーブルを結合する
http://rfs.jp/sb/sql/s03/03_3.html


2016年9月6日火曜日

【R】 freadでcsvを読み込むときにnullが入っているとnull以前のデータまでしか読み込めない

例えば、

1
2
3
null
4
5
6

というsample.csvというファイルがあったとする。

もし、freadを使って読み込もうとすると、

fread("./sample.csv")

1
2
3

となり、null以下を読み込めない。

回避策は、
  1. そもそもnullを含まないデータを用意する
  2. read.csvを使う
などがある。

read.csvを使うとnullを無視してデータを読むことができる。
つまり、

1
2
3
4
5
6

というデータとして読み込むことができる。
その一方で、freadよりも読み込み速度が遅くなるという欠点もある。

 【Rで大規模データを扱う】freadを使えば26倍早くcsvファイルを読み込める
http://mototeds.blogspot.jp/2016/03/rcsv.html