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 report_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 report_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                    NULLIF(endpoint, '') as "from_path",
91                    NULLIF(target_url, '') as "to_path",
92                    SUM(clicks)::bigint as "count!"
93                FROM report_link_click_hourly
94                WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2
95                  AND ($3::text IS NULL OR target_url LIKE $3 || '%')
96                  AND NOT is_external
97                GROUP BY endpoint, target_url
98                ORDER BY SUM(clicks) DESC
99                LIMIT $4
100                "#,
101                start,
102                end,
103                path_prefix,
104                limit
105            )
106            .fetch_all(&*self.pool)
107            .await
108            .map_err(Into::into)
109        } else {
110            sqlx::query_as!(
111                TrafficNavigationRow,
112                r#"
113                SELECT
114                    NULLIF(endpoint, '') as "from_path",
115                    NULLIF(target_url, '') as "to_path",
116                    SUM(clicks)::bigint as "count!"
117                FROM report_link_click_hourly
118                WHERE bucket >= date_trunc('hour', $1::timestamptz, 'UTC') AND bucket < $2
119                  AND ($3::text IS NULL OR target_url LIKE $3 || '%')
120                GROUP BY endpoint, target_url
121                ORDER BY SUM(clicks) DESC
122                LIMIT $4
123                "#,
124                start,
125                end,
126                path_prefix,
127                limit
128            )
129            .fetch_all(&*self.pool)
130            .await
131            .map_err(Into::into)
132        }
133    }
134}
systemprompt_analytics/repository/traffic/pages.rs

systemprompt_analytics/repository/traffic/
pages.rs
