非エンジニアがSalesforceとMetabaseを使って目標管理ダッシュボードを作る

[Ateam Brides Inc. Advent Calendar 2021](https://qiita.com/advent-calendar/2021/ateam-brides)の14日目は
株式会社エイチームブライズのたけだが担当します！

# はじめに
- これは非エンジニアの私が、ある部門の ~~無茶振り~~ 依頼を受けて、スプレッドシートを使わずに目標管理ダッシュボードを作る話です。
- 以下の方におすすめ
    - 野良スプレッドシートが散在することに辟易している方
    - 安定稼働してくれる目標管理ダッシュボードがほしい方
    - SQL覚えたのでドヤりたい非エンジニアの方



# 完成図


- こういう感じで目標と実績を並べてます。（数字はダミー）
    - 今回は簡略化して、ハナユメウエディングデスクへの来店数と来店予約数、来店率の3つです。
- 本当はもっとレポートが下に並んでます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/231065/2b71f63c-e31e-35f0-6e04-e085aa10681c.png)

- 担当部門からのオーダーは以下のようなものでした。
    - 目標進捗を管理するダッシュボードなので、実績と目標値が横並びになっていること
    - KPIごとに異なるセグメンテーションが存在する（例では一個だけですが）
        - 来店数は都道府県別で表示するけど、予約数は予約経路別で表示する、みたいなイメージ
    - 合計値とセグメント分けの値どちらも表示すること

# 構成
- 以下3つのツールを使います。代替ツールはいくらでもあります。
    - Salesforce
    - BigQuery
    - Metabase
- 構成図はこんな感じ。シンプルです。
    
![architecture.drawio.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/231065/013bf03b-b8f1-21f2-f04f-918579ebb4a1.png)

- Salesforce
    - 実績と目標データの入力
- BigQuery
    - Metabase（BIツール）で可視化するための仲介
    - Salesforce → Metabaseに直で接続もできると思いますが、会社の方針としてデータの集約場所をBigQueryにしており、その都合で今回も経由
- Metabse
    - ダッシュボード作成。SQLを使用



# 作り方
## Salesforceで目標値と実績データを生成
### 目標値
- 今回はサンプルとして以下のテーブルを用意します。

**objectives**

| kpi    | segment | value |
|--------|---------|-------|
| 来店数 | A       | 5     |
| 来店数 | B       | 3     |
| 予約数 | A       | 7     |
| 予約数 | B       | 4     |

#### カスタムオブジェクトを作る
- 目標を管理するカスタムオブジェクトを作ります。
- カラムは3つ
    - kpi
        - 今回の例だと来店数と予約数です。
        - 来店率は割り算してやります。
    - segment
        - 今回はひとつですが、本番で使っているものはあと5個くらいあります。
    - value
        - 目標値
- セグメントやKPIは部署によって変わると思います。
    - そのたびにオブジェクトやテーブルが増えると、管理が大変なので、Salesforceの[レコードタイプ](https://www.circlace.com/blog/agora/bu3021_20190910.html)という機能で分けて管理することにしました。
        - うちの会社では1部署＝1レコードタイプで管理しています。


#### フォーマットを作って部門長に入力依頼
- サンプルは4レコードなのでそうでもないですが、実際のものは KPI×セグメント×12ヶ月 の組み合わせで、レコード数が200を超えました。
- SalesforceはGoogle フォームのようにポチポチとレコードを生成することが可能ですが、さすがに200回の繰り返し単調作業は気が狂いそうなので、スプレッドシートから一括インポートします。
- 目標値は部門長が管理しているので、それをフォーマットに記入してもらいます。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/231065/6fe56ac3-7537-1be0-92a0-2590b86e8fb6.png)

- 塗りつぶし過ぎてなんのこっちゃって感じですよね、、
- 青く塗りつぶしてるところは、セグメントの値を埋めた状態で部門長に渡して、目標値のところに記入をしてもらいます。
    - 黒塗りは何も書かないセル。
- ここだけややめんどいですが、エクセルのピボットテーブルなどでがんばってもらいます。

