Appendix - Customer Support Efficiency

• The data is located in the cloud, divided into three markets. Links to access the data Market 1, Market 2, Market 3.

• The data is publicly available.

• The data is considered 1st party and is considered trustworthy.

• The data is stored in CSV format.

• The dataset is comprised of three small files, approximately 30-40kB in size.

• The data analyzed in this study covers the period from January 1st to March 31st, 2022.

• The tables consist of 11 columns: date_created, contacts_n, contacts_n_1, contacts_n_2, contacts_n_3, contacts_n_4, contacts_n_5, contacts_n_6, contacts_n_7, new_type, and new_market.

• Data is anonymized and fictionalized to ensure privacy. The values in the "new_market" column (market_1, market_2, and market_3) indicate the three different city service areas the data represents.

• The data also includes five problem types: Type_1 represents account management, Type_2 represents technician troubleshooting, Type_3 represents scheduling, Type_4 represents construction, and Type_5 represents internet and wifi.

• One limitation of the data is that it's not possible to determine if the caller is a person or a business.

• A mockup of the finished dashboard has been created.

• Created a mockup of the finished dashboard to answer the business questions. 

• Uploaded the three datasets into BigQuery and created a unified target table.

SELECT*

FROM `x.market_1`

UNION ALL

SELECT*

FROM `x.market_2`

UNION ALL

SELECT*

FROM `x.market_3`

• The target table consisted of 1351 rows.

• Observed that 181 rows had a null value for the first call. Among these, 151 rows also had a null value for repeated calls and were removed, resulting in 1200 rows remaining.

• Identified 30 rows with a null value for the first call but a value larger than 0 for repeated calls. While these entries may contain errors, they represented only a small fraction of the total rows and were retained for analysis.

• Uploaded the target table with 1200 rows to Tableau Public.

• Renamed the "contacts_n" column to "Day 0," "contacts_n_1" to "Day 1," "contacts_n_2" to "Day 2," and so on.

• Created a calculated field to determine the total number of repeated calls per day: [Day 1] + [Day 2] + [Day 3] + [Day 4] + [Day 5] + [Day 6] + [Day 7].

• Created charts to address the business questions: How often does the customer service team receive repeat calls from customers? What problem types generate the most repeat calls? Which market city's customer service team receives the most repeat calls?

  • 1. • Chart 1: Displays the number of first and repeated calls per day.

       • Chart 2: Illustrates the total number of first calls versus repeated calls.

       • Chart 3: Shows the number of first and repeated calls per market.

       • Chart 4: Depicts the distribution of repeated calls based on the number of days after the initial call.

       • Chart 5: Presents the percentage of repeated calls per market.

       • Chart 6: Highlights the percentage of repeated calls per problem type.

       • Chart 7: Visualizes the distribution of repeated calls per market and problem type.

• Created a dashboard and added a date filter for interactive exploration. Link to dashboard: Customer Support