# WhatsApp Chatbot Log Analysis Workflow

Complete pipeline: Google Sheets export → Python analysis → 20 matplotlib charts → Word report.
Built from session with drh. Edi Santosa (Disbunak Kalsel's WhatsApp chatbot).

## Pipeline Overview

```
Google Sheets (n8n logged)
    ↓ CSV export
/tmp/spreadsheet_data.csv
    ↓ Python parse + categorize
/tmp/chatbot_data.json
    ↓ matplotlib charts (venv /tmp/mplenv)
/tmp/charts/*.png (20 charts)
    ↓ python-docx embed
Laporan_Analisis_Chatbot_WhatsApp.docx
    ↓ copy to final location
/home/ubuntu/kantor/<folder>/
```

## Step 1 — Export Google Sheets to CSV

```python
import urllib.request
url = 'https://docs.google.com/spreadsheets/d/<SHEET_ID>/export?format=csv&gid=<GID>'
urllib.request.urlretrieve(url, '/tmp/spreadsheet_data.csv')
```

**Get IDs from URL:**
```
https://docs.google.com/spreadsheets/d/1x0pAx5uqI-ttgr9x3qk8gah_72oudcINyr2S_ffPxUQ/edit?gid=756284932#gid=756284932
```
- `SHEET_ID` = `1x0pAx5uqI-ttgr9x3qk8gah_72oudcINyr2S_ffPxUQ`
- `GID` = `756284932`

## Step 2 — Parse Data

```python
import csv
from datetime import datetime
from collections import Counter, defaultdict

rows = []
with open('/tmp/spreadsheet_data.csv', 'r', encoding='utf-8') as f:
    reader = csv.DictReader(f)
    for row in reader:
        rows.append(row)

data = []
for r in rows:
    try:
        waktu = datetime.fromisoformat(r['Waktu '].strip().replace('Z', '+00:00'))
        data.append({
            'waktu': waktu,
            'tanggal': waktu.date(),
            'bulan': waktu.strftime('%Y-%m'),
            'jam': waktu.hour,
            'hari': waktu.strftime('%A'),
            'nomor': r['Nomor Pengirim'].strip(),
            'nama': r['Nama Pengirim'].strip(),
            'pertanyaan': r['pertanyaan'].strip(),
            'jawaban': r['Jawaban AI'].strip(),
        })
    except:
        pass  # skip unparseable rows
```

**Note:** Column name includes trailing space (`'Waktu '` not `'Waktu'`). Check with `list(rows[0].keys())`.

## Step 3 — Keyword-Based Question Categorization

```python
import re

def categorize(q):
    q_lower = q.lower().strip()
    if len(q_lower) < 4 or re.match(r'^(tes|selamat|salam|halo|hai)$', q_lower):
        return 'Tes / Sapaan'
    if any(k in q_lower for k in ['pompa air','mesin pompa','laptop','wifi','mobil','injektor']):
        return 'Off-topic'
    if any(k in q_lower for k in ['pmk','penyakit kuku dan mulut','fmd']):
        return 'PMK'
    if any(k in q_lower for k in ['rabies','anjing','kucing','kera','lalulintas']):
        return 'Rabies & Lalu Lintas Hewan'
    if any(k in q_lower for k in ['lsd','lumpy skin','bengkak bentol']):
        return 'LSD'
    if any(k in q_lower for k in ['pakan','ransum','nutrisi','jagung','dedak','sagu','keong','bungkil','itik','layer','petelur']):
        return 'Pakan & Nutrisi'
    if any(k in q_lower for k in ['bunting','beranak','reproduksi','inseminasi','ib ','birahi','ovulasi']):
        return 'Reproduksi & Kebuntingan'
    if any(k in q_lower for k in ['cacing','obat cacing','ivermectin','kutu','tungau','caplak','scabies']):
        return 'Pengendalian Parasit'
    if any(k in q_lower for k in ['mastitis','subclinical','subklinis']):
        return 'Mastitis'
    if any(k in q_lower for k in ['surveilans','phms','monitoring','pelaporan','vaksin','vaksinasi','populasi']):
        return 'Surveilans & Pelaporan'
    if any(k in q_lower for k in ['rph','rumah potong','hewan qurban','kesejahteraan','penanganan']):
        return 'RPH & Kesejahteraan Hewan'
    return 'Lainnya'

for d in data:
    d['kategori'] = categorize(d['pertanyaan'])
```

## Step 4 — Generate Charts (matplotlib)

**Setup isolated venv** (Hermes's default venv lacks matplotlib + python-docx):
```bash
python3 -m venv /tmp/mplenv
/tmp/mplenv/bin/pip install matplotlib python-docx -q
```

**Chart generation script pattern:**
```python
import sys
sys.path.insert(0, '/tmp/mplenv/lib/python3.11/site-packages')
import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt
# ...
plt.rcParams.update({
    'font.family': 'DejaVu Sans',
    'axes.spines.top': False,
    'axes.spines.right': False,
})
os.makedirs('/tmp/charts', exist_ok=True)

def save(fig, name):
    path = f'/tmp/charts/{name}.png'
    fig.savefig(path, dpi=150, bbox_inches='tight')
    plt.close(fig)
    return path
```

**Run with:** `/tmp/mplenv/bin/python3 /tmp/generate_charts.py`

## Step 5 — Generate Word Report

```python
import sys
sys.path.insert(0, '/tmp/mplenv/lib/python3.11/site-packages')
from docx import Document
from docx.shared import Inches, Pt, RGBColor, Cm
from docx.enum.text import WD_ALIGN_PARAGRAPH

doc = Document()
section = doc.sections[0]
section.page_width  = Inches(8.27)   # A4
section.page_height = Inches(11.69)
section.left_margin   = Inches(1)
section.right_margin  = Inches(1)
# ...
doc.save('/tmp/report.docx')
```

**Embed chart in docx:**
```python
doc.add_picture('/tmp/charts/chart01_name.png', width=Inches(6.0))
doc.paragraphs[-1].alignment = WD_ALIGN_PARAGRAPH.CENTER
```

## Step 6 — Deliver

```bash
# Copy to final location
cp /tmp/report.docx /home/ubuntu/kantor/<folder>/

# Copy all charts
cp /tmp/charts/*.png /home/ubuntu/kantor/<folder>/
```

## Font Pitfall (CJK / Special Characters)

DejaVu Sans (matplotlib default) does NOT render:
- Chinese characters (子, 母, etc.)
- Mathematical Fraktur symbols
- Emoji

**Always use ASCII-only labels in chart text.** Full names go in the Word doc table.

If category names contain non-ASCII, map to ASCII abbreviations for charts only:
```python
# In charts: 'Lainnya' (NOT '母子 (母子 - Lainnya')
# In Word table: full name preserved
```

## Standard 20-Chart Set for WhatsApp Chatbot Analysis

| # | Chart Type | Purpose |
|---|-----------|---------|
| 1 | Bar+Line (monthly) | Volume trend |
| 2 | Horizontal bar | Top senders |
| 3 | Bar (24h) | Hourly distribution |
| 4 | Pie | Category proportions |
| 5 | Horizontal bar | Top categories |
| 6 | Line+fill | Cumulative unique senders |
| 7 | Bar (7 days) | Day-of-week pattern |
| 8 | Bar (daily) | Daily volume spike detection |
| 9 | Dual histogram | Q&A length distribution |
| 10 | Stacked bar | Top categories × month |
| 11 | Horizontal bar | Avg answer length per category |
| 12 | Dual bar | Messages vs unique senders |
| 13 | Heatmap (imshow) | Day × Hour activity |
| 14 | Line+fill | Cumulative messages over time |
| 15 | Histogram | Question word count |
| 16 | Horizontal bar | Top keyword frequencies |
| 17 | Donut | Main category distribution |
| 18 | Multi-line | Disease topic trends by month |
| 19 | Histogram | User engagement distribution |
| 20 | Horizontal bar | Topic summary (all categories) |

## Connecting Analysis to Government Functions

Map chatbot topic categories → Indonesian government Bidang tugas:

| Category from Chatbot | Corresponding Function |
|----------------------|----------------------|
| Pakan & Nutrisi | Funk (c) — kebutuhan & ketersediaan pakan ternak |
| Surveilans & Pelaporan | Funk (d) — pengamatan, pencegahan penyakit hewan |
| Rabies & Lalu Lintas | Funk (d)+(e) — pemberantasan penyakit + kesmavet |
| Reproduksi & Kebuntingan | Funk (b) — peningkatan & pengembangan produksi |
| Mastitis, Parasit | Funk (d) — pencegahan & pemberantasan penyakit |
| RPH & Kesejahteraan | Funk (d)+(f) — pemotongan & fungsi lain |
| Perbibitan | Funk (a) — penyediaan & standar mutu perbibitan |

Always connect analysis findings back to the specific function letter (a/b/c/d/e/f) of the Bidang.