SalesforceとRedashでSaaSサブスクリプションのMRRを分解して可視化する

石渡貴大@マインディア

2022年11月9日 10:48

アルファベットとカタカナが多くてルー大柴感がすごいタイトルになってしまいました。

マインディアはサブスクリプションで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にデータをエクスポートして可視化してるけど、直接レコードに移動しにくいとかデメリットもあった。

Visualforceページにredashのレポートをembedして、ホーム画面に両方並べるのがすごい良さげな気がしている pic.twitter.com/X5kyStYxBI
— 石渡貴大 @ 株式会社マインディア (@takahirostone) October 20, 2021

最後に

他にもSalesforce関連や、SaaSのマーケティングについて他の会社さんがどうやっているか聞いてみたいので、カジュアルにお話しさせていただけると嬉しいです！お気軽にMeetyもしくはtwitterでご連絡ください！

Salesforce使い始めて3日だけど、毎日2件ずつくらい問い合わせしてる。めっちゃむずくない？？
— 石渡貴大 @ 株式会社マインディア (@takahirostone) April 23, 2021

また、今後他のKPIの可視化についても機会があれば書いてみたいなと思っています。

よろしければサポートお願いします！そのうちオリジナルドメインにしたいなと思っているのでその資金にさせていただきます！