# Sheet レイヤー設計書 — Kintone × HubSpot × Looker Studio 中間処理

**目的**: 複数 Kintone アプリ (8+) + HubSpot CRM (3 オブジェクト) のデータを、Looker Studio で正しく可視化するための中間スプレッドシート (`NBS Looker Studio Data Source`、ID: `1VhGBUELKaV7-FpLES-pj0ikzGiG7X9CQWMtDGFcsWxQ`) の構造設計とクレンジング責務の整理。

**設計の原則**:
1. **重い処理は Apps Script で前処理 → Sheet には綺麗なデータだけ書く**
2. **Looker Studio は表示と簡易フィルタに専念** (= calc field 最小化)
3. **データ階層を 3 レイヤーで明確化** (Raw / Source-of-Truth / Analytics)
4. **JOIN キーの命名を全 Sheet で統一**
5. **1 アプリの取得失敗が他に影響しない try-catch 分離**

---

## 1. 3 レイヤーアーキテクチャ

```
┌──────────────────────────────────────────────────────────────────┐
│ Layer 1: Raw Data Sheets (Kintone 生データ・最低限のクレンジング)│
│    raw_kintone_385_cases / raw_kintone_385_cases_meisai 等       │
│    更新: 毎日全件 fetch、Sheet 全上書き                          │
└──────────────────────────────────────────────────────────────────┘
                              ↓
┌──────────────────────────────────────────────────────────────────┐
│ Layer 2: Source-of-Truth Sheets (HubSpot 由来・enrich 済み)     │
│    LookerStudio_Companies / Contacts / Deals                     │
│    更新: 毎朝 7:00 HubSpot Search API 経由                       │
└──────────────────────────────────────────────────────────────────┘
                              ↓
┌──────────────────────────────────────────────────────────────────┐
│ Layer 3: Analytics Sheets (集計・JOIN・Looker 直結)             │
│    LookerStudio_SalesByCase / _CohortMilestones / 等             │
│    更新: Layer 1+2 が揃った後に集計実行                          │
└──────────────────────────────────────────────────────────────────┘
                              ↓
                      Looker Studio
                  (5 + 4 ページ構成)
```

---

## 2. 全 Sheet 一覧 (Layer 別)

### Layer 1: Raw Data Sheets (Kintone 生データ)

> 命名規則: `raw_kintone_{app_id}_{snake_case_label}`
> Apps Script で Kintone REST API から取得 → 最小限のクレンジング → Sheet に書き込み。

| Sheet 名 | 元 Kintone アプリ | 1 行 = | 主キー | 更新頻度 |
|---------|----------------|--------|--------|----------|
| `raw_kintone_382_inquiries` | 382 問合せ | 1 問合せ | `inquiry_number` | 毎朝 |
| `raw_kintone_383_deals` | 383 商談 | 1 商談 | `deal_number` | 毎朝 |
| `raw_kintone_385_cases` | 385 案件管理 (親) | 1 案件 | `case_number` | 毎朝 |
| `raw_kintone_385_cases_meisai` | 385 案件管理 (meisai subtable 展開) | 1 商品明細行 | `case_number + line_no` | 毎朝 |
| `raw_kintone_377_products` | 377 商品マスタ | 1 商品 SKU | `product_code` | 毎朝 (差分のみ実質変化) |
| `raw_kintone_381_trainings` | 381 研修内容 | 1 研修 | `training_number` | 毎朝 |
| `raw_kintone_387_companies` | 387 取引先マスタ | 1 顧客 | `customer_number` | 毎朝 |
| `raw_kintone_384_quotes` | 384 見積書 (将来) | 1 見積 | `quote_number` | (未着手) |
| `raw_kintone_386_invoices` | 386 請求書 (将来) | 1 請求 | `invoice_number` | (未着手) |

> ※ 現状の Code.gs (Phase 1.5) は `LookerStudio_*` 命名で出力している。**v2 で `raw_kintone_*` へリネーム検討** (現行ダッシュボードを壊さない移行が必要)。

### Layer 2: Source-of-Truth Sheets (HubSpot 由来)

