Automation: Auto-Generate and Email Your Weekly Shrink Report

What This Builds

Instead of spending 45–60 minutes every Friday compiling your shrink data and writing a management summary, this automation does it for you. You enter shrink data throughout the week in a Google Sheet. On Friday at 4pm, the system automatically collects the week's data, passes it to ChatGPT to generate a narrative summary with root causes, and emails the finished report to your store director — all without you touching it.

Prerequisites

• Comfortable using Google Sheets (can enter data, use basic formulas)
• Google account (free)
• ChatGPT account (free at chatgpt.com — for testing; Zapier uses its own OpenAI connection)
• Zapier account (free tier works; allows 5 active zaps)
• Time needed: 1.5–2 hours to build; runs automatically forever after

The Concept

Think of this as setting up a weekly assistant. You're the data entry person — you record what went wrong during the week in a shared spreadsheet. The automation is the report writer — it takes your raw entries and turns them into a polished summary email at the end of every week. You set it up once, and Friday afternoons become a little easier forever.

Build It Step by Step

Part 1: Set Up Your Google Sheets Shrink Log

Step 1.1: Create the spreadsheet Open Google Sheets (sheets.google.com). Create a new sheet named "Weekly Shrink Log [Year]."

Step 1.2: Set up column headers in Row 1:

• Column A: Date
• Column B: Item Name
• Column C: Category (Produce / Deli / Meat / Bakery / etc.)
• Column D: Quantity Lost
• Column E: Unit (cases / units / lbs)
• Column F: Dollar Value ($)
• Column G: Type of Loss (Damage / Spoilage / Date Code / Theft / Unknown)
• Column H: Notes (optional — e.g., "DSD delivery, left in back room too long")

Step 1.3: Add a "Report Trigger" cell In cell J1, type "Report Trigger". In cell J2, enter: =SUMIF(A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())-WEEKDAY(TODAY(),2)+1),F:F) — this calculates the current week's total shrink value. You'll use this cell as a trigger.

Add a second cell K2 with the label "SEND REPORT" and leave it empty for now — you'll type "YES" here each Friday to trigger the automation.

Step 1.4: Share the spreadsheet Click Share → enter your email (the one you'll use with Zapier). Also share with your assistant manager if you want them to log entries too.

Part 2: Set Up Zapier

Step 2.1: Create your Zapier account Go to zapier.com. Sign up with your Google account (or any email). Choose the free plan.

Step 2.2: Create a new Zap Click Create Zap in the top right.

Step 2.3: Set the Trigger — Google Sheets "New or Updated Row"

• Click the Trigger box. Search "Google Sheets."
• Select the Google Sheets app.
• Choose event: New or Updated Spreadsheet Row.
• Connect your Google account when prompted.
• Select your spreadsheet and the sheet tab ("Weekly Shrink Log").
• Set "Trigger Column" to column K (your "SEND REPORT" column).
• Click Continue and test the trigger.

Step 2.4: Add an Action — OpenAI (ChatGPT) "Send Prompt"

• Click the + button to add a step.
• Search "OpenAI" and select it.
• Choose action: Send Prompt.
• Connect your OpenAI account (you need an OpenAI API key — get one free at platform.openai.com → API keys).
• In the Prompt field, type:

You are a grocery department manager's reporting assistant.

Here is this week's shrink log data:
[insert your data summary here - Zapier will auto-populate this]

Please write a professional weekly shrink summary for the store director. Include:
1. Total shrink value for the week
2. Top 3 shrink items by dollar value
3. Any patterns observed (day, category, type of loss)
4. 2-3 possible root causes
5. 1 recommended action for next week

Keep it under 250 words. Professional tone.

• In the data field, map it to your Google Sheets data using Zapier's field mapping.

Step 2.5: Add a Final Action — Gmail "Send Email"

• Add another step. Search "Gmail" and select it.
• Choose action: Send Email.
• Connect your Gmail account.
• Fill in:

  • To: your store director's email
  • Subject: Weekly Shrink Report — [Department] — Week of [Date]
  • Body: Map this to the output from the ChatGPT step
  • CC: yourself (optional but recommended)

Step 2.6: Test and turn on the Zap Click Test in each step. If everything looks correct, click Publish to turn on your Zap.

Part 3: Test and Refine

Step 3.1: Run your first test Enter a few rows of sample shrink data in your Google Sheet. In cell K2, type "YES." Wait 1–2 minutes. Check your email — the automated report should arrive.

Step 3.2: Review the output Read the generated report. Does it sound like something you'd send? Is the language appropriate for your store director? If the tone needs adjustment, update the prompt in your Zapier OpenAI step.

Step 3.3: Reset the trigger After each weekly send, delete "YES" from cell K2 so it doesn't re-trigger. You can set a recurring reminder on your phone: "Friday 4pm — type YES in shrink sheet."

Real Example: Produce Department, Week of March 15

Setup: 8 rows of shrink data entered throughout the week (strawberries, bagged salads, herbs, pre-cut fruit, etc.)

Input: Friday 4pm — manager types "YES" in K2

Output email received 2 minutes later:

Time saved: 50 minutes of manual data compilation and report writing, every single week.

What to Do When It Breaks

• Zapier doesn't trigger when I type YES → Check that the trigger column in Zapier settings matches the exact column (K) where you're typing. Zapier sometimes needs 5–10 minutes to detect a change.
• ChatGPT output is vague or too generic → Improve your prompt. Add more context about your specific department and what your store director cares about most.
• Email isn't sending → Check that Gmail authorization in Zapier is still connected (it expires occasionally). Go to Zapier → Apps → Gmail → reconnect.
• OpenAI step fails → Check your API key at platform.openai.com. Free API keys have usage limits — if exceeded, you need to add billing or create a new key.

Variations

• Simpler version: Skip Zapier entirely. Every Friday, paste the week's Google Sheet data into Claude manually and ask for the summary. Takes 3 minutes instead of automating it — still saves 45 minutes vs. writing from scratch.
• Extended version: Add a second Zap that runs a 4-week trend analysis the first Friday of each month — summarizing patterns over the past month rather than just the current week.

What to Do Next

• This week: Build the Google Sheets log and start entering data manually — even before automating
• This month: Set up the Zapier connection and run your first automated report
• Advanced: Connect to a Slack channel instead of email so the report posts automatically to a store management group

Advanced guide for Grocery Department Manager professionals. This automation uses paid API services (OpenAI) — cost is typically $0.01–0.05 per weekly report at current API pricing.