How to Automate Weekly Reports with ChatGPT, Zapier, and Google Sheets

Why Automated Reports Save 3-5 Hours Per Week

Every Monday morning, someone on your team manually pulls numbers from spreadsheets, calculates week-over-week changes, writes a summary, formats it nicely, and sends it to stakeholders. This ritual takes 3-5 hours — time that could be spent on actual analysis and decision-making.

The automation pipeline in this guide eliminates the mechanical work entirely. Your data lives in Google Sheets (where it probably already is). Zapier triggers the workflow on a schedule. ChatGPT transforms raw numbers into a readable narrative with insights and recommendations. The finished report lands in Slack or email before anyone arrives at their desk.

This is not a complicated AI project. It is a simple, reliable automation that you can set up in under an hour and run indefinitely.

What You Need

• Google Sheets with your weekly metrics (free)
• Zapier account (free tier works for basic automation; paid for multi-step)
• OpenAI API key for ChatGPT ($5-10/month for weekly reports)
• Delivery channel: Slack, email, or Google Docs

Total cost: approximately $5-30/month depending on report frequency and Zapier plan.

Step 1: Organize Your Data Source

Google Sheets Structure

Set up your metrics spreadsheet with a consistent structure:

Row 1 (Headers):
Week | Revenue | Orders | Avg Order Value | New Customers |
Customer Retention | Support Tickets | NPS Score | Marketing Spend |
Conversion Rate

Row 2 (This Week):
2026-W13 | $142,500 | 1,847 | $77.12 | 312 | 89.2% | 127 | 62 | $12,400 | 3.4%

Row 3 (Last Week):
2026-W12 | $135,200 | 1,723 | $78.45 | 287 | 88.7% | 143 | 59 | $11,800 | 3.2%

Important: keep the structure consistent. The automation reads specific columns by position or name. If columns change, the prompt template needs updating.

Automatic Data Entry

Ideally, your metrics populate automatically:

• Revenue/orders: Shopify or Stripe webhook → Google Sheets
• Marketing: Google Ads API → Sheets add-on
• Support: Zendesk export → Sheets
• NPS: survey tool → Sheets integration

If manual entry is unavoidable, enter data by Sunday evening so the Monday report has current numbers.

Step 2: Create the Zapier Workflow

Trigger: Schedule

1. Create a new Zap in Zapier
2. Trigger: Schedule by Zapier
3. Frequency: Every week
4. Day: Monday
5. Time: 7:00 AM (before your team’s first meeting)

Action 1: Get Data from Google Sheets

1. App: Google Sheets
2. Action: Get Spreadsheet Row(s)
3. Spreadsheet: select your metrics spreadsheet
4. Sheet: select the data sheet
5. Row: get the last 2 rows (current week and previous week)

Action 2: Format Data for ChatGPT

Use a Formatter by Zapier step to combine the data into a clean text block:

This week ({{Week_Current}}):
Revenue: {{Revenue_Current}}
Orders: {{Orders_Current}}
AOV: {{AOV_Current}}
New Customers: {{NewCustomers_Current}}
Retention: {{Retention_Current}}
Support Tickets: {{Tickets_Current}}
NPS: {{NPS_Current}}
Marketing Spend: {{Marketing_Current}}
Conversion Rate: {{ConvRate_Current}}

Last week ({{Week_Previous}}):
Revenue: {{Revenue_Previous}}
Orders: {{Orders_Previous}}
...

Action 3: Generate Report with ChatGPT

1. App: ChatGPT (OpenAI)
2. Action: Conversation
3. Model: GPT-4o
4. System prompt:

You are a business analyst writing a weekly performance report
for the leadership team. Your tone is professional, data-driven,
and concise. You highlight wins, flag concerns, and suggest
actions. Format the report with clear sections and bullet points.
Keep it under 500 words.

5. User message:

Generate this week's performance report based on the following data.
Compare to last week and highlight significant changes (>5% change).

{{formatted_data_from_step_2}}

Structure the report as:

## Weekly Performance Report — {{Week_Current}}

### Executive Summary
(3 bullet points: biggest win, biggest concern, key recommendation)

### Key Metrics
(table comparing this week vs last week with % change)

### Wins This Week
(what went well and why)

### Areas of Concern
(what declined and potential causes)

### Recommended Actions
(2-3 specific actions for the coming week)

### Outlook
(brief forward-looking statement based on trends)

Action 4: Deliver the Report

Option A: Slack

1. App: Slack
2. Action: Send Channel Message
3. Channel: #weekly-reports
4. Message: ChatGPT’s output from Step 3

Option B: Email

1. App: Gmail or Email by Zapier
2. To: leadership@company.com
3. Subject: “Weekly Performance Report — {{Week_Current}}”
4. Body: ChatGPT’s output (formatted as HTML for rich email)

