funnel_python.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1m/hvnyjbcx2rxfb9d__rb_m7h00000gp/T/ipykernel_12644/3059708972.py:11: DtypeWarning: Columns (4,7,8,9) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  ride_requests_df = pd.read_csv('metrocar_data/ride_requests.csv')\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Assuming you have the relevant data available as pandas DataFrames or CSV files,\n",
    "# you can start by creating the required DataFrames for each step in the funnel.\n",
    "\n",
    "\n",
    "downloads_df = pd.read_csv('metrocar_data/app_downloads.csv')\n",
    "\n",
    "signups_df = pd.read_csv('metrocar_data/signups.csv')\n",
    "\n",
    "ride_requests_df = pd.read_csv('metrocar_data/ride_requests.csv')\n",
    "\n",
    "accepted_requests_df = ride_requests_df[ride_requests_df['accept_ts'].notnull()]\n",
    "\n",
    "completed_rides_df = accepted_requests_df[accepted_requests_df['cancel_ts'].isnull()]\n",
    "\n",
    "reviews_df = pd.read_csv('metrocar_data/reviews.csv')\n",
    "\n",
    "transactions_df = pd.read_csv('metrocar_data/transactions.csv')\n",
    "\n",
    "pre_ride_df = downloads_df.merge(signups_df, left_on='app_download_key', right_on='session_id', how='inner')\n",
    "\n",
    "filtered_transactions_df = transactions_df[transactions_df['charge_status'] == 'Approved']\n",
    "\n",
    "payment_df = ride_requests_df.merge(filtered_transactions_df, on='ride_id', how='inner')\n",
    "\n",
    "#payment_df = payment_df[payment_df['charge_status'] == 'Approved']\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>user_id</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>100002</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>100004</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>100007</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>100008</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>100010</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6228</th>\n",
       "      <td>117614</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6229</th>\n",
       "      <td>117615</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6230</th>\n",
       "      <td>117617</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6231</th>\n",
       "      <td>117621</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6232</th>\n",
       "      <td>117622</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>6233 rows × 2 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      user_id  count\n",
       "0      100002      1\n",
       "1      100004      1\n",
       "2      100007      1\n",
       "3      100008      1\n",
       "4      100010      1\n",
       "...       ...    ...\n",
       "6228   117614      1\n",
       "6229   117615      1\n",
       "6230   117617      1\n",
       "6231   117621      1\n",
       "6232   117622      1\n",
       "\n",
       "[6233 rows x 2 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "payment_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>transaction_id</th>\n",
       "      <th>ride_id</th>\n",
       "      <th>purchase_amount_usd</th>\n",
       "      <th>charge_status</th>\n",
       "      <th>transaction_ts</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10000000</td>\n",
       "      <td>3000000</td>\n",
       "      <td>13.55</td>\n",
       "      <td>Approved</td>\n",
       "      <td>2021-03-28 19:11:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10000001</td>\n",
       "      <td>3000001</td>\n",
       "      <td>27.77</td>\n",
       "      <td>Approved</td>\n",
       "      <td>2021-11-10 16:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10000002</td>\n",
       "      <td>3000002</td>\n",
       "      <td>21.84</td>\n",
       "      <td>Approved</td>\n",
       "      <td>2021-09-08 21:03:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10000003</td>\n",
       "      <td>3000004</td>\n",
       "      <td>26.86</td>\n",
       "      <td>Approved</td>\n",
       "      <td>2021-05-28 09:21:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10000004</td>\n",
       "      <td>3000005</td>\n",
       "      <td>21.72</td>\n",
       "      <td>Approved</td>\n",
       "      <td>2021-12-01 16:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223646</th>\n",
       "      <td>10223646</td>\n",
       "      <td>3385468</td>\n",
       "      <td>27.23</td>\n",
       "      <td>Approved</td>\n",
       "      <td>2022-01-30 09:21:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223647</th>\n",
       "      <td>10223647</td>\n",
       "      <td>3385469</td>\n",
       "      <td>25.06</td>\n",
       "      <td>Approved</td>\n",
       "      <td>2021-11-12 17:57:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223648</th>\n",
       "      <td>10223648</td>\n",
       "      <td>3385472</td>\n",
       "      <td>23.29</td>\n",
       "      <td>Approved</td>\n",
       "      <td>2021-10-31 18:24:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223649</th>\n",
       "      <td>10223649</td>\n",
       "      <td>3385473</td>\n",
       "      <td>18.88</td>\n",
       "      <td>Approved</td>\n",
       "      <td>2021-11-05 10:42:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>223651</th>\n",
       "      <td>10223651</td>\n",
       "      <td>3385475</td>\n",
       "      <td>19.37</td>\n",
       "      <td>Approved</td>\n",
       "      <td>2022-02-24 10:44:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>212628 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        transaction_id  ride_id  purchase_amount_usd charge_status  \\\n",
       "0             10000000  3000000                13.55      Approved   \n",
       "1             10000001  3000001                27.77      Approved   \n",
       "2             10000002  3000002                21.84      Approved   \n",
       "3             10000003  3000004                26.86      Approved   \n",
       "4             10000004  3000005                21.72      Approved   \n",
       "...                ...      ...                  ...           ...   \n",
       "223646        10223646  3385468                27.23      Approved   \n",
       "223647        10223647  3385469                25.06      Approved   \n",
       "223648        10223648  3385472                23.29      Approved   \n",
       "223649        10223649  3385473                18.88      Approved   \n",
       "223651        10223651  3385475                19.37      Approved   \n",
       "\n",
       "             transaction_ts  \n",
       "0       2021-03-28 19:11:00  \n",
       "1       2021-11-10 16:59:00  \n",
       "2       2021-09-08 21:03:00  \n",
       "3       2021-05-28 09:21:00  \n",
       "4       2021-12-01 16:59:00  \n",
       "...                     ...  \n",
       "223646  2022-01-30 09:21:00  \n",
       "223647  2021-11-12 17:57:00  \n",
       "223648  2021-10-31 18:24:00  \n",
       "223649  2021-11-05 10:42:00  \n",
       "223651  2022-02-24 10:44:00  \n",
       "\n",
       "[212628 rows x 5 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions_df[transactions_df['charge_status'] == 'Approved']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                Step  Count  Previous Count  Conversion   Drop-off\n",
      "0       App Download  23608             NaN         NaN        NaN\n",
      "1             Signup  17623         23608.0   74.648424  25.351576\n",
      "2       Request Ride  12406         17623.0   70.396641  29.603359\n",
      "3  Driver Acceptance  12278         12406.0   98.968241   1.031759\n",
      "4     Completed Ride   6233         12278.0   50.765597  49.234403\n",
      "5  Completed Payment   6233          6233.0  100.000000   0.000000\n",
      "6   Completed Review   4348          6233.0   69.757741  30.242259\n"
     ]
    }
   ],
   "source": [
    "# Perform funnel analysis\n",
    "\n",
    "steps = [\n",
    "    ('App Download', downloads_df, 'app_download_key'),\n",
    "    ('Signup', signups_df, 'user_id'),\n",
    "    ('Request Ride', ride_requests_df, 'user_id'),\n",
    "    ('Driver Acceptance', accepted_requests_df, 'user_id'),\n",
    "    ('Completed Ride', completed_rides_df, 'user_id'),\n",
    "    ('Completed Payment', payment_df, 'user_id'),\n",
    "    ('Completed Review', reviews_df, 'user_id')\n",
    "]\n",
    "\n",
    "funnel_data = []\n",
    "previous_count = None\n",
    "\n",
    "for step, df, id_column in steps:\n",
    "    count = df[id_column].nunique() if id_column else len(df)\n",
    "    conversion = count / previous_count * 100 if previous_count is not None else None\n",
    "    drop_off = 100 - conversion if conversion is not None else None\n",
    "    \n",
    "    funnel_data.append({\n",
    "        'Step': step,\n",
    "        'Count': count,\n",
    "        'Previous Count': previous_count,\n",
    "        'Conversion': conversion,\n",
    "        'Drop-off': drop_off\n",
    "    })\n",
    "    \n",
    "    previous_count = count\n",
    "\n",
    "# Convert the funnel data to a pandas DataFrame for further analysis or visualization\n",
    "funnel_df = pd.DataFrame(funnel_data)\n",
    "\n",
    "# Display the funnel DataFrame\n",
    "print(funnel_df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/1m/hvnyjbcx2rxfb9d__rb_m7h00000gp/T/ipykernel_12445/3467492101.py:3: DtypeWarning: Columns (4,7,8,9) have mixed types. Specify dtype option on import or set low_memory=False.\n",
      "  ride_requests = pd.read_csv('metrocar_data/ride_requests.csv')\n"
     ]
    }
   ],
   "source": [
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "metro",
   "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.11.3"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}