2017.04.27
第3回 Zチャートで業績の推移を確認する
分析の現場では、時系列で売上金額やユーザー数、ページビューなど、各種のデータを集計することは、高い頻度で発生します。Webサイトやサービスの状況を把握する上でも、重要度が高い作業です。本項では、ECサイトの2014年から2015年の2年間にわたる売上データをサンプルとして、時系列で売上を評価するレポートを紹介し、そのレポートを作成するためのSQLを説明します。
書籍のChapter4-1「時系列に沿ってデータを集約する」から、4-1-5「Zチャートで業績の推移を確認する」を紹介します。
顧客に提供するサービスや商品、コンテンツには、季節によって売上が変動するものがあります。本項で紹介するZチャートは、「月次売上」「売上累計」「移動年計」の3つの指標で構成され、季節変動の影響を受けずにトレンドを読み解くことができる手法です。
Zチャートの作成
Zチャートを作成するには、「月次売上」「売上累計」「移動年計」の3 つの指標が必要になります。各指 標ごとに、下記に示す範囲でそれぞれ集計します。
月次売上
売上の合計を月別に集計します。
売上累計
該当月の売上に前月までの売上累計を合計した値となります。
移動年計
該当月の売上に過去11 ヶ月の売上累計を合計します。
各指標の集計済みデータであるZチャートのイメージは、下表の通りです。
Zチャートを読み解くポイント
売上累計への着目
月次売上が一定の場合、売上累計は直線になります。横軸で右に行くほどグラフの傾きが急になる曲線であれば、直近の売上は上昇傾向となり、緩やかになっていれば直近の売上は減少傾向となります。グラフの表示期間で、売上がどのように推移しているのかを読み解くことが可能です（次図：左）。
移動年計への着目
前年から当年にかけて売上が一定の場合は、移動年計は直線になります。右肩上がりになっている場合、売上は上昇傾向となり、右肩下がりであれば減少傾向となります。グラフに表示されていない過去1年の長いスパンで、売上がどのように推移しているのかを読み解くことができます（次図：右）。
さまざまな形状のZチャート
実際にいくつかのパターンで作成したZチャートを下図に示します。それぞれのグラフが何を表して いるのかを解説します。
左のグラフは、売上がほぼ一定で推移している状態なので綺麗な「Z」を表しています。
続いて、中央のグラフは、期末に向かって売上が成長した状態です。月次売上と売上累計、移動年計のすべてが右肩上がりを示していることが分かります。
そして、右のグラフは、昨年は期末に向かって売上が成長したが、翌年はその成長が止まり、2年前と同じ水準になった状態を示しています。月次売上と売上累計は直線的な形状を表していますが、移動年計が右肩下がりになっています。
Zチャートを作成するための指標を集計する
Zチャートを作成するために必要な指標を集計するSQLを、下記のコード例に示します。
Zチャートに必要な指標は月単位で集計するので、まずは購入ログを月別の売上に集計します。次に、各月の売上に対して、累計売上と移動年計を計算します。移動年計を計算するためには、Zチャートを表示したい月から過去1年分の売上データが必要ですが、グラフ表示の際には不要となるので、最後に必要な期間のデータだけを抽出します。
WITH
daily_purchase AS (
-- 略
)
, monthly_amount AS (
-- 月別の売上を集計
SELECT
year
, month
, SUM(purchase_amount) AS amount
FROM daily_purchase
GROUP BY year, month
)
, calc_index AS (
SELECT
year
, month
, amount
-- 2015年の累計売上を計算
, SUM(CASE WHEN year = '2015' THEN amount END)
OVER(ORDER BY year, month ROWS UNBOUNDED PRECEDING)
AS agg_amount
-- 当月から11ヶ月前までの合計売上（移動年計）を計算
, SUM(amount)
OVER(ORDER BY year, month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
AS year_avg_amount
FROM
monthly_purchase
ORDER BY
year, month
)
-- 最後に、2015年のデータのみに絞り込む
SELECT
concat(year, '-', month) AS year_month
-- ■ Redshiftの場合はconcat関数を組み合わせるか、||演算子を用いる
-- concat(concat(year, '-'), month) AS year_month
-- year || '-' || month AS year_month
, amount
, agg_amount
, year_avg_amount
FROM
calc_index
WHERE
year = '2015'
ORDER BY
year_month
;
year_month | amount | agg_amount | year_avg_amount ------------+---------+------------+----------------- 2015-01 | 1178122 | 1178122 | 13748528 2015-02 | 1015284 | 2193406 | 13695593 2015-03 | 1165606 | 3359012 | 13689762 2015-04 | 1128273 | 4487285 | 13692506 2015-05 | 1056663 | 5543948 | 13584690 2015-06 | 1156540 | 6700488 | 13595066 2015-07 | 1268495 | 7968983 | 13560345 2015-08 | 1230429 | 9199412 | 13653186 2015-09 | 1104604 | 10304016 | 13701285 2015-10 | 1289990 | 11594006 | 13906510 2015-11 | 1245537 | 12839543 | 14033296 2015-12 | 1143558 | 13983101 | 13983101
コード例では、2015年の累計売上を計算するために、SUM関数の中でCASE式を用いて2015年 の売上のみに絞り込み、SUMウィンドウ関数を用いて累計を計算しています。
また、移動年計を計算するために、同じくSUMウィンドウ関数を用いています。ここで、当月を含む過去11ヶ月の売上を合計したいので、ROWS BETWEEN 11 PRECEDING AND CURRENT ROWの指定で、現在行から11 行前までのデータを合計しています。
コード例では、すべての月の売上が必ず存在する前提でこの構文を用いています。売上データが存 在しない月が発生するケースでは、2015年の累計売上を計算した場合と同様に、SUM関数の中でCASE式を用いて、集計対象を絞り込むとよいでしょう。
まとめ
本項で説明した通り、季節トレンドの影響を受けずに、売上の成長や衰退をさまざまな角度から、読み解くことができるのがZチャートです。常日頃からチェックする必要があるグラフではありませんが、定期的に確認しましょう。
