""" CXInsights Dashboard - Export Functions Export insights to Excel, PDF, and other formats. """ import io import json from datetime import datetime from pathlib import Path import pandas as pd import streamlit as st from config import COLORS def create_excel_export(summary: dict, analyses: list[dict], batch_id: str) -> io.BytesIO: """ Create comprehensive Excel export with multiple sheets. Sheets: - Executive Summary - Call Details - Poor CX Drivers - FCR Analysis - Churn Risk - Agent Performance """ output = io.BytesIO() with pd.ExcelWriter(output, engine='openpyxl') as writer: # Sheet 1: Executive Summary summary_data = { "Metric": [ "Batch ID", "Generated At", "Total Calls Analyzed", "Successful Analyses", "Failed Analyses", "Poor CX Drivers Found", "Lost Sales Drivers Found", ], "Value": [ batch_id, summary.get("generated_at", "N/A"), summary.get("summary", {}).get("total_calls", 0), summary.get("summary", {}).get("successful_analyses", 0), summary.get("summary", {}).get("failed_analyses", 0), summary.get("poor_cx", {}).get("total_drivers_found", 0), summary.get("lost_sales", {}).get("total_drivers_found", 0), ] } df_summary = pd.DataFrame(summary_data) df_summary.to_excel(writer, sheet_name="Executive Summary", index=False) # Sheet 2: Outcomes Distribution outcomes = summary.get("outcomes", {}) if outcomes: df_outcomes = pd.DataFrame([ {"Outcome": k, "Count": v, "Percentage": f"{v/sum(outcomes.values())*100:.1f}%"} for k, v in sorted(outcomes.items(), key=lambda x: -x[1]) ]) df_outcomes.to_excel(writer, sheet_name="Outcomes", index=False) # Sheet 3: Call Details call_data = [] for a in analyses: call_data.append({ "Call ID": a.get("call_id", ""), "Outcome": a.get("outcome", ""), "FCR Status": a.get("fcr_status", ""), "Churn Risk": a.get("churn_risk", ""), "Agent Classification": a.get("agent_classification", ""), "Poor CX Drivers": len(a.get("poor_cx_drivers", [])), "FCR Failure Drivers": len(a.get("fcr_failure_drivers", [])), "Churn Risk Drivers": len(a.get("churn_risk_drivers", [])), "Duration (sec)": a.get("observed", {}).get("audio_duration_sec", ""), "Total Turns": a.get("observed", {}).get("turn_metrics", {}).get("total_turns", ""), }) df_calls = pd.DataFrame(call_data) df_calls.to_excel(writer, sheet_name="Call Details", index=False) # Sheet 4: Poor CX Drivers Detail poor_cx_data = [] for a in analyses: for d in a.get("poor_cx_drivers", []): poor_cx_data.append({ "Call ID": a.get("call_id", ""), "Driver Code": d.get("driver_code", ""), "Confidence": f"{d.get('confidence', 0):.0%}", "Origin": d.get("origin", ""), "Reasoning": d.get("reasoning", ""), "Corrective Action": d.get("corrective_action", ""), "Evidence": "; ".join([e.get("text", "") for e in d.get("evidence_spans", [])]), }) if poor_cx_data: df_poor_cx = pd.DataFrame(poor_cx_data) df_poor_cx.to_excel(writer, sheet_name="Poor CX Drivers", index=False) # Sheet 5: FCR Failure Drivers fcr_data = [] for a in analyses: for d in a.get("fcr_failure_drivers", []): fcr_data.append({ "Call ID": a.get("call_id", ""), "Driver Code": d.get("driver_code", ""), "Confidence": f"{d.get('confidence', 0):.0%}", "Origin": d.get("origin", ""), "Reasoning": d.get("reasoning", ""), "Corrective Action": d.get("corrective_action", ""), }) if fcr_data: df_fcr = pd.DataFrame(fcr_data) df_fcr.to_excel(writer, sheet_name="FCR Failures", index=False) # Sheet 6: Churn Risk Drivers churn_data = [] for a in analyses: for d in a.get("churn_risk_drivers", []): churn_data.append({ "Call ID": a.get("call_id", ""), "Risk Level": a.get("churn_risk", ""), "Driver Code": d.get("driver_code", ""), "Confidence": f"{d.get('confidence', 0):.0%}", "Reasoning": d.get("reasoning", ""), "Corrective Action": d.get("corrective_action", ""), }) if churn_data: df_churn = pd.DataFrame(churn_data) df_churn.to_excel(writer, sheet_name="Churn Risk", index=False) # Sheet 7: Agent Performance agent_data = [] for a in analyses: positive = [s.get("skill_code", "") for s in a.get("agent_positive_skills", [])] improvement = [s.get("skill_code", "") for s in a.get("agent_improvement_areas", [])] agent_data.append({ "Call ID": a.get("call_id", ""), "Classification": a.get("agent_classification", ""), "Positive Skills": ", ".join(positive), "Improvement Areas": ", ".join(improvement), }) df_agent = pd.DataFrame(agent_data) df_agent.to_excel(writer, sheet_name="Agent Performance", index=False) # Sheet 8: Top Drivers Summary top_drivers = [] for d in summary.get("poor_cx", {}).get("top_drivers", []): top_drivers.append({ "Type": "Poor CX", "Driver Code": d.get("driver_code", ""), "Occurrences": d.get("occurrences", 0), "Call Rate": f"{d.get('call_rate', 0)*100:.1f}%", "Avg Confidence": f"{d.get('avg_confidence', 0):.0%}", }) for d in summary.get("lost_sales", {}).get("top_drivers", []): top_drivers.append({ "Type": "Lost Sales", "Driver Code": d.get("driver_code", ""), "Occurrences": d.get("occurrences", 0), "Call Rate": f"{d.get('call_rate', 0)*100:.1f}%", "Avg Confidence": f"{d.get('avg_confidence', 0):.0%}", }) if top_drivers: df_top = pd.DataFrame(top_drivers) df_top.to_excel(writer, sheet_name="Top Drivers Summary", index=False) output.seek(0) return output def create_executive_summary_html(summary: dict, analyses: list[dict], batch_id: str) -> str: """ Create HTML executive summary report for PDF export. """ total_calls = summary.get("summary", {}).get("total_calls", 0) # Calculate metrics poor_cx_calls = sum(1 for a in analyses if len(a.get("poor_cx_drivers", [])) > 0) poor_cx_rate = (poor_cx_calls / total_calls * 100) if total_calls > 0 else 0 high_churn = sum(1 for a in analyses if a.get("churn_risk") in ["HIGH", "AT_RISK"]) churn_rate = (high_churn / total_calls * 100) if total_calls > 0 else 0 # FCR rate - Per blueprint: Primera Llamada = FCR success fcr_success = sum(1 for a in analyses if a.get("fcr_status") == "FIRST_CALL") fcr_rate = (fcr_success / total_calls * 100) if total_calls > 0 else 0 # Top drivers top_drivers = summary.get("poor_cx", {}).get("top_drivers", [])[:5] # Outcomes outcomes = summary.get("outcomes", {}) html = f""" CXInsights Executive Report - {batch_id}
beyondcx

