File size: 15,165 Bytes
3e19b1e
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
"""Child ASIN search signals over complete monthly reports and their reported history."""
from __future__ import annotations

import datetime as dt
import importlib
import math

TABLE = "amzn_search_query_performance"
TERM = "search_query_data_search_query"
# Arrays use the public pdp_search_model wire order, with unknown values kept as None.
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",
}
MODEL_MISSING = "Search model unavailable: modules.pdp_search_model"
NO_REPORT = "No search report"



# SQP value columns: your sales and all sales on the searches (market share = SALES_COL / MARKET_SALES_COL).
SALES_COL = 6
MARKET_SALES_COL = 5

def _model():
    try:
        return importlib.import_module("modules.pdp_search_model")
    except ModuleNotFoundError as exc:
        if exc.name != "modules.pdp_search_model":
            raise
        return None


def trend_score(src, context):
    """Return the canonical score for {m: {YYYY-MM: FIELDS array}} and {months, history}."""
    model = _model()
    values = list(src.get("m", {}).values())
    if model is None or any(v[0] is None for v in values):
        return None
    return model.trendScore75(src, context)


def funnel_score(rows):
    """Return the canonical searches-weighted score from rate55 rows containing vol and total."""
    model = _model()
    if model is None or any(row.get("vol") is None for row in rows):
        return None
    return model.listScore57(rows)["score"]


def _columns(con):
    return {r[0] for r in con.execute(
        "SELECT column_name FROM information_schema.columns WHERE table_name=?", [TABLE]).fetchall()}


def _period_sql(columns):
    # A legacy report is monthly only when both dates span one complete calendar month.
    start = "TRY_CAST(substr(CAST(start_date AS VARCHAR),1,10) AS DATE)"
    end = "TRY_CAST(substr(CAST(end_date AS VARCHAR),1,10) AS DATE)"
    declared = " AND upper(trim(CAST(_report_period AS VARCHAR)))='MONTH'" if "_report_period" in columns else ""
    return (f"{start}=date_trunc('month',{start}) AND "
            f"{end}=last_day({start}) AND {end} < ?" + declared)


def _number(value):
    if value is None:
        return None
    number = float(value)
    return number if math.isfinite(number) and number >= 0 else None


def _sum(values):
    return None if any(v is None for v in values) else sum(values)


def _aggregate(arrays):
    totals = [_sum([row[i] for row in arrays]) for i in range(11)]
    for index, weight in ((11, 3), (12, 4)):
        prices, weights = [row[index] for row in arrays], [row[weight] for row in arrays]
        if any(v is None for v in prices + weights):
            totals.append(None)
        else:
            total = sum(weights)
            totals.append(sum(p * w for p, w in zip(prices, weights)) / total if total else prices[0])
    return totals


def _months(last):
    year, month = map(int, last.split("-"))
    ordinal = year * 12 + month - 1
    return [f"{i // 12:04d}-{i % 12 + 1:02d}" for i in range(ordinal - 11, ordinal + 1)]


def _load(con, columns, cutoff):
    selected = [f'TRY_CAST("{col}" AS DOUBLE)' if col in columns else "NULL" for col in COLUMNS.values()]
    sql = (f"SELECT CAST(asin AS VARCHAR), CAST({TERM} AS VARCHAR), "
           f"substr(CAST(start_date AS VARCHAR),1,7), {','.join(selected)} FROM {TABLE} "
           f"WHERE {_period_sql(columns)} ORDER BY asin, {TERM}, start_date")
    cursor = con.execute(sql, [cutoff])
    grouped, history = {}, set()
    for batch in iter(lambda: cursor.fetchmany(2048), []):
        for asin, query, month, *raw in batch:
            values = dict(zip(COLUMNS, map(_number, raw)))
            for key, count, price in (("mkt", "tp", "pm"), ("our", "mp", "py")):
                values[key] = (_number(values[count] * values[price])
                               if values[count] is not None and values[price] is not None else None)
            search = grouped.setdefault(asin, {}).setdefault(query, {"q": query, "m": {}})
            array, previous = [values[k] for k in FIELDS], search["m"].get(month)
            search["m"][month] = _aggregate([previous, array]) if previous is not None else array
            history.add(month)
    sources = {asin: list(queries.values()) for asin, queries in grouped.items()}
    return sources, sorted(history)


def _total(src, months):
    arrays = [src["m"][m] for m in months if m in src["m"]]
    return dict(zip(FIELDS, _aggregate(arrays))) if arrays else None


def _ratio(numerator, denominator):
    if numerator is None or denominator is None:
        return None, "Search report values missing"
    if denominator <= 0:
        return None, "Search report denominator is zero"
    return numerator / denominator, None


