# 帳票設計書 10-トップ投稿レポート

## 概要

会員獲得に貢献した投稿（記事）のランキングデータを提供する統計レポートの設計書。Ghost CMSの投稿分析機能において、どの投稿が無料会員のサインアップや有料転換に最も貢献したかを集計して提供するAPIレスポンスを定義する。

### 本帳票の処理概要

本帳票は、Ghost CMSの投稿（ポスト/ページ）が会員獲得に与えた影響をJSON形式で提供する。各投稿経由での無料会員サインアップ数、有料転換数、MRRへの貢献度を集計し、マーケティング効果の高いコンテンツを特定するためのデータを返却する。

**業務上の目的・背景**：サイト運営者がどのコンテンツが会員獲得に効果的かを分析し、コンテンツ戦略の最適化に活用する。高パフォーマンスのコンテンツパターンを特定し、今後のコンテンツ制作に反映させるために不可欠なデータとなる。

**帳票の利用シーン**：管理画面のDashboardやStats画面でトップ投稿ランキングを表示する際にAPIから取得される。コンテンツ効果分析や外部BIツールとの連携にも活用できる。

**主要な出力内容**：
1. 投稿別の無料会員サインアップ数（free_members）
2. 投稿別の有料転換数（paid_members）
3. 投稿別のMRR貢献額（mrr）
4. 投稿メタデータ（タイトル、公開日、URL）

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

**帳票の利用者**：サイト管理者、コンテンツマーケター。ダッシュボードのトップ投稿表示や外部連携に使用。

## 帳票種別

統計レポート（JSON API）

## 利用画面

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

## 出力形式

### 基本仕様

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

### APIエンドポイント

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

## 帳票レイアウト

### レスポンス構造

```json
{
  "data": [
    {
      "post_id": "uuid",
      "attribution_url": "/path/to/post",
      "attribution_type": "post",
      "attribution_id": "uuid",
      "title": "投稿タイトル",
      "published_at": "2024-01-15T10:00:00Z",
      "free_members": 50,
      "paid_members": 10,
      "mrr": 50000,
      "post_type": "post",
      "url_exists": true
    }
  ]
}
```

### 出力項目詳細

| No | 項目名 | 説明 | データ取得元 | 表示形式 |
|----|-------|------|-------------|---------|
| 1 | post_id | 投稿ID | posts.id | UUID |
| 2 | attribution_url | アトリビューションURL | members_created_events.attribution_url | パス文字列 |
| 3 | attribution_type | アトリビューション種別 | members_created_events.attribution_type | post/page/url/tag/author |
| 4 | attribution_id | アトリビューションID | members_created_events.attribution_id | UUID |
| 5 | title | 投稿タイトル | posts.title または生成 | 文字列 |
| 6 | published_at | 公開日時 | posts.published_at | ISO8601 |
| 7 | free_members | 無料会員サインアップ数 | 計算結果 | 整数 |
| 8 | paid_members | 有料転換数 | 計算結果 | 整数 |
| 9 | mrr | MRR貢献額 | 計算結果 | 整数（セント単位） |
| 10 | post_type | 投稿種別 | attribution_type | post/page/null |
| 11 | url_exists | URLが有効か | urlService判定 | boolean |

## 出力条件

### 抽出条件

| 条件名 | 説明 | 必須 |
|-------|------|-----|
| date_from | 開始日 | No |
| date_to | 終了日 | No |
| timezone | タイムゾーン | No |
| order | ソート順（free_members desc等） | No |
| limit | 取得件数上限（デフォルト: 20） | No |
| post_type | フィルタ（post/page） | No |

### ソート順

| 優先度 | 項目 | 昇順/降順 |
|-------|------|---------|
| 1 | 指定フィールド（order） | 指定方向（デフォルト: desc） |

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

### 参照テーブル一覧

