Skip to content

Auto Preferences Feature

Overview

The Auto Preferences feature automatically sets customer preferences to "NO" for items they have repeatedly substituted out from their orders. This helps streamline the customer experience by proactively managing their preferences based on their actual behavior.

How It Works

Detection Logic

  1. Trigger: The system runs before starting a new week
  2. Analysis: For each customer with orders in the current week who made substitutions:
  3. Identifies items that were substituted (where cust_menu_id != menu_id)
  4. Checks if those same items were substituted in any previous orders

  5. If an item was subbed before, it automatically sets that product classification to "NO"

  6. Preference Update: Creates or updates a record in custy_likes table with pref = 0

  7. Customer Notification:

  8. Sends an email notification listing all items set to "NO"
  9. Creates a popup notification for their next login

Database Schema

New Table: custy_preference_notifications

CREATE TABLE `custy_preference_notifications` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `customer_id` INT(11) NOT NULL,
    `notification_type` VARCHAR(64) NOT NULL,
    `notification_data` TEXT NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `shown_at` TIMESTAMP NULL DEFAULT NULL,
    `dismissed_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_customer_shown` (`customer_id`, `shown_at`),
    KEY `idx_customer_type` (`customer_id`, `notification_type`)
)

New Email Template: Auto Preferences Updated

  • Subject: "We updated your preferences based on your substitution history"
  • Contains placeholder: **auto_preference_list** for items list
  • Uses standard template variables: **first_name**

Files Created/Modified

New Files

  1. /kiv/helpers/helper.autoPreferences.php
  2. Core automation logic

  3. Functions:

    • processAutoPreferences($dryRun, $specificCustomerId) - Main processor
    • processCustomerAutoPreferences($customerId, $currentWeekId, $dryRun, &$stats) - Per-customer logic
    • sendAutoPreferenceEmail($customerId, $preferencesUpdated) - Email notification
    • createAutoPreferenceNotification($customerId, $preferencesUpdated) - Popup notification
    • getCustomerPreferenceNotifications($customerId) - Get unshown notifications
    • markNotificationShown($notificationId) - Mark as shown
    • markNotificationDismissed($notificationId) - Mark as dismissed

  4. /home/auto-preference-notification.php

  5. Modal/popup display for customer portal
  6. Shows list of items set to "NO"
  7. Provides links to update preferences

  8. Auto-dismisses when clicked outside or on escape key

  9. /home/dismiss-auto-pref-notification.php

  10. AJAX endpoint for dismissing notifications
  11. Validates customer ownership
  12. Marks notification as dismissed

Modified Files

  1. /kiv/migrate.php
  2. Added case 821: Create custy_preference_notifications table

  3. Added case 822: Insert email template

  4. /kiv/tools.php

  5. Added processAutoPreferencesRun to the $actions array at the top

  6. /inc/helper.tools.php

  7. Added processAutoPreferencesRun() function
  8. Provides browser-based wrapper for processAutoPreferences

  9. Supports query parameters: dry_run and customer

  10. /home/welcome.php

  11. Added include for auto-preference-notification.php
  12. Displays popup on customer login if notifications exist

Usage

Running the Automation

Before starting a new week in the system, access the tool from your browser:

  1. Go to /kiv/tools.php?processAutoPreferencesRun=1
  2. Use the quick links to run in different modes:
  3. Dry Run mode: ?processAutoPreferencesRun=1&dry_run=1 (test without making changes)
  4. Live mode: ?processAutoPreferencesRun=1 (actually process and send emails)
  5. Single customer: ?processAutoPreferencesRun=1&dry_run=1&customer=12345

The tool will display a formatted HTML page with: - Status banner (DRY RUN vs LIVE) - Full output in a formatted code block - Quick navigation links

Output Example

=== Auto Preferences Processor ===
*** DRY RUN MODE - No changes will be made ***

Processing for week ID: 456

Found 15 customers with substitutions this week

Processing customer ID: 12345
  Item 'Organic Bananas' (classify_id: 78) subbed 2 time(s) before
    [DRY RUN] Would set to NO
  Item 'Kale' (classify_id: 45) subbed 1 time(s) before
    [DRY RUN] Would set to NO

Processing customer ID: 67890
  Item 'Broccoli' (classify_id: 89) subbed 3 time(s) before
    [DRY RUN] Would set to NO

=== SUMMARY ===
Customers processed: 15
Customers with changes: 2
Total preferences set to NO: 3
Emails sent: 0

Customer Experience

  1. Email Notification: Customer receives email with list of items set to "NO"
  2. Login Popup: Next time they log in, they see a modal popup with:
  3. Explanation of why preferences were updated
  4. List of items set to "NO"
  5. Link to preferences page

  6. "Got It" button to dismiss

  7. Preferences Page: Customer can visit their preferences page (/home/love-hate.php) to review and change any preferences

