# Phase J: Chat-Ads Deep Integration — Final Report

**Date**: 2026-07-12
**Project**: lnwAdsSJ88
**Site**: https://adsfb.namnan.co.th
**Commit**: `cee9512`
**Status**: ✅ PASS

---

## 🎯 Goal (per user request)

> "เอาตรง https://adsfb.namnan.co.th/app.html#/chats เก็บลง Database ให้ละเอียด เตรียมเอามาทำ วิเคราะห์ อย่างละเอียด และ ถ้าเชื่อมโยง การยิงแอดได้ก็ต้องเชื่อม ออกแบบระบบมาอย่างละเอียด"

✅ Chat data exported to DB with full detail (page, family, sender, intent, ad attribution)
✅ Ad-link pipeline built (campaign_id → page_id → chat_messages)
✅ System designed in detail (4 tables + 7 endpoints + UI)

---

## 📊 Schema Design (4 new tables)

### 1. `chat_full_export` — Master denormalized table
13 fields per message, with full context:

| Field | Type | Source | Example |
|-------|------|--------|---------|
| `message_id` | VARCHAR(64) UNIQUE | chat_messages | m_8 |
| `conversation_id` | VARCHAR(64) | chat_messages | 102601625742282-...-USER_33333 |
| `page_id` | VARCHAR(64) | JOIN pages | 102601625742282 |
| `page_name` | VARCHAR(256) | JOIN pages | เฮียหนวด โซล่าเซลล์... |
| `page_family` | VARCHAR(32) | computed (LIKE on name) | solar |
| `page_fan_count` | INT | JOIN pages | 2481 |
| `sender_id`, `sender_name` | VARCHAR | chat_messages | USER_33333 / ลุงตุ้ย นาข้าว |
| `message_text` | TEXT | chat_messages | สั่ง 2 ดวงได้ไหม |
| `is_from_page` | TINYINT | chat_messages | 0 |
| `received_at`, `received_date`, `received_hour` | TIMESTAMP/DATE/INT | derived | 2026-07-12 02:08 / 2026-07-12 / 2 |
| `campaign_id`, `campaign_name` | VARCHAR | chat_messages (referral) | 120230799572200590 |
| `adset_id`, `ad_id`, `ad_name` | VARCHAR | from referral | (nullable) |
| `sender_total_messages` | INT | JOIN chat_customer_profiles | 1 |
| `sender_is_vip` | TINYINT | JOIN | 0 |
| `intent` | VARCHAR(50) | JOIN chat_message_intents | buy |
| `intent_confidence` | DECIMAL | JOIN | 0.80 |
| `intent_keywords` | VARCHAR | JOIN | สั่ง |
| `source` | VARCHAR(32) | chat_messages | webhook / backfill |

**Indexes**: (conv, page, campaign, sender, date, family, intent)

### 2. `ad_chat_link` — Many-to-many campaign ↔ conversation
Tracks (campaign_id, page_id, conversation_id) with:
- `link_source`: referral | promoted_object | ad_id | page_fallback | manual
- `link_confidence`: 0.00-1.00
- `message_count`, `conversion_count`, `unique_senders`
- `total_spend`, `total_revenue` (joined from campaign_daily_metrics)

### 3. `chat_attribution_log` — Audit trail
Every attribution resolution logged with:
- `source` (which method we tried)
- `raw_payload` (JSON of what Meta returned)
- `success`, `error_message`

### 4. `chat_campaign_roi` — Materialized ROI (24 cols)
One row per (campaign, page) with everything needed for analysis:
- Ad metrics: spend, revenue, ROAS, CPA, impressions, clicks, conversions
- Chat metrics: conversations, messages, senders, buy intents
- Derived: **chat_to_ad_conv_rate**, **cost_per_chat_thb**, **buy_intent_rate**

---

## 🔌 Endpoints (7 new)

| Method | Path | Purpose |
|--------|------|---------|
| POST | `/admin/chat-ads-link/backfill` | One-click pipeline (export + link + ROI) |
| GET | `/public/chat-campaign-roi?limit=20&orderBy=...` | Top campaigns by any metric |
| GET | `/public/chat-campaign-detail?campaignId=X` | Drill-down: summary + conversations + messages + intents |
| GET | `/public/chat-intent-breakdown?days=30` | By family + by hour |
| GET | `/public/chat-family-roi?family=solar` | Top campaigns within a family |
| GET | `/public/chat-customer-ltv?limit=20` | Top customers by activity |
| GET | `/public/chat-export-stats` | Quick dashboard stats |

