Mini Sesi 4 — Promo & Loyalty Berbasis Data untuk Retail/Grocery

Promo & loyalty adalah mesin penggerak trafik dan frekuensi belanja. Namun keduanya mudah menggerus margin bila tidak dikendalikan. Kuncinya: rules yang jelas, prioritas & stacking yang terdefinisi, targeting yang tepat, serta pengukuran lift & ROI yang disiplin.

1. Konsep Inti & Tujuan Promo

• Traffic builder (menarik kunjungan), basket builder (meningkatkan item per transaksi), margin builder (mix & match untuk mendorong komplementer).
• Tujuan harus kuantitatif: tambahan transaksi, lift unit, GM% terjaga, atau pergeseran pangsa kategori.

2. Tipe Promo Umum di Grocery

• Price-off (potongan harga langsung).
• Discount % (10%, 20%).
• Multi-buy (beli 3 seharga X, beli 2 diskon Y).
• Bundle (paket beberapa item dengan harga paket).
• B1G1 (beli satu gratis satu) atau mix & match antar kategori.
• Happy hour (jam/hari tertentu).
• Near-expiry markdown (FEFO; D-30/D-14/D-7).
• Voucher/Kupon (kode unik) & poin loyalty.

3. Desain Aturan: Conditions & Benefits

Mesin promo umumnya memerlukan conditions (syarat) dan benefits (manfaat).

• Condition: item, brand, kategori, total belanja, jam/hari, lokasi/cabang, level member.
• Benefit: price-off, %, gratis item, poin ekstra, voucher cashback.
• Kuantitas: min qty, max qty per transaksi/pelanggan, limit harian.

4. Pricebook vs Promo Rules

• Pricebook: harga normal per lokasi/periode. Stabil, prioritas rendah.
• Promo rules: harga spesial/perilaku diskon dengan conditions. Prioritas lebih tinggi, masa berlaku ketat.

5. Prioritas, Konflik, & Stacking

• Set priority numerik (besar = lebih diutamakan).
• Definisikan stackable=true/false (boleh ditumpuk?).
• Jika konflik, gunakan urutan: kupon (pelanggan) → promo rule → pricebook.

6. Penjadwalan & Segmentasi

• Schedule: start/end date, jam, hari, musim, kalender gajian.
• Segmentasi: RFM (Recency, Frequency, Monetary), level member, demografi, toko/cabang.

7. Loyalty: Poin, Tier, Voucher, & Breakage

7.1. Akumulasi Poin

# Poin dasar per transaksi:
# points_earned = floor(net_sales / rupiah_per_point)
# Booster: kategori/brand tertentu, jam tertentu, atau tier (Silver/Gold/Platinum)

7.2. Redeem & Breakage

• Redeem menjadi voucher/kupon (nilai tetap atau diskon %).
• Breakage = poin/kode yang tidak pernah ditebus (potensi margin).

7.3. Tiering

Tier Silver: total belanja 3 bulan ≥ 1,5 jt → booster 1.1x poin
Tier Gold  : total belanja 3 bulan ≥ 3 jt   → booster 1.25x poin
Tier Plat  : total belanja 3 bulan ≥ 6 jt   → booster 1.5x poin

8. Anti-Fraud & Kepatuhan

• Kupon satu kali pakai + validasi pelanggan + masa berlaku + blacklist SKU tertentu.
• Audit event: pembuatan kupon, percobaan redeem gagal, refund setelah redeem.
• Ikuti aturan pajak & perlindungan data pelanggan (email/telepon disimpan aman).

9. Template CSV: Rules Promo & Kupon

9.1. Rules Promo (contoh generik)

rule_id,rule_name,scope,selector,benefit_type,benefit_value,min_qty,max_qty,start_date,end_date,days_of_week,start_hour,end_hour,location,priority,stackable,member_level,active
RUL-0001,Snack Hemat,item,sku=GRY-SNCK-INDO-CHIT-68G-0012,price_off,400,1,,2025-12-01,2025-12-07,ALL,,,"STORE-001",90,false,ALL,TRUE
RUL-0002,Happy Hour Minuman,category,category=Beverages,percent,10,1,,2025-12-10,2025-12-24,MON-FRI,16:00,18:00,ALL,70,true,ALL,TRUE
RUL-0003,Mix&Match 3 Chips,category,category=Chips,multi_buy(3,disc_each),300,3,9,2025-12-10,2025-12-24,ALL,,,"STORE-001",80,true,ALL,TRUE
RUL-0004,Near Expiry D-7,item,tag=near_expiry,percent,25,1,12,2025-12-01,2025-12-31,ALL,,,"ALL",95,false,ALL,TRUE

9.2. Kupon/Voucher

coupon_code,issued_to,channel,valid_from,valid_to,max_redeem,amount_type,amount_value,applicable_scope,applicable_selector,min_basket,stack_with_promos,notes,active
NY25-ABCD1234,user:6281234567890,SMS,2025-12-20,2026-01-05,1,price_off,10000,category,category=Beverages,50000,false,Tahun Baru,TRUE
MEM-GOLD-15-XYZ,user:john@example.com,Email,2025-12-01,2025-12-31,2,percent,15,item,brand=Indomilk,75000,true,Gold booster,TRUE

10. Contoh JSON Payload Kupon/Voucher

{
  "coupon_code": "NY25-ABCD1234",
  "customer_ref": "user:6281234567890",
  "valid_from": "2025-12-20T00:00:00+07:00",
  "valid_to": "2026-01-05T23:59:59+07:00",
  "constraints": {
    "max_redeem": 1,
    "min_basket": 50000,
    "applicable": {"scope": "category", "selector": "category=Beverages"},
    "stack_with_promos": false
  },
  "benefit": {"type": "price_off", "value": 10000},
  "metadata": {"campaign": "NewYear2025", "channel": "SMS"}
}

