File size: 6,370 Bytes
f120be8 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 |
#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""Populate the SQLite database with deterministic demo data."""
import json
import os
import sqlite3
from datetime import datetime, timedelta
# Resolve database path inside data directory
SCRIPT_DIR = os.path.dirname(os.path.abspath(__file__))
PROJECT_ROOT = os.path.dirname(SCRIPT_DIR)
DATA_DIR = os.path.join(PROJECT_ROOT, "data")
os.makedirs(DATA_DIR, exist_ok=True)
DB_PATH = os.path.join(DATA_DIR, "sentiment_analysis.db")
print(f"📍 Database path: {DB_PATH}")
conn = sqlite3.connect(DB_PATH)
cursor = conn.cursor()
clientes = [
{"id": "ACME_CORP_001", "nombre": "Acme Corporation"},
{"id": "TECH_STARTUP_02", "nombre": "TechFlow Inc"},
{"id": "RETAIL_CHAIN_03", "nombre": "MegaStore Retail"},
{"id": "HEALTH_SVC_04", "nombre": "HealthCare Plus"},
{"id": "FINANCE_GROUP_05", "nombre": "FinanceFlow"},
]
print("📋 Inserting customers...")
for cliente in clientes:
cursor.execute(
"""
INSERT OR IGNORE INTO customer_profiles
(customer_id, name, context_type, first_contact, last_contact, total_interactions,
churn_risk, lifetime_sentiment, notes)
VALUES (?, ?, 'customer', ?, ?, 0, 0.3, 70, '')
""",
(
cliente["id"],
cliente["nombre"],
datetime.now(),
datetime.now(),
),
)
print(f" ✅ {cliente['nombre']}")
conn.commit()
print("\n📊 Inserting conversations...")
conversation_payload = {
"ACME_CORP_001": [
(30, 85, "Excelente servicio, muy satisfecho", "RISING", "MEDIUM"),
(24, 75, "Problemas con tiempos de respuesta", "DECLINING", "MEDIUM"),
(18, 55, "Muy decepcionado con la calidad", "DECLINING", "HIGH"),
(12, 35, "Considerando cambiar de proveedor", "DECLINING", "HIGH"),
(3, 15, "Definitivamente nos vamos", "DECLINING", "HIGH"),
],
"TECH_STARTUP_02": [
(25, 85, "Excelente trabajo, dashboard intuitivo", "STABLE", "LOW"),
(15, 86, "Muy contento, nos ayuda mucho", "STABLE", "LOW"),
(5, 87, "Seguimos muy satisfechos", "STABLE", "LOW"),
],
"RETAIL_CHAIN_03": [
(30, 75, "Satisfecho generalmente", "STABLE", "MEDIUM"),
(18, 55, "Algunos problemas pero nada crítico", "DECLINING", "MEDIUM"),
(5, 45, "Consideramos otros proveedores", "DECLINING", "MEDIUM"),
],
"HEALTH_SVC_04": [
(30, 62, "Buen inicio pero necesita mejoras", "RISING", "LOW"),
(18, 75, "Vemos mejoras significativas", "RISING", "LOW"),
(5, 92, "Excelente, funciona perfecto", "RISING", "LOW"),
],
"FINANCE_GROUP_05": [
(25, 83, "Muy buen servicio, confiamos", "STABLE", "LOW"),
(12, 84, "Excelente soporte continuo", "STABLE", "LOW"),
],
}
for customer_id, registros in conversation_payload.items():
for days_ago, score, mensaje, tendencia, riesgo in registros:
fecha = datetime.now() - timedelta(days=days_ago)
cursor.execute(
"""
INSERT INTO conversations
(customer_id, context_type, analysis_date, messages, sentiment_score,
trend, risk_level, predicted_action, confidence)
VALUES (?, 'customer', ?, ?, ?, ?, ?, ?, ?)
""",
(
customer_id,
fecha,
json.dumps([mensaje]),
score,
tendencia,
riesgo,
"CHURN" if riesgo == "HIGH" else ("ESCALATION" if customer_id == "RETAIL_CHAIN_03" else "RETENTION"),
0.78 if riesgo == "HIGH" else 0.9,
),
)
conn.commit()
print("\n🔄 Updating customer aggregates...")
for cliente in clientes:
cursor.execute(
"""
SELECT AVG(sentiment_score), COUNT(*)
FROM conversations
WHERE customer_id = ?
""",
(cliente["id"],),
)
promedio, total = cursor.fetchone()
if promedio is None:
promedio = 70.0
if total is None:
total = 0
if promedio < 40:
churn = 0.9
elif promedio < 55:
churn = 0.75
elif promedio < 70:
churn = 0.55
elif promedio < 80:
churn = 0.25
else:
churn = 0.05
cursor.execute(
"""
UPDATE customer_profiles
SET lifetime_sentiment = ?,
churn_risk = ?,
total_interactions = ?,
last_contact = ?
WHERE customer_id = ?
""",
(
round(promedio, 2),
churn,
total,
datetime.now(),
cliente["id"],
),
)
# Manual adjustments to align with scripted storyline
cursor.execute("UPDATE customer_profiles SET churn_risk = 0.85 WHERE customer_id = 'ACME_CORP_001'")
cursor.execute("UPDATE customer_profiles SET churn_risk = 0.55 WHERE customer_id = 'RETAIL_CHAIN_03'")
cursor.execute("UPDATE customer_profiles SET churn_risk = 0.05 WHERE customer_id IN ('TECH_STARTUP_02','FINANCE_GROUP_05')")
cursor.execute("UPDATE customer_profiles SET churn_risk = 0.09 WHERE customer_id = 'HEALTH_SVC_04'")
conn.commit()
print("\n🚨 Registering risk alerts...")
cursor.execute(
"""
INSERT INTO risk_alerts (customer_id, alert_type, severity, created_at, resolved, notes)
VALUES (?, 'CHURN_RISK', 'HIGH', ?, 0, ?)
""",
(
"ACME_CORP_001",
datetime.now(),
"Crisis detectada: el sentimiento cayó de 85 a 15 en 30 días",
),
)
cursor.execute(
"""
INSERT INTO risk_alerts (customer_id, alert_type, severity, created_at, resolved, notes)
VALUES (?, 'CHURN_RISK', 'MEDIUM', ?, 0, ?)
""",
(
"RETAIL_CHAIN_03",
datetime.now() - timedelta(days=5),
"Declive sostenido, comparando proveedores",
),
)
conn.commit()
print("\n📊 Database summary")
cursor.execute("SELECT COUNT(*) FROM customer_profiles")
print(f" • Customers: {cursor.fetchone()[0]}")
cursor.execute("SELECT COUNT(*) FROM conversations")
print(f" • Conversations: {cursor.fetchone()[0]}")
cursor.execute("SELECT COUNT(*) FROM risk_alerts")
print(f" • Alerts: {cursor.fetchone()[0]}")
cursor.execute("SELECT ROUND(AVG(sentiment_score), 2) FROM conversations")
print(f" • Avg Sentiment: {cursor.fetchone()[0]}/100")
conn.close()
print("\n✅ Demo data created successfully!")
|