【R】行番号を取得する(row(df))

以下でOK

df.data$行番号を入れたいカラム名 <- row(df.data)

ちなみに、

nrow()

とすると最大行数が入る。

【トレジャーデータ】先頭のバックスペース（\b）を除去（regexp_replace）

TDに溜まっているデータから記事別UUを集計しgs(google spreadsheet)にデータをアップロードしたところ下記のエラーが出てしまった。

 An invalid XML character (Unicode: 0x8) was found in the value of attribute "inputValue" and element is "gs:cell"

 結論から言うと、記事タイトルの先頭にバックスペースが紛れ込んでたレコードが一部あり、これが原因でエラーが出ていた（原因解明にTDのサポートが大変参考になった！）。

修正部分抜粋↓

) SELECT

--  yesterday.title, --記事タイトルをそのままセレクトすると\bが紛れているのでエラー

--  ltrim(yesterday.title), --空白除去。当初は先頭文字の空白が原因かと考えたが違っていた

  regexp_replace(yesterday.title, '[\b]') as title, --これで成功

メモ

• regexp_replace(column, '[\b]') --バックスペースを除去
• regexp_like(column,'^[\b]') --バックスペースがあるレコードを特定

本件で参考になるサイト

• Hadoop利用者ならきっと知ってる、Hive/Prestoクエリ関数の挙動の違い

てか、そもそも何で記事タイトルにバックスペースが紛れ込んでいるんだろう？？

【トレジャーデータ】記事別DAU階級推移（SQL：case文を使う）

 閲覧記事のDAUの構造的変化を把握したいときは、各記事をDAU階級*に分けてその変化を見る方法があります。

*DAU階級例

階級	UU数（dau）
A	10001~
B	5001~10000
C	1001~5000
D	101~1000
E	1~100

階級分けはkmeans等を使う場合が多いですが、複数のPJやメディア等を横串で評価する場合は定性評価で統一基準を設けても良いです（と思っています）。

実際には、下記のような集計をおこない階級別の推移を見て、施策の効果や現状の課題等を把握します。

date_time	A_dau	B_dau	C_dau	D_dau	E_dau
2016-10-20	?	?	?	?	?
2016-10-21	?	?	?	?	?
2016-10-22	?	?	?	?	?
2016-10-23	?	?	?	?	?
2016-10-24	?	?	?	?	?
2016-10-25	?	?	?	?	?
2016-10-26	?	?	?	?	?

具体的なSQLの例は下記となります。

データ名：data（すでに各記事のdauを集計済）
データ構造：date_time（yyyy-MM-dd）, page_name（記事の名前）, dau
補足：集計は度数分布表やヒストグラム作成に用いるcase文を利用

SELECT
date_time,
SUM(CASE
    WHEN dau >= 10001 THEN dau END) AS A_dau,
SUM(CASE
    WHEN dau >= 5001 AND dau < 10000 THEN dau END) AS B_dau,
SUM(CASE
    WHEN dau >= 1001 AND dau < 5000 THEN dau END) AS C_dau,
SUM(CASE
    WHEN dau >= 101 AND dau < 1000 THEN dau END) AS D_dau,
SUM(CASE
    WHEN dau >= 1 AND dau < 100 THEN dau END) AS E_dau
FROM
data
WHERE
--自動集計するためのコード
TD_TIME_RANGE(TD_TIME_PARSE(date_time),
              TD_TIME_ADD(TD_SCHEDULED_TIME(),
                          '-7d'), --7日分のデータを集計
              TD_SCHEDULED_TIME(),
              'jst')
GROUP BY
date_time
ORDER BY
date_time

今回は各記事のDAUを集計していますが、
THEN dau END　→　THEN 1 else 0 END
とすることで階級別の記事数を集計できます。

【スカイツリー】遠くに見えるスカイツリー

遠くに見えるトンガリがスカイツリー。

定点観測したらおもしろそう

でも、機材揃えるの面倒そうだからやめておこう。。

【学会】人工知能学会 設立30周年記念式典

もう30年。。