CXInsights Executive Report

Batch: {batch_id} | Generated: {datetime.now().strftime('%Y-%m-%d %H:%M')} | Calls Analyzed: {total_calls}
{total_calls}
Total Calls
{poor_cx_rate:.1f}%
Poor CX Rate
{fcr_rate:.1f}%
FCR Rate
{churn_rate:.1f}%
Churn Risk

Key Insights

{"".join([f'
{d.get("driver_code", "")} detected in {d.get("occurrences", 0)} calls ({d.get("call_rate", 0)*100:.0f}% of total)
' for d in top_drivers[:3]]) if top_drivers else '

No critical drivers detected.

'}

Outcome Distribution

{"".join([f'' for k, v in sorted(outcomes.items(), key=lambda x: -x[1])]) if outcomes else ''}
Outcome Count Percentage
{k}{v}{v/sum(outcomes.values())*100:.1f}%
No data

Top Poor CX Drivers

{"".join([f'' for d in top_drivers]) if top_drivers else ''}
Driver Occurrences Call Rate Confidence
{d.get("driver_code", "")}{d.get("occurrences", 0)}{d.get("call_rate", 0)*100:.1f}%{d.get("avg_confidence", 0):.0%}
No drivers detected

Generated by Beyond CXInsights | {datetime.now().strftime('%Y-%m-%d %H:%M')}

This report contains AI-generated insights. Please review with domain expertise.

""" return html def create_json_export(summary: dict, analyses: list[dict], batch_id: str) -> str: """Create JSON export of all data.""" export_data = { "batch_id": batch_id, "exported_at": datetime.now().isoformat(), "summary": summary, "analyses": analyses, } return json.dumps(export_data, indent=2, ensure_ascii=False) def render_export_section(summary: dict, analyses: list[dict], batch_id: str): """Render export options in the dashboard.""" st.markdown("### Export Options") col1, col2, col3 = st.columns(3) with col1: st.markdown("#### Excel Report") st.caption("Complete analysis with multiple sheets") excel_data = create_excel_export(summary, analyses, batch_id) st.download_button( label="Download Excel", data=excel_data, file_name=f"cxinsights_{batch_id}_{datetime.now().strftime('%Y%m%d')}.xlsx", mime="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", use_container_width=True, ) with col2: st.markdown("#### Executive Summary") st.caption("HTML report (print to PDF)") html_data = create_executive_summary_html(summary, analyses, batch_id) st.download_button( label="Download HTML", data=html_data, file_name=f"cxinsights_{batch_id}_executive_{datetime.now().strftime('%Y%m%d')}.html", mime="text/html", use_container_width=True, ) with col3: st.markdown("#### Raw Data") st.caption("JSON format for integration") json_data = create_json_export(summary, analyses, batch_id) st.download_button( label="Download JSON", data=json_data, file_name=f"cxinsights_{batch_id}_{datetime.now().strftime('%Y%m%d')}.json", mime="application/json", use_container_width=True, ) st.markdown("---") # Quick stats st.markdown("#### Export Preview") col1, col2 = st.columns(2) with col1: st.markdown("**Excel sheets included:**") st.markdown(""" - Executive Summary - Outcomes Distribution - Call Details - Poor CX Drivers - FCR Failures - Churn Risk - Agent Performance - Top Drivers Summary """) with col2: st.markdown("**Data summary:**") st.markdown(f""" - **Calls:** {len(analyses)} - **Poor CX instances:** {sum(len(a.get('poor_cx_drivers', [])) for a in analyses)} - **FCR failures:** {sum(len(a.get('fcr_failure_drivers', [])) for a in analyses)} - **Churn risk drivers:** {sum(len(a.get('churn_risk_drivers', [])) for a in analyses)} """)