systemprompt_analytics/repository/traffic/
audience.rs

1//! Geography, device, and bot traffic breakdowns.
2//!
3//! Copyright (c) systemprompt.io — Business Source License 1.1.
4//! See <https://systemprompt.io> for licensing details.
5
6use super::TrafficAnalyticsRepository;
7use crate::Result;
8use crate::models::reporting::{BotTotalsRow, BotTypeRow, DeviceRow, GeoRow};
9use chrono::{DateTime, Utc};
10
11impl TrafficAnalyticsRepository {
12    pub async fn get_geo_breakdown(
13        &self,
14        start: DateTime<Utc>,
15        end: DateTime<Utc>,
16        limit: i64,
17        engaged_only: bool,
18    ) -> Result<Vec<GeoRow>> {
19        if engaged_only {
20            sqlx::query_as!(
21                GeoRow,
22                r#"
23                SELECT
24                    COALESCE(country, 'Unknown') as "country",
25                    COUNT(*)::bigint as "count!"
26                FROM v_engaged_traffic
27                WHERE started_at >= $1 AND started_at < $2
28                GROUP BY country
29                ORDER BY COUNT(*) DESC
30                LIMIT $3
31                "#,
32                start,
33                end,
34                limit
35            )
36            .fetch_all(&*self.pool)
37            .await
38            .map_err(Into::into)
39        } else {
40            sqlx::query_as!(
41                GeoRow,
42                r#"
43                SELECT
44                    COALESCE(country, 'Unknown') as "country",
45                    COUNT(*)::bigint as "count!"
46                FROM v_clean_traffic
47                WHERE started_at >= $1 AND started_at < $2
48                GROUP BY country
49                ORDER BY COUNT(*) DESC
50                LIMIT $3
51                "#,
52                start,
53                end,
54                limit
55            )
56            .fetch_all(&*self.pool)
57            .await
58            .map_err(Into::into)
59        }
60    }
61
62    pub async fn get_device_breakdown(
63        &self,
64        start: DateTime<Utc>,
65        end: DateTime<Utc>,
66        limit: i64,
67        engaged_only: bool,
68    ) -> Result<Vec<DeviceRow>> {
69        if engaged_only {
70            sqlx::query_as!(
71                DeviceRow,
72                r#"
73                SELECT
74                    COALESCE(device_type, 'unknown') as "device",
75                    COALESCE(browser, 'unknown') as "browser",
76                    COUNT(*)::bigint as "count!"
77                FROM v_engaged_traffic
78                WHERE started_at >= $1 AND started_at < $2
79                GROUP BY device_type, browser
80                ORDER BY COUNT(*) DESC
81                LIMIT $3
82                "#,
83                start,
84                end,
85                limit
86            )
87            .fetch_all(&*self.pool)
88            .await
89            .map_err(Into::into)
90        } else {
91            sqlx::query_as!(
92                DeviceRow,
93                r#"
94                SELECT
95                    COALESCE(device_type, 'unknown') as "device",
96                    COALESCE(browser, 'unknown') as "browser",
97                    COUNT(*)::bigint as "count!"
98                FROM v_clean_traffic
99                WHERE started_at >= $1 AND started_at < $2
100                GROUP BY device_type, browser
101                ORDER BY COUNT(*) DESC
102                LIMIT $3
103                "#,
104                start,
105                end,
106                limit
107            )
108            .fetch_all(&*self.pool)
109            .await
110            .map_err(Into::into)
111        }
112    }
113
114    pub async fn get_bot_totals(
115        &self,
116        start: DateTime<Utc>,
117        end: DateTime<Utc>,
118    ) -> Result<BotTotalsRow> {
119        sqlx::query_as!(
120            BotTotalsRow,
121            r#"
122            SELECT
123                COUNT(*) FILTER (WHERE is_bot = false AND is_ai_crawler = false AND is_scanner = false AND is_behavioral_bot = false AND landing_page IS NOT NULL AND request_count > 0)::bigint as "human!",
124                COUNT(*) FILTER (WHERE is_bot = false AND is_ai_crawler = false AND is_scanner = false AND is_behavioral_bot = false AND (landing_page IS NULL OR request_count = 0))::bigint as "ghost!",
125                COUNT(*) FILTER (WHERE is_bot = true OR is_ai_crawler = true OR is_scanner = true OR is_behavioral_bot = true)::bigint as "bot!"
126            FROM user_sessions
127            WHERE started_at >= $1 AND started_at < $2
128            "#,
129            start,
130            end
131        )
132        .fetch_one(&*self.pool)
133        .await
134        .map_err(Into::into)
135    }
136
137    pub async fn get_bot_breakdown(
138        &self,
139        start: DateTime<Utc>,
140        end: DateTime<Utc>,
141    ) -> Result<Vec<BotTypeRow>> {
142        sqlx::query_as!(
143            BotTypeRow,
144            r#"
145            SELECT
146                bot_type as "bot_type",
147                COUNT(*)::bigint as "count!"
148            FROM v_bot_sessions
149            WHERE started_at >= $1 AND started_at < $2
150            GROUP BY 1
151            ORDER BY COUNT(*) DESC
152            "#,
153            start,
154            end
155        )
156        .fetch_all(&*self.pool)
157        .await
158        .map_err(Into::into)
159    }
160}
systemprompt_analytics/repository/traffic/audience.rs

systemprompt_analytics/repository/traffic/
audience.rs
