Every Monday morning, somewhere in your organization, a data analyst is copying numbers from a dashboard into a Word doc, writing sentences like "Revenue increased 12% month-over-month," and wondering why they spent four years learning statistics to do this. That analyst might be you.
Repetitive reporting is one of the most time-consuming and intellectually deadening parts of working with data. The actual insight — the anomaly, the trend, the actionable recommendation — takes minutes to spot. Translating it into a polished narrative for a VP who doesn't open spreadsheets? That takes the rest of the afternoon. And it happens again next week, and the week after that.
AI language models have genuinely changed this equation. Not by replacing your analytical judgment, but by eliminating the mechanical translation layer between data and prose. By the end of this lesson, you'll have built a repeatable, production-worthy workflow that takes raw data summaries and produces stakeholder-ready narrative reports — automatically, consistently, and with enough configurability to handle the real-world messiness your data actually contains.
What you'll learn:
You should be comfortable with Python and pandas at an intermediate level. You've worked with APIs before (REST calls, JSON parsing). You understand what a large language model is and have made at least a few API calls to OpenAI or a similar provider. You don't need to be a prompt engineering expert — we'll build that skill here — but you should know what a prompt is.
You'll need:
gpt-4o, but gpt-4-turbo or gpt-3.5-turbo work with minor adjustments)pandas, openai, python-docx, and jinja2 installedBefore writing a single line of code, let's talk about why the naive approach doesn't work — because most practitioners try it and then conclude "AI can't do this reliably." That conclusion is wrong, but the failure mode is instructive.
The naive approach is: paste your data into ChatGPT and ask it to write a summary. This produces something that sounds plausible but is structurally inconsistent, changes tone week to week, invents context it doesn't have, and occasionally hallucinates numbers. You spend as much time editing it as you would have writing it yourself.
The root problem is that language models don't fail at writing — they fail at grounding. When you dump a CSV into a prompt without structure, the model has to simultaneously figure out what the data means, what narrative conventions your organization uses, what the appropriate level of detail is, and what the audience cares about. It's doing too many jobs at once, and the output reflects that chaos.
The solution is systematic separation of concerns:
This is the architecture we'll build. Each layer does what it's actually good at.
The single biggest leverage point in this entire workflow is how you prepare data before it hits the prompt. Well-structured input produces dramatically better output than raw data — and it also makes your outputs auditable, because you can inspect exactly what facts the model was given.
Let's define our scenario. You're a data analyst at an e-commerce company. Every Monday, you report on last week's performance across three dimensions: revenue by channel, conversion rates by device type, and customer acquisition costs by campaign. Your audience is the VP of Marketing and her direct reports.
Start by building a ReportDataSummary class that computes everything the narrative needs, rather than passing raw DataFrames to the model:
import pandas as pd
import numpy as np
from dataclasses import dataclass, field
from typing import Optional
@dataclass
class MetricSummary:
name: str
current_value: float
previous_value: float
unit: str = ""
higher_is_better: bool = True
@property
def change_pct(self) -> float:
if self.previous_value == 0:
return 0.0
return ((self.current_value - self.previous_value) / self.previous_value) * 100
@property
def direction(self) -> str:
return "increased" if self.current_value > self.previous_value else "decreased"
@property
def is_positive_change(self) -> bool:
if self.higher_is_better:
return self.current_value > self.previous_value
return self.current_value < self.previous_value
def to_dict(self) -> dict:
return {
"name": self.name,
"current_value": round(self.current_value, 2),
"previous_value": round(self.previous_value, 2),
"change_pct": round(self.change_pct, 1),
"direction": self.direction,
"is_positive": self.is_positive_change,
"unit": self.unit,
}
def build_weekly_summary(current_df: pd.DataFrame, previous_df: pd.DataFrame) -> dict:
"""
Takes two weekly DataFrames and returns a structured summary dict
ready to inject into a prompt template.
"""
summary = {}
# Revenue by channel
rev_current = current_df.groupby("channel")["revenue"].sum()
rev_previous = previous_df.groupby("channel")["revenue"].sum()
channel_metrics = []
for channel in rev_current.index:
prev_val = rev_previous.get(channel, 0)
m = MetricSummary(
name=channel,
current_value=rev_current[channel],
previous_value=prev_val,
unit="USD",
higher_is_better=True,
)
channel_metrics.append(m.to_dict())
summary["revenue_by_channel"] = sorted(
channel_metrics, key=lambda x: x["current_value"], reverse=True
)
summary["total_revenue"] = MetricSummary(
name="Total Revenue",
current_value=current_df["revenue"].sum(),
previous_value=previous_df["revenue"].sum(),
unit="USD",
).to_dict()
# Conversion rate by device
conv_current = current_df.groupby("device").apply(
lambda g: g["converted"].sum() / len(g) * 100
)
conv_previous = previous_df.groupby("device").apply(
lambda g: g["converted"].sum() / len(g) * 100
)
device_metrics = []
for device in conv_current.index:
prev_val = conv_previous.get(device, 0)
m = MetricSummary(
name=device,
current_value=conv_current[device],
previous_value=prev_val,
unit="%",
)
device_metrics.append(m.to_dict())
summary["conversion_by_device"] = device_metrics
# Flag anomalies: anything more than 2 std devs from the channel's 4-week average
summary["anomalies"] = detect_anomalies(current_df)
return summary
def detect_anomalies(df: pd.DataFrame, threshold: float = 2.0) -> list[dict]:
"""
Flags channels or campaigns where daily revenue deviates significantly
from the week's own mean — a simple but practical anomaly signal.
"""
anomalies = []
daily = df.groupby(["date", "channel"])["revenue"].sum().reset_index()
for channel, group in daily.groupby("channel"):
mean = group["revenue"].mean()
std = group["revenue"].std()
if std == 0:
continue
for _, row in group.iterrows():
z_score = abs(row["revenue"] - mean) / std
if z_score > threshold:
anomalies.append({
"channel": channel,
"date": str(row["date"]),
"revenue": round(row["revenue"], 2),
"z_score": round(z_score, 2),
"direction": "spike" if row["revenue"] > mean else "drop",
})
return anomalies
Notice what we're doing here: all the math happens in Python, not in the prompt. The model will never be asked to compute a percentage change. It receives change_pct: 12.3 and direction: "increased" — structured facts it can weave into prose without doing arithmetic. This is the most important design decision in the entire workflow.
Tip: Always round your numbers before they hit the prompt. Sending
change_pct: 12.347291847to a language model wastes context tokens and occasionally produces ugly prose like "increased by 12.347 percent."
Now that we have a clean data summary, we need a prompt template that turns it into prose consistently — not just once, but every week, with different data, in a way that always sounds like your organization's reporting voice.
The key insight about prompt templates for reporting is that you're not asking the model to be creative — you're asking it to be reliable. The prompt should constrain the output shape, not leave it open-ended.
We'll use Jinja2 for templating because it's expressive and keeps the template in a separate file you can version and iterate independently of your code:
# File: templates/weekly_marketing_report.j2
You are a senior data analyst writing a weekly marketing performance report for {{audience}}.
Your writing style is: direct, data-driven, and concise. You use specific numbers. You do not use vague language like "slightly" or "somewhat" — instead you say what the number is. You do not use phrases like "it is worth noting that" or "it is important to highlight." You lead with the finding, not the setup.
The report covers the week of {{report_week}}.
## Report Structure
Write exactly these four sections, in this order. Use the headers exactly as written.
### Executive Summary
Two to three sentences. State the most important finding and its business implication. Include total revenue with week-over-week change.
### Revenue by Channel
For each channel listed below, write one to two sentences covering current revenue, week-over-week change, and any notable trend. Lead with the highest-revenue channel.
Channel data:
{% for channel in revenue_by_channel %}
- {{ channel.name }}: ${{ "{:,.0f}".format(channel.current_value) }} ({{ "+" if channel.change_pct > 0 else "" }}{{ channel.change_pct }}% WoW) — positive change: {{ channel.is_positive }}
{% endfor %}
Total revenue: ${{ "{:,.0f}".format(total_revenue.current_value) }}, {{ total_revenue.direction }} {{ total_revenue.change_pct | abs }}% from the prior week.
### Conversion Rate Performance
Analyze device-level conversion performance. Identify which device type had the strongest improvement and which needs attention.
Device data:
{% for device in conversion_by_device %}
- {{ device.name }}: {{ device.current_value }}% conversion rate ({{ "+" if device.change_pct > 0 else "" }}{{ device.change_pct }}% WoW)
{% endfor %}
### Anomalies & Flags
{% if anomalies %}
The following anomalies were detected this week. For each, describe what happened and suggest a possible explanation or next investigative step.
{% for anomaly in anomalies %}
- {{ anomaly.channel }} on {{ anomaly.date }}: ${{ "{:,.0f}".format(anomaly.revenue) }} revenue ({{ anomaly.direction }}, z-score: {{ anomaly.z_score }})
{% endfor %}
{% else %}
State that no significant anomalies were detected this week and that performance was within normal ranges.
{% endif %}
## Additional Context
{{additional_context}}
## Formatting Rules
- Use dollar signs and comma-separators for all currency values
- Express percentage changes with one decimal place and a + or - sign
- Do not fabricate data not present in the structured input above
- Do not add sections beyond the four specified
- Target length: 350–500 words total
There's a lot happening in this template. Let's break down the design decisions:
The persona instruction is specific, not generic. "Direct, data-driven, concise" with explicit anti-patterns ("slightly," "it is worth noting") shapes the prose in measurable ways. Generic instructions like "write professionally" produce generic output.
The structure is locked. We tell the model exactly what sections exist and what order they go in. This makes downstream parsing predictable — you can reliably extract the Executive Summary by splitting on the ### header.
Numbers arrive pre-formatted. The Jinja2 template handles the {:,.0f} formatting, so the model sees $1,247,392 rather than 1247392.0.
The anomaly section branches on data. If there are no anomalies, the instruction changes. This prevents the model from inventing fictional anomalies to fill a section.
Length is constrained. "350–500 words" is a meaningful business requirement — an exec won't read 1,200 words — and models follow length constraints reasonably well when given specific numbers.
Now we connect the pieces. Here's the core pipeline that takes raw DataFrames and produces a completed report:
import os
import json
from jinja2 import Environment, FileSystemLoader
from openai import OpenAI
client = OpenAI(api_key=os.environ["OPENAI_API_KEY"])
def render_prompt(summary: dict, template_name: str, context: dict) -> str:
"""Render a Jinja2 template with the data summary and additional context."""
env = Environment(loader=FileSystemLoader("templates"))
template = env.get_template(template_name)
return template.render(**summary, **context)
def generate_report(
prompt: str,
model: str = "gpt-4o",
temperature: float = 0.3,
) -> str:
"""
Call the OpenAI API with a structured prompt and return the narrative text.
Temperature 0.3 gives us consistency over creativity — appropriate for reporting.
"""
response = client.chat.completions.create(
model=model,
messages=[
{
"role": "system",
"content": (
"You are a professional data analyst. You write clear, accurate "
"reports based strictly on the data provided. You never invent "
"metrics or trends not present in your input."
),
},
{"role": "user", "content": prompt},
],
temperature=temperature,
max_tokens=1000,
)
return response.choices[0].message.content
def run_weekly_report_pipeline(
current_df: pd.DataFrame,
previous_df: pd.DataFrame,
report_week: str,
audience: str = "VP of Marketing and her direct reports",
additional_context: str = "",
output_path: str = "reports/",
) -> str:
"""
Full pipeline: data → summary → prompt → narrative → file.
Returns the generated narrative as a string.
"""
# Step 1: Build the structured summary
summary = build_weekly_summary(current_df, previous_df)
# Step 2: Add metadata
context = {
"report_week": report_week,
"audience": audience,
"additional_context": additional_context or "No additional context this week.",
}
# Step 3: Render the prompt
prompt = render_prompt(summary, "weekly_marketing_report.j2", context)
# Step 4: Generate the narrative
narrative = generate_report(prompt)
# Step 5: Save outputs (both the narrative and the structured summary for auditing)
os.makedirs(output_path, exist_ok=True)
week_slug = report_week.replace(" ", "_").replace("/", "-")
with open(f"{output_path}{week_slug}_narrative.md", "w") as f:
f.write(narrative)
with open(f"{output_path}{week_slug}_summary.json", "w") as f:
json.dump(summary, f, indent=2, default=str)
print(f"Report generated: {output_path}{week_slug}_narrative.md")
return narrative
Important: Save the structured summary JSON alongside every report. When someone asks you "why did the report say X?" next month, you'll be able to show them the exact data the model was given. This audit trail is what makes AI-generated reports trustworthy in a professional setting.
Notice the temperature=0.3 choice. This is deliberate. Higher temperature (0.7–1.0) produces more varied, "creative" prose — which is great for copywriting and terrible for reporting. You want the model to be a reliable transcription layer, not an improviser. At 0.3, you get consistent tone and phrasing across weekly cycles, which means your stakeholders develop pattern recognition for the report format.
Markdown is great for you, the engineer. It is not great for the VP who receives it via email on a Tuesday morning. Let's add a layer that converts the generated narrative into a formatted Word document using python-docx:
from docx import Document
from docx.shared import Pt, RGBColor, Inches
from docx.enum.text import WD_ALIGN_PARAGRAPH
import re
from datetime import datetime
def narrative_to_docx(
narrative: str,
report_week: str,
output_path: str,
company_name: str = "Acme Commerce",
) -> None:
"""
Converts a markdown-formatted narrative to a styled Word document.
Handles ## and ### headers, bold text, and bullet points.
"""
doc = Document()
# Set document margins
for section in doc.sections:
section.top_margin = Inches(1)
section.bottom_margin = Inches(1)
section.left_margin = Inches(1.25)
section.right_margin = Inches(1.25)
# Title block
title = doc.add_heading(f"{company_name} — Weekly Marketing Report", level=1)
title.alignment = WD_ALIGN_PARAGRAPH.CENTER
title_run = title.runs[0]
title_run.font.color.rgb = RGBColor(0x1A, 0x1A, 0x2E)
subtitle = doc.add_paragraph(f"Week of {report_week} | Generated {datetime.now().strftime('%B %d, %Y')}")
subtitle.alignment = WD_ALIGN_PARAGRAPH.CENTER
subtitle.runs[0].font.color.rgb = RGBColor(0x88, 0x88, 0x88)
subtitle.runs[0].font.size = Pt(10)
doc.add_paragraph() # spacer
# Parse and render the narrative
lines = narrative.split("\n")
for line in lines:
line = line.strip()
if not line:
doc.add_paragraph()
continue
if line.startswith("### "):
heading = doc.add_heading(line[4:], level=3)
heading.runs[0].font.color.rgb = RGBColor(0x16, 0x4B, 0x8C)
elif line.startswith("## "):
heading = doc.add_heading(line[3:], level=2)
heading.runs[0].font.color.rgb = RGBColor(0x1A, 0x1A, 0x2E)
elif line.startswith("- "):
para = doc.add_paragraph(style="List Bullet")
_add_formatted_run(para, line[2:])
else:
para = doc.add_paragraph()
_add_formatted_run(para, line)
doc.save(output_path)
print(f"Word document saved: {output_path}")
def _add_formatted_run(para, text: str) -> None:
"""Handle **bold** markdown within a paragraph."""
parts = re.split(r"(\*\*[^*]+\*\*)", text)
for part in parts:
if part.startswith("**") and part.endswith("**"):
run = para.add_run(part[2:-2])
run.bold = True
else:
para.add_run(part)
You can extend this to include a data table (a clean formatted table of the channel revenue numbers adds a lot of credibility to the narrative), a company logo, or a footer with the report generation timestamp. The python-docx API is verbose but predictable.
Here's where the workflow goes from "automated" to genuinely useful. Right now the model only knows what's in the data. But you know things the data doesn't: there was a platform outage on Wednesday, the Black Friday campaign launched Thursday, the main competitor ran an aggressive promotion.
The additional_context parameter in our pipeline handles this. The question is how to populate it systematically without requiring manual input every week.
Here are three practical patterns:
Pattern 1: A structured context file checked into your repo
Maintain a context/ directory with a YAML file per week:
# context/2024-W47.yaml
week: "2024-W47"
events:
- date: "2024-11-20"
description: "AWS us-east-1 partial outage affecting checkout flow, 14:00–17:30 UTC"
affected_channels: ["direct", "paid_search"]
- date: "2024-11-21"
description: "Black Friday email campaign launched to 2.1M subscribers"
affected_channels: ["email"]
campaigns_launched:
- "BF2024_Email_Blast"
- "BF2024_Retargeting"
notes: "YoY comparison skewed — Black Friday fell in W48 last year"
Load this at pipeline runtime and format it into the additional_context string. Now the model can write sentences like "The Wednesday revenue dip in paid search aligns with the documented AWS outage" — which is genuinely useful analysis, not hallucination, because you provided the grounding fact.
Pattern 2: Pull from a shared calendar or Notion database
If your team already logs major events somewhere, write a small integration that fetches events for the report week. A Notion database with a "Week" property and an "Event" property can be queried via the Notion API in about 20 lines of Python. This removes the manual step entirely.
Pattern 3: Prior week's report as context
Include a summary of last week's report in the prompt — specifically the "flags for follow-up" section, if you add one. This lets the model check whether flagged issues were resolved and creates narrative continuity across weeks. Use it cautiously: including too much prior context bloats the prompt and can cause the model to over-reference the past at the expense of the current week's data.
Here's a complete exercise you can run locally. It generates synthetic data, runs the full pipeline, and produces a Word document.
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
import random
def generate_synthetic_ecommerce_data(
start_date: str,
days: int = 7,
seed: int = 42
) -> pd.DataFrame:
"""Generate realistic-looking weekly e-commerce session data."""
np.random.seed(seed)
random.seed(seed)
channels = ["paid_search", "email", "organic", "social", "direct"]
devices = ["desktop", "mobile", "tablet"]
records = []
start = datetime.strptime(start_date, "%Y-%m-%d")
for day_offset in range(days):
date = start + timedelta(days=day_offset)
# Daily session volume varies by day of week
is_weekday = date.weekday() < 5
base_sessions = 8000 if is_weekday else 5500
for channel in channels:
channel_multiplier = {
"paid_search": 0.35, "email": 0.15, "organic": 0.25,
"social": 0.15, "direct": 0.10
}[channel]
session_count = int(base_sessions * channel_multiplier * np.random.uniform(0.85, 1.15))
for _ in range(session_count):
device = random.choices(devices, weights=[0.45, 0.45, 0.10])[0]
# Conversion rates vary by channel and device
base_cvr = {"paid_search": 0.032, "email": 0.041, "organic": 0.028,
"social": 0.019, "direct": 0.055}[channel]
device_modifier = {"desktop": 1.3, "mobile": 0.75, "tablet": 0.95}[device]
cvr = base_cvr * device_modifier
converted = np.random.random() < cvr
revenue = np.random.lognormal(mean=4.2, sigma=0.8) if converted else 0
records.append({
"date": date.strftime("%Y-%m-%d"),
"channel": channel,
"device": device,
"converted": converted,
"revenue": revenue,
})
return pd.DataFrame(records)
# Generate two weeks of data
current_week_df = generate_synthetic_ecommerce_data("2024-11-18", days=7, seed=42)
previous_week_df = generate_synthetic_ecommerce_data("2024-11-11", days=7, seed=99)
# Inject a spike to make the anomaly detection interesting
spike_mask = (current_week_df["date"] == "2024-11-21") & (current_week_df["channel"] == "email")
current_week_df.loc[spike_mask, "revenue"] *= 3.5
# Run the pipeline
narrative = run_weekly_report_pipeline(
current_df=current_week_df,
previous_df=previous_week_df,
report_week="November 18–24, 2024",
audience="VP of Marketing and her direct reports",
additional_context=(
"The email channel spike on November 21 corresponds to the Black Friday "
"preview campaign sent to 2.1M subscribers. This was an intentional event, "
"not an anomaly requiring investigation."
),
output_path="reports/",
)
# Export to Word
narrative_to_docx(
narrative=narrative,
report_week="November 18–24, 2024",
output_path="reports/2024-W47_marketing_report.docx",
)
print("\n--- Generated Narrative ---\n")
print(narrative)
When you run this, you should see the anomaly detection flag the email spike on November 21, and the narrative should correctly contextualize it using the additional_context you provided. If the model ignores your context and still frames it as a problem to investigate, that's a signal to strengthen the contextual instruction in your template.
Mistake 1: Asking the model to compute metrics from raw data
If you pass a raw DataFrame (or CSV text) and ask "what's the week-over-week change?", you'll get inconsistent results and occasional arithmetic errors. Language models are not calculators. Always pre-compute every metric in Python and pass only the results.
Mistake 2: Using temperature=0 for everything
Temperature 0 produces completely deterministic output — useful for debugging, but in production it can make the narrative feel robotic. At 0.3–0.4, you get consistency with enough variation to keep prose from reading like a template literally filled in. Experiment with your specific prompt.
Mistake 3: Prompts without negative constraints
Telling the model what to do isn't enough — you also need to tell it what NOT to do. "Do not fabricate data not present in the structured input" and "Do not add sections beyond the four specified" are not redundant. Without them, creative drift happens: the model adds a "Recommendations" section you didn't ask for, or invents a YoY comparison using made-up numbers.
Mistake 4: Not validating numeric references in the output
Before the report goes out, run a quick validation pass: extract all dollar amounts and percentages from the narrative and check that they exist in your summary JSON. This is about a 20-line script and it will catch the occasional model confabulation before your VP sees it.
import re
def validate_numbers_in_narrative(narrative: str, summary: dict) -> list[str]:
"""
Extract numbers from narrative and flag any that don't appear in the summary.
This is a heuristic, not a proof — but it catches obvious hallucinations.
"""
# Extract percentage values mentioned in the narrative
mentioned_pcts = set(re.findall(r"([+-]?\d+\.?\d*)\s*%", narrative))
# Get all expected percentages from summary
expected_pcts = set()
for channel in summary.get("revenue_by_channel", []):
expected_pcts.add(str(abs(round(channel["change_pct"], 1))))
for device in summary.get("conversion_by_device", []):
expected_pcts.add(str(abs(round(device["change_pct"], 1))))
unexpected = mentioned_pcts - expected_pcts
if unexpected:
return [f"Unverified percentage in narrative: {p}%" for p in unexpected]
return []
Mistake 5: Storing the API key in your code
Use environment variables or a secrets manager. This is so standard it shouldn't need saying, but AI-generated reporting scripts have a way of ending up on GitHub.
Mistake 6: Not iterating on your template
Your first template will produce okay output. Your fifth iteration will produce output that sounds like it was written by a competent analyst on your team. The template is not a one-time artifact — it's something you refine based on stakeholder feedback. Keep it in version control and treat template changes like code changes.
Once the basic workflow is working, you'll want to add a few things before this runs unsupervised:
Scheduling. Use a simple cron job, Airflow DAG, or GitHub Actions workflow to trigger the pipeline every Monday morning after your data warehouse updates. The pipeline should pull data from your actual data source (Snowflake, BigQuery, whatever you're using) rather than local CSVs.
Error handling and alerting. Wrap the generate_report call in a retry loop with exponential backoff — API rate limits and transient failures are real. If the pipeline fails, you want a Slack or email alert before 9am, not a confused VP asking why she didn't get her report.
Human review step. For high-stakes reports, add a review layer: the pipeline generates the draft and sends it to the analyst via email for approval before it goes to the VP. The analyst spends 3 minutes reading instead of 3 hours writing. This is how you deploy AI-generated reporting responsibly — the human stays in the loop, just much further downstream.
Versioning model and prompt together. When you upgrade from gpt-4o to whatever comes next, or when you update your template, document both changes together. A model change can shift tone meaningfully even with an unchanged prompt. Keep a CHANGELOG.md for your reporting pipeline.
You've built something genuinely useful here. The workflow you now have:
The skills you've practiced — structured prompt design, separation of concerns in AI pipelines, output validation — transfer directly to other AI automation workflows beyond reporting.
Where to go next:
The Monday morning copy-paste ritual is optional. You just automated it.
EXCERPT: Stop spending your afternoons translating dashboard numbers into Word documents. This hands-on lesson walks you through building a production-ready Python pipeline that takes raw data, computes structured summaries, and uses AI to generate consistent, professional narrative reports — automatically, every reporting cycle. META_DESCRIPTION: Learn to automate data reporting with AI: build a Python pipeline from pandas DataFrames to stakeholder-ready Word documents using LLMs and prompt templates. TAGS: ai automation, prompt engineering, python, data reporting, openai, workflow automation
Learning Path: Intro to AI & Prompt Engineering