systemprompt_analytics/repository/funnel/
stats.rs

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

systemprompt_analytics/repository/funnel/
stats.rs
