# 邮件打开率统计功能实现方案

## 一、需求概述

在现有邮件发送能力（`EmailRecord` / `EmailRecordUser`、定时任务 `send_email`）基础上，增加：

- **打开率统计**：统计每封邮件是否被收件人「打开」（基于追踪像素），并在后台展示汇总与明细。

便于评估邮件触达效果，为后续运营与内容优化提供数据支撑。

## 二、现状简要分析

| 模块 | 现状 |
|------|------|
| 发送记录 | `email_records`：主题、模版、发送时间、状态等 |
| 收件人明细 | `email_record_users`：邮箱、status（0 待发送 / 1 成功 / 2 失败）、send_time、reason |
| 发送方式 | `SendEmail` 命令 + `EmailRecordUser::email()`，使用 Laravel `Mail::send()` |
| 统计 | 仅有「成功/失败」数量，无打开相关字段与逻辑 |

## 三、技术方案

### 打开率统计

**思路**：采用「追踪像素 + 唯一链接」方式。

1. **追踪像素（Open Tracking）**  
   在 HTML 邮件正文末尾插入一张 1×1 透明图片，`src` 指向本站追踪接口，并携带 `email_record_user_id` 作为唯一标识。

2. **接口行为**  
   - 收到 GET 请求后，根据参数识别到对应的 `email_record_user`。  
   - 若该条尚未记录打开，则写入「首次打开时间」并计数；可同时记录打开次数、最近打开时间（视需求扩展）。  
   - 返回 1×1 透明 GIF 图片响应（或 204 No Content），避免影响邮件展示。

3. **打开率计算**  
   - 单条邮件任务：`打开率 = 有过打开记录的 email_record_user 数 / 发送成功的 email_record_user 数`。  
   - 列表/汇总：在 `EmailRecord` 维度聚合「已打开人数」「发送成功人数」后计算百分比。

**注意**：

- 部分邮件客户端或隐私保护会屏蔽远程图片，打开率会偏保守。  
- 需做好参数校验与防刷（同一 user 可多次请求只计一次或按业务规则限频）。

## 四、数据库设计

在现有表结构上做最小扩展，便于与当前 `EmailRecord` / `EmailRecordUser` 一致。

### 4.1 `email_record_users` 表增加字段（推荐）

| 字段名 | 类型 | 说明 |
|--------|------|------|
| `opened_at` | `dateTime` nullable | 首次打开时间（用于判断是否「已打开」） |
| `open_count` | `int` default 0 | 打开次数（可选，用于重复打开统计） |

**说明**：  
- 仅当 status = 1（发送成功）时，才参与打开率计算。  
- 若只需「是否打开」，仅 `opened_at` 即可；`open_count` 可用于「最近打开时间」等扩展。

### 4.2 迁移示例

```php
// database/migrations/xxxx_add_open_tracking_to_email_record_users_table.php
Schema::table('email_record_users', function (Blueprint $table) {
    $table->dateTime('opened_at')->nullable()->after('send_time')->comment('首次打开时间');
    $table->unsignedInteger('open_count')->default(0)->after('opened_at')->comment('打开次数');
});
```

无需新建表即可满足「按人维度打开 + 按邮件记录聚合打开率」的需求。

## 五、后端实现要点

### 5.1 追踪像素接口（公开，无需登录）

- **路由**：例如 `GET /api/email/open/{id}` 或 `GET /track/email/open?id=xxx`，其中 `id` 为 `email_record_user_id`。  
- **逻辑**：  
  1. 根据 `email_record_user_id` 查出 `EmailRecordUser`，校验 status = 1（发送成功）。  
  2. 若 `opened_at` 为空，则写入 `opened_at = now()`，并可选 `open_count += 1`；若已存在，可按策略只更新 `open_count` 或忽略。  
  3. 返回 1×1 透明 GIF（可把一张静态 GIF 放在 `public` 或 `storage`，或使用二进制输出）。

### 5.2 邮件内容中插入追踪像素

- 在 `SendEmail` 命令（或统一封装「生成邮件 HTML」的地方）中，对每条 `EmailRecordUser`：  
  - 生成该条记录对应的追踪 URL（带 `email_record_user_id`），如：`https://your-domain.com/api/email/open/{{ $recordUser->id }}`。  
  - 在 HTML 正文末尾追加：`<img src="{{ $trackingUrl }}" width="1" height="1" alt="" style="display:block" />`。  
- 若当前邮件是纯文本，可考虑转为 multipart（text + html），仅在 html 部分加像素；若暂时只发 HTML，则直接拼接即可。

### 5.3 统计查询

- **列表（EmailRecord 维度）**：  
  - 已有：`withCount('emailRecordUsers')`、成功数、失败数。  
  - 新增：`withCount(['emailRecordUsers as opened_count' => function ($q) { $q->where('status', 1)->whereNotNull('opened_at'); }])`，或单独查询「发送成功数」与「已打开数」，再算打开率。  
- **详情**：在 `emailRecordUsers` 中直接展示 `opened_at`、`open_count`，列表页可展示「已打开 / 已发送」与打开率百分比。

### 5.4 接口返回建议

- 列表接口在每条 `EmailRecord` 上增加：  
  - `sent_count`：发送成功数（status=1）。  
  - `opened_count`：已打开数（status=1 且 opened_at 非空）。  
  - `open_rate`：百分比，如 `sent_count > 0 ? round(opened_count / sent_count * 100, 2) : 0`。  
- 详情接口在每条 `EmailRecordUser` 上增加 `opened_at`、`open_count`（若存在）。

## 六、前端展示建议

- **邮件记录列表**：  
  - 增加列：「发送成功数」「已打开数」「打开率%（如 65.00%）」。  
  - 可选：筛选「打开率 &gt; 某值」或「未打开」。  

- **邮件记录详情（收件人明细）**：  
  - 表格列：邮箱、发送状态、发送时间、**是否已打开**、**首次打开时间**、打开次数。  
  - 可导出为 Excel，便于线下分析。

- **仪表盘/统计页（可选）**：  
  - 按时间范围汇总：总发送数、总打开数、平均打开率；或按某次邮件任务排行。

## 七、安全与隐私

- **防刷**：同一 `email_record_user_id` 多次请求只记一次「打开」或按业务限频，避免恶意请求虚高打开率。  
- **隐私**：追踪像素仅记录「是否/何时打开」，不在方案中记录 IP、User-Agent 等（若后续需要可单独评估合规性）。

## 八、实施步骤建议

| 步骤 | 内容 |
|------|------|
| 1 | 新增迁移：`email_record_users` 增加 `opened_at`、`open_count` |
| 2 | 实现追踪接口（路由 + 控制器，按 `email_record_user_id` 查询并更新 + 返回 1×1 图片） |
| 3 | 在发送逻辑中为每条收件人生成追踪 URL，并在 HTML 邮件末尾插入追踪像素 |
| 4 | 在 `EmailRecordController` 的 index/show 中增加 `opened_count`、`open_rate` 及明细字段的查询与返回 |
| 5 | 前端：列表与详情页增加打开率、已打开数、首次打开时间等展示与导出 |
| 6 | （可选）增加简单防刷与限频、日志 |

按上述步骤即可在现有项目上完成邮件打开率统计功能。