ブランド買い周りを調べたい

ユーザー07136

ユーザーID	BRAND	日付
1111	A	1月1日
2222	B	1月10日
1111	A	1月21日
3333	B	1月31日
3333	B	2月1日
2222	A	2月5日
1111	B	3月1日

このようなデータから、複数ブランドを購入しているユーザーが何人いるか？を抽出したいです。

データをピポットしてカウントだとデータ量が多く集計が困難な状況です。

いい方法ご存知の方いらっしゃいましたら教えてくださいませ。

Kmori

テーブルを横でもたない方法も考えてみました。

データセットをは下のようなものを想定しています。

 

ユーザーID	BRAND	日付	会員ランク	金額
1	A	2019/1/1	ゴールド	1000
1	B	2019/1/2	ゴールド	1000
1	C	2019/1/3	ゴールド	1000
2	A	2019/1/4	ゴールド	1000
2	B	2019/1/5	ゴールド	1000
2	C	2019/1/6	ゴールド	1000
3	A	2019/1/7	シルバー	1000
3	C	2019/1/8	シルバー	1000
4	B	2019/1/9	ブロンズ	1000
5	C	2019/1/10	ブロンズ	1000

 

こちらのDatasetを添付ファイルのようなETLで加工すると

下のようなデータセットができるので

 

ユーザーID	会員ランク	日付	基準BRAND	金額	追加購入日	うち基準BRAND以外	合計追加購入額
1	ゴールド	2019/1/1	A	1000	2019/1/1	A	1000
1	ゴールド	2019/1/2	B	1000	2019/1/2	B	1000
1	ゴールド	2019/1/3	C	1000	2019/1/3	C	1000
1	ゴールド	2019/1/1	A	1000	2019/1/2	うち基準BRAND以外	1000
1	ゴールド	2019/1/1	A	1000	2019/1/3	うち基準BRAND以外	1000
1	ゴールド	2019/1/2	B	1000	2019/1/1	うち基準BRAND以外	1000
1	ゴールド	2019/1/2	B	1000	2019/1/3	うち基準BRAND以外	1000
1	ゴールド	2019/1/3	C	1000	2019/1/1	うち基準BRAND以外	1000
1	ゴールド	2019/1/3	C	1000	2019/1/2	うち基準BRAND以外	1000
2	ゴールド	2019/1/4	A	1000	2019/1/4	A	1000
2	ゴールド	2019/1/5	B	1000	2019/1/5	B	1000
2	ゴールド	2019/1/6	C	1000	2019/1/6	C	1000
2	ゴールド	2019/1/4	A	1000	2019/1/5	うち基準BRAND以外	1000
2	ゴールド	2019/1/4	A	1000	2019/1/6	うち基準BRAND以外	1000
2	ゴールド	2019/1/5	B	1000	2019/1/4	うち基準BRAND以外	1000
2	ゴールド	2019/1/5	B	1000	2019/1/6	うち基準BRAND以外	1000
2	ゴールド	2019/1/6	C	1000	2019/1/4	うち基準BRAND以外	1000
2	ゴールド	2019/1/6	C	1000	2019/1/5	うち基準BRAND以外	1000
3	シルバー	2019/1/7	A	1000	2019/1/7	A	1000
3	シルバー	2019/1/8	C	1000	2019/1/8	C	1000
3	シルバー	2019/1/7	A	1000	2019/1/8	うち基準BRAND以外	1000
3	シルバー	2019/1/8	C	1000	2019/1/7	うち基準BRAND以外	1000
4	ブロンズ	2019/1/9	B	1000	2019/1/9	B	1000
5	ブロンズ	2019/1/10	C	1000	2019/1/10	C	1000

 

こちらを使ってカードを作ると

下のようなカードが作成できます。

※UU数はBeast Modeで「=COUNT(DISTINCT `ユーザーID`)」で作成しています。

