掲載が落ちてた！教えて 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);
 }
```