# 帳票設計書 15-トップソースレポート

## 概要

本ドキュメントは、Ghost CMSにおけるトップソースレポートの設計仕様を定義する。このレポートは、会員獲得に貢献した参照元（リファラー）の統計情報を、サインアップ数、有料コンバージョン数、MRR（月間経常収益）の観点で集計し、JSON形式でAPIエンドポイント経由で提供する。

### 本帳票の処理概要

本帳票は、会員がどの参照元（Google、Facebook、Twitter等）からサイトに訪れ、サインアップや有料転換に至ったかを分析するための統計データを提供する。アトリビューション（帰属）分析により、マーケティング施策の効果測定を支援する。

**業務上の目的・背景**：会員獲得はパブリッシングビジネスの成長において重要な指標である。どの流入元が効果的に会員を獲得しているかを把握することで、マーケティング予算の最適配分、効果的なチャネルへの注力、ROI（投資収益率）の改善が可能になる。本レポートは、参照元別のサインアップ数、有料コンバージョン数、MRRを一覧化し、成長ドライバーの特定を支援する。

**帳票の利用シーン**：管理画面のStats（統計）セクションでGrowth（成長）分析を行う際に利用される。指定期間内で最も会員獲得に貢献した参照元を確認することで、効果的なマーケティングチャネルを特定できる。

**主要な出力内容**：
1. 参照元（source）- 正規化されたソース名
2. サインアップ数（signups）- 無料会員登録数
3. 有料コンバージョン数（paid_conversions）
4. MRR（mrr）- 月間経常収益への貢献額

**帳票の出力タイミング**：管理画面でトップソースGrowth APIエンドポイント（`GET /ghost/api/admin/stats/top-sources-growth`）にアクセスした際にリアルタイムで生成される。

**帳票の利用者**：サイト管理者、マーケティング担当者、グロースハッカー

## 帳票種別

統計レポート / ランキング表（JSON形式）

## 利用画面

| 画面No | 画面名 | URL/ルーティング | 出力操作 |
|--------|--------|-----------------|---------|
| - | Stats画面 | `/ghost/#/stats/` | Growthセクション表示時に自動取得 |
| - | Top Sources Growth API | `GET /ghost/api/admin/stats/top-sources-growth` | APIリクエスト |

## 出力形式

### 基本仕様

| 項目 | 内容 |
|-----|------|
| ファイル形式 | JSON |
| 用紙サイズ | N/A（API出力） |
| 向き | N/A |
| ファイル名 | N/A（HTTPレスポンス） |
| 出力方法 | API経由でJSONレスポンス |
| 文字コード | UTF-8 |

### レスポンス構造

```json
{
  "data": [
    {
      "source": "string",
      "signups": "number",
      "paid_conversions": "number",
      "mrr": "number"
    }
  ],
  "meta": {}
}
```

## 帳票レイアウト

### レイアウト概要

JSON配列形式で参照元別の会員獲得統計を返却する。指定されたソート順（デフォルト: signups降順）で配列が構成される。

```
{
  "data": [
    ┌─────────────────────────────────────┐
    │  source: Google                     │
    │  signups: 150                       │
    │  paid_conversions: 25               │
    │  mrr: 125000                        │
    └─────────────────────────────────────┘
    ...（複数レコード）
  ],
  "meta": {}
}
```

### 明細部

| No | 項目名 | 説明 | データ取得元 | 表示形式 | 備考 |
|----|-------|------|-------------|---------|------|
| 1 | source | 参照元（正規化済み） | members_created_events.referrer_source | 文字列 | 正規化マップ適用後 |
| 2 | signups | サインアップ数 | members_created_events（重複排除） | 整数 | 有料転換していない会員 |
| 3 | paid_conversions | 有料コンバージョン数 | members_subscription_created_events（重複排除） | 整数 | |
| 4 | mrr | MRR（月間経常収益） | members_paid_subscription_events.mrr_delta | 整数（セント単位） | |

## 出力条件

### 抽出条件

| 条件名 | 説明 | 必須 |
|-------|------|-----|
| date_from | 開始日（YYYY-MM-DD形式） | No |
| date_to | 終了日（YYYY-MM-DD形式） | No |
| timezone | タイムゾーン（デフォルト: UTC） | No |
| order | ソート順（signups desc, paid_conversions desc, mrr desc, source desc） | No |
| limit | 取得件数上限（デフォルト: 50） | No |

### ソート順

| 優先度 | 項目 | 昇順/降順 |
|-------|------|---------|
| 1 | 指定されたorder（デフォルト: signups） | 降順（常に降順） |

### 改ページ条件

N/A（JSON形式のため改ページなし。limit パラメータで件数を制限）

## データベース参照仕様

### 参照テーブル一覧

