Skip to content

Calculator-to-Purchase System Documentation

Status: Production Ready
Version: 2.6
Last Updated: February 2025
Owner: Protocol Raw

1. Overview

What This System Does

The calculator-to-purchase system converts personalised feeding recommendations into completed subscription orders. Customers use the feeding calculator, arrive at the product page with their verified plan, complete a 4-step verification modal, and proceed to checkout. If they don't complete checkout, the abandonment recovery system automatically follows up with personalised emails.

Core Flow: 

Calculator → Product Page (with plan) → Modal (4 steps) → Checkout → Purchase → Webhook
                                              ↓
                                        [No purchase?]
                                              ↓
                                    Abandonment Recovery Emails

This architecture handles any volume â€â€ from first customer to 100,000+ â€â€ without modification.

Calculator Entry Points

The calculator is available in two locations:

Location URL Use Case
Homepage / (embedded section) Discovery â€â€ visitors browsing the site encounter the calculator naturally
Dedicated Page /pages/calculator Intent â€â€ visitors specifically seeking to calculate their dog's needs

Both entry points use identical calculation logic and lead to the same product page flow.

System Objectives

  1. Convert precision into purchase â€â€ customers who calculate are higher intent
  2. Capture clean retention data â€â€ verified plans enable accurate cohort analysis
  3. Prevent discount fraud â€â€ one discount per household maintains data integrity
  4. Recover abandoned sessions â€â€ personalised follow-up for non-converters
  5. Scale without reengineering â€â€ architecture supports growth from day one

2. Current Launch Configuration

These settings are launch-day choices, not architectural constraints. Each can be adjusted without system changes.

Geographic Scope

Current: London postcodes only (E, EC, N, NW, SE, SW, W, WC, BR, CR, DA, EN, HA, IG, KT, RM, SM, TW, UB)

To expand: Update postcode prefix list in edge function, or remove validation entirely for UK-wide.

Why London first: Ensures operational excellence and concentrated logistics before scaling. Framed to customers as "We're starting in London" (not "London only").

Calculator Discount

Current: £20 off first box for all calculator completions

To adjust: Modify discount amount, make conditional (referral-only, first-time only), or remove entirely.

Why £20: Incentivises calculator usage, captures email/address for remarketing, rewards precision over impulse buying.

Fraud Prevention

Current: Block discounts if address OR email already used

To evolve: Add phone verification, fuzzy address matching, or payment hold if fraud rates increase.

Why address-based: Simple, effective for launch. One discount per household prevents gaming while allowing legitimate flatmate purchases at full price.

3. Calculator Architecture

Two Calculator Variants

We maintain two versions of the calculator widget to avoid content redundancy:

Homepage Calculator (feed-calculator.liquid)

Full, self-contained version for embedding on the homepage.

Includes: - Header: "How Much Should I Feed My Dog?" - Subtitle explaining the methodology - 15kg example anchor (showing sample calculation) - Full calculator inputs (life stage, weight, activity, condition) - Results display with pricing - Treat adjustment selector (single dog only)

Use case: Homepage visitors need context before calculating. The widget must stand alone.

Dedicated Page Calculator (feed-calculator-minimal.liquid)

Stripped version for the dedicated calculator page.

Excludes: - Header (handled by hero section) - Subtitle (handled by hero section) - 15kg example anchor (handled by hero section)

Includes: - Calculator inputs only (life stage, weight, activity, condition) - Results display with pricing - Treat adjustment selector (single dog only)

Use case: The dedicated page has a hero section that provides context. Repeating it in the calculator widget would be redundant.

URL Parameters Generated

Both calculator variants generate identical URL parameters when redirecting to the product page:

Parameter Example Purpose
token 27d1ce68-76d6-... Session identifier for discount lookup
box 12kg Recommended box size
pet_count 1 Number of dogs
household_total 33 Monthly cost estimate
household_grams 275 Total daily grams
weeks 4 Delivery frequency
dog_name Kai Dog's name (single-dog only, for personalisation)
treat_level some Treat adjustment level (none/some/lots)
pets [{name,weight,...}] JSON array of pet details

