File size: 26,121 Bytes
95fb175
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
"""Read scoped PDP search history and reproduce the signed-off search model."""
import calendar
import contextlib
import datetime as dt
from copy import deepcopy
import json
import math
import re
from decimal import Decimal, InvalidOperation

TABLE = "amzn_search_query_performance"
ORDERS = "amzn_order_items"
TERM = "search_query_data_search_query"
# Array positions are the prototype's wire contract.
FIELDS = ("vol", "tc", "mc", "tp", "mp", "mkt", "our", "ti", "mi", "tca", "mca", "pm", "py")
COLUMNS = {
    "vol": "search_query_data_search_query_volume",
    "tc": "click_data_total_click_count", "mc": "click_data_asin_click_count",
    "tp": "purchase_data_total_purchase_count", "mp": "purchase_data_asin_purchase_count",
    "ti": "impression_data_total_query_impression_count", "mi": "impression_data_asin_impression_count",
    "tca": "cart_add_data_total_cart_add_count", "mca": "cart_add_data_asin_cart_add_count",
    "pm": "purchase_data_total_median_purchase_price_amount",
    "py": "purchase_data_asin_median_purchase_price_amount",
}
STEPS = (
    ("ctr", "Seen to clicked", "Title and main image", "mc", "mi", "tc", "ti", 100),
    ("atc", "Clicked to cart", "Price, images and bullets", "mca", "mc", "tca", "tc", 10),
    ("a2p", "Cart to bought", "Price and delivery", "mp", "mca", "tp", "tca", 5),
    ("cv", "Clicked to bought", "Bullets, images and reviews", "mp", "mc", "tp", "tc", 10),
)
DROP = {
    "ctr": ["See you, rarely click", "Title start or main image"],
    "atc": ["Click, rarely add to cart", "Price, images or bullets"],
    "a2p": ["Add to cart, rarely buy", "Price or delivery"],
    "cv": ["Click, rarely buy", "Bullets, images or reviews"],
}
FACT_SOURCES = {"amzn_listings": "asin1", "amzn_catalog_items": "asin", "amzn_listing_items": "lookup_asin"}
FACT_FIELDS = {"title": ("item_name", "item_description"), "brand": ("brand",),
               "bullets": ("bullet_point",), "desc": ("product_description",),
               "terms": ("generic_keyword", "backend_keywords"), "highlights": ("title_differentiation",)}


# SQP value columns: your sales and all sales on the searches.
_SALES_COL = 6
_MARKET_SALES_COL = 5


@contextlib.contextmanager
def _scoped_reader(cur, source_scope=None, *, tables=(TABLE, ORDERS)):
    from harness.runtime import mirror_cursor
    from core.perm_scope import app_source_cursor
    owned = not hasattr(cur, "execute")
    raw = mirror_cursor(cur) if owned else cur
    try:
        with app_source_cursor(raw, tables, source_scope) as scoped:
            yield scoped
    finally:
        if owned:
            raw.close()


def _array(row):
    values = {k: float(row.get(column) or 0) for k, column in COLUMNS.items()}
    values["py"] = values["py"] or values["pm"]
    values["mkt"] = values["tp"] * values["pm"]
    values["our"] = values["mp"] * values["py"]
    return [values[k] for k in FIELDS]


def _aggregate(arrays):
    values = [sum(x[i] or 0 for x in arrays) for i in range(11)]
    for i, wi in ((11, 3), (12, 4)):
        held = [x for x in arrays if x[i]]
        weight = sum(x[wi] or 0 for x in held)
        values.append(sum(x[i] * (x[wi] or 0) for x in held) / weight if weight else
                      held[0][i] if held else 0)
    return values


def _read_searches(cur, asin):
    columns = ",".join(("asin", TERM, "start_date", "_report_period", *COLUMNS.values()))
    result = cur.execute(f"SELECT {columns} FROM {TABLE} WHERE UPPER(_report_period) IN ('MONTH', 'WEEK') "
                         f"AND (asin=? OR {TERM} IN (SELECT {TERM} FROM {TABLE} WHERE asin=?)) "
                         f"ORDER BY asin, {TERM}, start_date", [asin, asin])
    names = [x[0] for x in result.description]
    return [dict(zip(names, row)) for row in result.fetchall()]


