Skip to content

Import Mandrill Stats

Menu Location: Tools > Import Mandrill Stats (Kiva Admin only) Access Level: Kiva Admin Last Updated: 2026-03-03

Overview

The Import Mandrill Stats tool allows Kiva administrators to import email open and click tracking data from Mandrill CSV exports into the KlCode database. This tool matches Mandrill activity records to existing email records in both the system_emails table (general email tracking) and drip_campaign_data table (drip campaign tracking), updating the opens and clicks counts.

Primary Functions: - Upload Mandrill activity CSV exports - Match CSV rows to existing email records using email address, subject, and timestamp - Preview all changes in a dry run before applying - Update opens and clicks counts for both system emails and drip campaigns - Report on match success rates and no-match records

Page Layout

Initial Upload View

  • File Upload Form: Simple file input for CSV upload with validation
  • Format Information: Expected CSV column headers listed for reference
  • Upload Button: Triggers CSV upload and dry run analysis

Analysis/Results View

  • Status Badge: Shows "DRY RUN" (yellow) or "COMMITTED" (green) at page top
  • CSV Summary Panel: Total rows processed, rows with stats, sender breakdown
  • System Emails Panel: Match stats (matched, updated, already correct)
  • Drip Emails Panel: Match stats (matched, updated, already correct)
  • No Match Panel: Count of unmatched rows (yellow if > 0)
  • Updates Log Panel: Detailed list of all changes to be applied (or applied)
  • No Match Details Panel: Sample of unmatched records with details
  • Action Buttons: Commit Changes (red), Start Over (gray), or Upload Another (after commit)

CSV Import Process

Step 1: Export from Mandrill

Required CSV Columns: - Message ID - Date - Recipient - Sender - Subject - Status - Channel - Tags - Subaccount - Opens - Clicks - Bounce Detail

The tool validates these columns on upload and will reject files with incorrect format.

Step 2: Upload & Dry Run

Purpose: Upload the CSV file and analyze matches without making any database changes.

Steps: 1. Click "Choose File" and select the Mandrill CSV export 2. Click "Upload & Analyze (Dry Run)" 3. The tool stores the file temporarily in kiv/csv-uploads/mandrill-import-temp.csv 4. Analysis runs immediately showing all potential updates

What the Dry Run Shows: - Total records in CSV and how many have opens/clicks data - Breakdown of senders in the file - How many system_emails records will be matched and updated - How many drip_campaign_data records will be matched and updated - Records that already have correct values (no update needed) - Records that couldn't be matched to database - Detailed preview of every planned update

Step 3: Review & Commit

Purpose: Review the dry run results and apply the updates to the database.

Steps: 1. Review the match statistics and update log 2. Check the "No Match Details" section to understand unmatched records 3. Click "Commit Changes" button (requires confirmation) 4. Updates are applied to the database 5. Temporary CSV file is automatically deleted

After Commit: - Status changes to "COMMITTED" (green badge) - Same statistics shown but now reflect applied changes - Can upload another CSV file to start over

Matching Logic

Time Window Matching

The tool uses a 5-minute time window (±300 seconds) when matching CSV records to database records. This accounts for minor timing differences between when Mandrill records the send time and when KlCode records it.

Match Criteria for system_emails: 1. Email address matches exactly (case-insensitive) 2. Subject line matches exactly 3. Date sent is within ±5 minutes of CSV timestamp

Match Criteria for drip_campaign_data: 1. Email address matches exactly (case-insensitive) 2. Email template subject matches exactly 3. Send timestamp is within ±5 minutes of CSV timestamp

Dual Matching

The tool attempts to match both system_emails AND drip_campaign_data for every CSV row. A single email send may update both tables if it matches both.

Update Behavior

For both tables, the tool uses max() logic: - If CSV shows 3 opens and database has 2, update to 3 - If CSV shows 5 clicks and database has 7, keep 7 - If values already match, no update is performed

This prevents overwriting higher counts with lower counts from partial CSV exports.

Common Use Cases

