1use std::collections::HashMap;
15
16use chrono::{DateTime, Utc};
17use rustango::core::aggregates::{count_all, count_distinct, sum};
18use rustango::core::Column as _;
19use rustango::core::SqlValue;
20use rustango::sql::{ExecError, Pool};
21use serde::Serialize;
22
23use super::model::AnalyticsEvent;
24
25#[derive(Serialize, Default)]
27pub struct Bucket {
28 pub label: String,
29 pub count: i64,
30}
31
32#[derive(Serialize, Default)]
34pub struct DayPoint {
35 pub day: String,
36 pub pageviews: i64,
37 pub visitors: i64,
38}
39
40#[derive(Serialize, Default)]
43pub struct Metrics {
44 pub unique_visitors: i64,
45 pub pageviews: i64,
46 pub sessions: i64,
47 pub bounce_rate: i64, pub avg_seconds: i64,
49 pub top_pages: Vec<Bucket>,
50 pub top_referrers: Vec<Bucket>,
51 pub countries: Vec<Bucket>,
52 pub devices: Vec<Bucket>,
53 pub series: Vec<DayPoint>,
54}
55
56fn as_i64(v: Option<&SqlValue>) -> i64 {
59 match v {
60 Some(SqlValue::I64(n)) => *n,
61 Some(SqlValue::I32(n)) => i64::from(*n),
62 Some(SqlValue::I16(n)) => i64::from(*n),
63 Some(SqlValue::F64(n)) => *n as i64,
64 Some(SqlValue::F32(n)) => f64::from(*n) as i64,
65 Some(SqlValue::Decimal(d)) => d.trunc().to_string().parse().unwrap_or(0),
68 _ => 0,
69 }
70}
71
72fn as_string(v: Option<&SqlValue>) -> String {
73 match v {
74 Some(SqlValue::String(s)) => s.clone(),
75 _ => String::new(),
76 }
77}
78
79async fn top_n(
82 pool: &Pool,
83 col: &'static str,
84 distinct: Option<&'static str>,
85 from: DateTime<Utc>,
86 to: DateTime<Utc>,
87 limit: i64,
88) -> Vec<Bucket> {
89 let agg = match distinct {
90 Some(dc) => count_distinct(dc),
91 None => count_all(),
92 };
93 let Ok(q) = AnalyticsEvent::objects()
94 .where_(AnalyticsEvent::created_at.gte(from))
95 .where_(AnalyticsEvent::created_at.lt(to))
96 .where_(AnalyticsEvent::event_type.eq("pageview".to_owned()))
97 .values(&[col])
98 .annotate("c", agg.into())
99 .order_by(&[("c", true)])
100 .limit(limit)
101 .compile()
102 else {
103 return Vec::new();
104 };
105 let rows = rustango::sql::fetch_aggregate_dict(pool, &q)
106 .await
107 .unwrap_or_default();
108 rows.into_iter()
109 .map(|r| Bucket {
110 label: as_string(r.get(col)),
111 count: as_i64(r.get("c")),
112 })
113 .collect()
114}
115
116pub async fn compute(
122 pool: &Pool,
123 from: DateTime<Utc>,
124 to: DateTime<Utc>,
125) -> Result<Metrics, ExecError> {
126 let (unique_visitors, pageviews, sessions, dur_sum, eng_count) = {
128 let compiled = AnalyticsEvent::objects()
129 .where_(AnalyticsEvent::created_at.gte(from))
130 .where_(AnalyticsEvent::created_at.lt(to))
131 .aggregate()
132 .values(&[])
133 .annotate(
134 "uv",
135 count_distinct("visitor_id")
136 .filter(AnalyticsEvent::event_type.eq("pageview".to_owned()))
137 .into(),
138 )
139 .annotate(
140 "pv",
141 count_all()
142 .filter(AnalyticsEvent::event_type.eq("pageview".to_owned()))
143 .into(),
144 )
145 .annotate(
146 "sess",
147 count_distinct("session_id")
148 .filter(AnalyticsEvent::event_type.eq("pageview".to_owned()))
149 .into(),
150 )
151 .annotate(
152 "dur",
153 sum("duration_ms")
154 .filter(AnalyticsEvent::event_type.eq("engagement".to_owned()))
155 .default(0_i64)
156 .into(),
157 )
158 .annotate(
159 "engn",
160 count_all()
161 .filter(AnalyticsEvent::event_type.eq("engagement".to_owned()))
162 .into(),
163 )
164 .compile();
165 let row = match compiled {
166 Ok(q) => rustango::sql::fetch_aggregate_dict(pool, &q)
167 .await
168 .unwrap_or_default()
169 .into_iter()
170 .next()
171 .unwrap_or_default(),
172 Err(_) => HashMap::new(),
173 };
174 (
175 as_i64(row.get("uv")),
176 as_i64(row.get("pv")),
177 as_i64(row.get("sess")),
178 as_i64(row.get("dur")),
179 as_i64(row.get("engn")),
180 )
181 };
182 let avg_seconds = if eng_count > 0 {
183 dur_sum / eng_count / 1000
184 } else {
185 0
186 };
187
188 let (bounced, total_sessions) = {
194 let d = pool.dialect();
195 let t = d.quote_ident("cms_analytics_event");
196 let created = d.quote_ident("created_at");
197 let session = d.quote_ident("session_id");
198 let etype = d.quote_ident("event_type");
199 let dur = d.quote_ident("duration_ms");
200 let sql = format!(
201 "SELECT COUNT(*) AS total, \
202 COUNT(CASE WHEN s.pv = 1 AND COALESCE(s.maxdur, 0) < 10000 THEN 1 END) AS bounced \
203 FROM (SELECT {session}, \
204 COUNT(CASE WHEN {etype} = 'pageview' THEN 1 END) AS pv, \
205 MAX(CASE WHEN {etype} = 'engagement' THEN {dur} END) AS maxdur \
206 FROM {t} WHERE {created} >= {p1} AND {created} < {p2} \
207 GROUP BY {session}) s",
208 p1 = d.placeholder(1),
209 p2 = d.placeholder(2),
210 );
211 rustango::sql::raw_query_pool::<(i64, i64)>(
212 &sql,
213 vec![SqlValue::DateTime(from), SqlValue::DateTime(to)],
214 pool,
215 )
216 .await
217 .ok()
218 .and_then(|rows| rows.into_iter().next())
219 .map_or((0, 0), |(total, bounced)| (bounced, total))
220 };
221 let bounce_rate = if total_sessions > 0 {
222 bounced * 100 / total_sessions
223 } else {
224 0
225 };
226
227 let top_pages = top_n(pool, "path", None, from, to, 20).await;
229 let mut top_referrers = top_n(pool, "referrer_host", None, from, to, 30).await;
230 top_referrers.retain(|b| !b.label.is_empty());
231 top_referrers.truncate(20);
232 let countries = top_n(pool, "country", Some("visitor_id"), from, to, 30).await;
233 let devices = top_n(pool, "device", None, from, to, 10).await;
234
235 let series = {
239 let d = pool.dialect();
240 let t = d.quote_ident("cms_analytics_event");
241 let created = d.quote_ident("created_at");
242 let visitor = d.quote_ident("visitor_id");
243 let etype = d.quote_ident("event_type");
244 let day = match d.name() {
245 "sqlite" => format!("strftime('%Y-%m-%d', {created})"),
246 "mysql" => format!("DATE_FORMAT({created}, '%Y-%m-%d')"),
247 _ => format!("to_char({created} AT TIME ZONE 'UTC', 'YYYY-MM-DD')"),
250 };
251 let sql = format!(
252 "SELECT {day} AS d, COUNT(*) AS pv, COUNT(DISTINCT {visitor}) AS uv \
253 FROM {t} WHERE {created} >= {p1} AND {created} < {p2} AND {etype} = 'pageview' \
254 GROUP BY {day} ORDER BY d ASC",
255 p1 = d.placeholder(1),
256 p2 = d.placeholder(2),
257 );
258 rustango::sql::raw_query_pool::<(String, i64, i64)>(
259 &sql,
260 vec![SqlValue::DateTime(from), SqlValue::DateTime(to)],
261 pool,
262 )
263 .await
264 .unwrap_or_default()
265 .into_iter()
266 .map(|(day, pageviews, visitors)| DayPoint {
267 day,
268 pageviews,
269 visitors,
270 })
271 .collect()
272 };
273
274 Ok(Metrics {
275 unique_visitors,
276 pageviews,
277 sessions,
278 bounce_rate,
279 avg_seconds,
280 top_pages,
281 top_referrers,
282 countries,
283 devices,
284 series,
285 })
286}
287
288#[cfg(test)]
289mod tests {
290 use super::*;
291
292 #[test]
295 fn a_decimal_sum_reads_as_its_integer_value() {
296 let d: rustango::__rust_decimal::Decimal = "3000000.00".parse().expect("decimal");
297 assert_eq!(as_i64(Some(&SqlValue::Decimal(d))), 3_000_000);
298 assert_eq!(as_i64(Some(&SqlValue::I64(7))), 7);
299 assert_eq!(as_i64(None), 0);
300 }
301}