def _load(cur, asin):
    records = _read_searches(cur, asin)
    mine = [r for r in records if r["asin"] == asin]
    history = sorted({str(r["start_date"])[:7] for r in mine if r["_report_period"].upper() == "MONTH"})
    months = history[-12:]
    weeks = sorted({str(r["start_date"])[:10] for r in mine if r["_report_period"].upper() == "WEEK"})
    grouped = {}
    for row in records:
        key = (row["asin"], row[TERM])
        search = grouped.setdefault(key, {"q": row[TERM], "m": {}, "w": {}})
        period = row["_report_period"].upper()
        bucket = search["m" if period == "MONTH" else "w"]
        start = str(row["start_date"])[:7 if period == "MONTH" else 10]
        bucket.setdefault(start, []).append(_array(row))
    rows = {}
    for (owner, _), search in grouped.items():
        for kind in ("m", "w"):
            search[kind] = {start: _aggregate(xs) for start, xs in search[kind].items()}
        search.update(v55(search, "all", {"months": months}) or dict.fromkeys(FIELDS, 0))
        rows.setdefault(owner, []).append(search)
    orders = cur.execute(f"SELECT SUBSTR(CAST(purchase_date AS VARCHAR),1,7), "
                         f"SUM(COALESCE(CAST(quantity AS DOUBLE),0)), SUM(COALESCE(CAST(item_price AS DOUBLE),0)) "
                         f"FROM {ORDERS} WHERE asin=? GROUP BY 1 ORDER BY 1", [asin]).fetchall()
    return {"asin": asin, "rows": rows, "months": months, "history": history, "weeks": weeks,
            "period": period_label("all", {"months": months}),
            "fields": list(FIELDS), "orders": {str(m): [float(u), float(s)] for m, u, s in orders},
            "row_counts": {p: sum(r["_report_period"].upper() == p for r in mine) for p in ("MONTH", "WEEK")}}


def load(cur, asin, *, source_scope=None):
    """Read every permitted monthly and weekly row, plus extended order-line sales."""
    with _scoped_reader(cur, source_scope) as scoped:
        return _load(scoped, asin)


def _source_listing(cur, table, asin, skus):
    columns = {r[0] for r in cur.execute("SELECT column_name FROM information_schema.columns WHERE table_name=?",
                                        [table]).fetchall()}
    identity = FACT_SOURCES[table]
    if identity in columns:
        predicate, params = f"{identity}=?", [asin]
    elif table == "amzn_listing_items" and "sku" in columns and skus:
        predicate, params = "sku IN (" + ",".join("?" for _ in skus) + ")", skus
    else:
        return {}
    stamps = [key for key in ("_snapshot_date", "last_updated_date", "_report_row_number") if key in columns]
    order = " ORDER BY " + ",".join(key + " DESC NULLS LAST" for key in stamps) if stamps else ""
    result = cur.execute(f"SELECT * FROM {table} WHERE {predicate}{order} LIMIT 1", params)
    names = [r[0] for r in result.description]
    row = result.fetchone()
    return dict(zip(names, row)) if row is not None else {}


def _fact_values(row):
    from modules.ranking_diagnostic import _content_text
    return {key: _content_text(value) for key, value, _ in _fact_entries(row)}


def _fact_entries(row):
    for field, value in row.items():
        if value is None or field.startswith("_"):
            continue
        if field == "attributes":
            attributes = json.loads(value) if isinstance(value, str) else value
            if isinstance(attributes, dict):
                yield from ((key, raw, "attributes." + key) for key, raw in attributes.items() if raw is not None)
        else:
            yield field.removeprefix("attributes_"), value, field


