systemprompt_analytics/repository/traffic/
pages.rs

1//! Page and navigation traffic queries.
2//!
3//! Copyright (c) systemprompt.io — Business Source License 1.1.
4//! See <https://systemprompt.io> for licensing details.
5
6use super::{NavigationQuery, PageQuery, TrafficAnalyticsRepository};
7use crate::Result;
8use crate::models::reporting::{TrafficNavigationRow, TrafficPageRow};
9
10impl TrafficAnalyticsRepository {
11    pub async fn get_pages(&self, query: PageQuery<'_>) -> Result<Vec<TrafficPageRow>> {
12        let PageQuery {
13            start,
14            end,
15            limit,
16            engaged_only,
17            referrer,
18            path_prefix,
19        } = query;
20        if engaged_only {
21            sqlx::query_as!(
22                TrafficPageRow,
23                r#"
24                SELECT
25                    landing_page as "page",
26                    COALESCE(referrer_source, 'direct') as "source",
27                    COUNT(*)::bigint as "count!"
28                FROM v_engaged_traffic
29                WHERE started_at >= $1 AND started_at < $2
30                  AND ($3::text IS NULL OR COALESCE(referrer_source, 'direct') = $3)
31                  AND ($4::text IS NULL OR landing_page LIKE $4 || '%')
32                GROUP BY landing_page, referrer_source
33                ORDER BY COUNT(*) DESC
34                LIMIT $5
35                "#,
36                start,
37                end,
38                referrer,
39                path_prefix,
40                limit
41            )
42            .fetch_all(&*self.pool)
43            .await
44            .map_err(Into::into)
45        } else {
46            sqlx::query_as!(
47                TrafficPageRow,
48                r#"
49                SELECT
50                    landing_page as "page",
51                    COALESCE(referrer_source, 'direct') as "source",
52                    COUNT(*)::bigint as "count!"
53                FROM v_clean_traffic
54                WHERE started_at >= $1 AND started_at < $2
55                  AND landing_page IS NOT NULL
56                  AND ($3::text IS NULL OR COALESCE(referrer_source, 'direct') = $3)
57                  AND ($4::text IS NULL OR landing_page LIKE $4 || '%')
58                GROUP BY landing_page, referrer_source
59                ORDER BY COUNT(*) DESC
60                LIMIT $5
61                "#,
62                start,
63                end,
64                referrer,
65                path_prefix,
66                limit
67            )
68            .fetch_all(&*self.pool)
69            .await
70            .map_err(Into::into)
71        }
72    }
73
74    pub async fn get_navigation(
75        &self,
76        query: NavigationQuery<'_>,
77    ) -> Result<Vec<TrafficNavigationRow>> {
78        let NavigationQuery {
79            start,
80            end,
81            limit,
82            path_prefix,
83            internal_only,
84        } = query;
85        if internal_only {
86            sqlx::query_as!(
87                TrafficNavigationRow,
88                r#"
89                SELECT
90                    endpoint as "from_path",
91                    event_data->>'target_url' as "to_path",
92                    COUNT(*)::bigint as "count!"
93                FROM analytics_events
94                WHERE event_type = 'link_click'
95                  AND timestamp >= $1 AND timestamp < $2
96                  AND ($3::text IS NULL OR event_data->>'target_url' LIKE $3 || '%')
97                  AND COALESCE(event_data->>'is_external', 'false') <> 'true'
98                GROUP BY endpoint, event_data->>'target_url'
99                ORDER BY COUNT(*) DESC
100                LIMIT $4
101                "#,
102                start,
103                end,
104                path_prefix,
105                limit
106            )
107            .fetch_all(&*self.pool)
108            .await
109            .map_err(Into::into)
110        } else {
111            sqlx::query_as!(
112                TrafficNavigationRow,
113                r#"
114                SELECT
115                    endpoint as "from_path",
116                    event_data->>'target_url' as "to_path",
117                    COUNT(*)::bigint as "count!"
118                FROM analytics_events
119                WHERE event_type = 'link_click'
120                  AND timestamp >= $1 AND timestamp < $2
121                  AND ($3::text IS NULL OR event_data->>'target_url' LIKE $3 || '%')
122                GROUP BY endpoint, event_data->>'target_url'
123                ORDER BY COUNT(*) DESC
124                LIMIT $4
125                "#,
126                start,
127                end,
128                path_prefix,
129                limit
130            )
131            .fetch_all(&*self.pool)
132            .await
133            .map_err(Into::into)
134        }
135    }
136}
systemprompt_analytics/repository/traffic/pages.rs

systemprompt_analytics/repository/traffic/
pages.rs
