概要

本記事では、ECサイトや倉庫、実店舗ごとの在庫データをまとめて管理し、それを活用して在庫分析を行う方法を解説します。
在庫分析を行うことで、過剰在庫や欠品リスクを低減することができるだけでなく、過去の入出庫データから適正在庫数を算出することもできます。

ワークフロー導入の主なメリット

• 集計作業の自動化
  決まったスケジュールでワークフローが自動実行され、最新のデータが分析環境に反映されます。これにより、手作業でのCSVの書き出しやExcelでの手集計が不要になります。
• 現場の「入力のしやすさ」をそのまま活用
  現場の担当者は、現在使用している管理ツールに在庫情報を入力するだけで済みます。集計のために複雑な入力ルールを新たに設ける必要はありません。
• データの鮮度を保ち意思決定をスピーディに
  データウェアハウス（BigQueryなど）で計算された最新のデータや分析結果が自動的に反映されるため、管理者はダッシュボードやツールを開くだけで、常に最新の状況を確認できます。

ワークフローの設定手順

これから説明するワークフローでは以下のサービスのデータを一度BigQueryに集約したのちに、kintoneやスプレッドシートに連携するフローを作成します。

• データソース
  • ECサイト（Shopify）の商品情報
  • 実店舗（スマレジ）の在庫情報と商品情報
  • 倉庫（FTPなどのファイルサーバー）の商品情報
• データ集約ツール
  • Google BigQuery
• データ可視化ツール
  • kintone
  • Google Spreadsheets
  • Googleデータポータル (旧Looker Studio)

各ソースのデータはサンプルを抜粋したものです。実際にShopifyやスマレジから取得されるデータにはより詳細な在庫や商品情報が含まれています。

STEP1：ECサイト（Shopify）の商品情報をBigQueryに転送する

1. 転送元Shopify → 転送先Google BigQueryの転送設定を作成する
2. 転送設定STEP1のターゲットに「product_variants」を選択する
3. 転送モードは追記（APPEND）にする
4. STEP2のカラム定義で転送するカラムを選択する
5. STEP2の出力オプションにてパーティショニングを設定し、取り込み時間に基づいてパーティションを切る（任意）
6. STEP2の転送日時カラム設定で転送実行日時を入れるカラムを追加する 
   
7. BigQueryに転送したテーブルは以下。必要なカラムのみ転送する

STEP2：実店舗（スマレジ）の在庫情報をBigQueryに転送する

1. 転送元スマレジ → 転送先Google BigQueryの転送設定を作成する
2. 転送設定STEP1の取得対象に「在庫一覧」を選択する
3. 転送モードは全件洗い替えにする
4. STEP2のカラム定義で転送するカラムを選択する
5. STEP2の転送日時カラム設定で転送実行日時を入れるカラムを追加する 
6. BigQueryに転送したテーブルは以下。必要なカラムのみ転送する

STEP3：実店舗（スマレジ）の商品情報をBigQueryに転送する

1. 転送元スマレジ → 転送先Google BigQueryの転送設定を作成する
2. 転送設定STEP1の取得対象に「商品一覧」を選択する
3. 転送モードは全件洗い替えにする
4. STEP2のカラム定義で転送するカラムを選択する
5. BigQueryに転送したテーブルは以下。必要なカラムのみ転送する

STEP4：倉庫の商品在庫情報をBigQueryに転送する

FTPサーバー内の「/inventory/202601/」ディレクトリに、拠点名と日付を含んだファイル（例：stock_tokyo_20260101.csv）が、毎日定刻にアップロードされるようなデータを想定しています。

1. 転送元FTP・FTPS → 転送先Google BigQueryの転送設定を作成する
2. ジョブを実行した日付のファイルを取得するために、ファイルプレフィックスにカスタム変数を埋め込んで、動的に展開されるようにする
   • ファイルプレフィックスには/inventory/$YYYYMM$/stock_$place$_$YYYYMMDD$.csvを記載する
   • 追加するカスタム変数
     1. ディレクトの日付用
        • 変数名：$YYYYMM$
        • データ型：時刻・日付（キューイング時）
        • 日付フォーマット：%Y%m
        • タイムゾーン：Asia/Tokyo（+09:00）
     2. ファイルに含まれる日付用
        • 変数名：$YYYYMMDD$
        • データ型：時刻・日付（キューイング時）
        • 日付フォーマット：%Y%m%d
        • タイムゾーン：Asia/Tokyo（+09:00）
     3. 倉庫の場所用
        • 変数名：$place$
        • データ型：文字列
        • 値：実際に存在するディレクト名
          
