CLV_prediction.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Customer Lifetime Value (CLV)** = how much a company expects to earn from an average customer in a life time.\n",
    "\n",
    "CLV allows to benchmark customers and identify how much money the company can afford to spend on customer acquisition.\n",
    "\n",
    "**Historical CLV** = the sum of revenues of all customer transactions multiplied by average or product-level profit margin\n",
    "\n",
    "Problems with historical CLV:\n",
    "- doesn't account for customer tenure, retention or churn  (e.g. if the company is growing its customer base, historical CLV will be deflated due to short tenure)\n",
    "- doesn't account for new customers and their future revenue\n",
    "\n",
    "**Basic CLV** = Average Revenue * Profit Margin * Average Lifespan <br>\n",
    "(where Average Lifespan is e.g. average time before customer churn)\n",
    "\n",
    "**Granular CLV** = (Average Revenue per Transaction * Average Frequency * Profit Margin) * Average Lifespan <br>\n",
    "(where Avg Frequency is within the certain timeframe, e.g. a month) <br>\n",
    "accounts for each transaction\n",
    "\n",
    "**Traditional CLV** = (Average Revenue * Profit Margin) * Retention Rate / Churn Rate <br>\n",
    "(where Churn = 1 - Retention Rate) <br>\n",
    "Retention/Churn - a proxy of expected length of customer lifespan with the company <br>\n",
    "account for customer loyalty\n",
    "assumes that churn is final and customers do not return (especially critical for non-contractual business models)\n",
    "\n",
    "Because we don't have profit margin, we will calculate revenue-based CLV. \n",
    "\n",
    "But before calculating Basic, Granular and Traditional CLV, we will load, explore the data and then calculate retention rates using cohort analysis. We will need retention rates and churn rates for calculating Traditional CLV later."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 1: Load and Explore Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "retail = pd.read_csv(\"../cohort_analysis/online_retail_II.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Invoice</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>Price</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>489434</td>\n",
       "      <td>85048</td>\n",
       "      <td>15CM CHRISTMAS GLASS BALL 20 LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>6.95</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323P</td>\n",
       "      <td>PINK CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>489434</td>\n",
       "      <td>79323W</td>\n",
       "      <td>WHITE CHERRY LIGHTS</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>6.75</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>489434</td>\n",
       "      <td>22041</td>\n",
       "      <td>RECORD FRAME 7\" SINGLE SIZE</td>\n",
       "      <td>48</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>2.10</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>489434</td>\n",
       "      <td>21232</td>\n",
       "      <td>STRAWBERRY CERAMIC TRINKET BOX</td>\n",
       "      <td>24</td>\n",
       "      <td>2009-12-01 07:45:00</td>\n",
       "      <td>1.25</td>\n",
       "      <td>13085.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Invoice StockCode                          Description  Quantity  \\\n",
       "0  489434     85048  15CM CHRISTMAS GLASS BALL 20 LIGHTS        12   \n",
       "1  489434    79323P                   PINK CHERRY LIGHTS        12   \n",
       "2  489434    79323W                  WHITE CHERRY LIGHTS        12   \n",
       "3  489434     22041         RECORD FRAME 7\" SINGLE SIZE         48   \n",
       "4  489434     21232       STRAWBERRY CERAMIC TRINKET BOX        24   \n",
       "\n",
       "           InvoiceDate  Price  Customer ID         Country  \n",
       "0  2009-12-01 07:45:00   6.95      13085.0  United Kingdom  \n",
       "1  2009-12-01 07:45:00   6.75      13085.0  United Kingdom  \n",
       "2  2009-12-01 07:45:00   6.75      13085.0  United Kingdom  \n",
       "3  2009-12-01 07:45:00   2.10      13085.0  United Kingdom  \n",
       "4  2009-12-01 07:45:00   1.25      13085.0  United Kingdom  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retail.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1067371, 8)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retail.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number of unique customers: 5942\n"
     ]
    }
   ],
   "source": [
    "print(\"number of unique customers:\", retail['Customer ID'].nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "there are 34335 duplicated rows\n"
     ]
    }
   ],
   "source": [
    "#checking duplicates\n",
    "print(f\"there are {retail.duplicated().sum()} duplicated rows\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Invoice</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>Price</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>371</th>\n",
       "      <td>489517</td>\n",
       "      <td>21912</td>\n",
       "      <td>VINTAGE SNAKES &amp; LADDERS</td>\n",
       "      <td>1</td>\n",
       "      <td>2009-12-01 11:34:00</td>\n",
       "      <td>3.75</td>\n",
       "      <td>16329.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>383</th>\n",
       "      <td>489517</td>\n",
       "      <td>22130</td>\n",
       "      <td>PARTY CONE CHRISTMAS DECORATION</td>\n",
       "      <td>6</td>\n",
       "      <td>2009-12-01 11:34:00</td>\n",
       "      <td>0.85</td>\n",
       "      <td>16329.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>384</th>\n",
       "      <td>489517</td>\n",
       "      <td>22319</td>\n",
       "      <td>HAIRCLIPS FORTIES FABRIC ASSORTED</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 11:34:00</td>\n",
       "      <td>0.65</td>\n",
       "      <td>16329.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>385</th>\n",
       "      <td>489517</td>\n",
       "      <td>21913</td>\n",
       "      <td>VINTAGE SEASIDE JIGSAW PUZZLES</td>\n",
       "      <td>1</td>\n",
       "      <td>2009-12-01 11:34:00</td>\n",
       "      <td>3.75</td>\n",
       "      <td>16329.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>386</th>\n",
       "      <td>489517</td>\n",
       "      <td>21821</td>\n",
       "      <td>GLITTER STAR GARLAND WITH BELLS</td>\n",
       "      <td>1</td>\n",
       "      <td>2009-12-01 11:34:00</td>\n",
       "      <td>3.75</td>\n",
       "      <td>16329.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>390</th>\n",
       "      <td>489517</td>\n",
       "      <td>84951A</td>\n",
       "      <td>S/4 PISTACHIO LOVEBIRD COASTERS</td>\n",
       "      <td>1</td>\n",
       "      <td>2009-12-01 11:34:00</td>\n",
       "      <td>2.55</td>\n",
       "      <td>16329.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>391</th>\n",
       "      <td>489517</td>\n",
       "      <td>21491</td>\n",
       "      <td>SET OF THREE VINTAGE GIFT WRAPS</td>\n",
       "      <td>1</td>\n",
       "      <td>2009-12-01 11:34:00</td>\n",
       "      <td>1.95</td>\n",
       "      <td>16329.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>394</th>\n",
       "      <td>489517</td>\n",
       "      <td>21912</td>\n",
       "      <td>VINTAGE SNAKES &amp; LADDERS</td>\n",
       "      <td>1</td>\n",
       "      <td>2009-12-01 11:34:00</td>\n",
       "      <td>3.75</td>\n",
       "      <td>16329.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>657</th>\n",
       "      <td>489529</td>\n",
       "      <td>22028</td>\n",
       "      <td>PENNY FARTHING BIRTHDAY CARD</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 11:51:00</td>\n",
       "      <td>0.42</td>\n",
       "      <td>17984.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>658</th>\n",
       "      <td>489529</td>\n",
       "      <td>22036</td>\n",
       "      <td>DINOSAUR BIRTHDAY CARD</td>\n",
       "      <td>12</td>\n",
       "      <td>2009-12-01 11:51:00</td>\n",
       "      <td>0.42</td>\n",
       "      <td>17984.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Invoice StockCode                        Description  Quantity  \\\n",
       "371  489517     21912           VINTAGE SNAKES & LADDERS         1   \n",
       "383  489517     22130   PARTY CONE CHRISTMAS DECORATION          6   \n",
       "384  489517     22319  HAIRCLIPS FORTIES FABRIC ASSORTED        12   \n",
       "385  489517     21913     VINTAGE SEASIDE JIGSAW PUZZLES         1   \n",
       "386  489517     21821   GLITTER STAR GARLAND WITH BELLS          1   \n",
       "390  489517    84951A    S/4 PISTACHIO LOVEBIRD COASTERS         1   \n",
       "391  489517     21491    SET OF THREE VINTAGE GIFT WRAPS         1   \n",
       "394  489517     21912           VINTAGE SNAKES & LADDERS         1   \n",
       "657  489529     22028       PENNY FARTHING BIRTHDAY CARD        12   \n",
       "658  489529     22036             DINOSAUR BIRTHDAY CARD        12   \n",
       "\n",
       "             InvoiceDate  Price  Customer ID         Country  \n",
       "371  2009-12-01 11:34:00   3.75      16329.0  United Kingdom  \n",
       "383  2009-12-01 11:34:00   0.85      16329.0  United Kingdom  \n",
       "384  2009-12-01 11:34:00   0.65      16329.0  United Kingdom  \n",
       "385  2009-12-01 11:34:00   3.75      16329.0  United Kingdom  \n",
       "386  2009-12-01 11:34:00   3.75      16329.0  United Kingdom  \n",
       "390  2009-12-01 11:34:00   2.55      16329.0  United Kingdom  \n",
       "391  2009-12-01 11:34:00   1.95      16329.0  United Kingdom  \n",
       "394  2009-12-01 11:34:00   3.75      16329.0  United Kingdom  \n",
       "657  2009-12-01 11:51:00   0.42      17984.0  United Kingdom  \n",
       "658  2009-12-01 11:51:00   0.42      17984.0  United Kingdom  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#viewing duplicated rows\n",
    "retail[retail.duplicated()].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: After manual review, there are indeed lots of duplicated rows (e.g. row 371 and 394). Let's remove them."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "retail = retail.drop_duplicates(keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Invoice             0\n",
       "StockCode           0\n",
       "Description      4275\n",
       "Quantity            0\n",
       "InvoiceDate         0\n",
       "Price               0\n",
       "Customer ID    235151\n",
       "Country             0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#checking missing values\n",
    "retail.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note: there are 234007 rows without Customer ID. Since we are working at the customer level, we cannot aggregate these columns. Let's remove them from further analysis."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "retail = retail[retail['Customer ID'].notna()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Invoice         object\n",
       "StockCode       object\n",
       "Description     object\n",
       "Quantity         int64\n",
       "InvoiceDate     object\n",
       "Price          float64\n",
       "Customer ID    float64\n",
       "Country         object\n",
       "dtype: object"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# checking types of columns\n",
    "retail.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 2: Calculating Cohort Index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "retail['InvoiceDate'] = pd.to_datetime(retail['InvoiceDate'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "retail['YearMonth'] = retail['InvoiceDate'].dt.strftime(\"%Y-%m\")\n",
    "retail['YearMonth'] = pd.to_datetime(retail['YearMonth'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "retail['CohortMonth'] = retail.groupby(by=['Customer ID'])['YearMonth'].transform('min')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_cohort_index(df, transactions_col, cohort_month):\n",
    "    \n",
    "    year_diff = df[transactions_col].dt.year - df[cohort_month].dt.year\n",
    "\n",
    "    month_diff = df[transactions_col].dt.month - df[cohort_month].dt.month\n",
    "    \n",
    "    cohort_index = year_diff * 12 + month_diff + 1\n",
    "    \n",
    "    return cohort_index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "retail['CohortIndex'] = calculate_cohort_index(retail, 'InvoiceDate', 'CohortMonth')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retail['CohortIndex'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Invoice</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>Price</th>\n",
       "      <th>Customer ID</th>\n",
       "      <th>Country</th>\n",
       "      <th>YearMonth</th>\n",
       "      <th>CohortMonth</th>\n",
       "      <th>CohortIndex</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1067366</th>\n",
       "      <td>581587</td>\n",
       "      <td>22899</td>\n",
       "      <td>CHILDREN'S APRON DOLLY GIRL</td>\n",
       "      <td>6</td>\n",
       "      <td>2011-12-09 12:50:00</td>\n",
       "      <td>2.10</td>\n",
       "      <td>12680.0</td>\n",
       "      <td>France</td>\n",
       "      <td>2011-12-01</td>\n",
       "      <td>2011-08-01</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1067367</th>\n",
       "      <td>581587</td>\n",
       "      <td>23254</td>\n",
       "      <td>CHILDRENS CUTLERY DOLLY GIRL</td>\n",
       "      <td>4</td>\n",
       "      <td>2011-12-09 12:50:00</td>\n",
       "      <td>4.15</td>\n",
       "      <td>12680.0</td>\n",
       "      <td>France</td>\n",
       "      <td>2011-12-01</td>\n",
       "      <td>2011-08-01</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1067368</th>\n",
       "      <td>581587</td>\n",
       "      <td>23255</td>\n",
       "      <td>CHILDRENS CUTLERY CIRCUS PARADE</td>\n",
       "      <td>4</td>\n",
       "      <td>2011-12-09 12:50:00</td>\n",
       "      <td>4.15</td>\n",
       "      <td>12680.0</td>\n",
       "      <td>France</td>\n",
       "      <td>2011-12-01</td>\n",
       "      <td>2011-08-01</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1067369</th>\n",
       "      <td>581587</td>\n",
       "      <td>22138</td>\n",
       "      <td>BAKING SET 9 PIECE RETROSPOT</td>\n",
       "      <td>3</td>\n",
       "      <td>2011-12-09 12:50:00</td>\n",
       "      <td>4.95</td>\n",
       "      <td>12680.0</td>\n",
       "      <td>France</td>\n",
       "      <td>2011-12-01</td>\n",
       "      <td>2011-08-01</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1067370</th>\n",
       "      <td>581587</td>\n",
       "      <td>POST</td>\n",
       "      <td>POSTAGE</td>\n",
       "      <td>1</td>\n",
       "      <td>2011-12-09 12:50:00</td>\n",
       "      <td>18.00</td>\n",
       "      <td>12680.0</td>\n",
       "      <td>France</td>\n",
       "      <td>2011-12-01</td>\n",
       "      <td>2011-08-01</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Invoice StockCode                      Description  Quantity  \\\n",
       "1067366  581587     22899     CHILDREN'S APRON DOLLY GIRL          6   \n",
       "1067367  581587     23254    CHILDRENS CUTLERY DOLLY GIRL          4   \n",
       "1067368  581587     23255  CHILDRENS CUTLERY CIRCUS PARADE         4   \n",
       "1067369  581587     22138    BAKING SET 9 PIECE RETROSPOT          3   \n",
       "1067370  581587      POST                          POSTAGE         1   \n",
       "\n",
       "                InvoiceDate  Price  Customer ID Country  YearMonth  \\\n",
       "1067366 2011-12-09 12:50:00   2.10      12680.0  France 2011-12-01   \n",
       "1067367 2011-12-09 12:50:00   4.15      12680.0  France 2011-12-01   \n",
       "1067368 2011-12-09 12:50:00   4.15      12680.0  France 2011-12-01   \n",
       "1067369 2011-12-09 12:50:00   4.95      12680.0  France 2011-12-01   \n",
       "1067370 2011-12-09 12:50:00  18.00      12680.0  France 2011-12-01   \n",
       "\n",
       "        CohortMonth  CohortIndex  \n",
       "1067366  2011-08-01            5  \n",
       "1067367  2011-08-01            5  \n",
       "1067368  2011-08-01            5  \n",
       "1067369  2011-08-01            5  \n",
       "1067370  2011-08-01            5  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retail.tail()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 3: Calculating Average Active Users per Cohort"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "users_in_cohorts = retail.groupby(by=['CohortMonth', 'CohortIndex'])['Customer ID'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CohortMonth</th>\n",
       "      <th>CohortIndex</th>\n",
       "      <th>Customer ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>1</td>\n",
       "      <td>1045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>2</td>\n",
       "      <td>392</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>3</td>\n",
       "      <td>358</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>4</td>\n",
       "      <td>447</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-12-01</td>\n",
       "      <td>5</td>\n",
       "      <td>410</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  CohortMonth  CohortIndex  Customer ID\n",
       "0  2009-12-01            1         1045\n",
       "1  2009-12-01            2          392\n",
       "2  2009-12-01            3          358\n",
       "3  2009-12-01            4          447\n",
       "4  2009-12-01            5          410"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users_in_cohorts = users_in_cohorts.reset_index()\n",
    "users_in_cohorts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>CohortIndex</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "      <th>25</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CohortMonth</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009-12-01</th>\n",
       "      <td>1045.0</td>\n",
       "      <td>392.0</td>\n",
       "      <td>358.0</td>\n",
       "      <td>447.0</td>\n",
       "      <td>410.0</td>\n",
       "      <td>408.0</td>\n",
       "      <td>408.0</td>\n",
       "      <td>374.0</td>\n",
       "      <td>355.0</td>\n",
       "      <td>392.0</td>\n",
       "      <td>...</td>\n",
       "      <td>319.0</td>\n",
       "      <td>273.0</td>\n",
       "      <td>316.0</td>\n",
       "      <td>303.0</td>\n",
       "      <td>287.0</td>\n",
       "      <td>274.0</td>\n",
       "      <td>332.0</td>\n",
       "      <td>319.0</td>\n",
       "      <td>427.0</td>\n",
       "      <td>218.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-01</th>\n",
       "      <td>394.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>119.0</td>\n",
       "      <td>120.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>115.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>91.0</td>\n",
       "      <td>114.0</td>\n",
       "      <td>134.0</td>\n",
       "      <td>...</td>\n",
       "      <td>60.0</td>\n",
       "      <td>86.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>88.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-02-01</th>\n",
       "      <td>363.0</td>\n",
       "      <td>109.0</td>\n",
       "      <td>82.0</td>\n",
       "      <td>110.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>106.0</td>\n",
       "      <td>...</td>\n",
       "      <td>74.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>85.0</td>\n",
       "      <td>90.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-03-01</th>\n",
       "      <td>436.0</td>\n",
       "      <td>95.0</td>\n",
       "      <td>113.0</td>\n",
       "      <td>103.0</td>\n",
       "      <td>100.0</td>\n",
       "      <td>87.0</td>\n",
       "      <td>105.0</td>\n",
       "      <td>130.0</td>\n",
       "      <td>126.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>...</td>\n",
       "      <td>74.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>69.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>89.0</td>\n",
       "      <td>93.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-04-01</th>\n",
       "      <td>291.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>47.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>67.0</td>\n",
       "      <td>79.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>...</td>\n",
       "      <td>43.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-05-01</th>\n",
       "      <td>254.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>48.0</td>\n",
       "      <td>66.0</td>\n",
       "      <td>56.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>...</td>\n",
       "      <td>33.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>42.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-06-01</th>\n",
       "      <td>269.0</td>\n",
       "      <td>58.0</td>\n",
       "      <td>53.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>62.0</td>\n",
       "      <td>76.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>32.0</td>\n",
       "      <td>...</td>\n",
       "      <td>33.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-07-01</th>\n",
       "      <td>183.0</td>\n",
       "      <td>38.0</td>\n",
       "      <td>37.0</td>\n",
       "      <td>52.0</td>\n",
       "      <td>55.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>...</td>\n",
       "      <td>32.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-08-01</th>\n",
       "      <td>158.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>50.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>...</td>\n",
       "      <td>32.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-09-01</th>\n",
       "      <td>242.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>63.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>...</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-10-01</th>\n",
       "      <td>379.0</td>\n",
       "      <td>112.0</td>\n",
       "      <td>61.0</td>\n",
       "      <td>49.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>51.0</td>\n",
       "      <td>54.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-11-01</th>\n",
       "      <td>322.0</td>\n",
       "      <td>68.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>34.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>44.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-12-01</th>\n",
       "      <td>77.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>7.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-01-01</th>\n",
       "      <td>71.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>14.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>9.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-02-01</th>\n",
       "      <td>123.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-03-01</th>\n",
       "      <td>178.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>43.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>40.0</td>\n",
       "      <td>45.0</td>\n",
       "      <td>17.0</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-04-01</th>\n",
       "      <td>105.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>22.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>21.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>13.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-05-01</th>\n",
       "      <td>108.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>19.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>23.0</td>\n",
       "      <td>28.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-06-01</th>\n",
       "      <td>108.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>11.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-07-01</th>\n",
       "      <td>102.0</td>\n",
       "      <td>24.0</td>\n",
       "      <td>31.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>36.0</td>\n",
       "      <td>16.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-08-01</th>\n",
       "      <td>106.0</td>\n",
       "      <td>35.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>29.0</td>\n",
       "      <td>20.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-09-01</th>\n",
       "      <td>187.0</td>\n",
       "      <td>64.0</td>\n",
       "      <td>73.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-01</th>\n",
       "      <td>221.0</td>\n",
       "      <td>74.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-11-01</th>\n",
       "      <td>192.0</td>\n",
       "      <td>33.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-12-01</th>\n",
       "      <td>28.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "CohortIndex      1      2      3      4      5      6      7      8      9   \\\n",
       "CohortMonth                                                                   \n",
       "2009-12-01   1045.0  392.0  358.0  447.0  410.0  408.0  408.0  374.0  355.0   \n",
       "2010-01-01    394.0   86.0  119.0  120.0  110.0  115.0  105.0   91.0  114.0   \n",
       "2010-02-01    363.0  109.0   82.0  110.0   93.0   76.0   79.0  103.0  100.0   \n",
       "2010-03-01    436.0   95.0  113.0  103.0  100.0   87.0  105.0  130.0  126.0   \n",
       "2010-04-01    291.0   67.0   58.0   47.0   54.0   67.0   79.0   76.0   33.0   \n",
       "2010-05-01    254.0   49.0   45.0   49.0   48.0   66.0   56.0   33.0   17.0   \n",
       "2010-06-01    269.0   58.0   53.0   55.0   62.0   76.0   35.0   25.0   22.0   \n",
       "2010-07-01    183.0   38.0   37.0   52.0   55.0   28.0   21.0   28.0   26.0   \n",
       "2010-08-01    158.0   39.0   50.0   51.0   29.0   21.0   16.0   22.0   23.0   \n",
       "2010-09-01    242.0   73.0   63.0   34.0   22.0   25.0   34.0   24.0   30.0   \n",
       "2010-10-01    379.0  112.0   61.0   49.0   35.0   34.0   51.0   54.0   43.0   \n",
       "2010-11-01    322.0   68.0   31.0   34.0   30.0   30.0   44.0   35.0   28.0   \n",
       "2010-12-01     77.0    9.0    4.0    7.0    9.0    6.0    4.0    7.0    5.0   \n",
       "2011-01-01     71.0   15.0   15.0   14.0   15.0   11.0   11.0    9.0    8.0   \n",
       "2011-02-01    123.0   25.0   21.0   23.0   27.0   19.0   21.0   19.0   20.0   \n",
       "2011-03-01    178.0   43.0   43.0   40.0   40.0   30.0   39.0   40.0   45.0   \n",
       "2011-04-01    105.0   28.0   22.0   21.0   21.0   26.0   21.0   27.0   13.0   \n",
       "2011-05-01    108.0   29.0   27.0   19.0   24.0   23.0   28.0   12.0    NaN   \n",
       "2011-06-01    108.0   29.0   24.0   30.0   24.0   31.0   11.0    NaN    NaN   \n",
       "2011-07-01    102.0   24.0   31.0   30.0   36.0   16.0    NaN    NaN    NaN   \n",
       "2011-08-01    106.0   35.0   33.0   29.0   20.0    NaN    NaN    NaN    NaN   \n",
       "2011-09-01    187.0   64.0   73.0   30.0    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-10-01    221.0   74.0   39.0    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-11-01    192.0   33.0    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-12-01     28.0    NaN    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "\n",
       "CohortIndex     10  ...     16     17     18     19     20     21     22  \\\n",
       "CohortMonth         ...                                                    \n",
       "2009-12-01   392.0  ...  319.0  273.0  316.0  303.0  287.0  274.0  332.0   \n",
       "2010-01-01   134.0  ...   60.0   86.0   74.0   69.0   73.0   93.0   73.0   \n",
       "2010-02-01   106.0  ...   74.0   67.0   61.0   53.0   85.0   90.0   62.0   \n",
       "2010-03-01    50.0  ...   74.0   76.0   69.0   74.0   89.0   93.0   33.0   \n",
       "2010-04-01    34.0  ...   43.0   41.0   41.0   50.0   61.0   19.0    NaN   \n",
       "2010-05-01    22.0  ...   33.0   36.0   42.0   40.0   12.0    NaN    NaN   \n",
       "2010-06-01    32.0  ...   33.0   37.0   55.0   16.0    NaN    NaN    NaN   \n",
       "2010-07-01    22.0  ...   32.0   45.0   17.0    NaN    NaN    NaN    NaN   \n",
       "2010-08-01    21.0  ...   32.0   11.0    NaN    NaN    NaN    NaN    NaN   \n",
       "2010-09-01    34.0  ...   13.0    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2010-10-01    35.0  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2010-11-01    29.0  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2010-12-01     2.0  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-01-01    17.0  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-02-01    24.0  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-03-01    17.0  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-04-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-05-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-06-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-07-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-08-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-09-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-10-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-11-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-12-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "\n",
       "CohortIndex     23     24     25  \n",
       "CohortMonth                       \n",
       "2009-12-01   319.0  427.0  218.0  \n",
       "2010-01-01    88.0   29.0    NaN  \n",
       "2010-02-01    23.0    NaN    NaN  \n",
       "2010-03-01     NaN    NaN    NaN  \n",
       "2010-04-01     NaN    NaN    NaN  \n",
       "2010-05-01     NaN    NaN    NaN  \n",
       "2010-06-01     NaN    NaN    NaN  \n",
       "2010-07-01     NaN    NaN    NaN  \n",
       "2010-08-01     NaN    NaN    NaN  \n",
       "2010-09-01     NaN    NaN    NaN  \n",
       "2010-10-01     NaN    NaN    NaN  \n",
       "2010-11-01     NaN    NaN    NaN  \n",
       "2010-12-01     NaN    NaN    NaN  \n",
       "2011-01-01     NaN    NaN    NaN  \n",
       "2011-02-01     NaN    NaN    NaN  \n",
       "2011-03-01     NaN    NaN    NaN  \n",
       "2011-04-01     NaN    NaN    NaN  \n",
       "2011-05-01     NaN    NaN    NaN  \n",
       "2011-06-01     NaN    NaN    NaN  \n",
       "2011-07-01     NaN    NaN    NaN  \n",
       "2011-08-01     NaN    NaN    NaN  \n",
       "2011-09-01     NaN    NaN    NaN  \n",
       "2011-10-01     NaN    NaN    NaN  \n",
       "2011-11-01     NaN    NaN    NaN  \n",
       "2011-12-01     NaN    NaN    NaN  \n",
       "\n",
       "[25 rows x 25 columns]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cohorts_fin = users_in_cohorts.pivot(index='CohortMonth', columns='CohortIndex', values='Customer ID')\n",
    "cohorts_fin"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 4: Calculating Retention Rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "cohort_sizes = cohorts_fin.iloc[:, 0]\n",
    "retention = cohorts_fin.divide(cohort_sizes, axis='rows').round(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>CohortIndex</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "      <th>5</th>\n",
       "      <th>6</th>\n",
       "      <th>7</th>\n",
       "      <th>8</th>\n",
       "      <th>9</th>\n",
       "      <th>10</th>\n",
       "      <th>...</th>\n",
       "      <th>16</th>\n",
       "      <th>17</th>\n",
       "      <th>18</th>\n",
       "      <th>19</th>\n",
       "      <th>20</th>\n",
       "      <th>21</th>\n",
       "      <th>22</th>\n",
       "      <th>23</th>\n",
       "      <th>24</th>\n",
       "      <th>25</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CohortMonth</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2009-12-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.375</td>\n",
       "      <td>0.343</td>\n",
       "      <td>0.428</td>\n",
       "      <td>0.392</td>\n",
       "      <td>0.390</td>\n",
       "      <td>0.390</td>\n",
       "      <td>0.358</td>\n",
       "      <td>0.340</td>\n",
       "      <td>0.375</td>\n",
       "      <td>...</td>\n",
       "      <td>0.305</td>\n",
       "      <td>0.261</td>\n",
       "      <td>0.302</td>\n",
       "      <td>0.290</td>\n",
       "      <td>0.275</td>\n",
       "      <td>0.262</td>\n",
       "      <td>0.318</td>\n",
       "      <td>0.305</td>\n",
       "      <td>0.409</td>\n",
       "      <td>0.209</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.218</td>\n",
       "      <td>0.302</td>\n",
       "      <td>0.305</td>\n",
       "      <td>0.279</td>\n",
       "      <td>0.292</td>\n",
       "      <td>0.266</td>\n",
       "      <td>0.231</td>\n",
       "      <td>0.289</td>\n",
       "      <td>0.340</td>\n",
       "      <td>...</td>\n",
       "      <td>0.152</td>\n",
       "      <td>0.218</td>\n",
       "      <td>0.188</td>\n",
       "      <td>0.175</td>\n",
       "      <td>0.185</td>\n",
       "      <td>0.236</td>\n",
       "      <td>0.185</td>\n",
       "      <td>0.223</td>\n",
       "      <td>0.074</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-02-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.300</td>\n",
       "      <td>0.226</td>\n",
       "      <td>0.303</td>\n",
       "      <td>0.256</td>\n",
       "      <td>0.209</td>\n",
       "      <td>0.218</td>\n",
       "      <td>0.284</td>\n",
       "      <td>0.275</td>\n",
       "      <td>0.292</td>\n",
       "      <td>...</td>\n",
       "      <td>0.204</td>\n",
       "      <td>0.185</td>\n",
       "      <td>0.168</td>\n",
       "      <td>0.146</td>\n",
       "      <td>0.234</td>\n",
       "      <td>0.248</td>\n",
       "      <td>0.171</td>\n",
       "      <td>0.063</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-03-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.218</td>\n",
       "      <td>0.259</td>\n",
       "      <td>0.236</td>\n",
       "      <td>0.229</td>\n",
       "      <td>0.200</td>\n",
       "      <td>0.241</td>\n",
       "      <td>0.298</td>\n",
       "      <td>0.289</td>\n",
       "      <td>0.115</td>\n",
       "      <td>...</td>\n",
       "      <td>0.170</td>\n",
       "      <td>0.174</td>\n",
       "      <td>0.158</td>\n",
       "      <td>0.170</td>\n",
       "      <td>0.204</td>\n",
       "      <td>0.213</td>\n",
       "      <td>0.076</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-04-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.230</td>\n",
       "      <td>0.199</td>\n",
       "      <td>0.162</td>\n",
       "      <td>0.186</td>\n",
       "      <td>0.230</td>\n",
       "      <td>0.271</td>\n",
       "      <td>0.261</td>\n",
       "      <td>0.113</td>\n",
       "      <td>0.117</td>\n",
       "      <td>...</td>\n",
       "      <td>0.148</td>\n",
       "      <td>0.141</td>\n",
       "      <td>0.141</td>\n",
       "      <td>0.172</td>\n",
       "      <td>0.210</td>\n",
       "      <td>0.065</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-05-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.193</td>\n",
       "      <td>0.177</td>\n",
       "      <td>0.193</td>\n",
       "      <td>0.189</td>\n",
       "      <td>0.260</td>\n",
       "      <td>0.220</td>\n",
       "      <td>0.130</td>\n",
       "      <td>0.067</td>\n",
       "      <td>0.087</td>\n",
       "      <td>...</td>\n",
       "      <td>0.130</td>\n",
       "      <td>0.142</td>\n",
       "      <td>0.165</td>\n",
       "      <td>0.157</td>\n",
       "      <td>0.047</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-06-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.216</td>\n",
       "      <td>0.197</td>\n",
       "      <td>0.204</td>\n",
       "      <td>0.230</td>\n",
       "      <td>0.283</td>\n",
       "      <td>0.130</td>\n",
       "      <td>0.093</td>\n",
       "      <td>0.082</td>\n",
       "      <td>0.119</td>\n",
       "      <td>...</td>\n",
       "      <td>0.123</td>\n",
       "      <td>0.138</td>\n",
       "      <td>0.204</td>\n",
       "      <td>0.059</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-07-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.208</td>\n",
       "      <td>0.202</td>\n",
       "      <td>0.284</td>\n",
       "      <td>0.301</td>\n",
       "      <td>0.153</td>\n",
       "      <td>0.115</td>\n",
       "      <td>0.153</td>\n",
       "      <td>0.142</td>\n",
       "      <td>0.120</td>\n",
       "      <td>...</td>\n",
       "      <td>0.175</td>\n",
       "      <td>0.246</td>\n",
       "      <td>0.093</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-08-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.247</td>\n",
       "      <td>0.316</td>\n",
       "      <td>0.323</td>\n",
       "      <td>0.184</td>\n",
       "      <td>0.133</td>\n",
       "      <td>0.101</td>\n",
       "      <td>0.139</td>\n",
       "      <td>0.146</td>\n",
       "      <td>0.133</td>\n",
       "      <td>...</td>\n",
       "      <td>0.203</td>\n",
       "      <td>0.070</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-09-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.302</td>\n",
       "      <td>0.260</td>\n",
       "      <td>0.140</td>\n",
       "      <td>0.091</td>\n",
       "      <td>0.103</td>\n",
       "      <td>0.140</td>\n",
       "      <td>0.099</td>\n",
       "      <td>0.124</td>\n",
       "      <td>0.140</td>\n",
       "      <td>...</td>\n",
       "      <td>0.054</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-10-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.296</td>\n",
       "      <td>0.161</td>\n",
       "      <td>0.129</td>\n",
       "      <td>0.092</td>\n",
       "      <td>0.090</td>\n",
       "      <td>0.135</td>\n",
       "      <td>0.142</td>\n",
       "      <td>0.113</td>\n",
       "      <td>0.092</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-11-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.211</td>\n",
       "      <td>0.096</td>\n",
       "      <td>0.106</td>\n",
       "      <td>0.093</td>\n",
       "      <td>0.093</td>\n",
       "      <td>0.137</td>\n",
       "      <td>0.109</td>\n",
       "      <td>0.087</td>\n",
       "      <td>0.090</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-12-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.117</td>\n",
       "      <td>0.052</td>\n",
       "      <td>0.091</td>\n",
       "      <td>0.117</td>\n",
       "      <td>0.078</td>\n",
       "      <td>0.052</td>\n",
       "      <td>0.091</td>\n",
       "      <td>0.065</td>\n",
       "      <td>0.026</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-01-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.211</td>\n",
       "      <td>0.211</td>\n",
       "      <td>0.197</td>\n",
       "      <td>0.211</td>\n",
       "      <td>0.155</td>\n",
       "      <td>0.155</td>\n",
       "      <td>0.127</td>\n",
       "      <td>0.113</td>\n",
       "      <td>0.239</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-02-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.203</td>\n",
       "      <td>0.171</td>\n",
       "      <td>0.187</td>\n",
       "      <td>0.220</td>\n",
       "      <td>0.154</td>\n",
       "      <td>0.171</td>\n",
       "      <td>0.154</td>\n",
       "      <td>0.163</td>\n",
       "      <td>0.195</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-03-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.242</td>\n",
       "      <td>0.242</td>\n",
       "      <td>0.225</td>\n",
       "      <td>0.225</td>\n",
       "      <td>0.169</td>\n",
       "      <td>0.219</td>\n",
       "      <td>0.225</td>\n",
       "      <td>0.253</td>\n",
       "      <td>0.096</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-04-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.267</td>\n",
       "      <td>0.210</td>\n",
       "      <td>0.200</td>\n",
       "      <td>0.200</td>\n",
       "      <td>0.248</td>\n",
       "      <td>0.200</td>\n",
       "      <td>0.257</td>\n",
       "      <td>0.124</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-05-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.269</td>\n",
       "      <td>0.250</td>\n",
       "      <td>0.176</td>\n",
       "      <td>0.222</td>\n",
       "      <td>0.213</td>\n",
       "      <td>0.259</td>\n",
       "      <td>0.111</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-06-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.269</td>\n",
       "      <td>0.222</td>\n",
       "      <td>0.278</td>\n",
       "      <td>0.222</td>\n",
       "      <td>0.287</td>\n",
       "      <td>0.102</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-07-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.235</td>\n",
       "      <td>0.304</td>\n",
       "      <td>0.294</td>\n",
       "      <td>0.353</td>\n",
       "      <td>0.157</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-08-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.330</td>\n",
       "      <td>0.311</td>\n",
       "      <td>0.274</td>\n",
       "      <td>0.189</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-09-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.342</td>\n",
       "      <td>0.390</td>\n",
       "      <td>0.160</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-10-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.335</td>\n",
       "      <td>0.176</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-11-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>0.172</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2011-12-01</th>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>25 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "CohortIndex   1      2      3      4      5      6      7      8      9   \\\n",
       "CohortMonth                                                                \n",
       "2009-12-01   1.0  0.375  0.343  0.428  0.392  0.390  0.390  0.358  0.340   \n",
       "2010-01-01   1.0  0.218  0.302  0.305  0.279  0.292  0.266  0.231  0.289   \n",
       "2010-02-01   1.0  0.300  0.226  0.303  0.256  0.209  0.218  0.284  0.275   \n",
       "2010-03-01   1.0  0.218  0.259  0.236  0.229  0.200  0.241  0.298  0.289   \n",
       "2010-04-01   1.0  0.230  0.199  0.162  0.186  0.230  0.271  0.261  0.113   \n",
       "2010-05-01   1.0  0.193  0.177  0.193  0.189  0.260  0.220  0.130  0.067   \n",
       "2010-06-01   1.0  0.216  0.197  0.204  0.230  0.283  0.130  0.093  0.082   \n",
       "2010-07-01   1.0  0.208  0.202  0.284  0.301  0.153  0.115  0.153  0.142   \n",
       "2010-08-01   1.0  0.247  0.316  0.323  0.184  0.133  0.101  0.139  0.146   \n",
       "2010-09-01   1.0  0.302  0.260  0.140  0.091  0.103  0.140  0.099  0.124   \n",
       "2010-10-01   1.0  0.296  0.161  0.129  0.092  0.090  0.135  0.142  0.113   \n",
       "2010-11-01   1.0  0.211  0.096  0.106  0.093  0.093  0.137  0.109  0.087   \n",
       "2010-12-01   1.0  0.117  0.052  0.091  0.117  0.078  0.052  0.091  0.065   \n",
       "2011-01-01   1.0  0.211  0.211  0.197  0.211  0.155  0.155  0.127  0.113   \n",
       "2011-02-01   1.0  0.203  0.171  0.187  0.220  0.154  0.171  0.154  0.163   \n",
       "2011-03-01   1.0  0.242  0.242  0.225  0.225  0.169  0.219  0.225  0.253   \n",
       "2011-04-01   1.0  0.267  0.210  0.200  0.200  0.248  0.200  0.257  0.124   \n",
       "2011-05-01   1.0  0.269  0.250  0.176  0.222  0.213  0.259  0.111    NaN   \n",
       "2011-06-01   1.0  0.269  0.222  0.278  0.222  0.287  0.102    NaN    NaN   \n",
       "2011-07-01   1.0  0.235  0.304  0.294  0.353  0.157    NaN    NaN    NaN   \n",
       "2011-08-01   1.0  0.330  0.311  0.274  0.189    NaN    NaN    NaN    NaN   \n",
       "2011-09-01   1.0  0.342  0.390  0.160    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-10-01   1.0  0.335  0.176    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-11-01   1.0  0.172    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-12-01   1.0    NaN    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "\n",
       "CohortIndex     10  ...     16     17     18     19     20     21     22  \\\n",
       "CohortMonth         ...                                                    \n",
       "2009-12-01   0.375  ...  0.305  0.261  0.302  0.290  0.275  0.262  0.318   \n",
       "2010-01-01   0.340  ...  0.152  0.218  0.188  0.175  0.185  0.236  0.185   \n",
       "2010-02-01   0.292  ...  0.204  0.185  0.168  0.146  0.234  0.248  0.171   \n",
       "2010-03-01   0.115  ...  0.170  0.174  0.158  0.170  0.204  0.213  0.076   \n",
       "2010-04-01   0.117  ...  0.148  0.141  0.141  0.172  0.210  0.065    NaN   \n",
       "2010-05-01   0.087  ...  0.130  0.142  0.165  0.157  0.047    NaN    NaN   \n",
       "2010-06-01   0.119  ...  0.123  0.138  0.204  0.059    NaN    NaN    NaN   \n",
       "2010-07-01   0.120  ...  0.175  0.246  0.093    NaN    NaN    NaN    NaN   \n",
       "2010-08-01   0.133  ...  0.203  0.070    NaN    NaN    NaN    NaN    NaN   \n",
       "2010-09-01   0.140  ...  0.054    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2010-10-01   0.092  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2010-11-01   0.090  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2010-12-01   0.026  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-01-01   0.239  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-02-01   0.195  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-03-01   0.096  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-04-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-05-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-06-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-07-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-08-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-09-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-10-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-11-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "2011-12-01     NaN  ...    NaN    NaN    NaN    NaN    NaN    NaN    NaN   \n",
       "\n",
       "CohortIndex     23     24     25  \n",
       "CohortMonth                       \n",
       "2009-12-01   0.305  0.409  0.209  \n",
       "2010-01-01   0.223  0.074    NaN  \n",
       "2010-02-01   0.063    NaN    NaN  \n",
       "2010-03-01     NaN    NaN    NaN  \n",
       "2010-04-01     NaN    NaN    NaN  \n",
       "2010-05-01     NaN    NaN    NaN  \n",
       "2010-06-01     NaN    NaN    NaN  \n",
       "2010-07-01     NaN    NaN    NaN  \n",
       "2010-08-01     NaN    NaN    NaN  \n",
       "2010-09-01     NaN    NaN    NaN  \n",
       "2010-10-01     NaN    NaN    NaN  \n",
       "2010-11-01     NaN    NaN    NaN  \n",
       "2010-12-01     NaN    NaN    NaN  \n",
       "2011-01-01     NaN    NaN    NaN  \n",
       "2011-02-01     NaN    NaN    NaN  \n",
       "2011-03-01     NaN    NaN    NaN  \n",
       "2011-04-01     NaN    NaN    NaN  \n",
       "2011-05-01     NaN    NaN    NaN  \n",
       "2011-06-01     NaN    NaN    NaN  \n",
       "2011-07-01     NaN    NaN    NaN  \n",
       "2011-08-01     NaN    NaN    NaN  \n",
       "2011-09-01     NaN    NaN    NaN  \n",
       "2011-10-01     NaN    NaN    NaN  \n",
       "2011-11-01     NaN    NaN    NaN  \n",
       "2011-12-01     NaN    NaN    NaN  \n",
       "\n",
       "[25 rows x 25 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "retention"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 5: Calculate Basic CLV (revenue-based)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Basic CLV** = Average Revenue * Average Lifespan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "retail['PurchaseSize'] = retail['Quantity']*retail['Price']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "603.4894708998612"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate monthly spend per customer\n",
    "monthly_revenue = retail.groupby(by=['Customer ID', 'YearMonth'])['PurchaseSize'].sum().mean()\n",
    "monthly_revenue"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Average Lifespan can be inferred by looking into the average time it takes for customers to churn from the time they made their first purchase. For now, we assume that the customer lifespan is 3 years or 36 months."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "lifespan_months = 36"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "21725.620952395002"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CLV_basic = monthly_revenue * lifespan_months\n",
    "CLV_basic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Basic Customer Lifetime Value is 21725.62 USD."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 6: Calculate Granular CLV (revenue-based)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Granular CLV** = (Average Revenue per Transaction * Average Frequency) * Average Lifespan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "31.16675777794161"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# first mean() averages the revenue per invoice \n",
    "# second mean() averages the revenue per purchase\n",
    "revenue_per_purchase = retail.groupby(by=['Invoice'])['PurchaseSize'].mean().mean()\n",
    "revenue_per_purchase"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.6625050939132369"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate average number of unique invoices per customer each month\n",
    "avg_frequency_per_month = retail.groupby(by=['Customer ID', 'YearMonth'])['Invoice'].nunique().mean()\n",
    "avg_frequency_per_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1865.336168397165"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CLV_granular = revenue_per_purchase * avg_frequency_per_month * lifespan_months\n",
    "CLV_granular"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Granular CLV is 1865.34 USD, with each customer making on average 1.66 transactions per month and spending about 31 USD per transaction."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Step 7: Calculate Traditional CLV (revenue-based)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Traditional CLV method doens't require to define customer lifespan and instead uses retention to churn rate to access customer life expectancy.\n",
    "\n",
    "**Traditional CLV** = Average Revenue * Retention Rate / Churn Rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "603.4894708998612"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate monthly spend per customer\n",
    "monthly_revenue = retail.groupby(by=['Customer ID', 'YearMonth'])['PurchaseSize'].sum().mean()\n",
    "monthly_revenue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.1901439413052479"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#calculate average retention rate per customer\n",
    "avg_retention = retention.iloc[:, 1:].mean().mean()\n",
    "avg_retention"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.8098560586947521"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "avg_churn = 1 - avg_retention\n",
    "avg_churn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "141.69168125760655"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "CLV_traditional = monthly_revenue * (avg_retention/avg_churn)\n",
    "CLV_traditional"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average CLV: 141.69 USD at 19.0% average retention rate and 603.49 USD average monthly revenue\n"
     ]
    }
   ],
   "source": [
    "print(\"Average CLV: {:.2f} USD at {:.1%} average retention rate and {:.2f} USD average monthly revenue\".format(CLV_traditional, avg_retention, monthly_revenue))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Conclusion"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We calculated three types Lifetime Customer Values - Basic, Granular and Traditional. Basic LCV gave unrealistically high LCV - 21725.62 USD per customer. Granular CLV is much lower - with only 1865.33 USD per customer. Still, both Basic and Traditional CLV relied on an arbitrary value of lifespan per customer, which we assumed here to be 3 years. <br>\n",
    "The Traditional CLV, however, gave a more realistical number - only 141.69 USD per customer and was based on the real retention to churn ratio as a proxy for the customer lifespan. <br>\n",
    "Still, the traditional CLV method assumed that the churn is final, i.e. customers that churn do not come back later. Hence, it might underreport actual CLV, especially with low retention rates as in our case (19%)."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Acknowledgement"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Data Source:** Online Retail II Data Set, UCI Machine Learning Repository, http://archive.ics.uci.edu/ml/datasets/Online+Retail+II\n",
    "\n",
    "Analysis done after completing the course \"Customer Segmentation in Python\" on DataCamp (instructor - \n",
    "Karolis Urbonas)\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}