📦ECの在庫回転率をGA4×BigQueryで商品別に可視化して死に筋を特定する

はじめに：死に筋在庫がキャッシュを圧迫する課題

ECを運営していると、いつの間にか倉庫の片隅に「動かない在庫」がたまっていきます。発注したときは売れると思っていたのに、半年経っても箱が開いていない。これがいわゆる死に筋在庫です。

死に筋在庫の厄介なところは、損益計算書（PL）には現れにくく、キャッシュフローを静かに圧迫する点にあります。仕入れに使ったお金は在庫という形で倉庫に固定され、売れるまで現金には戻りません。在庫が増えれば増えるほど、本来なら次の仕入れや広告に回せたはずの資金が眠ってしまうわけです。

「売れていない商品はなんとなく分かる」という感覚はあっても、いざ「どの商品から手を打つべきか」を数字で説明しようとすると、案外むずかしいものです。受注データはネットショップの管理画面、在庫データはスプレッドシート、閲覧データはGA4と、データがバラバラに散らばっているからですね。

この記事では、これらのデータをBigQueryで商品別に結合し、在庫回転率とGA4の閲覧・カート・購入の行動データを突き合わせて死に筋を特定する方法を紹介します。中小ECや個人事業主の方でも、手元のデータで再現できる構成を意識しました。

💡 補足 この記事のSQLや数字はすべて「考え方を示すための例」です。実際のテーブル名・カラム名・しきい値は、お使いのショップカートやGA4のエクスポート設定に合わせて読み替えてください。

在庫回転率の考え方

基本の計算式

在庫回転率は、ある期間に在庫が何回入れ替わったかを示す指標です。一般的には次の式で計算します。

在庫回転率（回） = 売上原価 ÷ 平均在庫（原価ベース）

たとえば、ある期間の売上原価が120万円、平均在庫が30万円だったとすると、在庫回転率は4回になります。期間中に在庫が4回入れ替わった、というイメージですね。

回転率が高いほど在庫がよく動いている＝資金効率が良い、低いほど在庫が滞留している、と読みます。

回転日数のほうが直感的

回転率の「回」という単位はやや抽象的なので、現場では在庫回転日数に変換したほうが分かりやすいことが多いです。

在庫回転日数（日） = 集計期間の日数 ÷ 在庫回転率

90日間で回転率が4回なら、回転日数は約22.5日。つまり「いまの在庫は平均22.5日で売り切れるペース」という意味になります。これが180日、365日と伸びていくほど、その商品は死に筋に近づいているサインです。

💡 補足 売上原価がすぐ取れない場合は、売上数量と原価単価から「売上原価 ＝ 数量 × 原価単価」を組み立てます。原価が分からない商品は、いったん売上金額と在庫金額（販売価格ベース）で代用しても、滞留の傾向はつかめます。

データの持ち方

商品別に在庫回転率を出すには、最低限つぎの3種類のデータが必要です。

• 受注（売上）データ: いつ・どの商品（SKU）が・何個・いくらで売れたか
• 在庫データ: 日次または期末時点の、SKUごとの在庫数量・原価
• 行動データ（GA4）: 商品ページの閲覧、カート投入、購入イベント

このうち在庫データは「期首と期末の平均」で十分なことが多いですが、できれば日次スナップショットを残しておくと、後から任意の期間で平均在庫を計算できて便利です。

GA4×受注/在庫データをBigQueryで結合する

ここからは、GA4のBigQueryエクスポート（events_* テーブル）と、受注テーブル・在庫テーブルを商品別に結合していきます。

前提とするテーブル構成（例）

テーブル	主なカラム（例）
analytics_123456.events_*	event_name, event_date, items（商品配列）
mart.orders	order_date, sku, quantity, cost（原価）
mart.inventory_daily	snapshot_date, sku, stock_qty, unit_cost

GA4の items は配列（RECORD）なので、UNNEST で展開してSKU単位に開きます。商品の紐付けキーには item_id（GA4側）と sku（受注・在庫側）を使う想定です。

ステップ1：GA4から商品別の行動指標を集計する

まずは商品ごとに、閲覧数（view_item）・カート投入数（add_to_cart）・購入数（purchase）を集計します。

ここで1つ注意が必要です。GA4の purchase イベントは、1回の注文に複数商品が含まれていると items 配列に複数行ぶら下がります。そのため UNNEST(items) したあとに purchase イベントを数えると、件数が「注文数」ではなく「明細行（SKU）数」に膨らんでしまいます。閲覧（view_item）やカート投入（add_to_cart）は通常1イベント＝1商品なので問題になりにくいのですが、購入だけは扱いを分けるのが安全です。

そこでこの記事では、商品別CVRをつぎのように定義します。

• 分母：その商品の view_item イベント数（閲覧）
• 分子：その商品が含まれた purchase 内の明細数＝その商品の購入件数（注文単位ではなく商品単位）