def _value(values, kind):
    if values is None:
        return None, "Month not reported"
    if kind == "search":
        value = values[0]
        if value is None:
            return None, "Search report values missing: vol"
        return (value, None) if value > 0 else (None, "No positive searches reported")
    numerator, denominator = (SALES_COL, MARKET_SALES_COL) if kind == "market_share" else (2, 1)
    return _ratio(values[numerator], values[denominator])


def _year_change(src, months, kind):
    pairs = [(src["m"][month], src["m"][str(int(month[:4]) - 1) + month[4:]])
             for month in months if month in src["m"] and str(int(month[:4]) - 1) + month[4:] in src["m"]]
    count = len(pairs)
    if count < 3:
        return None, "Fewer than 3 months reported in both years", count
    current, prior = (_aggregate([p[i] for p in pairs]) for i in (0, 1))
    if kind == "search":
        ratio, reason = _ratio(current[0], prior[0])
    else:
        now, now_reason = _value(current, kind)
        before, before_reason = _value(prior, kind)
        ratio, reason = _ratio(now, before)
        reason = now_reason or before_reason or reason
    return (ratio - 1 if ratio is not None else None), reason, count


def _trend(src, context, kind):
    change, reason, matched = _year_change(src, context["months"], kind)
    series = []
    for month in sorted(set(context["history"]) | set(context["months"])):
        value, cause = _value(src["m"].get(month), kind)
        series.append({"month": month, "value": value, "reason": cause})
    return {"series": series, "change": change, "reason": reason, "matched_months": matched}


def _funnel(searches, context, model):
    scored = []
    reported = False
    for search in searches:
        values = _total(search, context["months"])
        if values is None:
            continue
        reported = True
        missing = [k for k in ("vol", "ti", "mi", "tc", "mc", "tca", "mca", "tp", "mp") if values[k] is None]
        if missing:
            return None, "Search report values missing: " + ", ".join(missing), []
        if model is not None:
            scored.append({"q": search["q"], **model.rate55(values)})
    if not reported:
        return None, "Not reported in the last 12 months", []
    score = funnel_score(scored) if model is not None else None
    reason = MODEL_MISSING if model is None else "Too few own observations or zero market rates" if score is None else None
    return score, reason, [{"q": r["q"], "vol": r["vol"], "steps": r["score"], "total": r["total"]} for r in scored]


def _exposure(values, model):
    if values is None:
        return None, "Not reported in the last 12 months"
    for key in ("ti", "mi", "mp", "mc"):
        if values[key] is None:
            return None, "Search report values missing: " + key
    if values["ti"] <= 0:
        return None, "Search report denominator is zero"
    base = "tp" if values["mp"] >= 2 else "tc" if values["mc"] >= 10 else None
    if values["mi"] > 0 and base and (values[base] is None or values[base] <= 0):
        return None, "Search report exposure denominator missing or zero"
    if model is None:
        return None, MODEL_MISSING
    result = model.expo59(values)
    return {"kind": result["k"], "multiple": result["x"], "base": result.get("by")}, None


def _asin_signals(searches, context):
    monthly = {}
    for search in searches:
        for month, values in search["m"].items():
            monthly.setdefault(month, []).append(values)
    src = {"m": {month: _aggregate(xs) for month, xs in monthly.items()}}
    model = _model()
    totals = _total(src, context["months"])
    trends = {kind: _trend(src, context, kind) for kind in ("search", "market_share", "click_share")}
    score = trend_score(src, context)
    score_reason = (MODEL_MISSING if model is None else "Too few positive reported months") if score is None else None
    if any(values[0] is None for values in src["m"].values()):
        score_reason = "Search report values missing: vol"
    funnel, funnel_reason, scored = _funnel(searches, context, model)
    exposure, exposure_reason = _exposure(totals, model)
    market, market_reason = _ratio(totals["our"], totals["mkt"]) if totals else (None, "Not reported in the last 12 months")
    clicks, clicks_reason = _ratio(totals["mc"], totals["tc"]) if totals else (None, "Not reported in the last 12 months")
    reasons = {"trend_score": score_reason, "funnel_score": funnel_reason, "exposure": exposure_reason,
               "market_share": market_reason, "click_share": clicks_reason}
    if not searches:
        reasons = dict.fromkeys(reasons, NO_REPORT)
        for trend in trends.values():
            trend.update(change=None, reason=NO_REPORT)
    return {"trend_score": score, "funnel_score": funnel, "market_share": market, "click_share": clicks,
            "exposure": exposure, "trends": trends, "reasons": reasons, "note": None if searches else NO_REPORT,
            "monthly": {m: dict(zip(FIELDS[:11], xs[:11])) for m, xs in src["m"].items()}, "searches": scored}


