The Scenario
You're a digital artist who switched from Patreon to Ko-fi 18 months ago. You set up webhooks on day one and have been logging every payment event ever since — 1,400+ rows of JSON in the "Raw Events" worksheet of your Excel workbook. A new accountant asked you for a supporter CRM last Tuesday: one row per unique supporter, their first and last payment dates, total lifetime spend, and number of transactions. You told her you'd have it by end of week.
It's Thursday.
The bad version:
- Try to use Power Query to parse the JSON strings — get it working for a subset of rows, then hit a wall when the JSON schema differs between donation and shop_order payloads
- Write a VBA macro to loop through the rows and extract fields — it runs for three minutes, then crashes on row 812 with a type mismatch error you can't trace
- Export the column to a Python script you found online, parse and deduplicate it there, import the result back into Excel, and realize you still need to compute first_payment_date and last_payment_date per supporter using MINIFS and MAXIFS on a dataset with inconsistent date formats
The accountant needs this by Friday. The workbook you were hoping to hand her is still a column of JSON strings.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Excel workbook. It reads the raw event data and, through its built-in Ko-fi integration, can parse, deduplicate, and aggregate your supporter history into a clean CRM-style ledger — in one pass.
Parse all Ko-fi webhook JSON payloads in column A of the "Raw Events" sheet and extract: transaction_id, event_type, amount, currency, supporter_email, supporter_name, and timestamp into "Parsed Events" — skip any row where column A is blank
What You Get
- A new "Parsed Events" worksheet with one row per transaction
- Columns: transaction_id, event_type, amount, currency, supporter_email, supporter_name, timestamp — correctly typed (amount as number, timestamp as date)
- Blank rows in column A are skipped without error
- Rows where the JSON key is
"from_email"instead of"supporter_email"are handled by normalizing to supporter_email during extraction
What If the Data Is Not Quite Ready
Two different key names for supporter email across payload versions
Your older payloads use "from_email" and your newer ones use "supporter_email". The Parsed Events worksheet has gaps in the supporter_email column for all the older rows.
In "Parsed Events", fill in any blank supporter_email cell by looking at the corresponding row in "Raw Events" column A and extracting the value of the "from_email" key — update supporter_email in place
Duplicate transaction_ids in the parsed data
A webhook retry fired some transactions twice. You now have duplicate transaction_ids in "Parsed Events" and the lifetime spend totals are inflated.
In "Parsed Events", identify all duplicate transaction_id values — keep only the first occurrence of each and delete the duplicate rows, then write a count of removed duplicates to cell A1 of a new "Dedup Log" sheet
Building the supporter ledger after parsing
You have a clean "Parsed Events" worksheet. Now you need the accountant's deliverable: one row per supporter, sorted by total lifetime spend.
From the "Parsed Events" sheet, deduplicate by supporter_email and write one row per supporter to "Supporter Ledger" with columns: email, name, first_payment_date, last_payment_date, total_spent, transaction_count — sorted by total_spent descending
The full parse-deduplicate-ledger kill chain
Parse all Ko-fi webhook JSON payloads in column A of "Raw Events" — skip blank rows, normalize "from_email" to supporter_email, extract transaction_id/event_type/amount/currency/supporter_email/supporter_name/timestamp to "Parsed Events" — then deduplicate by transaction_id keeping first occurrence — then write one row per unique supporter_email to "Supporter Ledger" with columns: email, name, first_payment_date, last_payment_date, total_spent, transaction_count — sorted by total_spent descending
One prompt, one pass. The accountant gets her CRM Friday morning.
Try It
Get the 7-day free trial of SheetXAI and open any Excel workbook with 18 months of Ko-fi webhook JSON in column A, then ask it to parse and deduplicate your supporter history into a clean ledger. For more Ko-fi workflows, see how to parse webhook payloads into structured columns or track membership churn and month-over-month growth, or return to the Ko-fi integration overview.