EDA and customer segmentation with RFM analysis.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np \n",
    "from plotly.offline import init_notebook_mode, iplot\n",
    "import plotly.graph_objs as go\n",
    "import matplotlib.pyplot as plt\n",
    "import plotly.plotly as py\n",
    "from plotly import tools\n",
    "from datetime import date\n",
    "import seaborn as sns\n",
    "import random \n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "init_notebook_mode(connected=True)\n",
    "import zipfile\n",
    "import os\n",
    "import winsound"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import sys"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sys.path.append('c:\\\\users\\\\chait\\\\anaconda3\\\\lib\\\\site-packages')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\"\"\"Helper Functions to do Plotting\"\"\"\n",
    "def generateLayoutBar(col_name):\n",
    "    \"\"\"\n",
    "    Generate a layout object for bar chart\n",
    "    \"\"\"\n",
    "    layout_bar = go.Layout(\n",
    "        autosize=False,  # auto size the graph? use False if you are specifying the height and width\n",
    "        width=800,  # height of the figure in pixels\n",
    "        height=600,  # height of the figure in pixels\n",
    "        title=\"Distribution of {} column\".format(col_name),  # title of the figure\n",
    "        # more granular control on the title font\n",
    "        titlefont=dict(\n",
    "            family='Courier New, monospace',  # font family\n",
    "            size=14,  # size of the font\n",
    "            color='black'  # color of the font\n",
    "        ),\n",
    "        # granular control on the axes objects\n",
    "        xaxis=dict(\n",
    "            tickfont=dict(\n",
    "                family='Courier New, monospace',  # font family\n",
    "                size=14,  # size of ticks displayed on the x axis\n",
    "                color='black'  # color of the font\n",
    "            )\n",
    "        ),\n",
    "        yaxis=dict(\n",
    "            #         range=[0,100],\n",
    "            title='Percentage',\n",
    "            titlefont=dict(\n",
    "                size=14,\n",
    "                color='black'\n",
    "            ),\n",
    "            tickfont=dict(\n",
    "                family='Courier New, monospace',  # font family\n",
    "                size=14,  # size of ticks displayed on the y axis\n",
    "                color='black'  # color of the font\n",
    "            )\n",
    "        ),\n",
    "        font=dict(\n",
    "            family='Courier New, monospace',  # font family\n",
    "            color=\"white\",  # color of the font\n",
    "            size=12  # size of the font displayed on the bar\n",
    "        )\n",
    "    )\n",
    "    return layout_bar\n",
    "\n",
    "\n",
    "def plotBar(dataframe_name, col_name, top_n=None):\n",
    "    \"\"\"\n",
    "    Plot a bar chart for the categorical columns\n",
    "\n",
    "    Arguments:\n",
    "    dataframe name\n",
    "    categorical column name\n",
    "\n",
    "    Output:\n",
    "    Plot\n",
    "    \"\"\"\n",
    "    # create a table with value counts\n",
    "    temp = dataframe_name[col_name].value_counts()\n",
    "    if top_n is not None:\n",
    "        temp = temp.head(top_n)\n",
    "    # creating a Bar chart object of plotly\n",
    "    data = [go.Bar(\n",
    "            x=temp.index.astype(str),  # x axis values\n",
    "            y=np.round(temp.values.astype(float) / temp.values.sum(), 4) * 100,  # y axis values\n",
    "            text=['{}%'.format(i) for i in np.round(temp.values.astype(float) / temp.values.sum(), 4) * 100],\n",
    "            # text to be displayed on the bar, we are doing this to display the '%' symbol along with the number on the bar\n",
    "            textposition='auto',  # specify at which position on the bar the text should appear\n",
    "            marker=dict(color='#0047AB'),)]  # change color of the bar\n",
    "    # color used here Cobalt Blue\n",
    "\n",
    "    layout_bar = generateLayoutBar(col_name=col_name)\n",
    "\n",
    "    fig = go.Figure(data=data, layout=layout_bar)\n",
    "    return iplot(fig)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "outpath = \"../data/\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "os.listdir(outpath)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for i in os.listdir(outpath):\n",
    "#     z = zipfile.ZipFile(outpath + i)\n",
    "#     z.extractall(path = outpath)\n",
    "#     z.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in os.listdir(outpath):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datapath = '../data/Data/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in os.listdir(datapath):\n",
    "    print(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics = pd.read_excel(datapath + 'Customer_Demographics.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Transaction = pd.read_excel(datapath + 'Customer_Transaction.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Store_Master = pd.read_excel(datapath + 'Store_Master.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Test_Set = pd.read_excel(datapath + 'Test_Set.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "[i.shape for i in [Customer_Demographics, Customer_Transaction, Store_Master, Test_Set]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "No information about the units given in the data. Assuming Revenue in dhirams."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Customer_Demographics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "datetime.strptime('Jun 1 2005  1:33PM', '%b %d %Y %I:%M%p')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics.First_txn_dt[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "t = datetime.strptime('02NOV2010:00:00:00', '%d%b%Y:%H:%M:%S')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_cols_demo = [ i for i in Customer_Demographics.columns if (i.endswith('dt')) or ('date' in i)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_cols_demo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics.First_txn_dt.dtype"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics[date_cols_demo] = Customer_Demographics[date_cols_demo].astype('O')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convertToDate(x):\n",
    "    try:\n",
    "        return datetime.strptime(x, '%d%b%Y:%H:%M:%S')\n",
    "    except:\n",
    "        return x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics[date_cols_demo] = Customer_Demographics[date_cols_demo].applymap(lambda x : convertToDate(x))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Feature engineering"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "date_cols_demo"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def extractColTypes(dataset):\n",
    "    \"\"\"This functions extracts numeric, categorical , datetime and boolean column types.\n",
    "    Returns 4 lists with respective column types\"\"\"\n",
    "    num_cols_list = [i for i in dataset.columns if dataset[i].dtype in ['int64','float64']]\n",
    "    cat_cols_list = [i for i in dataset.columns if dataset[i].dtype in ['object']]\n",
    "    date_cols_list = [i for i in dataset.columns if dataset[i].dtype in ['datetime64[ns]']]\n",
    "    bool_cols_list = [i for i in dataset.columns if dataset[i].dtype in ['bool']]\n",
    "    print (\"Numeric Columns:\", len(num_cols_list))\n",
    "    print (\"Categorical/Character Columns:\", len(cat_cols_list))\n",
    "    print (\"Date Columns:\",len(date_cols_list))\n",
    "    print (\"Boolean Columns:\",len(bool_cols_list))\n",
    "    return(num_cols_list,cat_cols_list,date_cols_list,bool_cols_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "demo_num_cols_list,demo_cat_cols_list,demo_date_cols_list,demo_bool_cols_list = extractColTypes(Customer_Demographics)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "demo_cat_cols_list"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Days since first transaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "today = convertToDate('24NOV2018:00:00:00')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getDaysDiff(x):\n",
    "    t = -1*(x - today)\n",
    "    return t.apply(lambda x : x.days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics['custSince'] = getDaysDiff(Customer_Demographics.First_txn_dt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### days since last accr and rdm transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics['ds_accr']= getDaysDiff(Customer_Demographics.Last_accr_txn_dt)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics['ds_rdm']= getDaysDiff(Customer_Demographics.Last_rdm_txn_dt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Distribution of the categorical columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "for i in demo_cat_cols_list[1:]:\n",
    "    plotBar(Customer_Demographics, i,top_n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Some columns here like the Income and Marital status are unspecified, as of now we cannot decide if they will help in the prediction, but we should keep a close watch for these."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "demo_num_cols_list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def plotHist(dataframe, col_name):\n",
    "    data = [go.Histogram(x=dataframe[col_name],\n",
    "       marker=dict(\n",
    "        color='#CC0E1D',# Lava (#CC0E1D)\n",
    "#         color = 'rgb(200,0,0)'   # you can provide color in HEX format or rgb format, genrally programmers prefer HEX format as it is a single string value and easy to pass as a variable\n",
    "    ))]\n",
    "    layout = go.Layout(title = \"Histogram of {}\".format(col_name))\n",
    "    fig = go.Figure(data= data, layout=layout)\n",
    "    return iplot(fig)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plotHist(Customer_Demographics, 'Age')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Looks like the age group of 30-40 are frequent visitors of the store, also we see some entries above 100, I will remove them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics.Age = Customer_Demographics.Age.clip_upper(100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plotHist(Customer_Demographics, 'Age')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plotHist(Customer_Demographics, 'Points')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Most people have 0-2000 points"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics[Customer_Demographics.Points>2000].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Just above 1000 customers have more than 2000 points"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let us see if these customers are distinguishable from the rest"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "for i in demo_cat_cols_list[1:]:\n",
    "    plotBar(Customer_Demographics[Customer_Demographics.Points>2000], i,top_n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> The distribution of all the above columns look very similar to those of the unfiltered data. There is no disctinguishing factor as such."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Does loyalty status have correlation with the number of days a customer was with the brand"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def plotMultiBox(dataframe,col_name, num_col_name):\n",
    "    data = []\n",
    "    for i in dataframe[col_name].unique():\n",
    "        trace = go.Box(y = dataframe[num_col_name][dataframe[col_name] == i],\n",
    "                      name = i)\n",
    "        data.append(trace)\n",
    "    layout = go.Layout(title=\"Boxplot of levels in {} for {} column\".format(col_name,num_col_name))\n",
    "    fig = go.Figure(data=data, layout=layout)\n",
    "    return (iplot(fig))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plotMultiBox(Customer_Demographics, 'Loyalty_Status', 'custSince')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> We see that in general, gold customers are with the company longer "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "plotMultiBox(Customer_Demographics, 'Gender', 'custSince')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Females have been customers for a marginally more number of days than males."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "plotMultiBox(Customer_Demographics, 'Income_Range', 'custSince')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Observations:\n",
    " - An interesting observation here, customers who earn more than 20,000 Dhirams have been with the company for over 2500 days, also there are no recent entries for such high income groups.\n",
    " - The recent entries are from <20000 dhiram earning group. May be the store now has more affordable goods.\n",
    " - This could also be a result of the location of the malls"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Customer_Transaction"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Transaction.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Transaction.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Transaction.head(20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plotHist(Customer_Transaction,'Revenue')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Transaction.Customer_ID = Customer_Transaction.Customer_ID.astype('O')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tran_num_cols_list, tran_cat_cols_list, tran_date_cols_list, tran_bool_cols_list = extractColTypes(Customer_Transaction)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "tran_cat_cols_list.remove('Customer_ID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "for i in tran_cat_cols_list:\n",
    "    plotBar(Customer_Transaction, i,top_n=10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Observations: \n",
    " - 7% return rate in the shops\n",
    " - Most returns are related to Size Problems\n",
    " - Store type, business and delivery have only one value"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The data here helps us do the RFM analysis of a customer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### Creating a new column recent_tran_date in Customer_Demographics "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    " Customer_Demographics[['Last_accr_txn_dt', 'Last_rdm_txn_dt']].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics['recent_tran_date'] = Customer_Demographics[['Last_accr_txn_dt', 'Last_rdm_txn_dt']].apply(max, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Demographics[['Last_accr_txn_dt', 'Last_rdm_txn_dt','recent_tran_date']].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cust_recent_tran_date = Customer_Demographics[['Customer_ID','recent_tran_date']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "cust_recent_tran_date.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Recency"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# same functions used before\n",
    "def getRecency(x):\n",
    "    t = -1*(x - today)\n",
    "    return t.apply(lambda x : x.days)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Recency = pd.DataFrame(cust_recent_tran_date['Customer_ID'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Recency['Recency'] = getDaysDiff(cust_recent_tran_date.recent_tran_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Recency.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Recency.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Frequency"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Considering the data in Customer Transaction table, we have the returns data as well, we will consider return as -1 invoice or -revenue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Transaction.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Transaction.Transaction_Type.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Customer_Transaction[Customer_Transaction.Transaction_Type != 'Return'].shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "Customer_Transaction[Customer_Transaction.Transaction_Type == 'Return'].Invoices = -1 * Customer_Transaction[Customer_Transaction.Transaction_Type == 'Return'].Invoices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Frequency = Customer_Transaction.groupby(['Customer_ID']).agg({'Invoices': np.sum}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Frequency.columns = ['Customer_ID', 'Frequency']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Frequency.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Frequency.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Frequency.Frequency[custId_Frequency.Frequency<0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Monetary Value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Monetary = Customer_Transaction.groupby(['Customer_ID']).agg({'Revenue': np.sum}).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Monetary.columns = ['Customer_ID','MonetaryValue']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Monetary.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_Monetary.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### combining R F M"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM = custId_Recency.join(custId_Frequency.set_index('Customer_ID'),on='Customer_ID')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM = custId_RFM.join(custId_Monetary.set_index('Customer_ID'),on='Customer_ID',)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM['RecencyRank'] = pd.qcut(custId_RFM.Recency,q = 5, labels = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM['FrequencyRank'] = pd.qcut(custId_RFM.Frequency,q = 5, labels = False, duplicates = 'drop')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM['MonetaryValueRank'] = pd.qcut(custId_RFM.MonetaryValue,q = 5, labels = False , duplicates = 'drop')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###### We do not have data for the 6 records in demographics table, dropping them"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.RecencyRank.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in custId_RFM.columns:\n",
    "    if 'Rank' in i:\n",
    "        print (i)\n",
    "        custId_RFM[i] = custId_RFM[i].apply(lambda x : str(int(np.round(x)+1)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.RecencyRank.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM['RFMScore'] = custId_RFM.RecencyRank + custId_RFM.FrequencyRank + custId_RFM.MonetaryValueRank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.to_csv(datapath + \"RFM.csv\",index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM.RFMScore.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Customer Segmentation"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    ">Segments\n",
    "- Best Customers : Recent, frequent and high monetary value\n",
    "- High-spending New Customers : Recent , not so frequent but high spending \n",
    "- Lowest-Spending Active Loyal Customers : recent and frequent but spend less\n",
    "- Churned Best Customers : Not recent but frequent and high spending"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "segments_dict = {'155':'Best_Customers',\n",
    "                 '145':'Best_Customers',\n",
    "                 '154':'Best_Customers',  \n",
    "                 '244':'Best_Customers',\n",
    "                 '254':'Best_Customers',\n",
    "                 '245':'Best_Customers',\n",
    "                 \n",
    "                 '135': 'High_Spending_New_Customers',\n",
    "                 '235': 'High_Spending_New_Customers',\n",
    "                 '134': 'High_Spending_New_Customers',\n",
    "                 '125': 'High_Spending_New_Customers',\n",
    "                 '124': 'High_Spending_New_Customers',\n",
    "                 '225': 'High_Spending_New_Customers',\n",
    "                \n",
    "                 '151': 'Lowest_Spending_Active_Loyal_Customers',\n",
    "                 '152': 'Lowest_Spending_Active_Loyal_Customers',\n",
    "                 '153': 'Lowest_Spending_Active_Loyal_Customers',\n",
    "                 '141': 'Lowest_Spending_Active_Loyal_Customers',\n",
    "                 '142': 'Lowest_Spending_Active_Loyal_Customers',\n",
    "                 '143': 'Lowest_Spending_Active_Loyal_Customers',\n",
    "                 '251': 'Lowest_Spending_Active_Loyal_Customers',\n",
    "                 '252': 'Lowest_Spending_Active_Loyal_Customers',\n",
    "                 '253': 'Lowest_Spending_Active_Loyal_Customers',\n",
    "                 \n",
    "                 '515': 'Curned_Best_Customers',\n",
    "                 '514': 'Curned_Best_Customers',\n",
    "                 '525': 'Curned_Best_Customers',\n",
    "                 '524': 'Curned_Best_Customers',\n",
    "                 '415': 'Curned_Best_Customers',\n",
    "                 '414': 'Curned_Best_Customers',\n",
    "                 \n",
    "                }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can add mote segments, but for this analysis I am limiting the number of segments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM['CustomerSegment'] = custId_RFM.RFMScore"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "custId_RFM['CustomerSegment'] = custId_RFM['CustomerSegment'].replace(segments_dict)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 3-D plotting of Customer Segments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Best_Customers\n",
    "Best_Customers = go.Scatter3d(\n",
    "    x=custId_RFM.Recency[custId_RFM['CustomerSegment']=='Best_Customers'],\n",
    "    y=custId_RFM.Frequency[custId_RFM['CustomerSegment']=='Best_Customers'],\n",
    "    z=custId_RFM.MonetaryValue[custId_RFM['CustomerSegment']=='Best_Customers'],\n",
    "    mode='markers',name ='Best_Customers',\n",
    "    marker=dict(\n",
    "        size=4,\n",
    "        opacity=0.6\n",
    "    )\n",
    ")\n",
    "\n",
    "## High_Spending_New_Customers\n",
    "High_Spending_New_Customers = go.Scatter3d(\n",
    "    x=custId_RFM.Recency[custId_RFM['CustomerSegment']=='High_Spending_New_Customers'],\n",
    "    y=custId_RFM.Frequency[custId_RFM['CustomerSegment']=='High_Spending_New_Customers'],\n",
    "    z=custId_RFM.MonetaryValue[custId_RFM['CustomerSegment']=='High_Spending_New_Customers'],\n",
    "    mode='markers',name ='High_Spending_New_Customers',\n",
    "    marker=dict(\n",
    "        size=4,\n",
    "        opacity=0.6\n",
    "    )\n",
    ")\n",
    "\n",
    "## Lowest_Spending_Active_Loyal_Customers\n",
    "Lowest_Spending_Active_Loyal_Customers = go.Scatter3d(\n",
    "    x=custId_RFM.Recency[custId_RFM['CustomerSegment']=='Lowest_Spending_Active_Loyal_Customers'],\n",
    "    y=custId_RFM.Frequency[custId_RFM['CustomerSegment']=='Lowest_Spending_Active_Loyal_Customers'],\n",
    "    z=custId_RFM.MonetaryValue[custId_RFM['CustomerSegment']=='Lowest_Spending_Active_Loyal_Customers'],\n",
    "    mode='markers',name ='Lowest_Spending_Active_Loyal_Customers',\n",
    "    marker=dict(\n",
    "        size=4,\n",
    "        opacity=0.6\n",
    "    )\n",
    ")\n",
    "## Curned_Best_Customers\n",
    "Curned_Best_Customers = go.Scatter3d(\n",
    "    x=custId_RFM.Recency[custId_RFM['CustomerSegment']=='Curned_Best_Customers'],\n",
    "    y=custId_RFM.Frequency[custId_RFM['CustomerSegment']=='Curned_Best_Customers'],\n",
    "    z=custId_RFM.MonetaryValue[custId_RFM['CustomerSegment']=='Curned_Best_Customers'],\n",
    "    mode='markers',name ='Curned_Best_Customers',\n",
    "    marker=dict(\n",
    "        size=4,\n",
    "        opacity=0.6\n",
    "    )\n",
    ")\n",
    "\n",
    "\n",
    "data = [Best_Customers,High_Spending_New_Customers, Lowest_Spending_Active_Loyal_Customers ,Curned_Best_Customers]\n",
    "layout = go.Layout(\n",
    "                    scene = dict(\n",
    "                    xaxis = dict(\n",
    "                        title='Recency',\n",
    "                        backgroundcolor=\"black\",\n",
    "                        showbackground=True,\n",
    "                        titlefont=dict(\n",
    "                                        size=16,\n",
    "                                        color='black'\n",
    "                                        )\n",
    "                        \n",
    "                        \n",
    "                    ),\n",
    "                    yaxis = dict(\n",
    "                        title='Frequency',\n",
    "                        showbackground=True,\n",
    "                        backgroundcolor=\"black\",\n",
    "                        titlefont=dict(\n",
    "                                        size=16,\n",
    "                                        color='black'\n",
    "                                        )\n",
    "                    ),\n",
    "                    zaxis = dict(\n",
    "                        title='MonetaryValue',\n",
    "                        backgroundcolor=\"black\",\n",
    "                        showbackground=True,\n",
    "                        titlefont=dict(\n",
    "                                        size=16,\n",
    "                                        color='black'\n",
    "                                        )\n",
    "                    )\n",
    "                    ),\n",
    "                        width=1000, # height of the figure in pixels\n",
    "                        height=800, # height of the figure in pixels\n",
    "    margin = dict( b =15),)\n",
    "fig = go.Figure(data=data, layout=layout)\n",
    "\n",
    "fig['layout'].update(title= \"RFM Customer Segmentation\")\n",
    "iplot(fig)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "> Observations:\n",
    " - Churned best customers had a maximum monetary value of 6000 dhirams, they did not shop in the last 1.5 year.\n",
    " - Best customers have shopped in the last 3 months and on an average shopped 50 times.\n",
    "     - Giving offers/discounts to these customers will be helpful in converting them to long term high valued customers.\n",
    " - High spending new customers have a maximum frequency of 9.\n",
    " - Lowest spending active loyal customers have visited less than 10 times in the last 2.5 months and spent a maximum of 500 dhirams.\n",
    "     - These low spendingg loyal customers can be converted to high spending customers if the company can offer discounts on the products."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Thank you for reading!"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "tensorflow",
   "language": "python",
   "name": "tensorflow"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}