Use Case 1: Import Last Month's Email Stats

Goal: Update all email tracking data with Mandrill's recorded opens/clicks from last month.

Steps: 1. Log into Mandrill dashboard 2. Navigate to Outbound > Activity 3. Filter by date range (e.g., last month) 4. Click "Export" and download CSV 5. In KlCode admin, navigate to Tools > Import Mandrill Stats 6. Upload the exported CSV file 7. Review dry run results to verify reasonable match rates 8. Click "Commit Changes" to apply updates

Expected Results: - High match rate (80%+ for recent sends) - Updates to both system_emails and drip campaign tracking - Some no-matches are normal (deleted customers, test sends, etc.)

Use Case 2: Backfill Historical Data

Goal: Import historical email engagement data from Mandrill to populate previously empty opens/clicks columns.

Steps: 1. Export a large date range from Mandrill (e.g., last 3 months) 2. Upload to Import Mandrill Stats tool 3. Review the "already correct" counts - these are records that already had data 4. Review update counts - these are records being backfilled 5. Commit changes to apply backfill

Tips: - Larger date ranges may take 30-60 seconds to process - The max() logic ensures you won't overwrite newer data with older data - Run recent date ranges more frequently, older ranges less frequently

Use Case 3: Verify Data After Mandrill Migration

Goal: After changing Mandrill settings or subaccounts, verify all sends are being tracked properly.

Steps: 1. Export last week's activity from Mandrill 2. Upload to tool and review dry run 3. Check "No Match Details" for patterns (are certain senders not matching?) 4. Investigate any systemic match failures (may indicate configuration issue) 5. Commit if match rates look good, or investigate before committing

Warning Signs: - Very low match rate (< 50%) may indicate a problem - All rows from one sender not matching could indicate database issue - High "BAD DATE" errors indicate CSV formatting problems

Troubleshooting

Low Match Rate (< 50%)

Symptoms: Many CSV rows showing up in "No Match Details" section, low counts in "Matched" statistics.

Solutions: 1. Check the date range of the CSV export - very old sends may have been deleted from database 2. Verify the CSV is from the correct Mandrill account/subaccount for this tenant 3. Check if customers were bulk-deleted recently (orphans their email records) 4. Look for patterns in senders - one sender not matching may indicate configuration issue

Common Causes: - Exported data from wrong Mandrill account - Exported data too old (customer records deleted) - Exported test sends that were never recorded in database

"BAD DATE" Errors in No Match Log

Symptoms: No Match Details section shows rows prefixed with "BAD DATE"

Solutions: 1. Check CSV formatting - ensure Date column is in standard format 2. Re-export from Mandrill with default date format settings 3. Open CSV in text editor to verify date format is readable

Common Causes: - CSV opened/saved in Excel which reformatted dates - Corrupted CSV file - Non-standard Mandrill export settings

All Updates Show "Already Correct"

Symptoms: High match rate but zero updates applied, all showing "already correct"

Check: 1. Verify this CSV hasn't already been imported 2. Check if running same export multiple times 3. Confirm the date range of the CSV export is new data

If Problem Persists: This is actually normal behavior - it means the data was already imported previously or Mandrill's counts match the database. No action needed.

File Upload Rejected

Symptoms: Upload fails with "Invalid CSV format" error message

Solutions: 1. Verify CSV has all 12 required columns 2. Check that first column header is exactly "Message ID" 3. Ensure file is actually CSV format (not Excel .xlsx) 4. Try re-exporting from Mandrill

Common Causes: - Uploaded wrong file type - CSV missing columns - Column headers modified after export

  • Email Stats (email-stats.php) - View aggregated email performance metrics that use this opens/clicks data
  • Emails Sent (emails-sent.php) - Browse individual email sends and their opens/clicks (populated by this tool)
  • Email Templates (email-templates.php) - Manage the email templates used in sends being tracked

Typical Workflow: 1. Export from Mandrill → Import Mandrill Stats tool → Commit updates 2. Import Mandrill Stats → Email Stats page to view updated metrics

Permissions & Access

