Shopware 5 Merge Customers

Merging Customers in Shopware 5: Cleaning Up Duplicate Customer Records

After running a Shopware 5 store for a while, you may notice that the system accumulates many duplicate customer records. A common issue is that multiple customer IDs exist for the same email address. This can happen when a registered user places orders but, at different times, also checks out as a guest. Over time, this results in multiple guest accounts associated with the same email, alongside the registered account.

To maintain data consistency and improve customer management, we can use a PHP script to clean up these duplicate entries. The script will:

1. Identify customers with duplicate email addresses.

2. Move all guest orders to the corresponding registered user account.

3. Delete the guest accounts, keeping only the registered user.

Running the Cleanup Script via SSH

Since this process involves database operations, we must execute the PHP script directly on the server using SSH. Follow these steps:

1. Connect to the Server

Log in to your Shopware server via SSH:

ssh your-user@your-server-ip

2. Navigate to Your Shopware Directory

Move to your Shopware installation directory:

cd /path/to/shopware

3. Execute the PHP Script

Run the cleanup script:

php merge_customers.php

Sample PHP Script for Merging Customers

Below is a simple PHP script that can help with merging customer accounts and moving guest orders:

boot();

// Get database connection
$db = $kernel->getContainer()->get('db');

// Logging function
function logMessage($message) {
    echo $message . PHP_EOL;
    file_put_contents(__DIR__ . '/var/log/account_merge_log.txt', date('Y-m-d H:i:s') . ': ' . $message . PHP_EOL, FILE_APPEND);
}

logMessage('Starting customer account merge process...');

// Retrieve all email addresses that have both registered and guest user accounts
$sql = "SELECT DISTINCT email 
        FROM s_user 
        WHERE email IN (
            SELECT email 
            FROM s_user 
            GROUP BY email 
            HAVING SUM(CASE WHEN accountmode = 0 THEN 1 ELSE 0 END) > 0 
            AND SUM(CASE WHEN accountmode = 1 THEN 1 ELSE 0 END) > 0
        )";

$duplicateEmails = $db->fetchAll($sql, [], PDO::FETCH_COLUMN);

$totalEmails = count($duplicateEmails);
logMessage("Found {$totalEmails} email addresses with both registered and guest accounts. Processing all at once.");

if ($totalEmails == 0) {
    logMessage("No eligible emails to process. Script exiting.");
    exit;
}

foreach ($duplicateEmails as $email) {
    logMessage("Processing email: {$email}");
    
    $accounts = $db->fetchAll(
        "SELECT 
            u.id, 
            u.accountmode,  -- Fetch account mode from s_user table (0=registered user, 1=guest user)
            u.customernumber,  -- Fetch customer number from s_user table
            COUNT(o.id) AS order_count,  -- Count associated orders
            u.lastlogin,  -- Fetch last login time from s_user table
            u.active  -- Fetch account activation status from s_user table
         FROM 
            s_user u
         LEFT JOIN 
            s_order o ON u.id = o.userID
         WHERE 
            u.email = ?
         GROUP BY 
            u.id
         ORDER BY 
            u.accountmode ASC, 
            order_count DESC, 
            u.lastlogin DESC",
        [$email]
    );
    
    $registeredAccount = null;
    $guestAccounts = [];
    
    foreach ($accounts as $account) {
        if ($account['accountmode'] == 0) {
            $registeredAccount = $account;
        } else {
            $guestAccounts[] = $account;
        }
    }
    
    if (!$registeredAccount || empty($guestAccounts)) {
        logMessage("  Warning: Email {$email} does not meet criteria (missing either registered or guest user). Skipping.");
        continue;
    }
    
    $mainAccountId = $registeredAccount['id'];
    logMessage("  Selecting registered account ID {$mainAccountId} as the main account (Order count: {$registeredAccount['order_count']})");

    $db->beginTransaction();
    try {
        $guestIds = array_column($guestAccounts, 'id');
        $placeholders = implode(',', array_fill(0, count($guestIds), '?'));

        $db->executeUpdate("UPDATE s_order SET userID = ? WHERE userID IN ($placeholders)", array_merge([$mainAccountId], $guestIds));
        $db->executeUpdate("UPDATE s_order_billingaddress SET userID = ? WHERE userID IN ($placeholders)", array_merge([$mainAccountId], $guestIds));
        $db->executeUpdate("UPDATE s_order_shippingaddress SET userID = ? WHERE userID IN ($placeholders)", array_merge([$mainAccountId], $guestIds));
        $db->executeUpdate("UPDATE s_order_basket SET userID = ? WHERE userID IN ($placeholders)", array_merge([$mainAccountId], $guestIds));
        $db->executeUpdate("DELETE FROM s_user WHERE id IN ($placeholders)", $guestIds);

        $db->commit();
        logMessage("  Successfully merged guest account orders into registered account ID {$mainAccountId} and deleted guest accounts for email {$email}");
    } catch (Exception $e) {
        $db->rollBack();
        logMessage("  Error merging email {$email}: " . $e->getMessage());
    }
}

logMessage("All eligible accounts have been processed.");

Best Practices

• Backup Your Database: Always create a backup before running any cleanup script to avoid accidental data loss.

• Schedule Regular Cleanups: Set up a scheduled task (cron job) to run this script periodically.

• Monitor Database Performance: Regularly check your database performance to ensure that removing guest users has a positive impact.

Conclusion

Cleaning up guest users without orders is a simple yet effective way to maintain a well-organized and efficient Shopware 5 store. By periodically running a cleanup script, you can optimize your database, improve system performance, and streamline customer management.