Email Open Tracker

1. 🧩 Project Overview

A lightweight, serverless, real-time email open tracking system combining Vercel Serverless Functions, Google Apps Script Webhooks, and Google Sheets. The system captures when an email recipient opens a tracked email by loading a 1×1 tracking pixel.

It logs metadata (IP, user agent, timestamp) and sends instant Gmail notifications every time the email is opened.

2. 🎯 Objectives & Goals

Track every email open event in real time.
Log each event into Google Sheets for audit and analytics.
Forward metadata to Google Apps Script for processing.
Send Gmail notifications automatically.
Deploy a minimal, scalable, cost‑free solution leveraging serverless infrastructure.

3. ✅ Acceptance Criteria

Pixel endpoint must respond with a valid 1×1 GIF.
Logs must appear in Google Sheets with correct schema.
Gmail notifications must trigger on every open.
System must operate without a backend server or database.
Endpoint must handle repeated opens.
Deployed on Vercel with environment variable support.

4. 💻 Prerequisites

Git & GitHub account
Vercel account
Google Account (for Sheets & Apps Script)
Node.js 18+ (optional for local testing)
Basic understanding of serverless functions

5. ⚙️ Installation & Setup

Clone the repository

git clone https://github.com/<your-username>/email-open-tracker.git
cd email-open-tracker

Folder Structure

email-open-tracker/
 ├── api/
 │    └── open.js
 ├── package.json
 ├── .gitignore
 └── README.md

Install Dependencies (if needed)

npm install

Configure Vercel Environment Variable

APPSCRIPT_WEBHOOK=<Your Google Apps Script URL>

Deploy

vercel --prod

6. 🔗 API Documentation

Endpoint

GET /api/open?mid=<mail_id>&v=<nonce>

Query Parameters

Param	Type	Required	Description
mid	string	yes	Unique email/message ID
v	string	no	Random value to avoid caching

Response

Content-Type: image/gif
1×1 transparent tracking pixel

7. 🖥️ UI / Frontend

This project does not include a UI. It is a pure backend pixel-tracking architecture.

If UI is added later, include:

Dashboard for logs
Table component for events
Real-time updates via WebSockets or Polling

8. 🔢 Status Codes

Code	Meaning
200	Pixel delivered successfully
500	Internal serverless function error
400	Missing or invalid query parameters

9. 🚀 Features

Unlimited email open tracking
Serverless, scalable, zero-cost model
Instant Gmail notifications
Logs maintained in Google Sheets
Fully stateless API

10. 🧱 Tech Stack & Architecture

Vercel Serverless Functions → pixel endpoint
Google Apps Script → webhook processor & email sender
Google Sheets → open-event storage
JavaScript (Node.js)
HTTP-based pixel tracking

11. 🛠️ Workflow & Implementation

Configure Google Sheet with header schema
Write Apps Script webhook handler
Deploy the script and get public URL
Add URL to Vercel environment variables
Build /api/open.js serverless function
Deploy to Vercel
Embed pixel in outbound email
Logs appear in Sheets
Gmail sends real-time notifications

12. 🧪 Testing & Validation

Test Case	Input	Expected Output
Pixel Load	URL hit	GIF + Sheet Row
Multiple Opens	Same email	Multiple log rows
Missing mid	URL without mid	400 error
Apps Script Down	Webhook unreachable	Pixel still loads

13. 🔍 Validation Summary

End-to-end flow validated for repeated open events
Verified pixel caching disabled (via v param)
Verified Sheets + Gmail notification latency ~1–2 seconds

14. 🧰 Verification Tools & Examples

cURL

curl "https://your-app.vercel.app/api/open?mid=test123&v=111"

Postman/Thunder Client
Google Chrome Network Inspector

15. 🧯 Troubleshooting

Issue	Fix
Pixel not firing	Add `v` query param to avoid cache
No logs	Verify Apps Script URL in Vercel
No Gmail notification	Check permissions in Apps Script
500 error	Missing env variable

16. 🔒 Security & Secrets

Do not expose Apps Script URL in public repos
Use HTTPS pixel endpoint
Use long random mid to avoid enumeration attacks

17. ☁️ Deployment

Vercel

Auto-builds API route
Instant CDN-based scaling

Netlify / GitHub Pages

Not supported for serverless API required by pixel tracking

18. ⚡ Quick-Start Cheat Sheet

1. Deploy Apps Script
2. Set APPSCRIPT_WEBHOOK in Vercel
3. Deploy to Vercel
4. Use pixel in email
5. Track opens in Sheets

19. 🧾 Usage Notes

Use unique mid for each email user
Add cache busters using v=<timestamp>
Pixel loads silently—no UI

20. 🧠 Performance & Optimization

Serverless cold start minimal
Uses fast, stateless HTTP requests
Apps Script batching possible for future scaling

21. 🌟 Enhancements

Dashboard UI
Analytics charts
Geo-IP enhancements
Event batching

22. 🧩 Maintenance & Future Work

Rotate script endpoints periodically
Add IP anonymization for GDPR compliance
Add retry queue if Apps Script is down

23. 🏆 Key Achievements

Achieved 100% serverless, zero-infrastructure system
Near real-time event tracking
Infinite scalability using Vercel + Google Apps Script

24. 🧮 High-Level Architecture

Email Client → Vercel Pixel API → Google Apps Script → Google Sheets + Gmail Notification

25. 🗂️ Folder Structure

email-open-tracker/
 ├── api/
 │    └── open.js
 ├── .gitignore
 ├── package.json
 └── README.md

26. 🧭 How to Demonstrate Live

Deploy to Vercel
Send email with:

<img src="https://your-app.vercel.app/api/open?mid=demo123&v=1" />

Open email from multiple devices
Show real-time Sheet logs and Gmail notifications

27. 💡 Summary & Closure

This project provides a production-grade, serverless email open tracking solution with zero infrastructure cost and instant scalability. Leveraging Vercel + Google Apps Script ensures quick deployments, real-time notifications, and a clean, fault-tolerant architecture suitable for enterprise-grade tracking pipelines.

All components are modular, extensible, and aligned with modern web engineering standards.

Name		Name	Last commit message	Last commit date
Latest commit History 3 Commits
api		api
.gitignore		.gitignore
README.md		README.md
package.json		package.json

bitsandbrains/serverless-email-open-intelligence-platform

Folders and files

Latest commit

History

Repository files navigation