LTV_example.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# ignore sys warning\n",
    "import warnings\n",
    "import os\n",
    "import glob\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\jiawei\\Anaconda3\\lib\\site-packages\\bokeh\\util\\deprecation.py:34: BokehDeprecationWarning:\n",
      "\n",
      "\n",
      "The bokeh.charts API has moved to a separate 'bkcharts' package.\n",
      "\n",
      "This compatibility shim will remain until Bokeh 1.0 is released.\n",
      "After that, if you want to use this API you will have to install\n",
      "the bkcharts package explicitly.\n",
      "\n",
      "\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ],
      "text/vnd.plotly.v1+html": [
       "<script>requirejs.config({paths: { 'plotly': ['https://cdn.plot.ly/plotly-latest.min']},});if(!window.Plotly) {{require(['plotly'],function(plotly) {window.Plotly=plotly;});}}</script>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "### visualization\n",
    "%matplotlib inline\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import plotly.offline as py\n",
    "import matplotlib as mat\n",
    "from bokeh.plotting import figure\n",
    "from bokeh.io import output_notebook, show\n",
    "from bokeh.models import Range1d, LinearAxis, NumeralTickFormatter, ColumnDataSource, ranges, LabelSet, Legend, SingleIntervalTicker, LinearAxis\n",
    "from bokeh.layouts import row\n",
    "from bokeh.charts import Bar\n",
    "from bokeh.palettes import brewer\n",
    "py.init_notebook_mode(connected=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# other library\n",
    "import datetime\n",
    "import calendar"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# data analysis and wrangling\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "#import re\n",
    "from functools import partial"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# LTV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "電商經營最重要的一環在於客戶價值的最大化，因此我們需要一個工具去幫我們檢視客戶的貢獻價值，而Life Time Value(LTV)便是一個適合的工具。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load order data from csv files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "path =r'C:\\Users\\jiawei\\Desktop\\odr_line_data' \n",
    "allFiles = glob.glob(path + \"/*.csv\")\n",
    "df = pd.DataFrame()\n",
    "list_ = []\n",
    "for file_ in allFiles:\n",
    "    df_temp = pd.read_csv(file_,index_col=None, header=0)\n",
    "    list_.append(df_temp)\n",
    "    del df_temp\n",
    "df = pd.concat(list_)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "選出想要分析的商城，並去除取消的訂單"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = df.loc[df.order_status != 'Cancel']\n",
    "df_sel = df.loc[df.shop_id == 1993].reset_index().drop('index', axis=1)\n",
    "del df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Select useful columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "選出需要使用的欄位"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_sel = df_sel[['cart_code', 'member_code', 'order_time', 'order_label', 'payment', \n",
    "                 'cost', 'qty', 'promotion_discount', 'ecoupon_discount']]\n",
    "df_sel['product_cost'] = df_sel['cost'] * df_sel['qty']\n",
    "df_sel.drop(['cost', 'qty'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cart_code</th>\n",
       "      <th>member_code</th>\n",
       "      <th>order_time</th>\n",
       "      <th>order_label</th>\n",
       "      <th>payment</th>\n",
       "      <th>promotion_discount</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>product_cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TG160229231193</td>\n",
       "      <td>37509</td>\n",
       "      <td>2016-02-29T15:53:06.153000Z</td>\n",
       "      <td>Repo</td>\n",
       "      <td>390</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>270.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TG160229231193</td>\n",
       "      <td>37509</td>\n",
       "      <td>2016-02-29T15:53:06.153000Z</td>\n",
       "      <td>Repo</td>\n",
       "      <td>350</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TG160229231193</td>\n",
       "      <td>37509</td>\n",
       "      <td>2016-02-29T15:53:06.153000Z</td>\n",
       "      <td>Repo</td>\n",
       "      <td>350</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>198.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        cart_code  member_code                   order_time order_label  \\\n",
       "0  TG160229231193        37509  2016-02-29T15:53:06.153000Z        Repo   \n",
       "1  TG160229231193        37509  2016-02-29T15:53:06.153000Z        Repo   \n",
       "2  TG160229231193        37509  2016-02-29T15:53:06.153000Z        Repo   \n",
       "\n",
       "   payment  promotion_discount  ecoupon_discount  product_cost  \n",
       "0      390                   0                 0         270.0  \n",
       "1      350                   0                 0           0.0  \n",
       "2      350                   0                 0         198.0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sel.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## time format"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "將日期格是由字串轉為以日為單位的時間格式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cart_code</th>\n",
       "      <th>member_code</th>\n",
       "      <th>order_time</th>\n",
       "      <th>order_label</th>\n",
       "      <th>payment</th>\n",
       "      <th>promotion_discount</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>product_cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TG160229231193</td>\n",
       "      <td>37509</td>\n",
       "      <td>2016-02-29</td>\n",
       "      <td>Repo</td>\n",
       "      <td>390</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>270.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TG160229231193</td>\n",
       "      <td>37509</td>\n",
       "      <td>2016-02-29</td>\n",
       "      <td>Repo</td>\n",
       "      <td>350</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TG160229231193</td>\n",
       "      <td>37509</td>\n",
       "      <td>2016-02-29</td>\n",
       "      <td>Repo</td>\n",
       "      <td>350</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>198.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        cart_code  member_code order_time order_label  payment  \\\n",
       "0  TG160229231193        37509 2016-02-29        Repo      390   \n",
       "1  TG160229231193        37509 2016-02-29        Repo      350   \n",
       "2  TG160229231193        37509 2016-02-29        Repo      350   \n",
       "\n",
       "   promotion_discount  ecoupon_discount  product_cost  \n",
       "0                   0                 0         270.0  \n",
       "1                   0                 0           0.0  \n",
       "2                   0                 0         198.0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time_format = '%Y-%m-%d'\n",
    "df_sel['order_time'] = df_sel.order_time.astype(str).str[0:10]\n",
    "df_sel['order_time'] = pd.to_datetime(df_sel['order_time'], format=time_format)\n",
    "df_sel.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TS TO TG"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "由於訂單資訊包含商品資訊，因此會出現單筆訂單多個商品(列)的情況，由於LTV不需要那麼細緻的資料，因此我們會把銅筆訂單的資訊彙整起來。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cart_code</th>\n",
       "      <th>member_code</th>\n",
       "      <th>order_time</th>\n",
       "      <th>order_label</th>\n",
       "      <th>payment</th>\n",
       "      <th>promotion_discount</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>product_cost</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>466458</th>\n",
       "      <td>TG171031Z02361</td>\n",
       "      <td>196828</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>Repo</td>\n",
       "      <td>628</td>\n",
       "      <td>-168</td>\n",
       "      <td>-100</td>\n",
       "      <td>485.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466459</th>\n",
       "      <td>TG171031Z02368</td>\n",
       "      <td>15088</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>Repo</td>\n",
       "      <td>710</td>\n",
       "      <td>0</td>\n",
       "      <td>-70</td>\n",
       "      <td>546.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466460</th>\n",
       "      <td>TG171031Z02417</td>\n",
       "      <td>267313</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>Repo</td>\n",
       "      <td>724</td>\n",
       "      <td>-254</td>\n",
       "      <td>0</td>\n",
       "      <td>549.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             cart_code  member_code order_time order_label  payment  \\\n",
       "466458  TG171031Z02361       196828 2017-10-31        Repo      628   \n",
       "466459  TG171031Z02368        15088 2017-10-31        Repo      710   \n",
       "466460  TG171031Z02417       267313 2017-10-31        Repo      724   \n",
       "\n",
       "        promotion_discount  ecoupon_discount  product_cost  \n",
       "466458                -168              -100         485.0  \n",
       "466459                   0               -70         546.0  \n",
       "466460                -254                 0         549.0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tg = df_sel.groupby(['cart_code', 'member_code', 'order_time', 'order_label'], as_index=False).sum()\n",
    "del df_sel\n",
    "df_tg.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate buy times"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "這邊我們計算每位消費者的購買次數"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cart_code</th>\n",
       "      <th>member_code</th>\n",
       "      <th>order_time</th>\n",
       "      <th>order_label</th>\n",
       "      <th>payment</th>\n",
       "      <th>promotion_discount</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>product_cost</th>\n",
       "      <th>times</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>466458</th>\n",
       "      <td>TG171031Z02361</td>\n",
       "      <td>196828</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>Repo</td>\n",
       "      <td>628</td>\n",
       "      <td>-168</td>\n",
       "      <td>-100</td>\n",
       "      <td>485.0</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466459</th>\n",
       "      <td>TG171031Z02368</td>\n",
       "      <td>15088</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>Repo</td>\n",
       "      <td>710</td>\n",
       "      <td>0</td>\n",
       "      <td>-70</td>\n",
       "      <td>546.0</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>466460</th>\n",
       "      <td>TG171031Z02417</td>\n",
       "      <td>267313</td>\n",
       "      <td>2017-10-31</td>\n",
       "      <td>Repo</td>\n",
       "      <td>724</td>\n",
       "      <td>-254</td>\n",
       "      <td>0</td>\n",
       "      <td>549.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             cart_code  member_code order_time order_label  payment  \\\n",
       "466458  TG171031Z02361       196828 2017-10-31        Repo      628   \n",
       "466459  TG171031Z02368        15088 2017-10-31        Repo      710   \n",
       "466460  TG171031Z02417       267313 2017-10-31        Repo      724   \n",
       "\n",
       "        promotion_discount  ecoupon_discount  product_cost  times  \n",
       "466458                -168              -100         485.0      2  \n",
       "466459                   0               -70         546.0     11  \n",
       "466460                -254                 0         549.0      3  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#print(df_tg.shape)\n",
    "df_tg['times'] = df_tg.groupby('member_code', as_index=False).cumcount()+1\n",
    "#print(df_tg.shape)\n",
    "df_tg.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get member first buy date time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "這邊我們取得消費者第一次購買的日期"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cart_code</th>\n",
       "      <th>member_code</th>\n",
       "      <th>order_time</th>\n",
       "      <th>order_label</th>\n",
       "      <th>payment</th>\n",
       "      <th>promotion_discount</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>product_cost</th>\n",
       "      <th>times</th>\n",
       "      <th>first_buy_date_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TG151209140050</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>New</td>\n",
       "      <td>500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TG151212140443</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-12</td>\n",
       "      <td>Repo</td>\n",
       "      <td>445</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2015-12-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TG151212140639</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-12</td>\n",
       "      <td>Repo</td>\n",
       "      <td>250</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2015-12-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        cart_code  member_code order_time order_label  payment  \\\n",
       "0  TG151209140050            1 2015-12-09         New      500   \n",
       "1  TG151212140443            1 2015-12-12        Repo      445   \n",
       "2  TG151212140639            1 2015-12-12        Repo      250   \n",
       "\n",
       "   promotion_discount  ecoupon_discount  product_cost  times  \\\n",
       "0                   0                 0           0.0      1   \n",
       "1                   0                 0           0.0      2   \n",
       "2                   0                 0           0.0      3   \n",
       "\n",
       "  first_buy_date_time  \n",
       "0          2015-12-09  \n",
       "1          2015-12-09  \n",
       "2          2015-12-09  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#print(df_tg.shape)\n",
    "df_tg = pd.merge(df_tg,\n",
    "                 df_tg.groupby('member_code', as_index=False)['order_time'].min(),\n",
    "                 'left',\n",
    "                 'member_code')\n",
    "df_tg.columns = ['cart_code', 'member_code', 'order_time', 'order_label', 'payment',\n",
    "                 'promotion_discount', 'ecoupon_discount', 'product_cost', 'times',\n",
    "                 'first_buy_date_time']\n",
    "#print(df_tg.shape)\n",
    "df_tg.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get max_day & basemonth"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "這邊取得每位會員計算貢獻的期間(這邊以一年呈現)，以及依照每位會員的首購日的月份作為基準月份"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def add_months(months, rows):\n",
    "    month = rows['first_buy_date_time'].month - 1 + months\n",
    "    year = int(rows['first_buy_date_time'].year + month / 12 )\n",
    "    month = month % 12 + 1\n",
    "    day = min(rows['first_buy_date_time'].day, calendar.monthrange(year, month)[1])\n",
    "    return datetime.date(year, month, day)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cart_code</th>\n",
       "      <th>member_code</th>\n",
       "      <th>order_time</th>\n",
       "      <th>order_label</th>\n",
       "      <th>payment</th>\n",
       "      <th>promotion_discount</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>product_cost</th>\n",
       "      <th>times</th>\n",
       "      <th>first_buy_date_time</th>\n",
       "      <th>maxmonth_1y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TG151209140050</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>New</td>\n",
       "      <td>500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>2016-12-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TG151212140443</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-12</td>\n",
       "      <td>Repo</td>\n",
       "      <td>445</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>2016-12-09</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TG151212140639</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-12</td>\n",
       "      <td>Repo</td>\n",
       "      <td>250</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>2016-12-09</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        cart_code  member_code order_time order_label  payment  \\\n",
       "0  TG151209140050            1 2015-12-09         New      500   \n",
       "1  TG151212140443            1 2015-12-12        Repo      445   \n",
       "2  TG151212140639            1 2015-12-12        Repo      250   \n",
       "\n",
       "   promotion_discount  ecoupon_discount  product_cost  times  \\\n",
       "0                   0                 0           0.0      1   \n",
       "1                   0                 0           0.0      2   \n",
       "2                   0                 0           0.0      3   \n",
       "\n",
       "  first_buy_date_time maxmonth_1y  \n",
       "0          2015-12-09  2016-12-09  \n",
       "1          2015-12-09  2016-12-09  \n",
       "2          2015-12-09  2016-12-09  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# maxmonth\n",
    "df_tg['maxmonth_1y'] = df_tg.apply(partial(add_months, 12), axis=1)\n",
    "df_tg['maxmonth_1y'] = pd.to_datetime(df_tg['maxmonth_1y'], format=time_format)\n",
    "df_tg.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cart_code</th>\n",
       "      <th>member_code</th>\n",
       "      <th>order_time</th>\n",
       "      <th>order_label</th>\n",
       "      <th>payment</th>\n",
       "      <th>promotion_discount</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>product_cost</th>\n",
       "      <th>times</th>\n",
       "      <th>first_buy_date_time</th>\n",
       "      <th>maxmonth_1y</th>\n",
       "      <th>basemonth</th>\n",
       "      <th>month</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TG151209140050</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>New</td>\n",
       "      <td>500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>2016-12-09</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>2015-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TG151212140443</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-12</td>\n",
       "      <td>Repo</td>\n",
       "      <td>445</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>2016-12-09</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>2015-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TG151212140639</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-12</td>\n",
       "      <td>Repo</td>\n",
       "      <td>250</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>2016-12-09</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>2015-12-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        cart_code  member_code order_time order_label  payment  \\\n",
       "0  TG151209140050            1 2015-12-09         New      500   \n",
       "1  TG151212140443            1 2015-12-12        Repo      445   \n",
       "2  TG151212140639            1 2015-12-12        Repo      250   \n",
       "\n",
       "   promotion_discount  ecoupon_discount  product_cost  times  \\\n",
       "0                   0                 0           0.0      1   \n",
       "1                   0                 0           0.0      2   \n",
       "2                   0                 0           0.0      3   \n",
       "\n",
       "  first_buy_date_time maxmonth_1y basemonth      month  \n",
       "0          2015-12-09  2016-12-09   2015-12 2015-12-01  \n",
       "1          2015-12-09  2016-12-09   2015-12 2015-12-01  \n",
       "2          2015-12-09  2016-12-09   2015-12 2015-12-01  "
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# get basemonth\n",
    "df_tg['basemonth'] = df_tg['first_buy_date_time'].astype(str).str[0:7]\n",
    "#df_tg['basemonth'] = pd.to_datetime(df_tg['basemonth'], format=time_format)\n",
    "df_tg['month'] = df_tg['order_time'].astype(str).str[0:7]\n",
    "df_tg['month'] = pd.to_datetime(df_tg['month'], format=time_format)\n",
    "df_tg.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate total buy times"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "這邊我們要統計每位顧客至今為止總交易次數"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_tg = pd.merge(left=df_tg,\n",
    "                 right=df_tg.groupby('member_code', as_index=False)['times'].max(),\n",
    "                 how='left',\n",
    "                 on='member_code')\n",
    "df_tg.columns = ['cart_code', 'member_code', 'order_time', 'order_label', 'payment',\n",
    "                 'promotion_discount', 'ecoupon_discount', 'product_cost', 'times',\n",
    "                 'first_buy_date_time', 'maxmonth_1y', 'basemonth', 'month', 'max_times']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cart_code</th>\n",
       "      <th>member_code</th>\n",
       "      <th>order_time</th>\n",
       "      <th>order_label</th>\n",
       "      <th>payment</th>\n",
       "      <th>promotion_discount</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>product_cost</th>\n",
       "      <th>times</th>\n",
       "      <th>first_buy_date_time</th>\n",
       "      <th>maxmonth_1y</th>\n",
       "      <th>basemonth</th>\n",
       "      <th>month</th>\n",
       "      <th>max_times</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TG151209140050</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>New</td>\n",
       "      <td>500</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>2016-12-09</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TG151212140443</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-12</td>\n",
       "      <td>Repo</td>\n",
       "      <td>445</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>2016-12-09</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TG151212140639</td>\n",
       "      <td>1</td>\n",
       "      <td>2015-12-12</td>\n",
       "      <td>Repo</td>\n",
       "      <td>250</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3</td>\n",
       "      <td>2015-12-09</td>\n",
       "      <td>2016-12-09</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>2015-12-01</td>\n",
       "      <td>21</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        cart_code  member_code order_time order_label  payment  \\\n",
       "0  TG151209140050            1 2015-12-09         New      500   \n",
       "1  TG151212140443            1 2015-12-12        Repo      445   \n",
       "2  TG151212140639            1 2015-12-12        Repo      250   \n",
       "\n",
       "   promotion_discount  ecoupon_discount  product_cost  times  \\\n",
       "0                   0                 0           0.0      1   \n",
       "1                   0                 0           0.0      2   \n",
       "2                   0                 0           0.0      3   \n",
       "\n",
       "  first_buy_date_time maxmonth_1y basemonth      month  max_times  \n",
       "0          2015-12-09  2016-12-09   2015-12 2015-12-01         21  \n",
       "1          2015-12-09  2016-12-09   2015-12 2015-12-01         21  \n",
       "2          2015-12-09  2016-12-09   2015-12 2015-12-01         21  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tg.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Filter order_time <= maxmonth_1y"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "在計算LTV之前，首先要將超過計算周期的交易紀錄去除，這樣才能讓每個月的比較有一樣的基準，週期大多數會訂在一年，但實際狀況依商城或服務而定。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>cart_code</th>\n",
       "      <th>member_code</th>\n",
       "      <th>order_time</th>\n",
       "      <th>order_label</th>\n",
       "      <th>payment</th>\n",
       "      <th>promotion_discount</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>product_cost</th>\n",
       "      <th>times</th>\n",
       "      <th>first_buy_date_time</th>\n",
       "      <th>maxmonth_1y</th>\n",
       "      <th>basemonth</th>\n",
       "      <th>month</th>\n",
       "      <th>max_times</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>34095</th>\n",
       "      <td>TG160421230967</td>\n",
       "      <td>212259</td>\n",
       "      <td>2016-04-21</td>\n",
       "      <td>Repo</td>\n",
       "      <td>925</td>\n",
       "      <td>-45</td>\n",
       "      <td>0</td>\n",
       "      <td>587.0</td>\n",
       "      <td>2</td>\n",
       "      <td>2016-03-07</td>\n",
       "      <td>2017-03-07</td>\n",
       "      <td>2016-03</td>\n",
       "      <td>2016-04-01</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>292018</th>\n",
       "      <td>TG170203Z00458</td>\n",
       "      <td>194012</td>\n",
       "      <td>2017-02-03</td>\n",
       "      <td>New</td>\n",
       "      <td>563</td>\n",
       "      <td>-58</td>\n",
       "      <td>-25</td>\n",
       "      <td>438.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2017-02-03</td>\n",
       "      <td>2018-02-03</td>\n",
       "      <td>2017-02</td>\n",
       "      <td>2017-02-01</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>230855</th>\n",
       "      <td>TG161127Z01580</td>\n",
       "      <td>42709</td>\n",
       "      <td>2016-11-27</td>\n",
       "      <td>New</td>\n",
       "      <td>730</td>\n",
       "      <td>0</td>\n",
       "      <td>-50</td>\n",
       "      <td>525.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2016-11-27</td>\n",
       "      <td>2017-11-27</td>\n",
       "      <td>2016-11</td>\n",
       "      <td>2016-11-01</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "             cart_code  member_code order_time order_label  payment  \\\n",
       "34095   TG160421230967       212259 2016-04-21        Repo      925   \n",
       "292018  TG170203Z00458       194012 2017-02-03         New      563   \n",
       "230855  TG161127Z01580        42709 2016-11-27         New      730   \n",
       "\n",
       "        promotion_discount  ecoupon_discount  product_cost  times  \\\n",
       "34095                  -45                 0         587.0      2   \n",
       "292018                 -58               -25         438.0      1   \n",
       "230855                   0               -50         525.0      1   \n",
       "\n",
       "       first_buy_date_time maxmonth_1y basemonth      month  max_times  \n",
       "34095           2016-03-07  2017-03-07   2016-03 2016-04-01          3  \n",
       "292018          2017-02-03  2018-02-03   2017-02 2017-02-01          1  \n",
       "230855          2016-11-27  2017-11-27   2016-11 2016-11-01          2  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tg_select = df_tg.loc[df_tg['order_time'] <= df_tg['maxmonth_1y']]\n",
    "#print(df_tg_select.shape)\n",
    "df_tg_select.sample(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate each usr payment & cost"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "這邊主要計算每個月份的每一位會員的貢獻"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>member_code</th>\n",
       "      <th>basemonth</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>payment</th>\n",
       "      <th>product_cost</th>\n",
       "      <th>promotion_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>-150</td>\n",
       "      <td>7903</td>\n",
       "      <td>2450.0</td>\n",
       "      <td>-72</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>-50</td>\n",
       "      <td>6216</td>\n",
       "      <td>1537.0</td>\n",
       "      <td>-525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>-432</td>\n",
       "      <td>2962</td>\n",
       "      <td>1591.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>0</td>\n",
       "      <td>3234</td>\n",
       "      <td>1009.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2015-12</td>\n",
       "      <td>-100</td>\n",
       "      <td>508</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   member_code basemonth  ecoupon_discount  payment  product_cost  \\\n",
       "0            1   2015-12              -150     7903        2450.0   \n",
       "1            2   2015-12               -50     6216        1537.0   \n",
       "2            3   2015-12              -432     2962        1591.0   \n",
       "3            4   2015-12                 0     3234        1009.0   \n",
       "4            5   2015-12              -100      508           0.0   \n",
       "\n",
       "   promotion_discount  \n",
       "0                 -72  \n",
       "1                -525  \n",
       "2                   0  \n",
       "3                   0  \n",
       "4                   0  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_same_piv = pd.pivot_table(df_tg_select, \n",
    "                             index=['member_code', 'basemonth'], \n",
    "                             values=['payment', 'product_cost', 'promotion_discount', 'ecoupon_discount'], \n",
    "                             aggfunc=np.sum).reset_index()\n",
    "#print(df_same_piv.shape)\n",
    "df_same_piv.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate month LTV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "計算每個月份進來的顧客未來一年的貢獻金額與成本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_month_piv = pd.pivot_table(df_same_piv, index=['basemonth'], \n",
    "                              values=['payment', 'product_cost', 'ecoupon_discount', 'promotion_discount'], \n",
    "                              aggfunc=np.mean).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>basemonth</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>payment</th>\n",
       "      <th>product_cost</th>\n",
       "      <th>promotion_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015-12</td>\n",
       "      <td>-113.570225</td>\n",
       "      <td>2152.126404</td>\n",
       "      <td>800.238202</td>\n",
       "      <td>-90.609551</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2016-01</td>\n",
       "      <td>-89.417227</td>\n",
       "      <td>1754.414536</td>\n",
       "      <td>647.104172</td>\n",
       "      <td>-67.165545</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2016-02</td>\n",
       "      <td>-37.895706</td>\n",
       "      <td>1786.015337</td>\n",
       "      <td>765.339264</td>\n",
       "      <td>-43.917178</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  basemonth  ecoupon_discount      payment  product_cost  promotion_discount\n",
       "0   2015-12       -113.570225  2152.126404    800.238202          -90.609551\n",
       "1   2016-01        -89.417227  1754.414536    647.104172          -67.165545\n",
       "2   2016-02        -37.895706  1786.015337    765.339264          -43.917178"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_month_piv.head(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Remove uncompleted month"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "這邊我們要先將一些未累積一年的月份與先前平台的其間資料給去除"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>basemonth</th>\n",
       "      <th>ecoupon_discount</th>\n",
       "      <th>payment</th>\n",
       "      <th>product_cost</th>\n",
       "      <th>promotion_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2016-08</td>\n",
       "      <td>-18.744102</td>\n",
       "      <td>1318.433004</td>\n",
       "      <td>753.547257</td>\n",
       "      <td>-63.079082</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016-09</td>\n",
       "      <td>-26.766452</td>\n",
       "      <td>1358.839365</td>\n",
       "      <td>810.968257</td>\n",
       "      <td>-99.818106</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2016-10</td>\n",
       "      <td>-28.956346</td>\n",
       "      <td>1280.437887</td>\n",
       "      <td>776.745947</td>\n",
       "      <td>-95.098581</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   basemonth  ecoupon_discount      payment  product_cost  promotion_discount\n",
       "8    2016-08        -18.744102  1318.433004    753.547257          -63.079082\n",
       "9    2016-09        -26.766452  1358.839365    810.968257          -99.818106\n",
       "10   2016-10        -28.956346  1280.437887    776.745947          -95.098581"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_month_piv = df_month_piv.iloc[3:(len(df_month_piv.index)-12),]\n",
    "df_month_piv.tail(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate Net LTV"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "這邊我們先扣除已知的訂單相關成本來得到Net LTV，知道這個數字之後，我們可以根據我們所需的獲利結構來規劃後續的行銷費用佔比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_month_piv.payment = np.round(df_month_piv.payment, 2)\n",
    "df_month_piv.product_cost = np.round(df_month_piv.product_cost, 2)\n",
    "df_month_piv.ecoupon_discount = np.round(df_month_piv.ecoupon_discount, 2)\n",
    "df_month_piv.promotion_discount = np.round(df_month_piv.promotion_discount, 1)\n",
    "df_month_piv.ecoupon_discount = -df_month_piv.ecoupon_discount\n",
    "df_month_piv.promotion_discount = -df_month_piv.promotion_discount\n",
    "df_month_piv['net_LTV'] = df_month_piv.payment - df_month_piv.product_cost - df_month_piv.ecoupon_discount - \\\n",
    "                          df_month_piv.promotion_discount\n",
    "df_month_piv['LTV'] = df_month_piv['payment']\n",
    "df_month_piv['total_discount'] = df_month_piv.ecoupon_discount + df_month_piv.promotion_discount\n",
    "df_month_piv.drop(['payment', 'ecoupon_discount', 'promotion_discount'], axis=1, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>basemonth</th>\n",
       "      <th>product_cost</th>\n",
       "      <th>net_LTV</th>\n",
       "      <th>LTV</th>\n",
       "      <th>total_discount</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2016-03</td>\n",
       "      <td>855.56</td>\n",
       "      <td>563.83</td>\n",
       "      <td>1527.03</td>\n",
       "      <td>107.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2016-04</td>\n",
       "      <td>814.50</td>\n",
       "      <td>530.47</td>\n",
       "      <td>1446.49</td>\n",
       "      <td>101.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2016-05</td>\n",
       "      <td>735.22</td>\n",
       "      <td>503.21</td>\n",
       "      <td>1305.67</td>\n",
       "      <td>67.24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2016-06</td>\n",
       "      <td>818.78</td>\n",
       "      <td>567.40</td>\n",
       "      <td>1465.52</td>\n",
       "      <td>79.34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2016-07</td>\n",
       "      <td>758.35</td>\n",
       "      <td>525.22</td>\n",
       "      <td>1369.71</td>\n",
       "      <td>86.14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2016-08</td>\n",
       "      <td>753.55</td>\n",
       "      <td>483.04</td>\n",
       "      <td>1318.43</td>\n",
       "      <td>81.84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2016-09</td>\n",
       "      <td>810.97</td>\n",
       "      <td>421.30</td>\n",
       "      <td>1358.84</td>\n",
       "      <td>126.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2016-10</td>\n",
       "      <td>776.75</td>\n",
       "      <td>379.63</td>\n",
       "      <td>1280.44</td>\n",
       "      <td>124.06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   basemonth  product_cost  net_LTV      LTV  total_discount\n",
       "3    2016-03        855.56   563.83  1527.03          107.64\n",
       "4    2016-04        814.50   530.47  1446.49          101.52\n",
       "5    2016-05        735.22   503.21  1305.67           67.24\n",
       "6    2016-06        818.78   567.40  1465.52           79.34\n",
       "7    2016-07        758.35   525.22  1369.71           86.14\n",
       "8    2016-08        753.55   483.04  1318.43           81.84\n",
       "9    2016-09        810.97   421.30  1358.84          126.57\n",
       "10   2016-10        776.75   379.63  1280.44          124.06"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_month_piv"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Draw LTV "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "    <div class=\"bk-root\">\n",
       "        <a href=\"http://bokeh.pydata.org\" target=\"_blank\" class=\"bk-logo bk-logo-small bk-logo-notebook\"></a>\n",
       "        <span id=\"95521962-b930-4322-8c64-3e8e498d9a2c\">Loading BokehJS ...</span>\n",
       "    </div>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "application/javascript": [
       "\n",
       "(function(global) {\n",
       "  function now() {\n",
       "    return new Date();\n",
       "  }\n",
       "\n",
       "  var force = true;\n",
       "\n",
       "  if (typeof (window._bokeh_onload_callbacks) === \"undefined\" || force === true) {\n",
       "    window._bokeh_onload_callbacks = [];\n",
       "    window._bokeh_is_loading = undefined;\n",
       "  }\n",
       "\n",
       "\n",
       "  \n",
       "  if (typeof (window._bokeh_timeout) === \"undefined\" || force === true) {\n",
       "    window._bokeh_timeout = Date.now() + 5000;\n",
       "    window._bokeh_failed_load = false;\n",
       "  }\n",
       "\n",
       "  var NB_LOAD_WARNING = {'data': {'text/html':\n",
       "     \"<div style='background-color: #fdd'>\\n\"+\n",
       "     \"<p>\\n\"+\n",
       "     \"BokehJS does not appear to have successfully loaded. If loading BokehJS from CDN, this \\n\"+\n",
       "     \"may be due to a slow or bad network connection. Possible fixes:\\n\"+\n",
       "     \"</p>\\n\"+\n",
       "     \"<ul>\\n\"+\n",
       "     \"<li>re-rerun `output_notebook()` to attempt to load from CDN again, or</li>\\n\"+\n",
       "     \"<li>use INLINE resources instead, as so:</li>\\n\"+\n",
       "     \"</ul>\\n\"+\n",
       "     \"<code>\\n\"+\n",
       "     \"from bokeh.resources import INLINE\\n\"+\n",
       "     \"output_notebook(resources=INLINE)\\n\"+\n",
       "     \"</code>\\n\"+\n",
       "     \"</div>\"}};\n",
       "\n",
       "  function display_loaded() {\n",
       "    if (window.Bokeh !== undefined) {\n",
       "      var el = document.getElementById(\"95521962-b930-4322-8c64-3e8e498d9a2c\");\n",
       "      el.textContent = \"BokehJS \" + Bokeh.version + \" successfully loaded.\";\n",
       "    } else if (Date.now() < window._bokeh_timeout) {\n",
       "      setTimeout(display_loaded, 100)\n",
       "    }\n",
       "  }\n",
       "\n",
       "  function run_callbacks() {\n",
       "    try {\n",
       "      window._bokeh_onload_callbacks.forEach(function(callback) { callback() });\n",
       "    }\n",
       "    finally {\n",
       "      delete window._bokeh_onload_callbacks\n",
       "    }\n",
       "    console.info(\"Bokeh: all callbacks have finished\");\n",
       "  }\n",
       "\n",
       "  function load_libs(js_urls, callback) {\n",
       "    window._bokeh_onload_callbacks.push(callback);\n",
       "    if (window._bokeh_is_loading > 0) {\n",
       "      console.log(\"Bokeh: BokehJS is being loaded, scheduling callback at\", now());\n",
       "      return null;\n",
       "    }\n",
       "    if (js_urls == null || js_urls.length === 0) {\n",
       "      run_callbacks();\n",
       "      return null;\n",
       "    }\n",
       "    console.log(\"Bokeh: BokehJS not loaded, scheduling load and callback at\", now());\n",
       "    window._bokeh_is_loading = js_urls.length;\n",
       "    for (var i = 0; i < js_urls.length; i++) {\n",
       "      var url = js_urls[i];\n",
       "      var s = document.createElement('script');\n",
       "      s.src = url;\n",
       "      s.async = false;\n",
       "      s.onreadystatechange = s.onload = function() {\n",
       "        window._bokeh_is_loading--;\n",
       "        if (window._bokeh_is_loading === 0) {\n",
       "          console.log(\"Bokeh: all BokehJS libraries loaded\");\n",
       "          run_callbacks()\n",
       "        }\n",
       "      };\n",
       "      s.onerror = function() {\n",
       "        console.warn(\"failed to load library \" + url);\n",
       "      };\n",
       "      console.log(\"Bokeh: injecting script tag for BokehJS library: \", url);\n",
       "      document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "    }\n",
       "  };var element = document.getElementById(\"95521962-b930-4322-8c64-3e8e498d9a2c\");\n",
       "  if (element == null) {\n",
       "    console.log(\"Bokeh: ERROR: autoload.js configured with elementid '95521962-b930-4322-8c64-3e8e498d9a2c' but no matching script tag was found. \")\n",
       "    return false;\n",
       "  }\n",
       "\n",
       "  var js_urls = [\"https://cdn.pydata.org/bokeh/release/bokeh-0.12.6.min.js\", \"https://cdn.pydata.org/bokeh/release/bokeh-widgets-0.12.6.min.js\"];\n",
       "\n",
       "  var inline_js = [\n",
       "    function(Bokeh) {\n",
       "      Bokeh.set_log_level(\"info\");\n",
       "    },\n",
       "    \n",
       "    function(Bokeh) {\n",
       "      \n",
       "    },\n",
       "    \n",
       "    function(Bokeh) {\n",
       "      \n",
       "      document.getElementById(\"95521962-b930-4322-8c64-3e8e498d9a2c\").textContent = \"BokehJS is loading...\";\n",
       "    },\n",
       "    function(Bokeh) {\n",
       "      console.log(\"Bokeh: injecting CSS: https://cdn.pydata.org/bokeh/release/bokeh-0.12.6.min.css\");\n",
       "      Bokeh.embed.inject_css(\"https://cdn.pydata.org/bokeh/release/bokeh-0.12.6.min.css\");\n",
       "      console.log(\"Bokeh: injecting CSS: https://cdn.pydata.org/bokeh/release/bokeh-widgets-0.12.6.min.css\");\n",
       "      Bokeh.embed.inject_css(\"https://cdn.pydata.org/bokeh/release/bokeh-widgets-0.12.6.min.css\");\n",
       "    }\n",
       "  ];\n",
       "\n",
       "  function run_inline_js() {\n",
       "    \n",
       "    if ((window.Bokeh !== undefined) || (force === true)) {\n",
       "      for (var i = 0; i < inline_js.length; i++) {\n",
       "        inline_js[i](window.Bokeh);\n",
       "      }if (force === true) {\n",
       "        display_loaded();\n",
       "      }} else if (Date.now() < window._bokeh_timeout) {\n",
       "      setTimeout(run_inline_js, 100);\n",
       "    } else if (!window._bokeh_failed_load) {\n",
       "      console.log(\"Bokeh: BokehJS failed to load within specified timeout.\");\n",
       "      window._bokeh_failed_load = true;\n",
       "    } else if (force !== true) {\n",
       "      var cell = $(document.getElementById(\"95521962-b930-4322-8c64-3e8e498d9a2c\")).parents('.cell').data().cell;\n",
       "      cell.output_area.append_execute_result(NB_LOAD_WARNING)\n",
       "    }\n",
       "\n",
       "  }\n",
       "\n",
       "  if (window._bokeh_is_loading === 0) {\n",
       "    console.log(\"Bokeh: BokehJS loaded, going straight to plotting\");\n",
       "    run_inline_js();\n",
       "  } else {\n",
       "    load_libs(js_urls, function() {\n",
       "      console.log(\"Bokeh: BokehJS plotting callback run at\", now());\n",
       "      run_inline_js();\n",
       "    });\n",
       "  }\n",
       "}(this));"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# bar-plot basic\n",
    "# 調整座標軸\n",
    "output_notebook()\n",
    "\n",
    "# source for pic\n",
    "source1 = ColumnDataSource(dict(x=df_month_piv['basemonth'].tolist(),y=df_month_piv['LTV'].tolist()))\n",
    "source2 = ColumnDataSource(dict(x=df_month_piv['basemonth'].tolist(),y=np.round(df_month_piv['net_LTV'].tolist(), 2)))\n",
    "source3 = ColumnDataSource(dict(x=df_month_piv['basemonth'].tolist(),y=np.round(df_month_piv['product_cost'].tolist(), 2)))\n",
    "source4 = ColumnDataSource(dict(x=df_month_piv['basemonth'].tolist(),y=np.round(df_month_piv['total_discount'].tolist(), 2)))\n",
    "\n",
    "# define pic size\n",
    "p = figure(plot_width=1500, plot_height=800, \n",
    "           #x_axis_type=datetime, \n",
    "           title='Each Month LTV vs CPA', \n",
    "           #x_axis_label='Month', \n",
    "           #y_axis_label='LTV',\n",
    "           x_range = source1.data[\"x\"],\n",
    "           y_range= ranges.Range1d(start=0,end=1600))\n",
    "\n",
    "# LTV\n",
    "p.vbar(x=df_month_piv['basemonth'].tolist(), \n",
    "       top=df_month_piv.LTV,\n",
    "       width=0.5, \n",
    "       bottom=0, \n",
    "       color='blue', \n",
    "       legend='LTV')\n",
    "\n",
    "# net_LTV\n",
    "p.line(df_month_piv['basemonth'].tolist(), \n",
    "       df_month_piv['net_LTV'], \n",
    "       legend = 'net_LTV', \n",
    "       color=\"red\",\n",
    "       line_width=2)\n",
    "\n",
    "p.circle(df_month_piv['basemonth'].tolist(), \n",
    "         df_month_piv['net_LTV'], \n",
    "         legend = 'net_LTV', \n",
    "         fill_color=\"red\", \n",
    "         size=8)\n",
    "\n",
    "# product_cost\n",
    "p.line(df_month_piv['basemonth'].tolist(), \n",
    "       df_month_piv['product_cost'], \n",
    "       legend = 'product_cost', \n",
    "       color=\"black\",\n",
    "       line_width=2)\n",
    "\n",
    "p.circle(df_month_piv['basemonth'].tolist(),\n",
    "         df_month_piv['product_cost'], \n",
    "         legend = 'product_cost',\n",
    "         color=\"black\",\n",
    "         size=8)\n",
    "\n",
    "\n",
    "# total_discount\n",
    "p.line(df_month_piv['basemonth'].tolist(), \n",
    "       df_month_piv['total_discount'], \n",
    "       legend = 'total_discount', \n",
    "       color=\"orange\",\n",
    "       line_width=2)\n",
    "\n",
    "p.circle(df_month_piv['basemonth'].tolist(), \n",
    "         df_month_piv['total_discount'], \n",
    "         legend = 'total_discount', \n",
    "         color=\"orange\",\n",
    "         size=8)\n",
    "\n",
    "# labels\n",
    "labels1 = LabelSet(x='x', \n",
    "                  y='y', \n",
    "                  text='y', \n",
    "                  level='glyph', \n",
    "                  x_offset=-30, \n",
    "                  y_offset=0, \n",
    "                  text_font_size='17pt',\n",
    "                  text_font_style='bold',\n",
    "                  text_color='blue',\n",
    "                  source=source1, \n",
    "                  render_mode='canvas')\n",
    "p.add_layout(labels1)\n",
    "\n",
    "\n",
    "#p.add_layout(LinearAxis(y_range_name=\"bar2\"), 'right')\n",
    "p.yaxis[0].formatter = NumeralTickFormatter(format=\"$0,0\")\n",
    "#p.yaxis[1].formatter = NumeralTickFormatter(format=\"$0\")\n",
    "p.legend.location = 'bottom_left'\n",
    "p.background_fill_color = \"white\"\n",
    "p.title.text_font_size = '12pt'\n",
    "p.xaxis.major_label_text_font_size = '12pt'\n",
    "p.yaxis.major_label_text_font_size = '12pt'\n",
    "show(p, notebook_handle=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "![LTV_1Y EXAMPLE](LTV_1Y.png)"
   ]
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}