# 帳票設計書 9-リファラー履歴レポート

## 概要

会員獲得元（リファラー）の履歴データを提供する統計レポートの設計書。Ghost CMSの会員獲得分析機能において、どの流入元からサインアップや有料転換が発生したかを日次で集計して提供するAPIレスポンスを定義する。

### 本帳票の処理概要

本帳票は、Ghost CMSの会員獲得におけるリファラー（流入元）別の統計データをJSON形式で提供する。サインアップ数と有料転換数を流入元別・日次で集計し、マーケティング効果の分析に必要なデータを返却する。

**業務上の目的・背景**：サイト運営者が会員獲得の流入経路を分析し、効果的なマーケティングチャネルを特定するために利用される。SNS、検索エンジン、メールなど各チャネルの貢献度を把握し、マーケティング投資の最適化に不可欠なデータとなる。

**帳票の利用シーン**：管理画面のDashboardやStats画面でリファラー分析グラフを表示する際にAPIから取得される。流入元別の会員獲得トレンド分析や外部BIツールとの連携にも活用できる。

**主要な出力内容**：
1. 日別・流入元別のサインアップ数（signups）
2. 日別・流入元別の有料転換数（paid_conversions）
3. 流入元別のMRR（getTopSourcesWithRange使用時）

**帳票の出力タイミング**：APIエンドポイントへのリクエスト時にリアルタイムで生成される。

**帳票の利用者**：サイト管理者、マーケティング担当者。ダッシュボードの流入元分析グラフ表示や外部連携に使用。

## 帳票種別

統計レポート（JSON API）

## 利用画面

| 画面No | 画面名 | URL/ルーティング | 出力操作 |
|--------|--------|-----------------|---------|
| - | Dashboard | /ghost/#/dashboard | 自動取得 |
| - | Stats | /ghost/#/stats | 自動取得 |

## 出力形式

### 基本仕様

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

### APIエンドポイント

| 項目 | 内容 |
|-----|------|
| HTTPメソッド | GET |
| パス | /ghost/api/admin/stats/referrers |
| 認証 | authAdminApi必須 |

## 帳票レイアウト

### レスポンス構造（getReferrersHistory）

```json
{
  "data": [
    {
      "date": "YYYY-MM-DD",
      "source": "Google",
      "signups": 10,
      "paid_conversions": 2
    }
  ],
  "meta": {}
}
```

### レスポンス構造（getTopSourcesWithRange）

```json
{
  "data": [
    {
      "source": "Google",
      "signups": 100,
      "paid_conversions": 20,
      "mrr": 50000
    }
  ],
  "meta": {}
}
```

### 出力項目詳細

| No | 項目名 | 説明 | データ取得元 | 表示形式 |
|----|-------|------|-------------|---------|
| 1 | date | 日付 | members_created_events.created_at | YYYY-MM-DD |
| 2 | source | 流入元 | referrer_source | 文字列（正規化済み） |
| 3 | signups | サインアップ数 | members_created_events | 整数 |
| 4 | paid_conversions | 有料転換数 | members_subscription_created_events | 整数 |
| 5 | mrr | MRR | members_paid_subscription_events | 整数（セント単位） |

## 出力条件

### 抽出条件

| 条件名 | 説明 | 必須 |
|-------|------|-----|
| date_from | 開始日（デフォルト: 90日前） | No |
| date_to | 終了日（デフォルト: 今日） | No |
| timezone | タイムゾーン | No |
| order | ソート順（signups desc等） | No |
| limit | 取得件数上限（デフォルト: 50） | No |

### ソート順

| 優先度 | 項目 | 昇順/降順 |
|-------|------|---------|
| 1 | date | 昇順（履歴） |
| - | 指定フィールド | 降順（Top Sources） |

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

### 参照テーブル一覧

| テーブル名 | 用途 | 結合条件 |
|-----------|------|---------|
| members_created_events | サインアップ記録 | referrer_source集計 |
| members_subscription_created_events | 有料転換記録 | referrer_source集計 |
| members_paid_subscription_events | MRR変動 | subscription_id結合 |

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

#### members_created_events

| 参照項目（カラム名） | 帳票項目との対応 | 取得条件 | 備考 |
|-------------------|----------------|---------|------|
| created_at | date | DATE(), >= 90日前 | 日付変換 |
| referrer_source | source | GROUP BY | 流入元 |
| member_id | signups | COUNT | サインアップ数 |

#### members_subscription_created_events

| 参照項目（カラム名） | 帳票項目との対応 | 取得条件 | 備考 |
|-------------------|----------------|---------|------|
| created_at | date | DATE(), >= 90日前 | 日付変換 |
| referrer_source | source | GROUP BY | 流入元 |
| member_id | paid_conversions | COUNT | 有料転換数 |

## 計算仕様

### 計算項目一覧

| 項目名 | 計算式 | 端数処理 | 備考 |
|-------|-------|---------|------|
| signups | COUNT(*) | - | 日次・流入元別 |
| paid_conversions | COUNT(*) | - | 日次・流入元別 |
| mrr | SUM(mrr_delta) | - | mrr_delta > 0のみ |

### ソース正規化マッピング

流入元（source）は以下のルールで正規化される：

**SNS系**:
- facebook, www.facebook.com, l.facebook.com → "Facebook"
- twitter, x.com → "Twitter"
- linkedin, www.linkedin.com → "LinkedIn"
- instagram, www.instagram.com → "Instagram"

**検索エンジン系**:
- google, www.google.com → "Google"
- bing, www.bing.com → "Bing"
- duckduckgo, duckduckgo.com → "DuckDuckGo"

