掲載が落ちてた！教えて slack botさん！！

はじめに

はじめまして、こんにちは。
Livesense not engineers Advent Calendar 2018、17日目を担当いたします、tomoharu33です。
去年のAdvent Calendarで「何も書けることがねえ！もう二度とやるものか！」と懲りたはずなのですが、1年も経つと気持ちは変わるものですね（先輩に圧力なんてかけられてないですよ、ほんとに）。

求人サイトにおける広告とは

普段のお仕事

私はいま、求人サイトの広告運用（主にパフォーマンス側）を行っています。
検索時に検索結果画面に出てくる広告や、ディスプレイ広告などの出稿改善を、日々行っている感じです。

求人という［商品］を宣伝する

広告は、［商品］を広報/宣伝するために利用されます。
家電ECサイトだと［商品］は カメラ や パソコン とかですね。
旅行サイトなら［商品］は 北海道旅行 や ハワイ旅行 でしょうか。

そして求人サイトの場合、主な［商品］は 求人案件 になります。
［商品］の数は多ければ多いほど、広告の出稿効率は良くなります。

他とは違う求人サイトなのです

成果報酬型です

リブセンスの求人サイトは、他の求人サイトとは違ったビジネスモデルになっています。
多くの求人サイトは、「1週間掲載したら〇〇円」のような 掲載課金型 と呼ばれるモデルですが、リブセンスの場合は、「1人採用されたら〇〇円」といった 成果報酬型 と呼ばれるモデルです。

これはつまり、「いつまで掲載するか」という掲載期間を事前に決めておらず、企業様の採用ニーズが満たされたら自由に掲載をストップできる、ということです。

こいつは大変だ！

さて、勘のよい方はこいつの面倒さに気づいたことでしょう。
今までの文章を整理してみましょう。

• 求人サイトの広告運用です
  • 商品は求人案件です
  • 商品数が多いほど、広告の出稿効率は上がります
• 成果報酬型のサービスです
  • いつまで掲載するかはわかりません
  • 企業様で自由に掲載のオンオフができます

これはつまり、

• 掲載がいつ落ちるかわからないから、いつ広告を止めたらいいかわからない
  • すでにない［商品］の広告を出してもお金の無駄である
• 掲載数が急に減ったりすると、広告の出稿効率が落ちる
  • ［商品］が大幅に入れ替わると学習効率が悪いんだとか

こんな悲劇があるということです。あぁなんて世知辛い世の中なのでしょう…

やりたいこと

せめて、掲載数が大幅に減ったことが早めにわかれば、何かしらの対応が取れる。そもそも大幅に減らないことが理想ではありますが、、。
しかも、掲載数の増減はセールス側もちゃんと把握できてなさそう。セールス側に企業名を共有してあげて、対応を取ってもらいたい。

そこで

slackのbotを使って、掲載数が減った時にアラート通知をしよう！

ということで、以下にその手順と詳しい内容について書きました。似たような悩みをお持ちの稀有な方がいらっしゃれば、ぜひ参考にしてください（そして一緒に飲みにいきましょう）。

手順

• 社内DBからスプレッドシートにrawdataはきだし
• 直近の案件数差分を企業ごとに計算
• 案件数差分がある企業をslackにアラート
• 社内担当者が把握してなかったら対応してもらう
• これを毎日自動化する

一つずつ解説していきます。

社内DBからスプレッドシートにrawdataはきだし

まずはこんな感じのクエリを書きました。

select
  日付,
  担当者,
  企業名,
  count(案件ID)
from
  案件マスタ
  inner join 企業マスタ
  inner join 担当者マスタ
where
  日付 between DATEADD(day, -3, CURRENT_DATE) and current_date
group by
  日付,
  担当者,
  企業名
;

「直近の案件数差分」を計算するために、過去3日間の企業ごと案件数と担当者を引っ張ってきます。
SQLのmax関数使ってクエリ上で算出できるやん！というご指摘は傷つくのでご遠慮くださいませ。でも今思えばそうした方がよかったです。。

直近の案件数差分を企業ごとに計算