def _product_count(facts):
    value = facts.get("number_of_items")
    if value is None:
        value = facts.get("item_package_quantity")
    try:
        count = Decimal(str(value))
    except InvalidOperation:
        return ""  # A nonnumeric source fact cannot establish the product count.
    return str(int(count)) if count.is_finite() and count > 0 and count == count.to_integral_value() else ""


def _listing_context(asin, facts, stamp):
    parts = {part: next((facts[k] for k in keys if facts.get(k)), "") for part, keys in FACT_FIELDS.items()}
    title = parts["title"]
    count = _product_count(facts)
    listing = {"asin": asin, "brand": parts["brand"], "sku": {"full": f"{count} count" if count else ""}}
    meta = {**facts, **listing, "title": title, "bullets": parts["bullets"].splitlines()}
    if "price" in meta:
        try:
            price = float(meta.pop("price"))
            if math.isfinite(price):
                meta["price"] = price
        except ValueError:
            pass  # An unreported numeric price cannot support a projection.
    return {"listing": listing, "meta": {asin: meta},
            "live": {asin: {"title": title, "bullets": parts["bullets"].splitlines(), "highlights": parts["highlights"]}},
            "desc": {asin: parts["desc"]}, "terms": {asin: {"terms": parts["terms"]}},
            "pd": {asin: facts}, "last_updated": stamp, "part_states": {}}


def listing_facts(cur, asin, *, source_scope=None):
    """Build live listing facts only from permitted listing and catalog fields and rows."""
    facts, sources, stamps, skus = {}, {}, [], []
    with _scoped_reader(cur, source_scope, tables=tuple(FACT_SOURCES)) as scoped:
        for table in FACT_SOURCES:
            row = _source_listing(scoped, table, asin, skus)
            if row.get("seller_sku"):
                skus.append(row["seller_sku"])
            values = _fact_values(row)
            facts.update({key: value for key, value in values.items() if value})
            sources.update({key: {"table": "ut_" + table, "field": field, "value": deepcopy(raw)}
                            for key, raw, field in _fact_entries(row) if values[key]})
            stamps.extend(str(row[key]) for key in ("_snapshot_date", "last_updated_date") if row.get(key))
    return {**_listing_context(asin, facts, max(stamps, default=None)), "fact_sources": sources}


def live_part(context, asin, part):
    """Return source live text without inventing a workflow state or draft authority."""
    live = context["live"].get(asin, {})
    values = {"title": live.get("title", ""), "bullets": live.get("bullets", []),
              "highlights": live.get("highlights", ""), "desc": context["desc"].get(asin, ""),
              "terms": context["terms"].get(asin, {}).get("terms", "")}
    return {"live": values[part], "draft": None}


