Help us understand the problem. What is going on with this article?

Power BI × R で予測分析「予算達成できる可能性は?」に答える

筆者はメーカーの営業部門でデータ分析をしています。
毎年、年の瀬が迫ったこの時期になると、会社の上の人からほぼ毎日投げかけられる質問はこれです。

「このままいくと売上予算達成できそう?達成できる可能性は何パーセントだと思う?」
「このままだと予算までいくら足りない見込み?ベストケースとワーストケース両方出して」

これは、まじめに答えようとすると結構難しいです。要は、月末とか年度末とかまでの売上を統計的に予測して、その確率分布を求めないといけません。偉い人から聞かれるたびにそんな計算していたら、仕事どころじゃない。ここに業務効率化のチャンスが眠っているわけです。

Power BI が何とかしてくれないかなー。

作ったもの

というわけで、R で予測モデルを構築して、その結果をもとに売上着地見込み予算達成可能性を教えてくれる Power BI レポートを作ってみました。

image.png

要件

このレポートの想定ユーザは、営業やマーケティング部門で、特にマネジメントに関わる人たちです。こういう人たちが実務的に使えるようにするためには、以下のような要件を満たす必要があります。

予測区間

ビジネスの人たちは不確実性、特に最悪のシナリオを考えて行動したいと思っています。「着地見込みは 1億円」という情報だけでは役に立ちません。「1億 ± 100万円」なのか、「1億 ± 5,000万円」なのかでは大違いです。ですので、期待値だけでなく 90% 予測上限と下限を表示する必要があります。

アドホックな集計条件

ビジネスの人たちが投げてくるリクエストは、アドホックで予測不可能です。たとえば、「明日から月末までの期間合計の、関東エリアの A および B という製品カテゴリ合計の売上予測(ベストケース、ワーストケース両方)は?」みたいな感じです。ですので、スライサで期間やエリアや製品カテゴリを絞り込んでも、きちんと結果を表示できるようにする必要があります。

全体の設計

全体的な図は以下のようになります。

image.png

R で予測モデルを構築し、その結果を Power BI に取り込んで可視化する、というのが全体の流れです。

Power BI の役割は、R の出力した統計学的な数値を単にグラフ化したりすることではありません。Power BI のもっとも重要な役割は、R の出力した数値を「材料」として、ユーザからの要求に応じてオーダーメイドで「調理」することです。

どういうことでしょうか。

ユーザからのリクエストはアドホックです。起こりうるフィルタ条件の組み合わせ数は無限大に近く、あらかじめすべてのパターンの予測値を計算してディスクやメモリに保存しておくことはできません。

また、R ビジュアルの中でオンデマンドに計算することもできません。なぜなら、統計的な計算というのは、基本的にすごく重い時間のかかる処理だからです。ビジュアルの中で R をいちいち起動して計算していたら、ユーザから苦情がきます。また、ビジュアルが受け取るのはテーブル全体ではなく、スライサで絞り込まれたり粗い粒度で集計された N の少ないデータです。これでは統計的に正しい結果を保証できません。

そこで、ベストなやり方は、R であらかじめ計算して保存しておいた予測情報を、ユーザの問い合わせに応じて集計しなおすことです。たとえば、「エリア × 製品カテゴリ × 日付」のような、可能な限り粒度の細かい予測情報を R で計算し、その結果をあらかじめ Power BI のテーブルに読み込んでおいて、クエリ条件に応じてこれを DAX で集計するのです。これであれば、どのようなアドホックな問い合わせに対しても、瞬時に正しい結果を返すことができます。

言葉を変えると、R が出力した「売上予測」をひとつのファクトとして、「売上」や「予算」などのファクトと同列に扱い、期間、エリア、製品カテゴリなど、どのディメンション(次元)でスライスしても、正しい売上予測を計算できるように、リレーションシップやメジャーを設計すればよい、ということになります。

image.png

そもそも集計とは・・・

全体像がわかったところで、「売上予測」ファクトテーブルの中身を検討したいと思うのですが、その前に予備知識として、どうしてもこのトピックに触れておく必要あります。

そもそも集計には3種類あるということです。

  • 加法的 (Additive) - どのディメンションに対しても、SUM で集計することができる。
  • 非加法的 (Non-Additive) - どのディメンションに対しても SUM で集計することができない。
  • 準加法的 (Semi-Additive) - ディメンションによって SUM で集計できる場合とできない場合がある。

