テクテク日記

テクテク＝テクノロジー&一歩ずつ（テクテク）

Power BIによるSaaS分析2 -モデリング・指標

DAX / Data Model Power Query Power BI

前回の記事はSaaSビジネスに関する概要について話をしました。今回は実際にダミーデータを使って、どのようにモデリングし、指標を作っていくかを見ていきたいと思います。

ダミーデータ

前回話をしたダミーデータですが、以下よりダウンロードできます。データのみが入っているPower BIファイルとなります。

ダウンロード先
https://bit.ly/3507aj8

※ダミーデータは既にDAXを記述できる状態となっており、説明不要であれば「SaaSビジネスの指標」へ

ダウンロードしたファイルを解凍し、「SaaS_Analytics_dummy.pbix」というファイルを開きます。分析業務における基礎ですが、最初に

ソースデータ分析

を行います。

ソースデータ分析とは、

  1. データソースの種類の確認
  2. 対象データのボリューム（テーブル数、行数・列数、クエリ数等）の確認
  3. データ粒度（Granularity）、データ濃度（Cardinality）の確認
  4. 各列の中身について吟味

を行うことです。今回の例でいえば、以下のことをやっていきます。

  1. 全てPower BI内にて入力されたデータ（Power Queryによる日付テーブル含む）
  2. 4つのテーブルで構成されており、ボリュームは非常に小さい
    • Sales: ファクトテーブル
    • dCustomer: 顧客マスタ
    • dPricing: プライスマスタ
    • dCalendar: 日付マスタ
      dを付けているテーブルはDimensionのdを意味しており、これを見ればファクトテーブルではないことが簡単に判別できます
  3. （デモ用なので）データの粒度も濃度も特筆すべきものはなし
  4. 日付列、テキスト列、数値列の3つが基本になりますので、ファクト（Sales）とディメンション（それ以外）の中身についてざっくり見ていく

ここまでキレイなデータがそろっていれば棒棒鶏（バンバンジー）・・ではなく、万々歳ですが、通常はPower Queryで”データをマッサージ”してあげる必要があります。

なお、留意点としてdCalendarだけ、ベストプラクティス（全て1日ずつのシーケンスで構成されている必要がある）ではなく、マンスリーの日付テーブルになっていますが、月次ベースのデータ分析であれば何ら支障はありません。これに関して、SQLBIのマルコさんに直接聞いたことがありますが、

ニーズに合わせてマンスリーベースの日付テーブルを使ってもよいが、問題が発生しないとも限らない

という回答でした。個人的な感想として、マンスリーベースの日付テーブルを5年以上使ってきていますが、不具合に遭遇したことがありません。よって、今回はマンスリーベースの日付テーブルで話をし進めていきます。が、基本はシーケンスで全ての日付を埋めて使用することがベストプラクティスであること、ご注意ください。ご参考までに、月次テーブルをベースとした分析について詳述した記事も共有しておきます。

marshal115.hatenablog.com

なお、Power Query虎の穴勉強会でも話に出ていましたが、Power QueryのTable.ProfileとTable.Schema関数を使うと、それぞれのクエリに関する詳細なメタデータ情報を見ることができます（下図）。

f:id:marshal115:20220214182944p:plain

こちら2つの関数はインフォメーション系のテーブル関数になりますが、どちらもSalesというクエリ（テーブル）に対するメタ情報を取得しています。微妙に違いがありますので、以下顕著な違いだけ、まとめておきます。

  • Table.Schema vs Table.Profile
    • クエリのパフォーマンス
      圧倒的にTable.Schemaに軍配。理由は全ての行をスキャンしないため。Table.ProfileはDistinctCountという処理を行うため、クエリ全体をメモリに読み込んで処理を行う

    • 列の並び順
      Table.SchemaはNameとPositionという列がある通り、左から右の列までそのまま結果を返す。一方、Table.Profileを実施すると、列の並び順が変わってしまう（下図の通り。Profilingのパフォーマンスを最適化するためか？）

      f:id:marshal115:20220214185205p:plain

      Table.Schemaが従来の並び順で上から下まで表示されている

    • 使い分け
      データ型、並び順等を知りたい場合はTable.Schema、データの分布詳細を知りたい場合はTable.Profile（データモデリングの際、DistinctCountの情報が特に有用）

データモデリング

前置きが長くなりましたが、ここからデータモデルを作っていきます。まずは、テーブル間のリレーションシップを構築します。各テーブルのリレーションシップは以下の通り（図はDAX Studioより）。

f:id:marshal115:20220214190426p:plain

これで∞（多）-1のリレーションを構築できますが、一番下のSales[EventType] - dEventType[EventType]というリレーションシップを作るためのテーブル（dEventType）がないので、これをまずはDAXで計算テーブルとして作ります。

Power BIを開いた状態で、以下の順番にクリックしていきます。

f:id:marshal115:20220214190950p:plain

すると数式バーが出現しますので、下記DAX式を貼り付けます。

dEventType =
SELECTCOLUMNS (
    { ( "Signed-up", 1 ), ( "Trial", 2 ) },
    "EventType", [Value1],
    "Sort", [Value2]
)

これにより、以下の計算テーブルが構築されました。

f:id:marshal115:20220214192000p:plain

ここから上図（DAX Studioの図）のリレーションシップのKey同士をつなげていけば、最終的には以下のようなデータモデルが出来上がります。1つのファクトテーブルをディメンションテーブルが囲う形で完全なるスタースキーマとなります。