11. SQL Analitik: Redeem, Lift, ROI, Cannibalization

Catatan: sesuaikan nama tabel/kolom dengan sistem Anda. Contoh menggunakan gudang data POS sederhana.

11.1. Redeem Rate & Breakage

-- coupons(coupon_code, issued_to, valid_from, valid_to)
-- redemptions(coupon_code, order_id, redeemed_at, amount_benefit)
SELECT
  COUNT(DISTINCT c.coupon_code) AS issued,
  COUNT(DISTINCT r.coupon_code) AS redeemed,
  ROUND(100.0 * COUNT(DISTINCT r.coupon_code) / NULLIF(COUNT(DISTINCT c.coupon_code),0), 2) AS redeem_rate_pct,
  COUNT(DISTINCT c.coupon_code) - COUNT(DISTINCT r.coupon_code) AS breakage_count
FROM coupons c
LEFT JOIN redemptions r ON r.coupon_code = c.coupon_code
AND r.redeemed_at BETWEEN c.valid_from AND c.valid_to;

11.2. Lift Penjualan (Before–During)

-- sales_lines(order_time, sku, qty, total)
WITH base AS (
  SELECT sku, AVG(qty) AS avg_qty_before
  FROM sales_lines
  WHERE order_time BETWEEN DATE '2025-11-01' AND DATE '2025-11-30'
  GROUP BY sku
),
during AS (
  SELECT sku, AVG(qty) AS avg_qty_during
  FROM sales_lines
  WHERE order_time BETWEEN DATE '2025-12-01' AND DATE '2025-12-07'
  GROUP BY sku
)
SELECT d.sku, b.avg_qty_before, d.avg_qty_during,
       ROUND(100.0 * (d.avg_qty_during - b.avg_qty_before) / NULLIF(b.avg_qty_before,0), 2) AS lift_pct
FROM during d
JOIN base b ON b.sku = d.sku
ORDER BY lift_pct DESC;

11.3. ROI Promo Sederhana

-- pricebook & cost_view seperti sesi sebelumnya
-- promo_cost bisa diisi: biaya materi, fee platform, dsb.
SELECT
  s.sku,
  SUM(s.qty) AS units,
  SUM(s.total) AS revenue,
  SUM(s.qty * c.moving_avg_cost) AS cogs,
  SUM(s.discount_amount) AS discount_given,
  (SUM(s.total) - SUM(s.qty * c.moving_avg_cost)) AS gross_margin_after_discount,
  ((SUM(s.total) - SUM(s.qty * c.moving_avg_cost)) - SUM(s.discount_amount) - SUM(s.promo_cost)) AS roi_value
FROM sales_lines s
JOIN cost_view c ON c.sku = s.sku
WHERE s.order_time BETWEEN DATE '2025-12-01' AND DATE '2025-12-07'
GROUP BY s.sku
ORDER BY roi_value DESC;

11.4. Cannibalization (Substitusi Antar SKU)

-- bandingkan SKU target vs SKU sekeluarga (kategori/brand sama) di periode promo
WITH fam AS (
  SELECT sku, category FROM products WHERE category = 'Chips'
),
b AS (
  SELECT sku, SUM(qty) qty_bef FROM sales_lines
  WHERE order_time BETWEEN DATE '2025-11-01' AND DATE '2025-11-30'
  GROUP BY sku
),
d AS (
  SELECT sku, SUM(qty) qty_dur FROM sales_lines
  WHERE order_time BETWEEN DATE '2025-12-01' AND DATE '2025-12-07'
  GROUP BY sku
)
SELECT f.category, COALESCE(d.sku,b.sku) AS sku,
       COALESCE(b.qty_bef,0) AS before_qty,
       COALESCE(d.qty_dur,0) AS during_qty,
       (COALESCE(d.qty_dur,0) - COALESCE(b.qty_bef,0)) AS delta_qty
FROM fam f
LEFT JOIN b ON b.sku = f.sku
LEFT JOIN d ON d.sku = f.sku
ORDER BY delta_qty ASC;

12. SOP Eksekusi dan Audit Promo

1) Rancang tujuan promo (metric & target).
2) Buat rules & kupon (CSV/JSON), set prioritas & stacking.
3) Uji di sandbox: 5 skenario transaksi + konflik promo.
4) Sinkron ke POS, cetak label rak, training kasir.
5) Monitoring harian: redeem rate, margin, keluhan harga.
6) Tutup periode: hitung lift & ROI, review cannibalization.
7) Audit jejak perubahan & tindak lanjut anomali.

13. Matriks Perbandingan Tipe Promo

Checklist Implementasi

• Tetapkan kamus promo (condition/benefit, prioritas, stacking).
• Definisikan near-expiry markdown otomatis (D-30/14/7).
• Rancang tier loyalty & rupiah per poin; tetapkan TTL kupon.
• Aktifkan audit trail, limit redeem per pelanggan, dan blacklist SKU.
• Siapkan dashboard: redeem rate, lift, ROI, breakage, cannibalization.

Kesimpulan

Program promo & loyalty yang sehat bukan sekadar diskon, melainkan arsitektur rules, targeting yang tepat, serta pengukuran lift & ROI yang ketat. Dengan template CSV/JSON, SOP, dan SQL analitik di atas, Anda bisa merancang kampanye yang menaikkan trafik dan basket tanpa kehilangan kendali atas margin.

Sumber/Referensi

• Shopify — Discounts
• Square — Create Discounts
• Lightspeed Retail (X-Series) — Promotions
• Odoo — Point of Sale (Promotions & Loyalty)