Ivan Harold Gutierrez

Elevating Yellevate Prepared by: Ivan Harold C. Gutierrez SCENARIO Yellevate has been dealing with client conflicts for the past few years. The business had been receiving client complaints that resulted in revenue losses, thus it wanted to figure out how to effectively eliminate or reduce these losses. METHODOLOGY Google’s six phases of data analysis: ask, prepare, process, analyze, share, and act. ASK Statistically, payment opt-outs resulted from over 20% of the disputes brought against Yellevate. Due to this, revenue has decreased by about 5% annually (in USD). The data analyst team has now been requested by management to help resolve the issue. Using information obtained by the organization, the team must determine the causes of these problems and develop workable solutions. PREPARE Using of the ROCCC system to determine the credibility and integrity of data. Reliability - Yellevate via Refocus has made the data available exclusively for this project. Originality - The data came from Yellevate database via Refocus. Comprehensiveness - Yellevate provided data from 5 different countries with the customer ID, number of days settled, disputes and total invoices. While the data tracks many factors, it did not provide which type of service the dispute came from and more relevant details about it. Current - The latest data is 2021 Cited — All the data needed for this project can be accessed through this link. PROCESS The first thing to do was to create new database and create a new table. To create a new table, the SQL syntax is shown below. Figure 1.1 Creating Table For Import Next is to import the dataset in pgAdmin. On the left side panel, right-click on the table that was created earlier, and then select Import/Export Data… Figure 1.2 Importing dataset in pgAdmin To find the file that has to be imported, click the folder icon. The encoding should be UTF8 and the format should be CSV. To import the CSV file, click OK. Figure 1.3 Checking Other Options for Importing DATA CLEANING The column that can be checked for consistency issues is the country column. Using the DISTINCT function, we can see all of the listed countries. After running the code, there are only five different countries. Figure 1.4 Distinct Query in Country Column Clients must comprehend the meanings of the numerals 0 and 1 when this table is presented to them. The team has included a new column that will show if a claim is disputed or not. The data dictionary states that the disputed field has a value of 0 or 1. The numbers "1" and "0" indicate whether or not the customer contested the invoice. Figure 1.5 Adding New Column - Disputed Or Not Disputed A value of "0" indicates that the customer did not win the dispute and is legally required to pay the full invoice amount, it either means that Yellevate won the dispute, or there was never a dispute. On the other hand, a value of "1" indicates that Yellevate lost the dispute, the dispute was settled in the customer's favor, and the customer is not required to pay the invoice. Figure 1.6 Adding New Column - Won Or Lost Several queries was also made by the team in order to answer the questions that circumstances around the dispute problem. Figure 1.7 Supporting Queries ANALYZE Table 1.1 Average Settlement of Invoice Per Country Country Average Settlement Of Invoice (Days) Russia 29 France 28 United States 28 Spain 25 China 23 Average 26 Table 1.1 shows that China recorded the shortest processing time for invoice settlement, while Russia recorded the longest. Table 1.2 Average Timeframe of Dispute Settlement Per Country Country Average TImeframe of Dispute Settlement (Days) United States 41 Russia 38 Spain 37 China 34 France 34 Average 36 Table 1.2 shows that United States needs the most time to resolve disputes, whereas France and China record the least amount of time. Table 1.3 Percentage of Lost Revenue Per Country Country Percentage Of Lost Dispute France 34.23 Russia 8.72 China 8.2 Spain 6.78 United States 3.75 Average 4.67 Table 1.3 shows that France needs the most time to resolve disputes, whereas France and China record the least amount of time. Table 1.4 Percentage and Revenue Lost Per Country Total Revenue Lost Country Percent Revenue Lost France $ 526,264.00 15.41 Russia $ 81,291.00 3.43 China $ 42,630.00 1.08 United States $ 22,936.00 0.84 Spain $ 17,046.00 1.04 Average $ 690,167.00 4.67 Table 1.4 shows that Spain and United States recorded the lowest percentage and overall revenue losses, France recorded the largest. Table 1.5 Customer with the Highest Dispute Ratio Customer ID Sum of Disputes Won Sum of Disputes Lost Dispute Ratio 4632-QZOKX 9 8 100.00 3448-OWJOT 15 12 96.43 7600-OISKG 14 8 95.65 9771-QTLGZ 13 8 95.45 9725-EZTEJ 13 11 92.31 Table 1.6 Customer with the Highest Revenue Loss Ratio Country Sum of Revenue Gained Sum of Revenue Lost Loss Ratio 4632-QZOKX $ 55,259 $ 42,486 76.89 3448-OWJOT $ 115,767 $ 81,783 70.64 9725-EZTEJ $ 126,486 $ 88,124 69.67 9771-QTLGZ $ 71,525 $ 43,770 61.2 7600-OISKG $ 99,710 $ 49,426 49.57 Table 1.5 shows the customers with the highest dispute ratio while Table 1.6 shows the customers with the highest revenue loss ratio. SHARE Figure 1.8 Average Settlement of Invoice Per Country The average processing time in which invoices are settled is 26 days. Russia, United States and France record an above average processing time for settling invoices while China records at the lowest processing time. Yellevate must look at how China processes and settles the invoice faster compared to other countries. Figure 1.9 Average Timeframe of Dispute Settlement Per Country United States recorded at the longest average processing time of 41 days, which is 17% longer compared to China and France, which has both average processing time of 34 days. Furthermore, the average processing time for the five countries is 36 days. The company must adapt to the way how China and France settle disputes. Figure 1.10 Percentage of Lost Dispute Per Country France is the only country exceeded the average dispute percentage of 17.69%. The country had the greatest percentage of disputes that were lost, at 34.23%, which is nine times greater than the United States' percentage of disputes that were lost (3.75%). Figure 1.11 Percentage Revenue Lost Per Country The average percentage of revenue lost from disputes is 4.67%. The United States only has 0.84% lost conflicts; however France has a 13.35% loss rate, which is 15 times greater than the United States. Figure 1.12 Revenue Lost Due To Disputes Per Country Figure 1.13 Percentage and Revenue Lost Per Country Figures 1.11, 1.12 and 1.13 clearly show that France recorded the highest revenue losses that reached $526,264.00 which is almost 31 times higher compared to Spain. Figure 1.14 Revenue and Loss (2020 - 2021) Figures 1.14 shows that there is a steady rise in losses through time, resulting in decrease in collected revenue. In line with this observation, the team recommends that the company look into implementing recommended action plans the soonest possible time to reverse the trend line and increase the revenue. Figure 1.15 Customers with Highest Dispute Ratio Figure 1.15 shows the customers with the highest dispute ratio. This means that more than 90% of the transactions made by these five clients was disputed. For customer ID 4632-QZOKX, all of its transactions made was disputed and nearly half was lost. Figure 1.16 Customers with Highest Revenue Loss Ratio Figure 1.16 shows the top 5 customers with an alarming revenue loss ratio. Revenue gained as well as revenue lost was also shown in the graph. It can be seen that for customer ID 4632-QZOKX, $42,486 was lost while only $55,259 was gained by the company. It is 77% loss to gain ratio. Figures 1.15 and 1.16 shows the top 5 customers with an alarming dispute and revenue loss ratio. It will be crucial for the management to determine the root cause of these disputes by communicating first with this five clients. ACT RECOMMENDATIONS 1. A contract in place to firm up what has been agreed in terms of timescales, payment terms, notice periods etc., reducing the chances of disagreements later. If conflicts do arise, each party can then refer to the contract and statement of work to establish who (if anyone) is on the right. Possible terms that may be included in the contract: a) Set milestones for every project - project and payment will be broken down into stages b) Specific time frames for each milestone and projects c) Set regular touch base within the project timeframe to report on progress 2. Gather more information like service provided and reason for disputes. 3. Clear and well-defined dispute process and guidelines. 4. Personnel needs to be trained on how to handle disputes and other matters. 5. Deep dive on China's processes related to dispute and client management and implement best practices across all sites, starting with France.