def validate(cur, asin, *, source_scope=None, per="all"):
    """Reconcile the loaded window against independent SQL sums within 0.5 percent."""
    with _scoped_reader(cur, source_scope) as scoped:
        model = load(scoped, asin)
        history = scoped.execute(f"SELECT DISTINCT SUBSTR(CAST(start_date AS VARCHAR),1,7) "
                                 f"FROM {TABLE} WHERE asin=? AND UPPER(_report_period)='MONTH' ORDER BY 1",
                                 [asin]).fetchall()
        window = [r[0] for r in history][-12:]
        period, width = ("WEEK", 10) if per.startswith("week:") else ("MONTH", 7)
        selected = window if per == "all" else [per[5:] if period == "WEEK" else per]
        metrics = ("vol", "tp", "mp", "mc")
        sums = [f"SUM(COALESCE(CAST({COLUMNS[k]} AS DOUBLE),0))" for k in metrics]
        sums.append(f"SUM(COALESCE(CAST({COLUMNS['tp']} AS DOUBLE),0)*COALESCE(CAST({COLUMNS['pm']} AS DOUBLE),0))")
        sums.append(f"SUM(COALESCE(CAST({COLUMNS['mp']} AS DOUBLE),0)*COALESCE(NULLIF(CAST({COLUMNS['py']} AS DOUBLE),0),"
                    f"CAST({COLUMNS['pm']} AS DOUBLE),0))")
        placeholders = ",".join("?" for _ in selected) or "NULL"
        sql = (f"SELECT {','.join(sums)} FROM {TABLE} WHERE asin=? AND UPPER(_report_period)=? "
               f"AND SUBSTR(CAST(start_date AS VARCHAR),1,{width}) IN ({placeholders})")
        expected = scoped.execute(sql, [asin, period, *selected]).fetchone()
        rows = kwModel55({"asin": asin}, per, model)
        actual = sumRows57(rows)
        checks = {k: _ties(actual.get(k, 0), v or 0) for k, v in zip((*metrics, "mkt", "our"), expected)}
        volume_rows = scoped.execute(f"SELECT {TERM}, SUM(COALESCE(CAST({COLUMNS['vol']} AS DOUBLE),0)) "
            f"FROM {TABLE} WHERE asin=? AND UPPER(_report_period)=? AND "
            f"SUBSTR(CAST(start_date AS VARCHAR),1,{width}) IN ({placeholders}) GROUP BY 1",
            [asin, period, *selected]).fetchall()
        volumes = dict(volume_rows)
        total, top = sum(volumes.values()), max([1, *volumes.values()])
        checks["share denominators"] = _ties(actual.get("vol", 0), total) and _ties(max([1, *(r["vol"] for r in rows)]), top)
        checks["shares"] = all(part57(r["vol"], total, top) == {"p": volumes[r["q"]] / total if total else 0,
                                                              "w": volumes[r["q"]] / top} for r in rows)
        # Orders are modeled monthly, so WEEK supplies no order validation evidence.
        if period == "MONTH":
            orders = scoped.execute(f"SELECT SUM(COALESCE(CAST(quantity AS DOUBLE),0)), "
                                    f"SUM(COALESCE(CAST(item_price AS DOUBLE),0)) "
                                    f"FROM {ORDERS} WHERE asin=? AND "
                                    f"SUBSTR(CAST(purchase_date AS VARCHAR),1,7) IN ({placeholders})",
                                    [asin, *selected]).fetchone()
            held = [model["orders"][m] for m in selected if m in model["orders"]]
            checks.update({k: _ties(sum(v[i] for v in held), orders[i] or 0)
                           for i, k in enumerate(("units", "sales"))})
        counts = dict(scoped.execute(f"SELECT UPPER(_report_period), COUNT(*) FROM {TABLE} "
                                     "WHERE asin=? AND UPPER(_report_period) IN ('MONTH','WEEK') GROUP BY 1",
                                     [asin]).fetchall())
        checks["every row read"] = all(model["row_counts"][p] == counts.get(p, 0) for p in ("MONTH", "WEEK"))
        checks["window"] = model["months"] == window
        return {"ok": all(checks.values()), "checks": checks, "actual": actual,
                "expected": dict(zip((*metrics, "mkt", "our"), [float(v or 0) for v in expected]))}


def _ties(a, b):
    return abs(a - float(b)) <= max(1e-9, abs(float(b)) * 0.005)


def v55(q, per, context):
    if per.startswith("week:"):
        values = q.get("w", {}).get(per[5:])
        return dict(zip(FIELDS, values)) if values is not None else None
    data = q.get("m") or {}
    arrays = [data[m] for m in context["months"] if data.get(m)] if per == "all" else [data[per]] if data.get(per) else []
    return dict(zip(FIELDS, _aggregate(arrays))) if arrays else None


def period_span(per, context):
    """Return the selected MONTH or WEEK window without mixing report grains."""
    if per.startswith("week:"):
        start = dt.date.fromisoformat(per[5:])
        return str(start), str(start + dt.timedelta(days=6))
    months = context["months"] if per == "all" else [per]
    if not months:
        return None, None
    return months[0] + "-01", f"{months[-1]}-{calendar.monthrange(int(months[-1][:4]), int(months[-1][5:]))[1]}"