これは例えばBを購入したユーザーは3人いて、Bを合計3千円購入しており

B（基準BRAND）を購入し、かつB（基準BRAND）以外を購入したユーザーが2人いて合計4千円を

購入しているという意味です。

ここからBのみ購入ユーザーは3人-2人で1人ということがわかります。

 

こちらもご参考にしてください。

 

 

 

 

Kmori

いろいろやり方があると思いますが、こんなETLを作成してはいかがでしょうか？

 

入力Dataset

↓

①グループ化：ユーザーごとに各BRANDを何回購入したか算出

　　識別する列：ユーザーID、BRAND

　　新しい集計列：日付のカウント

↓

②グループ化：ユーザーごとにいくつのBRANDを購入したか算出

　　識別する列：ユーザーID

　　新しい集計列：BRANDのカウント

↓

③グループ化：購入したBRAND個数別にユーザー数を算出

　　識別する列：BRANDのカウント

　　新しい集計列：ユーザーIDのカウント

↓

④Dataset出力

 

グループ化を3回行うことでブランドの購入種類ごとにユーザー数が

算出できると思います。

 

お試しください。

 

ユーザー07136

アドバイスありがとうございます。

どうもイメージどおり行かず・・・

イメージとしてはこのデータから任意の期間に

ブランドAを買っているユーザー（IDカウント）・・○名

ブランドAと他のブランドを買っているユーザー・・・○名

というデータが出ないだろうかと模索しています。

 

更にいわゆる会員ランク、合計購買金額紐付かせられるとベストなのですが・・・

ですので元データでいうと下記のようなイメージです（こちらを先に出すべきでした。申し訳ございません）

ユーザーID	BRAND	日付	会員ランク	金額
1111	A	1月1日	ゴールド	5000
2222	B	1月10日	シルバー	2500
1111	A	1月21日	ゴールド	8000
3333	B	1月31日	シルバー	1000
3333	B	2月1日	シルバー	2000
2222	A	2月5日	ゴールド	3000
1111	B	3月1日	シルバー	1000
4444	C	3月2日	ブルー	2000
5555	B	3月3日	シルバー	2000
1111	A	3月4日	ゴールド	9000

を

会員ランク	ブランド	ユーザー数	合計金額
ゴールド	Aのみ購入
ゴールド	Aと他のブランド購入
ゴールド	Bのみ購入
ゴールド	Bと他のブランド購入
ゴールド	Cのみ購入
シルバー	Aのみ購入
シルバー	Aと他のブランド購入
シルバー	Bのみ購入
シルバー	Bと他のブランド購入
シルバー	Cのみ購入

こういった形式で抽出というイメージです。

 

これっち

こんにちは。

テーブルを横持ちにしては如何でしょうか。

イメージとしては↓の感じです。

	フラグ1	フラグ2	フラグ3	フラグ4	フラグ5	フラグ1	フラグ2	フラグ3	フラグ4	フラグ5
会員ランク	Aのみ_人数	Aと他_人数	Bのみ_人数	Bと他_人数	Cのみ_人数	Aのみ_金額	Aと他_金額	Bのみ_金額	Bと他_金額	Cのみ_金額
ゴールド	1	3	0	0	0	3000	22000	0	0	0
シルバー	0	0	5	0	0		0	8500	0	0

 

対象データにフラグ設定すれば、ビーストモードで集計可能かと。

ユーザーID	BRAND	日付	会員ランク	金額	フラグ
1111	A	1月1日	ゴールド	5000	2
2222	B	1月10日	シルバー	2500	3
1111	A	1月21日	ゴールド	8000	2
3333	B	1月31日	シルバー	1000	3
3333	B	2月1日	シルバー	2000	3
2222	A	2月5日	ゴールド	3000	1
1111	B	3月1日	シルバー	1000	3
4444	C	3月2日	ブルー	2000	5
5555	B	3月3日	シルバー	2000	3
1111	A	3月4日	ゴールド	9000	2

 

