File size: 13,362 Bytes
9e15897
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
"""Read-only Operator dashboard figures from campaign reports and Amazon economics."""
import datetime as dt
import hashlib
import math

from modules import amazon_operator as op

AD_TYPES = ("sp", "sb", "sd")
ECONOMICS = "amzn_economics"
METRICS = ("spend", "ad_sales", "orders", "total_sales", "organic_sales", "acos", "tacos")
SORTS = {"spend", "sales", "orders", "acos", "campaign"}


def _period(start, end):
    try:
        first, last = (dt.date.fromisoformat(str(value)) for value in (start, end))
    except (TypeError, ValueError) as exc:
        raise ValueError("Use YYYY-MM-DD for the overview dates.") from exc
    if first > last:
        raise ValueError("The overview start must be on or before its end.")
    return first.isoformat(), last.isoformat()


def _ratio(spend, sales):
    return round(100 * spend / sales, 2) if spend is not None and sales is not None and sales > 0 else None


def _figures(spend, sales, orders, total):
    return {"spend": round(spend, 2), "ad_sales": round(sales, 2), "orders": orders,
            "total_sales": round(total, 2) if total is not None else None,
            "organic_sales": round(total - sales, 2) if total is not None else None,
            "acos": _ratio(spend, sales), "tacos": _ratio(spend, total)}


def _campaign_source(cur):
    """Union campaign reports without reading product reports or materializing source rows."""
    parts = []
    for ad_type in AD_TYPES:
        table = op._ads_table(ad_type, "campaigns")
        cols = op._columns(cur, table)
        if not cols:
            continue
        missing = {"date", "campaign_id", "cost", "sales", "orders"} - cols
        if missing:
            return None, f"{table} lacks {', '.join(sorted(missing))}. Sync Amazon Ads first."
        text = lambda name: f'COALESCE(CAST("{name}" AS VARCHAR), \'\')' if name in cols else "''"
        parts.append(
            f"SELECT '{ad_type}' AS ad_type, {text('_connection_id')} AS connection_id, "
            f"{text('profile_id')} AS profile_id, {text('campaign_id')} AS campaign_id, "
            f"{text('campaign_name')} AS campaign, TRY_CAST(\"date\" AS DATE) AS day, "
            'TRY_CAST(cost AS DOUBLE) AS spend, TRY_CAST(sales AS DOUBLE) AS sales, '
            f'TRY_CAST(orders AS DOUBLE) AS orders FROM {table}')
    return (" UNION ALL ".join(parts), None) if parts else (None, op.NOT_INSTALLED)


def _empty(start, end, reason, *, no_prior=False, first=None, last=None):
    return {"start": start, "end": end, "state": "unavailable", "reason": reason,
            "noPrior": no_prior, "firstDataDate": first, "dataDate": last,
            "totals": dict.fromkeys(METRICS), "daily": [],
            "campaigns": {"items": [], "total": 0, "cursor": None}}


def _page(cur, source, start, end, cursor, limit, sort, descending):
    """Page grouped campaigns in SQL, with a stable identity across types, profiles and connections."""
    if isinstance(limit, bool) or not isinstance(limit, int) or limit < 1:
        raise ValueError("The campaign page size must be a positive integer.")
    if sort not in SORTS:
        raise ValueError("Choose a campaign sort: spend, sales, orders, acos or campaign.")
    scope = hashlib.sha256(f"{start}|{end}|{sort}|{descending}".encode()).hexdigest()[:16]
    offset = 0
    if cursor:
        decoded = op._decode(cursor)
        if len(decoded) != 2 or decoded[0] != scope or not str(decoded[1]).isdigit():
            raise ValueError("The campaign cursor does not match this overview.")
        offset = int(decoded[1])
    grouped = (
        f"WITH reports AS ({source}) SELECT ad_type, connection_id, profile_id, campaign_id, "
        "arg_max(campaign, day) AS campaign, SUM(spend) AS spend, SUM(sales) AS sales, SUM(orders) AS orders, "
        "CASE WHEN SUM(sales) > 0 THEN SUM(spend) / SUM(sales) * 100 END AS acos "
        "FROM reports WHERE day BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) GROUP BY 1, 2, 3, 4")
    total = cur.execute(f"SELECT COUNT(*) FROM ({grouped})", [start, end]).fetchone()[0]
    direction = "DESC" if descending else "ASC"
    rows = cur.execute(
        f"SELECT * FROM ({grouped}) ORDER BY {sort} {direction} NULLS LAST, "
        "ad_type, connection_id, profile_id, campaign_id LIMIT ? OFFSET ?",
        [start, end, limit, offset]).fetchall()
    fields = ("ad_type", "connection_id", "profile_id", "campaign_id", "campaign", "spend", "sales", "orders", "acos")
    items = [{k: round(v, 2) if k in ("spend", "sales", "acos") and v is not None else v
              for k, v in zip(fields, row)} for row in rows]
    next_offset = offset + len(rows)
    return {"items": items, "total": total,
            "cursor": op._encode([scope, next_offset]) if next_offset < total else None}