1つ目の 加法的 (Additive) な集計はもっとも単純です。たとえば、「合計売上」は加法的 (Additive) です。「製品 A の売上 = 1万円、製品 B の売上 = 3万円」のとき、「製品 A および B の合計売上」は「1万円 + 3万円 = 4万円」になるので、「合計売上」は「製品」というディメンションに対して加法的です。同様に、「昨日の売上」と「今日の売上」を足し合わせると「2日間の合計売上」になるので、「日付」というディメンションに対しても加法的です。DAX でメジャーを定義するなら

合計売上 = SUM ( '受注'[金額] )

のような感じでシンプルです。世の中の BI ツールは基本的に SUM 集計に向いていて、Power BI も例外ではありません。

2つ目の 非加法的 (Non-Additive) は、典型的には「比率」とか「平均」とかがこれに該当します。たとえば、「平均販売単価」を考えると、「製品 A の平均販売単価 = 100円、製品 B の平均販売単価 = 300円」のとき、「製品 A および B の平均販売単価」は、当然ながら「100円 + 300円 = 400円」ではありません。ですので、「平均販売単価」は「製品」というディメンションに対して非加法的です。このパターンでは、ちょっと頭を使って DAX メジャーを書く必要が出てきます。

多くの場合、非加法的 (Non-Additive) な集計値同士を集計することはできません。上の平均販売単価の例で、「製品 A および B の平均販売単価」はどのように計算すればよいでしょうか。「(100円 + 300円) ÷ 2 = 200円」という計算は間違いです。なぜなら、「製品 A の販売数 = 1,000個、製品 B の販売数 = 10個」のような場合、「製品 A および B の平均販売単価」は明らかに100円に近い値になるはずだからです。このように、「平均販売単価」を求めるのに「平均販売単価」同士を集計することはできません。

上の場合、「製品 A および B の合計販売金額」を「製品 A および B の合計販売数」で割るのが正しい計算です。「合計販売金額」と「合計販売数」はどちらも加法的 (Additive) です。DAX では、

合計販売金額 = SUM ( '受注'[金額] )
合計販売数 = SUM ( '受注'[数量] )

という 2つのメジャーを定義したうえで、

平均販売単価 = DIVIDE ( [合計販売金額], [合計販売数] )

のようになります。このように、多くの場合、非加法的 (Non-Additive) な値は、加法的 (Additive) な値同士を掛けたり割ったりすることで求められます。

3つ目の 準加法的 (Semi-Additive) な集計については、詳しくは触れませんが、これに属する代表的なものは「棚卸在庫」や「口座残高」などです。これらは、「日付」というディメンションに限っては SUM を使うことができませんが、ほかのすべてのディメンションに関しては SUM で合計することができます。

「売上予測」の集計ロジック

さて、ここまでを踏まえた上で「売上予測」の集計ロジックを考えてみたいと思います。

要件を振り返ってみましょう。

期待値だけでなく 90% 予測上限と下限を表示する必要があります。

まず、期待値は加法的 (Additive) なので簡単です。日ごとの期待値を SUM すれば月合計の期待値になりますし、製品ごとの期待値を SUM すれば全製品合計の期待値になります。したがって、売上予測期待値のメジャーは、本質的にはこれだけです。

売上予測期待値 = SUM ( '売上予測'[期待値] )

これだけで、「明日から月末までの期間合計の、関東エリアの A および B という製品カテゴリ合計の売上予測は?」というような複雑なクエリが飛んできても、答えられるわけです。

問題は、予測上限と下限です。わかりやすく、以下のケースを考えてみましょう。

来月の日ごとの売上 90% 予測区間は、毎日「1億 ± 1,000万円」です。この場合、「来月30日間の合計売上」の 90% 予測区間の下限はいくらでしょうか?

「9,000万円 × 30日 = 27億円」と思った方は、残念ながら間違いです。なぜなら、9,000万円というワーストケースの売上が、30日間毎日続くことがありえるでしょうか?売上というのは、良い日もあれば悪い日もあるものです。毎日悪い売上が続くのは明らかに異常事態で、その確率は90%下限よりはるかに低いはずです。

このように、予測上限や下限は 非加法的 (Non-Additive) であり、上限値や下限値同士をそのまま集計することはできません。ちょうど、先ほど「平均販売単価」同士を集計することはできなかったのと同じです。したがって、R が出力した毎日の予測上限や下限をPower BI にインポートしても意味がないということです。

