Ateam Brides Inc. Advent Calendar 2021の14日目は
株式会社エイチームブライズのたけだが担当します！
はじめに
- これは非エンジニアの私が、ある部門の
無茶振り依頼を受けて、スプレッドシートを使わずに目標管理ダッシュボードを作る話です。 - 以下の方におすすめ
- 野良スプレッドシートが散在することに辟易している方
- 安定稼働してくれる目標管理ダッシュボードがほしい方
- SQL覚えたのでドヤりたい非エンジニアの方
完成図
- こういう感じで目標と実績を並べてます。（数字はダミー）
- 今回は簡略化して、ハナユメウエディングデスクへの来店数と来店予約数、来店率の3つです。
- 本当はもっとレポートが下に並んでます。
- 担当部門からのオーダーは以下のようなものでした。
- 目標進捗を管理するダッシュボードなので、実績と目標値が横並びになっていること
- KPIごとに異なるセグメンテーションが存在する（例では一個だけですが）
- 来店数は都道府県別で表示するけど、予約数は予約経路別で表示する、みたいなイメージ
- 合計値とセグメント分けの値どちらも表示すること
構成
- 以下3つのツールを使います。代替ツールはいくらでもあります。
- Salesforce
- BigQuery
- Metabase
- 構成図はこんな感じ。シンプルです。
- 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
- セグメントやKPIは部署によって変わると思います。
- そのたびにオブジェクトやテーブルが増えると、管理が大変なので、Salesforceのレコードタイプという機能で分けて管理することにしました。
- うちの会社では1部署＝1レコードタイプで管理しています。
- そのたびにオブジェクトやテーブルが増えると、管理が大変なので、Salesforceのレコードタイプという機能で分けて管理することにしました。
フォーマットを作って部門長に入力依頼
- サンプルは4レコードなのでそうでもないですが、実際のものは KPI×セグメント×12ヶ月 の組み合わせで、レコード数が200を超えました。
- SalesforceはGoogle フォームのようにポチポチとレコードを生成することが可能ですが、さすがに200回の繰り返し単調作業は気が狂いそうなので、スプレッドシートから一括インポートします。
- 目標値は部門長が管理しているので、それをフォーマットに記入してもらいます。
- 塗りつぶし過ぎてなんのこっちゃって感じですよね、、
- 青く塗りつぶしてるところは、セグメントの値を埋めた状態で部門長に渡して、目標値のところに記入をしてもらいます。
- 黒塗りは何も書かないセル。
- ここだけややめんどいですが、エクセルのピボットテーブルなどでがんばってもらいます。
データインポートウィザードを使って一括インポート
- 返ってきたスプレッドシートをデータインポートウィザードを使って、一括インポートします。
- アップロード後に目標修正が入ったりしますが、その際は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の15日目は、
みやたけさんがお送りします！
きっと僕が、前座を務めたことが恥ずかしくなるくらいの良記事を認めてくれることでしょう！！！