> 命名規則: `LookerStudio_{Object}` (既存命名を維持)
> HubSpot Search API で取得 → enrichment (ラベル付与 / Owner 名 / Pipeline / Stage の日本語化) → Sheet に書き込み。

| Sheet 名 | 元 HubSpot オブジェクト | 1 行 = | 主キー | 更新頻度 |
|---------|--------------------|--------|--------|----------|
| `LookerStudio_Companies` | Companies | 1 会社 | `id` | 毎朝 |
| `LookerStudio_Contacts` | Contacts | 1 コンタクト | `id` | 毎朝 |
| `LookerStudio_Deals` | Deals | 1 商談 (= Kintone 商談と 1:1) | `id` | 毎朝 |

### Layer 3: Analytics Sheets (集計・JOIN・Looker 直結)

> 命名規則:
> - 営業ダッシュボード用: `LookerStudio_{Purpose}` (Looker Studio 直結のため可視名)
> - 内部分析用 (Cohort 等): `_{PascalCase}` (アンダースコア prefix で隠す)

| Sheet 名 | 集計内容 | 1 行 = | 役割 | 元 Sheet |
|---------|---------|--------|------|---------|
| **`LookerStudio_SalesByCase`** | 案件単位集計 + 機械販売判定 | 1 案件 | 営業ランキング / 担当者別売上 / 月次販売件数 | `raw_kintone_385_cases` + `raw_kintone_385_cases_meisai` |
| **`LookerStudio_SalesRecords`** | 商品明細フラット + 商品×案件 cross | 1 商品明細行 | 商品別売上 / SKU 分析 | `raw_kintone_385_cases_meisai` + `raw_kintone_377_products` |
| `LookerStudio_OwnerMonthlyKPI` | 担当者 × 月 × 商材 (将来) | 1 行 = 担当者×月 | 個人ダッシュボード | `LookerStudio_SalesByCase` |
| `_StageSnapshots` | Deal Stage 日次スナップショット | 1 行 = Deal×日付 | Pipeline 履歴 (Pro 機能代替) | `LookerStudio_Deals` (毎日追記) |
| `_DealCurrentState` | Deal の最新状態 (= cohort 用基礎データ) | 1 行 = 1 Deal | Cohort 集計の前段 | `LookerStudio_Deals` (毎日上書き) |
| `_CohortMilestones` | コホート月次×経過日数の勝率 | 1 行 = recorded_date×cohort_month | Cohort 分析 | `_DealCurrentState` (毎日追記) |
| `_SyncLog` | 各 sync 実行ログ | 1 行 = 実行 | デバッグ・運用監視 | Apps Script 実行ログ |

---

## 3. JOIN キーの命名統一

全 Sheet で以下のキー名を統一する (英語 snake_case)。

| 統一キー名 | Kintone フィールドコード | HubSpot プロパティ | 例 |
|-----------|---------------------|------------------|----|
| `customer_number` | kokyakubangou | nbs_customer_number | "211624" |
| `inquiry_number` | toiawasebangou | nbs_inquiry_number | "12175" |
| `deal_number` | syoudanbangou | nbs_deal_number | "11164" |
| `case_number` | ankenbangou | (未保持) | "#21882" |
| `product_code` | syouhinkodo (商品マスタ) | (未保持) | "13120" |
| `quote_number` | mitsumoribangou | (未保持) | — |
| `invoice_number` | seikyuubangou | (未保持) | — |
| `training_number` | kensyuubangou | (未保持) | "2263" |
| `sales_owner` | eigyoutantousya (USER_SELECT → name) | 営業担当者_JP | "西尾 和也" |
| `department` | bumon (DROP_DOWN value) | (Pipeline で判定) | "1000　営業部：1100　直販" |

→ Apps Script でフィールド名変換は完了している。**Sheet 列名はこの統一名で出力**。

---

## 4. クレンジング箇所の役割分担

