Zenn
🐙

Chat-gptにお題を作らせて、GCPを使ったデータ基盤＆分析モデルの作成のアウトプット その1

に公開
Google Cloud
データ分析
データ分析基盤
ChatGPT
tech

Chat-gptにお題を作らせて、GCPを使ったデータ基盤＆分析モデルの作成のアウトプット

🔥やることの目的

GCPを使って、実務レベルのデータ基盤と分析の仕組みを自分で作ってみる
データアナリストから一歩進んで、データエンジニアっぽいこともできるようになるのが目標

🧱背景とか

最近の現場だと、Cloud Storage と BigQuery を使ってデータ分析するのが普通になってきてるらしい。
だけど「見よう見まね」じゃなくて、自分の手で構築して、動かしてみたい！けど、おあつらえ向きのアウトプットの場がない。ので、chatGPTにデータ基盤構築+分析モデル構築の課題を出してもらい、GCPの無料枠活用してその課題をこなしていこうと思う

🗂️今回の課題の全容

  • Cloud Storage にCSVをアップして、BigQueryに外部テーブルとして取り込み
  • 複数のマスタ（ユーザー、商品など）と売上データを結合し、マートを作成
  • マートデータをLooker Studioで可視化してダッシュボード化
  • 分析用SQLをdbtで管理する仕組みを試す
  • 機械学習モデルを構築して、売上予測やユーザー行動の分析にもチャレンジ

最終的には、「データ収集→加工→集計→可視化→分析モデル構築」までを一通り自分の手で回せるようになるのが目標。今回のデータはGPTにつくらせたので、後述のスキーマを使えば似たようなデータ作れると思う

🧰使用技術ピックアップ

  • GCP Cloud Storage：CSVファイルの置き場所
  • BigQuery：SQLでのデータ加工・集計・可視化データの出力元
  • Looker Studio：ダッシュボード作成ツール
  • BigQuery ML：BigQuery上での機械学習モデルの構築・予測
  • Vertex AI：GCPの本格的な機械学習プラットフォーム（モデルの学習・デプロイ・API化）
  • dbt：SQLモデルのバージョン管理と依存関係の整理

✅Week1でやったこと

☁️データ基盤のベース作り

  • Cloud StorageにCSVデータをアップロード
  • BigQueryと連携してテーブルを作成
  • raw.sales テーブルを作ってデータ投入
列名 説明
order_id 注文ID (PK)
order_date 注文日
user_id ユーザーID
product_id 商品ID
quantity 購入数
price 単価
  • raw.products テーブルを作ってデータ投入
列名 説明
product_id 商品ID (PK)
product_name 商品名
category 商品カテゴリ
price 単価
release_date 発売日
  • raw.users テーブルを作ってデータ投入
列名 説明
user_id ユーザーID
name ユーザー名
region 地域
dob 生年月日
join_date 登録日

📊日別売上のマート（ビュー）作った！

売上データから、日別で「どれくらい売れてたか」をざっくり見られるビューを作成。

作ったビュー名：mart_daily_sales_summary

CREATE OR REPLACE VIEW `project_id.raw.mart_daily_sales_summary` AS
SELECT
  order_date AS sale_date,
  SUM(price * quantity) AS total_sales,
  SUM(quantity) AS total_quantity,
  COUNT(DISTINCT user_id) AS unique_users,
  SAFE_DIVIDE(SUM(price * quantity), SUM(quantity)) AS average_unit_price
FROM
  `project_id.raw.sales`
GROUP BY
  order_date
ORDER BY
  sale_date;

✅Week2でやったこと - 簡易分析

📊 商品別売上TOP10分析

商品ごとの売上を集計し、売上TOP10商品を抽出したよ
作成したクエリ：

SELECT 
  p.product_id,
  p.product_name, 
  SUM(s.price * s.quantity) AS total_sales
FROM 
  raw.sales AS s
JOIN raw.products AS p ON s.product_id = p.product_id
GROUP BY p.product_id, product_name
ORDER BY total_sales DESC
LIMIT 10;

### 📊 地域別売上分析

地域ごとの売上金額を集計し、売上が高い地域を特定したよ
作成したクエリ：
```sql
SELECT 
  users.region,
  SUM(sales.price * sales.quantity) AS total_sales
FROM raw.users
JOIN raw.sales ON  users.user_id = sales.user_id
JOIN raw.products ON sales.product_id = products.product_id
GROUP BY users.region
ORDER BY total_sales;

📊 日別売上推移

日別の売上推移を集計し、売上のトレンドを可視化したよ
作成したクエリ:

SELECT 
  order_date,
  SUM(price * quantity) AS total_sales,
  COUNT(DISTINCT(user_id)) AS unique_user,
  SUM(price * quantity) / COUNT(DISTINCT user_id) AS avg_sales
FROM raw.sales
GROUP BY order_date;

✅ Week3でやったこと - 売上予測モデルの構築（BigQuery ML）

🛠️ モデル構築のステップ

1. 特徴量の選定

  • 曜日（day_of_week）：週末や平日の売上傾向を捉える。
  • 月（month）：季節性や月ごとの売上変動を考慮。

2. モデルの作成

CREATE OR REPLACE MODEL `raw.sales_forecast_model`
OPTIONS(
  model_type='linear_reg',
  input_label_cols=['total_sales'],
  data_split_method='AUTO_SPLIT'  -- 自動で訓練データと評価データを分割
) AS
SELECT 
  EXTRACT(DAYOFWEEK FROM sale_date) AS day_of_week,
  EXTRACT(MONTH FROM sale_date) AS month,
  total_sales
FROM `raw.mart_daily_sales_summary`;

3. モデルの評価

SELECT
  r2_score AS R2,SQRT(mean_squared_error) AS rmse
FROM
  ML.EVALUATE(MODEL `raw.sales_forecast_model`,
    (SELECT
      EXTRACT(DAYOFWEEK FROM sale_date) AS day_of_week,
      EXTRACT(MONTH FROM sale_date) AS month,
      total_sales
    FROM `raw.mart_daily_sales_summary`));

精度は以下の通り。精度は求めてないから...
R2:0.020206920409252538
RMSE:28294.7762072067

4. 予測の実施

同一のデータで訓練し、同一のデータで予測するというタブーをやってるけど、あくまでお題を通したアウトプットが目的だから...

SELECT
  sale_date,
  total_sales,
  predicted_total_sales
FROM
  ML.PREDICT(MODEL `raw.sales_forecast_model`,
    (SELECT
      sale_date,
      EXTRACT(DAYOFWEEK FROM sale_date) AS day_of_week,
      EXTRACT(MONTH FROM sale_date) AS month,
      total_sales
    FROM `raw.mart_daily_sales_summary`))
ORDER BY sale_date;

✅ Week4でやったこと - 可視化かな？

Discussion