Tier Pricing

Tier Price Database

-- catalog_product_entity_decimal for tier_price attribute
SELECT value_id, entity_id, store_id, value
FROM catalog_product_entity_decimal
WHERE attribute_id = 136 -- tier_price attribute
LIMIT 10;
+----------+-----------+----------+--------+
| value_id | entity_id | store_id | value  |
+----------+-----------+----------+--------+
|      567 |       123 |        0 | 25.0000|
|      568 |       123 |        0 | 20.0000|
|      569 |       124 |        0 | 45.0000|
+----------+-----------+----------+--------+

-- Tier price details in separate table
SELECT * FROM catalog_product_entity_tier_price
WHERE entity_id = 123;
+----------+-----------+----------+---------+--------+--------+
| value_id | entity_id | all_groups| cust_group | qty  | price  |
+----------+-----------+----------+---------+--------+--------+
|       1  |       123 |        1 |         0 |  10.00 | 25.0000|
|       2  |       123 |        1 |         0 |  50.00 | 20.0000|
|       3  |       123 |        0 |         1 |  10.00 | 27.0000|
|       4  |       123 |        0 |         2 |  10.00 | 22.0000|
+----------+-----------+----------+---------+--------+--------+

Tier Price Configuration

// Set tier prices
$prices = [
    [
        'website_id' => 0,
        'cust_group' => 1,    // General group
        'price_qty' => 10,
        'price' => 25.00,
        'price_type' => 0,    // 0=fixed, 1=discount%
    ],
    [
        'website_id' => 0,
        'cust_group' => 1,
        'price_qty' => 50,
        'price' => 20.00,
        'price_type' => 0,
    ],
];

$product->setData('tier_price', $prices);
$product->save();

Price Break Structure

Base Price: $29.99

Tier 1: 10+ units → $25.00 (17% off)
Tier 2: 25+ units → $22.00 (27% off)
Tier 3: 50+ units → $18.00 (40% off)
Tier 4: 100+ units → $15.00 (50% off)

Quantity Break Calculation

// Calculate tier price for quantity
function calculateTierPrice($product, $qty) {
    $tierPrices = $product->getTierPrices();
    $basePrice = $product->getPrice();
    
    $bestPrice = $basePrice;
    
    foreach ($tierPrices as $tier) {
        if ($qty >= $tier['price_qty'] && $tier['price'] < $bestPrice) {
            $bestPrice = $tier['price'];
        }
    }
    
    return $bestPrice;
}

// Example: 25 units
// Base: $29.99 × 25 = $749.75
// Tier 1 (10+): $25.00 × 25 = $625.00 (save $124.75)
// Tier 2 (25+): $22.00 × 25 = $550.00 (save $199.75)
// Best: Tier 2 at $550.00

Quantity Break Display

// Get tier price info
$tierPrices = $product->getTierPrices();

// Display on frontend
foreach ($tierPrices as $tier) {
    echo $tier['price_qty'] . '+ → $' . $tier['price'];
}
// Output:
// 10+ → $25.00
// 25+ → $22.00
// 50+ → $18.00

Discount Percentage

// Calculate discount percentage
$basePrice = 29.99;
$tierPrice = 25.00;
$discount = (($basePrice - $tierPrice) / $basePrice) * 100;
// Returns: 16.6%

// Display savings
echo 'Buy 10+ and save ' . round($discount) . '%';
// Output: Buy 10+ and save 17%

Customer Group Tier Prices

-- Different prices per customer group
SELECT * FROM catalog_product_entity_tier_price
WHERE entity_id = 123;
+----------+-----------+----------+---------+--------+--------+
| value_id | entity_id | all_groups| cust_group | qty  | price  |
+----------+-----------+----------+---------+--------+--------+
|       1  |       123 |        1 |         0 |  10.00 | 25.0000|
|       2  |       123 |        1 |         0 |  50.00 | 20.0000|
|       3  |       123 |        0 |         1 |  10.00 | 27.0000|  // General
|       4  |       123 |        0 |         2 |  10.00 | 22.0000|  // Wholesale
|       5  |       123 |        0 |         3 |  10.00 | 18.0000|  // VIP
+----------+-----------+----------+---------+--------+--------+

all_groups Flag

all_groups = 1: Applies to ALL customer groups
all_groups = 0: Applies to specific cust_group

Priority: Specific group > All groups > Base price

Customer Group Tier Calculation

// Get customer group
$customerGroupId = $customer->getGroupId();

// Get tier prices for this group
$tierPrices = $product->getTierPrices();
$relevantTiers = [];

foreach ($tierPrices as $tier) {
    if ($tier['all_groups'] == 1 || $tier['cust_group'] == $customerGroupId) {
        $relevantTiers[] = $tier;
    }
}

// Find best price for quantity
$qty = 25;
$bestPrice = $product->getPrice();

foreach ($relevantTiers as $tier) {
    if ($qty >= $tier['price_qty'] && $tier['price'] < $bestPrice) {
        $bestPrice = $tier['price'];
    }
}

Customer Group Configuration

Customer Groups:
  0 = Not Logged In
  1 = General
  2 = Wholesale
  3 = VIP

Tier Pricing:
  Wholesale: 10+ → $22.00 (27% off)
  VIP: 10+ → $18.00 (40% off)
  General: 10+ → $25.00 (17% off)

Frontend Display

// In catalog_product_view template
<?php if ($tierPrices = $product->getTierPrices()): ?>
    <div class="tier-prices">
        <p><?= __('Buy more, save more!') ?></p>
        <ul>
            <?php foreach ($tierPrices as $tier): ?>
                <li>
                    <?= __('Buy %1+ for $%2 each', $tier['price_qty'], number_format($tier['price'], 2)) ?>
                    (<?= __('Save %1%', round((($product->getPrice() - $tier['price']) / $product->getPrice()) * 100)) ?>)
                </li>
            <?php endforeach; ?>
        </ul>
    </div>
<?php endif; ?>

Admin Management

Admin > Catalog > Products > Edit Product
  → Advanced Prices
    → Tier Price
      Add Tier:
        Website: All Websites
        Customer Group: All Groups
        Qty: 10
        Price: $25.00

Programmatic Management

// Get tier prices
$tierPrices = $product->getTierPrices();

// Add tier price
$newTier = [
    'website_id' => 0,
    'cust_group' => 1,
    'price_qty' => 100,
    'price' => 15.00,
    'price_type' => 0,
];
$tierPrices[] = $newTier;
$product->setData('tier_price', $tierPrices);
$product->save();

// Remove tier price
$tierPrices = array_filter($tierPrices, function($tier) {
    return $tier['price_qty'] != 100;
});
$product->setData('tier_price', array_values($tierPrices));
$product->save();

Best Practices

1. Set logical quantity breaks (10, 25, 50, 100)
2. Offer meaningful discounts (10-50%)
3. Display savings percentage
4. Consider customer group pricing
5. Test tier price calculation
6. Monitor tier price impact on revenue
7. Use tier pricing for B2B sales
8. Set minimum quantities realistically