askrobots / dbbasic-object-server
Public
Runs live, versioned Python application objects on one VM, with a suite of apps: projects, notes, tasks, files and more.
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 |
| 4 | WORKED -- whether the person who read the pitch on Monday came back on |
| 5 | Thursday and bought something. Two things were missing for that, and this |
| 6 | module 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 | |
| 15 | Everything here is pure: rows in, numbers out, no data directory, no I/O. |
| 16 | The same posture object_visitors takes, and for the same reason -- a fold |
| 17 | you 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 |
| 22 | the cookie existed, and rows from anyone who sent Do Not Track, carry no |
| 23 | token; the only thread left for those is the IP address, which is not a |
| 24 | person (an office is one, a phone on the train is three). Stitching by IP |
| 25 | is better than dropping the rows, and pretending the result is as solid |
| 26 | as a cookie-threaded funnel is not. So `funnel` returns the IP-stitched |
| 27 | fraction as DATA, next to the numbers it qualifies, where a caller has to |
| 28 | decide 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 |
| 31 | new visitor. A second browser is a second visitor. A phone is a third. |
| 32 | Every one of those errors points the same way -- undercounting -- which |
| 33 | is the right direction for a number to be wrong in, and it is still |
| 34 | wrong. `returning_visitors` returns `floor: True` and the caveat text |
| 35 | itself, so a surface cannot render the number without being handed the |
| 36 | caveat to render beside it. |
| 37 | |
| 38 | **Time to conversion admits what aged out.** `page_views` is bounded by |
| 39 | days AND rows (docs/analytics.md), so a visitor whose first visit fell |
| 40 | off the end of retention has a conversion with no beginning. That is |
| 41 | reported as its own count -- not silently dropped, which would bias the |
| 42 | median toward fast conversions, and not counted as zero days, which would |
| 43 | be 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 |
| 48 | carrying both correlates an anonymous browsing thread with a named |
| 49 | account, retroactively, for somebody who was never asked -- |
| 50 | docs/analytics.md's cookie rule 4, the move that turns analytics into |
| 51 | surveillance. `build_conversion` therefore writes at most ONE of them and |
| 52 | prefers the anonymous one. See its docstring before changing it. |
| 53 | """ |
| 54 | |
| 55 | from __future__ import annotations |
| 56 | |
| 57 | import json |
| 58 | import statistics |
| 59 | from collections import defaultdict |
| 60 | from datetime import datetime, timedelta, timezone |
| 61 | from typing import Any, Iterable, Sequence |
| 62 | |
| 63 | CONVERSIONS_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. |
| 67 | FUNNEL_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. |
| 72 | IP_THREAD_PREFIX = "ip:" |
| 73 | |
| 74 | STEP_PATH, STEP_EVENT = "path", "event" |
| 75 | |
| 76 | FLOOR_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 | |
| 82 | AGED_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 | |
| 92 | def _text(value: Any) -> str: |
| 93 | return str(value if value is not None else "").strip() |
| 94 | |
| 95 | |
| 96 | def _day(stamp: Any) -> str: |
| 97 | text = _text(stamp) |
| 98 | return text[:10] if len(text) >= 10 else "" |
| 99 | |
| 100 | |
| 101 | def _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 | |
| 117 | def 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 | |
| 133 | def 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 | |
| 141 | def 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 | |
| 178 | def 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 | |
| 193 | def 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 | |
| 199 | def 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 | |
| 242 | def 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 | |
| 266 | def 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 | |
| 314 | def _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 | |
| 332 | def _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 | |
| 341 | def 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 | |
| 461 | def 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 | |
| 524 | def 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 | |
| 615 | def _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] |