Product_metrics.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import requests\n",
    "from urllib.parse import urlencode\n",
    "import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# импортируем данные по ссылкам из Яндекс Диска\n",
    "\n",
    "base_url = 'https://cloud-api.yandex.net/v1/disk/public/resources/download?'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "public_key = 'https://disk.yandex.ru/d/Ta-weYfGM5CHjg'\n",
    "final_url = base_url + urlencode(dict(public_key=public_key))\n",
    "response = requests.get(final_url)\n",
    "download_url = response.json()['href']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Выгружены данные из AppMetrica за период с 1 января по 31 марта 2020, только по пользователям, зарегистрированным позднее 1 января 2020.\n",
    "\n",
    "\n",
    "Описание данных \n",
    "\n",
    "- date – дата совершения события\n",
    "- event - событие\n",
    "\n",
    "app_install – установка приложения\n",
    "app_start – открыть приложения\n",
    "registration – зарегистрироваться \n",
    "search – перейти на страницу поиска товаров (каталог)\n",
    "open_item – открыть товар\n",
    "choose_item – отправить товар в корзину\n",
    "tap_basket – перейти в корзину\n",
    "purchase – подтверждение покупки\n",
    "\n",
    "- gender – пол пользователя\n",
    "- os_name – платформа пользователя\n",
    "- city – город пользователя\n",
    "- device_id – идентификатор устройства пользователя\n",
    "- urm_source – канал, с которого пришел пользователь\n",
    "\n",
    "yandex-direct – Яндекс директ\n",
    "google_ads – реклама в Google\n",
    "vk_ads – реклама в ВК\n",
    "instagram_ads – реклама в instagram\n",
    "facebook_ads – реклама в facebook\n",
    "referral – акция «приведи друга»\n",
    "Если стоит ‘-’, то канал не определен или это скачивание приложения напрямую или посещение не с рекламы  \n",
    "\n",
    "- purchase_sum – стоимость покупки (при совершении события ‘purchase’)\n",
    "\n",
    "Обратите внимание на следующее:\n",
    "- В выгрузке только уникальные действия пользователей за каждый день \n",
    "- Можно миновать стадию установки приложения, если оно было установлено ранее\n",
    "- Можно миновать стадию регистрации, если пользователь был уже залогинен на момент сессии. Однако незарегистрированные пользователи не могут оформить покупку."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.read_csv(download_url)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2747968, 8)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "data['date'] = pd.to_datetime(data['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date            datetime64[ns]\n",
       "event                   object\n",
       "purchase_sum           float64\n",
       "os_name                 object\n",
       "device_id                int64\n",
       "gender                  object\n",
       "city                    object\n",
       "utm_source              object\n",
       "dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.dtypes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>date</th>\n",
       "      <th>event</th>\n",
       "      <th>purchase_sum</th>\n",
       "      <th>os_name</th>\n",
       "      <th>device_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>city</th>\n",
       "      <th>utm_source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>669460</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>833621</td>\n",
       "      <td>male</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>vk_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>1579237</td>\n",
       "      <td>male</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>referal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>1737182</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>facebook_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>4029024</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>facebook_ads</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date      event  purchase_sum  os_name  device_id  gender  \\\n",
       "0 2020-01-01  app_start           NaN  android     669460  female   \n",
       "1 2020-01-01  app_start           NaN      ios     833621    male   \n",
       "2 2020-01-01  app_start           NaN  android    1579237    male   \n",
       "3 2020-01-01  app_start           NaN  android    1737182  female   \n",
       "4 2020-01-01  app_start           NaN      ios    4029024  female   \n",
       "\n",
       "               city    utm_source  \n",
       "0            Moscow             -  \n",
       "1            Moscow        vk_ads  \n",
       "2  Saint-Petersburg       referal  \n",
       "3            Moscow  facebook_ads  \n",
       "4            Moscow  facebook_ads  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "75032"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# MAU февраля:\n",
    "\n",
    "data \\\n",
    "    .query(\"'2020-02-01' <= date <= '2020-02-29'\") \\\n",
    "    .device_id \\\n",
    "    .nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "80297"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Количество установок в январе:\n",
    "\n",
    "data \\\n",
    "    .query(\"'2020-01-01' <= date <= '2020-01-31' and event == 'app_install'\") \\\n",
    "    .shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Присвойте пользователям когорты по дню установки приложения.\n",
    "# Посчитайте для них  конверсию из установки в покупку в течение 7 дней.\n",
    "# Для какой когорты конверсия была наибольшей? Ответ впишите в формате: дд.мм.гггг\n",
    "\n",
    "# Примечание: считаем пользователя сконвертировавшимся,\n",
    "# если с момента установки до совершения первой покупки прошло не более 7 дней.\n",
    "\n",
    "install_events = \\\n",
    "    data \\\n",
    "        .query(\"event == 'app_install'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>date</th>\n",
       "      <th>event</th>\n",
       "      <th>purchase_sum</th>\n",
       "      <th>os_name</th>\n",
       "      <th>device_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>city</th>\n",
       "      <th>utm_source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2515061</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_install</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>4921563</td>\n",
       "      <td>male</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>vk_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2515062</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_install</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>1311583</td>\n",
       "      <td>male</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2515063</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_install</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>23006325</td>\n",
       "      <td>male</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2515064</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_install</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>5645652</td>\n",
       "      <td>male</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>-</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2515065</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_install</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>8798567</td>\n",
       "      <td>male</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>referal</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              date        event  purchase_sum  os_name  device_id gender  \\\n",
       "2515061 2020-01-01  app_install           NaN      ios    4921563   male   \n",
       "2515062 2020-01-01  app_install           NaN      ios    1311583   male   \n",
       "2515063 2020-01-01  app_install           NaN  android   23006325   male   \n",
       "2515064 2020-01-01  app_install           NaN  android    5645652   male   \n",
       "2515065 2020-01-01  app_install           NaN      ios    8798567   male   \n",
       "\n",
       "                     city utm_source  \n",
       "2515061            Moscow     vk_ads  \n",
       "2515062            Moscow          -  \n",
       "2515063  Saint-Petersburg          -  \n",
       "2515064            Moscow          -  \n",
       "2515065  Saint-Petersburg    referal  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "install_events.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# для каждого клиента находим дату его первой покупки\n",
    "\n",
    "first_buy = \\\n",
    "data \\\n",
    "    .query(\"event == 'purchase' and device_id in @install_events.device_id\") \\\n",
    "    .groupby('device_id') \\\n",
    "    .agg({'date': 'min'}) \\\n",
    "    .reset_index() \\\n",
    "    .rename(columns={'date': 'first_buy'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "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>device_id</th>\n",
       "      <th>first_buy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4046</td>\n",
       "      <td>2020-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4120</td>\n",
       "      <td>2020-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4125</td>\n",
       "      <td>2020-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4202</td>\n",
       "      <td>2020-02-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4234</td>\n",
       "      <td>2020-02-19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57453</th>\n",
       "      <td>35315632</td>\n",
       "      <td>2020-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57454</th>\n",
       "      <td>35321120</td>\n",
       "      <td>2020-03-25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57455</th>\n",
       "      <td>35342310</td>\n",
       "      <td>2020-03-27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57456</th>\n",
       "      <td>35367797</td>\n",
       "      <td>2020-03-29</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57457</th>\n",
       "      <td>35370030</td>\n",
       "      <td>2020-03-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>57458 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       device_id  first_buy\n",
       "0           4046 2020-01-04\n",
       "1           4120 2020-01-07\n",
       "2           4125 2020-01-07\n",
       "3           4202 2020-02-27\n",
       "4           4234 2020-02-19\n",
       "...          ...        ...\n",
       "57453   35315632 2020-03-31\n",
       "57454   35321120 2020-03-25\n",
       "57455   35342310 2020-03-27\n",
       "57456   35367797 2020-03-29\n",
       "57457   35370030 2020-03-31\n",
       "\n",
       "[57458 rows x 2 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_buy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = install_events[['date', 'device_id']].merge(first_buy, how='left', on='device_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = users \\\n",
    "    .rename(columns={'date': 'install_date'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "users = users[['device_id', 'install_date', 'first_buy']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>device_id</th>\n",
       "      <th>install_date</th>\n",
       "      <th>first_buy</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4921563</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2020-01-10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1311583</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23006325</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5645652</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8798567</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   device_id install_date  first_buy\n",
       "0    4921563   2020-01-01 2020-01-10\n",
       "1    1311583   2020-01-01        NaT\n",
       "2   23006325   2020-01-01 2020-01-01\n",
       "3    5645652   2020-01-01        NaT\n",
       "4    8798567   2020-01-01        NaT"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "users['days'] = users['first_buy'] - users['install_date']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>device_id</th>\n",
       "      <th>install_date</th>\n",
       "      <th>first_buy</th>\n",
       "      <th>days</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4921563</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2020-01-10</td>\n",
       "      <td>9 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1311583</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>23006325</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>0 days</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5645652</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>8798567</td>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>NaT</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   device_id install_date  first_buy   days\n",
       "0    4921563   2020-01-01 2020-01-10 9 days\n",
       "1    1311583   2020-01-01        NaT    NaT\n",
       "2   23006325   2020-01-01 2020-01-01 0 days\n",
       "3    5645652   2020-01-01        NaT    NaT\n",
       "4    8798567   2020-01-01        NaT    NaT"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "users.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_1 = \\\n",
    "users \\\n",
    "    .query(\"days <= datetime.timedelta(days=7)\") \\\n",
    "    .groupby('install_date') \\\n",
    "    .agg({'device_id': 'count'}) \\\n",
    "    .rename(columns={'device_id': 'less_than_7_days'}) \\\n",
    "    .reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_2 = \\\n",
    "users \\\n",
    "    .groupby('install_date') \\\n",
    "    .agg({'device_id': 'count'}) \\\n",
    "    .rename(columns={'device_id': 'total_amount'}) \\\n",
    "    .reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = df_1.merge(df_2, on='install_date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['CR'] = df['less_than_7_days'] / df['total_amount']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['CR'] = df['CR'] * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>install_date</th>\n",
       "      <th>less_than_7_days</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>CR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1408</td>\n",
       "      <td>3579</td>\n",
       "      <td>39.340598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>1186</td>\n",
       "      <td>3144</td>\n",
       "      <td>37.722646</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>834</td>\n",
       "      <td>2402</td>\n",
       "      <td>34.721066</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>639</td>\n",
       "      <td>1831</td>\n",
       "      <td>34.898962</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-05</td>\n",
       "      <td>587</td>\n",
       "      <td>1671</td>\n",
       "      <td>35.128665</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>86</th>\n",
       "      <td>2020-03-27</td>\n",
       "      <td>277</td>\n",
       "      <td>1199</td>\n",
       "      <td>23.102585</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>87</th>\n",
       "      <td>2020-03-28</td>\n",
       "      <td>209</td>\n",
       "      <td>1091</td>\n",
       "      <td>19.156737</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>223</td>\n",
       "      <td>1117</td>\n",
       "      <td>19.964190</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>89</th>\n",
       "      <td>2020-03-30</td>\n",
       "      <td>204</td>\n",
       "      <td>994</td>\n",
       "      <td>20.523139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>90</th>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>241</td>\n",
       "      <td>968</td>\n",
       "      <td>24.896694</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>91 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   install_date  less_than_7_days  total_amount         CR\n",
       "0    2020-01-01              1408          3579  39.340598\n",
       "1    2020-01-02              1186          3144  37.722646\n",
       "2    2020-01-03               834          2402  34.721066\n",
       "3    2020-01-04               639          1831  34.898962\n",
       "4    2020-01-05               587          1671  35.128665\n",
       "..          ...               ...           ...        ...\n",
       "86   2020-03-27               277          1199  23.102585\n",
       "87   2020-03-28               209          1091  19.156737\n",
       "88   2020-03-29               223          1117  19.964190\n",
       "89   2020-03-30               204           994  20.523139\n",
       "90   2020-03-31               241           968  24.896694\n",
       "\n",
       "[91 rows x 4 columns]"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>install_date</th>\n",
       "      <th>less_than_7_days</th>\n",
       "      <th>total_amount</th>\n",
       "      <th>CR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>1408</td>\n",
       "      <td>3579</td>\n",
       "      <td>39.340598</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  install_date  less_than_7_days  total_amount         CR\n",
       "0   2020-01-01              1408          3579  39.340598"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df.CR == df.CR.max()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "-                32460\n",
       "yandex-direct    29368\n",
       "google_ads       26286\n",
       "vk_ads           23189\n",
       "instagram_ads    20096\n",
       "facebook_ads     13916\n",
       "referal           9282\n",
       "Name: utm_source, dtype: int64"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# С какого платного маркетингового канала пришло больше всего новых пользователей?\n",
    "\n",
    "data \\\n",
    "    .query(\"event == 'app_install'\") \\\n",
    "    .utm_source \\\n",
    "    .value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Проанализируйте на каком этапе воронки отваливается бОльшая часть клиентов.\n",
    "# Посмотрите отдельно сценарии для зарегистрированных и для незарегистрированных пользователей. \n",
    "# На каком шаге отваливается больше всего зарегистрированных пользователей?\n",
    "\n",
    "# В ответ впишите один из шагов, конверсия из которого (в следующий шаг) самая низкая, в таком формате:\n",
    "# «Поиск», «Добавление товара», «Переход в корзину», «Регистрация».\n",
    "\n",
    "# Подсказка:\n",
    "# - Для того, чтобы выделить группу тех, кому нужно и не нужно регистрироваться, добавьте колонку с датой регистрации.\n",
    "# Если дата регистрации < даты совершения события, то пользователь уже зарегистрирован."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "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>device_id</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2669658</th>\n",
       "      <td>294193</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2669659</th>\n",
       "      <td>22917617</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2669660</th>\n",
       "      <td>15248490</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2669661</th>\n",
       "      <td>252062</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2669662</th>\n",
       "      <td>2251583</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747963</th>\n",
       "      <td>2984778</td>\n",
       "      <td>2020-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747964</th>\n",
       "      <td>27301864</td>\n",
       "      <td>2020-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747965</th>\n",
       "      <td>1294285</td>\n",
       "      <td>2020-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747966</th>\n",
       "      <td>3010574</td>\n",
       "      <td>2020-03-31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747967</th>\n",
       "      <td>11153353</td>\n",
       "      <td>2020-03-31</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>78310 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         device_id       date\n",
       "2669658     294193 2020-01-01\n",
       "2669659   22917617 2020-01-01\n",
       "2669660   15248490 2020-01-01\n",
       "2669661     252062 2020-01-01\n",
       "2669662    2251583 2020-01-01\n",
       "...            ...        ...\n",
       "2747963    2984778 2020-03-31\n",
       "2747964   27301864 2020-03-31\n",
       "2747965    1294285 2020-03-31\n",
       "2747966    3010574 2020-03-31\n",
       "2747967   11153353 2020-03-31\n",
       "\n",
       "[78310 rows x 2 columns]"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.query(\"event == 'register'\")[['device_id', 'date']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "\"['date'] not in index\"",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-55-d9ca0d4f1ffa>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[0mdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mdata\u001b[0m\u001b[0;31m \u001b[0m\u001b[0;31m\\\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m     .merge(data.query(\"event == 'register'\")[['device_id', 'date']],\n\u001b[0m\u001b[1;32m      3\u001b[0m               \u001b[0mhow\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'left'\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      4\u001b[0m               on='device_id') \\\n\u001b[1;32m      5\u001b[0m     \u001b[0;34m.\u001b[0m\u001b[0mrename\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcolumns\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m{\u001b[0m\u001b[0;34m'date_y'\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m'registration_date'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'date_x'\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0;34m'date'\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/tljh/user/lib/python3.7/site-packages/pandas/core/frame.py\u001b[0m in \u001b[0;36m__getitem__\u001b[0;34m(self, key)\u001b[0m\n\u001b[1;32m   2906\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mis_iterator\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2907\u001b[0m                 \u001b[0mkey\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mlist\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2908\u001b[0;31m             \u001b[0mindexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mloc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_listlike_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mraise_missing\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2909\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2910\u001b[0m         \u001b[0;31m# take() does not accept boolean indexers\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/tljh/user/lib/python3.7/site-packages/pandas/core/indexing.py\u001b[0m in \u001b[0;36m_get_listlike_indexer\u001b[0;34m(self, key, axis, raise_missing)\u001b[0m\n\u001b[1;32m   1252\u001b[0m             \u001b[0mkeyarr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindexer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnew_indexer\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0max\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_reindex_non_unique\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkeyarr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1253\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1254\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_validate_read_indexer\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkeyarr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindexer\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0maxis\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mraise_missing\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mraise_missing\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1255\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mkeyarr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindexer\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1256\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/opt/tljh/user/lib/python3.7/site-packages/pandas/core/indexing.py\u001b[0m in \u001b[0;36m_validate_read_indexer\u001b[0;34m(self, key, indexer, axis, raise_missing)\u001b[0m\n\u001b[1;32m   1302\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mraise_missing\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1303\u001b[0m                 \u001b[0mnot_found\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mlist\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mset\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mkey\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m-\u001b[0m \u001b[0mset\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0max\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1304\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0mKeyError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34mf\"{not_found} not in index\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1305\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1306\u001b[0m             \u001b[0;31m# we skip the warning on Categorical\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mKeyError\u001b[0m: \"['date'] not in index\""
     ]
    }
   ],
   "source": [
    "data = data \\\n",
    "    .merge(data.query(\"event == 'register'\")[['device_id', 'date']],\n",
    "              how='left',\n",
    "              on='device_id') \\\n",
    "    .rename(columns={'date_y': 'registration_date'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "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>date_x</th>\n",
       "      <th>event</th>\n",
       "      <th>purchase_sum</th>\n",
       "      <th>os_name</th>\n",
       "      <th>device_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>city</th>\n",
       "      <th>utm_source</th>\n",
       "      <th>registration_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>669460</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>-</td>\n",
       "      <td>2020-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>833621</td>\n",
       "      <td>male</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>vk_ads</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>1579237</td>\n",
       "      <td>male</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>referal</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>1737182</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>4029024</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      date_x      event  purchase_sum  os_name  device_id  gender  \\\n",
       "0 2020-01-01  app_start           NaN  android     669460  female   \n",
       "1 2020-01-01  app_start           NaN      ios     833621    male   \n",
       "2 2020-01-01  app_start           NaN  android    1579237    male   \n",
       "3 2020-01-01  app_start           NaN  android    1737182  female   \n",
       "4 2020-01-01  app_start           NaN      ios    4029024  female   \n",
       "\n",
       "               city    utm_source registration_date  \n",
       "0            Moscow             -        2020-01-07  \n",
       "1            Moscow        vk_ads        2020-01-01  \n",
       "2  Saint-Petersburg       referal        2020-01-01  \n",
       "3            Moscow  facebook_ads        2020-01-01  \n",
       "4            Moscow  facebook_ads               NaT  "
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# смотрим воронку для зарегистрированных пользователей"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "417101"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.query(\"event == 'search' and date_x > registration_date\").shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "314473"
      ]
     },
     "execution_count": 62,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.query(\"event == 'choose_item' and date_x > registration_date\").shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "75.39492832671225"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# CR из поиска в добавление товара в корзину\n",
    "314473 / 417101 * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "217256"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.query(\"event == 'tap_basket' and date_x > registration_date\").shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "69.08574027022988"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# CR из добавления товара в корзину в переход в корзину\n",
    "217256 / 314473 * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "73630"
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.query(\"event == 'purchase' and date_x > registration_date\").shape[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 70,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "33.890893692234044"
      ]
     },
     "execution_count": 70,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# CR из перехода в корзину в оплату\n",
    "73630 / 217256 * 100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Пользователи, пришедшие с каких каналов, показали самую низкую конверсию в первую покупку?\n",
    "\n",
    "# В ответ впишите один из каналов в таком формате: «Яндекс», «Гугл», «ВК», «Фейсбук», «Инстаграм», «Реферальная программа».\n",
    "\n",
    "# Подсказка: \n",
    "# - CR = кол-во юзеров, впервые совершивших покупку/кол-во юзеров, впервые открывших приложение \n",
    "\n",
    "inorganic_users = data.query(\"utm_source != '-'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2137510, 9)"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inorganic_users.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "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>date_x</th>\n",
       "      <th>event</th>\n",
       "      <th>purchase_sum</th>\n",
       "      <th>os_name</th>\n",
       "      <th>device_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>city</th>\n",
       "      <th>utm_source</th>\n",
       "      <th>registration_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>833621</td>\n",
       "      <td>male</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>vk_ads</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>1579237</td>\n",
       "      <td>male</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>referal</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>1737182</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>4029024</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>8383386</td>\n",
       "      <td>female</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>vk_ads</td>\n",
       "      <td>2020-01-23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      date_x      event  purchase_sum  os_name  device_id  gender  \\\n",
       "1 2020-01-01  app_start           NaN      ios     833621    male   \n",
       "2 2020-01-01  app_start           NaN  android    1579237    male   \n",
       "3 2020-01-01  app_start           NaN  android    1737182  female   \n",
       "4 2020-01-01  app_start           NaN      ios    4029024  female   \n",
       "6 2020-01-01  app_start           NaN  android    8383386  female   \n",
       "\n",
       "               city    utm_source registration_date  \n",
       "1            Moscow        vk_ads        2020-01-01  \n",
       "2  Saint-Petersburg       referal        2020-01-01  \n",
       "3            Moscow  facebook_ads        2020-01-01  \n",
       "4            Moscow  facebook_ads               NaT  \n",
       "6  Saint-Petersburg        vk_ads        2020-01-23  "
      ]
     },
     "execution_count": 73,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inorganic_users.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_start = \\\n",
    "    inorganic_users \\\n",
    "        .query(\"event == 'app_start'\") \\\n",
    "        .groupby('device_id') \\\n",
    "        .agg({'date_x': 'min'}) \\\n",
    "        .reset_index() \\\n",
    "        .rename(columns={'date_x': 'first_start_date'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "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>device_id</th>\n",
       "      <th>first_start_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4013</td>\n",
       "      <td>2020-01-15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4014</td>\n",
       "      <td>2020-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4016</td>\n",
       "      <td>2020-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4046</td>\n",
       "      <td>2020-01-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4060</td>\n",
       "      <td>2020-01-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   device_id first_start_date\n",
       "0       4013       2020-01-15\n",
       "1       4014       2020-01-02\n",
       "2       4016       2020-01-04\n",
       "3       4046       2020-01-08\n",
       "4       4060       2020-01-03"
      ]
     },
     "execution_count": 86,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_start.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(158283, 2)"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_start.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {},
   "outputs": [],
   "source": [
    "inorg_users = inorganic_users.query(\"event == 'purchase'\")[['date_x', 'event', 'device_id', 'utm_source']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {},
   "outputs": [],
   "source": [
    "inorg_users = inorg_users \\\n",
    "    .groupby('device_id') \\\n",
    "    .agg({'date_x': 'min'}) \\\n",
    "    .reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {},
   "outputs": [],
   "source": [
    "start_and_buy_users = first_start.merge(inorg_users, how='left', on='device_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(158283, 3)"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_and_buy_users.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "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>device_id</th>\n",
       "      <th>first_start_date</th>\n",
       "      <th>date_x</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4013</td>\n",
       "      <td>2020-01-15</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4014</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2020-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4016</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4046</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>2020-01-11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4060</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>2020-03-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   device_id first_start_date     date_x\n",
       "0       4013       2020-01-15        NaT\n",
       "1       4014       2020-01-02 2020-02-01\n",
       "2       4016       2020-01-04        NaT\n",
       "3       4046       2020-01-08 2020-01-11\n",
       "4       4060       2020-01-03 2020-03-07"
      ]
     },
     "execution_count": 96,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "start_and_buy_users.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "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>device_id</th>\n",
       "      <th>utm_source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>833621</td>\n",
       "      <td>vk_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1579237</td>\n",
       "      <td>referal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1737182</td>\n",
       "      <td>facebook_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4029024</td>\n",
       "      <td>facebook_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>8383386</td>\n",
       "      <td>vk_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747961</th>\n",
       "      <td>4294936</td>\n",
       "      <td>yandex-direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747962</th>\n",
       "      <td>9613546</td>\n",
       "      <td>google_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747963</th>\n",
       "      <td>2984778</td>\n",
       "      <td>facebook_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747965</th>\n",
       "      <td>1294285</td>\n",
       "      <td>google_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2747966</th>\n",
       "      <td>3010574</td>\n",
       "      <td>google_ads</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2137510 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         device_id     utm_source\n",
       "1           833621         vk_ads\n",
       "2          1579237        referal\n",
       "3          1737182   facebook_ads\n",
       "4          4029024   facebook_ads\n",
       "6          8383386         vk_ads\n",
       "...            ...            ...\n",
       "2747961    4294936  yandex-direct\n",
       "2747962    9613546     google_ads\n",
       "2747963    2984778   facebook_ads\n",
       "2747965    1294285     google_ads\n",
       "2747966    3010574     google_ads\n",
       "\n",
       "[2137510 rows x 2 columns]"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "inorganic_users[['device_id', 'utm_source']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df = start_and_buy_users.merge(inorganic_users[['device_id', 'utm_source']].drop_duplicates(subset='device_id'),\n",
    "                          how='left',\n",
    "                          on='device_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "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>device_id</th>\n",
       "      <th>first_start_date</th>\n",
       "      <th>date_x</th>\n",
       "      <th>utm_source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4013</td>\n",
       "      <td>2020-01-15</td>\n",
       "      <td>NaT</td>\n",
       "      <td>vk_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4014</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>referal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4016</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>NaT</td>\n",
       "      <td>yandex-direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4046</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>2020-01-11</td>\n",
       "      <td>facebook_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4060</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>2020-03-07</td>\n",
       "      <td>yandex-direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158278</th>\n",
       "      <td>35379281</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>NaT</td>\n",
       "      <td>vk_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158279</th>\n",
       "      <td>35380796</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>referal</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158280</th>\n",
       "      <td>35381595</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>facebook_ads</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158281</th>\n",
       "      <td>35388218</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>yandex-direct</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158282</th>\n",
       "      <td>35397767</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>google_ads</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>158283 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        device_id first_start_date     date_x     utm_source\n",
       "0            4013       2020-01-15        NaT         vk_ads\n",
       "1            4014       2020-01-02 2020-02-01        referal\n",
       "2            4016       2020-01-04        NaT  yandex-direct\n",
       "3            4046       2020-01-08 2020-01-11   facebook_ads\n",
       "4            4060       2020-01-03 2020-03-07  yandex-direct\n",
       "...           ...              ...        ...            ...\n",
       "158278   35379281       2020-03-29        NaT         vk_ads\n",
       "158279   35380796       2020-03-31        NaT        referal\n",
       "158280   35381595       2020-03-31        NaT   facebook_ads\n",
       "158281   35388218       2020-03-31        NaT  yandex-direct\n",
       "158282   35397767       2020-03-31        NaT     google_ads\n",
       "\n",
       "[158283 rows x 4 columns]"
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "final_df['is_conversed'] = np.where(pd.isnull(final_df.date_x), 0, 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "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>device_id</th>\n",
       "      <th>first_start_date</th>\n",
       "      <th>date_x</th>\n",
       "      <th>utm_source</th>\n",
       "      <th>is_conversed</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4013</td>\n",
       "      <td>2020-01-15</td>\n",
       "      <td>NaT</td>\n",
       "      <td>vk_ads</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4014</td>\n",
       "      <td>2020-01-02</td>\n",
       "      <td>2020-02-01</td>\n",
       "      <td>referal</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4016</td>\n",
       "      <td>2020-01-04</td>\n",
       "      <td>NaT</td>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4046</td>\n",
       "      <td>2020-01-08</td>\n",
       "      <td>2020-01-11</td>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4060</td>\n",
       "      <td>2020-01-03</td>\n",
       "      <td>2020-03-07</td>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>1</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>158278</th>\n",
       "      <td>35379281</td>\n",
       "      <td>2020-03-29</td>\n",
       "      <td>NaT</td>\n",
       "      <td>vk_ads</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158279</th>\n",
       "      <td>35380796</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>referal</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158280</th>\n",
       "      <td>35381595</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158281</th>\n",
       "      <td>35388218</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158282</th>\n",
       "      <td>35397767</td>\n",
       "      <td>2020-03-31</td>\n",
       "      <td>NaT</td>\n",
       "      <td>google_ads</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>158283 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        device_id first_start_date     date_x     utm_source  is_conversed\n",
       "0            4013       2020-01-15        NaT         vk_ads             0\n",
       "1            4014       2020-01-02 2020-02-01        referal             1\n",
       "2            4016       2020-01-04        NaT  yandex-direct             0\n",
       "3            4046       2020-01-08 2020-01-11   facebook_ads             1\n",
       "4            4060       2020-01-03 2020-03-07  yandex-direct             1\n",
       "...           ...              ...        ...            ...           ...\n",
       "158278   35379281       2020-03-29        NaT         vk_ads             0\n",
       "158279   35380796       2020-03-31        NaT        referal             0\n",
       "158280   35381595       2020-03-31        NaT   facebook_ads             0\n",
       "158281   35388218       2020-03-31        NaT  yandex-direct             0\n",
       "158282   35397767       2020-03-31        NaT     google_ads             0\n",
       "\n",
       "[158283 rows x 5 columns]"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "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>is_conversed</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>utm_source</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>yandex-direct</th>\n",
       "      <td>0.343438</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>google_ads</th>\n",
       "      <td>0.343914</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>facebook_ads</th>\n",
       "      <td>0.358706</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>instagram_ads</th>\n",
       "      <td>0.368752</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vk_ads</th>\n",
       "      <td>0.388420</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>referal</th>\n",
       "      <td>0.407112</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               is_conversed\n",
       "utm_source                 \n",
       "yandex-direct      0.343438\n",
       "google_ads         0.343914\n",
       "facebook_ads       0.358706\n",
       "instagram_ads      0.368752\n",
       "vk_ads             0.388420\n",
       "referal            0.407112"
      ]
     },
     "execution_count": 114,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "final_df \\\n",
    "    .groupby('utm_source') \\\n",
    "    .agg({'is_conversed': 'mean'}) \\\n",
    "    .sort_values('is_conversed')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Пользователи, пришедшие с какого канала, имеют медианный первый чек выше? (учитываются только первые покупки пользователей)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "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>date_x</th>\n",
       "      <th>event</th>\n",
       "      <th>purchase_sum</th>\n",
       "      <th>os_name</th>\n",
       "      <th>device_id</th>\n",
       "      <th>gender</th>\n",
       "      <th>city</th>\n",
       "      <th>utm_source</th>\n",
       "      <th>registration_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>669460</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>-</td>\n",
       "      <td>2020-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>833621</td>\n",
       "      <td>male</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>vk_ads</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>1579237</td>\n",
       "      <td>male</td>\n",
       "      <td>Saint-Petersburg</td>\n",
       "      <td>referal</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>android</td>\n",
       "      <td>1737182</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>2020-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-01</td>\n",
       "      <td>app_start</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ios</td>\n",
       "      <td>4029024</td>\n",
       "      <td>female</td>\n",
       "      <td>Moscow</td>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>NaT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      date_x      event  purchase_sum  os_name  device_id  gender  \\\n",
       "0 2020-01-01  app_start           NaN  android     669460  female   \n",
       "1 2020-01-01  app_start           NaN      ios     833621    male   \n",
       "2 2020-01-01  app_start           NaN  android    1579237    male   \n",
       "3 2020-01-01  app_start           NaN  android    1737182  female   \n",
       "4 2020-01-01  app_start           NaN      ios    4029024  female   \n",
       "\n",
       "               city    utm_source registration_date  \n",
       "0            Moscow             -        2020-01-07  \n",
       "1            Moscow        vk_ads        2020-01-01  \n",
       "2  Saint-Petersburg       referal        2020-01-01  \n",
       "3            Moscow  facebook_ads        2020-01-01  \n",
       "4            Moscow  facebook_ads               NaT  "
      ]
     },
     "execution_count": 125,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_purchase_date = data.query(\"event == 'purchase'\") \\\n",
    "    .groupby('device_id') \\\n",
    "    .agg({'date_x': 'min'}) \\\n",
    "    .reset_index() \\\n",
    "    .rename(columns={'date_x': 'first_purchase_date'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "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>device_id</th>\n",
       "      <th>first_purchase_date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4014</td>\n",
       "      <td>2020-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4046</td>\n",
       "      <td>2020-01-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4060</td>\n",
       "      <td>2020-03-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4120</td>\n",
       "      <td>2020-01-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4125</td>\n",
       "      <td>2020-01-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   device_id first_purchase_date\n",
       "0       4014          2020-02-01\n",
       "1       4046          2020-01-04\n",
       "2       4060          2020-03-07\n",
       "3       4120          2020-01-07\n",
       "4       4125          2020-01-07"
      ]
     },
     "execution_count": 127,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_purchase_date.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "purchase_df = first_purchase_date.merge(data, how='left', on='device_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "metadata": {},
   "outputs": [],
   "source": [
    "purchase_df = purchase_df.query(\"event == 'purchase' and first_purchase_date == date_x\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 137,
   "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>purchase_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>utm_source</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>facebook_ads</th>\n",
       "      <td>389.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>google_ads</th>\n",
       "      <td>390.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>yandex-direct</th>\n",
       "      <td>392.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vk_ads</th>\n",
       "      <td>393.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>instagram_ads</th>\n",
       "      <td>393.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>referal</th>\n",
       "      <td>395.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>-</th>\n",
       "      <td>398.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               purchase_sum\n",
       "utm_source                 \n",
       "facebook_ads          389.0\n",
       "google_ads            390.5\n",
       "yandex-direct         392.5\n",
       "vk_ads                393.0\n",
       "instagram_ads         393.5\n",
       "referal               395.5\n",
       "-                     398.5"
      ]
     },
     "execution_count": 137,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "purchase_df \\\n",
    "    .groupby('utm_source') \\\n",
    "    .agg({'purchase_sum': 'median'}) \\\n",
    "    .sort_values('purchase_sum')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Данные по затратам на рекламу:\n",
    "\n",
    "# Яндекс – 10 491 707 руб.\n",
    "# Гугл – 10 534 878 руб.\n",
    "# Фейсбук – 8 590 498 руб.\n",
    "# Инстаграм – 8 561626 руб.\n",
    "# ВК – 9 553 531 руб.\n",
    "\n",
    "# Какой платный канал привлечения имеет самый высокий ROMI?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 139,
   "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>purchase_sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>utm_source</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>-</th>\n",
       "      <td>21449749.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>facebook_ads</th>\n",
       "      <td>12249901.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>google_ads</th>\n",
       "      <td>12868276.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>instagram_ads</th>\n",
       "      <td>14546969.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>referal</th>\n",
       "      <td>8837044.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>vk_ads</th>\n",
       "      <td>16389652.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>yandex-direct</th>\n",
       "      <td>13915368.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               purchase_sum\n",
       "utm_source                 \n",
       "-                21449749.5\n",
       "facebook_ads     12249901.0\n",
       "google_ads       12868276.0\n",
       "instagram_ads    14546969.0\n",
       "referal           8837044.5\n",
       "vk_ads           16389652.5\n",
       "yandex-direct    13915368.0"
      ]
     },
     "execution_count": 139,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data \\\n",
    "    .groupby('utm_source') \\\n",
    "    .agg({'purchase_sum': 'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.4259826380263403"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ROMI Фейсбук\n",
    "\n",
    "(12249901 - 8590498) / 8590498"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.22149264566708793"
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ROMI Гугл\n",
    "\n",
    "(12868276 - 10534878) / 10534878"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 145,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.6990895187432854"
      ]
     },
     "execution_count": 145,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ROMI Инстаграм\n",
    "\n",
    "(14546969 - 8561626) / 8561626"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 146,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7155596187420128"
      ]
     },
     "execution_count": 146,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ROMI ВКонтакте\n",
    "\n",
    "(16389652 - 9553531) / 9553531"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 147,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.3263206835646478"
      ]
     },
     "execution_count": 147,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# ROMI Яндекс\n",
    "\n",
    "(13915368 - 10491707) / 10491707"
   ]
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}