<?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 Sharon Obi on Medium]]></title>
        <description><![CDATA[Stories by Sharon Obi on Medium]]></description>
        <link>https://medium.com/@sharonobi360?source=rss-14e79255a32c------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*7u91ht6vEeR0AHSH</url>
            <title>Stories by Sharon Obi on Medium</title>
            <link>https://medium.com/@sharonobi360?source=rss-14e79255a32c------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 23:43:25 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sharonobi360/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[What 13 Years of World Bank Data Tells Us About Financial Inclusion.]]></title>
            <link>https://medium.com/@sharonobi360/what-13-years-of-world-bank-data-tells-us-about-financial-inclusion-62a6a4fd4ad6?source=rss-14e79255a32c------2</link>
            <guid isPermaLink="false">https://medium.com/p/62a6a4fd4ad6</guid>
            <category><![CDATA[hng-internship-2025]]></category>
            <category><![CDATA[financial-inclusion]]></category>
            <category><![CDATA[world-bank-group]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[microsoft-excel]]></category>
            <dc:creator><![CDATA[Sharon Obi]]></dc:creator>
            <pubDate>Thu, 06 Nov 2025 15:07:33 GMT</pubDate>
            <atom:updated>2025-11-06T15:07:33.168Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*euaFKWrN2Q2b5XNU_aGrTA.png" /></figure><p>There are <a href="https://www.linkedin.com/posts/segunadeyemi_fintech-nigeria-2024inreview-activity-7301197229858406401-RQNV/">over 430 Fintech</a> start-ups in Nigeria, with 5 of them having achieved Unicorn status.</p><p>The Central Bank of Nigeria set an ambitious target to increase <a href="https://www.cbn.gov.ng/Out/2022/CCD/NFIS1.pdf">financial inclusion in Nigeria’s adult population to 95%</a> by year-end 2024.</p><p>It is important to have an understanding of where we are now and find out ‘<strong>How Financial Inclusion has trended in Nigeria between 2011 and 2024.’</strong></p><p>This project examines the reality of financial inclusion in Nigeria, delving into the World Bank’s <a href="https://www.worldbank.org/en/publication/globalfindex/download-data">Global Findex Database 2025</a>.</p><p>The World Bank collected the data using varying approaches, depending on the country­­ ­­ — face-to-face interviews were conducted with respondents above 15 years of age, from low-income households in Nigeria. For each year captured, responses were recorded and aggregated in the database.</p><p><strong>Data Preprocessing</strong></p><p>The data contained 8,566 records and 456 columns, which captured data such as mobile phone ownership, internet use, digital safety, account ownership, payments, savings, credit, and financial resilience across different demographics (gender, age, education, income, and job status).</p><p>The columns were pruned, and only those columns relevant to this analysis (22) were left. These columns include indicators for bank account ownership, mobile money account ownership, online merchant payments, savings using mobile apps, loans taken for business, and utility payments using a mobile device.</p><p>The required preprocessing workflows were implemented to prepare the data for analysis.</p><p><strong>Insights</strong></p><p><strong>a. </strong>At least 6 out of 10 Nigerians have access to either traditional bank accounts or mobile bank accounts. This falls short of the Central Bank of Nigeria’s (CBN) target of 95% financial inclusion, but not due to a lack of effort.</p><p><strong>b. </strong>Financial inclusion trended upwards from 2011 to 2024, with an encouraging 40% increase.</p><p><strong>c.</strong> Mobile Money is the future; it saw a 279% increase and has the potential to surge upwards as adoption increases.</p><p><strong>d. Gender gap:</strong> is closing, but women are still lagging in mobile banking adoption. Women&#39;s account ownership is at 52% compared ot 74% for men.</p><ul><li>Mobile money account ownership: Women 25% vs. Men 41%</li><li>Digital Payments: Women 35% vs. Men 53%.</li></ul><p><strong>e. Education:</strong> 69.32% of those with secondary education and above have an account compared to 51.08% for those with Primary education or less.</p><p><strong>f. Income: </strong>54.65%<strong> </strong>of the poorest have accounts compared to the richest, with 67.1% having accounts. Disparity in income levels is reflected in the ownership of accounts and is the main factor influencing adoption.</p><p><strong><em>NorthStar Metrics</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mEVgUirnPNtgLeY67vuQvw.png" /></figure><p><strong><em>Dashboard</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8e5Ghv8lyoEHgR7ebziqfA.png" /></figure><p><strong>Recommendations</strong></p><p><strong>1. Strengthen Mobile Money Policy Implementation. </strong>Building on the current policies, <strong><em>Mobile Money should be prioritized to capture more of the unbanked.</em></strong></p><p>The growth of 279% mobile banking adoption indicates that the sub-industry is ripe for more efficient regulatory frameworks that will encourage increased adoption.</p><p><strong>2. Women-Targeted Initiatives: <em>Launch national financial literacy and adoption initiatives in rural and under-banked areas</em></strong> to support increased adoption.</p><p><strong>3. USSD Solutions:</strong> fintech start-ups committed to financial inclusion can <strong><em>promote USSD solutions for savings, payments, and micro-insurance</em></strong> to reach those in rural areas who, due to income, aren’t able to afford smartphones. They can also offer B2G services to drive market-level remittance to encourage swift compliance.</p><p>Access the dataset and Excel dashboard on <a href="https://github.com/Shane360/Financial_Inclusion_In_Nigeria">GitHub</a>.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=62a6a4fd4ad6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Bharat Herald: Post-COVID Analysis and Recovery]]></title>
            <link>https://medium.com/@sharonobi360/bharat-herald-post-covid-analysis-and-recovery-478bbdae143d?source=rss-14e79255a32c------2</link>
            <guid isPermaLink="false">https://medium.com/p/478bbdae143d</guid>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[legacy-media]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[code-basics]]></category>
            <dc:creator><![CDATA[Sharon Obi]]></dc:creator>
            <pubDate>Fri, 17 Oct 2025 09:19:38 GMT</pubDate>
            <atom:updated>2025-10-18T13:27:15.180Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GVnVrZ_plY_0G63e1hnEjA.jpeg" /><figcaption>Image from <a href="https://pixabay.com/photos/newspapers-press-news-newspaper-444450/">Pixabay</a></figcaption></figure><p><strong>Introduction</strong></p><p>We will explore Bharat Media&#39;s current challenges with its print and digital platforms, utilizing data provided by <a href="https://codebasics.io/challenges/codebasics-resume-project-challenge/21">Code Basics</a> to visualize key metrics and realities with MySQL and Power BI. The objective was to assess Bharat Herald’s print and digital performance after the COVID-19 pandemic, identify factors behind circulation and revenue decline, and propose strategies for relaunch and ad revenue optimization. The dataset included the following dimensions and fact data: <em>dim_city, dim_ad_category, fact_print_sales, fact_ad_revenue, fact_city_readiness, fact_digital_pilot.</em></p><p>Rigorous <a href="https://github.com/Shane360/Bharat-Herald-Media-Strategy/blob/main/scripts/data_cleaning.sql">data preprocessing</a> was carried out on the data before tackling the Business Requests — 6 analytical questions — <strong><em>Monthly circulation drop check</em></strong>, <strong><em>Yearly revenue by category</em></strong>, <strong><em>2024 Print Efficiency</em></strong>, <strong><em>Internet Readiness Growth for 2021</em></strong>, <strong><em>Multi-year decline (2019–2024)</em></strong>, and <strong><em>Digital readiness vs. Pilot Engagement Outlier(s)</em></strong>. Other <a href="https://github.com/Shane360/Bharat-Herald-Media-Strategy/tree/main/scripts/primary_and_secondary_analysis">key questions</a> for analysis were: <strong><em>Top performing cities</em></strong>, <strong><em>Print Waste Analysis, Revenue by City, Ad Revenue v. Circulation ROI, and Digital Relaunch Prioritization.</em></strong></p><p><strong>Executive Summary</strong></p><p><a href="https://invest-india-revamp-static-files.s3.ap-south-1.amazonaws.com/s3fs-public/2020-04/ficci-ey-m-and-e-report-2020.pdf">EY</a> reported a 3% decline in advertising revenue for print media companies and a 2% increase in subscription revenues in 2019. COVID-era disruptions, however, had a dire impact on legacy media companies — revenue <a href="https://www.theindiaforum.in/article/covid-hastens-transformation-india-s-newspapers-advertising-collapses-and-digitisation-picks?utm_source=chatgpt.com">declined by 67% in Q1 2021</a>, and advertising revenues fell by <a href="https://inspirajournals.com/uploads/Issues/996724634.pdf">40% in 2020</a>.</p><p>Bharat Herald faced similar challenges, having recorded a 70.5% drop in print circulation, volatile revenue, and a failed digital pilot.</p><p>The decline stemmed from <strong><em>COVID-era disruptions, logistics issues</em></strong>, <strong><em>unsatisfactory mobile UX</em></strong>, and <strong><em>low digital adoption of mobile platforms</em></strong>, compounded by<strong><em> volatile advertising revenues</em></strong>.</p><p>Despite this, the data shows clear digital readiness in cities such as Mumbai (digital readiness index of 0.619), Varanasi (0.701), and Ahmedabad (0.692), respectively, and a cumulative 1.1 million engagements. These insights reveal a clear opportunity to relaunch digital platforms with improved UX and accessibility.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/946/1*fARNS3esJolXVZHhya0LNw.png" /><figcaption>High-level Overview</figcaption></figure><p><strong>Findings — What Went Wrong</strong></p><p><strong><em>1.</em></strong><em> </em><strong><em>Post-COVID decline:</em></strong></p><p>· Revenue fell by 2.44%, from ~INR 368 million in 2019 to ~INR 359 million in 2020, before recovering by 1.89% in 2024 (~INR 374.9 million).</p><p>· Circulation dropped by an alarming 70.5% from ~ 39.5 million copies in 2019 to ~29.5 million copies in 2024. This was driven by post-pandemic shifts in digital consumption.</p><p><strong><em>2.</em></strong><em> </em><strong><em>Digital pilot</em></strong><em>:</em></p><p>· <strong>Adoption averaged 54.5%,</strong> but retention was low due to <strong><em>UX/UX issues</em></strong> (long load times, small fonts, and inability to zoom).</p><p>· These issues influenced the <strong>bounce rate of 65.85% </strong>on <strong>average</strong>.</p><p>· <strong>Additional barriers to success<em> </em></strong>were <strong><em>limited internet access</em></strong> and <strong><em>smartphone access</em> </strong>in a few regions.</p><p><strong><em>3.</em></strong><em> </em><strong><em>Revenue fluctuations:</em></strong></p><p>· <strong>Price Sensitivity</strong> drove a -16.69% YoY revenue decline in 2020 (losses of ~ INR 21 million in Government revenue, and ~INR 29 million from FMCGs).</p><p>· <strong>Seasonal drops in demand</strong> in Q3 2019, Q1 2020, and Q3 2023 affected Government ads but did not affect Real Estate (+14.6% in 2020) and Automobile (+32.39% in 2024).</p><p>· <strong>The Real Estate slowdown</strong> in 2022 (-13.73% YoY) contributed to volatility but was not the sole cause.</p><p><strong>Recommendations</strong></p><p><strong><em>1.</em></strong><em> </em><strong><em>Relaunch Digital Platforms:</em></strong></p><p><strong><em>Phase 1:</em></strong> Resolve UI/UX issues on WhatsApp, Web, and e-Mobile platforms.</p><p><strong><em>Phase 2: </em></strong>Run targeted ads on social media platforms.</p><p><strong><em>Phase 3: </em></strong>Focus relaunch on key growth cities such as Varanasi, Mumbai, and Ahmedabad. Create WhatsApp channels and amplify reports from journalists in each city with a large, established audience.</p><p><strong><em>Phase 4: </em></strong>Build a functional platform ecosystem that will unlock new ad revenue streams.</p><p><strong><em>2.</em></strong><em> </em><strong><em>Market Re-engagement:</em></strong></p><p>· Strengthen presence in cities with high engagement potential (Delhi, Ahmedabad) and consolidate presence in high-performing cities such as Lucknow and Patna.</p><p><strong><em>3.</em></strong><em> </em><strong><em>Restore Advertiser Confidence:</em></strong></p><p>· Introduce discounted ad bundles and highlight expanded reach potential (including foreign audiences).</p><p>· Rebuild trust with FMCGs and the Government through transparency and measurable ROI.</p><p><strong>Dashboard</strong></p><p>After data cleaning and standardization in MySQL, I created Views for the key metrics and analyses. These Views were connected to Power BI to ensure quick load times and a light report. Below are snapshots; you can access the full interactive dashboard <a href="https://app.powerbi.com/groups/me/reports/78bb2cd6-cce3-407e-82b2-94106807a136?ctid=63b8c9cd-edf4-475f-9ef3-aa69bb5579d6&amp;pbi_source=linkShare"><strong>here</strong></a><strong>.</strong></p><p>You can view the much more technical work (data pre-processing and SQL queries) behind this report on my <a href="https://github.com/Shane360/Bharat-Herald-Media-Strategy/tree/main"><strong>GitHub</strong></a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MVTJEvlkjKzdoSEJQ5Z3kQ.png" /><figcaption>Revenue Metrics</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MUosUOWE9CPaE4QvV-vS9g.png" /><figcaption>Digital Pilot Metrics</figcaption></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=478bbdae143d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Credit Risk Analysis And Fraud Prevention]]></title>
            <link>https://medium.com/@sharonobi360/credit-risk-analysis-and-fraud-detection-f18fc314441c?source=rss-14e79255a32c------2</link>
            <guid isPermaLink="false">https://medium.com/p/f18fc314441c</guid>
            <category><![CDATA[microsoft-sql-server]]></category>
            <category><![CDATA[risk-management-solutions]]></category>
            <category><![CDATA[credit-risk-management]]></category>
            <category><![CDATA[fraud-detection]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Sharon Obi]]></dc:creator>
            <pubDate>Thu, 21 Aug 2025 04:39:11 GMT</pubDate>
            <atom:updated>2025-09-19T16:38:10.505Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DlbQssYnaJmdp35rAgksoQ.jpeg" /><figcaption>Image by <a href="https://tinyurl.com/yc3ury2e">Hartono Subagio</a> from Pixabay.</figcaption></figure><p><strong>Introduction</strong></p><p>Credit risk is a pain point for banks, with global credit losses projected to increase by 7% to $850 billion in 2025, according to <a href="https://www.spglobal.com/_assets/documents/ratings/research/101608090.pdf">S&amp;P Global Ratings</a>. In this report, we build on the <a href="https://medium.com/@sharonobi360/bank-customer-analysis-and-fraud-detection-9a21f0846fb8">Bank Customer Analysis report,</a> utilizing <a href="https://zoomchartswebstorage.blob.core.windows.net/contest/Onyx_Data_DataDNA_Challenge_December2024.zip">data </a>from the <a href="https://zoomcharts.com/en/microsoft-power-bi-custom-visuals/challenges/onyx-data-december-2024">ZoomCharts Onyz Challenge</a>, to refine the business logic for identifying customers at risk of defaulting on their loan repayments.</p><p>Another key risk in banking operations is fraud — current cybercrime trends and innovations in AI make banking fraud more challenging as criminals are now leveraging and repurposing technology to carry out their fraudulent activities. The <a href="https://www.fbi.gov/contact-us/field-offices/anchorage/news/fbi-releases-annual-internet-crime-report">FBI </a>reported losses exceeding $16 billion from cybercrime in 2024, while <a href="https://investor.aciworldwide.com/news-releases/news-release-details/app-scams-emerge-top-payments-fraud-threat-fraudsters-changing?_gl=1*nwe201*_ga*MTI5NzcxNDgyLjE2NzI5MzcwNjQ.*_ga_XFBVQC4DTD*MTY4Njg1NDk2MS4zNS4wLjE2ODY4NTQ5NzcuNDQuMC4w&amp;_ga=2.89169694.441061168.1686854961-129771482.1672937064">ACI Worldwide</a> projected that financial crime and fraud would cost banks and financial institutions around the world $40.62 billion by 2027.</p><p>Given these realities, proactive measures are necessary to limit the Bank’s exposure, and SQL can be an efficient tool in this regard. We explore risk policy recommendations, implement them using optimized SQL queries, and create automated risk control systems using triggers and real-time anomaly detection to flag suspicious transactions.</p><p><strong>1. Credit Risk Analysis</strong></p><p>Customers with fair to poor credit (credit scores between 669 and below) are best placed under credit watch, so that applications for further loans will be subject to additional scrutiny before disbursement to mitigate credit risks.</p><p>The code block below features a comprehensive CTE that classifies customers by <em>income and credit bracket, </em>computes their<em> debt-load ratio, </em>and then <em>assigns risk levels </em>accordingly, so we have a clear picture of each customer’s financial health and why they may pose a credit risk to the Bank.</p><pre>With RiskyCustomers AS(<br>  SELECT<br>    id,<br>    CASE<br>      WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>      WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>      WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100K)&#39;<br>      ELSE &#39;High Income (Above 100K)&#39;<br>    END AS income_bracket,<br>    CASE <br>      WHEN credit_score BETWEEN 800 AND 850 THEN &#39;Exceptional&#39;<br>      WHEN credit_score BETWEEN 740 AND 799 THEN &#39;Very Good&#39;<br>      WHEN credit_score BETWEEN 670 AND 739 THEN &#39;Good&#39; -- some banks treat &lt; 700 as &#39;subprime&#39;. THresholds vary according to bank policy<br>      WHEN credit_score BETWEEN 580 AND 669 THEN &#39;Fair&#39;<br>      ELSE &#39;Poor&#39;<br>    END AS credit_bracket,<br>    credit_score,<br>    yearly_income,<br>    total_debt,<br>    ROUND((total_debt/NULLIF(yearly_income, 0)),2) AS debt_load_ratio,<br>    CASE<br>      WHEN ROUND((total_debt/NULLIF(yearly_income, 0)),2) BETWEEN 0.5 AND 0.99 THEN &#39;Moderate&#39;<br>      WHEN ROUND((total_debt/NULLIF(yearly_income, 0)),2) BETWEEN 1.0 AND 1.99 THEN &#39;High&#39;<br>      WHEN ROUND((total_debt/NULLIF(yearly_income, 0)),2) &gt;= 2.0 THEN &#39;Critical&#39;<br>      ELSE &#39;Safe&#39; -- included here to complete the query but is filtered out during execution<br>    END AS risk_level<br>  FROM users_data<br>  WHERE credit_score &lt; 670 AND ROUND((total_debt/NULLIF(yearly_income, 0)),2) &gt;= 0.5<br>)<br><br>SELECT *<br>FROM RiskyCustomers<br>ORDER BY risk_level, debt_load_ratio DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/825/1*C7q5GEkNjvJhVc-ZDf--BA.png" /><figcaption>Snippet of Results</figcaption></figure><p><em>Insights:</em></p><p>394 customers fall into moderate to critical risk levels, making them prime candidates for collateralized loans (if approved), reduced credit limits, or collections from those already in default.</p><p><strong>2. Loan Triggers</strong></p><p>Triggers are a great way to automate risk control systems. With the right rules in place, a loan can be put on hold if such a loan will increase the debt-load ratio or debt-to-income ratio of a customer (working with the data from the <a href="https://medium.com/@sharonobi360/bank-customer-analysis-and-fraud-detection-9a21f0846fb8">Bank Customer Analysis</a>).</p><p>The workflow involves creating a log table to capture flagged accounts, inserting all current customer records that fit the criteria into the log table, and then developing the trigger to capture future instances.</p><p><strong><em>Step 1: Create a log table: </em></strong>This captures customer details and the date an account is flagged as a credit risk.</p><pre>CREATE TABLE credit_risk_flags (<br> flag_id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,<br> client_id INT NOT NULL,<br> credit_score INT NOT NULL,<br> yearly_income INT NOT NULL,<br> total_debt INT NOT NULL,<br> debt_load_ratio DECIMAL (10, 2) NOT NULL,<br> risk_level VARCHAR(20) NOT NULL,<br> flagged_at DATETIME DEFAULT GETDATE() NOT NULL<br>);</pre><p><strong><em>Step 2: Populate the table with current risks: </em></strong>using a batch insert of all current accounts that are at risk into the table.</p><p>We populate the credit_risk log table by pulling in the <em>client_id, credit score, yearly_income, total_debt, </em>and <em>debt_load_ratio. </em>A CASE statement is included to assign risk levels to each customer according to their debt-load ratio.</p><pre><br>INSERT INTO credit_risk_flags (client_id, credit_score, yearly_income, total_debt, debt_load_ratio, risk_level, flagged_at)<br>SELECT<br>   u.id,<br>   u.credit_score,<br>   u.yearly_income,<br>   u.total_debt,<br>   ROUND((u.total_debt/NULLIF(u.yearly_income, 0)),2) AS debt_load_ratio,<br>   CASE<br>      WHEN ROUND((total_debt/NULLIF(yearly_income, 0)),2) BETWEEN 0.5 AND 0.99 THEN &#39;Moderate&#39;<br>      WHEN ROUND((total_debt/NULLIF(yearly_income, 0)),2) BETWEEN 1.0 AND 1.99 THEN &#39;High&#39;<br>      WHEN ROUND((total_debt/NULLIF(yearly_income, 0)),2) &gt;= 2.0 THEN &#39;Critical&#39;<br>      ELSE &#39;Safe&#39; -- included here to complete the query but is filtered out during execution<br>   END AS risk_level,<br>   GETDATE()<br>FROM users_data u<br>WHERE ROUND((u.total_debt/NULLIF(u.yearly_income, 0)),2) &gt;= 0.5<br> AND credit_score &lt; 670;<br><br>SELECT * FROM credit_risk_flags; </pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/828/1*2JB7wgxiPhlmUwIJNht8HQ.png" /><figcaption>Snippet of results</figcaption></figure><p><strong><em>Step 3: Develop the trigger</em></strong>: This trigger is an automated system that flags customers&#39; accounts immediately after the insertion or update of users’ details, showing either a reduction in credit score or a change in total debt such that the debt load is now greater than or equal to 0.5.</p><pre>CREATE TRIGGER trg_flag_credit_risk ON users_data<br>AFTER INSERT, UPDATE<br>AS <br>BEGIN<br>  INSERT INTO credit_risk_flags (client_id, credit_score, yearly_income, total_debt, debt_load_ratio, risk_level)<br>  SELECT<br>    i.id,<br>    i.credit_score,<br>    yearly_income,<br>    total_debt,<br>    ROUND((i.total_debt * 1.0/NULLIF(i.yearly_income, 0)), 2) AS debt_load_ratio,<br>    CASE <br>      WHEN i.credit_score &lt; 670 AND ROUND((i.total_debt * 1.0/NULLIF(i.yearly_income, 0)), 2) BETWEEN 0.5 AND 0.99 THEN &#39;Moderate&#39;<br>      WHEN i.credit_score &lt; 670 AND ROUND((i.total_debt * 1.0/NULLIF(i.yearly_income, 0)), 2) BETWEEN 1.0 AND 1.99 THEN &#39;High&#39;<br>      WHEN i.credit_score &lt; 670 AND ROUND((i.total_debt * 1.0/NULLIF(i.yearly_income, 0)), 2) &gt;= 2.0 THEN &#39;Critical&#39;<br>    END AS risk_level<br>    FROM inserted i<br>    WHERE i.credit_score &lt; 670<br>    AND ROUND((i.total_debt * 1.0/NULLIF(i.yearly_income, 0)), 2) &gt;= 0.5;<br>END;</pre><p>Customers with a debt-load ratio between 0.5 and 0.99 will be flagged as Moderate, those between 1.0 and 1.99 are considered High risk, and those above 2.0 are regarded as Critical risk and may face various measures, including rejections and collections for those currently in default of repayment obligations.</p><p><strong>3. Real-time Fraud Detection</strong></p><p>Having flagged fraudulent transactions in the <a href="https://medium.com/@sharonobi360/bank-customer-analysis-and-fraud-detection-9a21f0846fb8">previous report</a>, we need to deploy optimized SQL queries with real-time anomaly detection to identify suspicious transactions.</p><p>Similar to the trigger for risky loans, the workflow for this system involves creating a log table to capture suspicious transactions, implementing the rules for fraud detection using SQL, and finally developing the trigger to flag transactions in real-time.</p><p><strong><em>Step 1: Create a log table for suspicious transactions</em></strong></p><pre>CREATE TABLE fraud_flags (<br> flag_id INT PRIMARY KEY IDENTITY (1,1) NOT NULL,<br> transaction_id INT NOT NULL,<br> client_id INT NOT NULL,<br> txn_date DATETIME DEFAULT GETDATE(),<br> avg_monthly_spend DECIMAL(10, 2) NOT NULL,<br> amount INT NOT NULL,<br> reason VARCHAR(500),<br> flagged_at DATETIME DEFAULT GETDATE()<br>);</pre><p><strong><em>Step 2: Define business logic for fraud detection</em></strong></p><p>These usually vary from bank to bank, but the most common indicators of fraudulent transactions are:</p><ul><li><em>High transaction value:</em> where the amount spent in one transaction is 3 times higher than the customer&#39;s average monthly spend.</li><li><em>High transaction volume within a short period:</em> where there are 4 or more transactions on an account within 5 minutes.</li></ul><p>The SQL implementation of these rules involves stacked CTEs that compute the <em>monthly spend per customer </em>and the <em>average monthly spend per customer</em> before returning the customer&#39;s details where an anomaly is detected — accounts that record a transaction (t.amount) greater than 3 times the customer&#39;s average monthly spend.</p><pre>With MonthlySpend AS (<br>  SELECT<br>        client_id,<br>        FORMAT(date, &#39;yyyy-MM&#39;) AS txn_month,<br>        SUM(amount) as monthly_spend<br>  FROM transactions_data<br>  GROUP BY client_id, FORMAT(date, &#39;yyyy-MM&#39;)<br>),<br>AvgSpend AS (<br>  SELECT<br>        client_id,<br>        ROUND(AVG(monthly_spend),2) AS avg_monthly_spend<br>  FROM MonthlySpend<br>  GROUP BY client_id<br>),<br>HighValueTransactions AS (<br>  SELECT<br>        t.id AS txn_id,<br>        t.client_id,<br>        t.date AS txn_date,<br>        a.avg_monthly_spend,<br>        t.amount AS txn_amount,<br>        &#39;High spend vs Avg spend&#39; AS reason<br>  FROM transactions_data t<br>  JOIN AvgSpend a ON t.client_id = a.client_id<br>  WHERE t.amount &gt; 3 * a.avg_monthly_spend<br>),<br>HighFrequency AS (<br>  SELECT<br>        t1.id AS txn_id,<br>        t1.client_id,<br>        t1.date AS txn_date,<br>        a.avg_monthly_spend,<br>        t1.amount AS txn_amount,<br>        &#39;High frequency transactions&#39; AS reason<br>  FROM transactions_data t1<br>  JOIN transactions_data t2 ON t1.client_id = t2.client_id<br>        AND t2.date BETWEEN DATEADD(MINUTE, -5, t1.date) AND t1.date -- looks at all transactions on the account within 5 minutes<br>  JOIN AvgSpend a ON a.client_id = t1.client_id<br>  GROUP BY t1.client_id, t1.id, t1.date, a.avg_monthly_spend, t1.amount<br>  HAVING COUNT(*) &gt;= 4<br>)<br><br>SELECT * FROM HighValueTransactions<br>UNION ALL<br>SELECT * FROM HighFrequency;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/787/1*8Sjg4aKDKybcbmlS6np1Gg.png" /><figcaption>Transactions that meet the criteria in the dataset.</figcaption></figure><p><strong><em>Step 3: Create a summary table</em></strong> to calculate and store the monthly spend and the average monthly spend. This table will be refreshed periodically and will act as a lookup table for the avg_monthly_spend criteria in the trigger. This is an SQL optimization technique to reduce the triggers&#39; dependence on computing the monthly and average monthly spend for each customer upon every insert or update (that would be computationally expensive).</p><pre>CREATE TABLE customer_spend_summary (<br>  client_id INT PRIMARY KEY,<br>  avg_monthly_spend DECIMAL(12,2)<br>)<br>-- Compute and insert the Avg Monthly Spend using subqueries<br>INSERT INTO customer_spend_summary(client_id, avg_monthly_spend)<br>SELECT<br>  client_id,<br>  ROUND(AVG(monthly_spend),2) as avg_monthly_spend<br>FROM (<br>  SELECT <br>    client_id,<br>    DATEPART(YEAR, date) AS txn_yr,<br>    DATEPART(MONTH, date) AS txn_month,<br>    SUM(amount) AS monthly_spend<br>  FROM transactions_data<br>  GROUP BY client_id, DATEPART(YEAR, date), DATEPART(MONTH, date)<br>  ) m<br>GROUP BY client_id;</pre><p><strong><em>Step 4: Create a trigger</em></strong> to flag suspicious transactions as they occur.</p><p>This trigger combines both rules for anomaly detection, so that the moment a transaction is recorded in the <em>transactions_data</em> table — which is 3 times higher than the customer&#39;s average monthly spend — or where multiple transactions are recorded on the same account, such transactions are flagged and logged in the <em>fraud_flags</em> table.</p><pre>CREATE TRIGGER tr_flag_suspicious_txns ON transactions_data<br>AFTER INSERT<br>AS<br>BEGIN<br>  SET NOCOUNT ON;<br>  -- a. High Spend vs. Avg Spend<br>  INSERT INTO fraud_flags (transaction_id, client_id, txn_date, avg_monthly_spend, amount, reason)<br>  SELECT<br>    i.id,<br>    i.client_id,<br>    i.date,<br>    cs.avg_monthly_spend,<br>    i.amount,<br>    &#39;High Spend vs Avg Spend&#39;<br>  FROM inserted i <br>  JOIN customer_spend_summary cs ON i.client_id = cs.client_id<br>  WHERE i.amount &gt; 3 * cs.avg_monthly_spend;<br><br> -- b. High frequency (4+ transactions within 5 minutes)<br>  INSERT INTO fraud_flags (transaction_id, client_id, txn_date, avg_monthly_spend, amount, reason)<br>  SELECT<br>    i.id,<br>    i.client_id,<br>    i.date,<br>    cs.avg_monthly_spend,<br>    i.amount,<br>    &#39;High frequency transactions&#39;<br>  FROM inserted i<br>  JOIN customer_spend_summary cs ON i.client_id = cs.client_id <br>  CROSS APPLY (<br>    SELECT COUNT(*) AS txn_count<br>    FROM transactions_data t<br>    WHERE t.client_id = i.client_id <br>      AND t.date BETWEEN DATEADD(MINUTE, -5, i.date) AND i.date<br>    ) recent r<br>  WHERE r.txn_count &gt;= 4<br>END;</pre><p><strong>Conclusion</strong></p><p>These automated risk prevention systems and real-time anomaly detection ensure a reduction in the Bank’s exposure to credit and fraud risk. We have shown that advanced SQL queries can be effective for risk monitoring and fraud detection.</p><p>The customer logs for loans and suspicious transactions also enable the Bank to comply with reporting obligations to regulators and fulfill its audit responsibilities to stakeholders.</p><p>For Aurora Bank, this means fewer defaults, fewer fraudulent payouts, and clearer compliance reports.</p><p>Full <a href="https://drive.google.com/file/d/1vGE2WCkppsUlOUfaRODaSIwTi8cyTFBP/view?usp=sharing"><strong><em>SQL Script available here</em></strong></a><strong>.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f18fc314441c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Bank Customer Analysis and Fraud Detection]]></title>
            <link>https://medium.com/@sharonobi360/bank-customer-analysis-and-fraud-detection-9a21f0846fb8?source=rss-14e79255a32c------2</link>
            <guid isPermaLink="false">https://medium.com/p/9a21f0846fb8</guid>
            <category><![CDATA[fraud-detection]]></category>
            <category><![CDATA[banking]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analytics]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Sharon Obi]]></dc:creator>
            <pubDate>Thu, 14 Aug 2025 13:56:28 GMT</pubDate>
            <atom:updated>2025-08-26T12:22:52.269Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1U4Ttf6YukWCj5IsuoVBRw.jpeg" /><figcaption>Image by <a href="https://tinyurl.com/4tveasvp">Qubes Pictures</a></figcaption></figure><p>This project analyzes over 150,000 rows of data from Aurora Banks customers&#39; transactions, user data, merchant data, and merchant descriptions to:</p><ul><li>Understand customer demographics, including their age, income, and credit brackets;</li><li>Discover customer spending behaviours within each demographic (for marketing purposes);</li><li>Flag suspicious transactions for fraud investigation; and</li><li>Identify customers who pose significant credit risks.</li></ul><p><strong>Data Preparation</strong></p><p>The data was obtained from the <a href="https://zoomcharts.com/en/microsoft-power-bi-custom-visuals/challenges/onyx-data-december-2024">ZoomCharts Onyx Data Challenge</a>, which consisted of 4 tables containing users&#39; data, cards’ data, transactions’ data, and mcc data (for merchants&#39; details).</p><p>The CSV data were imported into Azure Data Studio and appropriately preprocessed — checked for duplicates and only allowed null values where necessary in the dimension tables (users_data and mcc_codes).</p><p><strong>Business Analysis</strong></p><p><strong><em>1. Customer Profiles</em></strong></p><p>We first verify the total volume of transactions, average incomes, and credit scores of the users to get a high-level understanding of income levels, overall creditworthiness, and transaction volume among customers between January 1, 2022, and October 31, 2024. Thereafter, we dive into customer segmentation across age, gender, and income, and lastly compare these segments by their credit score brackets.</p><pre>-- 1.1 Total transactions by Customers<br>SELECT <br>    u.id,<br>    COUNT(t.id) as txn_count,<br>    ROUND(SUM(t.amount), 2)as total_txn_usd<br>FROM users_data u<br>JOIN transactions_data t <br>    ON u.id = t.client_id<br>GROUP BY u.id<br>ORDER BY txn_count DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/771/1*VLs9MrxgLeOpGFDf9Lo5-Q.png" /><figcaption>303 Customers accounted for all the transactions recorded.</figcaption></figure><pre>SELECT <br>    ROUND(SUM(t.amount), 2)as total_txn_usd,<br>    ROUND(AVG(u.per_capita_income), 2) as avg_per_capita,<br>    ROUND(AVG(u.yearly_income), 2) as avg_yearly_income,<br>    AVG(u.credit_score) as avg_credit_score<br>FROM users_data u<br>JOIN transactions_data t <br>    ON u.id = t.client_id;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/460/1*AL429z24hcOFVt9uxfkWKw.png" /></figure><p><em>Insights</em>:</p><ul><li>303 out of the 2000 total customers accounted for transactions worth $6,874,483.48 between January 1, 2022, and October 31, 2024.</li><li>The average salary per capita and average yearly income of the customer base are $22,795.57 and $45,250.75, respectively.</li><li>The Average credit score for customers is 719, which is a Good FICO score, but we shall dive deeper very soon.</li></ul><p><strong><em>a. Customer distribution by Age</em></strong></p><pre>SELECT<br>    CASE <br>        WHEN birth_year BETWEEN 1995 AND 2002 THEN &#39;Young&#39;<br>        WHEN birth_year BETWEEN 1970 AND 1994 THEN &#39;Middle-Aged&#39;<br>        WHEN birth_year BETWEEN 1959 AND 1969 THEN &#39;Old&#39;<br>        ELSE &#39;Very Old&#39;<br>    END AS age_bracket,<br>    COUNT(*) as total_customers<br>FROM users_data<br>GROUP BY <br>    CASE <br>        WHEN birth_year BETWEEN 1995 AND 2002 THEN &#39;Young&#39;<br>        WHEN birth_year BETWEEN 1970 AND 1994 THEN &#39;Middle-Aged&#39;<br>        WHEN birth_year BETWEEN 1959 AND 1969 THEN &#39;Old&#39;<br>        ELSE &#39;Very Old&#39;<br>    END<br>ORDER BY total_customers DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/377/1*teqk4UQvGX61sQ9oxwC8Kg.png" /></figure><p><em>Insight</em>:</p><ul><li>Most customers (906) are middle-aged, born between 1970 and 1994.</li></ul><p><strong><em>b. Customer distribution by Gender</em></strong></p><pre>With AgeGroup AS (<br>    SELECT <br>        CASE <br>            WHEN birth_year BETWEEN 1995 AND 2002 THEN &#39;Young&#39;<br>            WHEN birth_year BETWEEN 1970 AND 1994 THEN &#39;Middle-Aged&#39;<br>            WHEN birth_year BETWEEN 1959 AND 1969 THEN &#39;Old&#39;<br>            ELSE &#39;Very Old&#39;<br>        END As AgeGroup, <br>        gender <br>    FROM users_data<br>)<br><br>SELECT <br>    AgeGroup,<br>    gender,<br>    COUNT(gender) AS GenderCount,   <br>    RANK() OVER (ORDER BY COUNT(gender) DESC) AS AgeRank<br>FROM AgeGroup<br>GROUP BY AgeGroup, gender;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/536/1*q8fGlQ_GdWMrKK6KOgnCCg.png" /></figure><p><em>Insight</em>:</p><ul><li>Most of the bank&#39;s customers are Middle-aged Females (456), followed by middle-aged men (450).</li></ul><p><strong><em>c. Customer distribution by Income Levels</em></strong></p><pre>SELECT <br>    CASE <br>        WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>        WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>        WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100K)&#39;<br>        ELSE &#39;High Income (&gt;100K)&#39;<br>    END AS income_bracket,<br>    COUNT(*) as total_customers<br>FROM users_data<br>GROUP BY <br>        CASE <br>        WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>        WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>        WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100K)&#39;<br>        ELSE &#39;High Income (&gt;100K)&#39;<br>    END<br>ORDER BY total_customers DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/461/1*AWaYGjFm3WT2_9AGeWCBUw.png" /></figure><p><em>Insights</em>:</p><ul><li>Middle-income earners represent the largest segment of the bank&#39;s customers. This offers a prime target for tailored banking products.</li></ul><p><strong><em>d. Average Spend by Income levels</em></strong></p><pre>With TotalSpend AS(<br>    SELECT <br>        u.yearly_income,<br>        u.total_debt,<br>        u.credit_score,<br>        SUM(t.amount) as total_spend<br>    FROM users_data u<br>    JOIN transactions_data t ON u.id = t.client_id<br>    GROUP BY u.yearly_income, u.total_debt, u.credit_score<br>),<br>IncomeBracket AS (<br>    SELECT<br>        yearly_income,<br>        total_debt,<br>        total_spend,<br>        CASE <br>            WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>            WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>            WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100K)&#39;<br>            ELSE &#39;High Income (Above 100K)&#39;<br>        END AS income_bracket<br>    FROM TotalSpend<br>)<br><br>SELECT<br>    income_bracket,<br>    ROUND(AVG(total_spend), 2) as avg_spend<br>FROM IncomeBracket<br>GROUP BY income_bracket<br>ORDER BY avg_spend DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/411/1*ml5TNXAn_Y2gkJfOlAV3dA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/991/1*GjSfxdJOAW7bCFtZeGwTNg.png" /></figure><p><em>Insights:</em></p><ul><li>High-income customers, although the fewest (52), recorded the highest average spend ($45,710.36).</li></ul><p><strong><em>e. Customer demographics by Average Credit Score, Average Income, and Average Debts:</em></strong></p><p>The Fair Isaac Corporation (FICO) Scores are the most widely used by many credit institutions for determining a person&#39;s creditworthiness. A score below 580 is considered Poor, and one between 580 and 669 is considered Fair. A score between 670 and 739 is considered Good by most lenders, and one between 740 and 799 is Very Good. A credit score above 800 is Exceptional.</p><pre>With CustomerSegmentation AS (<br>    SELECT <br>        id,<br>        gender,<br>        total_debt,<br>        yearly_income,<br>        credit_score,<br>        CASE <br>            WHEN birth_year BETWEEN 1995 AND 2002 THEN &#39;Young&#39;<br>            WHEN birth_year BETWEEN 1970 AND 1994 THEN &#39;Middle-Aged&#39;<br>            WHEN birth_year BETWEEN 1959 AND 1969 THEN &#39;Old&#39;<br>            ELSE &#39;Very Old&#39;<br>        END As age_group,<br>        CASE <br>            WHEN credit_score BETWEEN 800 AND 850 THEN &#39;Exceptional&#39;<br>            WHEN credit_score BETWEEN 740 AND 799 THEN &#39;Very Good&#39;<br>            WHEN credit_score BETWEEN 670 AND 739 THEN &#39;Good&#39;<br>            WHEN credit_score BETWEEN 580 AND 669 THEN &#39;Fair&#39;<br>            ELSE &#39;Poor&#39;<br>        END AS credit_bracket,<br>        CASE<br>            WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>            WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>            WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100k)&#39;<br>            ELSE &#39;High Income (Above 100K)&#39;<br>        END AS income_bracket<br>    FROM users_data u<br>)<br><br>SELECT <br>    credit_bracket,    <br>    ROUND(AVG(credit_score), 2) AS avg_credit_score,<br>    age_group,<br>    gender,<br>    COUNT(gender) AS gender_count,<br>    income_bracket,<br>    ROUND(AVG(yearly_income), 2) AS avg_income,<br>    ROUND(AVG(total_debt), 2) AS avg_debt<br>FROM CustomerSegmentation<br>GROUP BY <br>    credit_bracket,<br>    age_group,<br>    gender,<br>    income_bracket<br>ORDER BY credit_bracket, gender_count, avg_income;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/823/1*n15Wfz-T_1ECKtTQq6IGnw.png" /><figcaption>Snippet of Results</figcaption></figure><p><em>Insights</em>:</p><ul><li>Most of the customers with Exceptional credit scores are middle-income earners.</li><li>Middle-aged, middle-income earners populate most credit brackets, presenting a great opportunity to target financial products and services.</li><li>Financial products and services such as loans, children&#39;s savings accounts, bonds, and specialized savings accounts can be targeted at middle-income, middle-aged customers.</li><li>Very old customers hold strong credit; these might be lower earners, but they are quite trustworthy borrowers</li></ul><p><strong><em>2. Credit Risk Analysis</em></strong></p><p><strong><em>a. Debt Load Ratio: </em></strong>The debt-to-income ratio is primarily calculated by dividing total monthly debt by gross monthly income. Due to the absence of monthly repayment data, this analysis approximates credit risk using a total debt-to-income load ratio, using the total outstanding debt to yearly income</p><pre>SELECT TOP 10<br>    id,<br>     CASE <br>        WHEN birth_year BETWEEN 1995 AND 2002 THEN &#39;Young&#39;<br>        WHEN birth_year BETWEEN 1970 AND 1994 THEN &#39;Middle-Aged&#39;<br>        WHEN birth_year BETWEEN 1959 AND 1969 THEN &#39;Old&#39;<br>        ELSE &#39;Very Old&#39;<br>    END As age_group,<br>    credit_score,<br>            CASE <br>            WHEN credit_score BETWEEN 800 AND 850 THEN &#39;Exceptional&#39;<br>            WHEN credit_score BETWEEN 740 AND 799 THEN &#39;Very Good&#39;<br>            WHEN credit_score BETWEEN 670 AND 739 THEN &#39;Good&#39;<br>            WHEN credit_score BETWEEN 580 AND 669 THEN &#39;Fair&#39;<br>            ELSE &#39;Poor&#39;<br>        END AS credit_bracket,<br>        CASE<br>            WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>            WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>            WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100k)&#39;<br>            ELSE &#39;High Income (Above 100K)&#39;<br>        END AS income_bracket,  <br>    yearly_income,<br>    total_debt,<br>    ROUND((total_debt * 1.0/NULLIF(yearly_income, 0)), 2) AS debt_load_ratio<br>FROM users_data<br>ORDER BY debt_load_ratio DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/815/1*4ZZJOTOBVdW6F3ogjDAfVQ.png" /><figcaption>Customers with high debt load ratio</figcaption></figure><p><em>Insights</em>:</p><ul><li>8 out of the 10 customers with the highest debt load are highly leveraged (their debts outweigh their annual income by ~360% — 498%). Despite this, many have Good to Exceptional credit scores and fall within the Middle-Income bracket.</li><li>This suggests that these customers may owe good debts, such as mortgages, or have an impressive repayment history, keeping their credit scores high despite high borrowing.</li><li>To properly compute the DTI and distinguish between good risk and overextension, we need more data on the monthly debt repayments, repayment history, account age and activity, and product holdings; hence, the reversion to Total DTI.</li></ul><p><strong><em>b. Credit Risk: </em></strong>To return the top 10 customers who pose a significant credit risk to the bank, we look at those with a high debt load ratio who have a poor credit score.</p><pre>SELECT TOP 10<br>    id,<br>     CASE <br>        WHEN birth_year BETWEEN 1995 AND 2002 THEN &#39;Young&#39;<br>        WHEN birth_year BETWEEN 1970 AND 1994 THEN &#39;Middle-Aged&#39;<br>        WHEN birth_year BETWEEN 1959 AND 1969 THEN &#39;Old&#39;<br>        ELSE &#39;Very Old&#39;<br>    END As age_group,<br>    credit_score,<br>            CASE <br>            WHEN credit_score BETWEEN 800 AND 850 THEN &#39;Exceptional&#39;<br>            WHEN credit_score BETWEEN 740 AND 799 THEN &#39;Very Good&#39;<br>            WHEN credit_score BETWEEN 670 AND 739 THEN &#39;Good&#39;<br>            WHEN credit_score BETWEEN 580 AND 669 THEN &#39;Fair&#39;<br>            ELSE &#39;Poor&#39;<br>        END AS credit_bracket,<br>        CASE<br>            WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>            WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>            WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100k)&#39;<br>            ELSE &#39;High Income (Above 100K)&#39;<br>        END AS income_bracket,  <br>    yearly_income,<br>    total_debt,<br>    ROUND((total_debt * 1.0/NULLIF(yearly_income, 0)), 2) AS debt_load_ratio<br>FROM users_data<br>WHERE credit_score  &lt; 580<br>ORDER BY debt_load_ratio DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/810/1*gdCHMD7toPWU7n-FUHkp6Q.png" /><figcaption>Customers with High Credit Risk</figcaption></figure><p><em>Insights:</em></p><ul><li>Of the 81 customers with poor credit scores, the top 10 have a total debt load ratio of ~244% — 389%. Statistically, persons with a DTI ratio above 50% are very likely to default on their loans.</li><li>9 of the top 10 risky customers are middle-income individuals.</li><li>We need more data on monthly loan repayments to capture a more precise DTI ratio.</li></ul><p><strong><em>3. Spending Behaviour</em></strong></p><p><strong><em>Where do customers spend the most money?</em></strong></p><pre>SELECT TOP 5<br>    m.description,<br>    ROUND(SUM(t.amount), 2) TxnVolume<br>FROM transactions_data t <br>JOIN mcc_codes m ON t.mcc = m.mcc_id<br>GROUP BY m.description<br>ORDER by TxnVolume DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/483/1*GOt8zsRwWMh8E31r_U13zw.png" /></figure><p><em>Insights:</em></p><ul><li>Money transfers account for the largest customer spend ($596,560), followed by Grocery Stores, Supermarkets ($501,980.56), Wholesale Clubs ($471,078.75), and Drug Stores and Pharmacies ($430,486.73).</li></ul><p><strong><em>Where do the high-income customers spend the most?</em></strong></p><pre>SELECT TOP 5<br>    CASE<br>        WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>        WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>        WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100k)&#39;<br>        ELSE &#39;High Income (Above 100K)&#39;<br>    END AS income_bracket,<br>    m.Description as merchant,<br>    ROUND(SUM(t.amount), 2) as txn_volume<br>FROM transactions_data t<br>JOIN mcc_codes m ON m.mcc_id = t.mcc<br>JOIN users_data u ON t.client_id = u.id<br>WHERE yearly_income &gt; 100000 AND errors IS NULL<br>GROUP BY <br>    CASE<br>        WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>        WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>        WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100k)&#39;<br>        ELSE &#39;High Income (Above 100K)&#39;<br>    END,<br>        m.description<br>ORDER BY txn_volume DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/675/1*QJZj785Xp4Lanp1at265gA.png" /><figcaption>Top 5 places/merchants where customers spend the most</figcaption></figure><p><em>Insights</em>:</p><ul><li>High-income customers spent the most on eating places and restaurants (fine dining most likely) ($31,013), followed by money transfers ($23,480). Fast food restaurants were the third highest in transaction volume ($16,939), indicating that they are spending a significant amount on food.</li></ul><p><strong><em>4. Card Usage Patterns</em></strong></p><p><strong><em>Average Number of Cards by Demographic</em></strong></p><pre>SELECT<br>    CASE <br>        WHEN credit_score BETWEEN 800 AND 850 THEN &#39;Exceptional&#39;<br>        WHEN credit_score BETWEEN 740 AND 799 THEN &#39;Very Good&#39;<br>        WHEN credit_score BETWEEN 670 AND 739 THEN &#39;Good&#39;<br>        WHEN credit_score BETWEEN 580 AND 669 THEN &#39;Fair&#39;<br>        ELSE &#39;Poor&#39;<br>    END AS credit_bracket,<br>    CASE<br>        WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>        WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>        WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100k)&#39;<br>        ELSE &#39;High Income (Above 100K)&#39;<br>    END AS income_bracket,  <br>    AVG(num_credit_cards) as avg_num_cards<br>FROM users_data<br>GROUP BY <br>    CASE <br>        WHEN credit_score BETWEEN 800 AND 850 THEN &#39;Exceptional&#39;<br>        WHEN credit_score BETWEEN 740 AND 799 THEN &#39;Very Good&#39;<br>        WHEN credit_score BETWEEN 670 AND 739 THEN &#39;Good&#39;<br>        WHEN credit_score BETWEEN 580 AND 669 THEN &#39;Fair&#39;<br>        ELSE &#39;Poor&#39;<br>    END,<br>    CASE<br>        WHEN yearly_income &lt; 20000 THEN &#39;Low Income (Below 20K)&#39;<br>        WHEN yearly_income BETWEEN 20000 AND 50000 THEN &#39;Middle Income (20K - 50K)&#39;<br>        WHEN yearly_income BETWEEN 50000 AND 100000 THEN &#39;Upper Income (50K - 100k)&#39;<br>        ELSE &#39;High Income (Above 100K)&#39;<br>    END<br>ORDER BY avg_num_cards DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/820/1*mED1Cgh60bgZOQoorAsEBw.png" /><figcaption>Low income earners own more credit cards on average</figcaption></figure><p><em>Insights</em>:</p><ul><li>Those in the low-income bracket with exceptional credit scores own 6 credit cards on average (the highest).</li><li>Those with poor to fair credit scores have between 1 and 2 credit cards.</li></ul><p><strong><em>5. Fraud Detection</em></strong></p><p>We isolate and flag accounts on which transactions for the month exceed twice the average monthly spend of such customers.</p><p>Three common table expressions (CTEs) are stacked, first to determine the monthly spend, the second to calculate the average monthly spend for each customer, and then, the last one to designate the appropriate severity level where fraud was detected using our parameters.</p><pre>With MonthlySpend AS (<br>    SELECT <br>        client_id,<br>        FORMAT(date, &#39;yyyy-MM&#39;) as txn_month,<br>        SUM(amount) as monthly_spend<br>    FROM transactions_data <br>    GROUP BY client_id, FORMAT(date, &#39;yyyy-MM&#39;)<br>),<br>AvgSpend AS (<br>    SELECT<br>        client_id,<br>        AVG(monthly_spend) as avg_monthly_spend<br>    FROM MonthlySpend<br>    GROUP BY client_id<br>),<br>SpikeLevels AS (<br>SELECT <br>    m.client_id AS client_id,<br>    m.txn_month AS txn_month,<br>    m.monthly_spend,<br>    a.avg_monthly_spend,<br>    (m.monthly_spend - a.avg_monthly_spend) AS spike_diff,<br>    (m.monthly_spend - a.avg_monthly_spend)/a.avg_monthly_spend AS percent_diff,<br> CASE<br>        WHEN m.monthly_spend &gt; 3 * a.avg_monthly_spend  THEN &#39;Critical&#39;<br>        WHEN m.monthly_spend &gt; 2 * a.avg_monthly_spend  THEN &#39;High&#39;<br>        WHEN m.monthly_spend &gt; 1.5 *a.avg_monthly_spend THEN &#39;Moderate&#39;<br>    ELSE &#39;Safe&#39;<br> END AS fraud_alert<br>FROM AvgSpend a<br>JOIN MonthlySpend m ON a.client_id = m.client_id<br>WHERE m.monthly_spend &gt; 2 * a.avg_monthly_spend <br>)<br><br>SELECT <br>    client_id,<br>    txn_month,<br>    monthly_spend,<br>    avg_monthly_spend,<br>    ROUND(spike_diff, 2) as spike_diff,<br>    ROUND(percent_diff, 2) AS percent_diff,<br> fraud_alert<br>FROM SpikeLevels<br>ORDER by fraud_alert;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/771/1*H4NNMTTbSp6fR0dKJhciYQ.png" /><figcaption>Snippet of 525 rows for transactions ranging from moderate to critical</figcaption></figure><p><strong><em>Customers with multiple suspicious transactions</em></strong></p><p>Finally, we flag accounts that initiated multiple critical-level transactions to help in prioritizing investigations</p><pre>With MonthlySpend AS (<br>    SELECT <br>        client_id,<br>        FORMAT(date, &#39;yyyy-MM&#39;) as txn_month,<br>        SUM(amount) as monthly_spend<br>    FROM transactions_data <br>    GROUP BY client_id, FORMAT(date, &#39;yyyy-MM&#39;)<br>),<br>AvgSpend AS (<br>    SELECT<br>        client_id,<br>        AVG(monthly_spend) as avg_monthly_spend<br>    FROM MonthlySpend<br>    GROUP BY client_id<br>),<br>SpikeLevels AS (<br>SELECT <br>    m.client_id AS client_id,<br>    m.txn_month AS month,<br>    m.monthly_spend,<br>    a.avg_monthly_spend,<br>    (m.monthly_spend - a.avg_monthly_spend) AS spike_diff,<br>    (m.monthly_spend - a.avg_monthly_spend)/a.avg_monthly_spend AS percent_diff,<br> CASE<br>        WHEN m.monthly_spend &gt; 3 * a.avg_monthly_spend  THEN &#39;Critical&#39;<br>        WHEN m.monthly_spend &gt; 2 * a.avg_monthly_spend  THEN &#39;High&#39;<br>        WHEN m.monthly_spend &gt; 1.5 *a.avg_monthly_spend THEN &#39;Moderate&#39;<br>    ELSE &#39;Safe&#39;<br> END AS fraud_alert<br>FROM AvgSpend a<br>JOIN MonthlySpend m ON a.client_id = m.client_id<br>WHERE m.monthly_spend &gt; 2 * a.avg_monthly_spend<br>),<br>MultipleCriticals AS (<br>    SELECT<br>        client_id<br>    FROM SpikeLevels<br>    WHERE fraud_alert = &#39;Critical&#39;<br>    GROUP BY client_id<br>    HAVING COUNT(*) &gt; 1<br>),<br>FraudTxnCounts AS (<br>    SELECT <br>        s.client_id,<br>        s.month,<br>        s.fraud_alert,<br>        COUNT(*) OVER (PARTITION BY mc.client_id) as txn_count<br>    FROM SpikeLevels s <br>    JOIN MultipleCriticals mc ON s.client_id = mc.client_id<br>    WHERE s.fraud_alert = &#39;Critical&#39;<br>)<br><br>SELECT DISTINCT<br>    f.client_id,<br>    f.fraud_alert,<br>    f.txn_count<br>FROM FraudTxnCounts f<br>ORDER BY f.txn_count DESC, f.client_id DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/770/1*uUfracjj8PlaI-NI4gVdpQ.png" /></figure><p><em>Insights:</em></p><ul><li>101 transactions between January 2022 and October 2024 were flagged for critical fraud level activity.</li><li>23 different clients had numerous fraudulent transactions on their accounts.</li></ul><p><em>Top Reasons for Transaction Errors</em></p><pre>SELECT TOP 5<br>    errors,<br>    ROUND(SUM(amount), 2) as txn_volume<br>FROM transactions_data<br>WHERE errors IS NOT NULL<br>GROUP BY errors<br>ORDER BY txn_volume DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/249/1*eLxjoDdJ5HNv18vmVkjrDw.png" /></figure><p><strong><em>Transaction Errors by Merchants</em></strong></p><pre>SELECT TOP 5<br>    m.[Description],<br>    t.errors,<br>    ROUND(SUM(t.amount), 2) as txn_volume_USD    <br>FROM transactions_data t <br>JOIN mcc_codes m ON t.mcc = m.mcc_id<br>WHERE t.errors IS NOT NULL<br>GROUP BY m.[Description], t.errors<br>ORDER BY txn_volume_USD DESC;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/458/1*kbq84j-YDkY2Hnh5Iz-frg.png" /></figure><p><em>Insights:</em></p><ul><li>Insufficient balance was the chief reason for transaction errors, accounting for a total of $123,259.26 of failed transactions.</li><li>Another key reason for transaction failures is technical glitches ($15,212.79), followed closely by client-side errors such as wrong pins, wrong card numbers, and wrong CVVs.</li><li>For merchants, Money Transfers accounted for the highest errors — $16,900 in failed transactions due to insufficient balance.</li></ul><p><strong>Conclusion</strong></p><p>This analysis provides a clear picture of the bank&#39;s customer base, their spending patterns, and associated risks (credit and fraud). Middle-income, middle-aged customers make up the core segment, driving transaction volume and engagement across most categories.</p><p>High-income customers, although a fraction of the other segments in number, recorded a much higher average spend ($45,710.36) than the other income segments. Their expenditure showed a disproportionately high spend on dining, fast food, and money transfers, indicating an opportunity for marketing in these merchant outlets</p><p>Credit Risk analysis revealed debt-heavy but credit-worthy customers who are possibly leveraging good debt. This underscores the need for bespoke credit management strategies.</p><p>Customer spending is anchored on money transfers and essential goods, highlighting opportunities for loyalty programs and partnerships with these merchant categories.</p><p>Fraud detection flagged 23 customers with repeated critical-level anomalies, signaling a need for immediate investigative actions to mitigate financial exposure.</p><p>These insights, if leveraged, can enhance profitability, reduce exposure to credit and fraud risks, and strengthen customer loyalty.</p><p><strong>Recommendations</strong></p><ol><li><strong>Marketing &amp; Customer Retention: </strong>Focus marketing efforts on middle-aged, middle-income earners to maximise reach and develop premium offerings to high-income customers. Build partnerships with fine dining establishments and fast food outlets frequented by customers, to launch cross-promotional offers and loyalty incentives.</li><li><strong>Risk &amp; Fraud Prevention: </strong>Initiate immediate investigations on the 23 customers whose accounts recorded multiple critical-level transactions to minimize exposure to fraud. Enhance fraud detection by implementing business rules for live detection to ensure swift action.</li><li><strong>Operational Adjustments: </strong>Upgrade network, third-party, and inter-banking software to minimize technical glitches. Monitor customers with high debt load and strong credit scores to identify sustainable lending policies and nuanced credit limits without increasing default risk.</li><li><strong>Expand Data Collection: </strong>Broaden data collection to include monthly loan repayments to enable more precise debt calculations and analyses.</li></ol><p><em>Access the full </em><a href="https://drive.google.com/file/d/1vGE2WCkppsUlOUfaRODaSIwTi8cyTFBP/view?usp=sharing"><strong><em>SQL Script here</em></strong></a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9a21f0846fb8" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What Drives Sales and Revenue At A Restaurant? (Part 2 - Predictive Modeling)]]></title>
            <link>https://medium.com/@sharonobi360/what-drives-sales-and-revenue-at-a-restaurant-part-2-predictive-modeling-7abab4c07fea?source=rss-14e79255a32c------2</link>
            <guid isPermaLink="false">https://medium.com/p/7abab4c07fea</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-science-projects]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[lasso-regression]]></category>
            <category><![CDATA[supervised-learning]]></category>
            <dc:creator><![CDATA[Sharon Obi]]></dc:creator>
            <pubDate>Sat, 12 Jul 2025 22:58:31 GMT</pubDate>
            <atom:updated>2025-10-31T18:46:27.888Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RR5SJvIR9w5LNdM9lbXIKw.jpeg" /><figcaption>Image by <a href="https://tinyurl.com/9dubpsye">AlexNut</a></figcaption></figure><p><strong>Introduction</strong></p><p>Having conducted an exploratory analysis of the data in <a href="https://medium.com/@sharonobi360/what-drives-sales-and-revenue-in-a-restaurant-pt-1-e24d555cc103">Part 1</a>, this section answers the question, ‘Which features best predict monthly revenue?’.</p><p>We will walk through the modeling pipeline, performance comparisons (for Linear, RidgeCV, LassoCV, Decision Tree, Random Forest, Gradient Boosting, and XGBoost Regression models), feature selection, SHapely Additive exPlanation (SHAP) Analysis, and unscale coefficients to unravel real-world impact.</p><p><strong>Data Preparation</strong></p><p>A correlation matrix was used to confirm which features were highly correlated with the target feature (monthly revenue). Then the data was split into train and test sets (70/30).</p><pre># 1. Visualize the Matrix<br>plt.figure(figsize=(15, 6))<br>sns.heatmap(rest_data.corr(), cmap=&#39;coolwarm&#39;, annot=True, fmt=&#39; .2f&#39;)<br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/850/1*sgSsE0hX_U8yyHLhb49M8Q.png" /></figure><p>The number of customers is highly positively correlated with the monthly revenue. The data was then split into a training set (700 records) and a test set (300 records):</p><pre># 2. Split the data into training and test sets<br>from sklearn.model_selection import train_test_split, GridSearchCV<br>from sklearn.preprocessing import StandardScaler<br>import statsmodels.api as sm<br><br>X = rest_data.drop(&#39;Monthly_Revenue&#39;, axis=1)<br>y = rest_data[&#39;Monthly_Revenue&#39;]<br>X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3, random_state=42)<br>print(X_train.shape, X_test.shape, y_train.shape, y_test.shape)</pre><p><strong>Model Evaluation</strong></p><p>The models were initially trained on the full features, followed by training on pruned features after the Random Forest model was trained (Feature selection).</p><p>The R2 scores and Root Mean Square Errors (RMSE) for all the models were compared both on the full features and on the pruned features to determine which model accurately predicts the changes in monthly revenue.</p><pre># Pruned features<br>pruned_cols = [&#39;Average_Customer_Spending&#39;,&#39;Promotions&#39;, &#39;Reviews&#39;, &#39;Cuisine_Italian&#39;, &#39;Cuisine_Japanese&#39;, &#39;Cuisine_Mexican&#39;]<br><br>X_train_pruned = X_train.drop(columns=pruned_cols, axis=1)<br>X_test_pruned = X_test.drop(columns=pruned_cols, axis=1)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/513/1*f1mmzedT3JJQ8lr3X9EgYw.png" /><figcaption>Model Evaluation Summary: Full vs Pruned features</figcaption></figure><p><strong>Best Model</strong></p><p>The Linear Model and LassoCV performed the best on the Pruned features; however, considering the LassoCV model’s suitability for feature selection by driving irrelevant feature coefficients close to 0 using L1 regularization, LassoCV is the preferred model.</p><p>The top 3 features were Number_of_Customers, Marketing_Spend, and Menu_Price.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/240/1*g94WvPIN9hnjyTcHotSHnw.png" /><figcaption>LassoCV Scaled Coefficients</figcaption></figure><p><strong>Feature Importance using SHAP</strong></p><p>SHapely Additive exPlanations makes complex models interpretable by showing the extent to which each feature contributes to a specific prediction, using game-theory principles.</p><pre># Summary Bar Plot<br>shap.summary_plot(shap_values_lasso, X_test_scaled_df, plot_type=&quot;bar&quot;, show=False)<br><br># Summary Beeswarm Plot <br>shap.summary_plot(shap_values_lasso, X_test_scaled_df, show=False)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/789/1*94HKgDktHFq-zFwRkXltVA.png" /><figcaption>SHAP Important Features: Bar Plot</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/770/1*Kuog9O_H4gZTrKVaRiXScg.png" /><figcaption>SHAP Important Features: Beeswarm Plot</figcaption></figure><p>The plots above are consistent with the feature importance indicated by the Random Forest Regression model, thereby reinforcing the relevance of the top 3 features.</p><pre>important_features = pd.Series(random_forest.feature_importances_, index=X_train.columns).sort_values(ascending=False).plot(kind=&#39;bar&#39;)<br>plt.title(&#39;Important Features&#39;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/547/1*Lgy9WV0NoaHzPDczCrMsDQ.png" /><figcaption>Random Forest important Features</figcaption></figure><p><strong>Unscaled Coefficients</strong></p><p>The scaled features return the standardized values of each record(i.e. they have been transformed to have a mean of 0 and a standard deviation of 1). To obtain the exact dollar impact of the LassoCV coefficients, we need to unscale the coefficients:</p><pre># Unscaled coefficients<br><br>X_stds = X_train_pruned.std(ddof=0)<br><br>true_coef = lassocv_p.coef_ / X_stds.values<br><br>true_lasso_coef = pd.DataFrame({<br>    &#39;Feature&#39;: X_train_pruned.columns,<br>    &#39;True Coefficient&#39;: true_coef<br>}).sort_values(by=&#39;True Coefficient&#39;, ascending=False)<br><br>print(&quot;Unscaled LassoCV Coefficients:&quot;)<br>true_lasso_coef</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/271/1*4D1ZNxKdDYVRkAED1uHKfw.png" /><figcaption>LassoCV Unscaled Coefficients</figcaption></figure><p>This model predicts that for every $1 increase in marketing spend, there is a $4.89 increase in monthly revenue. Additionally, for every new customer who patronizes the restaurant, monthly revenue increases by $2.87.</p><p><strong>Key Insights</strong></p><p>a. LassoCV proved to be the most accurate model in predicting the monthly revenue using the available data.</p><p>b. The most important features for predicting the monthly revenue were Marketing_Spend, Number of Customers, and Menu_Price. The SHAP bar and beeswarm plots also reiterate the importance of these 3 features.</p><p>d. The Random Forest model showed strong overfitting on the training set with an R2 score of 0.856 (85.6%).</p><p>e. Marketing spend is more effective at the macro-level (increased expenditure boosts revenue), but shows diminishing returns at the micro-level.</p><p><strong>Takeaways</strong></p><ul><li>Focus promotion on Mexican and Italian dishes because they showed marginal gains in monthly revenue.</li><li>Increase marketing spend via targeted marketing campaigns or by offering bundle deals (for instance, pairing Japanese dishes with Mexican or Italian dishes at discounted rates. This will boost average customer spend and sales of the dishes from other cuisines.</li></ul><p>It is important to distinguish correlation from causation; while some features show strong correlation with monthly revenue, this does not mean that they are the true drivers. Given the limitations of the data, other factors driving revenue may be at play that weren’t captured by the dataset, and we cannot conclusively determine causality.</p><p>With additional data on seasonal trends, customer demographics, and geographical trends, our model can serve as a tool for automated, real-time revenue prediction.</p><p><a href="https://colab.research.google.com/drive/1JbaN4AOfd4_c4sqDDGg-ofx4EPvbmP4H#scrollTo=Rehj3PqZIW-Y">The full notebook code is available here.</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7abab4c07fea" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What Drives Sales and Revenue in a Restaurant? (Part 1-Data Analysis)]]></title>
            <link>https://medium.com/@sharonobi360/what-drives-sales-and-revenue-in-a-restaurant-pt-1-e24d555cc103?source=rss-14e79255a32c------2</link>
            <guid isPermaLink="false">https://medium.com/p/e24d555cc103</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[exploratory-data-analysis]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[restaurant-business]]></category>
            <dc:creator><![CDATA[Sharon Obi]]></dc:creator>
            <pubDate>Fri, 11 Jul 2025 12:25:35 GMT</pubDate>
            <atom:updated>2025-08-28T13:24:13.482Z</atom:updated>
            <content:encoded><![CDATA[<h3>What Drives Sales and Revenue at a Restaurant? (Part 1 - Data Analysis)</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bqVuUxNUiqvvPTtRTJcsrg.jpeg" /><figcaption>Image by: <a href="https://tinyurl.com/3x28f9ym">Alexas_Fotos</a></figcaption></figure><p><strong>Introduction</strong></p><p>In this two-part series, I explore the question: “How can restaurants increase revenue using data-backed insights?” In Part 1, I focus on exploratory data analysis of 1,000 menu items to identify:</p><ul><li>Which dishes and cuisines are the most profitable?</li><li>Whether promotions boost sales, and</li><li>Whether marketing spend directly leads to more revenue.</li></ul><p>In Part 2, I train multiple machine learning models, test them, and explain model behaviour and feature importance using SHAP.</p><p><strong>Dataset</strong></p><p>The <a href="https://drive.google.com/file/d/1KZj3SiPTwGytt1XGExQnhiNTLSgovJZa/view?usp=sharing">data</a> obtained from Start-Tech academy contains 1,000 distinct records, each representing a dish served in the restaurant&#39;s menu. Although no data dictionary was provided, columns like Number of Customers, Menu Price, Marketing Spend, Cuisine Type, Average Customer Spending, Promotions, Reviews, and Monthly Revenue suggest that each record is a unique dish. The dataset uses numeric indexing, representing each dish, which makes it suitable for Machine Learning purposes.</p><p><strong>Data Preprocessing</strong></p><p>In preparing the data for analysis, the following preprocessing activities were carried out:</p><p><em>a. No null/missing values were found in the data.</em></p><p><em>b. Cuisine type (being categorical) was converted to dummy variables using one-hot encoding.</em></p><pre># Cuisine_Type was one-hot encoded to convert categorical values to numeric format.<br><br>cuisine_dummies = pd.get_dummies(rest_data[&#39;Cuisine_Type&#39;], prefix=&#39;Cuisine&#39;, drop_first=True).astype(int)<br>rest_data = rest_data.drop(&#39;Cuisine_Type&#39;, axis=1)<br>rest_data = pd.concat([rest_data, cuisine_dummies], axis=1)<br>rest_data</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yowhT5URxr31sIERXlSaiw.png" /></figure><p><em>c. Handled Outliers</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*crub6kTNUqHbT7M9-TYyyw.png" /><figcaption>Used rest_data.describe() to identify outliers</figcaption></figure><pre>rest_data.plot(kind=&#39;box&#39;, figsize=(15, 9))<br><br>plt.show()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ssgd3HNSEmEMJfT2w-ENdA.png" /><figcaption>Box plot to show the position of outliers</figcaption></figure><p>Negative values in the Monthly_Revenue column, like -28.97 (which is impossible considering that the column contains revenue for a month and not profit/loss), were treated as data entry errors and were imputed with 0.</p><pre># Imputing the data<br><br>rest_data.loc[rest_data[&#39;Monthly_Revenue&#39;] &lt;= 0, &#39;Monthly_Revenue&#39;] = 0<br>rest_data[rest_data[&#39;Monthly_Revenue&#39;] &lt;= 0]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SHHeZVV7ZY_uz0vRqz3cmw.png" /><figcaption>Negative values imputed with 0</figcaption></figure><p><strong>Exploratory Data Analysis</strong></p><p>The following questions tackled by this analysis paint a picture of the restaurant&#39;s current performance and possible factors that play a role in driving revenue:</p><p><strong><em>Top performers</em></strong></p><pre>## 1. Top 5 Most Popular dishes<br>## 2. Dishes with the highest average_customer spend<br>## 3. Top Dishes with the most revenue<br><br>most_popular_dishes = rest_data.sort_values(by=&#39;Number_of_Customers&#39;, ascending=False).head(5)<br><br>highest_avg_spend_dishes = rest_data.sort_values(by=&#39;Average_Customer_Spending&#39;, ascending=False).head(5)<br><br>highest_earning_dishes = rest_data.sort_values(by=&#39;Monthly_Revenue&#39;, ascending=False).head(5)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/808/1*0r4UwQ_oK86H8q15z2rUAQ.png" /><figcaption>1. Top 5 most popular dishes</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/804/1*fV1AJhwUFUWaI9pdVZJePA.png" /><figcaption>2. Dishes with the highest average customer spend (Where are customers spending the most money?)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/904/1*g8DcBSxMFHCI42EhstbAQA.png" /><figcaption>3. Top Dishes with the most revenue</figcaption></figure><p><strong><em>Cuisine-level Analysis</em></strong></p><pre>## 4. Best performing cuisine (By total customers, monthly revenue and average spend)<br>## 5. Least performing cuisine<br><br><br>popular_cuisines = rest_data.groupby([&#39;Cuisine_Italian&#39;, &#39;Cuisine_Japanese&#39;, &#39;Cuisine_Mexican&#39;]).agg(<br>                    Total_Customers=(&#39;Number_of_Customers&#39;, &#39;sum&#39;),<br>                    Total_Monthly_Rev=(&#39;Monthly_Revenue&#39;,&#39;sum&#39;),<br>                    Avg_group_spend=(&#39;Average_Customer_Spending&#39;, &#39;mean&#39;)<br>                    ).sort_values(by=&#39;Total_Customers&#39;,ascending=False)<br>popular_cuisines<br><br># Return a proper dataframe with the Cuisines as columns<br><br>cuisine_cols = [&#39;Cuisine_Italian&#39;, &#39;Cuisine_Japanese&#39;, &#39;Cuisine_Mexican&#39;]<br><br>cuisine_indicators = rest_data[cuisine_cols]<br><br># Transpose the cuisine_indicators so they&#39;re in a column<br>total_metrics = pd.DataFrame({<br>                &#39;Total_Customers&#39;: cuisine_indicators.T.dot(rest_data[&#39;Number_of_Customers&#39;]),<br>                &#39;Total_Monthly_Revenue&#39;: cuisine_indicators.T.dot(rest_data[&#39;Monthly_Revenue&#39;]),<br>                &#39;Avg_Group_Spend&#39;: cuisine_indicators.T.dot(rest_data[&#39;Average_Customer_Spending&#39;])<br>})<br><br># Clean up the index names<br>total_metrics.index = total_metrics.index.str.replace(&#39;Cuisine_&#39;, &#39;&#39;)<br>total_metrics.index.name = &#39;Cuisine&#39;<br><br><br>best_performing_cuisine = total_metrics.sort_values(<br>    by=&#39;Total_Customers&#39;, ascending=False<br>).head(1)<br><br>best_performing_cuisine<br><br><br><br>lowest_earning_cuisine = total_metrics.sort_values(by=&#39;Total_Monthly_Revenue&#39;, ascending=True).head(1)<br>lowest_earning_cuisine</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/443/1*e0-FZQwGEfmUuC2QKAUS9g.png" /><figcaption>4. Best-performing cuisine</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/436/1*yf96r9Z4ylUeXYSUqo1WpQ.png" /><figcaption>5. Least-performing cuisine</figcaption></figure><p><strong><em>Promotion Effectiveness</em></strong></p><pre>## 6. Impact of Promotion on Revenue (Dishes)<br><br>promoted_dishes = rest_data.loc[rest_data[&#39;Promotions&#39;]== 1]<br><br>non_promoted_dishes = rest_data.loc[rest_data[&#39;Promotions&#39;] == 0]<br><br><br>promoted_avg = promoted_dishes.groupby(&#39;Promotions&#39;).agg(<br>    avg_monthly_revenue=(&#39;Monthly_Revenue&#39;, &#39;mean&#39;),<br>    avg_customer_spend=(&#39;Average_Customer_Spending&#39;, &#39;mean&#39;)<br>    ).sort_values(by=&#39;avg_monthly_revenue&#39;, ascending=False)<br><br>non_promoted_avg = non_promoted_dishes.groupby(&#39;Promotions&#39;).agg(<br>    avg_monthly_revenue=(&#39;Monthly_Revenue&#39;, &#39;mean&#39;),<br>    avg_customer_spend=(&#39;Average_Customer_Spending&#39;, &#39;mean&#39;)<br>    ).sort_values(by=&#39;avg_monthly_revenue&#39;, ascending=False)<br><br><br>print(&quot;Dishes with Promos:&quot;, promoted_dishes.shape[0])<br>print(&quot;Dishes without Promos:&quot;, non_promoted_dishes.shape[0])<br>print(&quot;\nAverage Monthly Revenue (Promoted):\n&quot;, promoted_avg)<br>print(&quot;\nAverage Monthly Revenue (Non-Promoted):\n&quot;, non_promoted_avg)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/378/1*Xo1tfoCmyRPxvsNyXg7eEA.png" /></figure><p><strong>Insights: </strong>Promoted dishes generated a slightly higher monthly revenue (<em>$270.5</em> vs. <em>$267.8</em>) with a difference of about <em>$3.33</em>. The almost identical average customer spend for promoted and non-promoted dishes indicates that promos had minimal impact on individual spending behavior. The increased revenue may be more attributable to volume effects rather than increased spending per customer.</p><pre>## 7. Impact of Promotion on Revenue (Cuisine-level)<br><br>promoted_cuisine_cols = [<br>    &#39;Number_of_Customers&#39;,<br>    &#39;Marketing_Spend&#39;,<br>    &#39;Average_Customer_Spending&#39;,<br>    &#39;Promotions&#39;,<br>    &#39;Monthly_Revenue&#39;<br>]<br><br># Define the list of cuisine indicator columns<br>cuisine_indicator_cols = [&#39;Cuisine_Italian&#39;, &#39;Cuisine_Japanese&#39;, &#39;Cuisine_Mexican&#39;]<br><br># List to store aggregated data for cuisine/promotion combinations<br>combined_cuisine_performance = []<br><br># Loop through each cuisine type and extract and clean the name<br>for cuisine_col in cuisine_indicator_cols:<br>    cuisine_name = cuisine_col.replace(&#39;Cuisine_&#39;, &#39;&#39;)<br><br>    # Loop through and each promotion status<br>    for promo_status in [0, 1]:<br>        filtered_data = rest_data[<br>            (rest_data[cuisine_col] == 1) &amp;<br>            (rest_data[&#39;Promotions&#39;] == promo_status)<br>        ]<br><br>        # Calculate the sums for the specific cuisines and promotion status<br>        total_customers = filtered_data[&#39;Number_of_Customers&#39;].sum()<br>        total_monthly_revenue = filtered_data[&#39;Monthly_Revenue&#39;].sum()<br>        num_dishes = len(filtered_data) #  This returns the number of dishes (promoted vs. non-promoted)<br><br>        # Append the results to the combined cuisines list<br>        combined_cuisine_performance.append({<br>            &#39;Cuisine&#39;: cuisine_name,<br>            &#39;Number_of_Dishes&#39;: num_dishes,<br>            &#39;Promotions&#39;: promo_status,<br>            &#39;Total_Customers&#39;: total_customers,<br>            &#39;Total_Monthly_Revenue&#39;: total_monthly_revenue<br>        })<br><br># Convert to a data frame<br>cuisine_performance = pd.DataFrame(combined_cuisine_performance)<br><br># Optional: Sort for better readability (e.g., by Cuisine and then Promotions)<br>cuisine_performance = cuisine_performance.sort_values(<br>    by=[&#39;Total_Customers&#39;, &#39;Total_Monthly_Revenue&#39;], ascending=[False, False]<br>)<br><br>cuisine_performance</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/672/1*ilsbxx7lWVcPi41O6_uutA.png" /><figcaption>Cuisine-level impact of promotion on revenue</figcaption></figure><p><strong>Insights</strong></p><p>a. Japanese cuisine had the most number of dishes that had promos (140) and despite this attracted (7,355 paying customers) and raked in a total monthly revenue of <em>$37,192.06</em>. Promoted dishes (122) raked in <em>$33,993.38</em> from 6,786 customers. Japanese dishes attracted more revenue and customers, even without promotions, indicating a high organic demand.</p><p>b. Mexican dishes with promos (129) outperformed Mexican dishes which didn’t have promos (121) in customers (marginally), as dishes with promos attracted 6,984 customers and raked in <em>$34,784.71</em> compared to non-promoted dishes which attracted 6,221 customers and <em>$32,585.72</em> in revenue. The effect of promotion on Mexican dishes is marginal.</p><p>c. Italian dishes with promos (116) attracted 6,529 paying customers and generated <em>$31,348.27</em> in total monthly revenue. Non-promoted Italian dishes (116) attracted 5,714 customers with total monthly revenue of <em>$29,857.70</em>.</p><p>The effects of promotions are more effective in the Italian and Mexican cuisines. Promotions could be used to drive revenue from Italian and Mexican dishes.</p><p><strong><em>Marketing Spend vs. Revenue</em></strong></p><pre># 8. Impact of Marketing Spend on Monthly Revenue <br>cuisine_columns = [&#39;Cuisine_Italian&#39;, &#39;Cuisine_Japanese&#39;, &#39;Cuisine_Mexican&#39;]<br><br>indicators = rest_data[cuisine_columns]<br>market_spend_metrics = pd.DataFrame({<br>    &#39;total_market_spend&#39;: indicators.T.dot(rest_data[&#39;Marketing_Spend&#39;]),<br>    &#39;total_monthly_revenue&#39;: indicators.T.dot(rest_data[&#39;Monthly_Revenue&#39;])<br>})<br><br><br>market_spend_metrics.index = market_spend_metrics.index.str.replace(&#39;Cuisine_&#39;, &#39;&#39;, regex=False)<br>market_spend_metrics.index.name = &#39;Cuisine&#39;<br><br>marketing_spend_performance = market_spend_metrics.sort_values(by=[&#39;total_market_spend&#39;, &#39;total_monthly_revenue&#39;], ascending=[False, False])<br><br>marketing_spend_performance</pre><p><strong>Insights: </strong>Marketing spend influences revenue, but has diminishing results at the micro level.</p><p>We observe a significant increase in monthly revenue where marketing spend is the highest; a difference of $9,979 between Japanese and Italian cuisine, with a marketing spend difference of $361.59. However, a marginal difference between the marketing spend for Italian cuisine and Mexican cuisine ($1.29) did not yield a higher revenue for Italian cuisine.</p><p>This suggests that although higher marketing spend can drive sales, small increases in marketing spend are not sufficient. Other operational or product factors most likely play a role at the microeconomic level.</p><p><strong>Key Takeaways</strong></p><p>The insights from the analyses indicate that:</p><ul><li>Japanese cuisine is the top performer across the board, and these results were achieved with minimal use of promotions, suggesting organic demand.</li><li>Japanese cuisine had the highest marketing spend ($2,700), suggesting that although the dishes did not have promotions attached to sales, investments were made in marketing Japanese dishes.</li><li>Promotions had a marginal impact on revenue, especially for Italian and Mexican dishes; the numbers suggest the superiority of marketing expenditure in driving sales.</li><li>Marketing spend is more effective at the macro-level (increased expenditure boosts revenue), but shows diminishing returns at the micro-level.</li></ul><p>Recommendations:</p><ul><li>Focus marketing budget on high-performing cuisines like Japanese dishes.</li><li>Use promotions to boost revenue from Italian and Mexican dishes.</li><li>Review menu pricing for high-performing dishes to maximize average customer spend. Consider adding complimentary items like drinks at a slight discount to encourage increased customer spend.</li></ul><p>In Part 2, we’ll train Linear, RidgeCV, LassoCV, Random Forest, Decision Tree, Gradient Boost, and XGBoost machine learning models to predict monthly revenue. <a href="https://colab.research.google.com/drive/1JbaN4AOfd4_c4sqDDGg-ofx4EPvbmP4H#scrollTo=Rehj3PqZIW-Y">The full notebook code is available here.</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e24d555cc103" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Marketing Insight Report]]></title>
            <link>https://medium.com/@sharonobi360/marketing-insight-report-36d647cd8822?source=rss-14e79255a32c------2</link>
            <guid isPermaLink="false">https://medium.com/p/36d647cd8822</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[microsoft-excel]]></category>
            <category><![CDATA[hng12]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[internships]]></category>
            <dc:creator><![CDATA[Sharon Obi]]></dc:creator>
            <pubDate>Thu, 30 Jan 2025 16:08:15 GMT</pubDate>
            <atom:updated>2025-03-26T17:04:58.994Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4qVVIl0tuvli2_rgDBxEaA.jpeg" /><figcaption>Image by <a href="https://pixabay.com/users/jarmoluk-143740/?utm_source=link-attribution&amp;utm_medium=referral&amp;utm_campaign=image&amp;utm_content=906720">Michal Jarmoluk</a> from <a href="https://pixabay.com//?utm_source=link-attribution&amp;utm_medium=referral&amp;utm_campaign=image&amp;utm_content=906720">Pixabay</a></figcaption></figure><p>Data Analysis using Excel</p><p><strong>Introduction</strong></p><p>The dataset being analyzed is a record of sales at a Superstore obtained from <a href="https://www.kaggle.com/datasets/bravehart101/sample-supermarket-dataset">Kaggle </a>with these columns; Ship Mode, Consumer segment, Country, City, State, Category, Sub-category, Sales, Quantity, Discount, and Profit.</p><p>Key variables for this analysis: Consumer segment, State, Category, Sub-category, Sales, Quantity, and Profit.</p><p>The business questions answered through the analysis are:</p><p>a. What are the top-selling products/categories?</p><p>b. What are the top customer segments?</p><p>c. Which states recorded the most revenue and profits?</p><p>Excel was used for the analysis; an initial perusal of the data showed that it was clean, having no null values, duplicates, or errors that would hinder the analysis.</p><p><strong>Observations</strong></p><p>a. Technology Category recorded the highest sales ($836,154.03) and a profit of $145,454.95, whilst recording the fewest units sold (234).</p><p>b. The top-selling product by quantity in the Technology category is Phones accounting for a volume of 3,289 units with $330,007.05 in revenue and $44,515.73 in profit.</p><p>c. The most profitable product in the category, however, is Copiers with a Profit of $55,617.82 from $149,528.03 in Revenue.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4bEboaBIVqyQtwibTm2c6g.png" /><figcaption>Pivot table and clustered column chart showing top Category &amp; Product.</figcaption></figure><p>d. The highest loss was recorded in Table sales (-$17,725.48); losses were recorded in the sale of Supplies (-$1,189) and Bookcases (-$3,472.56).</p><p>e. The top customer segment was the Consumer segment with a volume of 19,521 items across all product categories, revenue of $1,161,401.34, and profit of $134,119.21.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1012/1*eSD2tAFQxQntRwgay2jl6Q.png" /><figcaption>Top Customer Segment</figcaption></figure><p>f. California, New York, Washington, Michigan, and Virginia are the top 5 states by sales volume, revenue, and profit. California performed the best having recorded the most sales volume (7,667 items) pulling in a revenue of $457,687.63 and a profit of $76,381.39.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ARSBb8dR-BPOwlWfwtaSqA.png" /><figcaption>Pivot table and chart showing top-performing States</figcaption></figure><p>h. The worst-performing states were Texas, Pennsylvania, Ohio, and Illinois. Texas performed the worst with a total loss of $25,729.36 from the sale of 3,724 items and a revenue of $170,188.05.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*eCu91KiBg0c5Zd-xqZh0Og.png" /><figcaption>Pivot table filtered to return States that made a loss</figcaption></figure><p><strong>Conclusion</strong></p><p>While the data indicates that Copiers are the most profitable items across the states and Tables, the least performing product, additional data on the cost of goods sold and marketing spend is required to investigate the reasons behind the losses and identify the most appropriate products and states to target with increased marketing efforts.</p><p>Download the Excel File <a href="https://docs.google.com/spreadsheets/d/13Rz37XeZ4Vs7RbsXur0b1QVXTkLsJjxW/edit?usp=sharing&amp;ouid=111922294505604550627&amp;rtpof=true&amp;sd=true"><strong>here</strong></a></p><p>This is one of the tasks in the ongoing HNG 12 Internship program. To learn more about the HNG Internship program, visit any of these sites:</p><p><a href="https://hng.tech/internship">https://hng.tech/internship</a></p><p><a href="https://hng.tech/hire/data-analysts">https://hng.tech/hire/data-analysts</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=36d647cd8822" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Paris Olympics 2024 Analysis]]></title>
            <link>https://medium.com/@sharonobi360/paris-olympics-2024-cee4ac4254fb?source=rss-14e79255a32c------2</link>
            <guid isPermaLink="false">https://medium.com/p/cee4ac4254fb</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[olympics-2024-in-paris]]></category>
            <category><![CDATA[exploratory-data-analysis]]></category>
            <dc:creator><![CDATA[Sharon Obi]]></dc:creator>
            <pubDate>Thu, 09 Jan 2025 22:19:15 GMT</pubDate>
            <atom:updated>2025-03-26T17:03:46.727Z</atom:updated>
            <content:encoded><![CDATA[<p>Data Analysis using Python</p><p>This EDA project analyzes data from the Paris Olympics 2024 (athletes &amp; medalists) to uncover key performers in the last Olympics.</p><p>This analysis helps companies structure marketing campaigns, identify top performers and popular sports, and explore sponsorship deals with these athletes in preparation for the next Olympic games.</p><p><strong>Methodology</strong></p><p>Python was utilized to pre-process the data, and carry out the analysis to identify trends in sports and medals.</p><p><strong>Business questions</strong></p><ul><li>Who are the top Athletes?</li><li>What sports had the highest number of events?</li><li>Which of the sporting events had the most medals?</li></ul><p><strong>NorthStar Metrics</strong></p><ol><li>Total No. of Athletes.</li><li>Total Number of Medals. Total Gold, Silver, and Bronze medals.</li><li>Top 5 countries by medals. The top 5 countries by Gold medals won.</li><li>Top Sports (Sports with the most participants).</li></ol><p><strong>Insights</strong></p><ol><li>A total of 11,115 athletes participated in the Paris Olympics.</li><li>2,315 medals were awarded representing a margin of 20.83% against the total number of participants. Of the total medals, 752 were gold, 756 were silver and 807 were bronze medals.</li><li>The athlete with the most gold medals was Marchand Leon with 4 gold medals. 11 other athletes won 3 Gold medals. The athlete however, with the most number of medals was Zhang Yufei from China with 6 medals from athletic events (5 bronze medals and 1 silver medal; mostly in Athletic events).</li><li>Medals won by Country; United States had the highest number of medals (330 medals) and the top 5 countries with the most medals were, the United States (330), France (186), China (168), Great Britain (161), Australia (123). 91 countries won medals in total.</li><li>The United States led the pack with the most Gold medals 134 medals, China came to a not-so-close second with 71 gold medals, Netherlands (66), France (53), and Great Britain (40).</li><li>Sports with the most participants (Athletics (2,023), Swimming (836), Football (553), Rowing (493), and Hockey (415)).</li></ol><p><strong>Importing and Loading the Data</strong></p><p>The data tables were obtained from <a href="https://www.kaggle.com/datasets/muhammadehsan02/olympic-summer-games-paris-2024">Kaggle</a> and loaded into Jupyter Notebook. First, the proper libraries were imported to enable numeric analysis and calculations (Numpy), structured table data (Pandas), and preliminary visualizations for the regression analysis (Matplotlib and Seaborn).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/943/1*hMH0AlC_sQeHP9qLi3jVkg.png" /><figcaption>Image by Author: Importing and loading the data.</figcaption></figure><p><strong>Observing the Schema</strong></p><p>Using the <em>df.info() </em>method summarizes how the Schema is structured; this includes details such as memory usage, the number of columns, the details of each column in the dataset including, data type, and the number of non-null values.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/937/1*Ejyw8PEGCpMihiNSeue4Zw.png" /><figcaption>Image by Author: Summary of dataframe</figcaption></figure><p><strong>Total No. of Athletes</strong></p><p>The <em>df.unique()</em> method was used to extract the number of unique values in the identifier for each athlete (<em>code </em>column).</p><p>11,115 Athletes participated in the Paris 2024 games.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/946/1*EM1uTOjCzsTdHnU55gknlQ.png" /><figcaption>Image by Author</figcaption></figure><p><strong>Total Number of Medals</strong> <strong>Won</strong></p><p>I loaded the data for the medals won at the Paris Olympics from <a href="https://www.kaggle.com/datasets/muhammadehsan02/olympic-summer-games-paris-2024">Kaggle</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wZBzUADUgVeVoDuXAjE4Tg.png" /></figure><p><strong>Total Gold, Silver, and Bronze medals</strong></p><p>The relevant column for this analysis is the medal_type column, so we shall be focusing on that column to confirm the total number of medals won for each category (Gold, Silver, and Bronze).</p><p><strong>Gold</strong></p><p>The medal_type column was filtered to return only columns where Gold medal was won. The result shows that 752 gold medals were won during the Paris Olympics.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/941/1*B_4FQV4zRv-_FNNnRd96dg.png" /><figcaption>Image by Author</figcaption></figure><p><strong>Silver</strong></p><p>The same filtering technique was used for the Silver and Bronze Medals and the data revealed that 756 Silver medals were won and 807 Bronze Medals.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/945/1*4kpko8_JKwgDZ3KX-5sH8g.png" /><figcaption>Image by Author: Silver Medals</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/930/1*0uFmpuYYLzJ6zLOeSJqA5Q.png" /><figcaption>Image by Author: Bronze Medals</figcaption></figure><p><strong>Countries with the most medals.</strong></p><p>I grouped the medals dataset by the medal_type column, counted the rows for each country, and sorted the list in descending order.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/940/1*X_uiOMmgB0xiEYiDuYSaQg.png" /></figure><p><strong>The top 5 countries by Gold medals won</strong></p><p>United States had the highest number of medals (330 Medals); the other countries with the most medals were, France (186), China (168), Great Britain (161), and Australia (123).</p><p>Countries with the most gold medals: United States (134), China (7), Netherlands, France &amp; Great Britain.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/966/1*Kx8Nf7J_9vyLIYa8rq688w.png" /><figcaption>Image by Author: Top Countries</figcaption></figure><p><strong>Sports with the most no. of participants.</strong></p><p>The following are the sports with the most Athletics with 2,023 participants, Swimming with 83 participants, Football with 553 participants, Rowing with 493 participants, and Hockey with 415 participants.</p><p><strong>Top Sports (Sports with the most participants)</strong></p><p>I first renamed the discipline column to the sports column for ease of reference.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/967/1*5RrD62sHF5NFglPP4DYZ7g.png" /><figcaption>Image by Author: Top Sports</figcaption></figure><p><strong>Top Athletes</strong></p><p>The Athlete with the most medals was Zhang Yufei with 6 medals (1 Silver and 5 bronze medals) in swimming.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/967/1*sLAmkb8EM_OzaQ1Lj7YRAA.png" /></figure><p><strong>Top Athletes with the most Gold medals</strong></p><p>I grouped the Athletes who won Gold Medals by name and the number of gold medals they won in descending order. The gold medalists were also filtered to return only those who won more than 1 gold medal.</p><p>Marchand Leon who participated in Swimming events for France had the most gold medals (4) followed by Simone Biles, Carrington Lisa, Oka Shinnosuke, McIntosh Summer, Lim Sihyeon, Lavreysen Harrie, Thomas Gabrielle, Kim Woojin, O’Callaghan Mollie and Huske Torri (3 Medals).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/980/1*p5l95w6m8kMwSEkj66GHEQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/990/1*bcYVyt0nmGVtMfChy0OOfg.png" /></figure><p><strong>Insights</strong></p><ol><li>A total of 11,115 athletes participated in the Paris Olympics.</li><li>2,315 medals were awarded representing a margin of 20.83% against the total number of participants. Of the total medals, 752 were gold, 756 were silver and 807 were bronze medals.</li><li>The athlete with the most gold medals was Marchand Leon with 4 gold medals. 11 other athletes won 3 Gold medals. The athlete however, with the most number of medals was Zhang Yufei from China with 6 medals from athletic events (5 bronze medals and 1 silver medal; mostly in Athletic events).</li><li>Medals won by Country; United States had the highest number of medals (330 medals) and the top 5 countries with the most medals were, the United States (330), France (186), China (168), Great Britain (161), Australia (123). 91 countries won medals in total.</li><li>The United States led the pack with the most Gold medals 134 medals, China came to a not-so-close second with 71 gold medals, Netherlands (66), France (53), and Great Britain (40).</li><li>Sports with the most participants (Athletics (2,023), Swimming (836), Football (553), Rowing (493), and Hockey (415)).</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cee4ac4254fb" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Flight Status Analysis]]></title>
            <link>https://medium.com/@sharonobi360/flight-status-analysis-0949e5e1d609?source=rss-14e79255a32c------2</link>
            <guid isPermaLink="false">https://medium.com/p/0949e5e1d609</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[business-analysis]]></category>
            <category><![CDATA[airlines]]></category>
            <dc:creator><![CDATA[Sharon Obi]]></dc:creator>
            <pubDate>Sat, 14 Dec 2024 23:29:48 GMT</pubDate>
            <atom:updated>2025-03-26T17:02:52.868Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*UelNKWULByhN-0_mGb72Tw.png" /><figcaption>Image by Author: Title Page</figcaption></figure><p>Data Analysis using Power BI</p><p><strong>Introduction</strong></p><p>This analysis focuses on flight data from the Federal Aviation Administration for 2015 to extract the airlines with the most delayed and canceled flights and understand the reasons for these delays/cancellations against the backdrop of the total flights embarked on during the period.</p><p><strong>Insights</strong></p><ol><li>Of the 1.95 million flights in 2015, 58% (1.13 million) were on time, 40.5% were delayed and 1.5% were canceled.</li><li>The top reason for cancellation was the weather (57.3% of reports tell us this).</li><li>The second reason is the Airline carriers with about 8,000 flights cancelled for this reason in 2015.</li><li>Southwest Airlines Co. had the most recorded delays with about 142 thousand delays within the year.</li><li>American Eagle Airlines canceled the most number of flights; 5,731 flights in total.</li></ol><p><strong>Status Dashboard</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9gSPAXMagI1RHVVIqaugJg.png" /><figcaption>Image by Author: Status Dashboard</figcaption></figure><p><strong>NorthStar Metrics</strong></p><p><strong>a. Total Flights On Time:</strong> the benchmark for the total on-time flights.</p><p><strong>b. Total Flights Delayed:</strong> this is the benchmark for total delayed flights.</p><p><strong>c. Total Flights Cancelled:</strong> this is the benchmark for the total number of canceled flights.</p><p><strong>d. Top Reasons for Cancellations: </strong>the top 3 reasons for cancellations to guide stakeholders in crafting strategies for mitigating or reducing the frequency of these cancellations.</p><p><strong>e. Airlines with the most cancellations:</strong> This takes the post-analysis process a step further as it streamlines the fact-finding and cancellation mitigation process.</p><p><strong>f. Airports with the most delays:</strong> It is important to pinpoint the airports where the most flight delays were recorded so that further inquiries can be made to determine the reasons for the delay, as the data did not contain reasons for these delays.</p><p><strong>Data Preparation</strong></p><p>The data was loaded from the CSV files in the airline flight delays folder obtained from kaggle.com and the headers were promoted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/774/1*MS2LdpneNLFHq5lyQKbz_g.png" /><figcaption>Image by Author</figcaption></figure><p>The tables loaded are; flights (fact table containing details of all flights within the year in view), airlines (dimension table describing the code of each airline), airports (dimension table describing the airports in which the flights departed and landed), and cancellation codes (dimension table explaining the reasons for cancellation).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/700/1*iH0hkLDO3FyXdd_Zt-8_jg.png" /><figcaption>Image by Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*AjnjB4guteSgPWdMfdz2rA.png" /><figcaption>Image by Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/730/1*y25zqVrhLN9EEw2wQGS7EA.png" /><figcaption>Image by Author</figcaption></figure><p><strong>Data Cleaning</strong></p><p>The data looked clean after checking for errors in distinct values in the key columns such as; Year, Month, Day of Week, Airline, Origin Airport, Departure delay, Cancelled, and Cancelation Reason.</p><p>Also, there were no null values in columns that weren&#39;t supposed to have null values (Year, Month, Day_of_week, Airline, and Origin_airport). The other columns could afford to have null values as they were indicators of key metrics (e.g. where a record in the departure_delay column is null, it means there was no delay for such a flight).</p><p>Removal of columns that were unnecessary for the analysis:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/440/1*xi2ikKoo3KiT-ml--XicbQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/440/1*gqw-26_XSjuOj2O2SBpHtw.png" /><figcaption>Image by Author: before and after removal of unnecessary columns</figcaption></figure><p>Creation of a calculated column to indicate the status of each flight (On time, Delayed, and canceled).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PJCWMQSlkMEgP115l0jf_w.png" /><figcaption>Image by Author</figcaption></figure><p><strong>Data Modelling</strong></p><p>Created <strong>one-to-many</strong> relationships between the dimension tables and fact table to achieve a star schema; this optimizes performance by ensuring an efficient model with reduced load times considering the size of the datasets.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/735/1*HcDe8d5TFUG2KO05HST_qg.png" /><figcaption>Image by Author</figcaption></figure><p><strong>DAX Measures</strong></p><p>a. Total Flights</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/442/1*vyL9ZsrojfpI9u_XDw4IWA.png" /><figcaption>Image by Author</figcaption></figure><p>b. Total On-time flights</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/445/1*FJi3oitQ6G_hB2wrbS7PUQ.png" /><figcaption>Image by Author</figcaption></figure><p>c. Total Delayed Flights</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/437/1*CN3zQWsWmwt_q2-QkNmK2w.png" /><figcaption>Image by Author</figcaption></figure><p>d. Total Canceled Flights</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/443/1*El3HMuhzPMj_8C3lT-DuIQ.png" /><figcaption>Image by Author</figcaption></figure><p>e. % On-time:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/439/1*f4VoWWf_RUxRJU-V7jJjSQ.png" /><figcaption>Image by Author</figcaption></figure><p>f. % of Flights Delayed:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/442/1*j1L6HlkzvFP1Yt13qwQDNw.png" /><figcaption>Image by Author</figcaption></figure><p>g. % Canceled</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/440/1*SW4pyYncVrPlX8PodZxWxg.png" /><figcaption>Image by Author</figcaption></figure><p>h. Delay margin</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3Zlv3wMSAzqUYx25fJy7TA.png" /><figcaption>Image by Author</figcaption></figure><p>I. Cancellation Margin</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*c4iH9Yi3hzi2Z21ctZe-TA.png" /><figcaption>Image by Author</figcaption></figure><p><strong>Dashboard Design</strong></p><p><a href="https://app.powerbi.com/groups/me/reports/2e05a296-b2f6-4c68-a2a7-7cc542d1fa1f/569ed0c727108576ddd9?experience=power-bi&amp;bookmarkGuid=cb477e79e08d56840608">Power BI</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9gSPAXMagI1RHVVIqaugJg.png" /><figcaption>Image by Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*wn7CYir-YJzUcHECI_mqQw.png" /><figcaption>Image by Author</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0849wNOW0tSpK-uXyMxXAA.png" /><figcaption>Image by Author</figcaption></figure><p><strong>Insights</strong></p><ol><li>58% (1.13 million flights) were on time, 40.5% (750,486 flights) were delayed and 1.5% (28,570 flights) were cancelled.</li><li>The top reason for the cancellation was the weather — 57.3% of reports tell us this (16,372 flights), closely followed by Airline carriers with about 8,084 flights canceled in 2015.</li><li>Southwest Airlines Co. had the most recorded delays with about 142,120 delays within the year.</li><li>American Eagle Airlines canceled the most number of flights; 5,731 flights in total.</li><li>The highest number of cancellations was recorded in February 2015 (6,432 flights).</li><li>The highest number of delays was recorded in June 2015; with 88,432 flights in total.</li><li>Hartsfield-Jackson International Airport recorded the most number of flight delays (129,782 flights delayed).</li></ol><p><strong>Recommendations</strong></p><ol><li>More research is needed to determine why Airlines canceled last year and initiate strategies for mitigating these delays.</li><li>Probe into the affairs of Southwest Airlines Co. and American Eagle Airlines alone.</li><li>Further research into the reasons for delays; the dataset does not capture that information.</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0949e5e1d609" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Customer Churn Analysis]]></title>
            <link>https://medium.com/@sharonobi360/customer-churn-analysis-480db3c3ac1c?source=rss-14e79255a32c------2</link>
            <guid isPermaLink="false">https://medium.com/p/480db3c3ac1c</guid>
            <category><![CDATA[power-bi]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[business-analytics]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Sharon Obi]]></dc:creator>
            <pubDate>Fri, 29 Nov 2024 00:08:14 GMT</pubDate>
            <atom:updated>2025-03-26T17:01:08.206Z</atom:updated>
            <content:encoded><![CDATA[<p>Data Analysis using Power BI</p><p><strong>Introduction</strong></p><p>This project was part of the instructions executed during my internship with PwC Switzerland via the Forage platform. It features data from a telecom client through the Retention Manager with the intent to minimize customer churn and boost customer retention.</p><p><strong>Instruction</strong></p><p>Define appropriate KPIs to reflect Customer demographics and Key Insights into major factors driving customer churn.</p><p><strong>Insights</strong></p><p>From the analysis which shall be described in detail hereunder, we discovered that the major factors leading to customer churn were:</p><ol><li><strong>Short-term contracts (month-to-month). </strong>Customers with short-term contracts are more likely to leave than those that have mid to long-term contracts.</li><li><strong>High complaints regarding technical issues, and unavailability of tech support.</strong> Customers who had 2 and above unresolved complaints regarding technical issues were also very likely to leave.</li><li><strong>Unavailability of online security services.</strong> Customers who did not have access to online security services were also more likely to churn than others who did.</li><li><strong>Fibre Optic Service.</strong> Of all the internet services provided by the company, a staggering 69% (1,290 subscribers) of those that churned were subscribed to the Fibre Optic Internet Service.</li><li>The client had lost over $139,0000 as a result of the churn with a churn rate of 26.54% within the period under review.</li></ol><p><strong>Dashboard</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MiPT3nn3TK2tsZ4OarySCA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t2zQmn6sYQYbOsoBsX40xw.png" /><figcaption>Image by Author: Churn Analysis Dashboard view (Demographics and Overview)</figcaption></figure><p><strong>NorthStar Metrics</strong></p><p>Key Performance Indicators relevant to the business requirements are:</p><ol><li><em>Customer Demographics:</em> Total No. of Customers, Gender of Customers, % of Subscribers that are Elderly, Subscribers with Dependants, and Subscribers with Partners.</li><li><em>Churn Rate:</em> Total No. Churned vs. Total customers, % Churn</li><li><em>Account Info: </em>Total amount charged, Total monthly charge, Total number of tickets</li></ol><p><strong>Dimensions</strong></p><ol><li><em>Years as Customer:</em> this dimension is important to fully understand the correlation if any between the years/time spent as a customer (at a more granular level) and the likelihood of churning.</li><li><em>Customer Status:</em> this slicer enables the report consumer to view customer demographics and their churn rate by high-level time categories (short-term, mid-term, and long-term).</li></ol><p><strong>Data Preparation</strong></p><p>The dataset features a fact table containing 7,043 rows; considering that the requirements did not require the creation of another table as that would have resulted in a many-to-many relationship.</p><p><strong>DAX Measures</strong></p><ol><li>Churn Rate %</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/413/1*mAFqWLP8sSxgcxm2THZcAA.png" /><figcaption>Image by Author: Churn Rate %</figcaption></figure><p>2. Churns last month</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/399/1*NIEDZWf0FCzs866eXYy_JA.png" /><figcaption>IMage by Author: Churns last month</figcaption></figure><p>3. Females that churned</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/403/1*0Wr8UNdxQiYyrgZNY4eYeg.png" /><figcaption>Image by Author: females that churned</figcaption></figure><p>4. Males that churned</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/438/1*mpwGUtLSgKXU8WN5QEHeUg.png" /><figcaption>Image by Author: Males that churned</figcaption></figure><p>5. Total Churn</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/404/1*35TeqF2Asha_XegEDLBk0A.png" /><figcaption>Image by Author: Total Customers that churned</figcaption></figure><p>6. Total Customers</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/412/1*EgeuaCOenhkkcPt_-jAdeg.png" /><figcaption>Image by Author: Total No. of Subscribers</figcaption></figure><p>7. Total Male and Female Customers</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/438/1*O4xXu5au0KF6_pWuTHHFzw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/439/1*oJQ07n0ryqaiP60cJovPnA.png" /><figcaption>Image by Author: Total male and female subscribers.</figcaption></figure><p>8. Senior Citizens: The column had just 0s and 1s as values and we would eventually need to have Yes and No. Created a calculated column for this purpose.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/400/1*3aCfW3MkCkX7yM8J7qyOSg.png" /></figure><p>9. Total Revenue</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/447/1*QJp8fwGhY0f5S_qa0FABnw.png" /></figure><p>10. Total Loss from churn</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/396/1*sEvFQULP_XeYu1gIFwtYqg.png" /></figure><p>11. Total Monthly Charge</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/416/1*zcJRy7DgG1Mmht4s0GcQyg.png" /></figure><p><strong>Risk Analysis</strong></p><p>Before utilizing the AI Key Influencer feature, it was important to observe account information as it relates to services available to staff and the correlation with their churning or not.</p><p>What this Dashboard shows is that customers with the following metrics are at a high risk of churning:</p><p>a. Fiber Optic Internet Service subscription</p><p>b. No Technical Support</p><p>c. Month-to-Month Contract with the 1-year subscription contract or less</p><p>d. Electronic cheque as payment method</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kAuH6muYAd93HgaRSkpUdg.png" /><figcaption>Image by Author: Customer Churn Risk Analysis</figcaption></figure><p><strong>Dashboard Cross-section</strong></p><p><a href="https://app.powerbi.com/links/ZzD7a-SZK4?ctid=63b8c9cd-edf4-475f-9ef3-aa69bb5579d6&amp;pbi_source=linkShare">Power BI</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*MiPT3nn3TK2tsZ4OarySCA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*t2zQmn6sYQYbOsoBsX40xw.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RFbo5xdStbGOkHaeYC_QOA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*78EzqYYp46_DcY0kwfId6g.png" /></figure><p><strong>RECOMMENDATIONS:</strong></p><ol><li>Marketing to push for long-term contracts in campaigns.</li><li>Customer care to review technical issues (Tickets) and revisit support for these kinds of issues. It will be crucial to find out why these issues were unresolved and deploy fixes for these issues to avert future recurrence.</li><li>Audit the Fibre optic internet service to determine the drawbacks or issues with the service.</li><li>Meeting with Devs to review coverage of Online Security Services.</li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=480db3c3ac1c" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>