Amazon QuickSightで在庫を可視化する方法

背景

私は、かつてアプリケーションエンジニアでしたが、現在は インフラ周りを扱う SREチームとして活動しています。SREチームの業務の中で BIツールである QuickSightをインフラ関連のコスト分析などに使用しています。
元アプリケーションエンジニアとしての経験から、QuickSightは業務データの分析にも活用できるツールだと考えています。
この記事では、仕入・売上のデータはあるが、在庫の把握ができていないケースを想定した分析画面を作成する手順を紹介します。

データ準備

以下のようなcsvデータを用意してみました。
日々発生した仕入、売上が記帳されたファイルを想定しています。

csvファイルはUTF8で作成してください。Shift-jisで作成するとQuickSightにアップロードしたときに文字化けが発生します。

売上仕入データ.csv

売上仕入データ.csv

日付,商品,区分,数量,金額
2023/9/4,ビール,仕入,400,80000
2023/9/4,ワイン,仕入,150,105000
2023/9/5,ビール,売上,92,32200
2023/9/5,ワイン,売上,71,67450
2023/9/9,ビール,売上,145,50750
2023/9/9,ワイン,売上,51,48450
2023/9/10,ビール,売上,131,45850
2023/9/11,ビール,仕入,500,100000
2023/9/11,ワイン,仕入,250,175000
2023/9/13,ビール,売上,99,34650
2023/9/13,ワイン,売上,73,69350
2023/9/14,ビール,売上,165,57750
2023/9/14,ワイン,売上,89,84550
2023/9/16,ビール,売上,121,42350
2023/9/16,ワイン,売上,56,53200
2023/9/17,ビール,売上,93,32550
2023/9/18,ビール,仕入,600,120000
2023/9/18,ビール,売上,145,50750
2023/9/18,ワイン,仕入,180,126000
2023/9/18,ワイン,売上,118,112100
2023/9/19,ビール,売上,136,47600
2023/9/20,ビール,売上,99,34650
2023/9/22,ビール,売上,178,62300
2023/9/25,ビール,仕入,200,40000
2023/9/25,ワイン,仕入,120,84000
2023/9/26,ビール,売上,92,32200
2023/9/26,ワイン,売上,121,114950
2023/9/28,ビール,売上,88,30800
2023/9/28,ワイン,売上,81,76950
2023/9/30,ビール,売上,93,32550
2023/10/2,ビール,仕入,400,80000
2023/10/2,ワイン,仕入,200,140000
2023/10/4,ビール,売上,127,44450
2023/10/4,ワイン,売上,92,87400
2023/10/6,ビール,売上,100,35000
2023/10/6,ワイン,売上,90,85500
2023/10/8,ビール,売上,180,63000
2023/10/9,ビール,仕入,700,140000
2023/10/9,ワイン,仕入,150,105000
2023/10/11,ビール,売上,134,46900
2023/10/11,ワイン,売上,80,76000
2023/10/14,ビール,売上,120,42000
2023/10/16,ビール,売上,192,67200
2023/10/16,ビール,仕入,200,40000
2023/10/16,ワイン,仕入,120,84000
2023/10/17,ビール,売上,146,51100
2023/10/17,ワイン,売上,112,106400
2023/10/18,ワイン,売上,56,53200
2023/10/19,ビール,売上,97,33950
2023/10/21,ワイン,売上,58,55100
2023/10/22,ビール,売上,185,64750
2023/10/23,ビール,仕入,500,100000
2023/10/23,ワイン,仕入,300,210000
2023/10/24,ビール,売上,150,52500
2023/10/25,ビール,売上,112,39200
2023/10/26,ビール,売上,91,31850
2023/10/26,ワイン,売上,124,117800
2023/10/27,ワイン,売上,67,63650
2023/10/28,ビール,売上,119,41650
2023/10/28,ワイン,売上,82,77900
2023/10/30,ビール,仕入,500,100000
2023/10/30,ビール,売上,115,40250
2023/10/30,ワイン,仕入,200,140000
2023/10/30,ワイン,売上,75,71250
2023/10/31,ビール,売上,147,51450
2023/10/31,ワイン,売上,72,68400
2023/11/3,ビール,売上,99,34650
2023/11/3,ワイン,売上,89,84550
2023/11/4,ビール,売上,167,58450
2023/11/6,ビール,仕入,400,80000
2023/11/6,ワイン,仕入,300,210000
2023/11/7,ビール,売上,97,33950
2023/11/7,ワイン,売上,126,119700
2023/11/8,ビール,売上,190,66500
2023/11/8,ワイン,売上,92,87400
2023/11/12,ビール,売上,106,37100
2023/11/12,ワイン,売上,81,76950
2023/11/13,ビール,仕入,500,100000
2023/11/13,ビール,売上,197,68950
2023/11/13,ワイン,仕入,300,210000
2023/11/13,ワイン,売上,53,50350
2023/11/15,ビール,売上,148,51800
2023/11/16,ワイン,売上,67,63650
2023/11/18,ワイン,売上,157,149150
2023/11/19,ビール,売上,136,47600
2023/11/20,ビール,仕入,600,120000
2023/11/20,ビール,売上,174,60900
2023/11/20,ワイン,仕入,300,210000
2023/11/20,ワイン,売上,85,80750
2023/11/22,ワイン,売上,78,74100
2023/11/23,ビール,売上,177,61950
2023/11/24,ビール,売上,80,28000
2023/11/24,ワイン,売上,63,59850
2023/11/26,ビール,売上,147,51450
2023/11/26,ワイン,売上,89,84550
2023/11/27,ビール,仕入,400,80000
2023/11/27,ワイン,仕入,300,210000
2023/11/28,ビール,売上,173,60550
2023/11/28,ワイン,売上,169,160550
2023/11/29,ビール,売上,89,31150
2023/11/30,ビール,売上,120,42000
2023/11/30,ワイン,売上,89,84550

