Self-hostable website analytics on Django: a straightforward collector API, dashboards, a world map, and PDF reports.
analyticsdjangodockerhandcodedpythonself-hostedsqliteviteweb-analytics
1from django.db.models import Avg, Count, FloatField, Q
2from django.db.models.functions import Cast, TruncDate
3from django.utils import timezone
4
5from .constants import BUILT_IN_EVENTS
6
7
8def human_events(events):
9 """Exclude bot-tagged events from a queryset.
10
11 SQLite's JSON NOT-EQUAL doesn't match rows where the key is missing, so
12 `exclude(data__is_bot=True)` drops everything. `has_key` matches only rows
13 that contain the key — and we only ever set is_bot when the UA is a bot.
14 """
15 return events.exclude(data__has_key="is_bot")
16
17
18def total_live_users(property_obj):
19 """
20 Total unique user_ids seen in the last 30 minutes.
21 """
22 return (
23 human_events(property_obj.events)
24 .filter(created_at__gte=timezone.now() - timezone.timedelta(minutes=30))
25 .exclude(data__user_id__isnull=True)
26 .values("data__user_id")
27 .distinct()
28 .count()
29 )
30
31
32# Cap time-on-page to filter out idle-tab outliers (left open for hours).
33# < 1s is likely bot/instant-exit; > 30min is almost certainly idle.
34TIME_ON_PAGE_MIN_S = 1
35TIME_ON_PAGE_MAX_S = 30 * 60
36
37
38def _pct_change(current, previous):
39 if not previous:
40 return 0
41 return round((current - previous) / previous * 100)
42
43
44def _event_counts(qs):
45 """Single-aggregate pass for the five built-in event counts + total."""
46 return qs.aggregate(
47 session_start=Count("id", filter=Q(event="session_start")),
48 page_view=Count("id", filter=Q(event="page_view")),
49 click=Count("id", filter=Q(event="click")),
50 scroll=Count("id", filter=Q(event="scroll")),
51 total=Count("id"),
52 )
53
54
55def _engaged_users(qs, session_starts):
56 if not session_starts:
57 return 0
58 engaged = (
59 qs.exclude(data__user_id__isnull=True)
60 .values("data__user_id")
61 .annotate(c=Count("id"))
62 .filter(c__gte=10)
63 .count()
64 )
65 return round(engaged / session_starts * 100, 2)
66
67
68def _avg_time_on_page(qs):
69 try:
70 avg = (
71 qs.filter(event="page_leave")
72 .annotate(time_on_page_s=Cast("data__time_on_page", FloatField()) / 1000)
73 .filter(
74 time_on_page_s__gte=TIME_ON_PAGE_MIN_S,
75 time_on_page_s__lte=TIME_ON_PAGE_MAX_S,
76 )
77 .aggregate(avg=Avg("time_on_page_s"))["avg"]
78 )
79 return round(avg, 2) if avg is not None else 0
80 except TypeError:
81 return 0
82
83
84def standard_event_cards(events_filtered, events_filtered_prev):
85 """Standard metric cards. Two aggregate queries plus engagement/time-on-page helpers."""
86 cur = _event_counts(events_filtered)
87 prev = _event_counts(events_filtered_prev)
88
89 cards = [
90 {
91 "name": "Total session starts",
92 "value": cur["session_start"],
93 "percent_change": _pct_change(cur["session_start"], prev["session_start"]),
94 "help_text": "Unique users visiting your site for your selected date range.",
95 },
96 {
97 "name": "Total page views",
98 "value": cur["page_view"],
99 "percent_change": _pct_change(cur["page_view"], prev["page_view"]),
100 "help_text": "Total pages viewed for your selected date range.",
101 },
102 {
103 "name": "Total clicks",
104 "value": cur["click"],
105 "percent_change": _pct_change(cur["click"], prev["click"]),
106 "help_text": "Total clicks users made on all your pages for your selected date range.",
107 },
108 {
109 "name": "Total scrolls",
110 "value": cur["scroll"],
111 "percent_change": _pct_change(cur["scroll"], prev["scroll"]),
112 "help_text": "Total scrolls users made on all your pages for your selected date range.",
113 },
114 {
115 "name": "Total events",
116 "value": cur["total"],
117 "percent_change": _pct_change(cur["total"], prev["total"]),
118 "help_text": "All events for your selected date range, including custom events.",
119 },
120 ]
121
122 engagement_cur = _engaged_users(events_filtered, cur["session_start"])
123 engagement_prev = _engaged_users(events_filtered_prev, prev["session_start"])
124 cards.append({
125 "name": "Total user engagement",
126 "value": f"{engagement_cur}%",
127 "percent_change": _pct_change(engagement_cur, engagement_prev),
128 "help_text": "An engaged user is a user more than 10 events collected for your selected date range.",
129 })
130
131 time_cur = _avg_time_on_page(events_filtered)
132 time_prev = _avg_time_on_page(events_filtered_prev)
133 cards.append({
134 "name": "Avg. time on page",
135 "value": f"{time_cur}s",
136 "percent_change": _pct_change(time_cur, time_prev),
137 "help_text": "Average time a user spends on each page. Sessions over 30 minutes are excluded as idle.",
138 })
139
140 return cards
141
142
143def custom_event_cards(property_obj, events_filtered, events_filtered_prev):
144 """Returns (cards, custom_events). Custom events are non-built-in event names."""
145 custom_events = list(
146 human_events(property_obj.events)
147 .exclude(event__in=BUILT_IN_EVENTS)
148 .values("event")
149 .distinct()
150 .order_by("event")
151 )
152
153 active_names = {c["event"] for c in property_obj.custom_cards if c.get("value") is True}
154 for ce in custom_events:
155 ce["active"] = ce["event"] in active_names
156
157 if not active_names:
158 return [], custom_events
159
160 cur = events_filtered.filter(event__in=active_names).values("event").annotate(c=Count("id"))
161 prev = events_filtered_prev.filter(event__in=active_names).values("event").annotate(c=Count("id"))
162 cur_map = {r["event"]: r["c"] for r in cur}
163 prev_map = {r["event"]: r["c"] for r in prev}
164
165 cards = []
166 for ce in custom_events:
167 if ce["event"] not in active_names:
168 continue
169 v = cur_map.get(ce["event"], 0)
170 p = prev_map.get(ce["event"], 0)
171 cards.append({
172 "name": ce["event"],
173 "value": v,
174 "percent_change": _pct_change(v, p),
175 })
176 return cards, custom_events
177
178
179def events_graph(events_filtered, date_end_obj, date_range):
180 """
181 One GROUP BY query for daily counts, then bucket into days/weeks/months
182 in Python. Buckets step backwards from date_end.
183 """
184 rows = (
185 events_filtered.annotate(day=TruncDate("created_at"))
186 .values("day")
187 .annotate(count=Count("id"))
188 )
189 by_day = {r["day"]: r["count"] for r in rows if r["day"]}
190
191 end_date = date_end_obj.date()
192
193 def bucket_sum(start_date, days):
194 return sum(
195 by_day.get(start_date + timezone.timedelta(days=j), 0)
196 for j in range(days)
197 )
198
199 if date_range <= 28:
200 points = [
201 {
202 "label": end_date - timezone.timedelta(days=i),
203 "count": by_day.get(end_date - timezone.timedelta(days=i), 0),
204 }
205 for i in range(date_range)
206 ]
207 elif date_range <= 6 * 28:
208 points = [
209 {
210 "label": end_date - timezone.timedelta(days=7 * w),
211 "count": bucket_sum(end_date - timezone.timedelta(days=7 * w), 7),
212 }
213 for w in range(date_range // 7)
214 ]
215 else:
216 points = [
217 {
218 "label": end_date - timezone.timedelta(days=28 * m),
219 "count": bucket_sum(end_date - timezone.timedelta(days=28 * m), 28),
220 }
221 for m in range(date_range // 28)
222 ]
223
224 points.sort(key=lambda k: k["label"])
225 for p in points:
226 p["label"] = p["label"].strftime("%b %-d")
227 return points
228
229
230def _top_by_key(qs, key, limit=10, event=None):
231 """Generic top-N list for a JSON key with count."""
232 if event is not None:
233 qs = qs.filter(event=event)
234 rows = (
235 qs.exclude(**{f"{key}__isnull": True})
236 .exclude(**{key: ""})
237 .values(key)
238 .annotate(count=Count("id"))
239 .order_by("-count")[:limit]
240 )
241 return [{"label": r[key], "count": r["count"]} for r in rows]
242
243
244def events_by_screen_size(events_filtered, limit=7):
245 rows = (
246 events_filtered.filter(event="session_start")
247 .exclude(data__screen_width__isnull=True)
248 .values("data__screen_width", "data__screen_height")
249 .annotate(count=Count("id"))
250 .order_by("-count")[:limit]
251 )
252 return [
253 {
254 "label": f"{r['data__screen_width']}x{r['data__screen_height']}",
255 "count": r["count"],
256 }
257 for r in rows
258 ]
259
260
261def events_by_device(events_filtered, limit=7):
262 return _top_by_key(events_filtered, "data__device", limit, event="session_start")
263
264
265def events_by_browser(events_filtered, limit=7):
266 return _top_by_key(events_filtered, "data__browser", limit, event="session_start")
267
268
269def events_by_platform(events_filtered, limit=7):
270 return _top_by_key(events_filtered, "data__platform", limit, event="session_start")
271
272
273def events_by_page_url(events_filtered, limit=10):
274 return _top_by_key(events_filtered, "data__url", limit)
275
276
277def page_views_by_page_url(events_filtered, limit=10):
278 return _top_by_key(events_filtered, "data__url", limit, event="page_view")
279
280
281def events_by_custom_event(events_filtered, limit=10):
282 rows = (
283 events_filtered.exclude(event__in=BUILT_IN_EVENTS)
284 .values("event")
285 .annotate(count=Count("id"))
286 .order_by("-count")[:limit]
287 )
288 return [{"label": r["event"], "count": r["count"]} for r in rows]
289
290
291def session_starts_by_referrer(events_filtered, limit=10):
292 return _top_by_key(events_filtered, "data__referrer", limit, event="session_start")
293
294
295def page_views_by_utm(events_filtered, field, limit=10):
296 return _top_by_key(events_filtered, f"data__utm_{field}", limit, event="page_view")
297
298
299def session_starts_by_country(events_filtered):
300 """Sessions grouped by ISO 3166-1 alpha-2 country code."""
301 rows = (
302 events_filtered.filter(event="session_start")
303 .exclude(data__country__isnull=True)
304 .values("data__country")
305 .annotate(count=Count("id"))
306 )
307 return {r["data__country"]: r["count"] for r in rows}
308
309
310def session_starts_by_country_region(events_filtered):
311 """
312 Sessions grouped first by country, then by region within that country.
313
314 Returned shape: {"US": {"CA": 42, "NY": 17}, "DE": {"BY": 9}, ...}.
315 Used by the world map for click-to-drill-down — the whole tree ships
316 with the dashboard so no extra request is needed when a country is
317 selected.
318 """
319 rows = (
320 events_filtered.filter(event="session_start")
321 .exclude(data__country__isnull=True)
322 .exclude(data__region__isnull=True)
323 .values("data__country", "data__region")
324 .annotate(count=Count("id"))
325 )
326 out = {}
327 for r in rows:
328 out.setdefault(r["data__country"], {})[r["data__region"]] = r["count"]
329 return out
330
331
332def bot_traffic(events_all, limit=10):
333 """
334 Bot-only stats for the dashboard's bot card. Takes the unfiltered (bots
335 included) events queryset.
336 """
337 bots = events_all.filter(data__is_bot=True)
338 total = bots.count()
339 if not total:
340 return {"total": 0, "top_bots": [], "top_pages": []}
341 top_bots = list(
342 bots.exclude(data__bot_name__isnull=True)
343 .exclude(data__bot_name="")
344 .values("data__bot_name")
345 .annotate(count=Count("id"))
346 .order_by("-count")[:limit]
347 )
348 top_pages = list(
349 bots.exclude(data__url__isnull=True)
350 .exclude(data__url="")
351 .values("data__url")
352 .annotate(count=Count("id"))
353 .order_by("-count")[:limit]
354 )
355 return {
356 "total": total,
357 "top_bots": [{"label": r["data__bot_name"], "count": r["count"]} for r in top_bots],
358 "top_pages": [{"label": r["data__url"], "count": r["count"]} for r in top_pages],
359 }