SQL練習場

orders を注文日の降順で確認します。まずは大規模テーブルを LIMIT 付きで見る癖をつけます。

productsをcategoryでGROUP BYし、商品マスタの偏りを確認します。

CRMや広告分析の入口として、usersの獲得チャネル分布を確認します。

strftime('%Y-%m', order_date) で月を作り、売上を集計します。

usersをprefectureで集計し、会員分布を確認します。地域別CRM施策の入口になる集計です。

ordersとstoresをJOINし、EC / Retail / Appごとの注文ボリュームを確認します。

web_sessionsをdeviceで集計し、利用端末の傾向を確認します。

ad_clicksとcampaignsをJOINし、媒体別のクリック数と広告費を確認します。

send / open / click / conversionの件数を確認し、CRMログの全体感をつかみます。

productsから価格が高い商品を確認します。商品分析ではまず上位商品の特徴を見ることが多いです。

usersとordersを結合し、ユーザー単位で注文回数・累計売上・最終購入日を出します。

order_itemsとproductsをJOINし、カテゴリ単位で売上・推定粗利を集計します。

ad_clicksの広告費とordersの売上をcampaign_id単位で結合します。

crm_eventsをsegment_id単位で集計し、open/click/conversionの比率を見ます。

usersとordersを結合し、獲得チャネルごとの注文回数・売上・平均LTVを比較します。

会員登録日と初回注文日の差を出し、初回購入転換の速さを見ます。

storesのregionを使い、地域別の注文数・売上・平均注文額を集計します。

ブランド単位で売上と粗利を出し、利益貢献の高いブランドを探します。

web_sessionsからutm_source別のセッション数と平均PVを確認します。

campaignsのobjectiveを軸に、施策目的ごとの売上貢献を比較します。

ユーザー別に最終購入日・購入回数・累計売上を作り、スコアリングします。

最終購入から90日以上経過し、過去売上が一定以上あるユーザーを抽出します。

ad_clicksとordersをuser_idで結合し、クリック後7日以内の注文を成果として扱います。

CRM接触ログと注文を組み合わせ、セグメントごとの施策成果を見ます。

初回購入月をコホートにして、各月の購入ユーザー数を追います。

セッション後7日以内の注文をコンバージョンとして、流入元別CVRを算出します。

同一注文内で一緒に買われた商品カテゴリの組み合わせを抽出します。

LTV・最終購入・購入回数から、施策対象にしやすい優良顧客を抽出します。

キャンペーン別に広告費、注文、粗利を組み合わせて、費用対効果を判断します。

高LTVだが直近購入が遠いユーザーに、CRM接触履歴を付けて復帰対象を作ります。