| テーブル名 | 用途 | 結合条件 |
|-----------|------|---------|
| members_created_events | 無料会員サインアップ | attribution_url集計 |
| members_subscription_created_events | 有料転換 | attribution_url集計 |
| members_paid_subscription_events | MRR変動 | subscription_id結合 |
| posts | 投稿メタデータ | id結合 |

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

#### members_created_events

| 参照項目（カラム名） | 帳票項目との対応 | 取得条件 | 備考 |
|-------------------|----------------|---------|------|
| attribution_url | attribution_url | GROUP BY | アトリビューションURL |
| attribution_type | attribution_type | WHERE IN | post/page/url/tag/author |
| attribution_id | attribution_id | SELECT | 対象ID |
| member_id | free_members | COUNT DISTINCT | 重複除外カウント |

#### members_subscription_created_events

| 参照項目（カラム名） | 帳票項目との対応 | 取得条件 | 備考 |
|-------------------|----------------|---------|------|
| attribution_url | - | GROUP BY | 結合用 |
| member_id | paid_members | COUNT DISTINCT | 重複除外カウント |

#### members_paid_subscription_events

| 参照項目（カラム名） | 帳票項目との対応 | 取得条件 | 備考 |
|-------------------|----------------|---------|------|
| mrr_delta | mrr | SUM | MRR変動値 |
| subscription_id | - | JOIN | 結合用 |

## 計算仕様

### 計算項目一覧

| 項目名 | 計算式 | 端数処理 | 備考 |
|-------|-------|---------|------|
| free_members | COUNT DISTINCT (signed up on post, NOT paid on same post) | - | 重複除外 |
| paid_members | COUNT DISTINCT (paid conversion attributed to post) | - | 重複除外 |
| mrr | SUM(mrr_delta) | - | セント単位 |

### 計算ロジック

**free_members（無料会員）の定義**:
投稿経由でサインアップしたが、**同じ投稿経由では**有料転換していない会員数。
別の投稿経由で有料転換した場合はこのカウントに含まれる。

**paid_members（有料会員）の定義**:
この投稿経由で有料転換した会員数。

**処理フロー**:
1. attribution_url別にfree_members、paid_members、mrrを集計するCTEを構築
2. 3つのCTEをUNIONして全URLを取得
3. postsテーブルとLEFT JOINしてメタデータを付与
4. orderパラメータでソートし、limitで件数制限
5. タイトル生成・URL存在確認で結果を補完

### タイトル生成ルール

posts.titleがない場合、パスから以下のルールでタイトルを生成:

| パス | 生成タイトル |
|-----|-------------|
| / | "Homepage" |
| /tag/{slug} | "tag/{slug}" |
| /author/{slug} | "author/{slug}" |
| その他 | パスそのまま |

## 処理フロー

### 出力フロー

```mermaid
flowchart TD
    A[API要求] --> B[free_members CTE構築]
    A --> C[paid_members CTE構築]
    A --> D[mrr CTE構築]
    B --> E[URL UNION]
    C --> E
    D --> E
    E --> F[postsテーブル結合]
    F --> G[ソート・件数制限]
    G --> H[タイトル補完]
    H --> I[JSONレスポンス]
```

## エラー処理

### エラーケース一覧

| エラー種別 | 発生条件 | 表示メッセージ | 対処方法 |
|----------|---------|--------------|---------|
| 認証エラー | 認証なし | 401 Unauthorized | ログイン必要 |
| 無効なorderフィールド | 不正なフィールド指定 | BadRequestError | 有効なフィールドを指定 |
| データなし | アトリビューションなし | 空配列 | - |

## パフォーマンス要件

| 項目 | 内容 |
|-----|------|
| 想定データ件数 | 〜limit件（デフォルト20） |
| 目標出力時間 | 1秒以内 |
| 同時出力数上限 | 特に制限なし |

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

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

## 備考

- attribution_urlベースの集計（post_idではない）
- post_typeフィルタでpost/pageの絞り込みが可能
- urlServiceでURL存在確認を行い、url_existsフラグを設定
- Tinybirdクライアントがある場合はビュー数も取得可能

---

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

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