Dog Name Logic: - Single dog with custom name → dog_name=Kai - Single dog with default name ("Dog 1") → dog_name omitted - Multiple dogs → dog_name omitted (uses generic "your pack" messaging)

Why Two Versions?

HOMEPAGE:
┌─────────────────────────────────────────┐
│  [Other homepage content above]         │
│                                         │
│  ┌───────────────────────────────────┐  │
│  │ How Much Should I Feed My Dog?   │  │  â†Â Widget includes intro
│  │ [Explanation + 15kg example]     │  │
│  │ [Calculator inputs]              │  │
│  │ [Results]                        │  │
│  └───────────────────────────────────┘  │
│                                         │
│  [Other homepage content below]         │
└─────────────────────────────────────────┘

DEDICATED PAGE:
┌─────────────────────────────────────────┐
│  ┌───────────────────────────────────┐  │
│  │ HERO SECTION                     │  │  â†Â Hero handles intro
│  │ "Feed the individual..."         │  │
│  │ [Explanation + 15kg example]     │  │
│  └───────────────────────────────────┘  │
│                                         │
│  ┌───────────────────────────────────┐  │
│  │ CALCULATOR (MINIMAL)             │  │  â†Â Widget is just inputs
│  │ [Calculator inputs]              │  │
│  │ [Results]                        │  │
│  └───────────────────────────────────┘  │
│                                         │
│  ┌───────────────────────────────────┐  │
│  │ CONTENT SECTION                  │  │
│  │ [Energy density education]       │  │
│  │ [Life stages]                    │  │
│  │ [FAQs]                           │  │
│  └───────────────────────────────────┘  │
└─────────────────────────────────────────┘

4. Treat Adjustment Feature

Overview

After calculating their dog's plan, single-dog customers can adjust the feeding recommendation to account for treats. This reduces the daily food portion to leave caloric "room" for treats without overfeeding.

Availability: Single adult/senior dogs only (not puppies, not multipet)

Treat Levels

Level Label Multiplier Use Case
none No treats most days 1.0 (100%) Rarely gives treats or scraps
some Some treats 0.9 (90%) Most dogs â€â€ occasional training treats, small snacks
lots Lots of treats 0.8 (80%) Daily chews or frequent snacks

What Updates When Treat Level Changes

When a user selects a different treat level, the following recalculate in real-time:

  1. Daily grams â€â€ reduces proportionally to treat multiplier
  2. Daily calories â€â€ reduces proportionally to treat multiplier
  3. Human-readable pouch fraction â€â€ updates based on new grams
  4. Days supply â€â€ increases (same box lasts longer with less daily usage)
  5. Weekly price â€â€ decreases (box lasts more weeks)
  6. First delivery weekly price â€â€ decreases proportionally
  7. Alternative box recommendation â€â€ re-evaluated for validity
  8. CTA links â€â€ updated with new treat_level and household_grams parameters

Alternative Box Re-evaluation

When treats reduce the daily grams significantly, the originally recommended alternative box may no longer make sense. The system re-evaluates:

Scenario: 12kg box recommended, 16kg shown as "better value" alternative.

  • With "No treats": 16kg lasts ~5 weeks → valid alternative
  • With "Lots of treats": 16kg would last ~7.5 weeks → exceeds 6-week max
  • Action: Alternative flips from 16kg "value" upsell to 8kg "convenience" option

Logic: 

const altMaxDays = 6 * 7 + 3; // 45 days (6 weeks + buffer)

if (altAdjustedDaysSupply > altMaxDays && basePlanData.alternativeType === 'value') {
  // Flip to smaller box as convenience option
  showSmallerBoxAsConvenience();
} else {
  // Keep original alternative, update pricing
  updateAlternativePricing();
}

Technical Implementation

State Storage: 

let basePlanData = null;      // Stores unadjusted calculation
let currentTreatLevel = 'none';
const TREAT_MULTIPLIERS = { 'none': 1.0, 'some': 0.90, 'lots': 0.80 };

