Automatic margin calculation for dropshipping

Implementation of Automatic Margin Calculation in Dropshipping

Margin in dropshipping is the difference between store's retail price and supplier's wholesale price minus other costs: payment processing, shipping, advertising. Automatic calculation is needed so when supplier's cost changes, retail price recalculates by set rules without manager involvement.

What Affects Final Margin

• Wholesale price — changes with each supplier price sync
• Payment processing fee — 1.5–3.5% depending on payment system
• Shipping cost — may be compensated or included in price
• Returns — statistical percentage built into markup
• Advertising — CAC (cost per acquisition) for breakeven calculation

Margin Rules Model

Schema::create('margin_rules', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->enum('scope', ['global', 'supplier', 'category', 'product']);
    $table->nullableMorphs('scopeable'); // polymorphic: Supplier, Category, Product
    $table->enum('type', ['percent', 'fixed', 'table']);
    $table->decimal('value', 8, 2)->nullable(); // for percent and fixed
    $table->jsonb('table_config')->nullable(); // for type=table
    $table->decimal('min_retail_price', 10, 2)->nullable(); // lower bound
    $table->boolean('include_acquiring', true)->default(true);
    $table->decimal('acquiring_percent', 5, 2)->default(2.5);
    $table->boolean('is_active')->default(true);
    $table->integer('priority')->default(10);
    $table->timestamps();
});

Complete Margin Calculator

class MarginCalculator
{
    /**
     * Calculate retail price from wholesale accounting for all costs
     */
    public function calculateRetailPrice(
        float $supplierPrice,
        MarginRule $rule,
        ?float $shippingCost = null,
    ): float {
        // Base retail price by markup rule
        $baseRetail = match($rule->type) {
            'percent' => $supplierPrice * (1 + $rule->value / 100),
            'fixed'   => $supplierPrice + $rule->value,
            'table'   => $this->applyTable($supplierPrice, $rule->table_config),
        };

        // Account for payment processing fee
        if ($rule->include_acquiring) {
            $baseRetail = $baseRetail / (1 - $rule->acquiring_percent / 100);
        }

        // Account for shipping cost if included in price
        if ($shippingCost && $rule->include_shipping) {
            $baseRetail += $shippingCost;
        }

        // Apply lower bound
        if ($rule->min_retail_price && $baseRetail < $rule->min_retail_price) {
            $baseRetail = $rule->min_retail_price;
        }

        return round($baseRetail, 2);
    }

    /**
     * Calculate actual margin for reports
     */
    public function calculateMargin(float $retailPrice, float $supplierPrice, float $acquiringPercent = 2.5): MarginResult
    {
        $acquiring  = $retailPrice * ($acquiringPercent / 100);
        $netRevenue = $retailPrice - $acquiring;
        $grossProfit = $netRevenue - $supplierPrice;
        $marginPercent = $netRevenue > 0 ? ($grossProfit / $netRevenue) * 100 : 0;

        return new MarginResult(
            retailPrice:    $retailPrice,
            supplierPrice:  $supplierPrice,
            acquiring:      round($acquiring, 2),
            netRevenue:     round($netRevenue, 2),
            grossProfit:    round($grossProfit, 2),
            marginPercent:  round($marginPercent, 2),
        );
    }

    /**
     * Tiered markup by table
     */
    private function applyTable(float $price, array $table): float
    {
        // Example table_config:
        // [{"max_price": 500, "percent": 50}, {"max_price": 2000, "percent": 35},
        //  {"max_price": 10000, "percent": 25}, {"max_price": null, "percent": 15}]
        foreach ($table as $tier) {
            if ($tier['max_price'] === null || $price <= $tier['max_price']) {
                return $price * (1 + $tier['percent'] / 100);
            }
        }
        return $price * 1.15; // fallback
    }
}

Margin Rule Resolver

Rules applied by specificity order: product > category > supplier > global.

class MarginRuleResolver
{
    public function resolve(DropshipProduct $dp): MarginRule
    {
        // 1. Individual rule for specific product
        $rule = MarginRule::where('scope', 'product')
            ->where('scopeable_type', Product::class)
            ->where('scopeable_id', $dp->product_id)
            ->active()
            ->orderBy('priority')
            ->first();

        if ($rule) return $rule;

        // 2. Rule for product category
        if ($dp->product?->category_id) {
            $categoryIds = $dp->product->category->ancestorsAndSelf()->pluck('id');
            $rule = MarginRule::where('scope', 'category')
                ->where('scopeable_type', Category::class)
                ->whereIn('scopeable_id', $categoryIds)
                ->active()
                ->orderBy('priority')
                ->first();

            if ($rule) return $rule;
        }

        // 3. Rule for supplier
        $rule = MarginRule::where('scope', 'supplier')
            ->where('scopeable_type', Supplier::class)
            ->where('scopeable_id', $dp->supplier_id)
            ->active()
            ->first();

        if ($rule) return $rule;

        // 4. Global default rule
        return MarginRule::where('scope', 'global')->active()->firstOrFail();
    }
}

Automatic Recalculation on Wholesale Price Change

class RecalculateRetailPriceListener
{
    public function handle(SupplierPriceUpdatedEvent $event): void
    {
        $dp   = $event->dropshipProduct;
        $rule = $this->resolver->resolve($dp);

        if (!$dp->product || $dp->product->price_locked) {
            return; // price manually fixed — don't change
        }

        $newRetailPrice = $this->calculator->calculateRetailPrice(
            supplierPrice: $dp->supplier_price,
            rule: $rule,
        );

        $dp->product->update(['price' => $newRetailPrice]);

        Log::debug('Retail price recalculated', [
            'product_id'    => $dp->product_id,
            'supplier_price' => $dp->supplier_price,
            'retail_price'  => $newRetailPrice,
            'rule'          => $rule->name,
        ]);
    }
}

Margin Report

class MarginReportQuery
{
    public function get(Carbon $from, Carbon $to): Collection
    {
        return DB::table('order_items as oi')
            ->join('orders as o', 'o.id', '=', 'oi.order_id')
            ->join('products as p', 'p.id', '=', 'oi.product_id')
            ->join('dropship_products as dp', 'dp.product_id', '=', 'p.id')
            ->join('dropship_suppliers as s', 's.id', '=', 'dp.supplier_id')
            ->whereBetween('o.paid_at', [$from, $to])
            ->where('o.status', 'completed')
            ->selectRaw('
                s.name as supplier,
                SUM(oi.quantity * oi.price) as revenue,
                SUM(oi.quantity * dp.supplier_price) as cost,
                SUM(oi.quantity * (oi.price - dp.supplier_price)) as gross_profit,
                ROUND(
                    SUM(oi.quantity * (oi.price - dp.supplier_price)) /
                    NULLIF(SUM(oi.quantity * oi.price), 0) * 100, 2
                ) as margin_percent
            ')
            ->groupBy('s.id', 's.name')
            ->orderByDesc('gross_profit')
            ->get();
    }
}

Timeline

Basic calculator (percentage markup) + auto-recalculation on price sync — 2–3 business days. Tiered rules, priority hierarchy, fee accounting, margin report — 3–5 business days.