# Email Tracking Implementation Guide ## Overview This document describes the complete email tracking system for auto-quotation emails, including how tracking works, what was fixed, and how to use it. ## Architecture ### Components 1. **EmailTrigger Service** (`app/Services/AutoQuotation/Helpers/EmailTrigger.php`) - Sends auto-quotation emails with tracking - Logs emails to database with tracking ID - Prepares email data and templates 2. **EmailTrackingService** (`app/Service/AutoQuote/EmailTrackingService.php`) - Handles tracking events (open, click, download) - Updates aggregate statistics - Logs activities 3. **Tracking Routes** (`routes/web.php`) - Public routes for tracking email opens, clicks, and downloads - No authentication required 4. **Email Template** (`resources/views/emails/layouts/auto_quotation.blade.php`) - Responsive email layout with tracking integration - Multiple tracking methods for reliability ## Tracking Methods ### 1. Logo Tracking (Primary Method) The company logo in the email header is loaded through a tracking URL: ```blade {{ env('APP_NAME') }} ``` - **Route:** `/track/logo/{id}` - **Advantage:** Most reliable, always loads, visible to user - **Tracks:** Email opens ### 2. Footer Social Icon Tracking (Secondary Method) The first social media icon (Facebook) in footer uses tracking: ```blade Facebook ``` - **Route:** `/track/footer/{id}` - **Advantage:** Additional tracking point if header fails - **Tracks:** Email opens ### 3. Hidden Pixel Tracking (Fallback Method) Invisible 1x1 pixel at end of email: ```blade ``` - **Route:** `/track/open/{id}` - **Advantage:** Doesn't affect email appearance - **Limitation:** May be blocked by email clients ### 4. Click Tracking Quotation URL links are replaced with tracked URLs: ```php $emailBody = str_replace('{{quotation_url}}', url('/track/click/' . $emailLog->id), $emailBody); ``` - **Route:** `/track/click/{id}` - **Action:** Tracks click, then redirects to actual quotation page - **Tracks:** Link clicks ### 5. Download Tracking PDF download links use tracked URLs: - **Route:** `/track/download/{id}` - **Action:** Tracks download, then redirects to PDF download - **Tracks:** File downloads ## How It Works ### Email Sending Flow ``` 1. Auto-quotation generated ↓ 2. EmailTrigger.send() called ↓ 3. Buyer info fetched from lead ↓ 4. Email template loaded ↓ 5. Email data prepared (variables) ↓ 6. Template parsed with variables ↓ 7. Email logged to database → Gets tracking ID ↓ 8. {{quotation_url}} replaced with tracking URL ↓ 9. Email sent via Mail::send() with tracking ID ↓ 10. Blade template adds tracking images automatically ``` ### Tracking Flow ``` Buyer opens email ↓ Logo/footer image loads from /track/logo/{id} ↓ EmailTrackingService.trackOpen() called ↓ Event logged in auto_quote_email_events table ↓ Aggregate counters updated in auto_quote_emails table ↓ Activity logged for seller dashboard ``` ## Database Schema ### auto_quote_emails table ```sql - id (tracking ID) - auto_quote_workflow_id - auto_email_setting_id - quotation_number - recipient_email - email_body - sent_at - open_count (incremented on each open) - click_count (incremented on each click) - download_count (incremented on each download) - first_opened_at - last_opened_at - first_clicked_at - last_clicked_at - first_downloaded_at - last_downloaded_at ``` ### auto_quote_email_events table ```sql - id - auto_quote_email_id - event_type (open/click/download) - ip_address - user_agent - referrer - created_at ``` ## Routes Summary | Route | Purpose | Returns | |-------|---------|---------| | `/track/open/{id}` | Hidden pixel tracking | pixel.png (1x1 transparent) | | `/track/logo/{id}` | Logo image tracking | Company logo image | | `/track/footer/{id}` | Footer icon tracking | Social icon image | | `/track/click/{id}` | Link click tracking | Redirect to quotation page | | `/track/download/{id}` | Download tracking | Redirect to PDF download | ## Code Improvements Made ### ❌ Before (Issues) ```php // Hardcoded user ID - sends to same person always! $buyer = User::where('id', 113840)->first(); // Debug code left in production ///dd($quotation); /// dd($buyer,'buyer'); // Redundant data fetching $buyer = CrmLeadBuyerDetail::query()->where('id', $lead->buyer_user_id)->first(); // ... when $buyer was already passed as parameter // Duplicate keys 'mail' => $buyer->email ?? null, 'email' => $buyer->email ?? null, // Inconsistent naming 'mobile_no.' => $buyer->mobile_no ?? null, // trailing period! // Dead code (20+ lines commented out) // return [ // 'buyer_name' => $buyer->name ?? '', // ... // ]; // Duplicate tracking logic public function trackEmailOpen(string $quotationNumber) { // Manually updating counts - duplicates EmailTrackingService } ``` ### ✅ After (Fixed) ```php // Properly fetch buyer from lead $lead = \App\Models\Crm\CrmLead::query() ->where('id', $quotation->leadId) ->first(); $buyer = CrmLeadBuyerDetail::query() ->where('id', $lead->buyer_user_id) ->first(); // Clean data mapping $mapping = [ 'email' => $buyer->email ?? '', 'mobile_no' => $buyer->mobile_no ?? '', 'quotation_url' => '{{quotation_url}}', // Replaced with tracking URL later ]; // Use centralized tracking service public function trackEmailOpen(int $emailId): void { app(\App\Service\AutoQuote\EmailTrackingService::class)->trackOpen($emailId); } ``` ## Email Template Variables Available variables in email templates: | Variable | Description | |----------|-------------| | `{{crm_lead_id}}` | Lead ID | | `{{lead_owner}}` | Assigned lead owner name | | `{{account}}` | Buyer account name | | `{{first_name}}` | Buyer first name | | `{{last_name}}` | Buyer last name | | `{{email}}` | Buyer email | | `{{mobile_no}}` | Buyer mobile number | | `{{phone}}` | Buyer phone | | `{{quotation_number}}` | Quotation number | | `{{quotation_url}}` | Tracked link to quotation (auto-replaced) | | `{{lead_source}}` | Lead source name | | `{{lead_status}}` | Lead status name | | `{{title}}` | Lead title | | `{{budget}}` | Buyer budget | | `{{timeline}}` | Project timeline | | `{{description}}` | Lead description | ## Usage Examples ### In Email Template Body ```html

