Build customer lists for new-category campaigns

from google.oauth2.credentials import Credentials
from googleapiclient.discovery import build
from datetime import datetime, timedelta, timezone
from clickruntime import on_trigger, report_fetched, credentials, http, state

SHEET_ID = "your-spreadsheet-id"

STORE = "https://yourstore.com/wp-json/wc/v3"

def wc(method, path, **kw):
    resp = http.request(method, STORE + path, auth=(credentials.secret("WC_CONSUMER_KEY"), credentials.secret("WC_CONSUMER_SECRET")), **kw)
    resp.raise_for_status()
    return resp.json()

def sheets():
    return build("sheets", "v4", credentials=Credentials(credentials.connection("google", "work")), cache_discovery=False).spreadsheets().values()

@on_trigger
def run(event):
    known = state.get("categories")
    cats = wc("GET", "/products/categories", params={"per_page": 100})
    new = [c for c in cats if known is not None and c["id"] not in known and c["parent"]]
    report_fetched(len(new))
    if known is None or new:
        state.set("categories", [c["id"] for c in cats])
    if not new:
        return {"lists": 0}
    after = (datetime.now(timezone.utc) - timedelta(days=365)).strftime("%Y-%m-%dT%H:%M:%S")
    orders = wc("GET", "/orders", params={"after": after, "per_page": 100, "status": "completed"})
    for c in new:
        product_ids = {p["id"] for p in wc("GET", "/products", params={"category": c["parent"], "per_page": 100})}
        buyers = {o["billing"]["email"]: o["billing"]["first_name"] for o in orders
                  if any(li["product_id"] in product_ids for li in o["line_items"])}
        rows = [[c["name"], name, email, "Not sent"] for email, name in buyers.items()]
        if rows:
            sheets().append(spreadsheetId=SHEET_ID, range="Campaigns!A:D", valueInputOption="RAW", body={"values": rows}).execute()
    return {"lists": len(new)}