--下記転載---

 （一社）人工知能学会は、このたび創立30周年を迎えます。30周年を記念しまして講演会並びに式典を開催します。会場にまだ余裕がございますので是非ご参加ください。

 開催日：2016年11月11日(金)
場所：慶応義塾大学 日吉キャンパス 協生館内 藤原洋記念ホール
http://www.ai-gakkai.or.jp/30anniversary/

参加は無料，当日参加も可能ですが，
 事前登録の〆切が *** 10/28 *** に迫っておりますので，お早めにご登録をお願いいたします．
 （それ以降はオンサイトでの参加登録受付となります）
http://www.knt.co.jp/ec/2016/JSAI/

--------------------------
■プログラム

記念講演会
9:30-11:45
・人工知能研究倫理について(仮）
・学会３０周年の２０１６年は人工知能にとってどういう年だったか　
 　　　　　　松原　仁 (公立はこだて未来大学）
 人工知能学会30周年記念式典
13:30-17:30
・会長挨拶　山田 誠二　　　　　　　　
・来賓挨拶　瀧 寛和 (和歌山大学学長)
・招待講演 中島 秀之　 (東京大学）
 　　　　　　　人工知能研究の歴史と未来：日本からの発信
・パネルセッション：「知能系学会サバイバル」
 　　　　　　認知科学会、ヒューマンインタフェース学会、
 　　　　　　日本知能情報ファジィ学会、人工知能学会　　　　　　　　　　　　
・第二回全脳アーキテクチャ・ハッカソン
　　　　　　「みんなで作る認知アーキテクチャ」概要説明と表彰式
 　　　　　　https://github.com/wbap/hackathon2016
・人狼知能コンテスト概要説明と表彰式
・30周年記念論文表彰式

【コンポ】ONKYO X-NFR7TXが届いた

ONKYOのコンポ（X-NFR7TX）が届いた！
溜め込んでいたCDをやっと聴ける。

本体の様子。ボリュームコントロール部分がプラスティックで安っぽいが、その他はシンプルな作りで高級感もあり良い感じ。

スピーカーは木の質感がとても良い。今回はスピーカー台も合わせて購入。

スピーカー台は、小型スピーカースタンド（2台1組）NX-B300。
これもしっかりした作りで良い感じ。特に土台の重量感が良い。

実際の音は。。。動画を撮ってみた

なかなか良い感じ。
CDは、ヴィヴァルディ ヴァイオリン協奏曲第4番ヘ短調 '冬' 第1楽章。

やっと、PC以外で音を聞くことができた。。

コンポぽこぽこコンポこんぽう！

【トレジャーデータ】昨日及び一昨日の各記事のuu,pvを集計して比較する

前置き。。

デイリーで記事別UUランキング（昨日分）を集計している場合、その前日（一昨日）からどれだけ増減があるかは気になるところ。そこで、下記の集計をおこなうSQLを考える。

title	昨日_uu	昨日_pv	一昨日_uu	一昨日_pv	diff_uu	diff_pv
記事A	1000	1500	1200	1500	-200	0
記事B	200	300	200	250	0	50
記事C	100	200	200	300	-100	-100

・・・

WITH yesterday AS(
  SELECT
    MAX(title) AS title,
    COUNT(1) AS pv,
    COUNT(DISTINCT user_id) AS uu,
    page_id,
    page_type
  FROM
    access_log
  WHERE
--毎日自動で集計するためにTD_SCHEDULED_TIMEを使う
    TD_TIME_RANGE(time,
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-1d'),
      TD_SCHEDULED_TIME(),
      'jst')
  GROUP BY
    page_id,
    page_type
  ORDER BY
    uu DESC
),
day_before_yesterday AS(
  SELECT
    MAX(title) AS title,
    COUNT(1) AS pv,
    COUNT(DISTINCT user_id) AS uu,
    page_id,
    page_type
  FROM
    access_log
  WHERE
    TD_TIME_RANGE(time,
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-2d'),
      TD_TIME_ADD(TD_SCHEDULED_TIME(),
        '-1d'),
      'jst')
  GROUP BY
    page_id,
    page_type
  ORDER BY
    uu DESC
) SELECT
  yesterday.title,
  yesterday.uu AS yesterday_uu,
  yesterday.pv AS yesterday_pv,
  day_before_yesterday.uu AS day_before_yesterday_uu,
  day_before_yesterday.pv AS day_before_yesterday_pv,
  yesterday.uu - day_before_yesterday.uu AS diff_uu,
  yesterday.pv - day_before_yesterday.pv AS diff_pv
FROM (yesterday) LEFT
JOIN (day_before_yesterday)
  ON (
-- 記事を一意に識別するにはpage_typeとpage_idが必要なため下記の条件を設定
    yesterday.page_type = day_before_yesterday.page_type
    AND yesterday.page_id = day_before_yesterday.page_id
  )
ORDER BY
  yesterday.uu DESC

【眺望】朝の景色が気持ちよかったので

朝起きたら良い感じの空だったので写真とってみました

夏は隅田川の花火大会が見れそう

【時間凍結おじさん】新宿で遭遇

テレビでも取り上げられていた時間凍結おじさんに遭遇！

イタリア人の大道芸人の方で名前はウルフギャングさんと言うらしい。
新宿を選んだ理由は「カジュアルな姿で急いでいるひとが多いから」とテレビで説明されていた。（自身の姿がカジュアルだからより風景に溶け込める？）まあ、丸の内でやるよりは溶け込めてるかも。

お金を入れるとちょっと動く

お金を入れる人が結構たくさんいて30秒以上止まっていることはなかった

• 参考情報

【動画】『とくダネ！』が東京に現れたイタリア人の大道芸人「時間凍結おじさん」を特集

【便利サイト】各メディアのセッション数を簡単に把握する（SimilarWeb）

SimilarWebを使うと簡単に各メディアのセッション数を把握できる。必要な情報は、ドメイン情報だけ。実際に使ってみた結果は下記となる。

*下記セッション数は手入力なので間違っている可能性あり。また、メディアタイプや運営会社も参考程度に書いているだけなので注意。



各メディアのセッション数（縦軸は対数にしています）

メディア	月次セッション数(2016-09)	メディアタイプ	運営会社
facebook.com	27,000,000,000	SNS	facebook
youtube.com	21,000,000,000	動画	google
twitter.com	3,000,000,000	SNS	twitter
instagram.com	1,700,000,000	SNS	facebook
google.co.jp	1,600,000,000	ポータル	google
yahoo.co.jp	1,400,000,000	ポータル	ヤフー
naver.jp	257,000,000	ポータル	Line
ameblo.jp	218,000,000	ブログ	サーバーエージェント
goo.ne.jp	203,000,000	ポータル	エヌ・ティ・ティレゾナント
asahi.com	43,000,000	ニュース	朝日新聞社
mixi.jp	35,000,000	SNS	mixi
yomiuri.co.jp	34,000,000	ニュース	読売新聞社
mery	30,000,000	女性向け	ペロリ
mbga.jp	28,000,000	SNS	DeNA
sankei.com	28,000,000	ニュース	産経デジタル
infoseek.co.jp	20,000,000	ポータル	楽天
sponichi.co.jp	19,000,000	ニュース	スポーツニッポン新聞社
jooy.jp	17,500,000	男性向け	DeNA
line.me	12,000,000	SNS	line
スキンケア大学	12,000,000	女性向け	リッチメディア
メリカリ	9,800,000	女性向け	メリカリ
locari	9,000,000	女性向け	Wondershake
japan.cnet.com	7,400,000	ニュース	朝日インタラクティブ
by.S	7,000,000	女性向け	サイバーエージェント
gunosy.com	6,800,000	ニュース	グノシー
cnn.co.jp	4,200,000	ニュース	朝日インタラクティブ
4meee!	2,300,000	女性向け	ロケットベンチャー
smartlog.jp	1,500,000	男性向け	Smartlog
gqjapan.jp	1,400,000	男性向け	コンデナスト・ジャパン
ciel-fashion.jp	1,200,000	女性向け	Nagisa
mens.tasclap.jp	952,000	男性向け	カカクコム
otokomaeken.com	802,000	男性向け	ビヨンデイジ
trilltrill	770,000	女性向け	TRILL
vokka.jp	770,000	男性向け	JUKKI
kyoko-np.net	685,000	虚構ニュース	個人運営
openers.jp	650,000	男性向け	OPENERS
mens-skincare-univ.com	550,000	男性向け	リッチメディア
houyhnhnm.jp	538,000	男性向け	ライノ
form.allabout.co.jp	452,000	男性向け	ALL About
goethe.nikkei.co.jp	234,000	男性向け	日本経済新聞社
mendy.jp	73,000	男性向け	MENDY
7gogo.jp	17,000	SNS	7gogo

以上のように、各メディアのセッション数が分かるので非常に便利。

一方、下記のような記事もあった

【炎上】SimilarWebの信憑性のないデータに各メディア責任者が猛抗議

確かに、正確性については議論もあるが、
他の計測ツールでも確認したところ同様のデータが取れていたので、
個人的には今後も利用させて頂く予定

【ID】imidはSafariが捕捉できず、finger printはユーザの重複が多い

＊あくまで現時点での使用感

imid：インティメートマージャーが発行しているID

サードパーティのcookie情報からユーザを特定しているため、ファーストでないとcookie取得に問題があるブラウザではIDが正しく取得できない。例えば、Mobile Safariだと、ID数=PV数となってしまう。ただ、Safariを除外すると問題なく使える。


finger print：クライアントの端末情報を元に画像を生成してユーザを特定するID

重複が多い。FPでUU数を計測したところ、GAのUU数の1/3になった。個人的には、全く使っていない。


・各IDの参考サイト
Treasure Dataに追加されたExport to Intimate mergerの機能とその使い方
Canvas Fingerprintingとは？

【セミナー報告】TUG (Treasure Data User Group) vol.3 - Biz Dev編

セミナー参加報告

セミナー名
TUG (Treasure Data User Group) vol.3 - Biz Dev編

• 目的

データ分析ビジネス創出のための情報収集及び各企業の担当者と意見交換

• 概要

紹介された事例から分かったことは「箱からプライベートDMPへ」とTDの活用用途が変わりつつあること。その背景には、外部データ・サービスとの連携による次世代のマーケティング基盤の構築、すなわち、リアルおよびインターネット空間のユーザ一人一人のデータを捕捉することでマスから個人への直接アプローチのマーケティングへシフトしたいというクライアントニーズの高まりがある。

実際、本セミナーTUGの会長を務める電通では、将来のユーザーベースマーケティングでも先駆者であり続けるために、TDの外部サービスとして利用者にIDを付与する企業であるインティメートマージャーと資本提携し、TDを基盤にしたプライベートDMPを構築・運用している。このように電通を筆頭に、TDをプライベートDMPとして選択する動きが広まっている理由としては、⑴導入費用工数の低さ、⑵データ連携の容易さ、⑶サポートの充実さが決め手になったと各企業の担当者より聞くことができた。例えば、大規模なプライベートDMPを運用している資生堂では、上記の３点が整っていることで、実質３名という極めて少ない人数で運用することが可能になっているそうだ。

このように、電通、資生堂など大手企業を筆頭にTDを基盤にしたプライベートDMPの構築が広がっている現状、及び、この広がりがさらなるデータ連携の可能性を相乗的に拡大していく構造を鑑みると、今後もTDを軸にしたプライベートDMPの構築が広がっていくと考えられる。幸い、当社はTDを基盤とするプライベートDMPを構築しつつある。そのため、近い将来、拡大すると思われる他社とのデータ連携ビジネスの波に乗る準備は進んでいると言える。本年度中には当社データ基盤の構築が完了する見込みなので、その後はデータ連携先となる企業を開拓することが課題になるだろう。

• セミナー情報

【セミナー情報】TUG (Treasure Data User Group) vol.3 - Biz Dev編

【検討中コード】

---

WITH mysite_with AS(
  SELECT
    MIN(TD_TIME_FORMAT(time,
        'yyyy-MM-dd',
        'JST')) AS date_time,
    imid
  FROM
    log_db.log_mysite
  WHERE
    TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-07-01',
      'JST')
    AND td_browser != 'Mobile Safari'
  GROUP BY
    imid
),
mysite_access_day_with AS(
  SELECT
    TD_TIME_FORMAT(time,
      'yyyy-MM-dd',
      'JST') AS access_day,
    imid
  FROM
    log_db.log_mysite
  WHERE
    TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-10-01',
      'JST')
    AND td_browser != 'Mobile Safari'
  GROUP BY
    TD_TIME_FORMAT(time,
      'yyyy-MM-dd',
      'JST'),
    imid
),
othersite_with AS(
  SELECT
    MIN(TD_TIME_FORMAT(time,
        'yyyy-MM-dd HH:mm:ss',
        'JST')) AS date_time,
    imid
  FROM
    log_db.log_othersite
  WHERE
    TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-10-01',
      'JST')
    AND td_browser != 'Mobile Safari'
    AND brand = 'brand/ie'-- ブランド名を指定
  GROUP BY
    imid
) SELECT
  date_diff(
    'day',
    CAST(
      mysite_with.date_time AS TIMESTAMP
    ),
    CAST(
      next_access.access_day AS TIMESTAMP
    )
  ) AS time_diff,
  COUNT(DISTINCT mysite_with.imid) AS uu