Recalculation Function: recalculateWithTreats(treatLevel) - Retrieves base MER from basePlanData.petData[0].base_mer - Applies multiplier to get adjusted MER - Recalculates grams: Math.round((adjustedMER / FORMULA_ME) / 25) * 25 - Updates all display elements and CTA links

UI/UX Design

Component: .treat-adjustment-section

Layout: - Title: "Do your dog's daily calories include treats?" - Subtitle: "This leaves room in the calorie budget. You can change it anytime." - 3 radio options in horizontal grid (stacks on small phones) - Delta text shows adjustment when some or lots selected

Feedback: - Selection highlights immediately with checkmark - Values animate smoothly (400ms duration) - Delta text fades in: "Adjusted: 10% less food to leave room for treats"

5. Dedicated Calculator Page

Page Structure

URL: /pages/calculator
Template: page.calculator.json

The page consists of three sections loaded via JSON template:

{
  "sections": {
    "calculator-hero": {
      "type": "calculator-page-hero"
    },
    "feed-calculator": {
      "type": "feed-calculator-minimal"
    },
    "calculator-content": {
      "type": "calculator-page-content"
    }
  },
  "order": [
    "calculator-hero",
    "feed-calculator",
    "calculator-content"
  ]
}

Section 1: Hero (calculator-page-hero.liquid)

Purpose: Position the calculator, set expectations, show example pricing.

Content: - Headline: "Feed the individual, not the average." - Paragraph 1: "A 15kg dog sleeping on a sofa needs very different fuel than a 15kg dog running in a field." - Callout: "Generic body-weight percentages ignore this." - Paragraph 2: MER standard explanation - 15kg example anchor (4 cards: daily feed, box size, weekly cost, first box savings) - CTA: "Calculate Your Dog's Plan ↓"

Section 2: Calculator (feed-calculator-minimal.liquid)

Purpose: Collect dog details and display personalised results.

Content: - Life stage selector (Puppy / Adult / Senior) - Multipet option (up to 3 dogs) - Weight slider with manual input - Neutered/intact toggle - Activity level selector - Body condition selector (with SVG illustrations) - Calculate button - Results display (daily grams, calories, pouch equivalent) - Treat adjustment selector (single dog only) - Pricing breakdown - CTA to product page with token

Section 3: Content (calculator-page-content.liquid)

Purpose: Educate on energy density, reinforce single-SKU positioning, answer FAQs.

Energy Density Section (Split Layout): - Left: "Why We Count Calories, Not Just Grams" + explanation - Right: Visual comparison (500g ≠ 500g â€â€ chicken vs beef calorie difference) - Tagline: "We don't just weigh the food. We engineer the fuel."

Life Stages Section: - Header: "Every Life Stage. One Formula." - 3 cards: Puppies (growth), Adults (maintenance), Seniors (precision)

FAQ Section: - Expandable accordion format - Why different from other calculators, weighing food, multiple dogs, recalculation frequency, all life stages suitability

6. User Flow

Product Page (Entry from Calculator)

When customers arrive from calculator with URL params (?token=xxx&box=12kg&daily_grams=375&weeks=4&dog_name=Kai):

Calculator Banner Appears: - Title: "Your Verified Plan" - Shows: Box size, daily feeding, delivery frequency - Footer: "Complete nutrition, verified safe, £20 off your first box"

Custom Pricing: - Discounted price shown (based on current discount config) - Regular price crossed out - Message: "Calculator discount applied"

Custom Button: - "Claim Your £20 Off" (replaces default add-to-cart)

  • Title: "Lock in your plan"
  • Subtitle: "We'll send your transition guide after your first order"
  • Input: Email field (validated)
  • Button: "Continue"
  • Progress: ● ○ ○
  • Title: "Delivery postcode"
  • Subtitle: "We're starting in London to ensure quality at launch"
  • Input: Postcode (auto-uppercase)
  • Button: "Continue"
  • Progress: ● ● ○

