Automate WhatsApp Reports with Python

# Method 1: pywhatkit (personal/testing only)
pip install pywhatkit

# Method 2: Twilio (production)
pip install twilio

# Supporting libraries
pip install pandas openpyxl schedule python-dotenv requests

import pywhatkit as kit
import datetime

# Send a message immediately (waits 15 seconds then sends)
# Phone number must include country code: +91 for India
kit.sendwhatmsg_instantly(
    phone_no    = "+919999999999",
    message     = "Daily Sales Report: ₹42L | Target: ₹45L | Gap: ₹3L",
    wait_time   = 15,       # seconds to wait for WhatsApp Web to load
    tab_close   = True,    # close the tab after sending
    close_time  = 3        # seconds to wait before closing
)

# Schedule a message for a specific time today
now  = datetime.datetime.now()
hour = now.hour
mins = now.minute + 2   # send 2 minutes from now

kit.sendwhatmsg(
    phone_no     = "+919999999999",
    message      = "Good morning! Today's MIS report is attached.",
    time_hour    = hour,
    time_min     = mins,
    wait_time    = 20,
    tab_close    = True
)

# Send to a WhatsApp Group (use group name exactly)
kit.sendwhatmsg_to_group_instantly(
    group_id    = "Sales Team India",   # exact group name
    message     = "🔔 EOD Sales Summary — See below",
    wait_time   = 15,
    tab_close   = True
)

# .env — store all secrets here
TWILIO_ACCOUNT_SID=ACxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
TWILIO_AUTH_TOKEN=your_auth_token_here
TWILIO_WHATSAPP_FROM=whatsapp:+14155238886   # Twilio sandbox number
REPORT_RECIPIENT=whatsapp:+919999999999      # your WhatsApp number

from dotenv import load_dotenv
import os

load_dotenv()  # loads .env file

ACCOUNT_SID = os.getenv("TWILIO_ACCOUNT_SID")
AUTH_TOKEN  = os.getenv("TWILIO_AUTH_TOKEN")
FROM_NUMBER = os.getenv("TWILIO_WHATSAPP_FROM")
TO_NUMBER   = os.getenv("REPORT_RECIPIENT")

from twilio.rest import Client
from dotenv      import load_dotenv
import os

load_dotenv()

client = Client(
    os.getenv("TWILIO_ACCOUNT_SID"),
    os.getenv("TWILIO_AUTH_TOKEN")
)

# Send a plain text message
message = client.messages.create(
    from_ = os.getenv("TWILIO_WHATSAPP_FROM"),
    to    = os.getenv("REPORT_RECIPIENT"),
    body  = "Hello from Python! Your daily report is ready."
)

print(f"Message sent! SID: {message.sid}")
print(f"Status: {message.status}")  # queued → sent → delivered

import pandas as pd
from datetime   import datetime
from twilio.rest import Client
from dotenv      import load_dotenv
import os

load_dotenv()
client = Client(os.getenv("TWILIO_ACCOUNT_SID"), os.getenv("TWILIO_AUTH_TOKEN"))

def build_sales_report(df: pd.DataFrame) -> str:
    """Build a formatted WhatsApp sales report from a DataFrame."""
    today        = datetime.now().strftime("%d %b %Y")
    total_rev    = df["Revenue"].sum()
    total_orders = len(df)
    top_region   = df.groupby("Region")["Revenue"].sum().idxmax()
    top_rev      = df.groupby("Region")["Revenue"].sum().max()
    target       = 4500000   # ₹45L daily target
    gap          = target - total_rev
    pct          = total_rev / target * 100

    # WhatsApp formatting: *bold*, _italic_, emoji
    report = f"""📊 *Daily Sales Report — {today}*
━━━━━━━━━━━━━━━━━━━━
💰 *Revenue Today:* ₹{total_rev/100000:.1f}L
🎯 *Target:* ₹{target/100000:.1f}L
📈 *Achievement:* {pct:.1f}%
{"✅" if gap <= 0 else "⚠️"} *Gap:* ₹{abs(gap)/100000:.1f}L {"(Target Met! 🎉)" if gap <= 0 else "(Shortfall)"}

📦 *Total Orders:* {total_orders:,}
🏆 *Top Region:* {top_region} — ₹{top_rev/100000:.1f}L

*Region Breakdown:*
"""

    # Add region-wise breakdown
    region_summary = df.groupby("Region")["Revenue"].sum().sort_values(ascending=False)
    for region, rev in region_summary.items():
        bar = "█" * int(rev / total_rev * 10)
        report += f"{region}: ₹{rev/100000:.1f}L {bar}\n"

    report += f"\n_Generated at {datetime.now().strftime('%H:%M')} | Pyivot Solutions_"
    return report