def overview(tenant_key, start, end, *, st=None, cursor=None, limit=50, sort="spend", descending=True):
    """Return daily percentages, sales and spend plus one server-paged row per campaign."""
    start, end = _period(start, end)
    st = op._rt(tenant_key, st)
    with op._reader(tenant_key, st) as cur:
        if cur is None:
            return _empty(start, end, op.NOT_INSTALLED)
        source, reason = _campaign_source(cur)
        if source is None:
            return _empty(start, end, reason)
        first, last = cur.execute(f"SELECT MIN(day), MAX(day) FROM ({source})").fetchone()
        if first is None:
            return _empty(start, end, "No campaign history has arrived from Amazon Ads.")
        first, last = str(first), str(last)
        if end < first:
            return _empty(start, end, f"No complete Ads history exists before {first}.",
                          no_prior=True, first=first, last=last)
        bad = " OR ".join(f"{k} IS NULL OR NOT isfinite({k})" for k in ("spend", "sales", "orders"))
        if cur.execute(f"SELECT COUNT(*) FROM ({source}) WHERE day BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) "
                       f"AND ({bad})", [start, end]).fetchone()[0]:
            return _empty(start, end, "Campaign reports contain missing or invalid metrics.", first=first, last=last)
        daily_ads = {}
        result = cur.execute(f"SELECT day, SUM(spend), SUM(sales), SUM(orders) FROM ({source}) "
                             "WHERE day BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) GROUP BY day ORDER BY day",
                             [start, end])
        while batch := result.fetchmany(256):
            daily_ads.update({str(row[0]): row[1:] for row in batch})
        economics = {}
        econ_cols = op._columns(cur, ECONOMICS)
        reason = f"Ads history starts on {first}. Earlier figures are unavailable." if start < first else None
        if {"start_date", "sales_net_product_sales_amount"} <= econ_cols:
            result = cur.execute(
                'SELECT TRY_CAST(left(CAST(start_date AS VARCHAR), 10) AS DATE) AS day, '
                'SUM(TRY_CAST(sales_net_product_sales_amount AS DOUBLE)), '
                'COUNT(*) FILTER (WHERE TRY_CAST(sales_net_product_sales_amount AS DOUBLE) IS NULL '
                'OR NOT isfinite(TRY_CAST(sales_net_product_sales_amount AS DOUBLE))) '
                f'FROM {ECONOMICS} WHERE left(CAST(start_date AS VARCHAR), 10) BETWEEN ? AND ? '
                'GROUP BY day ORDER BY day', [start, end])
            while batch := result.fetchmany(256):
                economics.update({str(day): value if not invalid else None for day, value, invalid in batch})
        else:
            reason = "Amazon economics sales are unavailable. Sync Amazon economics first."
        daily = []
        day = dt.date.fromisoformat(start)
        while day.isoformat() <= end:
            key = day.isoformat()
            if key < first or key > last:
                daily.append({"date": key, **dict.fromkeys(METRICS)})
            else:
                spend, sales, orders = daily_ads.get(key, (0.0, 0.0, 0.0))
                daily.append({"date": key, **_figures(spend, sales, orders, economics.get(key))})
            if key == end:
                break
            day += dt.timedelta(days=1)
        spend, sales, orders = (sum(row[i] for row in daily_ads.values()) for i in range(3))
        total = sum(economics.values()) if economics and all(v is not None for v in economics.values()) else None
        if any(row["total_sales"] is None for row in daily):
            reason = reason or "Some days have no economics sales or campaign history. Missing figures are unavailable."
        return {"start": start, "end": end, "state": "partial" if reason else "ready", "reason": reason,
                "noPrior": start < first, "firstDataDate": first, "dataDate": last,
                "totals": _figures(spend, sales, orders, total), "daily": daily,
                "campaigns": _page(cur, source, start, end, cursor, limit, sort, descending)}


