Making the real cost of support visible

The problem

There was no reliable way to understand the cost of supporting individual dealership accounts. Account Managers were relying on judgment because ticket data, call activity, and CRM records were disconnected.

What I did

• Built Google Apps Script automation to retrieve ticket and call information from the Zendesk API.
• Matched tickets to dealership accounts using the subdomain on each ticket, then combined the records with internal CRM information.
• Estimated servicing cost using average Support and Account Management salaries, with tickets weighted by resolution time.
• Built a Google Sheets dashboard that refreshed twice per day, targeted Director alerts, and weekly leadership reporting.

How the team used it

• Separate calls to Account Management from calls to Support.
• Review ticket volume, issue category, resolution time, agent, call duration, and account trends.
• Identify expensive accounts, offer targeted training, and prioritize product fixes and FAQ content.

Relevant services