def send_whatsapp_report(message: str, recipients: list) -> None:
    """Send a WhatsApp report to a list of recipients."""
    for number in recipients:
        try:
            msg = client.messages.create(
                from_ = os.getenv("TWILIO_WHATSAPP_FROM"),
                to    = f"whatsapp:{number}",
                body  = message
            )
            print(f"✅ Sent to {number} | SID: {msg.sid}")
        except Exception as e:
            print(f"❌ Failed for {number}: {e}")


# ── Run it ────────────────────────────────────────────────
if __name__ == "__main__":
    # Load your sales data
    df = pd.read_excel("today_sales.xlsx")

    report = build_sales_report(df)
    print(report)   # preview before sending

    recipients = ["+919999999999", "+918888888888"]
    send_whatsapp_report(report, recipients)

def send_report_with_image(image_url: str, caption: str, recipient: str) -> None:
    """Send a WhatsApp message with an image attachment."""
    try:
        msg = client.messages.create(
            from_     = os.getenv("TWILIO_WHATSAPP_FROM"),
            to        = f"whatsapp:{recipient}",
            body      = caption,
            media_url = [image_url]   # must be a public HTTPS URL
        )
        print(f"✅ Image sent | SID: {msg.sid}")
    except Exception as e:
        print(f"❌ Error: {e}")


# Send a dashboard screenshot
send_report_with_image(
    image_url = "https://yourdomain.com/reports/dashboard_today.png",
    caption   = "📊 *Daily Dashboard — 20 Apr 2025*\nFull report in the image above.",
    recipient = "+919999999999"
)

# Send a PDF report
send_report_with_image(
    image_url = "https://yourdomain.com/reports/monthly_mis.pdf",
    caption   = "📄 *Monthly MIS Report — April 2025*",
    recipient = "+919999999999"
)

import matplotlib.pyplot as plt
import matplotlib
matplotlib.use('Agg')   # non-interactive backend (for servers)
import pandas as pd

def generate_sales_chart(df: pd.DataFrame, output_path: str) -> str:
    """Generate a revenue bar chart and save to disk."""
    region_data = df.groupby("Region")["Revenue"].sum().sort_values()

    fig, ax = plt.subplots(figsize=(10, 6))
    bars = ax.barh(region_data.index, region_data.values / 100000,
                   color="#0A66C2", edgecolor="none")

    # Add value labels on bars
    for bar, val in zip(bars, region_data.values):
        ax.text(val / 100000 + 0.1, bar.get_y() + bar.get_height() / 2,
                f"₹{val/100000:.1f}L", va="center", fontsize=11, color="#333")

    ax.set_xlabel("Revenue (₹ Lakhs)")
    ax.set_title(f"Daily Revenue by Region — {pd.Timestamp.today().strftime('%d %b %Y')}",
                 fontsize=14, fontweight="bold", pad=15)
    ax.spines[["top", "right"]].set_visible(False)

    plt.tight_layout()
    plt.savefig(output_path, dpi=150, bbox_inches="tight")
    plt.close()
    return output_path


# Usage
df = pd.read_excel("today_sales.xlsx")
chart_path = generate_sales_chart(df, "sales_chart.png")

# Upload to your hosting and get the public URL
# (see Section 9 for automated upload to Hostinger via FTP)
public_url = "https://etlguru.in/reports/sales_chart.png"

send_report_with_image(
    image_url = public_url,
    caption   = "📊 *Revenue by Region — Today*\nSee full report for details.",
    recipient = "+919999999999"
)

"""
daily_report_bot.py
Automated daily WhatsApp sales report
Author: Ankit Kumar | ETLGuru.in
Run: python daily_report_bot.py
"""

import schedule
import time
import logging
import pandas   as pd
from datetime   import datetime
from twilio.rest import Client
from dotenv      import load_dotenv
import os

# ── Setup ─────────────────────────────────────────────────
load_dotenv()
logging.basicConfig(
    level   = logging.INFO,
    format  = "%(asctime)s [%(levelname)s] %(message)s",
    handlers= [logging.FileHandler("report_bot.log"), logging.StreamHandler()]
)
log = logging.getLogger(__name__)

client = Client(
    os.getenv("TWILIO_ACCOUNT_SID"),
    os.getenv("TWILIO_AUTH_TOKEN")
)

RECIPIENTS = [
    "+919999999999",   # CEO
    "+918888888888",   # Sales Manager
    "+917777777777",   # Operations Head
]

# ── Data loading ──────────────────────────────────────────
def load_todays_data() -> pd.DataFrame:
    """Load today's sales data from your source."""
    # Replace with your actual data source:
    # pd.read_sql("SELECT * FROM FactSales WHERE DATE(OrderDate)=CURDATE()", conn)
    # pd.read_excel("shared_drive/today_sales.xlsx")
    df = pd.read_excel("data/today_sales.xlsx")
    return df

