muskankashyap40@gmail.com

Google Fiber: Repeated Calls Dashboard

Google Business Intelligence Capstone Project

Objective

  • Analyze repeated customer calls across different markets and types of issues to identify patterns, trends, and insights.
  • Inform strategic decision-making for improving customer service and operational efficiency.

Tools and Skills

  1. Excel:

    Data loading, data type manipulation, data extraction, data cleaning, and preparation.

  2. Power Query:

    Data transformation including extracting month, week, quarter, and day, removing empty rows, and replacing null values.

  3. Tableau Public:

    Data visualization, merging multiple Excel sheets, creating calculated fields, generating various types of charts (column charts, pie charts), applying filters, and formatting tooltips.

Methodology

Excel:

  1. Load Data in Power Query:

    Import data from Excel files into Power Query for further processing.

  2. Data Type Transformation:

    Change data types as necessary for columns such as 'date' to ensure consistency and accuracy in analysis.

  3. Extract Time Components:

    Extract month, week, quarter, and day from the 'date' column to facilitate time-based analysis.

  4. Remove Empty Rows:

    Eliminate rows with empty values in columns like 'market' and 'type' to enhance data quality.

  5. Replace Empty Values:

    Replace null values with 0 in the 'contact_n_#' column to maintain data integrity.

  6. Repeat Data Cleaning:

    Apply the same data cleaning steps to all files, ensuring consistency across datasets.

  7. Standardize Column Names:

    Standardize column names such as changing "market_1" to "Market 1" for improved clarity and readability.

  8. Sheet Renaming:

    Rename sheets to "Market_1," "Market_2," and "Market_3" for easy identification and organization.

  9. Save as Excel Workbook:

    Save the cleaned and processed data as an Excel workbook for further analysis.

Tableau:

  1. Load Excel Workbook:

    Import the cleaned Excel workbook into Tableau Public.

  2. Merge Excel Sheets:

    Merge multiple Excel sheets using union to create a unified dataset for analysis.

  3. Create Calculated Fields:

    Generate calculated fields such as "Total repeated calls" by aggregating 'contact_n_#' columns.

  4. Visualizations - Day 0 Calls and Repeat Calls:

    Create a dual-axis chart showing day 0 calls and repeat calls by market type.

  5. Visualizations - Total Repeated Calls by Type:

    Design visuals illustrating total repeated calls by type, including pie charts and column charts.

  6. Apply Filters:

    Apply filters based on month and day to analyze data across different time periods.

  7. Tooltip Formatting:

    Enhance visualization clarity by formatting tooltips to provide additional context and insights.

Analysis Findings:

  1. Market-wise Distribution of Repeated Calls:

    Market 1 received the highest percentage of repeated calls.

  2. Distribution of Repeated Calls by Issue Type:

    Type 5 (Internet and WiFi issues) received the highest percentage of repeated calls.

  3. Prevalence of Repeated Calls Across Markets:

    Types 2 (Technician Troubleshooting) and 5 (Internet and WiFi) consistently experienced the highest volume of repeated calls.

  4. Timing of Repeated Calls:

    The majority of repeated calls occurred on day 1, following the initial contact (day 0).

  5. Day-wise Distribution of Repeated Calls:

    Market 1 and Market 2 experienced the highest volume of repeated calls on Mondays.