def period_label(per, context):
    start, end = period_span(per, context)
    if start is None:
        return ""
    if per.startswith("week:"):
        return f"{_mon(start)} {int(start[-2:])} to {_mon(end)} {int(end[-2:])}, {end[:4]}"
    labels = [f"{_mon(value)} {value[:4]}" for value in (start, end)]
    return labels[0] if start[:7] == end[:7] else " to ".join(labels)


def part57(volume, total, top):
    """Preserve the prototype's known-zero shares using full selected-period denominators."""
    return {"p": volume / total if total else 0, "w": volume / top if top else 0}


def rate55(v):
    divide = lambda a, b: a / b if b else None
    st, score = {}, {}
    for key, _, _, yn, yd, mn, md, minimum in STEPS:
        you, market = divide(v.get(yn, 0), v.get(yd, 0)), divide(v.get(mn, 0), v.get(md, 0))
        st[key] = [you, market]
        score[key] = max(0, min(10, 5 * (you or 0) / market)) if v.get(yd, 0) >= minimum and market else None
    scored = [s for s in STEPS if score[s[0]] is not None]
    weak = list(min(scored, key=lambda s: score[s[0]])[:3]) if scored else None
    return {**v, **{k: divide(v.get(n, 0), v.get(d, 0)) for k, n, d in
                   (("is", "mi", "ti"), ("cs", "mc", "tc"), ("as", "mca", "tca"), ("ps", "mp", "tp"))},
            "st": st, "score": score, "total": sum(score[s[0]] for s in scored) / len(scored) if scored else None,
            "weak": weak}


def kwModel55(listing, per, context):
    cached = context.get("_kw_rows", {}).get(listing["asin"], {}).get(per)
    if cached is not None:
        return cached
    own = context.get("rows", {})
    rows = []
    for src in own.get(listing["asin"], []):
        value = v55(src, per, context)
        if value is not None:
            owners = ([a for a in context["_owners"].get(src["q"], []) if a != listing["asin"]]
                      if "_owners" in context else
                      [a for a, searches in own.items() if a != listing["asin"] and any(r["q"] == src["q"] for r in searches)])
            rows.append({"q": src["q"], "src": src, "asin0": listing["asin"], **rate55(value), "owners": owners})
    return sorted(rows, key=lambda r: -r["mkt"])


def fix55(r):
    if not r["mi"] or (r["is"] or 0) < 0.002:
        return ["Not seen", "Title and search terms"]
    if not r["weak"]:
        return ["Too few clicks", "Wait for more data"]
    return ["At or above the market", "Keep"] if r["score"][r["weak"][0]] >= 5 else r["weak"][1:3]


def drop56(r):
    if not r["mi"] or (r["is"] or 0) < 0.002:
        return ["Rarely see you", "Add it to the title or search terms"]
    if not r["weak"]:
        return ["Too few clicks to tell", ""]
    return ["Same as the market or better", "No change"] if r["score"][r["weak"][0]] >= 4.5 else DROP[r["weak"][0]]


def inText55(words, q):
    keys = qWords(q)
    count = sum(k in words for k in keys)
    return "All" if keys and count == len(keys) else "Some" if count else "None"


def fitState63(listing, q, context):
    cached = context.get("_fit_states", {}).get(listing["asin"], {})
    if q in cached:
        return cached[q]
    keys = qWords(q)
    if set(keys) & {stemW(w) for w in ("clearance", "sale", "discount", "cheap", "cheapest", "free", "best",
                                     "bestseller", "deal", "offer", "guarantee")}:
        return "no"
    rows = context.get("rows", {}).get(listing["asin"], [])
    if (q in context["_fit_queries"].get(listing["asin"], set()) if "_fit_queries" in context else
            any(r["q"] == q and (r["mp"] > 0 or r["mc"] >= 3) for r in rows)):
        return "fit"
    return "fit" if all(k in fit_words(listing, context) for k in keys) else "check"


