financial_vis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "4f2b6377-5107-40af-b742-78f4c7060d45",
   "metadata": {
    "tags": []
   },
   "source": [
    "# **PayPal 🡲 Membershipworks Migration & Reporting**\n",
    "## *For the [Ithaca Generator](https://ithacagenerator.org/)*\n",
    "\n",
    "- - - \n",
    "\n",
    "#### *A work in progress by [Jess](https://www.transscendsurvival.org/)*\n",
    "\n",
    "\n",
    "#### The objectives of this notebook and its scripts are:\n",
    "##### - Merge PayPal & Membershipworks members in a sorta intelligent way to create kinda accurate financial reports\n",
    "##### - Convert a PayPal transaction export into an upsert-able csv to import into membershipworks\n",
    "##### - Keep tabs on PayPal memberships as they become deprecated \n",
    "\n",
    "\n",
    "\n",
    "- - - \n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b847619f-f175-4e16-9cbf-84e7ee663cdb",
   "metadata": {},
   "source": [
    "<div style=\"page-break-after: always\"></div>\n",
    "\n",
    "\\pagebreak"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cdd9925f-3f2b-43c7-91e3-d9a7eaa85ede",
   "metadata": {},
   "source": [
    "\n",
    "## **January 2022 Financial Stuff:**\n",
    "\n",
    "\n",
    "![](table_fig.png)\n",
    "  \n",
    "\n",
    "\n",
    "![](gross_income.png)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bf30aed5-13e8-478b-97ed-19860a958995",
   "metadata": {},
   "source": [
    "<div style=\"page-break-after: always\"></div>\n",
    "\n",
    "\\pagebreak"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d8315c8-686f-4e5d-ab4f-257b6c422e7c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from matplotlib import pyplot as plt\n",
    "import matplotlib.patches as mpatches\n",
    "from pandas.plotting import table\n",
    "import datetime\n",
    "import json\n",
    "import csv\n",
    "import random\n",
    "import math\n",
    "import random\n",
    "import shutil\n",
    "import os\n",
    "\n",
    "# Plot configurations\n",
    "# Set matplotlib font size\n",
    "SMALL_SIZE = 16\n",
    "MEDIUM_SIZE = 18\n",
    "BIGGER_SIZE = 24\n",
    "EXTRACHUNGUS_SIZE = 28\n",
    "EXTRAJANKY_SIZE = 44\n",
    "\n",
    "plt.rc('font', size=EXTRACHUNGUS_SIZE)          # controls default text sizes\n",
    "plt.rc('axes', titlesize=EXTRACHUNGUS_SIZE)    # fontsize of the axes title\n",
    "plt.rc('axes', labelsize=BIGGER_SIZE)    # fontsize of the x and y labels\n",
    "plt.rc('xtick', labelsize=BIGGER_SIZE)    # fontsize of the tick labels\n",
    "plt.rc('ytick', labelsize=BIGGER_SIZE)    # fontsize of the tick labels\n",
    "plt.rc('legend', fontsize=EXTRACHUNGUS_SIZE)    # legend fontsize\n",
    "plt.rc('figure', titlesize=EXTRAJANKY_SIZE)  # fontsize of the figure title\n",
    "\n",
    "# `Halt()` to quietly exit mid cell:\n",
    "class HaltNoTrace(Exception):\n",
    "    def _render_traceback_(self):\n",
    "        pass\n",
    "\n",
    "def Halt():\n",
    "    raise HaltNoTrace\n",
    "\n",
    "# filter out ragged array warning cause i r b lazy:\n",
    "np.warnings.filterwarnings('ignore', category=np.VisibleDeprecationWarning)         \n",
    "\n",
    "## file paths:\n",
    "# paypal csv obtained from `https://www.paypal.com/reports/statements/custom`\n",
    "paypal_csv_fp = \"./csv/paypal_export.CSV\"\n",
    "paypal_df = pd.read_csv(paypal_csv_fp)\n",
    "print('loaded %d paypal records' % paypal_df.shape[0])\n",
    "\n",
    "# memebershipworks csv with existing account IDs exported from `https://membershipworks.com/admin/#folder/Members`\n",
    "mw_ids_csv_fp = \"./csv/mw_export.CSV\"\n",
    "mw_ids_df = pd.read_csv(mw_ids_csv_fp, encoding='cp437')\n",
    "print('loaded %d existing membershipworks records' % mw_ids_df.shape[0])\n",
    "\n",
    "## files to export\n",
    "\n",
    "# all members:\n",
    "membershipworks_import_fp = './csv/membershipworks_import.csv'\n",
    "\n",
    "# members as of last month:\n",
    "membershipworks_pastmonth_import_fp = './csv/membershipworks_import_pastmonth.csv'\n",
    "\n",
    "# establish what one month ago was:\n",
    "thisMonth = datetime.datetime.today().replace(day=1)\n",
    "lastMonth = thisMonth - datetime.timedelta(days=31)\n",
    "priorMonth = lastMonth.replace(day=1) - datetime.timedelta(days=31)\n",
    "\n",
    "# convert the date column values into python datetime objects:\n",
    "paypal_df['Date'] = pd.to_datetime(paypal_df['Date'])\n",
    "print('converted `Date` column to datetime objects')\n",
    "\n",
    "# transactions below this value are discarded:\n",
    "min_net = 10\n",
    "standard_price = 35\n",
    "   \n",
    "# keep only rows from within a range:\n",
    "dates_df = paypal_df[paypal_df['Date'] <= thisMonth]\n",
    "dates_df = dates_df[dates_df['Date'] >= lastMonth]\n",
    "\n",
    "print('kept %d records processed between %s and %s; discarded %d records' %\n",
    "      (dates_df.shape[0],\n",
    "       thisMonth.__format__('%d/%m/%y').__str__(),\n",
    "       lastMonth.__format__('%d/%m/%y').__str__(),\n",
    "       paypal_df.shape[0] - dates_df.shape[0]))\n",
    "\n",
    "\n",
    "members = []\n",
    "membership_levels = ['Extra Membership', \n",
    "                    'Offline Extra Membership',\n",
    "                    'Standard Membership', \n",
    "                    'Offline Standard Membership',\n",
    "                    'Offline VIP Membership']\n",
    "\n",
    "\n",
    "memershipworks_df=pd.DataFrame(columns=['Name',\n",
    "                                        'Email',\n",
    "                                        'Membership Level',\n",
    "                                        'Join Date',\n",
    "                                        'Renewal Date',\n",
    "                                        'Account ID'])\n",
    "\n",
    "# is there a level value?\n",
    "is_lvl = lambda _L: isinstance(_Series[_L].item(), str)\n",
    "\n",
    "# is there a mw id value?\n",
    "has_id = has_level = lambda _S: _S.shape[0] \n",
    "\n",
    "# some members might make multiple transactions per month; only count transactions > $10.\n",
    "for _, row in dates_df.iterrows():\n",
    "    \n",
    "    # make sure this is a valid row\n",
    "    if type(row['From Email Address']) == str:\n",
    "\n",
    "        # make sure this is a membership payment, not a lil donation stub or refund or sumth:\n",
    "        if eval(row['Net']) > 10 and eval(row['Net']) < 140 and row['Description'] == 'Subscription Payment': \n",
    "            if row['From Email Address'] not in members:\n",
    "                members.append(row['From Email Address'])\n",
    "            else:\n",
    "                print(\"%s already in member list! continuing...\" % (row['Name']))             \n",
    "                continue \n",
    "        else:\n",
    "            print(\"discarded %s's %s record for %s, continuing...\" % (row['Name'], row['Description'], row['Gross']))\n",
    "            continue\n",
    "        \n",
    "        ## get Account ID if there is one:\n",
    "        # The pandas series of membershipworks stuff of this Account ID:\n",
    "        _Series = mw_ids_df[mw_ids_df['Email'] == row['From Email Address']]['Account ID']  \n",
    "        membership_id = '' if not has_id(_Series) else _Series.item()\n",
    "\n",
    "        ## get membership level if there is one:\n",
    "        # The pandas series of membershipworks stuff of this email address:\n",
    "        _Series = mw_ids_df[mw_ids_df['Email'] == row['From Email Address']]\n",
    "        new_lvl = 'Offline Standard Membership' if eval(row['Gross']) < standard_price else 'Offline Extra Membership'\n",
    "        membership_level = new_lvl if not has_level(_Series) else [l for l in membership_levels if is_lvl(l)][0]\n",
    "        \n",
    "        # get a past join date; not sure how best to actually track this but its whatever:\n",
    "        join_date = lastMonth.__format__('%m/%d/%Y').__str__()\n",
    "\n",
    "        # append:\n",
    "        memershipworks_df.loc[row['From Email Address']] = row['Name'], row[\n",
    "            'From Email Address'], membership_level, join_date, join_date, membership_id\n",
    "\n",
    "        \n",
    "mw_member_names = set(mw_ids_df['Account Name']) - set(memershipworks_df['Name'])\n",
    "\n",
    "for mw_member in mw_member_names:\n",
    "    \n",
    "    # get exported MW member info:\n",
    "    _Series = mw_ids_df[mw_ids_df['Account Name']==mw_member]\n",
    "    \n",
    "    mw_email = _Series['Email'].item()\n",
    "    \n",
    "    # get membership id if we can:\n",
    "    mw_membership_id = _Series['Account ID']\n",
    "\n",
    "    # get membership level if we can:\n",
    "    mw_membership_level = [l for l in membership_levels if is_lvl(l)][0]\n",
    "  \n",
    "    if 'Offline Standard' in mw_membership_level or 'Offline Extra' in mw_membership_level:\n",
    "        print('please REVIEW %s from MW member directory in PayPal' % mw_member)\n",
    "    \n",
    "    mw_join_date = pd.to_datetime(_Series['Join Date']).item().__format__('%m/%d/%Y').__str__()\n",
    "    memershipworks_df.loc[mw_email] = mw_member, mw_email, mw_membership_level, mw_join_date, mw_join_date, mw_membership_id\n",
    "\n",
    "        \n",
    "memershipworks_df.to_csv(membershipworks_import_fp)\n",
    "\n",
    "export_str = 'exported: %d Members! \\n - %d Standard Members \\n - %d Offline Standard Members \\n - %d Extra Members \\n - %d Offline Extra Members \\n ...to a membershipworks-readable format at %s' % (memershipworks_df.shape[0], memershipworks_df[memershipworks_df['Membership Level'] == 'Standard Membership'].shape[0],\n",
    " memershipworks_df[memershipworks_df['Membership Level'] == 'Offline Standard Membership'].shape[0],\n",
    " memershipworks_df[memershipworks_df['Membership Level'] == 'Extra Membership'].shape[0], \n",
    " memershipworks_df[memershipworks_df['Membership Level'] == 'Offline Extra Membership'].shape[0],\n",
    " membershipworks_import_fp)\n",
    "print(export_str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "91cc475e-a932-4263-abf4-b29ae0bdca06",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Plot some stuff:\n",
    "def bar_view():\n",
    "    offline_extra_ct = memershipworks_df[memershipworks_df['Membership Level'] == 'Offline Extra Membership'].shape[0]\n",
    "    offline_extra_gross = 75 * offline_extra_ct\n",
    "\n",
    "    offline_std_ct = memershipworks_df[memershipworks_df['Membership Level'] == 'Offline Standard Membership'].shape[0]\n",
    "    offline_std_gross = 35 * offline_std_ct\n",
    "\n",
    "    extra_ct = memershipworks_df[memershipworks_df['Membership Level'] == 'Extra Membership'].shape[0]\n",
    "    extra_gross = 75 * extra_ct\n",
    "\n",
    "    std_ct = memershipworks_df[memershipworks_df['Membership Level'] == 'Standard Membership'].shape[0]\n",
    "    std_gross = 35 * std_ct\n",
    "\n",
    "    counts = [offline_extra_ct, offline_std_ct, extra_ct, std_ct]\n",
    "    gross = [offline_extra_gross, offline_std_gross, extra_gross, std_gross]\n",
    "    labels = ['Off. Extra', 'Off. Standard', 'Extra', 'Standard']\n",
    "\n",
    "    counts = np.array(counts)\n",
    "    gross = np.array(gross)\n",
    "    labels = np.array(labels)\n",
    "\n",
    "    # sorts our numpy arrays:\n",
    "    map_sort = lambda *sortables: map(np.array, zip(*sorted(zip(*sortables))))\n",
    "\n",
    "    # this is the order we'll sort by:\n",
    "    sortable_arrays = gross, counts, labels\n",
    "\n",
    "    # apply sort:\n",
    "    gross, counts, labels = map_sort(*sortable_arrays)\n",
    "\n",
    "    # reverse so the most prevalent countries are listed first:\n",
    "    counts = counts[::-1]\n",
    "    gross = gross[::-1]\n",
    "    labels = labels[::-1]\n",
    "\n",
    "    # initialize plot:\n",
    "    width = 0.8\n",
    "    fig3 = plt.figure(figsize=(12, 12))\n",
    "    ax3 = fig3.add_subplot()\n",
    "\n",
    "    labels_loc = np.arange(len(labels))\n",
    "\n",
    "    # draw bars:\n",
    "    rects3 = ax3.bar(labels_loc + width/2, gross, width, label='', color=['blue', 'green', 'green', 'blue'])\n",
    "\n",
    "    plt.xticks(rotation = 75)\n",
    "    ax3.set_xlabel('Membership Type')\n",
    "    ax3.set_ylabel('Gross Incoome')\n",
    "    ax3.set_title('Monthly Gross Income: %d (%d per annum)' % (sum(gross), sum(gross*12)))\n",
    "\n",
    "    ax3.set_xticks(labels_loc)\n",
    "    ax3.set_xticklabels(labels)\n",
    "\n",
    "    for xy in zip(labels_loc, gross):\n",
    "        if xy[1]: # if 0, don't plot\n",
    "            ax3.text(xy[0] + width/2, xy[1], '%s$' % xy[1].__str__(),\n",
    "                    ha='center', va='bottom')\n",
    "\n",
    "    fig3.tight_layout()\n",
    "    plt.tight_layout()\n",
    "    plt.savefig('gross_income.png')\n",
    "    \n",
    "bar_view()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e2a8202a-35a3-4c79-9b4e-54dd5a894410",
   "metadata": {},
   "outputs": [],
   "source": [
    "def table_view():    \n",
    "    ## build a table of sums ##\n",
    "    total_members = memershipworks_df.shape[0]\n",
    "    offline_extra_members = memershipworks_df[memershipworks_df['Membership Level'] == 'Offline Extra Membership'].shape[0]\n",
    "    extra_members = memershipworks_df[memershipworks_df['Membership Level'] == 'Extra Membership'].shape[0]\n",
    "    offline_std_members = memershipworks_df[memershipworks_df['Membership Level'] == 'Offline Standard Membership'].shape[0]\n",
    "    std_members = memershipworks_df[memershipworks_df['Membership Level'] == 'Standard Membership'].shape[0]\n",
    "    spons_members = memershipworks_df[memershipworks_df['Membership Level'] == 'Offline VIP Membership'].shape[0]\n",
    "\n",
    "    count_df = pd.DataFrame()\n",
    "\n",
    "    # convert sums with __str__ to avoid recasting\n",
    "    # as a float when the table gets drawn\n",
    "    count_df.loc['Count:', 'Total Members'] = total_members.__str__()\n",
    "    count_df.loc['Count:', 'Offline Extra'] = offline_extra_members.__str__()\n",
    "    count_df.loc['Count:', 'Extra'] = extra_members.__str__()\n",
    "    count_df.loc['Count:', 'Offline Standard'] = offline_std_members.__str__()\n",
    "    count_df.loc['Count:', 'Standard'] = std_members.__str__()\n",
    "    count_df.loc['Count:', 'Sponsored'] = spons_members.__str__()\n",
    "\n",
    "    # draw long low table only:\n",
    "    fig = plt.figure(figsize=(18,2))\n",
    "    ax = fig.add_subplot(111, frame_on=False)\n",
    "    ax.xaxis.set_visible(False)\n",
    "    ax.yaxis.set_visible(False)\n",
    "\n",
    "\n",
    "    # draw table:\n",
    "    table_fig = table(ax, count_df, rowLoc='right',\n",
    "                 colLoc='center', loc='bottom',\n",
    "                 bbox=[.1, -0.3, 1, 1.2])\n",
    "\n",
    "    table_fig.auto_set_font_size(False)\n",
    "    table_fig.set_fontsize(18)\n",
    "\n",
    "    fig.savefig('table_fig.png')\n",
    "\n",
    "table_view()"
   ]
  }
 ],
 "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
}