<?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 Mathew kariuki on Medium]]></title>
        <description><![CDATA[Stories by Mathew kariuki on Medium]]></description>
        <link>https://medium.com/@makmwa124?source=rss-191c72e011d3------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*Fg_P7-qIDca2jSGg.jpg</url>
            <title>Stories by Mathew kariuki on Medium</title>
            <link>https://medium.com/@makmwa124?source=rss-191c72e011d3------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 00:49:16 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@makmwa124/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[Customer Lifetime Value in Python: Predict It, Then Check It Against Real Spend]]></title>
            <link>https://medium.com/@makmwa124/customer-lifetime-value-in-python-predict-it-then-check-it-against-real-spend-128abbe54506?source=rss-191c72e011d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/128abbe54506</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[customer-lifetime-value]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <category><![CDATA[pandas]]></category>
            <dc:creator><![CDATA[Mathew kariuki]]></dc:creator>
            <pubDate>Tue, 17 Nov 2026 08:51:01 GMT</pubDate>
            <atom:updated>2026-11-17T08:51:01.783Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, I’m Mathew. “How much is each customer worth?” is one of the most common questions a marketing or finance team will ask an analyst. Most CLV tutorials stop once they’ve produced a number. I want to go one step further: predict value from one period, then check that prediction against what customers actually spent next. That second step is what tells you whether the number deserves any trust.</p><p>The data is the cleaned UK online gift retailer dataset (online_retail_clean.csv), running from 1 December 2010 to 9 December 2011. We only need CustomerID, InvoiceNo, InvoiceDate, Revenue and Country.</p><h3>Split time before you build anything</h3><p>A model that predicts the future has to be tested on a future it hasn’t seen. Otherwise you’re only measuring how well it remembers the past. So the first step is a split in time: a calibration period to build features, and a holdout period to test them.</p><pre>import pandas as pd<br>import numpy as np</pre><pre>clean = pd.read_csv(&#39;online_retail_clean.csv&#39;, parse_dates=[&#39;InvoiceDate&#39;])<br>clean[&#39;CustomerID&#39;] = clean[&#39;CustomerID&#39;].astype(int)</pre><pre>cutoff = pd.Timestamp(&#39;2011-09-01&#39;)<br>calib = clean[clean[&#39;InvoiceDate&#39;] &lt; cutoff]<br>holdout = clean[clean[&#39;InvoiceDate&#39;] &gt;= cutoff]<br>calib.shape[0], holdout.shape[0]   # (223127, 168023)</pre><p>The holdout covers only about three months, yet it holds 168,023 rows. That’s because autumn is this retailer’s busy pre-Christmas season. Keep that in mind: seasonality can make the holdout look different from the calibration period for reasons that have nothing to do with your model.</p><h3>One row per customer</h3><p>CLV is built per customer, so the transactions get rolled up with a named aggregation. Crucially, this uses calibration rows only. If holdout data leaked in here, the validation later would be meaningless.</p><pre>cust = calib.groupby(&#39;CustomerID&#39;).agg(<br>    TotalRevenue=(&#39;Revenue&#39;, &#39;sum&#39;),<br>    Orders=(&#39;InvoiceNo&#39;, &#39;nunique&#39;),<br>    FirstPurchase=(&#39;InvoiceDate&#39;, &#39;min&#39;),<br>    LastPurchase=(&#39;InvoiceDate&#39;, &#39;max&#39;),<br>)<br>cust.shape[0]   # 3314</pre><h3>The simple CLV formula</h3><p>The model is deliberately simple, which makes it easy to explain and easy to check:</p><p><strong>CLV = average order value × purchases per month × assumed lifespan in months</strong></p><pre>cust[&#39;AvgOrderValue&#39;] = cust[&#39;TotalRevenue&#39;] / cust[&#39;Orders&#39;]</pre><pre>calib_period_days = (cutoff - calib[&#39;InvoiceDate&#39;].min()).days<br>cust[&#39;PurchaseFreqPerMonth&#39;] = cust[&#39;Orders&#39;] / (calib_period_days / 30)</pre><pre>lifespan_months = 12<br>cust[&#39;PredictedCLV&#39;] = (cust[&#39;AvgOrderValue&#39;]<br>                        * cust[&#39;PurchaseFreqPerMonth&#39;]<br>                        * lifespan_months)<br>cust[&#39;PredictedCLV&#39;].describe().round(2)</pre><pre>count      3314.00<br>mean       2048.21<br>50%         715.82<br>max      231801.68</pre><p>The median customer is worth about 716 GBP over 12 months, but the mean is about 2,048. When the mean sits nearly three times above the median, value is concentrated in a small group of customers. That’s a very common retail pattern. Summed across everyone, the total comes to about 6,787,762 GBP.</p><p>The top of the list shows two very different routes to a high CLV:</p><pre>AvgOrderValue  PurchaseFreqPerMonth  PredictedCLV<br>CustomerID<br>14646             4394.57                  4.40     231801.68<br>18102             5020.66                  2.86     172137.01<br>12346            77183.60                  0.11     101780.57<br>14911              666.33                 11.32      90503.30</pre><p>Customer 12346 is there because of one enormous order. Customer 14911 places small orders very often. A single huge order is a much shakier basis for a forecast than a steady habit, even if the formula treats them the same.</p><h3>The step most tutorials skip: validation</h3><p>Now attach what each customer really spent in the holdout, and scale the prediction to the holdout’s length. Comparing a 12-month prediction with three months of real spend would make any model look wildly over-optimistic.</p><pre>actual = holdout.groupby(&#39;CustomerID&#39;)[&#39;Revenue&#39;].sum()<br>cust[&#39;ActualHoldoutRevenue&#39;] = cust.index.map(actual).fillna(0)<br>(cust[&#39;ActualHoldoutRevenue&#39;] &gt; 0).mean().round(3)   # 0.588</pre><pre>holdout_months = (clean[&#39;InvoiceDate&#39;].max() - cutoff).days / 30   # 3.3<br>cust[&#39;PredictedHoldoutRevenue&#39;] = (cust[&#39;AvgOrderValue&#39;]<br>                                   * cust[&#39;PurchaseFreqPerMonth&#39;]<br>                                   * holdout_months)</pre><pre>correlation = cust[[&#39;PredictedHoldoutRevenue&#39;,<br>                    &#39;ActualHoldoutRevenue&#39;]].corr().iloc[0, 1]   # 0.68<br>mae = (cust[&#39;PredictedHoldoutRevenue&#39;]<br>       - cust[&#39;ActualHoldoutRevenue&#39;]).abs().mean()             # 624.8</pre><h3>How to read the result</h3><ul><li><strong>About 59% of customers came back</strong> in the holdout. The other 41% spent nothing, yet every one of them got a positive prediction.</li><li><strong>Correlation of 0.68</strong> is fairly strong: customers predicted to spend more generally did. But correlation only checks ranking and direction. A model that’s always too high can still score well.</li><li><strong>MAE of 624.80 GBP</strong> per customer is large next to a median order of about 279 GBP. MAE is pulled up by a handful of very big accounts, so a median absolute error is a useful companion.</li></ul><p>Then the baseline check, which I’d run for any model. Does order size alone predict future spend?</p><pre>naive = cust[[&#39;AvgOrderValue&#39;, &#39;ActualHoldoutRevenue&#39;]].corr().iloc[0, 1]<br>round(naive, 3), round(correlation, 3)   # (0.091, 0.68)</pre><p>Order value on its own tells you almost nothing (0.091). Adding frequency is what makes the model useful. If your model can’t beat a one-column baseline, it isn’t worth the extra complexity.</p><h3>Turning CLV into action</h3><p>The most useful list a CLV model produces is often the valuable customers who’ve gone quiet:</p><pre>high = cust[cust[&#39;PredictedCLV&#39;] &gt; cust[&#39;PredictedCLV&#39;].quantile(0.9)]<br>churn_risk = high[high[&#39;ActualHoldoutRevenue&#39;] == 0]<br>churn_risk.shape[0], high.shape[0]   # (33, 332)<br>churn_risk[&#39;PredictedHoldoutRevenue&#39;].sum()   # about 107,562 GBP</pre><p>33 of the 332 top-decile customers spent nothing in the holdout. They’re strong candidates for a win-back call. Present the 107,562 GBP carefully, though: it’s what the model expected, not money that was definitely lost, and three quiet months isn’t proof someone has churned.</p><p>Recency backs this up. Splitting customers into recency quartiles, the most recent group averages about 4,645 GBP of predicted CLV and the least recent about 692. Recency isn’t in the formula, so a customer who stopped buying months ago can still be overvalued.</p><h3>Watch out</h3><ol><li><strong>The lifespan is an assumption, and it drives the total.</strong> Total CLV is about 3.39 million GBP at 6 months, 6.79 million at 12 and 13.58 million at 24, because lifespan is a plain multiplier. Always state it when you quote a CLV figure.</li><li><strong>Frequency is understated for newer customers.</strong> Every customer is divided by the full calibration period, even if they first bought last month. Using each customer’s own first purchase date is a worthwhile improvement.</li><li><strong>One-time buyers get optimistic values.</strong> They average about 531 GBP against about 3,129 GBP for repeat buyers, because the formula assumes they keep buying once every nine months or so.</li><li><strong>Small groups mislead.</strong> Average CLV by country is led by EIRE at about 69,253 GBP, but that likely rests on very few customers. Check the count before you report it.</li></ol><h3>Get the full notebook, dataset and quiz</h3><p>The lesson on my site has the full runnable notebook, the dataset, and a quiz so you can check you’ve really got it.</p><ul><li>Full lesson: <a href="https://www.mathewkanalytics.com/lessons/real-world-data-analytics/007-customer-lifetime-value-modeling/?utm_source=medium">Customer Lifetime Value Modeling</a></li><li>Watch the video walkthrough: <a href="https://www.youtube.com/watch?v=FPA9Jwz7YTA">https://www.youtube.com/watch?v=FPA9Jwz7YTA</a></li><li>Subscribe for the next lesson in the series: <a href="https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1">https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1</a></li></ul><p>Everything is free. Try moving the cutoff to July 2011 and see whether the correlation holds up.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=128abbe54506" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Sentiment Analysis in Python with VADER: Score, Classify and Test Real Posts]]></title>
            <link>https://medium.com/@makmwa124/sentiment-analysis-in-python-with-vader-score-classify-and-test-real-posts-0dc02a35b585?source=rss-191c72e011d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/0dc02a35b585</guid>
            <category><![CDATA[nlp]]></category>
            <category><![CDATA[sentiment-analysis]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Mathew kariuki]]></dc:creator>
            <pubDate>Fri, 13 Nov 2026 08:31:01 GMT</pubDate>
            <atom:updated>2026-11-13T08:31:01.492Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, I’m Mathew. A marketing team looks at a feed of social posts and asks two simple questions: how positive or negative is this conversation, and do the posts moderators flagged sound more negative than the rest? You can guess by scrolling, but a guess is not consistent across hundreds of posts. Here’s how to measure it properly with VADER, and how to be honest about what the numbers can and can’t tell you.</p><p>The data is 944 scraped social posts in social_posts_raw.csv, with an id, a label (1 for a post moderators flagged, 0 for a normal one) and the post text in tweet. It&#39;s one modest sample, so treat the findings as describing these posts, not social media in general.</p><h3>1. Score one post, then every post</h3><p>VADER is a lexicon-based tool. A lexicon is a dictionary of words with hand-assigned positive or negative ratings, so there’s no model to train. You hand it text and it hands back scores.</p><p>One thing that trips people up: the package you install is vaderSentiment, but the class you import is SentimentIntensityAnalyzer.</p><pre>import pandas as pd<br>from vaderSentiment.vaderSentiment import SentimentIntensityAnalyzer<br> <br>posts = pd.read_csv(&quot;social_posts_raw.csv&quot;)<br>analyzer = SentimentIntensityAnalyzer()<br> <br>analyzer.polarity_scores(posts[&quot;tweet&quot;].iloc[3])</pre><pre>{&#39;neg&#39;: 0.0, &#39;neu&#39;: 0.719, &#39;pos&#39;: 0.281, &#39;compound&#39;: 0.7249}</pre><p>Always test on a single post first so you know what comes back. neg, neu and pos are the shares of the text that read as negative, neutral and positive. compound is the one you&#39;ll use most: a single overall score from -1 (very negative) to +1 (very positive). This post, which says &quot;i love u take with u all the time&quot;, scores 0.7249, clearly positive.</p><p>Now score every post, keeping only the compound value:</p><pre>posts[&quot;compound&quot;] = posts[&quot;tweet&quot;].apply(<br>    lambda t: analyzer.polarity_scores(t)[&quot;compound&quot;]<br>)<br>posts[&quot;compound&quot;].describe().round(3)</pre><pre>count    944.000<br>mean       0.214<br>std        0.489<br>min       -0.931<br>25%        0.000<br>50%        0.224<br>75%        0.637<br>max        0.959</pre><p>The average is 0.214 and the median 0.224, so the conversation leans positive. Look at the 25th percentile, though: exactly 0. That tells you a big chunk of posts contain no words VADER recognises at all. The standard deviation of 0.489 is large for a scale that only runs from -1 to 1, so this is a spread-out conversation, not a uniformly mild one.</p><h3>2. Turn scores into Positive, Neutral and Negative</h3><p>A score is great for analysis, but stakeholders usually want categories. pd.cut does the binning. The cut-offs of -0.05 and 0.05 are the thresholds commonly recommended for VADER, and the outer edges sit just beyond -1 and 1 so no score falls outside.</p><pre>bins = [-1.01, -0.05, 0.05, 1.01]<br>labels = [&quot;Negative&quot;, &quot;Neutral&quot;, &quot;Positive&quot;]<br>posts[&quot;sentiment_class&quot;] = pd.cut(posts[&quot;compound&quot;], bins=bins, labels=labels)<br>posts[&quot;sentiment_class&quot;].value_counts()</pre><pre>Positive    506<br>Neutral     233<br>Negative    205</pre><p>Just over half the posts are positive. A handy headline metric is the positive-to-negative ratio:</p><pre>n_pos = (posts[&quot;sentiment_class&quot;] == &quot;Positive&quot;).sum()<br>n_neg = (posts[&quot;sentiment_class&quot;] == &quot;Negative&quot;).sum()<br>round(n_pos / n_neg, 2)   # 2.47</pre><p>About two and a half positive posts for every negative one. Ratios like this are easy to compare week to week, but they ignore neutral posts and how strong each post is. Counting extremes helps there: 126 posts score above 0.8 and only 21 score below -0.8, so intensely negative posts are rare in this sample.</p><h3>3. Read the extremes before you trust the scores</h3><p>This is the step people skip, and it’s the most important one. If the top and bottom posts don’t make sense to a human, nothing built on the scores will either.</p><pre>posts.nlargest(5, &quot;compound&quot;)[[&quot;tweet&quot;, &quot;compound&quot;]]<br>posts.nsmallest(5, &quot;compound&quot;)[[&quot;tweet&quot;, &quot;compound&quot;]]</pre><pre>tweet                                               compound<br>655  shocking events in orlando - now will the usa ...   -0.9314<br>568  i got the call yesterday.  mom was diagnosed w...   -0.9246<br>...<br>253  watch fancy tails&#39;s vine &quot;mad #mad   #teeth #b...   -0.8910</pre><p>The most positive posts (around 0.95 to 0.96) are about a happy baby, love and Father’s Day. The most negative include news about Orlando and a medical diagnosis, so VADER is picking up genuine distress. But one entry is a video titled “mad”, which shows the weakness of word-based scoring: a word can carry negative weight even when nobody is angry.</p><p>You can peek inside the lexicon to see why some posts dominate:</p><pre>lex = analyzer.lexicon<br>lex[&quot;love&quot;], lex[&quot;hate&quot;], lex[&quot;good&quot;]</pre><pre>(3.2, -2.7, 1.9)</pre><p>“love” is weighted much more heavily than “good”, which is why posts full of “love” crowd the top of the list.</p><p>Neutral posts deserve a look too, because that’s where mis-scored content hides:</p><pre>neutral = posts[posts[&quot;sentiment_class&quot;] == &quot;Neutral&quot;]<br>neutral[&quot;tweet&quot;].head(5).tolist()</pre><p>The first one is “bihday your majesty”, where the misspelling of “birthday” hides the positive word. Another, a sad post about Orlando tagged #heabreaking, also lands as Neutral for the same reason. A lexicon only knows correctly spelled words.</p><h3>4. Does sentiment line up with the moderation label?</h3><p>Now the useful part: comparing sentiment with something independent of it.</p><pre>from scipy import stats<br> <br>posts.groupby(&quot;label&quot;)[&quot;compound&quot;].mean().round(3)<br># 0    0.24<br># 1   -0.11<br> <br>flagged = posts.loc[posts[&quot;label&quot;] == 1, &quot;compound&quot;]<br>normal = posts.loc[posts[&quot;label&quot;] == 0, &quot;compound&quot;]<br>t_stat, p_value = stats.ttest_ind(flagged, normal, equal_var=False)<br>round(t_stat, 3), round(p_value, 5)</pre><pre>(-6.129, 0.0)</pre><p>Normal posts average 0.24 and flagged posts average -0.11. equal_var=False runs Welch&#39;s t-test, which doesn&#39;t assume both groups have the same spread. That&#39;s the right call here because the groups are very different sizes. The t-statistic is -6.129 and the p-value rounds to 0.0. It isn&#39;t literally zero, it&#39;s just smaller than five decimal places can show, so when you report it, write p &lt; 0.001.</p><p>An average can hide a lot, so count classes inside the flagged group:</p><pre>posts.loc[posts[&quot;label&quot;] == 1, &quot;sentiment_class&quot;].value_counts()</pre><pre>Negative    30<br>Positive    24<br>Neutral     17</pre><p>Negative is the largest group, but most of the 71 flagged posts are not negative at all. Flagged content can be sarcastic or upbeat in tone. Hold onto that thought for section 6.</p><h3>5. Which topics and post styles feel positive?</h3><p>Hashtags group posts by topic, so averaging sentiment per hashtag shows which topics feel good or bad. explode gives each hashtag its own row, and I keep only hashtags used at least 5 times, because an average over one or two posts is unreliable.</p><pre>posts[&quot;hashtags&quot;] = posts[&quot;tweet&quot;].str.findall(r&quot;#\w+&quot;)<br>exploded = posts.explode(&quot;hashtags&quot;).dropna(subset=[&quot;hashtags&quot;])<br>tag_sent = exploded.groupby(&quot;hashtags&quot;)[&quot;compound&quot;].mean()<br>tag_counts = posts.explode(&quot;hashtags&quot;)[&quot;hashtags&quot;].value_counts()<br>common = tag_counts[tag_counts &gt;= 5].index<br> <br>tag_sent.loc[common].sort_values().head(10)</pre><p>At the top end, #thankful scores 0.882, followed by #beautiful and #quote. At the bottom, #orlando is -0.344 and #christinagrimmie -0.337, both tied to tragic news, then #trump at -0.139. Notice #gold, #blog and #silver all share exactly 0.07136. That&#39;s a sign they come from the same batch of near-identical posts, so treat them as one source, not three topics.</p><p>Length and word count tell a quieter story:</p><pre>posts[&quot;word_count&quot;] = posts[&quot;tweet&quot;].str.split().str.len()<br>word_bins = pd.cut(posts[&quot;word_count&quot;], bins=[0, 5, 10, 15, 100])<br>posts.groupby(word_bins, observed=True)[&quot;compound&quot;].mean().round(3)</pre><pre>(0, 5]       0.167<br>(5, 10]      0.326<br>(10, 15]     0.285<br>(15, 100]    0.033</pre><p>Character length has a weak correlation of -0.14 with sentiment, and hashtag count a weak 0.191. The buckets show something a single correlation misses: the pattern isn’t a straight line. Posts of 6 to 10 words are the most positive, while posts over 15 words average just 0.033, the most mixed or neutral group.</p><h3>6. Can sentiment alone flag problem posts?</h3><p>Flagged posts are more negative on average, so it’s tempting to build a rule: flag anything with compound below -0.3.</p><pre>predicted = (posts[&quot;compound&quot;] &lt; -0.3).astype(int)<br>accuracy = (predicted == posts[&quot;label&quot;]).mean()<br> <br>tp = ((predicted == 1) &amp; (posts[&quot;label&quot;] == 1)).sum()<br>fp = ((predicted == 1) &amp; (posts[&quot;label&quot;] == 0)).sum()<br>fn = ((predicted == 0) &amp; (posts[&quot;label&quot;] == 1)).sum()<br>precision = tp / (tp + fp)<br>recall = tp / (tp + fn)</pre><p>Accuracy comes out at 81.2%, which sounds decent. It isn’t. Only 71 of 944 posts are flagged, so a rule that never flags anything would be right far more often than that. This rule is worse than doing nothing.</p><p>Precision and recall show why. Precision is 0.165, so only about one in six posts the rule flags really was flagged. Recall is 0.366, so it catches roughly a third of the truly flagged posts. Sentiment and moderation are related, and the t-test shows it, but they’re different signals. A statistically significant gap in averages does not make a good classifier.</p><p>Putting it all together, here’s how to read each number:</p><ul><li><strong>Compound mean (0.214):</strong> the headline tone. Mildly positive here.</li><li><strong>Class counts and ratio:</strong> what stakeholders understand. Report these next to the mean, because a healthy mean can hide a vocal negative minority.</li><li><strong>Group comparison plus t-test:</strong> whether sentiment tracks something real. Here it does.</li><li><strong>Precision and recall:</strong> whether sentiment is good enough to act on alone. Here it isn’t.</li></ul><h3>Common mistakes</h3><ol><li><strong>Trusting scores without reading examples.</strong> Always look at the top, bottom and neutral posts. That’s how you spot the “mad” video and the mangled “bihday”.</li><li><strong>Judging a rule by accuracy on imbalanced data.</strong> With few flagged posts, accuracy flatters any rule. Check precision and recall.</li><li><strong>Assuming cleaning always matters.</strong> I removed every @user tag and re-scored, and the average change was 0.0, because VADER has no entry for those tags. Test a cleaning step before you claim it helps or hurts.</li><li><strong>Forgetting what Jupyter displays.</strong> Only the last line of a cell is shown. If you run two sanity checks in one cell, wrap them in a tuple or print() so you see both.</li></ol><h3>Get the full notebook, dataset and quiz</h3><p>The lesson on my site has the full runnable notebook, the dataset, and a quiz so you can check you’ve really got it.</p><ul><li>Full lesson: <a href="https://www.mathewkanalytics.com/lessons/real-world-data-analytics/022-sentiment-analysis-on-real-text-data/?utm_source=medium">Sentiment Analysis on Real Text Data</a></li><li>Watch the video walkthrough: <a href="https://www.youtube.com/watch?v=vi3_IrMI6f0">https://www.youtube.com/watch?v=vi3_IrMI6f0</a></li><li>Subscribe for the next lesson in the series: <a href="https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1">https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1</a></li></ul><p>Everything is free. Try the -0.5 threshold on the flagging rule and see how precision and recall move.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0dc02a35b585" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Sales Forecasting in Python: Exponential Smoothing vs Naive and Moving Averages]]></title>
            <link>https://medium.com/@makmwa124/sales-forecasting-in-python-exponential-smoothing-vs-naive-and-moving-averages-8fff5804e923?source=rss-191c72e011d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/8fff5804e923</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[time-series-analysis]]></category>
            <category><![CDATA[forecasting]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Mathew kariuki]]></dc:creator>
            <pubDate>Tue, 10 Nov 2026 08:31:01 GMT</pubDate>
            <atom:updated>2026-11-10T08:31:01.695Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, I’m Mathew. A retailer wants to know how much revenue to expect over the coming weeks, and, just as important, how far that number can be trusted. Anyone can draw a line into the future. The useful part is testing that line on weeks the model never saw and checking whether it beats the simplest possible guesses.</p><p>The data is the cleaned UK online gift retailer dataset from earlier in the series (online_retail_clean.csv), using InvoiceDate and Revenue, covering December 2010 to December 2011. That is roughly one year of history, which matters later: one year is not enough for a model to learn a Christmas pattern.</p><h3>From transactions to a complete weekly series</h3><p>Forecasting needs one value per period. First collapse the 391,150 cleaned transactions into daily revenue:</p><pre>import pandas as pd<br>import numpy as np<br> <br>clean = pd.read_csv(&quot;online_retail_clean.csv&quot;, parse_dates=[&quot;InvoiceDate&quot;])<br>clean[&quot;DateOnly&quot;] = clean[&quot;InvoiceDate&quot;].dt.date<br>daily = clean.groupby(&quot;DateOnly&quot;)[&quot;Revenue&quot;].sum()<br>daily.index = pd.to_datetime(daily.index)<br>daily.shape[0]   # 305</pre><p>Only 305 days have sales, but the data spans more than a year. Days with no orders are not missing data, they are real days with zero revenue. If you drop them, the timeline gets squeezed and every weekly total is wrong. So build the full calendar and fill the gaps:</p><pre>full_range = pd.date_range(daily.index.min(), daily.index.max(), freq=&quot;D&quot;)<br>daily = daily.reindex(full_range, fill_value=0)<br>daily.shape[0]   # 374<br> <br>daily.groupby(daily.index.dayofweek).mean().round(0)</pre><pre>0    25028.0<br>1    31556.0<br>2    28879.0<br>3    35912.0<br>4    27033.0<br>5        0.0<br>6    14712.0</pre><p>Thursday is the strongest day and Saturday is exactly zero: this business took no orders on any Saturday in the data. A daily model would have to learn that. Weekly totals sidestep it, and they are closer to how a retailer actually plans stock and staff:</p><pre>weekly = daily.resample(&quot;W&quot;).sum()   # 54 weeks, ending Sundays<br>weekly = weekly.iloc[1:-1]           # drop the partial first and last week<br>weekly.head(3)</pre><pre>2010-12-12    210678.74<br>2010-12-19    161598.80<br>2010-12-26     45285.91</pre><p>The data starts on a Wednesday and ends on a Friday, so the first and last weeks are incomplete. Leave them in and the model sees a fake collapse in revenue. Notice the Christmas week drop to 45,285.91: that one is real, and it shows how seasonal this business is.</p><h3>An honest train/test split and two error metrics</h3><p>With time series you split by time, never randomly. A random split lets the model learn from weeks that come after the ones it is predicting, which flatters every result.</p><pre>train = weekly.iloc[:-8]<br>test = weekly.iloc[-8:]<br>len(train), len(test)   # (44, 8)<br> <br>def mae(actual, forecast):<br>    return np.mean(np.abs(np.asarray(actual) - np.asarray(forecast)))<br> <br>def rmse(actual, forecast):<br>    return np.sqrt(np.mean((np.asarray(actual) - np.asarray(forecast)) ** 2))</pre><p>The last eight weeks run from mid-October to early December, the busiest stretch of the year, so this is a tough test.</p><p>MAE (mean absolute error) is the average miss in pounds per week, whichever direction. RMSE squares each miss before averaging, so a few big misses push it up much more. Lower is better for both. np.asarray strips the pandas index so nothing gets silently aligned by date.</p><h3>Two baselines: naive and moving average</h3><p>Before any model, set the bar. The naive forecast says next week will look like the last week you saw. The moving average uses the mean of the last four training weeks as a flat forecast.</p><pre>naive = pd.Series([train.iloc[-1]] * len(test), index=test.index)<br>naive_mae, naive_rmse = mae(test, naive), rmse(test, naive)<br># (47079.0, 51439.0)<br> <br>ma_value = train.iloc[-4:].mean()<br>ma = pd.Series([ma_value] * len(test), index=test.index)<br>ma_mae, ma_rmse = mae(test, ma), rmse(test, ma)<br># (15407.0, 21369.0)</pre><p>The naive forecast misses by about 47,079 a week on average, because the last training week came before the pre-Christmas climb. Averaging four weeks catches the higher recent level and cuts MAE to about 15,407. That is a strong baseline for something this simple.</p><p>The lesson also tries a straight line fitted through all training weeks with np.polyfit. It scores an MAE of about 47,703 and an RMSE of about 51,075, almost as poor as naive, because a line fitted to the whole year is too flat for the autumn surge.</p><h3>Exponential smoothing with statsmodels</h3><p>Flat forecasts cannot follow a trend. Exponential smoothing tracks a level and a trend, with recent weeks weighted more heavily. The notebook calls this model Holt-Winters, but look at the arguments: seasonal=None means there is no seasonal term. What you actually have is Holt&#39;s linear trend method.</p><pre>from statsmodels.tsa.holtwinters import ExponentialSmoothing<br> <br>hw_model = ExponentialSmoothing(train, trend=&quot;add&quot;, seasonal=None).fit()<br>hw_forecast = hw_model.forecast(len(test))<br> <br>hw_mae, hw_rmse = mae(test, hw_forecast), rmse(test, hw_forecast)<br># (16324.0, 18995.0)</pre><p>The forecast rises in a straight line, from about 211,716 for the first test week to 256,118 for the last. That steady climb is exactly what a trend-only model does: it carries the recent slope forward and nothing else.</p><p>There is a good reason to skip the seasonal term here. With about a year of weekly data, the model would see each Christmas only once, which is not enough to estimate a yearly pattern.</p><h3>How to read the result</h3><p>Put every model in one table:</p><pre>comparison = pd.DataFrame({<br>    &quot;Model&quot;: [&quot;Naive&quot;, &quot;Moving Average&quot;, &quot;Holt-Winters&quot;],<br>    &quot;MAE&quot;: [naive_mae, ma_mae, hw_mae],<br>    &quot;RMSE&quot;: [naive_rmse, ma_rmse, hw_rmse],<br>})<br>comparison.round(0)</pre><pre>Model      MAE     RMSE<br>0           Naive  47079.0  51439.0<br>1  Moving Average  15407.0  21369.0<br>2    Holt-Winters  16324.0  18995.0</pre><p>Here is the honest reading. <strong>On MAE, the four-week moving average wins</strong> (15,407 against Holt’s 16,324). On RMSE, Holt wins (18,995 against 21,369), which means its misses are a little bigger on a typical week but it avoids the very large ones. Both comfortably beat naive.</p><p>So which one should you pick? It depends on what a bad week costs. If a single large miss before Christmas means empty shelves, RMSE is the better guide and Holt has a case. If you care about the typical weekly error, the moving average is the better model on this test, and it is simpler.</p><p>The notebook also computes “percent better than naive” from the lowest MAE in the table:</p><pre>best_mae = comparison[&quot;MAE&quot;].min()<br>round((naive_mae - best_mae) / naive_mae * 100, 1)   # 67.3</pre><p>That 67.3% belongs to the moving average, not Holt. The notebook’s final recap line prints “using Holt-Winters, beating the naive baseline by 67.3%”, which ties the figure to the wrong model. Holt’s own improvement over naive is a little smaller. Whenever you report a percentage, name the model it came from.</p><p>The largest single Holt miss was the week ending 23 October 2011: actual revenue 245,694 against a forecast of 218,059, an under-forecast. Under-forecasting is the direction that leads to stock-outs, so it is worth flagging.</p><h3>One test window is not enough: a rolling backtest</h3><p>Eight weeks can be lucky or unlucky. A rolling backtest repeats the honest test from several cut-off points, training on everything before each one and forecasting the next four weeks:</p><pre>errors = []<br>for cutoff in [-20, -16, -12, -8]:<br>    f_train, f_test = weekly.iloc[:cutoff], weekly.iloc[cutoff:cutoff + 4]<br>    f_model = ExponentialSmoothing(f_train, trend=&quot;add&quot;, seasonal=None).fit()<br>    errors.append(mae(f_test, f_model.forecast(len(f_test))))<br> <br>[round(e, 0) for e in errors]<br># [29268.0, 24092.0, 110298.0, 16628.0]<br>round(np.mean(errors), 0), round(np.std(errors), 0)<br># (45071.0, 37926.0)</pre><p>Three folds miss by tens of thousands of pounds a week. The third, which trains up to mid-September and has to forecast the start of the autumn surge, misses by about 110,298. That pulls the average MAE to about 45,071 with a standard deviation of about 37,926. The lesson is clear: this model struggles most exactly when the trend changes suddenly. Note that only Holt was backtested this way, so we cannot say how the moving average would fare across the same folds.</p><h3>Refit and forecast the next six weeks</h3><p>Once a method has been tested, you refit it on all the data so the forecast uses the most recent weeks too:</p><pre>final_model = ExponentialSmoothing(weekly, trend=&quot;add&quot;, seasonal=None).fit()<br>future = final_model.forecast(6)<br>future.round(0)</pre><pre>2011-12-11    270317.0<br>2011-12-18    276555.0<br>2011-12-25    282792.0<br>2012-01-01    289030.0<br>2012-01-08    295268.0<br>2012-01-15    301505.0</pre><p>The six weeks add up to 1,715,468 GBP, and the first forecast week is 9.5% above the latest actual week. Now sense-check it. Revenue keeps climbing through Christmas week and into mid-January. In December 2010 the same business dropped to 45,285.91 in Christmas week. A model without a seasonal term has no idea that happens, so treat this total as likely on the high side.</p><h3>Watch out</h3><ol><li><strong>Trusting the label instead of the arguments.</strong> A model called “Holt-Winters” with seasonal=None is Holt&#39;s linear method. It will not capture Christmas.</li><li><strong>Attaching a headline number to the wrong model.</strong> The 67.3% improvement comes from the moving average’s MAE. Report each figure next to the model that earned it.</li><li><strong>Dropping zero days or keeping partial weeks.</strong> Both distort weekly totals. Use reindex with fill_value=0 (plain fillna(0) does nothing when the dates aren&#39;t in the index), then trim incomplete weeks.</li><li><strong>Reading one test window as the truth.</strong> The single test gave Holt an MAE of about 16,324; the backtest averaged about 45,071. Check residuals too: in training they averaged about 9,411.5, so the model tended to under-predict.</li></ol><h3>Get the full notebook, dataset and quiz</h3><p>The lesson on my site has the full runnable notebook, the dataset, and a quiz so you can check you’ve really got it.</p><ul><li>Full lesson: <a href="https://www.mathewkanalytics.com/lessons/real-world-data-analytics/005-sales-forecasting-for-retail-demand/?utm_source=medium">Sales Forecasting for Retail Demand</a></li><li>Watch the video walkthrough: <a href="https://www.youtube.com/watch?v=4F8B8RAFITM">https://www.youtube.com/watch?v=4F8B8RAFITM</a></li><li>Subscribe for the next lesson in the series: <a href="https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1">https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1</a></li></ul><p>Everything is free. Try changing the moving-average window and see if you can beat my numbers.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8fff5804e923" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Customer Churn Analysis in Python: Find Who Leaves and What It Costs]]></title>
            <link>https://medium.com/@makmwa124/customer-churn-analysis-in-python-find-who-leaves-and-what-it-costs-0cf6c48aba5e?source=rss-191c72e011d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/0cf6c48aba5e</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[customer-churn]]></category>
            <dc:creator><![CDATA[Mathew kariuki]]></dc:creator>
            <pubDate>Fri, 06 Nov 2026 08:26:01 GMT</pubDate>
            <atom:updated>2026-11-06T08:26:01.351Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, I’m Mathew. Every subscription business asks the same two questions: which customers are most likely to leave, and how much money walks out of the door when they do? You don’t need machine learning to start answering them. A handful of pandas groupby calls on real customer records will get you surprisingly far, as long as you read the results honestly.</p><h3>The data and the baseline</h3><p>The dataset is a set of real telecom customer records (the familiar Telco churn data), loaded from telco_churn_raw.csv. The lesson uses a sample of 486 customers and 21 columns. The ones that matter most are tenure (months as a customer), Contract, PaymentMethod, MonthlyCharges, TotalCharges, the internet and add-on service columns, and Churn.</p><pre>import pandas as pd<br> <br>customers = pd.read_csv(&quot;telco_churn_raw.csv&quot;)<br>customers.shape                      # (486, 21)<br> <br>customers[[&quot;tenure&quot;, &quot;Contract&quot;, &quot;MonthlyCharges&quot;, &quot;Churn&quot;]].head(3)</pre><pre>tenure        Contract  MonthlyCharges Churn<br>0       1  Month-to-month           29.85    No<br>1      34        One year           56.95    No<br>2       2  Month-to-month           53.85   Yes</pre><p>Notice that Churn is stored as the words Yes and No, not 1 and 0. So to count churners you compare with &quot;Yes&quot;, which gives a True/False column. The mean of a boolean column is the share of Trues, and that is your churn rate:</p><pre>overall = round((customers[&quot;Churn&quot;] == &quot;Yes&quot;).mean() * 100, 1)<br>overall                              # 25.3</pre><p>About one customer in four has left. This 25.3% is the baseline. A segment is only interesting if its rate sits clearly above or below it.</p><p>A quick caveat before going further: 486 customers is a modest sample. Once you start slicing it, some segments hold only a few dozen people, and their percentages can swing a lot.</p><h3>Churn by contract and payment method</h3><p>Contract type is the classic churn driver, because it reflects commitment. The same Yes-share calculation inside each group does the job:</p><pre>def churn_rate(s):<br>    return round((s == &quot;Yes&quot;).mean() * 100, 1)<br> <br>customers.groupby(&quot;Contract&quot;)[&quot;Churn&quot;].apply(churn_rate).sort_values(ascending=False)<br>customers.groupby(&quot;PaymentMethod&quot;)[&quot;Churn&quot;].apply(churn_rate).sort_values(ascending=False)</pre><pre>Contract<br>Month-to-month    42.2<br>One year           4.1<br>Two year           2.7<br> <br>PaymentMethod<br>Electronic check             41.7<br>Mailed check                 20.5<br>Bank transfer (automatic)    17.9<br>Credit card (automatic)      12.1</pre><p>(The notebook writes the rate as an inline lambda; I&#39;ve pulled it into a small function so it&#39;s easier to reuse.)</p><p>Month-to-month customers churn at 42.2%, against 4.1% and 2.7% on longer contracts. That gap is enormous. Payment method tells a similar story: electronic check customers churn at 41.7%, while the two automatic methods are lowest. One plausible reason is that automatic payment removes a monthly moment where the customer actively decides to keep paying. But that’s a correlation, not proof that switching someone’s payment method would keep them.</p><p>A named aggregation puts size, risk and value side by side, which is what a manager actually needs:</p><pre>customers.groupby(&quot;Contract&quot;).agg(<br>    Count=(&quot;customerID&quot;, &quot;count&quot;),<br>    ChurnRatePct=(&quot;Churn&quot;, churn_rate),<br>    AvgMonthly=(&quot;MonthlyCharges&quot;, &quot;mean&quot;),<br>).round(2)</pre><pre>Count  ChurnRatePct  AvgMonthly<br>Month-to-month    275          42.2       68.49<br>One year           98           4.1       64.64<br>Two year          113           2.7       61.29</pre><p>The riskiest group is also the biggest and the highest-paying. That combination is why month-to-month customers are where retention effort goes first.</p><h3>When do customers leave? Tenure buckets</h3><p>Average tenure already hints at the answer: customers who stayed average 37.0 months, churners just 15.9. Averages hide the shape, though, so bucket tenure with pd.cut:</p><pre>bins = [0, 6, 12, 24, 48, 100]<br>labels = [&quot;0-6mo&quot;, &quot;6-12mo&quot;, &quot;1-2yr&quot;, &quot;2-4yr&quot;, &quot;4yr+&quot;]<br>customers[&quot;TenureBucket&quot;] = pd.cut(customers[&quot;tenure&quot;], bins=bins, labels=labels)<br> <br>customers.groupby(&quot;TenureBucket&quot;, observed=True)[&quot;Churn&quot;].apply(churn_rate)</pre><pre>TenureBucket<br>0-6mo     54.5<br>6-12mo    42.0<br>1-2yr     23.4<br>2-4yr     13.0<br>4yr+       9.8</pre><p>This is a clean staircase. Over half of customers in their first six months have churned, and the rate keeps falling with every bucket. For a business, that turns a vague “we lose customers” into a specific “the first year is the danger zone”, which is something an onboarding team can act on.</p><p>One detail worth knowing: each pd.cut bucket includes its upper edge, so a tenure of exactly 6 lands in 0-6mo.</p><h3>Services: internet type and add-ons</h3><p>Churners also paid more on average ($72.75 a month against $63.77 for those who stayed). Is that about price, or about what they bought? The internet service split helps:</p><pre>customers.groupby(&quot;InternetService&quot;)[&quot;Churn&quot;].apply(churn_rate)<br># Fiber optic 37.4, DSL 19.4, No 7.4<br> <br>addon_cols = [&quot;OnlineSecurity&quot;, &quot;OnlineBackup&quot;, &quot;DeviceProtection&quot;,<br>              &quot;TechSupport&quot;, &quot;StreamingTV&quot;, &quot;StreamingMovies&quot;]<br>customers[&quot;AddonCount&quot;] = (customers[addon_cols] == &quot;Yes&quot;).sum(axis=1)<br>customers.groupby(&quot;AddonCount&quot;)[&quot;Churn&quot;].apply(churn_rate)</pre><pre>AddonCount<br>0    22.7<br>1    43.6<br>2    30.8<br>3    25.3<br>4    21.7<br>5    10.6<br>6     0.0</pre><p>Fibre customers churn the most, and fibre tends to be the pricier service, which links back to the higher bills among churners. Customers with no internet at all churn at only 7.4%.</p><p>The add-on count is a neat engagement measure: comparing six columns with &quot;Yes&quot; gives a True/False table, and .sum(axis=1) counts across each row. Churn peaks at one add-on and falls steadily as customers take more services. The zero group breaks the pattern, and the likely reason is that it includes those phone-only customers who rarely leave. That&#39;s a good reminder to ask &quot;who is actually in this group?&quot; before telling a story about it.</p><h3>Turning churn into money</h3><p>Churn rates become a business conversation once they’re in currency:</p><pre>monthly = customers.loc[customers[&quot;Churn&quot;] == &quot;Yes&quot;, &quot;MonthlyCharges&quot;].sum()<br>round(monthly, 2)                    # 8948.4<br>round(monthly * 12, 2)               # 107380.8</pre><p>The churned customers were paying $8,948.40 a month between them, or $107,380.80 a year if you scale by 12.</p><p>Be careful with the wording here. The notebook calls this “revenue at risk”, but these customers have already left, so it’s really revenue already lost in this dataset. The annual figure also assumes every one of them would have stayed and paid the same for a full year, which is optimistic. Present it as a rough sense of scale, not a forecast. Even so, it shows why a retention programme that saves only a fraction of these customers can pay for itself.</p><h3>A high-risk segment and a two-line prediction rule</h3><p>Combine the two strongest signals:</p><pre>high_risk = customers[(customers[&quot;Contract&quot;] == &quot;Month-to-month&quot;) &amp;<br>                      (customers[&quot;PaymentMethod&quot;] == &quot;Electronic check&quot;)]<br>len(high_risk), churn_rate(high_risk[&quot;Churn&quot;])     # (134, 50.0)<br> <br>low_risk = customers[customers[&quot;Contract&quot;] != &quot;Month-to-month&quot;]<br>churn_rate(low_risk[&quot;Churn&quot;])                      # 3.3</pre><p>134 customers, exactly half of whom churned, roughly double the 25.3% baseline. Everyone on a longer contract churns at 3.3%.</p><p>Now a simple rule as a first “model”: predict churn for month-to-month customers with under 12 months of tenure.</p><pre>customers[&quot;PredictedChurn&quot;] = ((customers[&quot;Contract&quot;] == &quot;Month-to-month&quot;) &amp;<br>                               (customers[&quot;tenure&quot;] &lt; 12)).astype(int)<br>actual = (customers[&quot;Churn&quot;] == &quot;Yes&quot;).astype(int)<br>pred = customers[&quot;PredictedChurn&quot;]<br> <br>accuracy = (pred == actual).mean()                 # 0.772<br>tp = ((pred == 1) &amp; (actual == 1)).sum()<br>fp = ((pred == 1) &amp; (actual == 0)).sum()<br>fn = ((pred == 0) &amp; (actual == 1)).sum()<br>precision = tp / (tp + fp)                         # 0.544<br>recall = tp / (tp + fn)                            # 0.602</pre><h3>How to read the result</h3><p>Accuracy of 77.2% sounds decent until you remember the baseline. With 25.3% churn, a lazy rule that predicts “nobody leaves” is already right about three times in four. So accuracy alone barely tells you anything here.</p><p>Precision and recall are the useful pair. Precision of 0.544 means just over half of the flagged customers really left. Recall of 0.602 means the rule catches about 60% of all churners. For a retention team, that’s a reasonable first call list from two lines of code, and a benchmark any later model has to beat.</p><p>To turn it into action, keep flagged customers who haven’t left yet and sort by monthly charges:</p><pre>at_risk = customers[(customers[&quot;PredictedChurn&quot;] == 1) &amp; (customers[&quot;Churn&quot;] == &quot;No&quot;)]<br>at_risk[[&quot;customerID&quot;, &quot;tenure&quot;, &quot;MonthlyCharges&quot;]].sort_values(<br>    &quot;MonthlyCharges&quot;, ascending=False).head(5)</pre><p>The top five pay between $89.85 and $99.30 a month, all with tenures under a year. Calling the most valuable at-risk customers first is the simplest way to spend a retention budget wisely.</p><h3>Watch out: common mistakes</h3><ol><li><strong>Overstating the segment combination.</strong> The lesson’s wrap-up says the two-factor segment has “dramatically higher churn than either factor alone”. The outputs say 50.0%, against 42.2% for month-to-month and 41.7% for electronic check on their own. That’s higher, but not dramatically. Also, the 50.0% vs 3.3% comparison sets one narrow segment against everyone on a longer contract, so it’s not a like-for-like pair.</li><li><strong>Trusting a sanity check without reading it.</strong> The notebook includes a “segment sizes sum correctly” check that returns False. The data is fine: the check compares the 134 high-risk customers with all 275 month-to-month customers, which can never match. A guard is only useful if it tests the right thing.</li><li><strong>Quoting tiny groups.</strong> The 0.0% churn for six add-ons and the 35.4% senior churn (versus 23.3% for non-seniors) may rest on small groups. Check value_counts() before you put them on a slide.</li><li><strong>Treating correlation as cause.</strong> Paperless billing customers churn at 30.5% against 17.5%, but paperless billing probably travels with month-to-month contracts and electronic checks. Investigate it, don’t act on it alone.</li></ol><h3>Get the full notebook, dataset and quiz</h3><p>The lesson on my site has the full runnable notebook, the dataset, and a quiz so you can check you’ve really got it.</p><ul><li>Full lesson: <a href="https://www.mathewkanalytics.com/lessons/real-world-data-analytics/023-real-customer-churn-and-retention-analysis/?utm_source=medium">Customer Churn and Retention Analysis</a></li><li>Watch the video walkthrough: <a href="https://www.youtube.com/watch?v=jLkN6Z4zBrg">https://www.youtube.com/watch?v=jLkN6Z4zBrg</a></li><li>Subscribe for the next lesson in the series: <a href="https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1">https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1</a></li></ul><p>Everything is free. Try tightening the rule to under 6 months of tenure and see which of precision and recall moves.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=0cf6c48aba5e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[SQL Joins and Aggregations in Python: INNER JOIN, LEFT JOIN, GROUP BY and HAVING]]></title>
            <link>https://medium.com/@makmwa124/sql-joins-and-aggregations-in-python-inner-join-left-join-group-by-and-having-5c227f8ea6ea?source=rss-191c72e011d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/5c227f8ea6ea</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Mathew kariuki]]></dc:creator>
            <pubDate>Tue, 03 Nov 2026 08:26:01 GMT</pubDate>
            <atom:updated>2026-11-03T08:26:01.629Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, I’m Mathew. Real business questions almost never live in one table. “Which product categories bring in the most revenue?” needs order items, products and a category lookup, and “where are our customers?” needs orders and customers. That’s what joins and aggregations are for, and you can practise them in Python with nothing more than sqlite3 and pandas.</p><p>The data is a real, multi-table extract of the Olist Brazilian E-Commerce dataset: separate CSVs for orders, customers, order items, products and a category name translation table. It’s a sample (the items table has 1,365 rows), so treat the numbers below as a way to learn the technique, not as conclusions about Brazilian e-commerce.</p><h3>Load several CSVs into one SQLite database</h3><p>The trick that makes this practical is pandas’ to_sql. You read each CSV into a DataFrame and write it straight into an in-memory SQLite database as its own table. No server, no install, nothing to clean up afterwards.</p><pre>import sqlite3<br>import pandas as pd<br> <br>conn = sqlite3.connect(&#39;:memory:&#39;)<br>pd.read_csv(&#39;orders_sample.csv&#39;).to_sql(&#39;orders&#39;, conn, index=False, if_exists=&#39;replace&#39;)<br>pd.read_csv(&#39;customers_sample.csv&#39;).to_sql(&#39;customers&#39;, conn, index=False, if_exists=&#39;replace&#39;)<br>pd.read_csv(&#39;order_items_sample.csv&#39;).to_sql(&#39;items&#39;, conn, index=False, if_exists=&#39;replace&#39;)<br>pd.read_csv(&#39;products_sample.csv&#39;).to_sql(&#39;products&#39;, conn, index=False, if_exists=&#39;replace&#39;)<br>pd.read_csv(&#39;category_translation.csv&#39;).to_sql(&#39;translation&#39;, conn, index=False, if_exists=&#39;replace&#39;)<br>print(&#39;All five real tables loaded&#39;)</pre><p>Why bother, when pandas has merge? Because a lot of analyst work happens in SQL, against a warehouse you don&#39;t control, and this setup lets you write exactly the same kind of query you&#39;d write at work. index=False stops pandas adding its row index as an extra column, and if_exists=&#39;replace&#39; means you can rerun the cell without errors.</p><p>From here on, every query follows the same pattern: write the SQL as a string, then hand it to pd.read_sql so the result comes back as a DataFrame.</p><h3>INNER JOIN: keep only the rows that match</h3><p>An INNER JOIN combines two tables on a shared key and keeps only the rows where the key exists on both sides. Here, every order carries a customer_id, and the customers table tells us which state that customer is in.</p><pre>query = &#39;&#39;&#39;<br>SELECT o.order_id, o.order_status, c.customer_state<br>FROM orders o<br>INNER JOIN customers c ON o.customer_id = c.customer_id<br>LIMIT 5<br>&#39;&#39;&#39;<br>result = pd.read_sql(query, conn)<br>print(result)</pre><pre>order_id order_status customer_state<br>0  e481f51cbdc54678b7cc49136f2d6af7    delivered             SP<br>1  53cdb2fc8bc7dce0b6741e2150273451    delivered             BA<br>2  47770eb9100c2d0c44946d9cf07ec65d    delivered             GO<br>3  949d5b44dbf5de918fe9c16f97b45f8a    delivered             RN<br>4  ad21c59c0840e6cb83a9ceb5573f8159    delivered             SP</pre><p>Two habits worth copying. First, the short table aliases (o, c) keep queries readable once you have three or four tables. Second, prefix every column with its alias, even when the name is unique. When someone adds a column with the same name to another table six months later, your query won&#39;t suddenly break.</p><h3>LEFT JOIN: keep every row from the first table</h3><p>A LEFT JOIN keeps every row from the left table, whether or not it finds a match on the right. Where there’s no match, the right-hand columns come back as NULL. That’s what you want when the left table is the thing you’re counting, such as order items, and you don’t want any of them quietly disappearing.</p><pre>query = &#39;&#39;&#39;<br>SELECT i.order_id, i.product_id, p.product_category_name, i.price<br>FROM items i<br>LEFT JOIN products p ON i.product_id = p.product_id<br>LIMIT 5<br>&#39;&#39;&#39;<br>print(pd.read_sql(query, conn))</pre><p>The first rows show furniture and decor (moveis_decoracao) at 109.9, perfume (perfumaria) at 179.9 and pet supplies (pet_shop) at 99.9. Notice that one order appears twice with the same product: the items table has one row per item, not one row per order.</p><p>The real value of a LEFT JOIN is that it lets you check how good your keys are. Count the rows where the right side came back empty:</p><pre>query = &#39;&#39;&#39;<br>SELECT<br>    COUNT(*) AS total_items,<br>    SUM(CASE WHEN p.product_id IS NULL THEN 1 ELSE 0 END) AS unmatched_items<br>FROM items i<br>LEFT JOIN products p ON i.product_id = p.product_id<br>&#39;&#39;&#39;<br>print(pd.read_sql(query, conn))</pre><pre>total_items  unmatched_items<br>0         1365                0</pre><p>All 1,365 item rows found their product, so in this sample an INNER JOIN would give the same rows. That’s a good result, but you only know it because you checked. I run this kind of query before trusting any join, because an unmatched key in a real warehouse is usually a data quality problem someone needs to hear about.</p><h3>Join three or more tables in one query</h3><p>You can chain as many JOIN clauses as you need. Each one adds a table and says how it connects to something already in the query. Here we go from orders, to the customer’s state, to the items in each order, to each item’s product category.</p><pre>query = &#39;&#39;&#39;<br>SELECT o.order_id, c.customer_state, p.product_category_name, i.price<br>FROM orders o<br>JOIN customers c ON o.customer_id = c.customer_id<br>JOIN items i ON o.order_id = i.order_id<br>JOIN products p ON i.product_id = p.product_id<br>LIMIT 5<br>&#39;&#39;&#39;<br>print(pd.read_sql(query, conn))</pre><pre>order_id customer_state  product_category_name   price<br>0  e481f51cbdc54678b7cc49136f2d6af7             SP  utilidades_domesticas   29.99<br>1  53cdb2fc8bc7dce0b6741e2150273451             BA             perfumaria  118.70<br>2  47770eb9100c2d0c44946d9cf07ec65d             GO             automotivo  159.90<br>3  949d5b44dbf5de918fe9c16f97b45f8a             RN               pet_shop   45.00<br>4  ad21c59c0840e6cb83a9ceb5573f8159             SP              papelaria   19.90</pre><p>A plain JOIN is the same as INNER JOIN. The helpful way to read a chain like this is as a path through the data model: orders link to customers by customer_id, orders link to items by order_id, and items link to products by product_id. If you can draw that path on paper, you can write the query.</p><h3>GROUP BY across a join</h3><p>Joins become really useful once you aggregate the result. This query answers the revenue question from the start: it joins items to products, translates the Portuguese category names into English, then groups by category.</p><pre>query = &#39;&#39;&#39;<br>SELECT t.product_category_name_english AS category,<br>       COUNT(*) AS num_items,<br>       ROUND(SUM(i.price), 2) AS total_revenue<br>FROM items i<br>JOIN products p ON i.product_id = p.product_id<br>JOIN translation t ON p.product_category_name = t.product_category_name<br>GROUP BY t.product_category_name_english<br>ORDER BY total_revenue DESC<br>LIMIT 5<br>&#39;&#39;&#39;<br>print(pd.read_sql(query, conn))</pre><pre>category  num_items  total_revenue<br>0   health_beauty        139       17953.39<br>1  sports_leisure        131       15970.22<br>2   watches_gifts         65       13785.66<br>3  bed_bath_table        125       11810.30<br>4      cool_stuff         49        9339.41</pre><p>Look at the two columns together, not just the ranking. Health and beauty leads on revenue, but bed, bath and table sold almost as many items (125) for noticeably less money. Watches and gifts reached third place with only 65 items, which tells you those items cost more on average. Revenue is item count times price, and both halves matter.</p><p>Also note that total_revenue here is the sum of item prices. It doesn&#39;t include freight or payment data, so it&#39;s product revenue, not what customers paid in total.</p><h3>HAVING: filter on the aggregated result</h3><p>WHERE filters rows before grouping. HAVING filters groups after the aggregation has run, which is why it can use COUNT(*) and WHERE can&#39;t.</p><pre>query = &#39;&#39;&#39;<br>SELECT customer_state, COUNT(*) AS num_orders<br>FROM customers<br>GROUP BY customer_state<br>HAVING COUNT(*) &gt;= 20<br>ORDER BY num_orders DESC<br>&#39;&#39;&#39;<br>print(pd.read_sql(query, conn))<br>conn.close()</pre><pre>customer_state  num_orders<br>0              SP         512<br>1              RJ         134<br>2              MG         120<br>3              RS          73<br>4              PR          72<br>5              BA          54<br>6              SC          36<br>7              GO          33<br>8              DF          27<br>9              ES          24<br>10             PE          23</pre><p>The query counts rows in the customers table. In Olist each order gets its own customer_id, which is why the notebook labels the count num_orders. São Paulo (SP) dominates with 512, nearly four times Rio de Janeiro (RJ) at 134.</p><p>The &gt;= 20 threshold is the analyst bit. States with only a handful of orders produce unstable averages and rates, so cutting them out before you compare states keeps you from drawing conclusions from tiny groups. Close the connection when you&#39;re done, since an in-memory database disappears with it.</p><h3>Watch out</h3><ol><li><strong>Join fan-out.</strong> When one order has several items, joining orders to items repeats the order once per item. If you then COUNT(*), you&#39;re counting items, not orders. Use COUNT(DISTINCT o.order_id) when you mean orders.</li><li><strong>INNER JOIN silently drops rows.</strong> In the GROUP BY query, any product whose category has no entry in the translation table would vanish from the totals without an error. Run a LEFT JOIN with a CASE WHEN ... IS NULL check first, as we did for products.</li><li><strong>Using WHERE for an aggregate.</strong> WHERE COUNT(*) &gt;= 20 is an error. Conditions on individual rows go in WHERE, conditions on groups go in HAVING.</li><li><strong>Grouping by one name and selecting another.</strong> Every non-aggregated column in your SELECT should appear in the GROUP BY. SQLite is lenient here and will return a value anyway, but other databases will refuse, and the value SQLite picks may not be the one you expect.</li></ol><h3>Which join when?</h3><ul><li>You only care about rows that exist in both tables: INNER JOIN.</li><li>The left table is what you’re counting and nothing should go missing: LEFT JOIN.</li><li>You want to check key quality: LEFT JOIN plus a count of NULLs on the right side.</li><li>You need a summary per group: GROUP BY with COUNT, SUM or AVG.</li><li>You need to keep only some of those groups: HAVING.</li></ul><h3>Get the full notebook, dataset and quiz</h3><p>The lesson on my site has the full runnable notebook, the dataset, and a quiz so you can check you’ve really got it.</p><ul><li>Full lesson: <a href="https://www.mathewkanalytics.com/lessons/data-analytics-zero-to-hero/017-sql-joins-and-aggregations/?utm_source=medium">SQL Joins and Aggregations</a></li><li>Watch the video walkthrough: <a href="https://www.youtube.com/watch?v=gFh6dQ8PnLw">https://www.youtube.com/watch?v=gFh6dQ8PnLw</a></li><li>Subscribe for the next lesson in the series: <a href="https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1">https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1</a></li></ul><p>Everything is free. Load the tables, change one query at a time, and see what you find.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5c227f8ea6ea" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Market Basket Analysis in Python: Support, Confidence and Lift from Scratch]]></title>
            <link>https://medium.com/@makmwa124/market-basket-analysis-in-python-support-confidence-and-lift-from-scratch-c5035ffedbf7?source=rss-191c72e011d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/c5035ffedbf7</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[retail]]></category>
            <dc:creator><![CDATA[Mathew kariuki]]></dc:creator>
            <pubDate>Fri, 30 Oct 2026 08:21:01 GMT</pubDate>
            <atom:updated>2026-10-30T08:21:01.771Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, I’m Mathew. A retailer asks a simple question: which products should we bundle, place side by side, or recommend at checkout? Market basket analysis answers it by looking at what ends up on the same invoice. I’ll build support, confidence and lift by hand in Python, with no black-box library, so you know exactly what each number means.</p><p>The data is the cleaned UK online gift retailer dataset (online_retail_clean.csv, 391,150 rows). We only need three columns: InvoiceNo (one invoice is one basket), Description (the product) and Country.</p><h3>The three measures in plain English</h3><p>Before any code, here’s what we’re calculating:</p><ul><li><strong>Support</strong>: how often an item, or a pair of items, appears across all invoices.</li><li><strong>Confidence</strong>: of the baskets that contain A, what share also contain B?</li><li><strong>Lift</strong>: how much more often A and B appear together than you’d expect by chance. Above 1 means they go together; exactly 1 means no relationship.</li></ul><p>All three come from two kinds of count: how many baskets contain each item, and how many contain each pair. Python’s standard library gives us both tools.</p><pre>import pandas as pd<br>from itertools import combinations<br>from collections import Counter<br> <br>clean = pd.read_csv(&quot;online_retail_clean.csv&quot;, parse_dates=[&quot;InvoiceDate&quot;])<br>clean.shape   # (391150, 9)</pre><p>combinations generates every pair from a set of items, and Counter is a dictionary that counts things for you.</p><h3>Build the baskets</h3><p>Buying patterns differ by country, so I restrict to the UK. That leaves 16,579 distinct invoices, and that number becomes the denominator for every support figure.</p><p>Counting pairs across thousands of products creates a huge, noisy table, so I focus on the 40 products that appear on the most invoices. Then each basket becomes a set of product names, which means a product listed twice on one invoice only counts once.</p><pre>uk = clean[clean[&quot;Country&quot;] == &quot;United Kingdom&quot;]<br>n_invoices = uk[&quot;InvoiceNo&quot;].nunique()   # 16579<br> <br>top_products = (uk.groupby(&quot;Description&quot;)[&quot;InvoiceNo&quot;]<br>                  .nunique().sort_values(ascending=False).head(40))<br>top_set = set(top_products.index)<br> <br>in_top = uk[uk[&quot;Description&quot;].isin(top_set)]<br>baskets = in_top.groupby(&quot;InvoiceNo&quot;)[&quot;Description&quot;].apply(set)<br>len(baskets)   # 11365<br> <br>baskets = baskets[baskets.apply(len) &gt;= 2]<br>len(baskets)   # 7716</pre><p>11,365 invoices contain at least one top-40 product, and 7,716 of those contain two or more. A one-item basket can’t tell you anything about what’s bought together, so it goes. One subtlety: a basket with lots of products can still drop out here if only one of them is in the top 40. On average the remaining baskets hold 4.15 top-40 products.</p><h3>Count items and pairs</h3><p>Now the heart of the method. One loop counts items, another counts pairs.</p><pre>item_counts = Counter()<br>for basket in baskets:<br>    for item in basket:<br>        item_counts[item] += 1<br>support = {item: cnt / n_invoices for item, cnt in item_counts.items()}<br> <br>pair_counts = Counter()<br>for basket in baskets:<br>    for a, b in combinations(sorted(basket), 2):<br>        pair_counts[(a, b)] += 1<br>pair_support = {pair: cnt / n_invoices for pair, cnt in pair_counts.items()}<br> <br>len(pair_counts)   # 780</pre><p>The sorted(basket) matters. Without it, (A, B) and (B, A) would be stored as two different pairs and every count would be split in half.</p><p>The most common single item is WHITE HANGING HEART T-LIGHT HOLDER, on about 9.6% of UK invoices, followed by JUMBO BAG RED RETROSPOT at about 8.0%. The most common pair is the pink polka dot and red retrospot jumbo bags, on about 3.05% of invoices.</p><p>With 40 products there are exactly 780 possible pairs, and all 780 were seen at least once. That density is a warning: “bought together at least once” means almost nothing here, which is why we need lift.</p><h3>Confidence and lift as functions</h3><p>Confidence divides the pair count by the count of the first item. Lift divides pair support by what you’d expect if the two items were bought independently.</p><pre>def confidence(a, b):<br>    key = tuple(sorted((a, b)))<br>    return pair_counts[key] / item_counts[a]<br> <br>def lift(a, b):<br>    key = tuple(sorted((a, b)))<br>    return pair_support[key] / (support[a] * support[b])<br> <br>example_a, example_b = sorted(pair_counts, key=lambda p: -pair_counts[p])[0]<br>round(confidence(example_a, example_b), 3), round(confidence(example_b, example_a), 3)<br># (0.663, 0.382)<br>round(lift(example_a, example_b), 2)<br># 8.29</pre><p>For the jumbo bag pair, about 66% of baskets with the pink bag also have the red one, but only about 38% of red bag baskets have the pink. Why the difference? The pink bag appears in 763 baskets and the red one in 1,326. Same pair count, different denominator. Confidence has a direction; lift doesn’t. A lift of 8.29 says the two bags turn up together about eight times more often than chance would predict, whichever way round you read it.</p><h3>Assemble and rank the rules table</h3><p>Putting every measure into one DataFrame makes sorting, filtering and saving easy. I store confidence in both directions so I never have to recalculate it.</p><pre>rows = []<br>for (a, b), cnt in pair_counts.items():<br>    rows.append((a, b, cnt, pair_support[(a, b)],<br>                 confidence(a, b), confidence(b, a), lift(a, b)))<br> <br>rules = pd.DataFrame(rows, columns=[&quot;A&quot;, &quot;B&quot;, &quot;co_count&quot;, &quot;support&quot;,<br>                                    &quot;conf_A_to_B&quot;, &quot;conf_B_to_A&quot;, &quot;lift&quot;])<br> <br>meaningful = rules[rules[&quot;co_count&quot;] &gt;= 20]<br>meaningful.sort_values(&quot;lift&quot;, ascending=False).head(10)</pre><p>The top of the table by lift:</p><pre>A                            B                                  co_count   lift<br>ALARM CLOCK BAKELIKE GREEN   ALARM CLOCK BAKELIKE RED                454  16.50<br>WOODEN FRAME ANTIQUE WHITE   WOODEN PICTURE FRAME WHITE FINISH       458  12.97<br>HEART OF WICKER LARGE        HEART OF WICKER SMALL                   398  10.76<br>JAM MAKING SET PRINTED       JAM MAKING SET WITH JARS                259   9.51<br>LUNCH BAG APPLE DESIGN       LUNCH BAG SUKI DESIGN                   329   9.33</pre><p>The green and red bakelike alarm clocks appear together in 454 baskets with a lift of about 16.5. Most of the top rules are colour or design variants of the same product. That’s a real finding: these customers buy matching sets.</p><h3>How to read the result</h3><p>Numbers in a table need translating before anyone can act on them. The notebook turns the top rule into a sentence:</p><pre>top_rule = meaningful.sort_values(&quot;lift&quot;, ascending=False).iloc[0]<br>print(f&quot;Customers who buy &#39;{top_rule[&#39;A&#39;]}&#39; are {round(top_rule[&#39;lift&#39;], 1)}x &quot;<br>      f&quot;more likely to also buy &#39;{top_rule[&#39;B&#39;]}&#39; than random chance would predict.&quot;)</pre><p>A few ways to read the rules table sensibly:</p><ul><li><strong>High confidence, modest lift</strong> usually means B is just popular. Several lunch bags point to LUNCH BAG RED RETROSPOT with high confidence, but their lift is only around 7, lower than the top pairs. Always look at lift next to confidence.</li><li><strong>Compare against a baseline, not just 1.</strong> Here 426 of the 780 rules have lift above 2. That’s partly because we picked the 40 most popular products, which tend to land in the same large orders. A lift of 3 is unremarkable in this table.</li><li><strong>Find anchor products.</strong> Count how often each product appears in strong rules:</li></ul><pre>strong = meaningful[meaningful[&quot;lift&quot;] &gt; 2]<br>appearance_counts = pd.concat([strong[&quot;A&quot;], strong[&quot;B&quot;]]).value_counts()<br>appearance_counts.head(4)</pre><pre>SET/5 RED RETROSPOT LID GLASS BOWLS    33<br>GARDENERS KNEELING PAD KEEP CALM       33<br>PACK OF 72 RETROSPOT CAKE CASES        32<br>RECIPE BOX PANTRY YELLOW DESIGN        32</pre><p>The red retrospot glass bowls link strongly to the cake cases, the pantry jelly moulds and the pantry recipe box. That’s a ready-made “retro baking” bundle idea. And these associations aren’t niche: 92.9% of the multi-item baskets contain at least one strong pair.</p><h3>Watch out</h3><p>The biggest trap in market basket analysis is the minimum count. A pair seen only a handful of times can show an enormous lift by pure coincidence, so it pays to see how the threshold changes the size of your rules table before you pick one.</p><pre>for min_count in [5, 10, 20, 50, 100]:<br>    n_rules = (rules[&quot;co_count&quot;] &gt;= min_count).sum()<br>    print(f&quot;min_count={min_count}: {n_rules} rules survive&quot;)</pre><pre>min_count=5: 780 rules survive<br>min_count=10: 780 rules survive<br>min_count=20: 779 rules survive<br>min_count=50: 643 rules survive<br>min_count=100: 231 rules survive</pre><ol><li><strong>Rare pairs fake huge lifts.</strong> Set the minimum count before you look at the results, so you’re not tempted to tune it to the answer you want. Here a minimum of 20 removes almost nothing, because the top 40 products are all common, while 100 leaves a shorter, more robust list.</li><li><strong>Forgetting to sort pair names.</strong> If you store pairs unsorted, your lookups in confidence and lift will quietly miss half the counts. A spot check helps: the notebook recounts the jumbo bag pair with a plain loop over baskets and confirms it matches pair_counts.</li><li><strong>Treating lift as cause.</strong> Lift describes co-occurrence. It says the items go together, not that showing one will make people buy the other. Test a bundle before you assume it works.</li><li><strong>Reading the top 40 as the whole catalogue.</strong> Single-item support here comes from multi-item baskets of top-40 products only, so it’s slightly conservative. For a full catalogue, the mlxtend library runs the same apriori logic far more efficiently; the maths underneath is identical to what we built.</li></ol><h3>Get the full notebook, dataset and quiz</h3><p>The lesson on my site has the full runnable notebook, the dataset, and a quiz so you can check you’ve really got it.</p><ul><li>Full lesson: <a href="https://www.mathewkanalytics.com/lessons/real-world-data-analytics/003-market-basket-analysis-and-association-rules/?utm_source=medium">Market Basket Analysis and Association Rules</a></li><li>Watch the video walkthrough: <a href="https://www.youtube.com/watch?v=WgEPeEFlm9g">https://www.youtube.com/watch?v=WgEPeEFlm9g</a></li><li>Subscribe for the next lesson in the series: <a href="https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1">https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1</a></li></ul><p>Everything is free. Try the same analysis on German or French invoices and see whether the top rules change.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c5035ffedbf7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[pandas merge, join and concat: Combine and Reshape Real Tables in Python]]></title>
            <link>https://medium.com/@makmwa124/pandas-merge-join-and-concat-combine-and-reshape-real-tables-in-python-bf848509cffa?source=rss-191c72e011d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/bf848509cffa</guid>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[programming]]></category>
            <dc:creator><![CDATA[Mathew kariuki]]></dc:creator>
            <pubDate>Tue, 27 Oct 2026 08:21:01 GMT</pubDate>
            <atom:updated>2026-10-27T08:21:01.512Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, I’m Mathew. Real data almost never arrives as one tidy spreadsheet. The question “how much did customers in each state spend, and how did they pay?” needs orders from one table, customers from another, prices from a third and payments from a fourth. Being able to combine those tables correctly is what turns a folder of CSV files into an answer.</p><p>The data here is a real extract of the Olist Brazilian E-Commerce dataset, split across separate tables for orders, customers, order items, products, category translations and payments, the same way a production database would store it. It’s a sample of 1,200 orders, so treat any totals as illustrations of the technique, not findings about Brazilian e-commerce.</p><h3>1. Load the tables and spot the keys</h3><p>Before joining anything, look at the shape of each table and find the columns they share.</p><pre>import pandas as pd<br> <br>orders = pd.read_csv(&quot;orders_sample.csv&quot;)<br>customers = pd.read_csv(&quot;customers_sample.csv&quot;)<br>items = pd.read_csv(&quot;order_items_sample.csv&quot;)<br>products = pd.read_csv(&quot;products_sample.csv&quot;)<br>print(orders.shape, customers.shape, items.shape, products.shape)</pre><pre>(1200, 8) (1200, 5) (1365, 7) (1105, 9)</pre><p>Those numbers already tell a story. There are 1,200 orders but 1,365 item rows, so some orders contain more than one item. That matters later, because it means a join between orders and items can’t keep one row per order.</p><p>Printing a few rows of each table shows the keys: orders has order_id and customer_id, items has order_id and product_id. Those shared columns are the bridges between tables. I always write them down before I write a single merge, because guessing the key is the fastest way to get a wrong result that still looks plausible.</p><h3>2. merge with an inner join</h3><p>merge lines up two tables on a shared key column. An inner join keeps only the rows where the key exists in both tables.</p><pre>orders_customers = orders.merge(customers, on=&quot;customer_id&quot;, how=&quot;inner&quot;)<br>print(orders_customers.shape)<br>print(orders_customers[[&quot;order_id&quot;, &quot;customer_id&quot;, &quot;customer_state&quot;]].head(3))</pre><pre>(1200, 12)<br>                           order_id                       customer_id  \<br>0  e481f51cbdc54678b7cc49136f2d6af7  9ef432eb6251297304e76186b10a928d<br>1  53cdb2fc8bc7dce0b6741e2150273451  b0830fb4747a6c6d20dea0b8c802d7ef<br>2  47770eb9100c2d0c44946d9cf07ec65d  41ce2a54c0b03bf3443c3d931a367089<br> <br>  customer_state<br>0             SP<br>1             BA<br>2             GO</pre><p>We started with 1,200 orders and ended with 1,200 rows, so every order found its customer. The column count went from 8 to 12: the 5 customer columns were added, with customer_id shared rather than duplicated. Each order now carries the state it shipped to.</p><p>That row-count check is the habit I want you to build. After every merge, compare the shape before and after. If an inner join quietly drops rows, you’ve lost data without any error message.</p><h3>3. Left joins and filling in the gaps</h3><p>A left join keeps every row from the left table, whether or not a match exists on the right. Where there’s no match, the new columns are filled with missing values. This is the join you want when the left table is the thing you’re analysing and the right table just adds detail.</p><pre>order_items_full = items.merge(products, on=&quot;product_id&quot;, how=&quot;left&quot;)<br>print(order_items_full.shape)<br>print(order_items_full[&quot;product_category_name&quot;].isna().sum())</pre><pre>(1365, 15)<br>12</pre><p>All 1,365 item rows survived, and 12 of them have no product category. An inner join would have silently dropped those 12 rows, and their revenue with them. With a left join they stay, and you can see the gap and decide what to do about it.</p><p>The category names in this dataset are in Portuguese, so a second left join brings in the English translation:</p><pre>translation = pd.read_csv(&quot;category_translation.csv&quot;)<br>order_items_full = order_items_full.merge(<br>    translation, on=&quot;product_category_name&quot;, how=&quot;left&quot;<br>)</pre><pre>product_category_name product_category_name_english<br>0      moveis_decoracao               furniture_decor<br>1            perfumaria                     perfumery<br>3              pet_shop                      pet_shop<br>5    relogios_presentes                 watches_gifts<br>7       cama_mesa_banho                bed_bath_table</pre><p>A lookup table like this is the classic use for a left join: you want every item kept, with a friendlier label added where one exists.</p><h3>Which join when</h3><ul><li><strong>inner</strong>: only rows that match in both tables. Good when an unmatched row is meaningless for your question.</li><li><strong>left</strong>: every row from your main table, extra detail where available. My default for enrichment.</li><li><strong>right</strong>: the mirror image of left. In practice I just swap the table order and use left.</li><li><strong>outer</strong>: every row from both sides. Useful for reconciliation, when you want to see what’s missing on each side.</li></ul><h3>4. Chain merges into one analysis table</h3><p>Most real questions need more than two tables. You can chain merges, starting from the table that defines what one row means and adding detail step by step.</p><pre>payments = pd.read_csv(&quot;payments_sample.csv&quot;)<br>full = (orders<br>        .merge(customers, on=&quot;customer_id&quot;, how=&quot;left&quot;)<br>        .merge(items, on=&quot;order_id&quot;, how=&quot;left&quot;)<br>        .merge(products, on=&quot;product_id&quot;, how=&quot;left&quot;)<br>        .merge(payments, on=&quot;order_id&quot;, how=&quot;left&quot;))<br>print(full.shape)<br>print(full[[&quot;order_id&quot;, &quot;customer_state&quot;, &quot;price&quot;, &quot;payment_type&quot;]].head(5))</pre><pre>(1429, 30)<br>                           order_id customer_state   price payment_type<br>0  e481f51cbdc54678b7cc49136f2d6af7             SP   29.99  credit_card<br>1  e481f51cbdc54678b7cc49136f2d6af7             SP   29.99      voucher<br>2  e481f51cbdc54678b7cc49136f2d6af7             SP   29.99      voucher<br>3  53cdb2fc8bc7dce0b6741e2150273451             BA  118.70       boleto<br>4  47770eb9100c2d0c44946d9cf07ec65d             GO  159.90  credit_card</pre><p>Look closely at the first three rows. It’s the same order, the same item and the same price of 29.99, repeated three times because that order was paid with one credit card and two vouchers. We started with 1,200 orders and ended with 1,429 rows.</p><p>This is the single most important thing to understand about merging. When a key appears several times on the right, every match becomes its own row. Nothing is wrong with the merge, but if you now sum price you&#39;ll count that 29.99 three times. One wide table is great for exploring, but before you total anything, ask what one row actually represents.</p><h3>5. concat: stack tables by position</h3><p>merge combines tables side by side using a key. concat does something different: it stacks tables on top of each other, matching columns by name. Use it when you have the same kind of data split into pieces, like monthly exports or separate status lists.</p><pre>delivered = orders[orders[&quot;order_status&quot;] == &quot;delivered&quot;].head(3)<br>shipped = orders[orders[&quot;order_status&quot;] == &quot;shipped&quot;].head(3)<br>combined = pd.concat([delivered, shipped])<br>print(combined[[&quot;order_id&quot;, &quot;order_status&quot;]])</pre><pre>order_id order_status<br>0    e481f51cbdc54678b7cc49136f2d6af7    delivered<br>1    53cdb2fc8bc7dce0b6741e2150273451    delivered<br>2    47770eb9100c2d0c44946d9cf07ec65d    delivered<br>44   ee64d42b8cf066f35eac1cf57de1aa85      shipped<br>154  6942b8da583c2f9957e990d028607019      shipped<br>162  36530871a5e80138db53bcfd8a104d90      shipped</pre><p>Notice the index: 0, 1, 2, then 44, 154, 162. concat keeps the original row labels. That&#39;s handy for tracing rows back to their source, but if you stack files that each start at 0 you&#39;ll get duplicate index values. Pass ignore_index=True when you want a clean 0 to n index.</p><h3>6. Reshape with melt (and pivot)</h3><p>Merging changes which columns you have. Reshaping changes the layout of the same data between wide (one column per measure) and long (one row per measure). Charting libraries and many group operations prefer long data.</p><p>First, a small summary to reshape, total item price per state from the combined table:</p><pre>state_totals = (full.groupby(&quot;customer_state&quot;)[&quot;price&quot;]<br>                    .sum().reset_index().head(6))<br> <br>long_form = state_totals.melt(<br>    id_vars=&quot;customer_state&quot;, var_name=&quot;metric&quot;, value_name=&quot;value&quot;<br>)<br>print(long_form)</pre><pre>customer_state metric     value<br>0             AC  price    199.00<br>1             AL  price    525.58<br>2             AM  price     23.99<br>3             AP  price    189.90<br>4             BA  price  10190.21<br>5             CE  price   3642.92</pre><p>id_vars are the columns that identify a row and stay put. Every other column becomes a pair: its name goes into metric and its value into value. With a single measure the output looks almost the same as the input, but add a second column, such as freight or item count, and each state gets one row per measure. That long format is what you feed into a faceted chart or a groupby(&quot;metric&quot;).</p><p>pivot is the reverse trip: it takes long data and spreads one column back out into separate columns. One caution on the numbers above: they are sums over the merged table, so orders with more than one payment row, like the voucher order above, have their price counted more than once. That&#39;s exactly the fan-out from section 4, and a good reminder to aggregate at the right level first.</p><h3>Watch out</h3><ol><li><strong>Row explosion from one-to-many keys.</strong> If the row count grows after a merge, find out why before you sum anything. Here, 1,200 orders became 1,429 rows.</li><li><strong>Inner joins that silently drop data.</strong> Twelve items had no category. An inner join would have removed them with no warning. Check shape before and after every merge.</li><li><strong>Merging on the wrong key.</strong> Always merge on true identifiers like order_id or customer_id, not on names or labels that might repeat.</li><li><strong>Confusing concat with merge.</strong> concat stacks by position and column name. It does not match rows by key, so use merge whenever rows need to line up by ID.</li></ol><h3>Get the full notebook, dataset and quiz</h3><p>The lesson on my site has the full runnable notebook, the dataset, and a quiz so you can check you’ve really got it.</p><ul><li>Full lesson: <a href="https://www.mathewkanalytics.com/lessons/data-analytics-zero-to-hero/011-pandas-merging-joining-and-reshaping/?utm_source=medium">Pandas Merging, Joining and Reshaping</a></li><li>Watch the video walkthrough: <a href="https://www.youtube.com/watch?v=dX5WRGzoigA">https://www.youtube.com/watch?v=dX5WRGzoigA</a></li><li>Subscribe for the next lesson in the series: <a href="https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1">https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1</a></li></ul><p>Everything is free. Grab the CSVs, run each merge yourself, and watch those row counts change.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bf848509cffa" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Cohort Analysis in Python: Build a Customer Retention Heatmap with pandas]]></title>
            <link>https://medium.com/@makmwa124/cohort-analysis-in-python-build-a-customer-retention-heatmap-with-pandas-3281f3ec0328?source=rss-191c72e011d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/3281f3ec0328</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[customer-retention]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[marketing-analytics]]></category>
            <dc:creator><![CDATA[Mathew kariuki]]></dc:creator>
            <pubDate>Tue, 27 Oct 2026 07:00:58 GMT</pubDate>
            <atom:updated>2026-10-27T07:00:58.548Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, I’m Mathew. Once someone buys from you for the first time, how likely are they to come back next month, and is the business getting better or worse at keeping them? One overall repeat rate can’t answer that, because it mixes brand-new customers with people who have been around for a year. Cohort analysis fixes this by comparing like with like.</p><p>A cohort is a group of customers who share a starting point. Here that’s the month of their first purchase, and you follow each group forward in time. By the end you’ll have a retention table, a heatmap, and a few honest conclusions you could put in front of a manager.</p><h3>The data and the month labels</h3><p>The dataset is the cleaned transactions file from a UK online gift retailer, online_retail_clean.csv, covering December 2010 to December 2011. You only need four columns: CustomerID, InvoiceNo, InvoiceDate and Revenue.</p><p>Cohort analysis works in months, so every transaction needs a month label. Then each customer needs a cohort: the earliest month they bought anything.</p><pre>import pandas as pd<br>import matplotlib.pyplot as plt<br> <br>clean = pd.read_csv(&quot;online_retail_clean.csv&quot;, parse_dates=[&quot;InvoiceDate&quot;])<br>clean[&quot;CustomerID&quot;] = clean[&quot;CustomerID&quot;].astype(int)<br>clean.shape   # (391150, 9)<br> <br># Cut every timestamp down to the first day of its month<br>clean[&quot;InvoiceMonth&quot;] = clean[&quot;InvoiceDate&quot;].values.astype(&quot;datetime64[M]&quot;)<br> <br># A customer&#39;s cohort is the month of their first purchase<br>first_purchase = clean.groupby(&quot;CustomerID&quot;)[&quot;InvoiceMonth&quot;].min().rename(&quot;CohortMonth&quot;)<br> <br># Attach the cohort to every transaction<br>merged = clean.merge(first_purchase, on=&quot;CustomerID&quot;)</pre><p>So 2010–12–01 08:26 becomes 2010–12–01. Customer 12346 lands in the January 2011 cohort, 12347 in December 2010, and 12349 not until November 2011.</p><p>Two small things matter here. Converting CustomerID to an integer makes it a clean grouping and merge key. And because each customer has exactly one cohort month, the merge doesn&#39;t create extra rows. If you prefer .dt.to_period(&quot;M&quot;) for the month label, that works too, but it gives a period type that behaves differently in arithmetic, so pick one approach and stick with it.</p><h3>The cohort index: putting every cohort on one timeline</h3><p>To compare a January starter with a June starter, you need “months since first purchase”, not calendar months. Month 0 is the first purchase month, month 1 the next one, and so on. That number is the cohort index.</p><pre>year_diff = merged[&quot;InvoiceMonth&quot;].dt.year - merged[&quot;CohortMonth&quot;].dt.year<br>month_diff = merged[&quot;InvoiceMonth&quot;].dt.month - merged[&quot;CohortMonth&quot;].dt.month<br>merged[&quot;CohortIndex&quot;] = year_diff * 12 + month_diff<br> <br>merged[&quot;CohortIndex&quot;].describe()</pre><pre>min       0.0<br>25%       0.0<br>50%       3.0<br>75%       7.0<br>max      12.0</pre><p>Splitting years and months and recombining them as year_diff * 12 + month_diff handles the jump from December to January correctly. The index runs from 0 to 12 with a median of 3, and at least a quarter of all transactions happen in a customer&#39;s very first month.</p><h3>Counting customers per cohort and pivoting</h3><p>Retention is about people, not transaction lines. So for each cohort and each month offset, count unique customers, then reshape the long list into a grid with one row per cohort and one column per month.</p><pre>cohort_data = (merged.groupby([&quot;CohortMonth&quot;, &quot;CohortIndex&quot;])[&quot;CustomerID&quot;]<br>               .nunique().reset_index())<br> <br>cohort_pivot = cohort_data.pivot(index=&quot;CohortMonth&quot;,<br>                                 columns=&quot;CohortIndex&quot;,<br>                                 values=&quot;CustomerID&quot;)<br>cohort_pivot.iloc[:5, :6]</pre><pre>CohortIndex      0      1      2      3      4      5<br>CohortMonth<br>2010-12-01   884.0  323.0  286.0  339.0  320.0  352.0<br>2011-01-01   416.0   91.0  111.0   95.0  132.0  120.0<br>2011-02-01   380.0   71.0   71.0  109.0  103.0   93.0<br>2011-03-01   452.0   67.0  114.0   90.0  101.0   76.0<br>2011-04-01   300.0   63.0   61.0   63.0   59.0   68.0</pre><p>The December 2010 cohort started with 884 customers and 323 of them bought again the following month. The January 2011 cohort started with 416 and only 91 came back in month 1. The values are floats because later cohorts have empty cells for months that haven’t happened yet. That empty lower-right triangle is normal.</p><h3>From counts to retention rates, and the heatmap</h3><p>Raw counts are hard to compare because cohorts start at different sizes. Divide each row by its own month-0 value and you get a retention rate.</p><pre>cohort_sizes = cohort_pivot.iloc[:, 0]<br>retention = cohort_pivot.divide(cohort_sizes, axis=0)<br> <br>plt.figure(figsize=(11, 7))<br>plt.imshow(retention, cmap=&quot;YlGnBu&quot;, aspect=&quot;auto&quot;, vmin=0, vmax=1)<br>plt.colorbar(label=&quot;Retention Rate&quot;)<br>plt.yticks(range(len(retention.index)),<br>           [d.strftime(&quot;%Y-%m&quot;) for d in retention.index])<br>plt.xlabel(&quot;Months Since First Purchase&quot;)<br>plt.title(&quot;Customer Retention Heatmap by Cohort&quot;)<br>plt.tight_layout()<br>plt.savefig(&quot;cohort_retention_heatmap.png&quot;, dpi=120)</pre><p>axis=0 is the important bit: it tells pandas to line the sizes up with the row index, so each cohort is divided by its own starting size. Month 0 is always 1.0.</p><p>For the heatmap, vmin=0, vmax=1 fixes the colour scale from 0% to 100%. Without it, matplotlib stretches the colours to fit your data, and two heatmaps from different periods would no longer be comparable. In the result you&#39;ll see a dark first column, a noticeably stronger December 2010 row, and the empty triangle where younger cohorts have no data yet.</p><h3>How to read the result</h3><p>A heatmap is a picture. Managers usually want a handful of numbers, so pull them out of the table.</p><pre>month1 = retention[1].dropna().sort_index()<br>month1.mean().round(3)                     # 0.204<br> <br>month1.idxmax(), month1.idxmin()           # 2010-12 (36.5%), 2011-11 (11.1%)<br> <br>half = len(month1) // 2<br>round(month1.iloc[:half].mean(), 3), round(month1.iloc[half:].mean(), 3)<br># (0.22, 0.189)<br> <br>retention[6].dropna().mean().round(3)      # 0.244</pre><p>Here’s how I’d read these:</p><ul><li><strong>Month-1 retention averages 0.204.</strong> About one in five new customers buys again the very next month. It’s a good early warning metric because every cohort except the newest has it.</li><li><strong>Best and worst cohorts.</strong> December 2010 is best at 36.5%, November 2011 weakest at 11.1%. Before calling that a trend, think about who is in each: November’s new customers include many one-off Christmas shoppers, and December 2010 includes people who were already buying before the data starts.</li><li><strong>Early versus late.</strong> The earlier half of cohorts averages 0.22 and the later half 0.189, a drop of about 3 percentage points. It looks like a decline, but the early half contains the unusual December 2010 cohort and the later half contains the pre-Christmas months. Re-run it without December 2010 before you report it.</li><li><strong>Month 6 averages 0.244</strong>, higher than month 1. That can happen when seasonal peaks bring customers back.</li></ul><p>Looking only at cohorts that have reached month 6, most hold about 22% to 27%, December 2010 stays highest at 36.2%, and June 2011 falls to 9.5%.</p><h3>Do retained customers spend more?</h3><p>Retention counts heads. The business also cares about money, so repeat the same groupby and pivot with Revenue summed instead of customers counted, then compare customers who came back in month 1 with everyone else.</p><pre>revenue_pivot = (merged.groupby([&quot;CohortMonth&quot;, &quot;CohortIndex&quot;])[&quot;Revenue&quot;]<br>                 .sum().reset_index()<br>                 .pivot(index=&quot;CohortMonth&quot;, columns=&quot;CohortIndex&quot;, values=&quot;Revenue&quot;))<br> <br>avg_rev_per_active = revenue_pivot / cohort_pivot<br> <br>returners = merged.loc[merged[&quot;CohortIndex&quot;] == 1, &quot;CustomerID&quot;].unique()<br>total_rev = clean.groupby(&quot;CustomerID&quot;)[&quot;Revenue&quot;].sum()<br>is_ret = total_rev.index.isin(returners)<br>round(total_rev[is_ret].mean(), 2), round(total_rev[~is_ret].mean(), 2)<br># (4717.98, 1238.93)</pre><p>The December 2010 cohort spent 565,200 in its first month and still 325,803 in month 5. The January 2011 cohort dropped from 289,033 in month 0 to 53,129 in month 1. Per active customer, December 2010 rises from 639.37 in month 0 to 925.58 in month 5, which suggests the customers who stay tend to be the bigger buyers.</p><p>Month-1 returners spent 4,717.98 on average against 1,238.93 for the rest, about 280.8% more. That supports investing in early follow-up, like a welcome series in the first few weeks. But be careful: returners have more orders by definition, so part of the gap is built in. It’s a strong link, not proof that a campaign would cause extra spend. A small test campaign is the honest way to measure the real effect.</p><h3>Common mistakes</h3><p>These are the ones I see most often, plus the checks I use to catch them.</p><pre># Spot-check one cell by hand<br>manual = merged[(merged[&quot;CohortMonth&quot;] == cohort_pivot.index[0]) &amp;<br>                (merged[&quot;CohortIndex&quot;] == 1)][&quot;CustomerID&quot;].nunique()<br>manual == cohort_pivot.iloc[0, 1]          # True (323)<br> <br># Month-0 counts should add up to every customer<br>cohort_pivot.iloc[:, 0].sum() == clean[&quot;CustomerID&quot;].nunique()   # True</pre><ol><li><strong>Using </strong><strong>count() instead of </strong><strong>nunique().</strong> A customer with ten lines in a month would count ten times, and your retention rates would be meaningless.</li><li><strong>Ignoring partial periods.</strong> December 2010 has 884 new customers partly because it includes everyone already active when the data starts. December 2011 has only 41 because the data ends on 9 December. Both ends of any dataset need a caveat.</li><li><strong>Ranking cohorts of different ages together.</strong> December 2010 has 4,801 customer-months, more than January (1,628) and March (1,290) combined, but it has also had more months to build up that total. Compare cohorts of similar age.</li><li><strong>Confusing first-month-only with one-time.</strong> 37.8% of customers only bought in their first month, yet the overall repeat rate is 65.3%. A customer who places two orders in the same month counts as a repeat buyer in one metric and first-month-only in the other. Say which one you mean.</li></ol><p>The two True results confirm that one cell and the whole first column reconcile. In total this covers 13 monthly cohorts and 4,334 unique customers, and I&#39;d put that scope line at the top of any report so readers know what sits behind the percentages.</p><h3>Get the full notebook, dataset and quiz</h3><p>The lesson on my site has the full runnable notebook, the dataset, and a quiz so you can check you’ve really got it.</p><ul><li>Full lesson: <a href="https://www.mathewkanalytics.com/lessons/real-world-data-analytics/004-cohort-analysis-and-customer-retention/?utm_source=medium">Cohort Analysis and Customer Retention</a></li><li>Watch the video walkthrough: <a href="https://www.youtube.com/watch?v=Bdi4uP16cKI">https://www.youtube.com/watch?v=Bdi4uP16cKI</a></li><li>Subscribe for the next lesson in the series: <a href="https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1">https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1</a></li></ul><p>Everything is free. Try the heatmap on your own customer data next, it’s often the quickest way to start a useful conversation about loyalty.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3281f3ec0328" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Cleaning in pandas: Missing Values, Duplicates and Data Types]]></title>
            <link>https://medium.com/@makmwa124/data-cleaning-in-pandas-missing-values-duplicates-and-data-types-ed2208224d8e?source=rss-191c72e011d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/ed2208224d8e</guid>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-cleaning]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[python]]></category>
            <dc:creator><![CDATA[Mathew kariuki]]></dc:creator>
            <pubDate>Thu, 22 Oct 2026 22:25:54 GMT</pubDate>
            <atom:updated>2026-10-22T22:25:54.892Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, I’m Mathew. Before you can answer “how much did we sell?” or “who are our best customers?”, you have to trust the rows you’re counting. Real data arrives with gaps, repeated rows, dates stored as text and returns mixed in with sales. This tutorial walks through a cleaning routine in pandas that I use on almost every new dataset.</p><p>The data is a real extract of the UCI Online Retail dataset: actual 2010–2011 UK e-commerce transactions, messy in exactly the way real data is. It’s a small sample of 1,500 rows, so treat it as a practice ground for the technique, not as a source of business conclusions.</p><h3>First look: spot the problems before fixing anything</h3><p>Resist the urge to start fixing straight away. Spend two minutes finding out what’s wrong first, because the fix depends on the problem.</p><pre>import pandas as pd<br> <br>df = pd.read_csv(&quot;online_retail_sample.csv&quot;)<br>print(df.shape)        # (1500, 8)<br>print(df.dtypes)<br>print(df.info())</pre><p>The info() output already tells most of the story:</p><pre>#   Column       Non-Null Count  Dtype<br> 0   InvoiceNo    1500 non-null   object<br> 1   StockCode    1500 non-null   object<br> 2   Description  1499 non-null   object<br> 3   Quantity     1500 non-null   int64<br> 4   InvoiceDate  1500 non-null   object<br> 5   UnitPrice    1500 non-null   float64<br> 6   CustomerID   1442 non-null   float64<br> 7   Country      1500 non-null   object</pre><p>Three things jump out. Description and CustomerID have gaps. InvoiceDate is object, which means pandas sees it as text, not as a date. And CustomerID is a float64, which is odd for an ID. Then count missing values and duplicates directly:</p><pre>print(df.isna().sum())<br>print(df.duplicated().sum())                     # 36<br>print(df[df.duplicated(keep=False)].head(4))</pre><p>isna().sum() confirms 1 missing description and 58 missing customer IDs. duplicated() finds 36 rows that are exact copies of an earlier row. Passing keep=False marks every copy, including the first, so you can see the pairs side by side. In this sample, two lines for &quot;UNION JACK FLAG LUGGAGE TAG&quot; on invoice 536409 are identical in every column.</p><h3>Handling missing values: drop or fill?</h3><p>There’s no single right answer to missing data. The choice depends on what the column means and what you’ll do with it next.</p><pre>print(df[&quot;CustomerID&quot;].isna().mean())   # 0.03866666666666667<br>df_dropped = df.dropna(subset=[&quot;CustomerID&quot;])<br>print(df_dropped.shape)                  # (1442, 8)</pre><p>The .mean() trick on a True/False column gives the share of missing values: just under 4% of rows have no customer. If your next step is customer analysis (repeat purchases, cohorts, lifetime value), a row with no customer can&#39;t be attributed to anyone, so dropping it is reasonable. Notice the subset argument: without it, dropna would drop a row if <em>any</em> column was missing, which is rarely what you want.</p><p>The description is a different case. A single missing product name doesn’t make the sale invalid, so I keep the row and fill the gap with a clear placeholder:</p><pre>df[&quot;Description&quot;] = df[&quot;Description&quot;].fillna(&quot;UNKNOWN ITEM&quot;)<br>print(df[&quot;Description&quot;].isna().sum())   # 0</pre><p>Using a label like UNKNOWN ITEM rather than an empty string means it shows up honestly in any later count or chart, instead of hiding.</p><h3>Removing duplicates</h3><p>Exact duplicates usually come from a system logging the same line twice. Left in, they inflate revenue and quantity totals.</p><pre>before = df.shape[0]<br>df = df.drop_duplicates()<br>after = df.shape[0]<br>print(f&quot;Removed {before - after} real duplicate rows&quot;)<br># Removed 36 real duplicate rows</pre><p>The number matches what duplicated().sum() reported earlier, which is a good sanity check. I like printing the before and after counts every time I remove rows. It turns a silent operation into something you (and a reviewer) can verify.</p><p>One caution: drop_duplicates() with no arguments only removes rows that match in every column. That&#39;s the safe default. If you pass subset=, you&#39;re making a business decision about what counts as &quot;the same&quot;, so be sure you mean it.</p><h3>Fixing data types</h3><p>Wrong types cause quiet bugs: IDs that print as 17850.0, dates you can&#39;t group by month, numbers that sort as text. The notebook fixes two.</p><pre>df = df.dropna(subset=[&quot;CustomerID&quot;])<br>df[&quot;CustomerID&quot;] = df[&quot;CustomerID&quot;].astype(&quot;Int64&quot;)<br>print(df[&quot;CustomerID&quot;].dtype)        # Int64</pre><p>Why was CustomerID a float in the first place? Because a classic NumPy integer column can&#39;t hold missing values, so pandas stores any whole-number column with gaps as float. The capital-I Int64 is pandas&#39; nullable integer type, which can hold whole numbers alongside missing values. The IDs now print as 17850 instead of 17850.0.</p><p>Dates are the other big one:</p><pre>df[&quot;InvoiceDate&quot;] = pd.to_datetime(df[&quot;InvoiceDate&quot;])<br>print(df[&quot;InvoiceDate&quot;].dtype)                   # datetime64[ns]<br>print(df[&quot;InvoiceDate&quot;].dt.day_name().head(3))   # Wednesday, Wednesday, Wednesday</pre><p>Once the column is a real datetime, the whole .dt accessor opens up: day names, months, hours, and date arithmetic. The first invoices fall on 2010-12-01, which was indeed a Wednesday. That kind of quick spot check is worth doing whenever you convert dates.</p><h3>Cleaning text columns</h3><p>Text columns pick up invisible problems such as stray spaces. Two strings that look the same on screen won’t match in a groupby or a merge if one has a trailing space.</p><pre>has_whitespace = (df[&quot;Description&quot;].str.strip() != df[&quot;Description&quot;]).sum()<br>print(has_whitespace)    # 270<br>df[&quot;Description&quot;] = df[&quot;Description&quot;].str.strip()</pre><p>270 descriptions had leading or trailing whitespace. That’s a lot to fix by hand and one line with the .str accessor, which applies a string method to every value in the column at once.</p><p>Inconsistent capitalisation is the other common text issue. Comparing in a single case makes counts reliable:</p><pre>print(df[&quot;Country&quot;].str.upper().value_counts().head(5))</pre><pre>UNITED KINGDOM    1261<br>NORWAY              73<br>EIRE                21<br>FRANCE              20<br>GERMANY             15</pre><p>Note that this line only <em>displays</em> the uppercased values. If you want the change to stick, assign it back to the column, as with the strip step above.</p><h3>Filtering invalid or suspicious rows</h3><p>The last step is the one that needs the most business sense. Some rows are technically valid data but don’t belong in the analysis you’re doing.</p><pre>returns = df[df[&quot;Quantity&quot;] &lt; 0]<br>print(returns.shape[0])    # 12<br>print(returns[[&quot;InvoiceNo&quot;, &quot;Description&quot;, &quot;Quantity&quot;]].head(3))</pre><pre>InvoiceNo                      Description  Quantity<br>141   C536379                         Discount        -1<br>154   C536383  SET OF 3 COLOURED  FLYING DUCKS        -1<br>235   C536391    PLASTERS IN TIN CIRCUS PARADE       -12</pre><p>There are 12 rows with negative quantities. The C prefix on the invoice number marks a cancellation, and one row is a &quot;Discount&quot; line rather than a product. None of these are errors in the data, but they aren&#39;t sales either. For a sales analysis, keep only positive quantities and positive prices:</p><pre>sales_only = df[(df[&quot;Quantity&quot;] &gt; 0) &amp; (df[&quot;UnitPrice&quot;] &gt; 0)]<br>print(f&quot;{df.shape[0]} total rows, {sales_only.shape[0]} genuine sales rows&quot;)<br># 1406 total rows, 1394 genuine sales rows</pre><p>I keep this as a separate sales_only frame rather than overwriting df. If someone later asks about return rates, the returns are still there.</p><h3>How to read the result</h3><p>From 1,500 raw rows we end up with 1,406 cleaned rows and 1,394 genuine sales lines. Every removal has a reason you can state in one sentence: duplicates were logged twice, missing customers can’t be attributed, returns aren’t sales. That’s the test I’d apply to any cleaning step. If you can’t explain why a row was removed, you probably shouldn’t remove it.</p><p>The order also matters. I look first, then handle gaps, then duplicates, then types, then text, and filter on business rules last. Fixing types before you know where the gaps are tends to throw errors, and filtering too early can hide problems you haven’t spotted yet.</p><h3>Watch out</h3><ol><li><strong>Dropping on every column.</strong> df.dropna() with no subset removes a row if any column is missing. On wide data that can throw away far more than you meant to.</li><li><strong>Assuming </strong><strong>strip fixes all spacing.</strong> It only removes spaces at the start and end. The &quot;SET OF 3 COLOURED FLYING DUCKS&quot; description above still has a double space in the middle after stripping.</li><li><strong>Forgetting to assign back.</strong> String methods and most pandas operations return a new result. df[&quot;Country&quot;].str.upper() on its own changes nothing in df.</li><li><strong>Treating returns as bad data.</strong> Negative quantities are real events. Filter them out for a sales question, but keep them available for other questions.</li></ol><h3>Get the full notebook, dataset and quiz</h3><p>The lesson on my site has the full runnable notebook, the dataset, and a quiz so you can check you’ve really got it.</p><ul><li>Full lesson: <a href="https://www.mathewkanalytics.com/lessons/data-analytics-zero-to-hero/008-pandas-data-cleaning/?utm_source=medium">Pandas Data Cleaning</a></li><li>Watch the video walkthrough: <a href="https://www.youtube.com/watch?v=NIq-xSWcQG4">https://www.youtube.com/watch?v=NIq-xSWcQG4</a></li><li>Subscribe for the next lesson in the series: <a href="https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1">https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1</a></li></ul><p>Everything is free. Try the routine on a messy file of your own and see how many problems the first look catches.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ed2208224d8e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to Analyze an A/B Test in Python (Chi-Square, Effect Size, CI)]]></title>
            <link>https://medium.com/@makmwa124/how-to-analyze-an-a-b-test-in-python-chi-square-effect-size-ci-d3a3f0c948f3?source=rss-191c72e011d3------2</link>
            <guid isPermaLink="false">https://medium.com/p/d3a3f0c948f3</guid>
            <category><![CDATA[ab-testing]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[statistics]]></category>
            <dc:creator><![CDATA[Mathew kariuki]]></dc:creator>
            <pubDate>Tue, 20 Oct 2026 13:00:03 GMT</pubDate>
            <atom:updated>2026-10-20T14:43:07.206Z</atom:updated>
            <content:encoded><![CDATA[<p>Hi, I’m Mathew. Most A/B test tutorials end with “variant B won!”. Real life is messier, and the most useful skill is knowing when the data does <strong>not</strong> support a conclusion. Let’s analyze a real experiment and practice reading it honestly.</p><h3>The experiment</h3><p>A mobile game (the well-known Cookie Cats test) moved its progression gate, a point where players hit a pause, from level 30 (gate_30) to level 40 (gate_40). Players were randomly assigned. The question is whether the move changes retention.</p><p>One important note: the lesson works with a <strong>sample of 2,256 players</strong>, so the numbers below belong to that sample.</p><pre>import pandas as pd<br>import numpy as np<br>from scipy import stats<br> <br>ab_test = pd.read_csv(&quot;ab_test_cookie_cats_raw.csv&quot;)<br>ab_test[&quot;version&quot;].value_counts()<br># gate_40    1135<br># gate_30    1121</pre><p>Columns: userid, version, sum_gamerounds, retention_1 and retention_7 (booleans: did the player come back after 1 day / 7 days).</p><p>Always check group balance first. The split is 50.31% vs 49.69%, which is what we’d expect from random assignment.</p><h3>Retention by group</h3><p>Because retention is a True/False column, its mean is the retention rate:</p><pre>ab_test.groupby(&quot;version&quot;)[&quot;retention_1&quot;].mean().round(4) * 100<br># gate_30    43.44<br># gate_40    43.00<br> <br>ab_test.groupby(&quot;version&quot;)[&quot;retention_7&quot;].mean().round(4) * 100<br># gate_30    19.18<br># gate_40    17.71</pre><p>Day-1 retention is almost identical. Day-7 retention is 1.47 points lower for gate_40. Tempting to call it, right? Not yet.</p><h3>Is the gap bigger than chance?</h3><p>For a yes/no outcome across two groups, the chi-square test of independence is the standard tool. Build a contingency table with crosstab:</p><pre>table_7 = pd.crosstab(ab_test[&quot;version&quot;], ab_test[&quot;retention_7&quot;])<br>chi2, p_value, dof, expected = stats.chi2_contingency(table_7)<br>round(p_value, 4)   # 0.3976</pre><p>Day 1 gives p = 0.8633 and day 7 gives p = 0.3976. With alpha = 0.05, <strong>neither is significant</strong>. A gap this size could easily appear by random chance in a sample this small.</p><h3>Engagement needs a different test</h3><p>Total game rounds is skewed. The mean says 54.83 rounds for gate_30 and 44.70 for gate_40, but the medians are both 15. The max is 1,906 rounds and 97 players played zero. A few heavy players drag the means around, so a t-test on raw means is a poor fit. Use a non-parametric test:</p><pre>g30 = ab_test.loc[ab_test[&quot;version&quot;] == &quot;gate_30&quot;, &quot;sum_gamerounds&quot;]<br>g40 = ab_test.loc[ab_test[&quot;version&quot;] == &quot;gate_40&quot;, &quot;sum_gamerounds&quot;]<br> <br>stat, p = stats.mannwhitneyu(g30, g40, alternative=&quot;two-sided&quot;)<br>round(p, 4)   # 0.487</pre><p>Not significant either. The mean gap was outlier noise.</p><h3>Effect size and confidence interval</h3><p>A p-value says whether, not how much. Report the size of the gap in both forms:</p><pre>gap_pts = 19.18 - 17.71                       # 1.47 percentage points<br>rel_lift = (19.18 / 17.71 - 1) * 100          # 8.3 percent relative</pre><p>These tell different stories: “1.47 points” sounds tiny, “8.3% relative” sounds big. Both describe the same observed gap, so state both.</p><p>Then a 95% confidence interval on the difference in proportions:</p><pre>p1, p2 = 0.1918, 0.1771<br>n1, n2 = 1121, 1135<br> <br>se = np.sqrt(p1 * (1 - p1) / n1 + p2 * (1 - p2) / n2)<br>low  = (p1 - p2 - 1.96 * se) * 100<br>high = (p1 - p2 + 1.96 * se) * 100<br># (-1.73, 4.67)</pre><p>The interval runs from <strong>-1.73 to +4.67 points</strong>. It includes zero, which is the same message as the p-value, but more informative: the true effect could be a small harm to gate_30, nothing, or a few points of benefit.</p><h3>Looking inside the segments</h3><p>The notebook also splits by engagement. Among the top 25% most active players, day-7 retention is 60.58% (gate_30) vs 52.46% (gate_40). Among the bottom 25%, it&#39;s 1.63% vs 1.52%. So the observed gap sits almost entirely among heavy players. That&#39;s a hypothesis worth testing with more data, not a proven result: the notebook doesn&#39;t run significance tests on these segments, and slicing a sample into smaller pieces makes noise easier to mistake for signal. Engagement and retention are strongly related overall (correlation 0.527), which is why engagement is a sensible place to look.</p><h3>A word on the business framing</h3><p>The notebook includes a “what if” calculation: with 1,000,000 weekly active players, a 1.47-point gap would equal about 14,700 more retained players. That’s a useful way to size the stakes, but it only holds if the effect is real, and here the confidence interval includes zero. Present it as a “what this might be worth if confirmed” figure, never as a forecast.</p><h3>My honest takeaway</h3><ul><li>The point estimates lean toward keeping the gate at level 30 (19.18% vs 17.71% day-7 retention).</li><li><strong>But in this sample the difference is not statistically significant</strong> (p = 0.3976, CI -1.73 to +4.67 points).</li><li>The sensible next step is more data, or a pre-planned larger test, not a confident announcement.</li></ul><p>Learning to say “the evidence isn’t conclusive yet” is a real analyst skill. Stakeholders trust people who don’t oversell.</p><h3>A checklist you can reuse</h3><p>Here’s the routine I’d follow on any A/B test:</p><ol><li>Confirm the groups are balanced in size.</li><li>Compare the key rates, and check both short and long horizons (here day 1 and day 7).</li><li>Pick the right test for the metric: chi-square for yes/no outcomes, Mann-Whitney for skewed counts.</li><li>Report effect size in absolute and relative terms.</li><li>Add a confidence interval, because it shows the range of plausible effects.</li><li>Slice by segment only to generate hypotheses, and say so.</li><li>Write the conclusion in plain English, including what you don’t know.</li></ol><p>Following the same steps every time keeps you honest, and it makes your analysis easy for colleagues to review.</p><h3>Get the full notebook, dataset and quiz</h3><p>The full lesson includes the charts, the retention funnel, conditional retention, sanity checks, the saved summary table, and a quiz.</p><ul><li>Full lesson: <a href="https://www.mathewkanalytics.com/lessons/real-world-data-analytics/024-marketing-campaign-a-b-test-analysis/?utm_source=medium">Marketing Campaign A/B Test Analysis</a></li><li>Watch the video walkthrough: <a href="https://www.youtube.com/watch?v=uZWLJbzC7m0">https://www.youtube.com/watch?v=uZWLJbzC7m0</a></li><li>Subscribe for more real-data tutorials: <a href="https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1">https://www.youtube.com/@KarMat-Analytics?sub_confirmation=1</a></li></ul><p>It’s all free. Thanks for reading!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d3a3f0c948f3" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>