Problem
- Each spend inquiry required checking the period in the ticket and querying the data manually
- Periods often spanned several months or years, so the same queries were run over and over
Company
CSAM Spend Aggregation & Reply Drafts
A pipeline that aggregates spend per requested period and drafts a reply for agents to review
Period | 2026.06
Spend inquiries that were answered by querying each period by hand
My Role
- Designed and built a pipeline connecting Zendesk, SharePoint, Trino/Hive and Google Sheets
- Split the requested period into monthly and yearly ranges, aggregated them and generated reply text
- Cut repeated query cost and kept re-runs consistent with a SQLite cache and pre-fetching
- Posted results as Zendesk internal notes for final agent review to manage accuracy risk
Result
- Every day at 21:00, target tickets are aggregated and reply drafts are prepared automatically
- Compared manual replies with automated totals to find mismatches and data-source limits
Technical Decisions
Results posted as Zendesk internal notes for agent review
Amounts go directly to customers, so a person checks them instead of auto-sending
SQLite cache and pre-fetching
Cuts repeated query cost and keeps re-runs consistent
Running in Production
- Compared manual replies with automated totals to find mismatches and data-source limits
- Retries on Hive/Trino query failure; tickets without a member ID are skipped with a warning log
