エクセルでできる広告予算の最適化を考える その3

ここでは仮に3つの数値（Cost、Click、CV）をもとに、
DEAから広告効率を評価をする方法を説明します。

■手順
1.エクセルシートをダウンロード。
2.データを貼り付ける。
3.計算の準備をする。
4.媒体ごとの効率値を求める。
5.効率値から媒体を評価する。

の手順で最適な広告を抽出します。

　　
■手順の詳細

1.エクセルシートをダウンロード。

　エクセルのシートをここからダウンロードしてください。

2.データを貼り付ける。

　　・はりつけるところは、オレンジ色の太枠で囲ったところです。
　　・詳しい計算の仕方などは、計算しきをご参照ください。

3.計算の準備をする（ソルバーを設定する。）
　

　　・目的セルは赤字のセルの値。
　　　なお、目的セルとは最大化(or最小化or特定の値)したい対象の値です。

　　・目的値は「最大化」にチェック。
　　
　　・変数セルの変更は、薄いオレンジで塗られている箇所です。
　　　なお、変更セルは、該当の値を変化させる事によって、
　　　目的値を最大化させる変数のことです。

　　・制約条件の対象は、上から２表目の条件式と記載される行です。
　　　この条件は各媒体の条件式が1を超えてはいけないという条件です。

　　・制約のない変数を非負数にするにチェックをいれる。
　　
　　・解決方法の選択は「シンプレックス　LP」を選択。

　　・最後に「解決」を押すと計算ができます。
　　
　　※使用されるエクセルによっては、ソルバーが入っていない場合があります。

　　　その場合は、「エクセル　ソルバー　導入」などで検索をかけてみてください！

4.媒体ごとのD効率値を求める。

・ソルバーを使って一個一個の媒体の効率値を計算します。
　　　計算する対象の媒体は効率値の横の青枠で囲ったセルで変更できます。

　　・青枠のセルを変えてから「3.」の操作を行い、それを繰り返して、
　　　すべての媒体の効率値を計算します。

　　　※計算が終わったごとに上から3番目の表に数値をコピーして記録していきます。

5.効率値から媒体を評価する。

　すべての操作が終わったら、効率値で媒体を評価することが可能です。

■DEAの詳しい仕組みや、効率値の読み解き方等

　このあたりはまた後日。。。

■詳細を知りたい方。。
　本記事は、下記の本を参考に記載しています。

　さらに、詳しい計算の仕組みなどについては、228P以降に掲載されていますので、
　こちらの本をご参照ください。

 

データクレンジングの方法

分析ができるようにデータを整理整頓することを
データクレンジングと言います。
あらゆるデータを紐付けて、
分析用のデータセットを作ります。

データクレンジングは大きく３つの操作があるかと思っています。

1.結合
2.整列
3.抽出

1.結合（マージ）
　結合は、正規化されたデータを分析用にデータをまとめる作業です。
　たとえば、コンビニの売上データを分析するときに、
　２つのデータが用意されていたとします。
  ----
  　データ名:df1（店舗属性）　カラムの名前: ShopID　|　adress　｜　openDate
　  データ名:df2（店舗売上）　カラムの名前: ShopID　|　totalRevenue
  ----

 これをSQLで結合を行う場合は

 　select * from df1 innner join df2 on df1.ShopId = df2.ShopID;

 これをRで結合する場合は

 　merge(df1,df2,by="ShopID",all = T)

2.整列（ソート）
 整列は、まとめたデータを順番に並べる作業です。
 コンビニの売上データの例をとると、
 「出店日」順で並べる。
 「店舗売上」順で並べる。といった操作です。

 「出店日順で並べる。」をSQLで行う場合には

    select * from df3 order by openDate ; 

 　同じくRで行う場合には

   
merge(df1,df2,by="ShopID",all = T)

  （＊）df3はdf1とdf2をマージしたデータです。

3.抽出（フィルタリング）
 抽出は、データを意味のあるデータ群にまとめる作業です。
 同じようにコンビニの売上データの例をとると
 「店舗売上」を５億円超をAランク、５億円〜３億円をBランク、
  「出店日」を年度ごとにまとめる。といった作業です。


　ランク付けをSQLで行う場合には

  
select * from df3 where totalRevenue > 500000000;
select * from df3 where totalRevenue < 500000000 and totalRevenue >= 300000000;

同じく、Rで行う場合には

 
rankA <- (df3$totalrevenue > 500000000)
rankB <- (df3$totalrevenue <= 500000000) &(df3$totalrevenue > 300000000)


df_rankA <- df3[rankA,]
df_rankB <- df3[rankB,]

以下、補足
◯データクレンジングの操作は本当にこれだけ？
重複チェック、Nullチェックや、ナンバリングなど他にも
細かい操作はたくさんあるかと思いますが、
基本は上記の３つの組み合わせの作業なのかなと思ってます。