def signals(con, asins=None, *, today=None):
    """Read all complete MONTH facts from the supplied tenant cursor and return {months, history, asins}."""
    cutoff = (today or dt.date.today()).replace(day=1)
    columns = _columns(con)
    required = {"asin", TERM, "start_date", "end_date"}
    sources, history = _load(con, columns, cutoff) if required <= columns else ({}, [])
    context = {"months": _months(history[-1]) if history else [], "history": history}
    wanted = sorted(sources) if asins is None else list(dict.fromkeys(asins))
    result = {**context, "asins": {asin: _asin_signals(sources.get(asin, []), context) for asin in wanted}}
    if columns and not required <= columns:
        cause = "Search report columns missing: " + ", ".join(sorted(required - columns))
        for row in result["asins"].values():
            row.update(note=cause, reasons=dict.fromkeys(row["reasons"], cause))
            for trend in row["trends"].values():
                trend["reason"] = cause
    return result


def _sql_number(columns, key):
    if COLUMNS[key] not in columns:
        return "NULL::DOUBLE"
    value = f'TRY_CAST("{COLUMNS[key]}" AS DOUBLE)'
    return f"CASE WHEN isfinite({value}) AND {value}>=0 THEN {value} END"


def _sql_sums(columns):
    expressions = {key: _sql_number(columns, key) for key in COLUMNS}
    expressions.update(mkt=f"({expressions['tp']})*({expressions['pm']})",
                       our=f"({expressions['mp']})*({expressions['py']})")
    return ",".join(f"CASE WHEN COUNT(*)=COUNT({expressions[key]}) THEN SUM({expressions[key]}) END"
                    for key in FIELDS[:11])


def validate(con, result=None, *, asins=None, today=None):
    """Tie every ASIN month and derived signal to independent uncapped raw-source SQL aggregates."""
    result = signals(con, asins, today=today) if result is None else result
    columns, cutoff = _columns(con), (today or dt.date.today()).replace(day=1)
    if not {"asin", TERM, "start_date", "end_date"} <= columns:
        return {"ok": False, "checks": {"signals tie to the search report": False}, "reason": "Search report unavailable"}
    query = (f"SELECT CAST(asin AS VARCHAR), CAST({TERM} AS VARCHAR), "
             f"substr(CAST(start_date AS VARCHAR),1,7), {_sql_sums(columns)} FROM {TABLE} "
             f"WHERE {_period_sql(columns)} GROUP BY 1,2,3 ORDER BY 1,2,3")
    sources, monthly, history = {}, {}, set()
    cursor = con.execute(query, [cutoff])
    for batch in iter(lambda: cursor.fetchmany(2048), []):
        for asin, term, month, *values in batch:
            values = [float(v) if v is not None else None for v in values]
            sources.setdefault(asin, {}).setdefault(term, {})[month] = values + [None, None]
            history.add(month)
    cursor = con.execute(
        f"SELECT CAST(asin AS VARCHAR), substr(CAST(start_date AS VARCHAR),1,7), {_sql_sums(columns)} "
        f"FROM {TABLE} WHERE {_period_sql(columns)} GROUP BY 1,2 ORDER BY 1,2", [cutoff])
    for batch in iter(lambda: cursor.fetchmany(2048), []):
        for asin, month, *values in batch:
            monthly.setdefault(asin, {})[month] = dict(zip(FIELDS[:11], values))
    context = {"months": _months(max(history)) if history else [], "history": sorted(history)}
    checks = {"store complete-month window": result["months"] == context["months"],
              "reported history": result["history"] == context["history"]}
    ties, metrics = True, True
    for asin, actual in result["asins"].items():
        ties = ties and _same(actual["monthly"], monthly.get(asin, {}))
        searches = [{"q": q, "m": months} for q, months in sources.get(asin, {}).items()]
        expected = _asin_signals(searches, context)
        keys = ("trend_score", "funnel_score", "market_share", "click_share", "exposure", "trends", "reasons", "note", "searches")
        metrics = metrics and all(_same(actual[key], expected[key]) for key in keys)
    checks.update({"signals tie to the search report": ties, "derived signals tie to raw SQL": metrics})
    ok = all(checks.values())
    return {"ok": ok, "checks": checks, "reason": None if ok else "Search signals differ from raw SQL"}


def _same(actual, expected):
    if isinstance(actual, dict) and isinstance(expected, dict):
        return actual.keys() == expected.keys() and all(_same(actual[k], expected[k]) for k in actual)
    if isinstance(actual, list) and isinstance(expected, list):
        return len(actual) == len(expected) and all(_same(a, b) for a, b in zip(actual, expected))
    if isinstance(actual, (int, float)) and isinstance(expected, (int, float)):
        return math.isclose(actual, expected, rel_tol=1e-10, abs_tol=1e-9)
    return actual == expected