max関数を使わなかったので、スプレッドシート上で加工して差分を算出する必要がありました。

企業一覧を作成(unique関数)

企業名
A株式会社
B株式会社
C株式会社

企業ごとの3日間の案件数を並べる（vlook関数 & arrayformula関数）

企業名	11/30	12/01	12/02
A株式会社	100	80	80
B株式会社	3000	3000	2100
C株式会社	200	180	200

案件数差分がある企業をslackにアラート

以下のGoogle Apps Scriptを書きました。getRangeのnumからなんとなくシートの構造を想像していただけると…。

   var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('確認用');
   var message = ""; 
   
   var postUrl = 'https://hooks.slack.com/hogehogeurl'; // slackの案件数アラート部屋
   var username = 'alert_bot';  // 通知時に表示されるユーザー名
   var icon = ':hatching_chick:';  // 通知時に表示されるアイコン
   var lastRow = sheet.getLastRow(); // 最後の行を取得する
    
   function alert_for_slack() {
    
     for(var i = 2; i <= lastRow; i++) {
         var cnt_diff = sheet.getRange(i, 6).getValue() - Math.max.apply(null, [sheet.getRange(i, 5).getValue(), sheet.getRange(i, 4).getValue()]);     
         if(cnt_diff < 0 && sheet.getRange(i, 2).getValue() !== '-'){ 
         message = sheet.getRange(i, 1).getValue() + ' が ' + (-cnt_diff) + '件減った！ ';
          /* slack投稿*/
          var jsonData =
              {
                "username" : username,
                "icon_emoji": icon,
                "text" : message
              };
          var payload = JSON.stringify(jsonData);
          var options =
              {
                "method" : "post",
                "contentType" : "application/json",
                "payload" : payload
              };
          UrlFetchApp.fetch(postUrl, options);
        }
     }
     
   }

slack投稿はこちらを参考にさせていただきました↓
https://qiita.com/chihiro/items/c7b11abc78f5d806c3a8

該当のデータを配列に格納して、まとめてslackに送った方がいいんだろうなと思いましたが、配列にするとbotが「DATA！」とだけ叫んでしまうので、諦めて一回一回slackに投げる方法にしました。うまいやり方をどなたか教えていただきたいです。

社内担当者が把握してなかったら対応してもらう

ここが重要です。セールス側に速やかに対応してもらうために

• 案件が減ることによる広告出稿の非効率性
• そもそもの広告出稿の仕組みと構造
• slackのアラート通知部屋と対応フロー

これらの共有会を行いました。全担当者にアラート通知部屋に入ってもらい、確認後の反応を毎日行ってもらっています。

これを毎日自動化する

仕組みができれば、あとは自動化するだけです。

社内ツール「query2csv」にクエリを登録

弊社には、クエリを登録しておくと、毎日決まった時間に自動でクエリを回し、スプレッドシートへはきだしてくれる素敵なツールがあります。
それに今回のクエリを登録しておきます。

クエリが回ったあとの時間にGASのトリガーを設定

クエリが回った後に自動でslackにアラートが飛ぶよう、GASのトリガーを設定します。
現在は、セールスが確認しやすいお昼にアラートが飛ぶようにしています。

まとめ

以上が、「案件数アラート」と呼んでいるシステムの仕組みです。
これによって、事業部内での「広告出稿」に関する共通理解も進んだ感じがしていて、息のしやすい世の中になったなあと感じております。
また、社内DB→GAS→slackの連携が面白くて、今では別のところにも活用しています。

ちなみに

直接は言いにくいことも、ボットに喋らせるとスムーズだったりします。

 function comment_for_slack() {
    message = '@〇〇さん、早く対応してね！！';
    /* slack投稿*/
    var jsonData =
        {
          "username" : username,
          "icon_emoji": icon,
          "text" : message
        };
    var payload = JSON.stringify(jsonData);
    var options =
        {
          "method" : "post",
          "contentType" : "application/json",
          "payload" : payload
        };
    UrlFetchApp.fetch(postUrl, options);
 }