f:id:marshal115:20220214192225p:plain

docs.microsoft.com

以下それぞれのテーブルの列（属性）について簡単に解説します。

ファクトテーブル

Salesテーブル

  • Date: 日付列（月次ベース）
  • CustomerID: 顧客Key
  • EventType: Tiral（トライアル会員） / Signed-up（有料会員）
  • PriceType: Free / Standard / Premium
  • MonthlyPrice: PriceTypeに依拠

殆どがリレーションシップにおけるKey列ですが、シンプルなデータセットです。

ディメンションテーブル

dCustomerテーブル

  • CustomerID: 顧客Key
  • Country: 国
  • OS: パソコンのOS
  • OS_Version: OSのバージョン
  • Sex: 性別
  • TrialDate: トライアル開始月
  • StartDate: 有料会員開始月
  • EndDate: 有料会員最終月
  • UpgradeDate: StandardからPremiumへのアップグレード月

dPricingテーブル

  • PriceType: 有料プランの種類
  • MonthlyPrice: 月額料金（円）
  • Sort: PriceTypeの並べ替えKey

dEventTypeテーブル

  • EventType: イベントステータス（トライアル or 有料サインアップ）
  • Sort: EventTypeの並べ替えKey

dCalendarテーブル

  • Date: 年月（日付）
  • Year: 年（整数）
  • Month: 月（整数）
  • YM: 年月（テキスト）
  • YearMonthNumber: 年月シーケンス（整数）
  • YrFlagSort: YrFlagのソートキー
  • YrFlag: 1yr（直近1年）、2yr（直近2年）、>=3yrs（3年以上）

これで全てのテーブルにおける列の定義を確認できました。Power BIを日頃使っている人であれば、次の作業がSales（ファクトテーブル）にある全ての列を非表示させることが定石であると指摘するでしょうが、いったんこの作業を無視して、指標を作るところに入ります。

SaaSビジネスの指標

前回の記事で少し紹介しましたが、SaaSビジネスには複数の指標があります。以下これらの指標の紹介となります（カッコ内はPower BIで使用するメジャー名）。自分でいろいろ作ってみたりしたものの、指標数があまりにも多いため、代表的なものを紹介をします。

  • 客数指標
    • 全顧客数（# Customers）
      # CustomersはTrial顧客も含むため、CALCULATE用のBaseメジャー*1扱い
      • 有料サインアップ顧客数（# Customers_Signup）
      • 有料サインアップ顧客数_CVR用（# Customers_Signup_forCVR）
      • トライアル顧客数（# Customers_Trial）
      • トライアル顧客数_CVR用（# Customers_Trial_forCVR）
      • 前月顧客数（# Customers_Signup.PM）
    • CVR（Conversion Rate）
      • コンバージョン率（Conversion Rate）
        = [# Customers_Signup_forCVR] ÷ [# Customers_Trial_forCVR]
    • 新規顧客数（# New Customers）
    • 解約顧客数（# Lost Customers）
    • プレミアムプラン顧客数（# Customer_Premium）
    • プレミアム顧客割合（% Customer_Premium）
    • 維持顧客数（# Customer Retained）
    • 顧客リテンション率（Customer Retention）
    • 顧客チャーン率（Customer Churn）
  • 収益指標
    • MRR（Total MRR - Monthly Recurring Revenue）
      月次経常収益（売上）で収益ベースの指標におけるBaseメジャー
      • 前月MRR（Total MRR.PM）
      • Standard単価のみMRR（StandardOnly MRR）
      • Expansion貢献分MRR（delta Expansion MRR）
        => ネーミングセンスゼロですが、次回解説します
      • delta Expansion MRR vs MRR（delta Expansion MRR vs Total MRR）
      • PremiumプランMRR（MRR by Premium）
      • StandardプランMRR（MRR by Standard）
    • 新規顧客MRR（MRR from New Customers）
    • ネット新規MRR（Net MRR from New Customers）
    • チャーンMRR（Churn MRR）
      =>離反顧客のMRR
    • グロス・レベニュー・チャーン（Gross Revenue Churn）
      =>収益ベースの解約率（Dollar Churn Rate）
    • ネット・レベニュー・チャーン（Net Revenue Churn）
      =>グロス・レベニュー・チャーンとの違いは、既存顧客の解約やダウングレードだけではなく、アップグレードなどによって発生した収益の増加も考慮
    • ネットレベニュー・リテンション（NRR -Net Revenue Retention）
      =>収益ベースのリテンション率
    • クイック・レシオ（Quick Ratio）
      =>「顧客ロイヤリティ」を担保できているかの判断

指標が非常に多くなってしまいましたが、時間のある方はダミーデータを使って、上記指標をまず構築できるかどうか試してみるのも面白いかもしれません。答え合わせ的な形になりますが、次回の記事は実際にDAXでこれらの指標を構築していきます。

なお、Tabular Editor 3*2を持っている方は、まとめてDAXスクリプトを実行できるよう、スクリプト自体も記事に載せておきます。

次回は下記より

marshal115.hatenablog.com

*1:該当メジャーをCALCULATE等で参照し、別の用途として使っていくメジャー

*2:Power BIの分析エンジンであるTabularモデルを手軽に操作できる最強のサードパーティツール。有料ですが、本格的なBI開発者には欠かせない