◯結合について
結合は、内部結合、外部結合を正しく理解してデータ操作を行い、
正しく結合しないと、存在しないはずのデータが増えたりします！
データクレンジングの作業にも落とし穴があるので、確認しながら作業をすることが大事です！

データサイエンティストとして大切なこと

タイトルは、SEO対策のため大げさにしてますが、
今後、自分が仕事をする上で大切だと思うことを書きます。

２０１０年当時の前の会社の入社間もない頃に先輩からいわれたのが、
『「クロス集計」はよく使うが「多変量解析」なんてあまりしない。
　それは、クライアントが理解できない場合があるから。』

大学院で多変量解析を学んだ自分としては、
やっとの思いで入社したマーケティング会社にも関わらず、
自分の強みを活かせないのではと
ショックを受けつつ先輩の話を聞いていていました。

ただ、諸先輩方がクロス集計だけで問題点の発見から、
解決策のヒントまでを抽出できるのを目の当たりにし、
データ分析の本質をなんとなく理解することができました。

そんなことがあった２年後には「重回帰分析を使って分析をしてほしい」
などと多変量解析を使った分析案件などが増えました。
ちょうど、ビッグデータなどといったような言葉が出始めた頃かと思います。

今度こそ自分の強みを活かせると希望を抱いても、
いざ案件に携わってみると、
実は分析のための分析をひたすらするという悔しい思いもしました。

ただ、こうした経験をしたことによって
データアナリスト（データサイエンティスト）として
大切なことがなんとなく見えたような気がしています。

■大切なこと

１.
数学がすごく苦手な人にも、
分析の仕組みと、分析から出た結果をわかりやすく説明する。

２.

分析のための分析をするなどの要望があったら
目的を整理して課題解決に役立つファインディングスを
抽出するための分析プランを提案する。

3.
売り上げを向上させる方法のヒントになる等、
直接課題解決に役立つファインディングスを提示し、
みんなを巻き込んでアクションにつなげる。

⇒結局、数字をいじった分析レポートであっても
　・ストーリー（分析の仕組みや、ファクト）
　・オチ　　　（分析結果や、ファクトからわかること）
　がの二つが噛み合ってないと理解してもらえないし、
　ファクトからわかることだけだしても、
　その後のアクションにもつながらないと価値にならなかったり
　するのかなぁと思ったりしています。

----
でも、まあ「大切なこと」といってもほぼ理想なので
現実ではできない部分も多いですね。。

ビッグデータより小さいデータを扱うメモ

エクセルでは扱えないが、
サーバで分析するにはちょっと仰々しい場合の
データ分析はどうやればいんですかね。
Pythonを初めて間もないですが、
勉強がてら触ってみました。

■やったこと
ニコニコ動画のコメントデータを国立情報学研究所からDLして、
ほんの一部のデータを単純集計できる形にまでもってきました。
＊DLした全データでやったわけではないです。

なお、ニコニコ動画のデータはここから落とせます。

■やった手順
1.コメントデータのダウンロード
↓
2.Pythonでコメントのみを抽出
↓
3.コメントをChaSenに投げて形態素情報を抽出＜Pythonからじゃなく、シェルから＞
↓
4.ChaSenから取得した形態素情報をsedとかを使ってクレンジング＜これもシェル＞
↓
5.sqliteに格納してSQLでコメントの単語を集計

■やった手順の詳細
1.コメントデータのダウンロード
　これはダウンロードをするサイトで詳しい情報がのっています。
　が、最初wgetがわからなかったのでつまづきました。。。
　wgetはmacportから一瞬でダウンロードできます。

2.Pythonでコメントのみを抽出
　ダウンロードしてきたデータは２種類あります。
　その２種類の最初のファイルだけを読み込むPythonコードです。
　なので、新しいディレクトリを作って、
　・0001.dat.gz
　・0001.tar.gz
　と下記の２つのPythonコードを同一ディレクトリに格納したら
　プログラムは動くかと思います。

input.py output.py
3.コメントをChaSenに投げて形態素情報を抽出
　そもそも、ChaSenとは、日本語の文章を名詞とか連体詞とか語彙の機能別に
　仕分けてくれるすごいツールのようです。

　例）
   -------
　熱意 ネツイ 熱意 名詞-一般
　に ニ に 助詞-格助詞-一般
　燃える モエル 燃える 動詞-自立 一段 基本形
　商社マン ショウシャマン 商社マン 名詞-一般
　------

　で、僕がやったのは、
　すごく原始的な方法です。。


4.ChaSenから取得した形態素情報をsedとかを使ってクレンジング
　３で出たデータをそのままsqliteに突っ込む方法があるかと思うのですが、
　なかなかうまく入らなかったのでクレンジングしました。
　＊ChaSenが吐き出すデータはタブ区切りっぽいのですが、
　　sqlite側を調整してもデータが入らず、
　　吐き出したデータ側を調整することにしました。

