---
title: 'The Average Time to Resolve Complaint'
disqus: hackmd
---

The Average Time to Resolve Complaint
===

## Table of Contents

[TOC]

## Background
:::info
### :bulb: **Objective**
The company want to know the average time to resolve complaint across number of different dimensions.
:::

## Software Requirement

1. Docker
2. Postgre SQL Docker Containers
3. pgadmin Docker Containers
4. Microsoft Excel
5. Microsoft Power Point

## Steps

1. Install Docker
2. Install and run Postgre SQL Docker Containers
3. Install and run pgadmin Docker Containers
4. Go to pgadmin local host (localhost:5050)
5. Login with username and password
6. Open postgresql
7. Create new database in postgresql
8. Create table crm_events with this query

Query: Create crm_events table
    CREATE TABLE CRM_Events 
    (
  	Data_Received Date,
  	Product VARCHAR(50),
  	Sub_product VARCHAR(100),
  	Issue Text,
  	Sub_issue VARCHAR(255),
  	Consumer_complaint_narrative Text,
  	Tags Varchar (255),
  	Consumer_consent_provided Varchar (255),
	Submitted Varchar (50),
	Data_sent_to_company Date,
	Company_response_to_consumer Text,
	Timely_response Varchar (50),
	Consumer_disputed Varchar (50),
	Complaint_ID Varchar (100),
	Client_ID Varchar (100),
            PRIMARY KEY (Complaint_ID )
    )

9. Import crm_event.csv data to the table using import menu in postgres
10. Create table crm_call_center_logs with this query

Query: Create crm_call_center_logs tables

    CREATE TABLE crm_call_center_logs 
    (
        Data_Received Date,
        Complaint_ID Varchar (100),
        Rand_Client Varchar (100),
        Phone_Final VARCHAR(50),
        Vru_line  VARCHAR(100),
        Call_id int,
        Priority int,
        Type Varchar (50),
        Outcome Varchar (50),
        Server Varchar (100),
        Ser_start time,
        ser_exit time,
        ser_time time
    )

11. Join the table using this query

Query: Joining table

    SELECT 	ce.complaint_id,
		ce.client_id,
		ce.data_received,
		ce.product,
		case when ce.sub_product is null then 'Credit card' else ce.sub_product end as Sub_Product,
		ce.issue,
		ce.sub_issue,
		ce.consumer_complaint_narrative,
		ce.submitted,
		ce.data_sent_to_company,
		ce.company_response_to_consumer,
		ce.timely_response,
		ce.consumer_disputed,
		cccl.rand_client,
		cccl.phone_final,
		cccl.vru_line,
		cccl.call_id,
		cccl.priority,
		cccl.type,
		cccl.outcome,
		cccl.server,
		cccl.ser_start,
		cccl.ser_exit,
		cccl.ser_time
    FROM 	crm_events ce
    INNER JOIN crm_call_center_logs cccl on cccl.complaint_id = ce.complaint_id and cccl.rand_client = ce.client_id

12. Save the query result to excel
13. Analysis the result
14. Summarises the analysis finding in power point

---

###### Terimakasih