上記ファイルをQuickSightのデータセットに取り込みます。

項目の属性（文字列など）も自動で判定してくれます。いいですね。
※でも、日付は判定してくれないこともあります。

分析で可視化する

作成したデータセットを分析で表示します。
まずはビジュアルで「テーブル」を選択して、そのまま表示してみました。

次にピボットテーブルで日別に表示してみました。

「仕入数」「売上数」「在庫数」は計算フィールドで定義しています。
在庫数は取引日以前の仕入数と売上数から求めるためrunningSum関数を使用しています。

仕入数

ifelse(区分='仕入',数量,0)

売上数

ifelse(区分='売上',数量,0)

在庫数

runningSum(sum(仕入数 - 売上数), [日付 ASC], [商品]) 

日付ごとの在庫の状況が見えてきましたね。ただ、取引が発生していない日が表示されていないのもわかりにくいです。
カレンダ形式で表示してみることにします。そのために以下の２つのcsvを用意しました。
カレンダ.csvは対象期間の日付リスト、商品.csvは取り扱う商品のリストを想定しています。
「年度」は今回の検証では必要なかったのですが、カレンダデータと商品データの結合キーとして利用しています。
後ほどデータの結合を紹介しますが、キーが１つ以上必要なようです。

カレンダ.csv

カレンダ.csv

年度,カレンダ日付
2023,2023/9/1
2023,2023/9/2
2023,2023/9/3
2023,2023/9/4
2023,2023/9/5
2023,2023/9/6
2023,2023/9/7
2023,2023/9/8
2023,2023/9/9
2023,2023/9/10
2023,2023/9/11
2023,2023/9/12
2023,2023/9/13
2023,2023/9/14
2023,2023/9/15
2023,2023/9/16
2023,2023/9/17
2023,2023/9/18
2023,2023/9/19
2023,2023/9/20
2023,2023/9/21
2023,2023/9/22
2023,2023/9/23
2023,2023/9/24
2023,2023/9/25
2023,2023/9/26
2023,2023/9/27
2023,2023/9/28
2023,2023/9/29
2023,2023/9/30
2023,2023/10/1
2023,2023/10/2
2023,2023/10/3
2023,2023/10/4
2023,2023/10/5
2023,2023/10/6
2023,2023/10/7
2023,2023/10/8
2023,2023/10/9
2023,2023/10/10
2023,2023/10/11
2023,2023/10/12
2023,2023/10/13
2023,2023/10/14
2023,2023/10/15
2023,2023/10/16
2023,2023/10/17
2023,2023/10/18
2023,2023/10/19
2023,2023/10/20
2023,2023/10/21
2023,2023/10/22
2023,2023/10/23
2023,2023/10/24
2023,2023/10/25
2023,2023/10/26
2023,2023/10/27
2023,2023/10/28
2023,2023/10/29
2023,2023/10/30
2023,2023/10/31
2023,2023/11/1
2023,2023/11/2
2023,2023/11/3
2023,2023/11/4
2023,2023/11/5
2023,2023/11/6
2023,2023/11/7
2023,2023/11/8
2023,2023/11/9
2023,2023/11/10
2023,2023/11/11
2023,2023/11/12
2023,2023/11/13
2023,2023/11/14
2023,2023/11/15
2023,2023/11/16
2023,2023/11/17
2023,2023/11/18
2023,2023/11/19
2023,2023/11/20
2023,2023/11/21
2023,2023/11/22
2023,2023/11/23
2023,2023/11/24
2023,2023/11/25
2023,2023/11/26
2023,2023/11/27
2023,2023/11/28
2023,2023/11/29
2023,2023/11/30