Validation: UK format + geographic scope check (currently London prefixes)

  • Title: "Delivery address"
  • Subtitle: "One verified plan per address"
  • Input: Address field with autocomplete (getAddress.io API)
  • Button: "Verify Plan"
  • Progress: ● ● ●

Success Screen (New Customer)

  • Checkmark animation
  • Title: "Plan Secured"
  • Discount code displayed
  • Plan details (box size, daily feeding, delivery frequency, pricing)
  • Button: "Continue to Secure Checkout"
  • Note: "Your batch will be independently tested before shipping"

Success Screen (Existing Customer)

  • Title: "Welcome Back!"
  • No discount code (full price checkout)
  • Plan details
  • Button: "Continue to Secure Checkout"

7. Technical Architecture

System Components

┌─────────────────────────────────────────────────────────────────┐
│ 1. CALCULATOR (Two Entry Points)                                │
│                                                                 │
│    HOMEPAGE                    DEDICATED PAGE                   │
│    /                           /pages/calculator                │
│    feed-calculator.liquid      calculator-page-hero.liquid      │
│    (full version)              feed-calculator-minimal.liquid   │
│                                calculator-page-content.liquid   │
│                                                                 │
│    Both generate: token, box size, daily_grams, weeks, dog_name │
│    Both redirect to: /products/product?token=xxx&...            │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│ 2. PRODUCT PAGE                                                 │
│    - Reads URL params (including dog_name)                      │
│    - Shows calculator banner + custom pricing                   │
│    - "Claim Your £20 Off" button triggers modal                 │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│ 3. MODAL (3-STEP FLOW)                                          │
│    Step 1: Email                                                │
│    Step 2: Postcode (geographic validation)                     │
│    Step 3: Address (autocomplete + fraud check)                 │
│    → Sends dog_name to Edge Function                            │
└─────────────────────────────────────────────────────────────────┘
                              │
                              ▼
┌─────────────────────────────────────────────────────────────────┐
│ 4. EDGE FUNCTION: calculator-apply-discount                     │
│    - Validates geographic scope                                 │
│    - Checks fraud (email + address)                             │
│    - Generates Shopify discount code                            │
│    - Saves to calculator_discounts (including dog_name)         │
└─────────────────────────────────────────────────────────────────┘
                              │
              ┌───────────────┴───────────────┐
              ▼                               ▼
┌─────────────────────────┐     ┌─────────────────────────────────┐
│ 5a. SUCCESS → CHECKOUT  │     │ 5b. NO PURCHASE (ABANDONMENT)   │
│  - Shows plan + code    │     │  - Record sits with used=false  │
│  - Adds to cart         │     │  - Cron job detects after 1hr   │
│  - Redirects to checkout│     │  - Sends personalised email     │
└─────────────────────────┘     └─────────────────────────────────┘
              │                               │
              ▼                               ▼
┌─────────────────────────┐     ┌─────────────────────────────────┐
│ 6a. WEBHOOK: Order      │     │ 6b. RECOVERY EMAIL              │
│  - Marks used=true      │     │  - "Kai's plan is waiting"      │
│  - Records used_at      │     │  - Links back to checkout       │
│  - Triggers downstream  │     │  - Second email at 48hrs        │
└─────────────────────────┘     └─────────────────────────────────┘

Database Schema

Table: raw_ops.calculator_discounts

Column Type Purpose
id uuid Primary key
token text Calculator session token
email text Normalised lowercase
postcode text Uppercase, no spaces
address text Normalised for matching
box_size text '8kg', '12kg', '16kg'
daily_grams integer Calculated daily portion
delivery_weeks integer Subscription frequency
discount_code text 'CALC20-xxxxx' format
regular_price integer Pre-discount price
discounted_price integer Post-discount price
dog_name text Dog's name for personalisation (NULL for multipet)
pet_count integer Number of dogs in household
household_daily_grams integer Total daily grams for all dogs
household_total_gbp integer Monthly cost for household
used boolean Flips true on purchase
used_at timestamp When order completed
abandonment_sent_at timestamp When recovery email sent (NULL = not sent)
created_at timestamp When discount generated
expires_at timestamp Discount expiry
fraud_flag boolean Flagged for fraud
fraud_reason text Why flagged

