Automation Recipe: Contract Renewal Alert System

What This Builds

Instead of relying on a spreadsheet you have to remember to check, this automation monitors your contract renewal dates and emails you a personalized briefing 90, 60, and 30 days before each renewal, including contract highlights and negotiation talking points. You set it up once, and it runs on autopilot for every contract in your tracker.

Prerequisites

• Comfortable using ChatGPT for procurement tasks (Level 3)
• A Google account (free)
• A Zapier account (free tier covers this automation)
• A ChatGPT account with API access (ChatGPT Plus, $20/mo), or use Zapier's native ChatGPT integration
• Time to build: 1.5-2 hours
• Cost: Free to $20/mo depending on tools

The Concept

Think of this as setting up a smart contract calendar assistant. You maintain one Google Sheet with your contracts. The automation checks that sheet every day, looks for upcoming renewal dates, and when a deadline is approaching, it uses ChatGPT to write you a personalized briefing about that contract. You show up to renewal conversations prepared, not scrambling.

Build It Step by Step

Part 1: Build Your Contract Tracker in Google Sheets

1. Open Google Sheets and create a new spreadsheet named "Contract Renewal Tracker"

2. Create these column headers in Row 1:

   • A: Vendor Name
   • B: Contract Description (e.g., "3-year facilities management services")
   • C: Contract Start Date
   • D: Contract End Date (renewal date)
   • E: Auto-Renewal Terms (e.g., "Auto-renews 1 year unless 90-day notice given")
   • F: Annual Contract Value ($)
   • G: Notice Deadline (date you must notify vendor by to avoid auto-renewal)
   • H: Key Terms Summary (2-3 sentence summary of what the contract covers)
   • I: Last Renewal Status (Renewed/Renegotiated/Cancelled/Pending)

3. Fill in your top 10-20 contracts. For notice deadlines: if a contract auto-renews with 90-day notice, set column G to 90 days before column D.

4. Add a formula in a new column J to calculate days until notice deadline:

   • In J2: =G2-TODAY()
   • This shows how many days until the notice deadline for each contract

Part 2: Set Up the Zapier Automation

1. Go to zapier.com and create a free account
2. Click + Create Zap

Trigger: Schedule: Daily Check 3. Search for and select Schedule by Zapier as the trigger 4. Set it to run Every Day at 8:00 AM (your time zone)

Action 1: Find Contracts Due for Alerts 5. Add a Google Sheets action: "Look Up Spreadsheet Row" 6. Connect to your Contract Renewal Tracker 7. In the lookup criteria, filter for rows where column J (days until notice) equals 90, 60, or 30

Note: Zapier's free tier handles basic lookups. For filtering multiple values (90 OR 60 OR 30), you may need to create three separate Zaps, one for each alert window.

Action 2: Generate the Briefing with ChatGPT 8. Add a ChatGPT action (or use Zapier's AI by Zapier action if preferred) 9. Set the action to "Send Prompt" 10. In the prompt field, construct a dynamic message using the data from Google Sheets:

You are a procurement analyst. Write a contract renewal briefing for a purchasing agent.

Contract details:
- Vendor: [Vendor Name from Sheet]
- Contract: [Contract Description from Sheet]
- Renewal date: [Contract End Date from Sheet]
- Annual value: $[Annual Contract Value from Sheet]
- Auto-renewal terms: [Auto-Renewal Terms from Sheet]
- Notice deadline: [Notice Deadline from Sheet], [days until notice] days away
- Key terms: [Key Terms Summary from Sheet]

Include:
1. What action is needed and by when (3 sentences max)
2. Two negotiation talking points if choosing to renegotiate
3. One market context note relevant to this category
Keep it under 200 words.

Action 3: Send the Email 11. Add a Gmail (or Outlook) action: "Send Email" 12. Set recipient to your email 13. Subject: ⚠️ Contract Renewal Alert: [Vendor Name]: [days] days to notice deadline 14. Body: [ChatGPT output from previous action]

Part 3: Test and Refine

1. Manually change a test contract's notice deadline to today in your spreadsheet
2. Run the Zap manually (Zap → Run) to test it
3. You should receive an email within 30-60 seconds with a contract briefing
4. Check the email:

   • Is the vendor name and contract description correct?
   • Is the date calculation accurate?
   • Is the ChatGPT briefing relevant and useful?

Common adjustments:

• If the briefing is too generic, add more detail to the "Key Terms Summary" column in your sheet
• If you want richer market context, add a "Category" column (e.g., "Facilities Services", "IT Software") and include it in the ChatGPT prompt

Real Example: Facilities Management Contract Alert

Setup: You have a 3-year facilities management contract for $240,000/year. End date: December 31, 2026. 90-day notice required. You entered all this in your tracker.

Trigger: It's October 2, exactly 90 days before December 31.

Input to ChatGPT: Vendor: "CleanTech Facilities", Contract: "3-year janitorial and maintenance services", Annual value: $240,000, Notice deadline: October 2, Key terms: "Covers daily cleaning, HVAC PM, and grounds maintenance for 3 buildings"

Output email you receive:

Time saved: Instead of missing this deadline and being locked in for another 3 years, you caught it and negotiated a 7% reduction + improved SLAs.

What to Do When It Breaks

• Zap fires but no email arrives → Check your Gmail/Outlook action for permission issues. Re-authorize the email account in Zapier.
• ChatGPT produces generic output → Add more detail to the Key Terms Summary column. The richer your data, the better the briefing.
• Date calculations seem off → Verify your sheet uses actual Date format columns (not text). Check the formula =G2-TODAY() is showing whole numbers.
• Zap fires for contracts not near deadline → Add a filter step: "Only continue if [Days Until Notice] is less than 91" to prevent off-cycle alerts.

Variations

• Simpler version: Skip ChatGPT entirely. Set up just the Google Sheets + Gmail Zap to send a plain reminder email: "Contract with [Vendor] renews in [X] days. Notice deadline: [date]." Takes 30 minutes to build and still prevents missed renewals.
• Extended version: Add a Slack message action (in addition to email) to post in your team's procurement channel so your manager also sees renewal alerts. Useful if renewals need management approval.

What to Do Next

• This week: Enter all your active contracts into the Google Sheet
• This month: Refine the ChatGPT prompt based on your first few alerts. Customize the negotiation talking points to reference your specific categories
• Advanced: Add a "Budget Year" column and connect the tracker to a second Zap that creates a year-end budget review for all contracts expiring in the next 12 months

Advanced guide for Purchasing Agent professionals. These techniques use more sophisticated AI features that may require paid subscriptions.