| テーブル名 | 用途 | 結合条件 |
|-----------|------|---------|
| members_created_events | サインアップ数（無料会員） | 主テーブル |
| members_subscription_created_events | 有料コンバージョン数 | member_id結合 |
| members_paid_subscription_events | MRR計算 | subscription_id, member_id結合 |

### テーブル別参照項目詳細

#### members_created_events

| 参照項目（カラム名） | 帳票項目との対応 | 取得条件 | 備考 |
|-------------------|----------------|---------|------|
| referrer_source | source | 日付フィルタ適用 | 正規化前の値 |
| member_id | COUNT DISTINCT → signups | | 重複排除 |
| created_at | 日付フィルタ | | |

#### members_subscription_created_events

| 参照項目（カラム名） | 帳票項目との対応 | 取得条件 | 備考 |
|-------------------|----------------|---------|------|
| referrer_source | source | 日付フィルタ適用 | |
| member_id | COUNT DISTINCT → paid_conversions | | 重複排除 |
| subscription_id | MRR計算用結合キー | | |
| created_at | 日付フィルタ | | |

#### members_paid_subscription_events

| 参照項目（カラム名） | 帳票項目との対応 | 取得条件 | 備考 |
|-------------------|----------------|---------|------|
| mrr_delta | SUM → mrr | mrr_delta > 0 | 正のMRR変動のみ |
| subscription_id | 結合キー | | |
| member_id | 結合キー | | |

## 計算仕様

### 計算項目一覧

| 項目名 | 計算式 | 端数処理 | 備考 |
|-------|-------|---------|------|
| signups | COUNT DISTINCT member_id（有料転換していない会員のみ） | なし | LEFT JOIN + WHERE msce.id IS NULL |
| paid_conversions | COUNT DISTINCT member_id | なし | |
| mrr | SUM(mrr_delta) | なし | セント単位 |

### ソース正規化ロジック

referrer_sourceは以下の正規化マップに基づいて統一された表示名に変換される：

- `www.facebook.com`, `l.facebook.com`, `m.facebook.com` → `Facebook`
- `twitter`, `x.com`, `com.twitter.android` → `Twitter`
- `www.google.com`, `google.com` → `Google`
- `linkedin.com`, `www.linkedin.com` → `LinkedIn`
- `mail.google.com`, `gmail.com` → `Gmail`
- 空文字またはnull → `Direct`

## 処理フロー

### 出力フロー

```mermaid
flowchart TD
    A[APIリクエスト受信] --> B[パラメータ検証]
    B --> C[日付境界の計算]
    C --> D[会員数クエリと MRRクエリを並列実行]
    D --> E[fetchMemberCountsBySource]
    D --> F[fetchMrrSourcesWithRange]
    E --> G[結果をソースごとに集約]
    F --> G
    G --> H[ソース名の正規化]
    H --> I[指定フィールドでソート]
    I --> J[limit適用]
    J --> K[結果をJSON形式で返却]
    K --> L[終了]
```

## エラー処理

### エラーケース一覧

| エラー種別 | 発生条件 | 表示メッセージ | 対処方法 |
|----------|---------|--------------|---------|
| データなし | 該当期間にデータなし | N/A | 空配列返却 |
| 内部エラー | クエリ実行失敗 | ログ出力のみ | 空配列返却 |

## パフォーマンス要件

| 項目 | 内容 |
|-----|------|
| 想定データ件数 | 数十〜数百件（limit=50がデフォルト） |
| 目標出力時間 | 1秒以内 |
| 同時出力数上限 | 制限なし（通常のAPI制限に従う） |

## セキュリティ考慮事項

- Admin API認証が必須（mw.authAdminApi）
- posts権限のbrowseパーミッションが必要
- APIキャッシュによる負荷軽減（statsService.cache）
- 個人を特定できる情報（メールアドレス等）は含まれない

## 備考

- signupsはサインアップしたが同期間内で有料転換していない会員数（純粋な無料会員数）
- ソース正規化マップは`referrers-stats-service.js`で定義されている
- MRRはセント単位で返却される（表示時に通貨フォーマット変換が必要）
- order指定に関わらず、常に降順（大きい順）でソートされる

---

## コードリーディングガイド

本帳票を理解するために参照すべきファイルと、推奨する読み解き順序を以下に示す。

### 推奨読解順序

#### Step 1: データ構造を理解する

まず、トップソースレポートで扱うデータ構造を理解することが重要。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 1-1 | schema.js | `ghost/core/core/server/data/schema/schema.js` | members_created_events, members_subscription_created_events, members_paid_subscription_eventsテーブルの構造（行526-626） |

**読解のコツ**: `referrer_source`カラムが参照元を追跡し、`attribution_id`/`attribution_type`とは異なるトラッキング情報であることを理解する。

#### Step 2: エントリーポイントを理解する