3. 転送モードは追記（APPEND）にする
4. STEP2のカラム定義で転送するカラムを選択する
5. STEP2の転送日時カラム設定で転送実行日時を入れるカラムを追加する 
6. STEP2の出力オプションにてパーティショニングを設定し、取り込み時間に基づいてパーティションを切る（任意）
7. BigQueryに転送したテーブルは以下。必要なカラムのみ転送する

STEP5：データマート（SQL）でスマレジのデータを名寄せする

スマレジの在庫情報と商品情報で取得したproductIdをキーにして、在庫情報にJANコードを紐づける以下のデータマートを作成します。

作成するデータマート：

• スマレジから取得した商品情報と在庫情報のデータを紐づける

データマート作成のポイント：

• データ取得設定
  • クエリ実行モード：データ転送モード
  • 書き込みモード：追記（APPEND）
  • パーティショニングを設定し、取り込み時間に基づいてパーティションを切る（任意）

SQLクエリのサンプル：

SELECT
  s.storeId,
  s.productId,
  p.productCode AS productCode, -- 商品情報からJANコードを紐付け
  p.productName,                -- 商品情報から商品名を紐付け
  s.stockAmount,                -- 在庫数
  s.layawayStockAmount,         -- 取り置き在庫数
  s.transferred_at              -- 転送日時（いつ時点の在庫データか）
FROM
  `smaregi_stock` AS s
LEFT OUTER JOIN
  `smaregi_products` AS p
ON
  s.productId = p.productId

STEP6：データマート（SQL）で結合する

BigQueryに転送された各テーブルをUNIONで結合します。

データマート作成のポイント：

• データ取得設定
  • クエリ実行モード：データ転送モード
  • 書き込みモード：増分更新
    • キーカラム：jan_code と location_name
    • キーが一致するレコードが存在した場合の挙動：スキップ
    • 処理対象期間：範囲を指定する
      • 基準カラム：transferred_at
      • 期間：1日前 〜 

SQLクエリのサンプル：

-- 1. Shopify在庫の抽出
WITH shopify AS (
  SELECT
    barcode AS jan_code,
    display_name AS product_name,
    'Shopify-EC' AS location_name, -- 拠点名として固定
    inventory_quantity AS stock_count,
    transferred_at
  FROM `shopify_variants`
),

-- 2. スマレジ在庫（結合済みテーブルを使用）
smaregi AS (
  SELECT
    productCode AS jan_code,
    productName AS product_name,
    CAST(storeId AS STRING) AS location_name, -- 拠点名としてstoreIdを使用
    stockAmount AS stock_count,
    transferred_at
  FROM `combined_smaregi_data` -- STEP5で作成したデータマート
),

-- 3. 倉庫在庫
warehouse AS (
  SELECT
    barcode AS jan_code,
    product_name,
    location_code AS location_name, -- 拠点名として倉庫コードを使用
    available_stock AS stock_count,
    transferred_at
  FROM `warehouse_stock`
)

-- 4. 全拠点のデータをUNION（統合）して一つのマートにする
SELECT * FROM shopify
UNION ALL
SELECT * FROM smaregi
UNION ALL
SELECT * FROM warehouse

STEP7：データマート（SQL）で集計する

BigQueryに転送された各テーブルを「JANコード（バーコード）」をキーにして結合します。これにより、バラバラだったシステム間の在庫数を比較できるデータマートを作成します。

データマート作成のポイント：

• データ取得設定
  • クエリ実行モード：データ転送モード
  • 書き込みモード：全件洗い替え（REPLACE）

SQLクエリのサンプル：

• lacation_nameを日本語名に書き換える
• 在庫拠点（EC、実店舗、倉庫）ごとにグループ分けする
• 販売スピードの計算（直近1週間の在庫数を比較）
• 在庫のステータスを設定
• jon_codeとlocation_nameを組み合わせてユニークキーを作成

