Automate Customer Follow-Ups and Reports with Python: A Small Business Workflow

import smtplib
import csv
from email.mime.text import MIMEText
from datetime import datetime, timedelta
import logging

# ------------------------------------------------
# CONFIGURATION — fill in your Gmail credentials
# ------------------------------------------------
SMTP_SERVER = "smtp.gmail.com"
SMTP_PORT = 587
SENDER_EMAIL = "you@gmail.com"         # <-- CHANGE THIS
SENDER_PASSWORD = "your-app-password"  # <-- USE GMAIL APP PASSWORD

CSV_FILE = "customers.csv"
LOG_FILE = "automation_log.txt"

# ---- Message templates ----
TEMPLATES = {
    "quote_sent": (
        "Hi {name},\n\n"
        "I just wanted to check in on the quote we sent you on {last_contact}. "
        "Happy to adjust the pricing or quantities if needed.\n\n"
        "Let me know — no pressure.\n\nBest,\nYour Business"
    ),
    "appointment": (
        "Hi {name},\n\n"
        "Thanks again for the consultation on {last_contact}. "
        "If you have any follow-up questions just reply to this email.\n\n"
        "Looking forward to working with you!\n\nBest,\nYour Business"
    ),
    "cart_abandon": (
        "Hi {name},\n\n"
        "I noticed you left some items in your cart. "
        "I'd be happy to reserve them for you — just let me know.\n\n"
        "Thanks,\nYour Business"
    ),
    "followup": (
        "Hi {name},\n\n"
        "Quick check-in regarding your pending order. "
        "Need any help finalizing it?\n\n"
        "Cheers,\nYour Business"
    ),
}

# ---- Sales data (replace with your real numbers) ----
WEEKLY_SALES = {
    "items_sold": 142,
    "revenue_idr": 4875000,
    "new_customers": 8,
    "top_product": "Free-range eggs (12-pack)",
}

# ------------------------------------------------
# SETUP LOGGING
# ------------------------------------------------
logging.basicConfig(
    filename=LOG_FILE,
    level=logging.INFO,
    format="%(asctime)s — %(levelname)s — %(message)s",
    datefmt="%Y-%m-%d %H:%M:%S",
)
logger = logging.getLogger("followup_reporter")


def send_email(to_email: str, subject: str, body: str) -> bool:
    """Send plain-text email via Gmail SMTP."""
    msg = MIMEText(body, "plain")
    msg["Subject"] = subject
    msg["From"] = SENDER_EMAIL
    msg["To"] = to_email

    try:
        with smtplib.SMTP(SMTP_SERVER, SMTP_PORT) as server:
            server.starttls()
            server.login(SENDER_EMAIL, SENDER_PASSWORD)
            server.send_message(msg)
        logger.info(f"Email sent to {to_email} — subject: {subject}")
        return True
    except Exception as e:
        logger.error(f"Failed to send email to {to_email}: {e}")
        return False


def process_customers():
    """Read CSV and send follow-up emails where last_contact is >7 days ago."""
    today = datetime.now()
    seven_days_ago = today - timedelta(days=7)

    sent = 0
    skipped = 0

    try:
        with open(CSV_FILE, "r", newline="", encoding="utf-8") as f:
            reader = csv.DictReader(f)
            for row in reader:
                name = row["name"].strip()
                email = row["email"].strip()
                ctype = row.get("type", "followup").strip()
                last_contact_str = row.get("last_contact", "").strip()

                if not email:
                    skipped += 1
                    continue

                # Skip customers contacted within the last 7 days
                if last_contact_str:
                    try:
                        last_contact_date = datetime.strptime(last_contact_str, "%Y-%m-%d")
                        if last_contact_date > seven_days_ago:
                            logger.info(f"Skipped {name} — contacted recently ({last_contact_str})")
                            skipped += 1
                            continue
                    except ValueError:
                        logger.warning(f"Could not parse date for {name}: {last_contact_str}")

                template = TEMPLATES.get(ctype, TEMPLATES["followup"])
                body = template.format(name=name, last_contact=last_contact_str)
                subject = f"Checking in, {name}"

                if send_email(email, subject, body):
                    sent += 1
                else:
                    skipped += 1

    except FileNotFoundError:
        logger.error(f"CSV file {CSV_FILE} not found.")
        print(f"ERROR: {CSV_FILE} not found — create it first.")
        return

    return sent, skipped


def generate_weekly_report():
    """Print and log a weekly sales summary."""
    report = (
        f"📊 WEEKLY SALES REPORT — {datetime.now().strftime('%b %d, %Y')}\n"
        f"{'─' * 45}\n"
        f"Items sold         : {WEEKLY_SALES['items_sold']}\n"
        f"Revenue (IDR)      : Rp {WEEKLY_SALES['revenue_idr']:,}\n"
        f"New customers      : {WEEKLY_SALES['new_customers']}\n"
        f"Top product         : {WEEKLY_SALES['top_product']}\n"
        f"{'─' * 45}\n"
    )
    print(report)
    logger.info(f"Weekly report generated:\n{report}")
    return report


def main():
    print("=== Customer Follow-Up + Weekly Report Automation ===\n")

    # Step 1 — follow-ups
    print("📬 Sending follow-up emails...")
    result = process_customers()
    if result:
        sent, skipped = result
        print(f"   ✅ Sent: {sent}   ⏭️  Skipped: {skipped}\n")

    # Step 2 — weekly report
    print("📈 Generating weekly report...")
    report = generate_weekly_report()

    print("✅ Done. Check automation_log.txt for full details.")


if __name__ == "__main__":
    main()

python3 followup_reporter.py

crontab -e
# Add this line:
0 8 * * 1 cd /path/to/your/folder && python3 followup_reporter.py