systemprompt_analytics/repository/funnel/
stats.rs

1//! Funnel step-statistics computation.
2//!
3//! Copyright (c) systemprompt.io — Business Source License 1.1.
4//! See <https://systemprompt.io> for licensing details.
5
6use crate::Result;
7use chrono::{DateTime, Utc};
8use systemprompt_identifiers::FunnelId;
9
10use super::FunnelRepository;
11use crate::models::{FunnelStats, FunnelStep, FunnelStepStats};
12
13impl FunnelRepository {
14    pub async fn get_stats(
15        &self,
16        funnel_id: &FunnelId,
17        since: DateTime<Utc>,
18    ) -> Result<FunnelStats> {
19        let funnel = self.find_by_id(funnel_id).await?.ok_or_else(|| {
20            crate::AnalyticsError::SessionNotFound(format!("funnel {}", funnel_id))
21        })?;
22
23        let total_entries = sqlx::query_scalar!(
24            r#"
25            SELECT COUNT(*) as "count!"
26            FROM funnel_progress
27            WHERE funnel_id = $1 AND created_at >= $2
28            "#,
29            funnel_id.as_str(),
30            since
31        )
32        .fetch_one(&*self.pool)
33        .await?;
34
35        let total_completions = sqlx::query_scalar!(
36            r#"
37            SELECT COUNT(*) as "count!"
38            FROM funnel_progress
39            WHERE funnel_id = $1 AND created_at >= $2 AND completed_at IS NOT NULL
40            "#,
41            funnel_id.as_str(),
42            since
43        )
44        .fetch_one(&*self.pool)
45        .await?;
46
47        let overall_conversion_rate = if total_entries > 0 {
48            (total_completions as f64 / total_entries as f64) * 100.0
49        } else {
50            0.0
51        };
52
53        let step_stats = self
54            .calculate_step_stats(funnel_id, &funnel.steps, since)
55            .await?;
56
57        Ok(FunnelStats {
58            funnel_id: funnel_id.clone(),
59            funnel_name: funnel.funnel.name,
60            total_entries,
61            total_completions,
62            overall_conversion_rate,
63            step_stats,
64        })
65    }
66
67    async fn calculate_step_stats(
68        &self,
69        funnel_id: &FunnelId,
70        steps: &[FunnelStep],
71        since: DateTime<Utc>,
72    ) -> Result<Vec<FunnelStepStats>> {
73        if steps.is_empty() {
74            return Ok(Vec::new());
75        }
76
77        let step_orders: Vec<i32> = steps.iter().map(|s| s.step_order).collect();
78
79        let rows = sqlx::query!(
80            r#"
81            SELECT
82                s.step_order,
83                COUNT(*) FILTER (WHERE fp.current_step >= s.step_order) as "entered_count!",
84                COUNT(*) FILTER (WHERE fp.current_step > s.step_order) as "exited_count!"
85            FROM UNNEST($3::int4[]) AS s(step_order)
86            LEFT JOIN funnel_progress fp
87                ON fp.funnel_id = $1 AND fp.created_at >= $2
88            GROUP BY s.step_order
89            ORDER BY s.step_order
90            "#,
91            funnel_id.as_str(),
92            since,
93            &step_orders
94        )
95        .fetch_all(&*self.pool)
96        .await?;
97
98        let mut stats = Vec::with_capacity(steps.len());
99        for row in rows {
100            let entered_count = row.entered_count;
101            let exited_count = row.exited_count;
102            let conversion_rate = if entered_count > 0 {
103                (exited_count as f64 / entered_count as f64) * 100.0
104            } else {
105                0.0
106            };
107
108            stats.push(FunnelStepStats {
109                step_order: row.step_order.unwrap_or(0),
110                entered_count,
111                exited_count,
112                conversion_rate,
113                avg_time_to_next_ms: None,
114            });
115        }
116
117        Ok(stats)
118    }
119}
systemprompt_analytics/repository/funnel/stats.rs

systemprompt_analytics/repository/funnel/
stats.rs
