Log daily support numbers and churn signals to a sheet

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from datetime import date, timedelta
from clickruntime import on_trigger, http, credentials, ai, notify

ZD, AGENT = "https://yourcompany.zendesk.com/api/v2", "you@yourcompany.com"

def zd(method, path, **kw):
    r = http.request(method, ZD + path, auth=(f"{AGENT}/token", credentials.secret("ZENDESK_API_TOKEN")), **kw)
    return r.raise_for_status() or (r.json() if r.content else None)


sheets = lambda: build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets()
SHEET_ID = "your-spreadsheet-id"

@on_trigger
def run(event):
    day = (date.today() - timedelta(days=1)).isoformat()
    count = lambda q: zd("GET", "/search/count", params={"query": f"type:ticket {q}"})["count"]
    s = sheets()
    s.values().append(spreadsheetId=SHEET_ID, range="Metrics!A:D", valueInputOption="RAW",
                      body={"values": [[day, count(f"created:{day}"), count(f"solved:{day}"), count("status:open")]]}).execute()
    tickets = zd("GET", "/search.json", params={"query": f"type:ticket updated:{day}"})["results"][:80]
    if not tickets:
        return {"risks": 0}
    text = "\n".join(f"{i}. {t['subject']} | {t['description'][:300]}" for i, t in enumerate(tickets))
    risks = ai.generate(text, system="List tickets suggesting the customer may cancel or switch, with a short reason.",
                        json_schema={"type": "object", "properties": {"items": {"type": "array", "items": {"type": "object",
                        "properties": {"n": {"type": "integer"}, "reason": {"type": "string"}}}}}}, max_output_tokens=500).json["items"]
    rows = [[day, tickets[r["n"]]["id"], tickets[r["n"]]["subject"], r["reason"]] for r in risks]
    if rows:
        s.values().append(spreadsheetId=SHEET_ID, range="Churn!A:D", valueInputOption="RAW", body={"values": rows}).execute()
        notify.slack(f"{len(rows)} possible churn signals from yesterday's tickets, see the Churn tab.", channel="#customer-success")
    return {"risks": len(rows)}