Automate Weekly Reports With Python & Free AI APIs

By Andrii Klymenko · Updated August 10, 2026

How I Automated 80% of My Weekly Reporting With 3 Python Scripts and Free AI APIs

The system described here uses three Python scripts working in sequence: a data extraction script, a transformation and aggregation script, and an AI-powered summarization script that calls a free LLM API to generate human-readable narrative. Together, these three scripts handle data pulling, metric calculation, and executive summary generation — the three tasks that consume the majority of a reporting workflow. The 80% figure refers specifically to the manual steps eliminated: copy-pasting from dashboards, writing commentary, and formatting slides. What remains manual is the final quality check and stakeholder-specific edits, which require human judgment.

If you want to automate weekly data reporting with a Python script in a production environment, the honest prerequisite is this: most tutorials show you a working proof-of-concept that breaks the moment it hits real data. Pagination breaks the extraction. Rate limits break the API calls. Silent failures kill the schedule. This article covers the actual architecture — including the error handling, retry logic, and scheduling decisions that tutorials skip.

—

Why Most Reporting Automation Tutorials Fail in Production

The gap between a tutorial and a production system is not about complexity — it is about error handling, statefulness, and observability.

A typical tutorial does this:

`python

data = requests.get(api_url).json()

summary = openai.chat(data)

send_email(summary)

`

A production pipeline needs to handle:

• API timeouts and transient errors — network blips that would silently kill the job
• Partial data delivery — APIs that paginate and return incomplete results if you don’t loop
• LLM hallucinations on structured data — the model confidently summarizing wrong numbers
• Scheduler drift — cron jobs that overlap when the previous run hasn’t finished
• No alert when everything silently succeeds but produces nothing — the empty report problem

The fix for each of these is not exotic. It is a few dozen lines of defensive code. The problem is that tutorials optimize for clarity of the happy path, not resilience of the failure path.

—

The Architecture: 3 Scripts, One Pipeline

Here is how the three-script system is structured:

`

┌─────────────────┐ ┌──────────────────┐ ┌────────────────────┐

│ Script 1: │───▶│ Script 2: │───▶│ Script 3: │

│ extract.py │ │ transform.py │ │ summarize.py │

│ │ │ │ │ │

│ Pull raw data │ │ Aggregate + │ │ LLM API call + │

│ from sources │ │ calculate KPIs │ │ format + deliver │

└─────────────────┘ └──────────────────┘ └────────────────────┘

│ │ │

Saves to Saves to Sends email /

raw_data/ processed/ posts to Slack

`

Each script is independent and restartable. If transform.py fails, you don’t re-pull data. If summarize.py fails, you don’t recalculate metrics. This modularity is the most important architectural decision — it makes debugging fast and re-runs cheap.

—

Script 1: Production-Ready Data Extraction

The extraction script needs three things beyond the basic API call: retry logic, pagination handling, and a checkpoint system.

Retry Logic with Exponential Backoff

`python

import requests

import time

import logging

from pathlib import Path

logging.basicConfig(

level=logging.INFO,

format=’%(asctime)s %(levelname)s %(message)s’,

handlers=[

logging.FileHandler(‘pipeline.log’),

logging.StreamHandler()

]

)

def fetch_with_retry(url, headers, params, max_retries=3, backoff_factor=2):

for attempt in range(max_retries):

try:

response = requests.get(url, headers=headers, params=params, timeout=30)

response.raise_for_status()

return response.json()

except requests.exceptions.RequestException as e:

wait = backoff_factor ** attempt

logging.warning(f”Attempt {attempt + 1} failed: {e}. Retrying in {wait}s”)

time.sleep(wait)

raise RuntimeError(f”All {max_retries} attempts failed for {url}”)

`

Pagination Handling