Indexes: - idx_calculator_discounts_email â€â€ fraud checking - idx_calculator_discounts_address â€â€ fraud checking - idx_calculator_discounts_postcode â€â€ geographic analytics - idx_calculator_discounts_token â€â€ token lookup for recovery URLs - idx_calculator_discounts_abandonment_queue â€â€ abandonment recovery queries

Fraud Prevention Logic

1. Email already purchased (used = true)?
   → Return "Welcome back" flow, no discount

2. Email has unused discount (used = false)?
   → Return existing discount code

3. Address already in system?
   → Block: "This delivery address has already been used"

4. All checks pass?
   → Generate new discount, save to database

Edge Functions

Function Purpose
calculator-generate-token-multipet Generates secure token for calculator results
calculator-apply-discount Validates and generates discount codes, saves dog_name
send-abandonment-events Sends recovery events to Customer.io (hourly cron)
shopify-webhook-handler Marks discounts as used on purchase

Database Functions

Function Purpose
fn_get_abandonment_candidates() Returns sessions ready for recovery email
fn_mark_abandonment_sent(p_ids) Marks sessions as emailed to prevent duplicates

8. Abandonment Recovery System

Overview

Customers who complete the modal but don't purchase receive automated recovery emails with personalised messaging using their dog's name.

Timing Windows

Email Trigger Content
Email 1 1-2 hours after modal completion "Kai's plan is ready"
Email 2 48-50 hours after modal completion "Still thinking about Kai's plan?"

Personalisation Logic

Scenario dog_name value Email subject
Single dog, named "Kai" "Kai's plan is ready"
Single dog, unnamed NULL "Your plan is ready"
Multi-dog household NULL "Your pack's plan is ready"

Customer.io Event Payload

When send-abandonment-events runs, it sends:

{
  "name": "modal_1h",
  "data": {
    "dog_name": "Kai",
    "has_dog_name": true,
    "box_size": "12kg",
    "daily_grams": 275,
    "delivery_weeks": 4,
    "discount_code": "CALC20-ABCD1234",
    "regular_price": 109,
    "discounted_price": 89,
    "savings": 20,
    "hours_since_created": 1
  }
}

Cron Configuration

Schedule: Every hour (0 * * * *) Function: send-abandonment-events Batch Size: 100 records per run

Duplicate Prevention

  1. fn_get_abandonment_candidates() filters by abandonment_sent_at IS NULL
  2. After successful send, fn_mark_abandonment_sent() sets timestamp
  3. Time-window approach (1h, 48h) ensures each email sends once

9. Design System

Typography

  • Headings: Montserrat (bold, tight tracking)
  • Body: Inter (readable, professional)
  • Monospace: For discount codes, postcodes

Colour System

  • Primary action: #B85C3A (Burnt Sienna)
  • Success/Trust: #2D5144 (Forest Green)
  • Text primary: #2B2523 (Espresso)
  • Text secondary: #6B6360 (Taupe)
  • Background: #FEFDF7 (Cream), #EBE8E3 (Warm Linen)

Animation Principles

  • Fast enough to feel responsive
  • Slow enough to feel premium
  • Scroll-triggered where appropriate
  • Never blocks user action

Mobile Optimisation

  • Sticky calculate button
  • Single-column card stacking
  • 44px minimum touch targets
  • Full-width inputs
  • Safe area inset support

10. Monitoring & Analytics

Key Metrics

Metric Target Red Flag
Calculator → Order conversion 65%+ <50%
Abandonment recovery rate 15-25% <10%
Box 2 retention 70%+ <60%
Box 3 retention (of Box 2) 80%+ <70%
Fraud attempts <5% >10%