#### データインポートウィザードを使って一括インポート 
- 返ってきたスプレッドシートを[データインポートウィザード](https://www.sunbridge.com/blog/tips/salesforce-import-wizard/)を使って、一括インポートします。
    - アップロード後に目標修正が入ったりしますが、その際はSalesforceから直接修正してもらってます。


### 実績データ
- もともとSalesforceを使ってたので既に生成済み。
    - カスタムオブジェクトやらフローやらを使ってAccountの項目をもたせてます。
    - 今回は割愛
- こっちもサンプルを用意しました。

**reservations**

| id | segment | isVisit |
|----|---------|---------|
| 1  | A       | true    |
| 2  | A       | false   |
| 3  | A       | false   |
| 4  | A       | true    |
| 5  | A       | false   |
| 6  | A       | false   |
| 7  | A       | false   |
| 8  | A       | true    |
| 9  | A       | true    |
| 10 | A       | true    |
| 11 | B       | false   |
| 12 | B       | true    |
| 13 | B       | true    |
| 14 | B       | true    |
| 15 | B       | false   |

- id
    - ユニーク。1レコード＝1予約
- segment
    - objectives.segmentと同じ。
- isVisit
    - 来店フラグ。trueなら来店あり。



## BigQueryにSalesforceにある目標と実績データを貯める
- 1回/日の頻度でSalesforce → BigQueryに転送してます。
- このあたりはいつも、頼れるエンジニア @takaHAL に~~丸投げ~~お願いしてるので割愛。



## Metabaseで可視化
- あとはごりごりクエリを書いていくだけです。
    - 例として来店率のクエリを貼ります。

```.sql
WITH objectives AS ( -- 目標値
  SELECT 
    segment
    , SUM(CASE WHEN kpi = '予約数' THEN value ELSE 0 END) AS targetReservations
    , SUM(CASE WHEN kpi = '来店数' THEN value ELSE 0 END) AS targetVisits 
  FROM 
    objectives
  GROUP BY
    1
), reservations AS ( -- 実績
  SELECT
    segment
    , COUNT(*) AS cntReservations
    , SUM(CASE WHEN isVisit THEN 1 ELSE 0 END) AS cntVisits
  FROM
    reservations
  GROUP BY
    1
) -- がっちゃんこ
  SELECT
    o.segment
    , o.targetVisits / NULLIF(o.targetReservations, 0) AS objective
    , r.cntVisits / NULLIF(r.cntReservations, 0) AS result
  FROM
    objectives AS o
  INNER JOIN 
    reservations AS r
  ON
    o.segment = r.segment  
UNION ALL -- 合計値もつける
  SELECT
    'total' AS segment
    , SUM(o.targetVisits) / NULLIF(SUM(o.targetReservations), 0) AS objective
    , SUM(r.cntVisits) / NULLIF(SUM(r.cntReservations), 0) AS result
  FROM
    objectives AS o
  INNER JOIN 
    reservations AS r
  ON
    o.segment = r.segment
  GROUP BY
    1 
```

- 合計値をくっつけないといけないので、最後に UNION ALL でくっつけてます。
- これを実行すると以下のように結果が出力されます。

| segment | objective          | result             |
|---------|--------------------|--------------------|
| total   | 0.7272727272727273 | 0.5333333333333333 |
| A       | 0.7142857142857143 | 0.5                |
| B       | 0.75               | 0.6                |

- このままダッシュボードに貼ってもいいし、Metabaseのビジュアライゼーションで整形してもOK。
    - うちで実際使っているものは、カラム名を日本語にしたり、棒グラフにしたりしてます。

# 完成
- 非エンジニアが作れるくらいのものなので、割とシンプルで簡単だったと思います。
- 最初に目標値を入れるところはやや面倒ですが、どの部署にも応用しやすく、汎用性は高いと思ってます。
- 「会社の業績数字が点在してて分析しづらい！」って方はぜひお試しください。
    - 実績データは部門によって、いろんなクラウドなりフォルダなりに点在してしまうと思うので、とりあえずBigQueryに集めるのがおすすめです。
    - BIツールはなんでもいいと思います。



[Ateam Brides Inc. Advent Calendar 2021](https://qiita.com/advent-calendar/2021/ateam-brides)の15日目は、
みやたけさんがお送りします！
きっと僕が、前座を務めたことが恥ずかしくなるくらいの良記事を認めてくれることでしょう！！！