ChurnCalculation.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Read the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "# Veriyi yükle\n",
    "df = pd.read_excel(\"kartData1.xlsx\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "df[\"trxdate\"] = pd.to_datetime(df[\"trxdate\"], format =\"%d/%m/%Y\")\n",
    "\n",
    "df[\"trx_month\"] = df[\"trxdate\"].dt.to_period('M')\n",
    "start_period = pd.Period(\"2023-07\")\n",
    "end_period = pd.Period(\"2024-04\")\n",
    "\n",
    "df_selected_period = df[(df[\"trx_month\"] >= start_period) & (df[\"trx_month\"] <= end_period)]\n",
    "\n",
    "df_selected_period[\"TotalAmountLC\"] = pd.to_numeric(df_selected_period[\"TotalAmountLC\"], errors='coerce')\n",
    "\n",
    "eight_months_TotalAmountLC = df_selected_period.groupby([\"Customer_id\"])[\"TotalAmountLC\"].sum().reset_index()\n",
    "\n",
    "sorted_eight_months_TotalAmountLC = eight_months_TotalAmountLC[\"TotalAmountLC\"].sort_values()\n",
    "\n",
    "customers = range(1, len(sorted_eight_months_TotalAmountLC) + 1)\n",
    "eigtht_months_TotalAmountLC_values = sorted_eight_months_TotalAmountLC.values\n",
    "\n",
    "total_TotalAmountLC = eight_months_TotalAmountLC[\"TotalAmountLC\"].sum()\n",
    "\n",
    "percentile_80 = sorted_eight_months_TotalAmountLC.quantile(0.8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "monthly_TotalAmountLC_per_customer = df_selected_period.groupby([\"Customer_id\", \"trx_month\"])[\"TotalAmountLC\"].sum().reset_index()\n",
    "\n",
    "all_customers = df_selected_period[\"Customer_id\"].unique()\n",
    "all_months = pd.period_range(start=start_period, end=end_period, freq='M')\n",
    "\n",
    "customer_month_combinations = pd.MultiIndex.from_product([all_customers, all_months], names=['Customer_id', 'trx_month'])\n",
    "full_customer_month_TotalAmountLC = pd.DataFrame(index=customer_month_combinations).reset_index()\n",
    "\n",
    "full_customer_month_TotalAmountLC = pd.merge(full_customer_month_TotalAmountLC, monthly_TotalAmountLC_per_customer, on=[\"Customer_id\", \"trx_month\"], how=\"left\")\n",
    "\n",
    "# Filling the TotalAmountLC values with 0 when the customer had no record for that month\n",
    "full_customer_month_TotalAmountLC[\"TotalAmountLC\"].fillna(0, inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted_TotalAmountLC = df_selected_period.groupby(\"Customer_id\")[\"TotalAmountLC\"].sum().sort_values()\n",
    "\n",
    "customers = range(1, len(sorted_TotalAmountLC) + 1)\n",
    "monthly_TotalAmountLC_values = sorted_TotalAmountLC.values"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Calculation of the slope between months i-1 and i-2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "slopes = []\n",
    "for i in range(len(sorted_eight_months_TotalAmountLC) - 1):\n",
    "    slope = (sorted_eight_months_TotalAmountLC.iloc[i-1] - sorted_eight_months_TotalAmountLC.iloc[i-2])\n",
    "    slopes.append(slope)\n",
    "\n",
    "print(slopes)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finding the TotalAmountLC values for a specific customer, used for checking if everything is alright\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Müşteri ID: 1108\n"
     ]
    }
   ],
   "source": [
    "customer_id = 1108\n",
    "\n",
    "customer_monthly_TotalAmountLC = full_customer_month_TotalAmountLC[full_customer_month_TotalAmountLC[\"Customer_id\"] == customer_id]\n",
    "\n",
    "print(\"Müşteri ID:\", customer_id)\n",
    "for index, row in customer_monthly_TotalAmountLC.iterrows():\n",
    "    print(\"Aylık TotalAmountLC ({}) : {:.2f} TL\".format(row[\"trx_month\"], row[\"TotalAmountLC\"]))\n",
    "\n",
    "\n",
    "months = customer_monthly_TotalAmountLC[\"trx_month\"]\n",
    "TotalAmountLC_values = customer_monthly_TotalAmountLC[\"TotalAmountLC\"]\n",
    "\n",
    "\n",
    "months = months.dt.to_timestamp()\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "plt.plot(months, TotalAmountLC_values, marker='o', color='blue')\n",
    "plt.title('Customer Expenditure Trend'.format(customer_id))\n",
    "plt.xlabel('Month')\n",
    "plt.ylabel('Amount of expenditure')\n",
    "plt.grid(True)\n",
    "plt.show()\n",
    "print(customer_monthly_TotalAmountLC)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Remove Outlier from the Data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Müşteri ID: 1108\n"
     ]
    }
   ],
   "source": [
    "mean_TotalAmountLC = customer_monthly_TotalAmountLC[\"TotalAmountLC\"].mean()\n",
    "std_TotalAmountLC = customer_monthly_TotalAmountLC[\"TotalAmountLC\"].std()\n",
    "\n",
    "threshold = 2 \n",
    "\n",
    "outliers = customer_monthly_TotalAmountLC[\n",
    "    (customer_monthly_TotalAmountLC[\"TotalAmountLC\"] < mean_TotalAmountLC - threshold * std_TotalAmountLC) |\n",
    "    (customer_monthly_TotalAmountLC[\"TotalAmountLC\"] > mean_TotalAmountLC + threshold * std_TotalAmountLC)\n",
    "]\n",
    "\n",
    "non_outlier_data = customer_monthly_TotalAmountLC[~customer_monthly_TotalAmountLC.index.isin(outliers.index)]\n",
    "non_outlier_mean = non_outlier_data[\"TotalAmountLC\"].mean()\n",
    "\n",
    "customer_monthly_TotalAmountLC.loc[outliers.index, \"TotalAmountLC\"] = non_outlier_mean\n",
    "\n",
    "print(\"Müşteri ID:\", customer_id)\n",
    "for index, row in customer_monthly_TotalAmountLC.iterrows():\n",
    "    print(\"Aylık TotalAmountLC ({}) : {:.2f} TL\".format(row[\"trx_month\"], row[\"TotalAmountLC\"]))\n",
    "\n",
    "months_filtered = customer_monthly_TotalAmountLC[\"trx_month\"]\n",
    "TotalAmountLC_values_filtered = customer_monthly_TotalAmountLC[\"TotalAmountLC\"]\n",
    "months_filtered = months_filtered.dt.to_timestamp()\n",
    "\n",
    "plt.figure(figsize=(10, 6))\n",
    "plt.plot(months_filtered, TotalAmountLC_values_filtered, marker='o', color='green')\n",
    "plt.title('The Expenditure Trend of the Customer {} After Replacing Outliers with Non-Outlier Mean'.format(customer_id))\n",
    "plt.xlabel('Month')\n",
    "plt.ylabel('Amount of Expenditure')\n",
    "plt.grid(True)\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Remove the Outliers from All Customers"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Customer_id trx_month  TotalAmountLC\n",
      "643720     16082019   2023-07         119.99\n",
      "643721     16082019   2023-08           0.00\n",
      "643722     16082019   2023-09           0.00\n",
      "643723     16082019   2023-10           0.00\n",
      "643724     16082019   2023-11           0.00\n",
      "...             ...       ...            ...\n",
      "5          97425780   2023-12         710.00\n",
      "6          97425780   2024-01         119.99\n",
      "7          97425780   2024-02         468.00\n",
      "8          97425780   2024-03         340.00\n",
      "9          97425780   2024-04           0.00\n",
      "\n",
      "[643730 rows x 3 columns]\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "outlierRemoved_data = []\n",
    "\n",
    "for customer_id, group in full_customer_month_TotalAmountLC.groupby('Customer_id'):\n",
    "    mean_TotalAmountLC = group[\"TotalAmountLC\"].mean()\n",
    "    \n",
    "    mean_TotalAmountLC = group[\"TotalAmountLC\"].mean()\n",
    "    std_TotalAmountLC = group[\"TotalAmountLC\"].std()\n",
    "   \n",
    "    outliers = group[(group[\"TotalAmountLC\"] < mean_TotalAmountLC - threshold * std_TotalAmountLC) |\n",
    "                     (group[\"TotalAmountLC\"] > mean_TotalAmountLC + threshold * std_TotalAmountLC)]\n",
    "    \n",
    "    # Change the outlier values with the average of the data points\n",
    "    group.loc[outliers.index, \"TotalAmountLC\"] = mean_TotalAmountLC\n",
    "    \n",
    "    outlierRemoved_data.append(group)\n",
    "\n",
    "outlierRemoved_customer_monthly_TotalAmountLC = pd.concat(outlierRemoved_data)\n",
    "\n",
    "print(outlierRemoved_customer_monthly_TotalAmountLC)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Calculate the Average Negative Slope"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculate_slope(TotalAmountLC_values):\n",
    "    slopes_percent = []\n",
    "    for i in range(1, len(TotalAmountLC_values)):\n",
    "        slope_percent = ((TotalAmountLC_values[i-2] - TotalAmountLC_values[i-1]) / TotalAmountLC_values[i-2])  if TotalAmountLC_values[i-2] != 0 else 0\n",
    "        slopes_percent.append(slope_percent)\n",
    "    return slopes_percent\n",
    "\n",
    "customer_monthly_slopes_percent = {}\n",
    "\n",
    "for customer_id, month_TotalAmountLC in outlierRemoved_customer_monthly_TotalAmountLC.groupby(\"Customer_id\"):\n",
    "    TotalAmountLC_values_outlierRemoved = month_TotalAmountLC[\"TotalAmountLC\"].values\n",
    "    slopes_percent = calculate_slope(TotalAmountLC_values_outlierRemoved)\n",
    "    for month, slope_percent in zip(month_TotalAmountLC[\"trx_month\"], slopes_percent):\n",
    "        key = (customer_id, month)\n",
    "        customer_monthly_slopes_percent[key] = slope_percent\n",
    "\n",
    "sorted_customer_monthly_slopes_percent = sorted(customer_monthly_slopes_percent.items(), key=lambda x: x[0][0])\n",
    "\n",
    "for key, slope_percent in sorted_customer_monthly_slopes_percent:\n",
    "    customer_id, month = key\n",
    "    print(\"Customer ID:\", customer_id, \"Month:\", month+1, \"Slope (%):\", slope_percent)\n",
    "\n",
    "negative_slopes = [slope_percent for slope_percent in customer_monthly_slopes_percent.values() if slope_percent < 0]\n",
    "\n",
    "average_negative_slope = np.mean(negative_slopes)\n",
    "\n",
    "print(\"Average Negative Slope: {:.2f}%\".format(average_negative_slope))\n",
    "slope_2023_07 = customer_monthly_slopes_percent.get((customer_id, \"2023-07\"), None)\n",
    "print(slope_2023_07)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Saving the Slope Values to a CSV File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Customer_id  trxdate    Slope\n",
      "0    16082019  2023-08  0.63506\n",
      "1    16082019  2023-09  1.00000\n",
      "2    16082019  2023-10  0.00000\n",
      "3    16082019  2023-11  0.00000\n",
      "4    16082019  2023-12  0.00000\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "slope_df = pd.DataFrame(columns=[\"Customer_id\", \"trxdate\", \"Slope\"])\n",
    "\n",
    "for (customer_id, month), slope_percent in customer_monthly_slopes_percent.items():\n",
    "    slope_df = pd.concat([slope_df, pd.DataFrame({\"Customer_id\": [customer_id], \"trxdate\": [month + 1], \"Slope\": [slope_percent]})], ignore_index=True)\n",
    "\n",
    "slope_df[\"trxdate\"] = slope_df[\"trxdate\"].dt.to_timestamp()\n",
    "\n",
    "slope_df[\"trxdate\"] = slope_df[\"trxdate\"].dt.strftime('%Y-%m')\n",
    "\n",
    "\n",
    "print(slope_df.head())\n",
    "slope_df.to_csv(\"slope.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Saving the Moving Average Values to a CSV File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "MA_df = pd.DataFrame(columns=[\"Customer_id\", \"trxdate\", \"Moving_Average\"])\n",
    "\n",
    "for Customer_id, group in outlierRemoved_customer_monthly_TotalAmountLC.groupby(\"Customer_id\"):\n",
    "    TotalAmountLC_values = group[\"TotalAmountLC\"].values\n",
    "    slopes_percent = calculate_slope(TotalAmountLC_values)\n",
    "    for i, (month, TotalAmountLC, slope_percent) in enumerate(zip(group[\"trx_month\"]+1, TotalAmountLC_values, slopes_percent)):\n",
    "        moving_average = group[\"TotalAmountLC\"].iloc[:i].mean()\n",
    "        moving_average = 0 if pd.isnull(moving_average) else moving_average\n",
    "        MA_df = pd.concat([MA_df, pd.DataFrame({\"Customer_id\": [Customer_id], \"trxdate\": [month], \"Moving_Average\": [moving_average]})], ignore_index=True)\n",
    "print(MA_df.head())\n",
    "MA_df.to_csv(\"MA.csv\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Calculating Churn for Every Row & Saving the Churn Values to a CSV File"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "        Customer_id  trxdate  Churn\n",
      "0          16082019  2023-07      0\n",
      "1          16082019  2023-08      0\n",
      "2          16082019  2023-09      1\n",
      "3          16082019  2023-10      1\n",
      "4          16082019  2023-11      1\n",
      "...             ...      ...    ...\n",
      "643725     97425780  2023-12      1\n",
      "643726     97425780  2024-01      0\n",
      "643727     97425780  2024-02      0\n",
      "643728     97425780  2024-03      0\n",
      "643729     97425780  2024-04      0\n",
      "\n",
      "[643730 rows x 3 columns]\n",
      "Toplam Ay Sayısı: 643730\n",
      "Churn Olma Sayısı: 163957\n",
      "Churn Gerçekleşme Oranı: 25.47%\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "churn_data = []\n",
    "\n",
    "for customer_id, group in outlierRemoved_customer_monthly_TotalAmountLC.groupby(\"Customer_id\"):\n",
    "    TotalAmountLC_values = group[\"TotalAmountLC\"].values\n",
    "    slopes_percent = calculate_slope(TotalAmountLC_values)  \n",
    "\n",
    "    churn_data.append((customer_id, \"2023-07\", 0))\n",
    "\n",
    "    for i, (month, TotalAmountLC, slope_percent) in enumerate(zip(group[\"trx_month\"]+1, TotalAmountLC_values, slopes_percent)):\n",
    "        if month != \"2023-07\":\n",
    "            if slope_percent < 0 and TotalAmountLC < group[\"TotalAmountLC\"].iloc[:i].mean():\n",
    "                churn_data.append((customer_id, month, 1))  # Churn\n",
    "            elif TotalAmountLC == 0:  # ELabel Customer as Churn if She made no transaction in that month\n",
    "                churn_data.append((customer_id, month, 1))  # Churn\n",
    "            else:\n",
    "                churn_data.append((customer_id, month, 0))  # Nonchurn\n",
    "\n",
    "churn_df = pd.DataFrame(churn_data, columns=[\"Customer_id\", \"trxdate\", \"Churn\"])\n",
    "churn_df.to_csv(\"churn_analysis.csv\", index=False)\n",
    "\n",
    "churn_count = churn_df[\"Churn\"].sum()\n",
    "\n",
    "total_months = len(churn_df)\n",
    "\n",
    "churn_rate = churn_count / total_months\n",
    "\n",
    "print(churn_df)\n",
    "print(\"Toplam Ay Sayısı:\", total_months)\n",
    "print(\"Churn Olma Sayısı:\", churn_count)\n",
    "print(\"Churn Gerçekleşme Oranı: {:.2%}\".format(churn_rate))\n"
   ]
  }
 ],
 "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.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}