商品.csv

商品.csv

年度,商品
2023,ビール
2023,ワイン

「カレンダ」と「商品」は「売上仕入データ」のデータセットとして作成します。
カレンダ.csvをもとにデータセットを作成し、そのデータセットに商品.csvを追加します。
※カレンダ.csvをアップしたら「カレンダ日付」が文字列として認識されてしまいました。取込後に日付の型に変更しています。
※「売上仕入データ」のデータセットに「カレンダ」と「商品」のcsvファイルを追加することも試しましたが、想定した結合ができませんでした。

「年度」で結合し、結合タイプは「Left」にします。
「Right」でもよいですが、カレンダをベースに表示するということで「Left」にしています。

結合されると日付・商品ごとのデータになります。
今回、「年度」は使わないので、除外しています。
分析で利用するときにわかりにくくなるので、結合した項目は除外しておくのがよいです。

次に、「売上仕入データ」に「カレンダ」のデータセットを追加します。
「カレンダ」の日付、商品をベースにするので結合は「Right」を指定します。
また、結合句は商品と日付を指定します。「売上仕入データ」の商品と日付は使わないので、フィールドから除外します。

これでデータセットの準備は終わりです。分析画面に戻ります。

「売上仕入データ」の商品と日付を除外したので、正しく表示されていませんね。カレンダ日付やカレンダの商品を使用するように変更します。
また、在庫数の計算フィールドを以下のように変更します。

在庫数

runningSum(sum(仕入数 - 売上数), [カレンダ日付 ASC], [商品[カレンダ]]) 

下記のように日付ごと仕入数や売上数が表示されるようになりました。

折れ線グラフを利用することで在庫推移もわかりやすくなります。

QuickSightにはインサイト機能があり、異常値を教えてくれたり、予測値を教えてくれたりします。10/8にビールの在庫がなくなりそうだったので、10/9に多めに仕入たのですが、在庫過剰になってしまったみたいですね。見通しが甘かったようです。

フィルタ機能での絞り込み

今回は３カ月のデータを使用していますが、１カ月ごとに推移を見たい場合があります。その場合はフィルタ機能を利用します。指定した年月のデータでフィルタできるようにパラメータ、コントロール、フィルタを設定します。

データは2023/11でフィルタされましたが、在庫がおかしくなってしまいました。。。

在庫数の計算はrunningSum関数を使用していましたが、2023/11でフィルタリングしたせいで2023/10以前のデータが取得できなくなったようです。
クラスメソッドさんのDevelopersIOの記事にもありましたが、前月以前のデータを使用した計算フィールドを定義しているときにフィルタ機能を使うと想定外の結果になることがあります。

今回はテーブル計算関数(minOver)を使うことにしました。テーブル計算関数は「Amazon QuickSight での評価の順序」によると、分析を表示する最後のほうで評価されるとのことです。
以下の計算フィールドを定義します。

フィルタ用日付

minOver(min(カレンダ日付),[カレンダ日付])

また、フィルタ項目をカレンダ日付からフィルタ用日付に変更します。

上記によって年月を指定した場合でも正しく表示されるようになりました。

まとめ

Amazon QuickSightで売上・仕入データをもとに在庫データを可視化する方法を試してみました。さらに発展させ、棚卸回転率や前月・前年比較などもできそうです。QuickSightに関しては、Generative AI機能も発表され（Amazon QuickSight の Generative BI 機能を発表）、AWSの中でもアップデートが多いサービスと思っています。データ分析基盤として活用していきたいです。

余談

普段はAWSコストをQuickSightで可視化しています。円安が止まらないですね。。。厳しいです・・・

（この記事は2023/12/15時点の内容です）