Skip to content

Abandonment Recovery Email System - Complete Documentation

System Version: 1.0
Date Implemented: November 2024
Status: Production Ready ✅

Table of Contents

  1. System Overview
  2. Architecture
  3. Database Schema
  4. Edge Functions
  5. Customer.io Integration
  6. Modal Pre-Fill System
  7. Email Templates
  8. Testing Guide
  9. Troubleshooting
  10. Future Enhancements

System Overview

Purpose

Recover abandoned calculator completions by sending personalized follow-up emails with pre-filled checkout experience.

Key Features

  • Two-email sequence: 1-hour and 48-hour follow-ups
  • Personalization: Dog name, feeding plan details, pricing
  • Pre-fill magic: Email, postcode, and address auto-populate in modal
  • £20 discount: Incentive for completion
  • One-time use: Prevents discount abuse

Success Metrics

  • Open rates (target: 40%+)
  • Click-through rates (target: 15%+)
  • Conversion rates (target: 8%+)
  • Revenue recovery per email

Architecture

High-Level Flow

User completes calculator
    ↓
Record saved to calculator_discounts table
    ↓
Hourly cron triggers send-abandonment-events edge function
    ↓
Function queries for candidates (1-2h old, 48-50h old)
    ↓
Events sent to Customer.io (modal_1h, modal_48h)
    ↓
Customer.io sends emails with token URL
    ↓
User clicks email CTA → Product page with token
    ↓
Modal opens → Fields pre-filled from database
    ↓
3 clicks → Checkout with subscription + discount
    ↓
Order completed → Discount marked as used

Components

  1. Database (raw_ops.calculator_discounts)
  2. Stores calculator completions

  3. Tracks email sends and discount usage

  4. Edge Functions

  5. send-abandonment-events: Identifies candidates, sends to Customer.io
  6. calculator-apply-discount: Creates/validates discount codes

  7. shopify-webhook: Marks discounts as used on order completion

  8. Customer.io

  9. Receives events via API
  10. Sends HTML emails

  11. Tracks opens/clicks

  12. Shopify Liquid

  13. Product page personalization
  14. Calculator modal with pre-fill logic

Database Schema

Table: raw_ops.calculator_discounts

CREATE TABLE raw_ops.calculator_discounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    token TEXT UNIQUE NOT NULL,  -- UUID for lookups
    email TEXT NOT NULL,
    dog_name TEXT,
    postcode TEXT NOT NULL,
    address TEXT NOT NULL,
    box_size TEXT NOT NULL,
    daily_grams INTEGER NOT NULL,
    calculated_mer INTEGER NOT NULL,
    delivery_weeks INTEGER NOT NULL,
    discount_code TEXT UNIQUE NOT NULL,  -- Human-readable (CALC20-XXXXX)
    regular_price INTEGER NOT NULL,
    discounted_price INTEGER NOT NULL,
    pet_count INTEGER DEFAULT 1,
    household_daily_grams INTEGER,
    used BOOLEAN DEFAULT FALSE,
    used_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    abandonment_1h_sent_at TIMESTAMPTZ,
    abandonment_48h_sent_at TIMESTAMPTZ
);

-- Indexes for performance
CREATE INDEX idx_calculator_discounts_created_at ON raw_ops.calculator_discounts(created_at);
CREATE INDEX idx_calculator_discounts_email ON raw_ops.calculator_discounts(email);
CREATE INDEX idx_calculator_discounts_token ON raw_ops.calculator_discounts(token);
CREATE INDEX idx_calculator_discounts_used ON raw_ops.calculator_discounts(used);

Key Fields

  • token: UUID for database lookups (e.g., 550e8400-e29b-41d4-a716-446655440000)
  • discount_code: Human-readable code shown to customer (e.g., CALC20-ABC12345)
  • abandonment_*_sent_at: Timestamps prevent duplicate sends

Edge Functions

1. send-abandonment-events

Purpose: Identifies abandonment candidates and sends events to Customer.io

Location: supabase/functions/send-abandonment-events/index.ts

Trigger: Hourly cron (every hour at :00)

Logic:

// Query for candidates using database function
const { data: candidates } = await supabase
  .rpc('fn_get_abandonment_candidates');

