Create Data Reports Using AI

Company,Month,Region,Total Sales,Product A Sales,Product B Sales,Product C Sales,Quarter Target Achieved,Top Sales Rep,New Clients,Client Churn Rate,Growth vs Last Quarter,Marketing Spend,Customer Satisfaction,Notable Events
AcmeCorp,2024-03,North,98000,45000,37000,16000,Yes,Sarah Dee,13,2%,5.5%,11500,8.9,"Launched B2B platform"
AcmeCorp,2024-03,South,76800,30000,39000,7800,Yes,John Lee,9,3%,4.5%,9200,8.3,"New partnership established"
BetaBiz,2024-03,West,82000,22000,47000,13000,No,Monica Tai,7,4%,-1.0%,10900,7.3,"Ad campaign underperformed"
BetaBiz,2024-03,East,94500,40000,41000,13500,Yes,Derek Shah,11,2.2%,7.2%,12400,8.6,"Exceeded upsell targets"
ZenithLtd,2024-03,Central,101300,38000,51200,12000,Yes,Rita Ganesh,14,1.1%,9.6%,13800,9.2,"Record-high client retention"
ZenithLtd,2024-03,East,85450,25000,47000,13450,No,Marcus Bell,8,3.8%,-2.8%,10100,7.9,"Sales dip in Product C"
GammaInc,2024-03,North,91200,39000,43000,9200,Yes,Emily Jones,10,1.5%,6.2%,11800,8.8,"Employee incentive program"
GammaInc,2024-03,Sales Ops,79000,31000,39000,9000,No,David Yu,6,5.5%,-3.2%,8700,7.1,"System migration delayed"

pip install requests pandas

import os
import pandas as pd
import requests

os.makedirs('reports', exist_ok=True)

df = pd.read_csv("sales_data.csv")

company_groups = df.groupby("Company")

def build_prompt(company, group):
    """
    Build a markdown prompt with data summary, chart instructions, and slide structure.
    """
    summary = []
    regions = group['Region'].unique()
    total_sales = group['Total Sales'].sum()
    total_clients = group['New Clients'].sum()
    churn = group['Client Churn Rate'].mean()
    satisfaction = group['Customer Satisfaction'].mean()
    growth = group['Growth vs Last Quarter'].mean()
    marketing = group['Marketing Spend'].sum()
    notable = "; ".join(group['Notable Events'].unique())

    # Markdown-structured prompt
    prompt = f"""
## Sales Report for {company}

### 1. Executive Summary
- Total sales: **${total_sales:,.0f}**
- Average client churn: **{churn:.2f}%**
- Customer satisfaction: **{satisfaction:.2f}/10**
- Notable events: _{notable}_

### 2. Regional Performance
**Bar Chart:** Regional Total Sales

| Region | Sales |
|---|---|
"""    
    for region in regions:
        reg_sales = group[group['Region'] == region]['Total Sales'].sum()
        prompt += f"| {region} | ${reg_sales:,.0f} |\n"

    prompt += """

### 3. Product Performance
**Bar Chart:** Sales by Product per Region

| Region | Product A | Product B | Product C |
|---|---|---|---|
"""
    for region in regions:
        gr = group[group['Region'] == region]
        a = gr['Product A Sales'].sum()
        b = gr['Product B Sales'].sum()
        c = gr['Product C Sales'].sum()
        prompt += f"| {region} | ${a:,.0f} | ${b:,.0f} | ${c:,.0f} |\n"

    prompt += f"""

### 4. Key Metrics & Trends
- Aggregate new clients this month: **{total_clients}**
- Mean growth vs last quarter: **{growth:.2f}%**
- Total marketing spend: **${marketing:,.0f}**

### 5. Top Performers
| Region | Top Sales Rep | New Clients |
|---|---|---|
"""
    for region in regions:
        gr = group[group['Region'] == region]
        rep = gr['Top Sales Rep'].iloc[0]
        clients = gr['New Clients'].iloc[0]
        prompt += f"| {region} | {rep} | {clients} |\n"

    prompt += """

---

**Instructions:**
- Create 1 slide per section (5 total).
- Use clean, professional visuals.
- For charts, display the specified bar chart with given data.
- Use summary bullet points before every chart or table for clarity.
**Do exactly as in said here.**
"""

    return prompt

for company, group in company_groups:
    print(f"Generating report for {company}")
    prompt = build_prompt(company, group)
    data = {
        "prompt": prompt,
        "n_slides": "5",
        "language": "English",
        "layout": "general",
        "export_as": "pdf"
    }
    response = requests.post(
        "http://localhost:5000/api/v1/ppt/presentation/generate",
        data=data
    )
    if response.ok:
        result = response.json()
        print("Downloading report...")
        download_url = f"http://localhost:5000{result['path']}"
        filename = f"reports/{company}_Sales_Report.pdf"
        file_response = requests.get(download_url)
        if file_response.ok:
            with open(filename, 'wb') as f:
                f.write(file_response.content)
            print(f"Report for {company} saved as {filename}")
        else:
            print(f"Failed to download report for {company}: {file_response.status_code}")
    else:
        print(f"Failed to generate report for {company}: {response.text}")

python generate_sales_reports.py