Automation: Auto-Draft Your Weekly District Manager Report

What This Builds

Every Monday at 7am, a pre-drafted weekly performance summary email lands in your Gmail drafts folder. It pulls your sales data from a Google Sheet, sends it to ChatGPT to generate the narrative, and creates an email draft addressed to your district manager — ready for you to review and send in under 3 minutes. Instead of spending 60–90 minutes writing a weekly report, you spend 3 minutes reviewing one.

Prerequisites

• Comfortable using ChatGPT for weekly summaries (Level 3)
• Google account (Sheets + Gmail)
• Zapier account (free tier has limited zaps; Starter at $20/mo recommended)
• ChatGPT Plus with API access (or OpenAI API account — ~$5–10/month usage)

The Concept

This automation works like a relay race: Google Sheets holds your numbers → Zapier runs the relay at a scheduled time → ChatGPT writes the report → Zapier drops a Gmail draft in your inbox. You're the last runner: you review and send.

Think of it as hiring a report-writing assistant who works at 7am every Monday, reads your numbers, and has a draft ready when you walk in.

Build It Step by Step

Part 1: Set Up Your Sales Data Sheet

Create a Google Sheet with these columns:

• A: Week ending date
• B: Sales (actual $)
• C: Sales target ($)
• D: Variance %
• E: Key events (a brief text note: "2 call-outs Saturday, LP incident Tuesday")
• F: Next week priorities (brief text: "Spring sale launch, full team")

Every week, fill in one new row. This is the only data entry you do — and you'll be entering it anyway for your own records.

Name the sheet "Weekly Store Data" and the tab "Summary."

Part 2: Get Your ChatGPT API Key

Go to platform.openai.com → sign in → API Keys → Create new secret key. Copy and save it securely — you'll paste it into Zapier. Note: API usage costs approximately $0.01–0.05 per report, billed to your OpenAI account. Set a monthly budget limit of $5 at platform.openai.com/account/limits.

Part 3: Build the Zapier Workflow

Go to zapier.com → Create Zap.

Trigger: Schedule by Zapier

• Trigger: "Schedule"
• Frequency: Every week
• Day: Monday
• Time: 7:00 AM (your time zone)

Action 1: Google Sheets — Get Row

• App: Google Sheets
• Action: "Look Up Spreadsheet Row"
• Connect your Google account
• Spreadsheet: Weekly Store Data
• Worksheet: Summary
• Lookup column: A (date)
• Lookup value: "Last row" (or use Zapier's date formatter to match the most recent Monday)

This pulls the latest row of your data.

Action 2: OpenAI (ChatGPT) — Send Prompt

• App: OpenAI (in Zapier's app directory)
• Action: "Send Message"
• Connect using your API key from Part 2
• Model: "gpt-4o-mini" (cheaper; good enough for this)
• Message: Build this prompt using Zapier's field mapping:

Write a professional weekly retail store manager summary email to my district manager.
Store: [your store name and type].
Week ending: {{date from Sheet column A}}
Sales: ${{column B}} vs target ${{column C}} ({{column D}} variance).
Key events this week: {{column E}}
Next week priorities: {{column F}}
Format: Subject line + 3-4 paragraph email. Professional tone. Be direct about any misses, explain the reason, and show a plan.

Action 3: Gmail — Create Draft

• App: Gmail
• Action: "Create Draft"
• To: [your district manager's email]
• Subject: Use the subject line from the ChatGPT output (use Zapier field mapping)
• Body: Map the ChatGPT output text

Test the Zap: Click "Test" on each step. If it all works, the test should create a Gmail draft. Turn on the Zap.

Part 4: Test and Refine

The first 3–4 weeks, compare the auto-drafted report to what you would have written. Add notes to your Google Sheet's "Key events" column to improve the quality of the narrative. Adjust the ChatGPT prompt in Zapier if the output is too long, too formal, or missing key details.

Real Example: Your Monday Morning

Setup: Your store is a 45-person specialty electronics retailer. You've built this automation.

Input (what you entered in the Sheet on Sunday night):

• Sales: $82,400 vs $88,000 target (-6.4%)
• Key events: "2 call-outs Saturday, register 3 down for 2 hours Friday"
• Next week: "New laptop promotion launch, training Tuesday AM"

Output (what's in your Gmail drafts at 7am Monday):

Subject: Weekly Store Summary — Week Ending March 22

Hi [DM name],

Here's my summary for the week ending March 22. We came in at $82,400 against our $88,000 target, a -6.4% variance. Two contributing factors: a register outage on Friday afternoon (register 3 was down for approximately 2 hours during peak hours, creating checkout delays) and two unplanned call-outs on Saturday that left us short on our highest-traffic day.

No systemic issues — these were operational one-offs. I've logged a maintenance ticket for the register and documented both call-outs in the team files.

For next week, I'm focused on a strong launch of the new laptop promotion and have scheduled a 30-minute team training Tuesday morning to make sure everyone knows the key selling points and the promotional pricing structure.

Reach out with any questions — otherwise I'll see you at the Thursday ops call.

[Your name]

Time saved: 60–90 minutes writing → 3 minutes reviewing and sending.

What to Do When It Breaks

• ChatGPT API returns an error → Check your API key is still valid at platform.openai.com. Check your billing — you may have hit your monthly limit. Increase the limit.
• Google Sheets can't find the latest row → Make sure you're entering data consistently in the same format. Verify the Zapier "Look Up Row" step is configured to find the last non-empty row.
• Gmail draft isn't created → Re-authenticate your Gmail connection in Zapier (connections expire occasionally). Test each Zapier step individually to find where it's breaking.
• The report is too generic → Improve the "Key events" and "Next week priorities" columns in your Sheet — the quality of the output depends on the quality of your input.

Variations

• Simpler version: Skip Zapier entirely. Just enter your numbers into a Notes app each week and paste them into the ChatGPT custom instructions template from Level 3. Semi-automated, no cost.
• Extended version: Add a fourth column to the Sheet for "customer complaints this week" and a fifth for "staffing events" — gives the AI more to work with for richer narratives.

What to Do Next

• This week: Build the Google Sheet, enter 4 weeks of historical data, and test the Zapier trigger.
• This month: Run for 4 weeks and compare your report quality to your previous manual efforts.
• Advanced: Connect the same Sheet to a Zapier zap that also tracks your week-over-week trends in a separate summary tab — giving you a running record for your quarterly reviews.

Advanced guide for retail store manager professionals. These techniques use more sophisticated AI features that may require paid subscriptions.