`python

def fetch_all_pages(base_url, headers, params):

all_records = []

page = 1

while True:

params[‘page’] = page

data = fetch_with_retry(base_url, headers, params)

records = data.get(‘results’, [])

if not records:

break

all_records.extend(records)

logging.info(f”Fetched page {page}, total records: {len(all_records)}”)

Respect rate limits

time.sleep(0.5)

if not data.get(‘next’):

break

page += 1

return all_records

`

Checkpoint System

`python

import json

from datetime import datetime

def save_raw_data(data, source_name):

timestamp = datetime.now().strftime(‘%Y%m%d_%H%M%S’)

output_path = Path(f’raw_data/{source_name}_{timestamp}.json’)

output_path.parent.mkdir(exist_ok=True)

with open(output_path, ‘w’) as f:

json.dump({‘extracted_at’: timestamp, ‘records’: data}, f)

logging.info(f”Saved {len(data)} records to {output_path}”)

return str(output_path)

`

The checkpoint means you have a timestamped snapshot of every pull. When a stakeholder questions a number three weeks later, you have the source file.

—

Script 2: Transformation and KPI Calculation

This script reads from the checkpoint files, not from a live connection. That distinction matters: your metrics are calculated from the same data your extraction captured, not from a second API call that might return slightly different results.

`python

import pandas as pd

import json

from pathlib import Path

from datetime import datetime, timedelta

def load_latest_raw(source_name):

raw_files = sorted(Path(‘raw_data’).glob(f'{source_name}_*.json’))

if not raw_files:

raise FileNotFoundError(f”No raw data files found for {source_name}”)

latest = raw_files[-1]

logging.info(f”Loading from checkpoint: {latest}”)

with open(latest) as f:

return json.load(f)[‘records’]

def calculate_kpis(records):

df = pd.DataFrame(records)

Define your date window explicitly — never assume “last 7 days” is ambiguous

end_date = datetime.now().date()

start_date = end_date – timedelta(days=7)

df[‘date’] = pd.to_datetime(df[‘date’]).dt.date

week_df = df[(df[‘date’] >= start_date) & (df[‘date’] <= end_date)]

metrics = {

‘period_start’: str(start_date),

‘period_end’: str(end_date),

‘total_records’: len(week_df),

‘daily_breakdown’: week_df.groupby(‘date’).size().to_dict(),

Add your domain-specific aggregations here

}

return metrics

`

The critical production habit here: always log the date range your calculation covers. When a report says “last week” and a reader is looking at it on Wednesday, “last week” is ambiguous. Log the explicit ISO dates and include them in the report output.

—

Script 3: Free LLM APIs for AI-Powered Summarization

This is where the automated reporting pipeline with a free LLM API gets practical. Several providers offer free tiers that are sufficient for weekly reporting workloads:

• Google Gemini API — free tier with generous token limits as of 2025
• Groq API — free tier with fast inference on open models
• Together AI — free credits on signup, access to open-source models
• Ollama — fully local, no API cost, runs models like Llama 3 on your own machine

For scheduled automated reporting, Ollama is often the right choice: no rate limit surprises, no API key rotation, no cost at any volume. The tradeoff is that you need a machine with sufficient RAM to run the model.

The Summarization Script with Guardrails