def fit_words(listing, context):
    """Reuse the request's proved product words for search fit and competitor word fit."""
    if "_fit_words" in context:
        return context["_fit_words"].get(listing["asin"], set())
    rows = context.get("rows", {}).get(listing["asin"], [])
    live = context.get("live", {}).get(listing["asin"], {})
    details = context.get("pd", {}).get(listing["asin"], {})
    text = " ".join([live.get("title", ""), *live.get("bullets", []),
                     context.get("desc", {}).get(listing["asin"], ""),
                     *(v for v in details.values() if isinstance(v, str))])
    own = wordsOf(text)
    for row in rows:
        if row["mp"] > 0 or row["mc"] >= 3:
            own.update(qWords(row["q"]))
    return own


def indexed(context, *, per=None):
    """Index immutable request data without sharing derived state between requests or tenants."""
    clean = {k: v for k, v in context.items() if k not in ("_fit_words", "_fit_queries", "_fit_states", "_owners", "_kw_rows")}
    owners = {}
    for asin, rows in clean["rows"].items():
        for row in rows:
            owners.setdefault(row["q"], []).append(asin)
    clean.update(_owners=owners, _fit_words={asin: fit_words({"asin": asin}, clean) for asin in clean["rows"]},
                 _fit_queries={asin: {r["q"] for r in rows if r["mp"] > 0 or r["mc"] >= 3}
                               for asin, rows in clean["rows"].items()})
    clean["_fit_states"] = {asin: {r["q"]: fitState63({"asin": asin}, r["q"], clean) for r in rows}
                            for asin, rows in clean["rows"].items()}
    if per is not None:
        clean["_kw_rows"] = {clean["asin"]: {per: kwModel55({"asin": clean["asin"]}, per, clean)}}
    return clean


def expo59(v):
    if not v or not v["ti"]:
        return None
    if not v["mi"]:
        return {"k": "none", "label": "Not seen", "x": None}
    base = ("purchase", v["mp"], v["tp"]) if v["mp"] >= 2 else ("click", v["mc"], v["tc"]) if v["mc"] >= 10 else None
    if base is None:
        return {"k": "few", "label": "Few", "x": None}
    x = (base[1] / base[2]) / (v["mi"] / v["ti"]) if base[2] else math.inf
    key, label = ("under", "Under-exposed") if x >= 1.25 else ("over", "Over-exposed") if x <= 0.8 else ("match", "Matched")
    return {"k": key, "label": label, "x": x, "by": base[0]}


def myPrice59(listing, context):
    rows = [r for r in context.get("rows", {}).get(listing["asin"], []) if r["py"] > 0 and r["mp"] > 0]
    weight = sum(r["mp"] for r in rows)
    return sum(r["py"] * r["mp"] for r in rows) / weight if weight else None


def sumRows57(rows):
    return {k: sum(r.get(k, 0) or 0 for r in rows) for k in FIELDS[:11]} if rows else {}


def listScore57(rows):
    scored = [r for r in rows if r["total"] is not None]
    weight, total = sum(r["vol"] for r in scored), sum(r["vol"] for r in rows)
    return {"score": sum(r["total"] * r["vol"] for r in scored) / weight if weight else None,
            "cover": weight / total if total else 0, "n": len(scored)}


def trend63(src, context):
    pts = [(i, (src.get("m", {}).get(m) or [0])[0] or 0) for i, m in enumerate(context["months"])]
    pts = [(i, v) for i, v in pts if v > 0]
    if len(pts) < 3:
        return {"g": None, "last": pts[-1][1] if pts else 0}
    xb, yb = sum(i for i, _ in pts) / len(pts), sum(math.log(v) for _, v in pts) / len(pts)
    slope = sum((i - xb) * (math.log(v) - yb) for i, v in pts) / sum((i - xb) ** 2 for i, _ in pts)
    return {"g": max(-0.3, min(0.4, math.exp(slope) - 1)),
            "last": math.exp(yb + slope * (len(context["months"]) - 1 - xb))}


def at80(src, key, month):
    x = src.get("m", {}).get(month)
    if not x:
        return None
    if key == "vol":
        return x[0] if x[0] > 0 else None
    n, d = {"ms": (_SALES_COL, _MARKET_SALES_COL), "cs": (2, 1)}[key]
    return (x[n] or 0) / x[d] if x[d] else None