FROM (mysite_with)
JOIN
  (othersite_with)
  ON (
    --他社サイトのブランドサイト訪問
    mysite_with.imid = othersite_with.imid
  )
JOIN
  (
    -- アクセス初日後、最初のアクセス日を抽出
    SELECT
      MIN(TD_TIME_FORMAT(TD_TIME_PARSE(mysite_access_day_with.access_day),
          'yyyy-MM-dd',
          'jst')) AS access_day,
      mysite_access_day_with.imid
    FROM (mysite_access_day_with)
    JOIN
      (mysite_with)
      ON (
        mysite_access_day_with.access_day != mysite_with.date_time
--修正
　　AND
        mysite_access_day_with.imid = mysite_with.imid
      )
    GROUP BY
      mysite_access_day_with.imid
  )next_access
  ON (
    mysite_with.imid = next_access.imid
  )
GROUP BY
  date_diff(
    'day',
    CAST(
      mysite_with.date_time AS TIMESTAMP
    ),
    CAST(
      next_access.access_day AS TIMESTAMP
    )
  )
ORDER BY
  time_diff

---

【セミナー情報】TUG (Treasure Data User Group) vol.3 - Biz Dev編

タイトル：TUG (Treasure Data User Group) vol.3 - Biz Dev編
日時：　2016/10/05（水） 16:00～19:30
会場：　EBiS303
住所：　東京都渋谷区恵比寿1-20-8 エビススバルビル 5F
詳細：　http://eventdots.jp/event/600439

