Customer Reminder MCP

A Model Context Protocol (MCP) server that helps business owners manage and automatically send customer reminders via email. This system integrates with Google Sheets to read customer data and sends scheduled reminders based on due dates.

Features

🚀 Google Sheets Integration

• Connect to Google Sheets to read customer reminder data

• Support for multiple date formats (YYYY-MM-DD, MM/DD/YYYY, DD/MM/YYYY)

• Automatic data validation and error handling

📧 Email Reminders

• Send personalized email reminders to customers

• Customizable message templates with dynamic placeholders

• Support for Gmail SMTP with app passwords

⏰ Intelligent Scheduling

• APScheduler-based background processing

• Configurable interval checking (minutes, hours, days)

• Prevents duplicate reminder sends with tracking system

• Immediate execution or scheduled background service

🔌 Model Context Protocol (MCP) Integration

• Full MCP server implementation

• Tools for checking reminders, sending reminders, and retrieving data

• Resources for accessing customer reminders and sent reminder history

• Test mode to verify sheet connection and server status

Project Structure

Prerequisites

1. Python 3.8+ installed

2. Google Sheets API credentials (service account JSON file)

3. Gmail account with app password enabled

4. Google Sheet with customer reminder data

Setup Instructions

1. Install Dependencies

Or install individually:

2. Set up Google Sheets API Credentials

1. Copy the provided example file:

   cp credentials.example.json credentials.json

2. Replace the placeholder values in credentials.json with your actual Google service account credentials.

3. SECURITY WARNING: Never commit your credentials.json file to version control or share it publicly!

3. Create Environment File

1. Copy the provided example file:

   cp .env.example .env

2. Edit the .env file and update with your actual values:

4. Test Your Configuration

Test your connection to Google Sheets and verify the setup:

This will verify your connection and display the available MCP tools and resources.

3. Setup Google Sheets API

1. Go to Google Cloud Console

2. Create a new project or select existing

3. Enable Google Sheets API

4. Create service account credentials:

   • Navigate to "IAM & Admin" > "Service Accounts"

   • Click "Create Service Account"

   • Provide a name and description

   • For role, select a minimal role like "Viewer" or create a custom role with specific permissions

5. Create a key for the service account:

   • Select your new service account

   • Go to the "Keys" tab

   • Click "Add Key" > "Create new key"

   • Choose JSON format

6. Download the JSON credentials file and save it as credentials.json in your project directory

7. Share your Google Sheet with the service account email address (limited to "Viewer" or "Editor" as needed)

8. IMPORTANT: Keep your credentials secure:

   • Never commit this file to version control

   • Restrict access to the credentials file on your local system

   • Consider encrypting the file when not in use

4. Prepare Your Google Sheet

Create a Google Sheet with these columns:

• customer_name - Customer's full name

• customer_email - Customer's email address

• due_date - When reminder is due (YYYY-MM-DD format)

• message_template - Custom message (use {customer_name} and {due_date} placeholders)

• reminder_type - Set to "email"

Example:

Usage

Run Once (Immediate)

Start Scheduler (Background Service)

Run MCP Server

Help

How It Works

1. Data Source: Reads customer data from Google Sheets

2. Date Checking: Identifies reminders due today or overdue

3. Duplicate Prevention: Tracks sent reminders to prevent duplicates

4. Email Sending: Sends personalized emails via SMTP

5. Scheduling: Runs automatically at specified intervals

6. Logging: Provides detailed console output for monitoring

7. MCP Integration: Exposes functionality via Model Context Protocol

Scheduler Settings

• DEFAULT_INTERVAL_MINUTES: Default check interval (default: 6)

MCP Server Tools

The MCP server exposes the following tools:

• send_reminder: Sends a single reminder to a customer

• check_due_reminders: Checks for and sends all due reminders

• get_customer_reminders: Retrieves customer reminder data

• get_sent_reminders: Retrieves history of sent reminders

• get_sheet_status: Checks connection status with Google Sheets

MCP Server Resources

• reminders://customer-reminders: Customer reminder data

• reminders://sent-reminders: History of sent reminders

Troubleshooting

Common Issues

Gmail Authentication Error (535)

• Ensure 2-Step Verification is enabled

• Use app password, not regular password

• Check username/password in .env file

Google Sheets Not Found

• Verify spreadsheet name matches exactly

• Ensure service account has access to the sheet

• Check credentials.json file exists

No Reminders Found

• Verify date format in Google Sheet (YYYY-MM-DD)

• Check if reminders are due (today or overdue)

• Ensure required columns exist with correct names

Development

Testing

Use short intervals for testing:

Test the MCP server functionality:

This will show:

1. Available tools and resources

2. Example tool calls

3. Google Sheets connection status

4. Current reminders in the system

Adding Features

The modular design allows easy extension:

• Add new reminder types (SMS, etc.)

• Integrate with different data sources

• Add web dashboard

• Implement advanced scheduling

• Create new MCP tools and resources

License

This project is part of the Model Context Protocol ecosystem and is designed for business automation and customer relationship management for Preshify.

Security Best Practices

Protecting Sensitive Information

1. Never commit credentials to version control

   • Both credentials.json and .env files contain sensitive information

   • Always keep these files in your .gitignore

   • Use example files (provided) to show the structure without exposing real data

2. Regularly rotate credentials

   • Periodically generate new Google service account keys

   • Update your Gmail app password regularly

   • Revoke unused or potentially compromised credentials

3. Limit permissions

   • Your Google service account should have minimal required permissions

   • Share your Google Sheet with the service account email using the most restrictive permissions necessary

4. Monitor for unauthorized access

   • Regularly check the Google Cloud Console for unusual activity

   • Review application logs for unexpected behavior

   • Be alert to unplanned or unexpected emails being sent

Data Handling Recommendations

1. Minimize data retention

   • Only store the customer data you need

   • Consider implementing a data retention policy

   • Regularly clean up old reminder data

2. Implement logging best practices

   • Don't log sensitive customer information

   • Use log rotation to prevent log files from growing too large

   • Regularly review logs for security issues

Support

For issues, questions, or feature requests, please create an issue in the repository.