5.sqliteに格納してSQLでコメントの単語を集計
　あとはカンマ区切りを指定して、output_8.txtをsqliteに突っ込めば
　SQLで集計したり、Rで分析したりすることができるかと。
　ただ、最初の読み込みで1ファイルだけしか取得していないので、
　全データでやるには調整する必要があります。
　また、EOSを途中で消しちゃっているので文章がひとつながりになっていたり、
　コメントと動画情報が紐づいていなかったりするので、
　まだまだ整備をする必要があるかと思います。

にしても、ちょっと効率の悪いやり方っすね。
もっといいやり方ないかな。。
まともにこのまま、全データ読み込もうとするとHDDがいくらあっても足りなくなるな。。

データのクレンジング方法

色々とデータをいじっているのですが、

テキストデータを扱う場合は

awk、seqなどを使うと早く処理できるなーと思いつつ。

もっといい方法はないかなーと思っていたら、

sqliteは、どんなファイル形式であろうと、

一度sqliteに突っ込んでしまえばSQLになって、

RとかPythonとかとも連携できてしまうそうですね。

ローデータをsqliteに突っ込んでから、
クレンジングデータを作った上で、
分析するのがいいんでしょうかね。

これも試行錯誤が必要ですな。

具体的は手法の話についてはこちら。

Pocketボタンを追加しました

今日から、このブログにPocketボタンを追加しました。
せっかくなんで、bloggerにPocketボタンを追加した作業メモとしてブログにしてみます。

■追加の仕方

やり方はとっても簡単。

Pocketボタンをするには
管理画面から「テンプレート」→「テンプレートを編集」を選んで、
挿入したい位置にこの2行のコードを貼り付けるだけです。

<a class='pocket-btn' data-lang='ja' data-pocket-align='left' data-pocket-count='vertical' expr:data-save-url='data:post.url' href='https://getpocket.com/save'>Pocket</a>
<script type='text/javascript'>!function(d,i){if(!d.getElementById(i)){var j=d.createElement(&quot;script&quot;);j.id=i;j.src=&quot;https://widgets.getpocket.com/v1/j/btn.js?v=1&quot;;var w=d.getElementById(i);d.body.appendChild(j);}}(document,&quot;pocket-btn-js&quot;);</script>

※編集は自己責任でお願いいたします！
　少なくとも、いじる前にバックアップをとることは必須です。

■解説

公式ページのコードジェネレーターからタグを取得できますが、
これでは各詳細ページ別（記事ごと）のURLをクリップすることができません。。
そこで、ここの2番目のコードを参考にします。
記事によると、2行目をblogger用に調整する必要があります。
調整の方法は下記の通りです。

---
調整前　data-save-url="{Permalink}"
調整後　expr:data-save-url='data:post.url'
---

expr:　　　　　→　blogger独自の変数を使用しますよ。というおまじない。
data:post.url　→　これがblogger独自の変数で、各記事ごとのURLを表します。

twitterやfacebookのボタンも、
これらのおまじないを応用すれば、bloggerに貼り付けることが可能です。

エクセルでできる広告予算の最適化を考える その2

前回の記事から2週間近くたったのですが、
なんとか糸口が見てきました。

■解決につかう手法
いろいろと検討した結果、効率性効果DEA（データ包絡分析法）を使用しようかと思っています。
この分析手法をつかうと、複数の指標を一度に評価することが可能です。

■DEAの特徴
例えば広告効果を検証するときに、次ような指標をみたりします。


CPA　＝　Cost　/　CV　（広告出稿費用　/　成約数）
CPA　＝　CV / Cost 　（成約数　/　広告出稿費用）
CVR  ＝　CV / Click　（成約数　/　広告がクリックされた数）
※13/7/23修正


どちらも広告効率を表す指標ですが、
CPAもCVRも似ているいるようで別々の指標であるため、
一つの土俵の上で比較することができません。

ですが、DEAを使うと、
複数の指標を一つの指標に合成することができます。
比較評価を簡単にすることができるのです。


■DEAの使い方
DEAでは、入力　と　出力　に分けて、一つのシステムの効率化を考えます。
効率化とは、あるシステムの入力を最小にして、出力を最大にすることです。

今回の例では、
2つの入力と1つの出力で表現していますが、
入力と出力が沢山ある場合の複雑な場合でも、
DEAで適正に評価することができます。

このようにDEAを使うと、
小さい入力で、最大の効果を発揮している広告媒体を抽出することも
夢ではないのです！！
（実データに当てはめてみないとなんとも言えませんが。。。）

■DEAが用いられている例

プロ野球選手の評価をしている例があるようです。
http://www.orsj.or.jp/~archive/pdf/bul/Vol.47_03_137.pdf


■まとめ
Webサイトの目的に応じて、出力の部分は変わってくると思います。
DEAはこの出力と入力を柔軟に変化させられることが、
いいところであると思いました。

次回はDEAでエクセルを使って
効率的な広告媒体を抽出する方法を
実際の計算方法をまじえて説明したいと思います。


＞＞その3へ