私はマーケでリードを集めて、インサイドセールスに渡す仕事をしています。マーケオプスとしてリードの管理もしていて、その管理はスプレッドシートです。Salesforceからリードのレポートを出してスプレッドシートに貼り、施策別にリード数と商談になった数を並べています。
データを抜くのは、SOQLという、Salesforceに問い合わせの文を書く仕組みです。抜いたら、施策別のファネルをスプレッドシートで組み直す。週に1回か月に1回、1回で1〜2時間かかります。
先週のタブに、今週のエクスポートをそのまま貼り直す。
「よし、貼っただけで今週の分も出た」
ファネルの合計を、レポートの総件数と見比べる。
「……合わない。総件数より少ない」
行が増えた分が数式の範囲から外れていたか、列の並びが変わって別の列を数えていたか。どちらもエラーは出ません。
貼るタブと集計するタブを分けて、集計の側を行数と列の位置に頼らない書き方にしておけば、貼り直すたびに組み直さずに済むはずです。翌週からは消して貼るだけで済む。
貼ったデータの下や右に集計を置いている場合
集計の表を貼ったデータの下に置いていれば、翌週のエクスポートが前回より長いときに、貼ったデータが集計の表を上書きします。右側に置いていれば、貼り直しで中身を消すときに集計も一緒に消えます。
数式の側も同じです。
=COUNTIFS($B$2:$B$500, $G2, $C$2:$C$500, "商談化")
500行目までを数える式なので、エクスポートが501行になった時点で、はみ出した分は数に入りません。エラーは出ない。ファネルの合計と総件数を見比べるまで気づけません。
貼るタブの中身
タブを2つ用意します。「データ」と「集計」とします。
「データ」のタブは、1行目がレポートの見出し、2行目からがレコードで、それ以外は何も置きません。合計の行も、メモも、数式も置かない。集計の数式はこのタブの列をまるごと数えるので、余計なものがあると一緒に数えます。
貼り直すときは、タブ全体を選んで中身を消してから、A1を選んで値のみ貼り付け(Macは⌘+Shift+V、WindowsはCtrl+Shift+V)です。消さずに上から貼ると、今回の方が行数が少ないとき、前回の残りが下に残ったまま数えられてしまう。
集計のタブの数式
「データ」のタブが、A列がリードID、B列がキャンペーン名、C列が状況、D列が作成日の並びで、商談になったリードはC列に「商談化」と入るとします。
「集計」のタブは、A列に施策名を書き、B列から右が数式です。B2とC2とD2は、下の行にもコピーします。
B2: =COUNTIF('データ'!$B:$B, $A2)
C2: =COUNTIFS('データ'!$B:$B, $A2, 'データ'!$C:$C, "商談化")
D2: =IF(B2=0, "", C2/B2)
$B:$Bは、B列の全部です。行番号を書いていないので、エクスポートが2,000行でも5,000行でも、範囲を書き直しません。
COUNTIFSは、2つ目以降の範囲を1つ目と同じ行数と列数にする決まりです。片方だけ$C$2:$C$500のように行番号つきで残すとエラーになります。両方を列全体にしておけばそろう。
A列の施策名は、手で書いても、=UNIQUE('データ'!B2:B)で「データ」から取り出しても構いません。手で書く方をすすめます。取り出す形だと、増えた施策の行に横の数式が無く、気づかないまま表が1行短いことがあるからです。
レポートの列の並びが変わる場合
ここまでの式は、キャンペーン名がB列にある前提です。レポートに列を1本足して、キャンペーン名がC列に動くと、$B:$Bは別の列を数えます。エラーは出ません。
そこまで守りたいなら、列を位置で指すのをやめて、1行目の見出しから探します。
=COUNTIFS(INDEX('データ'!$A:$Z, 0, MATCH("キャンペーン名", 'データ'!$1:$1, 0)), $A2, INDEX('データ'!$A:$Z, 0, MATCH("状況", 'データ'!$1:$1, 0)), "商談化")
MATCHが、1行目の中で「キャンペーン名」が左から何番目にあるかを返します。INDEXは、行に0を指定するとその列全体が返る関数です。列が右に動けばMATCHが返す数字が変わるだけで、数式は触らない。
誰かが見出しを「CP名」に変えると、MATCHが見つけられずに#N/Aになります。そのときは、数式の中の見出しの文字を直せば戻ります。
翌週、新しいエクスポートを貼るとき
この形にしておけば、翌週やるのは「データ」のタブを消して貼ることだけです。「集計」のタブを開く必要はありません。施策が増えた週だけ、A列に1行足して横の数式をコピーします。
貼るところまでは、私が作ったReportZenというGoogle Workspace Marketplaceのアドオンで自動にできます。Salesforceの表形式のレポートをGoogleスプレッドシートのタブへ全件取り込んで、毎朝自動で更新するものです。更新のたびにタブの中身を消してから書き直すので、前回の残りが下に残ることもありません。「集計」のタブは、この記事の数式がそのまま使えます。
数式の動きは公式ヘルプで確かめました。