{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "f8c7a62b",
   "metadata": {},
   "source": [
    "# Библиотеки"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c6bb3ab6",
   "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 tqdm import tqdm\n",
    "from pprint import pprint"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bae2a8b8",
   "metadata": {},
   "source": [
    "# TODO\n",
    "\n",
    "1. [x] Подписи к графикам\n",
    "2. [x] Переделать все под `pd.DataFrame` (?)\n",
    "3. [x] Поправить заголовки"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7cfc02e",
   "metadata": {},
   "source": [
    "# Данные"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "67c32cdd",
   "metadata": {},
   "source": [
    "## Payments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "038a62d8",
   "metadata": {},
   "outputs": [
    {
     "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>payment_date</th>\n",
       "      <th>Payment_types</th>\n",
       "      <th>real_cost</th>\n",
       "      <th>account_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2021-01-01 00:00:56</td>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>9.650000</td>\n",
       "      <td>2907221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2021-01-01 00:01:48</td>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>0.955872</td>\n",
       "      <td>3228373</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2021-01-01 00:03:42</td>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>3.026930</td>\n",
       "      <td>318552</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2021-01-01 00:04:27</td>\n",
       "      <td>Payment_system_2</td>\n",
       "      <td>99.990000</td>\n",
       "      <td>3832817</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2021-01-01 00:06:41</td>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>1.432550</td>\n",
       "      <td>7229767</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          payment_date     Payment_types  real_cost  account_id\n",
       "0  2021-01-01 00:00:56  Payment_system_5   9.650000     2907221\n",
       "1  2021-01-01 00:01:48  Payment_system_5   0.955872     3228373\n",
       "2  2021-01-01 00:03:42  Payment_system_5   3.026930      318552\n",
       "3  2021-01-01 00:04:27  Payment_system_2  99.990000     3832817\n",
       "4  2021-01-01 00:06:41  Payment_system_5   1.432550     7229767"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "payments = pd.read_csv(\"data/payments.csv\")\n",
    "payments.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68048c5a",
   "metadata": {},
   "source": [
    "## Persents"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "a4b72e40",
   "metadata": {},
   "outputs": [
    {
     "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>Payment_types</th>\n",
       "      <th>Share, %</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Payment_system_1</td>\n",
       "      <td>20.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Payment_system_2</td>\n",
       "      <td>15.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Payment_system_3</td>\n",
       "      <td>58.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Payment_system_4</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>24.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Payment_types  Share, %\n",
       "0  Payment_system_1      20.7\n",
       "1  Payment_system_2      15.0\n",
       "2  Payment_system_3      58.5\n",
       "3  Payment_system_4       0.0\n",
       "4  Payment_system_5      24.0"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "persents = pd.read_csv(\"data/persents.csv\", index_col=0)\n",
    "persents.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a2ba6d3",
   "metadata": {},
   "source": [
    "## Registrations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a0f238ba",
   "metadata": {},
   "outputs": [
    {
     "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>account_id</th>\n",
       "      <th>created_date</th>\n",
       "      <th>campaign</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3842380.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>BRA_MS1_install</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3842381.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3842382.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3842383.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3842384.0</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>BRA_MS1_install</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   account_id created_date         campaign\n",
       "0   3842380.0   2021-01-01  BRA_MS1_install\n",
       "1   3842381.0   2021-01-01              NaN\n",
       "2   3842382.0   2021-01-01              NaN\n",
       "3   3842383.0   2021-01-01              NaN\n",
       "4   3842384.0   2021-01-01  BRA_MS1_install"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "registrations = pd.read_csv(\"data/registrations.csv\", index_col=0)#.dropna()\n",
    "registrations.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f52e9466",
   "metadata": {},
   "source": [
    "## Budget"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bf031a83",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "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>media_source</th>\n",
       "      <th>Campaign_type</th>\n",
       "      <th>Target</th>\n",
       "      <th>Plan, USD</th>\n",
       "      <th>Installs</th>\n",
       "      <th>Spend, USD</th>\n",
       "      <th>Deviation, USD</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Media_source_1</td>\n",
       "      <td>install</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>100</td>\n",
       "      <td>10460</td>\n",
       "      <td>99.12</td>\n",
       "      <td>0.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Media_source_1</td>\n",
       "      <td>purchase</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>400</td>\n",
       "      <td>1081</td>\n",
       "      <td>398.67</td>\n",
       "      <td>1.33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Media_source_1</td>\n",
       "      <td>install</td>\n",
       "      <td>Russia</td>\n",
       "      <td>200</td>\n",
       "      <td>11894</td>\n",
       "      <td>246.30</td>\n",
       "      <td>-46.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Media_source_1</td>\n",
       "      <td>purchase</td>\n",
       "      <td>Russia</td>\n",
       "      <td>500</td>\n",
       "      <td>1441</td>\n",
       "      <td>616.77</td>\n",
       "      <td>-116.77</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Media_source_1</td>\n",
       "      <td>install</td>\n",
       "      <td>Ukrane</td>\n",
       "      <td>100</td>\n",
       "      <td>6424</td>\n",
       "      <td>99.43</td>\n",
       "      <td>0.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Media_source_1</td>\n",
       "      <td>purchase</td>\n",
       "      <td>Ukrane</td>\n",
       "      <td>500</td>\n",
       "      <td>242</td>\n",
       "      <td>348.13</td>\n",
       "      <td>151.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Media_source_2</td>\n",
       "      <td>install</td>\n",
       "      <td>Brazil</td>\n",
       "      <td>100</td>\n",
       "      <td>152</td>\n",
       "      <td>81.15</td>\n",
       "      <td>18.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Media_source_2</td>\n",
       "      <td>install</td>\n",
       "      <td>English_speaking</td>\n",
       "      <td>100</td>\n",
       "      <td>371</td>\n",
       "      <td>99.95</td>\n",
       "      <td>0.05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Total</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2000</td>\n",
       "      <td>32065</td>\n",
       "      <td>1989.52</td>\n",
       "      <td>10.48</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     media_source Campaign_type            Target  Plan, USD  Installs  \\\n",
       "0  Media_source_1       install            Brazil        100     10460   \n",
       "1  Media_source_1      purchase            Brazil        400      1081   \n",
       "2  Media_source_1       install            Russia        200     11894   \n",
       "3  Media_source_1      purchase            Russia        500      1441   \n",
       "4  Media_source_1       install            Ukrane        100      6424   \n",
       "5  Media_source_1      purchase            Ukrane        500       242   \n",
       "6  Media_source_2       install            Brazil        100       152   \n",
       "7  Media_source_2       install  English_speaking        100       371   \n",
       "8           Total           NaN               NaN       2000     32065   \n",
       "\n",
       "   Spend, USD  Deviation, USD  \n",
       "0       99.12            0.88  \n",
       "1      398.67            1.33  \n",
       "2      246.30          -46.30  \n",
       "3      616.77         -116.77  \n",
       "4       99.43            0.57  \n",
       "5      348.13          151.87  \n",
       "6       81.15           18.85  \n",
       "7       99.95            0.05  \n",
       "8     1989.52           10.48  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "budget = pd.read_excel(\"data/Рекламный бюджет январь 2021.xlsx\")\n",
    "budget.loc[0:5, 'media_source'] = 'Media_source_1'\n",
    "budget.loc[6:7, 'media_source'] = 'Media_source_2'\n",
    "budget"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "561d9fce",
   "metadata": {},
   "source": [
    "# Analisys"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e9e2cb6",
   "metadata": {},
   "source": [
    "## Оптимальный срок окупаемости"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1701b5e5",
   "metadata": {},
   "source": [
    "$$\\Large\n",
    "T = \\frac{IC}{FV}\n",
    "$$\n",
    "\n",
    "где:\n",
    "\n",
    "- $\\large T$  — срок окупаемости\n",
    "- $\\large IC$ — инвестиционные расходы\n",
    "- $\\large FV$ — прибыль"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "8353352e",
   "metadata": {},
   "outputs": [
    {
     "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>payment_date</th>\n",
       "      <th>Payment_types</th>\n",
       "      <th>real_cost</th>\n",
       "      <th>account_id</th>\n",
       "      <th>Share, %</th>\n",
       "      <th>cost</th>\n",
       "      <th>created_date</th>\n",
       "      <th>campaign</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>2021-01-01 15:10:26</td>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>2.445710</td>\n",
       "      <td>3861811</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.858740</td>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>RUS_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>2021-01-01 15:22:00</td>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>2.445710</td>\n",
       "      <td>3861811</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.858740</td>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>RUS_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>827</th>\n",
       "      <td>2021-01-01 17:16:09</td>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>9.624570</td>\n",
       "      <td>3844647</td>\n",
       "      <td>24.0</td>\n",
       "      <td>7.314673</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>BRA_MS1_install</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>837</th>\n",
       "      <td>2021-01-01 17:17:45</td>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>9.624570</td>\n",
       "      <td>3844647</td>\n",
       "      <td>24.0</td>\n",
       "      <td>7.314673</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>BRA_MS1_install</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1922</th>\n",
       "      <td>2021-01-02 01:08:32</td>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>0.955872</td>\n",
       "      <td>3845613</td>\n",
       "      <td>24.0</td>\n",
       "      <td>0.726463</td>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>RUS_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301124</th>\n",
       "      <td>2021-12-29 17:14:22</td>\n",
       "      <td>Payment_system_4</td>\n",
       "      <td>2.620000</td>\n",
       "      <td>3924840</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.620000</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>RUS_MS1_install</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301169</th>\n",
       "      <td>2021-12-29 18:55:43</td>\n",
       "      <td>Payment_system_4</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>3924840</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>RUS_MS1_install</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301243</th>\n",
       "      <td>2021-12-29 21:22:10</td>\n",
       "      <td>Payment_system_4</td>\n",
       "      <td>0.310000</td>\n",
       "      <td>3924840</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.310000</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>RUS_MS1_install</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>301471</th>\n",
       "      <td>2021-12-30 08:15:58</td>\n",
       "      <td>Payment_system_4</td>\n",
       "      <td>0.380000</td>\n",
       "      <td>3924840</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.380000</td>\n",
       "      <td>2021-01-29</td>\n",
       "      <td>RUS_MS1_install</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303332</th>\n",
       "      <td>2021-12-31 20:58:48</td>\n",
       "      <td>Payment_system_5</td>\n",
       "      <td>2.309120</td>\n",
       "      <td>3861811</td>\n",
       "      <td>24.0</td>\n",
       "      <td>1.754931</td>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>RUS_MS1_purchase</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>711 rows × 8 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "               payment_date     Payment_types  real_cost  account_id  \\\n",
       "330     2021-01-01 15:10:26  Payment_system_5   2.445710     3861811   \n",
       "339     2021-01-01 15:22:00  Payment_system_5   2.445710     3861811   \n",
       "827     2021-01-01 17:16:09  Payment_system_5   9.624570     3844647   \n",
       "837     2021-01-01 17:17:45  Payment_system_5   9.624570     3844647   \n",
       "1922    2021-01-02 01:08:32  Payment_system_5   0.955872     3845613   \n",
       "...                     ...               ...        ...         ...   \n",
       "301124  2021-12-29 17:14:22  Payment_system_4   2.620000     3924840   \n",
       "301169  2021-12-29 18:55:43  Payment_system_4   0.100000     3924840   \n",
       "301243  2021-12-29 21:22:10  Payment_system_4   0.310000     3924840   \n",
       "301471  2021-12-30 08:15:58  Payment_system_4   0.380000     3924840   \n",
       "303332  2021-12-31 20:58:48  Payment_system_5   2.309120     3861811   \n",
       "\n",
       "        Share, %      cost created_date          campaign  \n",
       "330         24.0  1.858740   2021-01-07  RUS_MS1_purchase  \n",
       "339         24.0  1.858740   2021-01-07  RUS_MS1_purchase  \n",
       "827         24.0  7.314673   2021-01-01   BRA_MS1_install  \n",
       "837         24.0  7.314673   2021-01-01   BRA_MS1_install  \n",
       "1922        24.0  0.726463   2021-01-02  RUS_MS1_purchase  \n",
       "...          ...       ...          ...               ...  \n",
       "301124       0.0  2.620000   2021-01-29   RUS_MS1_install  \n",
       "301169       0.0  0.100000   2021-01-29   RUS_MS1_install  \n",
       "301243       0.0  0.310000   2021-01-29   RUS_MS1_install  \n",
       "301471       0.0  0.380000   2021-01-29   RUS_MS1_install  \n",
       "303332      24.0  1.754931   2021-01-07  RUS_MS1_purchase  \n",
       "\n",
       "[711 rows x 8 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = payments.join(persents.set_index(\"Payment_types\"), on=\"Payment_types\")\n",
    "df['cost'] = df['real_cost'] * (100 - df['Share, %']) / 100\n",
    "df = df.join(registrations.set_index('account_id'), on=\"account_id\").dropna()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "0a797a02",
   "metadata": {},
   "outputs": [],
   "source": [
    "d = {\n",
    "    (\"Media_source_1\",       \"install\",            \"Brazil\"): \"BRA_MS1_install\",\n",
    "    (\"Media_source_1\",      \"purchase\",            \"Brazil\"): \"BRA_MS1_purchase\",\n",
    "    (\"Media_source_1\",       \"install\",            \"Russia\"): \"RUS_MS1_install\",\n",
    "    (\"Media_source_1\",      \"purchase\",            \"Russia\"): \"RUS_MS1_purchase\",\n",
    "    (\"Media_source_1\",       \"install\",            \"Ukrane\"): \"UKR_MS1_install\",\n",
    "    (\"Media_source_1\",      \"purchase\",            \"Ukrane\"): \"UKR_MS1_purchase\",\n",
    "    (\"Media_source_2\",       \"install\",            \"Brazil\"): \"BRA_MS2_install\",\n",
    "    (\"Media_source_2\",       \"install\",  \"English_speaking\"): \"ENG_MS2_install\",\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "95da41ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "T = []\n",
    "for l in budget.iloc:\n",
    "    source, campaign_type, target, *_, spend, _ = l\n",
    "    if source == 'Total':\n",
    "        break\n",
    "    cmp = d[source, campaign_type, target]\n",
    "    tmp_df = df[df['campaign'] == cmp]\n",
    "    T.append((source, campaign_type, target, spend / tmp_df['cost'].sum()))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "e950eeaf",
   "metadata": {},
   "outputs": [],
   "source": [
    "T_df = pd.DataFrame(T, columns=[\"Media Source\", 'Campaign type', 'Target', \"Time, (months)\"])\n",
    "T_df = T_df.set_index([\"Media Source\", 'Campaign type', 'Target'])\n",
    "T_df.to_csv('tables/payback-period.csv')\n",
    "T_df.to_markdown('tables/payback-period.md')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "772ce00f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 114444 characters]",
      "text/plain": [
       "<Figure size 1280x960 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(1, 1, figsize=(8,6), dpi=160)\n",
    "ax.grid()\n",
    "# ax.set_xlim(1, 70)\n",
    "ax.set(xlabel='Срок окупаемости, (месяцы)', \n",
    "       ylabel='Рекламная компания',\n",
    "       title='Оптимальный срок окупаемости')\n",
    "\n",
    "plt.xticks(rotation='vertical')\n",
    "sns.barplot(ax=ax, data=T_df, x=\"Time, (months)\", hue=T_df.index)\n",
    "plt.legend(title=\"\");\n",
    "plt.savefig(\"pics/payback-period.png\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "29c916e9",
   "metadata": {},
   "source": [
    "## Определить наиболее успешные кейсы"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b307b90",
   "metadata": {},
   "source": [
    "### Рекламные кампании"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "9dfee9c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "T_sorted_df = T_df.sort_values(by='Time, (months)')\n",
    "T_sorted_df.to_csv('tables/payback-period.sorted.csv')\n",
    "T_sorted_df.to_markdown('tables/payback-period.sorted.md')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "2821cb8e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 113452 characters]",
      "text/plain": [
       "<Figure size 1280x960 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(1, 1, figsize=(8,6), dpi=160)\n",
    "ax.grid()\n",
    "ax.set(xlabel='Срок окупаемости, месяцы', ylabel='Рекламная компания', title='Оптимальный срок окупаемости')\n",
    "\n",
    "# plt.xticks(rotation='vertical')\n",
    "sns.barplot(ax=ax, data=T_sorted_df, x=\"Time, (months)\", hue=T_df.index)\n",
    "plt.legend(title=\"\");\n",
    "plt.savefig(\"pics/payback-period.sorted.png\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1543549",
   "metadata": {},
   "source": [
    "### Тип рекламных кампаний"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "93819b37",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 47412 characters]",
      "text/plain": [
       "<Figure size 640x960 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(1, 1, figsize=(4,6), dpi=160)\n",
    "ax.grid()\n",
    "ax.set(title='Оптимальный срок окупаемости, \\nсгруппированный по типам рекламных кампаний')\n",
    "\n",
    "sns.boxplot(ax=ax, data=T_sorted_df, y='Time, (months)', hue=\"Campaign type\");\n",
    "plt.savefig(\"pics/payback-period.ct.png\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "781727e6",
   "metadata": {},
   "source": [
    "### Рекламная сеть"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "588eab3e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 48472 characters]",
      "text/plain": [
       "<Figure size 640x960 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(1, 1, figsize=(4,6), dpi=160)\n",
    "ax.grid()\n",
    "ax.set(title='Оптимальный срок окупаемости, \\nсгруппированный по рекламным сетям')\n",
    "\n",
    "sns.boxplot(ax=ax, data=T_sorted_df, y='Time, (months)', hue=\"Media Source\");\n",
    "plt.savefig(\"pics/payback-period.ms.png\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2128f1b7",
   "metadata": {},
   "source": [
    "## Накопительный ARPU успешных рекламных кампаний"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "48a87a99",
   "metadata": {},
   "source": [
    "$$\\Large\n",
    "ARPU = \n",
    "\\frac{\\text{Доход}}{\\text{Число пользователей}}\n",
    "$$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "c122ab03",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date, datetime, timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3723a9b9",
   "metadata": {},
   "outputs": [
    {
     "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>account_id</th>\n",
       "      <th>created_date</th>\n",
       "      <th>payment_date</th>\n",
       "      <th>cost</th>\n",
       "      <th>campaign</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>330</th>\n",
       "      <td>3861811</td>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>1.858740</td>\n",
       "      <td>RUS_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>339</th>\n",
       "      <td>3861811</td>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>1.858740</td>\n",
       "      <td>RUS_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1922</th>\n",
       "      <td>3845613</td>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>0.726463</td>\n",
       "      <td>RUS_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2920</th>\n",
       "      <td>3843913</td>\n",
       "      <td>2021-01-01</td>\n",
       "      <td>2021-01-02</td>\n",
       "      <td>0.726463</td>\n",
       "      <td>RUS_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4335</th>\n",
       "      <td>3908313</td>\n",
       "      <td>2021-01-22</td>\n",
       "      <td>2021-01-03</td>\n",
       "      <td>4.237699</td>\n",
       "      <td>UKR_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>290371</th>\n",
       "      <td>3864062</td>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>2021-12-17</td>\n",
       "      <td>0.619811</td>\n",
       "      <td>RUS_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>295786</th>\n",
       "      <td>3908063</td>\n",
       "      <td>2021-01-22</td>\n",
       "      <td>2021-12-24</td>\n",
       "      <td>23.013104</td>\n",
       "      <td>UKR_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>296561</th>\n",
       "      <td>3908313</td>\n",
       "      <td>2021-01-22</td>\n",
       "      <td>2021-12-25</td>\n",
       "      <td>36.344492</td>\n",
       "      <td>UKR_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>298659</th>\n",
       "      <td>3908063</td>\n",
       "      <td>2021-01-22</td>\n",
       "      <td>2021-12-26</td>\n",
       "      <td>23.013104</td>\n",
       "      <td>UKR_MS1_purchase</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>303332</th>\n",
       "      <td>3861811</td>\n",
       "      <td>2021-01-07</td>\n",
       "      <td>2021-12-31</td>\n",
       "      <td>1.754931</td>\n",
       "      <td>RUS_MS1_purchase</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>325 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        account_id created_date payment_date       cost          campaign\n",
       "330        3861811   2021-01-07   2021-01-01   1.858740  RUS_MS1_purchase\n",
       "339        3861811   2021-01-07   2021-01-01   1.858740  RUS_MS1_purchase\n",
       "1922       3845613   2021-01-02   2021-01-02   0.726463  RUS_MS1_purchase\n",
       "2920       3843913   2021-01-01   2021-01-02   0.726463  RUS_MS1_purchase\n",
       "4335       3908313   2021-01-22   2021-01-03   4.237699  UKR_MS1_purchase\n",
       "...            ...          ...          ...        ...               ...\n",
       "290371     3864062   2021-01-07   2021-12-17   0.619811  RUS_MS1_purchase\n",
       "295786     3908063   2021-01-22   2021-12-24  23.013104  UKR_MS1_purchase\n",
       "296561     3908313   2021-01-22   2021-12-25  36.344492  UKR_MS1_purchase\n",
       "298659     3908063   2021-01-22   2021-12-26  23.013104  UKR_MS1_purchase\n",
       "303332     3861811   2021-01-07   2021-12-31   1.754931  RUS_MS1_purchase\n",
       "\n",
       "[325 rows x 5 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = registrations.dropna()\n",
    "df = payments.join(df.set_index(\"account_id\"), on=\"account_id\").dropna()\n",
    "df = df.join(persents.set_index(\"Payment_types\"), on=\"Payment_types\")\n",
    "df['cost'] = df['real_cost'] * (100 - df['Share, %']) / 100\n",
    "df['payment_date'] = pd.to_datetime(df['payment_date'], format=\"%Y-%m-%d %H:%M:%S\").dt.date\n",
    "df['created_date'] = pd.to_datetime(df['created_date'], format=\"%Y-%m-%d\").dt.date\n",
    "df = df[df['campaign'].str.contains(\"purchase\")]\n",
    "table = df[[\"account_id\", \"created_date\", \"payment_date\", \"cost\", \"campaign\"]]\n",
    "table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "ae68e44e",
   "metadata": {},
   "outputs": [],
   "source": [
    "campaigns = list(table['campaign'].unique())\n",
    "accounts = list(table['account_id'].unique())\n",
    "payments = list(table['payment_date'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "11ba3dae",
   "metadata": {},
   "outputs": [],
   "source": [
    "result_table = table.groupby(by=[\"campaign\",\"account_id\",\"created_date\",\"payment_date\"], as_index=False).sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "139b348c",
   "metadata": {},
   "outputs": [],
   "source": [
    "dates = [7,14,30]\n",
    "dates = [i for i in range(1, 32)]\n",
    "ARPUs = np.zeros((len(campaigns), len(dates)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "8d3253d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "for i, c in enumerate(campaigns):\n",
    "    cond1 = result_table['campaign'] == c\n",
    "    num_accounts = len(result_table[cond1])\n",
    "    \n",
    "    for acc in accounts:\n",
    "        cond2 = result_table['account_id'] == acc\n",
    "        cond = np.logical_and(cond1, cond2)\n",
    "        \n",
    "        tmp_table = result_table[cond]\n",
    "        \n",
    "        \n",
    "        s = np.zeros(len(dates))\n",
    "        for j,d in enumerate(dates):\n",
    "            cond = tmp_table['payment_date'] <= tmp_table['created_date'] + timedelta(days=d)\n",
    "            s[j] += tmp_table[cond]['cost'].sum()\n",
    "#         print(s)\n",
    "        ARPUs[i] += s \n",
    "    ARPUs[i] /= num_accounts\n",
    "# print(ARPUs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "f2c30d3d",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_ARPU = pd.DataFrame(ARPUs.T, columns=campaigns)\n",
    "# df_ARPU\n",
    "df_ARPU['days'] = list(map(lambda x: f\"{x} days\" if x > 1 else f\"{x} day\", dates))\n",
    "df_ARPU_melted = df_ARPU.melt('days', var_name='Campaign type', value_name='ARPU')\n",
    "df_ARPU.iloc[[6, 13, 29]].to_csv('tables/arpu.csv')\n",
    "df_ARPU.iloc[[6, 13, 29]].to_markdown('tables/arpu.md')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "905d7a36",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 111192 characters]",
      "text/plain": [
       "<Figure size 1280x960 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(1, 1, figsize=(8,6), dpi=160)\n",
    "ax.grid()\n",
    "# ax.set_xlim(1, 70)\n",
    "ax.set(xlabel='Days', ylabel='Average revenue per user')\n",
    "ax.set(title='ARPU, \\nсгруппированный по рекламным компаниям')\n",
    "\n",
    "# for i, c in enumerate(campaigns):\n",
    "plt.xticks(rotation=70)\n",
    "sns.lineplot(ax=ax, data=df_ARPU_melted, x='days', y='ARPU', hue='Campaign type');\n",
    "plt.savefig(\"pics/arpu.png\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b14810e",
   "metadata": {},
   "source": [
    "## Фактический CPI (cost per install)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7cc81742",
   "metadata": {},
   "source": [
    "$$\\Large\n",
    "CPI = \n",
    "\\frac{\\text{Количество установок}}{\\text{Бюджет}}\n",
    "$$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "47fe30e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "d = {\n",
    "    (\"Media_source_1\",       \"install\",            \"Brazil\"): \"BRA_MS1_install\",\n",
    "    (\"Media_source_1\",      \"purchase\",            \"Brazil\"): \"BRA_MS1_purchase\",\n",
    "    (\"Media_source_1\",       \"install\",            \"Russia\"): \"RUS_MS1_install\",\n",
    "    (\"Media_source_1\",      \"purchase\",            \"Russia\"): \"RUS_MS1_purchase\",\n",
    "    (\"Media_source_1\",       \"install\",            \"Ukrane\"): \"UKR_MS1_install\",\n",
    "    (\"Media_source_1\",      \"purchase\",            \"Ukrane\"): \"UKR_MS1_purchase\",\n",
    "    (\"Media_source_2\",       \"install\",            \"Brazil\"): \"BRA_MS2_install\",\n",
    "    (\"Media_source_2\",       \"install\",  \"English_speaking\"): \"ENG_MS2_install\",\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "3bfa10eb",
   "metadata": {},
   "outputs": [],
   "source": [
    "CPI = list()\n",
    "tmp_budget = budget.groupby(by=[\"media_source\", \"Campaign_type\",'Target']).sum()\n",
    "for (MS, Type, Target), c in d.items():\n",
    "    cond = registrations['campaign'] == c\n",
    "    num_installs = cond.sum()\n",
    "    \n",
    "    b = tmp_budget.loc[MS, Type, Target]['Spend, USD']\n",
    "    \n",
    "    CPI.append((MS, Type, Target, b / num_installs))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "b7a20a84",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.DataFrame(CPI, columns=[\"Media Source\", 'Campaign type', 'Target', \"CPI\"])\n",
    "df = df.set_index([\"Media Source\", 'Campaign type', 'Target'])\n",
    "df = df.sort_values(by='CPI')\n",
    "df.to_csv('tables/cpi.sorted.csv')\n",
    "df.to_markdown('tables/cpi.sorted.md')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "db865d1c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 99444 characters]",
      "text/plain": [
       "<Figure size 1280x960 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(1, 1, figsize=(8,6), dpi=160)\n",
    "ax.grid()\n",
    "ax.set(title='Фактический CPI')\n",
    "sns.barplot(ax=ax, data=df, x='CPI', hue=df.index)\n",
    "plt.legend(title=\"\");\n",
    "plt.savefig(\"pics/cpi.sorted.png\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "57f5cc90",
   "metadata": {},
   "outputs": [
    {
     "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></th>\n",
       "      <th></th>\n",
       "      <th>CPI</th>\n",
       "      <th>Installs</th>\n",
       "      <th>Spend, USD</th>\n",
       "      <th>Plan CPI</th>\n",
       "      <th>Deviation</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Media Source</th>\n",
       "      <th>Campaign type</th>\n",
       "      <th>Target</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">Media_source_1</th>\n",
       "      <th rowspan=\"3\" valign=\"top\">install</th>\n",
       "      <th>Brazil</th>\n",
       "      <td>0.008648</td>\n",
       "      <td>10460</td>\n",
       "      <td>99.12</td>\n",
       "      <td>0.009476</td>\n",
       "      <td>0.000828</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ukrane</th>\n",
       "      <td>0.014644</td>\n",
       "      <td>6424</td>\n",
       "      <td>99.43</td>\n",
       "      <td>0.015478</td>\n",
       "      <td>0.000834</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russia</th>\n",
       "      <td>0.019472</td>\n",
       "      <td>11894</td>\n",
       "      <td>246.30</td>\n",
       "      <td>0.020708</td>\n",
       "      <td>0.001236</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Media_source_2</th>\n",
       "      <th>install</th>\n",
       "      <th>English_speaking</th>\n",
       "      <td>0.245577</td>\n",
       "      <td>371</td>\n",
       "      <td>99.95</td>\n",
       "      <td>0.269407</td>\n",
       "      <td>0.023830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">Media_source_1</th>\n",
       "      <th rowspan=\"2\" valign=\"top\">purchase</th>\n",
       "      <th>Brazil</th>\n",
       "      <td>0.350018</td>\n",
       "      <td>1081</td>\n",
       "      <td>398.67</td>\n",
       "      <td>0.368797</td>\n",
       "      <td>0.018780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Russia</th>\n",
       "      <td>0.390114</td>\n",
       "      <td>1441</td>\n",
       "      <td>616.77</td>\n",
       "      <td>0.428015</td>\n",
       "      <td>0.037901</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Media_source_2</th>\n",
       "      <th>install</th>\n",
       "      <th>Brazil</th>\n",
       "      <td>0.533882</td>\n",
       "      <td>152</td>\n",
       "      <td>81.15</td>\n",
       "      <td>0.533882</td>\n",
       "      <td>0.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Media_source_1</th>\n",
       "      <th>purchase</th>\n",
       "      <th>Ukrane</th>\n",
       "      <td>1.338962</td>\n",
       "      <td>242</td>\n",
       "      <td>348.13</td>\n",
       "      <td>1.438554</td>\n",
       "      <td>0.099592</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                    CPI  Installs  Spend, USD  \\\n",
       "Media Source   Campaign type Target                                             \n",
       "Media_source_1 install       Brazil            0.008648     10460       99.12   \n",
       "                             Ukrane            0.014644      6424       99.43   \n",
       "                             Russia            0.019472     11894      246.30   \n",
       "Media_source_2 install       English_speaking  0.245577       371       99.95   \n",
       "Media_source_1 purchase      Brazil            0.350018      1081      398.67   \n",
       "                             Russia            0.390114      1441      616.77   \n",
       "Media_source_2 install       Brazil            0.533882       152       81.15   \n",
       "Media_source_1 purchase      Ukrane            1.338962       242      348.13   \n",
       "\n",
       "                                               Plan CPI  Deviation  \n",
       "Media Source   Campaign type Target                                 \n",
       "Media_source_1 install       Brazil            0.009476   0.000828  \n",
       "                             Ukrane            0.015478   0.000834  \n",
       "                             Russia            0.020708   0.001236  \n",
       "Media_source_2 install       English_speaking  0.269407   0.023830  \n",
       "Media_source_1 purchase      Brazil            0.368797   0.018780  \n",
       "                             Russia            0.428015   0.037901  \n",
       "Media_source_2 install       Brazil            0.533882   0.000000  \n",
       "Media_source_1 purchase      Ukrane            1.438554   0.099592  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "compare_budget = budget.dropna().rename(\n",
    "    {\n",
    "        'media_source':\"Media Source\", \n",
    "        'Campaign_type':'Campaign type',\n",
    "    }, axis=1).set_index([\"Media Source\", 'Campaign type', 'Target'])[['Installs','Spend, USD']]\n",
    "compare_df = df.join(compare_budget)\n",
    "compare_df['Plan CPI'] = compare_df['Spend, USD'] / compare_df['Installs']\n",
    "compare_df['Deviation'] = abs(compare_df['CPI'] - compare_df['Plan CPI'])\n",
    "compare_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "889fe58f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "image/png": "[encoded data, 112744 characters]",
      "text/plain": [
       "<Figure size 1280x960 with 1 Axes>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "fig, ax = plt.subplots(1, 1, figsize=(8,6), dpi=160)\n",
    "ax.grid()\n",
    "ax.set(title='Отклонения фактического CPI от бюджета')\n",
    "sns.barplot(ax=ax, data=compare_df, y='Deviation', hue=df.index)\n",
    "plt.legend(title=\"\");\n",
    "plt.savefig(\"pics/cpi.deviation.png\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.13.0"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "ToC",
   "title_sidebar": "Contents",
   "toc_cell": true,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "186.667px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