【定　員】100名
【参加費】無料
【持ち物】名刺2枚

アジェンダは下記の通り

15:30	開場
16:00	スタート
16:00〜16:10	ご挨拶 トレジャーデータユーザー会 会長 電通 データ・テクノロジーセンター テクニカルディレクター 山崎 茂樹さま
16:10〜16:35	事例紹介 1 資生堂ワタシプラス　One to Oneマーケティングへの取り組み 資生堂ジャパン ダイレクトマーケティング部 Web推進室 吉本 健二さま
16:35〜17:00	事例紹介 2 CNET Japanが考えるこれからの時代のWebメディア 朝日インタラクティブ 技術・制作部 ディレクター兼シニアマネージャー 石澤 知広さま
17:00〜17:10	休憩 10分
17:10〜17:20	ライトニングトーク1 事例紹介：オイシックスのデータ分析基盤 オイシックス システム本部 副本部長 普川 泰如さま
17:20〜17:30	ライトニングトーク2 電通 データ・テクノロジーセンター テクニカルディレクター 山崎 茂樹さま インティメートマージャー 代表取締役社長 簗島 亮次さま
17:30〜17:50	トレジャーデータからのお知らせ トレジャーデータ マーケティング担当ディレクター 堀内 健后
17:50〜18:00	アンケート
18:00	懇親会
19:30	終了