Dashboard Views

  • Calculator Funnel: Discounts → Orders → Box 2 → Box 3
  • Abandonment Recovery: Sent → Recovered → Revenue impact
  • Box Size Retention: Which size has best retention?
  • Weekly Cohorts: Is retention improving over time?
  • Geographic Distribution: Where are customers?
  • Entry Point Analysis: Homepage vs dedicated page conversion
  • Treat Level Distribution: What % select each treat level?

Quick Queries

-- Retention check
SELECT total_orders_placed, box_2_retention_pct
FROM raw_ops.v_phase_a_kpis;

-- Box size comparison
SELECT * FROM raw_ops.v_box_size_retention;

-- Weekly cohorts
SELECT * FROM raw_ops.v_weekly_cohorts 
ORDER BY cohort_week DESC LIMIT 8;

-- Abandonment recovery stats
SELECT * FROM raw_ops.v_abandonment_stats;

-- Abandonment queue health
SELECT * FROM raw_ops.check_abandonment_queue_health();

11. Operations & Troubleshooting

Common Issues

"I already used this discount" → They started but didn't purchase. System returns their existing unused code. This is correct.

Flatmates can't both get discount → Intentional. Second person pays full price, or support manually issues code if justified.

"Address already used" but claims first time → Someone else at address used it. Check database. Manual code for legitimate multi-unit buildings.

Outer area postcode rejected → Not in current geographic scope. Manually issue if legitimate delivery area, consider expanding prefix list.

Recovery emails not sending → Check cron job status, edge function logs, Customer.io event activity. See troubleshooting section below.

Abandonment Recovery Troubleshooting

Check 1: Is cron job running? 

SELECT * FROM cron.job WHERE jobname LIKE '%abandonment%';

Check 2: Are there candidates to process? 

SELECT * FROM raw_ops.fn_get_abandonment_candidates();

Check 3: Queue health status 

SELECT * FROM raw_ops.check_abandonment_queue_health();

Check 4: Edge function logs → Supabase Dashboard → Edge Functions → send-abandonment-events → Logs

Check 5: Customer.io receiving events? → Customer.io → Activity Logs → Search for modal_1h or modal_48h

Manual Discount Codes

When to issue: - Legitimate edge case outside current scope - Customer support exception - System error recovery

How to issue: 1. Shopify → Discounts 2. Create: SUPPORT-[name]-[date] 3. Set: £20 off, subscription only, single use 4. Log reason 5. Send to customer

Never use CALC20- prefix manually (reserved for system).

12. File Locations

Shopify Templates

File Purpose
templates/page.calculator.json Dedicated calculator page structure

Shopify Sections

File Purpose
sections/feed-calculator.liquid Full calculator (homepage)
sections/feed-calculator-minimal.liquid Stripped calculator (dedicated page)
sections/calculator-page-hero.liquid Dedicated page hero
sections/calculator-page-content.liquid Education + FAQs
sections/main-product.liquid Product page

Shopify Snippets

File Purpose
snippets/calculator-checkout-modal.liquid 4-step verification modal

Supabase Edge Functions

Function Purpose
calculator-generate-token-multipet Token generation
calculator-apply-discount Discount validation + creation
send-abandonment-events Abandonment recovery (hourly cron)
shopify-webhook-handler Order completion processing

Supabase Database

Resource Purpose
raw_ops.calculator_discounts Core data table
raw_ops.fn_get_abandonment_candidates() Find sessions for recovery
raw_ops.fn_mark_abandonment_sent() Mark as emailed
raw_ops.v_abandonment_stats Recovery metrics view
raw_ops.check_abandonment_queue_health() Monitoring function

Cron Jobs

Job Schedule Purpose
send-abandonment-events-hourly 0 * * * * Trigger recovery emails

Customer.io

Resource Purpose
Event: modal_1h 1-hour abandonment trigger
Event: modal_48h 48-hour abandonment trigger
Campaign: "Calculator Recovery" Email sequence

Metabase

