P
AI for Retail Buyer

Automation Recipe: Weekly Sales Report from Data to Draft

Zapier

For Retail Buyers

Tools: Zapier + Google Sheets + Gmail | Time to build: 2 hours | Difficulty: Intermediate-Advanced Prerequisites: Comfortable using Google Sheets and Gmail AI features. see Level 2 guides

What This Builds

An automated pipeline that takes your weekly sales data export (from ERP or as an emailed report) and automatically generates a draft sales commentary email with narrative analysis. ready for you to review, personalize, and send each Monday morning. Cuts weekly report production from 2-3 hours to 20-30 minutes of review.

Prerequisites

  • Zapier account, free tier for simple version; {{tool:Zapier.price}}/month for multi-step with AI action
  • Google Sheets (your weekly sales data lands here or is exportable to it)
  • Gmail account
  • Your weekly sales data exports in a consistent format
  • A clear weekly report template (what sections do you always include?)

The Concept

Zapier watches for a trigger. a new row in your Google Sheet, an emailed report attachment, or a scheduled time. then passes your data to an AI action that writes the narrative commentary, and deposits the draft into your Gmail drafts folder. Monday morning, you open one email draft, verify the numbers, add your personal judgment calls, and send.

Build It Step by Step

Part 1: Standardize your weekly data input

The automation works best when data arrives in a consistent format. Create a Google Sheet with these columns:

  • Week (week number or date)
  • Category
  • Net Sales
  • % to Plan
  • % to LY
  • Top Performer 1 (name + % change)
  • Top Performer 2
  • Underperformer 1 (name + % change)
  • Underperformer 2
  • Inventory (weeks of supply)
  • Actions Taken
  • Notes/Context

Each Monday morning, spend 5 minutes filling in this structured sheet before the automation runs. The discipline of structured data input is what makes the automation possible.

What you should see: A clean Google Sheet where each row is one week's data.

Part 2: Create your Zapier account

Go to zapier.com and sign up. Connect your Google account when prompted.

Part 3: Set up your Zap trigger

Click Create Zap → set the Trigger:

  • App: Google Sheets
  • Trigger: New Row (fires when you add a new week's row)
  • Connect your spreadsheet and select the correct sheet tab
  • Test with your sample data row

What you should see: Zapier pulling in your data row and showing each column as a variable.

Part 4: Add an AI action to write the commentary

Add an action step:

  • App: AI by Zapier (or OpenAI if you have an API key)
  • Action: Summarize or Generate Text
  • In the prompt field, build your AI instruction using Zapier variables:
Copy and paste this
Write a professional weekly sales commentary for the [Category] buying team.

Data:
- Week: [Week variable]
- Net Sales: $[Net Sales variable], [% to Plan variable]% to plan, [% to LY variable]% vs. last year
- Top performers: [Top Performer 1 variable], [Top Performer 2 variable]
- Underperformers: [Underperformer 1 variable], [Underperformer 2 variable]
- Inventory: [Inventory variable] weeks of supply
- Actions taken: [Actions Taken variable]
- Context: [Notes variable]

Write 2-3 paragraphs: (1) performance headline and drivers, (2) key opportunities and risks, (3) forward actions. Professional and concise.

Part 5: Create the Gmail draft

Add another action:

  • App: Gmail
  • Action: Create Draft
  • To: your email address (so it drafts to you)
  • Subject: "Week [Week variable] Sales Commentary: [Category]. DRAFT"
  • Body: [Output from the AI action step]

Part 6: Test the full automation

Add a test row to your Google Sheet. Zapier should fire: pull the data → generate narrative → create Gmail draft. Check your Gmail Drafts folder. Review the output for quality.

Part 7: Activate and run weekly

Turn on the Zap. Each week when you fill in your data row, the draft is ready in Gmail within 5 minutes. Your weekly process becomes: fill in structured data (5 min) → review and edit the draft (15-20 min) → send.

Real Example: Monday Morning Workflow

Before automation: Export ERP data → format in Excel → build charts → write narrative commentary → paste into email → format → send. Total: 2-3 hours.

After automation:

  1. Monday 8:00 AM: Fill in weekly data sheet (5 minutes)
  2. 8:05 AM: Zapier fires automatically
  3. 8:10 AM: Gmail draft appears with full commentary
  4. 8:10-8:30 AM: Review draft, verify numbers, add your personal judgment on what the numbers mean strategically
  5. 8:30 AM: Send. 30 minutes total instead of 2-3 hours.

Sample output quality: "Week 12 results for Women's Footwear were mixed: net sales of $312K came in -4% to plan but +2% vs. prior year, with performance skewed by a continued warm weather pattern delaying seasonal transition. Boots underperformed significantly at -18% to plan, while sneakers (+22%) and early sandal demand (+14%) provided meaningful offset. With boots now marked and at 2.1 weeks supply, and sandals running lean at 1.4 weeks, the forward focus is a potential sandal reorder and monitoring the transition point as weather normalizes..."

What to Do When It Breaks

  • Zap fires but no draft appears → Check Gmail permissions. Zapier needs explicit permission to create drafts; re-authenticate
  • AI commentary is generic → Improve your prompt in the AI action step; add specific format requirements and examples
  • Data variables appear blank → Check that Zapier correctly mapped the sheet columns to the prompt variables; re-test the trigger
  • Automation runs but numbers are wrong → Review your input data. the AI cannot correct errors in the sheet; validate your input row

Variations

  • Simpler version: Skip the AI action. just have Zapier format your data into a pre-written template with blank [NARRATIVE] sections you fill in manually
  • Extended version: Add a step that sends the draft to your assistant buyer for preliminary review before it reaches you

What to Do Next

  • This week: Build the Google Sheet template and practice filling it in consistently for 2 weeks before adding the automation
  • This month: Build the full Zap and test for 3-4 weeks before relying on it
  • Advanced: Expand to include a Tableau or Looker Studio embed in the email so the narrative arrives alongside the charts automatically

Advanced guide for retail buyer professionals. Requires Zapier account (free for simple version, {{tool:Zapier.price}}/month for AI actions).