Implementation Details

How Substitutions Are Detected

A substitution occurs when: 

cust_order_contents.cust_menu_id != cust_order_contents.menu_id
AND cust_order_contents.cust_menu_id > 0

  • menu_id = What the customer has chosen (their substitution choice if cust_menu_id is set)
  • cust_menu_id = What was originally assigned to the customer (menu_id gets moved here when customer makes a sub)

When a customer makes a substitution: 1. The original menu_id value is moved to cust_menu_id 2. The new choice is stored in menu_id 3. If no substitution was made, menu_id contains the item and cust_menu_id is 0

How Preferences Are Set

Uses the custy_likes table: - customer = Customer ID - product_class = The classify_id from the menu item - pref = 0 (NO) or 1 (YES)

The system: 1. Gets the classify_id from the menu table for the substituted item 2. Checks if a custy_likes record exists for this customer + classify_id 3. If exists: Updates pref to 0 (if not already) 4. If not exists: Creates new record with pref = 0

Notification System

Notifications are stored in custy_preference_notifications: - notification_data = JSON with preferences array and count - shown_at = Timestamp when modal was displayed (NULL = not shown yet) - dismissed_at = Timestamp when customer dismissed it (NULL = not dismissed)

The system only shows unshown notifications (where shown_at IS NULL).

Testing

Test Scenarios

  1. Dry Run Test: 
    php command-line-tools.php process-auto-preferences --dry-run
  2. Should show what would happen without making changes
  3. No emails sent

  4. No database changes

  5. Single Customer Test: 

    php command-line-tools.php process-auto-preferences --dry-run --customer 12345

  6. Test with a specific customer who has made substitutions

  7. Full Run Test: 

    php command-line-tools.php process-auto-preferences

  8. Actually updates preferences
  9. Sends emails

  10. Creates popup notifications

  11. Popup Test:

  12. After running automation, log in as affected customer
  13. Should see modal popup on welcome page
  14. Click "Got It" - modal should close
  15. Refresh page - modal should NOT appear again (marked as shown)

Verification Queries

Check if preferences were set: 

SELECT cl.*, pc.name
FROM custy_likes cl
JOIN product_classification pc ON cl.product_class = pc.id
WHERE cl.customer = 12345
AND cl.pref = 0
ORDER BY cl.id DESC

Check notifications created: 

SELECT *
FROM custy_preference_notifications
WHERE customer_id = 12345
ORDER BY created_at DESC

Check substitution history: 

SELECT co.id, co.week_id, coc.menu_id, coc.cust_menu_id, m.name as original_item
FROM cust_order co
JOIN cust_order_contents coc ON co.id = coc.cust_order_id
JOIN menu m ON coc.menu_id = m.id
WHERE co.customer = 12345
AND coc.cust_menu_id != coc.menu_id
AND coc.cust_menu_id > 0
ORDER BY co.week_id DESC

Integration with Weekly Workflow

Recommended workflow before starting a new week:

  1. Run dry run to see impact:

  2. Navigate to /kiv/tools.php?processAutoPreferencesRun=1&dry_run=1

  3. Review the output - check customer count and items being set

  4. Run actual processing:

  5. Navigate to /kiv/tools.php?processAutoPreferencesRun=1

  6. Verify emails were sent (check email logs or mailhog in dev)

  7. Proceed with starting new week in system

Troubleshooting

No customers found

  • Check that current week has orders with substitutions
  • Verify that cust_order_contents has records where cust_menu_id != menu_id

Emails not sending

  • Check email template exists: SELECT * FROM email_templates WHERE name = 'Auto Preferences Updated'
  • Verify customer has valid email address
  • Check email sending configuration and logs
  • Verify notification was created in database
  • Check that shown_at is NULL
  • Ensure auto-preference-notification.php is included in welcome page
  • Check browser console for JavaScript errors

Preferences not updating

  • Verify menu table has valid classify_id for items
  • Check that custy_likes table is accessible
  • Review error messages in automation output

Future Enhancements

Possible improvements: 1. Add threshold setting (e.g., only set to NO after 3+ subs, not 2) 2. Add admin interface to review auto-set preferences before applying 3. Add customer setting to opt-out of auto-preferences 4. Track and report on accuracy (did customer change preference back?) 5. Email summary to admin showing all customers affected 6. Add preference history tracking (when/why preference was changed)

Security Notes

  • Notification dismissal endpoint validates customer ownership
  • Only shows notifications for logged-in customers
  • AJAX endpoint returns proper HTTP status codes
  • SQL injection protection via mysql_real_escape_string and intval

Performance Considerations

  • Indexes added to custy_preference_notifications for efficient queries
  • Query optimization using JOINs instead of nested loops where possible
  • Notifications marked as "shown" to avoid repeated displays
  • Email sending happens inline (not queued) - consider adding queue for large customer base