​

🤝 CRM Module (crm_*)

​

1. Module Purpose

• Menyimpan contact (prospek, customer, partner).
• Menyimpan lead penjualan dan status pipeline.
• Menyimpan campaign marketing.

​

2. Tables & Structure

​

3. Key Fields & Relationships

​

3.1 crm_contact

• Fields: company_id, name, email, phone, type (prospect, customer, partner), source.

​

3.2 crm_lead

• Fields: contact_id, company_id, title, stage, expected_value, expected_close_date, owner_user_id.
• FK: contact_id → crm_contact.id, owner_user_id → core_user.id.

​

3.3 crm_campaign

• Fields: company_id, code, name, start_date, end_date, budget, status.
• Campaign dapat dikaitkan ke leads via aplikasi (mis. tabel relasi many-to-many bila dibutuhkan di masa depan).

​

4. Business Flows

​

4.1 Contact → Lead → Customer

1. Contact masuk ke sistem (crm_contact).
2. Tim sales membuat lead (crm_lead) untuk contact tersebut.
3. Setelah deal close, lead dapat dikonversi menjadi customer di modul Sales (so_customer).

​

4.2 Campaign Performance (Konseptual)

1. Marketing menjalankan campaign (crm_campaign).
2. Leads ditandai sebagai hasil campaign tersebut.
3. Analisa dilakukan untuk melihat conversion rate per campaign.

​

5. Example Reports (SQL)

​

5.1 Lead Pipeline per Stage

SELECT
  l.stage,
  COUNT(*)                 AS lead_count,
  SUM(l.expected_value)    AS pipeline_value
FROM crm_lead l
WHERE l.company_id = :company_id
GROUP BY l.stage;

​

5.2 Lead per Owner (Salesperson)

SELECT
  u.email AS owner,
  COUNT(*) AS lead_count
FROM crm_lead l
JOIN core_user u ON u.id = l.owner_user_id
WHERE l.company_id = :company_id
GROUP BY u.id;

​

5.3 Contact List dengan Lead Terbaru

SELECT
  c.id,
  c.name,
  c.email,
  MAX(l.created_at) AS last_lead_created
FROM crm_contact c
LEFT JOIN crm_lead l ON l.contact_id = c.id
WHERE c.company_id = :company_id
GROUP BY c.id;