def validate(tenant_key, start, end, *, st=None, report=None):
    """Reconcile each figure against separate campaign and economics aggregations, independent of overview's union."""
    start, end = _period(start, end)
    st = op._rt(tenant_key, st)
    report = overview(tenant_key, start, end, st=st) if report is None else report
    independent = {"spend": 0.0, "ad_sales": 0.0, "orders": 0.0, "total_sales": None}
    days, errors = {}, []
    with op._reader(tenant_key, st) as cur:
        if cur is None:
            return {"ok": False, "detail": op.NOT_INSTALLED, "independent": independent}
        for ad_type in ("sp", "sb", "sd"):
            table = op._ads_table(ad_type, "campaigns")
            cols = op._columns(cur, table)
            if not cols:
                continue
            if not {"date", "cost", "sales", "orders"} <= cols:
                errors.append(f"{table} has missing metrics.")
                continue
            result = cur.execute(
                'SELECT TRY_CAST("date" AS DATE), SUM(TRY_CAST(cost AS DOUBLE)), '
                'SUM(TRY_CAST(sales AS DOUBLE)), SUM(TRY_CAST(orders AS DOUBLE)) '
                f'FROM {table} WHERE TRY_CAST("date" AS DATE) BETWEEN CAST(? AS DATE) AND CAST(? AS DATE) '
                'GROUP BY 1', [start, end])
            while batch := result.fetchmany(256):
                for day, spend, sales, orders in batch:
                    values = (spend, sales, orders)
                    if any(v is None or not math.isfinite(v) for v in values):
                        errors.append(f"{table} has invalid metrics.")
                        continue
                    rec = days.setdefault(str(day), dict(spend=0.0, ad_sales=0.0, orders=0.0, total_sales=None))
                    for key, value in zip(("spend", "ad_sales", "orders"), values):
                        rec[key] += value
                        independent[key] += value
        if {"start_date", "sales_net_product_sales_amount"} <= op._columns(cur, ECONOMICS):
            result = cur.execute(
                'SELECT left(CAST(start_date AS VARCHAR), 10), SUM(TRY_CAST(sales_net_product_sales_amount AS DOUBLE)) '
                f'FROM {ECONOMICS} WHERE left(CAST(start_date AS VARCHAR), 10) BETWEEN ? AND ? GROUP BY 1',
                [start, end])
            while batch := result.fetchmany(256):
                for day, sales in batch:
                    if sales is None or not math.isfinite(sales):
                        errors.append("Economics has invalid sales.")
                        continue
                    days.setdefault(day, dict(spend=0.0, ad_sales=0.0, orders=0.0, total_sales=None))["total_sales"] = sales
                    independent["total_sales"] = (independent["total_sales"] or 0.0) + sales
        else:
            errors.append("Economics sales are unavailable.")
    def expected_figures(row):
        spend, sales, total = row["spend"], row["ad_sales"], row["total_sales"]
        return {"spend": round(spend, 2), "ad_sales": round(sales, 2), "orders": row["orders"],
                "total_sales": round(total, 2) if total is not None else None,
                "organic_sales": round(total - sales, 2) if total is not None else None,
                "acos": round(spend * 100 / sales, 2) if sales > 0 else None,
                "tacos": round(spend * 100 / total, 2) if total is not None and total > 0 else None}

    expected = expected_figures(independent)

    def ties(actual, wanted):
        return all((actual.get(k) is None if v is None else
                    isinstance(actual.get(k), (int, float)) and math.isfinite(actual[k]) and abs(actual[k] - v) <= 0.01)
                   for k, v in wanted.items())

    if report.get("start") != start or report.get("end") != end or report.get("state") == "unavailable":
        errors.append("The requested overview window is unavailable or mismatched.")
    if not ties(report.get("totals", {}), expected):
        errors.append("Overview totals do not tie to campaign cost, campaign sales and economics sales.")
    observed = {row.get("date"): row for row in report.get("daily", [])}
    for day, row in days.items():
        wanted = expected_figures(row)
        if not ties(observed.get(day, {}), wanted):
            errors.append(f"Daily figures do not tie on {day}.")
    return {"ok": not errors, "detail": " ".join(errors) or "Campaign cost, ad sales and economics sales tie.",
            "app_total": report.get("totals"), "independent": expected}