`python

import requests

import json

OLLAMA_URL = “http://localhost:11434/api/generate”

MODEL = “llama3”

def build_prompt(metrics):

“””

Strict prompt engineering: give the model the numbers,

tell it exactly what format to produce, and constrain its behavior.

“””

return f”””You are a data analyst writing a weekly business report.

METRICS FOR THE WEEK {metrics[‘period_start’]} to {metrics[‘period_end’]}:

• Total records processed: {metrics[‘total_records’]}
• Daily breakdown: {json.dumps(metrics[‘daily_breakdown’], indent=2)}

Write a 3-paragraph executive summary:

1. What happened this week (numbers only, no editorializing)
2. Notable patterns in the daily breakdown
3. One specific recommendation based on the data

Rules:

• Do not invent numbers not provided above
• Do not use phrases like “significant increase” without a specific percentage
• Keep each paragraph under 80 words
• Output only the summary text, no headers or labels”””

def generate_summary(metrics, max_retries=2):

prompt = build_prompt(metrics)

for attempt in range(max_retries):

try:

response = requests.post(

OLLAMA_URL,

json={

“model”: MODEL,

“prompt”: prompt,

“stream”: False,

“options”: {“temperature”: 0.2} # Low temp = more factual

},

timeout=120

)

response.raise_for_status()

summary = response.json()[‘response’].strip()

Validation: check the summary references actual numbers

if str(metrics[‘total_records’]) not in summary:

logging.warning(“LLM output does not reference key metric — review required”)

return summary

except Exception as e:

logging.error(f”LLM call failed (attempt {attempt + 1}): {e}”)

if attempt == max_retries – 1:

return f”[AUTOMATED SUMMARY UNAVAILABLE — manual review required. Raw metrics: {metrics}]”

`

The temperature setting of 0.2 is not arbitrary. Higher temperatures produce more varied prose but also more hallucinated specifics. For a report that includes numbers, low temperature keeps the model closer to the data it was given.

The fallback — returning the raw metrics when the LLM call fails — is what separates a production script from a demo. A failed report is worse than a plain-text one.

—

Scheduling: Why Cron Is Usually Enough (and When It Isn’t)

For most data analyst automation scripts in production, cron is sufficient. Here is a working cron setup:

`bash

Run every Monday at 8:00 AM

0 8 1 cd /home/user/reporting && python extract.py >> logs/cron.log 2>&1

15 8 1 cd /home/user/reporting && python transform.py >> logs/cron.log 2>&1

30 8 1 cd /home/user/reporting && python summarize.py >> logs/cron.log 2>&1

`

The 15-minute gaps between scripts are a simple sequential dependency. For something more robust, use a pipeline runner:

When cron is not enough:

• Scripts take variable time and could overlap
• You need retry on failure without manual intervention
• You want a UI to see run history

Alternatives for these cases:

Tool	Cost	Learning Curve	Retry Logic	UI
Cron	Free	Low	Manual	None
Prefect (free tier)	Free	Medium	Built-in	Yes
Apache Airflow	Free (self-hosted)	High	Built-in	Yes
GitHub Actions	Free (public repos)	Low-Medium	Built-in	Basic

For a single analyst running weekly reports, GitHub Actions is an underrated choice: free, version-controlled alongside your code, and has a built-in run history UI.

—

The Observability Layer: Knowing When Things Break

A scheduled Python report with AI summarization that silently fails is worse than no automation at all — you discover the problem when a stakeholder asks why the report didn’t arrive.

Minimum viable observability:

`python

import smtplib

from email.mime.text import MIMEText

def send_alert(subject, body, recipient):

“””Use for both success notifications and failure alerts.”””

msg = MIMEText(body)

msg[‘Subject’] = subject

msg[‘From’] = ‘reporting-bot@yourdomain.com’

msg[‘To’] = recipient

with smtplib.SMTP(‘smtp.gmail.com’, 587) as server:

server.starttls()

server.login(EMAIL_USER, EMAIL_PASS)

server.send_message(msg)

At the end of summarize.py:

try:

run_full_pipeline()

send_alert(

subject=f”✅ Weekly Report Generated — {datetime.now().date()}”,

body=summary_text,

recipient=RECIPIENT_EMAIL

)

except Exception as e:

send_alert(

subject=f”❌ Weekly Report FAILED — {datetime.now().date()}”,

body=f”Error: {str(e)}\n\nCheck pipeline.log for details.”,

recipient=ALERT_EMAIL

)

`

This pattern — wrapping the entire pipeline execution in a try/except that sends an alert either way — means you are notified on both success and failure. Success notifications sound unnecessary until you realize they also confirm the scheduler is running.

—

What the 80% Automation Actually Looks Like