WITH latest_inventory AS (
  SELECT
    jan_code,
    product_name,
    location_name AS raw_location_name, -- 元のID(1, TOKYO_01など)
    -- 【在庫の拠点名を日本語表記にする】
    CASE 
      WHEN location_name = 'Shopify-EC' THEN 'ECサイト(Shopify)'
      WHEN location_name = '1' THEN '新宿店'
      WHEN location_name = '2' THEN '梅田店'
      WHEN location_name = 'TOKYO_01' THEN '東京第一倉庫'
      WHEN location_name = 'OSAKA_01' THEN '大阪中央倉庫'
      ELSE CONCAT('未定義拠点:', location_name) -- 新店が出た場合などの保険
    END AS location_display_name,
    -- 【拠点種別の付与】分析の切り口として便利
    CASE 
      WHEN location_name = 'Shopify-EC' THEN 'EC'
      WHEN location_name IN ('1', '2') THEN '実店舗'
      ELSE '倉庫'
    END AS location_type,
    stock_count AS current_stock,
    transferred_at
  FROM `inventory_union_all`
  QUALIFY ROW_NUMBER() OVER(PARTITION BY jan_code, location_name ORDER BY transferred_at DESC) = 1
),

sales_velocity AS (
  -- 直近7日間の消化数算出
  SELECT
    jan_code,
    location_name,
    MAX(IF(DATE(transferred_at) = DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY), stock_count, 0)) - 
    MAX(IF(DATE(transferred_at) = CURRENT_DATE(), stock_count, 0)) AS weekly_sales_count
  FROM `inventory_union_all`
  WHERE transferred_at >= TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY))
  GROUP BY 1, 2
)

-- 3. 結合してデータマートを作成
SELECT
  CONCAT(i.jan_code, '_', i.raw_location_name) AS unique_key,
  i.jan_code,
  i.product_name,
  i.location_display_name AS location_name, -- 日本語名を出力
  i.location_type,                          -- 拠点タイプを追加
  i.current_stock,
  COALESCE(v.weekly_sales_count, 0) AS weekly_sales_count,
  CASE 
    WHEN i.current_stock < COALESCE(v.weekly_sales_count, 0) THEN '至急補充'
    WHEN i.current_stock >= 100 AND COALESCE(v.weekly_sales_count, 0) <= 0 THEN '死蔵リスク'
    WHEN i.current_stock = 0 AND COALESCE(v.weekly_sales_count, 0) > 0 THEN '欠品'
    ELSE '適正'
  END AS optimization_status,
  i.transferred_at AS last_updated_at
FROM latest_inventory i
LEFT JOIN sales_velocity v ON i.jan_code = v.jan_code AND i.raw_location_name = v.location_name

STEP8：BIツールやMAツールで可視化させる

今回は作成したデータマートをkintoneへ転送することで、在庫管理をする担当者に分析データを連携します。

1. kintoneにアプリを作成しておく。
   ※ 各フィールドのフィールドコードはBigQueryのカラム名にしておくと良いです。
2. 転送元Google BigQuery → 転送先kintoneの転送設定を作成する
3. 転送設定STEP1のSQLで SELECT * FROM latest_inventoryのように記載してSTEP7で作成したデータマートを転送対象にする
4. 転送モードはupsertにしてアップデートキーにはunique_keyを指定する

転送を実行するとkintoneに以下の形式で転送されました。在庫ステータスが「至急補充」になっている商品に関しては、早急に対応が必要、ということがわかります。
また、最近の売れ行きも「直近7日間の消化数」を確認することで可視化できます。

STEP9：ワークフローによる自動化の設定

ここまでに作成した５つの転送設定と３つのデータマート定義を、ワークフロー機能を活用して一つの処理としてまとめます。これにより、ワークフローの実行のみで一連のデータ処理を制御することができます。 

ワークフロー作成のポイント：

• 各チャネルからの在庫情報取得タスクは同時に実行しても問題ないため、並列にフローを結びます。そして、「タスク同時実行上限数」を５などに設定することで、同タイミングで実行するジョブ数を制御します。これにより、ワークフロー全体の処理時間を短縮させることができます。
• ワークフローにスケジュールを設定することで、定期的にワークフローを実行することができます。例えば、毎日朝6:00に実行するように設定しておくことで、出勤後に最新のデータを参照することができます。
• ワークフローに通知を設定することで、API側の問題でエラーが発生した、取得するデータ内に不正な値が入ってきたなどの原因でジョブがエラーになった際に、slackやメールなどお好みの通知先にお知らせすることができます。