処理の起点となるAPIルーティングとコントローラーを特定。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 2-1 | routes.js | `ghost/core/core/server/web/api/endpoints/admin/routes.js` | topSourcesGrowthのルート定義（行172） |
| 2-2 | stats.js | `ghost/core/core/server/api/endpoints/stats.js` | topSourcesGrowthコントローラー（行495-528） |

**主要処理フロー**:
1. **行172**: `GET /stats/top-sources-growth`ルート定義
2. **行495-528**: `topSourcesGrowth`コントローラー、order, limit, date_from, date_to, timezone, member_statusオプションを受け取り

#### Step 3: サービス層を理解する

ビジネスロジックの中核となるサービスクラスを読み解く。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 3-1 | stats-service.js | `ghost/core/core/server/services/stats/stats-service.js` | getTopSourcesWithRangeメソッド（行233-235） |
| 3-2 | referrers-stats-service.js | `ghost/core/core/server/services/stats/referrers-stats-service.js` | getTopSourcesWithRange実装（行319-408） |

**主要処理フロー**:
- **referrers-stats-service.js 行323-326**: 会員数とMRRの並列クエリ実行
- **referrers-stats-service.js 行329-364**: ソースごとの集約処理
- **referrers-stats-service.js 行366-397**: ソート処理
- **referrers-stats-service.js 行400-402**: limit適用

#### Step 4: 会員数クエリを理解する

signupsとpaid_conversionsの計算ロジックを確認。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 4-1 | referrers-stats-service.js | `ghost/core/core/server/services/stats/referrers-stats-service.js` | fetchMemberCountsBySourceメソッド（行244-306） |

**主要処理フロー**:
- **行249-262**: signupsクエリ（有料転換していない会員のみ）
- **行265-271**: paid_conversionsクエリ
- **行274-305**: 結果の結合

#### Step 5: ソース正規化を理解する

normalizeSource関数と正規化マップを確認。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 5-1 | referrers-stats-service.js | `ghost/core/core/server/services/stats/referrers-stats-service.js` | SOURCE_NORMALIZATION_MAP, normalizeSource関数（行7-120） |

**主要処理フロー**:
- **行7-105**: ソーシャルメディア、検索エンジン、メールプラットフォーム、ニュースアグリゲータの正規化マップ
- **行112-120**: normalizeSource関数（空/null→'Direct'変換含む）

### プログラム呼び出し階層図

```
routes.js (ルート定義)
    │
    └─ stats.js (APIコントローラー)
           │
           └─ topSourcesGrowth
                  └─ statsService.api.getTopSourcesWithRange(options)
                          └─ StatsService.getTopSourcesWithRange()
                                  └─ ReferrersStatsService.getTopSourcesWithRange()
                                          │
                                          ├─ fetchMemberCountsBySource()
                                          │      ├─ members_created_events
                                          │      └─ members_subscription_created_events
                                          │
                                          ├─ fetchMrrSourcesWithRange()
                                          │      └─ members_paid_subscription_events
                                          │
                                          ├─ normalizeSource()
                                          │
                                          └─ ソート・limit適用
```

### データフロー図

```
[入力]                    [処理]                           [出力]

HTTPリクエスト ───▶ API Controller ───▶ ReferrersStatsService ───▶ JSON Response
  (date_from,          (パラメータ検証)                              { data: [...],
   date_to,                                                          meta: {} }
   order, limit)
                              │
                              ▼
                    ┌──────────────────┐
                    │   Database       │
                    │ ├─ members_      │
                    │ │   created_     │ ──▶ signups (有料転換なし)
                    │ │   events       │
                    │ ├─ members_      │
                    │ │   subscription_│ ──▶ paid_conversions
                    │ │   created_     │
                    │ │   events       │
                    │ └─ members_      │
                    │     paid_        │ ──▶ mrr
                    │     subscription_│
                    │     events       │
                    └──────────────────┘
                              │
                              ▼
                    ┌──────────────────┐
                    │  Source正規化    │ ──▶ Facebook, Google, Direct等
                    │  集約・ソート    │
                    └──────────────────┘
```

### 関連ファイル一覧

| ファイル | パス | 種別 | 役割 |
|---------|------|------|------|
| routes.js | `ghost/core/core/server/web/api/endpoints/admin/routes.js` | ソース | APIルート定義 |
| stats.js | `ghost/core/core/server/api/endpoints/stats.js` | ソース | APIコントローラー |
| stats-service.js | `ghost/core/core/server/services/stats/stats-service.js` | ソース | サービス層ラッパー |
| referrers-stats-service.js | `ghost/core/core/server/services/stats/referrers-stats-service.js` | ソース | トップソースクエリ実装、正規化マップ |
| date-utils.js | `ghost/core/core/server/services/stats/utils/date-utils.js` | ソース | 日付ユーティリティ |
| schema.js | `ghost/core/core/server/data/schema/schema.js` | 設定 | DBスキーマ定義 |
