Script_DS_Project1_V1.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "Script_DS_Project1_V1.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyPLKIG9QL9xiZopVqYH3UP1",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/poongodimsa/DataScience-projects-for-vidiq/blob/main/Script_DS_Project1_V1.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "# Project 1 -  Revenue by customer by month - V 1"
      ],
      "metadata": {
        "id": "NwKkzYgAyZBF"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Version 1 - Specifications - For Paypal only"
      ],
      "metadata": {
        "id": "NlXDBvN74zP1"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "####  Final result:\n",
        "\n",
        "1. Get EUR customer email addresses to USD amounts from EUR amounts\n",
        "\n",
        "2. Remove /ignore all EUR amounts from the calculation. If you add all EUR (net) together. This will give you zero value\n",
        "\n",
        "3. Ignore any amounts under \"payments@vid.io\" - Anway there will be no amounts for this email if you do the above two steps\n",
        "\n",
        "\n",
        "#### Description:\n",
        "\n",
        "Some of our customers pay in EUR. Since our default currency is USD, Paypal immediately converts these EUR payments to USD\n",
        "\n",
        "The Problem we have; See the below screenshot taken from the 2022.02 Payroll data file. (A:5 to A:7)\n",
        "\n",
        "Patrick is a EU customer. he paid us in EU currency. EUR 10.00\n",
        "Paypal immediately converted to USD 9.84\n",
        "You can also see that when you add both EUR amounts it will net set off ( Zero value)\n",
        "So in these multi-currency cases, we need to get the USD values only. But the problem; there is no email address as the unique ID assigned to USD amounts. It is blank\n",
        "\n",
        "The workaround here is \n",
        "In the above case, the Customer's email is knopey@gmx.de. But this is assigned to the EUR amount instead of the USD amount.\n",
        "\n",
        "Transaction ID - 5RE84019JL6683131 This is the reference for the EUR amount. But we have a different reference for the USD amount. So we cannot use that get the customer email\n",
        "\n",
        "Reference Txn ID - 5RE84019JL6683131 - Check the reference under \"Reference Txn ID\" for the USD amount now compare this with the above transaction ID. You will see there is a match\n",
        "In Excel, we can use the \"VlookUp\" formula to get the customer email to USD amount using these two different IDs\n",
        "Now please find a way to get the correct email ID to USD amount"
      ],
      "metadata": {
        "id": "jvEgfhlL5I9D"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Version 0 - Specifications\n",
        "---"
      ],
      "metadata": {
        "id": "25_-Npp436K2"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "\n",
        "\n",
        "Headers - Months in format “YYYY-MM”\n",
        "\n",
        "Row Indexes - \n",
        "1.\tCustomer Name\n",
        "2.\tPlatform (Stripe or PayPal)\n",
        "3.\tCustomer Unique ID\n",
        "4.\tCustomer Country (if available)\n",
        "5.\tCustomer Plan (Pro or Boost; if available)\n",
        "6.\tCustomer Plan Duration (Monthly or Yearly, if available)\n",
        "\n",
        "Rows - Unique customers\n",
        "\n",
        "Values - sum of total payments (received and refunded) per month for each customer\n",
        "\n",
        "1.\tStripe - \n",
        "\n",
        "○\tuse \"customer_id\" as the unique ID to get the data\n",
        "2.\tPayPal\n",
        "\n",
        "○\tImportant -----> Consider Only \"Subscription Payment\" & \"Payment Refund\" types (\"E\" column in the PayPal statements). Remove all the other types\n",
        "\n",
        "○\tUse \"From Email Address\" as the unique ID to get the data ( There is no special code for customer) \n",
        "\n",
        "3.\tIgnore ChartMogul (“CM”) for now. This will be used in the second iteration.\n",
        "\n",
        "Output\n",
        "\n",
        "●\tshould be an excel file with one tab of revenue per customer per month\n",
        "\n",
        "●\tSort by start month\n",
        "\n",
        "\n",
        "Stripe\n",
        "\n",
        "Customer Plan - You can map to \"statement_descriptor\" ( AL Column)\n",
        "\n",
        "Customer Plan Duration - N/A - You Can keep this blank\n",
        "\n",
        "Sum of total payments per customer ------> Always use Gross Amounts (G Column)\n",
        "\n",
        "Paypal\n",
        "\n",
        "Customer Unique ID - map to 'From Email Address? - \"YES\"\n",
        "\n",
        "Customer Plan - You Can map the \"Subject\" /  \"AD\" Column \n",
        "\n",
        "Customer Plan Duration - N/A - You Can keep this blank\n",
        "\n",
        "Amounts -----> Always use Gross Amounts / \"H\" Column\n",
        "\n"
      ],
      "metadata": {
        "id": "JuFhgd5K1TpD"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Script\n",
        "---"
      ],
      "metadata": {
        "id": "FHvKpNaw3tjR"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "import pandas as pd\n",
        "import os\n",
        "from datetime import datetime\n",
        "from dateutil.relativedelta import *\n"
      ],
      "metadata": {
        "id": "SanXBAdhNeuc"
      },
      "execution_count": 1,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Mount Gdrive"
      ],
      "metadata": {
        "id": "vL4YBOciZNdO"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Mount GDrive\n",
        "from google.colab import drive\n",
        "drive.mount('/content/gdrive')\n",
        "path = \"/content/gdrive/My Drive/Data Science 2022/\""
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "LaAgaFqFStup",
        "outputId": "e8d2d447-5c7c-46be-99c5-65a13293267c"
      },
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Mounted at /content/gdrive\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "def get_month_headers(startmonth,monthcount):\n",
        "  '''\n",
        "  Function: Returns list of dates starting from 'startmonth' incrementing till 'monthcount' counts\n",
        "  startdate: date in format 'YYYY-MM' as string datatype\n",
        "  monthcount: Number of increments\n",
        "  '''\n",
        "\n",
        "  lstout = [] #Intialize\n",
        "\n",
        "  start = datetime.strptime(startmonth,'%Y-%m') \n",
        "  for c in range(monthcount):\n",
        "    lstout.append((start + relativedelta(months=+c)).strftime('%Y-%m'))\n",
        "    \n",
        "  return(lstout)\n",
        "\n",
        "def gen_header():\n",
        "  lstheader = [ \"Customer Name\",\"Platform\",\"Customer Unique ID\", \"Customer Country\", \"Customer Plan\", \"Customer Plan Duration\" ]\n",
        "  lstmonths = get_month_headers('2020-01', 27)\n",
        "  return(lstheader + lstmonths)\n"
      ],
      "metadata": {
        "id": "7-Qv9DxGfSsx"
      },
      "execution_count": 3,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### Stripe"
      ],
      "metadata": {
        "id": "esTpLsjFNRUe"
      }
    },
    {
      "cell_type": "markdown",
      "source": [
        "Loop through the input files to calculate revenue per customer"
      ],
      "metadata": {
        "id": "MNY4J0FsZWx_"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Set file paths\n",
        "inpath = path + \"Stripe/\"\n",
        "outpath = path + \"Results/\"\n",
        "\n",
        "c=0\n",
        "for file in os.listdir(inpath):\n",
        "  c+=1\n",
        "  print(file)\n",
        "  df = pd.read_csv(inpath + file, index_col=False)\n",
        "\n",
        "  #Change the data type of gross to numeric\n",
        "  df = df.astype({\"gross\": str})\n",
        "  df['gross'] = df['gross'].str.replace(',','')\n",
        "  df = df.astype({\"gross\": float})\n",
        "\n",
        "\n",
        "  #*********** Calculate revenue per customer per month ******************\n",
        "  #Apply Group by to input data\n",
        "  sum_df = df.groupby(['customer_id' ], as_index=False).agg({'customer_name':'first', 'customer_id':'first',\n",
        "                                                            'shipping_address_country':'first', 'statement_descriptor':'first','gross': 'sum'}) #grouped by dataframe'\n",
        "  sum_df = sum_df.sort_values('customer_id')\n",
        "  \n",
        "  #Form revenue headers\n",
        "  month = '20' + file[7:12].replace('.','-') #From input file name\n",
        "  sum_df.rename(columns={'customer_name':'Customer Name', 'customer_id':'Customer Unique ID', \n",
        "                        'shipping_address_country': \"Customer Country\",'statement_descriptor':'Customer Plan', 'gross': month},inplace = True)\n",
        "  \n",
        "  \n",
        "  #Create base dataframe from first file\n",
        "  if c==1:\n",
        "    final_df=sum_df.copy()\n",
        "  else:\n",
        " \n",
        "    #Merge using full outer join\n",
        "    final_df = final_df.merge(sum_df, how='outer', on = 'Customer Unique ID')\n",
        "\n",
        "    #Drop columns with '_x\n",
        "    cols = [col for col in final_df.columns if col.lower()[-2:] != '_x']\n",
        "    final_df=final_df[cols]\n",
        "\n",
        "    #Remove '_y' from column name\n",
        "    for col in final_df.columns:\n",
        "      if col.lower()[-2:] == '_y':\n",
        "        final_df.rename(columns = {col: col[:-2]}, inplace=True)\n",
        "\n",
        "#insert 'Platform' and 'customer plan duration' cols\n",
        "final_df.insert(1,\"Platform\", \"Stripe\")\n",
        "final_df.insert(1,'Customer Plan Duration',\"\")\n",
        "\n",
        "\n",
        "#Reorder the headers\n",
        "lsthead = gen_header()\n",
        "final_df = final_df.reindex(columns=lsthead)\n",
        "\n",
        "#Sort by first month\n",
        "final_df = final_df.sort_values('2020-01')\n",
        "\n",
        "#Export data to excel without index\n",
        "final_df.to_excel(outpath + 'Stripe_RevenueByCustomerByMonth.xlsx',index = False)\n",
        "\n",
        "print('done')"
      ],
      "metadata": {
        "id": "g1lSZmt2AGeN",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "938d3bf8-2562-46b8-8d65-930a2393c172"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_20.01.csv\n",
            "stripe_20.02.csv\n",
            "stripe_20.03.csv\n",
            "stripe_20.04.csv\n",
            "stripe_20.05.csv\n",
            "stripe_20.06.csv\n",
            "stripe_20.07.csv\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2882: DtypeWarning: Columns (19,20,38,42,43,45,47,49,50,51,52) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_20.08.csv\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2882: DtypeWarning: Columns (20,43,47,52) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_20.09.csv\n",
            "stripe_20.10.csv\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2882: DtypeWarning: Columns (20,43,47) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_20.11.csv\n",
            "stripe_20.12.csv\n",
            "stripe_21.01.csv\n",
            "stripe_21.02.csv\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2882: DtypeWarning: Columns (19,43,47) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_21.03.csv\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2882: DtypeWarning: Columns (20) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_21.08.csv\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2882: DtypeWarning: Columns (19) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_21.07.csv\n",
            "stripe_21.06.csv\n",
            "stripe_21.05.csv\n",
            "stripe_21.04.csv\n",
            "stripe_21.09.csv\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/pandas/core/frame.py:5047: SettingWithCopyWarning: \n",
            "A value is trying to be set on a copy of a slice from a DataFrame\n",
            "\n",
            "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
            "  errors=errors,\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_21.10.csv\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2882: DtypeWarning: Columns (19,44,47,50,52,57) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_21.11.csv\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2882: DtypeWarning: Columns (39,41,42) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_21.12.csv\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py:2882: DtypeWarning: Columns (44,47,50,52,57) have mixed types.Specify dtype option on import or set low_memory=False.\n",
            "  exec(code_obj, self.user_global_ns, self.user_ns)\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "stripe_22.03.csv\n",
            "stripe_22.02.csv\n",
            "stripe_22.01.csv\n",
            "done\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "#### PayPal"
      ],
      "metadata": {
        "id": "mbNc1DpLNYiS"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "#Set file paths\n",
        "inpathpp = path + \"PayPal/\"\n",
        "outpath = path + \"Results/\"\n",
        "\n",
        "c=0\n",
        "for file in os.listdir(inpathpp):\n",
        "  \n",
        "  c+=1\n",
        "  print(file)\n",
        "  \n",
        "  df = pd.read_csv(inpathpp + file, encoding = \"ISO-8859-1\", index_col=False)\n",
        "  \n",
        "  #Consider Only \"Subscription Payment\" & \"Payment Refund\" types (\"E\" column in the PayPal statements). Remove all the other types\n",
        "  df = df[(df[\"Type\"] == \"Subscription Payment\") | (df[\"Type\"] == \"Payment Refund\")]\n",
        "  \n",
        "  #Change the data type of gross to numeric\n",
        "  df = df.astype({\"Gross\": str})\n",
        "  df['Gross'] = df['Gross'].str.replace(',','')\n",
        "  df = df.astype({\"Gross\": float})\n",
        "\n",
        "  #*********** Calculate revenue per customer per month ******************\n",
        "  #Apply Group by to input data\n",
        "  sum_df = df.groupby(['From Email Address' ], as_index=False).agg({'Name':'first', 'From Email Address':'first',\n",
        "                                                            'Country':'first', 'Subject':'first','Gross': 'sum'}) #grouped by dataframe'\n",
        "  sum_df = sum_df.sort_values('From Email Address')\n",
        "  \n",
        "  #Form revenue headers\n",
        "  month =  file[7:14].replace('.','-')  #From input file name\n",
        "\n",
        "  sum_df.rename(columns={'Name':'Customer Name', 'From Email Address':'Customer Unique ID', \n",
        "                        'Country': \"Customer Country\",'Subject':'Customer Plan', 'Gross': month},inplace = True)\n",
        "  \n",
        "  \n",
        "  #Create base dataframe from first file\n",
        "  if c==1:\n",
        "    final_df=sum_df.copy()\n",
        "  else:\n",
        " \n",
        "    #Merge using full outer join\n",
        "    final_df = final_df.merge(sum_df, how='outer', on = 'Customer Unique ID')\n",
        "\n",
        "    #Drop columns with '_x\n",
        "    cols = [col for col in final_df.columns if col.lower()[-2:] != '_x']\n",
        "    final_df=final_df[cols]\n",
        "\n",
        "    #Remove '_y' from column name\n",
        "    for col in final_df.columns:\n",
        "      if col.lower()[-2:] == '_y':\n",
        "        final_df.rename(columns = {col: col[:-2]}, inplace=True)\n",
        "\n",
        "#insert 'Platform' and 'customer plan duration' cols\n",
        "final_df.insert(1,\"Platform\", \"PayPal\")\n",
        "final_df.insert(1,'Customer Plan Duration',\"\")\n",
        "\n",
        "\n",
        "#Reorder the headers\n",
        "lsthead = gen_header()\n",
        "final_df = final_df.reindex(columns=lsthead)\n",
        "\n",
        "#Sort by first month\n",
        "final_df = final_df.sort_values('2020-01')\n",
        "\n",
        "#Export data to excel without index\n",
        "final_df.to_excel(outpath + 'PayPal_RevenueByCustomerByMonth.xlsx',index = False)\n",
        "\n",
        "print('done')\n",
        "\n",
        "  "
      ],
      "metadata": {
        "id": "w-0tUINoo10P"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        ""
      ],
      "metadata": {
        "id": "faWn593xYK1l"
      },
      "execution_count": null,
      "outputs": []
    }
  ]
}