Skip to main content

rustango_cms/analytics/
query.rs

1//! Dashboard aggregates over `cms_analytics_event`.
2//!
3//! Built on the ORM's aggregate API (`rustango::core::aggregates`
4//! + `QuerySet::{aggregate,values,annotate}`): typed columns, conditional
5//! `.filter()` aggregates, and `count_distinct`. Results come back as
6//! `Vec<HashMap<String, SqlValue>>` (the shape is dynamic), decoded by alias.
7//!
8//! Date filtering is typed (`created_at.gte(from)`) — which works across
9//! dialects because `created_at` is stored as an RFC3339 timestamp (set at
10//! insert, not via SQLite's space-formatted `CURRENT_TIMESTAMP`). The only
11//! raw query is the per-day time series, whose `GROUP BY <day-bucket>`
12//! expression isn't expressible through the column-based `.values()` builder.
13
14use 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/// A `(label, count)` group row (top pages, referrers, countries, devices).
26#[derive(Serialize, Default)]
27pub struct Bucket {
28    pub label: String,
29    pub count: i64,
30}
31
32/// One point in the per-day time series.
33#[derive(Serialize, Default)]
34pub struct DayPoint {
35    pub day: String,
36    pub pageviews: i64,
37    pub visitors: i64,
38}
39
40/// Everything the dashboard renders for a date range (except `active_now`,
41/// which the handler fills from the in-memory presence gauge).
42#[derive(Serialize, Default)]
43pub struct Metrics {
44    pub unique_visitors: i64,
45    pub pageviews: i64,
46    pub sessions: i64,
47    pub bounce_rate: i64, // percent 0-100
48    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
56// ---- SqlValue decode helpers (aggregate dicts are dynamically typed) ----
57
58fn 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        // SUM over a BIGINT widens to NUMERIC on Postgres and DECIMAL on
66        // MySQL, so a duration total arrives as a Decimal there (#716).
67        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
79/// Top-N `(label, count)` grouped by `col` over pageviews in the range.
80/// `distinct` counts distinct visitors instead of rows (used for countries).
81async 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
116/// Compute all dashboard metrics for `[from, to)`.
117///
118/// # Errors
119/// Driver / SQL failures from the per-day series query. (The ORM aggregate
120/// queries degrade to zeroes/empties on error rather than propagating.)
121pub async fn compute(
122    pool: &Pool,
123    from: DateTime<Utc>,
124    to: DateTime<Utc>,
125) -> Result<Metrics, ExecError> {
126    // 1. Scalars — one aggregate query, conditional `.filter()` per metric.
127    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    // 2. Bounce — a bounce is a single-pageview session whose longest
189    //    engagement is under 10s. Counted in SQL (#653): grouping by session
190    //    and counting in Rust loaded one row per session in the range to
191    //    produce one percentage. COUNT, not SUM, so every backend returns a
192    //    plain integer (SUM is DECIMAL on MySQL, #716).
193    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    // 3. Top-N breakdowns.
228    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    // 4. Per-day series — the one raw query. `DATE()`/`strftime`/`DATE_FORMAT`
236    //    bucketing isn't expressible via the column-based `.values()` builder.
237    //    `created_at` is RFC3339, so the range binds as a plain `DateTime`.
238    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            // `to_char` on a timestamptz formats in the session TimeZone;
248            // pin it to UTC, as MySQL and SQLite already bucket (#744).
249            _ => 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    /// A SUM over BIGINT comes back as a Decimal on Postgres and
293    /// MySQL; reading it as 0 made "avg. time on page" 0s there.
294    #[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}