# ── Report builder ────────────────────────────────────────
def build_report(df: pd.DataFrame) -> str:
    today       = datetime.now().strftime("%d %b %Y")
    total_rev   = df["Revenue"].sum()
    orders      = len(df)
    avg_order   = total_rev / orders if orders > 0 else 0
    target      = 4500000
    achievement = total_rev / target * 100
    top_region  = df.groupby("Region")["Revenue"].sum().idxmax()

    status = "✅ ON TRACK" if achievement >= 95 else ("⚠️ AT RISK" if achievement >= 85 else "🔴 BEHIND")

    region_lines = ""
    for region, rev in df.groupby("Region")["Revenue"].sum().sort_values(ascending=False).items():
        region_lines += f"  • {region}: ₹{rev/100000:.1f}L\n"

    return f"""🏢 *DAILY MIS REPORT*
📅 {today} | {status}
━━━━━━━━━━━━━━━━━━━━━━

💰 *Revenue:* ₹{total_rev/100000:.2f}L
🎯 *Target:* ₹{target/100000:.1f}L
📊 *Achievement:* {achievement:.1f}%

📦 *Orders:* {orders:,}
💵 *Avg Order Value:* ₹{avg_order:,.0f}
🏆 *Top Region:* {top_region}

*Region Breakdown:*
{region_lines}
_ETLGuru.in | Pyivot Solutions_
_Sent at {datetime.now().strftime("%H:%M")} IST_"""

# ── Sender ────────────────────────────────────────────────
def send_report() -> None:
    log.info("Starting daily report job...")
    try:
        df     = load_todays_data()
        report = build_report(df)
        log.info(f"Report built: {len(report)} characters")

        for number in RECIPIENTS:
            try:
                msg = client.messages.create(
                    from_ = os.getenv("TWILIO_WHATSAPP_FROM"),
                    to    = f"whatsapp:{number}",
                    body  = report
                )
                log.info(f"Sent to {number}: {msg.sid}")
                time.sleep(1)   # small delay between messages
            except Exception as e:
                log.error(f"Failed for {number}: {e}")

        log.info("Daily report job complete")

    except Exception as e:
        log.error(f"Report job failed: {e}")

# ── Scheduler ─────────────────────────────────────────────
schedule.every().monday.at("09:00").do(send_report)
schedule.every().tuesday.at("09:00").do(send_report)
schedule.every().wednesday.at("09:00").do(send_report)
schedule.every().thursday.at("09:00").do(send_report)
schedule.every().friday.at("09:00").do(send_report)
# EOD summary at 6:30 PM
schedule.every().day.at("18:30").do(send_report)

if __name__ == "__main__":
    log.info("Report bot started — running scheduler...")
    send_report()   # send immediately on startup
    while True:
        schedule.run_pending()
        time.sleep(30)

def check_and_alert(df: pd.DataFrame) -> None:
    """Send WhatsApp alerts only when thresholds are breached."""
    alerts = []

    # ── Revenue alert ─────────────────────────────────────────
    hourly_rev = df[df["Hour"] == datetime.now().hour]["Revenue"].sum()
    if hourly_rev < 200000:   # below ₹2L in this hour
        alerts.append(f"💸 *Low Revenue Alert*\nThis hour: ₹{hourly_rev/1000:.0f}K (target ₹2L+)")

    # ── Stockout alert ────────────────────────────────────────
    stockouts = df[df["StockDays"] <= 3]
    if not stockouts.empty:
        items = ", ".join(stockouts["ProductName"].tolist()[:5])
        alerts.append(f"🚨 *Stockout Risk*\n{len(stockouts)} items < 3 days stock:\n{items}")

    # ── Return rate alert ─────────────────────────────────────
    return_rate = df["IsReturn"].mean() * 100
    if return_rate > 5:
        alerts.append(f"⚠️ *High Return Rate*\nToday: {return_rate:.1f}% (threshold: 5%)")

    # ── Send all alerts ───────────────────────────────────────
    if alerts:
        full_alert = ("🔔 *BUSINESS ALERTS — "
                    + datetime.now().strftime("%d %b %Y, %H:%M")
                    + " IST*\n━━━━━━━━━━━━━━━━━\n\n"
                    + "\n\n".join(alerts))
        for number in RECIPIENTS:
            client.messages.create(
                from_ = os.getenv("TWILIO_WHATSAPP_FROM"),
                to    = f"whatsapp:{number}",
                body  = full_alert
            )
        log.info(f"Sent {len(alerts)} alerts to {len(RECIPIENTS)} recipients")
    else:
        log.info("No alerts triggered — all metrics within thresholds")

# Run alert checks every 30 minutes
schedule.every(30).minutes.do(lambda: check_and_alert(load_todays_data()))