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

ユーザーIDBRAND日付
1111A1月1日
2222B1月10日
1111A1月21日
3333B1月31日
3333B2月1日
2222A2月5日
1111B3月1日

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

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

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

Best Answer

  • Kmori
    Kmori ⚪️
    Accepted Answer

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

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

     

    ユーザーIDBRAND日付会員ランク金額
    1A2019/1/1ゴールド1000
    1B2019/1/2ゴールド1000
    1C2019/1/3ゴールド1000
    2A2019/1/4ゴールド1000
    2B2019/1/5ゴールド1000
    2C2019/1/6ゴールド1000
    3A2019/1/7シルバー1000
    3C2019/1/8シルバー1000
    4B2019/1/9ブロンズ1000
    5C2019/1/10ブロンズ1000

     

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

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

     

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

     

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

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

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

    カード画像.PNG

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

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

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

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

     

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

     

     

     

     

Answers

  • Kmori
    Kmori ⚪️

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

     

    入力Dataset

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

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

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

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

      識別する列:ユーザーID

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

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

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

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

    ④Dataset出力

     

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

    算出できると思います。

     

    お試しください。

     

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

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

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

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

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

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

     

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

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

    ユーザーIDBRAND日付会員ランク金額
    1111A1月1日ゴールド5000
    2222B1月10日シルバー2500
    1111A1月21日ゴールド8000
    3333B1月31日シルバー1000
    3333B2月1日シルバー2000
    2222A2月5日ゴールド3000
    1111B3月1日シルバー1000
    4444C3月2日ブルー2000
    5555B3月3日シルバー2000
    1111A3月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のみ_金額
    ゴールド13000300022000000
    シルバー00500 0850000

     

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

    ユーザーIDBRAND日付会員ランク金額フラグ
    1111A1月1日ゴールド50002
    2222B1月10日シルバー25003
    1111A1月21日ゴールド80002
    3333B1月31日シルバー10003
    3333B2月1日シルバー20003
    2222A2月5日ゴールド30001
    1111B3月1日シルバー10003
    4444C3月2日ブルー20005
    5555B3月3日シルバー20003
    1111A3月4日ゴールド90002

     

    ビーストモード 例)

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