**メール系**:
- gmail, mail.google.com → "Gmail"
- outlook, outlook.live.com → "Outlook"

**その他**:
- null または空文字 → "Direct"

### 計算ロジック

1. 過去90日間（または指定期間）のサインアップを日次・流入元別に集計
2. 同期間の有料転換を日次・流入元別に集計
3. 両者をマージしてsignupsとpaid_conversionsを統合
4. 結果を日付昇順でソート

## 処理フロー

### 出力フロー

```mermaid
flowchart TD
    A[API要求] --> B[サインアップ履歴取得]
    A --> C[有料転換履歴取得]
    B --> D[日付・流入元でマージ]
    C --> D
    D --> E[日付昇順ソート]
    E --> F[JSONレスポンス]
```

## エラー処理

### エラーケース一覧

| エラー種別 | 発生条件 | 表示メッセージ | 対処方法 |
|----------|---------|--------------|---------|
| 認証エラー | 認証なし | 401 Unauthorized | ログイン必要 |
| データなし | イベントなし | 空配列 | - |

## パフォーマンス要件

| 項目 | 内容 |
|-----|------|
| 想定データ件数 | 〜90日 x 流入元数 |
| 目標出力時間 | 1秒以内 |
| 同時出力数上限 | 特に制限なし |

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

- 管理者認証が必要
- 流入元統計のため個人情報は含まない

## 備考

- ソース正規化により同一プラットフォームの異なるドメインを統合
- 空の流入元は"Direct"として扱う
- Top Sources APIでは重複会員を除外（DISTINCT）

---

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

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

### 推奨読解順序

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

まず、流入元の正規化マッピングを理解する。

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

**読解のコツ**: SOURCE_NORMALIZATION_MAPで各ドメインがどのように正規化されるか把握する。

#### Step 2: 履歴取得ロジックを理解する

リファラー履歴の取得ロジックを確認する。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 2-1 | referrers-stats-service.js | `ghost/core/core/server/services/stats/referrers-stats-service.js` | getReferrersHistory関数（135-171行目） |

**主要処理フロー**:
- **135-171行目**: getReferrersHistory（メイン処理）
- **176-188行目**: fetchAllPaidConversionSources（有料転換取得）
- **193-205行目**: fetchAllSignupSources（サインアップ取得）

#### Step 3: Top Sources APIを理解する

集計済みのTop Sources取得ロジックを確認する。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 3-1 | referrers-stats-service.js | `ghost/core/core/server/services/stats/referrers-stats-service.js` | getTopSourcesWithRange関数（319-408行目） |
| 3-2 | referrers-stats-service.js | `ghost/core/core/server/services/stats/referrers-stats-service.js` | fetchMemberCountsBySource関数（244-306行目） |

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

```
GET /ghost/api/admin/stats/referrers
    │
    ├─ api/endpoints/stats.js#referrersHistory
    │      │
    │      └─ StatsService.getReferrersHistory()
    │             │
    │             └─ ReferrersStatsService.getReferrersHistory()
    │                    │
    │                    ├─ fetchAllSignupSources()
    │                    │      └─ SELECT DATE(created_at), COUNT(*), referrer_source
    │                    │         FROM members_created_events
    │                    │         WHERE created_at >= 90日前
    │                    │
    │                    └─ fetchAllPaidConversionSources()
    │                           └─ SELECT DATE(created_at), COUNT(*), referrer_source
    │                              FROM members_subscription_created_events
    │                              WHERE created_at >= 90日前

GET /ghost/api/admin/stats/top-sources
    │
    └─ ReferrersStatsService.getTopSourcesWithRange(options)
           │
           ├─ fetchMemberCountsBySource(options)
           │      ├─ FREE: members_created_events LEFT JOIN members_subscription_created_events
           │      └─ PAID: members_subscription_created_events
           │
           └─ fetchMrrSourcesWithRange(options)
                  └─ members_subscription_created_events JOIN members_paid_subscription_events
```

### データフロー図

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

APIリクエスト ───▶ StatsService ───▶ ReferrersStatsService
(date_from?)                              │
                                          ▼
                              ┌───────────────────────┐
                              │ サインアップ履歴取得  │
                              │ (90日 or 指定期間)    │
                              └───────────────────────┘
                                          │
                                          ▼
                              ┌───────────────────────┐
                              │ 有料転換履歴取得      │
                              │ (90日 or 指定期間)    │
                              └───────────────────────┘
                                          │
                                          ▼
                              ┌───────────────────────┐
                              │ マージ処理            │
                              │ (date + source)       │
                              └───────────────────────┘
                                          │
                                          ▼
                              ┌───────────────────────┐
                              │ ソース正規化          │
                              │ (normalizeSource)     │
                              └───────────────────────┘
                                          │
                                          ▼
                              ┌───────────────────────┐
                              │ JSONレスポンス        │
                              │ {data, meta}          │
                              └───────────────────────┘
```

### 関連ファイル一覧

| ファイル | パス | 種別 | 役割 |
|---------|------|------|------|
| referrers-stats-service.js | `ghost/core/core/server/services/stats/referrers-stats-service.js` | ソース | リファラー統計ロジック |
| stats-service.js | `ghost/core/core/server/services/stats/stats-service.js` | ソース | 統計サービス集約 |
| routes.js | `ghost/core/core/server/web/api/endpoints/admin/routes.js` | ソース | ルーティング定義 |
| date-utils.js | `ghost/core/core/server/services/stats/utils/date-utils.js` | ソース | 日付ユーティリティ |