| 処理 | 場所 | 関数 / 設定 |
|------|------|------------|
| Kintone 認証 (Token / Basic) | Apps Script | `getKintoneAuthHeader(app)` |
| HubSpot 認証 (Private App) | Apps Script | `getConfig().HUBSPOT_TOKEN` |
| データ型変換 (Date → ISO) | Apps Script | `toIsoDate()` |
| 数値強制 (string → number) | Apps Script | `coerceValue()` + `NUMERIC_PROPERTIES` |
| 値マッピング (enum → ラベル) | Apps Script | `PRODUCT_LABELS`, `OWNER_ID_TO_NAME_JP` 等 |
| Owner ID → 名前 | Apps Script | `extractUserSelectName()`, `normalizeOwnerName()` |
| subtable 展開 (1→N 行) | Apps Script | `buildSalesRecordRows()` |
| 集計・グルーピング | Apps Script | `buildSalesByCaseRows()` |
| **フィルタフラグ** (`is_main_unit`, `is_direct`, `is_machine_sale`, `is_cancelled`) | Apps Script | `buildSalesByCaseRows()` 内 |
| Sheet 上の Date 自動変換抑制 | Apps Script | `setNumberFormat('@')` を setValues 前に |
| TEXT 型固定 (yyyy-MM など) | Apps Script | `cur.getRange('D:D').setNumberFormat('@')` |
| ヘッダー固定・装飾 | Sheet 手動 (任意) | 視認性のみ、ロジックに影響なし |
| 期間フィルタ・スライサー | Looker Studio | コントロール |
| 表示用配色 (ヒートマップ等) | Looker Studio | スタイル設定 |
| ピボット (cohort × days × win_rate) | Looker Studio | ピボットテーブル |

**鉄則**: 「**Looker Studio の calc field で複雑な式を書かない**」。書きたい時は Apps Script に戻して Sheet 上で計算済み列を作る。

---

## 5. 更新タイミング・依存関係

```
07:00 JST 毎朝 (Apps Script Trigger)
    │
    ├─ Step 1: syncKintoneToHubSpot()
    │     382/383 から HubSpot Contacts/Deals に増分 upsert
    │
    ├─ Step 2: syncAllToSheets()
    │     HubSpot → LookerStudio_Companies / Contacts / Deals
    │
    ├─ Step 3: syncKintoneToSalesSheets() (Phase 1.5)
    │     385 → LookerStudio_SalesRecords / SalesByCase
    │     377 → LookerStudio_Products
    │     381 → LookerStudio_Trainings
    │
    ├─ Step 4: recordStageSnapshot()
    │     LookerStudio_Deals → _StageSnapshots 追記
    │
    ├─ Step 5: recordDealCurrentState()
    │     LookerStudio_Deals → _DealCurrentState 上書き
    │
    └─ Step 6: recordCohortMilestones()
          _DealCurrentState → _CohortMilestones 追記
```

**依存関係**:
- Step 4-6 は Step 2 (HubSpot → Sheets) の後ろ必須
- Step 3 (Phase 1.5) は Step 1 とは独立 (Kintone 案件アプリは HubSpot を経由しない)
- 1 つの Step が失敗しても次の Step に try-catch で進む (= partial failure 耐性)

---

## 6. データ綺麗さの担保 (具体例)

### 案件管理 (385) のクレンジング

| 課題 | Apps Script 内の処理 |
|------|-------------------|
| `bumon` が「1000　営業部：1100　直販」のような全角複合文字列 | `is_direct = bumon.indexOf('直販') >= 0 ? 1 : 0` で簡略化 |
| `kyanseruhuragu` がチェックボックス (空 or `["キャンセル"]`) | `is_cancelled = (value || []).indexOf('キャンセル') >= 0 ? 1 : 0` |
| `eigyoutantousya` が USER_SELECT (配列 + オブジェクト) | `extractUserSelectName(field)` で文字列化 |
| 営業担当者名「西尾　和也」(全角スペース) | `normalizeOwnerName(name)` で半角化 |
| `meisai` が subtable (N 行) | `buildSalesRecordRows()` でフラット化 |
| 商品名「13120 CLEAR/SP ef★本体一式」混在 | `is_main_unit = name.indexOf('本体一式') >= 0` でフラグ化 |
| 日付フィールドが空欄 / Date 型 / 文字列の混在 | `toIsoDate()` で yyyy-MM-dd 統一 |
| 金額が string / number 混在 | `Number(value || 0)` で number 強制 |