つまり「その商品を見た回数のうち、その商品が実際に買われた件数の割合」です。注文単位のCVRを出したい場合は、後述のように transaction_id のユニーク数で数え方を切り替えてください。

-- GA4: 商品別の行動指標（直近90日の例）
-- view_item / add_to_cart はイベント単位、purchase は items 明細単位で数える
WITH events AS (
  SELECT
    e.event_name,
    e.user_pseudo_id,
    e.items,
    -- 注文を一意に識別するキー（purchase イベントに入る）
    (SELECT ep.value.string_value
       FROM UNNEST(e.event_params) AS ep
      WHERE ep.key = 'transaction_id') AS transaction_id
  FROM `analytics_123456.events_*` AS e
  WHERE _TABLE_SUFFIX BETWEEN
        FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
        AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND e.event_name IN ('view_item', 'add_to_cart', 'purchase')
),
-- items を展開してSKU単位に開く
ga4_items AS (
  SELECT
    item.item_id        AS sku,
    events.event_name,
    events.user_pseudo_id,
    events.transaction_id
  FROM events,
       UNNEST(events.items) AS item
),
ga4_summary AS (
  SELECT
    sku,
    COUNTIF(event_name = 'view_item')   AS view_cnt,
    COUNTIF(event_name = 'add_to_cart') AS cart_cnt,
    -- 商品別の購入「件数」（明細単位）
    COUNTIF(event_name = 'purchase')    AS purchase_cnt,
    -- 注文単位で見たいときはこちら（その商品が含まれた注文数）
    COUNT(DISTINCT IF(event_name = 'purchase', transaction_id, NULL))
                                        AS purchase_orders,
    COUNT(DISTINCT user_pseudo_id)      AS users
  FROM ga4_items
  GROUP BY sku
)
SELECT * FROM ga4_summary

purchase_cnt（明細単位）と purchase_orders（注文単位）を両方持っておくと、用途に応じて分子を選べます。商品別CVRは前者、注文への寄与を見たいときは後者、というふうに使い分けると混乱しません。

⚠️ 注意 events_* への日付フィルタは、必ず _TABLE_SUFFIX で絞ってください。これを忘れると全期間をスキャンしてしまい、スキャン量＝課金が一気に膨らみます。event_timestamp だけで絞ってもスキャン量は減らない点に気をつけましょう。

ステップ2：受注・在庫から回転率を計算する

つぎに、受注データから売上原価を、在庫データから平均在庫を出します。平均在庫は日次スナップショットの平均で近似します。

-- 受注: 期間中の売上原価
WITH cogs AS (
  SELECT
    sku,
    SUM(quantity)        AS sold_qty,
    SUM(quantity * cost) AS cogs_amount
  FROM `mart.orders`
  WHERE order_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
                       AND CURRENT_DATE()
  GROUP BY sku
),
-- 在庫: 期間中の平均在庫（原価ベース）
avg_inv AS (
  SELECT
    sku,
    AVG(stock_qty)             AS avg_stock_qty,
    AVG(stock_qty * unit_cost) AS avg_stock_amount
  FROM `mart.inventory_daily`
  WHERE snapshot_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
                          AND CURRENT_DATE()
  GROUP BY sku
),
turnover AS (
  SELECT
    COALESCE(c.sku, i.sku) AS sku,
    c.cogs_amount,
    i.avg_stock_amount,
    i.avg_stock_qty,
    -- 在庫回転率（回）
    SAFE_DIVIDE(c.cogs_amount, i.avg_stock_amount) AS turnover,
    -- 在庫回転日数（日）
    SAFE_DIVIDE(90, SAFE_DIVIDE(c.cogs_amount, i.avg_stock_amount)) AS turnover_days
  FROM cogs AS c
  FULL OUTER JOIN avg_inv AS i USING (sku)
)
SELECT * FROM turnover

SAFE_DIVIDE を使うことで、売上ゼロや在庫ゼロのSKUでもゼロ除算エラーにならず NULL で返ります。死に筋ほど分母・分子がゼロに近いので、ここは安全な割り算にしておくのが実務的です。

ステップ3：行動指標と回転率を1つのテーブルに結合する

最後に、ステップ1（GA4）とステップ2（受注・在庫）をSKUで結合し、判定用の1枚にまとめます。ここでは前の2ステップを ga4_summary・turnover という同名のCTEとして再利用する前提で書いています（ビューにしている場合はそのビュー名に読み替えてください）。

CVRの定義はステップ1で決めたとおり、分母を view_cnt（その商品の閲覧数）、分子を purchase_cnt（その商品の購入件数＝明細単位）とします。注文単位で見たいときは分子を purchase_orders に差し替えてください。

SELECT
  COALESCE(g.sku, t.sku) AS sku,
  -- GA4行動
  g.view_cnt,
  g.cart_cnt,
  g.purchase_cnt,
  SAFE_DIVIDE(g.cart_cnt, g.view_cnt)     AS cart_rate,   -- 閲覧→カート率
  SAFE_DIVIDE(g.purchase_cnt, g.view_cnt) AS cvr,         -- 閲覧→購入率（商品単位）
  -- 在庫
  t.avg_stock_qty,
  t.turnover,
  t.turnover_days