To be precise about what “80%” means in this system: the tasks eliminated are data extraction, metric calculation, and first-draft narrative generation. These are the steps that previously required opening dashboards, copying numbers to a spreadsheet, writing summary paragraphs, and formatting the output. The tasks that remain human are reviewing the AI-generated summary for accuracy, adjusting framing for specific audiences, and making judgment calls about what matters this week versus last week.

The value of this setup is not just time saved. It is consistency — every report follows the same structure, covers the same date ranges, and references the same source data. Manual reporting introduces subtle inconsistencies (different date interpretations, forgotten metrics, variable commentary style) that make week-over-week comparison harder. An automated pipeline eliminates that variability.

For teams evaluating whether to automate weekly business reports with AI APIs, the honest assessment is this: the initial setup takes longer than a single manual report. The break-even point depends on report frequency and complexity. The ongoing value is in reproducibility and the analyst’s time being redirected toward interpretation rather than assembly.

—

Automate Weekly Data Reporting: Where to Start

If you want to implement this system, prioritize in this order:

1. Start with Script 2 (transformation) first — build the metric definitions before you automate extraction. Knowing exactly what you need to calculate informs what data you need to pull.

1. Add Script 1 (extraction) second — with retry logic and checkpoints from day one, not as a refactor later.

1. Add the LLM summarization last — once the data pipeline is stable, the AI layer is a two-hour addition. Adding it to an unstable pipeline means you won’t know whether bad summaries come from bad data or bad prompts.

1. Run manually for two weeks before scheduling — catch edge cases (missing data for a holiday, API changes, schema changes) before cron takes over.

1. Add the alert system before you add the schedule — never run unmonitored automation.

The full codebase for this system — including environment variable management, logging configuration, and a simple Makefile to run each stage — is available in the linked GitHub repository. If you are building a production-ready automated reporting pipeline and want feedback on your specific data sources or metric definitions, drop the details in the comments. The specific edge cases — timezone handling, multi-source joins, partial data detection — are where the real complexity lives, and they are worth discussing in concrete terms.

Frequently Asked Questions

How do I automate weekly data reporting with a Python script?

You can automate weekly data reporting using three Python scripts working in sequence: an extraction script that pulls raw data from sources, a transformation script that aggregates metrics and calculates KPIs, and a summarization script that calls a free LLM API to generate a human-readable narrative. Together, these three scripts eliminate the manual steps of copy-pasting from dashboards, writing commentary, and formatting slides, handling roughly 80% of a typical reporting workflow.

Why does Python reporting automation break in production environments?

Most reporting automation tutorials only handle the happy path and skip critical failure scenarios such as API timeouts, pagination issues that cause incomplete data delivery, LLM hallucinations on structured data, scheduler drift from overlapping cron jobs, and silent failures that produce empty reports. A production-ready pipeline requires defensive code including retry logic, exponential backoff, pagination handling, and proper logging and alerting.

What is exponential backoff and why is it used in data extraction scripts?

Exponential backoff is a retry strategy where the wait time between failed API attempts increases exponentially with each attempt, for example waiting 1 second after the first failure, 2 seconds after the second, and 4 seconds after the third. It is used in data extraction scripts to handle transient network errors and API timeouts without immediately failing the entire pipeline, giving the external service time to recover before retrying.

Why should the transformation script read from saved checkpoint files instead of making a new API call?

Reading from checkpoint files ensures that KPI calculations are performed on the exact same data that was captured during extraction, rather than from a second API call that might return slightly different results due to data updates or timing differences. Checkpoint files also create a timestamped snapshot of every data pull, allowing you to trace and verify specific numbers if stakeholders question a reported metric weeks later.

📚 Related Articles

• Multi-Agent AI Automation for Small Business Python
• Local LLMs vs Copilot: Self-Hosted AI for Data Scientists
• Build Python Multi-Agent System: Complete Guide
• 847 Side Hustles Analyzed: Only 3 Scale With AI