Resource Purpose
Dashboard: "Retention Analysis" Core KPIs
Dashboard: "Abandonment Recovery" Recovery metrics
Collection: Protocol Raw Analytics All queries

Version History: - v2.2 (Jan 2025): Restored missing sections (User Flow, Technical Architecture, full Abandonment Recovery) from v2.0; merged with v2.1 treat adjustment feature - v2.1 (Jan 2025): Added treat adjustment feature with dynamic alternative box re-evaluation - v2.0 (Nov 2024): Added dog name capture, abandonment recovery system - v1.0 (Nov 2024): Initial documentation

Owner: Protocol Raw Operations

Version 2.6 Changes — Previous Diet Capture

Overview

Version 2.6 adds a 4th step to the checkout modal to capture what the customer's dog currently eats. This enables tracking of "% new to raw" — a key metric for validating the thesis that Protocol Raw converts raw-curious premium buyers rather than existing raw believers.

The modal is now 4 steps (was 3):

Step Title Purpose
1 Email Capture email, check existing customer
2 Postcode Geographic validation
3 Address Fraud prevention
4 Current Diet (NEW) Capture previous feeding history

Step 4 UI

  • Title: "One last thing"
  • Subtitle: "This helps us tailor your transition guidance"
  • Question: "What does [dog_name] currently eat?"
  • Options:
  • Kibble or dry food → kibble
  • Fresh / cooked food → fresh_cooked
  • Raw food → raw
  • Mixed / not sure → mixed_unsure
  • Button: "Verify Plan"

Database Schema Changes

Table: calculator_discounts 

ALTER TABLE raw_ops.calculator_discounts 
ADD COLUMN previous_diet TEXT;
-- Values: 'kibble', 'fresh_cooked', 'raw', 'mixed_unsure'

Table: customers 

ALTER TABLE raw_ops.customers 
ADD COLUMN acquisition_diet TEXT;
-- Immutable after first order. Copied from calculator_discounts.previous_diet

Data Flow

Modal Step 4 (user selects diet)
       ↓
calculator-apply-discount Edge Function
       ↓
calculator_discounts.previous_diet (stored)
       ↓
[Customer completes order]
       ↓
shopify-webhook-handler Edge Function
       ↓
customers.acquisition_diet (copied, immutable)
       +
Customer.io identify (profile attributes set)

Customer.io Integration

On first order, two profile attributes are set:

Attribute Type Description
acquisition_diet string Raw value: kibble, fresh_cooked, raw, mixed_unsure
is_new_to_raw boolean true if kibble or fresh_cooked

Segmentation Value

  • % New to Raw = kibble + fresh_cooked customers
  • % Converting from competitor = raw customers
  • % DIY converting = (if we add home-prepared option later)

Analytics Queries

Acquisition composition: 

SELECT 
  acquisition_diet,
  COUNT(*) as customers,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) as pct
FROM raw_ops.customers
WHERE acquisition_diet IS NOT NULL
GROUP BY acquisition_diet
ORDER BY customers DESC;

% New to raw: 

SELECT 
  ROUND(100.0 * COUNT(*) FILTER (WHERE acquisition_diet IN ('kibble', 'fresh_cooked')) / COUNT(*), 1) as pct_new_to_raw
FROM raw_ops.customers
WHERE acquisition_diet IS NOT NULL;

Files Changed

File Change
snippets/calculator-checkout-modal.liquid Added Step 4 HTML, CSS, JS
calculator-apply-discount/index.ts Accept and store previous_diet
shopify-webhook-handler/index.ts Copy to acquisition_diet, sync to Customer.io
Database Added columns to calculator_discounts and customers

Version History: - v2.6 (Feb 2025): Added Step 4 (current diet) for % new-to-raw tracking - v2.2 (Jan 2025): Restored missing sections from v2.0; merged with v2.1 treat adjustment feature - v2.1 (Jan 2025): Added treat adjustment feature with dynamic alternative box re-evaluation - v2.0 (Nov 2024): Added dog name capture, abandonment recovery system - v1.0 (Nov 2024): Initial documentation