### 推奨読解順序

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

まず、トップ投稿計算のデータ構造を理解する。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 1-1 | posts-stats-service.js | `ghost/core/core/server/services/stats/posts-stats-service.js` | 型定義（21-52行目） |

**読解のコツ**: TopPostsOptions、AttributionResult、TopPostResultの型定義で入出力構造を把握する。

#### Step 2: メイン処理を理解する

トップ投稿取得のメインロジックを確認する。

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

**主要処理フロー**:
- **99-116行目**: パラメータ検証（order, limit）
- **119-121行目**: CTE構築（free_members, paid_members, mrr）
- **126-183行目**: CTEとpostsのJOINクエリ
- **186-198行目**: post_typeフィルタ適用
- **201-203行目**: タイトル補完

#### Step 3: サブクエリを理解する

各メトリクスのサブクエリ構築を確認する。

| 順序 | ファイル | パス | 読解ポイント |
|-----|---------|------|-------------|
| 3-1 | posts-stats-service.js | `ghost/core/core/server/services/stats/posts-stats-service.js` | _buildFreeMembersSubquery（450-503行目） |
| 3-2 | posts-stats-service.js | `ghost/core/core/server/services/stats/posts-stats-service.js` | _buildPaidMembersSubquery（513-548行目） |
| 3-3 | posts-stats-service.js | `ghost/core/core/server/services/stats/posts-stats-service.js` | _buildMrrSubquery（558-597行目） |

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

```
GET /ghost/api/admin/stats/top-posts
    │
    ├─ api/endpoints/stats.js#topPosts
    │      │
    │      └─ StatsService.getTopPosts(options)
    │             │
    │             └─ PostsStatsService.getTopPosts(options)
    │                    │
    │                    ├─ _buildFreeMembersSubquery(options, true)
    │                    │      └─ SELECT attribution_url, COUNT(DISTINCT member_id)
    │                    │         FROM members_created_events
    │                    │         LEFT JOIN members_subscription_created_events
    │                    │         WHERE msce.id IS NULL
    │                    │
    │                    ├─ _buildPaidMembersSubquery(options, true)
    │                    │      └─ SELECT attribution_url, COUNT(DISTINCT member_id)
    │                    │         FROM members_subscription_created_events
    │                    │
    │                    ├─ _buildMrrSubquery(options, true)
    │                    │      └─ SELECT attribution_url, SUM(mrr_delta)
    │                    │         FROM members_subscription_created_events
    │                    │         JOIN members_paid_subscription_events
    │                    │
    │                    └─ _enrichWithTitles(results)
    │                           └─ タイトル生成・URL存在確認
```

### データフロー図

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

APIリクエスト ───▶ StatsService ───▶ PostsStatsService
(order, limit,                             │
 date_from...)                             ▼
                              ┌─────────────────────┐
                              │ CTE構築             │
                              │ (free/paid/mrr)     │
                              └─────────────────────┘
                                          │
                                          ▼
                              ┌─────────────────────┐
                              │ URL UNION           │
                              │ (全attribution_url) │
                              └─────────────────────┘
                                          │
                                          ▼
                              ┌─────────────────────┐
                              │ posts LEFT JOIN     │
                              │ (メタデータ付与)    │
                              └─────────────────────┘
                                          │
                                          ▼
                              ┌─────────────────────┐
                              │ ソート・フィルタ    │
                              │ (order, post_type)  │
                              └─────────────────────┘
                                          │
                                          ▼
                              ┌─────────────────────┐
                              │ タイトル補完        │
                              │ (urlService)        │
                              └─────────────────────┘
                                          │
                                          ▼
                              ┌─────────────────────┐
                              │ JSONレスポンス      │
                              │ {data}              │
                              └─────────────────────┘
```

### 関連ファイル一覧

| ファイル | パス | 種別 | 役割 |
|---------|------|------|------|
| posts-stats-service.js | `ghost/core/core/server/services/stats/posts-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` | ソース | 日付ユーティリティ |
