Chapter2-Recency_Frequency_Monetary_Value_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Recency, Frequency, Monetary Value analysis\n",
    "\n",
    "Understand customers based on their unique behavioral attributes.\n",
    "\n",
    "### Group customers into cohorts and analyze their behavior over time.\n",
    "In this chapter, we will dive into a very popular technique called RFM segmentation, which stands for Recency, Frequency and Monetary value segmentation.\n",
    "\n",
    "### What is RFM segmentation?\n",
    "Behavioral customer segmentation based on three metrics:\n",
    "\t- Recency (R): measure how recent was each customer's last purchase, \n",
    "\t- Frequency (F): measure how many purchases the customer has done in the last 12 months\n",
    "\t- Monetary Value (M): measures how much has the customer spent in the last 12 months\n",
    "\n",
    "we will use these values to assign customers to RFM segments. Once we have calculated these numbers, the next step is to group them into some sort of categorization such as high, medium and low. (Hay muchas formas de hacer esto)\n",
    "\n",
    "### Grouping RFM values\n",
    "\n",
    "The RFM values can be grouped in several ways:\n",
    "\t- Percentiles e.g. quantiles: We can break customers into groups of equal size based on percentile values of each metric\n",
    "\t- Pareto 80/20 cut: We can assign either high or low value to each metric based on a 80/20% Pareto split\n",
    "\t- Custom - based on business knowledge: we can use existing knowledge from previous business insights about certain threshold values for each metric\n",
    "\n",
    "We are going to implement percentile-based grouping.\n",
    "\n",
    "### Short review of percentiles\n",
    "\n",
    "Process of calculating percentiles:\n",
    "\t1. Sort customers based on that metric\n",
    "\t2. Break customers into a pre-defined number of groups of equal size\n",
    "\t3. Assign a label to each group\n",
    "\n",
    "Pandas function for calculating percentiles: qcut()\n",
    "\n",
    "### Calculate percentiles with Python\n",
    "Data with eight CustomerID and a randomly calculated Spend values.\n",
    "We are going to implement percentile-based grouping."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_example = {'CustomerID': [0,1,2,3,4,5,6,7], 'Spend': [137,335,172,355,303,233,244,229]}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "data = pd.DataFrame(data_example)"
   ]
  },
  {
   "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>CustomerID</th>\n",
       "      <th>Spend</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>137</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>335</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>355</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>303</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>244</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>229</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID  Spend\n",
       "0           0    137\n",
       "1           1    335\n",
       "2           2    172\n",
       "3           3    355\n",
       "4           4    303\n",
       "5           5    233\n",
       "6           6    244\n",
       "7           7    229"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   CustomerID  Spend Spend_Quartile\n",
      "0           0    137              1\n",
      "2           2    172              1\n",
      "7           7    229              2\n",
      "5           5    233              2\n",
      "6           6    244              3\n",
      "4           4    303              3\n",
      "1           1    335              4\n",
      "3           3    355              4\n"
     ]
    }
   ],
   "source": [
    "# Create a spend quartile with 4 groups and labels ranging from 1 through 4 \n",
    "spend_quartile = pd.qcut(data['Spend'], q=4, labels=range(1,5))\n",
    "\n",
    "# Assign the quartile values to the Spend_Quartile column in data\n",
    "data['Spend_Quartile'] = spend_quartile\n",
    "\n",
    "# Print data with sorted Spend values\n",
    "print(data.sort_values('Spend'))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Calculate Recency deciles (q=10)**\n",
    "\n",
    "We have created a dataset for you with random CustomerID and Recency_Days values as data. You will now use this dataset to group customers into quartiles based on Recency_Days values and assign labels to each of them.\n",
    "\n",
    "Be cautious about the labels for this exercise. You will see that the labels are inverse, and will required one additional step in separately creating them. If you need to refresh your memory on the process of creating the labels, check out the slides!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.DataFrame({'CustomerID': [0,1,2,3,4,5,6,7], 'Recency_Days': [37,235,396,72,255,393,203,133]})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Store labels from 4 to 1 in a decreasing order\n",
    "r_labels = list(range(4, 0, -1))\n",
    "\n",
    "# Create a spend quartile with 4 groups and pass the previously created labels \n",
    "recency_quartiles = pd.qcut(data['Recency_Days'], q=4, labels=r_labels)\n",
    "\n",
    "# Assign the quartile values to the Recency_Quartile column in `data`\n",
    "data['Recency_Quartile'] = recency_quartiles "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "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>CustomerID</th>\n",
       "      <th>Recency_Days</th>\n",
       "      <th>Recency_Quartile</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>37</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>133</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>203</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>235</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>255</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>393</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>396</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID  Recency_Days Recency_Quartile\n",
       "0           0            37                4\n",
       "3           3            72                4\n",
       "7           7           133                3\n",
       "6           6           203                3\n",
       "1           1           235                2\n",
       "4           4           255                2\n",
       "5           5           393                1\n",
       "2           2           396                1"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.sort_values('Recency_Days')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>CustomerID</th>\n",
       "      <th>Recency_Days</th>\n",
       "      <th>Recency_Quartile</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>37</td>\n",
       "      <td>Active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>72</td>\n",
       "      <td>Active</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>7</td>\n",
       "      <td>133</td>\n",
       "      <td>Lapsed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>203</td>\n",
       "      <td>Lapsed</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>235</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>255</td>\n",
       "      <td>Inactive</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>393</td>\n",
       "      <td>Churned</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>396</td>\n",
       "      <td>Churned</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CustomerID  Recency_Days Recency_Quartile\n",
       "0           0            37           Active\n",
       "3           3            72           Active\n",
       "7           7           133           Lapsed\n",
       "6           6           203           Lapsed\n",
       "1           1           235         Inactive\n",
       "4           4           255         Inactive\n",
       "5           5           393          Churned\n",
       "2           2           396          Churned"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Create string labels\n",
    "r_labels = ['Active', 'Lapsed', 'Inactive', 'Churned']\n",
    "\n",
    "# Divide into groups based on quartiles\n",
    "recency_quartiles = pd.qcut(data['Recency_Days'], q=4, labels=r_labels)\n",
    "\n",
    "# Create new column\n",
    "data['Recency_Quartile'] = recency_quartiles\n",
    "\n",
    "# Sort values from lowest to highest\n",
    "data.sort_values('Recency_Days')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Recency, Frequency, Monetary Value calculation\n",
    "\n",
    "(Calculate metrics for each customer).\n",
    "\n",
    "**Definitions**\n",
    "\t- Recency - it's the number of days since last customer transaction - the lower it is, the better, since every company wants ots customers to be recent and active. \n",
    "\t- Frequency - calculates the number of transactions in the last 12 months, although there are variations such as average monthly transactions which depict the essence of this metric as well\n",
    "\t- Monetary Value - total spend in the last 12 months\n",
    "\n",
    "One comment though - the 12 months is a standard way to do this, but it can be chosen arbitrarily depending on the business model and the lifecycle of the products and customers.\n",
    "\n",
    "\n",
    "**Dataset and preparations**\n",
    "\t- Same online dataset like in the previous lessons\n",
    "\t- Need to do some data preparation before calculating the RFM value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import datetime as dt\n",
    "\n",
    "online = pd.read_csv('Online Retail.csv', sep=';')\n",
    "\n",
    "# convert object to datetime\n",
    "online['InvoiceDate'] = pd.to_datetime(online['InvoiceDate'])\n",
    "\n",
    "# convert object to float\n",
    "online['UnitPrice'] = online['UnitPrice'].apply(lambda x: x.replace(',', '.'))\n",
    "online['UnitPrice'] = online['UnitPrice'].apply(lambda col:pd.to_numeric(col, errors='coerce'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>InvoiceNo</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>UnitPrice</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>536365</td>\n",
       "      <td>85123A</td>\n",
       "      <td>WHITE HANGING HEART T-LIGHT HOLDER</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-01-12 08:26:00</td>\n",
       "      <td>2.55</td>\n",
       "      <td>17850.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>536365</td>\n",
       "      <td>71053</td>\n",
       "      <td>WHITE METAL LANTERN</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-01-12 08:26:00</td>\n",
       "      <td>3.39</td>\n",
       "      <td>17850.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>536365</td>\n",
       "      <td>84406B</td>\n",
       "      <td>CREAM CUPID HEARTS COAT HANGER</td>\n",
       "      <td>8</td>\n",
       "      <td>2010-01-12 08:26:00</td>\n",
       "      <td>2.75</td>\n",
       "      <td>17850.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>536365</td>\n",
       "      <td>84029G</td>\n",
       "      <td>KNITTED UNION FLAG HOT WATER BOTTLE</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-01-12 08:26:00</td>\n",
       "      <td>3.39</td>\n",
       "      <td>17850.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>536365</td>\n",
       "      <td>84029E</td>\n",
       "      <td>RED WOOLLY HOTTIE WHITE HEART.</td>\n",
       "      <td>6</td>\n",
       "      <td>2010-01-12 08:26:00</td>\n",
       "      <td>3.39</td>\n",
       "      <td>17850.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  InvoiceNo StockCode                          Description  Quantity  \\\n",
       "0    536365    85123A   WHITE HANGING HEART T-LIGHT HOLDER         6   \n",
       "1    536365     71053                  WHITE METAL LANTERN         6   \n",
       "2    536365    84406B       CREAM CUPID HEARTS COAT HANGER         8   \n",
       "3    536365    84029G  KNITTED UNION FLAG HOT WATER BOTTLE         6   \n",
       "4    536365    84029E       RED WOOLLY HOTTIE WHITE HEART.         6   \n",
       "\n",
       "          InvoiceDate  UnitPrice  CustomerID         Country  \n",
       "0 2010-01-12 08:26:00       2.55     17850.0  United Kingdom  \n",
       "1 2010-01-12 08:26:00       3.39     17850.0  United Kingdom  \n",
       "2 2010-01-12 08:26:00       2.75     17850.0  United Kingdom  \n",
       "3 2010-01-12 08:26:00       3.39     17850.0  United Kingdom  \n",
       "4 2010-01-12 08:26:00       3.39     17850.0  United Kingdom  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "online.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "online = online.loc[(online['InvoiceDate'] >= '2010-12-10') & (online['InvoiceDate'] < '2011-12-13')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>InvoiceNo</th>\n",
       "      <th>StockCode</th>\n",
       "      <th>Description</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>InvoiceDate</th>\n",
       "      <th>UnitPrice</th>\n",
       "      <th>CustomerID</th>\n",
       "      <th>Country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25281</th>\n",
       "      <td>538365</td>\n",
       "      <td>22469</td>\n",
       "      <td>HEART OF WICKER SMALL</td>\n",
       "      <td>8</td>\n",
       "      <td>2010-12-12 10:11:00</td>\n",
       "      <td>1.65</td>\n",
       "      <td>17243.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25282</th>\n",
       "      <td>538365</td>\n",
       "      <td>84030E</td>\n",
       "      <td>ENGLISH ROSE HOT WATER BOTTLE</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-12-12 10:11:00</td>\n",
       "      <td>4.25</td>\n",
       "      <td>17243.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25283</th>\n",
       "      <td>538365</td>\n",
       "      <td>22112</td>\n",
       "      <td>CHOCOLATE HOT WATER BOTTLE</td>\n",
       "      <td>3</td>\n",
       "      <td>2010-12-12 10:11:00</td>\n",
       "      <td>4.95</td>\n",
       "      <td>17243.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25284</th>\n",
       "      <td>538365</td>\n",
       "      <td>22835</td>\n",
       "      <td>HOT WATER BOTTLE I AM SO POORLY</td>\n",
       "      <td>5</td>\n",
       "      <td>2010-12-12 10:11:00</td>\n",
       "      <td>4.65</td>\n",
       "      <td>17243.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25285</th>\n",
       "      <td>538365</td>\n",
       "      <td>84029E</td>\n",
       "      <td>RED WOOLLY HOTTIE WHITE HEART.</td>\n",
       "      <td>4</td>\n",
       "      <td>2010-12-12 10:11:00</td>\n",
       "      <td>3.75</td>\n",
       "      <td>17243.0</td>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      InvoiceNo StockCode                      Description  Quantity  \\\n",
       "25281    538365     22469            HEART OF WICKER SMALL         8   \n",
       "25282    538365    84030E    ENGLISH ROSE HOT WATER BOTTLE         1   \n",
       "25283    538365     22112       CHOCOLATE HOT WATER BOTTLE         3   \n",
       "25284    538365     22835  HOT WATER BOTTLE I AM SO POORLY         5   \n",
       "25285    538365    84029E   RED WOOLLY HOTTIE WHITE HEART.         4   \n",
       "\n",
       "              InvoiceDate  UnitPrice  CustomerID         Country  \n",
       "25281 2010-12-12 10:11:00       1.65     17243.0  United Kingdom  \n",
       "25282 2010-12-12 10:11:00       4.25     17243.0  United Kingdom  \n",
       "25283 2010-12-12 10:11:00       4.95     17243.0  United Kingdom  \n",
       "25284 2010-12-12 10:11:00       4.65     17243.0  United Kingdom  \n",
       "25285 2010-12-12 10:11:00       3.75     17243.0  United Kingdom  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "online.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Min:2010-12-12 10:11:00; Max:2011-12-10 17:19:00\n"
     ]
    }
   ],
   "source": [
    "print('Min:{}; Max:{}'.format(min(online.InvoiceDate),\n",
    "                              max(online.InvoiceDate)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's create a hypothetical snapshot_day data as if we're doing analysis recently\n",
    "snapshot_date = max(online.InvoiceDate) + dt.timedelta(days=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2011-12-11 17:19:00')"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "snapshot_date"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Building RFM segments**\n",
    "\n",
    "- Cuartiles: 4 segmentos del mismo tamaño\n",
    "- Recientes: ordenar los clientes en orden descentente, porque se considera mejor cuando el valor es menor\n",
    "- Frecuency and monetary quartiles: ordenar en forma ascentender porque se considera mejor cuando el valor es más alto."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The online dataset has already been pre -processed and only includes the recent 12 months of data. In the real world, we would be working with the most recent snapshot of the data of today or yesterday, but in this case the data comes from 2010 and 2011, so we have to create hypothetical snapshot date that we'll use as a starting point to calculate metrics as if we're doing the analysis on the most recent data.\n",
    "\n",
    "With days equal=1 argument we create a period of 1 day which we can then add to our date.\n",
    "\n",
    "We aggregate the data on a Customer level, and calculate three metrics: we used the InvoceDate and pass it to the lambda function, and then take a difference between our snapshot date - which would be today in the real world - and the most recent or max() invoice date, this fives us the number of days between hypothetical today and the last transaction."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Nota:\n",
    "Arreglar el siguiente agrupamiento como funciones separadas para poder crear las funciones nuevas.\n",
    "\n",
    "https://code.i-harness.com/es/q/1231cb5\n",
    "\n",
    "https://www.e-learn.cn/content/wangluowenzhang/79068\n",
    "\n",
    "https://stackoverrun.com/es/q/12261730"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aggregate data on a customer level\n",
    "datamart = online.groupby(['CustomerID']).agg({\n",
    "        'InvoiceDate': lambda x: (snapshot_date - x.max()).days,\n",
    "        'InvoiceNo': 'count',\n",
    "        'TotalSum': 'sum'})\n",
    "\n",
    "# Rename columns for easier interpretation\n",
    "datamart.rename(columns = {'InvoiceDate': 'Recency',\n",
    "        'InvoiceNo': 'Frequency',\n",
    "        'TotalSum': 'MonetaryValue'}, inplace=True)\n",
    "\n",
    "# Check the first rows\n",
    "datamart.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create labels for Recency and Frequency\n",
    "r_labels = range(3, 0, -1); f_labels = range(1, 4)\n",
    "\n",
    "# Assign these labels to three equal percentile groups \n",
    "r_groups = pd.qcut(datamart['Recency'], q=3, labels=r_labels)\n",
    "\n",
    "# Assign these labels to three equal percentile groups \n",
    "f_groups = pd.qcut(datamart['Frequency'], q=3, labels=f_labels)\n",
    "\n",
    "# Create new columns R and F \n",
    "datamart = datamart.assign(R=r_groups.values, F=f_groups.values)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calcular puntuación RFM\n",
    "\n",
    "Asignar a los clientes tres grupos según los percentiles de Valor Monetario y luego calculará un RFM_Score, que es una suma de los valores R, F y M."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create labels for MonetaryValue\n",
    "m_labels = range(1, 4)\n",
    "\n",
    "# Assign these labels to three equal percentile groups \n",
    "m_groups = pd.qcut(datamart['MonetaryValue'], q=3, labels=m_labels)\n",
    "\n",
    "# Create new column M\n",
    "datamart = datamart.assign(M=m_groups.values)\n",
    "\n",
    "# Calculate RFM_Score\n",
    "datamart['RFM_Score'] = datamart[['R','F','M']].sum(axis=1)\n",
    "print(datamart['RFM_Score'].head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Create segments named Top, Middle, Low. If the RFM score is greater than or equal to 10, the level should be \"Top\". If it's between 6 and 10 it should be \"Middle\", and otherwise it should be \"Low\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Define rfm_level function\n",
    "def rfm_level(df):\n",
    "    if df['RFM_Score'] >= 10:\n",
    "        return 'Top'\n",
    "    elif (df['RFM_Score'] >= 6) and (df['RFM_Score'] < 10):\n",
    "        return 'Middle'\n",
    "    else:\n",
    "        return 'Low'\n",
    "\n",
    "# Create a new variable RFM_Level\n",
    "datamart['RFM_Level'] = datamart.apply(rfm_level, axis=1)\n",
    "\n",
    "# Print the header with top 5 rows to the console\n",
    "print(datamart.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Calculate average values for each RFM_Level, and return a size of each segment \n",
    "rfm_level_agg = datamart.groupby('RFM_Level').agg({\n",
    "    'Recency': 'mean',\n",
    "    'Frequency': 'mean',\n",
    "  \n",
    "    # Return the size of each segment\n",
    "    'MonetaryValue': ['mean', 'count']\n",
    "}).round(1)\n",
    "\n",
    "# Print the aggregated dataset\n",
    "print(rfm_level_agg)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Frequency MonetaryValue       Recency\n",
    "               mean          mean count    mean\n",
    "RFM_Level                                      \n",
    "Low             3.2          52.7  1075   180.8\n",
    "Middle         10.7         202.9  1547    73.9\n",
    "Top            47.1         959.7  1021    20.3\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}