### 商品マスタ (377)

| 課題 | クレンジング |
|------|-----------|
| 商品コードが 5 桁固定 (zero-padded?) | `String(syouhinkodo)` でゼロ落ち防止 |
| 単価が円表記 | Number 化 |
| 「本体一式」と「メンテナンス専用水」を区別 | `is_main_unit` フラグを商品マスタ側にも持たせる |

### 共通 (全 Sheet)

| 課題 | クレンジング |
|------|-----------|
| Sheet が `2024-06` を Date 型に自動変換 | `setNumberFormat('@')` を setValues の**前**に |
| 過去データのインポートで欠損値混入 | `|| ''` または `|| 0` で defaulting |
| 同一 Kintone レコードが複数同期で重複 | 主キー (case_number 等) で uniqueness 担保 (= Apps Script 内で Map に集約) |

---

## 7. エラー耐性・運用監視

### `_SyncLog` シートの構造 (推奨)

| 列 | 例 |
|----|-----|
| `recorded_at` | "2026-05-12T07:01:23+0900" |
| `sync_name` | "syncKintoneToSalesSheets" |
| `status` | "success" / "partial" / "failed" |
| `records_fetched` | 5234 |
| `records_written` | 5234 |
| `duration_ms` | 124000 |
| `error_message` | "" or "GAIA_IA02: token mismatch for app=385" |

→ Apps Script 末尾で各 sync 実行後に追記する関数 `logSyncResult()` を追加する (v2)。

### partial failure 設計

- 各 Sheet 書き込みを try-catch で個別に分離
- 1 つの Sheet が失敗しても他は進む
- Logger.log にエラー詳細を残す + `_SyncLog` に記録

### ロールバック

- Sheet は毎日全上書き (Layer 1, Layer 3) なので、`_SyncLog` で失敗を検知 → 翌朝再実行で復旧
- `_CohortMilestones` だけは追記式 → 重複行が混入する可能性 → 必要なら手動で削除

---

## 8. 将来拡張 (今は未実装、設計のみ)

### 384 見積書 / 386 請求書 の取り込み

ねらい:
- 見積書 → 受注の歩留まり分析
- 請求書 → 売上計上タイミングの分析
- 入金 → キャッシュフロー分析 (374 入金記録過去データも対象?)

実装:
- `raw_kintone_384_quotes` シート追加
- `raw_kintone_386_invoices` シート追加
- 案件 (`case_number`) で JOIN 可能

### 家庭用問合せ (1829) の取り込み

NBS は業務用+家庭用の両軸。現状は業務用 (382 問合せ) のみ。
- `raw_kintone_1829_home_use_inquiries` 追加
- 業務用 / 家庭用の問合せ動向を分離分析

### Kintone「営業担当者マスタ」(389) / 「営業エリアマスタ」(390)

- HubSpot Owner ID と Kintone 営業担当者マスタの統合管理
- `raw_kintone_389_sales_owners` 追加で運用負荷を下げる (Code.gs の `OWNER_ID_TO_NAME_JP` を自動化)

---

## 9. HubSpot との二重存在の整理

| エンティティ | 一次データソース (SoT) | HubSpot 上の扱い | Sheet 上の扱い |
|------------|--------------------|----------------|--------------|
| 顧客 | Kintone 387 取引先マスタ | Companies (同期済) | `LookerStudio_Companies` (HubSpot 経由) + `raw_kintone_387_companies` (raw) |
| 問合せ | Kintone 382 問合せ | Contacts (同期済) | `LookerStudio_Contacts` (HubSpot 経由) + `raw_kintone_382_inquiries` (raw) |
| 商談 | Kintone 383 商談 | Deals (同期済) | `LookerStudio_Deals` (HubSpot 経由) + `raw_kintone_383_deals` (raw) |
| **案件 (受注)** | **Kintone 385 案件管理** | **HubSpot Deal とは別 (Phase B で Line Items 同期予定)** | `LookerStudio_SalesByCase` + `LookerStudio_SalesRecords` (Kintone 直接、HubSpot 経由しない) |
| 商品 | Kintone 377 商品マスタ | Products (Phase B で同期予定) | `LookerStudio_Products` (Kintone 直接) |
| 研修 | Kintone 381 研修内容 | (HubSpot 同期予定なし、要検討) | `LookerStudio_Trainings` (Kintone 直接) |
| 見積 | Kintone 384 見積書 | (HubSpot 同期予定なし、Quotes は Pro+ 機能) | (未実装) |
| 請求 | Kintone 386 請求書 | (HubSpot 同期予定なし、Invoices は Commerce Hub) | (未実装) |

