Runs live, versioned Python application objects on one VM, with a suite of apps: projects, notes, tasks, files and more.

0 downloads

dbbasic-object-server/object_conversions.py
Download zip
Clone
Clone with git
git clone https://gitstable.com/git/askrobots/dbbasic-object-server.git
HTTPS
https://gitstable.com/git/askrobots/dbbasic-object-server.git
SSH
ssh://git@gitstable.com:2222/askrobots/dbbasic-object-server.git

Cloning needs no sign-in. Pushing over HTTPS asks for your username and an access token. SSH needs your key under SSH keys.

danq9 signup: the person chooses the password and name on the confirm page 83c7aa6 2 weeks, 5 days ago History
object_conversions.py · 24.8 KB · 622 lines
1"""Goals, funnels and the question a page view cannot answer on its own.
2
3`page_views` says what was requested. It cannot say whether any of it
4WORKED -- whether the person who read the pitch on Monday came back on
5Thursday and bought something. Two things were missing for that, and this
6module is the half that folds them:
7
8* a **thread** -- the visitor cookie (`object_analytics`), stamped into
9 `page_views.session_id`, which is what makes two visits one person
10 rather than two strangers; and
11* a **goal** -- one `conversions` row written by whichever app owns the
12 transition worth counting (`packages/app-analytics/objects/system/
13 record_conversion.py`).
14
15Everything here is pure: rows in, numbers out, no data directory, no I/O.
16The same posture object_visitors takes, and for the same reason -- a fold
17you can call with a list of dicts is a fold you can argue with in a test.
18
19## Three refusals, because the obvious version of each one lies
20
21**A funnel says how much of itself is guesswork.** Rows written before
22the cookie existed, and rows from anyone who sent Do Not Track, carry no
23token; the only thread left for those is the IP address, which is not a
24person (an office is one, a phone on the train is three). Stitching by IP
25is better than dropping the rows, and pretending the result is as solid
26as a cookie-threaded funnel is not. So `funnel` returns the IP-stitched
27fraction as DATA, next to the numbers it qualifies, where a caller has to
28decide what to do with it rather than being able to not notice it.
29
30**Returning visitors are a FLOOR, never a census.** A cleared cookie is a
31new visitor. A second browser is a second visitor. A phone is a third.
32Every one of those errors points the same way -- undercounting -- which
33is the right direction for a number to be wrong in, and it is still
34wrong. `returning_visitors` returns `floor: True` and the caveat text
35itself, so a surface cannot render the number without being handed the
36caveat to render beside it.
37
38**Time to conversion admits what aged out.** `page_views` is bounded by
39days AND rows (docs/analytics.md), so a visitor whose first visit fell
40off the end of retention has a conversion with no beginning. That is
41reported as its own count -- not silently dropped, which would bias the
42median toward fast conversions, and not counted as zero days, which would
43be a lie in the same direction but louder.
44
45## The join that must never happen
46
47`conversions` has a `session_id` column and a `user_id` column. A row
48carrying both correlates an anonymous browsing thread with a named
49account, retroactively, for somebody who was never asked --
50docs/analytics.md's cookie rule 4, the move that turns analytics into
51surveillance. `build_conversion` therefore writes at most ONE of them and
52prefers the anonymous one. See its docstring before changing it.
53"""
54
55from __future__ import annotations
56
57import json
58import statistics
59from collections import defaultdict
60from datetime import datetime, timedelta, timezone
61from typing import Any, Iterable, Sequence
62
63CONVERSIONS_COLLECTION = "conversions"
64
65# Where a shop describes its own funnel, so that having one is a settings
66# row rather than a deploy. A JSON list; see parse_funnel_steps.
67FUNNEL_STEPS_SETTING = "analytics.funnel_steps"
68
69# Threads stitched by address rather than by cookie are prefixed, so a
70# fold can always tell the two apart afterwards without a second pass and
71# a caller can never mistake one for the other.
72IP_THREAD_PREFIX = "ip:"
73
74STEP_PATH, STEP_EVENT = "path", "event"
75
76FLOOR_CAVEAT = (
77 "A floor, never a census: a cleared cookie is a new visitor, a second "
78 "browser is a second visitor, and a phone is a third. This undercounts, "
79 "which is the right direction for it to be wrong in."
80)
81
82AGED_OUT_CAVEAT = (
83 "page_views is bounded by age and by row count, so a visitor whose "
84 "first visit has already aged out has a conversion with no beginning. "
85 "Those are counted separately rather than dropped -- dropping them "
86 "would bias the median toward fast conversions."
87)
88
89
90# --- small readers -----------------------------------------------------------
91
92def _text(value: Any) -> str:
93 return str(value if value is not None else "").strip()
94
95
96def _day(stamp: Any) -> str:
97 text = _text(stamp)
98 return text[:10] if len(text) >= 10 else ""
99
100
101def _moment(stamp: Any) -> datetime | None:
102 """An aware datetime from whatever the record layer wrote, or None.
103
104 Never raises on junk: a malformed timestamp is one row that cannot
105 take part in a duration, not a report that fails to render.
106 """
107 text = _text(stamp).replace(" ", "T")
108 if text.endswith("Z"):
109 text = text[:-1] + "+00:00"
110 try:
111 parsed = datetime.fromisoformat(text)
112 except ValueError:
113 return None
114 return parsed if parsed.tzinfo else parsed.replace(tzinfo=timezone.utc)
115
116
117def parse_metadata(value: Any) -> dict[str, Any]:
118 """A conversion's metadata blob as a dict. A blob that is not a JSON
119 object reads as empty rather than as an error -- it is a free-text
120 column and a report must survive whatever ended up in it."""
121 if isinstance(value, dict):
122 return value
123 text = _text(value)
124 if not text:
125 return {}
126 try:
127 parsed = json.loads(text)
128 except (TypeError, ValueError):
129 return {}
130 return parsed if isinstance(parsed, dict) else {}
131
132
133def source_ref(collection: str, record_id: str) -> str:
134 """The provenance string a conversion is deduplicated on:
135 `orders/ord-1`. Same shape as every other marker in this house."""
136 return f"{_text(collection)}/{_text(record_id)}"
137
138
139# --- writing one ---------------------------------------------------------------
140
141def build_conversion(
142 *, event_type: str, source: str = "", session_id: str = "",
143 user_id: str = "", metadata: dict[str, Any] | None = None,
144) -> dict[str, str]:
145 """The `conversions` record one goal implies. Pure; created_at is
146 stamped by the record layer on write.
147
148 **At most one of `session_id` and `user_id` is written, and the
149 anonymous one wins.** Both columns exist and it is one line to fill
150 them both, which is exactly why this refuses: a row holding an opaque
151 visitor token AND an account id de-anonymises every page view that
152 token ever made, retroactively, for a person who was never asked.
153 docs/analytics.md, cookie rule 4. The anonymous one wins because it is
154 the one a funnel needs -- the account id is recoverable from the
155 record the conversion points at, if somebody with a reason to look
156 goes and looks, which is a different act from having it pre-joined in
157 an analytics table.
158
159 `source` is the provenance marker (`orders/ord-1`) and lives inside
160 metadata rather than in a column of its own, because the schema is
161 fixed and correct and a private field for one handler's bookkeeping
162 would be a worse trade than a documented key in the blob.
163 """
164 blob = dict(metadata or {})
165 if source:
166 blob["source"] = _text(source)
167 return {
168 "event_type": _text(event_type),
169 "session_id": _text(session_id),
170 # Never both. See the docstring; the enforcement is here rather
171 # than at the call sites because it has to survive a call site
172 # written by somebody who has not read the docstring.
173 "user_id": "" if _text(session_id) else _text(user_id),
174 "metadata": json.dumps(blob, sort_keys=True) if blob else "",
175 }
176
177
178def recorded_sources(rows: Iterable[dict]) -> set[tuple[str, str]]:
179 """Every (event_type, source) already in `conversions`.
180
181 Read once and folded into a set rather than scanned per candidate:
182 conversions is append storage and grows forever, and re-reading it
183 once per goal is the shape that makes a handler quietly quadratic.
184 """
185 seen: set[tuple[str, str]] = set()
186 for row in rows:
187 source = _text(parse_metadata(row.get("metadata")).get("source"))
188 if source:
189 seen.add((_text(row.get("event_type")), source))
190 return seen
191
192
193def already_recorded(rows: Iterable[dict], event_type: str, source: str) -> bool:
194 return (_text(event_type), _text(source)) in recorded_sources(rows)
195
196
197# --- reading them back ---------------------------------------------------------
198
199def by_event_type(
200 conversions: Iterable[dict], *, window_days: int = 0,
201 now: datetime | None = None,
202) -> list[dict[str, Any]]:
203 """Conversions grouped by goal, newest activity first.
204
205 `threaded` is how many of each kind can take part in a funnel at all.
206 It is reported per event type rather than as one total because the
207 answer differs sharply by goal: a conversion recorded by a public page
208 that saw the cookie is threaded, and one recorded by a back-office
209 transition (an order confirmed by staff two days later) never can be.
210 """
211 now = now or datetime.now(timezone.utc)
212 earliest = ((now - timedelta(days=window_days - 1)).strftime("%Y-%m-%d")
213 if window_days > 0 else "")
214
215 counts: dict[str, dict[str, Any]] = {}
216 for row in conversions:
217 event_type = _text(row.get("event_type"))
218 if not event_type:
219 continue
220 day = _day(row.get("created_at"))
221 if earliest and day and day < earliest:
222 continue
223 entry = counts.setdefault(event_type, {
224 "event_type": event_type, "count": 0, "threaded": 0,
225 "first": "", "last": "",
226 })
227 entry["count"] += 1
228 if _text(row.get("session_id")):
229 entry["threaded"] += 1
230 stamp = _text(row.get("created_at"))
231 if stamp:
232 if not entry["first"] or stamp < entry["first"]:
233 entry["first"] = stamp
234 if stamp > entry["last"]:
235 entry["last"] = stamp
236 return sorted(counts.values(),
237 key=lambda row: (-row["count"], row["event_type"]))
238
239
240# --- the funnel ------------------------------------------------------------------
241
242def parse_funnel_steps(raw: Any) -> tuple[list[dict[str, str]], str]:
243 """(steps, error) from the `analytics.funnel_steps` setting.
244
245 Returns the error as a STRING rather than raising, because the caller
246 is a page: an operator who typed malformed JSON into a settings row
247 needs to be told which row and why, on the screen where they typed it,
248 not to be shown a 500 or -- worse -- an empty funnel that looks like
249 nobody converted.
250 """
251 if raw is None or (isinstance(raw, str) and not raw.strip()):
252 return [], ""
253 if isinstance(raw, str):
254 try:
255 raw = json.loads(raw)
256 except (TypeError, ValueError) as exc:
257 return [], f"not valid JSON ({exc})"
258 if not isinstance(raw, (list, tuple)):
259 return [], "expected a JSON list of steps"
260 try:
261 return normalize_steps(raw), ""
262 except ValueError as exc:
263 return [], str(exc)
264
265
266def normalize_steps(steps: Sequence[Any]) -> list[dict[str, str]]:
267 """Ordered step definitions in one shape: {label, kind, match}.
268
269 Accepts the two spellings an operator would actually write:
270
271 ["/shop", "/checkout", "order_confirmed"]
272 [{"label": "Browsed", "path": "/shop"},
273 {"label": "Bought", "event_type": "order_confirmed"}]
274
275 A bare string beginning with `/` is a PATH PREFIX and anything else is
276 an event_type, which is unambiguous because every path starts with a
277 slash and no event type may.
278 """
279 out: list[dict[str, str]] = []
280 for raw in steps or ():
281 if isinstance(raw, str):
282 match = raw.strip()
283 if not match:
284 continue
285 kind = STEP_PATH if match.startswith("/") else STEP_EVENT
286 out.append({"label": match, "kind": kind, "match": match})
287 continue
288 if not isinstance(raw, dict):
289 raise ValueError(f"a step must be a string or an object, got {type(raw).__name__}")
290 # Idempotent on its own output. A caller that validated the steps
291 # once (a page reporting a bad settings row before it renders
292 # anything) then hands them to `funnel`, which normalizes again --
293 # and a normalizer that rejects its own output turns that
294 # perfectly reasonable sequence into a 500.
295 if _text(raw.get("kind")) in (STEP_PATH, STEP_EVENT) and _text(raw.get("match")):
296 out.append({"label": _text(raw.get("label")) or _text(raw["match"]),
297 "kind": _text(raw["kind"]), "match": _text(raw["match"])})
298 continue
299 path = _text(raw.get("path") or raw.get("path_prefix"))
300 event_type = _text(raw.get("event_type") or raw.get("event"))
301 if path and event_type:
302 raise ValueError(
303 f"step {raw.get('label') or path!r} names both a path and an "
304 "event_type; a step is one or the other")
305 if not path and not event_type:
306 raise ValueError("a step needs a `path` or an `event_type`")
307 kind = STEP_PATH if path else STEP_EVENT
308 match = path or event_type
309 out.append({"label": _text(raw.get("label")) or match,
310 "kind": kind, "match": match})
311 return out
312
313
314def _thread_key(row: dict) -> tuple[str, bool]:
315 """(key, stitched_by_ip) for one page view.
316
317 The cookie when there is one; the address when there is not. The
318 fallback is deliberate rather than a purity failure: refusing to
319 thread the cookieless would silently delete every visitor who sent Do
320 Not Track from every funnel, which reports a smaller and cleaner
321 business than the real one.
322 """
323 token = _text(row.get("session_id"))
324 if token:
325 return token, False
326 ip = _text(row.get("ip"))
327 if ip:
328 return IP_THREAD_PREFIX + ip, True
329 return "", False
330
331
332def _matches(step: dict[str, str], event: tuple[str, str, str]) -> bool:
333 _stamp, kind, value = event
334 if kind != step["kind"]:
335 return False
336 if kind == STEP_PATH:
337 return value.startswith(step["match"])
338 return value == step["match"]
339
340
341def funnel(
342 page_views: Iterable[dict], conversions: Iterable[dict],
343 steps: Sequence[Any],
344) -> dict[str, Any]:
345 """How many distinct visitors reached each step, and where they left.
346
347 A step is a path prefix (`/shop`) or an event_type
348 (`order_confirmed`), and the order is the operator's: this is a
349 SEQUENCE, so a visitor counts at step N only if they had already
350 reached step N-1 and then did step N afterwards. That "afterwards" is
351 the difference between a funnel and a Venn diagram -- somebody who
352 bought last week and browsed today did not convert today.
353
354 Threading is by `session_id`, falling back to the IP address for rows
355 that have none, and the result SAYS how much of itself is IP-stitched
356 (`ip_stitched`, `ip_stitched_pct`, and a caveat in `caveats`). A
357 funnel that hides its own uncertainty is the kind of analytics this
358 repo is written against; an office behind one address is one thread
359 that looks like a very decisive shopper.
360
361 Conversions with no session token cannot be attached to any visitor at
362 all. They are counted in `unthreaded_conversions` and named in a
363 caveat rather than quietly dropped -- today that is MOST of them,
364 because a back-office transition (an order confirmed by staff the next
365 morning) has no browser anywhere near it.
366 """
367 steps = normalize_steps(steps)
368 if not steps:
369 return {
370 "configured": False, "steps": [], "entered": 0, "converted": 0,
371 "ip_stitched": 0, "ip_stitched_pct": 0.0,
372 "unthreaded_conversions": 0, "caveats": [],
373 }
374
375 events: dict[str, list[tuple[str, str, str]]] = defaultdict(list)
376 ip_threads: set[str] = set()
377
378 for row in page_views:
379 key, by_ip = _thread_key(row)
380 if not key:
381 continue
382 events[key].append((_text(row.get("created_at")), STEP_PATH,
383 _text(row.get("path")) or "/"))
384 if by_ip:
385 ip_threads.add(key)
386
387 unthreaded_conversions = 0
388 for row in conversions:
389 token = _text(row.get("session_id"))
390 if not token:
391 unthreaded_conversions += 1
392 continue
393 events[token].append((_text(row.get("created_at")), STEP_EVENT,
394 _text(row.get("event_type"))))
395
396 reached: list[set[str]] = [set() for _ in steps]
397 for key, timeline in events.items():
398 timeline.sort(key=lambda event: event[0])
399 cursor = -1
400 for index, step in enumerate(steps):
401 found = None
402 for position in range(cursor + 1, len(timeline)):
403 if _matches(step, timeline[position]):
404 found = position
405 break
406 if found is None:
407 break
408 reached[index].add(key)
409 cursor = found
410
411 entered = len(reached[0])
412 stitched = len(reached[0] & ip_threads)
413 rows: list[dict[str, Any]] = []
414 previous: int | None = None
415 for index, step in enumerate(steps):
416 count = len(reached[index])
417 rows.append({
418 "index": index,
419 "label": step["label"],
420 "kind": step["kind"],
421 "match": step["match"],
422 "visitors": count,
423 "ip_stitched": len(reached[index] & ip_threads),
424 "drop_off": 0 if previous is None else previous - count,
425 "drop_off_pct": (None if not previous
426 else round(100.0 * (previous - count) / previous, 1)),
427 "of_entered_pct": (round(100.0 * count / entered, 1)
428 if entered else 0.0),
429 })
430 previous = count
431
432 caveats: list[str] = []
433 if stitched:
434 caveats.append(
435 f"{stitched} of {entered} visitors in this funnel are stitched by "
436 "IP address rather than by a visitor cookie -- rows written before "
437 "the cookie existed, and everyone who sent Do Not Track. An IP is "
438 "not a person: an office behind one connection is one thread.")
439 if unthreaded_conversions:
440 caveats.append(
441 f"{unthreaded_conversions} conversion(s) carry no visitor token "
442 "and could not be attached to any visitor, so they cannot appear "
443 "in an event step here. A goal recorded by a back-office "
444 "transition has no browser anywhere near it.")
445
446 return {
447 "configured": True,
448 "steps": rows,
449 "entered": entered,
450 "converted": len(reached[-1]),
451 "ip_stitched": stitched,
452 "ip_stitched_pct": (round(100.0 * stitched / entered, 1)
453 if entered else 0.0),
454 "unthreaded_conversions": unthreaded_conversions,
455 "caveats": caveats,
456 }
457
458
459# --- new versus returning ----------------------------------------------------------
460
461def returning_visitors(
462 page_views: Iterable[dict], window_days: int = 30, *,
463 now: datetime | None = None,
464) -> dict[str, Any]:
465 """New versus returning, by visitor token, within the window.
466
467 A token counts as RETURNING when it was seen on more than one calendar
468 day inside the window, or at all before it. Days rather than visits,
469 because a person who reads three pages over lunch has not returned;
470 they are still here.
471
472 **This is a floor and says so in the return value** (`floor: True`,
473 `caveat`), not only in a comment somebody could render without. Every
474 error in it points one way: a cleared cookie, a private window, a
475 second browser and a phone are each counted as a new visitor. It is
476 also blind by design to anyone who refused the cookie -- those page
477 views are counted in `no_token_addresses` instead, so the size of the
478 blind spot is on the same screen as the number it qualifies.
479 """
480 now = now or datetime.now(timezone.utc)
481 earliest = (now - timedelta(days=window_days - 1)).strftime("%Y-%m-%d")
482
483 days_seen: dict[str, set[str]] = defaultdict(set)
484 seen_before_window: set[str] = set()
485 untokened_addresses: set[str] = set()
486
487 for row in page_views:
488 day = _day(row.get("created_at"))
489 if not day:
490 continue
491 token = _text(row.get("session_id"))
492 if not token:
493 if day >= earliest and _text(row.get("ip")):
494 untokened_addresses.add(_text(row.get("ip")))
495 continue
496 if day < earliest:
497 seen_before_window.add(token)
498 else:
499 days_seen[token].add(day)
500
501 new = returning = 0
502 for token, days in days_seen.items():
503 if len(days) > 1 or token in seen_before_window:
504 returning += 1
505 else:
506 new += 1
507
508 counted = new + returning
509 return {
510 "window_days": window_days,
511 "new": new,
512 "returning": returning,
513 "counted": counted,
514 "returning_pct": (round(100.0 * returning / counted, 1)
515 if counted else 0.0),
516 "no_token_addresses": len(untokened_addresses),
517 "floor": True,
518 "caveat": FLOOR_CAVEAT,
519 }
520
521
522# --- time to conversion ------------------------------------------------------------
523
524def time_to_conversion(
525 page_views: Iterable[dict], conversions: Iterable[dict],
526) -> dict[str, Any]:
527 """Days between a visitor's first recorded page and their conversion.
528
529 The number that tells you whether your funnel is a funnel or a queue:
530 a median of zero days is a shop people buy from on arrival, and a
531 median of nine is a decision somebody goes away and thinks about.
532
533 Only cookie-threaded conversions can answer it, and the two ways of
534 not being able to are reported apart, because they mean different
535 things and have different fixes:
536
537 * `unthreaded` -- the conversion carries no visitor token at all
538 (a back-office transition). Nothing about retention would help.
539 * `no_first_view` -- it has a token, and no page view with that token
540 survives. Their first visit aged out of a bounded log, so this
541 conversion has a beginning nobody can see. Counted, never dropped:
542 dropping them biases the median toward fast conversions, since the
543 slowest journeys are exactly the ones whose start ages out first.
544
545 A conversion timestamped BEFORE its visitor's first page view is clock
546 skew or a row edited by hand; it is clamped to zero and counted in
547 `before_first_view` rather than allowed to drag a median negative.
548 """
549 first_seen: dict[str, datetime] = {}
550 for row in page_views:
551 token = _text(row.get("session_id"))
552 moment = _moment(row.get("created_at"))
553 if not token or moment is None:
554 continue
555 if token not in first_seen or moment < first_seen[token]:
556 first_seen[token] = moment
557
558 samples: list[float] = []
559 unthreaded = no_first_view = unreadable = before_first_view = 0
560 for row in conversions:
561 token = _text(row.get("session_id"))
562 if not token:
563 unthreaded += 1
564 continue
565 start = first_seen.get(token)
566 if start is None:
567 no_first_view += 1
568 continue
569 moment = _moment(row.get("created_at"))
570 if moment is None:
571 unreadable += 1
572 continue
573 days = (moment - start).total_seconds() / 86400.0
574 if days < 0:
575 before_first_view += 1
576 days = 0.0
577 samples.append(days)
578
579 def _round(value: float) -> float:
580 return round(value, 2)
581
582 ordered = sorted(samples)
583 caveats: list[str] = []
584 if no_first_view:
585 caveats.append(
586 f"{no_first_view} conversion(s) have a visitor token whose first "
587 f"page view is no longer in page_views. {AGED_OUT_CAVEAT}")
588 if unthreaded:
589 caveats.append(
590 f"{unthreaded} conversion(s) carry no visitor token, so they have "
591 "no first visit to measure from at all.")
592 if before_first_view:
593 caveats.append(
594 f"{before_first_view} conversion(s) are timestamped before their "
595 "visitor's first page view (clock skew, or a hand-edited row); "
596 "clamped to zero days rather than allowed to pull the median "
597 "below it.")
598
599 return {
600 "count": len(ordered),
601 "median_days": _round(statistics.median(ordered)) if ordered else None,
602 "p25_days": _round(_quantile(ordered, 0.25)) if ordered else None,
603 "p75_days": _round(_quantile(ordered, 0.75)) if ordered else None,
604 "min_days": _round(ordered[0]) if ordered else None,
605 "max_days": _round(ordered[-1]) if ordered else None,
606 "same_day": sum(1 for value in ordered if value < 1.0),
607 "unthreaded": unthreaded,
608 "no_first_view": no_first_view,
609 "unreadable_timestamp": unreadable,
610 "before_first_view": before_first_view,
611 "caveats": caveats,
612 }
613
614
615def _quantile(ordered: list[float], fraction: float) -> float:
616 """Nearest-rank quantile. Deliberately not interpolated: these are
617 real observed journeys, and an interpolated p75 is a duration nobody
618 actually took."""
619 if not ordered:
620 return 0.0
621 index = max(0, min(len(ordered) - 1, int(round(fraction * (len(ordered) - 1)))))
622 return ordered[index]