Price Drop Notification for E-Commerce

Development of Price Drop Notification for E-Commerce

Price drop notification is a tool to return "undecided" buyers. A user added a product to wishlist or list of desires but didn't buy — perhaps the price seemed too high. When the price drops, an automated email brings them back into the funnel. Development takes 1–2 business days, provided wishlist is already implemented.

Data Schema

Price drop subscriptions can be part of wishlist system or separate entity:

CREATE TABLE price_drop_subscriptions (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    variant_id BIGINT REFERENCES product_variants(id) ON DELETE CASCADE,
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    email VARCHAR(255) NOT NULL,
    price_at_subscription NUMERIC(12,2) NOT NULL,
    target_price NUMERIC(12,2),    -- desired price (optional)
    notified_at TIMESTAMP,
    notified_price NUMERIC(12,2),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_pds_user_product
    ON price_drop_subscriptions(user_id, product_id, COALESCE(variant_id, 0));

price_at_subscription — price at subscription moment. Notify when it drops relative to it, not relative to last known price.

Subscription Button on Product Card

Shown next to "Add to Cart" button. For authorized users — one click:

const PriceDropButton = ({ product, variant }: PriceDropProps) => {
  const { user } = useAuth();
  const [subscribed, setSubscribed] = useState(product.is_price_drop_subscribed ?? false);

  const toggle = async () => {
    if (!user) {
      // Redirect to login with return_url
      router.push(`/login?redirect=${encodeURIComponent(window.location.pathname)}`);
      return;
    }

    if (subscribed) {
      await api.delete(`/price-drop-subscriptions/${product.id}`);
      setSubscribed(false);
      toast.success('Subscription cancelled');
    } else {
      await api.post('/price-drop-subscriptions', {
        product_id: product.id,
        variant_id: variant?.id ?? null,
      });
      setSubscribed(true);
      toast.success('You will be notified when price drops');
    }
  };

  return (
    <button
      onClick={toggle}
      className={cn('flex items-center gap-1.5 text-sm', {
        'text-blue-600': subscribed,
        'text-gray-500 hover:text-gray-700': !subscribed,
      })}
    >
      <BellIcon className={cn('w-4 h-4', { 'fill-blue-600': subscribed })} />
      {subscribed ? 'Tracking price' : 'Track price'}
    </button>
  );
};

Subscription API

public function subscribe(Request $request): JsonResponse
{
    $request->validate([
        'product_id' => 'required|exists:products,id',
        'variant_id' => 'nullable|exists:product_variants,id',
        'target_price' => 'nullable|numeric|min:0',
    ]);

    $product = Product::find($request->product_id);
    $currentPrice = $product->sale_price ?? $product->price;

    PriceDropSubscription::updateOrCreate(
        [
            'user_id'    => $request->user()->id,
            'product_id' => $request->product_id,
            'variant_id' => $request->variant_id,
        ],
        [
            'email'                  => $request->user()->email,
            'price_at_subscription'  => $currentPrice,
            'target_price'           => $request->target_price,
            'notified_at'            => null,
            'notified_price'         => null,
        ]
    );

    return response()->json(['subscribed' => true, 'price_snapshot' => $currentPrice]);
}

Trigger on Price Change

Check fires when product price updates — in observer or after ERP import:

class ProductObserver
{
    public function updated(Product $product): void
    {
        $priceChanged = $product->isDirty('price') || $product->isDirty('sale_price');
        if (!$priceChanged) return;

        $newPrice = $product->sale_price ?? $product->price;
        $oldPrice = $product->getOriginal('sale_price') ?? $product->getOriginal('price');

        if ($newPrice < $oldPrice) {
            CheckPriceDropSubscriptions::dispatch($product, $newPrice)->onQueue('notifications');
        }
    }
}

Notification Sending Job

class CheckPriceDropSubscriptions implements ShouldQueue
{
    public function __construct(
        private Product $product,
        private float $newPrice,
    ) {}

    public function handle(): void
    {
        $threshold = 0.05; // notify on 5%+ drop

        PriceDropSubscription::where('product_id', $this->product->id)
            ->whereNull('notified_at')
            ->chunkById(100, function ($subscriptions) use ($threshold) {
                foreach ($subscriptions as $sub) {
                    $dropPercent = ($sub->price_at_subscription - $this->newPrice) / $sub->price_at_subscription;

                    if ($dropPercent < $threshold) continue;
                    if ($sub->target_price && $this->newPrice > $sub->target_price) continue;

                    Mail::to($sub->email)->queue(new PriceDropNotification($sub, $this->product, $this->newPrice));

                    $sub->update([
                        'notified_at'    => now(),
                        'notified_price' => $this->newPrice,
                    ]);
                }
            });
    }
}

chunkById — important for scalability: if a product has thousands of subscribers, you can't load them all into memory.

Price Drop Email

Email contains:

• Product photo, name
• Old price (strikethrough) → new price
• Percentage drop: "Price fell by 23%"
• "Buy Now" button with UTM tag utm_source=price_drop
• Offer deadline (if drop is temporary)
• "Unsubscribe from price notifications" link

Repeat Notifications

After notified_at, subscription is considered "used". If price continues to fall — whether to notify again is determined by setting. Recommended: repeat notification when drop another 10%+ relative to notified_price.

Price Tracking List in Personal Cabinet

In /account/price-tracking section user sees all subscriptions: product, price at subscription, current price, change. This also motivates adding more products — user sees service value.

const PriceTrackingList = ({ subscriptions }: { subscriptions: PriceDropSubscription[] }) => (
  <table className="w-full text-sm">
    <thead>
      <tr className="border-b">
        <th className="text-left py-2">Product</th>
        <th>Price at subscription</th>
        <th>Current price</th>
        <th>Change</th>
        <th></th>
      </tr>
    </thead>
    <tbody>
      {subscriptions.map(sub => {
        const change = ((sub.current_price - sub.price_at_subscription) / sub.price_at_subscription) * 100;
        return (
          <tr key={sub.id} className="border-b">
            <td className="py-3">
              <a href={`/products/${sub.product.slug}`} className="font-medium hover:underline">
                {sub.product.name}
              </a>
            </td>
            <td className="text-center">{formatPrice(sub.price_at_subscription)}</td>
            <td className="text-center font-medium">{formatPrice(sub.current_price)}</td>
            <td className={cn('text-center text-sm', change < 0 ? 'text-green-600' : 'text-gray-400')}>
              {change < 0 ? `−${Math.abs(change).toFixed(1)}%` : `+${change.toFixed(1)}%`}
            </td>
            <td>
              <button onClick={() => unsubscribe(sub.id)} className="text-gray-400 hover:text-red-500">
                Unsubscribe
              </button>
            </td>
          </tr>
        );
      })}
    </tbody>
  </table>
);