SalesforceとRedashでSaaSサブスクリプションのMRRを分解して可視化する
アルファベットとカタカナが多くてルー大柴感がすごいタイトルになってしまいました。
マインディアはサブスクリプションでSaaSを提供しています。MRRをはじめとしたKPIの可視化は非常に重要だと捉えていて、誰でも手軽にKPIを確認できる環境整備に取り組んでいます。
KPI可視化の重要性は改めてここで語るまでもないと思いますが、各社で環境が違うので、なかなか自社にぴったりあったメソッドが見つからなかったりしますよね。ここで紹介する内容が誰かのお役に立てば嬉しいです。
取り組み前の悩み
今回の取り組みは以下のような悩みから出発しているので、同じようなことにお悩みの方には共感いただけるんじゃないかなと思っています。
Salesforceのレポート機能だけだと他のオブジェクトとのJOINが難しかったり、前月・前年データとの比較などがやりづらい
単純なMRRの集計は可能だが、New MRRやChurned MRRなどに分解するのが難しい
App Exchangeを使えばできそうだが、まだ有料のApp Exchangeを使うような規模ではない
SOQLでのJOINは難しいので、クエリを書くならできればSQLで書きたい
Salesforce上でレポートを作るとSalesforceのアカウントを持っている人しかレポートが見られない
Salesforceには存在しないデータ（顧客のログイン履歴など）もKPIとする場合、Salesforceではレポートを作れない
紹介する内容
ここでは
Salesforceで管理されている毎月のサブスクリプション売上データからMRRを集計し、前月MRR、New MRR、Expansion MRR、Downgrade MRR、Churned MRRに分解してグラフ化して、ダッシュボード化する。必要ならSalesforceのダッシュボードにも埋め込む
というタスクを例としてご紹介していきます。
書いている人
株式会社マインディアというマーケティングDXのSaaSを扱う会社でBtoC・BtoB両方のマーケティングをしています。
Salesforceに関しては初心者なのでツッコミ大歓迎です、、、！（この関数使えばSalesforceの標準レポート機能でもできるよー、とか）
何か気になる点があればお気軽に下記twitterまでご連絡ください！
twitter @takahirostone
手順
SalesforceとRedashを連携する
RedashからSalesforceのレコードを取得するクエリ（SOQL）を書く
ビジュアライズに必要なクエリ（SQLite）を書いてグラフ化する
（必要なら）Salesforceのダッシュボードにグラフを埋め込む
1. SalesforceとRedashを連携する
まずはRedashからSalesforceのデータを取得できるようにします。手順はこの記事が参考になります。
2. SOQLを書く
select
Name
,CloseDate
,Amount
,AccountId
,StageName
from
Opportunityこんな感じでSOQLを書きます。データソースには1で連携したSalesforceを選んでください。
このクエリでは商談オブジェクト（Opportunity）からレコードを取得しています。もちろん必要があればカスタム項目も取得できます（項目名の後ろに「__c」をつける必要があります）。
複数のオブジェクトをまたいだデータが必要になる場合、この段階でJOINする方法もありますが、SOQLのJOINは普通のSQLとJOINとはかなり書き方が違うので、難しいです。オブジェクト同士の親子関係も意識しないといけなくて、私もちゃんとした書き方を覚えていないです。なので、SOQLではシンプルに1つのオブジェクトからカラムを取得するだけに留めておき、RedashのQuery Results機能を使うようにしています。
3. SQLiteを書いてグラフ化する
with
o1 as (
select
date(CloseDate, 'start of month') as YearMonth
,AccountId
,sum(Amount) as sum_Amount
from
query_**** //2で書いたクエリのIDを入れます
where
StageName = '****'
and **** //必要な条件を入れます
group by
1, 2
)
,o2 as (
select
*
from
o1
)
,o3 as (
select
*
from
o1
)
,mrrs as (
select
o1.YearMonth
,o1.AccountId
,o2.sum_Amount as last_month_mrr
,case
when o2.sum_Amount is null then o1.sum_Amount
else null
end as new_mrr
,case
when o1.sum_Amount - o2.sum_Amount > 0 then o1.sum_Amount - o2.sum_Amount
else null
end as expansion_mrr
,case
when o1.sum_Amount - o2.sum_Amount < 0 then o1.sum_Amount - o2.sum_Amount
else null
end as downgrade_mrr
,case
when o3.sum_Amount is null then - o1.sum_Amount
else null
end as next_churned_mrr
from
o1
left join
o2
on
o1.YearMonth = date(o2.YearMonth, '+1 months')
and o1.AccountId = o2.AccountId
left join
o3
on
o1.YearMonth = date(o3.YearMonth, '-1 months')
and o1.AccountId = o3.AccountId
)
,m1 as (
select
YearMonth
,sum(last_month_mrr) as last_month_mrr
,sum(new_mrr) as new_mrr
,sum(expansion_mrr) as expansion_mrr
,sum(downgrade_mrr) as downgrade_mrr
from
mrrs
group by
1
)
,m2 as (
select
YearMonth
,sum(next_churned_mrr) as next_churned_mrr
from
mrrs
group by
1
)
,mt1 as (
select
YearMonth
,sum(sum_Amount) as sum_Amount
from
o1
group by
1
)
,mt2 as (
select
*
from
mt1
)
,ym as ( //月ごとのデータに抜けがなければここのサブクエリは必要ありません
select
min(YearMonth) as YearMonth
from
o1
union all
select
date(YearMonth, '+1 months') as YearMonth
from
ym
where
ym.YearMonth < (select date(max(YearMonth), '+1 months') from o1)
)
select
ym.YearMonth
,mt1.sum_Amount as total_MRR
,mt2.sum_Amount as last_month_MRR
,m1.new_mrr as new_MRR
,m1.expansion_mrr as expansion_MRR
,m1.downgrade_mrr as downgrade_MRR
,m2.next_churned_mrr as churned_MRR
from
ym
left join
m1
on
ym.YearMonth = m1.YearMonth
left join
m2
on
ym.YearMonth = date(m2.YearMonth, '+1 months')
left join
mt1
on
ym.YearMonth = mt1.YearMonth
left join
mt2
on
ym.YearMonth = date(mt2.YearMonth, '+1 months')RedashのQuery Resultsの機能を使い、2で取得したレコードデータを可視化していきます。
Query ResultsのRDBMSはSQLiteなので、特に日付・時刻の関数などはお気をつけください。
あくまでも一例ですが、グラフは棒グラフにして「Stacking」をStackに、total_MRRのみ折れ線グラフにすると見やすいかなと思います。
グラフができたらRedashのダッシュボードに配置します。Redashでダッシュボード化することで、Salesforceに存在しないデータのKPI（例えば顧客のログイン履歴データなど）と並べて可視化することができます。
4. Salesforceダッシュボードへの埋め込み
SalesforceでVisualforceページを作成し、そこにiframeでRedashのグラフを埋め込みます。
<apex:page>
<h1>MRR</h1>
<p>
<iframe src="https://******.com/embed/query/****/visualization/********></iframe>
</p>
</apex:page>Apexですが、基本的にはHTMLっぽく書けば大丈夫かと思います。
そして、それをダッシュボードに配置すると、Salesforceで作成したレポートと並べて表示することも可能です。
Salesforceのダッシュボードだとグルーピングの制限とかJOINがしづらいとかでredashにデータをエクスポートして可視化してるけど、直接レコードに移動しにくいとかデメリットもあった。
— 石渡 貴大 @ 株式会社マインディア (@takahirostone) October 20, 2021
Visualforceページにredashのレポートをembedして、ホーム画面に両方並べるのがすごい良さげな気がしている pic.twitter.com/X5kyStYxBI
最後に
他にもSalesforce関連や、SaaSのマーケティングについて他の会社さんがどうやっているか聞いてみたいので、カジュアルにお話しさせていただけると嬉しいです！お気軽にMeetyもしくはtwitterでご連絡ください！
Salesforce使い始めて3日だけど、毎日2件ずつくらい問い合わせしてる。めっちゃむずくない？？
— 石渡 貴大 @ 株式会社マインディア (@takahirostone) April 23, 2021
また、今後他のKPIの可視化についても機会があれば書いてみたいなと思っています。
いいなと思ったら応援しよう！
