Yellevate - Excel and SQL Project

Problem

Yellevate has been struggling with client disputes. Yellevate defines disputes as clients expressing dissatisfaction with the company’s services and refusing to pay for them. This has been a substantial financial burden for the company: statistically, nearly 20% of the disputes raised against Yellevate resulted in a payment opt-out. This has led to an approximate 5% annual loss of revenue (in USD). Management has now approached us to help resolve the issue. Using data the company collected about these disputes. We must identify their causes and develop actionable strategies to solve them. The company’s top executives will use the analysis that we will be presented.

Tasks

Analyst were also tasked to answer this following questions by the end of this study; 1. The processing time in which invoices are settled. 2. The processing time for the company to settle disputes. 3. Percentage of the disputes received by the company that were lost. 4. Percentage of revenue lost from disputes. 5. The country where the company reached the highest losses from lost disputes.

Methodology

We will base our methodology on Google’s Data Analysis Phases; Ask, Prepare, Process, Analyze, Share, and Act. Ask The team will ask the right questions, which will help us to further understand the problem by Attending the Project Brief Session, asking questions and clarifications during the Q&A Session, and asking mentors via the Thinkific platform. Prepare For this Phase, our team will download the .csv file provided by the mentors in the Thinkific platform. Also offered was the data dictionary, which contains the description of each column in the .csv file. The data dictionary will also act as a reference to us once we start cleaning the file itself. Process For the cleaning process of the .csv file, all the boolean terms (1 and 0) will be replaced by their corresponding text values to have much better credibility and understanding of the raw data. The team has also added new columns which pertain to the settlement status of who will pay the invoice if the dispute is successful or unsuccessful. Analyze Once the raw is clean, understandable, and has no outliers, the team shall start the analysis part. We shall use Microsoft Excel and its features and functions to answer the Data Analysis Goals. The team will also collaborate to get the most accurate analysis of the said .csv file. Share Data Visualization shall commence once the team has finished analyzing the given data. The team will use Microsoft Excel to create visualization via Charts. Different types of charts shall be used depending on the data needed and insight. Act The team shall provide insights and recommendations once the analysis and Visualization process commences. The recommendations shall directly help the company Yellevate to lessen disputes and loss of revenue.

SQL ETL

For the cleaning process of the .csv file, all the boolean terms (1 and 0) will be replaced by their corresponding text values to have much better credibility and understanding of the raw data. The team has also added new columns which pertain to the settlement status of who will pay the invoice if the dispute is successful or unsuccessful.

Data Visualization and Insights

Recommendation

1. To standardize the guidelines and parameters for disputing invoices, we can analyze historical disputes to identify common reasons and develop procedures for successful and unsuccessful disputes. Yellevate can use data on the average processing time of invoices to set targets for improvement and track progress toward these targets. 2. Using payment history data, we may identify clients who consistently pay on time and reward them through loyalty or rewards programs. Yellevate can also perform surveys or feedback forms, which Yellevate can send to clients via email to determine which rewards or incentives are most appealing. 3. To reiterate the contract between our company and its clients, we can use data on past disputes and non-payment to develop targeted communication strategies emphasizing the importance of timely payment's significance and the consequences of disputes or non-payment. We can use data on clients' preferred communication channels to ensure the message reaches the client/s. 4. Performing background checks on potential clients can be an alternative approach to reducing revenue loss. We can use data on clients' payment history, creditworthiness, and financial stability to assess the likelihood of disputes or non-payment. We can set specific criteria for these factors and develop a scoring system to rank potential clients. 5. Developing a survey or feedback form can help Yellevate understand why clients are disputing and settling invoices past the due date. We can use data from these surveys to identify common reasons for disputes and develop targeted strategies to address them. We can also track changes in dispute and payment behavior over time to measure the effectiveness of these strategies. 6. Sending email reminders to clients before the due date can be an excellent approach to reducing late payments. We can use data on email reminders' optimal timing and frequency to maximize their effectiveness. We can also track changes in payment behavior over time to measure the impact of these reminders. 7. We recommend conducting a separate study on countries with high revenue loss and dispute cases to identify specific factors contributing to these issues. We can use data on the demographics, industries, and payment behavior of clients in these countries to develop targeted strategies to address these issues. We can track revenue and dispute behavior changes over time to measure these strategies' effectiveness.