初めまして、こんにちは。白木と申します。デザイナーです。
普段は開発・デザイン部の管理やマーケティング部門との橋渡しをしています。業務ではMTGの時間がもっとも多く、隙間でコード書いたりデザインしてます。
今日は弊社のマーケティング部門の作業を一部自動化したお話をします。
本稿は背景・経緯・設計・実装についてお話しますので、実装だけに興味ある方は少しスクロールして「STEP4. さぁ、実装だ！ 」あたりからお読みいただくとよいかと思います。
Webマーケティングの作業はとても手間がかかる
みなさんご存知のとおり、Webサービスの運用はWebマーケティング、とりわけリスティングなどの広告とは無縁でいられません。依然としてSEOも強力な手法ですが、短期的に数字を重ねるならやはり広告が王道です。
弊社でもGoogle、Yahooをはじめとしたさまざまな広告を使っており、これらを機動的・効果的に運用する重要性はもはや説明不要でしょう。
しかし、これら広告は運用規模が大きくなると手間が膨れ上がる傾向にあります。キャンペーンの設定、モニタリング、数値採取、レポート作成など。あっという間に事務的作業が大きな比率を占めるようになり、本来すべき将来予測、施策立案など生産性の高い仕事に時間が割けなくなります。そして徐々に全体として機動性が失われます（これに対するソリューションとして、広告システムが提供するAPIが思い浮かびますが、利用にお金がかかること、また、そもそもAPIが提供されていない広告システムも多数あることを考えると、ある程度は自力でどうにかせざるを得ません）。
弊社でもこういった状況に陥りつつあり、広告担当者のAさんとOさんが大変そうに見えたので隙間でお手伝いさせていただくことにしました。
STEP1. まずは担当者にユーザーヒアリング
さて、まずは広告担当のAさんOさんのお話しを聴きましょう。現地・現物・現認が基本ですね*1。彼らの話を聴き、何が大変かを洗い出し、システムでどう解決できるかを明るく話し合います。ポイントは、担当者が苦労している部分だけでなく、その前後関係・周辺・背景も理解することです。
具体的にはこんな感じでヒアリングするとよさげです。
- そもそも何が大変なのか？
- その作業は何のためにやってるのか？
- その作業はどれくらい時間かかってるのか？
- 他で代替できないか？
- その作業のあとそのデータをどうするのか？
- それやる前にやっていることはあるか？
- 他の方法を用いない理由は何かあるか？（具体的な方法あげながら）
- それやってる時のモチベーションはどうか？
- 本当にそれが必要なのか？
ヒアリングの隠れたポイント
隠れたポイントはモチベーションも丁寧に見ることです。もし作業に対してモチベーションや意義を感じていないならがっつり省力化します。何かしらの意義を感じているのであれば改めて本当に必要な作業か議論し、なお必要であれば、意義を満たしながら省力化するようにすればよいです。
当然ながら局所最適解に陥らないことにも留意します。
なお、ヒアリングするときは相手の気持ちに配慮を忘れずに。相手にしてみれば自分の仕事を否定される面もあるので、ムッとすることもあります。そういう時は「あなたが本質的なところに時間が割けるようにしたい」というメッセージを伝え、同じ方向を向いていることを理解してもらうことです（本当にそう思っていますし）。
作業風景を必ず見せてもらう
ヒアリング後は作業風景を見ます。話と実際の作業が異なることは十分にあります。また、言葉に現れていないタスクや無駄というも意外にあるものです。こういったことを取りこぼすと中途半端で思った以上に成果があがりません。作業に必ず同席を。言葉に現れていないポイントが必ず見つかるはずです。
STEP2. ヒアリング結果をまとめディスカッション。要件整理。
今回、ヒアリングを通してメモしたことをいくつか箇条書きにしておきます（全部ではないです）。
- 上司から不定期で予測レポート作成の指示がおりてくる。
- データの採取期間が適宜変わる。
- 各広告サービスの管理画面を回ってアカウントごとの数値を採取する。
- 取得した数値を、担当者の手元にあるファイルに転記。月次集計を行う。
- 上司からの指示がなくても定期的に週3回、この予測を行う。
- 一つの広告システムにはアカウントが複数あってログインログアウトが面倒そう。
- 予測を作るときはその根拠となるデータ対象期間を複数とるので手間がある。
また作業風景を見て気になった点はこちら。
- 数値を取得したあとに平均を毎回検索している（セルを複数選択するだけですが）。
- 過去一週間の数字を予測のたびに算出している（セルを複数選択するだけですが）。
- 集計するときは合計数値しか見ていないので、個々のキャンペーンのパフォーマンスまでは目が行っていない（別途確認する模様）。
作業時間は約2時間/週。年間にすると100時間の作業です。
さらにAさんOさんとディスカッションを行い、広告の数値を取得するところがボトルネックで、心的疲労も大きそうだったので、ここをまず自動化することで要件を固めました。
STEP3. さて実装・・の前にモック作りと仕様すり合わせ
次にモックを作ります。AさんOさんが確実に使いやすく、且つ汎用性を失わない形にすることが大事ですね。汎用性を持ち、誰でも理解・利用可能ならば、多くの人に利用してもらえますし他の人が同じ轍を踏まずにすみます。
また、この段階のアウトプットは明確に。ここで認識違いがあると手戻りのリスクになります。アウトプットを固め、モックを見てもらい「本当にこれで使えるのか」をさまざまな切り口でくどいくらいヒアリングします。
今回、私から提案したのはGoogleSpreadsheetを利用したアウトプットです。イメージとしては下図のようなものです（数字は適当）*2。
これはキャンペーンごとのクリック、コスト、CV、CPAが格納されている生データを持つシートです。すべてのデータの基礎になるとても大事なシートです。
そしてもう一つは、その生データをカテゴリごとに集計したものです（下図。数字は適当）。
GoogleSpreadsheetは機能的で汎用性が高く、データと人とのインタフェースとしてはエクセルに相当するくらい馴染みやすいものです。
また、JavaScriptベースのGoogleAppsScriptによるデータの取り回しもでき、APIも兼ね備えているのでWebとの連携がしやすい。つまり利用側、実装側双方にとってメリットが大きいソリューションです。
「CSVにしてメールで送っておしまい」ということも検討できますが、受け手がメールを開いて、添付ファイル開いて、数字コピーして・・ということを考えると実効性が低いことのほうが多いです。こういうときは巨人の肩の上に乗って省力化しちゃいましょう。
STEP4. さぁ、実装だ！
前段が長くなりましたが、実装に入ります。ここからはコード記述が多くなります。 また今回はサンプルとしてYahoo!のスポンサードサーチを取り上げて説明します。
アーキテクチャ
まずはアーキテクチャを。今回は下図のようなものにしました。
Webページからの数値取得はヘッドレスブラウザのCasperJSを使い、結果をTSVに吐き出します。それをnodeからGoogleSpreadsheetに投げる。GoogleSpreadsheet側では、データを集計・加工するGoogleAppsScriptのバッチ処理が一日一回まわります。で、その処理が終わったらslackにメッセージを投げるという形です。
やや複雑ですが、利用している言語がほぼJSであり、アプリケーションエンジニアだけでなくフロントエンドエンジニアもメンテナンスできる点が良いかなと個人的には思っています。
また取得したデータはテキスト保存してgitに管理させておきます。DBレスで極めて身軽、ポータビリティが高い構成です。
環境構築
サーバー側の実行環境はnodeです。他にインストールするものは、CasperJSとそのベースとなるPhantomJS、それとGoogleSpreadsheetにデータを投げるためのnodeのライブラリ（google-spreadsheet, async）などをインストールします。
今回はDocker使いましたのでDockerfileを以下に記載しておきます。
FROM ubuntu:trusty RUN apt-get update \ && apt-get -y upgrade \ && apt-get -y install build-essential chrpath wget curl libssl-dev libxft-dev unzip python git libfreetype6 libfreetype6-dev libfontconfig1 libfontconfig1-dev # PhantomJS インストール RUN export PHANTOM_JS="phantomjs-2.1.1-linux-x86_64" \ && wget https://bitbucket.org/ariya/phantomjs/downloads/$PHANTOM_JS.tar.bz2 \ && tar xvjf $PHANTOM_JS.tar.bz2 \ && mv $PHANTOM_JS /usr/local/share \ && ln -sf /usr/local/share/$PHANTOM_JS/bin/phantomjs /usr/local/bin \ && rm $PHANTOM_JS.tar.bz2 # CasperJS インストール RUN git clone git://github.com/casperjs/casperjs.git \ && cd casperjs \ && ln -sf `pwd`/bin/casperjs /usr/local/bin/casperjs WORKDIR /home/docker/work # NodeJSのインストール RUN wget -qO- https://deb.nodesource.com/setup_4.x | sudo bash - \ && apt-get install -y nodejs \ && npm install google-spreadsheet async WORKDIR src
DockerのImageレイヤーはあまり増やしたくないので、適度に&&でつなぎ、キャッシュ依存度を高めておきます。
PhantomJSやCasperJSについてはnpmではなくソースをから展開します（npmだとインストールレベルの管理・把握が面倒なので）。npmに依存するのはGoogleSpreadsheetにデータを投げるときに使うgoogle-spreadsheetとasyncだけにします。
CasperJSによるYahoo!から数値取得
CasperJSによる数値取得は特別なことはしていません。ここでは骨子だけ平易に書いておきます。かなり省略して書いていますのでそのままでは動きません。
/* * Yahoo!のスポンサードサーチから広告数値を取得します * @args {String} 'daily' or null * * dailyを指定すると前日の数値を取得します。 * 何も指定しないで実行するとグローバル変数の start_date と end_date を対象期間として * 各日付の数値を取得します。 * * @usage `$ casperjs yahoo_ad_data_picker.js daily` * */ /* * モジュール読み込み */ var fs = require('fs'), utils = require('../lib/utils.js'); // オレオレUtil /* * グローバル変数定義 */ var login_id = 'type your account', password = 'type your password'; var start_url = 'http://business.yahoo.co.jp/', login_url = 'https://login.bizmanager.yahoo.co.jp/login' + '?url=https://promotionalads.business.yahoo.co.jp/Advertiser/Accounts', adsys_url = 'https://promotionalads.business.yahoo.co.jp/'; var account_ids = [], crumb = null, start_date = new Date('2016/01/01'), end_date = new Date('2016/01/07'); var casper = require('casper').create(); casper.echo("INFO : start.", "GREEN_BAR"); /* * 引数処理 */ var tar_dates; if(!casper.cli.has(0) || casper.cli.get(0) != 'daily') { tar_dates = utils.getDateArray(start_date, end_date); }else{ tar_dates = utils.getDateArray(); start_date = utils.getYesterday(); end_date = utils.getYesterday(); } /* * エラー時の処理定義 */ casper.on('error', function(msg, backtrace) { this.echo("Error : " + msg); this.capture('./error.png'); fs.write('./error.html', this.getHTML(), 'w'); }); /* * 一度Yahoo内のページを経由しないと、 * Loginページにてinput[type='hidden'][name='crumb']が付与されないのでstart_urlを踏む */ casper.start(start_url); /* * 出力ファイルの初期化 */ casper.then(function(){ var i; for(i = 0; i < tar_dates.length; i++ ){ fs.write('./out/' + utils.toLocaleString(tar_dates[i], '-') + '.tsv', '', 'w'); } }); /* * ログイン */ casper.thenOpen(login_url, function(){ this.fillSelectors( "form[name='login_form']", { 'input[name="user_name"]' : login_id, 'input[name="password"]' : password }, true ); } ); /* * キャンペーンのURL情報をAPIから取得 */ casper.then(function(){ // ログイン処理後にリダイレクトがあるのでwait casper.wait(5000, function(){ crumb = this.getElementAttribute("input[name='crumb']", 'value'); if(crumb === null){ this.echo('Error : crumb is null').exit(); }else{ this.echo('INFO : crumb is ' + crumb); } // ページ内に表示されている広告キャンペーンアカウントの総数を取得 var total_num = this.getElementAttribute('table.PagingArea th div:nth-child(2)', 'data-total'), api_url = adsys_url + 'Advertiser/Ajax/Accounts/AccountListApi.php', url_params = { '_' : null, 'crumb' : crumb , 'isBundleAdvertiser' : 1 , 'divide' : '' , 'results' : 100 , 'sort_item' : 'accountId' , 'sort_order' : '-' , 'searchType' : 'accountName' , 'searchKey' : '' , 'page' : 1 }, var i = 1; len = Math.ceil(total_num / url_params['results']); // 全てのアカウント情報を取得するために、100件構成のアカウントリストを充足するまで呼び出す for(i = 1; i <= len; i++ ){ (function(page_num, this_){ url_params['_'] = (new Date).getTime(); url_params['page'] = page_num; var tar_url = api_url + '?' + createGetParamsStrByObj(url_params) ; this_.thenOpen(tar_url, function(){ var account_json = JSON.parse(this.getPageContent()); account_json = account_json.ResultSet.Result; var ai = 0, len = account_json.length; for(ai = 0; ai < len; ai++ ){ account_ids.push(account_json[ai]['ID']) } }); })(i, this); } }); }); /* * キャンペーンのコスト情報をAPIから取得 */ casper.each(tar_dates, function(self, tar_date){ self.then(function(){ var error_ids = []; var i = 0, l = account_ids.length; // キャンペーンアカウントの数だけ回す for(i = 0; i < l; i++){ (function(account_id, i, this_){ // 気持ちwait this_.wait(1000, function(){ var api_url = adsys_url + 'Advertiser/Ajax/Campaigns/SummaryApi.php', enc_tar_date = encodeURIComponent(utils.toLocaleString(tar_date, '/')), url_params = { '_' : (new Date).getTime() , 'accountId' : account_id , 'campaignId' : '' , 'dateRangeType' : 'CUSTOM_DATE' , 'dateRangeStartDate' : enc_tar_date , 'dateRangeEndDate' : enc_tar_date , 'crumb' : crumb , 'ItemSelect' : account_id , 'sort_item' : 'Cost' , 'sort_order' : '-' , 'results' : '25' , 'useFilter' : 0 , 'searchIdEntity' : '' , 'page' : 1 }; var tar_url = api_url + '?' + createGetParamsStrByObj(url_params); this.thenOpen(tar_url, function(){ var summary_json = JSON.parse(this.getPageContent()); summary_json = summary_json.ResultSet.Total; var output = [ account_id , summary_json['Clicks'] , summary_json['Cost'] , summary_json['UniqueConversions'] , summary_json['CostUniqueConversions'] ]; fs.write('./out/' + utils.toLocaleString(tar_date, '-') + '.tsv', output.join('\t') + "\n", 'a'); }); }); })(account_ids[i], i, this); } }); }); /* * タスク終了 */ casper.run(function() { this.echo('Done.', 'GREEN_BAR').exit(); });
で、このスクリプトを実行します。
$ casperjs yahoo_ad_data_picker.js daily
するとスクリプトと同じ階層のoutディレクトリに以下のようなファイルができます。
1528614877 1 77 0 0 8349966648 2 60 0 0 120390454 6 504 0 0 9496487401 4 187 0 0 1582964683 0 0 0 0 2466369134 2 199 0 0 738523464 0 0 0 0 1451325219 0 0 0 0 3450894908 2 149 0 0 1565386334 18 1,136 0 0 5916174850 17 1,126 2 563 9614577780 0 0 0 0 1297618051 8 550 1 550 225835569 8 452 0 0 8572431975 0 0 0 0 6279508966 0 0 0 0 1843037438 0 0 0 0 6691244345 7 535 0 0 8024417193 0 0 0 0 1453747705 1 99 0 0
左からキャンペーンID、クリック数、コスト、CV、CPAという順に並んでいます（数値は架空です）。今回はスペースでpaddingしましたが、実際はTSVです。
TSVの値をGoogleSpreadsheetへAPI経由で投げる
先ほどできたTSVをGoogleSpreadsheetに投げます。あらかじめGoogleSpreadsheetを用意したり、GoogleSpreadsheet API利用のための認証を行う必要がありますが、ここではその説明は割愛し参考リンクだけ貼っておきます。
参考
- Sheets | Google Developers
- Python3でGoogle SpreadsheetをDBのように利用する
- [GAS][スプレッドシート]Idからスプレッドシートオブジェクトを取得するには : 逆引きGoogle Apps Script
GoogleSpreadsheetにデータを投げるスクリプトは以下のようなものです。
/* * CasperJSで取得した数値をGoogleSpreadsheetに記録します * @arg1 {String} cutom or daily * @usage `$node post_to_spreadsheet.js daily` */ 'use strict'; var GoogleSpreadsheet = require('google-spreadsheet'), credentials = require('./lib/google-creds.json'), utils = require('./lib/utils.js'), // オレオレutil async = require('async'), startDate = new Date('2016/06/27'), endDate = new Date('2016/07/27'); /* * 引数チェック */ if(process.argv.length < 2) { console.log('ERROR : missing argument.'); return; } if(process.argv[2] != 'custom' && process.argv[2] != 'daily'){ console.log('ERROR : term is invalide ' + process.argv[3]); return; }else if(process.argv[2] == 'daily'){ startDate = null; endDate = null; } /* * 変数定義 */ var targetAdService = process.argv[3], doc_id = 'type your spreadsheet id', doc = new GoogleSpreadsheet(doc_id), targetDates = utils.getDateArray(startDate, endDate), rowHeader = [], tsvJson = null, dataSheetNum = 1, // データを書き込むシートのindexを入れてください sheet, colIndex; async.eachSeries(targetDates, function(targetDate, step_) { async.series([ // 変数初期化 function(step){ sheet = null; colIndex = null; rowHeader = []; var inputFile = './out/' + utils.toLocaleString(targetDate, '-') + ".tsv"; tsvJson = utils.convertToMyJson(utils.loadTSV(inputFile)); step(); }, // 認証 function(step) { doc.useServiceAccountAuth(credentials, step); }, // 書き込む対象のシートを取得 function(step) { doc.getInfo(function(err, info) { sheet = info.worksheets[dataSheetNum]; }); }, // 書き込む対象となる日付のカラムインデックスと取得 function(step) { sheet.getCells({ 'min-row' : 1, 'max-row' : 1, 'min-col' : 1, 'max-col' : sheet.colCount, 'return-empty' : true }, function(err, cells) { var i; for(i = 0; i < sheet.colCount; i++){ if(cells[i].value == utils.toLocaleString(targetDate, '/')){ colIndex = i + 1; break; } } step(); }); }, // 書き込むキャンペーンIDがあるカラムのインデックスを取得 function(step) { sheet.getCells({ 'min-row' : 2, 'max-row' : sheet.rowCount, 'min-col' : 1, 'max-col' : 1, 'return-empty' : true }, function(err, cells){ var i; for(i = 0; i < cells.length; i++ ){ rowHeader.push(cells[i].value); } step(); }); }, // キャンペーンIDの行インテックスと、日付カラムのインデックスをもとに値を埋めていきます function(step) { sheet.getCells({ 'min-row' : 2, 'max-row' : sheet.rowCount, 'min-col' : colIndex, 'max-col' : colIndex, 'return-empty' : true }, function(err, cells){ var i; for(i = 0; i < rowHeader.length; i++ ){ if(rowHeader[i] == ''){ continue; } var rowHeaderItems = rowHeader[i].split("-"); if(rowHeaderItems.length == 2){ var campaignId = rowHeaderItems[0], dataType = rowHeaderItems[1]; }else if(rowHeaderItems.length == 3){ var campaignId = rowHeaderItems[0] + '-' + rowHeaderItems[1], dataType = rowHeaderItems[2]; } cells[i].value = tsvJson[campaignId][dataType]; } try{ sheet.bulkUpdateCells(cells, step); }catch(e){ console.log(e); } }); } ], function(err){ if (err){ console.log(err); }else{ console.log("Done."); step_(); } }); });
これで先ほどサンプルでお見せしたような形（下図再掲）にデータが埋まります。完全な生データですね。貴重なデータシートです。
次はこの生データをもとに集計作業を行います。
GoogleAppsScriptで集計処理
データをGoogleSpreadsheetに渡し終わったらGoogleAppsScriptで集計処理を行います。
「nodeから投げるときに計算も済ませれば？」と思うかもしれません。しかしGoogleSpreadsheet側のAPI上限が決まっていること、GoogleSpreadsheet側で作ったほうが動作検証が早く開発効率が高いことなどを踏まえ、集計処理はGoogleSpreadsheet側（GoogleAppsScript）で行います。
コードは割愛します。先ほどの生データを用いて下図のようなアウトプットを目指して組めばよいです。ミニマム実装にすれば200行以下のコードで済むと思います。
書いたスクリプトは一日一回実行するようにしておきます（GAS側でそういう設定ができます）。
結果をslackでお知らせ
最後に集計処理が終わったらslackにお知らせを流して終了です。成功した場合はかわいいクマがシートへのURLともにがお知らせしてくれます。
失敗した場合は、私に「失敗してるぞ」というお知らせが来ます…orz
STEP5. リリースと導入支援
やっと実装が終わりました。これでAさんOさんに引き渡しです。引き渡しはGoogleSpreadsheetのURLを渡して終了です。簡単。ほぼ説明なしで理解いただけました。
その後はしばらく様子見し、一週間後に使い心地をヒアリングします。不便はないか、当初想定した通りに使えているか、もっとこうしたほうがいいのではないか、など。一度、実物ができると考えが発展的・建設的になり、前向きな議論できていいですね。
今回も、デイリーだけでなく、マンスリー、ウィークリーの集計結果があるとよいとのことで追加実装しました。また単純集計だけでなく、分析作業も一部自動化できそうだったので、これも改めて検討していこうと思います。
今後の課題
結構ざっくり作ったためまだまだ改善の余地があり、大きな課題として以下の5点を認識しています。
- ほかの広告システムへの適用（横展開）
- バックアップの仕組みの構築
- 異常検知の構築
- プログラムの例外処理
- キャンペーン追加時のオペレーション自動化
1, 2はすでに取り組みつつあり、その一方で私が一番興味がある課題は3です。
イメージとしてはmonitによるサーバー監視ライクな仕組みができたらいいな、と。CPUやメモリの数値を定点観測し、異常数値が出たらお知らせがいく仕組みですね。広告数値が直近10日のトレンドから著しくずれていたらアラートを出す、的な。ここにはうまいこと機械学習を噛ませると面白いと思っています。
まとめ
今回の取り組みはこの記事がずっと頭に残っていたことから始まります。
フェイスブックは業務を自動化して社員を「過去の仕事」から解放した http://www.dhbr.net/articles/-/3542
「うちもこんな風にしたいなー」とほど良い課題を探していたら、AさんとOさんの悩み発掘した、というのが真の経緯です。Facebook程ではありませんが、それに近いことができたかなとは思っています。
また、AさんOさんの作業が軽くなったことも嬉しかったですが、さらに嬉しかったことがありました。今回の件を管理部の方がどこかで聞いたらしく、経理周りのお仕事でも一部自動化を試みる機運が出てきたことです。ここでもまた事務作業を自動化して生産性を高めることができれば、経理の方としても会社としてもハッピーだと思います。
最後に、こういったテーマを見つけたときに一緒に楽しく議論できるメンバーに恵まれて助けられました。「それ面白いね」とか「こうしたらスマートだよね」とか「どうせだったら新しい技術入れてみよう」とか言ってくれるメンバーには非常に力をもらいました。インフラ周りについては並河 (@namikawa) さんとDockerを用いた構成について議論できたのは刺激的で楽しかったですし、CasperJSでの処理についてkitoさんと有益な議論ができました。また、メモリリークの問題でaxtstar(@axtstart)さんに助けられました。
何より「こういう風にできるかもしれないけどどう？」と話したときに「やってみましょう」と言ってくれたAさんOさんには、大きなチャンスをいただけたと思っており感謝でいっぱいです。
さ、100時間分もっと楽しいことやっていきましょう！
