Prompt Chain: Transform ESP Data into a Client Report in 4 Steps

What This Builds

A 4-step prompt chain that takes raw ESP data export (messy CSV or pasted numbers) and transforms it into a polished client report through sequential AI prompts, each step's output becomes the next step's input. The result is better than a single "summarize this" prompt because each step focuses on one job: clean, analyze, narrate, format.

Prerequisites

• Claude account, free tier works; {{tool:Claude.plan}} ({{tool:Claude.price}}) for longer conversations with large datasets
• Campaign data from your ESP (copied from the UI or exported as CSV)
• 20 minutes per report after you've practiced the workflow once

The Concept

A prompt chain is like a production line where each worker does one specialized job rather than one worker trying to do everything. A baker doesn't mix dough AND frost cakes at the same time, they do them in sequence. Chaining prompts works the same way: Step 1 cleans the data (so analysis doesn't include junk), Step 2 finds patterns (before writing prose), Step 3 writes the narrative (based on confirmed patterns), Step 4 formats it (after the content is solid).

Trying to do all four in one prompt produces a muddled result. Doing them in sequence produces a finished, structured report.

Build It Step by Step

Part 1: Set Up Your Claude Session

Open Claude and start a new conversation. If you have a Claude Project for this client, start the conversation inside that project, it will have brand and audience context already loaded.

If no Project: start the conversation with a brief brand context message:

Context for this session: I'm preparing a monthly email performance report for [client/brand name], a [brand type]. Non-technical marketing manager audience. Measure success by: open rate, CTR, and revenue attributed to email.

Part 2: Step 1. Data Cleaning Prompt

Paste your raw ESP data (as much as you have, even messy is fine) and run:

Here is my raw email campaign data for [month]:
[paste raw data — CSV rows, copied from the ESP dashboard, whatever format you have]

Step 1 task: Clean and structure this data. Output a clean table with these columns:
- Campaign name
- Date sent
- Segment targeted
- Recipients
- Open rate (%)
- CTR (%)
- Revenue ($)
- Unsubscribe rate (%)

Remove any duplicate rows, fix formatting inconsistencies, and flag any data that looks like an anomaly (unusually high or low values). Output only the clean table — no analysis yet.

What you should see: A clean, structured table. Review it to make sure the data looks right before moving to Step 2.

Part 3: Step 2. Pattern Analysis Prompt

Now that the data is clean, analyze it:

Step 2 task: Analyze the clean data table above. Identify:

1. Best-performing campaign (by open rate, CTR, and revenue — note if different campaigns win each metric)
2. Worst-performing campaign — what's the likely root cause based on the data?
3. Any notable trend across the month (improving, declining, volatile?)
4. Day-of-week patterns if visible
5. Segment performance comparison if multiple segments are represented
6. Any anomalies that need explanation

Output as a bulleted analysis. No narrative prose yet — just the findings.

What you should see: A bulleted list of factual observations about the data patterns.

Part 4: Step 3. Narrative Writing Prompt

With clean data and factual analysis in hand, write the narrative:

Step 3 task: Using the data table and the analysis above, write a client-ready monthly performance narrative with these sections:

1. EXECUTIVE SUMMARY — 3 sentences. What happened overall? Are we trending up, flat, or down?
2. HIGHLIGHTS — 2-3 specific wins. Name the campaigns, explain what drove the results.
3. AREAS FOR IMPROVEMENT — 1-2 underperformers. Be honest but constructive about root causes.
4. RECOMMENDATIONS — 3 specific actions for next month. Make them actionable, not vague.
5. FORWARD-LOOKING — 1 paragraph on what's coming up in [next month] and how we should prepare.

Tone: Professional, confident, helpful. Like a knowledgeable colleague, not a consultant. No jargon. Explain what the numbers mean, not just what they are. Target audience: a marketing manager who is smart but not a data analyst.

What you should see: A full 400-600 word report narrative that reads naturally and makes specific recommendations.

Part 5: Step 4. Formatting Prompt

Final step. format the report for how it will be delivered (email, Google Doc, slide):

Step 4 task: Reformat the narrative above into a clean, client-ready format.

Output format: [choose one]
Option A — Email format: Short subject line suggestion + 3-paragraph email body with key stats bolded
Option B — Doc format: Section headers, bullet points for recommendations, clean paragraph prose for narrative sections
Option C — Exec summary format: 200-word maximum, top 3 wins, top 3 recommendations, one next-step CTA

I want: [specify Option A, B, or C]

What you should see: The report reformatted for your chosen delivery format, ready to copy into an email, Google Doc, or slide.

Real Example: B2B SaaS Client Report

Setup: Managing email for a project management SaaS. 3 campaigns in May: product update newsletter, feature launch announcement, user re-engagement.

Step 1 Input: Messy copy-paste from HubSpot dashboard with inconsistent formatting

Step 1 Output: Clean table, 3 rows, all metrics normalized. Claude flagged the re-engagement campaign as having an unusually high unsubscribe rate (1.8% vs normal 0.2%).

Step 2 Output: Analysis noting the feature launch email dominated on CTR (5.1%), the newsletter had decent opens but low CTR (suggests content interest but weak CTA), and the re-engagement campaign's high unsubscribes suggests the dormant segment needs further cleaning.

Step 3 Output: A 500-word narrative that opens: "May was a strong month for feature-driven email. the launch announcement was our best CTR performance in 6 months. The re-engagement campaign surfaced an important list hygiene issue we should address before the summer push."

Step 4 Output: A clean email format ready to send.

Total time: 22 minutes from raw data to client-ready report.

What to Do When It Breaks

• Step 2 analysis is obvious/shallow → Add a line: "Don't state the obvious. Focus on non-obvious patterns and things that require explanation." This pushes toward more insightful analysis.
• Step 3 narrative is too long → Add: "Keep the total narrative under 400 words." Claude will respect word limits.
• Data in Step 1 won't clean properly → Paste data in smaller chunks (one campaign at a time) if the full export is too large or messy. Combine into one table manually before Step 2.
• Output feels generic → You've forgotten to add brand context. Go back and add a "context for this session" message at the start, or use a Claude Project that already has the client context loaded.

Variations

• Simpler version: Skip Step 1 (clean data yourself in 5 minutes) and run Steps 2, 3, 4 only. Works well when your ESP exports clean CSVs.
• Extended version: Add a Step 0 before data cleaning: "Here are my goals for this client and what they care most about." Then the entire chain is informed by client-specific priorities, not just generic performance analysis.

What to Do Next

• This week: Run this prompt chain on last month's data for one client. Time yourself and compare to your normal process.
• This month: Save each step's prompts as a reusable template document. Adapt the context line for each client.
• Advanced: Combine this prompt chain with the Zapier reporting pipeline (Level 4 guide). use Zapier to pass the data automatically, and Claude for the analysis and narrative.

Advanced guide for email marketing specialist professionals. These techniques use more sophisticated AI features that may require paid subscriptions.