Optimizing Customer Support Excellence: A Business Intelligence (BI) Dashboard for Google Fiber's Repeat Caller Insights

The customer service team of Google Fiber, which offers fiber optic internet services, is exploring repeat call trends to reduce the number of calls customers need to make to resolve issues.

The goals is to develop a dynamic dashboard to explore
trends in repeat calls.

1. Optimizing Customer Support
Excellence: A Business
Intelligence (BI) Dashboard
for Google Fiber's Repeat
Caller Insights
BI Analyst: Reggi Ahmad Fauzi
PROJECT CASE STUDY
COURSERA
How often are customers repeatedly contacting the customer service team?

2. [email protected]
bit.ly/reggi-portfolio
linkedin.com/in/reggiizuaf
Check out my 'LinkedIn' page to learn more about me,
and please take a look at my other projects ??????????????????
Get in Touch
2023

3. Summary
Overview
The customer service team of Google Fiber, which offers fiber optic internet services, is exploring
repeat call trends to reduce the number of calls customers need to make to resolve issues.
I was interviewed for a BI position on Google Fiber's call center team. As part of the interview
process, I was asked to develop a dashboard tool that allows the team to explore trends in repeat
calls to understand how effectively they can answer customer questions the first time.
Develop a dynamic
dashboard to explore
trends in repeat calls
MAIN GOALS

4. Primary Question:
Primary Requirements:
How often are customers repeatedly contacting the customer service team?
The content appears to be a list of requests for charts and tables that measure
and explore repeat calls by various factors such as date, market, and problem
type.
The charts should offer insights into the reasons behind repeat calls, trends in
different market cities, and be designed to show trends over various
timeframes.
Business Problems
The team aims to improve operational efficiency and customer satisfaction by reducing call
volume. A dashboard is needed to showcase insights into repeat caller volumes in different
markets and types of problems they represent.

5. Succes Criteria
Specific: BI insights must clearly identify the specific characteristics of a repeat calls, including
how often customers are repeating calls.
Measurable: Calls should be evaluated using measurable metrics, including frequency and
volume. For example, do customers call with a specific problem more often than others?
Which market city experiences the most call? How many customers are calling more than
once?
Action-oriented: These outcomes must quantify the number of repeat callers under different
circumstances to provide the Google Fiber team with insights into customer satisfaction.
Relevant: All metrics must support the primary question: How often are customers repeatedly
contacting the customer service team?
Time-bound: Analyze data that spans at least one year to understand how repeat callers
change over time. Exploring data that spans multiple months will capture peaks and valleys in
usage.
Clarify succes with SMART criteria:

6. TOTAL DATAS
data.csv data.csv
ASSUMPTIONED
COLUMNS
Market_1.csv
Market_2.csv
Market_3.csv
data.csv
UNION ALLEXTRACT
TRANSFORM
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
new_market
Call Date
Call 0
Call 1
Call 2
Call 3
Call 4
Call 5
Call 6
Call 7
Call Type
Market Loc
3 CSV documents
Data Processing
Overview
Data source used is Google Fiber’s market dataset that are fictionalized versions of the
actual data and was made available during this project by Coursera.

7. QUERYING THE DATA
SELECT *
FROM `tutor-streamline-dataflow.fiber.market_1`
UNION ALL
SELECT *
FROM `tutor-streamline-dataflow.fiber.market_2`
UNION ALL
SELECT *
FROM `tutor-streamline-dataflow.fiber.market_3`
EXPORT TO
CSV
“data.csv“
Extracting Process
We create a target table to consolidate and store the Google Fiber datasets. As a BI analyst,
we will use programs such as BigQuery and Dataflow to move and analyze data with SQL.
SQL Query

8. data.csv
Loading Process
Now, we use the target table created to design a BI visualization and dashboard that will
address the Google Fiber customer service team’s questions using Tableau.

9. A new dashboard to analyze
the problem types and
number of repeat callers in
three different market cities.
Fields include: date, number
of repeat call, call type and
market location
01 02
A chart with metrics should
have clickable details to
view specific information
and filters are applied for a
specific time range (month,
week).
03
Strategy:
Dashboard Funcionality
Reference
Dashboard
Scope
Filter &
Granularity

10. Repeat Calls by Date
Percentage Call 0 and 1 Repeats
by Market & Type
Percentage Call 0 Repeats by
Weekday
Repeat Calls by Market & Type
Chart Type:
Chart Type:Chart Type:
Chart Type:
Dimension:
Dimension:Dimension:
Dimension:
Metrics :
Metrics :Metrics :
Metrics :
Bar chart & table
Horizontal bar chartBar chart
Bar chart & table
call type, call date
Market, call typeDate (weekday),
Market, call type
Repeat calls (contact_#)
call 0 (contact_0), call 1Call 0 (contact_0)
Call 0 (contact_0)
Strategy:
Metrics & Chart

11. Type 2 & type 5 - Highest problem type
March - Significant increase in calls
What's happen in March ?
Repeat calls by month
Insights into The Problems

12. Monday - More calls tend to occur
Number of calls is evenly distributed, except on
weekends
Do customers call CS less on weekends or are
there fewer CS who can receive calls?
Calls 0 repeat by weekday
Insights into The Problems

13. Market 1 - Highest total calls, with the
dominant types 2 and type 5
Market 3 - tends to have fewer total calls
relative to market 1, but it is still worth
noting for call type 5
What’s happen in market 1 ?
Repeat calls by market &
call type
Insights into The Problems

14. Market 1 need to take a closer look!
There is a
What’s happen in market 1?
What’s happen in increasing number of
type 2 on first call (call 0)?
Calls 0 & call 1 by market
and type
Insights into The Problems

15. Dashboard Appearance

16. Dashboard Appearance

17. Dashboard Appearance

18. Market 1 has been showing a steady growth in repeat calls in last three months. It is important to identify the
specific needs and preferences of this market in order to improve customer satisfaction.
Problem types 2 and 5 require urgent attention as they have been causing a lot of dissatisfaction among
customers. A thorough analysis of the root causes of these problems is necessary to find effective solutions. It
is also essential to communicate with customers who have experienced these problems and provide them with
satisfactory resolutions to retain their loyalty.
By focusing on market 1 and addressing problem types 2 and 5, we can improve customer satisfaction and
build a strong brand reputation. It is important to continuously monitor and evaluate to ensure the customers
expectation needs.
"
Reflection:
Next Step

19. Data Is
Just Everything!
It is about
the people
It is about
their behavior
It is about
their wants
It is about
what we have on
this earth.
It is useful
in many fields.