Changes between Version 1 and Version 2 of AdvancedReports

Timestamp:

06/14/26 23:19:08 (30 hours ago)

Author:

213257

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Advanced Reports =
 
-== Unit Demand Forecast — Quarterly Interest Ranking ==
+== Unit Demand — Quarterly Interest Ranking ==
 
 Which currently available units show the strongest demand signals in the last quarter,
@@ -116 +116 @@
 
 ----
+
+== Client Engagement & Conversion — Semi-Annual ==
+
+What steps do clients go through over a six month period?
+This report segments the clients into two types - Cold interest meaning client only made an inquiry,
+and Warm interest meaning client made an appointment. Ranking them by an engagement score based on the inquiry volume and the appointment count.
+Showing the average price range for each client and the time taken from first inquiry to an appointment.
+With the goal for giving insight into the behaviour of clients.
+
+=== SQL
+
+{{{
+WITH client_inquiries AS (
+    SELECT
+        c.client_id,
+        c.name AS client_name,
+        c.email AS client_email,
+        c.phone AS client_phone,
+        COUNT(DISTINCT i.inquiry_id) AS total_inquiries,
+        MIN(i.created_at) AS first_inquiry_date,
+        MAX(i.created_at) AS last_inquiry_date,
+        ROUND(AVG(u.price), 2) AS avg_inquired_price
+    FROM project.client c
+    JOIN project.inquiry i  ON c.client_id = i.client_id
+        AND i.created_at >= CURRENT_DATE - INTERVAL '6 months'
+    JOIN project.unit u     ON i.unit_id   = u.unit_id
+    GROUP BY c.client_id, c.name, c.email, c.phone
+),
+client_appointments AS (
+    SELECT
+        c.client_id,
+        COUNT(DISTINCT a.appointment_id) AS total_appointments,
+        MIN(ts.date) AS first_appointment_date
+    FROM project.client c
+    JOIN project.appointment a ON c.client_id = a.client_id
+    JOIN project.timeslot ts ON a.timeslot_id = ts.timeslot_id
+    WHERE ts.date >= CURRENT_DATE - INTERVAL '6 months'
+    GROUP BY c.client_id
+)
+
+SELECT
+    RANK() OVER (
+        ORDER BY (
+            ci.total_inquiries +
+            COALESCE(ca.total_appointments, 0) * 3
+        ) DESC
+    ) AS engagement_rank,
+    ci.client_name,
+    ci.client_email,
+    ci.client_phone,
+    CASE
+        WHEN COALESCE(ca.total_appointments, 0) > 0 THEN 'Warm Interest'
+        ELSE 'Cold Interest'
+    end AS interest,
+    ci.total_inquiries,
+    COALESCE(ca.total_appointments, 0) AS total_appointments,
+    CASE
+        WHEN ca.first_appointment_date IS NOT NULL
+        THEN (ca.first_appointment_date - ci.first_inquiry_date::date)
+        ELSE NULL
+    end AS days_to_first_appointment,
+    ci.avg_inquired_price,
+    ci.first_inquiry_date,
+    ci.last_inquiry_date
+FROM client_inquiries ci
+LEFT JOIN client_appointments ca ON ci.client_id = ca.client_id
+ORDER BY engagement_rank;
+}}}