• セミナー参加報告

【セミナー報告】TUG (Treasure Data User Group) vol.3 - Biz Dev編

【晩御飯】ホットケーキでPDCA

今日の晩御飯はホットケーキ

泡が出てきた

ひっくり返すの失敗

美味しくいただきました。（ちょっと焼けてなかった気も。。）

• まとめ

あるべき姿：美味しいホットケーキを作る
問題：概ね美味しかったがちょっと生っぽいところが美味しくなかった
原因：ひっくり返すのを失敗して一部分の厚みが増しよく焼けていないところできた
仮説：小さなホットケーキを作っていればひっくり返すのを失敗することなく均等に熱が行き渡り一部が生っぽいホットケーキになることなく美味しくいただける
検証：また次回

【トレジャーデータ】自社サイトから他社サイトへ遷移する日数の分布集計（日数差分はdate_diffを用いる）

前置き。。

• 遷移日数の分布を見ればユーザの行動変容を読み取れる

自社サイトから他社サイトへ遷移するまでの期間を知ることは、自社サイトを通じたユーザの行動変容を知る上で大変有用。例えば、他社サイト→自社サイト、自社サイト→他社サイトとの遷移期間の分布がそれぞれ同様の構造を持っていれば、自社サイトを通じた行動変容は起こっていないと言える。一方、自社サイト→他社サイトの遷移で遷移期間に対するUUの減衰の変化、例えば、通常、遷移期間が長くなるほど右肩下がりに減衰するはずのUUがある特定の期間で増加するような現象が見られる場合、その特定に期間においてユーザの行動変容が起きているという仮説を立てることができる。この仮説を設定することで、ユーザの行動変容が起きるタイミングで広告配信をおこなう等の施策が設定可能となる。ただ、効果の高い施策を実施することのみを目的とする場合、基本的には、自社サイト→他社サイトへの遷移は24時間以内に起きるのが圧倒的に多いため、自社サイト訪問直後にアプローチするのが最も高い効果が期待される。

