{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "from copy import copy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for every string executing\n",
    "from IPython.core.interactiveshell import InteractiveShell\n",
    "InteractiveShell.ast_node_interactivity = \"all\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "bank_df = pd.read_csv('test_task_data_201808091645.csv', delimiter=';')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "## Наши данные"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 45889 entries, 0 to 45888\n",
      "Data columns (total 8 columns):\n",
      "eventdatetime      45889 non-null object\n",
      "installdatetime    45889 non-null object\n",
      "user_level         45889 non-null int64\n",
      "pack               45889 non-null object\n",
      "price              45889 non-null float64\n",
      "customeruserid     45889 non-null object\n",
      "countrycode        45887 non-null object\n",
      "bank_type          45889 non-null object\n",
      "dtypes: float64(1), int64(1), object(6)\n",
      "memory usage: 2.8+ MB\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>eventdatetime</th>\n",
       "      <th>installdatetime</th>\n",
       "      <th>user_level</th>\n",
       "      <th>pack</th>\n",
       "      <th>price</th>\n",
       "      <th>customeruserid</th>\n",
       "      <th>countrycode</th>\n",
       "      <th>bank_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-06-01 22:47:37</td>\n",
       "      <td>2018-05-22 22:49:44</td>\n",
       "      <td>120</td>\n",
       "      <td>piggy_bank</td>\n",
       "      <td>2.99</td>\n",
       "      <td>045CC293-E490-4F6A-9ED6-B0B1E302F245</td>\n",
       "      <td>AU</td>\n",
       "      <td>old_bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-06-01 22:48:48</td>\n",
       "      <td>2018-05-17 15:12:13</td>\n",
       "      <td>59</td>\n",
       "      <td>pack_1</td>\n",
       "      <td>1.99</td>\n",
       "      <td>F7F650C9-D847-46C9-B3A4-9D9C4C75AB56</td>\n",
       "      <td>US</td>\n",
       "      <td>old_bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-06-01 22:51:15</td>\n",
       "      <td>2018-05-30 16:02:33</td>\n",
       "      <td>28</td>\n",
       "      <td>pack_2</td>\n",
       "      <td>4.99</td>\n",
       "      <td>4f88616ca3347ee</td>\n",
       "      <td>US</td>\n",
       "      <td>old_bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-06-01 22:52:33</td>\n",
       "      <td>2018-05-13 02:10:43</td>\n",
       "      <td>222</td>\n",
       "      <td>adventure_pack</td>\n",
       "      <td>4.99</td>\n",
       "      <td>80C3857A-C205-430D-A889-A1D08830AE50</td>\n",
       "      <td>US</td>\n",
       "      <td>old_bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-06-01 22:52:34</td>\n",
       "      <td>2018-05-25 22:53:44</td>\n",
       "      <td>62</td>\n",
       "      <td>pack_1</td>\n",
       "      <td>1.99</td>\n",
       "      <td>5D21A355-AEB7-4141-BE13-554D738929EC</td>\n",
       "      <td>US</td>\n",
       "      <td>old_bank</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        eventdatetime     installdatetime  user_level            pack  price  \\\n",
       "0 2018-06-01 22:47:37 2018-05-22 22:49:44         120      piggy_bank   2.99   \n",
       "1 2018-06-01 22:48:48 2018-05-17 15:12:13          59          pack_1   1.99   \n",
       "2 2018-06-01 22:51:15 2018-05-30 16:02:33          28          pack_2   4.99   \n",
       "3 2018-06-01 22:52:33 2018-05-13 02:10:43         222  adventure_pack   4.99   \n",
       "4 2018-06-01 22:52:34 2018-05-25 22:53:44          62          pack_1   1.99   \n",
       "\n",
       "                         customeruserid countrycode bank_type  \n",
       "0  045CC293-E490-4F6A-9ED6-B0B1E302F245          AU  old_bank  \n",
       "1  F7F650C9-D847-46C9-B3A4-9D9C4C75AB56          US  old_bank  \n",
       "2                       4f88616ca3347ee          US  old_bank  \n",
       "3  80C3857A-C205-430D-A889-A1D08830AE50          US  old_bank  \n",
       "4  5D21A355-AEB7-4141-BE13-554D738929EC          US  old_bank  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bank_df.info()\n",
    "bank_df.bank_type.replace('old bank', 'old_bank', inplace=True)\n",
    "bank_df.eventdatetime = pd.to_datetime(bank_df.eventdatetime)\n",
    "bank_df.installdatetime = pd.to_datetime(bank_df.installdatetime)\n",
    "bank_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4.989019352033051"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bank_df.customeruserid.count()/bank_df.customeruserid.value_counts().count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "### Немного общей инфы\n",
    "**По каждому предложению сколько раз его купили**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pack_1            19741\n",
       "pack_2            10192\n",
       "pack_3             6118\n",
       "pack_4             2508\n",
       "piggy_bank         2235\n",
       "starter_pack       1718\n",
       "adventure_pack     1496\n",
       "smart_offer_2       585\n",
       "smart_offer_3       325\n",
       "pack_5              261\n",
       "smart_offer_1       222\n",
       "pack_6              207\n",
       "smart_offer_4       203\n",
       "smart_offer_5        59\n",
       "smart_offer_6        19\n",
       "Name: pack, dtype: int64"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bank_df.pack.value_counts(sort=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Сколько было покупок для каждой цены вообще**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1.99     19741\n",
       "4.99     11688\n",
       "9.99      6118\n",
       "2.99      3953\n",
       "19.99     2508\n",
       "3.99       585\n",
       "7.99       325\n",
       "49.99      261\n",
       "0.99       222\n",
       "99.99      207\n",
       "14.99      203\n",
       "34.99       59\n",
       "74.99       19\n",
       "Name: price, dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x2775bb09550>"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 21268 characters]\n",
      "text/plain": [
       "<Figure size 1440x720 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sns.set(rc={'figure.figsize':(20,10)})\n",
    "bank_df.price.value_counts()\n",
    "bank_df.price.value_counts().plot.bar()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x2775a99bbe0>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 20236 characters]\n",
      "text/plain": [
       "<Figure size 1440x720 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "sns.countplot('price', data=bank_df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Видно, что прайс в 35 и 75 совсем почти не покупается. Либо потому, что в банке во всплывающем окошке не появляются эти карточки, либо это никому не интересно. Скорее первое. Потому что остальные карточки с большим колвом продаж есть на экране. В общем с этой штукой можно поиграться и посмотреть. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 35964 characters]\n",
      "text/plain": [
       "<Figure size 1440x720 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bank_df.price.plot(kind ='kde', figsize=(20,10), logy=False);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pack\n",
       "adventure_pack     4.99\n",
       "pack_1             1.99\n",
       "pack_2             4.99\n",
       "pack_3             9.99\n",
       "pack_4            19.99\n",
       "pack_5            49.99\n",
       "pack_6            99.99\n",
       "piggy_bank         2.99\n",
       "smart_offer_1      0.99\n",
       "smart_offer_2      3.99\n",
       "smart_offer_3      7.99\n",
       "smart_offer_4     14.99\n",
       "smart_offer_5     34.99\n",
       "smart_offer_6     74.99\n",
       "starter_pack       2.99\n",
       "Name: price, dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bank_df.groupby('pack').price.mean()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "smart pack exists only in new bank\n",
      "\n",
      "new_bank    1413\n",
      "Name: bank_type, dtype: int64\n",
      "\n"
     ]
    }
   ],
   "source": [
    "print('smart pack exists only in new bank\\n\\n{}'.format(bank_df[bank_df.pack.str.contains('smart')].bank_type.value_counts()), end='\\n\\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Посчитаем в какой ценовой категории соотношение покупок больше всего** \n",
    "\n",
    "Тут получается, что покупки с ценником больше 10 не так уж и много. Опять же прайс меньше 10 делают в два раза больше выручки, чем прайс больше."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "count of goods for price < 10 is 42632 with all over amount of money 175696.68000000002\n",
      "count of goods for price > 10 is 3257 with all over amount of money 90412.43\n",
      "count of goods for price between 10 and 20 is 2711 with all over amount of money 53177.89000000001\n"
     ]
    }
   ],
   "source": [
    "print('count of goods for price < 10 is', bank_df[bank_df.price < 10].price.count(), \n",
    "      'with all over amount of money', bank_df[bank_df.price < 10].price.sum())\n",
    "print('count of goods for price > 10 is', bank_df[bank_df.price > 10].price.count(), \n",
    "      'with all over amount of money', bank_df[bank_df.price > 10].price.sum())\n",
    "print('count of goods for price between 10 and 20 is', bank_df[(bank_df.price > 10) & (bank_df.price < 20)].price.count(), \n",
    "      'with all over amount of money', bank_df[(bank_df.price > 10) & (bank_df.price < 20)].price.sum())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Банк старый, банк новый\n",
    "\n",
    "Сколько операций было совершено в новом и старом банке? \n",
    "Операций в новом банке в полтора раза больше, чем в старом. Надо запомнить, это существенное различие. Также видно, что за счет новых предложений появился больше выбор. И люди этот выбор делают."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "new_bank    27000\n",
       "old_bank    18889\n",
       "Name: bank_type, dtype: int64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "coef for comparing = 1.4294033564508444\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<matplotlib.axes._subplots.AxesSubplot at 0x2775af5b7f0>"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "[encoded data, 21676 characters]\n",
      "text/plain": [
       "<Figure size 1440x720 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "bank_df.bank_type.value_counts()\n",
    "norm_coef = (bank_df.bank_type.value_counts()/bank_df.bank_type.value_counts()['old_bank'])[0]\n",
    "print('coef for comparing = {}'.format(norm_coef))\n",
    "sns.countplot('price', data=bank_df, hue='bank_type')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Что там со средним чеком?** \n",
    "\n",
    "Кажется новый банк получше все таки продает. На графике плохо, но видно, что средний чек поднялся и распределение чека у нового банка похудело внизу и потолстело вверху, и можно предположить, что игроки теперь чаще покупают карточки дороже. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "average bill\n",
      "bank_type\n",
      "new_bank    5.996889\n",
      "old_bank    5.516073\n",
      "Name: price, dtype: float64\n",
      "\n",
      "median bill\n",
      "bank_type\n",
      "new_bank    3.99\n",
      "old_bank    2.99\n",
      "Name: price, dtype: float64\n"
     ]
    },
    {
     "data": {
      "image/png": "[encoded data, 39524 characters]\n",
      "text/plain": [
       "<Figure size 1440x720 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "print('average bill')\n",
    "print(bank_df.groupby(bank_df.bank_type).price.mean())\n",
    "print('\\nmedian bill')\n",
    "print(bank_df.groupby(bank_df.bank_type).price.median())\n",
    "sns.violinplot(data=bank_df, y='price', x='bank_type', gridsize=300, cut=5);"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Как мы видим и действительно, для дешевых покупок ничего не изменилось в распределениях трат между банками. \n",
    "\n",
    "1.4 ~ 1.42\n",
    "\n",
    "А вот в дорогом сегменте карточек покупок стало существенно больше. \n",
    "\n",
    "1.7 > 1.4 почти в 1.3 раза."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "for prices < 10 counts of sale of new and old bank\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "new_bank    24913\n",
       "old_bank    17719\n",
       "Name: bank_type, dtype: int64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "new_bank    1.000000\n",
       "old_bank    1.406005\n",
       "Name: bank_type, dtype: float64"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# how much new bank best sale low prices packs vs old bank\n",
    "print('for prices < 10 counts of sale of new and old bank')\n",
    "bank_df[bank_df.price < 10].bank_type.value_counts()\n",
    "bank_df[bank_df.price < 10].bank_type.value_counts().new_bank / bank_df[bank_df.price < 10].bank_type.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "for prices > 10 counts of sale of new and old bank\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "new_bank    2087\n",
       "old_bank    1170\n",
       "Name: bank_type, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "new_bank    1.000000\n",
       "old_bank    1.783761\n",
       "Name: bank_type, dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/plain": [
       "1.2479057613168725"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('for prices > 10 counts of sale of new and old bank')\n",
    "bank_df[bank_df.price > 10].bank_type.value_counts()\n",
    "bank_df[bank_df.price > 10].bank_type.value_counts().new_bank / bank_df[bank_df.price > 10].bank_type.value_counts()\n",
    "bank_df[bank_df.price > 10].bank_type.value_counts().new_bank / bank_df[bank_df.price > 10].bank_type.value_counts()[1] / norm_coef"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "На спецальные паки кажется тоже существенно повлияло смена банка. Причем, тут разница от двух до трех раз, что опять же существенно больше, чем 1.4."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "piggy distribution by bank type\n",
      "\n",
      "new_bank    1725\n",
      "old_bank     510\n",
      "Name: bank_type, dtype: int64\n",
      "\n",
      "adventure distibution by bank type\n",
      "\n",
      "new_bank    975\n",
      "old_bank    521\n",
      "Name: bank_type, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print('piggy distribution by bank type\\n\\n{}'.format(bank_df[bank_df.pack.str.contains('piggy')].bank_type.value_counts()), end='\\n\\n')\n",
    "\n",
    "print('adventure distibution by bank type\\n\\n{}'.format(bank_df[bank_df.pack.str.contains('advent')].bank_type.value_counts()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "toc-hr-collapsed": false
   },
   "source": [
    "### Умные скидки?\n",
    "\n",
    "**Посчитаем, как влияет умное предложение на постоянных покупателей**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "amount of good customers 3006\n",
      "\n",
      "distribution and amount of smart_offers of good customers:\n",
      "\n",
      "smart_offer_2    413\n",
      "smart_offer_3    274\n",
      "smart_offer_4    177\n",
      "smart_offer_5     57\n",
      "smart_offer_1     45\n",
      "smart_offer_6     18\n",
      "Name: pack, dtype: int64\n",
      "\n",
      "distribution and amount of smart_offers of bad customers:\n",
      "\n",
      "smart_offer_1    177\n",
      "smart_offer_2    172\n",
      "smart_offer_3     51\n",
      "smart_offer_4     26\n",
      "smart_offer_5      2\n",
      "smart_offer_6      1\n",
      "Name: pack, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# define good customers as who buy more then 3 times\n",
    "good_customers = bank_df.customeruserid.value_counts()[(bank_df.customeruserid.value_counts() > 3)].index\n",
    "print('amount of good customers {}'.format(len(good_customers)))\n",
    "\n",
    "# and see what if smart offers affect on good and bad customers\n",
    "print('\\ndistribution and amount of smart_offers of good customers:\\n')\n",
    "print(bank_df[bank_df.customeruserid.isin(good_customers)].pack.value_counts() \\\n",
    "[bank_df[bank_df.customeruserid.isin(good_customers)].pack.value_counts().index.str.contains('smart')])\n",
    "print('\\ndistribution and amount of smart_offers of bad customers:\\n')\n",
    "print(bank_df[~bank_df.customeruserid.isin(good_customers)].pack.value_counts() \\\n",
    "[bank_df[~bank_df.customeruserid.isin(good_customers)].pack.value_counts().index.str.contains('smart')])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Фан факт.** Клиент который заплатил через 6 секунд после установки приложения, причем будучи сразу 9 уровня, платил всего три раза и в старом банке, правда второй платеж был на крупную сумму. А кто ждал почти два года до первой оплаты заплатил уже в новом банке и тоже всего три раза."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "min time between install and pay 0 days 00:00:06\n",
      "max time between install and pay 533 days 08:31:58\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>eventdatetime</th>\n",
       "      <th>installdatetime</th>\n",
       "      <th>user_level</th>\n",
       "      <th>pack</th>\n",
       "      <th>price</th>\n",
       "      <th>customeruserid</th>\n",
       "      <th>countrycode</th>\n",
       "      <th>bank_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>24322</th>\n",
       "      <td>2018-07-24 09:33:01</td>\n",
       "      <td>2017-02-18 20:51:00</td>\n",
       "      <td>100</td>\n",
       "      <td>pack_2</td>\n",
       "      <td>4.99</td>\n",
       "      <td>7B057292-CA40-4491-A5DE-40B395C3A304</td>\n",
       "      <td>CA</td>\n",
       "      <td>new_bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27703</th>\n",
       "      <td>2018-07-29 09:31:37</td>\n",
       "      <td>2017-02-18 20:51:00</td>\n",
       "      <td>103</td>\n",
       "      <td>pack_3</td>\n",
       "      <td>9.99</td>\n",
       "      <td>7B057292-CA40-4491-A5DE-40B395C3A304</td>\n",
       "      <td>CA</td>\n",
       "      <td>new_bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32962</th>\n",
       "      <td>2018-08-06 05:22:58</td>\n",
       "      <td>2017-02-18 20:51:00</td>\n",
       "      <td>103</td>\n",
       "      <td>adventure_pack</td>\n",
       "      <td>4.99</td>\n",
       "      <td>7B057292-CA40-4491-A5DE-40B395C3A304</td>\n",
       "      <td>CA</td>\n",
       "      <td>new_bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34801</th>\n",
       "      <td>2018-05-18 14:18:43</td>\n",
       "      <td>2018-05-18 14:18:37</td>\n",
       "      <td>9</td>\n",
       "      <td>pack_3</td>\n",
       "      <td>9.99</td>\n",
       "      <td>31F22D78-5C12-4E79-87CB-B870EF5FE171</td>\n",
       "      <td>CN</td>\n",
       "      <td>old_bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35631</th>\n",
       "      <td>2018-05-20 13:54:24</td>\n",
       "      <td>2018-05-18 14:18:37</td>\n",
       "      <td>17</td>\n",
       "      <td>pack_6</td>\n",
       "      <td>99.99</td>\n",
       "      <td>31F22D78-5C12-4E79-87CB-B870EF5FE171</td>\n",
       "      <td>CN</td>\n",
       "      <td>old_bank</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37995</th>\n",
       "      <td>2018-05-22 15:19:59</td>\n",
       "      <td>2018-05-18 14:18:37</td>\n",
       "      <td>32</td>\n",
       "      <td>starter_pack</td>\n",
       "      <td>2.99</td>\n",
       "      <td>31F22D78-5C12-4E79-87CB-B870EF5FE171</td>\n",
       "      <td>CN</td>\n",
       "      <td>old_bank</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            eventdatetime     installdatetime  user_level            pack  \\\n",
       "24322 2018-07-24 09:33:01 2017-02-18 20:51:00         100          pack_2   \n",
       "27703 2018-07-29 09:31:37 2017-02-18 20:51:00         103          pack_3   \n",
       "32962 2018-08-06 05:22:58 2017-02-18 20:51:00         103  adventure_pack   \n",
       "34801 2018-05-18 14:18:43 2018-05-18 14:18:37           9          pack_3   \n",
       "35631 2018-05-20 13:54:24 2018-05-18 14:18:37          17          pack_6   \n",
       "37995 2018-05-22 15:19:59 2018-05-18 14:18:37          32    starter_pack   \n",
       "\n",
       "       price                        customeruserid countrycode bank_type  \n",
       "24322   4.99  7B057292-CA40-4491-A5DE-40B395C3A304          CA  new_bank  \n",
       "27703   9.99  7B057292-CA40-4491-A5DE-40B395C3A304          CA  new_bank  \n",
       "32962   4.99  7B057292-CA40-4491-A5DE-40B395C3A304          CA  new_bank  \n",
       "34801   9.99  31F22D78-5C12-4E79-87CB-B870EF5FE171          CN  old_bank  \n",
       "35631  99.99  31F22D78-5C12-4E79-87CB-B870EF5FE171          CN  old_bank  \n",
       "37995   2.99  31F22D78-5C12-4E79-87CB-B870EF5FE171          CN  old_bank  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('min time between install and pay {}'.format((bank_df.eventdatetime - bank_df.installdatetime).min()))\n",
    "print('max time between install and pay {}'.format((bank_df.eventdatetime - bank_df.installdatetime).max()))\n",
    "lcind = bank_df.iloc[0:,0:2].diff(periods = 1, axis=1).nsmallest(1, 'installdatetime').index\n",
    "fcind = bank_df.iloc[0:,0:2].diff(periods = -1, axis=1).nsmallest(1, 'eventdatetime').index\n",
    "bank_df[bank_df.customeruserid.isin(bank_df.iloc[[lcind[0], fcind[0]]].customeruserid)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Хорошо, найдем всех пользователей, которые покупали и в старом банке и в новом банке. И посмотрим у них средний чек по банкам."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "temptable = bank_df[bank_df.customeruserid.duplicated(False)].sort_values(by='customeruserid').reset_index().drop('index',axis=1)\n",
    "custid = set()\n",
    "endofindex = temptable.index.max()\n",
    "for ind in temptable.index:\n",
    "    if ind == endofindex:\n",
    "        pass\n",
    "    elif ((temptable.iat[ind, 5] == temptable.iat[ind+1, 5]) & (temptable.iat[ind, 7] != temptable.iat[ind+1, 7])):\n",
    "        custid.add(temptable.iat[ind, 5])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "avg check \n",
      "bank_type\n",
      "new_bank    6.350488\n",
      "old_bank    5.657405\n",
      "Name: price, dtype: float64\n",
      "\n",
      "median check \n",
      "bank_type\n",
      "new_bank    4.99\n",
      "old_bank    1.99\n",
      "Name: price, dtype: float64\n"
     ]
    }
   ],
   "source": [
    "print('avg check \\n{}'.format(\n",
    "bank_df[bank_df.customeruserid.isin(custid)].groupby('bank_type').price.mean()))\n",
    "print('\\nmedian check \\n{}'.format(\n",
    "bank_df[bank_df.customeruserid.isin(custid)].groupby('bank_type').price.median()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Как видно, новый банк положительно повлиял на средний чек, а также медиана выросла, что говорит нам о том, что больше половины людей стали покупать более дорогие карточки"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Итоги\n",
    "Вроде вполне понятно, что новый банк привел к увеличению выручки, а также увеличению среднего чека и пользовательского поведения, в сторону покупок более дорогих карточек с валютой. \n",
    "Можно все проверить статистически, но на мой взгляд все наглядно и так.\n",
    "Умные предложения не вносят пока существенной разницы в поведении кажется, тем более рано еще судить, так как их относительно недавно ввели, первая покупка по ним была в августе. \n",
    "Еще не накопилось достаточно данных, хотя уже можно сказать, что те платил раньше с охотой пользуются этими предложениями, а те, \n",
    "кто раньше не платил, некоторые все таки начали именно с этих умных предложений. \n",
    "Понятно, что из данных не выжато еще все. Можно еще проверить некоторые гипотезы, посмотреть зависимость от времени в покупках, от страны, и того как растет уровень в зависимости от оплат. \n",
    "Если же добавить данных, то станет что то еще более понятным. \n",
    "В остальном же, гипотезы первоначально проверены, стоит копать дальше в конкретное какое то поведени."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  },
  "toc-autonumbering": false,
  "toc-showcode": true,
  "toc-showmarkdowntxt": true,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
