kpi_analysys.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d39ade49",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import os\n",
    "import sqlalchemy\n",
    "\n",
    "from collections import Counter\n",
    "import plotly.express as pe\n",
    "from dotenv import load_dotenv\n",
    "import plotly.io as pio\n",
    "pio.renderers.default='notebook'\n",
    "\n",
    "from datetime import date\n",
    "from datetime import datetime\n",
    "import copy\n",
    "\n",
    "import tqdm\n",
    "from IPython.display import clear_output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "bb127660",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:90% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:90% !important; }</style>\"))\n",
    "\n",
    "pd.set_option('max_colwidth', 120)\n",
    "pd.set_option('display.width', 500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "16402ec3",
   "metadata": {},
   "outputs": [],
   "source": [
    "from dython import nominal"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a64bed58",
   "metadata": {},
   "source": [
    "----------"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "3e27f76f",
   "metadata": {},
   "outputs": [],
   "source": [
    "load_dotenv('.env')\n",
    "\n",
    "host = os.getenv('EVENTS_DB_HOST')\n",
    "db = os.getenv('EVENTS_DB_NAME')\n",
    "user = os.getenv('EVENTS_DB_USER')\n",
    "password = os.getenv('EVENTS_DB_PASSWORD')\n",
    "port = os.getenv('EVENTS_DB_PORT')\n",
    "connection_str = 'postgresql://{0}:{1}@{2}:{3}/{4}'.format(user, password, host, port, db)\n",
    "\n",
    "engine = sqlalchemy.create_engine(connection_str, execution_options={\"stream_results\":True})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9642fe12",
   "metadata": {},
   "outputs": [],
   "source": [
    "ACCOUNT_ID = 11"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "83d51a74",
   "metadata": {},
   "outputs": [],
   "source": [
    "from dateutil import rrule\n",
    "from datetime import timedelta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "c72544bc",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CPU times: user 830 ms, sys: 73.7 ms, total: 903 ms\n",
      "Wall time: 1.27 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "query_sessions = f'''\n",
    "    select *\n",
    "    from data.customer_profile_sessions cps \n",
    "\n",
    "    left join(\n",
    "    select id as beh_id,guest_id, customer_profile_id from data.customer_profile_behaviour cpb\n",
    "    where cpb.account_id = {ACCOUNT_ID}\n",
    "    ) cpb on cpb.beh_id = cps.customer_profile_behaviour_id\n",
    "\n",
    "\n",
    "    where cps.account_id = {ACCOUNT_ID} and cps.garbage_session = False and cps.session_start >= '2023-01-01' and cps.session_start < '2023-02-01' \n",
    "'''\n",
    "\n",
    "sessions = pd.read_sql_query(query_sessions, engine)\n",
    "\n",
    "\n",
    "query_sessions1 = f'''\n",
    "    select *\n",
    "    from data.customer_profile_session_journey_statuses\n",
    "    where account_id = {ACCOUNT_ID} \n",
    "    and created >= '2022-12-25' and created < '2023-02-05'\n",
    "\n",
    "'''\n",
    "sessions1 = pd.read_sql_query(query_sessions1, engine)\n",
    "\n",
    "query_sessions2 = f'''\n",
    "    select *\n",
    "    from data.customer_journey_statuses\n",
    "    \n",
    "'''\n",
    "statuses = pd.read_sql_query(query_sessions2, engine)\n",
    "\n",
    "query_sessions3 = f'''\n",
    "    select *\n",
    "    from data.sessions_campaign_dict\n",
    "    \n",
    "'''\n",
    "campaigns = pd.read_sql_query(query_sessions3, engine)\n",
    "\n",
    "query_sessions4 = f'''\n",
    "    select *\n",
    "    from data.sessions_source_dict\n",
    "    \n",
    "'''\n",
    "sources = pd.read_sql_query(query_sessions4, engine)\n",
    "\n",
    "query_sessions5 = f'''\n",
    "    select *\n",
    "    from data.sessions_medium_dict\n",
    "    \n",
    "'''\n",
    "mediums = pd.read_sql_query(query_sessions5, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0c60fe5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sessions['duration'] = sessions['session_end'] - sessions['session_start']\n",
    "sessions['duration'] = sessions['duration'].apply(lambda x: x.total_seconds())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "94b64196",
   "metadata": {},
   "outputs": [],
   "source": [
    "statuses = statuses.rename(columns={'id': 'journey_status_id'})\n",
    "sources = sources.rename(columns={'id': 'source_id'})\n",
    "mediums = mediums.rename(columns={'id': 'medium_id'})\n",
    "campaigns = campaigns.rename(columns={'id': 'campaign_id'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "58d194fe",
   "metadata": {},
   "outputs": [],
   "source": [
    "sessions1 = sessions1.merge(statuses[['journey_status_id', 'status_name']], how='left', on='journey_status_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "38ae3e2c",
   "metadata": {},
   "outputs": [],
   "source": [
    "sessions = sessions.merge(sources, how='left', on='source_id').merge(mediums, how='left', on='medium_id').merge(campaigns, how='left', on='campaign_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "3e36a917",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25149"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sessions.id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "94f8d522",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "25149"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sessions = sessions.merge(sessions1[['id', 'journey_status_id', 'created', 'status_name']], how='inner', on='id')\n",
    "sessions.id.nunique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "82157bd7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Organic Search', 'Social', 'Direct', 'Referral', 'Email', 'Paid Search']"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chanls = sessions.channel.unique().tolist()\n",
    "chanls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "682d9cdd",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Consideration',\n",
       " 'Bounce',\n",
       " 'Awareness',\n",
       " 'Acquisition',\n",
       " 'Service',\n",
       " 'LoyaltyPlus',\n",
       " 'Loyalty']"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "types = sessions.status_name.unique().tolist()\n",
    "types"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "da2fce93",
   "metadata": {},
   "source": [
    "------"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "7e3da463",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 25149 entries, 0 to 25148\n",
      "Data columns (total 47 columns):\n",
      " #   Column                         Non-Null Count  Dtype         \n",
      "---  ------                         --------------  -----         \n",
      " 0   id                             25149 non-null  int64         \n",
      " 1   s_id                           25149 non-null  object        \n",
      " 2   account_id                     25149 non-null  int64         \n",
      " 3   customer_profile_behaviour_id  25149 non-null  int64         \n",
      " 4   channel                        25149 non-null  object        \n",
      " 5   utm_source                     15709 non-null  object        \n",
      " 6   utm_medium                     15553 non-null  object        \n",
      " 7   utm_campaign                   15034 non-null  object        \n",
      " 8   utm_content                    15409 non-null  object        \n",
      " 9   utm_term                       5 non-null      object        \n",
      " 10  ip                             25149 non-null  object        \n",
      " 11  ipcountry                      25149 non-null  object        \n",
      " 12  browser_family                 25149 non-null  object        \n",
      " 13  browser_version                20869 non-null  object        \n",
      " 14  os_family                      25149 non-null  object        \n",
      " 15  os_version                     25079 non-null  object        \n",
      " 16  device_family                  25149 non-null  object        \n",
      " 17  device_brand                   23788 non-null  object        \n",
      " 18  device_model                   23788 non-null  object        \n",
      " 19  device_type                    25149 non-null  object        \n",
      " 20  screen                         25149 non-null  object        \n",
      " 21  language                       25149 non-null  object        \n",
      " 22  session_start                  25149 non-null  datetime64[ns]\n",
      " 23  session_end                    25149 non-null  datetime64[ns]\n",
      " 24  created_x                      25149 non-null  datetime64[ns]\n",
      " 25  updated                        25149 non-null  datetime64[ns]\n",
      " 26  actions_count                  25149 non-null  int64         \n",
      " 27  page_views_count               25149 non-null  int64         \n",
      " 28  referer                        15977 non-null  object        \n",
      " 29  add_to_basket_count            25149 non-null  int64         \n",
      " 30  garbage_session                25149 non-null  bool          \n",
      " 31  s_ci                           25149 non-null  int64         \n",
      " 32  source_id                      25149 non-null  int64         \n",
      " 33  medium_id                      25149 non-null  int64         \n",
      " 34  campaign_id                    25149 non-null  int64         \n",
      " 35  source_medium_id               25149 non-null  int64         \n",
      " 36  is_conversion                  507 non-null    object        \n",
      " 37  beh_id                         25149 non-null  int64         \n",
      " 38  guest_id                       25149 non-null  object        \n",
      " 39  customer_profile_id            25149 non-null  int64         \n",
      " 40  duration                       25149 non-null  float64       \n",
      " 41  source                         25149 non-null  object        \n",
      " 42  medium                         25149 non-null  object        \n",
      " 43  campaign                       25149 non-null  object        \n",
      " 44  journey_status_id              25149 non-null  int64         \n",
      " 45  created_y                      25149 non-null  datetime64[ns]\n",
      " 46  status_name                    25149 non-null  object        \n",
      "dtypes: bool(1), datetime64[ns](5), float64(1), int64(14), object(26)\n",
      "memory usage: 9.0+ MB\n"
     ]
    }
   ],
   "source": [
    "sessions.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "a3e5f94b",
   "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>source</th>\n",
       "      <th>medium</th>\n",
       "      <th>campaign</th>\n",
       "      <th>ipcountry</th>\n",
       "      <th>device_family</th>\n",
       "      <th>add_to_basket_count</th>\n",
       "      <th>duration</th>\n",
       "      <th>status_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>google.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>GB</td>\n",
       "      <td>Huawei CLT-L29</td>\n",
       "      <td>0</td>\n",
       "      <td>385.043</td>\n",
       "      <td>Consideration</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>meta</td>\n",
       "      <td>Facebook_Mobile_Feed</td>\n",
       "      <td>TOFU_Winter22_Prospecting_Europe_Excluded-LT</td>\n",
       "      <td>IE</td>\n",
       "      <td>Samsung SM-A037G</td>\n",
       "      <td>0</td>\n",
       "      <td>8.683</td>\n",
       "      <td>Bounce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>meta</td>\n",
       "      <td>Facebook_Marketplace</td>\n",
       "      <td>TOFU_Winter22_Prospecting_Europe_Excluded-LT</td>\n",
       "      <td>NL</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>0</td>\n",
       "      <td>30.002</td>\n",
       "      <td>Bounce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IGShopping</td>\n",
       "      <td>Social</td>\n",
       "      <td>(none)</td>\n",
       "      <td>NO</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>0</td>\n",
       "      <td>15.805</td>\n",
       "      <td>Bounce</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>GB</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>0</td>\n",
       "      <td>38.233</td>\n",
       "      <td>Awareness</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       source                medium                                      campaign ipcountry     device_family  add_to_basket_count  duration    status_name\n",
       "0  google.com              referral                                        (none)        GB    Huawei CLT-L29                    0   385.043  Consideration\n",
       "1        meta  Facebook_Mobile_Feed  TOFU_Winter22_Prospecting_Europe_Excluded-LT        IE  Samsung SM-A037G                    0     8.683         Bounce\n",
       "2        meta  Facebook_Marketplace  TOFU_Winter22_Prospecting_Europe_Excluded-LT        NL            iPhone                    0    30.002         Bounce\n",
       "3  IGShopping                Social                                        (none)        NO            iPhone                    0    15.805         Bounce\n",
       "4    (direct)                (none)                                        (none)        GB            iPhone                    0    38.233      Awareness"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sliced = sessions[['source', 'medium', 'campaign', 'ipcountry', 'device_family', 'add_to_basket_count', 'duration', 'status_name']]\n",
    "smcid = [['source_id', 'medium_id', 'campaign_id', 'ipcountry', 'device_family']]\n",
    "kpiv = ['add_to_basket_count', 'duration', 'status_name']\n",
    "sliced.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "fc084d51",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "source       medium    campaign  ipcountry  device_family   \n",
       "(direct)     (none)    (none)    AE         Other               1\n",
       "                                            iPhone              1\n",
       "                                 AF         Samsung SM-G950F    2\n",
       "                                 AT         2203121C            1\n",
       "                                            Huawei VOG-L29      2\n",
       "                                                               ..\n",
       "youtube.com  referral  (none)    NL         Other               1\n",
       "                                 NZ         iPhone              1\n",
       "                                 US         Mac                 1\n",
       "                                            Samsung SM-T827V    1\n",
       "                                            iPhone              3\n",
       "Length: 5754, dtype: int64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sliced.groupby(['source', 'medium', 'campaign', 'ipcountry', 'device_family']).size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "fe690c30",
   "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>source</th>\n",
       "      <th>medium</th>\n",
       "      <th>campaign</th>\n",
       "      <th>ipcountry</th>\n",
       "      <th>device_family</th>\n",
       "      <th>length</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AE</td>\n",
       "      <td>Other</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AF</td>\n",
       "      <td>Samsung SM-G950F</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AT</td>\n",
       "      <td>2203121C</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AT</td>\n",
       "      <td>Huawei VOG-L29</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     source  medium campaign ipcountry     device_family  length\n",
       "0  (direct)  (none)   (none)        AE             Other       1\n",
       "1  (direct)  (none)   (none)        AE            iPhone       1\n",
       "2  (direct)  (none)   (none)        AF  Samsung SM-G950F       2\n",
       "3  (direct)  (none)   (none)        AT          2203121C       1\n",
       "4  (direct)  (none)   (none)        AT    Huawei VOG-L29       2"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combs = sliced.groupby(['source', 'medium', 'campaign', 'ipcountry', 'device_family']).size().reset_index().rename(columns={0: 'length'})\n",
    "combs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "7f467eb1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(5754, 9)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combs['bounce_rate'] = 0\n",
    "combs['conversion_rate'] = 0\n",
    "combs['med_duration'] = 0\n",
    "combs.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "8e9ed49d",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████████████████████████████████████████████████████████████████████████████████████████████████| 5754/5754 [01:00<00:00, 95.47it/s]\n"
     ]
    }
   ],
   "source": [
    "clear_output(wait=True)\n",
    "\n",
    "rown = 0\n",
    "pbar = tqdm.tqdm(total=len(combs))\n",
    "for index, row in combs.iterrows():\n",
    "#     print(row['source'])\n",
    "    cuts = sliced[(sliced.source == row.source) & (sliced.medium == row.medium) & \n",
    "                  (sliced.campaign == row.campaign) & (sliced.ipcountry == row.ipcountry) &\n",
    "                  (sliced.device_family == row.device_family)]\n",
    "    \n",
    "    bounc = len(cuts[cuts.status_name == 'Bounce'])\n",
    "    atb = len(cuts[cuts.add_to_basket_count >= 1])\n",
    "    le = row['length']\n",
    "#     print(rown, bounc, atb, le)\n",
    "    \n",
    "    if bounc != 0:\n",
    "        try:\n",
    "            combs.iloc[rown, combs.columns.get_loc('bounce_rate')] = bounc/le\n",
    "        except ZeroDivisionError:\n",
    "            combs.iloc[rown, combs.columns.get_loc('bounce_rate')] = 0\n",
    "    else:\n",
    "        combs.iloc[rown, combs.columns.get_loc('bounce_rate')] = 0\n",
    "    \n",
    "    if atb != 0:\n",
    "        try:\n",
    "            combs.iloc[rown, combs.columns.get_loc('conversion_rate')] = atb/le\n",
    "        except ZeroDivisionError:\n",
    "            combs.iloc[rown, combs.columns.get_loc('conversion_rate')] = 0\n",
    "    else:\n",
    "        combs.iloc[rown, combs.columns.get_loc('conversion_rate')] = 0\n",
    "\n",
    "    if cuts.duration.sum() != 0:   \n",
    "        try:\n",
    "            combs.iloc[rown, combs.columns.get_loc('med_duration')] = cuts.duration.median()\n",
    "        except ZeroDivisionError:\n",
    "            combs.iloc[rown, combs.columns.get_loc('med_duration')] = 0\n",
    "    else:\n",
    "        combs.iloc[rown, combs.columns.get_loc('med_duration')] = 0\n",
    "    \n",
    "    pbar.update(1)\n",
    "    rown += 1\n",
    "\n",
    "pbar.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "21427a01",
   "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>source</th>\n",
       "      <th>medium</th>\n",
       "      <th>campaign</th>\n",
       "      <th>ipcountry</th>\n",
       "      <th>device_family</th>\n",
       "      <th>length</th>\n",
       "      <th>bounce_rate</th>\n",
       "      <th>conversion_rate</th>\n",
       "      <th>med_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AE</td>\n",
       "      <td>Other</td>\n",
       "      <td>1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>17.170</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>77.412</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AF</td>\n",
       "      <td>Samsung SM-G950F</td>\n",
       "      <td>2</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>135.370</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AT</td>\n",
       "      <td>2203121C</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>141.854</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AT</td>\n",
       "      <td>Huawei VOG-L29</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>194.603</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     source  medium campaign ipcountry     device_family  length  bounce_rate  conversion_rate  med_duration\n",
       "0  (direct)  (none)   (none)        AE             Other       1          1.0              0.0        17.170\n",
       "1  (direct)  (none)   (none)        AE            iPhone       1          0.0              0.0        77.412\n",
       "2  (direct)  (none)   (none)        AF  Samsung SM-G950F       2          1.0              0.0       135.370\n",
       "3  (direct)  (none)   (none)        AT          2203121C       1          0.0              0.0       141.854\n",
       "4  (direct)  (none)   (none)        AT    Huawei VOG-L29       2          0.0              0.0       194.603"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5fcdaaf1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# combs.to_csv('combs.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "20ba4506",
   "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>source</th>\n",
       "      <th>medium</th>\n",
       "      <th>campaign</th>\n",
       "      <th>ipcountry</th>\n",
       "      <th>device_family</th>\n",
       "      <th>length</th>\n",
       "      <th>bounce_rate</th>\n",
       "      <th>conversion_rate</th>\n",
       "      <th>med_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AT</td>\n",
       "      <td>Other</td>\n",
       "      <td>15</td>\n",
       "      <td>0.266667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>108.9300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AT</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>30</td>\n",
       "      <td>0.233333</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>156.1095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>BE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>76</td>\n",
       "      <td>0.421053</td>\n",
       "      <td>0.026316</td>\n",
       "      <td>27.1455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>CA</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>15</td>\n",
       "      <td>0.733333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.3100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>CH</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>18</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>145.2065</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      source  medium campaign ipcountry device_family  length  bounce_rate  conversion_rate  med_duration\n",
       "6   (direct)  (none)   (none)        AT         Other      15     0.266667         0.000000      108.9300\n",
       "9   (direct)  (none)   (none)        AT        iPhone      30     0.233333         0.066667      156.1095\n",
       "20  (direct)  (none)   (none)        BE        iPhone      76     0.421053         0.026316       27.1455\n",
       "24  (direct)  (none)   (none)        CA        iPhone      15     0.733333         0.000000        8.3100\n",
       "32  (direct)  (none)   (none)        CH        iPhone      18     0.333333         0.000000      145.2065"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combs = combs[combs.length >= 10].reset_index(drop=True)\n",
    "combs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "90477224",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "24"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(combs.source.nunique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "id": "88cd28d3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "source\n",
       "(direct)             31\n",
       "IGShopping           10\n",
       "facebook.com         19\n",
       "google.com           21\n",
       "instagram.com        14\n",
       "iwearundress.com     35\n",
       "meta                155\n",
       "dtype: int64"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combs.groupby('source').size()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "id": "3aed9896",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "93\n",
      "7\n"
     ]
    }
   ],
   "source": [
    "norms = sliced.groupby(['source']).size().reset_index().rename(columns={0: 'length'})\n",
    "print(len(norms))\n",
    "norms = norms[norms['source'].isin(combs.source.unique().tolist())].reset_index(drop=True)\n",
    "print(len(norms))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "id": "0c6a0f9c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(7, 5)"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "norms['bounce_rate'] = 0\n",
    "norms['conversion_rate'] = 0\n",
    "norms['med_duration'] = 0\n",
    "norms.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "id": "becbf01b",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|███████████████████████████████████████████████████████████████████████████████████████████████████████████| 7/7 [00:00<00:00, 225.31it/s]\n"
     ]
    }
   ],
   "source": [
    "clear_output(wait=True)\n",
    "\n",
    "rown = 0\n",
    "pbar = tqdm.tqdm(total=len(norms))\n",
    "for index, row in norms.iterrows():\n",
    "#     print(row['source'])\n",
    "    cuts = sliced[(sliced.source == row.source)]\n",
    "    \n",
    "    bounc = len(cuts[cuts.status_name == 'Bounce'])\n",
    "    atb = len(cuts[cuts.add_to_basket_count >= 1])\n",
    "    le = row['length']\n",
    "#     print(rown, bounc, atb, le)\n",
    "    \n",
    "    if bounc != 0:\n",
    "        try:\n",
    "            norms.iloc[rown, norms.columns.get_loc('bounce_rate')] = bounc/le\n",
    "        except ZeroDivisionError:\n",
    "            norms.iloc[rown, norms.columns.get_loc('bounce_rate')] = 0\n",
    "    else:\n",
    "        norms.iloc[rown, norms.columns.get_loc('bounce_rate')] = 0\n",
    "    \n",
    "    if atb != 0:\n",
    "        try:\n",
    "            norms.iloc[rown, norms.columns.get_loc('conversion_rate')] = atb/le\n",
    "        except ZeroDivisionError:\n",
    "            norms.iloc[rown, norms.columns.get_loc('conversion_rate')] = 0\n",
    "    else:\n",
    "        norms.iloc[rown, norms.columns.get_loc('conversion_rate')] = 0\n",
    "\n",
    "    if cuts.duration.sum() != 0:   \n",
    "        try:\n",
    "            norms.iloc[rown, norms.columns.get_loc('med_duration')] = cuts.duration.median()\n",
    "        except ZeroDivisionError:\n",
    "            norms.iloc[rown, norms.columns.get_loc('med_duration')] = 0\n",
    "    else:\n",
    "        norms.iloc[rown, norms.columns.get_loc('med_duration')] = 0\n",
    "    \n",
    "    pbar.update(1)\n",
    "    rown += 1\n",
    "\n",
    "pbar.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "e0de4047",
   "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>source</th>\n",
       "      <th>length</th>\n",
       "      <th>bounce_rate</th>\n",
       "      <th>conversion_rate</th>\n",
       "      <th>med_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>2408</td>\n",
       "      <td>0.433970</td>\n",
       "      <td>0.019934</td>\n",
       "      <td>32.8780</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>IGShopping</td>\n",
       "      <td>356</td>\n",
       "      <td>0.221910</td>\n",
       "      <td>0.033708</td>\n",
       "      <td>82.7105</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>facebook.com</td>\n",
       "      <td>1192</td>\n",
       "      <td>0.166946</td>\n",
       "      <td>0.011745</td>\n",
       "      <td>80.7195</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>google.com</td>\n",
       "      <td>1595</td>\n",
       "      <td>0.264577</td>\n",
       "      <td>0.031975</td>\n",
       "      <td>105.4340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>instagram.com</td>\n",
       "      <td>1030</td>\n",
       "      <td>0.072816</td>\n",
       "      <td>0.010680</td>\n",
       "      <td>101.0330</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          source  length  bounce_rate  conversion_rate  med_duration\n",
       "0       (direct)    2408     0.433970         0.019934       32.8780\n",
       "1     IGShopping     356     0.221910         0.033708       82.7105\n",
       "2   facebook.com    1192     0.166946         0.011745       80.7195\n",
       "3     google.com    1595     0.264577         0.031975      105.4340\n",
       "4  instagram.com    1030     0.072816         0.010680      101.0330"
      ]
     },
     "execution_count": 77,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "norms.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "id": "146f87e7",
   "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>source</th>\n",
       "      <th>medium</th>\n",
       "      <th>campaign</th>\n",
       "      <th>ipcountry</th>\n",
       "      <th>device_family</th>\n",
       "      <th>length</th>\n",
       "      <th>bounce_rate</th>\n",
       "      <th>conversion_rate</th>\n",
       "      <th>med_duration</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AT</td>\n",
       "      <td>Other</td>\n",
       "      <td>15</td>\n",
       "      <td>0.266667</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>108.9300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>AT</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>30</td>\n",
       "      <td>0.233333</td>\n",
       "      <td>0.066667</td>\n",
       "      <td>156.1095</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>BE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>76</td>\n",
       "      <td>0.421053</td>\n",
       "      <td>0.026316</td>\n",
       "      <td>27.1455</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>CA</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>15</td>\n",
       "      <td>0.733333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.3100</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>CH</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>18</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>145.2065</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      source  medium campaign ipcountry device_family  length  bounce_rate  conversion_rate  med_duration\n",
       "6   (direct)  (none)   (none)        AT         Other      15     0.266667         0.000000      108.9300\n",
       "9   (direct)  (none)   (none)        AT        iPhone      30     0.233333         0.066667      156.1095\n",
       "20  (direct)  (none)   (none)        BE        iPhone      76     0.421053         0.026316       27.1455\n",
       "24  (direct)  (none)   (none)        CA        iPhone      15     0.733333         0.000000        8.3100\n",
       "32  (direct)  (none)   (none)        CH        iPhone      18     0.333333         0.000000      145.2065"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "combs.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "id": "aeec3df6",
   "metadata": {},
   "outputs": [],
   "source": [
    "kpi = ['bounce_rate', 'conversion_rate', 'med_duration']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "d5e118ce",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n",
      "/tmp/ipykernel_157821/1334107378.py:27: FutureWarning:\n",
      "\n",
      "The frame.append method is deprecated and will be removed from pandas in a future version. Use pandas.concat instead.\n",
      "\n"
     ]
    }
   ],
   "source": [
    "tops = pd.DataFrame()\n",
    "# norms['target_kpi'] = 0\n",
    "# norms['b_bounce_rate'] = 0\n",
    "# norms['b_conversion_rate'] = 0\n",
    "# norms['b_med_duration'] = 0\n",
    "# norms['d_bounce_rate'] = 0\n",
    "# norms['d_conversion_rate'] = 0\n",
    "# norms['d_med_duration'] = 0\n",
    "for sou in norms.source:\n",
    "#     print(sou)\n",
    "    cot = combs[combs.source == sou]\n",
    "    norm = norms[norms.source == sou]\n",
    "#     print(type(norm))\n",
    "#     print(norm['bounce_rate'])\n",
    "#     print(cot.source)\n",
    "    for i in range(0, len(kpi)):\n",
    "        val = kpi[i]\n",
    "        if val == 'bounce_rate':\n",
    "            cut = cot.sort_values(val, ascending=True).head(1)\n",
    "        else:\n",
    "            cut = cot.sort_values(val, ascending=False).head(1)\n",
    "        cut['target_kpi'] = val\n",
    "        cut['kpi_value'] = cut[val]\n",
    "#         print(val)\n",
    "        cut['kpi_norma'] = norm[val].iloc[0]\n",
    "        cut['kpi_delta'] = (cut[val] - norm[val].iloc[0]) / norm[val].iloc[0] * 100\n",
    "        tops = tops.append(cut)\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "id": "f3d4d399",
   "metadata": {},
   "outputs": [],
   "source": [
    "tops = tops.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "id": "fc3a49f3",
   "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>source</th>\n",
       "      <th>medium</th>\n",
       "      <th>campaign</th>\n",
       "      <th>ipcountry</th>\n",
       "      <th>device_family</th>\n",
       "      <th>length</th>\n",
       "      <th>bounce_rate</th>\n",
       "      <th>conversion_rate</th>\n",
       "      <th>med_duration</th>\n",
       "      <th>target_kpi</th>\n",
       "      <th>kpi_value</th>\n",
       "      <th>kpi_norma</th>\n",
       "      <th>kpi_delta</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>NL</td>\n",
       "      <td>Generic Smartphone</td>\n",
       "      <td>17</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>6.5860</td>\n",
       "      <td>bounce_rate</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.433970</td>\n",
       "      <td>-100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>ID</td>\n",
       "      <td>Mac</td>\n",
       "      <td>10</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>167.0630</td>\n",
       "      <td>conversion_rate</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.019934</td>\n",
       "      <td>401.666667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>(direct)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>(none)</td>\n",
       "      <td>EE</td>\n",
       "      <td>Mac</td>\n",
       "      <td>17</td>\n",
       "      <td>0.176471</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>169.6240</td>\n",
       "      <td>med_duration</td>\n",
       "      <td>169.624000</td>\n",
       "      <td>32.878000</td>\n",
       "      <td>415.919460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>IGShopping</td>\n",
       "      <td>Social</td>\n",
       "      <td>(none)</td>\n",
       "      <td>DK</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>30</td>\n",
       "      <td>0.033333</td>\n",
       "      <td>0.033333</td>\n",
       "      <td>115.9230</td>\n",
       "      <td>bounce_rate</td>\n",
       "      <td>0.033333</td>\n",
       "      <td>0.221910</td>\n",
       "      <td>-84.978903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>IGShopping</td>\n",
       "      <td>Social</td>\n",
       "      <td>(none)</td>\n",
       "      <td>IE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>16</td>\n",
       "      <td>0.375000</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>55.1745</td>\n",
       "      <td>conversion_rate</td>\n",
       "      <td>0.125000</td>\n",
       "      <td>0.033708</td>\n",
       "      <td>270.833333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>IGShopping</td>\n",
       "      <td>Social</td>\n",
       "      <td>(none)</td>\n",
       "      <td>NL</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>13</td>\n",
       "      <td>0.153846</td>\n",
       "      <td>0.076923</td>\n",
       "      <td>195.5880</td>\n",
       "      <td>med_duration</td>\n",
       "      <td>195.588000</td>\n",
       "      <td>82.710500</td>\n",
       "      <td>136.472999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>facebook.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>BE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>22</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>99.1290</td>\n",
       "      <td>bounce_rate</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.166946</td>\n",
       "      <td>-100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>facebook.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>US</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>39</td>\n",
       "      <td>0.179487</td>\n",
       "      <td>0.076923</td>\n",
       "      <td>43.3760</td>\n",
       "      <td>conversion_rate</td>\n",
       "      <td>0.076923</td>\n",
       "      <td>0.011745</td>\n",
       "      <td>554.945055</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>facebook.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>SE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>11</td>\n",
       "      <td>0.090909</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>177.9980</td>\n",
       "      <td>med_duration</td>\n",
       "      <td>177.998000</td>\n",
       "      <td>80.719500</td>\n",
       "      <td>120.514250</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>google.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>DE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>13</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.230769</td>\n",
       "      <td>242.7220</td>\n",
       "      <td>bounce_rate</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.264577</td>\n",
       "      <td>-100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>google.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>DE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>13</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.230769</td>\n",
       "      <td>242.7220</td>\n",
       "      <td>conversion_rate</td>\n",
       "      <td>0.230769</td>\n",
       "      <td>0.031975</td>\n",
       "      <td>621.719457</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>google.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>EE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>16</td>\n",
       "      <td>0.062500</td>\n",
       "      <td>0.062500</td>\n",
       "      <td>325.1780</td>\n",
       "      <td>med_duration</td>\n",
       "      <td>325.178000</td>\n",
       "      <td>105.434000</td>\n",
       "      <td>208.418537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>instagram.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>BE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>209.9335</td>\n",
       "      <td>bounce_rate</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.072816</td>\n",
       "      <td>-100.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>instagram.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>EE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>20</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>116.8250</td>\n",
       "      <td>conversion_rate</td>\n",
       "      <td>0.050000</td>\n",
       "      <td>0.010680</td>\n",
       "      <td>368.181818</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>instagram.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>BE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>30</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>209.9335</td>\n",
       "      <td>med_duration</td>\n",
       "      <td>209.933500</td>\n",
       "      <td>101.033000</td>\n",
       "      <td>107.787060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>iwearundress.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>FR</td>\n",
       "      <td>Mac</td>\n",
       "      <td>13</td>\n",
       "      <td>0.230769</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>8.9430</td>\n",
       "      <td>bounce_rate</td>\n",
       "      <td>0.230769</td>\n",
       "      <td>0.625721</td>\n",
       "      <td>-63.119446</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>iwearundress.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>NL</td>\n",
       "      <td>Mac</td>\n",
       "      <td>11</td>\n",
       "      <td>0.454545</td>\n",
       "      <td>0.090909</td>\n",
       "      <td>19.8420</td>\n",
       "      <td>conversion_rate</td>\n",
       "      <td>0.090909</td>\n",
       "      <td>0.015521</td>\n",
       "      <td>485.714286</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>iwearundress.com</td>\n",
       "      <td>referral</td>\n",
       "      <td>(none)</td>\n",
       "      <td>IE</td>\n",
       "      <td>Other</td>\n",
       "      <td>11</td>\n",
       "      <td>0.818182</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>326.4810</td>\n",
       "      <td>med_duration</td>\n",
       "      <td>326.481000</td>\n",
       "      <td>8.595000</td>\n",
       "      <td>3698.499127</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>meta</td>\n",
       "      <td>Instagram_Feed</td>\n",
       "      <td>TOFU_Winter22_Prospecting_Europe_Excluded-LT</td>\n",
       "      <td>DE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>10</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>51.4945</td>\n",
       "      <td>bounce_rate</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.451766</td>\n",
       "      <td>-77.864630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>meta</td>\n",
       "      <td>Facebook_Stories</td>\n",
       "      <td>MOFU All</td>\n",
       "      <td>NO</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>10</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>45.0735</td>\n",
       "      <td>conversion_rate</td>\n",
       "      <td>0.100000</td>\n",
       "      <td>0.008592</td>\n",
       "      <td>1063.937008</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>meta</td>\n",
       "      <td>Facebook_Mobile_Feed</td>\n",
       "      <td>MOFU All</td>\n",
       "      <td>DE</td>\n",
       "      <td>iPhone</td>\n",
       "      <td>22</td>\n",
       "      <td>0.136364</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>119.8510</td>\n",
       "      <td>med_duration</td>\n",
       "      <td>119.851000</td>\n",
       "      <td>22.103000</td>\n",
       "      <td>442.238610</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              source                medium                                      campaign ipcountry       device_family  length  bounce_rate  conversion_rate  med_duration       target_kpi   kpi_value   kpi_norma    kpi_delta\n",
       "0           (direct)                (none)                                        (none)        NL  Generic Smartphone      17     0.000000         0.000000        6.5860      bounce_rate    0.000000    0.433970  -100.000000\n",
       "1           (direct)                (none)                                        (none)        ID                 Mac      10     0.100000         0.100000      167.0630  conversion_rate    0.100000    0.019934   401.666667\n",
       "2           (direct)                (none)                                        (none)        EE                 Mac      17     0.176471         0.000000      169.6240     med_duration  169.624000   32.878000   415.919460\n",
       "3         IGShopping                Social                                        (none)        DK              iPhone      30     0.033333         0.033333      115.9230      bounce_rate    0.033333    0.221910   -84.978903\n",
       "4         IGShopping                Social                                        (none)        IE              iPhone      16     0.375000         0.125000       55.1745  conversion_rate    0.125000    0.033708   270.833333\n",
       "5         IGShopping                Social                                        (none)        NL              iPhone      13     0.153846         0.076923      195.5880     med_duration  195.588000   82.710500   136.472999\n",
       "6       facebook.com              referral                                        (none)        BE              iPhone      22     0.000000         0.000000       99.1290      bounce_rate    0.000000    0.166946  -100.000000\n",
       "7       facebook.com              referral                                        (none)        US              iPhone      39     0.179487         0.076923       43.3760  conversion_rate    0.076923    0.011745   554.945055\n",
       "8       facebook.com              referral                                        (none)        SE              iPhone      11     0.090909         0.000000      177.9980     med_duration  177.998000   80.719500   120.514250\n",
       "9         google.com              referral                                        (none)        DE              iPhone      13     0.000000         0.230769      242.7220      bounce_rate    0.000000    0.264577  -100.000000\n",
       "10        google.com              referral                                        (none)        DE              iPhone      13     0.000000         0.230769      242.7220  conversion_rate    0.230769    0.031975   621.719457\n",
       "11        google.com              referral                                        (none)        EE              iPhone      16     0.062500         0.062500      325.1780     med_duration  325.178000  105.434000   208.418537\n",
       "12     instagram.com              referral                                        (none)        BE              iPhone      30     0.000000         0.000000      209.9335      bounce_rate    0.000000    0.072816  -100.000000\n",
       "13     instagram.com              referral                                        (none)        EE              iPhone      20     0.000000         0.050000      116.8250  conversion_rate    0.050000    0.010680   368.181818\n",
       "14     instagram.com              referral                                        (none)        BE              iPhone      30     0.000000         0.000000      209.9335     med_duration  209.933500  101.033000   107.787060\n",
       "15  iwearundress.com              referral                                        (none)        FR                 Mac      13     0.230769         0.000000        8.9430      bounce_rate    0.230769    0.625721   -63.119446\n",
       "16  iwearundress.com              referral                                        (none)        NL                 Mac      11     0.454545         0.090909       19.8420  conversion_rate    0.090909    0.015521   485.714286\n",
       "17  iwearundress.com              referral                                        (none)        IE               Other      11     0.818182         0.000000      326.4810     med_duration  326.481000    8.595000  3698.499127\n",
       "18              meta        Instagram_Feed  TOFU_Winter22_Prospecting_Europe_Excluded-LT        DE              iPhone      10     0.100000         0.000000       51.4945      bounce_rate    0.100000    0.451766   -77.864630\n",
       "19              meta      Facebook_Stories                                      MOFU All        NO              iPhone      10     0.500000         0.100000       45.0735  conversion_rate    0.100000    0.008592  1063.937008\n",
       "20              meta  Facebook_Mobile_Feed                                      MOFU All        DE              iPhone      22     0.136364         0.000000      119.8510     med_duration  119.851000   22.103000   442.238610"
      ]
     },
     "execution_count": 91,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tops"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "21e42e5a",
   "metadata": {},
   "outputs": [],
   "source": [
    "tops.to_csv('tops_kpi.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}