Hello {{first_name}} {{last_name}}

Thank you for your inquiry regarding {{title}}.

Please find your quotation #{{quotation_number}} below.

View Quotation ``` ### Testing Tracking ``` 1. Visit: /test-email-tracking 2. Get test email ID 3. Test tracking URLs: - /track/logo/{id} - /track/click/{id} - /track/open/{id} ``` ## Best Practices ### ✅ Do's - Always pass the full lead/buyer context to avoid redundant queries - Use EmailTrackingService for all tracking operations - Test with real email clients (Gmail, Outlook, etc.) - Monitor tracking pixel loading in production - Check spam folder if tracking shows zero opens ### ❌ Don'ts - Don't hardcode user IDs for testing - Don't leave debug code (dd, var_dump) in production - Don't duplicate tracking logic across multiple services - Don't modify tracking aggregate counters manually - Don't block images in test emails (disables tracking) ## Tracking Reliability ### Why Multiple Tracking Methods? 1. **Logo Tracking** - Most reliable, always loads for visual emails 2. **Footer Tracking** - Backup if header images disabled 3. **Pixel Tracking** - Fallback, but may be blocked Email clients that block tracking: - Outlook with external images disabled - Gmail with image proxy (still works but delayed) - Privacy-focused clients ## Performance Considerations - Tracking routes are cached-disabled to ensure accurate counts - Images served with `no-cache` headers - Tracking events logged asynchronously where possible - Failed tracking doesn't prevent email delivery ## Security - No authentication required on tracking routes (by design) - Email ID is unique and hard to guess - No sensitive data exposed in tracking URLs - IP addresses and user agents logged for analytics ## Monitoring Check tracking effectiveness: ```sql -- Overall open rate SELECT COUNT(*) as total_sent, COUNT(CASE WHEN open_count > 0 THEN 1 END) as opened, ROUND(COUNT(CASE WHEN open_count > 0 THEN 1 END) * 100.0 / COUNT(*), 2) as open_rate FROM auto_quote_emails WHERE sent_at >= DATE_SUB(NOW(), INTERVAL 30 DAY); -- Click-through rate SELECT COUNT(*) as total_sent, COUNT(CASE WHEN click_count > 0 THEN 1 END) as clicked, ROUND(COUNT(CASE WHEN click_count > 0 THEN 1 END) * 100.0 / COUNT(*), 2) as ctr FROM auto_quote_emails WHERE sent_at >= DATE_SUB(NOW(), INTERVAL 30 DAY); ``` ## Troubleshooting ### Email Not Tracking Opens 1. Check if images are enabled in email client 2. Verify tracking pixel exists: `/public/pixel.png` 3. Check if logo files exist in `/public/frontend/images/` 4. Review logs for tracking errors ### Clicks Not Registering 1. Verify `{{quotation_url}}` is in template 2. Check if replacement happens before sending 3. Test tracking URL directly: `/track/click/{id}` 4. Check redirect logic in routes/web.php ### Database Errors 1. Ensure `auto_quote_email_events` table exists 2. Check database connection (using 'crm' connection) 3. Verify foreign key constraints 4. Check column names match schema ## Future Enhancements - [ ] Heatmap showing which links clicked most - [ ] Geolocation tracking based on IP - [ ] Device type analytics (mobile vs desktop) - [ ] Time-to-open analytics - [ ] A/B testing for email templates - [ ] Bounce tracking integration - [ ] Spam complaint monitoring - [ ] Unsubscribe link tracking ## Related Files - `app/Services/AutoQuotation/Helpers/EmailTrigger.php` - Main email sending service - `app/Service/AutoQuote/EmailTrackingService.php` - Tracking event handler - `routes/web.php` (lines 43-200) - Tracking routes - `resources/views/emails/layouts/auto_quotation.blade.php` - Email layout with tracking - `resources/views/emails/auto_quotation_email.blade.php` - Email content wrapper - `app/Models/Crm/Quote/AutoQuoteEmail.php` - Email log model - `app/Models/Crm/Quote/AutoQuoteEmailEvent.php` - Tracking event model --- **Last Updated:** January 15, 2026 **Version:** 2.0 **Status:** Production Ready ✅