def _mon(month):
    return calendar.month_abbr[int(month[5:7])]


def trend80(src, key, context):
    pts = [(m, at80(src, key, m)) for m in context["months"]]
    pts = [(m, v) for m, v in pts if v is not None]
    if not pts:
        return {"g": None, "why": "Not reported in the last 12 months"}
    first, last = pts[0], pts[-1]
    if len(pts) < 2:
        return {"g": None, "why": f"Reported in {_mon(first[0])} only, so there is no change to work out"}
    if first[1] <= 0:
        return {"g": None, "why": f"It starts at 0 in {_mon(first[0])}, so a percent change cannot be worked out"}
    why = f"{_mon(first[0])} to {_mon(last[0])}"
    if last[0] != context["months"][-1]:
        why += f". Amazon did not report it after {_mon(last[0])}"
    return {"g": last[1] / first[1] - 1, "why": why}


def _back12(month):
    return str(int(month[:4]) - 1) + month[4:]


def _like(src, index, months):
    data = src.get("m") or {}
    pairs = [(data[m], data[_back12(m)]) for m in months if data.get(m) and data.get(_back12(m))]
    return {"a": sum(x[index] or 0 for x, _ in pairs), "b": sum(y[index] or 0 for _, y in pairs), "n": len(pairs)}


def yoyLL75(src, index, context):
    return _like(src, index, context["months"])


def trendScore75(src, context):
    r = yoyLL75(src, 0, context)
    year = r["a"] / r["b"] - 1 if r["n"] >= 3 and r["b"] > 0 else None
    trend = trend63(src, context)["g"]
    parts = [max(-1, min(1, v)) for v in (year, trend / 0.15 if trend is not None else None) if v is not None]
    return math.floor(50 + 50 * sum(parts) / len(parts) + 0.5) if parts else None


def _year_months(context):
    months = context["months"]
    year = months[-1][:4] if months else ""
    ytd = [m for m in months if m.startswith(year)]
    return year, ytd, [m for m in ytd if _back12(m) in context.get("history", months)]


def srch83(src, context):
    year, _, months = _year_months(context)
    r = _like(src, 0, months)
    if r["n"] and r["b"]:
        a, b = (format(r[k], ",.3f").rstrip("0").rstrip(".") for k in ("a", "b"))
        return {"g": r["a"] / r["b"] - 1,
                "why": f"{a} from {b} searches, {r['n']} {'month' if r['n'] == 1 else 'months'} reported in both years"}
    was = any(src.get("m", {}).get(_back12(m)) for m in months)
    span = f"{_mon(months[0])} to {_mon(months[-1])}" if months else ""
    why = "Not reported for you in the same months this year" if was else (
        f"Amazon did not report this search for you in {span} {int(year) - 1 if year else -1}")
    return {"g": None, "why": why}


def buy83(src, context):
    _, _, months = _year_months(context)
    data = src.get("m") or {}
    return {"a": sum((data.get(m) or [0] * 5)[4] or 0 for m in months),
            "b": sum((data.get(_back12(m)) or [0] * 5)[4] or 0 for m in months)}


def ytd83(src, index, context):
    return sum((src.get("m", {}).get(m) or [0] * 13)[index] or 0 for m in _year_months(context)[1])


def stemW(word):
    word = re.sub("[^a-z0-9]", "", word.lower())
    if word.endswith("ss"):
        return word
    if re.search("(ches|shes|xes|zes)$", word):
        return word[:-2]
    if word.endswith("ies"):
        return word[:-3] + "y"
    return word[:-1] if word.endswith("s") else word


def foldTea(text):
    return re.sub(r"\btea\s*-?\s*lights?\b", "tealight", (text or "").lower(), flags=re.ASCII)


def wordsOf(text):
    return {stemW(w) for w in re.split("[^a-z0-9]+", foldTea(text)) if len(w) > 3}


def qWords(query):
    return list(dict.fromkeys(stemW(w) for w in re.split(r"\s+", foldTea(query)) if len(w) > 3))