Required Access Level: Kiva Admin

This tool is restricted to Kiva administrators only because: - It directly modifies database tracking data - Incorrect imports could corrupt historical data - Requires understanding of Mandrill configuration and multi-tenant architecture

Access Level Capabilities: - Kiva Admin: Full access to upload, dry run, and commit imports - Administrator: No access (tool not visible in menu) - Manager: No access - Customer Service: No access

Best Practices

Import Frequency

  1. Import monthly for regular maintenance and data hygiene
  2. Import weekly if email engagement metrics are critical to business decisions
  3. Import immediately after Mandrill configuration changes to verify tracking

Data Verification

  1. Always review dry run results before committing
  2. Verify match rates are reasonable (70%+ for recent data is typical)
  3. Spot-check a few "No Match" records to understand why they didn't match
  4. Review update log to ensure changes look expected

File Management

  • Download CSVs directly from Mandrill without opening in Excel
  • Use descriptive date ranges when exporting from Mandrill
  • Don't modify CSV files after export (can corrupt formatting)

Things to Avoid

  • ❌ Don't commit without reviewing dry run results first
  • ❌ Don't import the same CSV file multiple times (wastes time, but won't corrupt data due to max() logic)
  • ❌ Don't modify CSV files in Excel before uploading (Excel changes date formats)
  • ❌ Don't panic about no-matches - some are expected (test sends, deleted customers, etc.)

Technical Details

Database Tables Updated

system_emails: - opens column: Count of times email was opened - clicks column: Count of times links in email were clicked

drip_campaign_data: - opens column: Count of times drip email was opened - clicks column: Count of times links were clicked

Temporary File Storage

Uploaded CSVs are stored temporarily at: 

kiv/csv-uploads/mandrill-import-temp.csv

This file is automatically deleted after commit or when clicking "Start Over".

Processing Time

  • Small files (< 1,000 rows): 5-10 seconds
  • Medium files (1,000-10,000 rows): 15-30 seconds
  • Large files (> 10,000 rows): 30-60 seconds

Quick Reference Card

Task Action/Location
Upload new CSV Choose file → Upload & Analyze button
Review matches Check System Emails and Drip Emails panel statistics
See what will change Scroll to "Updates" panel in dry run
Check unmatched records Scroll to "No Match Details" panel
Apply updates Click "Commit Changes" button (requires confirmation)
Start over with new file Click "Start Over" button (deletes temp CSV)
Import another after commit Click "Upload Another" button
Export from Mandrill Mandrill dashboard → Outbound → Activity → Export

FAQs

Why do some records show as "already correct"?

The tool uses max() logic to prevent overwriting higher counts with lower counts. If the database already has the same or higher open/click counts than the CSV, no update is needed. This commonly happens when importing the same date range multiple times.

Can I import the same CSV twice?

Yes, but it's unnecessary. The second import will show all records as "already correct" since the data was already applied. The max() logic prevents data corruption from duplicate imports.

What happens to opens/clicks that occurred after the CSV export?

They are preserved. The tool only updates if the CSV value is higher than the database value. So if the database has 10 opens and CSV has 8, it keeps 10.

Why aren't all my emails matching?

Common reasons: (1) CSV is from a different Mandrill account than this tenant uses, (2) Email records were deleted when customers were deleted, (3) Test sends that were never recorded in database, (4) Very old sends outside normal retention, (5) Timing issues if system clocks are significantly off.

How far back can I import data?

Technically unlimited, but match rates drop for older data because customer and email records get deleted over time. Recent data (< 3 months) typically has 80%+ match rates. Data older than 1 year may have 30-50% match rates.

Change Log

2026-03-03

  • Bug fix: Fixed timezone handling for Mandrill CSV dates - dates are now correctly interpreted as UTC instead of local server timezone. This resolves issues where timestamps would fall outside the 5-minute matching window due to timezone offset (r26199)
  • Initial documentation created
  • All sections completed
  • Tool added in revision r26196

End of Documentation

For additional help, contact Kiva Logic support.