FROM ga4_summary AS g          -- ステップ1の結果
FULL OUTER JOIN turnover AS t  -- ステップ2の結果
  ON g.sku = t.sku
ORDER BY t.turnover_days DESC

💡 補足 ga4_summary と turnover は、それぞれステップ1・ステップ2のクエリをCTEやビューにした想定です。1本のクエリにまとめるなら、ステップ1の WITH events AS (...) , ga4_items AS (...) , ga4_summary AS (...) とステップ2の cogs / avg_inv / turnover を1つの WITH に連結し、最後にこの SELECT を置けば通しで動きます。日次で自動更新したい場合は、このクエリを定期実行で1枚のデータマートに落としておくと、レポート側が軽くなります。仕組み化のやり方はBigQueryのスケジュールドクエリでデータマートを毎日更新するで解説しています。

死に筋判定と打ち手

データが1枚にまとまったら、いよいよ「どの商品が死に筋か」を判定します。死に筋といっても状態はいくつかに分かれ、それぞれ打ち手が変わります。

パターンごとに分けて考える

パターン	行動データ	在庫の状態	解釈
A. 見られて売れない	PV多い／CVR低い	滞留（回転日数長い）	商品ページに問題
B. そもそも見られない	PV少ない	滞留	露出・導線に問題
C. 過剰在庫	PV・売上ともに普通	滞留	仕入れすぎ
D. 健全	PV・CVRともに高い	回転速い	維持・追加仕入れ

判定をSQLで表すと、たとえばこんなイメージです（しきい値はあくまで例）。

SELECT
  sku,
  CASE
    WHEN turnover_days >= 180 AND view_cnt >= 100 AND cvr < 0.005
      THEN 'A: 見られて売れない（ページ改善）'
    WHEN turnover_days >= 180 AND view_cnt < 30
      THEN 'B: 見られていない（露出改善）'
    WHEN turnover_days >= 180
      THEN 'C: 過剰在庫（値下げ・処分）'
    ELSE 'D: 健全'
  END AS deadstock_type
FROM product_overview   -- ステップ3の結果
ORDER BY turnover_days DESC

パターン別の打ち手

A：見られているのに売れない（回転日数180日・PV100以上・CVR0.5%未満などが目安） 閲覧は集まっているのに買われていないので、原因は商品ページ側にある可能性が高いです。写真の追加、説明文・サイズ表記の見直し、価格・送料の再検討、レビューの掲出などが効きます。閲覧があるということは需要の入口は存在するので、改善のリターンが大きいパターンです。なお「閲覧後に離脱している」より詳しい分析はGA4×BigQueryで商品ページの離脱率を分析するが参考になります。

B：そもそも見られていない 入口の問題です。カテゴリ導線、サイト内検索、特集やトップページへの掲載、外部流入（SEO・SNS・広告）を見直します。ページを直しても、人が来ていなければ売れません。

C：閲覧も売上も普通だが在庫が多すぎる 過剰在庫です。これ以上はページ改善では動きにくいので、セット販売・値下げ・アウトレット・福袋などで現金化を優先します。在庫として持ち続けるコスト（保管料・陳腐化リスク）と、値下げによる損失を天秤にかけて判断します。

D：健全 回転が速く、よく見られ、よく買われている商品です。欠品させないよう在庫を厚めに持ち、関連商品としての露出を増やすなど、攻めの施策を回します。

⚠️ 注意 季節商品やセール直後は、回転日数が一時的にゆがみます。「いまだけ売れた／売れなかった」だけで死に筋と決めつけず、前年同期や複数期間で見るのが安全です。新商品も、販売開始からの経過日数が短いと回転日数が長く出やすいので、判定対象から外すか別扱いにしましょう。

まとめ

死に筋在庫は「なんとなく分かる」で放置すると、じわじわとキャッシュを圧迫していきます。本記事で見たように、

• 在庫回転率（売上原価 ÷ 平均在庫）と回転日数で滞留を数値化する
• GA4の閲覧・カート・購入データをBigQueryでSKU単位に結合する
• 「見られて売れない／見られていない／過剰在庫／健全」に切り分ける

という流れに落とし込めば、感覚ではなくデータで「どの商品から手を打つか」を説明できるようになります。

最初から完璧なデータ基盤を作る必要はありません。まずは手元の受注データと在庫データ、GA4のエクスポートをBigQueryに集め、商品別の回転日数を一覧にするところから始めてみてください。そこに行動データを重ねるだけで、打ち手の優先順位がぐっと見えやすくなります。

関連記事もあわせてどうぞ。

• GA4×BigQueryで商品ページの離脱率を分析する
• BigQueryのスケジュールドクエリでデータマートを毎日更新する