ビーストモード　例）

　SUM(DISTINCT CASE WHEN `会員ランク` = 'ゴールド' AND `フラグ`= '1' THEN `金額` ELSE 0 END)

Kmori

テーブルを横でもたない方法も考えてみました。

データセットをは下のようなものを想定しています。

 

ユーザーID	BRAND	日付	会員ランク	金額
1	A	2019/1/1	ゴールド	1000
1	B	2019/1/2	ゴールド	1000
1	C	2019/1/3	ゴールド	1000
2	A	2019/1/4	ゴールド	1000
2	B	2019/1/5	ゴールド	1000
2	C	2019/1/6	ゴールド	1000
3	A	2019/1/7	シルバー	1000
3	C	2019/1/8	シルバー	1000
4	B	2019/1/9	ブロンズ	1000
5	C	2019/1/10	ブロンズ	1000

 

こちらのDatasetを添付ファイルのようなETLで加工すると

下のようなデータセットができるので

 

ユーザーID	会員ランク	日付	基準BRAND	金額	追加購入日	うち基準BRAND以外	合計追加購入額
1	ゴールド	2019/1/1	A	1000	2019/1/1	A	1000
1	ゴールド	2019/1/2	B	1000	2019/1/2	B	1000
1	ゴールド	2019/1/3	C	1000	2019/1/3	C	1000
1	ゴールド	2019/1/1	A	1000	2019/1/2	うち基準BRAND以外	1000
1	ゴールド	2019/1/1	A	1000	2019/1/3	うち基準BRAND以外	1000
1	ゴールド	2019/1/2	B	1000	2019/1/1	うち基準BRAND以外	1000
1	ゴールド	2019/1/2	B	1000	2019/1/3	うち基準BRAND以外	1000
1	ゴールド	2019/1/3	C	1000	2019/1/1	うち基準BRAND以外	1000
1	ゴールド	2019/1/3	C	1000	2019/1/2	うち基準BRAND以外	1000
2	ゴールド	2019/1/4	A	1000	2019/1/4	A	1000
2	ゴールド	2019/1/5	B	1000	2019/1/5	B	1000
2	ゴールド	2019/1/6	C	1000	2019/1/6	C	1000
2	ゴールド	2019/1/4	A	1000	2019/1/5	うち基準BRAND以外	1000
2	ゴールド	2019/1/4	A	1000	2019/1/6	うち基準BRAND以外	1000
2	ゴールド	2019/1/5	B	1000	2019/1/4	うち基準BRAND以外	1000
2	ゴールド	2019/1/5	B	1000	2019/1/6	うち基準BRAND以外	1000
2	ゴールド	2019/1/6	C	1000	2019/1/4	うち基準BRAND以外	1000
2	ゴールド	2019/1/6	C	1000	2019/1/5	うち基準BRAND以外	1000
3	シルバー	2019/1/7	A	1000	2019/1/7	A	1000
3	シルバー	2019/1/8	C	1000	2019/1/8	C	1000
3	シルバー	2019/1/7	A	1000	2019/1/8	うち基準BRAND以外	1000
3	シルバー	2019/1/8	C	1000	2019/1/7	うち基準BRAND以外	1000
4	ブロンズ	2019/1/9	B	1000	2019/1/9	B	1000
5	ブロンズ	2019/1/10	C	1000	2019/1/10	C	1000

 

こちらを使ってカードを作ると

下のようなカードが作成できます。

※UU数はBeast Modeで「=COUNT(DISTINCT `ユーザーID`)」で作成しています。

これは例えばBを購入したユーザーは3人いて、Bを合計3千円購入しており

B（基準BRAND）を購入し、かつB（基準BRAND）以外を購入したユーザーが2人いて合計4千円を

購入しているという意味です。

ここからBのみ購入ユーザーは3人-2人で1人ということがわかります。

 

こちらもご参考にしてください。