This project uses SQL, PHP, HTML, and JavaScript to generate reports from mock customer support data.
The Customer Support System is a customer service platform allowing customers to communicate with customer support agents to resolve customer issues.
To report an issue, a customer opens a ticket, selecting a category (Account access/Bug report/Order cancellation/Overcharged transaction/Undelivered goods/Wrong order), and leaves a message explaining the issue.
To address the issue, a customer support agent sends a message on the ticket, which the customer may reply to.
The customer and the agent exchange messages until the issue is resolved and the agent marks the ticket as resolved.
Multiple agents may be involved in the resolution of a ticket.
A ticket is a record of an issue reported by a customer.
An automatically generated unique identifier for each ticket. Numeric/integer. This is the primary key. A foreign key linking the CUSTOMER table to identify the customer who opened the ticket. Describes the type of issue reported by the customer. Text containing alphabetic lettersPossible values: Account access/Bug report/Order cancellation/Overcharged transaction/Undelivered goods/Wrong order
A numerical representation of the customer's satisfaction with the customer service during the resolution of the ticket. Numeric/integer.Possible values: 1/2/3/4/5
Whether or not the ticket has been resolved. Text containing alphabetic letters.Possible values: open/resolved
A message is a communication sent by a customer or agent under a ticket.
An automatically generated unique identifier for each message. Numeric/integer. This is the primary key. A foreign key linking the SENDER table to identify the sender of the message. A timestamp generated when a sender (a customer/agent) sends a message. Date and time. The actual text contents of the message. Text containing alphanumeric and symbolic characters.A customer is a user of the Customer Support System who opens a help ticket to request assistance.
An automatically generated unique identifier for each customer. Numeric/integer This is the primary key. A foreign key linking the SENDER table to identify the customer when they send a message. The customer's email address. Text containing alphanumeric and symbolic characters. The customer's first name. Text containing alphabetic characters. The customer's last name. Text containing alphabetic characters. The customer's phone number. Numeric/integer.A agent is a customer support represantative who acts on a help ticket by replying to the customer's messages and marking the ticket as resolved once the issue has been resolved.
An automatically generated unique identifier for each agent. Numeric/integer This is the primary key. A foreign key linking the SENDER table to identify the agent when they send a message. The agent's email address. Text containing alphanumeric and symbolic characters. The agent's first name. Text containing alphabetic characters. The agent's last name. Text containing alphabetic characters. The agent's phone number. Numeric/integer.An sender is a customer or agent who sends a message.
An automatically generated unique identifier for each sender. Numeric/integer. This is the primary key. Whether the sender is a customer or an agent. Text containing alphabetic characters. agent/customerI used Mockaroo to generate mock data for the database. I then used Random Sentence Generator to generate text content for the messages between customers and agents. The mock dataset contains records for:
- 34 agents.
- 123 customers.
- 377 tickets.
- 6341 messages.
The following reports may be generated from our database.
With the customer support data in our database, we can track how long it takes for a ticket to be resolved. We calculate the ticket resolution time by subtracting the timestamp of the first message from that of the last message on a resolved ticket. We use the following SQL query to calculate the minimum, average, and maximum ticket resolution times:
SELECT
MIN(resolution_time) AS min_resolution_time,
AVG(resolution_time) AS avg_resolution_time,
MAX(resolution_time) AS max_resolution_time
FROM
(
SELECT
TIMESTAMPDIFF(SECOND, MIN(message_sent_time), MAX(message_sent_time)) / 3600 AS resolution_time
FROM
MESSAGE, TICKET
WHERE
MESSAGE.ticket_id = TICKET.ticket_id AND
ticket_status = "resolved"
GROUP BY MESSAGE.ticket_id
) AS RESOLUTION_TIME_TABLE;
The results may be displayed on a dashboard like this:
Suppose that in the resolution of a ticket, more than one agent may become involved. We may wish to find out how often this occurs. We do so with the following SQL query:
SELECT
number_of_agents,
COUNT(number_of_agents) AS number_of_tickets
FROM
(
SELECT
COUNT(DISTINCT MESSAGE.sender_id) AS number_of_agents
FROM
MESSAGE,
SENDER,
TICKET
WHERE
MESSAGE.sender_id = SENDER.sender_id AND
sender_type = "agent" AND
MESSAGE.ticket_id = TICKET.ticket_id AND
ticket_status = "resolved"
GROUP BY MESSAGE.ticket_id
) AS AGENT_COUNT_TABLE
GROUP BY number_of_agents;
The results may be presented in the form of a pie chart like this:
We may write the following SQL query to show us how many tickets each agent has dealt with:
SELECT
CONCAT(agent_first_name, " ", agent_last_name) AS agent,
COUNT(DISTINCT MESSAGE.ticket_id) AS number_of_tickets
FROM
AGENT,
MESSAGE
WHERE
AGENT.sender_id = MESSAGE.sender_id
GROUP BY MESSAGE.sender_id;
We may display the results in a bar graph like this:
If we wish to measure our perfomance over time by tracking how our ticket resolution time decreases or increases each month, we may write the following SQL query:
SELECT
month_name,
MIN(resolution_time) AS min_resolution_time,
AVG(resolution_time) AS avg_resolution_time,
MAX(resolution_time) AS max_resolution_time
FROM
(
SELECT
TIMESTAMPDIFF(SECOND, MIN(message_sent_time), MAX(message_sent_time)) / 3600 AS resolution_time,
MONTH(MIN(message_sent_time)) AS month_number,
DATE_FORMAT(MIN(message_sent_time), "%b") AS month_name
FROM
MESSAGE, TICKET
WHERE
MESSAGE.ticket_id = TICKET.ticket_id AND
ticket_status = "resolved"
GROUP BY MESSAGE.ticket_id
) AS RESOLUTION_TIME_TABLE
GROUP BY month_number;
Below is the resulting graph:
A customer may rate the quality of the customer service they recieve during the resolution of a ticket. The rating is a number between 1 and 5. The following SQL query allows us to see the ratings recieved by each agent from customers:
SELECT
CONCAT(agent_first_name, " ", agent_last_name) AS agent,
MIN(ticket_rating) AS min_rating,
AVG(ticket_rating) AS avg_rating,
MAX(ticket_rating) AS max_rating
FROM
AGENT,
MESSAGE,
TICKET
WHERE
AGENT.sender_id = MESSAGE.sender_id AND
MESSAGE.ticket_id = TICKET.ticket_id
GROUP BY MESSAGE.sender_id;
We may display the results like this:
This project is based on the guide "Tracking Customer Service Metrics with SQL."