Option C: Google Docs

1. App: Google Docs
2. Action: Create Document
3. Title: “Weekly Report — {{Week_Current}}”
4. Content: ChatGPT’s output
5. Folder: shared “Weekly Reports” folder

Example Output

Here is what the automated report looks like:

## Weekly Performance Report — 2026-W13

### Executive Summary
- Revenue grew 5.4% to $142,500, driven by a 7.2% increase in orders
- NPS improved to 62 (from 59), suggesting recent product updates
  are resonating with customers
- Support ticket volume declined 11.2% — investigate if this reflects
  fewer issues or fewer customers reaching out

### Key Metrics
| Metric           | This Week  | Last Week  | Change |
|------------------|------------|------------|--------|
| Revenue          | $142,500   | $135,200   | +5.4%  |
| Orders           | 1,847      | 1,723      | +7.2%  |
| Avg Order Value  | $77.12     | $78.45     | -1.7%  |
| New Customers    | 312        | 287        | +8.7%  |
| Retention        | 89.2%      | 88.7%      | +0.5pp |
| Support Tickets  | 127        | 143        | -11.2% |
| NPS              | 62         | 59         | +5.1%  |
| Marketing Spend  | $12,400    | $11,800    | +5.1%  |
| Conversion Rate  | 3.4%       | 3.2%       | +6.3%  |

### Wins This Week
- New customer acquisition up 8.7%, suggesting the new landing page
  launched last Tuesday is performing well
- Conversion rate hit 3.4%, the highest in 6 weeks
- NPS improvement indicates customer satisfaction trending upward

### Areas of Concern
- Average order value declined slightly (-1.7%). Monitor whether
  the promotional pricing on starter plans is cannibalizing
  premium orders
- Marketing spend increased 5.1% while revenue grew 5.4% —
  efficiency is flat. Review channel-level ROAS

### Recommended Actions
1. Run an A/B test on the new landing page to isolate the
   conversion lift and decide on full rollout
2. Analyze the AOV decline by product category to identify
   if it is a mix shift or true price compression
3. Review marketing spend by channel — pause any channels
   with ROAS below 3x

### Outlook
Positive momentum entering W14. If the landing page conversion
lift holds and AOV stabilizes, we are on track for a record
March.

Customizing the Report Template

For Different Audiences

Executive team: high-level, 3 bullet points, focus on revenue and growth Marketing team: channel-level metrics, CAC, ROAS, attribution Product team: NPS, support tickets, feature adoption, retention Sales team: pipeline, close rate, deal size, quota attainment

Create separate Zaps with different prompt templates for each audience.

For Different Data Sources

The same pattern works with any data source Zapier can connect to:

• Stripe → financial metrics
• HubSpot → pipeline and sales metrics
• Google Analytics → traffic and engagement
• Zendesk → support metrics
• Mixpanel → product usage metrics

Troubleshooting

Report Has Incorrect Calculations

ChatGPT may occasionally miscalculate percentages from raw data. Fix: calculate percentage changes in the Google Sheet itself and include pre-calculated values in the data sent to ChatGPT.

Report Sounds Too Generic

Fix: add company-specific context to the system prompt: “Our Q1 target is $X in revenue. Our main competitor just launched [feature]. We recently changed our pricing from [old] to [new].”

Zapier Workflow Fails Silently

Fix: add a final step that sends a confirmation message to your personal Slack DM: “Weekly report generated and delivered at [timestamp].” If you do not receive this, the workflow failed.

Report Format Breaks in Email

Fix: use HTML formatting in the ChatGPT prompt: “Format the output as clean HTML with h2, h3, table, and p tags.” Then use Zapier’s email step with HTML body enabled.

Frequently Asked Questions

How much does this cost per month?

ChatGPT API: approximately $0.10-0.50 per report (GPT-4o). Zapier: free tier allows 100 tasks/month (enough for weekly reports). Google Sheets: free. Total: under $5/month for most use cases.

Can I add charts or visualizations?

ChatGPT generates text, not images. For charts, add a separate Zapier step that uses Google Sheets’ chart feature or a tool like QuickChart.io to generate chart images from the data.

What if I need daily reports instead of weekly?

Change the Zapier trigger from weekly to daily. Adjust the ChatGPT prompt to compare today vs. yesterday. Consider shorter reports (3 bullet points + one key metric) for daily cadence.

Can I use Claude API instead of ChatGPT?

Yes. Replace the ChatGPT Zapier step with an HTTP request to the Anthropic API. The prompt structure is the same — only the API endpoint and authentication change.

How do I handle missing data?

Add a condition in Zapier: if critical data fields are empty, send an alert instead of generating a report. “Weekly report could not be generated — missing data in columns [X, Y].”