ODBC Driver for Freshdesk: Querying Tickets, Contacts, and Conversations
What it is
An ODBC driver for Freshdesk exposes Freshdesk data (tickets, contacts, conversations, agents, etc.) as standard relational tables that BI, reporting, and ETL tools can query using SQL.
Key tables and common columns
- Tickets: ticket_id, subject, description, status, priority, group_id, agent_id, requester_id, created_at, updated_at, closed_at, source
- Contacts (Requesters): contact_id, name, email, phone, created_at, last_activity_at
- Conversations (Replies/Notes): conversation_id, ticket_id, author_id, author_type (agent/contact), body, created_at, private (boolean)
- Agents: agent_id, name, email, role, team_id, active
- Groups/Teams: group_id, name, managerid
Typical queries
- Recent open tickets:
sql
SELECT ticket_id, subject, status, priority, agent_id, created_at FROM tickets WHERE status IN (‘Open’,‘Pending’)ORDER BY createdat DESC LIMIT 50;
- Tickets with requester contact details:
sql
SELECT t.ticket_id, t.subject, c.name AS requester_name, c.email FROM tickets t JOIN contacts c ON t.requester_id = c.contact_id WHERE t.created_at >= DATE_SUB(CURRENTDATE, INTERVAL 30 DAY);
- Conversation thread for a ticket:
sql
SELECT conversation_id, author_type, author_id, body, created_at FROM conversations WHERE ticket_id = 12345 ORDER BY createdat ASC;
- Agent workload (open tickets per agent):
sql
SELECT a.agent_id, a.name, COUNT(*) AS open_tickets FROM agents a JOIN tickets t ON a.agent_id = t.agent_id WHERE t.status IN (‘Open’,‘Pending’) GROUP BY a.agent_id, a.name ORDER BY open_tickets DESC;
Joins and denormalization tips
- Use JOINs between tickets, contacts, agents, and conversations for full context.
- For performance, create materialized views or denormalized reporting tables (e.g., ticket + latest conversation + requester details).
- Filter on indexed timestamp columns (created_at, updated_at) to reduce API-backed query cost.
Pagination and API limits
- Many ODBC drivers for Freshdesk translate SQL into Freshdesk API calls; large scans may hit rate limits.
- Use date filters, LIMIT/OFFSET, or keyset pagination (WHERE created_at > last_sync) for incremental loads.
- Prefer batch syncs and avoid full-table SELECT * queries on large datasets.
Authentication and setup
- Typically uses Freshdesk API key (or OAuth) plus your domain; configure DSN in ODBC manager; test via ODBC query tool or BI connection panel.
Security and compliance
- Ensure encrypted connections (TLS) between driver and Freshdesk.
- Limit driver user permissions and rotate API keys regularly.
Best practices
- Define explicit column lists rather than SELECT *.
- Schedule incremental extracts for nightly reporting.
- Cache or materialize heavy joins in your data warehouse.
- Monitor API usage and add exponential backoff for retries.
If you want, I can generate example SQL for a specific report (SLA breaches, NPS by agent, or monthly volume) — tell me which report to build.
Leave a Reply