// Send events to Customer.io
for (const candidate of candidates) {
  const eventPayload = {
    name: candidate.abandonment_type, // 'modal_1h' or 'modal_48h'
    data: {
      dog_name: candidate.dog_name,
      has_dog_name: !!candidate.dog_name,
      box_size: candidate.box_size,
      daily_grams: candidate.daily_grams,
      daily_calories: candidate.calculated_mer,
      delivery_weeks: candidate.delivery_weeks,
      token: candidate.token,  // UUID, not discount_code!
      discount_code: candidate.discount_code,
      regular_price: candidate.regular_price,
      discounted_price: candidate.discounted_price,
      savings: candidate.regular_price - candidate.discounted_price
    }
  };

  await fetch(`https://track.customer.io/api/v1/customers/${candidate.email}/events`, {
    method: 'POST',
    headers: {
      'Authorization': `Basic ${btoa(SITE_ID:API_KEY)}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(eventPayload)
  });

  // Mark as sent
  await supabase
    .from('calculator_discounts')
    .update({
      [`abandonment_${candidate.abandonment_type.replace('modal_', '')}_sent_at`]: new Date().toISOString()
    })
    .eq('id', candidate.calculator_discount_id);
}

Database Function: fn_get_abandonment_candidates()

CREATE OR REPLACE FUNCTION raw_ops.fn_get_abandonment_candidates()
RETURNS TABLE (
  email TEXT,
  dog_name TEXT,
  box_size TEXT,
  daily_grams INTEGER,
  calculated_mer INTEGER,
  delivery_weeks INTEGER,
  token TEXT,
  discount_code TEXT,
  regular_price INTEGER,
  discounted_price INTEGER,
  hours_since_created NUMERIC,
  abandonment_type TEXT,
  calculator_discount_id UUID
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  -- 1-hour candidates
  SELECT 
    cd.email,
    cd.dog_name,
    cd.box_size,
    cd.daily_grams,
    cd.calculated_mer,
    cd.delivery_weeks,
    cd.token,
    cd.discount_code,
    cd.regular_price,
    cd.discounted_price,
    EXTRACT(EPOCH FROM (NOW() - cd.created_at)) / 3600 AS hours_since_created,
    'modal_1h'::TEXT AS abandonment_type,
    cd.id AS calculator_discount_id
  FROM raw_ops.calculator_discounts cd
  WHERE cd.used = FALSE
    AND cd.abandonment_1h_sent_at IS NULL
    AND cd.created_at >= NOW() - INTERVAL '2 hours'
    AND cd.created_at <= NOW() - INTERVAL '1 hour'

  UNION ALL

  -- 48-hour candidates
  SELECT 
    cd.email,
    cd.dog_name,
    cd.box_size,
    cd.daily_grams,
    cd.calculated_mer,
    cd.delivery_weeks,
    cd.token,
    cd.discount_code,
    cd.regular_price,
    cd.discounted_price,
    EXTRACT(EPOCH FROM (NOW() - cd.created_at)) / 3600 AS hours_since_created,
    'modal_48h'::TEXT AS abandonment_type,
    cd.id AS calculator_discount_id
  FROM raw_ops.calculator_discounts cd
  WHERE cd.used = FALSE
    AND cd.abandonment_48h_sent_at IS NULL
    AND cd.abandonment_1h_sent_at IS NOT NULL
    AND cd.created_at >= NOW() - INTERVAL '50 hours'
    AND cd.created_at <= NOW() - INTERVAL '48 hours';
END;
$$;

Cron Configuration:

-- In Supabase Dashboard → Database → Cron Jobs
SELECT cron.schedule(
  'send-abandonment-events',
  '0 * * * *',  -- Every hour at :00
  $$
  SELECT net.http_post(
    url := 'https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/send-abandonment-events',
    headers := '{"Authorization": "Bearer YOUR_SERVICE_ROLE_KEY"}'::jsonb
  ) AS request_id;
  $$
);

2. shopify-webhook (Updated)

Purpose: Marks discount as used when order completes

Addition to existing function:

// After order is created, check if calculator discount was used
const discountCodes = payload.discount_codes || [];
const calcDiscount = discountCodes.find(d => d.code?.startsWith('CALC20-'));

if (calcDiscount) {
  // Mark discount as used
  const { data: discountData } = await supabase
    .from('calculator_discounts')
    .update({ 
      used: true, 
      used_at: new Date().toISOString() 
    })
    .eq('discount_code', calcDiscount.code)
    .select('email, dog_name, box_size, daily_grams, calculated_mer, discount_code, regular_price, discounted_price')
    .single();

  // Send order_completed event to Customer.io (exits abandonment sequence)
  const eventPayload = {
    name: 'order_completed',
    data: {
      dog_name: discountData.dog_name,
      box_size: discountData.box_size,
      daily_grams: discountData.daily_grams,
      daily_calories: discountData.calculated_mer,
      discount_code: discountData.discount_code,
      order_number: payload.order_number,
      order_total: payload.total_price
    }
  };

  await fetch(`https://track.customer.io/api/v1/customers/${discountData.email}/events`, {
    method: 'POST',
    headers: {
      'Authorization': `Basic ${btoa(CUSTOMERIO_SITE_ID + ':' + CUSTOMERIO_API_KEY)}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify(eventPayload)
  });
}

Environment Variables Required:

CUSTOMERIO_SITE_ID=8633d5bc763716f848d1
CUSTOMERIO_API_KEY=b08777c3150e4cf96485

Customer.io Integration

Credentials

Site ID: 8633d5bc763716f848d1
API Key: b08777c3150e4cf96485
Authentication: Basic Auth (Base64 encoded SITE_ID:API_KEY)

Campaign Setup

Campaign 1: 1-Hour Abandonment

  • Trigger: Event modal_1h
  • Goal: Event order_completed
  • Frequency: Once per person
  • Email: Send HTML template (email1-customerio.html)
  • Exit: When order_completed event received

Campaign 2: 48-Hour Abandonment

  • Trigger: Event modal_48h
  • Goal: Event order_completed
  • Frequency: Once per person
  • Email: Send HTML template (email2-customerio.html)
  • Exit: When order_completed event received

Event Payload Structure

{
  "name": "modal_1h",
  "data": {
    "dog_name": "Luna",
    "has_dog_name": true,
    "box_size": "12kg",
    "daily_grams": 375,
    "daily_calories": 710,
    "delivery_weeks": 4,
    "token": "550e8400-e29b-41d4-a716-446655440000",
    "discount_code": "CALC20-ABC12345",
    "regular_price": 109,
    "discounted_price": 89,
    "savings": 20
  }
}

Customer.io Variable Syntax

{{ event.dog_name }}
{{ event.has_dog_name }}
{{ event.box_size }}
{{ event.daily_grams }}
{{ event.daily_calories }}
{{ event.delivery_weeks }}
{{ event.token }}
{{ event.discount_code }}
{{ event.regular_price }}
{{ event.discounted_price }}
{{ event.savings }}

CRITICAL: Use {{ event.token }} (UUID) in URLs, NOT {{ event.discount_code }}

Product Page URL Structure

From Email CTA:

https://www.protocolraw.co.uk/products/protocol-raw-complete-{{ event.box_size }}-box?token={{ event.token }}&box={{ event.box_size }}&household_grams={{ event.daily_grams }}&weeks={{ event.delivery_weeks }}&dog_name={{ event.dog_name }}

Example:

https://www.protocolraw.co.uk/products/protocol-raw-complete-12kg-box?token=550e8400-e29b-41d4-a716-446655440000&box=12kg&household_grams=375&weeks=4&dog_name=Luna

Pre-Load Logic:

// Get URL params
const urlParams = new URLSearchParams(window.location.search);
const token = urlParams.get('token');

// Pre-load user data if token exists
let userDataLoaded = false;
let preloadedData = null;

if (token) {
  console.log('🔍 Token found, pre-loading user data...');

  fetch(`https://znfjpllsiuyezqlneqzr.supabase.co/rest/v1/calculator_discounts?token=eq.${token}&select=email,address,postcode`, {
    headers: {
      'apikey': 'YOUR_ANON_KEY',
      'Authorization': 'Bearer YOUR_ANON_KEY',
      'Accept-Profile': 'raw_ops'  // CRITICAL: Specify schema
    }
  })
  .then(res => res.json())
  .then(data => {
    if (data && data.length > 0) {
      preloadedData = data[0];
      userDataLoaded = true;
      console.log('✅ User data pre-loaded:', { 
        email: preloadedData.email, 
        postcode: preloadedData.postcode,
        hasAddress: !!preloadedData.address 
      });

      // If modal is already open, fill fields now
      if (modal.style.display === 'block') {
        fillFieldsFromPreloadedData();
      }
    }
  })
  .catch(err => {
    console.error('❌ Error pre-loading token data:', err);
  });
}

Fill Fields Function:

function fillFieldsFromPreloadedData() {
  if (!preloadedData) return;

  console.log('📝 Filling fields with preloaded data');

  // Fill email
  if (preloadedData.email) {
    const emailInput = document.getElementById('calc-email');
    emailInput.value = preloadedData.email;
    document.getElementById('continue-email').disabled = false;
    formData.email = preloadedData.email;
  }

  // Fill postcode
  if (preloadedData.postcode) {
    const postcodeInput = document.getElementById('calc-postcode');
    postcodeInput.value = preloadedData.postcode;
    document.getElementById('continue-postcode').disabled = false;
    formData.postcode = preloadedData.postcode;
  }

  // Fill address
  if (preloadedData.address) {
    const addressInput = document.getElementById('calc-address');
    addressInput.value = preloadedData.address;
    document.getElementById('verify-plan').disabled = false;
    formData.address = preloadedData.address;
  }
}

Modal Open Detection:

// Watch for modal being shown
const observer = new MutationObserver((mutations) => {
  mutations.forEach((mutation) => {
    if (mutation.type === 'attributes' && mutation.attributeName === 'style') {
      if (modal.style.display === 'block' && userDataLoaded && preloadedData) {
        console.log('🎯 Modal opened with preloaded data - filling fields');
        fillFieldsFromPreloadedData();
      }
    }
  });
});

observer.observe(modal, { attributes: true });

Email Templates

Email 1: 1-Hour Follow-Up

Subject: {% if event.has_dog_name %}{{ event.dog_name }}'s{% else %}Your{% endif %} feeding plan

Preheader: {{ event.daily_grams }}g per day. {{ event.daily_calories }} kcal. Your code is ready.

Key Content: - Personalized with dog name - Specific feeding amounts (grams + calories) - Proof-forward messaging (lab testing, QR codes) - Discount code visibility - CTA: "Start Verified Plan - £20 Off"

Design: - Protocol Raw brand colors (Espresso, Burnt Sienna, Cream) - Montserrat Bold for headings - Inter Regular for body - Responsive mobile breakpoints

File: /mnt/user-data/outputs/email1-customerio.html

Email 2: 48-Hour Follow-Up

Subject: A question about {% if event.has_dog_name %}{{ event.dog_name }}'s{% else %}your{% endif %} feeding plan

Preheader: Your discount is still active.

Key Content: - Addresses objections (safety, transition) - UKAS-accredited lab testing - 10-day transition schedule - Discount still active - CTA: "Complete your order"

Tone: Calm, systematic, not pushy

File: /mnt/user-data/outputs/email2-customerio.html

Testing Guide

Manual Testing

1. Create Test Record

INSERT INTO raw_ops.calculator_discounts (
  token, email, dog_name, postcode, address, 
  box_size, daily_grams, calculated_mer, delivery_weeks,
  discount_code, regular_price, discounted_price,
  pet_count, household_daily_grams, used, created_at
) VALUES (
  gen_random_uuid()::text,
  'test@protocolraw.co.uk',
  'Luna',
  'SW1A1AA',
  '10 Downing Street, London',
  '12kg',
  375,
  710,
  4,
  'CALC20-' || UPPER(SUBSTR(md5(random()::text), 1, 8)),
  109,
  89,
  1,
  375,
  false,
  NOW() - INTERVAL '1 hour 30 minutes'  -- For 1h email
)
RETURNING token, discount_code;

2. Trigger Edge Function Manually

curl -X POST \
  https://znfjpllsiuyezqlneqzr.supabase.co/functions/v1/send-abandonment-events \
  -H "Authorization: Bearer YOUR_SERVICE_ROLE_KEY"

3. Check Customer.io

  • Go to People → Search for email
  • View Activity → Should see modal_1h event
  • Check if email campaign triggered

4. Test URL

https://www.protocolraw.co.uk/products/protocol-raw-complete-12kg-box?token=YOUR-UUID-TOKEN&box=12kg&household_grams=375&weeks=4&dog_name=Luna

5. Verify Pre-Fill

  • Open browser console (F12)
  • Look for: ✅ User data pre-loaded:
  • Click "Start Verified Plan"
  • Modal should open with fields pre-filled

6. Complete Flow

  • Click Continue → Continue → Verify Plan
  • Should see success screen with discount code
  • Click "Continue to Checkout"
  • Should redirect with email + discount pre-applied

Automated Testing

Test Scenarios:

  1. No token in URL → Modal works normally (no pre-fill)
  2. Invalid token → Modal works normally (no pre-fill, logs error)
  3. Valid token → Fields pre-fill correctly
  4. Used discount → Shows "Welcome Back!" (no discount)
  5. Order completion → Discount marked as used, order_completed event sent

Troubleshooting

Common Issues

1. "No data found for token"

Cause: Using discount code instead of UUID token in URL

Fix: Ensure email uses {{ event.token }} not {{ event.discount_code }}

Check: 

SELECT token, discount_code 
FROM raw_ops.calculator_discounts 
WHERE discount_code = 'CALC20-ABC12345';

2. "404 Not Found" on Supabase fetch

Cause: Missing Accept-Profile: raw_ops header

Fix: Add header to fetch request: 

headers: {
  'apikey': '...',
  'Authorization': '...',
  'Accept-Profile': 'raw_ops'  // ← Required!
}

3. Modal doesn't progress past email step

Cause: Syntax error in JavaScript (missing comma, bracket, etc.)

Fix: Check browser console for Uncaught SyntaxError

4. Fields not pre-filling

Causes: - Token not in URL - Fetch failing (check Network tab) - Modal opening before data loads (should be fixed by MutationObserver)

Debug: 

console.log('Token:', token);  // Should be UUID
console.log('Preloaded data:', preloadedData);  // Should have email/postcode/address
console.log('User data loaded:', userDataLoaded);  // Should be true

5. Emails not sending

Causes: - Cron not running - Customer.io campaigns not active - Wrong event name

Check: 1. Supabase cron logs 2. Customer.io People → Activity 3. Event name matches campaign trigger exactly

6. Discount not applying at checkout

Causes: - Discount code not created in Shopify - Wrong selling plan (subscription vs one-time)

Check: 

SELECT discount_code, used 
FROM raw_ops.calculator_discounts 
WHERE email = 'test@example.com';

Then check Shopify Admin → Discounts

Future Enhancements

Short-Term (Next 3 Months)

  1. A/B Testing
  2. Subject line variants
  3. Send time optimization

  4. CTA button copy

  5. Enhanced Personalization

  6. Reference specific calculator inputs
  7. Breed-specific messaging

  8. Regional messaging

  9. Third Email

  10. Day 7 final reminder
  11. Social proof / testimonials
  12. Urgency without pressure

Medium-Term (3-6 Months)

  1. SMS Integration
  2. 1-hour SMS reminder

  3. Higher urgency, shorter message

  4. Retargeting Pixels

  5. Facebook/Instagram ads
  6. Google Display ads

  7. Based on abandonment segment

  8. Win-Back Campaigns

  9. Re-engage after 30 days
  10. New offer or messaging angle

Long-Term (6-12 Months)

  1. Machine Learning
  2. Optimal send time prediction
  3. Churn prediction

  4. Personalized discount amounts

  5. Progressive Profiling

  6. Capture additional data over time
  7. Reduce initial friction

  8. Build richer profiles

  9. Multi-Pet Households

  10. Separate abandonment logic
  11. Household-level vs pet-level offers

Key Metrics to Track

Email Performance

  • Open Rate: Target 40%+
  • Click-Through Rate: Target 15%+
  • Conversion Rate: Target 8%+
  • Revenue per Email: Target £25+

System Health

  • Cron Success Rate: 100%
  • Customer.io API Success Rate: 99.9%+
  • Modal Pre-Fill Success Rate: 95%+
  • Checkout Completion Rate: 60%+

Business Impact

  • Total Revenue Recovered: £/month
  • Average Order Value: Recovered vs organic
  • Lifetime Value: Recovered customers vs organic
  • Cost per Acquisition: Including email costs

Contact & Ownership

System Owner: Anton (Founder)
Implementation Date: November 2024
Last Updated: November 2024
Status: Production

Related Documentation: - Calculator System Documentation - Customer.io Campaign Setup Guide - Shopify Discount Code Management - Modal Pre-Fill Technical Spec

Change Log

v1.0 - November 2024

  • Initial implementation
  • Two-email sequence (1h, 48h)
  • Modal pre-fill system
  • Customer.io integration
  • Order completion tracking

End of Documentation