examples/Complex Example - Retargeting results report.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Retargeting results report\n",
    "\n",
    "Here is the code that is used in a real project to estimate the results of retargeting campaign.\n",
    "\n",
    "So, we have some customers that are gone, but we try to return them back.\n",
    "\n",
    "To calculate the results, we need 2 tables:\n",
    "\n",
    "- All the orders for some period of time with customers identifier;\n",
    "- Identifiers of customers that we tried to return."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from lemuras import Table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Helpful functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Normalizes phone number\n",
    "def filterPhone(x):\n",
    "    try:\n",
    "        return str(int(x))[-10:]\n",
    "    except:\n",
    "        return ''\n",
    "\n",
    "\n",
    "# Returns index of phone numbes column\n",
    "def findTels(people):\n",
    "    for col in people.columns:\n",
    "        if ('tel' in col) or ('phone' in col):\n",
    "            return people.column_indices[col]\n",
    "    return None\n",
    "\n",
    "\n",
    "# Returns index of email column\n",
    "def findMail(people):\n",
    "    for col in people.columns:\n",
    "        if 'email' in col:\n",
    "            return people.column_indices[col]\n",
    "    return None"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load and preprocess orders table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The table contains all the orders of all the customers for some period of time."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>orders.csv</b>, 6 columns, 2270 rows.<br>\n",
       "<table>\n",
       "<tr><th>client_id</th><th>dt</th><th>total</th><th>bonus</th><th>tel</th><th>email</th></tr>\n",
       "<tr><td>npOCzzDgwf3RRrSw</td><td>2018-02-08</td><td>540</td><td>0</td><td>7355155113</td><td>afgs-71@mail.ru</td></tr>\n",
       "<tr><td>fvJurzvTg8vaRp3J</td><td>2018-02-08</td><td>1000</td><td>0</td><td>7037259419</td><td>cctgrafp4cc3777@yandex.ru</td></tr>\n",
       "<tr><td>4fr4na4v8vOw33s7</td><td>2018-02-08</td><td>1010</td><td>0</td><td>7595533171</td><td>rcftgfp4cc@list.ru</td></tr>\n",
       "<tr><td>nnzjRpafAzqQnCjp</td><td>2018-02-08</td><td>920</td><td>0</td><td>7405255533</td><td>None</td></tr>\n",
       "<tr><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"orders.csv\", 6 columns, 2270 rows.\n",
       "'client_id' 'dt' 'total' 'bonus' 'tel' 'email'\n",
       "'npOCzzDgwf3RRrSw' 2018-02-08 540 0 '7355155113' 'afgs-71@mail.ru'\n",
       "'fvJurzvTg8vaRp3J' 2018-02-08 1000 0 '7037259419' 'cctgrafp4cc3777@yandex.ru'\n",
       "'4fr4na4v8vOw33s7' 2018-02-08 1010 0 '7595533171' 'rcftgfp4cc@list.ru'\n",
       "'nnzjRpafAzqQnCjp' 2018-02-08 920 0 '7405255533' None\n",
       ". . ."
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "orders = Table.from_csv('data/orders.csv', False)\n",
    "\n",
    "if not ('tel' in orders.columns and 'client_id' in orders.columns and 'total' in orders.columns):\n",
    "    print('Wrong orders table!')\n",
    "\n",
    "orders.apply('tel', filterPhone)\n",
    "\n",
    "orders"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load and preprocess people table"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The table contains identifiers of the customers that we tried to return."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>people.csv</b>, 1 columns, 4295 rows.<br>\n",
       "<table>\n",
       "<tr><th>tel</th></tr>\n",
       "<tr><td>7752253123</td></tr>\n",
       "<tr><td>7755253330</td></tr>\n",
       "<tr><td>7753253525</td></tr>\n",
       "<tr><td>7739253352</td></tr>\n",
       "<tr><th>...</th></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"people.csv\", 1 columns, 4295 rows.\n",
       "'tel'\n",
       "'7752253123'\n",
       "'7755253330'\n",
       "'7753253525'\n",
       "'7739253352'\n",
       ". . ."
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "people = Table.from_csv('data/people.csv', False)\n",
    "\n",
    "# Let's imagine that the table is loaded by some user\n",
    "# and we don't know the real structure.\n",
    "# Thus, we need to find the column.\n",
    "people_tel_ind = findTels(people)\n",
    "if people_tel_ind is None:\n",
    "    print('Wrong people table!')\n",
    "\n",
    "people.apply(people_tel_ind, lambda x: filterPhone(x))\n",
    "\n",
    "people"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Select orders by our people"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Found by phone: 2270\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Filtered orders.csv</b>, 6 columns, 100 rows.<br>\n",
       "<table>\n",
       "<tr><th>client_id</th><th>dt</th><th>total</th><th>bonus</th><th>tel</th><th>email</th></tr>\n",
       "<tr><td>vTzQyRgpAz1Dzzrd</td><td>2018-02-08</td><td>880</td><td>0</td><td>True</td><td>87788433718@mail.ru</td></tr>\n",
       "<tr><td>8QCpTluUfnC8uAsp</td><td>2018-02-08</td><td>400</td><td>110</td><td>True</td><td>None</td></tr>\n",
       "<tr><td>zpzqzAzRXcnpOaXQ</td><td>2018-02-08</td><td>600</td><td>0</td><td>True</td><td>ngtucrpzccffc@rambler.ru</td></tr>\n",
       "<tr><td>4fcnzzuwdn13rl7D</td><td>2018-02-08</td><td>500</td><td>39</td><td>True</td><td>None</td></tr>\n",
       "<tr><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Filtered orders.csv\", 6 columns, 100 rows.\n",
       "'client_id' 'dt' 'total' 'bonus' 'tel' 'email'\n",
       "'vTzQyRgpAz1Dzzrd' 2018-02-08 880 0 True '87788433718@mail.ru'\n",
       "'8QCpTluUfnC8uAsp' 2018-02-08 400 110 True None\n",
       "'zpzqzAzRXcnpOaXQ' 2018-02-08 600 0 True 'ngtucrpzccffc@rambler.ru'\n",
       "'4fcnzzuwdn13rl7D' 2018-02-08 500 39 True None\n",
       ". . ."
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "people_tel = people.column(people_tel_ind)\n",
    "orders_tel = orders.column('tel')\n",
    "\n",
    "checker = orders_tel.isin(people_tel)\n",
    "print('Found by phone: ' + str(checker.count()))\n",
    "\n",
    "people_mail_ind = findMail(people)\n",
    "if people_mail_ind is not None:\n",
    "    people_mail = people.column(people_mail_ind)\n",
    "    orders_mail = orders.column('email')\n",
    "    c_mail = (orders_mail.isin(people_mail) & (orders_mail.lengths() > 5))\n",
    "    print('Found by mail: ' + str(c_mail.count()))\n",
    "    checker = checker | c_mail\n",
    "\n",
    "united = orders.loc(checker)\n",
    "\n",
    "united.to_csv('data/united.csv')\n",
    "united"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Returned customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<b>Table</b> object, title: <b>Aggregated</b>, 6 columns, 82 rows.<br>\n",
       "<table>\n",
       "<tr><th>tel</th><th>email</th><th>client_id</th><th>TotalSum</th><th>TotalAvg</th><th>Orders</th></tr>\n",
       "<tr><td>True</td><td>87788433718@mail.ru</td><td>vTzQyRgpAz1Dzzrd</td><td>1270</td><td>635.0</td><td>2</td></tr>\n",
       "<tr><td>True</td><td>None</td><td>8QCpTluUfnC8uAsp</td><td>400</td><td>400.0</td><td>1</td></tr>\n",
       "<tr><td>True</td><td>None</td><td>4fcnzzuwdn13rl7D</td><td>979</td><td>489.5</td><td>2</td></tr>\n",
       "<tr><td>True</td><td>None</td><td>rfzza7lTpusYw4Yn</td><td>980</td><td>980.0</td><td>1</td></tr>\n",
       "<tr><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th><th>...</th></tr></table>"
      ],
      "text/plain": [
       "- Table object, title: \"Aggregated\", 6 columns, 82 rows.\n",
       "'tel' 'email' 'client_id' 'TotalSum' 'TotalAvg' 'Orders'\n",
       "True '87788433718@mail.ru' 'vTzQyRgpAz1Dzzrd' 1270 635.0 2\n",
       "True None '8QCpTluUfnC8uAsp' 400 400.0 1\n",
       "True None '4fcnzzuwdn13rl7D' 979 489.5 2\n",
       "True None 'rfzza7lTpusYw4Yn' 980 980.0 1\n",
       ". . ."
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "byclient = united.groupby(['tel', 'email', 'client_id'])\n",
    "byclient = byclient.agg({'total': {'TotalSum': 'sum', 'TotalAvg': 'mean', 'Orders': 'count'}})\n",
    "\n",
    "byclient.to_csv('data/cli.csv')\n",
    "byclient"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "- Results:\n",
      "People in group: 4295\n",
      "People returned: 82\n",
      "Retention: 1.9%\n",
      "Orders number: 100\n",
      "Orders per person: 1.21\n",
      "Average check: 1206\n",
      "Total sum: 120613\n"
     ]
    }
   ],
   "source": [
    "res = united.groupby()\n",
    "res = res.agg({'total': {'TotalSum': 'sum', 'TotalAvg': 'mean'}})\n",
    "\n",
    "ful = res.cell('TotalSum')\n",
    "avg = res.cell('TotalAvg')\n",
    "\n",
    "print('- Results:')\n",
    "print('People in group: ' + str(people.rowcnt))\n",
    "print('People returned: ' + str(byclient.rowcnt))\n",
    "print('Retention: ' + str(int(1000.0 * byclient.rowcnt / people.rowcnt) / 10.0) + '%')\n",
    "print('Orders number: ' + str(united.rowcnt))\n",
    "if (byclient.rowcnt > 0):\n",
    "    print('Orders per person: ' + str(int(100.0 * united.rowcnt / byclient.rowcnt) / 100.0))\n",
    "print('Average check: ' + str(int(avg)))\n",
    "print('Total sum: ' + str(int(ful)))"
   ]
  }
 ],
 "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.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}