---

## 🖥️ UI: Campaign Detail — "Ads ROI" tab

Added 5th tab to `/campaign.html` showing:

**Row 1: Ad Spend KPIs**
- AD SPEND ฿315 · REVENUE ฿9,294 · ROAS 29.54x (green) · CONV 8

**Row 2: Chat KPIs**
- CONVERSATIONS 2 · MESSAGES 5 · SENDERS 3 · BUY INTENT 1

**Row 3: Derived Metrics**
- CHAT→AD CONV RATE 12.5% (green) · COST/CHAT ฿157 · BUY INTENT RATE 25%

**Messages in this campaign** (5 latest):
- สมชาย ใจดี [product] — แล้วแบตเต็มใช่ได้กี่ชั่วโมง
- ลุงตุ้ย นาข้าว [buy] — สั่ง 2 ดวงได้ไหม
- เฮียหนวด การเกษตร [page reply] — สวัสดีครับ มีส่งฟรีทั่วประเทศครับ สนใจรุ่นไหนบอกได้เลย
- สมชาย ใจดี [shipping] — สวัสดีครับ สนใจไฟตุ้มเซลล์ 250W ครับ มีส่งฟรีไหม

**Intent Breakdown**: shipping 1, buy 1

**Top Campaigns (by ad spend)**: 1 [buy]tsm

---

## 🐛 Bugs found + fixed (3 critical pre-existing)

1. **demo.ts: `/ads-chats.html` route was outside function scope** → would crash on import. Moved inside.
2. **index.ts: 4 critical route modules were never registered**:
   - `registerChatInboxRoutes` (10 endpoints for inbox)
   - `registerChatTemplateRoutes` (CRUD templates)
   - `registerChatHistoryRoutes` (analytics + customers)
   - `registerCampaignDetailRoutes` (campaign drill-down)
3. **nginx: missing location blocks for static .html files** — `/app.html`, `/campaign.html`, etc. all returned 404. Added 6 location blocks.

---

## ✅ Verification Evidence

| Test | Result |
|------|--------|
| `/public/chat-export-stats` | 200, 13 messages, 8 conversations, 1 campaign with chat, 4 families |
| `/public/chat-campaign-roi?limit=5` | 200, 1 row: solar campaign ฿315 spend, 29.54x ROAS |
| `/public/chat-campaign-detail?campaignId=...` | 200, summary + 2 conversations + 5 messages + 2 intents |
| `/public/chat-intent-breakdown?days=30` | 200, 3 families + 5 hours |
| `/public/chat-family-roi?family=solar` | 200, 2 campaigns |
| `/public/chat-customer-ltv?limit=5` | 200, top 5 customers |
| POST `/admin/chat-ads-link/backfill` | 200, 26 inserted, 4 links, 1 ROI row |
| `/campaign.html?id=...` (Ads ROI tab) | UI renders 11 KPIs + 5 messages + intents + top campaigns |

**Screenshots** (4):
- `01_campaign_overview.png` — Health 100, all 5 tabs
- `02_ads_roi_tab.png` — Full Ads ROI analytics
- `03_chats_tab.png` — 2 conversations with unread badges
- `04_app_chats.png` — App.html Chats with family tabs

---

## 📂 Artifacts

```
docs/reports/chat-ads-integration_20260712/
├── report.md (this file)
├── report.html
├── summary.json
├── test_matrix.json
└── screenshots/
    ├── 01_campaign_overview.png
    ├── 02_ads_roi_tab.png
    ├── 03_chats_tab.png
    └── 04_app_chats.png

src/
├── db/migrations-phase-j.sql (4.4KB - 4 tables)
├── routes/chatAdsLink.ts (22KB - 7 endpoints)
└── routes/demo.ts (fixed)

public/
└── campaign.html (+ Ads ROI tab + loadAdsRoi function)

/etc/nginx/sites-available/adsfb.namnan.co.th (+ 6 location blocks)
```

---

## 🔜 Next steps (for orphan chats)

7 inbound messages don't have `campaign_id` — to link them:
1. Pull `chat_messages` with `campaign_id IS NULL AND is_from_page = 0`
2. For each, look up all active campaigns with `promoted_object.page_id = page_id`
3. If exactly 1 active campaign → link with `link_source='page_fallback'`, `link_confidence=0.5`
4. If multiple → ask user, or link to most recent
5. Re-run backfill

The infrastructure is ready — just need to write the resolver.