civiMemberReportsExplore.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1cb4ffc1-3b7a-4d8a-a49b-19667d802e61",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import re"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dc152992-d0ea-43bf-abeb-de59eabe6e25",
   "metadata": {},
   "source": [
    "Priorities:\n",
    "- historical conversion rate of trial members: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0d247695-92de-4680-8148-26dd713f7a55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'C:\\\\Users\\\\200669681\\\\OneDrive - NBCUniversal\\\\My Documents\\\\ghfc\\\\membershipReportsCIVI'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "os.getcwd()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "24011f1e-6875-43a3-bdb0-feb52ddb9167",
   "metadata": {},
   "outputs": [],
   "source": [
    "#will contain membership type history, however, if the plan had been cancelled, it won't show the start date of the membership\n",
    "encompassing = pd.read_csv('./membershipReportingLogic SampleReports/membershipAllEncompassingReport.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b49b5712-e0f7-4ffa-bc5a-02536df1dd1a",
   "metadata": {},
   "outputs": [],
   "source": [
    "contactActivityDetails = pd.read_csv('./activityDetailsReport_contactReport.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "3098b0ef-6f96-4c0f-b2b4-05971410b7c8",
   "metadata": {},
   "outputs": [],
   "source": [
    "activityReport = pd.read_csv('./membershipReportingLogic SampleReports/select_activity_report.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "688900e2-8ab1-421c-aafc-2c0d751060e5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Subject                                          \n",
       "Status changed from New to Expired                   705\n",
       "Status changed from New to Active                    515\n",
       "Status changed from Active to Cancelled              121\n",
       "Status changed from Active to General Leave          120\n",
       "Status changed from Active to Expired                 91\n",
       "Status changed from Active to Deactivated             62\n",
       "Status changed from Re-activated to Active            44\n",
       "Status changed from Expired to Active                 42\n",
       "Status changed from New to Deactivated                38\n",
       "Status changed from Pending to New                    37\n",
       "Type changed from Zucchini Plan to Lettuce Plan       29\n",
       "Status changed from General Leave to Re-activated     26\n",
       "Status changed from New to Cancelled                  18\n",
       "Status changed from Parental Leave to Active          16\n",
       "Status changed from Deactivated to Active             15\n",
       "dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#top 15\n",
    "contactActivityDetails[['Subject']].value_counts(dropna = False)[0:15]\n",
    "#contactActivityDetails['Subject'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "6fdfbfe8-b342-45ad-a798-a90f20aaaffb",
   "metadata": {},
   "outputs": [],
   "source": [
    "member_status = encompassing[['Membership Type','Status']].value_counts()[encompassing[['Membership Type','Status']].value_counts()>10]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "5618368c-ecec-42a0-b31c-f7552f6949c9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.series.Series"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "type(member_status)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "776a553d-713d-4834-b63f-a2a665ff747b",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = member_status.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "1dd03091-6a03-4f10-ab75-a903790dd95c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = df.rename({'Membership Type':'membership_type','Status':'status',0:'count'}, axis = 1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "2e04ce85-a512-46d4-8cea-39356a5e3510",
   "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>membership_type</th>\n",
       "      <th>status</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Apple Plan</td>\n",
       "      <td>Deactivated</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Apple Plan</td>\n",
       "      <td>Cancelled</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Carrot Plan</td>\n",
       "      <td>Current</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Carrot Plan</td>\n",
       "      <td>Cancelled</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Lettuce Plan</td>\n",
       "      <td>Current</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Lettuce Plan</td>\n",
       "      <td>Cancelled</td>\n",
       "      <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Lettuce Plan</td>\n",
       "      <td>Deactivated</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Lettuce Plan</td>\n",
       "      <td>New</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Trial Membership</td>\n",
       "      <td>Expired</td>\n",
       "      <td>1548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Trial Membership</td>\n",
       "      <td>New</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zucchini Plan</td>\n",
       "      <td>Cancelled</td>\n",
       "      <td>113</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Zucchini Plan</td>\n",
       "      <td>Deactivated</td>\n",
       "      <td>97</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Zucchini Plan</td>\n",
       "      <td>Expired</td>\n",
       "      <td>84</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Zucchini Plan</td>\n",
       "      <td>Current</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Zucchini Plan</td>\n",
       "      <td>New</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     membership_type       status  count\n",
       "11        Apple Plan  Deactivated     13\n",
       "13        Apple Plan    Cancelled     12\n",
       "7        Carrot Plan      Current     28\n",
       "9        Carrot Plan    Cancelled     22\n",
       "2       Lettuce Plan      Current    112\n",
       "6       Lettuce Plan    Cancelled     59\n",
       "8       Lettuce Plan  Deactivated     23\n",
       "10      Lettuce Plan          New     22\n",
       "0   Trial Membership      Expired   1548\n",
       "5   Trial Membership          New     71\n",
       "1      Zucchini Plan    Cancelled    113\n",
       "3      Zucchini Plan  Deactivated     97\n",
       "4      Zucchini Plan      Expired     84\n",
       "12     Zucchini Plan      Current     12\n",
       "14     Zucchini Plan          New     11"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2.sort_values('membership_type')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "99a7173a-f9ae-4f06-92d4-f4e1cb95d600",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_wide = df2.pivot_table( index='membership_type', columns='status', values='count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "5e729aaf-08e7-4282-9818-27a9136cadd9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Text(0.5, 1.0, 'Heatmap: Membership Type - Status')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAokAAAFaCAYAAABylcc9AAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjQuMiwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8rg+JYAAAACXBIWXMAAAsTAAALEwEAmpwYAABFQUlEQVR4nO3dd5xcVd3H8c83kACR3kNAg0hHaQEpCkGKqFQFwUoUjV1BAcHHR6M+KBZsIGJEDE2KCIIoHUKTXkIIRVqAQOi9pe3v+eOcMTdTdmdmd3Z3dr7vvO4rM+fee+6ZO3dnfnPaVURgZmZmZlY0bKALYGZmZmaDj4NEMzMzM6vgINHMzMzMKjhINDMzM7MKDhLNzMzMrIKDRDMzMzOr4CDRzIYsSVFYJg50eZohaVzZ6xjXwL5t//rNbOA4SLSGSBpT9sUzucZ2MwrbzOjfUi5UjvLyjh+osgxWVc5RSHpS0mI1tp9aZfsx/VxsG0QkTaxyTfS4DHS5B5qkD0u6QNIsSXMkvSLpUUk3SPqjpC9U2Wdyqz5by9/Hvszb2tOiA10AMxuUVgE+AZxYTJS0E/CuASmRNePQwuN/D1gprIKkE4ADy5KHA0sCawDvBvYF/tDPRTP7LweJZlbLwZQFicC3BqIgQ4GkpSLilf48ZkT8op8OdQnwalnafsDYwvMfAy/0U3kGNUm7sHCAeDtwMfASsDzph9j2A1A0s4VFhBcvdS/AGCAKy+Qa280obDOjynoBHwUuAGYBc4AXgauBCcCiVfb5LHAGMB14Ou/zKnAP6df2+t2UoepSY9vJwOakD+1XgGeBk4CV8rbjgCnAa8DzwOnA6CrlPRQ4F7gv5zE353cn8Etg9Sr7TC6eN2Bp4GjgEWA28CAwEVisyr5TCvtO6cV7Oq/weOfCdhsAXVW2CWBMlXzfDZwMPAS8kc/XncAPgeV7Kj/wDuBsUmDxYj6Xa+VtNwb+Cbycl3+Wv/95u2IZJ+b9/pHzfA24Fti1xnkZDnwOuAx4hnS9PUsKiPat4zyOB/Ym1eC9wsLX2y7AecDj+X19HXgUuBL4GbBOYdtxZfmOA/bMZX+NFFj8A1ivp9ffQ54fB27KZXk+n/t1e/FZMbnWNQLcUEg/t8q+5eXburd/H81ck61aSH//pdfxANU/75YEdi88H192TqotE/O2ywM/BS4FHs7XyFzguXw9Hgos0c35rrZMrlGOMWXlnlG+T2HdlqTP8EeAN/N7MBO4Dvg1MLa/3gMvdV6rA10AL+210AdBIrAYKTjs7gPpSmBk2X639LDPm8C4GmWoutTYdlr+8Crf/m5SE+z8KuvuoeyLiRRQdHf854ENyvaZXFj/NDC1xr6XAIuU7TulsH5KL97TvxUe/6uw3QmF9LPL9hlTluf3WBBQVlseoSwAKSv/Q6QvtPL9ngL2IAUy5eueBlYsy7O4/tIa72sXcEDZfsuTAqbu3r+/AMO6OY9Xl++Tt/tED/kGML6Q77iydf+qsU9Pr39iN3leViPPF4B3NflZMbnWNQJ8rJA+F1itbN/jC+un99HfR8PXZAs/R39TOO6zwNp17DO+jutmYt52ozq2vRV4S43rodoyuUY5yv/2Z5Tvk9O3z+91j+X3MngWNzdbb20o6ZAq6Ut3s8/RwIfy4y5SwDENeBvwKVIQOY70y3JCYb9nSMHlA6Qvr7nAqqTamjXyfseSPiABjiR9cX+nkMeZpGCzOxuRvjBOI/3y3Smnrw+cSvoQPA3YNpcTYD1gr5x/yUxS4PNILm8Aq5NqUJcHliPVGu1WoxwrAcsCfyR9kXwUWCuv2xn4CvDbHl5LM6YBS+Vj7CppfVLA9sm8/j5Szd1Hqu0saR/gB4Wk60gB2ltI7++qwFuBcyW9MyLmV8lmzXzMnwFvB/bJ6SuTauCeAY4B1iGdd0jn60BSDUo1O5Gunb8CK5K+7IaTarWPk3RJRMzK254MbJEfv0mq/XgA2JDUjDqMFOjcRWpGrea9+TWcSQpoSvl9rbDNfbk8c0jXxvrA1jXyK/kAcDOppnsH0nUIPb/+7uxICmqnAJux4JpclhSYbdZEnt05G/g5MJrU7elA4EcAkhZl4WvrTzXyqPvvo4+uyb50W+HxCsB9kqaRPptuB66JiKll+9xMqgEsNuO/wMLXX6nfaRdwL+mHzpN5uxGk62sf0jnfDPgS8AtSDeyhpBrunQv5Ffu03tXoiyzzJRZ0cXuc9Fn6CrAasDbp78UGm4GOUr2010JlbUk9y4zC/sux8K/Jw8ry/1Jh3Twqa0YWJwVmBwIHAYeQ+s0Vj7dGN+UdX+N1zShsM5f86xgYWVbeOaX8SYHwnMK6o6vkuxTpg3cCqY/fIcDfC/u8CQwvbD+5rLyfKqxbngXBZgB3lx1rSmHdlF68pxOB9xeeTyI1x5Wef5FuahNYuMb3n4AK69Yv22+vGuUPYNvCusfL1m2V04cBTxTS/1b22or7PAssW1j3mbL1h+X08lqYj5bl+dPCuufINVZVzuNLwFurnO87CtvsX2X90sDKhefjyvK9sXTNkILcp+p8/RO7yfPSsvfppLL1WzbxWTG51jWS13+nsO4Rcq0sC197syl8BlTJs66/D5q8Jlv4ObooCze5V1vurVaWsnMwo4fjjCb9iPoyqT/xIaQfgaX9Ly/bfmKxDDXyHN/D+zqjsG5yIf3vhfTDq+S7BFW67XgZ2MU1idbftmLhAVM/lVSr5mORvP0FAJK+QQpWuqulhFQj81gvynhdRMwAiIjXJT0DjMrrro2Ix/K6lyU9TfoghhQAk8s6DPg/4JukGs5aFiPVas2qsm4uqUmTfLznJf2DVPMBsL6kt0TEa3n9uEZeZHci4mJJ00k1Z58i9d2CFBSdRKrNqCBpJAvXOn0Q6JJU61DvIX15lHskIq4rPifVOAA8HBE35HJ2SXqIBe/PctR2fkS8WHh+KqkJvTQVWKl2prxG40xJZ1Ld8qQgo1oty8kR8WiV9KtJfSMBJkv6EqmW8j+kYObqiHi5m9dxQkTMBYiIuZIeJtWwQvevvzunRP6mzk4CPl14PpZUK9WXJgH/S/rh91ZSDek/gf0L25wXEc/W2L+uvw9SUNIX1+R/SVqD6n8DL0XEH7vbN5d1nqQdSTV1B5I+s8qtC5wjac+I+EdPeZaVbzngz6SuGTVfaI3jtsrVpL60AP8naU/SNf8Aqel7SkQ83o/lsTo4SLTeOikixpcn5vm73lZl++UbzH+lnN8epObnenQXlNWj/INqTuHxE2Xr5hUeF+cd/SpwRJ3Hq1Xe56Ky2eupsufLsiCA62u/JDX1LZ4XgOMj4o1uvmCXo/svpXIr1Ujvi/eg3ELnLgdYz5OCdFgQYDV1jVZxb43075CCoj1I7/12eSl5UtI+ZUFy0Yyy57MLj5ud+7b8uip/3mzwWVNEPCvpNBaM8v2CpEtZ0H0AUhBfS71/H9A312TRWqTm8nKPkJq/e5R/3E0EJkpahzSoZltS95lS0C/SD82GgkTS3+2ePW7V+89KqDy3tfL8DekH1QGkGvCt8lLykqTPRsQ5fVAm6yMOEq2/PV/2/I+kX5O1lPoPFmsXXiP1q7kqBywfJNVA9JW53ayb1826omJ5nyD1sbo9ImZL+jLwuzryWEHSImVfhKuUbfNineVpxmmk/k6lY84h9fnsTqm5r/TFcQVwYTfb310jvS/eg3ILnTtJw1k4+Hkx/19+jf6U1FRdy4M10qsG7xHxKrCXpFVIX5Jr52V3Uo3oqsAppL6Y1ZSfm6i6VWPKr6v+us5+zYIg8YPA51kQ2D1CGlBTS71/H6Umzt5eky0TEf8hfQ6eIulQ0g+MUs15tR/bNeXa/D0KSVeSurs8HBHzJZ1Fmn+xWV1lz5coHHtpKt8DAPL79HlJh5Gu+3VJMxjsSgq6lwFOknRRRLzei/JZH3KQaP3tBtKXfOnaWyyqzOUmaVngAxExLSetWFj9UERcVHheDMjKlX+hjmysuE0rlvfWUvNoboau9wN6OGlaklPyvsuTAomSe0pNzXn9FBbMrXZVb5ufc0D7O1ITP8DpEfFkD/u8Lul2FjTvrUqqfVxoDr0coO0OXN+bMjZoD0nLFpqcP0nq0lByc/7/2rL9Zte4RlclTc1SrUm5JkkbAfdHxFOkQTil9ItJI8sB1pS0QkQ810jevfApScUm5wPK1t9cvkNfiIi7JF0BvI/0XhTP858jojwgKar776Ovr8mImEJjtZMLkXQAadDMX8q6QECqGS7WnJdfA8XPtGqfZ8uy8HV9QUQ8kI+7MmmwUy0LfV5KGlklYCsv71YsCKyPoMZ5kbQuMDMiXiAF6Rfm9M1Izc2Qpv1Zv/DcBpiDROtXEfGCpD+SBqgAfDqPnr2MNNJtZWBTYBtSDdzpebv7WDDq7p25j9hdpA747+vmkKX5FEfk54dIWpE0hcqDEXFuX7yuKu4j1Q4BfCi/5sdJo7rH1tyr0p8kvYdUk7UfC2pZIPXparXfkjq6Q/190n7KglHeGwDTJZ1LGmW5NKmf47j8eE36b4LlFYCbc03KiqSBKyVvkPooEhHTJF1I6iMH8L38Hvw7b7ca6T3cAriGNH9jI44C3puDo0dJTaRLk0ZLl5TmTuwvOwFTJF1JmiO0OOL+jojo6/6IRb9hwd9wqVtDF5UTuVdT79/HYLsm1wS+D/xa0nWkEc3PkAa6fYg0CKrkX2X7ziw8Xknp1qjTSbWlp5A+815kwbn4bq61DlJ/zeIP2HIzy57/RdL1pGm/zs81nreQ3p9S14bjJO1GCr67G5n/NeDAfI09RDr3iwEfLtuuvz4PrB4DPXLGS3st9M08iYvT8zyJC+1Hao54qcZ25aObx5Ud76wa+11Qo7yTu3ktda0jBblzqhxzLumDvOroQBYeufgktefqu5yyCXjpw9HNdewzvtZryOu/T/dz0lV77TXL34t1xWNdW+M96QIOLNtvhW7OfVQ7XpXzOL7Guavn2v9JYftxZevKr+96X//EbvL8R41yvARs0uRnxeTurpHCdsNIgxeK215YR56N/n00fE22aqFsFHE3y83AUmX7vpPKyexLy9i8zaE11s8kzSFZel7+2bwyeeL3Kss+3by3peUmFh5tX/xMPLaO13t6q8+9l8aWZjs5mzUtIt6MiN1I/fRKd52YQ7pzxr2kL9GvUBhlGhEP5ucXkvp6vU5qut6DNKdddyaQahWeIP0ibrmI+DepduYa0jQ3r5D6Qm1P+gKrx5ukpqGfkfpnzSUFpT8CPhQRzfbNa7mI+AFpjskTgftJNXBvkO7+cBVpYuNNI48i7yeXkQYGXEgKft4g1Q7uFhELzcUXqZl3G9Jdfi4mffHNJdVyTCfN8/cZmuvbdTRpUNC1pJrEN3Les3LZ9o+Iegc99ZWjSa/lxlyeF0k1pO+OiDtaeeBITcrHlCV3N2ClpKG/j0F2Tf6aVIP2W1Lz9sOkz7W5pJrAK0mD37aNsls5RuqC8xHSe1W1tjkifg58gTTJ/1xSLeVppMEx5QO/ivs9TapBv5L0mVXLBFKN+GM5/4dJ53170jmt5s+kPs5XkN6n10jB7jP5eF9kwVysNkgoR/hmNgjkpqMD8tNHImLMwJXGhipJ40hfzCU7ROpnNyAk7Uyq4YIUNIyOPM1P2XaT8d+HWb9xn0QzM+t3eXDaJqQ+chMLq46rFiCaWf9zkGhmZgNhExauzYQ0oOHo/i+KmVXjPolmZjbQniL1mRtX3gfPzAaO+ySamZmZWQXXJJqZmZlZBfdJ7ACLjhjt6uJ+MG+O703faouOGD3QRRjyfB3bENL0XXGaMffZhxr+rh2+4tv7tYyNcpBoZmZm1ltd/TINb79ykGhmZmbWW93earw9OUg0MzMz660uB4lmZmZmViZck2hmZmZmFVyTaGZmZmYVXJNoZmZmZhU8utnMzMzMKgzBmkTfccXMzMzMKrgm0czMzKy3PHDFzMzMzMp5ChwzMzMzq+SaRDMzMzOr4JpEMzMzM6vgKXDMzMzMrIJrEs3MzMysgvskmpmZmVkF1ySamZmZWQXXJJqZmZlZuQgPXDEzMzOzcm5uNjMzM7MKQ7C5edhAF2CgSdpbUkhar5f5TJa0TwPbT5T0uKQ7JN0laY9C+iG9KYuZmZn1s+hqfOmBpBMlPS3prirrDsnxy4qFtCMkPSDpPknvL6RvLmlaXvdbSarnJXV8kAh8DLgW2H8Ajv2riNgE2Bc4UZLfDzMzs3bUNb/xpWeTgV3LEyWtAewMPFpI24AUy2yY9zlO0iJ59e+BCcDaeanIs5qODkokLQlsCxxIIUiUNE7S1ZLOlXS3pONLAZykVyUdLek2SZdLWqlKvptLukrSrZIuljSqu3JExD3APGDFYrqkz0u6WdJUSX+TNDKnT86/BP4t6aFGajDNzMysBVpQkxgRVwPPV1n1K+AwIAppewJnRMTsiHgYeADYMscgS0fE9RERwMnAXvW8pI4OEkkn6aKI+A/wvKTNCuu2BL4FvBNYC/hwTn8LcFtEbAZcBXy/mKGk4cAxwD4RsTlwInBkd4WQ9G6gC3imbNU5EbFFRGwM3EMKZktGAe8BdgOOquvVmpmZWWt0dTW+NCF3T3s8IqaWrRoNPFZ4PjOnjc6Py9N71OkDVz4G/Do/PiM/vy0/vykiHgKQdDopIDubFMydmbc5FTinLM91gY2AS3OT/yLArBrHP1jSJ4FXgP0iIsq6CWwk6f+AZYElgYsL6/4eEV3A3ZJWqfP1mpmZ2SAhaQKpGbhkUkRM6mb7kcD/ALtUW10lLbpJ71HHBomSVgDeRwrEghTMhaTD8iblJ7DWCS1PFzA9Irauoxi/iohfdLN+MrBXREyVNB4YV1g3u+yYCxeicOFpkWUYNuwtdRTHzMzMmtLEFDg5IKwZFFaxFrAmMDVXKq0O3CZpS1IN4RqFbVcHnsjpq1dJ71EnNzfvA5wcEW+LiDERsQbwMKnGEFI7/pq5L+J+pMEtkM5ZqQ/gxwvpJfcBK0naGlLzs6QNmyzjUsCs3IT9iUZ2jIhJETE2IsY6QDQzM2uxfmhujohpEbFyjlvGkALAzSLiSeB8YH9Ji0lakzRA5aaImAW8ImmrPKr508B59Ryvk4PEjwHnlqX9jRT4AVxP6ut3Fyl4LG37GrChpFtJNZE/LGYQEXNIQeRPJU0F7gC2abKM/wvcCFwK3NtkHmZmZtZqLQgSc3e364F1Jc2UdGCtbSNiOnAWcDdwEfCVWHAbmC8BJ5AGszwIXFjPS1Ia6GJFksYBh0TEblXWvRoRS/Z7oXph0RGj/Sb3g3lzHh/oIgx5i46oq6+19YKvYxtC6poLsK+8cfXkhr9rl9hufL+WsVEd2yfRzMzMrM8MwTuuOEisIiKmAFNqrGurWkQzMzPrB753s5mZmZlVcE2imZmZmVVwTaKZmZmZVXBNopmZmZlVcE2imZmZmVVwTaKZmZmZVXCQaGZmZmYV3NxsZmZmZhVck2hmZmZmFVyTaGZmZmYVhmBN4rCBLoCZmZmZDT6uSTQzMzPrLTc3m5mZmVmFIdjc7CDRzMzMrLccJJqZmZlZhYiBLkGfc5BoZmZm1luuSTQzMzOzCg4SzczMzKyCRzebmZmZWQXXJJqZmZlZBQ9cMTMzM7MKrkm0dqSBLkCHGLHY6gNdhCFv3pzHB7oIZmbVOUg0MzMzswoeuGJmZmZm5aLLfRLNzMzMrNwQbG4eNtAFMDMzM2t70dX40gNJJ0p6WtJdhbSfS7pX0p2SzpW0bGHdEZIekHSfpPcX0jeXNC2v+62kuoYrNBQkSlojF3impDmS3pfTV8rpWzSSn5mZmdmQ0BWNLz2bDOxalnYpsFFEvAv4D3AEgKQNgP2BDfM+x0laJO/ze2ACsHZeyvOsqu4gUdKawC3AR4DpQOnARMQzwFjgc/XmZ2ZmZma1RcTVwPNlaZdExLz89AagNLXGnsAZETE7Ih4GHgC2lDQKWDoiro+IAE4G9qrn+I30STwS6AI2At4Ani5b/y9g9wbyMzMzMxsaBqZP4meBM/Pj0aSgsWRmTpubH5en96iR5uadgOMi4jGgWh3pIyyIZs3MzMw6R1dXw4ukCZJuKSwT6j2cpP8B5gGnlZKqbBbdpPeokZrEpYFZ3awf0WB+ZmZmZkNDE7fli4hJwKRG95N0ALAbsGNuQoZUQ7hGYbPVgSdy+upV0nvUSE3iY6TOkLVsRWr/NjMzM+ssTdQkNkPSrsC3gT0i4vXCqvOB/SUtlseRrA3cFBGzgFckbZVHNX8aOK+eYzUSJJ4DfFbSRoW0yAX+CLAvcFYD+ZmZmZkNDS0Y3SzpdOB6YN08s8yBwLHAUsClku6QdDxAREwnxWF3AxcBX4mI+TmrLwEnkCrzHgQurOclKeqsHpW0dC7oGOBqYBfgMlIz9JbAHcC2EfFmXRlavxk+YvTQmwZ+EKpz2inrhTmzZ/a8kZlZ0q8fyq///LMNf9eOPPTEQf3FUXdNYkS8DGxNikTHkk7+zsC6wHHADg4QzczMrCO1Zp7EAdXQQJMcKH4D+IaklUiB4jNRb3WkmZmZ2RAUQ/C2fE2PRs4TaJuZmZlZG9QMNqrhIFHSR4G9gbfnpIeAcyPCg1bMzMysM9VxL+Z2U3eQKGkkacj0+0jNzC/m/7cAPirpC6Th2K+1oJxmZmZmg9cQrElsZAqcHwM7AscAq0XE8hGxHLBaTtuBdOs+MzMzs87ST/Mk9qdGmpv3A/4aEQcVEyPiSeAgSaPzNgdV7mpmZmY2hHV4TeLSwJXdrL8ib2NmZmbWWaKr8WWQa6Qm8U7SLV5qWRuY1rvimJmZmbWhIViT2EiQ+F3gXElTIuIfxRWS9gQ+B+zVh2UzMzMzawudPk/iJ4CHgb9Lug+4h3Tv5g1Id12ZBnxS0icL+0REHNhXhe0LklYFfk0alT0bmAEcFBH/6ePjjAG2iYi/1Fh3D3AfMIJ0m8MvA28FLoiIjcr3MTMzM+tPjQSJ4wuP18tL0bvyUhTAoAkSlW6uey5wUkTsn9M2AVYBegwS8/6KSB0Jyp+XGQN8HKgIErMHI2ITSYuS+nPuBdzWyOsxMzOzQWIINjc3cu/mYU0si7Sy8E3YAZgbEceXEiLijoi4RtKSki6XdJukabkJHUljJN0j6ThSEPfesudrSPq5pLvyfvvlrI/K294h6eBaBYqIecC/gXcU0/Nxr8nluU3SNjl9nKQpks6WdK+k03KwamZmZgOl0+/dPARsBNxaY92bwN4R8bKkFYEbJJ2f160LfCYivpybiovPPwJsAmwMrAjcLOlq4HDgkIjYrbsC5UnKdwS+V7bqaWDniHhT0trA6cDYvG5TYEPgCeA6YFvg2npOgJmZmbVAG4xWblQjd1z5I3BCRNzYwvIMJAE/lrQd0AWMJjVDAzwSETcUti0+fw9wekTMB56SdBWpv+PLPRxvLUl3kJrkz4uIC3MAWjIcODY3h88H1imsuykiZgLkPMbgINHMzGzgtEHNYKMaqUn8DPBZSfcAJwCnRMRzrSlWy0wH9qmx7hPASsDmETFX0gxg8byu/FaDxefNNvU+GBGbdLP+YOApUg3lMFJNZ8nswuP5VHkfJU0AJgAMW2QZhg17S5PFNDMzs57EEAwSG5lMe3Xgf0g1XL8EZko6Q9LOLSlZa1wBLCbp86UESVtI2h5YBng6B4g7AG+rM8+rgf0kLSJpJWA74CbgFWCpXpR1GWBWHhTzKaCh/p0RMSkixkbEWAeIZmZmLTYE+yQ2MnDlyYg4KiLWBcYBZwG7ARdJeljS/0pao0Xl7BMREcDewM6SHpQ0HZhI6tt3GjBW0i2kWsV768z2XNJE41NJQehh+VaFdwLzJE3tbuBKN44DDpB0A6mpubw208zMzAaLIXjvZqW4qcmdpaWAj5GmuRlL6st3KfAH4PzoTebWZ4aPGO33oR94kHnrzZk9c6CLYGbto18/lF/58gca/q5d6rgLB/UXRyPNzdUsQbpf89KkN+M14N3AOcBUSev3Mn8zMzOzwa+Tm5tLJA2T9CFJ5wCPAT8DXiLdlm81YBTw+fz/H/uwrGZmZmaDUkQ0vAx23Y5ulvQ94JyIuEvSWsBngQNIAeDLwCRgUkRMK9v1xDz/3y9aUGYzMzOzwaUNagYb1dMUOBOBB4C7gPtz2vWkUc5nRsSbNfaDdE/kWb0sn5mZmdng14FBYtExwB8i4u56No6IC4ALmiqVmZmZWRsZivMk1h0kRsQ3WlkQMzMzs7bVoUHi0HvVZmZmZn1p8E972LB6gsTvFu9Q0oOIiB17UyAzMzOzdtOK5mZJJ5JuXPJ0RGyU05YHzgTGkMZ/fDQiXsjrjiDNXT0f+HpEXJzTNwcmk6Yu/BfwjXrmsq4nSFw/L/VwraOZmZl1ntY0N08GjgVOLqQdDlweEUdJOjw//7akDYD9gQ1JUxJeJmmdiJgP/B6YANxAChJ3BS7s6eD1zJP4yYgYVufS0P2FzczMzKy6iLgaeL4seU/gpPz4JGCvQvoZETE7Ih4mzU6zpaRRwNIRcX2uPTy5sE+3GhndbGZmZmbV9F+fxFUiYhZARMyStHJOH02qKSyZmdPm5sfl6T1ykGhmZmbWS830SZQ0gdQMXDIpIiY1WYRq94GObtJ75CDRzMzMrLeaqEnMAWGjQeFTkkblWsRRwNM5fSawRmG71YEncvrqVdJ71FOfxJOAB+sqspmZmVmHiq5oeGnS+aRbJJP/P6+Qvr+kxSStCawN3JSbpl+RtJUkAZ8u7NOtbmsSI+IzzZTezMzMrKO0oE+ipNOBccCKkmYC3weOAs6SdCDwKLAvQERMl3QWcDcwD/hKHtkM8CUWTIFzIXWMbAZQHdPkWJsbPmK03+R+kH6gWSvNmT2z543MzJJ+/VB+bvftG/6uXeEfVw3qLw73STQzMzPrrQ6944qZmZmZdSMcJJqZmZlZBQeJZmZmZlbONYmApNWA3YG356SHgAsi4vG+LJiZmZlZu+j4IFHS/wLfzfsVR+QcI+nIiPhBXxbOzMzMrB10dJAo6avAD4CbgV+R5uERsAFwMPA9Sc9FxLGtKKiZmZnZoBWDejabptQ9T6Kk+4AXgPdExLyydcOB64BlImLdPi+l9cqSI9f0PIn9YKkRSwx0EYa8Z19/aaCLMOTNneOeQ/1h+IjRA12EIW/unMf7NWp7crtxDX/Xrnr1lEEdWfZ0W76itwKnlweIABExFzgtb2NmZmbWUaJLDS+DXSN9Eh8Flupm/VJ5GzMzM7OOMhT7JDZSk3gs8AVJo8pXSBoNfBE4pq8KZmZmZmYDp5GaxJeAp4B7JZ0K3AsEaeDKJ4D/AC9L+nRxp4g4uY/KamZmZjYoxRAcuNJIkDi58PhLVdZvXrYNpCDSQaKZmZkNaUOxubmRIHGHlpXCzMzMrI21w0CURtUdJEbEVa0siJmZmVm7qnNGwbbiezebmZmZ9VJH1SQWBqCcEhFRPiClFg9UMTMzs07TUUEiaRBKAGcAcwrPuzsLHqhiZmZmHafTmpt3AIiIOcXnZmZmZrawjqpJLB+o4oErZmZmZtV1+jyJZmZmZlZFp8+TiCQBOwFrAytQ2T8xIuJHfVQ2MzMzs7bQ1ck1iZLWBv4OrEftwSsBOEg0MzOzjtLpzc3HAGsB3wauAJ5rSYnMzMzM2kxHDVyp4j3AryPiF60qjJmZmVk76rQpcMrNAR5uVUHMzMzM2tVQrEkc1sC2FwPbtqogZmZmZu2qK9TwUg9JB0uaLukuSadLWlzS8pIulXR//n+5wvZHSHpA0n2S3t+b19RIkPhNYGtJ35I0ojcHNTMzM7PuSRoNfB0YGxEbAYsA+wOHA5dHxNrA5fk5kjbI6zcEdgWOk7RIs8fv7t7ND1VJXhL4GXCUpCeA+WXrIyLWarYwZmZmZu2ohaObFwWWkDQXGAk8ARwBjMvrTwKmkAYW7wmcERGzgYclPQBsCVzf7IFreZQ0pU1bkvRqRCxZ57Z7Af+JiLvz8/HAJRHxRAvLNxnYHngJ6AK+EhHX5/QLIuLsVh3bzMzM+lYrBq5ExOOSfkGKyd4gxSaXSFolImblbWZJWjnvMhq4oZDFzJzWlO5uyzeu2Uzb0F7ABcDd+fl44C5StN5Kh0bE2ZJ2Af4AvKvFxzMzM7MWaGYybUkTgAmFpEkRMamwfjlS7eCawIvAXyV9srssq6Q1Hb420iex7UlaS9JFkm6VdI2k9SRtA+wB/FzSHZK+DYwFTsvPl5A0Q9KKOY+xkqbkx0tK+rOkaZLulPSRnL6LpOsl3Sbpr5J6qtG8GnhHlfJ+T9LNubPqpHzHGyRNkfRTSTdJ+o+k9/bZSTIzM7OGRaiJJSZFxNjCMqks252AhyPimYiYC5wDbAM8JWkUQP7/6bz9TGCNwv6r04sKr7qDREkrSFq/LG1NScdIOq23I2j6ySTgaxGxOXAIcFxE/Bs4n1Srt0lE/BS4BfhEfv5GN/n9L/BSRLwzIt4FXJGDye8CO0XEZjmvb/ZQrt2BaVXSj42ILXJn1SWA3QrrFo2ILYGDgO/3kL+ZmZm1UETjSx0eBbaSNDJXFO0I3EOKWw7I2xwAnJcfnw/sL2kxSWuSbqN8U7OvqZF5En8DrEPqAEmuHbsGWC2v30/S+yLi6mYL00q5vNuQqmpLyYv1MtudSKOIAIiIFyTtBmwAXJePM4LaHUZ/Lum7wDPAgVXW7yDpMFJH1eWB6cA/8rpz8v+3AmN69zLMzMysN1px7+aIuFHS2cBtwDzgdlKF15LAWZIOJAWS++btp0s6i9R9bh5pvEP5IOO6NRIkbg2cWni+HylA/CBwB3ApcBip6XQwGga8GBGbNLHvPBbUui5eSBeVbf0CLo2Ij9WR76G1BqhIWhw4jjTs/TFJE8uOPTv/P58q72Oxn8OI4SswfNGl6iiOmZmZNaNVo5sj4vtUthjOJtUqVtv+SODIvjh2I30SVyFFqyUfAG6JiIsi4klgMrBpXxSqFSLiZdJw8H0BlGycV78CFKOo8uczgM3z448U0i8Bvlp6kjuY3gBsK+kdOW2kpHWaKHIpIHw214Lu08jOxX4ODhDNzMxaq1WTaQ+kRoLEuaR+cSXbA1cVnr8IrNAHZeorIyXNLCzfBD4BHChpKqnpds+87RnAoZJul7QWKeA9vjRwBfgB8BtJ17Dw3JD/ByyXB5ZMBXaIiGdIo6NPl3QnKWhcr9HCR8SLwB9JfRX/DtzcaB5mZmbWP6KJZbBT1NlzUtKNwOvA+0gDLc4FPhQRF+X1E4EJEbFazUxsQCw5cs12uBbb3lIjluh5I+uVZ19/aaCLMOTNnfP4QBehIwwf0fTUdVanuXMe79equn+P+kjD37XbzPrboK5ObKRP4u9INWwvkAZSPES6FUzJdlQfoWtmZmY2pLXwjisDpu4gMSJOltQF7E26S8iP85w9SFoBWIY00MLMzMyso3QNdAFaoK4gMd8cejTwr4g4tXx9RDzHgoEdZmZmZh0lqt7spL3VO3BlOKl5udpcfmZmZmYdrSsaXwa7umoSI+JNSc8Cr7W4PGZmZmZtp6uDaxIB/sXCt4UzMzMzM1Jzc6PLYNdIkHgYMErSSZLeme8IYmZmZmZDUCNT4DxNmvtxY+CTAIV7IJdERDSSp5mZmVnb69jRzdnJtMcE4WZmZmb9qh2ajxvVyDyJ41tYDjMzM7O21ek1iWZmZmZWxVAMEhsZuIKkRSR9WtKpki6VtGlOXy6n+2aUZmZm1nGG4ujmumsSJY0ELgG2Ic2XOBJYLq9+GTgKOBH4bh+X0czMzGxQ6xr8MV/DGqlJnAiMJd27+e2wIASOiPnAOcD7+7JwZmZmZu2gCzW8DHaNBIn7ApMi4jyqN70/AIzpi0KZmZmZtZNoYhnsGhm4showtZv1rwNL9a44ZmZmZu1nKA5caSRIfA7obmDKhsATvSuOmZmZWfvpqrzBSNtrpLn5cuAzeQDLQiStCXwWuKivCmZmZmbWLjq9ufkHwC3AzcDppNe3q6SdgS8Cs4Gf9HkJzczMzAa5jm5ujogHJO1Imubmhzn5kPz/XcCnIuKxPi6fmZmZ2aA3FKfAaeiOKxFxK7CxpI2A9UnT4NwfEbe3onBmZmZm7aAdprRpVFO35YuIu0i1h2ZmZmYdrx36GDaq4SBR0mrA7qQJtQEeAi6IiMf7smBmZmZm7aLjm5sl/S/ptnvDy1YdI+nIiPhBn5XM+syrrz880EUwszYx99mHBroIHWHkiMUHughmPap7ChxJXyWNcL4D+DiwCbAp8Imc9r28jZmZmVlH6WpiqYekZSWdLeleSfdI2lrS8pIulXR//n+5wvZHSHpA0n2SenW75EbmSfwacBOwbUScERF3RsTUiDgd2Ba4NW9jZmZm1lFaOE/ib4CLImI9YGPgHuBw4PKIWJs0j/XhAJI2APYn3eBkV+A4SYs0+5oaCRLfCpweEfPKV0TEXOC0vI2ZmZlZR+lS40tPJC0NbAf8CSAi5kTEi8CewEl5s5OAvfLjPYEzImJ2RDwMPABs2exraiRIfJTu7828VN7GzMzMrKO0qLn57cAzwJ8l3S7pBElvAVaJiFkA+f+V8/ajgeKc1TPp/pbK3WokSDwW+IKkUeUrJI0m3XXlmGYLYmZmZtaumgkSJU2QdEthmVCW7aLAZsDvI2JT4DVy03IN1eonm56dp+boZkmfLkt6CXgKuFfSqcC9+cAbkAav/Ad4udmCmJmZmbWraGIKnIiYBEzqZpOZwMyIuDE/P5sUJD4laVREzMqVd08Xtl+jsP/qwBONlyzpbgqcyaQgsNrL/lKVtM2BPwMnN1sYMzMzs3bUins3R8STkh6TtG5E3AfsCNydlwOAo/L/5+Vdzgf+IumXwGrA2qRBx03pLkjcodlMzczMzDpJK4LE7GvAaZJGkG5g8hlSd8GzJB1IGg+yL0BETJd0FimInAd8JSLmN3vgmkFiRFzVbKZmZmZmnaRVt+WLiDuAsVVW7Vhj+yOBI/vi2E3du9nMzMzMFvBt+dKw64+T2rhXoLK/YkTEgX1UNjMzM7O20MLm5gFTd5AoaRtSh8jlu9ksAAeJZmZm1lGGYpDYyDyJx5DOwZ7A8hExrMrS9K1fzMzMzNpVC2/LN2AaaW7eAPheRPyjVYUxMzMza0ed3idxFjC3VQUxMzMza1ed3tx8AvBxSW5SNjMzMyvo9Obmn5Bm775e0u+BGUDFBI0RcXXfFM3MzMysPXS1RdjXmEaCxCVI095sTqpVLCdSYOyaRjMzM7M210iQ+Dvgo8DfgWuAF1pRIDMzM7N2MxT7JDYSJO4JnBgRn29VYczMzMza0dBrbG4sSBRwc6sKYmZmZtauhmJNYiOjm6cAW/XFQSWtIOmOvDwp6fHC8xF5mz0kHd5DPuMlHVsjPSTtWEjbO6ft0wflHyPprt7mUyXfqq8nr/uXpGX7+phmZmbWe11qfBnsGqlJPAi4QtI3gWMjYk6zB42I54BNACRNBF6NiF+U1ktaNCLOJ90GsFnTgI8Bl+fn+wNTe5Ffn8mvb14j+0TEB1tVHjMzM+udoTi6uZGaxCuBJYGfA69KekTSQ2XLg80WRNJkSb+UdCXw02KtmqTdJd0o6XZJl0lapY4srwG2lDRc0pLAO4A7CsfbXNJVkm6VdLGkUTl9iqRfSbpa0j2StpB0jqT7Jf1fIf9FJZ0k6U5JZ0saWUe+P5Z0FfANSftKukvSVEnFaYNWk3RRPt7PCuWdIWnFXIt5b7Vjm5mZ2cDo9HkSH6X1r2kdYKeImC9pfCH9WmCriAhJnwMOA77VQ14BXAa8H1iGVCu5JoCk4aR7Ue8ZEc9I2g84Evhs3ndORGwn6RvAeaRpf54HHpT0q7zNusCBEXGdpBOBL0v6TQ/5LhsR2+cyTAPeHxGPlzUjbwJsCswG7pN0TEQ8VvbaKo4N/AIzMzMbEEOxT2LdQWJEjGthOUr+GhEVE3QDqwNn5lq5EcDDdeZ3BvB1UpD4LeA7OX1dYCPgUkmQ5nacVdiv1Mw9DZgeEbMAJD0ErAG8CDwWEdfl7U7Nx7moh3zPLDy+Dpgs6SzgnEL65RHxUj7e3cDbgPIgsdqxHSSamZkNkE5vbu4Pr9VIP4bUD/KdwBeAxevJLCJuIgVtK0bEfwqrRAr+NsnLOyNil8L62fn/rsLj0vNSYF1+NUQd+f739UXEF4HvkoLOOyStUHZsSHe0qRbIVzv2QiRNkHSLpFsmTZpUJQszMzPrK53e3AyApO2AXYBVgKMj4t7c528z4M6IeLFviwikmsDH8+MDGtz3CODNsrT7gJUkbR0R1+fm53UiYnoD+b61tD9pgMy1jeQraa2IuBG4UdLupGCxN8deSERMAkrRYTtci2ZmZm1rKDY3112TKGkRSWeSBrB8h9TPbrW8eh7pTixf7usCZhOBv0q6Bni2kR0j4sKIuLIsbQ6wD2mAzFTSgJZtGizTPcABku4Elgd+32C+P5c0LU+lczWNjbyuOHaDZTczM7M+1EU0vAx2iqivkJK+A/yQ1LfvIlKgslNEXJHX/wlYNyLe06KyGmmORuCCiNiogd0G/5VoZoPC3GcfGugidIQVx+w80EUY8l569cF+nYnw4DH7N/xd+6sZZwzq2RIb6ZP4aeDkiPgN1Wvz7gHW6pNSmZmZmbWRriaWwa6RIHEMcH03618ElutNYaxnETGjwVpEMzMza7Fo4t9g18jAlVdI/d9qeQfwTO+KY2ZmZtZ+2qFmsFGN1CReC3xSeQLAIknLkQayXFmxl5mZmdkQNxQHrjQSJB4JrA1cAeyW0zaW9AXgNuAtwFF9WzwzMzMzGwh1B4kRcQvwYWA94M85+Rek6VeWAPaOiLv7vIRmZmZmg1wrJ9PO0xDeLumC/Hx5SZdKuj//v1xh2yMkPSDpPknv781ramgy7Yj4V56CZWdgfdIdRu4HLo6I13tTEDMzM7N21eLm42+QZpFZOj8/nHQb36MkHZ6ff1vSBsD+wIakuawvk7ROjVse96jhO65ExGzggryYmZmZdbxWDVyRtDrwIVK3v2/m5D2BcfnxScAU4Ns5/Ywcqz0s6QFgS7qfnaamwXbvZjMzM7O208IpcH4NHMbCcegqETELIP+/ck4fDTxW2G5mTmtKtzWJkq5oML+IiB2bLYyZmZlZO2qmJlHSBGBCIWlSREwqrN8NeDoibpU0rp4sq6Q13Q7eU3PzOGAuMKfO/Ab/eG4zMzOzPtbM5Ng5IJzUzSbbAntI+iCwOLC0pFOBpySNiohZkkYBT+ftZwJrFPZfHXii4YJlPTU3zyNFpZcBnwCWiYilulmW7j47MzMzs6GnFbfli4gjImL1iBhDGpByRUR8EjgfOCBvdgBwXn58PrC/pMUkrUmauvCmZl9TTzWJo0n3bB4PnAs8Lelk4MSIuK/Zg5qZmZkNJV3Rr42pRwFnSToQeBTYFyAipks6C7ibVNH3lWZHNgMo6nxRkrYk3VVlP9IQ7JuAPwFnRsQrzRbA+oW7AZhZXeY++9BAF6EjrDhm54EuwpD30qsPVuuf1zKffNuHG/6uPfWRc/q1jI1qZDLtmyLii8AoUu3ia8AfgCckfbJF5TMzMzMb9IbibfmamSfxTeA0STNITeo7AW/v43KZmZmZtY1mBq4Mdg0FiZJWY0EfxbVJI2Z+woLb9JmZmZl1nFZNpj2QegwSJQ0nzeD9GWAXYD5p9MzBpNvxDcXzYmZmZla3dmg+blRPk2n/Fvg4sBxwJ/At4NSIeL4fymZmZmbWFjqxufmrwBvA6cBtefvxUs3BOBERv+q74pmZmZkNfkOxWbWePolLkGoTP17HtgE4SDQzM7OOUu+Ugu2kpyBxh34phZmZmZkNKt0GiRFxVX8VxMzMzKxdddzAFRsaRi27wUAXoSM88/pLA12EIW+FJZYa6CIMefM9YUW/eG3OmwNdBOtjQ/Evx0GimZmZWS914uhmMzMzM+uBm5vNzMzMrEInjm42MzMzsx64T6KZmZmZVXCfRDMzMzOr4D6JZmZmZlbBfRLNzMzMrIJrEs3MzMysgvskmpmZmVmFLjc3m5mZmVm5oRciOkg0MzMz6zX3STQzMzOzCg4SzczMzKzCUJwCZ9hAF8DMzMzMBh/XJJqZmZn1kpubzczMzKzCUJwn0c3NZmZmZr0UEQ0vPZG0hqQrJd0jabqkb+T05SVdKun+/P9yhX2OkPSApPskvb83r8lBopmZmVkvdRENL3WYB3wrItYHtgK+ImkD4HDg8ohYG7g8Pyev2x/YENgVOE7SIs2+JgeJZmZmZr3UiprEiJgVEbflx68A9wCjgT2Bk/JmJwF75cd7AmdExOyIeBh4ANiy2dfkINHMzMysl5qpSZQ0QdIthWVCrfwljQE2BW4EVomIWZACSWDlvNlo4LHCbjNzWlM8cMXMzMysl5oZuBIRk4BJPW0naUngb8BBEfGypJqbVi1ak9q6JlHS3pLuKFu6JH2gD48xQ9KKVdL3kHR4D/uuJunsKuljJL2Ry3u3pOMlDcvpd/VV2c3MzKx/dEU0vNRD0nBSgHhaRJyTk5+SNCqvHwU8ndNnAmsUdl8deKLZ19TWQWJEnBsRm5QW4DjgGuDifjj2+RFxVA/bPBER+9RY/WAu87uADVjQn8DMzMzaTDTxrydKVYZ/Au6JiF8WVp0PHJAfHwCcV0jfX9JiktYE1gZuavY1tXWQWCRpHeB7wKciokvSOEkXFNYfK2l8fryFpH9LmirpJklLSVpE0i8kTZN0p6SvFbL/mqTb8rr1ch7jJR2bH0+W9Nuc50OS9snpPdYMRsQ84N/AO8pezxhJ1+Tj3iZpm5w+TtIUSWdLulfSaeqm3tnMzMxar0U1idsCnwLeV2gx/SBwFLCzpPuBnfNzImI6cBZwN3AR8JWImN/saxoSfRJzVexfgEMi4tEeth0BnAnsFxE3S1oaeAOYAKwJbBoR8yQtX9jt2YjYTNKXgUOAz1XJehTwHmA9UiRf0cxcozwjgR1JAW7R08DOEfGmpLWB04Gxed2mpOHtTwDXkS6ia+s5npmZmfW9VkymHRHXUr2fIaTYodo+RwJH9sXxh0SQCPwImB4RZ9Sx7brArIi4GSAiXgaQtBNwfK7ZIyKeL+xT6gNwK/DhGvn+PSK6gLslrVJHOdaSdAepQ+l5EXFhHrlUMhw4VtImwHxgncK6myJiZi73HcAYHCSamZkNmHr7GLaTtg8SJY0DPgJsVrZqHgs3py9e2oXqI31qpQPMzv/Pp/Y5m114XE/zb6lPYi0HA08BG5Nex5s1jlW1THkY/QSApZdYlZEjlivfxMzMzPqIb8s3yOTb0PwZ+HSeZLLoEWCD3HlzGRZUy94LrCZpi5zHUpIWBS4BvpgfU9bcPBCWIdV4dpH6IzQ0Y3pETIqIsREx1gGimZlZa7VqdPNAaveaxC+SJpD8fdnYjZ9ExJmSzgLuBO4HbgeIiDmS9gOOkbQEqT/iTsAJpCbdOyXNBf4IHNtvr6TSccDfJO0LXAm8NoBlMTMzs24MxZpE1XNbGGtvo5bdwG9yP3jm9ZcGughD3gpLLDXQRRjy5kfXQBehI7z4pn/3t9q8OY/368wfa66wccPftQ8/N3VQz07S1s3NZmZmZtYa7d7cbGZmZjbguoZgc7ODRDMzM7NeGord9xwkmpmZmfWSaxLNzMzMrIJrEs3MzMysQjvMe9goB4lmZmZmvTQU50l0kGhmZmbWS25uNjMzM7MKHrhiZmZmZhVck2hmZmZmFTxwxczMzMwquCbRzMzMzCq4T6KZmZmZVXBNopmZmZlVcJ9EMzMzM6vgybTNzMzMrIJrEs3MzMyswlDskzhsoAtgZmZmZoOPaxLNzMzMesl9Es3MzMyswlBsbnaQaGZmZtZLDhLNzMzMrMLQCxFBQzHytfYnaUJETBrocgxlPsf9w+e59XyOW8/nuDN5dLMNVhMGugAdwOe4f/g8t57Pcev5HHcgB4lmZmZmVsFBopmZmZlVcJBog5X7vrSez3H/8HluPZ/j1vM57kAeuGJmZmZmFVyTaGZmZmYVHCRa0yStKukMSQ9KulvSvySt0w/HfTX/P0bSXQ3uO1nSPq0pWf8ZqHNfOP44Sdv01/H6gqT5ku6QNF3SVEnflNSnn4GS9pK0QeH5DyXt1GReB0ka2eA+4yRd0MzxBkLhPSkthzeZT9PnOe/fVuetL0kKSUcXnh8iaeIAFskGEU+mbU2RJOBc4KSI2D+nbQKsAvxnAIs25PX23EtaJCLm13pep3HAq8C/G9xvIL0REZsASFoZ+AuwDPD9PjzGXsAFwN0AEfG9XuR1EHAq8HqvSzV4/fc96Y1a57nJa7vTzAY+LOknEfHsQBfGBhfXJFqzdgDmRsTxpYSIuAO4XdLlkm6TNE3SnvDfWr97JP0x1+RcImmJvO4dki7LtTu3SVorpx8q6WZJd0r6QXeFkbSIpJ8Xtv9CTpekY3Nt2z+BlVtzOvpVrXO/SLE2JL/u8fnxDEnfk3QtsG+V57tIuj6f/79KWrKw3w8K7+d6ksYAXwQOzrU/7+23V95HIuJp0rxvX83XSK3rZ8lq13Ne9+m87VRJp+Sa1T2An+fzslap5lrSBySdVdh3nKR/5Me/l3RL/rv4QU77OrAacKWkK3NarfdoV0n35vfyw/1yAltI0jKS7pO0bn5+uqTP58evSjo6n4PLJa2U0//bQtDAtT2kzlsvzCMNSjm4fIWklST9Lf9d3Cxp25w+TdKy+W/nOUmfzumnqBc1ujb4OEi0Zm0E3Fol/U1g74jYjBTMHC1Jed3awO8iYkPgReAjOf20nL4xsA0wS9IuefstgU2AzSVt1015DgReiogtgC2Az0taE9gbWBd4J/D5nH+7q3Xue/JmRLwnIs4oPgcuA74L7JTft1uAbxb2ezan/x44JCJmAMcDv4qITSLimmZfyECKiIdIn4ErU/v6qXo9S9oQ+B/gffm6/UZE/Bs4Hzg0n5cHC4e7FNhK0lvy8/2AM/Pj/4mIscC7gO0lvSsifgs8AewQETtIWpEq75GkxYE/ArsD7wVW7fsz1VJLaOHm5v0i4iXgq8BkSfsDy0XEH/P2bwFuy+fgKmrXAnd7bQ+B89bXfgd8QtIyZem/If2db0H6vD4hp18HbAtsCDxEOocAWwE3tL641l/c3Gx9TcCPc0DXBYwmNYMCPJxrvCAFOWMkLQWMjohzASLiTUi1JsAuwO15+yVJQePVNY67C/AuLehvuEzefjvg9Nzk9ISkK/rkVbanM2s83wrYALgux/MjgOsL252T/7+VoVfjUvoBU+v6mUn16/l9wNml5rmIeL67g0TEPEkXAbtLOhv4EHBYXv1RSRNIn8ejSO/FnWVZ1HqP1iP9Xd0PIOlU2uvOGFWbmyPiUkn7koKXjQurulhw3Z7KgmuzXE/Xdruftz4VES9LOhn4OvBGYdVOwAYLfuezdP7Mvob02foI6cfjBEmjgecj4tX+K7m1moNEa9Z0oNoAkE8AKwGbR8RcSTOAxfO62YXt5gNLsOBLupyAn0TEH+osj4CvRcTFCyVKH2To3Xe91rmfx8KtA4uXrX+txnMBl0bEx2ocr/S+zWcIfWZIejvpNT1N7etnPNWvZ9H4dXUm8BXgeeDmiHgl11YeAmwRES9Imkzl+wY13iOlvqhD7fpGaUDR+qSAZXlSsF5Nrdfe7bU9VM9bL/0auA34cyFtGLB1RBQDRyRdTbqW30qqUd+b9JnUlq0KVpubm61ZVwCLlfoKAUjaAngb8HT+Qt0hP68pIl4GZkraK+exmNKIzouBzxb6D41WGmxQy8XAlyQNz9uvk5v2rgb2V+pzNorUZNjuap37RUi/+hfLzUY71pnfDcC2kt6R8xqpnkdKvwIs1XjRB4fcl+144NhIk8XWun6Wofr1fDmpBnCFvP3yOb278zIF2IzU7aFU07U0KaB5SdIqwAcK2xfzqvUe3QusqdyPF6gV6Lebg4F7SK/nxNL7QvrOKv1A+jhwbQ/5dNp5a1quDT+L1PWi5BJS0z/w3+CaiHgMWBFYO3fbuJb0Y8dB4hDjINGakr9Y9wZ2VpqGZTowEfgXMFbSLaRaxXvryO5TwNcl3UkaLbtqRFxCGn16vaRpwNl0H5ScQBpRepvStDh/INV6nQvcD0wjNYtc1ehrHWy6OfdPkD7k7yT187y9ZiYL5/cMMB44Pb8HN5Ca47rzD2BvtdfAlVL/t+mkvmqXAKUBUbWun9Oocj1HxHTgSOAqSVOBX+Z8zgAOlXR7IQAh7zOfNPL5A/l/ImIq6X2aDpxI6utVMgm4UNKVtd6j3D1jAvBPpQEYj/T+NPWr8j6JR+Ug7nPAt3J/16tJ/QohBdQbSrqV1OT/w+4yH8LnrVWOJgV/JV8nXf93SrqbNGCt5EYWzKZwDakrRk9Bu7UZ33HFzMzagqRXI2LJgS6HWadwTaKZmZmZVXBNopmZmZlVcE2imZmZmVVwkGhmZmZmFRwkmpmZmVkFB4lmZmZmVsFBopl1HElvlzRJ0r2SXpf0gqS7JZ2UJ80ubTexNNF7L493UL57i5lZ2/DoZjPrKJLGkiZVnwucTJrIeglgHWB34LyI+GreNoCTImJ8L485A5gREeN6k4+ZWX8aMvdhNTOr0/eBkcCmEXFHcYWkrwKrDkShzMwGGzc3m1mnWRt4rjxABIiIroh4QtKYXIsIcICkKC2lbSXtJ+l8SY9Kmi3pWUl/l/SuYp55n7cB2xfzkTSmtF7S5PKySBqf140rpC0v6Vf5doxvSnpO0q2SDu31WTEzK+OaRDPrNA8C60r6cEScU2ObZ0j3FD+FdF/aSVW2+SrwfF73JLAW6X7A10naLCLuz9t9CvgV8Czpfs/FYzTqr8B2pHtLTyXViK4HjAN+3kR+ZmY1uU+imXUUSVuT+iQOB+4HrgVuBqZExD1l29bskyjpLRHxWlna+sAdwJ8i4suF9BnU6JNY6xh5oMufgR0iYoqkZYAXgd8X8zYzaxU3N5tZR4mI64HNgZOAZYDPAMcBd0u6RtLb68znNQAlS0takVQ7eB/w7hYU/Q1gNvDuUlO1mVkrOUg0s44TEdMiYnxErAKMAQ4gNSu/BzhP0oie8pC0qaQLgFeAl0gB4jPAO4HlWlDmOcBBwEbAw5KmSzpG0o59fSwzM3CQaGYdLiIeiYiTge2B60hB2Jbd7SPprcDVwKbAj4C9gV2AnUlT6vTFZ2tFn/GIOJ4U1H4euA3YB7hM0hl9cDwzs4V44IqZGRARIelGYFtgdA+b7w0sCewREVcWV0hagdQsvFD23eT1PLB8lfSqzd4RMQs4AThB0iKkwTUfk3R0RNzcQ7nNzOrmmkQz6yiSdpZU8QNZ0hKk2kCAu/P/r1I9gJtf2q0sj89TfZ7FWvkA/AfYWtLIQj7LkfpKFvMeWdwGICLmA3fmp7XyNzNrimsSzazT/ApYQdL5wDTgdWAN4OOku66cHBHT8rY3ADtJ+jbwKKnC8QzgwrzfKZKOBV4g1UB+kDTFTvln6w3AgZJ+BNwDdAH/yINfjgVOBa6QdAqwLKk5+REWDjjXAa6SdC5wVz7m+sCXgIdJfSrNzPqMp8Axs44iaRdgT9IgldGkoOwlUo3cKcDkiOjK264N/A7YClgKICKU120H/BjYmFSzeB3wbVLQNyYixhSOuTLwe2CHfDwBa0bEjLz+UNK8i6sCD5HmPOxi4SlwVgC+m/MYAywGPA5cAPw0N0ObmfUZB4lmZmZmVsF9Es3MzMysgoNEMzMzM6vgINHMzMzMKjhINDMzM7MKDhLNzMzMrIKDRDMzMzOr4CDRzMzMzCo4SDQzMzOzCg4SzczMzKyCg0QzMzMzq/D/0kHoGWSbkuEAAAAASUVORK5CYII=\n",
      "text/plain": [
       "<Figure size 720x360 with 2 Axes>"
      ]
     },
     "metadata": {
      "needs_background": "light"
     },
     "output_type": "display_data"
    }
   ],
   "source": [
    "#plt.rcParams['figure.figsize'] = [10, 5]\n",
    "fig, ax = plt.subplots(1, 1, figsize = (10,5))\n",
    "ax = sns.heatmap(df_wide)\n",
    "ax.set_ylabel('Membership Type',fontdict= { 'fontsize': 18})\n",
    "ax.set_xlabel('Status',fontdict= { 'fontsize': 18})\n",
    "ax.set_title('Heatmap: Membership Type - Status', fontdict = {'fontsize':20, 'fontweight':'bold'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "039675a9-096d-4209-9de4-db47708f4b75",
   "metadata": {},
   "outputs": [],
   "source": [
    "#remove New to Active subjects as those are irrelevant according to Travis\n",
    "contactActivityDetails_sansNtA=contactActivityDetails.loc[~contactActivityDetails['Subject'].str.contains('New to Active'),]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4bcc6435-78bd-4aad-896d-61e4a1a4b93a",
   "metadata": {},
   "outputs": [],
   "source": [
    "#merge encompassing to contactActivityDetails on email of the member; encompassing:contactActivityDetails = 1:many\n",
    "merged_df = pd.merge(encompassing, contactActivityDetails_sansNtA, how = 'left', left_on = 'Email', right_on = 'Target Email')\n",
    "merged_df.sort_values(by = ['Email','Activity Date'],na_position = 'last', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "0062ff1b-d40e-4e6e-abff-c9f76b5e6f73",
   "metadata": {},
   "outputs": [],
   "source": [
    "#SECOND ITERATION OF merged_df... this one w/o removal of any of the Subject types as there is suspected data loss\n",
    "merged_df2 = pd.merge(encompassing, contactActivityDetails, how = 'left', left_on = 'Email', right_on = 'Target Email')\n",
    "merged_df2.sort_values(by = ['Email','Activity Date'],na_position = 'last', inplace = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "98cf8c42-3d84-4ad8-8fac-8c6756a9823a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2572, 28)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d62f1fcc-2d3f-4ff2-9b97-b9f344a8795c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2004, 12)"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "contactActivityDetails.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "89566f3a-81c3-4a6e-aecd-c2e749eab3e7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array(['Trial Membership', 'Zucchini Plan', 'Lettuce Plan', 'Carrot Plan',\n",
       "       'Apple Plan', 'Avocado Plan'], dtype=object)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df['Membership Type'].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "f54c8177-e390-4454-97e6-be2556728645",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df['membership_hierarchy'] = merged_df['Membership Type'].replace(['Trial Membership', 'Zucchini Plan', 'Lettuce Plan', 'Carrot Plan',\n",
    "       'Apple Plan', 'Avocado Plan'],[0,1,2,2,2,2])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "a370bbdc-9f82-4800-8184-8fa88cecb0d6",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df2['membership_hierarchy'] = merged_df2['Membership Type'].replace(['Trial Membership', 'Zucchini Plan', 'Lettuce Plan', 'Carrot Plan',\n",
    "       'Apple Plan', 'Avocado Plan'],[0,1,2,2,2,2])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98b95598-a4f2-4d1e-86b4-1ee436a707e8",
   "metadata": {},
   "source": [
    "Trying to tell a story of the user journey by looking at events, dates, conversions and status changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "347b9ae5-557e-4c34-b5da-fa3970b016c9",
   "metadata": {},
   "outputs": [],
   "source": [
    "merged_df.to_csv('./contacts_encompassing_merged_sansNtA.csv',index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "72804207-6fe0-4ab0-bd3d-cace74ea8ddb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#analyze the keys (group names, in this case email address) to determine if there was an upgrade of some kind\n",
    "grouped_hier = merged_df.groupby(['Email'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "4c920020-c6eb-4de8-8c2b-b06fde6b7c51",
   "metadata": {},
   "outputs": [],
   "source": [
    "groupby_ser = pd.Series(grouped_hier['membership_hierarchy'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "228a0dba-5fcc-48fb-b77e-2f7102c61dbb",
   "metadata": {},
   "outputs": [],
   "source": [
    "#store email addresses and hierarchy value in a list... the hierarchy was assigned at the \"replace\" step above\n",
    "hier_list = []\n",
    "email_list = []"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "17a68044-4132-4344-b200-cd3930c902c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#place this into a list, the pair it to a list of emails to make a dictionary\n",
    "for index, value in groupby_ser.items():\n",
    "    hier_list.append(set(value[1].values))\n",
    "    email_list.append(value[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "e78743a1-8c80-4882-9655-bb3c00ec3d90",
   "metadata": {},
   "outputs": [],
   "source": [
    "#make a dictionary and a data frame of the merged lists\n",
    "res = dict(zip(email_list, hier_list))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "40f3d8d9-1231-4287-b3b3-d50a3cdffd61",
   "metadata": {},
   "outputs": [],
   "source": [
    "#determine the different journey types posted to the contacts table: first, sort by activity date, will need to account for initial membership type\n",
    "#merged_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "27eb87ae-ccce-4b3a-b870-fe4c55c49187",
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted_df = merged_df.sort_values(by=['Email','Activity Date']).reset_index(drop = True)\n",
    "sorted_df2 = merged_df2.sort_values(by=['Email','Activity Date']).reset_index(drop = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "26961f49-5103-4d04-a348-f15bfea1cafa",
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted_df.loc[:,['Email','Activity Date','Subject','Membership Type','Start Date', 'End Date','Status']].to_csv('./sorted_df.csv', index = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "226908ae-bb3e-4453-a6d2-b99ea360db92",
   "metadata": {},
   "outputs": [],
   "source": [
    "sorted_df2.loc[:,['Email','Activity Date','Subject','Membership Type','Start Date', 'End Date','Status']].to_csv('./sorted_df2.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "749b91a9-3ee1-4bd7-b97f-a97dc9d249d5",
   "metadata": {},
   "source": [
    "Isolate \n",
    "- members on non-working trial (current)\n",
    "- members on working trial (current)\n",
    "- members on non-working trial that converted (historical)\n",
    "- members on working trial that convereted (historical)\n",
    "- members on non-working trial that did not convert (historical)\n",
    "- members on workig trial that did not convert (historical)\n",
    "- trailing 30-day active full member-owners \n",
    "- trailing 30-day cancelled full member-owners\n",
    "\n",
    "Possible array of status: trial, converted member owner, expired trial, cancelled membership, active member owner. Creating a table of this kind could be a cartesian plane of 365*3*1992 = 2,181,240 (#of historical member owners). Apparently pandas can handle this"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "f6e626bf-280a-4abc-8b2f-9ce514132a8d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1992, 16)"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "encompassing.drop_duplicates(subset = 'Email').shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8940e5a7-559b-4ff4-aced-c0bbd2853c15",
   "metadata": {},
   "source": [
    "Survey the Activty Report; this may be the only table I need"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7af323cd-4dd6-401e-98fd-c4d0df7ed30d",
   "metadata": {},
   "outputs": [],
   "source": [
    "activityReport.columns = [i.replace(' ','_')+'_act' for i in list(activityReport.columns)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "9e39eff9-9eaa-41ae-bec5-91444b7ced71",
   "metadata": {},
   "outputs": [],
   "source": [
    "encompassing.columns = [i.replace(' ','_')+'_enc' for i in list(encompassing.columns)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "bf3fff70-b1b1-4537-9ff5-e750e27098d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "#activityReport['Subject'].unique()\n",
    "#print(activityReport.columns)\n",
    "#print(encompassing.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "def23b8e-af98-45bf-a514-d36657bae713",
   "metadata": {},
   "outputs": [],
   "source": [
    "#extract date portion\n",
    "#print(activityReport.dtypes)\n",
    "#print(encompassing.dtypes)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "10a1f7c6-0142-488c-ab44-deca82cbee8b",
   "metadata": {},
   "outputs": [],
   "source": [
    "activityReport = activityReport.assign(Activity_Date_DT_act = pd.to_datetime(activityReport['Activity_Date_act'], format = '%Y-%m-%d %H:%M'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "fe377c19-504a-4e7e-9aa0-473b1691e997",
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "time data 1/22/2022 doesn't match format specified",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\arrays\\datetimes.py:2211\u001b[0m, in \u001b[0;36mobjects_to_datetime64ns\u001b[1;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object, allow_mixed)\u001b[0m\n\u001b[0;32m   2210\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m-> 2211\u001b[0m     values, tz_parsed \u001b[38;5;241m=\u001b[39m \u001b[43mconversion\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mdatetime_to_datetime64\u001b[49m\u001b[43m(\u001b[49m\u001b[43mdata\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mravel\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mK\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   2212\u001b[0m     \u001b[38;5;66;03m# If tzaware, these values represent unix timestamps, so we\u001b[39;00m\n\u001b[0;32m   2213\u001b[0m     \u001b[38;5;66;03m#  return them as i8 to distinguish from wall times\u001b[39;00m\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\tslibs\\conversion.pyx:360\u001b[0m, in \u001b[0;36mpandas._libs.tslibs.conversion.datetime_to_datetime64\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mTypeError\u001b[0m: Unrecognized value type: <class 'str'>",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "Input \u001b[1;32mIn [13]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[0m encompassing \u001b[38;5;241m=\u001b[39m encompassing\u001b[38;5;241m.\u001b[39massign(Start_Date_DT_enc \u001b[38;5;241m=\u001b[39m \u001b[43mpd\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mto_datetime\u001b[49m\u001b[43m(\u001b[49m\u001b[43mencompassing\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mStart_Date_enc\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mformat\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43m \u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43m%\u001b[39;49m\u001b[38;5;124;43mY-\u001b[39;49m\u001b[38;5;124;43m%\u001b[39;49m\u001b[38;5;124;43mm-\u001b[39;49m\u001b[38;5;132;43;01m%d\u001b[39;49;00m\u001b[38;5;124;43m \u001b[39;49m\u001b[38;5;124;43m%\u001b[39;49m\u001b[38;5;124;43mH:\u001b[39;49m\u001b[38;5;124;43m%\u001b[39;49m\u001b[38;5;124;43mM\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m)\u001b[49m, Date_Received_DT_enc \u001b[38;5;241m=\u001b[39m pd\u001b[38;5;241m.\u001b[39mto_datetime(encompassing[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mDate_Received_enc\u001b[39m\u001b[38;5;124m'\u001b[39m], \u001b[38;5;28mformat\u001b[39m \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m'\u001b[39m\u001b[38;5;124m%\u001b[39m\u001b[38;5;124mY-\u001b[39m\u001b[38;5;124m%\u001b[39m\u001b[38;5;124mm-\u001b[39m\u001b[38;5;132;01m%d\u001b[39;00m\u001b[38;5;124m \u001b[39m\u001b[38;5;124m%\u001b[39m\u001b[38;5;124mH:\u001b[39m\u001b[38;5;124m%\u001b[39m\u001b[38;5;124mM\u001b[39m\u001b[38;5;124m'\u001b[39m))\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\tools\\datetimes.py:1051\u001b[0m, in \u001b[0;36mto_datetime\u001b[1;34m(arg, errors, dayfirst, yearfirst, utc, format, exact, unit, infer_datetime_format, origin, cache)\u001b[0m\n\u001b[0;32m   1049\u001b[0m         result \u001b[38;5;241m=\u001b[39m arg\u001b[38;5;241m.\u001b[39mmap(cache_array)\n\u001b[0;32m   1050\u001b[0m     \u001b[38;5;28;01melse\u001b[39;00m:\n\u001b[1;32m-> 1051\u001b[0m         values \u001b[38;5;241m=\u001b[39m \u001b[43mconvert_listlike\u001b[49m\u001b[43m(\u001b[49m\u001b[43marg\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_values\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[38;5;28;43mformat\u001b[39;49m\u001b[43m)\u001b[49m\n\u001b[0;32m   1052\u001b[0m         result \u001b[38;5;241m=\u001b[39m arg\u001b[38;5;241m.\u001b[39m_constructor(values, index\u001b[38;5;241m=\u001b[39marg\u001b[38;5;241m.\u001b[39mindex, name\u001b[38;5;241m=\u001b[39marg\u001b[38;5;241m.\u001b[39mname)\n\u001b[0;32m   1053\u001b[0m \u001b[38;5;28;01melif\u001b[39;00m \u001b[38;5;28misinstance\u001b[39m(arg, (ABCDataFrame, abc\u001b[38;5;241m.\u001b[39mMutableMapping)):\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\tools\\datetimes.py:402\u001b[0m, in \u001b[0;36m_convert_listlike_datetimes\u001b[1;34m(arg, format, name, tz, unit, errors, infer_datetime_format, dayfirst, yearfirst, exact)\u001b[0m\n\u001b[0;32m    400\u001b[0m \u001b[38;5;28;01massert\u001b[39;00m \u001b[38;5;28mformat\u001b[39m \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m \u001b[38;5;129;01mor\u001b[39;00m infer_datetime_format\n\u001b[0;32m    401\u001b[0m utc \u001b[38;5;241m=\u001b[39m tz \u001b[38;5;241m==\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mutc\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[1;32m--> 402\u001b[0m result, tz_parsed \u001b[38;5;241m=\u001b[39m \u001b[43mobjects_to_datetime64ns\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m    403\u001b[0m \u001b[43m    \u001b[49m\u001b[43marg\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    404\u001b[0m \u001b[43m    \u001b[49m\u001b[43mdayfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdayfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    405\u001b[0m \u001b[43m    \u001b[49m\u001b[43myearfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43myearfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    406\u001b[0m \u001b[43m    \u001b[49m\u001b[43mutc\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mutc\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    407\u001b[0m \u001b[43m    \u001b[49m\u001b[43merrors\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43merrors\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    408\u001b[0m \u001b[43m    \u001b[49m\u001b[43mrequire_iso8601\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mrequire_iso8601\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m    409\u001b[0m \u001b[43m    \u001b[49m\u001b[43mallow_object\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[38;5;28;43;01mTrue\u001b[39;49;00m\u001b[43m,\u001b[49m\n\u001b[0;32m    410\u001b[0m \u001b[43m\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m    412\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m tz_parsed \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m    413\u001b[0m     \u001b[38;5;66;03m# We can take a shortcut since the datetime64 numpy array\u001b[39;00m\n\u001b[0;32m    414\u001b[0m     \u001b[38;5;66;03m# is in UTC\u001b[39;00m\n\u001b[0;32m    415\u001b[0m     dta \u001b[38;5;241m=\u001b[39m DatetimeArray(result, dtype\u001b[38;5;241m=\u001b[39mtz_to_dtype(tz_parsed))\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\arrays\\datetimes.py:2217\u001b[0m, in \u001b[0;36mobjects_to_datetime64ns\u001b[1;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object, allow_mixed)\u001b[0m\n\u001b[0;32m   2215\u001b[0m         \u001b[38;5;28;01mreturn\u001b[39;00m values\u001b[38;5;241m.\u001b[39mview(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mi8\u001b[39m\u001b[38;5;124m\"\u001b[39m), tz_parsed\n\u001b[0;32m   2216\u001b[0m     \u001b[38;5;28;01mexcept\u001b[39;00m (\u001b[38;5;167;01mValueError\u001b[39;00m, \u001b[38;5;167;01mTypeError\u001b[39;00m):\n\u001b[1;32m-> 2217\u001b[0m         \u001b[38;5;28;01mraise\u001b[39;00m err\n\u001b[0;32m   2219\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m tz_parsed \u001b[38;5;129;01mis\u001b[39;00m \u001b[38;5;129;01mnot\u001b[39;00m \u001b[38;5;28;01mNone\u001b[39;00m:\n\u001b[0;32m   2220\u001b[0m     \u001b[38;5;66;03m# We can take a shortcut since the datetime64 numpy array\u001b[39;00m\n\u001b[0;32m   2221\u001b[0m     \u001b[38;5;66;03m#  is in UTC\u001b[39;00m\n\u001b[0;32m   2222\u001b[0m     \u001b[38;5;66;03m# Return i8 values to denote unix timestamps\u001b[39;00m\n\u001b[0;32m   2223\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m result\u001b[38;5;241m.\u001b[39mview(\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mi8\u001b[39m\u001b[38;5;124m\"\u001b[39m), tz_parsed\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\arrays\\datetimes.py:2199\u001b[0m, in \u001b[0;36mobjects_to_datetime64ns\u001b[1;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object, allow_mixed)\u001b[0m\n\u001b[0;32m   2197\u001b[0m order: Literal[\u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mF\u001b[39m\u001b[38;5;124m\"\u001b[39m, \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mC\u001b[39m\u001b[38;5;124m\"\u001b[39m] \u001b[38;5;241m=\u001b[39m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mF\u001b[39m\u001b[38;5;124m\"\u001b[39m \u001b[38;5;28;01mif\u001b[39;00m flags\u001b[38;5;241m.\u001b[39mf_contiguous \u001b[38;5;28;01melse\u001b[39;00m \u001b[38;5;124m\"\u001b[39m\u001b[38;5;124mC\u001b[39m\u001b[38;5;124m\"\u001b[39m\n\u001b[0;32m   2198\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m-> 2199\u001b[0m     result, tz_parsed \u001b[38;5;241m=\u001b[39m \u001b[43mtslib\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43marray_to_datetime\u001b[49m\u001b[43m(\u001b[49m\n\u001b[0;32m   2200\u001b[0m \u001b[43m        \u001b[49m\u001b[43mdata\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mravel\u001b[49m\u001b[43m(\u001b[49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[38;5;124;43mK\u001b[39;49m\u001b[38;5;124;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2201\u001b[0m \u001b[43m        \u001b[49m\u001b[43merrors\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43merrors\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2202\u001b[0m \u001b[43m        \u001b[49m\u001b[43mutc\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mutc\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2203\u001b[0m \u001b[43m        \u001b[49m\u001b[43mdayfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mdayfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2204\u001b[0m \u001b[43m        \u001b[49m\u001b[43myearfirst\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43myearfirst\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2205\u001b[0m \u001b[43m        \u001b[49m\u001b[43mrequire_iso8601\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mrequire_iso8601\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2206\u001b[0m \u001b[43m        \u001b[49m\u001b[43mallow_mixed\u001b[49m\u001b[38;5;241;43m=\u001b[39;49m\u001b[43mallow_mixed\u001b[49m\u001b[43m,\u001b[49m\n\u001b[0;32m   2207\u001b[0m \u001b[43m    \u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   2208\u001b[0m     result \u001b[38;5;241m=\u001b[39m result\u001b[38;5;241m.\u001b[39mreshape(data\u001b[38;5;241m.\u001b[39mshape, order\u001b[38;5;241m=\u001b[39morder)\n\u001b[0;32m   2209\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mValueError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\tslib.pyx:381\u001b[0m, in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\tslib.pyx:530\u001b[0m, in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: time data 1/22/2022 doesn't match format specified"
     ]
    }
   ],
   "source": [
    "encompassing = encompassing.assign(Start_Date_DT_enc = pd.to_datetime(encompassing['Start_Date_enc'], format = '%Y-%m-%d %H:%M'), Date_Received_DT_enc = pd.to_datetime(encompassing['Date_Received_enc'], format = '%Y-%m-%d %H:%M'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "8ac2dac9-22e6-4335-8431-3d1100f5c4d4",
   "metadata": {},
   "outputs": [
    {
     "ename": "KeyError",
     "evalue": "'Start_Date_DT_enc'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py:3621\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   3620\u001b[0m \u001b[38;5;28;01mtry\u001b[39;00m:\n\u001b[1;32m-> 3621\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43m_engine\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mcasted_key\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   3622\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\index.pyx:136\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\_libs\\index.pyx:163\u001b[0m, in \u001b[0;36mpandas._libs.index.IndexEngine.get_loc\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi:5198\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "File \u001b[1;32mpandas\\_libs\\hashtable_class_helper.pxi:5206\u001b[0m, in \u001b[0;36mpandas._libs.hashtable.PyObjectHashTable.get_item\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mKeyError\u001b[0m: 'Start_Date_DT_enc'",
      "\nThe above exception was the direct cause of the following exception:\n",
      "\u001b[1;31mKeyError\u001b[0m                                  Traceback (most recent call last)",
      "Input \u001b[1;32mIn [14]\u001b[0m, in \u001b[0;36m<cell line: 1>\u001b[1;34m()\u001b[0m\n\u001b[1;32m----> 1\u001b[0m encompassing \u001b[38;5;241m=\u001b[39m encompassing\u001b[38;5;241m.\u001b[39massign(Start_Date_DT_enc \u001b[38;5;241m=\u001b[39m \u001b[43mencompassing\u001b[49m\u001b[43m[\u001b[49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[38;5;124;43mStart_Date_DT_enc\u001b[39;49m\u001b[38;5;124;43m'\u001b[39;49m\u001b[43m]\u001b[49m\u001b[38;5;241m.\u001b[39mdt\u001b[38;5;241m.\u001b[39mdate, Date_Received_DT_enc \u001b[38;5;241m=\u001b[39m encompassing[\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mStart_Date_DT_enc\u001b[39m\u001b[38;5;124m'\u001b[39m]\u001b[38;5;241m.\u001b[39mdt\u001b[38;5;241m.\u001b[39mdate)\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\frame.py:3505\u001b[0m, in \u001b[0;36mDataFrame.__getitem__\u001b[1;34m(self, key)\u001b[0m\n\u001b[0;32m   3503\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39mcolumns\u001b[38;5;241m.\u001b[39mnlevels \u001b[38;5;241m>\u001b[39m \u001b[38;5;241m1\u001b[39m:\n\u001b[0;32m   3504\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_getitem_multilevel(key)\n\u001b[1;32m-> 3505\u001b[0m indexer \u001b[38;5;241m=\u001b[39m \u001b[38;5;28;43mself\u001b[39;49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mcolumns\u001b[49m\u001b[38;5;241;43m.\u001b[39;49m\u001b[43mget_loc\u001b[49m\u001b[43m(\u001b[49m\u001b[43mkey\u001b[49m\u001b[43m)\u001b[49m\n\u001b[0;32m   3506\u001b[0m \u001b[38;5;28;01mif\u001b[39;00m is_integer(indexer):\n\u001b[0;32m   3507\u001b[0m     indexer \u001b[38;5;241m=\u001b[39m [indexer]\n",
      "File \u001b[1;32m~\\Anaconda3\\lib\\site-packages\\pandas\\core\\indexes\\base.py:3623\u001b[0m, in \u001b[0;36mIndex.get_loc\u001b[1;34m(self, key, method, tolerance)\u001b[0m\n\u001b[0;32m   3621\u001b[0m     \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_engine\u001b[38;5;241m.\u001b[39mget_loc(casted_key)\n\u001b[0;32m   3622\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m \u001b[38;5;28;01mas\u001b[39;00m err:\n\u001b[1;32m-> 3623\u001b[0m     \u001b[38;5;28;01mraise\u001b[39;00m \u001b[38;5;167;01mKeyError\u001b[39;00m(key) \u001b[38;5;28;01mfrom\u001b[39;00m \u001b[38;5;21;01merr\u001b[39;00m\n\u001b[0;32m   3624\u001b[0m \u001b[38;5;28;01mexcept\u001b[39;00m \u001b[38;5;167;01mTypeError\u001b[39;00m:\n\u001b[0;32m   3625\u001b[0m     \u001b[38;5;66;03m# If we have a listlike key, _check_indexing_error will raise\u001b[39;00m\n\u001b[0;32m   3626\u001b[0m     \u001b[38;5;66;03m#  InvalidIndexError. Otherwise we fall through and re-raise\u001b[39;00m\n\u001b[0;32m   3627\u001b[0m     \u001b[38;5;66;03m#  the TypeError.\u001b[39;00m\n\u001b[0;32m   3628\u001b[0m     \u001b[38;5;28mself\u001b[39m\u001b[38;5;241m.\u001b[39m_check_indexing_error(key)\n",
      "\u001b[1;31mKeyError\u001b[0m: 'Start_Date_DT_enc'"
     ]
    }
   ],
   "source": [
    "encompassing = encompassing.assign(Start_Date_DT_enc = encompassing['Start_Date_DT_enc'].dt.date, Date_Received_DT_enc = encompassing['Start_Date_DT_enc'].dt.date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "9e4e144c-e7f9-4303-aab9-5812e195b2e3",
   "metadata": {},
   "outputs": [],
   "source": [
    "activityReport = activityReport.assign(Activity_Date_DT_act = activityReport['Activity_Date_DT_act'].dt.date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "34c04321-31d1-4fab-893b-bef43e333f90",
   "metadata": {},
   "outputs": [],
   "source": [
    "#first merge test\n",
    "# , how = 'left', left_on = 'Email', right_on = 'Target Email'\n",
    "first_merge = pd.merge(activityReport,encompassing, how = 'right', left_on = ['Target_Email','Activity_Date_DT'], right_on = ['Email','Start_Date_DT'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "0e3dc470-658f-4c06-adcd-5da909f5dcf3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "False    2095\n",
       "True      230\n",
       "Name: Source_Email, dtype: int64"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "first_merge['Source_Email'].isna().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "138cdd26-95f6-4aca-96e5-50e1749c18b5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     5594\n",
       "False    2095\n",
       "Name: Email, dtype: int64"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "second_merge = pd.merge(activityReport,encompassing, how = 'left', left_on = ['Target_Email','Activity_Date_DT'], right_on = ['Email','Start_Date_DT'])\n",
    "second_merge['Email'].isna().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "id": "be74fb28-624b-44c8-8f87-21d5ac349367",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     5594\n",
       "False    2095\n",
       "Name: Email_enc, dtype: int64"
      ]
     },
     "execution_count": 65,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "third_merge = pd.merge(activityReport,encompassing, how = 'left', left_on = ['Target_Email_act','Activity_Date_DT_act'], right_on = ['Email_enc','Date_Received_DT_enc'])\n",
    "third_merge['Email_enc'].isna().value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "id": "21be34eb-7d9a-44bb-b78d-9ddd0cff784f",
   "metadata": {},
   "outputs": [],
   "source": [
    "third_merge.loc[:,['Target_Email_act','Activity_Type_act','Subject_act','Activity_Date_DT_act','Membership_Type_enc','Status_enc','Start_Date_DT_enc','End_Date_enc','Email_enc','Date_Received_DT_enc']].sort_values(by = ['Target_Email_act','Activity_Date_DT_act']).to_csv('./mergeMiami.csv',index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ebfcf8fc-ec14-4820-b85c-f8b2132c7345",
   "metadata": {},
   "source": [
    "third_merge is good, except: \n",
    "1) I need to turn the join into a full outer join to capture non-corresponding date records btwn the two dataframes\n",
    "2) I may be missing some data from 'select_activity_report.csv'... an exemplary case is email: aatiell@gmail.com... this person signed up for a Lettuce acct but this only appears in the 'all encompassing' file. *Discovered with Travis that Target_Email_act may be the key to relating all relevant activity by user: selectively refer to email accounts in this field in order to properly merge with the \"encompassing\" table. A selective reference is necessary as the original foreign key was \"Target_Email_act\" and this is unreliable in cases of blanks/nulls"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "66fa264d-aca2-487b-bc86-d1cbb9e500e4",
   "metadata": {},
   "outputs": [],
   "source": [
    "activityReport = activityReport.assign(email_grouping = activityReport.apply(lambda x: x['Target_Email_act'] if re.match('admin@greenehillfood.coop|it@greenehillfood.coop', x['Source_Email_act']) else x['Source_Email_act'], axis = 1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "01599861-8007-47b0-a972-592a096f872e",
   "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>Assignee_Name_act</th>\n",
       "      <th>Target_Name_act</th>\n",
       "      <th>Source_Email_act</th>\n",
       "      <th>Target_Email_act</th>\n",
       "      <th>Activity_Type_act</th>\n",
       "      <th>Subject_act</th>\n",
       "      <th>Activity_Date_act</th>\n",
       "      <th>Activity_Status_act</th>\n",
       "      <th>Activity_Details_act</th>\n",
       "      <th>Activity_Date_DT_act</th>\n",
       "      <th>email_grouping</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>5575</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Agustin Tiell, Alba</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "      <td>Membership Signup</td>\n",
       "      <td>Zucchini Plan - Trial Plan Application - Statu...</td>\n",
       "      <td>2021-12-12 09:44</td>\n",
       "      <td>Completed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-12-12</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5979</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Agustin Tiell, Alba</td>\n",
       "      <td>admin@greenehillfood.coop</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "      <td>Change Membership Status</td>\n",
       "      <td>Status changed from New to Active</td>\n",
       "      <td>2022-02-11 01:54</td>\n",
       "      <td>Completed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-02-11</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6877</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Agustin Tiell, Alba</td>\n",
       "      <td>admin@greenehillfood.coop</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "      <td>Change Membership Status</td>\n",
       "      <td>Status changed from Active to Expired</td>\n",
       "      <td>2022-06-11 02:54</td>\n",
       "      <td>Completed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-11</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6953</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Change Membership Status</td>\n",
       "      <td>Status changed from Expired to Active</td>\n",
       "      <td>2022-06-27 10:25</td>\n",
       "      <td>Completed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-27</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6954</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Change Membership Type</td>\n",
       "      <td>Type changed from Zucchini Plan to Lettuce Plan</td>\n",
       "      <td>2022-06-27 10:25</td>\n",
       "      <td>Completed</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2022-06-27</td>\n",
       "      <td>aatiell@gmail.com</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      Assignee_Name_act      Target_Name_act           Source_Email_act  \\\n",
       "5575                NaN  Agustin Tiell, Alba          aatiell@gmail.com   \n",
       "5979                NaN  Agustin Tiell, Alba  admin@greenehillfood.coop   \n",
       "6877                NaN  Agustin Tiell, Alba  admin@greenehillfood.coop   \n",
       "6953                NaN                  NaN          aatiell@gmail.com   \n",
       "6954                NaN                  NaN          aatiell@gmail.com   \n",
       "\n",
       "       Target_Email_act         Activity_Type_act  \\\n",
       "5575  aatiell@gmail.com         Membership Signup   \n",
       "5979  aatiell@gmail.com  Change Membership Status   \n",
       "6877  aatiell@gmail.com  Change Membership Status   \n",
       "6953                NaN  Change Membership Status   \n",
       "6954                NaN    Change Membership Type   \n",
       "\n",
       "                                            Subject_act Activity_Date_act  \\\n",
       "5575  Zucchini Plan - Trial Plan Application - Statu...  2021-12-12 09:44   \n",
       "5979                  Status changed from New to Active  2022-02-11 01:54   \n",
       "6877              Status changed from Active to Expired  2022-06-11 02:54   \n",
       "6953              Status changed from Expired to Active  2022-06-27 10:25   \n",
       "6954    Type changed from Zucchini Plan to Lettuce Plan  2022-06-27 10:25   \n",
       "\n",
       "     Activity_Status_act  Activity_Details_act Activity_Date_DT_act  \\\n",
       "5575           Completed                   NaN           2021-12-12   \n",
       "5979           Completed                   NaN           2022-02-11   \n",
       "6877           Completed                   NaN           2022-06-11   \n",
       "6953           Completed                   NaN           2022-06-27   \n",
       "6954           Completed                   NaN           2022-06-27   \n",
       "\n",
       "         email_grouping  \n",
       "5575  aatiell@gmail.com  \n",
       "5979  aatiell@gmail.com  \n",
       "6877  aatiell@gmail.com  \n",
       "6953  aatiell@gmail.com  \n",
       "6954  aatiell@gmail.com  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "activityReport.loc[activityReport['email_grouping']=='aatiell@gmail.com',:].sort_values(by = 'Activity_Date_DT_act')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "fe8b0528-e7f0-4b55-87b6-5fe61086ebd1",
   "metadata": {},
   "outputs": [],
   "source": [
    "activityReport.loc[:,['Activity_Type_act', 'Subject_act',\n",
    "       'Activity_Date_act', 'Activity_Status_act', 'Activity_Details_act',\n",
    "       'Activity_Date_DT_act','email_grouping']].sort_values(by = ['email_grouping','Activity_Date_DT_act']).to_csv('./activityReportsNormalized.csv', index = False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "60d276ef-9dff-4be2-a5cc-70742268d74c",
   "metadata": {},
   "source": [
    "Thinking about establishing a hierarchy to label the predominating status of ea member at a moment in time, this will involve ignoring some activities like trial expirations when a higher trial (Zucchini) was been invoked.\n",
    "There are two types of relevant activities: change in membership types (including signups) and status changes: cancellations, expirations, general leave, etc"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6211d4ac-0e4d-41b1-928a-b34ca070904e",
   "metadata": {},
   "source": [
    "Other idea: 1) select for pertinent (or irrelevant Subject_act values). 2) Establish a lookback of the last two activity types to gauge the journey step at each row\n",
    "\n",
    "ELABORATION\n",
    "1) appears as though \"Status changed from Expired to Active' and 'Status changed from New to Active' can be removed\n",
    "2) pandas groupby: shift(-1) and shift(-2); I have the option of creating functions or even a class for this work"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "b663d246-7bf9-486b-acb0-af43700318a0",
   "metadata": {},
   "outputs": [],
   "source": [
    "#take a look at the permutations of some field values\n",
    "groups = activityReport.groupby('Activity_Type_act')['Subject_act'].apply(list)\n",
    "df1 = groups.reset_index(name = 'listvalues')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "9ce986ee-d9af-460b-8736-8a8f23ace86f",
   "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>Activity_Type_act</th>\n",
       "      <th>listvalues</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Change Membership Status</td>\n",
       "      <td>[Status changed from New to Expired, Status ch...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          Activity_Type_act                                         listvalues\n",
       "0  Change Membership Status  [Status changed from New to Expired, Status ch..."
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.loc[df1['Activity_Type_act'] == 'Change Membership Status',:]"
   ]
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}