product_analytics_метрики_продукта.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Продуктовая аналитика: Метрики продукта\n",
    "\n",
    "Обсудим, что такое метрики и зачем они нужны, что творится на рынке в отношении этих метрик и какие существуют особенности расчёта метрик в различных инструментах. Также поговорим про физический смысл и интерпретацию метрик и потренируемся считать метрики по сырым данным."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Проект: Доставка продуктов\n",
    "\n",
    "Вы работаете продуктовым аналитиком в компании по доставке продуктов на дом. Сервис доступен как в приложении на ios, так и на android. Вы настроили фронтовую аналитику в AppMetrica, и в конце квартала маркетинг-менеджер попросил вас проанализировать поведение пользователей, а также оценить эффективность каналов их привлечения. \n",
    "\n",
    "Вы выгрузили данные из AppMetrica за период с 1 января по 31 марта 2020, только по пользователям, зарегистрированным позднее 1 января 2020.\n",
    "\n",
    "Описание данных \n",
    "\n",
    "- date – дата совершения события\n",
    "- event - событие\n",
    "\n",
    "    - app_install – установка приложения\n",
    "    - app_start – открыть приложения\n",
    "    - register – зарегистрироваться \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",
    "    - referal – акция «приведи друга»\n",
    "    - Если стоит ‘-’, то канал не определен или это скачивание приложения напрямую или посещение не с рекламы  \n",
    "    \n",
    "- purchase_sum – стоимость покупки (при совершении события ‘purchase’)\n",
    "\n",
    "\n",
    "Обратите внимание на следующее:\n",
    "\n",
    "- В выгрузке только уникальные действия пользователей за каждый день \n",
    "- Можно миновать стадию установки приложения, если оно было установлено ранее\n",
    "- Можно миновать стадию регистрации, если пользователь был уже залогинен на момент сессии. Однако незарегистрированные пользователи не могут оформить покупку. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import numpy as np\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {},
   "outputs": [],
   "source": [
    "kc = pd.read_csv('/mnt/HC_Volume_18315164/home-jupyter/jupyter-a-eremina/KC_case_data.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "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": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kc.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Какие две метрики, по сравнению с остальными, поможет ответить на вопрос «Насколько хорошо маркетологи справляются со своей задачей привлечения новых пользователей»?\n",
    "\n",
    "\n",
    "- DAU\n",
    "- **CAC**\n",
    "- Количество сессий\n",
    "- **Количество инсталлов**\n",
    "- Retention 7-го дня\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Какая не денежная метрика в динамике лучше всего отражает нравится ли продукт пользователям?\n",
    "\n",
    "- Выручка\n",
    "- CR из установки в открытие приложения\n",
    "- CR из открытия приложения в покупку\n",
    "- MAU\n",
    "- **CR в повторную покупку в течение месяца**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "MAU февраля:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {},
   "outputs": [],
   "source": [
    "kc['date'] = pd.to_datetime(kc.date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "75032"
      ]
     },
     "execution_count": 84,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kc.query('date.dt.month == 2').device_id.nunique()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Количество установок в январе:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "80297"
      ]
     },
     "execution_count": 85,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kc.query(\"date.dt.month == 1 and event == 'app_install'\").device_id.count()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Проанализируйте на каком этапе воронки отваливается бОльшая часть клиентов. Посмотрите отдельно сценарии для зарегистрированных и для незарегистрированных пользователей. На каком шаге отваливается больше всего зарегистрированных пользователей?\n",
    "\n",
    "В ответ впишите один из шагов, конверсия из которого (в следующий шаг) самая низкая"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {},
   "outputs": [],
   "source": [
    "first_reg = kc.query('event == \"register\"').sort_values('date').drop_duplicates('device_id')\n",
    "kc['first_reg'] = kc['device_id'].map(first_reg.set_index('device_id')['date'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {},
   "outputs": [],
   "source": [
    "already_reg = kc.query('first_reg < date')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "event\n",
       "app_start      40991\n",
       "choose_item    37926\n",
       "purchase       24880\n",
       "search         40482\n",
       "tap_basket     34517\n",
       "Name: device_id, dtype: int64"
      ]
     },
     "execution_count": 88,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "already_reg.groupby('event')['device_id'].nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9875826400917275"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# конверсия из регистрации в поиск\n",
    "40482/40991"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9252274889609914"
      ]
     },
     "execution_count": 90,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# конверсия из поиска в добавление товара\n",
    "37926/40991"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.9101144333702473"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# конверсия из добавления товара в переход в корзину\n",
    "34517/37926"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7208042413883015"
      ]
     },
     "execution_count": 92,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# конверсия из перехода в корзину в покупку\n",
    "24880/34517"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Переход в корзину - минимум"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Пользователи, пришедшие с каких каналов, показали самую низкую конверсию в первую покупку?\n",
    "\n",
    "В ответ впишите один из каналов в таком формате: «Яндекс», «Гугл», «ВК», «Фейсбук», «Инстаграм», «Реферальная программа».\n",
    "\n",
    "Подсказка: \n",
    "\n",
    "- CR = кол-во юзеров, впервые совершивших покупку/кол-во юзеров, впервые открывших приложение "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "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>utm_source</th>\n",
       "      <th>count_purchase</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-</td>\n",
       "      <td>16598</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>9017</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>google_ads</td>\n",
       "      <td>11339</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>instagram_ads</td>\n",
       "      <td>10762</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>referal</td>\n",
       "      <td>6362</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>vk_ads</td>\n",
       "      <td>12364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>12028</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      utm_source  count_purchase\n",
       "0              -           16598\n",
       "1   facebook_ads            9017\n",
       "2     google_ads           11339\n",
       "3  instagram_ads           10762\n",
       "4        referal            6362\n",
       "5         vk_ads           12364\n",
       "6  yandex-direct           12028"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = kc.query('event == \"purchase\"').groupby('utm_source', as_index = False) \\\n",
    "       .agg({'device_id' : 'nunique'}).rename(columns={'device_id' : 'count_purchase'})\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "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>utm_source</th>\n",
       "      <th>count_start</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-</td>\n",
       "      <td>52268</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>25957</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>google_ads</td>\n",
       "      <td>38092</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>instagram_ads</td>\n",
       "      <td>31045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>referal</td>\n",
       "      <td>15924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>vk_ads</td>\n",
       "      <td>34079</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>40707</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      utm_source  count_start\n",
       "0              -        52268\n",
       "1   facebook_ads        25957\n",
       "2     google_ads        38092\n",
       "3  instagram_ads        31045\n",
       "4        referal        15924\n",
       "5         vk_ads        34079\n",
       "6  yandex-direct        40707"
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = kc.query('event == \"app_start\"').groupby('utm_source', as_index = False) \\\n",
    "  .agg({'device_id' : 'nunique'}).rename(columns={'device_id' : 'count_start'})\n",
    "df1 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "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>utm_source</th>\n",
       "      <th>count_purchase</th>\n",
       "      <th>count_start</th>\n",
       "      <th>CR</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>referal</td>\n",
       "      <td>6362</td>\n",
       "      <td>15924</td>\n",
       "      <td>0.399523</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>vk_ads</td>\n",
       "      <td>12364</td>\n",
       "      <td>34079</td>\n",
       "      <td>0.362804</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>9017</td>\n",
       "      <td>25957</td>\n",
       "      <td>0.347382</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>instagram_ads</td>\n",
       "      <td>10762</td>\n",
       "      <td>31045</td>\n",
       "      <td>0.346658</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-</td>\n",
       "      <td>16598</td>\n",
       "      <td>52268</td>\n",
       "      <td>0.317556</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>google_ads</td>\n",
       "      <td>11339</td>\n",
       "      <td>38092</td>\n",
       "      <td>0.297674</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>12028</td>\n",
       "      <td>40707</td>\n",
       "      <td>0.295477</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      utm_source  count_purchase  count_start        CR\n",
       "4        referal            6362        15924  0.399523\n",
       "5         vk_ads           12364        34079  0.362804\n",
       "1   facebook_ads            9017        25957  0.347382\n",
       "3  instagram_ads           10762        31045  0.346658\n",
       "0              -           16598        52268  0.317556\n",
       "2     google_ads           11339        38092  0.297674\n",
       "6  yandex-direct           12028        40707  0.295477"
      ]
     },
     "execution_count": 99,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = df.merge(df1, on = 'utm_source')\n",
    "df['CR'] = df.count_purchase / df.count_start\n",
    "df = df.sort_values('CR', ascending=False)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "У Яндкса самая низкая конверсия в покупку"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Пользователи, пришедшие с какого канала, имеют медианный первый чек выше? (учитываются только первые покупки пользователей)\n",
    "\n",
    "В ответ впишите один из каналов в таком формате: «Яндекс», «Гугл», «ВК», «Фейсбук», «Инстаграм», «Реферальная программа».\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "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>utm_source</th>\n",
       "      <th>device_id</th>\n",
       "      <th>purchase_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-</td>\n",
       "      <td>4046</td>\n",
       "      <td>429.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>-</td>\n",
       "      <td>4120</td>\n",
       "      <td>582.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-</td>\n",
       "      <td>4202</td>\n",
       "      <td>552.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-</td>\n",
       "      <td>4214</td>\n",
       "      <td>231.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-</td>\n",
       "      <td>4234</td>\n",
       "      <td>583.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78465</th>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>35245401</td>\n",
       "      <td>202.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78466</th>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>35258588</td>\n",
       "      <td>157.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78467</th>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>35266881</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78468</th>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>35277913</td>\n",
       "      <td>81.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>78469</th>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>35308278</td>\n",
       "      <td>658.5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>78470 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          utm_source  device_id  purchase_sum\n",
       "0                  -       4046         429.5\n",
       "1                  -       4120         582.5\n",
       "2                  -       4202         552.0\n",
       "3                  -       4214         231.5\n",
       "4                  -       4234         583.5\n",
       "...              ...        ...           ...\n",
       "78465  yandex-direct   35245401         202.5\n",
       "78466  yandex-direct   35258588         157.0\n",
       "78467  yandex-direct   35266881          80.0\n",
       "78468  yandex-direct   35277913          81.0\n",
       "78469  yandex-direct   35308278         658.5\n",
       "\n",
       "[78470 rows x 3 columns]"
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check = kc.query('event == \"purchase\"').groupby(['utm_source', 'device_id'],as_index=False).agg({'purchase_sum' : 'first'})\n",
    "check"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "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>utm_source</th>\n",
       "      <th>purchase_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-</td>\n",
       "      <td>398.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>referal</td>\n",
       "      <td>395.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>instagram_ads</td>\n",
       "      <td>393.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>vk_ads</td>\n",
       "      <td>393.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>392.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>390.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>google_ads</td>\n",
       "      <td>390.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      utm_source  purchase_sum\n",
       "0              -         398.5\n",
       "4        referal         395.0\n",
       "3  instagram_ads         393.5\n",
       "5         vk_ads         393.0\n",
       "6  yandex-direct         392.5\n",
       "1   facebook_ads         390.5\n",
       "2     google_ads         390.0"
      ]
     },
     "execution_count": 101,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "check.groupby('utm_source',as_index=False).agg({'purchase_sum' : 'median'}).sort_values('purchase_sum', ascending = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Медианный первый чек выше у пользователей с реферальной программы"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Данные по затратам на рекламу:\n",
    "\n",
    "Яндекс – 10 491 707 руб.\n",
    "\n",
    "Гугл – 10 534 878 руб.\n",
    "\n",
    "Фейсбук – 8 590 498 руб.\n",
    "\n",
    "Инстаграм – 8 561626 руб.\n",
    "\n",
    "ВК – 9 553 531руб.\n",
    "\n",
    "Расходы на реферальную программу: если пользователь приведет друга и последний совершит первую покупку, то оба получат по 100 рублей.\n",
    "\n",
    "Какой платный канал привлечения (среди рекламных) имеет самый высокий ROMI? \n",
    "\n",
    "В ответ впишите один из каналов в таком формате: «Яндекс», «Гугл», «ВК», «Фейсбук», «Инстаграм»."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### ROMI = (вал.прибыль - расходы на рекламу)/расходы на рекламу"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "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>utm_source</th>\n",
       "      <th>purchase_sum</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-</td>\n",
       "      <td>21449749.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>facebook_ads</td>\n",
       "      <td>12249901.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>google_ads</td>\n",
       "      <td>12868276.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>instagram_ads</td>\n",
       "      <td>14546969.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>referal</td>\n",
       "      <td>8837044.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>vk_ads</td>\n",
       "      <td>16389652.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>yandex-direct</td>\n",
       "      <td>13915368.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      utm_source  purchase_sum\n",
       "0              -    21449749.5\n",
       "1   facebook_ads    12249901.0\n",
       "2     google_ads    12868276.0\n",
       "3  instagram_ads    14546969.0\n",
       "4        referal     8837044.5\n",
       "5         vk_ads    16389652.5\n",
       "6  yandex-direct    13915368.0"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "kc.query('event == \"purchase\"').groupby('utm_source', as_index=False).agg({'purchase_sum' : 'sum'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.4259826380263403"
      ]
     },
     "execution_count": 103,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# facebook_ads\n",
    "(12249901.0 - 8590498) / 8590498"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.22149264566708793"
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# google_ads\n",
    "(12868276.0 - 10534878) / 10534878 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.6990895187432854"
      ]
     },
     "execution_count": 105,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# instagram_ads\n",
    "(14546969.0 - 8561626) / 8561626"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.7155596710786828"
      ]
     },
     "execution_count": 106,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# vk_ads\n",
    "(16389652.5 - 9553531) / 9553531"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "ВК имеет самый высокий ROMI"
   ]
  }
 ],
 "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
}