Find search terms that waste ad spend each week

from clickruntime import on_trigger, http, credentials, ai, notify

CUSTOMER_ID = "1234567890"
THRESHOLD = 20

def gaql(query):
    headers = {"Authorization": f"Bearer {credentials.connection('google', 'ads')}", "developer-token": credentials.secret("GOOGLE_ADS_DEVELOPER_TOKEN")}
    batches = http.post(f"https://googleads.googleapis.com/v21/customers/{CUSTOMER_ID}/googleAds:searchStream", headers=headers, json={"query": query}).json()
    return [row for b in batches for row in b.get("results", [])]

@on_trigger
def run(event):
    rows = gaql("SELECT search_term_view.search_term, campaign.name, metrics.cost_micros, metrics.conversions "
                "FROM search_term_view WHERE segments.date DURING LAST_7_DAYS AND metrics.conversions = 0")
    waste = sorted(((int(r["metrics"]["costMicros"]) / 1e6, r["searchTermView"]["searchTerm"], r["campaign"]["name"]) for r in rows
                    if int(r["metrics"].get("costMicros", 0)) / 1e6 >= THRESHOLD), reverse=True)[:25]
    if not waste:
        return {"terms": 0}
    listing = "\n".join(f"• £{c:,.2f} \"{t}\" ({camp})" for c, t, camp in waste)
    advice = ai.generate(listing, system="Suggest negative keywords (with match type) that would block these wasted search terms "
                         "without blocking relevant searches. One line each.", max_output_tokens=500).text
    notify.slack(f"*Search terms with spend and no conversions (last 7 days)*\n{listing}\n\n*Suggested negatives*\n{advice}", channel="#paid")
    return {"terms": len(waste), "wasted": round(sum(c for c, _, _ in waste), 2)}