ではどうするかというと、予測上限や下限は、予測分布のパラメータから計算する必要があります。予測値の確率分布が正規分布である場合、これは容易に求めることができます。仮に「明日の売上」が、「平均パラメータ = 1億円、標準偏差パラメータ = 500万円の正規分布にしたがう」という予測結果が出たとすると、90% 予測下限は「1億円 - 1.96 × 500万円 ≒ 9,000万円」という計算になります。

さらに、正規分布の「再生性」という性質のおかげで、「平均パラメータ」および「分散パラメータ」(標準偏差の2乗)は、加法的 (Additive) に集計することができます。つまり、毎日の売上が「平均パラメータ = 1億円、分散パラメータ = 2.5E+13(500万円の2乗)の正規分布」と予測しているとして、「30日間合計売上」の予測は、

  • 平均パラメータ = 1億円 × 30日 = 30億円
  • 分散パラメータ = 2.5E+13 × 30日 = 7.5E+14 ≒ 2,700万円の2乗

の正規分布にしたがうことになります。計算の内容は、それぞれのパラメータを30日分単純合計しているだけです。ここから、90% 予測下限は「30億円 - 1.96 × 2,700万円 ≒ 29.5億円」と計算することができます。(先ほどの「9,000万円 × 30日 = 27億円」という計算よりも高くなることに注目してください。)

したがって、「エリア × 製品カテゴリ × 日付」のような粒度の「予測平均」および「予測分散」という 2 つの正規分布パラメータを R から出力させれば解決できます。これをインポートして「売上予測」ファクトテーブルに入れておけば、どんな複雑なフィルタ条件が投げられても、予測上限や下限を計算することができるのです。

image.png

メジャーの DAX 式は(簡略化すると)このような感じになります。

予測平均 = SUM ( '売上予測'[平均パラメータ] )
予測分散 = SUM ( '売上予測'[分散パラメータ] )
売上予測90%上限 = [予測平均] + 1.96 * SQRT ( [予測分散] )
売上予測90%下限 = [予測平均] - 1.96 * SQRT ( [予測分散] )

「予測平均」と「予測分散」は単純 SUM による加法的 (Additive) なメジャーです。これを使って、非加法的 (Non-Additive) な「売上予測90%上限(下限)」を計算しています。

この計算、よく見ると、「平均販売単価」の計算と似てませんか?対比のために、もう一度「平均販売単価」の定義を出してみます。

合計販売金額 = SUM ( '受注'[金額] )
合計販売数 = SUM ( '受注'[数量] )
平均販売単価 = DIVIDE ( [合計販売金額], [合計販売数] )

いかがでしょうか。

ちなみに、このパラメータを使うと、「予算を達成できる可能性が何パーセントか」というメジャーも定義できます。簡略化するとこんな感じです。(実際には、「過去の日付では予測でなく実績を見る」などを考慮するので、もう少し複雑になります。)

予算達成可能性 =
1 - NORM.DIST ( [合計予算], [予測平均], SQRT ( [予測分散] ), TRUE )

こちらも、「合計予算」「予測平均」「予測分散」という 3 つの加法的 (Additive) なメジャーを使って、「予算達成可能性」という非加法的 (Non-Additive) な計算をしていることにご注目。

なお、売上予測分布が正規分布でない場合は、このようにはいきません。分析手法によっては、ランダムサンプリングによるシミュレーション結果としてしか予測分布を出力できなかったりします。その場合は、DAX ではどうにもなりません。BI としてユーザに使わせることを目的とする場合は、それに合わせて予測モデルも選ぶ必要がある、ということです。

おまけ:Rで売上を予測する

この記事のメインテーマは以上で終わりです。

ですが、まだ R に関して何も触れていません。このままだとタイトルが詐欺になってしまいますので、R 側の作業の流れも一通り紹介します。興味があればご覧ください。興味がない方はお疲れ様でした。

売上予測には Prophet というパッケージを使ってみます。Prophet ではビジネス時系列データによくある特徴(年・週など複数の周期の季節変動、休日、トレンド変化点の検出、市場飽和など)があらかじめモデル化されているので、専門知識がなくても手軽に予測できるというコンセプトで Facebook が開発しています。

このパッケージは Power BI Service でもサポートされているので、デスクトップで作成したものをそのまま Power BI Service に載せられるのがおいしいところです。

