Share:
Table of Contents
Building an AI Marketing Email Assistant
As a marketer, I used to spend hours every week crafting email summaries of our sales performance. It was time-consuming and repetitive - exactly the kind of task that’s perfect for automation. So I built an AI assistant that automatically generates these weekly marketing emails from our sales data. Here’s how you can build one too!
What You’ll Build
We’ll create a Python script that:
- Fetches sales data from Google Sheets
- Uses GPT to generate engaging summaries
- Formats and sends marketing emails
- Runs automatically on a weekly schedule
graph LR
A[Google Sheets] --> B[Python Script]
B --> C[OpenAI GPT]
C --> D[Email Format]
D --> E[Gmail/HubSpot]Setting Up Google Sheets and Sales Data
First, create a Google Sheet with your sales data. Here’s a simple structure to start with:
Date,Product,Revenue,Units Sold,Region
2024-02-01,Product A,1500.00,3,North
2024-02-02,Product B,750.00,5,South
2024-02-03,Product A,2000.00,4,EastConnecting to Google Sheets API
Let’s set up the Google Sheets connection:
from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from google.oauth2 import service_account
def setup_sheets_api():
# Load credentials from your service account file
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
creds = service_account.Credentials.from_service_account_file(
'service-account.json', scopes=SCOPES)
# Build the service
service = build('sheets', 'v4', credentials=creds)
return service
def get_sales_data(service, spreadsheet_id, range_name):
try:
result = service.spreadsheets().values().get(
spreadsheetId=spreadsheet_id,
range=range_name
).execute()
rows = result.get('values', [])
return rows
except Exception as e:
print(f"Error fetching data: {e}")
return NoneProcessing and Summarizing Sales Data with GPT
Now, let’s use OpenAI’s GPT to generate our email content:
from openai import OpenAI
import pandas as pd
def prepare_sales_summary(sales_data):
# Convert to DataFrame for easier processing
df = pd.DataFrame(sales_data[1:], columns=sales_data[0])
# Calculate key metrics
total_revenue = df['Revenue'].astype(float).sum()
top_product = df.groupby('Product')['Revenue'].sum().idxmax()
total_units = df['Units Sold'].astype(int).sum()
return {
'total_revenue': total_revenue,
'top_product': top_product,
'total_units': total_units
}
def generate_email_content(metrics):
client = OpenAI()
prompt = f"""Write a professional marketing email summarizing this week's sales:
- Total Revenue: ${metrics['total_revenue']:,.2f}
- Top Selling Product: {metrics['top_product']}
- Total Units Sold: {metrics['total_units']}
Make it engaging and highlight key achievements. Include a positive outlook.
"""
try:
response = client.chat.completions.create(
model="gpt-4",
messages=[{
"role": "system",
"content": "You are a professional marketing copywriter."
}, {
"role": "user",
"content": prompt
}],
temperature=0.7
)
return response.choices[0].message.content
except Exception as e:
print(f"Error generating content: {e}")
return NoneFormatting and Sending the Email
Let’s create a function to format and send our email using the Gmail API:
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from googleapiclient.discovery import build
def send_email(service, sender, to, subject, content):
message = MIMEMultipart()
message['to'] = to
message['from'] = sender
message['subject'] = subject
# Add HTML body
html_content = f"""
<html>
<body>
<div style="font-family: Arial, sans-serif; max-width: 600px; margin: 0 auto;">
{content}
<hr>
<p style="color: #666; font-size: 12px;">
This email was generated by our AI Marketing Assistant
</p>
</div>
</body>
</html>
"""
message.attach(MIMEText(html_content, 'html'))
try:
raw_message = base64.urlsafe_b64encode(message.as_bytes()).decode('utf-8')
service.users().messages().send(userId='me', body={'raw': raw_message}).execute()
return True
except Exception as e:
print(f"Error sending email: {e}")
return FalseAutomating with a Weekly Schedule
Finally, let’s set up automated weekly runs using Python’s schedule library:
import schedule
import time
def weekly_report_job():
# Setup APIs
sheets_service = setup_sheets_api()
gmail_service = setup_gmail_api()
# Get sales data
sales_data = get_sales_data(sheets_service, SPREADSHEET_ID, 'Sheet1!A1:E')
if not sales_data:
return
# Generate content
metrics = prepare_sales_summary(sales_data)
content = generate_email_content(metrics)
if not content:
return
# Send email
send_email(
gmail_service,
'your-email@company.com',
'marketing-team@company.com',
'Weekly Sales Performance Update',
content
)
# Schedule the job
schedule.every().monday.at("09:00").do(weekly_report_job)
while True:
schedule.run_pending()
time.sleep(60)Error Handling and Best Practices
-
API Rate Limits:
- Implement exponential backoff for API retries
- Cache API responses where appropriate
- Monitor your API usage
-
Data Validation:
- Always validate your sales data format
- Handle missing or malformed data gracefully
- Log any data inconsistencies
-
Security:
- Store API keys in environment variables
- Use service accounts with minimal permissions
- Regularly rotate credentials
Conclusion
By automating your weekly marketing emails with AI, you can:
- Save hours of manual work each week
- Maintain consistent communication with stakeholders
- Focus on strategic marketing initiatives
This solution can be extended to include:
- Custom charts and visualizations
- Multiple data sources
- A/B testing of email content
- Personalization based on recipient
Ready to try it yourself? Start with a small dataset and gradually expand the functionality. The possibilities for automation are endless!
Resources
Happy automating! 🚀
