1行ずらし結合→行間差分の計算

投稿者:

データを愛する皆さん、こんにちは。

「Data Processing with Tableau on my Mind(Tableauを意識したデータ加工)」をシリーズでお届けします。

1行ずらし結合(英名:1 Row Shifted Join)をご紹介します。

これにより、

  • データの1行1行の差分を測定し
  • その差分測定値をメジャーとして残し
  • ビン作成によるディメンジョン(カテゴリ)化

を行うことができます。

今回使用するデータはエクセルです。データベースがデータソースであるケースへの対応は近日中にご紹介いたします。

では、要件を説明します。

↓のデータは1人の顧客の購入履歴を示しています。

12行ありますが、1度に複数の商品が購入されていることもり、8回の購入があります。

カラムにオーダー日があり、それぞれの購入日を認識できます。ここから測定したい数値は

「各購入日における、前回購入日からの経過日数」です。 例えば、2回目の購入日(2014/8/24)と初回購入日(2014/1/3)の経過日数は233日です。これを3回目以降の各購入日に対しても測定したいという要件です。

このデータの状態でも、TableauのLOD表現により、

  • 初回購入日
  • 直近購入日

を測定することは可能です。

また、Tableauの表計算のLOOKUP関数により、前回購入日との経過日数は測定可能です。しかしながら、Tableau表計算の計算値からは以下を行えません。

  • メジャーとして残す
  • そのメジャーからビン(カテゴリ化された数値)の作成し、ディメンジョンとして残す

従いまして、Tableau表計算の計算値は、それが計算されたビューでしか使えません。サッカーに例えて言うと、シュートを打つしかない状況です。

このように、行と行を跨ぐ計算は比較的難しいものです。これに対して、列と列の計算は、「列A + 列B」という風に、比較的簡単に定義することが可能です。また、メジャーとしても残せます。これを踏まえて、行間を参照するのではなく、列間参照すれば事たりる形にデータ加工します。

データ加工後の結果イメージからご欄頂くと、↓

オーダー日が2列になっており、左側に前回、右側に今回のオーダー日が表示されています。左側と右側のオーダー日は1行ずれている点にご注目ください。この2つの列からTableauのDATEDIFF関数により、容易に経過日数を算出することが可能です。

では、データ加工のステップは以下のとおりです。

  1. 元データを正しくソートする(この場合は顧客ID、オーダー日)
  2. 元データに連番を振る(今回は、同一オーダー日が複数存在するケースがある場合は、カウントアップしない、また、顧客IDが切り替わった時点で連番を1に戻す
  3. 連番が付与されたデータを自己結合する。自己結合する

まず、連番を付与するエクセル計算式は、

=IF( F7<>F6, 1, IF(AND(F7=F6, C7<>C6), V6+1, V6))

です。ここでは、F列に顧客ID、C列にオーダー日が入っており、エクセルシートの6行目と7行目を参照し、7行目の連番を取得しています。

続いて自己結合ですが、

Tableauのクロスデータジョインを使い、同じエクセルシート同志を結合させてます。

結合条件は、(1)顧客IDと(2)Seq_NoとSeq_No-1です。

Tableauでは結合句に計算フィールドの結果を使用することができます。 ここでは、単純にSeq_No -1、です。

この後、算出されたオーダー間隔日数から、多角的な分析が可能となりますね。

各顧客について、オーダー間隔の最小、平均、最大を並べていくと、↓のようになります。

分析の展開については、様々なアイデアが膨らむとおもいますが、いくつかの例をTableauパッケージブック形式でTableau Publicにパブリッシュしておりますので、併せてご欄下さい。

1行ずらし結合からのオーダー間隔算出

活用用途が広く、役に立つテクニックかと思います。

ご参考になれば大変うれしいです。

ロックンロール!

Tableau小口

 

返信を残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です