Prophet はランダムサンプリングによって予測値をシミュレーションします。ですので、Prophet の出力をそのまま Power BI のデータモデルに読み込んだとしても DAX では欲しい結果を計算できません。ですが、Prophet のモデルでは予測分布が正規分布になりますので、単純にサンプルの平均と分散を集計して、これをパラメータとして Power BI のデータモデルに読み込めばよさそうです。

ではやってみましょう。sales というデータフレームには 2016/01/01 から 2019/12/19 までの、日付、製品カテゴリ、テリトリーごとの売上集計値が入っています。

head(sales)
#         Date  ProductCategory Territory   Sales
# 1 2016-01-01        Computers      East 6686278
# 2 2016-01-01        Computers      West 3693460
# 3 2016-01-01   Games and Toys      East  483554
# 4 2016-01-01   Games and Toys      West  460603
# 5 2016-01-01 Home Applicances      East 1508399
# 6 2016-01-01 Home Applicances      West  827106

tail(sales)
#            Date  ProductCategory Territory   Sales
# 8688 2019-12-19        Computers      East 5275322
# 8689 2019-12-19        Computers      West 6639583
# 8690 2019-12-19   Games and Toys      East  824369
# 8691 2019-12-19   Games and Toys      West  708981
# 8692 2019-12-19 Home Applicances      East 1198638
# 8693 2019-12-19 Home Applicances      West 1112577

はじめに、"Computers" という製品カテゴリの "East" テリトリーでの予測を2020年末まで出してみます。この売上データには、年単位および週単位のシーズナリティがあるものとします。こんな感じで Prophet が日ごとの予測値を出して、結果をきれいにプロットしてくれます。

library(prophet)
library(dplyr)

# "Computers" 製品カテゴリの "East" テリトリーの日ごとの売上を抽出
# ds に日付、y に売上を入れる
df <- sales %>%
    filter(ProductCategory == "Computers", Territory == "East") %>%
    select(ds = Date, y = Sales)

# 予測する将来の日付のデータフレーム (2020年末までの各日付)
future <- data.frame(ds = seq(as.Date("2019-12-20"),
                              as.Date("2020-12-31"),
                              by = 1))

# モデルを構築し、過去のデータからパラメータ推定する
m <- prophet(df,
             yearly.seasonality = TRUE,
             weekly.seasonality = TRUE,
             daily.seasonality = FALSE)

# 将来の予測値を計算する
forecast <- predict(m, future)

plot(m, forecast)

image.png

ただし、この予測結果に含まれているのは、点予測値(MAP)および予測上限・下限(デフォルトでは80%区間)だけです。このままでは Power BI に読み込んで使うことはできません。

# 予測結果オブジェクトに含まれる売上予測値 (点予測、上限、下限)
forecast %>% select(yhat, yhat_upper, yhat_lower) %>% head()
#      yhat yhat_upper yhat_lower
# 1 6351597    7276130    5447816
# 2 6005500    6910817    5128634
# 3 5820885    6682054    4945583
# 4 5679583    6614519    4763759
# 5 5763689    6667298    4815160
# 6 5995371    6890315    5172404

ほしいのは予測値の分布の平均および分散です。これは、生のランダムサンプルから集計することで得られます。

# 予測値のランダムサンプルを取得する
# 日数 × サンプル数 (1000) の行列になっている
yhat_samples <- prophet::predictive_samples(m, future)$yhat

# 日ごとの平均および分散を集計する
mu <- apply(yhat_samples, 1, median)
sigma2 <- apply(yhat_samples, 1, var)

# このデータフレームを Power BI に読み込む
out <- data.frame(date = future$ds, mu = mu, sigma2 = sigma2)
head(out)
#         date      mu       sigma2
# 1 2019-12-20 6322278 465149336465
# 2 2019-12-21 6007799 493821464432
# 3 2019-12-22 5837066 460320745183
# 4 2019-12-23 5666203 456017977654
# 5 2019-12-24 5736576 467079523389
# 6 2019-12-25 6036327 484732737539

R の側でやることは、ここまでと同じ流れを、各製品カテゴリおよびテリトリーの組み合わせごとに計算するだけです。あとは、この結果を Power BI に「売上予測」テーブルとして読み込めば OK です。

R のコード全体や、Power Query と R のデータ受け渡しなどに関しては PBIX ファイル を見ていただければなと。

まとめ

「データサイエンス」をテーマにした、BI の「データモデリング」についての記事でした。データモデリングって地味ですが、結構大事だし奥が深い、ということが伝わればと思います。

筆者の会社も予算達成できることを祈ります。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away