下記は、他社サイト→自社サイト、自社サイト→他社サイトへの遷移期間を集計するSQL。

 ---start---
WITH mysite_with AS(
  SELECT
    MIN(TD_TIME_FORMAT(time,
        'yyyy-MM-dd HH:mm:ss',
        'JST')) AS date_time,
    user_id
  FROM
    log_db.log_mysite
  WHERE
    TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-10-01',
      'JST')ｃ
  GROUP BY
    user_id
),
othersite_with AS(
  SELECT
    MIN(TD_TIME_FORMAT(time,
        'yyyy-MM-dd HH:mm:ss',
        'JST')) AS date_time,
    user_id
  FROM
    log_db.log_othersite
  WHERE
    TD_TIME_RANGE(time,
      '2016-06-01',
      '2016-10-01',
      'JST')
    AND brand = 'brand_name'-- ブランド名を指定
  GROUP BY
    user_id
) SELECT
  date_diff(
    'day', -- 他にも minute, hour, week, monthなども指定できる
    CAST(
      mysite_with.date_time AS TIMESTAMP
    ),
    CAST(
      othersite_with.date_time AS TIMESTAMP
    )
  ) AS time_diff,
  COUNT(DISTINCT mysite_with.user_id) AS uu
FROM (mysite_with)
JOIN
  (othersite_with)
  ON (
    mysite_with.user_id = othersite_with.user_id
  )
GROUP BY
  date_diff(
    'day',
    CAST(
      mysite_with.date_time AS TIMESTAMP
    ),
    CAST(
      othersite_with.date_time AS TIMESTAMP
    )
  )
ORDER BY
  time_diff
 ---end---


Prestoでの期間計算（date_diff）の詳細は下記参照

• Hadoop利用者ならきっと知ってる、Hive/Prestoクエリ関数の挙動の違い
• 6.10. Date and Time Functions and Operators