Dashboard Shieldtech

Sales Funnel & Marketing

Efektivitas Event (Prospek per Tipe Event)

pie

SELECT CASE se.event_type WHEN 0 THEN 'Open House' WHEN 1 THEN 'Exhibition / Other' WHEN 2 THEN 'Other' ELSE 'Uncategorized' END as event_category, COUNT(gbp.id) as total_prospects FROM shieldtech_admission.guest_book_prospects gbp JOIN shieldtech_admission.guest_books gb ON gbp.guest_book_id = gb.id JOIN shieldtech_admission.shi_events se ON gb.shi_event_id = se.id WHERE gb.shi_event_id IS NOT NULL GROUP BY event_category ORDER BY total_prospects DESC;

Tingkat Respon Follow-up (Leads Funnel Detail)

bar

SELECT COUNT(id) FROM shieldtech_admission.guest_book_prospects UNION ALL SELECT COUNT(id) FROM shieldtech_admission.guest_book_follow_ups WHERE status_id IN (1, 2, 5, 6) UNION ALL SELECT COUNT(gbp.id) FROM shieldtech_admission.guest_book_prospects gbp JOIN shieldtech_admission.guest_books gb ON gbp.guest_book_id = gb.id WHERE gb.is_buy_form = 1

Platform Sumber Informasi SHI (Awareness Channel)

bar

SELECT qo.text_en as source, COUNT(DISTINCT gq.guest_book_id) as total_prospects FROM shieldtech_admission.guest_book_questionnaires gq JOIN shieldtech_admission.questionnaire_options qo ON gq.questionnaire_option_id = qo.id WHERE qo.question_id = 1 AND gq.answer = '1' GROUP BY source ORDER BY total_prospects DESC LIMIT 10;

Alasan Ketertarikan Tertinggi

bar

SELECT qo.text_en as reason, COUNT(DISTINCT gq.guest_book_id) as total_prospects FROM shieldtech_admission.guest_book_questionnaires gq JOIN shieldtech_admission.questionnaire_options qo ON gq.questionnaire_option_id = qo.id WHERE qo.question_id = 2 AND gq.answer = '1' GROUP BY reason ORDER BY total_prospects DESC LIMIT 5;

Tren Sumber Prospek

Perkembangan jumlah prospek dari berbagai sumber informasi dari waktu ke waktu.

SELECT DATE_FORMAT(gb.created_at, '%Y-%m') AS ym, qo.text_en AS source, COUNT(DISTINCT gq.guest_book_id) AS total_prospects FROM shieldtech_admission.guest_book_questionnaires gq JOIN shieldtech_admission.questionnaire_options qo ON gq.questionnaire_option_id = qo.id JOIN shieldtech_admission.guest_books gb ON gq.guest_book_id = gb.id WHERE qo.question_id = 1 AND gq.answer = '1' AND gb.created_at IS NOT NULL GROUP BY ym, source ORDER BY ym ASC, source ASC;

Funnel Konversi Penerimaan Siswa

bar

SELECT '1. Leads Masuk (Guest Books)' as stage, COUNT(id) as total FROM shieldtech_admission.guest_books UNION ALL SELECT '2. Leads di Follow-Up' as stage, COUNT(DISTINCT guest_book_id) as total FROM shieldtech_admission.guest_book_follow_ups UNION ALL SELECT '3. Mengisi Aplikasi' as stage, COUNT(id) as total FROM shieldtech_admission.admission_student_profiles WHERE fill_by_parent = 1 UNION ALL SELECT '4. Diterima (Accepted)' as stage, COUNT(id) as total FROM shieldtech_admission.student_assessments WHERE acceptance_status_id = 1 UNION ALL SELECT '5. Menjadi Siswa Aktif (Bayar)' as stage, COUNT(DISTINCT pfc.prospect_profile_id) as total FROM shieldtech_admission.parent_financial_commitment_forms pfc WHERE pfc.status_id > 0;

Rata-rata Kecepatan Proses

19.2 Hari

Dari pendaftaran hingga diterima.

SELECT AVG(DATEDIFF(sa.confirmed_at, asp.enrollment_date)) as avg_days FROM shieldtech_admission.admission_student_profiles asp JOIN shieldtech_admission.student_assessments sa ON asp.id = sa.student_id WHERE sa.confirmed_at IS NOT NULL AND asp.enrollment_date IS NOT NULL;

Top 10 Asal Prospek (Kabupaten/Kota)

bar

SELECT r.name as regency_name, COUNT(gb.id) as total_prospects FROM shieldtech_admission.guest_books gb JOIN shieldtech_common.regencies r ON gb.regency_code = r.code GROUP BY r.name ORDER BY total_prospects DESC LIMIT 10;