<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Komobong Silas on Medium]]></title>
        <description><![CDATA[Stories by Komobong Silas on Medium]]></description>
        <link>https://medium.com/@komobongsilas?source=rss-fbd74acd569------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*5vuHs2pV94M1dEcoBveAJg@2x.jpeg</url>
            <title>Stories by Komobong Silas on Medium</title>
            <link>https://medium.com/@komobongsilas?source=rss-fbd74acd569------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 10:55:59 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@komobongsilas/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Finding Patterns in Biosorption: A Data-Driven Perspective]]></title>
            <link>https://medium.com/@komobongsilas/finding-patterns-in-biosorption-a-data-driven-perspective-45a159f39e6a?source=rss-fbd74acd569------2</link>
            <guid isPermaLink="false">https://medium.com/p/45a159f39e6a</guid>
            <category><![CDATA[adsorption]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[chemical-engineering]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[excel]]></category>
            <dc:creator><![CDATA[Komobong Silas]]></dc:creator>
            <pubDate>Thu, 24 Jul 2025 22:22:39 GMT</pubDate>
            <atom:updated>2025-07-24T22:38:19.306Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OOhiEEDGPm3Nx_CzECHo6w.png" /></figure><p>In my final year as a Chemical Engineering student, I completed a project on fixed-bed biosorption, an eco-friendly technique for removing heavy metals from wastewater. I used <em>Luffa cylindrica</em> (sponge gourd), a low-cost agricultural waste, as the biosorbent.</p><p>While the project focused on environmental remediation, what intrigued me most was the data behind the process. I discovered that increasing the bed height delayed breakthrough time, as a taller column provided more surface area for adsorption. This insight emerged through careful analysis of time-series data and breakthrough curves.</p><p>What began as a lab experiment became a deeper exploration into how data reveals trends and informs decisions. This experience sparked my growing interest in data analytics and engineering. Since then, I’ve been developing skills in Python, SQL, Excel, and Power BI to analyze, automate, and communicate data-driven insights.</p><p>This project showed me that engineering isn’t just about technical solutions, it’s about understanding the story data tells and using it to create smarter systems.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=45a159f39e6a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PIXAR ANIMATION STUDIOS PERFORMANCE ANALYSIS]]></title>
            <link>https://medium.com/@komobongsilas/pixar-animation-studios-performance-analysis-189ad15e0410?source=rss-fbd74acd569------2</link>
            <guid isPermaLink="false">https://medium.com/p/189ad15e0410</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-storytelling]]></category>
            <dc:creator><![CDATA[Komobong Silas]]></dc:creator>
            <pubDate>Thu, 12 Jun 2025 16:22:43 GMT</pubDate>
            <atom:updated>2025-06-12T16:22:43.911Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iS5FANQOiJm3h3s9rSVRmQ.png" /></figure><p><strong>INTRODUCTION<br></strong>Everyone loves a good movie — something that sticks with you, makes you laugh, maybe even gets you emotional. Pixar has been doing that for years. Whether it’s the nostalgia of <em>Toy Story</em>, the emotion in <em>Up</em>, or the creativity of <em>Inside Out</em>, they’ve built a solid track record of films that connect with people across all ages.</p><p>As a brand, <strong>Pixar Animation Studios</strong> has long been known for delivering high-quality animated films that combine storytelling, emotion, and technology. However, beyond the creativity, there’s a business side to their success — box office performance, audience ratings, and award recognition. This report takes a closer look at Pixar’s movies over time (1995–2024) to understand what’s been driving their success.</p><p><strong>PROBLEM STATEMENT<br></strong>Pixar has released numerous films over the years, but not all have performed equally. While some achieved critical acclaim and commercial success, others received mixed reviews or underperformed at the box office.</p><p>This analysis aims to answer a few key questions:</p><ul><li>What trends exist in Pixar’s box office performance over time?</li><li>How do audience and critic ratings relate to revenue?</li><li>Which films delivered the highest return on investment?</li><li>What genres or creative contributors are most associated with success?</li><li>How often do Pixar films get nominated for or win awards?</li></ul><p>By answering these, the goal is to better understand the factors that contribute to a successful Pixar film — whether that’s financial, critical, or industry recognition.</p><p><strong>DATASET DESCRIPTION<br></strong>The dataset consists of 6 tables that includes information on <strong>28 Pixar films</strong> released from 1995 to 2024. The details of the tables are given below:</p><ul><li><strong>Pixar_films</strong>: It contains information such as film name, release date, run time, film rating, plot.</li><li><strong>Pixar_people</strong>: Information about directors, producers, and their roles in each film.</li><li><strong>Genres</strong>: Film genre and subgenre types (e.g., Animation, Adventure, Comedy).</li><li><strong>Box_Office</strong>: Data on film budgets, domestic and international earnings, and worldwide box office revenue.</li><li><strong>Public_Response</strong>: Ratings from Rotten Tomatoes, Metacritic, CinemaScore, and IMDb.</li><li><strong>Academy</strong>: Nominations and wins at the Oscars.</li></ul><p><strong>TOOLS USED</strong>:</p><ul><li><strong>SQL</strong>: To clean, transform, and join the data across multiple tables and extract key insights from the database.</li><li><strong>Excel</strong>: To visualize the data, perform statistical analysis, and generate charts for interpretation</li></ul><p><strong>DATA CLEANING<br></strong>The importance of a clean dataset cannot be overemphasized as it ensures accurate and consistent insights. For the most part, the dataset was clean. The only changes that were made were:</p><ul><li>Removing Duplicates: A few duplicates were found in the pixar_people table and these duplicates were removed.</li><li>A new column that contains image urls of all pixar films was added to the pixar_films table to aid in the visualization process in Excel.</li></ul><p><strong>ANALYSIS<br></strong>The analysis was conducted using SQL queries to extract relevant metrics and insights from the dataset:</p><p><strong>1. Key Performance Indicators (KPIs)</strong></p><ul><li><strong>Total Number of Films</strong>: 28<br> Pixar has released <strong>28 films</strong> since its inception, demonstrating a consistent and prolific production output.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/221/0*FsN0pGKC4cGJlEtl" /></figure><ul><li><strong>Average Rating</strong>: <strong>80.6<br></strong>A combined score of ratings from imdb, rotten tomatoes and metacritic gives an <strong>average rating of 80.6</strong> across all Pixar films, reflecting generally positive reviews from critics and audiences. This suggests that, while not every film is universally adored, Pixar maintains a strong reputation for quality storytelling and animation.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/681/0*_y24ccYRy6JKBhqo" /></figure><ul><li><strong>Total Box Office Gross</strong>: <strong>$17.04 Billion<br></strong> Pixar’s films have collectively earned <strong>$17.04B</strong> at the global box office. This exceptional total demonstrates the widespread appeal of Pixar films, generating significant revenue across multiple regions and cultural markets.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/447/0*DgvKO4zYPeM04Eco" /></figure><ul><li><strong>Average Return on Investment (ROI)</strong>: <strong>318.23%<br></strong> The <strong>average ROI</strong> for Pixar films is an impressive <strong>318.23%</strong>, indicating that the studio has been able to generate significant profits relative to production costs. This financial efficiency reinforces Pixar’s position as one of the most commercially successful animation studios.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/526/0*D5YB6NkmG5a9cZGt" /></figure><ul><li><strong>Oscar Wins</strong>: <strong>18<br></strong> Pixar has won <strong>18 Academy Awards</strong> for Best Animated Feature, underscoring the studio’s unparalleled success in creating films that not only resonate with audiences but are also critically acclaimed at the highest level in the industry.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/284/0*FX2kroiEUZ9F8e23" /></figure><p><strong>2. What trends exist in Pixar’s box office performance over time and How do audience and critic ratings relate to revenue?<br></strong>To understand Pixar’s long-term performance, we visualized annual box office gross alongside average IMDb ratings for each film. The line chart provides a chronological overview of how revenue trends align (or sometimes diverge) from audience/critics reception.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/692/0*yyf34LkqqJJVR82E" /></figure><ul><li><strong>Consistent Critical Acclaim:</strong> Pixar maintained high average ratings (mostly above 80%) from the very beginning. As Pixar’s first feature film, <strong>Toy Story(1995)</strong> holds the title of the highest average rating <strong>(92.7</strong>). Other standout titles include <strong>Wall-E, Ratatouille, Finding Nemo, Toy Story 3 </strong>and<strong> Inside Out</strong> who all had average ratings of 90 and above.</li><li><strong>Revenue Peaks:</strong> <em>Inside Out 2</em> (2024) has earned the highest box office gross of <strong>$1.7B</strong>. This success reflects the enduring power of well-executed sequels and the studio’s ability to build on its successful original films. It is followed by<strong> Incredibles 2, Toy Story 4, Toy Story 3 </strong>and<strong> Finding Dory</strong> who all had a total revenue of above $1 Billion.</li><li><strong>Rating-Revenue Gaps:</strong> Despite having the lowest average ratings of <strong>53, <em>Cars 2</em> (2011)</strong> still performed decently in theatres (<strong>$559M)</strong>, suggesting strong brand loyalty. This indicates that financial performance doesn’t always correlate with positive reviews.</li><li><strong>Post-2020 Dip:</strong> <em>Turning Red</em> grossed only <strong>$21.8M</strong>, the lowest in the dataset. Its underperformance can likely be attributed to factors such as the pandemic, which limited its theatrical release. Despite this, it won the hearts of audiences and critics alike with an average rating of <strong>82.7</strong>.</li></ul><p>This trend can also be seen in other movies during the pandemic period (2020–2022) — <strong>Onward, Soul, Luca </strong>and<strong> Lightyear </strong>which were also received well by the public but didn’t do well in the box office.</p><p><em>Elemental</em> (2023) and <em>Inside Out 2</em> (2024) suggest a rebound, both in revenue and consistency in Pixar’s output.</p><p><strong>3. Which films delivered the highest return on investment?<br></strong>The Top 5 Films by %ROI reveals Pixar’s most financially efficient productions — those that turned relatively lower budgets into massive box office successes.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/509/0*dQeiBO8XGc4tzh-J" /></figure><p>The standout performer is <strong>Toy Story (1995)</strong> with an extraordinary <strong>1214.79%</strong> ROI proving its massive impact as Pixar’s debut.. This isn’t just a financial win — it reflects how a strong narrative combined with pioneering animation created a cultural phenomenon with minimal upfront risk.</p><p>Following closely are:</p><ul><li><strong>Finding Nemo (826.61%)</strong> — A fresh concept that captivated both kids and adults, reinforcing Pixar’s strength in emotional storytelling.</li><li><strong>Inside Out 2 (749.02%)</strong> — A sequel that outperformed expectations, showing Pixar’s ability to tap into audience nostalgia while staying relevant.</li><li><strong>The Incredibles (586.35%)</strong> and <strong>Incredibles 2 (521.40%)</strong> — These represent Pixar’s franchise power. The sustained ROI across both films confirms the value of world-building and long-term audience engagement.</li></ul><p><strong>4. How often do Pixar films get nominated for or win awards?</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/0*ljW1IhENAAIbUyoM" /></figure><p>The Award Pipeline illustrates Pixar’s journey from film production to industry recognition. Out of <strong>28 films</strong>, <strong>22 received nominations</strong>, and <strong>13 ultimately won awards</strong> — showcasing a conversion rate of nearly 59% from nomination to win, and about 46% of all Pixar films becoming award-winning.</p><p>This high success rate reflects Pixar’s consistent quality and critical acclaim, not just commercial appeal. It also emphasizes the studio’s ability to balance mass audience connection with storytelling excellence appreciated by award bodies.</p><p>From a strategic standpoint, maintaining such a track record across nearly three decades positions Pixar as a studio that delivers both box office success and industry prestige, a rare and valuable combination.</p><p><strong>5. What genres are most associated with success?</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/427/0*LwAE6TvK4A8gGsAN" /></figure><p>An analysis of Pixar’s box office revenue by genre reveals a clear pattern: the studio’s highest earnings come from <strong>Adventure</strong> and <strong>Comedy</strong> films.</p><ul><li><strong>Adventure films</strong> have generated <strong>$17.04 billion</strong>, the highest of any genre.</li><li><strong>Comedy</strong> follows closely, contributing <strong>$12.73 billion</strong>.</li></ul><p>Combined, these two genres account for <strong>over 85%</strong> of Pixar’s total box office revenue. In contrast, genres like <strong>Action ($2.97B)</strong>, <strong>Drama ($0.81B)</strong>, and <strong>Family ($0.52B)</strong> make significantly smaller contributions.</p><p>This distribution highlights Pixar’s strategic strength in producing emotionally engaging, humor-rich stories with adventurous plots — a storytelling formula that resonates strongly with audiences across all age groups. The studio’s focus on these genres has been a major driver of its commercial success.</p><p><strong>6. Who are the top creative contributors behind Pixar’s success?</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/322/0*o8bXDlseMvLV9GfF" /></figure><p>Pixar’s consistent performance can be attributed not just to storytelling and animation, but also to the talent behind the scenes. The top five contributors based on the number of films they’ve worked on are:</p><ul><li><strong>Andrew Stanton</strong> — <em>9 films</em></li><li><strong>Randy Newman</strong> — <em>9 films</em></li><li><strong>Michael Giacchino</strong> — <em>8 films</em></li><li><strong>Pete Docter</strong> — <em>7 films</em></li><li><strong>John Lasseter</strong> — <em>7 films</em></li></ul><p>These individuals have played crucial roles in shaping Pixar’s identity — from directing and producing to scoring the studio’s most iconic soundtracks. Their contributions underscores their central role in the studio’s creative and commercial success.</p><p><strong>DASHBOARD OVERVIEW<br></strong>The Excel dashboard consists of 2 pages to visualize the results.</p><p>The focus was on delivering a balance of storytelling and analytics — using charts, filters, and visuals to surface meaningful insights, while keeping the dashboard clean and interactive</p><p><strong>1. Summary Page<br></strong>This page gives a bird’s-eye view of Pixar’s performance. It includes:</p><ul><li><strong>Top 5 Films by ROI:</strong> A bar chart showing which films gave the best return on investment.</li><li><strong>Revenue &amp; Ratings per film over time:</strong> A combo chart that plots annual revenue alongside average IMDb ratings, showing how public reception aligns with financial performance.</li><li><strong>Awards Funnel:</strong> A funnel chart illustrating the total number of movies, how many films were nominated and how many eventually won awards.</li><li><strong>Top 5 Contributors &amp; Genres:</strong> A column and a bar chart that highlight the most frequently involved creatives and dominant genres, helping identify key drivers behind successful films.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*wnyVM7bOv53vy6Kw" /></figure><p>Slicers like <strong>film rating</strong> and <strong>time period</strong> allow for interactive filtering, so users can explore trends by specific categories.</p><p>Together, the analysis reveals not only what has worked well in the past, but also areas where Pixar could optimize future production strategies based on data-backed patterns.</p><p><strong>2. Film Details Page<br></strong>This page focuses on individual movie-level details. It includes:</p><ul><li>Key metrics like budget, box office figures, ROI% and average ratings</li><li>Individual ratings from IMDb, Rotten Tomatoes and metacritic</li><li>Release date, Runtime, Genre, Rating, Plot and award types</li><li>A film slicer to select a film and see its details.</li><li>Image of the selected movie.</li></ul><p>The film details page is helpful for anyone who wants to deep-dive into specific movies and compare their performance across multiple dimensions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*rhieV4CDkRx8a-Qt" /></figure><p>For example, in the above screenshot, Brave is the selected film. It had a budget of $185M, a revenue of $0.54B, ROI% of 191.34% and average rating of 73.0. It was also one of the films that won an award — Animated Feature.</p><p><strong>RECOMMENDATIONS</strong></p><ol><li><strong>Prioritize High-ROI Storytelling Styles<br></strong>Films like <em>Toy Story</em> and <em>Finding Nemo</em> delivered exceptional ROI (1214% and 826% respectively). Investing in sequels or spin-offs from similarly performing IPs — or maintaining the emotional storytelling formula they used — can sustain high profitability.</li><li><strong>Double Down on Adventure &amp; Comedy Genres<br></strong>These two genres generated the highest revenues — $17.04B and $12.73B respectively. Future productions should continue leveraging these genres’ broad appeal while exploring fresh narratives within them.</li><li><strong>Maximize Award Campaign Strategies<br></strong>With 22 out of 28 films nominated and 13 wins, Pixar clearly has award-worthy material. Strengthening marketing around award seasons could boost visibility, prestige, and long-term revenue.</li><li><strong>Leverage Proven Creative Contributors<br></strong>Consistently high-performing contributors like <em>Randy Newman</em> and <em>Andrew Stanton</em> (9 films each) should be prioritized for future projects — either as leads or mentors to new talents — to maintain storytelling excellence.</li><li><strong>Monitor Market Shifts Post-2020<br></strong>Films released post-2020 (e.g., <em>Onward</em>, <em>Lightyear</em>) saw reduced box office and lower ROI, partly due to external factors (e.g., COVID-19, streaming shifts). Regular review of distribution strategy (cinema vs. streaming) is crucial.</li><li><strong>Explore Genre Diversification Cautiously<br></strong>Genres like <em>Drama</em> and <em>Family</em> brought in the least revenue. If explored further, they should be supported with strong storytelling, known voices, or blended into more successful genres (e.g., comedy-drama).</li></ol><p><strong>CONCLUSION<br></strong>Pixar’s film history is a rich blend of creativity, commercial success, and critical acclaim. With an average rating of <strong>80.6</strong>, total box office revenue surpassing <strong>$17 billion</strong>, and a portfolio that has earned <strong>18 Oscars</strong>, Pixar has set a gold standard in animated storytelling. This analysis reveals clear trends — adventure and comedy drive the most success, certain creatives consistently contribute to high-performing films, and standout franchises deliver exceptional ROI.</p><p>However, recent releases suggest shifting audience dynamics and industry changes, making it essential for Pixar to adapt without losing its unique voice. By combining data-driven insights with its signature storytelling magic, Pixar can continue to innovate, connect with audiences, and deliver timeless stories for the next generation.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=189ad15e0410" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[CFPB CONSUMER COMPLAINTS ANALYSIS]]></title>
            <link>https://medium.com/@komobongsilas/cfpb-consumer-complaints-analysis-46f7ad55f6ad?source=rss-fbd74acd569------2</link>
            <guid isPermaLink="false">https://medium.com/p/46f7ad55f6ad</guid>
            <category><![CDATA[power-query]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Komobong Silas]]></dc:creator>
            <pubDate>Sat, 17 May 2025 19:10:19 GMT</pubDate>
            <atom:updated>2025-05-17T19:10:19.334Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UIOnD8exWswdGBYuF3G0xQ.png" /></figure><p><strong>INTRODUCTION<br></strong>In any industry, providing a service means being open to feedback — both positive and negative. This feedback serves as a valuable indicator of service quality, customer satisfaction, and operational performance.</p><p>In the financial sector, this is especially important. Analyzing consumer feedback, particularly in the form of complaints, enables companies to identify recurring issues, improve internal workflows, and ensure compliance with regulatory standards.</p><p>The <strong>Consumer Complaint Database</strong>, maintained by the Consumer Financial Protection Bureau (CFPB), contains complaints about financial products and services submitted by consumers in the United States. This report focuses on complaints recorded between May 2017 and August 2023.</p><p>Using Power BI, this analysis uncovers trends, evaluates company response patterns, and assesses the effectiveness of complaint resolution across different financial products and channels. The goal is to support stakeholders in identifying areas for improvement and implementing strategies that enhance customer experience.</p><p><strong>PROBLEM STATEMENT<br></strong>Consumers regularly report issues related to financial products and services such as Mortgages, Credit Reporting, and Student Loans. While there are established processes for addressing these complaints, concerns remain around the speed and quality of company responses. Some institutions struggle to ensure prompt and consistent resolutions, especially across various channels and product types.</p><p>In addition, recurring complaints linked to specific services, high complaint volumes in certain regions, and gaps in aligning internal practices with customer expectations and regulatory standards highlight the need for deeper analysis. Understanding these trends and company behaviours is essential for improving service delivery, strengthening consumer trust, and minimizing the recurrence of unresolved issues.</p><p><strong>DATASET DESCRIPTION</strong><br>The dataset is made up of 12 columns and 62516 rows excluding the column headers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8_6U5lWPI9cAKK2Hda-5mQ.png" /><figcaption>Overview of Dataset</figcaption></figure><ul><li>Complaint ID — unique identifier for each complaint made.</li><li>Submitted via — the channel the complaint was made through.</li><li>Date submitted — the date the complaint was submitted to the CFPB</li><li>Date received — the date the company received the complaint from CFPB</li><li>State — The state of the mailing address provided by the consumer</li><li>Product — The type of product the consumer identified in the complaint</li><li>Sub-product — The type of sub-product the consumer identified in the complaint (not all Products have Sub-products)</li><li>Issue — The issue the consumer identified in the complaint (possible values are dependent on Product)</li><li>Sub-issue — The sub-issue the consumer identified in the complaint (possible values are dependent on Product and Issue, and not all Issues have corresponding Sub-issues)</li><li>Company public response — The company’s optional, public-facing response to a consumer’s complaint. Companies can choose to select a response from a pre-set list of options that will be posted on the public database. For example, “Company believes complaint is the result of an isolated error.”</li><li>Company response to consumer — This is how the company responded. For example, “Closed with explanation.”</li><li>Timely response? — Whether the company gave a timely response (Yes/No)</li></ul><p><strong>DATA CLEANING<br></strong>Before proceeding with any form of analysis, having a clean dataset to work with is a necessity to ensure the results from the analysis are reliable for further action. It involves removing duplicates, handled missing values, and standardizing formats amongst others.</p><p>The dataset was imported into Power BI and the Power Query Editor was used for cleaning and transformation.</p><ul><li><strong>Checking for duplicates:</strong> Each row of the table was unique so there were no duplicates to remove.</li><li><strong>Correcting Data types:</strong> The complaint id was changed from whole number to text as it was not used in any aggregation during the analysis.</li><li><strong>Handling missing values</strong>: The columns sub-product, sub-issue, Company public response and Timely response all contained missing values.<br>For the<strong> sub-product</strong>, it was observed that they all came from the same category of product — savings or checking account. These missing values were not up to 1% of the entire dataset and therefore were resolved by imputation. The missing values were replaced with the mode of sub-product — checking account.<br>The <strong>sub-issue</strong> missing values were left blank as not all issues had a sub-issue.<br>The <strong>company public response</strong> was also left blank as companies can choose not to give a public response.<br>The <strong>timely response</strong> column, the missing values were as a result of the complaints where the company response to consumer was still in progress. Therefore, the missing values here were replaced with in progress to account for complaints that are yet to be resolved.</li><li><strong>Standardizing Data format: </strong>The state column only contained state codes which didn’t properly give information about the states. A new column was added containing the full name of each state. For example AL — Alabama, CO — Colorado.</li><li><strong>New column </strong>— A new column <strong>days to reach company </strong>was also created representing the number of days it takes a complaint to reach the company. It is the difference between date received and date submitted.</li></ul><p><strong>DATA MODELLING</strong><br>Data modelling involves proper organization of data for efficient analysis. It It involves designing relationships between tables and structuring data in a way that enhances performance, accuracy, and ease of use.</p><p>In the Power Query Editor, the the dataset was duplicated 7 times, — 1 fact table and 5 dimension tables.</p><ul><li><strong>DimProduct</strong> — The only columns kept here were the product and sub-product columns. The duplicates were removed and an index column sub-product id was also created as a unique identifier to link this table to the fact table.</li><li><strong>DimIssue </strong>— The sub-issue and issue columns were retained here. The duplicates were removed and an index column sub-issue id was created to link this table to the fact table.</li><li><strong>DimState </strong>— The state code and state name columns were retained, duplicates were removed and an index column state id was added to link this table to the fact table.</li><li><strong>DimSubmission</strong> — Here, only the submitted via column was kept, duplicates were removed and a unique identifier submission via id was also created to link this table to the fact table.</li><li><strong>DimCustomer_Response</strong> — Only the Company response to consumer column was kept here, duplicates were removed and a unique identifier company-customer response id was added to link to the fact table.</li><li><strong>DimPublic_Response</strong> — Only the Company public response column was kept here, duplicates were removed and a unique identifier company-public response id was added to link to the fact table.</li><li><strong>FactTable_Complaints </strong>— This table was joined to the different dimension tables based on a common column between them. The redundant columns were removed and the following were left:<br>Complaint id, date submitted, date received, timely response, company-customer response id, sub-issue id, sub-product id, state id, submission via id and days to reach company.</li></ul><p>The modelled tables were then loaded into Power BI for analysis and visualization.</p><ul><li><strong>Creation of Date Table</strong><br>A <strong>Date Table</strong> is important in Power BI because it helps you handle time-based analysis efficiently. Instead of relying on raw date columns in your data, a Date Table ensures accurate calculations, comparisons, and filtering across different time periods. This table was created using DAX functions.</li></ul><pre>DimDate = <br>ADDCOLUMNS<br>    (<br>        CALENDARAUTO(),<br>        &quot;Year&quot;,YEAR([Date]),<br>        &quot;Year Start&quot;,DATE( YEAR([Date]),1,1),<br>        &quot;YearEnd&quot;,DATE( YEAR([Date]),12,31),<br>        &quot;MonthNumber&quot;,MONTH([Date]),<br>        &quot;MonthStart&quot;,DATE( YEAR([Date]), MONTH([Date]), 1),<br>        &quot;MonthEnd&quot;,EOMONTH([Date],0),<br>        &quot;DaysInMonth&quot;,DATEDIFF(DATE( YEAR([Date]), MONTH([Date]), 1),EOMONTH([Date],0),DAY)+1,<br>        &quot;YearMonthNumber&quot;,INT(FORMAT([Date],&quot;YYYYMM&quot;)),<br>        &quot;YearMonthName&quot;,FORMAT([Date],&quot;YYYY-MMM&quot;),<br>        &quot;DayNumber&quot;,DAY([Date]),<br>        &quot;DayName&quot;,FORMAT([Date],&quot;DDDD&quot;),<br>        &quot;DayNameShort&quot;,FORMAT([Date],&quot;DDD&quot;),<br>        &quot;DayOfWeek&quot;,WEEKDAY([Date]),<br>        &quot;MonthName&quot;,FORMAT([Date],&quot;MMMM&quot;),<br>        &quot;MonthNameShort&quot;,FORMAT([Date],&quot;MMM&quot;),<br>        &quot;Quarter&quot;,QUARTER([Date]),<br>        &quot;QuarterName&quot;,FORMAT([Date],&quot;\QQ&quot;),<br>        &quot;YearQuarterNumber&quot;,INT(FORMAT([Date],&quot;YYYYQ&quot;)),<br>        &quot;YearQuarterName&quot;,FORMAT([Date],&quot;YYYY&quot;)&amp;&quot; &quot; &amp; FORMAT([Date],&quot;\QQ&quot;),<br>        &quot;QuarterStart&quot;,DATE( YEAR([Date]), (QUARTER([Date])*3)-2, 1),<br>        &quot;QuarterEnd&quot;,EOMONTH(DATE( YEAR([Date]), QUARTER([Date])*3, 1),0),<br>        &quot;WeekNumber&quot;,WEEKNUM([Date]),<br>        &quot;WeekStart&quot;, [Date]-WEEKDAY([Date])+1,<br>        &quot;WeekEnd&quot;,[Date]+7-WEEKDAY([Date])<br>    )</pre><p>The relationships between the tables in the data model can be seen in the screenshot below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/678/1*E5fxRRpjBBkl4OF5X9hmUQ.png" /><figcaption>The data model</figcaption></figure><p><strong>KEY INSIGHTS<br></strong>For the analysis, DAX functions were employed for calculations. A separate table was created to include all measures created. For example, the measure to calculate total number of complaints is shown below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/401/1*9M1epl8pd_aSXHQN7R0gww.png" /><figcaption>Example showing Calculation of total complaints measure</figcaption></figure><p>Different visuals were used to understand the insights from the data sets. These visuals include bar charts, line chart, map, decomposition tree, card visuals as well as slicers to filter the insights.</p><p><strong>OVERVIEW PAGE<br></strong>The overview page gives an overall look into the distribution of complaints made by consumers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/801/1*bvD-VdpX4LPT7kmrxa4aGA.png" /></figure><ol><li><strong>KPIs summary</strong></li></ol><p>A total of <strong>62,516</strong> consumer complaints were recorded across various products — this is a significant number of customer grievances, suggesting a need for better product/service quality or customer support.</p><p>On average, it took <strong>1.22 days</strong> for a complaint to reach the company, indicating an efficient reporting process.</p><p>Of the total complaints, <strong>97.61%</strong> were resolved, demonstrating a strong resolution rate. However, this leaves <strong>2.39%</strong> of complaints unresolved, which may indicate gaps in the complaint-handling process.</p><p>Among the resolved complaints, <strong>96.06%</strong> were addressed within the expected timeframe, while <strong>3.94%</strong> were delayed. This delay could contribute to customer dissatisfaction and may require further investigation to identify root causes.</p><p><strong>2. What is the distribution of complaints among different submission channels?</strong></p><p>The majority of complaints (<strong>45,423</strong>) were submitted via the <strong>Web</strong>, accounting for a significant portion of total complaints. This suggests that consumers prefer digital channels for lodging complaints, possibly due to ease of access and convenience.</p><p><strong>Referrals</strong> (10,766) and <strong>Phone complaints</strong> (4,684) also play a role, though at much lower volumes.</p><p>Other channels such as <strong>postal mail, fax, and email</strong> have minimal usage, indicating that traditional submission methods are becoming less relevant.</p><p><strong>3. How do complaints vary across products?</strong></p><p>The most complained-about product is <strong>Checking or Savings Accounts (24,814 complaints)</strong>, followed by <strong>Credit Cards (16,197 complaints)</strong> and <strong>Credit Reporting (7,710 complaints)</strong>.</p><p>Issues related to <strong>mortgages (6,601 complaints)</strong> and <strong>money transfers (3,453 complaints)</strong> also appear frequently.</p><p><strong>Student loans and payday loans have the lowest complaints</strong>, but this might be due to a smaller customer base rather than fewer issues.</p><p><strong>4. What is the trend of complaints over time?</strong></p><p>Complaints <strong>spike in July</strong>, peaking at around <strong>6,474 complaints</strong>.</p><p>There is also a steady rise in complaints from <strong>March to June</strong>, followed by a decline towards the end of the year.</p><p>The lowest complaint volumes occur in <strong>December and January</strong>, possibly due to holidays and reduced banking activities.</p><p>On a yearly basis, the highest number of complaints was recorded in <strong>2022</strong> with a total of <strong>12.95K</strong> cases.</p><p><strong>5. What is the distribution of complaints by state?</strong></p><p>Certain states, particularly <strong>California</strong>, have a significantly higher number of complaints (13,709). This could be due to a larger consumer base due to higher population, regional regulations, or product-specific issues.</p><p>Other high-complaint states include <strong>Florida(6,488),</strong> <strong>Texas(4686) and New York(4,442) </strong>which are also major population centres.</p><p><strong>PRODUCT DETAILS PAGE<br></strong>This page gives a deeper understanding into the root cause of complaints made about the various products.</p><p><strong>All Products</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/938/1*JO_v6KMqpWceqdSLNDFalw.png" /><figcaption>Product details page</figcaption></figure><p><strong>6. What is the number of unique sub-products and issues?</strong></p><p>There are a total of 46 sub-products and 76 issues that consumers complain about.</p><p><strong>7. What is the top issue and top sub-product across all products?</strong></p><p>Considering all categories of products, the <strong>most common area of customer complaints</strong> revolves around <strong>checking accounts</strong>, and within that, the biggest issue customers face is <strong>managing their accounts</strong>.</p><p>This suggests customers struggle with routine banking tasks, such as deposits and withdrawals, transaction processing delays, unexpected fees and deductions, account freezes or holds without clear explanations.</p><p>It could also indicate that banking platforms (apps, websites, and customer service) are not user-friendly, making it hard for customers to resolve issues. In addition, companies might not be clearly communicating rules, policies, or processes to customers.</p><p><strong>8. How do companies respond to customers?</strong></p><p><strong>Majority of complaints (41,044 cases) were closed with an explanation</strong>, implying that many issues stem from misunderstandings rather than actual policy violations.</p><p><strong>14,697 cases resulted in monetary relief</strong>, indicating that a significant portion of complaints involve financial disputes where refunds or compensations were warranted.</p><p><strong>5,273 cases were closed with non-monetary relief</strong>, possibly involving account adjustments, goodwill gestures, or policy changes.</p><p><strong>1,494 complaints remain in progress</strong>, which could indicate delays in resolving more complex disputes.</p><p><strong>A Specific Product<br></strong>Through the use of slicer, a specific product can be analysed closely. <br>Taking <strong>Debt Collection</strong> as an example,</p><p><strong>9. What is the top sub-product and issue for the product — Debt Collection?</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/885/1*t3coDS7MuFjRmD7wOU125g.png" /><figcaption>Insights on Product — debt collection</figcaption></figure><p>A total of 2.74K complaints came from debt collection with 9 sub-products and 6 issues peculiar to it. The highest number of complaints comes from the sub-product<strong> Credit card debt</strong> with <strong>Attempts to collect a debt owed</strong> being the leading cause of complaints.</p><p>Many complaints may stem from failure to provide proper debt validation. Additionally, disputes over incorrect or unrecognized debts suggest issues related to outdated records, mistaken identity, or fraudulent charges. A lack of clear communication from lenders regarding outstanding balances, interest accrual, and penalties may also contribute to consumer frustration.</p><p>A high number of complaints<strong> (2,367 cases) </strong>were <strong>closed with an explanation, 209</strong> with <strong>non-monetary relief</strong>, <strong>111</strong> with <strong>monetary relief</strong> and <strong>49</strong> complaints were yet to be resolved.</p><p><strong>RECOMMENDATIONS</strong></p><ol><li><strong>Improve Account Management Processes</strong></li></ol><p>A significant portion of complaints in the Checking or Savings Account category stems from issues related to <em>managing an account</em>. Financial institutions should review and simplify account maintenance procedures, enhance communication around account updates, and ensure digital banking platforms are user-friendly and transparent.</p><p><strong>2. Strengthen Identity Verification and Dispute Resolution</strong></p><p>For debt collection, complaints such as “debt resulted from identity theft” and “debt not owed” point to weak identity verification protocols. Companies should invest in more robust fraud detection systems and streamline their dispute resolution process to minimize wrongful collections and improve trust.</p><p><strong>3. Enhance Timely Response Mechanisms</strong></p><p>While a large percentage of complaints are resolved, timely resolution is slightly lower. Companies should implement automated complaint tracking systems, escalate unresolved cases proactively, and allocate resources strategically during high-volume periods to close this gap.</p><p><strong>4. Focus on State-Level Complaint Hotspots</strong></p><p>States like California and Florida received higher complaint volumes. These regions may require targeted interventions — such as regional customer service training, increased support staff, or educational outreach to reduce misunderstandings and mitigate issues at the source.</p><p><strong>5. Monitor and Optimize Submission Channels</strong></p><p>Most complaints are submitted via web and referrals. Firms should regularly audit these channels for efficiency, responsiveness, and accessibility. Ensuring smooth digital interfaces and responsive call centres can improve the customer experience at the point of contact.</p><p><strong>6. Standardize Internal Classification of Complaints</strong></p><p>Many issues seem to cluster under broad categories like “Incorrect information” or “Managing an account.” Companies should refine how complaints are categorized internally to gain clearer insights into root causes and tailor their solutions more precisely.</p><p><strong>7. Use Data to Anticipate Emerging Trends</strong></p><p>Institutions should adopt a more proactive stance by regularly analyzing complaint trends over time. Identifying new issues early — especially those tied to product updates or policy changes — can prevent escalation and maintain regulatory compliance.</p><p><strong>8. Promote Transparency and Customer Education</strong></p><p>Some complaints may arise from misunderstandings of terms or processes. Financial institutions should prioritize clearer communication in product documentation, terms of service, and regular customer education through webinars, FAQs, or newsletters.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=46f7ad55f6ad" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ANALYSIS OF VEHICLE THEFT IN NEW ZEALAND]]></title>
            <link>https://medium.com/@komobongsilas/analysis-of-vehicle-theft-in-new-zealand-acb0c50439ae?source=rss-fbd74acd569------2</link>
            <guid isPermaLink="false">https://medium.com/p/acb0c50439ae</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[excel]]></category>
            <dc:creator><![CDATA[Komobong Silas]]></dc:creator>
            <pubDate>Sun, 09 Mar 2025 21:05:24 GMT</pubDate>
            <atom:updated>2025-03-09T21:05:24.502Z</atom:updated>
            <content:encoded><![CDATA[<p>Vehicles make transportation easy. Little wonder a lot of people own vehicles. However, the number of vehicle theft cases has been increasing in New Zealand. The essence of this report is to analyse such cases to draw actionable insights and help vehicle owners and authorities to better safeguard their vehicles.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wnIVL-iUe3iYOivvW49msQ.png" /><figcaption>The Vehicle Theft Analysis in New Zealand Dashboard</figcaption></figure><p><strong>INTRODUCTION</strong></p><p>Vehicle theft is a common crime worldwide, and New Zealand is no exception. These thefts lead to financial losses for car owners and puts a strain on insurance companies. Certain regions and vehicle types are more prone to theft, influenced by factors like accessibility, demand for stolen parts, and lack of advanced security features.</p><p>This analysis will explore the patterns of car theft in New Zealand to understand the high-risk areas, the types of vehicles targeted, and the times when thefts are most likely to occur. By doing so, authorities, car owners, and manufacturers will get the right information on how to reduce these cases.</p><p><strong>PROBLEM STATEMENT</strong></p><p>Car theft is still a big problem in New Zealand, leaving car owners to deal with financial losses and emotional stress. Even with efforts to reduce these crimes, certain areas and types of vehicles are targeted more than others. To tackle this issue effectively, there is need to dig deeper into the patterns and reasons behind these thefts and figure out what can be done to prevent them.</p><p><strong>DATASET DESCRIPTION</strong></p><p>The dataset used for this analysis consists of three tables — stolen vehicles, make details and locations. It includes details of reported car thefts in New Zealand over 6-months timeframe (7th October 2021 to 6th April 2022). The details of the columns in the various tables are given below.</p><p><strong>Stolen Vehicles Table (fact table)</strong></p><ul><li>vehicle_id: Unique ID of a stolen vehicle</li><li>vehicle_type: Type of vehicle</li><li>make_id: Matches make_id in the make_details table</li><li>model_year: Model year of vehicle</li><li>vehicle_desc: Description of vehicle</li><li>color: Color of vehicle</li><li>date_stolen: Date the vehicle was stolen (MM/DD/YY)</li><li>location_id: Matches location_id in the locations table</li></ul><p><strong>Make Details Table (dimension table)</strong></p><ul><li>make_id: Unique ID of the make</li><li>make_name: Name of the make</li><li>make_type: Type of make (Standard or Luxury)</li></ul><p><strong>Locations Table (dimension table)</strong></p><ul><li>location_id: Unique ID of the region</li><li>region: Name of the region</li><li>country: Country where the region is located</li><li>population: Population of the region</li><li>density: Density of the region (population / km2)</li></ul><p><strong>DATA CLEANING<br></strong>Cleaning data is an important step in analysis to ensure the dataset does not contain any issues that can affect the outcome of analysis. It involves removing duplicates, handled missing values, and standardizing formats amongst others.<br> <br>Microsoft Excel was used for the cleaning and analysis. The dataset was imported into Excel and the Power Query Editor was used for cleaning and transformation.</p><ul><li><strong>Checking for duplicates:</strong> There were no duplicates in the tables as each row was unique.</li><li><strong>Correcting Data types:</strong> The data type of model_year in the stolen vehicles table was changed from to whole number. It was observed that date_stolen was also in text. However, changing to date data type, some values gave an error due to inconsistent format.</li><li><strong>Standardizing format:</strong> To correct the date_stolen format, new columns were created. The Extract option using delimiter under the <strong>Add Column Tab</strong> was employed here to separate the column into day, month and year.<br>Since the data was in the form MM/DD/YY, the new columns were merged using the Merge columns option in the Add column tab in the format DD/MM/YY and data type was then changed to date with no errors.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*wj9k0tqEcYmIkjnZ1WvtsA.png" /><figcaption>screenshot highlighting the original date_stolen and the newly created columns can be seen below.</figcaption></figure><p>Thereafter, the new date_stolen_ column was reordered to its correct position and the created columns — day, month, year as well as the original date_stolen column was deleted as they were no longer needed.</p><ul><li><strong>Handling missing values</strong>: Upon closer inspection, it was observed that columns vehicle_type, make_id, model_year, vehicle_desc and color columns in the stolen vehicles table all had missing values.</li></ul><p>These missing values were not up to 1% of the entire dataset and therefore were resolved by imputation. The replace option in the transform tab was used to achieve this.<br>For the qualitative data, the missing values were replaced by the mode<br>vehicle_type — Stationwagon , Make_id — 619, Vehicle_desc — HILUX, Color — Silver.<br>For the quantitative data, the model_year was replaced with the median 2005.</p><ul><li><strong>Merging the datasets</strong>: After cleaning the data, to make it easier to work with, the three tables were merged into one using the Merge Queries option in the Home tab.</li></ul><p>The make_details table was first joined to the stolen_vehicles table using the common column — make_id. Thereafter, the locations table was joined to the new merge table using the common column — location_id. The new table was then loaded into Excel for analysis.</p><p>The table loaded into excel consists of 4553 rows and 14 columns in total.</p><p><strong>DATA ANALYSIS<br></strong>Excel Pivot tables, Charts and Slicers were used to draw insights about the dataset.</p><ol><li><strong>Key Performance Indicators (KPIs)</strong></li></ol><p>The analysis shows that the total count of reported vehicle theft cases during the specified time was 4553. To further expand on this, this consists of 25 different vehicle types, 138 vehicle makes, 14 different colors across 13 regions in New Zealand.</p><p><strong>2. Vehicle Types<br>What types of vehicles are most often and least often stolen?</strong></p><p>Considering all theft cases, Station wagons and Saloons were the most stolen vehicles, accounting for 971 and 851 of total cases respectively. On the other hand, Articulated truck and Special purpose vehicles took the lead from behind with 1 reported case each.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/844/1*5dOHtisLYeCCBGXzqgl-3w.png" /></figure><p>The reason for this could be that Station wagons and Saloons are common on the road, making them easier for thieves to find. They are also easier to resell and there is also the high demand for popular vehicles which creates a lucrative market for stolen parts.</p><p><strong>3. Region<br>Which regions experience the most and least number of stolen vehicles?</strong></p><p>Auckland topped the list for the region with the most car thefts with 1638 reported cases, followed by Canterbury with a total of 660 cases. Southland and Nelson regions recorded the least number of stolen vehicles with a total of 26 and 92 reported cases respectively.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/692/1*kIQrOCiYPAJXuhLA_6P2VQ.png" /></figure><p>Auckland’s high population density and vehicle ownership could contribute to its elevated theft rates. Auckland and Canterbury are considered big cities which means a lot of people stay there. Due to the increase in population density, there are more cars on the streets for theft.</p><p><strong>4. Time-Based Patterns<br>On what day of the week is theft cases the highest and what day is it lowest?</strong></p><p>A new column — day of week was added to the table using the function =TEXT (date_stolen, “DDD”) and auto filled down for all rows.</p><p>From analysis, Mondays saw the highest number of thefts — 767, closely followed by Tuesdays — 711. The least number of thefts were recorded on Saturdays — 577 followed by Sundays — 595.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/756/1*LpK9Skxsch4nq3dpo5yNvA.png" /></figure><p>This trend in time could be because people are mostly home during the weekends and have the time to watch their cases unlike when they are busy at work during weekdays where the theft cases is on the high side.</p><p><strong>5. Vehicle Colors<br>What colors are the most popular and least popular for theft?</strong></p><p>Silver and white cars were the most frequently stolen with 1287 and 934 reported cases respectively. The colors Pink and Cream were on the low side with a total of 4 and 9 cases respectively.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/742/1*dQ-xWI69_kA9WAYm45uJaw.png" /></figure><p>The choice of colors for theft could be because Silver and white are popular colors for car owners and therefore more common for theft as it won’t draw attention. However, pink on the other hand is a color that stands out which is probably why the theft count is on the low side.</p><p><strong>6. Car Make<br>Which car makes have been stolen the most?</strong></p><p>Toyota — 731 and Trailer — 543 top the list as the most popular car makes to be stolen. Other makes that made it to top 10 include Nissan, Mazda, Ford, Honda, Mitsubishi, Subaru, Suzuki and Holden.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/756/1*W2TmPpEmevpEsw4r8-2MCw.png" /></figure><p>These car makes are popular and common making them an easy target for car thieves.</p><p><strong>7. Model Year<br>What car model years have been stolen the most?</strong></p><p>From the reported cases, the model years of the reported car theft cases range from 1940–2012. However, the early 2000s with 2005 car models taking the lead seems to be very popular amongst car thieves.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/755/1*CMaNBtgPkjmpoQd_DsNFaA.png" /></figure><p>Older cars with outdated security features were stolen more often, highlighting the need for better vehicle security systems.</p><p><strong>8. Make Type<br>What is the percentage of luxurious cars stolen?</strong></p><p>Luxurious cars only accounted for 4% of the reported cases. The remaining 96% were standard vehicles.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/756/1*2uaI4RiEgSgvi_7HbTxqHA.png" /></figure><p>The reason for this could be linked to the fact that luxurious cars stand out more and will draw attention if stolen unlike standard cars which are very common.</p><p><strong>RECOMMENDATIONS</strong></p><p><strong>Improve Vehicle Security</strong>:</p><ul><li>Promote the use of advanced anti-theft systems, such as GPS trackers, immobilizers, and alarms.</li><li>Encourage car owners to upgrade outdated security systems, especially for older vehicles.</li></ul><p><strong>Public Awareness Campaigns</strong>:</p><ul><li>Launch campaigns to educate the public on theft prevention, such as parking in well-lit areas and using steering wheel locks.</li><li>Raise awareness about high-risk vehicle types and models.</li></ul><p><strong>Enhanced Law Enforcement</strong>:</p><ul><li>Increase police patrols in hotspots like Auckland and Canterbury, especially during high-risk times such as Monday nights.</li><li>Invest in surveillance systems, such as CCTV cameras in public parking areas.</li></ul><p><strong>Collaboration with Manufacturers</strong>:</p><ul><li>Work with car manufacturers to design more theft-resistant features for frequently targeted models.</li><li>Encourage manufacturers to standardize advanced security measures across all new vehicles.</li></ul><p><strong>Data-Driven Policing</strong>:</p><ul><li>Utilize predictive analytics to allocate resources effectively to high-risk areas and times.</li><li>Continuously update and analyse data to identify emerging trends in vehicle theft.</li></ul><p><strong>CONCLUSION</strong></p><p>This analysis sheds light on car theft patterns in New Zealand. Auckland and Bay of Plenty are major hotspots, with stationwagons and saloons being the most targeted vehicles. Temporal patterns suggest that thefts are more likely on Mondays. Understanding these trends can help law enforcement and policymakers take targeted action. Future research could examine links to insurance claims or criminal networks for a deeper understanding.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=acb0c50439ae" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ADVENTUREWORKS2019 — ANALYSIS OF SALES AND CUSTOMER DATA]]></title>
            <link>https://medium.com/@komobongsilas/adventureworks2019-analysis-of-sales-and-customer-data-fd9c92dfc67b?source=rss-fbd74acd569------2</link>
            <guid isPermaLink="false">https://medium.com/p/fd9c92dfc67b</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[sql]]></category>
            <dc:creator><![CDATA[Komobong Silas]]></dc:creator>
            <pubDate>Fri, 07 Mar 2025 19:15:56 GMT</pubDate>
            <atom:updated>2025-03-07T19:15:56.553Z</atom:updated>
            <content:encoded><![CDATA[<h3>ADVENTUREWORKS2019 — ANALYSIS OF SALES AND CUSTOMER DATA</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tClVYy62QrNYUVXdkUTIKA.png" /></figure><p><strong>INTRODUCTION</strong></p><p>This work covers the exploration and analysis of the AdventureWorks2019 database to gain insights on sales trend as well as customer behaviour. The results of the analysis will be useful in making decisions and recommendations.</p><p><strong>AdventureWorks2019</strong> is a sample Online Transaction Processing (or OLTP) database provided by Microsoft. The database supports a manufacturing MNC named Adventure Works Cycles. It has proven to be an efficient database for practicing and learning SQL.</p><p>You can follow the link<strong> </strong><a href="https://learn.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&amp;tabs=ssms"><strong>here</strong></a> for details on downloading and restoring the database.</p><p>The analysis was carried out using SQL Server and necessary visualizations were carried out in Excel.</p><p><strong>DATA DESCRIPTION</strong></p><p>The database itself consists of about 72 tables. However, for the purpose of this work, only 9 of these tables are used:</p><ul><li><strong>Sales.SalesOrderHeader</strong><br>It contains general information about individual sale orders. The primary key is the SalesOrderID which uniquely identifies each record in the table. This column is referenced from the Sales.SalesOrderDetail table. The CustomerID, SalesPersonID and TerritoryID columns link to their respective tables.</li><li><strong>Sales.SalesOrderDetail</strong><br>It contains information about individual products associated with a specific sales order. The primary key is SalesOrderDetailID. The SalesOrderID links to the Sales.SalesOrderHeader table.</li><li><strong>Sales.Customer</strong><br>Contains customer records. The primary key is the CustomerID which links to the SalesOrderHeader table. The PersonID column links to the Person.Person table.</li><li><strong>Sales.SalesPerson</strong><br>It contains Salespersons records. The primary key is BusinessEntityID which links from Sales.SalesOrderHeader on SalesPersonID.</li><li><strong>Sales.SalesTerritory</strong><br>This table looks up the various sales territories. TerritoryID is the primary key which is linked from the Sales.Customer, Sales.SalesOrderHeader and Sales.SalesPerson tables.</li><li><strong>Production.Product </strong><br>It contains details about the various products sold such as Name, Colour. The ProductID column is the primary key which links this table to the Sales.SalesOrderDetail table. The ProductSubcategoryID column links to the Production.ProductSubCategory table.</li><li><strong>Production.ProductSubCategory</strong><br>This table contains details about product sub -categories. The ProductSubcategoryID is the primary key which links to the Production.Product table. The ProductCategoryID column links to Production.ProductCategory table.</li><li><strong>Production.ProductCategory</strong><br>It contains details about the product categories. The primary key is the ProductCategoryID which links it to Production.ProductSubCategory table.</li><li><strong>Person.Person</strong><br>It includes customer and employee basic information such as First Name and Last Name. The primary key in this table is the BusinessEntityID which can be used to look up person records. It links to the Sales.Customer table.</li></ul><p>The Entity Relationship Diagram (ERD) showing the connections of the table is shown below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*69y3DsMeBV5DvBg7UcbWRg.png" /><figcaption>ERD diagram for the tables</figcaption></figure><p><strong>QUESTIONS</strong></p><p><strong>Customer Behaviour Analysis</strong></p><ol><li>Retrieve the <strong>top 10 customers</strong> by total purchase amount.</li></ol><pre>--this query retrieves the customerid, firstname, lastname and total purchase amount <br> --of top 10 customers with the highest purchase amount.<br>SELECT TOP 10<br> c.CustomerID,<br> p.FirstName, <br> p.LastName,<br> ROUND(SUM(soh.TotalDue),2) AS purchase_amount<br>FROM Sales.Customer AS c<br>JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID =soh.CustomerID<br>JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID<br>GROUP BY c.CustomerID, p.FirstName, p.LastName<br>ORDER BY purchase_amount DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/752/1*kCZAKbr7unVULQZabQBdMg.png" /></figure><ul><li>The top 10 performing customers spent between $820K — $990K in purchasing products with Roger Harui leading with $989.2K.</li><li>It is worthy of note that the top 5 customers spend over $900K compared to the 6th to 10th places.<br>Since these customers already spend significantly, they may be open to premium products, bundles, or exclusive offers.</li></ul><p>2. Find customers who have made <strong>repeat purchases of the same product on different orders</strong>.</p><pre>--the cte retreives, customerid, productname and the number of unique orders each product waas bought by the customer.<br>WITH repeat_purchases AS<br> (<br>  SELECT <br>   c.CustomerID,<br>   pp.FirstName,<br>   pp.LastName,<br>   p.Name AS product_name, <br>   COUNT(DISTINCT sod.SalesOrderID) AS number_orders<br>  FROM Sales.Customer AS c<br>  JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID<br>  JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID<br>  JOIN Production.Product AS p ON sod.ProductID = p.ProductID<br>  JOIN Person.Person AS pp ON c.PersonID = pp.BusinessEntityID<br>  GROUP BY c.CustomerID, pp.FirstName, pp.LastName, p.Name<br> )<br>SELECT<br> DISTINCT CustomerID,<br> FirstName,<br> LastName<br>FROM repeat_purchases<br>WHERE number_orders &gt; 1 -- filtering for customers who have made more than 1 order for a product<br>ORDER BY CustomerID;<br><br>--total number of customers<br>SELECT COUNT(*) FROM Sales.Customer; -- 19820 customers</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9ReEP1NRYHkAq9shytUWdw.png" /></figure><ul><li>This query returned only 1,153 rows. This means that out of 19820 customers, only 1,153 made a repeat purchase of the same product on different orders.</li><li>If it’s a durable product like bikes, customers may not need a replacement anytime soon. Also, customers might feel the item is not worth the price for a repeat buy.</li><li>Also, Lack of Customer Engagement &amp; Follow-Ups such as not sending promotions, reminders, or personalized offers may contribute to customers forgetting about the product.</li></ul><p>3. List customers whose <strong>spending has dropped by more than 30% compared to the previous year</strong>.</p><pre>/*--cte retrieves the customerid, current_year, previous_year, the amount spent in currentyear and previous years <br>and the percentage change in spending for each customer based on the previous year.<br>*/<br>WITH customer_spending AS<br> (<br>  SELECT <br>   c.CustomerID,<br>   pp.FirstName,<br>   pp.LastName,<br>   YEAR(soh.OrderDate) AS current_year,<br>   LAG(YEAR(soh.OrderDate)) OVER(PARTITION BY c.CustomerID ORDER BY YEAR(soh.OrderDate)) AS previous_year,<br>   SUM(soh.TotalDue) AS current_year_spend, <br>   LAG(SUM(soh.TotalDue)) OVER(PARTITION BY c.CustomerID ORDER BY YEAR(soh.OrderDate)) AS previous_year_spend<br>  FROM Sales.Customer AS c<br>  INNER JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID<br>  INNER JOIN Person.Person AS pp ON c.PersonID = pp.BusinessEntityID<br>  GROUP BY c.CustomerID, pp.FirstName, pp.LastName, YEAR(soh.OrderDate)<br> ),<br>year_over_year_change AS<br> (<br>  SELECT <br>   CustomerID,<br>   FirstName,<br>   LastName,<br>   current_year,<br>   previous_year,<br>   current_year_spend,<br>   previous_year_spend,<br>   100 *(current_year_spend - previous_year_spend)/NULLIF(previous_year_spend,0) AS yoy_change<br>  FROM customer_spending<br> )<br>SELECT<br> DISTINCT CustomerID,<br> FirstName,<br> LastName<br>FROM year_over_year_change<br>--filtering for where the yoy change is below 30% (-30). negative 30 because it dropped.<br>WHERE yoy_change &lt; -30; </pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2D4mwNscgXH4gQXYyUMfSQ.png" /></figure><ul><li>The query returned 2,947 rows. This implies that when compared to the previous year, the total amount spent by 2947 customers in the current year dropped by 30% or more. This number accounts for about 14% of the entire customer base.</li><li>A decline at this scale could indicate broader market issues, economic downturns, or increased competition. Customers may have less disposable income due to inflation, job losses, or economic downturns. It could also be that competitors are offering cheaper alternatives, discounts, or loyalty programs.</li></ul><p>4. Identify the <strong>average number of days</strong> between repeat purchases for each customer.</p><pre>--Checking for customers who have been to the store more than once.<br>WITH repeat_purchases AS<br> (<br>  SELECT <br>   c.CustomerID,<br>   pp.FirstName,<br>   pp.LastName,<br>   soh.OrderDate AS current_purchase_date, <br>   LEAD(soh.OrderDate) OVER(PARTITION BY c.CustomerID ORDER BY soh.OrderDate) AS next_purchase_date<br>  FROM Sales.Customer AS c<br>  JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID =soh.CustomerID<br>  JOIN Person.Person AS pp ON c.PersonID = pp.BusinessEntityID<br> )<br>SELECT <br> CustomerID,<br> FirstName,<br> LastName,<br> COUNT(next_purchase_date) AS number_repeat_purchases, <br> AVG(DATEDIFF(DAY, current_purchase_date, next_purchase_date)) AS average_days_to_purchase<br>FROM repeat_purchases<br>--filter to ensure a customer returned after first visit<br>WHERE next_purchase_date IS NOT NULL<br>GROUP BY CustomerID, FirstName, LastName;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HdD5ctJyj5xS5CF72EpaqQ.png" /><figcaption>Snippet of results returned</figcaption></figure><p>A total of 7,470 customers returned for another purchase after their first time in the store.</p><p>From the snippet of the results,</p><ul><li>Most customers have only 1 repeat purchase, meaning they may not return to buy again. Julio Suarez and Ernest Zeng have 2 repeat purchases, showing slightly higher retention.</li><li>Jon Yuan has 730 days between purchases (around 2 years), meaning he buys very infrequently. Brittney Liu has only 2 days between purchases, suggesting a very fast repeat cycle. This variation suggests different customer segments with distinct purchasing habits.</li><li>Most customers take months or years to make repeat purchases (e.g., Katelyn Scott: 475 days, Gabriella Torres: 461 days). This may indicate that the product has a long lifecycle, meaning customers don’t need to buy frequently.</li></ul><p>5. Find the top <strong>5 most common product categories purchased</strong> by customers.</p><pre>--grouping product categories based on quantity sold<br>SELECT TOP 5 <br> pc.ProductCategoryID, <br> pc.Name AS ProductCategory_name, <br> SUM(sod.OrderQty) AS number_purchases<br>FROM Sales.SalesOrderDetail AS sod<br>JOIN Production.Product AS p ON sod.ProductID = p.ProductID<br>JOIN Production.ProductSubcategory AS sc ON p.ProductSubcategoryID = sc.ProductSubcategoryID<br>JOIN Production.ProductCategory AS pc ON pc.ProductCategoryID = sc.ProductCategoryID<br>GROUP BY pc.ProductCategoryID, pc.Name<br>ORDER BY number_purchases DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/866/1*HEpKB5A6Hqx87uUvqa2qaA.png" /></figure><p>The store has only 4 product categories in total. Based on quantity sold, Bikes lead with a total of 90.3K — this suggests bikes are the core revenue driver. It is followed by Clothing 73.7K, Accessories 61.9K and Components come behind with only 49.0K. It can be concluded that customers are more interested in purchasing bikes and clothing.</p><p><strong>Sales Performance Analysis</strong></p><p>6. Calculate <strong>total monthly sales revenue</strong> over the past three years.</p><pre>--checking minimum and maximum orderdates<br>SELECT MIN(OrderDate), MAX(OrderDate) --Ans; 2011-05-31 and 2014-06-30<br>FROM sales.SalesOrderHeader<br><br>--Using the logic that 3 years ago is 36 months ago from the max order date<br>SELECT <br> YEAR(OrderDate) AS OrderYear, <br> MONTH(OrderDate) AS OrderMonth,<br> SUM(TotalDue) AS revenue<br>FROM Sales.SalesOrderHeader<br>WHERE DATETRUNC(MONTH, OrderDate) &gt;= --subquery to get the last 3 years (36 months).<br> (<br>  SELECT<br>   DATEADD(MONTH, -35, DATETRUNC(MONTH, MAX(OrderDate)))<br>  FROM Sales.SalesOrderHeader<br> )<br>GROUP BY YEAR(OrderDate), MONTH(OrderDate)<br>ORDER BY OrderYear, OrderMonth;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*97uhEUoon3bRz1BtA8z0pA.png" /></figure><p>The past 3 years (36 months) covers the time period of July 2011 to June 2014.</p><ul><li>There is growth from 2011 to 2013. In 2011, revenue was inconsistent, with a huge jump in October ($5.16M) but low values in other months.<br>2012 saw steadier growth, peaking at $4.61M in June. 2013 performed even better, with revenue surpassing $5M in June, September, and October.</li><li>January 2014 started strong ($4.8M), but revenue collapsed in June 2014 to just $54K. This is a massive drop compared to May 2014 ($6M).<br>This drop could be because of business slowdown or seasonality, loss of a major contract or client, market changes impacting revenue.</li><li>In terms of seasonality changes, revenue drops in February every year: 2012: $1.64M, 2013: $2.6M, 2014: $1.47M (even lower than 2012.)<br>March and May tend to be strong months, while August and November are weaker.</li><li>2013 Was the Best Year. Revenue peaked at $5.72M in June 2013 (highest monthly revenue recorded). Most months in 2013 had higher revenue than 2012. However, 2014 started strong but declined sharply.</li></ul><p>7. Identify the <strong>most profitable products</strong> in terms of total revenue.</p><pre>--this query retrieves the top 10 products based on total revenue generated<br><br>SELECT TOP 10<br> p.Name AS product_name, <br> SUM(sod.LineTotal) AS revenue<br>FROM Sales.SalesOrderDetail AS sod<br>JOIN Production.Product AS p ON sod.ProductID = p.ProductID<br>GROUP BY p.Name<br>ORDER BY revenue DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/752/1*6Po-DBRRRFKiTbL5fZcXBg.png" /></figure><p>The top 10 most profitable products in terms of total revenue generated are mountain bikes and road bikes. This top 10 reiterates the notion that customers buy bikes more.</p><ul><li>Mountain-200 series dominates. The top 6 products are all from the Mountain-200 series. The highest-selling product is Mountain-200 Black, 38 with $4.40M in revenue.<br>Silver variants are also performing well, but Black variants seem to generate more revenue.</li><li>Road-250 and Road-150 series are trailing. The Road-250 Black series appears in the 8th, 9th, and 10th positions. The Road-150 Red, 56 is at the bottom with $1.85M in revenue.</li><li>The revenue gap between the top product and the lowest is significant ($2.55M difference).</li></ul><p>8. Determine the <strong>top 5 sales representatives</strong> based on total revenue generated.</p><pre>--this query retrieves the top 5 sales representatives with the highest revenue<br><br>SELECT TOP 5<br> sp.BusinessEntityID AS SalesPersonID,<br> pp.FirstName,<br> pp.LastName,<br> SUM(TotalDue) AS revenue<br>FROM Sales.SalesPerson AS sp<br>JOIN Sales.SalesOrderHeader AS soh ON soh.SalesPersonID = sp.BusinessEntityID<br>JOIN Person.Person  AS pp ON sp.BusinessEntityID = pp.BusinessEntityID<br>GROUP BY sp.BusinessEntityID, pp.FirstName, pp.LastName<br>ORDER BY revenue DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/752/1*QzHm1TQDFoP0QJPLo3aiRA.png" /></figure><p>The top 5 Salespersons brought in total revenue between $8M and $12M.</p><ul><li>Linda Mitchell leads with $11.70M, followed closely by Jillian Carson at $11.34M. Both are likely the top performers, significantly contributing to overall sales.</li><li>Michael Blythe ($10.48M) and Jae Pak ($9.59M) are in the mid-tier but still strong performers.</li><li>Tsvi Reiter, at $8.09M, has the lowest revenue among the top 5 but is still a key contributor.</li><li>The revenue gap between the top and bottom of the list is around $3.6M, suggesting a performance difference but not a huge disparity.</li></ul><p>9. Calculate the <strong>year-over-year sales growth rate</strong>.</p><pre>--this query retrieves the current year, previous year, accompanying sales revenue <br>--and the percentage change in sales compared to the preevious year<br><br>WITH yearly_sales AS<br> (<br>  SELECT<br>   YEAR(OrderDate) AS current_year,<br>   SUM(TotalDue) AS current_year_sales, <br>   LAG(SUM(TotalDue)) OVER(ORDER BY YEAR(OrderDate)) AS previous_year_sales<br>  FROM  Sales.SalesOrderHeader<br>  GROUP BY YEAR(OrderDate)<br> )<br>SELECT<br> current_year,<br> current_year_sales,<br> previous_year_sales,<br> 100* (current_year_sales - previous_year_sales)/NULLIF(previous_year_sales,0) AS yoy_change<br>FROM yearly_sales;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/747/1*RcgYx1e0x2CU0bU2TOQTjg.png" /></figure><ul><li>Strong Growth from 2011 to 2013. Revenue surged 166% in 2012, suggesting a major expansion, possibly due to increased sales, new product launches, or successful marketing efforts. Growth continued in 2013, though at a slower 30% YoY increase, indicating a more stabilized but still positive trajectory.</li><li>Sharp Decline in 2014 (-54%). The drastic revenue drop in 2014 wiped out much of the previous years’ gains. This could be due to market downturns, loss of key customers, operational issues, increased competition, or economic factors.</li></ul><p>10. Identify the top <strong>5 regions</strong> where AdventureWorks generates the most revenue.</p><pre>--this query retrieves the top 5 regions with the highest revenue<br>SELECT TOP 5<br> CONCAT(st.Name, &#39;, &#39;, st.[Group]) AS Region,<br> SUM(soh.TotalDue) AS revenue<br>FROM Sales.SalesOrderHeader AS soh<br>JOIN Sales.SalesTerritory As st ON soh.TerritoryID = st.TerritoryID<br>GROUP BY CONCAT(st.Name, &#39;, &#39;, st.[Group])<br>ORDER BY revenue DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/787/1*wAK_KBnm2_Lk84v4n_MbDA.png" /></figure><ul><li>Southwest, North America dominates with $27.15M, significantly outperforming other regions.</li><li>Canada ($18.40M) and Northwest, North America ($18.06M) are closely matched, suggesting strong sales performance in these areas.<br>Australia, Pacific ($11.81M) and Central, North America ($8.91M) generate lower revenue but still rank in the top five.</li><li>The gap between the top region (Southwest) and the lowest in the top five (Central) is nearly $18.2M, showing a stark difference in sales distribution.</li></ul><p><strong>Product and Inventory Insights</strong></p><p>11. Find <strong>products frequently purchased together</strong> (appear in the same order).</p><pre> --considering top 10 of three-products purchased in the same order. <br> --This query retrieves top 10 of a combination of 3 products purchased in the same order based on the number of orders<br>WITH frequently_purchased AS <br> (<br>  SELECT<br>   sod1.ProductID AS product1,<br>   sod2.ProductID AS product2,<br>   sod3.ProductID AS product3,<br>   COUNT(DISTINCT sod1.SalesOrderID) AS number_orders<br>  FROM Sales.SalesOrderDetail AS sod1<br>  --self joins to ensure orderid is the same and productids are different<br>  JOIN Sales.SalesOrderDetail AS sod2 ON sod1.SalesOrderID = sod2.SalesOrderID AND sod1.ProductID &lt; sod2.ProductID<br>  JOIN Sales.SalesOrderDetail AS sod3 ON sod2.SalesOrderID = sod3.SalesOrderID AND sod2.ProductID &lt; sod3.ProductID<br>  GROUP BY sod1.ProductID, sod2.ProductID, sod3.ProductID<br> )<br>SELECT TOP 10<br> p1.Name AS product_1, <br> p2.Name AS product_2,<br> p3.Name AS product_3,<br> fp.number_orders<br>FROM frequently_purchased AS fp<br>JOIN Production.Product AS p1 ON fp.product1 = p1.ProductID<br>JOIN Production.Product AS p2 ON fp.product2 = p2.ProductID<br>JOIN Production.Product AS p3 ON fp.product3 = p3.ProductID<br>ORDER BY number_orders DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/544/1*W8QNwYYGbmiFM4vzOUdLLA.png" /></figure><p>In terms of top 10 of 3-items frequently bought together by customers, Clothing(Jerseys and Caps) and Accessories(Helmets) top the list.</p><p>Helmets are an important accessory as they are found in all top 10 of 3-product triples. This is a clear indication that customers are safety conscious for when they want to ride their bikes.</p><p>12. Retrieve products that have <strong>never been sold</strong>.</p><pre>--this query retrives all products that are not in the salesorderdetail table.<br>--A product that is not in that table has not been sold<br><br>SELECT<br> p.Name AS product_name<br>FROM Production.Product AS p <br>LEFT JOIN Sales.SalesOrderDetail AS sod ON sod.ProductID = p.ProductID<br>WHERE sod.ProductID IS NULL -- filters for products not in salesorderdetail table.<br>ORDER BY p.Name;<br><br>SELECT COUNT(*) FROM Production.Product; -- 504 products</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pqqLCQwx9Y0u0HeSLTzSFg.png" /></figure><ul><li>The results show that 238 out of a total of 504 products have never been sold. This accounts for about 47% of all products. A large portion of products are stagnant, which could lead to storage costs and potential losses.</li><li>Majority of the products on the list are accessories or components. This further adds to the point that these product categories are not often sought after by customers. The business may be stocking items that do not align with customer preferences or seasonal demand.</li><li>These unsold products may not be getting enough visibility, marketing, or discounts to drive sales.</li></ul><p>13. Find the <strong>best-selling product in each category</strong>.</p><pre>--based on quantity sold<br>--the cte ranks products in each categroy based on the quantity sold<br><br>WITH product_Category_rank AS<br> (<br>  SELECT<br>   p.Name AS product_name,<br>   pc.Name AS product_category, <br>   SUM(sod.OrderQty) AS quantity_sold,<br>   RANK() OVER(PARTITION BY pc.Name ORDER BY SUM(sod.OrderQty) DESC) AS rn<br>  FROM Sales.SalesOrderDetail AS sod<br>  JOIN Production.Product AS p ON sod.ProductID = p.ProductID<br>  JOIN Production.ProductSubcategory AS sc ON p.ProductSubcategoryID = sc.ProductSubcategoryID<br>  JOIN Production.ProductCategory AS pc ON pc.ProductCategoryID = sc.ProductCategoryID<br>  GROUP BY p.Name, pc.Name<br> )<br>SELECT<br> product_name, product_category<br>FROM product_Category_rank<br>WHERE rn = 1; --filtering for products that are in the top 1 rank in their categories</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/309/1*jTiFggvJWxdEQkkzryZhwA.png" /><figcaption>Best selling products per category</figcaption></figure><ul><li>The <em>Mountain-200 Black, 38</em> bike is the top performer in the Bikes category. This suggests strong demand for this specific model — consideration should be made in expanding stock or promoting similar models.</li><li>The <em>AWC Logo Cap</em> is the best-selling item in Clothing, which could mean branded merchandise is popular — opportunities exist for cross-selling with other apparel.</li><li><em>Water Bottle — 30 oz.</em> leads Accessories, indicating high demand for hydration products. Bundling it with bikes or gym accessories could boost sales.</li><li><em>LL Road Frame — Black, 52</em> is the top-selling Component, showing demand for individual parts. Promoting related components might increase revenue.</li></ul><p>14. Identify the <strong>most returned products</strong> based on order cancellations.</p><pre>SELECT TOP 10<br> p.Name AS product_name, <br> COUNT(soh.Status) AS number_returned<br>FROM Sales.Customer AS c<br>JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID =soh.CustomerID<br>JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID<br>JOIN Production.Product AS p ON sod.ProductID = p.ProductID<br>WHERE soh.Status = 6 -- 6 represents cancelled orders from the data dictionary<br>GROUP BY p.Name<br>ORDER BY number_returned DESC;</pre><p>The query did not return any results as no product was returned.</p><p>15. Find products with the <strong>highest order quantities but lowest total revenue</strong>.</p><pre>--considering the products in top 100 order quantities and bottom 100 in revenue.<br>--this cte ranks products based on quantity in descending order(highest to lowest) and revnenue in ascending order(lowest to highest)<br><br>WITH product_rank AS<br> (<br>  SELECT<br>   p.Name AS product_name, <br>   SUM(sod.OrderQty) AS order_quantity,<br>   SUM(sod.LineTotal) AS revenue,<br>   DENSE_RANK() OVER(ORDER BY SUM(sod.OrderQty) DESC) AS quantity_rank_desc,<br>   DENSE_RANK() OVER(ORDER BY SUM(sod.LineTotal)) AS revenue_rank_asc<br>  FROM Sales.SalesOrderDetail AS sod<br>  JOIN Production.Product AS p ON sod.ProductID = p.ProductID<br>  GROUP BY p.Name<br> )<br>SELECT product_name<br>FROM product_rank<br>WHERE quantity_rank_desc &lt;=100 AND revenue_rank_asc &lt;=100; --filtering for products in top 100 rank of revenue and quantity</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*82lU-OcSZdIkDrA_BXp9Dw.png" /></figure><p>Out of a total of 504 products, 32 items fell into this category. This implies that these items are in the top 100 when quantity sold is considered but however fall into bottom 100 when revenue is considered.</p><p>These products contribute significantly to sales volume but have low revenue impact due to their lower unit price.</p><p><strong>Advanced Business Insights</strong></p><p>16. Calculate the <strong>monthly sales growth rate</strong> using a window function.</p><pre>WITH monthly_sales AS<br> (<br>  SELECT<br>   CAST(DATETRUNC(MONTH, OrderDate) AS DATE) AS month_,<br>   SUM(TotalDue) AS month_sales, <br>   LAG(SUM(TotalDue)) OVER(ORDER BY DATETRUNC(MONTH, OrderDate)) AS previous_month_sales<br>  FROM  Sales.SalesOrderHeader<br>  GROUP BY DATETRUNC(MONTH, OrderDate)<br> )<br>SELECT<br> month_,<br> month_sales,<br> previous_month_sales,<br> 100 * (month_sales - previous_month_sales)/NULLIF(previous_month_sales,0) AS mom_change<br>FROM monthly_sales;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S3F6iaSqHtMBnhEZP4GRhQ.png" /><figcaption>Excerpt showing the month over month change for 2013</figcaption></figure><p>Considering the year 2013,</p><ul><li>The revenue growth fluctuates significantly, with some months experiencing high positive percentage increase (e.g., June increased by 57% compared to May) and others showing sharp declines (e.g., August revenue dropped by 32% compared to July). This suggests that the business experiences volatility in sales.</li><li>The months of February, March, May, June, September and December saw strong growth, indicating potential seasonal factors, promotions, or increased customer demand.</li><li>April (-26%), August (-32%), November (-31%) had significant drops. These could be due to post-peak sales slumps, industry cycles, or external factors like economic conditions.</li><li>After the drop in November, revenue rebounded in December (+23%), which might be linked to holiday sales or year-end promotions.</li></ul><p>17. Identify <strong>customers who purchased the same product multiple times</strong> in different orders.</p><pre>WITH repeat_purchases AS<br> (<br>  SELECT <br>   c.CustomerID,<br>   pp.FirstName,<br>   pp.LastName,<br>   p.Name AS product_name, <br>   COUNT(DISTINCT sod.SalesOrderID) AS number_orders<br>  FROM Sales.Customer AS c<br>  JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID<br>  JOIN Sales.SalesOrderDetail AS sod ON soh.SalesOrderID = sod.SalesOrderID<br>  JOIN Production.Product AS p ON sod.ProductID = p.ProductID<br>  JOIN Person.Person AS pp ON c.PersonID = pp.BusinessEntityID<br>  GROUP BY c.CustomerID, pp.FirstName, pp.LastName, p.Name<br> )<br>SELECT *<br>FROM repeat_purchases<br>WHERE number_orders &gt; 1 -- filtering for customers who have made more than 1 order for a product<br>ORDER BY CustomerID, product_name;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xo3U_fTVbHqZZLWj_wB6vw.png" /></figure><p>Here, 1,153 customers purchased the same product multiple times in different orders.</p><p>18. Compute a <strong>3-month moving average revenue per sales representative</strong>.</p><pre>SELECT<br> sp.BusinessEntityID AS SalesPersonID,<br> CAST(DATETRUNC(MONTH, OrderDate) AS DATE) AS month_,<br> SUM(TotalDue) AS month_sales, <br> AVG(SUM(TotalDue)) OVER(PARTITION BY sp.BusinessEntityID ORDER BY DATETRUNC(MONTH, OrderDate) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS threemonth_movingavg<br>FROM Sales.SalesPerson AS sp<br>JOIN Sales.SalesOrderHeader AS soh ON soh.SalesPersonID = sp.BusinessEntityID<br>GROUP BY sp.BusinessEntityID ,DATETRUNC(MONTH, OrderDate);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/386/1*OZoR3W9AseSJe_sfmrx9qw.png" /><figcaption>Snippet of the results</figcaption></figure><p>The query returned 423 rows containing three-month moving average for each salesperson.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/926/1*W3s7yF-lhe3Iom6sEq187g.png" /><figcaption>3-month moving average monthly sales for salespersonid 274</figcaption></figure><p>Taking Salesperson id 274 for example, there is a lot of fluctuations with large spikes in months like in March 2014 — almost $160K and June 2013 — around $ 145K. On the other hand, big dips like in July 2012 and January 2014 where its barely $5K. Some months appear to be missing which could indicate gaps in data collection, irregular sales reporting, or months with zero sales.</p><p>The 3-month moving average smooths out extreme spikes and provides a clearer trend. For instance, June 2013’s peak is followed by a strong moving average ($51K), confirming sustained growth. March 2014 peak results in a moving average of $79K, suggesting some consistency.</p><p>Strong upward trends in late 2012 &amp; mid-2013 could indicate successful promotions, seasonal demand, or better performance. Sharp drops in early 2013 &amp; early 2014 might indicate off-seasons, economic shifts, or internal sales challenges.</p><p>The salesperson might be working on commission-based deals, causing inconsistent sales. External factors like market demand, product availability, or economic conditions could also be influencing sales.</p><p>19. Find <strong>frequently purchased product pairs</strong> using self-joins.</p><pre>--product pairs means 2 products<br>WITH frequently_purchased AS <br> (<br>  SELECT<br>   sod1.ProductID AS product1,<br>   sod2.ProductID AS product2,<br>   COUNT(DISTINCT sod1.SalesOrderID) AS number_orders<br>  FROM Sales.SalesOrderDetail AS sod1<br>--self join to check for same orderId and inequality to avoid duplicate pairs<br>  JOIN Sales.SalesOrderDetail AS sod2 ON sod1.SalesOrderID = sod2.SalesOrderID AND sod1.ProductID &lt; sod2.ProductID <br>  GROUP BY sod1.ProductID, sod2.ProductID<br> )<br>SELECT TOP 10<br> p1.Name AS product_1, <br> p2.Name AS product_2, <br> fp.number_orders<br>FROM frequently_purchased AS fp<br>JOIN Production.Product AS p1 ON fp.product1 = p1.ProductID<br>JOIN Production.Product AS p2 ON fp.product2 = p2.ProductID<br>ORDER BY number_orders DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/416/1*bkYANdSJmTtu_tJw6b6KRA.png" /><figcaption>Top 10 frequently bought product pairs</figcaption></figure><ul><li>In terms of product pairs, Bottles and Cages are in top 2 with the highest orders of over 1500. This suggests that customers buying water bottles almost always buy bottle cages, making them perfect cross-sell opportunities</li><li>The AWC Cap Logo Cap appears in 5 out of the 10 top pairs. Most notably, it is paired with: Long-Sleeve Logo Jersey, L (1,172 orders), Water Bottle — 30 oz. (1,019 orders), Sport-100 Helmet (various colours, around 1,000 times each). This suggests that customers frequently buy this cap alongside other cycling gear.</li><li>Helmets &amp; Jerseys Are Frequently Paired. This indicates that helmets, caps, and jerseys are part of a common shopping pattern, likely for cyclists buying full gear sets.</li><li>Mountain Bike Accessories Are Linked. Mountain Tire Tube &amp; HL Mountain Tire (957 orders) show that customers buying mountain tires often purchase tubes as well. This suggests a strong product dependency, making it a great bundle option.</li></ul><p>20. Detect customers whose <strong>spending has declined significantly over time</strong>.</p><pre>--Checking for customers whose spending has drastically reduced in 2 consecutive years.<br>WITH customer_spending AS<br> (<br>  SELECT <br>   c.CustomerID,<br>   p.FirstName,<br>   p.LastName,<br>   YEAR(soh.OrderDate) AS year_,<br>   SUM(soh.TotalDue) AS year_spend, <br>   LAG(SUM(soh.TotalDue)) OVER(PARTITION BY c.CustomerID ORDER BY YEAR(soh.OrderDate)) AS previous_year_spend<br>  FROM Sales.Customer AS c<br>  JOIN Sales.SalesOrderHeader AS soh ON c.CustomerID = soh.CustomerID<br>  JOIN Person.Person AS p ON c.PersonID = p.BusinessEntityID<br>  GROUP BY c.CustomerID, p.FirstName, p.LastName, YEAR(soh.OrderDate)<br> ),<br>yoy_change AS<br> (<br>  SELECT <br>   CustomerID,<br>   FirstName,<br>   LastName,<br>   year_,<br>   year_spend,<br>   previous_year_spend,<br>   100 *(year_spend - previous_year_spend)/NULLIF(previous_year_spend,0) AS yoy2_change<br>  FROM customer_spending<br> ),<br>two_years_yoy_change AS<br> (<br>  SELECT <br>   CustomerID,<br>   FirstName,<br>   LastName,<br>   year_,<br>   year_spend,<br>   previous_year_spend,<br>   yoy2_change AS year2_spend_percent_change,<br>   LEAD(yoy2_change) OVER(PARTITION BY CustomerID ORDER BY year_) AS year3_spend_percent_change<br>  FROM yoy_change  <br> )<br>SELECT<br> CustomerID,<br> FirstName,<br> LastName,<br> year2_spend_percent_change,<br> year3_spend_percent_change<br>FROM two_years_yoy_change<br>--checking for when the spend percent reduces consecutively compared to previous year<br>WHERE 0 &gt; year2_spend_percent_change -- to filter for where the spend reduced in second year compared to first year<br> AND year2_spend_percent_change &gt; year3_spend_percent_change; --to filter where spend reduced in third year compared to second year </pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*awXFKO0VPytABaGE9ZY1Bg.png" /></figure><p>There are 193 customers whose spending reduced drastically over 2 consecutive years. This suggests a pattern of declining engagement, not just a one-time dip.</p><p>For example, Eugene Huang spending dropped by 28.29% in year 2 and dropped even further by 75.67% in year 3 indicating a major shift in behaviour (possibly stopped purchasing altogether).</p><p>These customers might be at risk of completely stopping purchases. They might have switched to a competitor, a negative experience might have led to declining purchases or they may have reduced spending due to financial constraints.</p><p><strong>RECOMMENDATIONS</strong></p><p><strong>Increase Repeat Purchases </strong>— With only 5% of customers repurchasing certain products, introduce loyalty programs, personalized discounts, or targeted email campaigns to encourage repeat sales.</p><p><strong>Address Customer Drop-Off </strong>— Since 14% of customers reduced spending by over 30%, analyze their purchase history and offer win-back incentives, targeted promotions, or personalized product recommendations.</p><p><strong>Optimize Product Offerings</strong> — With 47% of products unsold, consider phasing out underperforming items, improving product visibility, or bundling them with bestsellers to increase movement.</p><p><strong>Boost High-Volume, Low-Revenue Products</strong> — Since accessories, gloves, and tubes sell in high quantities but generate low revenue, implement bundling, upselling premium variants, or adding value-driven pricing strategies.</p><p><strong>Enhance Top-Selling Product Performance </strong>— Mountain-200 Black (Bikes), Water Bottle — 30 oz. (Accessories), AWC Logo Cap (Clothing), and LL Road Frame — Black, 52 (Components) are bestsellers. Offer limited-edition versions, premium add-ons, or bundle deals to maximize revenue.</p><p><strong>Improve Regional Sales Strategies</strong> — With North America dominating revenue, explore market expansion opportunities in underperforming regions through localized marketing, partnerships, or demand analysis.</p><p><strong>Analyze Seasonal Trends</strong> — Revenue fluctuations suggest seasonal peaks and dips. Optimize inventory, pricing, and promotions based on historical trends to maximize sales during high seasons.</p><p><strong>Leverage High-Value Customers</strong> — Since a small segment contributes significantly to revenue, implement exclusive VIP perks, early access to new products, or premium services to increase retention.</p><p><strong>Target Repeat Buyers with Accessories </strong>— Given the high repeat purchases in bikes and accessories, introduce subscription-based models (e.g., tire tubes, gloves) or maintenance packages to keep customers engaged.</p><p><strong>Address Pricing &amp; Product Gaps</strong> — Analyze pricing strategies for low-performing items and consider introducing mid-tier products between the highest and lowest price points to capture more customers.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fd9c92dfc67b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[FIFA21 DATASET CLEANING USING POWER QUERY IN EXCEL]]></title>
            <link>https://medium.com/@komobongsilas/fifa21-dataset-cleaning-using-power-query-in-excel-cbfd6a1a3c0e?source=rss-fbd74acd569------2</link>
            <guid isPermaLink="false">https://medium.com/p/cbfd6a1a3c0e</guid>
            <category><![CDATA[excel]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[power-query]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-cleaning]]></category>
            <dc:creator><![CDATA[Komobong Silas]]></dc:creator>
            <pubDate>Fri, 24 Jan 2025 18:11:46 GMT</pubDate>
            <atom:updated>2025-01-24T18:11:46.429Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*F7VMDBl91IwUpJwDtlrhjQ.png" /></figure><p>Football has proven to be more than just a sport — it brings people together to celebrate skill, strategy, and passion. Every player has a story and we can all see it in the statistics. Player statistics tells us about their speed, passing accuracy, potential, and even market value.</p><p>The <strong>FIFA 21 dataset</strong> is a good choice for exploring player statistics as it is packed with this kind of information, making it a goldmine for analysis. <br>The dataset consists of 77 columns and 18980 rows (headers included) with details about players. Such details include their ID, Name, Age, Wage, Club, Contract, Nationality, Ratings to mention a few.</p><p>However, upon scanning through the dataset, it was discovered that it is inconsistent and messy and as a result, the insights we gain from analysis will be inaccurate.</p><p>Therefore, before diving into analysis, it is important to clean and prepare the data to ensure meaningful insights.</p><p>Data cleaning simply means fixing bad data in your data set. It covers taking care of missing values, duplicates, irrelevant data, correcting errors, incorrect data types to mention a few.</p><p>The goal of data cleaning is to ensure that the data is accurate, consistent and free of errors as raw data is often noisy, incomplete and inconsistent which can negatively impact the accuracy of analysis and its reliability of insights derived from it.</p><p>In this article, we’ll explore how the<strong> Power Query Editor</strong> in <strong>Microsoft Excel</strong> can be used to transform the FIFA21 dataset into a clean dataset ready for analysis and further action.</p><p><strong>The Data Cleaning Steps</strong></p><p>The raw FIFA21 dataset was in a csv (comma separated values) format. It was imported into Excel and duplicated for reference. I then proceeded to open up the Power Query Editor to proceed with the transformation.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eRg8hsCJG6G2mZyZ7nJkZA.png" /><figcaption>An overview of the raw dataset</figcaption></figure><p>Examining the dataset helped in identifying the columns that had issues. I will be highlighting the columns with issues and how I handled them.</p><ul><li><strong>Handling Whitespace</strong><br>In the Power Query Editor, the whitespace in the dataset was pronounced. To handle this, the <strong>show whitespace</strong> option in <strong>view tab</strong> was unchecked so that all whitespaces in the dataset are taken care of.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*n1IWvRKME7ZM3KkR" /><figcaption>Before with whitespace</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*0KHZtXTIBFCZR34k" /><figcaption>After unchecking whitespace</figcaption></figure><ul><li><strong>ID</strong></li></ul><p>The ID column data type was changed to text and renamed as Player ID as it uniquely identifies each player and will not be used for mathematical calculations.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ywEMxqe_pHRztxBfsPFORw.png" /><figcaption>A preview of the ID column before and after</figcaption></figure><ul><li><strong>Name</strong></li></ul><p>In the Name Column, sorting in descending order revealed 7 names with special characters. Upon inspection and comparing with the LongName column, it is discovered that they all bear Stefan and the special character was therefore replaced with an S using the <strong>replace values</strong> <strong>option</strong> in the <strong>Transform</strong> <strong>tab.</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ap33UA1VDfQlwhqfYnehnw.png" /><figcaption>A preview of the Name column before and after cleaning</figcaption></figure><p>However, by the end of cleaning, I ended up removing the column as some players have the same name. The long Name is a better column to identify the names.</p><ul><li><strong>Photourl and Playerurl</strong></li></ul><p>The photourl and playerurl were removed as we already have the data we need in the table. They contained metadata about the players which includes a link to the players picture and a link to the players website respectively.</p><ul><li><strong>OVA and POT</strong><br>For clarity, the OVA and POT columns were renamed to Overall rating and Potential rating respectively.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9ls-BtGzWfupL1nEISwYcA.png" /><figcaption>Before renaming</figcaption></figure><p>This comes after a google search to understand what they mean in football.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nlAFlONgQ3bnykH6xxhrcg.png" /><figcaption>After renaming</figcaption></figure><ul><li><strong>Contract</strong></li></ul><p>The contract column was inconsistent as dates were mixed with strings for example “2020 ~ 2023”, “Aug 1, 2021 On Loan”, and “Free” as seen in the screenshot below.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*dlsFTCQRztD51bYSfiGoXw.png" /><figcaption>Examples of values in Contract column</figcaption></figure><p>From the <strong>add column tab</strong>, I added a <strong>conditional column</strong> (Player Status) based on the Contract column to group players based on contract, loan and free.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_Q8vP4L1xp304248z4Mbxw.png" /><figcaption>Conditional Column creation on Contract</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vIn6iHB-ToXCS1P1QGQ7gA.png" /><figcaption>The new Player Status column after cleaning</figcaption></figure><p>The conditional column feature was also employed to divide the Contract column into two groups — Status start and Status end. They gives details about the year a contract/loan started and ended respectively. <br>The free players have null values for these columns since they have no date to identify.</p><p>To do this, I used the conditional columns to get the values for contract (contract duration) and loan (loan year) separately. There was no need creating a conditional column for free and were left as nulls as they did not have dates attached to them.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wwQ3asDTo_iJzwkYPzc4Ag.png" /><figcaption>Conditional column for contract duration based on Contract column</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HWRhwSq0qGg4qIqiEYFaXQ.png" /><figcaption>Conditional Column for Loan year based on Contract column</figcaption></figure><p>In the <strong>Transform tab</strong>,<strong> Split column by delimiter</strong> — before and after ( ~ ) was used to separate the contract duration into start and end. For the loan, unwanted text were replaced using the replace values option on the Transform tab, the data type was converted to date and the year was then extracted from it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HEYWF-_9SVGYDc1dCQ4zCw.png" /><figcaption>Splitting the contract duration by delimiter</figcaption></figure><p>Also for the loan, based on looking through the <strong>loan date end</strong> column, it is observed that the end date is the same. Therefore, the same year was used for start and end.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*jnfiAru9YYRvs2H1AmBmHg.png" /><figcaption>Replacing unwanted text in Loan year</figcaption></figure><p>After these columns were created, I proceeded to merge the contract start year and loan year columns using the <strong>Merge Columns option</strong> from the <strong>Add columns tab</strong> to get the Status Start.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lxu4IoorV4z65t2r4Npy8w.png" /><figcaption>Creating a merged Column from Contract start year and loan year</figcaption></figure><p>Also merged the contract end and loan year columns to get the Status end.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2p0thKTCk1fCOCxPZi7-dw.png" /><figcaption>Overview of added columns with Status Start and Status End highlighted</figcaption></figure><p>In the end, after getting the data I need, I proceeded to drop the contract duration, loan year, contract start year and contract end year columns. <br>I kept only the Player Status, Status Start and Status end columns. I reordered the kept columns to the correct positions. <br>Note that the initial contract column was dropped as I did not need it anymore and the data type of status start and end columns were later changed to whole numbers.</p><ul><li><strong>Height</strong></li></ul><p>The height column posed an issue as some were written in cm and the rest in feet/inches. As a result, I converted it to cm for consistency. For example, 5ft 4 inches tall (5&#39; 4&quot;) is calculated as<br>5ft × 12 = 60 inches.<br>60 inches + 4 inches = 64 inches.<br>64 × 2.54 = 162.56 cm</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sIBOyUOtw20_fhF65LPUcw.png" /><figcaption>A look into the height column</figcaption></figure><p>To do this, I added a conditional column to only get values for height in cm. Then I replaced the unwanted cm text and changed type to whole number. Also applied the conditional column again to get values for height in foot-inches separately. <br>The split column by delimiter (’) was used to separate the feet from the inches and data type was changed to whole numbers for both. <br>The first split column (feetinches1) represented the feet portion of the data. It was multiplied by 12 using the<strong> Standard option</strong> under the <strong>Number Column option</strong> in the <strong>Transform tab</strong> to get its equivalent in inches.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5j37Tz_ovnhmi18sYP30sw.png" /><figcaption>A screenshot showing the multiplication</figcaption></figure><p>This column was then added to the second split column (feetinches2) to get the height in inches. The new column (addition) was then multiplied by 2.54 to get the height in cm.<br>Following this, the addition and height in cm column were merged together and named height (cm). The data type was changed to whole number.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cKlpPcuC994VVQhp_nX42g.png" /><figcaption>Newly added columns highlighting the final Height (cm)</figcaption></figure><p>Having gotten the needed column (Height (cm)), I went ahead to drop the height cm, heightfeetinches and addition columns and reordered the new height to its correct position. As I did not need the original height anymore, I dropped the column.</p><ul><li><strong>Weight</strong></li></ul><p>This column also has a similar issue like the height column. Therefore, I followed similar steps.<br>In the weight column, I converted it to kg for consistency. For example, 130 pounds (130lbs) is calculated as<br>130 / 2.20462 = 58.97kg</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QLCK1Cuw7hmK47jaxYGxVA.png" /><figcaption>An overview of values in the weight column</figcaption></figure><p>Conditional columns were employed to separate the weight kg and weight pounds. The lbs and kg values were replaced for each column and data types were changed to whole numbers. The errors observed in the pound column was replaced with nulls.<br>The pounds column was then divided by 2.20462 to get the value in kg. Thereafter, the kg and pounds columns were merged together to get weight (kg).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YQcQT1GabC4Kucg6ra2H0w.png" /><figcaption>Screenshot showing added columns with Weight (kg) highlighted</figcaption></figure><p>Just as it is done for the previous columns, unnecessary added columns are deleted leaving only weight (kg). The data type was changed to whole number, reordered to its correct position and the original weight column was deleted.</p><ul><li><strong>Loan Date End</strong></li></ul><p>Although about 95% of it were blanks, it was not dropped as it contained details about when the loan ended for players who were on loan. It contained a lot of blanks due to the fact that many of the players were not on loan. All I did here was change the data type to date and this helped in changing these blank values to null.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K14LyTXjT9aCzi9XtOF_Xg.png" /><figcaption>Loan Date End before</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_qHaKm7CyEJ1SbwiWIj5pQ.png" /><figcaption>Loan Date End After</figcaption></figure><ul><li><strong>Value</strong></li></ul><p>The values were mixed up and inconsistent as some were in thousands and some in Millions with €signs attached. For example €103.5M, €0, €60K. They were all changed to a consistent format and data type was set to currency.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Hf3mN43ENcaLddbEfrvglg.png" /><figcaption>Overview of Value column</figcaption></figure><p>Similar to how we handled the weight and height columns, the conditional column was used to separate those with M and K. The unwanted texts in each column were removed and data type changed to whole numbers for K and decimal for M.<br>The million (M) column was then multiplied by 1,000,000 and the thousand (K) column was multiplied by 1,000.<br>Thereafter, both columns were merged together into a new column (Value (Euro (€) ) and the blanks in this new column were replaced with 0 to match the players that did not have value as it appeared in the original value column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ElOe3CeTVtxQGiFErPcc2w.png" /><figcaption>Screenshot of added columns based on Value column highlighting the final Value</figcaption></figure><p>As usual, we dropped the added columns we did not require any longer — Value K and Value M. The new column Value (Euro (€) was reordered, data type changed to currency and original value column was removed.</p><ul><li><strong>Wage</strong></li></ul><p>The wages were mixed up and inconsistent as some were in thousands and others were not with the € sign attached. For example €0, €950, €21K. They were all changed to a consistent format and data type was set to currency.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*demUB5EL0aT3UHv7kqD0fg.png" /><figcaption>Wage before cleaning</figcaption></figure><p>Similar to what was done for the Value column, the same steps were applied to clean the wage column.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*saAPHFnrgroUVuIqm6FbuA.png" /><figcaption>Newly generated wage column after cleaning</figcaption></figure><ul><li><strong>Release Clause</strong></li></ul><p>The release clause was also mixed up and inconsistent as some were in thousands or millions with the € sign attached. For example €14.7M, €0, €180K. They were all changed to a consistent format and data type was set to currency.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yiC4pNdsUDph0V_MtLPqlg.png" /><figcaption>Release Clause column before</figcaption></figure><p>The same steps taken to clean the Value and Wage columns were employed here.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yiLhCT6_wLR0FQ-dwb3n-A.png" /><figcaption>Release Clause highlighted after cleaning</figcaption></figure><ul><li><strong>Renaming Columns</strong></li></ul><p>Some columns were renamed for better understanding to align with football terminologies</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7_8rprDq-zf1B_tyNTTuyg.png" /><figcaption>Before renaming</figcaption></figure><p>They include:</p><p><strong>W</strong>/<strong>F</strong> — Weak Foot Rating</p><p><strong>SM</strong> — Skill Move Rating</p><p><strong>A/W</strong> — Attacking Work Rate</p><p><strong>D/W</strong> — Defence Work Rate</p><p><strong>IR</strong> — International Rating</p><p><strong>PAC</strong> — Pacing Rating</p><p><strong>SHO</strong> — Shooting Rating</p><p><strong>PAS</strong> — Passing Rating</p><p><strong>DRI</strong> — Dribbling Rating</p><p><strong>DEF</strong> — Physical Rating</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GYu9Xe5KS-io8NHtxz-Nwg.png" /><figcaption>After Renaming columns</figcaption></figure><ul><li><strong>W/F, S/M, IR</strong></li></ul><p>These columns represented ratings from 1 to 5 and had stars attached to them. Example 3 ★. The stars were removed and formatted as whole numbers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XeMCrVkqb917KoqkuVCXcg.png" /><figcaption>W/F, SM and IR before</figcaption></figure><p>To remove the stars, I highlighted each column individually, went to <strong>Column from Examples option</strong> on the <strong>Add column tab</strong> and selected from selection.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OusxM1x9tNke8t-6bdS5Rw.png" /><figcaption>An example of Column from Examples on the W/F column</figcaption></figure><p>All I had to do what type a sample of what I wanted and power query picked it and filled the remaining for me.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*a9QYzmjB4tFwMiF9KsjtfQ.png" /><figcaption>After Cleaning the columns</figcaption></figure><p>The added column from examples were reordered to correct positions and the original columns were dropped. The data types were also changed to whole numbers.</p><ul><li><strong>Hits</strong></li></ul><p>This column was also mixed up and inconsistent as some were blank, in thousands, and the rest not in thousands. For example 1.3K, 10. They were all changed to a consistent format and data type was set to whole numbers.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i5Yu1M0rG3H3H7ZwBrJcuA.png" /><figcaption>Hits column before</figcaption></figure><p>As it was done for earlier columns — Value, Wage and Release clause, a similar approach was used here to clean up the columns.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*E5MPa1bLx0QiIIX7P96QJQ.png" /><figcaption>The added columns for Hits</figcaption></figure><p>At the end of the cleaning, I dropped the Name column and changed the LongName to Name. Also renamed the Hits new column to new.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OH9zev1ZOvKC9hU5aV9rOA.png" /><figcaption>An overview of the cleaned dataset in Excel</figcaption></figure><p><strong>Conclusion<br></strong>The process of cleaning this dataset gave me the opportunity to explore the capabilities of Power Query. It was indeed a learning opportunity for me and I won’t fail to mention <a href="https://medium.com/u/82b0240c74c6">Ejike Uchenna Splendor</a> for pushing me to try my hands on this dataset. <br>Corrections and criticism are very much appreciated. <br>Thank you!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cbfd6a1a3c0e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MAKING SPOT TRADE ORDERS ON MEXC]]></title>
            <link>https://medium.com/@komobongsilas/making-spot-trade-orders-on-mexc-c7517f8950ea?source=rss-fbd74acd569------2</link>
            <guid isPermaLink="false">https://medium.com/p/c7517f8950ea</guid>
            <category><![CDATA[trading]]></category>
            <category><![CDATA[mexc-global]]></category>
            <category><![CDATA[defi]]></category>
            <category><![CDATA[cryptocurrency]]></category>
            <category><![CDATA[blockchain]]></category>
            <dc:creator><![CDATA[Komobong Silas]]></dc:creator>
            <pubDate>Thu, 28 Sep 2023 08:53:08 GMT</pubDate>
            <atom:updated>2023-10-04T07:29:35.890Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WY6Kr0AeEbTjVsyvNbjVDw.png" /></figure><p>The term ‘trading’ is not a strange concept as it is commonly used when talking about buying or selling.</p><p>There are a thousand and one illustrations of trading in our everyday lives. Remember the one time you exchanged chocolate ice cream with a friend to get vanilla in return? — that was a trade.</p><p>Bottom line is that at some point in our lives, we have all been involved in trading in one way or another.</p><p>In cryptocurrencies, trading involves the buying or selling of digital assets with the aim of making a profit from the changing value of the underlying asset.</p><p>A cryptocurrency exchange is an example of a platform you can utilize to make a trade. One of such exchanges which has stood out over time as one of the most credible exchanges out there is the MEXC platform.</p><p>MEXC offers safety and stability, high performance, high liquidity, localized language support and multi-currency support amongst others. If you are not familiar with MEXC, please kindly visit the website <a href="https://www.mexc.com/">here</a> to sign-up and learn more about the platform.</p><p>There are varieties of trading types on MEXC — spot, futures, ETF, margin and OTC trading. For the purpose of this article, we’ll draw our focus to spot trading.</p><h4><strong>WHAT EXACTLY IS SPOT TRADING?</strong></h4><p>As it applies to cryptocurrencies, spot trading refers to the practice of buying or selling cryptocurrencies at the current market prices for immediate exchange.</p><p>The settlement — the transfer of cash and assets — occurs ‘on the spot,’ hence the term ‘spot trading.’ In spot trading, you buy the asset with your own money.</p><p>This means you can only buy as much as you can afford, and nothing more. For this reason, it is considered relatively safer than other trading types. Generally, to profit from spot trading, the idea is to buy assets at a low price and sell at a higher price.</p><h4><strong>SPOT TRADING ON MEXC</strong></h4><p>There are many advantages that come with spot trading on MEXC. The platform is secure, user-friendly and the current spot trade fees is 0% meaning you get to trade for free.</p><p>MEXC provides the opportunity for you to make trades without having to complete KYC verification — just sign-up with your email address and you are good to go.</p><p>However, it is pertinent to note that before you can start spot trading on MEXC, you need a minimum of $5 in your spot wallets.</p><p>Deposits to your spot wallet can be made through the P2P market or depositing by cryptocurrency — you can learn about that <a href="https://www.mexc.com/support/articles/17827791509026">here</a>.</p><p>There are varieties of trading pairs on MEXC including the MX/USDT, BTC/USDC and MX/ETH pairs.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*coikojtVtFP-6DzZrRb56w.jpeg" /></figure><p>The MEXC platform offers three types of orders when it comes to spot trades — the market, the limit and the stop limit orders. These orders are described in simple terms below:</p><p><strong>MARKET ORDER</strong><br>It is basically the simplest type of order in which a trader buys or sells an asset immediately at its current price.</p><p>It is ideal when a trader wants to execute a trade quickly and is less concerned about the specific price at which the trade occurs.</p><p><strong>LIMIT ORDER<br></strong>In its most basic sense, it is an order to buy or sell an asset at a specified price.</p><p>It allows the trader to have more control over the price at which a trade is executed but may not be immediate if the market does not reach the specified price.</p><p><strong>STOP LIMIT ORDER<br></strong>This type of order consists of two prices — the stop and the limit prices. The stop price converts the order to a buy or sell order while the limit price is the maximum price at which a trader is willing to buy the asset or the minimum price at which the trader is willing to sell the asset.</p><p>It is mostly suitable for price-sensitive traders who want to protect their assets against high market volatility.</p><p>If you are not sure about how to spot-trade on MEXC, watch this video guide <a href="https://blog.mexc.com/lesson-2-trading/">here</a>.</p><h4><strong>A CASE SUDY ON USING THE VARIOUS ORDER TYPES ON MEXC SPOT TRADING</strong></h4><p><strong><em>Note: This serves for educational purposes on how to perform the various order types. Screenshots used are taken from the MEXC platform at the time of writing this article.</em></strong></p><p>For the longest time, Ben has been using MEXC to execute his spot trades. He is a registered user of MEXC who wants to make profits from the ever volatile crypto market.</p><p>He has been closely following the crypto market trends and observing the MX/USDT trading pair which is currently priced at $2.1611.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/615/1*y6zfLFC6xI3gAAsYhW7ppA.png" /><figcaption>The MX token chart on MEXC</figcaption></figure><p><strong><em>How do you think Ben is going to earn from spot trading the MX/USDT pair?</em></strong></p><p>Ben needs to understand how the various types of orders work and how he can utilize them to his advantage to make profits.</p><p><strong>MARKET ORDER<br>Buy:</strong> Ben sees the current market price as a buying opportunity and quickly places an order to buy $40 worth of $MX tokens which is approximately 18.5 MX tokens. He has successfully completed a market buy order.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FBx-c13BmzVdh-zR5Q9Nwg.png" /><figcaption>Screenshot showing market buy order</figcaption></figure><p><strong>Sell:</strong> Assuming Ben is happy with the price of $MX going up to $2.1661 per token and sees this as an opportunity to make profits and immediately sells 15 $MX worth approximately $32.5. He has successfully completed a market sell order with profits.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wqW5SeP8YmgxOfA32Dy3jw.png" /><figcaption>Screenshot showing market sell order</figcaption></figure><p><strong>LIMIT ORDER<br>Buy:</strong> Let’s say Ben analyses that the price of $MX will drop to $2.1283 and then pump again. To benefit from this, he places a buy limit order at $2.1283 for 18.79 $MX worth $40. If the price of $MX reaches or drops below $2.1283, the buy limit order will be executed and he will have the tokens in his spot wallet.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FR9RsMCd7zX-L7ZEnafBtg.png" /><figcaption>Screenshot showing buy limit order</figcaption></figure><p><strong>Sell:</strong> Upon accumulating more $MX at a lower price, he analyses the market and predicts that $MX will pump to around $2.1593 or more and then dip again. To make profit, he sets up a sell limit order for 15 $MX worth $32.3895 at $2.1593 per token. If the price of $MX increases to $2.1593 or more, his sell limit order will be automatically filled.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hskpEkounInRGOYdtIL7BQ.png" /><figcaption>Screenshot showing sell limit order</figcaption></figure><p><strong>STOP LIMIT ORDER<br>Buy:</strong> Ben has followed the $MX trend closely and wants to buy more if the price rises above $2.1686 as it signifies a bullish trend for him. To achieve this, he places a buy stop limit order choosing $2.1686 as a trigger price and $2.1687 as limit price for 18.44 $MX tokens worth $40. If the price of $MX reaches or goes beyond $2.1686, the buy stop limit order will be triggered creating a buy limit order for 18.44 $MX tokens at $2.1687 per token.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QAwoHoZhr8EuRgng3X261g.png" /><figcaption>Screenshot showing buy stop-limit order</figcaption></figure><p><strong>Sell:</strong> Say the price of $MX continues to pump and goes beyond $2.17. Ben wants to secure his profit and minimize loses. He places a sell stop limit order for 15 $MX setting the trigger price as $2.1560 and the limit as $2.1559. If the price of $MX falls to or goes below $2.1560, it triggers the sell stop limit order thereby creating a sell limit order for 15 $MX tokens at $2.1559 each. This is helpful in protecting his profits against sudden drops in the market price of $MX.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*BUO_pQIT5RvlHAL450rZOQ.png" /><figcaption>Screenshot showing sell stop-limit order</figcaption></figure><p>You can see that spot trading on MEXC offers diverse opportunities to profit from the volatile crypto market. Now that you understand the various types of orders and how they work, you can get started with spot trading on MEXC by clicking <a href="https://www.mexc.com/exchange/MX_USDT?_from=header">here</a>.</p><h4><strong>RISK DISCLOSURE</strong></h4><p>When we look closely, I am Ben and you are Ben — we all want to profit from the market volatility. However, note that all the types of orders described above are subject to risks.</p><p>It is very possible that the market trend does not go in the direction of Ben’s predictions due to market volatility. Therefore, if Ben sells at a price lower than his purchase, he will be making a loss. There is also liquidity risks, availability risks, third-party risks amongst others.</p><p>Learn more about these risks <a href="https://www.mexc.com/risk">here</a>. It is important to note that just like Ben, as much as you and I want to make profits from the volatility of crypto assets, we should not fail to factor in our risks. Like it is said, always do your own research (DYOR) and do not invest more than you can afford to loss.</p><p>For more information on MEXC, you can connect with the community on:<br><a href="https://www.facebook.com/mexcofficial">Facebook</a> | <a href="https://t.me/MEXCofficialNews">Telegram</a> | <a href="https://twitter.com/MEXC_Official">Twitter</a> | <a href="https://www.instagram.com/mexc_official/">Instagram</a> | <a href="https://www.youtube.com/@MEXCofficial">Youtube</a> | <a href="https://discord.com/invite/Hs2e93Xav5">Discord</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c7517f8950ea" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[MEXC KICKSTARTER EVENT]]></title>
            <link>https://medium.com/@komobongsilas/mexc-kickstarter-event-7bbce4b4ac5c?source=rss-fbd74acd569------2</link>
            <guid isPermaLink="false">https://medium.com/p/7bbce4b4ac5c</guid>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[defi]]></category>
            <category><![CDATA[cryptocurrency]]></category>
            <category><![CDATA[mexc-global]]></category>
            <category><![CDATA[trading]]></category>
            <dc:creator><![CDATA[Komobong Silas]]></dc:creator>
            <pubDate>Sat, 29 Jul 2023 10:05:56 GMT</pubDate>
            <atom:updated>2023-07-29T22:03:39.792Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NPQD3maAfODQUyusNIG7-w.png" /></figure><p>In a bid to explore the financial market and earn passive income, a great number of people all around the world have turned to cryptocurrencies and the blockchain industry.</p><p>I want to believe MEXC is not a strange word to anyone in the crypto space. It stands as world-leading, user-friendly cryptocurrency exchange. Whether you wanted to deposit a cryptocurrency or fiat or make a withdrawal, or you were interested in trading spot and futures as well as leveraging ETFs, in your quest to explore the crypto and blockchain industry, you must have come across MEXC. However, if it still sounds strange to you, there is no problem as there is always a first time. The reasons to choose MEXC as your go-to crypto exchange include safety and stability, high performance, high liquidity, localized language support and multi-currency support amongst others. You can always visit the website <a href="https://www.mexc.com/">here</a> to learn more about the platform.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/220/1*EvMklTr_k3T4GMh5uzikrw.gif" /></figure><p><strong>FAMILIAR WITH THE MX TOKEN?</strong></p><p>The MX TOKEN (MX) is a decentralized digital asset developed by the MEXC platform based on the Ethereum blockchain. It plays an important role in linking MX users, communities, project teams, and partners within the MEXC Exchange ecosystem. The benefits of holding this token includes taking part in governance, utility within ecosystems, free airdrops, and potential value appreciation. The team behind MEXC are keen rewarding active participation and as a result, certain events have been put in place to reward the MX token holders. These events include the futures M-day, Launchpad, the Kickstarter to mention a few. This article explores the Kickstarter event.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/632/1*NCI0cuQDScYxgL2fS7akoA.png" /><figcaption>A snapshot of the MX token chart from MEXC Exchange</figcaption></figure><p><strong>WHAT IS THE KICKSTARTER EVENT?</strong></p><p>Are you a holder of MX token? Have you been leaving your MX token sitting idle in your wallet? Or are you just hearing about the MX token and you want to explore it? Then the Kickstarter event is for you! An opportunity for you to earn passive income.</p><p>The Kickstarter event is an activity initiated during the pre-launch stage of a project where users can vote to support their favorite project on MEXC, which then airdrops its tokens to all successful voting users. This event is designed to identify high-quality projects and at the same time, bring airdrop benefits to MEXC users.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eJWcH7gjxX-i2hOKWWvyAw.png" /></figure><p><strong>PARTICIPATING IN THE KICKSTARTER EVENT</strong></p><p>To take part in this event, you must first be a registered user of the MEXC platform. In addition to that, you have to be holding the MX token in your spot wallet on the platform.</p><p>However, there are other criteria to be met to be qualified for participation in the event. Let me explain this — Assuming the kickstarter event for a project (let’s call it project P) on MEXC is slated to begin on 30th of July, 2023 12:00 UTC:</p><ul><li><strong>Qualification</strong></li></ul><p>To participate in this event for P, the MEXC platform requires that for 15 consecutive days prior to this date and time, you should not have less than 1000 MX tokens in your spot wallet.</p><p>Determining your MX token holdings within this time frame is easy. Everyday, the platform randomly takes 3 snapshots of holdings. The least amount taken during a day’s snapshot is taken as your holdings for the day. Let’s say during the random snapshots, your MX token balance is 200, 150 and 250 respectively. Your holdings for that day will be taken as 150 MX tokens. Likewise, your minimum MX token holdings during the 15- day snapshot time frame = your minimum daily holdings in during the 15-day period. In essence, say day 1, minimum was 150 MX as earlier stated, day 2 minimum was 325 MX and so on. Assuming on any of this days, your lowest amount is 75 MX tokens, that becomes your minimum holdings for the 15-day time frame and as a result, you cannot participate in the event for P. If on the other hand, your least amount during that period is greater or equal to 1000 MX, you are eligible to participate in the event for P. Note that the taking of snapshots ends a day before the beginning of the event for P using the time zone UTC+8.</p><ul><li><strong>Committing your MX Tokens</strong></li></ul><p>After qualification, you can go ahead to commit your MX tokens for project P. You can access the kickstarter <a href="https://www.mexc.com/sun/assessment">here</a>. This commitment can be carried out multiple times within the commitment period until your maximum allowed commitment is reached. The MEXC Kickstarter event usually will last half a day and the token will be traded the same day or the next day of the Kickstarter event.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*j7As6PxpY3t7VW4v8zyvpw.png" /><figcaption>A screenshot showing ongoing kickstarters at the time of writing this article</figcaption></figure><p>The required range for commitment of MX tokens on the platform is from 1000 MX tokens up to 500,000 MX tokens. However, every user has their limit. Assuming users A and B are both qualified to take part in the event for P with their minimum holdings during the 15-day time frame being 1200 MX and 510,000 MX tokens respectively. For user A, the maximum amount he can commit is 1200 MX tokens and the maximum amount for user B is 500,000 MX tokens (this is because 500000 is the maximum amount allowed for commitment by the MEXC platform).</p><p>Note that only successful commitments will be used for reward calculation and your MX will not be frozen.</p><p>You can find your committed MX amount and valid committed amount on the event page. You can find your commitment history under Event Details — Commitment History</p><ul><li><strong>Calculation of rewards</strong></li></ul><p>When the commitment period ends, the system will calculate rewards for each participant. The rewards are calculated based on commitment tiers. Basically, the more MX tokens you commit, the higher your rewards.</p><p>Estimated rewards = Valid committed amount of current users / Valid committed amount of all users * Total prize pool.</p><p>Where the valid committed amount = Committed amount * Commitment coefficient.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/630/1*3SUqgaMT_c0L2nWjZan0kA.png" /><figcaption>Committed quantity range and the corresponding commitment coefficient</figcaption></figure><p>Assuming for project P, the total prize pool is set as 70,000 P tokens and by the end of the campaign, the total valid committed amount of all users is 1,000,000 MX tokens. If User A made a commitment of 25,000 MX tokens, his reward will be calculated as:</p><p>A rewards = (25,000 MX * 1.2) / 1,000,000,000 * 70,000 P</p><p>Note that you can check your estimated rewards and actual rewards on the event page based on your committed MX tokens.</p><ul><li><strong>Reward Distribution</strong></li></ul><p>The distribution of the rewards will be based on the rules of the event for P. Users will get the rewards deposited into their spot wallets. To check the details of the actual rewards you received for this event, you can view it under Event Details — Reward History.</p><p><strong>Risk Disclosure</strong></p><p>It is important to always Do your own research (DYOR) before investing in any project. Say for project P, before you go ahead with making a commitment, it is important you carry out a background check on the project and assess your risks. Projects may face risks in operation, cyber- attacks, underlying technology as well as regulatory and legal environments. Also, the crypto market is not stable as price of tokens are continually fluctuating. All these can result in loss of funds or inability to make withdrawals. Therefore, in your quest to earn passive income, do not forget to factor in the risks and losses you may incur.</p><p>For more information on MEXC, you can connect with the community on:<br><a href="https://www.facebook.com/mexcofficial">Facebook</a> | <a href="https://t.me/MEXCofficialNews">Telegram</a> | <a href="https://twitter.com/MEXC_Official">Twitter</a> | <a href="https://www.instagram.com/mexc_official/">Instagram</a> | <a href="https://www.youtube.com/@MEXCofficial">Youtube</a> | <a href="https://discord.com/invite/Hs2e93Xav5">Discord</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7bbce4b4ac5c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[THE UNIQUE FEATURES OF STAFIHUB’S LIQUID STAKING SOLUTIONS IN COSMOS]]></title>
            <link>https://medium.com/@komobongsilas/the-unique-features-of-stafihubs-liquid-staking-solutions-in-cosmos-ae62ce89e625?source=rss-fbd74acd569------2</link>
            <guid isPermaLink="false">https://medium.com/p/ae62ce89e625</guid>
            <category><![CDATA[cosmos-network]]></category>
            <category><![CDATA[blockchain]]></category>
            <category><![CDATA[cryptocurrency]]></category>
            <category><![CDATA[defi]]></category>
            <category><![CDATA[liquid-staking]]></category>
            <dc:creator><![CDATA[Komobong Silas]]></dc:creator>
            <pubDate>Wed, 04 Jan 2023 17:06:45 GMT</pubDate>
            <atom:updated>2023-01-04T17:06:45.818Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NKGQxkPLts1AvlcnHZHbOA.png" /></figure><p>The blockchain industry has evolved over the years and over time, new ways to earn passive income with cryptocurrencies have emerged. These methods include airdrops, yield farming, liquidity mining and staking amongst numerous others.</p><p><strong>Let’s look more into Staking. What exactly is staking?</strong></p><p>In simple terms, staking involves locking your crypto assets for a specific period of time to help support the operation of a blockchain and in return, you earn rewards on it.</p><p>A lot of blockchains use a proof of stake(POS) consensus mechanism which allows network participants who want to support the blockchain by validating new transactions and adding new blocks to “stake” set sums of cryptocurrency. This staking helps ensure that only legitimate data and transactions are added to a blockchain.</p><p>The downside to staking is that during the period your assets are locked up, you cannot access your funds. Basically, your funds are illiquid as at that point as you cannot easily convert it into cash or other assets. You have to wait until the staking period is over before you can use it for another purpose. This issue leads us to <strong>Liquid Staking </strong>and what makes <strong>StaFiHub’s liquid staking solutions unique in the Cosmos ecoystem.</strong></p><p><strong>Introducing Liquid Staking with StaFiHub</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*daxNpe8nyqeyAeeVBfJWAg.png" /></figure><p>Let’s imagine you have 100 $ATOM tokens in your crypto wallet that you are holding and you do not have plans of spending it anytime soon. You want to stake these tokens and earn rewards however, you also want the convenience of being able to access it anytime you want, then liquid staking will be a good bet. And with StaFiHub, you can achieve this.</p><p>As opposed to regular staking, <strong>liquid staking</strong> involves locking up your crypto assets to earn rewards while also having access to your assets. This is the advantage liquid staking has over the POS protocols. In addition to earning the staking protocol’s original rewards, liquid staking allows users who have staked assets to have a derivative or liquid representation of that asset that they can use in decentralized applications.</p><p>Basically, in exchange for your locked up assets, you get the service’s native token, usually pegged 1:1 to whatever you deposited, and you can then use that token on decentralized finance platforms for lending, trading, as collateral or even take part in liquidity pools in order to earn additional yield. At your convenience, you can always trade back your tokens for the orginal tokens and walk away.</p><p><strong>What makes StaFiHub’s Liquid Staking Solutions to stand out on the Cosmos Ecosystem?</strong></p><p><strong>StaFiHub</strong> is a cross-chain staking derivatives platform on the Cosmos Ecosystem. It is built based on the Cosmos SDK developed by StaFi protocol to provide Liquid Staking Derivative SDK solutions for Cosmos Ecosystem projects through the Inter-Blockchain Communication (IBC) Protocol to rapidly grow the staking industry. rToken, short for reward-Token, is a synthetic staking derivative obtained when users stake PoS tokens through StaFi. You can use these rTokens on Dapps to earn extra passive income. Let’s say you stake your 100 $ATOM tokens with StaFiHub, you’ll get rATOM tokens in return.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9GaGVF2C2OdUlA31MH9SHg.png" /><figcaption>A screenshot of the StaFiHub Dapp</figcaption></figure><p>The Cosmos ecosystem has witnessed various solutions so why is that of StaFiHub so outstanding?</p><ul><li><strong>The presence of a Permissionless &amp; Automatic Delegation Algorithm</strong></li></ul><p>This is one of the features that makes StaFiHub stand out. It has introduced a smart delegation algorithm that is executed automatically to create maximized staking rewards for stakers and decentralized voting power for the projects. StaFiHub features a smart validator score system to evaluate all the validators on-chain based on their on-chain data which include voting power, slash records, uptime, commisions etc. There is no whitelist for validators to get the delegations from StaFiHub and it is totally permissionless and open for all on-chain validators. Learn more about this feature <a href="https://docs.stafihub.io/welcome-to-stafihub/rtoken/delegation-algorithm">here</a>.</p><ul><li><strong>It supports Integrations for both Multisig Account and Interchain Account (ICA)</strong></li></ul><p>Amongst all the various chains available on the Cosmos Ecosystem, only about four or five of these chains currently support the ICA module while the others are yet to get onboard with the ICA module. As opposed to some of these liquid staking projects that can only onboard Cosmos ecosystem projects that support the ICA module, StaFiHub has the advantage of not only supporting integration only for ICA chains but also the non-ICA chains having liquid staking solutions at the same time. These non-ICA chains could have StaFiHub’s liquid staking solutions in multisig accounts and will be smoothly and safely upgraded into the ICA features when the chains also support ICA in the future. Learn more about this <a href="https://docs.stafihub.io/welcome-to-stafihub/rtoken/integrate-rtoken">here</a>.</p><ul><li><strong>Giving back to the community through Mintdrops and Liquidity Farming Programs</strong></li></ul><p>Another great advantage is that StaFiHub is big on giving back to the community. As a way of saying thank you to users have have staked on StaFiHub and minted rTokens as well as provided liqudity, the platform set up these mintdrops and liquidity farming programs where users are rewarded with $FIS token.</p><p>Join the latest campaign <a href="https://twitter.com/StaFiHub_/status/1603706871822811141">here</a>.</p><ul><li><strong>It is highly secure</strong></li></ul><p>Security is a big issue when it comes to the success of crypto projects. StaFiHub has not taken this issue lightly. It is supported by the StaFi Protocol team who already have over two years of experience in liquidity staking and have issued eight rTokens with over $100M TVL at its peak. The protocol was also recommended by the Ethereum foundation as one of the six staking pools.</p><p>In the case of rATOM, which is the liquid staking solution StaFiHub has for the Cosmos Ecosystem’s native token ATOM, it plays an important role in helping to reduce gas fees and improving the overall UI/UX for stakers.</p><p>You can see that StaFiHub’s Liquid Staking Solutions is an integral part of the Cosmos Ecosystem due to its uniqueness and I strongly recommend you try it out yourself. Ready to explore liquid staking wirh StaFiHub? Follow the guide <a href="https://docs.stafihub.io/welcome-to-stafihub/user/user-guide/rtoken-dapp-guide">here</a>.</p><p><strong>About StaFi</strong></p><p>StaFi is the first DeFi protocol unlocking liquidity of staked assets. Users can stake PoS tokens through StaFi and receive rTokens in return, which are available for trading, while still earning staking rewards. rToken is a synthetic staking derivative issued by StaFi to users when users stake PoS tokens through StaFi <a href="https://app.stafi.io/">rToken App</a> . rTokens are anchored to the PoS tokens staked by users and the corresponding staking rewards. rTokens can be transferred and traded at any time.</p><p><a href="http://www.stafi.io/">Website</a> | <a href="https://app.stafi.io/">rToken App</a> | <a href="https://twitter.com/StaFi_Protocol">Twitter</a> | <a href="https://t.me/stafi_protocol">Telegram</a> | <a href="https://discord.com/invite/jB77etn">Discord</a> | <a href="https://commonwealth.im/stafi">Forum</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ae62ce89e625" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[THE NEED FOR PARIBUS IN LENDING AND BORROWING]]></title>
            <link>https://medium.com/@komobongsilas/the-need-for-paribus-in-lending-and-borrowing-e2f3e1de87bb?source=rss-fbd74acd569------2</link>
            <guid isPermaLink="false">https://medium.com/p/e2f3e1de87bb</guid>
            <category><![CDATA[nft]]></category>
            <category><![CDATA[lending]]></category>
            <category><![CDATA[defi-borrowing]]></category>
            <category><![CDATA[defi]]></category>
            <category><![CDATA[multichain]]></category>
            <dc:creator><![CDATA[Komobong Silas]]></dc:creator>
            <pubDate>Sun, 30 Oct 2022 21:31:36 GMT</pubDate>
            <atom:updated>2022-10-30T21:32:11.149Z</atom:updated>
            <content:encoded><![CDATA[<h3>THE NEED FOR PARIBUS IN DEFI LENDING AND BORROWING</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tnFzs_cEEjYT1RJh6-ysyQ.png" /></figure><p>We all know that the crypto space offers a wide range of opportunities for its users to earn passive income — one of which is through yield farming.</p><p><strong>WHAT IS YIELD FARMING?</strong></p><p>To put it simply, it involves lending or staking your crypto assets through decentralized finance in exchange for interest and other rewards. It is operated using smart contract technology and returns are measured in terms of annual percentage yields (APYs). The APY is the annual return rate of an asset, inclusive of compounding. Yield farming is quite profitable but also poses incredible risks.</p><p>Lending and borrowing is a yield farming strategy that is quite effective when it comes to earning passive income. Investors can supply their assets for lending and other users can borrow these assets. Supplying crypto means depositing assets into the protocol to be lent to other users, but it can also mean posting collateral for borrowing crypto. Borrowing on the other hand means taking out a crypto loan against the collateral you supplied into the platform.</p><p>When it comes to lending and borrowing, majority of it is done with conventional digital assets like cryptocurrencies and stable coins leaving out the unconventional digital assets like NFTs, Liquidity positions, synthetics and virtual lands amongst others. The reason this happens is because these unconventional assets are faced with problems such as underutilization, lack of liquidity and lack of interoperability across systems. Let us take NFTs as an example — most times, when you buy NFTs, you just hold them in your crypto wallets until you are ready to sell it off, hopefully at a better price.</p><p><em>What if I told you that there is a way for you to do more with these unconventional crypto assets to earn passive income?</em></p><p><strong>This is where </strong><a href="https://medium.com/u/fe91f6feaf37"><strong>Paribus</strong></a><strong> comes in.</strong></p><p><strong>WHY PARIBUS PROTOCOL?</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LyniSNcU_RieMDqOBN9YfQ.png" /></figure><p>Paribus is a cross-chain borrowing and lending protocol for NFTs, liquidity positions, and synthetic assets, building for the Cardano blockchain.</p><p>Borrowing on Paribus requires a collateral to make sure you are able to pay back your loan. Also, with Paribus, interest rates are dynamic and will be calculated based on a given pool’s utilization rate.</p><p>Paribus’ mission is to unlock the true potential of these assets, evolving them into interoperable financial instruments, capable of being used within DeFi protocols, on any chain. The Cardano blockchain will let Paribus remain chain agnostic and at the same time connect various assets across different blockchains. In addition, its interoperability will allow value to flow freely as it removes restrictions.</p><p>Think of any asset, if there is a way to trade it on decentralized marketplaces, then it has value and whatever you can trade, can definitely be leveraged. The good news is that with Paribus, in addition to your conventional assets, it is also giving your unconventional assets the opportunity to be maximized and unlock its full potential.</p><p>Say goodbye to just holding your NFTs until you are ready to trade them. With Paribus, you can hold your NFTs while using them as collateral to take loans and enter new positions in the crypto market. How about liquidity pool tokens? These tokens inherit the value of the underlying assets and by staking them, you can generate returns. In the same manner like with NFTs, you can supply these liquidity pool tokens as collateral to borrow other crypto assets. The same is applicable to other unconventional assets like synthetics, yield bearing assets to mention a few.</p><p>Generally, Paribus is definitely the place to be. You can see how Paribus is invested towards making sure that no stone is left unturned and all your unconventional assets have been maximized and have reached their full potential.</p><p><strong>Join Paribus</strong></p><p><a href="https://paribus.io/"><strong>Website</strong></a><strong> | </strong><a href="https://twitter.com/paribus_io"><strong>Twitter</strong></a><strong> | </strong><a href="https://t.me/paribus_io"><strong>Telegram</strong></a><strong> | </strong><a href="https://medium.com/@paribus_io"><strong>Medium</strong></a> <strong>| </strong><a href="https://discord.io/paribus"><strong>Discord</strong></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e2f3e1de87bb" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>