データ分析プラクティス①「DBからSQLでデータ抽出&BIツールで可視化」

今回は実際のデータ分析の業務を見据えたスキルの自己研鑽のために、

1. Bigquery
2. SQL
3. Tableau

の3つを使用して、

「DBからSQLでデータ抽出&BIツールで可視化」

を経験することを目標に取り組んでいきます。

使用するデータセット

Kaggleから見つけたブラジルのifoodというフード通販サービスの会社のキャンペーンによる購買データを使用します。
https://www.kaggle.com/datasets/jackdaoud/marketing-data/data

◆データセットの先頭10行

各特徴量の内容を書くと記事が間伸びするので、
ざっくりいうと各顧客の6回分のキャンペーンの購買結果と、
収入や家族構成や過去2年間の各商品の購入量などのデータです。

BigQueryにデータをアップ

kaggleからダウンロードしたCSVをBigQueryにアップ。
プレビューでしっかり読み込めてるのを確認。

続いて、SQLのクエリも試しに書いてみます。
Incomeのカラムのデータ型を確認するためのクエリを実行してみます。

floatのようですね。

SQLでデータ抽出

後でtableauでデータの探求はできますが、
SQLでクエリを書いてBigQuery上でデータをいじってみます。

◆最後に行われたキャンペーン(Responseカラム)の成功数を集計。

SELECT 
  Response, 
  COUNT(*) AS count_customers
FROM `datapractice-454014.ifood_dataset_fromKaggle.ifood_data`
GROUP BY Response;

結果

値が1の方が、購入に至った顧客です。
まだ何とも言えないので、もう少し条件を練ってみます。

◆次に収入でグループ分けした上で最後に行われたキャンペーンの購入率を集計。

SELECT 
  ROUND(Income, -4) AS income_group,  
  COUNT(*) AS total_customers,
  SUM(Response) AS accepted_customers,
  SUM(Response) / COUNT(*) AS acceptance_rate
FROM `datapractice-454014.ifood_dataset_fromKaggle.ifood_data`
GROUP BY income_group
ORDER BY income_group;

結果

高収入のグループほどキャンペーンでの購入率が高い傾向にあることが分かります。

データ前処理

このままtableauに移行する前に、データの状態も再確認します。
Kaggleから取得したデータということもあり既に整えられているデータなのですが、
さらにクエリを書く経験のために各カラムのデータ型や欠損値の有無を確認してみます。
スクショだと記事が間伸びするので、ここからは転記になります。

SELECT column_name, data_type
FROM `datapractice-454014.ifood_dataset_fromKaggle.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'ifood_data';

結果(一部)

全カラム intかfloatで、stringは含まれませんでした。
後でtableauで読み込む時にも問題なさそうです。
次に欠損値を確認します。

SELECT 
  COUNTIF(Income IS NULL) AS missing_income,
  COUNTIF(Kidhome IS NULL) AS missing_Kidhome,
  COUNTIF(Teenhome IS NULL) AS missing_Teenhome,
  COUNTIF(Recency IS NULL) AS missing_Recency,
  COUNTIF(MntWines IS NULL) AS missing_MntWines,
FROM `datapractice-454014.ifood_dataset_fromKaggle.ifood_data`;

結果

全カラム確認しましたが、欠損値は見つかりませんでした。
やってみて実感しましたが、PythonならPandasのisnullで一発なのに、SQLだと欠損値の確認が大変でした。

Tableauで可視化

最後にBigQueryをTableauに接続して、可視化作業と簡単に分析してみます。
分析の目的は「次回のキャンペーン戦略に活用できるインサイトを探る」です。

先ほどSQLでデータの一部だけを抽出する時に高収入であることがキャンペーンでの購買につながっていることが分かりました。まずはその点を可視化してみます。

◆顧客属性にあたるIncome(収入)とAge(年齢)のカラムでバブルチャートを作成。
色分けは「キャンペーンに参加したことが過去にあるorない」で行いました。これに直接該当するカラムはないのですが、Tableau上でもクエリを書いて新規カラムを作れるみたいです。下記のSQLを書きました。

IF [AcceptedCmp1] = 1 OR [AcceptedCmp2] = 1 OR 
   [AcceptedCmp3] = 1 OR [AcceptedCmp4] = 1 OR 
   [AcceptedCmp5] = 1 OR [Accepted Cmp Overall] = 1 
THEN "Participated"
ELSE "Not Participated"
END

オレンジ色の点がキャンペーンに一度でも参加したことある群なので、やはり高収入層へのキャンペーン訴求が効果ありそうです。

次に特徴量の中に、購買経路としてWeb or Store or Catalogの3つがあります。
経路によってキャンペーン参加の傾向があるかもしれません。

◆各媒体の総売り上げの差異を確認。

Storeが一番ですね。とはいえ、まとめて総数を出力したNum Web Visits Monthも目に止まります。Webで見て店舗で買うということもありますからね。

◆Web閲覧回数とキャンペーン参加の関係。

最上段のResponseは6回目のキャンペーンのことです。初回と5回目の参加率が気にはなりますが、概ねWeb閲覧回数が多いほどキャンペーンからコンバージョンしてる傾向にありそうです。なのでキャンペーンを打ち出す媒体はWeb中心が良いでしょう。

最後にこの会社が扱うフードが肉製品・魚製品・スイーツ・ワイン・フルーツの5つにカテゴリされてます。
売れ行きの商品は何でしょうか。

◆最も売れている商品

ワインと肉製品の２強ですね。ここまでハッキリしてるのであれば、キャンペーン参加と何か関係あるかもしれません。

◆各商品の購入量と各CPへの参加傾向。

ワイン

魚製品

フルーツ

肉製品

スイーツ

購入数の差異があったり、ピークの関係でグラフが縮小されてる分、余計に違いがはっきりと感じてしまいますが、
ワインでキャンペーン参加の傾向が高いことより、低人気の商品の購入者が明確にキャンペーンに参加していない傾向があることが分かることが大きいですね。そう言った裏付けからも、ワインあるいは次点の肉製品を絡ませたキャンペーンが売り上げのベースアップに繋がるかもしれません。今回は肉製品をチョイス。

今回の分析に基くインサイト

次回キャンペーンは、Web媒体を中心とした高収入者をターゲットに肉製品を絡ませた内容が売り上げアップに繋がる。

こちらの提案を持って、今回の自己研鑽は以上になります。

あとがき

内容や分析の深度はさておき、今まで自分の中でただの概念だった「Bigquery」や「tableau」が
今回の経験で「手に取って使えるツール」に昇華した気分です。
Tableauの操作に慣れるのに苦労しましたが、ドラッグアンドドロップだけで図表が簡単に出せるのは楽しかったですね。Pythonの方がコードさえ書けば柔軟に特徴量をいじって可視化しやすいかもと思う瞬間はありましたが、DBと繋げてダッシュボードが作れるメリットや、データさえ整っていれば誰でもお手軽に可視化&分析できるのはPyhtonにはない強みだなと思いました。
とはいえお高いソフトなので、個人では手が出しづらいのが惜しいですね。
あと、オープンソースのデータセットとはいえ、高所得者層ほどキャンペーン参加率が高いのは何となく肌感覚と一緒ですよね。
ポイントとかも結局使うお金が大きい方が還元料が多いですし。

何か間違っている部分やアドバイスありましたら、ぜひコメントお願いします。
ここまでお読みいただきありがとうございました。