Customer-analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "            order_date        order_id          customer  grand_total\n0  2009-07-11 00:00:00  CA-2011-100006       Dennis Kane          378\n1  2007-08-11 00:00:00  CA-2011-100090        Ed Braxton          699\n2              3/14/11  CA-2011-100293  Neil Franzڑsisch           91\n3              1/29/11  CA-2011-100328   Jasper Cacioppo            4\n4  2004-08-11 00:00:00  CA-2011-100363       Jim Mitchum           21",
      "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>order_date</th>\n      <th>order_id</th>\n      <th>customer</th>\n      <th>grand_total</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0</th>\n      <td>2009-07-11 00:00:00</td>\n      <td>CA-2011-100006</td>\n      <td>Dennis Kane</td>\n      <td>378</td>\n    </tr>\n    <tr>\n      <th>1</th>\n      <td>2007-08-11 00:00:00</td>\n      <td>CA-2011-100090</td>\n      <td>Ed Braxton</td>\n      <td>699</td>\n    </tr>\n    <tr>\n      <th>2</th>\n      <td>3/14/11</td>\n      <td>CA-2011-100293</td>\n      <td>Neil Franzڑsisch</td>\n      <td>91</td>\n    </tr>\n    <tr>\n      <th>3</th>\n      <td>1/29/11</td>\n      <td>CA-2011-100328</td>\n      <td>Jasper Cacioppo</td>\n      <td>4</td>\n    </tr>\n    <tr>\n      <th>4</th>\n      <td>2004-08-11 00:00:00</td>\n      <td>CA-2011-100363</td>\n      <td>Jim Mitchum</td>\n      <td>21</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders = pd.read_excel('Custormer-orders.xlsx',sheet_name='Custormer-orders', index_col=None)\n",
    "orders.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#   Create Table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as dt\n",
    "NOW = dt.datetime(2014,12,31)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Convert Date\n",
    "orders['order_date'] = pd.to_datetime(orders['order_date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "rfmTable = orders.groupby('customer').agg({'order_date': lambda x: (NOW - x.max()).days, # Recency\n",
    "                                        'order_id': lambda x: len(x),      # Frequency\n",
    "                                        'grand_total': lambda x: x.sum()}) # Monetary Value\n",
    "\n",
    "rfmTable['order_date'] = rfmTable['order_date'].astype(int)\n",
    "rfmTable.rename(columns={'order_date': 'recency', \n",
    "                         'order_id': 'frequency', \n",
    "                         'grand_total': 'monetary_value'}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "                 recency  frequency  monetary_value\ncustomer                                           \nAaron Bergman       1144          3             887\nAaron Hawkins         12          7            1744\nAaron Smayling       461          7            3050\nAdam Bellavance      105          8            7756\nAdam Hart             34         10            3249",
      "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>recency</th>\n      <th>frequency</th>\n      <th>monetary_value</th>\n    </tr>\n    <tr>\n      <th>customer</th>\n      <th></th>\n      <th></th>\n      <th></th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>Aaron Bergman</th>\n      <td>1144</td>\n      <td>3</td>\n      <td>887</td>\n    </tr>\n    <tr>\n      <th>Aaron Hawkins</th>\n      <td>12</td>\n      <td>7</td>\n      <td>1744</td>\n    </tr>\n    <tr>\n      <th>Aaron Smayling</th>\n      <td>461</td>\n      <td>7</td>\n      <td>3050</td>\n    </tr>\n    <tr>\n      <th>Adam Bellavance</th>\n      <td>105</td>\n      <td>8</td>\n      <td>7756</td>\n    </tr>\n    <tr>\n      <th>Adam Hart</th>\n      <td>34</td>\n      <td>10</td>\n      <td>3249</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfmTable.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "     order_date        order_id   customer  grand_total\n703  2011-11-16  CA-2011-160066  Adam Hart            5\n1606 2012-12-21  CA-2012-161795  Adam Hart            3\n2371 2013-06-27  CA-2013-144015  Adam Hart          262\n2429 2013-12-18  CA-2013-147109  Adam Hart          217\n2473 2013-09-16  CA-2013-149797  Adam Hart          842\n3045 2014-09-26  CA-2014-112004  Adam Hart          199\n3175 2014-04-16  CA-2014-118857  Adam Hart          196\n3315 2014-10-24  CA-2014-125451  Adam Hart         1170\n3719 2014-05-20  CA-2014-145702  Adam Hart          342\n4101 2014-11-27  CA-2014-165029  Adam Hart           13",
      "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>order_date</th>\n      <th>order_id</th>\n      <th>customer</th>\n      <th>grand_total</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>703</th>\n      <td>2011-11-16</td>\n      <td>CA-2011-160066</td>\n      <td>Adam Hart</td>\n      <td>5</td>\n    </tr>\n    <tr>\n      <th>1606</th>\n      <td>2012-12-21</td>\n      <td>CA-2012-161795</td>\n      <td>Adam Hart</td>\n      <td>3</td>\n    </tr>\n    <tr>\n      <th>2371</th>\n      <td>2013-06-27</td>\n      <td>CA-2013-144015</td>\n      <td>Adam Hart</td>\n      <td>262</td>\n    </tr>\n    <tr>\n      <th>2429</th>\n      <td>2013-12-18</td>\n      <td>CA-2013-147109</td>\n      <td>Adam Hart</td>\n      <td>217</td>\n    </tr>\n    <tr>\n      <th>2473</th>\n      <td>2013-09-16</td>\n      <td>CA-2013-149797</td>\n      <td>Adam Hart</td>\n      <td>842</td>\n    </tr>\n    <tr>\n      <th>3045</th>\n      <td>2014-09-26</td>\n      <td>CA-2014-112004</td>\n      <td>Adam Hart</td>\n      <td>199</td>\n    </tr>\n    <tr>\n      <th>3175</th>\n      <td>2014-04-16</td>\n      <td>CA-2014-118857</td>\n      <td>Adam Hart</td>\n      <td>196</td>\n    </tr>\n    <tr>\n      <th>3315</th>\n      <td>2014-10-24</td>\n      <td>CA-2014-125451</td>\n      <td>Adam Hart</td>\n      <td>1170</td>\n    </tr>\n    <tr>\n      <th>3719</th>\n      <td>2014-05-20</td>\n      <td>CA-2014-145702</td>\n      <td>Adam Hart</td>\n      <td>342</td>\n    </tr>\n    <tr>\n      <th>4101</th>\n      <td>2014-11-27</td>\n      <td>CA-2014-165029</td>\n      <td>Adam Hart</td>\n      <td>13</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# customer\n",
    "hart = orders[orders['customer']=='Adam Hart']\n",
    "hart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "True"
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(NOW - dt.datetime(2014,11,27)).days==34"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "      recency  frequency  monetary_value\n0.25     44.0        5.0          1145.0\n0.50    154.0        6.0          2257.0\n0.75    404.0        8.0          3784.0",
      "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>recency</th>\n      <th>frequency</th>\n      <th>monetary_value</th>\n    </tr>\n  </thead>\n  <tbody>\n    <tr>\n      <th>0.25</th>\n      <td>44.0</td>\n      <td>5.0</td>\n      <td>1145.0</td>\n    </tr>\n    <tr>\n      <th>0.50</th>\n      <td>154.0</td>\n      <td>6.0</td>\n      <td>2257.0</td>\n    </tr>\n    <tr>\n      <th>0.75</th>\n      <td>404.0</td>\n      <td>8.0</td>\n      <td>3784.0</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# RFM quantiles\n",
    "quantiles = rfmTable.quantile(q=[0.25,0.5,0.75])\n",
    "\n",
    "quantiles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "{'recency': {0.25: 44.0, 0.5: 154.0, 0.75: 404.0},\n 'frequency': {0.25: 5.0, 0.5: 6.0, 0.75: 8.0},\n 'monetary_value': {0.25: 1145.0, 0.5: 2257.0, 0.75: 3784.0}}"
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# quantiles to dictionary\n",
    "quantiles = quantiles.to_dict()\n",
    "quantiles"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "rfmSegmentation = rfmTable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Arguments (x = value, p = recency, monetary_value, frequency, d = quartiles dict)\n",
    "def RClass(x,p,d):\n",
    "    if x <= d[p][0.25]:\n",
    "        return 1\n",
    "    elif x <= d[p][0.50]:\n",
    "        return 2\n",
    "    elif x <= d[p][0.75]: \n",
    "        return 3\n",
    "    else:\n",
    "        return 4\n",
    "    \n",
    "# Arguments (x = value, p = recency, monetary_value, frequency, d = quartiles dict)\n",
    "def FMClass(x,p,d):\n",
    "    if x <= d[p][0.25]:\n",
    "        return 4\n",
    "    elif x <= d[p][0.50]:\n",
    "        return 3\n",
    "    elif x <= d[p][0.75]: \n",
    "        return 2\n",
    "    else:\n",
    "        return 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "rfmSegmentation['R_Quartile'] = rfmSegmentation['recency'].apply(RClass, args=('recency',quantiles,))\n",
    "rfmSegmentation['F_Quartile'] = rfmSegmentation['frequency'].apply(FMClass, args=('frequency',quantiles,))\n",
    "rfmSegmentation['M_Quartile'] = rfmSegmentation['monetary_value'].apply(FMClass, args=('monetary_value',quantiles,))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "rfmSegmentation['RFMClass'] = rfmSegmentation.R_Quartile.map(str) \\\n",
    "                            + rfmSegmentation.F_Quartile.map(str) \\\n",
    "                            + rfmSegmentation.M_Quartile.map(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": "                 recency  frequency  monetary_value  R_Quartile  F_Quartile  \\\ncustomer                                                                      \nAaron Bergman       1144          3             887           4           4   \nAaron Hawkins         12          7            1744           1           2   \nAaron Smayling       461          7            3050           4           2   \nAdam Bellavance      105          8            7756           2           2   \nAdam Hart             34         10            3249           1           1   \n\n                 M_Quartile RFMClass  \ncustomer                              \nAaron Bergman             4      444  \nAaron Hawkins             3      123  \nAaron Smayling            2      422  \nAdam Bellavance           1      221  \nAdam Hart                 2      112  ",
      "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>recency</th>\n      <th>frequency</th>\n      <th>monetary_value</th>\n      <th>R_Quartile</th>\n      <th>F_Quartile</th>\n      <th>M_Quartile</th>\n      <th>RFMClass</th>\n    </tr>\n    <tr>\n      <th>customer</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>Aaron Bergman</th>\n      <td>1144</td>\n      <td>3</td>\n      <td>887</td>\n      <td>4</td>\n      <td>4</td>\n      <td>4</td>\n      <td>444</td>\n    </tr>\n    <tr>\n      <th>Aaron Hawkins</th>\n      <td>12</td>\n      <td>7</td>\n      <td>1744</td>\n      <td>1</td>\n      <td>2</td>\n      <td>3</td>\n      <td>123</td>\n    </tr>\n    <tr>\n      <th>Aaron Smayling</th>\n      <td>461</td>\n      <td>7</td>\n      <td>3050</td>\n      <td>4</td>\n      <td>2</td>\n      <td>2</td>\n      <td>422</td>\n    </tr>\n    <tr>\n      <th>Adam Bellavance</th>\n      <td>105</td>\n      <td>8</td>\n      <td>7756</td>\n      <td>2</td>\n      <td>2</td>\n      <td>1</td>\n      <td>221</td>\n    </tr>\n    <tr>\n      <th>Adam Hart</th>\n      <td>34</td>\n      <td>10</td>\n      <td>3249</td>\n      <td>1</td>\n      <td>1</td>\n      <td>2</td>\n      <td>112</td>\n    </tr>\n  </tbody>\n</table>\n</div>"
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rfmSegmentation.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "pycharm": {
     "name": "#%%\n"
    }
   },
   "outputs": [],
   "source": [
    "# Create Dataset\n",
    "rfmSegmentation.to_csv('New-Dataset.csv', sep=',')\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.6.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}