**判断軸**:
- 営業活動 (商談・パイプライン) → HubSpot 経由 (= 営業マネジメントの主舞台)
- 販売実績 (案件・商品) → Kintone 直接 → Sheet (= 集計の主舞台)
- 経理連携 (請求・入金) → 未統合 (= 既存システムを尊重)

→ **HubSpot は「営業活動」、Sheet/Looker は「販売実績」** という役割分担。

---

## 10. Looker Studio データソース接続マッピング

各 Looker Studio ページに使う Sheet の対応関係:

| Looker ページ | データソース Sheet | 主な集計対象 |
|------------|------------------|------------|
| 1. Overview (KPI 全体) | `LookerStudio_Deals`, `LookerStudio_Contacts`, `LookerStudio_SalesByCase` | 月次 KPI |
| 2. Pipeline 詳細 | `LookerStudio_Deals`, `_StageSnapshots` | Pipeline ファネル |
| 3. 営業担当者別 | `LookerStudio_SalesByCase` (`is_machine_sale=1` フィルタ) | Owner 別売上 |
| 4. 商材・流入経路 | `LookerStudio_Contacts`, `LookerStudio_SalesRecords` | 商材別問合せ・販売 |
| 5. Cohort 分析 | `_CohortMilestones` | キャンペーン品質 |
| 6. 営業成績表 (佐藤再現) | `LookerStudio_SalesByCase` + `_TargetSettings` (将来) | 担当×商材×達成率 |
| 7. ランキング (佐藤再現) | `LookerStudio_SalesByCase` (`is_machine_sale=1`) | TOP10 |
| 8. 個人別ダッシュボード | `LookerStudio_SalesByCase`, `LookerStudio_Trainings` + Filter | 営業さん原紙再現 |
| 9. 営業明細 (佐藤再現) | `LookerStudio_Contacts`, `LookerStudio_Deals`, `LookerStudio_SalesRecords`, `LookerStudio_Trainings` | 問合せ/商談/案件/研修 4 タブ |

---

## 11. 設計の未解決ポイント (今後決める)

- [ ] `raw_kintone_*` への命名移行 (現状 `LookerStudio_*` 混在) — 移行コスト見合いで判断
- [ ] `_TargetSettings` Sheet 作成 (佐藤様目標値入力用、Phase B)
- [ ] 384 見積書 / 386 請求書 の同期 (Phase C)
- [ ] 1829 家庭用問合せの統合 (要スコープ確認)
- [ ] HubSpot Products / Line Items への移行 (Phase B、HubSpot ネイティブ化)
- [ ] `_SyncLog` への記録ロジック実装

---

## 12. 関連ファイル

- Apps Script: `~/dev/humbulls/clients/nbs/hubspot/scripts/looker_studio_sync/Code.gs` (1331 行)
- 中間スプシ: [NBS Looker Studio Data Source](https://docs.google.com/spreadsheets/d/1VhGBUELKaV7-FpLES-pj0ikzGiG7X9CQWMtDGFcsWxQ/)
- 佐藤さんレポート: [PoC自動化テスト 営業成績レポート](https://docs.google.com/spreadsheets/d/1HGDTffTlnjd3lcT_17yioBmPaTTesOQ_pqAxprzjtI4/)
- 設計関連:
  - `~/dev/humbulls/clients/nbs/drafts/looker-cohort-setup.md`
  - `~/dev/humbulls/clients/nbs/drafts/looker-sato-layout-pages.md`
  - `~/dev/humbulls/clients/nbs/hubspot/kintone-migration/mapping-v1.0.md`