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
|