第3回 Zチャートで業績の推移を確認する|Tech Book Zone Manatee

マナティ

ビッグデータ分析・活用のためのSQLレシピ

第3回 Zチャートで業績の推移を確認する

分析の現場では、時系列で売上金額やユーザー数、ページビューなど、各種のデータを集計することは、高い頻度で発生します。Webサイトやサービスの状況を把握する上でも、重要度が高い作業です。本項では、ECサイトの2014年から2015年の2年間にわたる売上データをサンプルとして、時系列で売上を評価するレポートを紹介し、そのレポートを作成するためのSQLを説明します。
書籍のChapter4-1「時系列に沿ってデータを集約する」から、4-1-5「Zチャートで業績の推移を確認する」を紹介します。

 顧客に提供するサービスや商品、コンテンツには、季節によって売上が変動するものがあります。本項で紹介するZチャートは、「月次売上」「売上累計」「移動年計」の3つの指標で構成され、季節変動の影響を受けずにトレンドを読み解くことができる手法です。

●図:Zチャート

Zチャートの作成

 Zチャートを作成するには、「月次売上」「売上累計」「移動年計」の3 つの指標が必要になります。各指 標ごとに、下記に示す範囲でそれぞれ集計します。

月次売上

 売上の合計を月別に集計します。

売上累計

 該当月の売上に前月までの売上累計を合計した値となります。

移動年計

 該当月の売上に過去11 ヶ月の売上累計を合計します。

 各指標の集計済みデータであるZチャートのイメージは、下表の通りです。

●表:Zチャート集計済みイメージ

Zチャートを読み解くポイント

売上累計への着目

 月次売上が一定の場合、売上累計は直線になります。横軸で右に行くほどグラフの傾きが急になる曲線であれば、直近の売上は上昇傾向となり、緩やかになっていれば直近の売上は減少傾向となります。グラフの表示期間で、売上がどのように推移しているのかを読み解くことが可能です(次図:左)。

移動年計への着目

 前年から当年にかけて売上が一定の場合は、移動年計は直線になります。右肩上がりになっている場合、売上は上昇傾向となり、右肩下がりであれば減少傾向となります。グラフに表示されていない過去1年の長いスパンで、売上がどのように推移しているのかを読み解くことができます(次図:右)。

●図:Zチャートを読み解くポイント

さまざまな形状のZチャート

 実際にいくつかのパターンで作成したZチャートを下図に示します。それぞれのグラフが何を表して いるのかを解説します。

 左のグラフは、売上がほぼ一定で推移している状態なので綺麗な「Z」を表しています。
 続いて、中央のグラフは、期末に向かって売上が成長した状態です。月次売上と売上累計、移動年計のすべてが右肩上がりを示していることが分かります。
 そして、右のグラフは、昨年は期末に向かって売上が成長したが、翌年はその成長が止まり、2年前と同じ水準になった状態を示しています。月次売上と売上累計は直線的な形状を表していますが、移動年計が右肩下がりになっています。

●図:さまざまな形状のZチャート

Zチャートを作成するための指標を集計する

 Zチャートを作成するために必要な指標を集計するSQLを、下記のコード例に示します。
 Zチャートに必要な指標は月単位で集計するので、まずは購入ログを月別の売上に集計します。次に、各月の売上に対して、累計売上と移動年計を計算します。移動年計を計算するためには、Zチャートを表示したい月から過去1年分の売上データが必要ですが、グラフ表示の際には不要となるので、最後に必要な期間のデータだけを抽出します。

●コード:2015 年の売上に対するZチャートを作成するクエリ
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チャートです。常日頃からチェックする必要があるグラフではありませんが、定期的に確認しましょう。

著者プロフィール

加嵜 長門(著者)
株式会社DMM.comラボ所属。慶應義塾大学大学院 政策・メディア研究科修士課程修了。大学院や学生ベンチャーにて、マルチメディアデータベースを対象とした検索やレコメンドアルゴリズムの研究およびサービス開発に従事し、現在DMM.comラボではビッグデータ活用基盤の構築に携わり、SparkやSQL on Hadoopを用いたレコメンド機能、ビッグデータ活用の研究開発を担当。 共著に『詳解Apache Spark』(技術評論社)。
田宮 直人(著者)
データコンサルタント。エンジニアとして大手新聞社の関連サービス、求人サービス、コミュニティサービスの開発に携わり、株式会社サイバーエージェント在籍時にデータアナリストへ転身、株式会社DMM.comラボではマーケティング開発部マネージャーとしてビッグデータ部を立ち上げる。現在はフリーランスとして、データの解析のみならず、データ解析環境の設計・構築、ログの設計、レコメンドAPIの作成など、データに関連する業務全般を担当している。