BBT Messages Import.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Bright By Text: Outgoing Messages Analysis"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Setup and Data Import"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# Widen notebook\n",
    "\n",
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "\n",
    "# Import libraries necessary\n",
    "import numpy as np\n",
    "import sys\n",
    "np.set_printoptions(threshold=sys.maxsize)\n",
    "import pandas as pd\n",
    "from time import time\n",
    "import datetime\n",
    "from IPython.display import display\n",
    "import re\n",
    "%matplotlib inline\n",
    "import psycopg2\n",
    "pd.set_option('display.max_columns', 500)\n",
    "pd.set_option('display.max_rows', 500)\n",
    "pd.options.display.float_format = '{:.3f}'.format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "# Connect to PostgeSQL\n",
    "\n",
    "try:\n",
    "    conn = psycopg2.connect(user = \"pbiusr1\",\n",
    "                                  password = \"RE_^V%Gj@EL6R!G\",\n",
    "                                  host = \"app.brightbytext.org\",\n",
    "                                  port = \"5432\",\n",
    "                                  database = \"bbtapi\")\n",
    "\n",
    "except (Exception, psycopg2.Error) as error :\n",
    "    print (\"Error while connecting to PostgreSQL\", error)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query to get archived outbound messages for 2017\n",
    "\n",
    "sql = '''\n",
    "    select\n",
    "    om.message_id,\n",
    "    om.created_at as outbound_message_created_at,\n",
    "    om.body as outbound_message_body,\n",
    "    om.partner_id as outbound_message_partner_id,\n",
    "    mt.name as message_type,\n",
    "    s.id as subscriber_id,\n",
    "    s.created_at,\n",
    "    s.signedup_at,\n",
    "    s.deactivated_at,\n",
    "    s.carrier_name,\n",
    "    z.code as subscriber_zip_code,\n",
    "    z.city as subscriber_city,\n",
    "    t.default_offset as timezone_default_offset,\n",
    "    t.iana_name as timezone_name,\n",
    "    s_state.name as subscriber_state,\n",
    "    ss.name as subscriber_source,\n",
    "    sst.name as subscriber_status,\n",
    "    l.name as subscriber_language,\n",
    "    sdr.name as subscriber_deactivation_method,\n",
    "    p.name as partner_name,\n",
    "    p.created_at as partner_created_date,\n",
    "    p.is_active as partner_is_active,\n",
    "    p_state.name as partner_state,\n",
    "    c.children_count,\n",
    "    c.dob_youngest_child,\n",
    "    c.dob_oldest_child,\n",
    "    msm.scheduled_message_id\n",
    "    \n",
    "from \n",
    "(\n",
    "select *\n",
    "    from archive.outbound_messages\n",
    "    where extract(year from created_at) = 2017\n",
    ") om\n",
    "left join public.message_types as mt\n",
    "on om.message_type_id = mt.id \n",
    "left join messages_scheduled_messages msm \n",
    "on om.message_id = msm.message_id \n",
    "left join public.v_subscribers as s\n",
    "on om.subscriber_id = s.id\n",
    "left join \n",
    "(\n",
    "    select subscriber_id, count(*) as children_count, min(date_of_birth) as dob_youngest_child, max(date_of_birth) as dob_oldest_child\n",
    "    from public.children \n",
    "    group by subscriber_id\n",
    ") c \n",
    "on om.subscriber_id = c.subscriber_id\n",
    "left join public.zip_codes as z \n",
    "on z.id = s.zip_id\n",
    "left join public.timezones t\n",
    "on z.time_zone_id = t.id\n",
    "left join public.states as s_state\n",
    "on s_state.id = z.state_id\n",
    "left join public.subscriber_sources as ss \n",
    "on ss.id = s.source_id\n",
    "left join public.subscriber_statuses as sst \n",
    "on sst.id = s.status_id\n",
    "left join public.languages as l\n",
    "on l.id = s.language_id\n",
    "left join public.subscriber_deactivation_reasons as sdr \n",
    "on sdr.id = s.deactivation_reason_id\n",
    "left join public.partners as p\n",
    "on p.id = s.partner_id\n",
    "left join public.partner_types as pt \n",
    "on pt.id = p.type_id\n",
    "left join public.states as p_state\n",
    "on p_state.id = p.state_id\n",
    "'''\n",
    "archived_messages_2017 = pd.read_sql_query(sql, conn)\n",
    "conn = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "scrolled": true
   },
   "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>message_id</th>\n",
       "      <th>outbound_message_created_at</th>\n",
       "      <th>outbound_message_body</th>\n",
       "      <th>outbound_message_partner_id</th>\n",
       "      <th>message_type</th>\n",
       "      <th>subscriber_id</th>\n",
       "      <th>created_at</th>\n",
       "      <th>signedup_at</th>\n",
       "      <th>deactivated_at</th>\n",
       "      <th>carrier_name</th>\n",
       "      <th>subscriber_zip_code</th>\n",
       "      <th>subscriber_city</th>\n",
       "      <th>timezone_default_offset</th>\n",
       "      <th>timezone_name</th>\n",
       "      <th>subscriber_state</th>\n",
       "      <th>subscriber_source</th>\n",
       "      <th>subscriber_status</th>\n",
       "      <th>subscriber_language</th>\n",
       "      <th>subscriber_deactivation_method</th>\n",
       "      <th>partner_name</th>\n",
       "      <th>partner_created_date</th>\n",
       "      <th>partner_is_active</th>\n",
       "      <th>partner_state</th>\n",
       "      <th>children_count</th>\n",
       "      <th>dob_youngest_child</th>\n",
       "      <th>dob_oldest_child</th>\n",
       "      <th>scheduled_message_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>6e92ae11-f983-479f-8e9c-2b689368954d</td>\n",
       "      <td>2017-05-03 15:11:30.588685</td>\n",
       "      <td>Invalid zipcode. Kindly check your zipcode and...</td>\n",
       "      <td>00000000-0000-0000-0000-000000000002</td>\n",
       "      <td>signup</td>\n",
       "      <td>53e4abdd-308a-4cfc-a6fc-410c89a28d2d</td>\n",
       "      <td>2017-05-03 14:30:08.345377</td>\n",
       "      <td>2017-05-03 14:30:08.345377</td>\n",
       "      <td>NaT</td>\n",
       "      <td>AT&amp;T Wireless</td>\n",
       "      <td>11096</td>\n",
       "      <td>Inwood</td>\n",
       "      <td>-5.000</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>New York</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>WNET - New York, NY</td>\n",
       "      <td>2018-03-25 21:32:21.001262</td>\n",
       "      <td>True</td>\n",
       "      <td>New York</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2015-06-06</td>\n",
       "      <td>2015-06-06</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>57c0ec0e-f812-4b45-a33a-213f4663f1c2</td>\n",
       "      <td>2017-05-09 16:34:15.603110</td>\n",
       "      <td>Is this zipcode correct?  If yes reply with 'Y...</td>\n",
       "      <td>00000000-0000-0000-0000-000000000002</td>\n",
       "      <td>signup</td>\n",
       "      <td>f328ee46-398c-422f-aa55-4816caeae06e</td>\n",
       "      <td>2017-05-09 16:31:26.230154</td>\n",
       "      <td>2017-05-09 16:31:26.230154</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>11219</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>-5.000</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>New York</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>WNET - New York, NY</td>\n",
       "      <td>2018-03-25 21:32:21.001262</td>\n",
       "      <td>True</td>\n",
       "      <td>New York</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2014-06-23</td>\n",
       "      <td>2014-06-23</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>17d99d4d-137a-4317-b4d8-a63a4690fc93</td>\n",
       "      <td>2017-05-04 18:40:41.904659</td>\n",
       "      <td>If you care for a young child, Bright by Text ...</td>\n",
       "      <td>f92494a8-bcce-473f-a56e-0ae2515c827e</td>\n",
       "      <td>signup</td>\n",
       "      <td>99915cfb-856b-4d59-8b94-0bd26d1342c4</td>\n",
       "      <td>2017-05-04 18:39:17.165132</td>\n",
       "      <td>2017-05-04 18:39:17.165132</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>78251</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>-6.000</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>KLRN - San Antonio</td>\n",
       "      <td>2018-08-28 20:58:04.700242</td>\n",
       "      <td>True</td>\n",
       "      <td>Texas</td>\n",
       "      <td>2.000</td>\n",
       "      <td>2014-09-02</td>\n",
       "      <td>2016-09-03</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>ffb5de23-d828-4e3a-becf-8b10b80cefba</td>\n",
       "      <td>2017-05-04 18:45:12.364195</td>\n",
       "      <td>Unrecognized date of birth format.  Please res...</td>\n",
       "      <td>f92494a8-bcce-473f-a56e-0ae2515c827e</td>\n",
       "      <td>signup</td>\n",
       "      <td>99915cfb-856b-4d59-8b94-0bd26d1342c4</td>\n",
       "      <td>2017-05-04 18:39:17.165132</td>\n",
       "      <td>2017-05-04 18:39:17.165132</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>78251</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>-6.000</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>KLRN - San Antonio</td>\n",
       "      <td>2018-08-28 20:58:04.700242</td>\n",
       "      <td>True</td>\n",
       "      <td>Texas</td>\n",
       "      <td>2.000</td>\n",
       "      <td>2014-09-02</td>\n",
       "      <td>2016-09-03</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>bc56c5c2-c5b2-4a95-bfd5-a284661e7052</td>\n",
       "      <td>2017-05-04 18:39:17.856495</td>\n",
       "      <td>KLRN welcomes you to Bright by Text! Expect 2 ...</td>\n",
       "      <td>f92494a8-bcce-473f-a56e-0ae2515c827e</td>\n",
       "      <td>signup</td>\n",
       "      <td>99915cfb-856b-4d59-8b94-0bd26d1342c4</td>\n",
       "      <td>2017-05-04 18:39:17.165132</td>\n",
       "      <td>2017-05-04 18:39:17.165132</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>78251</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>-6.000</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>KLRN - San Antonio</td>\n",
       "      <td>2018-08-28 20:58:04.700242</td>\n",
       "      <td>True</td>\n",
       "      <td>Texas</td>\n",
       "      <td>2.000</td>\n",
       "      <td>2014-09-02</td>\n",
       "      <td>2016-09-03</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1518155</td>\n",
       "      <td>e47de3c4-7eb7-4344-b597-59b88c22490b</td>\n",
       "      <td>2017-12-30 22:15:45.432168</td>\n",
       "      <td>Time makes sense to your child in terms of her...</td>\n",
       "      <td>d4d696af-37ff-427e-8035-2cb1bd630495</td>\n",
       "      <td>normal</td>\n",
       "      <td>6e44764b-7553-45b4-87b3-9d6375c3a793</td>\n",
       "      <td>2017-06-19 11:46:27.836526</td>\n",
       "      <td>2015-12-17 13:29:00.000000</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>80030</td>\n",
       "      <td>Westminster</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Sales Force</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>Bright by Three</td>\n",
       "      <td>2018-11-30 19:20:34.357527</td>\n",
       "      <td>True</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>2.000</td>\n",
       "      <td>2015-04-12</td>\n",
       "      <td>2015-04-13</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1518156</td>\n",
       "      <td>7f132f43-3c40-4dca-a2ef-935c9027875c</td>\n",
       "      <td>2017-12-30 22:23:50.798022</td>\n",
       "      <td>Sing the same song for specific activities, li...</td>\n",
       "      <td>d4d696af-37ff-427e-8035-2cb1bd630495</td>\n",
       "      <td>normal</td>\n",
       "      <td>127452b6-e5d5-456c-85a2-9470b740435d</td>\n",
       "      <td>2017-06-19 12:48:50.280428</td>\n",
       "      <td>2016-08-03 16:59:00.000000</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>80247</td>\n",
       "      <td>Denver</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Sales Force</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>Bright by Three</td>\n",
       "      <td>2018-11-30 19:20:34.357527</td>\n",
       "      <td>True</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>3.000</td>\n",
       "      <td>2015-01-02</td>\n",
       "      <td>2018-12-27</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1518157</td>\n",
       "      <td>f34aa581-f57d-4bad-b796-51684185764c</td>\n",
       "      <td>2017-12-30 22:39:05.404773</td>\n",
       "      <td>Singing Laundry: When you are doing laundry, s...</td>\n",
       "      <td>71718ec5-049d-47b9-80db-f9fbd0503152</td>\n",
       "      <td>normal</td>\n",
       "      <td>fef4ea4a-d491-474c-8762-6edb34530e73</td>\n",
       "      <td>2017-08-09 18:06:40.082440</td>\n",
       "      <td>2017-08-09 18:06:40.082440</td>\n",
       "      <td>NaT</td>\n",
       "      <td>AT&amp;T Wireless</td>\n",
       "      <td>93657</td>\n",
       "      <td>Sanger</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>ValleyPBS - Fresno, CA</td>\n",
       "      <td>2018-08-28 20:57:03.730535</td>\n",
       "      <td>True</td>\n",
       "      <td>California</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2017-05-02</td>\n",
       "      <td>2017-05-02</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1518158</td>\n",
       "      <td>99f8ad28-c4ca-4ca4-a2ec-21b656b87d73</td>\n",
       "      <td>2017-12-31 22:11:34.912976</td>\n",
       "      <td>Can your child recognize an object by touch wh...</td>\n",
       "      <td>00000000-0000-0000-0000-000000000002</td>\n",
       "      <td>normal</td>\n",
       "      <td>186576a4-f38d-4bae-8dd1-4faa77437895</td>\n",
       "      <td>2017-06-13 15:00:08.468997</td>\n",
       "      <td>2017-06-13 15:00:08.468997</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Sprint Spectrum, L.P.</td>\n",
       "      <td>11226</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>-5.000</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>New York</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>WNET - New York, NY</td>\n",
       "      <td>2018-03-25 21:32:21.001262</td>\n",
       "      <td>True</td>\n",
       "      <td>New York</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2016-04-11</td>\n",
       "      <td>2016-04-11</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1518159</td>\n",
       "      <td>9cdc675c-a8e2-40cf-a31e-d6be1195b4e9</td>\n",
       "      <td>2017-12-16 22:23:11.429382</td>\n",
       "      <td>Bring the bubbles out! Children like to watch ...</td>\n",
       "      <td>d4d696af-37ff-427e-8035-2cb1bd630495</td>\n",
       "      <td>normal</td>\n",
       "      <td>c651c94f-79b1-4bec-96b7-a515441403b4</td>\n",
       "      <td>2017-06-19 13:01:40.848896</td>\n",
       "      <td>2016-08-17 13:00:00.000000</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Verizon Wireless</td>\n",
       "      <td>80022</td>\n",
       "      <td>Commerce City</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Sales Force</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>Bright by Three</td>\n",
       "      <td>2018-11-30 19:20:34.357527</td>\n",
       "      <td>True</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2016-07-12</td>\n",
       "      <td>2016-07-12</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1518160 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   message_id outbound_message_created_at  \\\n",
       "0        6e92ae11-f983-479f-8e9c-2b689368954d  2017-05-03 15:11:30.588685   \n",
       "1        57c0ec0e-f812-4b45-a33a-213f4663f1c2  2017-05-09 16:34:15.603110   \n",
       "2        17d99d4d-137a-4317-b4d8-a63a4690fc93  2017-05-04 18:40:41.904659   \n",
       "3        ffb5de23-d828-4e3a-becf-8b10b80cefba  2017-05-04 18:45:12.364195   \n",
       "4        bc56c5c2-c5b2-4a95-bfd5-a284661e7052  2017-05-04 18:39:17.856495   \n",
       "...                                       ...                         ...   \n",
       "1518155  e47de3c4-7eb7-4344-b597-59b88c22490b  2017-12-30 22:15:45.432168   \n",
       "1518156  7f132f43-3c40-4dca-a2ef-935c9027875c  2017-12-30 22:23:50.798022   \n",
       "1518157  f34aa581-f57d-4bad-b796-51684185764c  2017-12-30 22:39:05.404773   \n",
       "1518158  99f8ad28-c4ca-4ca4-a2ec-21b656b87d73  2017-12-31 22:11:34.912976   \n",
       "1518159  9cdc675c-a8e2-40cf-a31e-d6be1195b4e9  2017-12-16 22:23:11.429382   \n",
       "\n",
       "                                     outbound_message_body  \\\n",
       "0        Invalid zipcode. Kindly check your zipcode and...   \n",
       "1        Is this zipcode correct?  If yes reply with 'Y...   \n",
       "2        If you care for a young child, Bright by Text ...   \n",
       "3        Unrecognized date of birth format.  Please res...   \n",
       "4        KLRN welcomes you to Bright by Text! Expect 2 ...   \n",
       "...                                                    ...   \n",
       "1518155  Time makes sense to your child in terms of her...   \n",
       "1518156  Sing the same song for specific activities, li...   \n",
       "1518157  Singing Laundry: When you are doing laundry, s...   \n",
       "1518158  Can your child recognize an object by touch wh...   \n",
       "1518159  Bring the bubbles out! Children like to watch ...   \n",
       "\n",
       "                  outbound_message_partner_id message_type  \\\n",
       "0        00000000-0000-0000-0000-000000000002       signup   \n",
       "1        00000000-0000-0000-0000-000000000002       signup   \n",
       "2        f92494a8-bcce-473f-a56e-0ae2515c827e       signup   \n",
       "3        f92494a8-bcce-473f-a56e-0ae2515c827e       signup   \n",
       "4        f92494a8-bcce-473f-a56e-0ae2515c827e       signup   \n",
       "...                                       ...          ...   \n",
       "1518155  d4d696af-37ff-427e-8035-2cb1bd630495       normal   \n",
       "1518156  d4d696af-37ff-427e-8035-2cb1bd630495       normal   \n",
       "1518157  71718ec5-049d-47b9-80db-f9fbd0503152       normal   \n",
       "1518158  00000000-0000-0000-0000-000000000002       normal   \n",
       "1518159  d4d696af-37ff-427e-8035-2cb1bd630495       normal   \n",
       "\n",
       "                                subscriber_id                 created_at  \\\n",
       "0        53e4abdd-308a-4cfc-a6fc-410c89a28d2d 2017-05-03 14:30:08.345377   \n",
       "1        f328ee46-398c-422f-aa55-4816caeae06e 2017-05-09 16:31:26.230154   \n",
       "2        99915cfb-856b-4d59-8b94-0bd26d1342c4 2017-05-04 18:39:17.165132   \n",
       "3        99915cfb-856b-4d59-8b94-0bd26d1342c4 2017-05-04 18:39:17.165132   \n",
       "4        99915cfb-856b-4d59-8b94-0bd26d1342c4 2017-05-04 18:39:17.165132   \n",
       "...                                       ...                        ...   \n",
       "1518155  6e44764b-7553-45b4-87b3-9d6375c3a793 2017-06-19 11:46:27.836526   \n",
       "1518156  127452b6-e5d5-456c-85a2-9470b740435d 2017-06-19 12:48:50.280428   \n",
       "1518157  fef4ea4a-d491-474c-8762-6edb34530e73 2017-08-09 18:06:40.082440   \n",
       "1518158  186576a4-f38d-4bae-8dd1-4faa77437895 2017-06-13 15:00:08.468997   \n",
       "1518159  c651c94f-79b1-4bec-96b7-a515441403b4 2017-06-19 13:01:40.848896   \n",
       "\n",
       "                       signedup_at deactivated_at           carrier_name  \\\n",
       "0       2017-05-03 14:30:08.345377            NaT          AT&T Wireless   \n",
       "1       2017-05-09 16:31:26.230154            NaT     T-Mobile USA, Inc.   \n",
       "2       2017-05-04 18:39:17.165132            NaT     T-Mobile USA, Inc.   \n",
       "3       2017-05-04 18:39:17.165132            NaT     T-Mobile USA, Inc.   \n",
       "4       2017-05-04 18:39:17.165132            NaT     T-Mobile USA, Inc.   \n",
       "...                            ...            ...                    ...   \n",
       "1518155 2015-12-17 13:29:00.000000            NaT     T-Mobile USA, Inc.   \n",
       "1518156 2016-08-03 16:59:00.000000            NaT     T-Mobile USA, Inc.   \n",
       "1518157 2017-08-09 18:06:40.082440            NaT          AT&T Wireless   \n",
       "1518158 2017-06-13 15:00:08.468997            NaT  Sprint Spectrum, L.P.   \n",
       "1518159 2016-08-17 13:00:00.000000            NaT       Verizon Wireless   \n",
       "\n",
       "        subscriber_zip_code subscriber_city  timezone_default_offset  \\\n",
       "0                     11096          Inwood                   -5.000   \n",
       "1                     11219        Brooklyn                   -5.000   \n",
       "2                     78251     San Antonio                   -6.000   \n",
       "3                     78251     San Antonio                   -6.000   \n",
       "4                     78251     San Antonio                   -6.000   \n",
       "...                     ...             ...                      ...   \n",
       "1518155               80030     Westminster                   -7.000   \n",
       "1518156               80247          Denver                   -7.000   \n",
       "1518157               93657          Sanger                   -8.000   \n",
       "1518158               11226        Brooklyn                   -5.000   \n",
       "1518159               80022   Commerce City                   -7.000   \n",
       "\n",
       "               timezone_name subscriber_state subscriber_source  \\\n",
       "0           America/New_York         New York       Text SignUp   \n",
       "1           America/New_York         New York       Text SignUp   \n",
       "2            America/Chicago            Texas       Text SignUp   \n",
       "3            America/Chicago            Texas       Text SignUp   \n",
       "4            America/Chicago            Texas       Text SignUp   \n",
       "...                      ...              ...               ...   \n",
       "1518155       America/Denver         Colorado       Sales Force   \n",
       "1518156       America/Denver         Colorado       Sales Force   \n",
       "1518157  America/Los_Angeles       California       Text SignUp   \n",
       "1518158     America/New_York         New York       Text SignUp   \n",
       "1518159       America/Denver         Colorado       Sales Force   \n",
       "\n",
       "        subscriber_status subscriber_language subscriber_deactivation_method  \\\n",
       "0               activated             English                           None   \n",
       "1               activated             English                           None   \n",
       "2               activated             English                           None   \n",
       "3               activated             English                           None   \n",
       "4               activated             English                           None   \n",
       "...                   ...                 ...                            ...   \n",
       "1518155         activated             English                           None   \n",
       "1518156         activated             English                           None   \n",
       "1518157         activated             English                           None   \n",
       "1518158         activated             English                           None   \n",
       "1518159         activated             English                           None   \n",
       "\n",
       "                   partner_name       partner_created_date  partner_is_active  \\\n",
       "0           WNET - New York, NY 2018-03-25 21:32:21.001262               True   \n",
       "1           WNET - New York, NY 2018-03-25 21:32:21.001262               True   \n",
       "2            KLRN - San Antonio 2018-08-28 20:58:04.700242               True   \n",
       "3            KLRN - San Antonio 2018-08-28 20:58:04.700242               True   \n",
       "4            KLRN - San Antonio 2018-08-28 20:58:04.700242               True   \n",
       "...                         ...                        ...                ...   \n",
       "1518155         Bright by Three 2018-11-30 19:20:34.357527               True   \n",
       "1518156         Bright by Three 2018-11-30 19:20:34.357527               True   \n",
       "1518157  ValleyPBS - Fresno, CA 2018-08-28 20:57:03.730535               True   \n",
       "1518158     WNET - New York, NY 2018-03-25 21:32:21.001262               True   \n",
       "1518159         Bright by Three 2018-11-30 19:20:34.357527               True   \n",
       "\n",
       "        partner_state  children_count dob_youngest_child dob_oldest_child  \\\n",
       "0            New York           1.000         2015-06-06       2015-06-06   \n",
       "1            New York           1.000         2014-06-23       2014-06-23   \n",
       "2               Texas           2.000         2014-09-02       2016-09-03   \n",
       "3               Texas           2.000         2014-09-02       2016-09-03   \n",
       "4               Texas           2.000         2014-09-02       2016-09-03   \n",
       "...               ...             ...                ...              ...   \n",
       "1518155      Colorado           2.000         2015-04-12       2015-04-13   \n",
       "1518156      Colorado           3.000         2015-01-02       2018-12-27   \n",
       "1518157    California           1.000         2017-05-02       2017-05-02   \n",
       "1518158      New York           1.000         2016-04-11       2016-04-11   \n",
       "1518159      Colorado           1.000         2016-07-12       2016-07-12   \n",
       "\n",
       "        scheduled_message_id  \n",
       "0                       None  \n",
       "1                       None  \n",
       "2                       None  \n",
       "3                       None  \n",
       "4                       None  \n",
       "...                      ...  \n",
       "1518155                 None  \n",
       "1518156                 None  \n",
       "1518157                 None  \n",
       "1518158                 None  \n",
       "1518159                 None  \n",
       "\n",
       "[1518160 rows x 27 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "archived_messages_2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download the archived messages dataset for 2017\n",
    "\n",
    "archived_messages_2017.to_csv(r'/Users/lisafan/Desktop/BBT/bbt_archived_messages_2017.csv', index=False)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to PostgeSQL\n",
    "\n",
    "try:\n",
    "    conn = psycopg2.connect(user = \"pbiusr1\",\n",
    "                                  password = \"RE_^V%Gj@EL6R!G\",\n",
    "                                  host = \"app.brightbytext.org\",\n",
    "                                  port = \"5432\",\n",
    "                                  database = \"bbtapi\")\n",
    "\n",
    "except (Exception, psycopg2.Error) as error :\n",
    "    print (\"Error while connecting to PostgreSQL\", error)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query to get archived outbound messages for 2018\n",
    "\n",
    "sql = '''\n",
    "    select\n",
    "    om.message_id,\n",
    "    om.created_at as outbound_message_created_at,\n",
    "    om.body as outbound_message_body,\n",
    "    om.partner_id as outbound_message_partner_id,\n",
    "    mt.name as message_type,\n",
    "    s.id as subscriber_id,\n",
    "    s.created_at,\n",
    "    s.signedup_at,\n",
    "    s.deactivated_at,\n",
    "    s.carrier_name,\n",
    "    z.code as subscriber_zip_code,\n",
    "    z.city as subscriber_city,\n",
    "    t.default_offset as timezone_default_offset,\n",
    "    t.iana_name as timezone_name,\n",
    "    s_state.name as subscriber_state,\n",
    "    ss.name as subscriber_source,\n",
    "    sst.name as subscriber_status,\n",
    "    l.name as subscriber_language,\n",
    "    sdr.name as subscriber_deactivation_method,\n",
    "    p.name as partner_name,\n",
    "    p.created_at as partner_created_date,\n",
    "    p.is_active as partner_is_active,\n",
    "    p_state.name as partner_state,\n",
    "    c.children_count,\n",
    "    c.dob_youngest_child,\n",
    "    c.dob_oldest_child,\n",
    "    msm.scheduled_message_id\n",
    "    \n",
    "from \n",
    "(\n",
    "select *\n",
    "    from archive.outbound_messages\n",
    "    where extract(year from created_at) = 2018\n",
    ") om\n",
    "left join public.message_types as mt\n",
    "on om.message_type_id = mt.id \n",
    "left join messages_scheduled_messages msm \n",
    "on om.message_id = msm.message_id \n",
    "left join public.v_subscribers as s\n",
    "on om.subscriber_id = s.id\n",
    "left join \n",
    "(\n",
    "    select subscriber_id, count(*) as children_count, min(date_of_birth) as dob_youngest_child, max(date_of_birth) as dob_oldest_child\n",
    "    from public.children \n",
    "    group by subscriber_id\n",
    ") c \n",
    "on om.subscriber_id = c.subscriber_id\n",
    "left join public.zip_codes as z \n",
    "on z.id = s.zip_id\n",
    "left join public.timezones t\n",
    "on z.time_zone_id = t.id\n",
    "left join public.states as s_state\n",
    "on s_state.id = z.state_id\n",
    "left join public.subscriber_sources as ss \n",
    "on ss.id = s.source_id\n",
    "left join public.subscriber_statuses as sst \n",
    "on sst.id = s.status_id\n",
    "left join public.languages as l\n",
    "on l.id = s.language_id\n",
    "left join public.subscriber_deactivation_reasons as sdr \n",
    "on sdr.id = s.deactivation_reason_id\n",
    "left join public.partners as p\n",
    "on p.id = s.partner_id\n",
    "left join public.partner_types as pt \n",
    "on pt.id = p.type_id\n",
    "left join public.states as p_state\n",
    "on p_state.id = p.state_id\n",
    "'''\n",
    "archived_messages_2018 = pd.read_sql_query(sql, conn)\n",
    "conn = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>message_id</th>\n",
       "      <th>outbound_message_created_at</th>\n",
       "      <th>outbound_message_body</th>\n",
       "      <th>outbound_message_partner_id</th>\n",
       "      <th>message_type</th>\n",
       "      <th>subscriber_id</th>\n",
       "      <th>created_at</th>\n",
       "      <th>signedup_at</th>\n",
       "      <th>deactivated_at</th>\n",
       "      <th>carrier_name</th>\n",
       "      <th>subscriber_zip_code</th>\n",
       "      <th>subscriber_city</th>\n",
       "      <th>timezone_default_offset</th>\n",
       "      <th>timezone_name</th>\n",
       "      <th>subscriber_state</th>\n",
       "      <th>subscriber_source</th>\n",
       "      <th>subscriber_status</th>\n",
       "      <th>subscriber_language</th>\n",
       "      <th>subscriber_deactivation_method</th>\n",
       "      <th>partner_name</th>\n",
       "      <th>partner_created_date</th>\n",
       "      <th>partner_is_active</th>\n",
       "      <th>partner_state</th>\n",
       "      <th>children_count</th>\n",
       "      <th>dob_youngest_child</th>\n",
       "      <th>dob_oldest_child</th>\n",
       "      <th>scheduled_message_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>ea851042-0aa4-4b1d-97eb-2ec187638c34</td>\n",
       "      <td>2018-01-08 22:38:22.181992</td>\n",
       "      <td>Describe the things your child points to in bo...</td>\n",
       "      <td>3a18f949-d80f-481b-8cd8-3c73be63e67e</td>\n",
       "      <td>normal</td>\n",
       "      <td>4d02bf4b-c61b-4227-a5b2-b4a81eae335a</td>\n",
       "      <td>2017-12-12 12:32:42.113661</td>\n",
       "      <td>2017-12-12 12:32:42.113661</td>\n",
       "      <td>2019-08-12 17:00:08.865406</td>\n",
       "      <td>Onvoy/3 - Sybase365</td>\n",
       "      <td>90001</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>Web SignUp</td>\n",
       "      <td>deactivated</td>\n",
       "      <td>English</td>\n",
       "      <td>Age Out</td>\n",
       "      <td>United Way for Greater Austin</td>\n",
       "      <td>2018-08-28 20:57:33.540197</td>\n",
       "      <td>True</td>\n",
       "      <td>Texas</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2017-12-12</td>\n",
       "      <td>2017-12-12</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>b23b4071-e7ae-40b1-822f-71905a77fbe3</td>\n",
       "      <td>2018-01-01 22:26:43.786828</td>\n",
       "      <td>Seeing patterns is an important pre-math skill...</td>\n",
       "      <td>d4d696af-37ff-427e-8035-2cb1bd630495</td>\n",
       "      <td>normal</td>\n",
       "      <td>16d242b2-c6b6-4f06-9f81-78ad2c7f766e</td>\n",
       "      <td>2017-06-19 13:25:46.393823</td>\n",
       "      <td>2017-03-27 13:00:00.000000</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>80204</td>\n",
       "      <td>Denver</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Sales Force</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>Bright by Three</td>\n",
       "      <td>2018-11-30 19:20:34.357527</td>\n",
       "      <td>True</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2013-11-29</td>\n",
       "      <td>2013-11-29</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>dc98843c-224c-4634-b762-3b23dca92ff7</td>\n",
       "      <td>2018-01-01 22:26:55.864001</td>\n",
       "      <td>Help your child learn by imitating you. Encour...</td>\n",
       "      <td>d4d696af-37ff-427e-8035-2cb1bd630495</td>\n",
       "      <td>normal</td>\n",
       "      <td>52ee6736-3a59-4a56-8bdb-4be856ef79c1</td>\n",
       "      <td>2017-06-19 13:26:52.539459</td>\n",
       "      <td>2017-04-03 09:58:00.000000</td>\n",
       "      <td>NaT</td>\n",
       "      <td>AT&amp;T Wireless</td>\n",
       "      <td>80033</td>\n",
       "      <td>Wheat Ridge</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Sales Force</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>Bright by Three</td>\n",
       "      <td>2018-11-30 19:20:34.357527</td>\n",
       "      <td>True</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2017-03-31</td>\n",
       "      <td>2017-03-31</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>79f32613-5735-4dd8-9206-84856cc823c8</td>\n",
       "      <td>2018-01-01 22:33:04.532669</td>\n",
       "      <td>When children's curiosity is nurtured, so is t...</td>\n",
       "      <td>3a18f949-d80f-481b-8cd8-3c73be63e67e</td>\n",
       "      <td>normal</td>\n",
       "      <td>4d02bf4b-c61b-4227-a5b2-b4a81eae335a</td>\n",
       "      <td>2017-12-12 12:32:42.113661</td>\n",
       "      <td>2017-12-12 12:32:42.113661</td>\n",
       "      <td>2019-08-12 17:00:08.865406</td>\n",
       "      <td>Onvoy/3 - Sybase365</td>\n",
       "      <td>90001</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>Web SignUp</td>\n",
       "      <td>deactivated</td>\n",
       "      <td>English</td>\n",
       "      <td>Age Out</td>\n",
       "      <td>United Way for Greater Austin</td>\n",
       "      <td>2018-08-28 20:57:33.540197</td>\n",
       "      <td>True</td>\n",
       "      <td>Texas</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2017-12-12</td>\n",
       "      <td>2017-12-12</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>5cb896cc-34e6-4067-b602-3f7959a38452</td>\n",
       "      <td>2018-01-01 22:33:04.996658</td>\n",
       "      <td>Writing starts with scribbling! Try this game ...</td>\n",
       "      <td>3a18f949-d80f-481b-8cd8-3c73be63e67e</td>\n",
       "      <td>normal</td>\n",
       "      <td>4d02bf4b-c61b-4227-a5b2-b4a81eae335a</td>\n",
       "      <td>2017-12-12 12:32:42.113661</td>\n",
       "      <td>2017-12-12 12:32:42.113661</td>\n",
       "      <td>2019-08-12 17:00:08.865406</td>\n",
       "      <td>Onvoy/3 - Sybase365</td>\n",
       "      <td>90001</td>\n",
       "      <td>Los Angeles</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>Web SignUp</td>\n",
       "      <td>deactivated</td>\n",
       "      <td>English</td>\n",
       "      <td>Age Out</td>\n",
       "      <td>United Way for Greater Austin</td>\n",
       "      <td>2018-08-28 20:57:33.540197</td>\n",
       "      <td>True</td>\n",
       "      <td>Texas</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2017-12-12</td>\n",
       "      <td>2017-12-12</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5104461</td>\n",
       "      <td>f56f60fe-e435-4d8b-975a-771ae324a880</td>\n",
       "      <td>2018-12-28 17:34:07.051202</td>\n",
       "      <td>The Financial Empowerment Center helps you wit...</td>\n",
       "      <td>None</td>\n",
       "      <td>scheduled</td>\n",
       "      <td>b1e07f05-e263-40cb-ac73-a69484002a70</td>\n",
       "      <td>2017-11-23 00:08:36.017898</td>\n",
       "      <td>2017-11-23 00:08:36.017898</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Verizon Wireless</td>\n",
       "      <td>78229</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>-6.000</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>KLRN - San Antonio</td>\n",
       "      <td>2018-08-28 20:58:04.700242</td>\n",
       "      <td>True</td>\n",
       "      <td>Texas</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2015-08-15</td>\n",
       "      <td>2015-08-15</td>\n",
       "      <td>ca75334a-2148-40c7-a0bf-8740dab820ec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5104462</td>\n",
       "      <td>f56f60fe-e435-4d8b-975a-771ae324a880</td>\n",
       "      <td>2018-12-28 17:34:10.823055</td>\n",
       "      <td>The Financial Empowerment Center helps you wit...</td>\n",
       "      <td>None</td>\n",
       "      <td>scheduled</td>\n",
       "      <td>3e42e7e7-1021-4955-bb7b-d3ceb099a99b</td>\n",
       "      <td>2018-07-21 15:48:05.519417</td>\n",
       "      <td>2018-07-21 15:48:05.519417</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Sprint Spectrum, L.P.</td>\n",
       "      <td>78251</td>\n",
       "      <td>San Antonio</td>\n",
       "      <td>-6.000</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Web SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>KLRN - San Antonio</td>\n",
       "      <td>2018-08-28 20:58:04.700242</td>\n",
       "      <td>True</td>\n",
       "      <td>Texas</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2015-11-15</td>\n",
       "      <td>2015-11-15</td>\n",
       "      <td>ca75334a-2148-40c7-a0bf-8740dab820ec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5104463</td>\n",
       "      <td>f56f60fe-e435-4d8b-975a-771ae324a880</td>\n",
       "      <td>2018-12-28 17:34:07.083416</td>\n",
       "      <td>The Financial Empowerment Center helps you wit...</td>\n",
       "      <td>None</td>\n",
       "      <td>scheduled</td>\n",
       "      <td>768d610a-9f83-462d-ab47-fae174ca6725</td>\n",
       "      <td>2018-04-07 18:16:18.289722</td>\n",
       "      <td>2018-04-07 18:16:18.289722</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>78148</td>\n",
       "      <td>Universal City</td>\n",
       "      <td>-6.000</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Web SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>KLRN - San Antonio</td>\n",
       "      <td>2018-08-28 20:58:04.700242</td>\n",
       "      <td>True</td>\n",
       "      <td>Texas</td>\n",
       "      <td>3.000</td>\n",
       "      <td>2010-11-01</td>\n",
       "      <td>2016-12-11</td>\n",
       "      <td>ca75334a-2148-40c7-a0bf-8740dab820ec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5104464</td>\n",
       "      <td>f56f60fe-e435-4d8b-975a-771ae324a880</td>\n",
       "      <td>2018-12-28 17:34:11.167373</td>\n",
       "      <td>The Financial Empowerment Center helps you wit...</td>\n",
       "      <td>None</td>\n",
       "      <td>scheduled</td>\n",
       "      <td>f131ff2e-cc01-441e-b5ca-f5445d6acafb</td>\n",
       "      <td>2018-07-14 17:38:31.532722</td>\n",
       "      <td>2018-07-14 17:38:31.532722</td>\n",
       "      <td>2019-01-14 20:17:52.698056</td>\n",
       "      <td>AT&amp;T Wireless</td>\n",
       "      <td>78152</td>\n",
       "      <td>Saint Hedwig</td>\n",
       "      <td>-6.000</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>Texas</td>\n",
       "      <td>Web SignUp</td>\n",
       "      <td>deactivated</td>\n",
       "      <td>English</td>\n",
       "      <td>STOP</td>\n",
       "      <td>KLRN - San Antonio</td>\n",
       "      <td>2018-08-28 20:58:04.700242</td>\n",
       "      <td>True</td>\n",
       "      <td>Texas</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2014-12-24</td>\n",
       "      <td>2014-12-24</td>\n",
       "      <td>ca75334a-2148-40c7-a0bf-8740dab820ec</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>5104465</td>\n",
       "      <td>1f3b3c32-a4a2-4af5-bed0-cb7f02719497</td>\n",
       "      <td>2018-12-27 18:31:13.017539</td>\n",
       "      <td>¡Ningún niño viene con un manual! Este año, pa...</td>\n",
       "      <td>None</td>\n",
       "      <td>scheduled</td>\n",
       "      <td>8a489a55-b762-4628-b246-6fde5341af12</td>\n",
       "      <td>2018-03-13 17:25:10.704334</td>\n",
       "      <td>2018-03-13 17:25:10.704334</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>84403</td>\n",
       "      <td>Ogden</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Utah</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>Spanish</td>\n",
       "      <td>None</td>\n",
       "      <td>United Way of Northern Utah - Ogden</td>\n",
       "      <td>2018-08-28 21:02:34.021621</td>\n",
       "      <td>True</td>\n",
       "      <td>Utah</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2017-09-17</td>\n",
       "      <td>2017-09-17</td>\n",
       "      <td>d8d83aa0-9028-46a5-885b-7a62d303c1e9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5104466 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   message_id outbound_message_created_at  \\\n",
       "0        ea851042-0aa4-4b1d-97eb-2ec187638c34  2018-01-08 22:38:22.181992   \n",
       "1        b23b4071-e7ae-40b1-822f-71905a77fbe3  2018-01-01 22:26:43.786828   \n",
       "2        dc98843c-224c-4634-b762-3b23dca92ff7  2018-01-01 22:26:55.864001   \n",
       "3        79f32613-5735-4dd8-9206-84856cc823c8  2018-01-01 22:33:04.532669   \n",
       "4        5cb896cc-34e6-4067-b602-3f7959a38452  2018-01-01 22:33:04.996658   \n",
       "...                                       ...                         ...   \n",
       "5104461  f56f60fe-e435-4d8b-975a-771ae324a880  2018-12-28 17:34:07.051202   \n",
       "5104462  f56f60fe-e435-4d8b-975a-771ae324a880  2018-12-28 17:34:10.823055   \n",
       "5104463  f56f60fe-e435-4d8b-975a-771ae324a880  2018-12-28 17:34:07.083416   \n",
       "5104464  f56f60fe-e435-4d8b-975a-771ae324a880  2018-12-28 17:34:11.167373   \n",
       "5104465  1f3b3c32-a4a2-4af5-bed0-cb7f02719497  2018-12-27 18:31:13.017539   \n",
       "\n",
       "                                     outbound_message_body  \\\n",
       "0        Describe the things your child points to in bo...   \n",
       "1        Seeing patterns is an important pre-math skill...   \n",
       "2        Help your child learn by imitating you. Encour...   \n",
       "3        When children's curiosity is nurtured, so is t...   \n",
       "4        Writing starts with scribbling! Try this game ...   \n",
       "...                                                    ...   \n",
       "5104461  The Financial Empowerment Center helps you wit...   \n",
       "5104462  The Financial Empowerment Center helps you wit...   \n",
       "5104463  The Financial Empowerment Center helps you wit...   \n",
       "5104464  The Financial Empowerment Center helps you wit...   \n",
       "5104465  ¡Ningún niño viene con un manual! Este año, pa...   \n",
       "\n",
       "                  outbound_message_partner_id message_type  \\\n",
       "0        3a18f949-d80f-481b-8cd8-3c73be63e67e       normal   \n",
       "1        d4d696af-37ff-427e-8035-2cb1bd630495       normal   \n",
       "2        d4d696af-37ff-427e-8035-2cb1bd630495       normal   \n",
       "3        3a18f949-d80f-481b-8cd8-3c73be63e67e       normal   \n",
       "4        3a18f949-d80f-481b-8cd8-3c73be63e67e       normal   \n",
       "...                                       ...          ...   \n",
       "5104461                                  None    scheduled   \n",
       "5104462                                  None    scheduled   \n",
       "5104463                                  None    scheduled   \n",
       "5104464                                  None    scheduled   \n",
       "5104465                                  None    scheduled   \n",
       "\n",
       "                                subscriber_id                 created_at  \\\n",
       "0        4d02bf4b-c61b-4227-a5b2-b4a81eae335a 2017-12-12 12:32:42.113661   \n",
       "1        16d242b2-c6b6-4f06-9f81-78ad2c7f766e 2017-06-19 13:25:46.393823   \n",
       "2        52ee6736-3a59-4a56-8bdb-4be856ef79c1 2017-06-19 13:26:52.539459   \n",
       "3        4d02bf4b-c61b-4227-a5b2-b4a81eae335a 2017-12-12 12:32:42.113661   \n",
       "4        4d02bf4b-c61b-4227-a5b2-b4a81eae335a 2017-12-12 12:32:42.113661   \n",
       "...                                       ...                        ...   \n",
       "5104461  b1e07f05-e263-40cb-ac73-a69484002a70 2017-11-23 00:08:36.017898   \n",
       "5104462  3e42e7e7-1021-4955-bb7b-d3ceb099a99b 2018-07-21 15:48:05.519417   \n",
       "5104463  768d610a-9f83-462d-ab47-fae174ca6725 2018-04-07 18:16:18.289722   \n",
       "5104464  f131ff2e-cc01-441e-b5ca-f5445d6acafb 2018-07-14 17:38:31.532722   \n",
       "5104465  8a489a55-b762-4628-b246-6fde5341af12 2018-03-13 17:25:10.704334   \n",
       "\n",
       "                       signedup_at             deactivated_at  \\\n",
       "0       2017-12-12 12:32:42.113661 2019-08-12 17:00:08.865406   \n",
       "1       2017-03-27 13:00:00.000000                        NaT   \n",
       "2       2017-04-03 09:58:00.000000                        NaT   \n",
       "3       2017-12-12 12:32:42.113661 2019-08-12 17:00:08.865406   \n",
       "4       2017-12-12 12:32:42.113661 2019-08-12 17:00:08.865406   \n",
       "...                            ...                        ...   \n",
       "5104461 2017-11-23 00:08:36.017898                        NaT   \n",
       "5104462 2018-07-21 15:48:05.519417                        NaT   \n",
       "5104463 2018-04-07 18:16:18.289722                        NaT   \n",
       "5104464 2018-07-14 17:38:31.532722 2019-01-14 20:17:52.698056   \n",
       "5104465 2018-03-13 17:25:10.704334                        NaT   \n",
       "\n",
       "                  carrier_name subscriber_zip_code subscriber_city  \\\n",
       "0          Onvoy/3 - Sybase365               90001     Los Angeles   \n",
       "1           T-Mobile USA, Inc.               80204          Denver   \n",
       "2                AT&T Wireless               80033     Wheat Ridge   \n",
       "3          Onvoy/3 - Sybase365               90001     Los Angeles   \n",
       "4          Onvoy/3 - Sybase365               90001     Los Angeles   \n",
       "...                        ...                 ...             ...   \n",
       "5104461       Verizon Wireless               78229     San Antonio   \n",
       "5104462  Sprint Spectrum, L.P.               78251     San Antonio   \n",
       "5104463     T-Mobile USA, Inc.               78148  Universal City   \n",
       "5104464          AT&T Wireless               78152    Saint Hedwig   \n",
       "5104465     T-Mobile USA, Inc.               84403           Ogden   \n",
       "\n",
       "         timezone_default_offset        timezone_name subscriber_state  \\\n",
       "0                         -8.000  America/Los_Angeles       California   \n",
       "1                         -7.000       America/Denver         Colorado   \n",
       "2                         -7.000       America/Denver         Colorado   \n",
       "3                         -8.000  America/Los_Angeles       California   \n",
       "4                         -8.000  America/Los_Angeles       California   \n",
       "...                          ...                  ...              ...   \n",
       "5104461                   -6.000      America/Chicago            Texas   \n",
       "5104462                   -6.000      America/Chicago            Texas   \n",
       "5104463                   -6.000      America/Chicago            Texas   \n",
       "5104464                   -6.000      America/Chicago            Texas   \n",
       "5104465                   -7.000       America/Denver             Utah   \n",
       "\n",
       "        subscriber_source subscriber_status subscriber_language  \\\n",
       "0              Web SignUp       deactivated             English   \n",
       "1             Sales Force         activated             English   \n",
       "2             Sales Force         activated             English   \n",
       "3              Web SignUp       deactivated             English   \n",
       "4              Web SignUp       deactivated             English   \n",
       "...                   ...               ...                 ...   \n",
       "5104461       Text SignUp         activated             English   \n",
       "5104462        Web SignUp         activated             English   \n",
       "5104463        Web SignUp         activated             English   \n",
       "5104464        Web SignUp       deactivated             English   \n",
       "5104465       Text SignUp         activated             Spanish   \n",
       "\n",
       "        subscriber_deactivation_method                         partner_name  \\\n",
       "0                              Age Out        United Way for Greater Austin   \n",
       "1                                 None                      Bright by Three   \n",
       "2                                 None                      Bright by Three   \n",
       "3                              Age Out        United Way for Greater Austin   \n",
       "4                              Age Out        United Way for Greater Austin   \n",
       "...                                ...                                  ...   \n",
       "5104461                           None                   KLRN - San Antonio   \n",
       "5104462                           None                   KLRN - San Antonio   \n",
       "5104463                           None                   KLRN - San Antonio   \n",
       "5104464                           STOP                   KLRN - San Antonio   \n",
       "5104465                           None  United Way of Northern Utah - Ogden   \n",
       "\n",
       "              partner_created_date  partner_is_active partner_state  \\\n",
       "0       2018-08-28 20:57:33.540197               True         Texas   \n",
       "1       2018-11-30 19:20:34.357527               True      Colorado   \n",
       "2       2018-11-30 19:20:34.357527               True      Colorado   \n",
       "3       2018-08-28 20:57:33.540197               True         Texas   \n",
       "4       2018-08-28 20:57:33.540197               True         Texas   \n",
       "...                            ...                ...           ...   \n",
       "5104461 2018-08-28 20:58:04.700242               True         Texas   \n",
       "5104462 2018-08-28 20:58:04.700242               True         Texas   \n",
       "5104463 2018-08-28 20:58:04.700242               True         Texas   \n",
       "5104464 2018-08-28 20:58:04.700242               True         Texas   \n",
       "5104465 2018-08-28 21:02:34.021621               True          Utah   \n",
       "\n",
       "         children_count dob_youngest_child dob_oldest_child  \\\n",
       "0                 1.000         2017-12-12       2017-12-12   \n",
       "1                 1.000         2013-11-29       2013-11-29   \n",
       "2                 1.000         2017-03-31       2017-03-31   \n",
       "3                 1.000         2017-12-12       2017-12-12   \n",
       "4                 1.000         2017-12-12       2017-12-12   \n",
       "...                 ...                ...              ...   \n",
       "5104461           1.000         2015-08-15       2015-08-15   \n",
       "5104462           1.000         2015-11-15       2015-11-15   \n",
       "5104463           3.000         2010-11-01       2016-12-11   \n",
       "5104464           1.000         2014-12-24       2014-12-24   \n",
       "5104465           1.000         2017-09-17       2017-09-17   \n",
       "\n",
       "                         scheduled_message_id  \n",
       "0                                        None  \n",
       "1                                        None  \n",
       "2                                        None  \n",
       "3                                        None  \n",
       "4                                        None  \n",
       "...                                       ...  \n",
       "5104461  ca75334a-2148-40c7-a0bf-8740dab820ec  \n",
       "5104462  ca75334a-2148-40c7-a0bf-8740dab820ec  \n",
       "5104463  ca75334a-2148-40c7-a0bf-8740dab820ec  \n",
       "5104464  ca75334a-2148-40c7-a0bf-8740dab820ec  \n",
       "5104465  d8d83aa0-9028-46a5-885b-7a62d303c1e9  \n",
       "\n",
       "[5104466 rows x 27 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "archived_messages_2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download the archived messages dataset for 2018\n",
    "\n",
    "archived_messages_2018.to_csv(r'/Users/lisafan/Desktop/BBT/bbt_archived_messages_2018.csv', index=False)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to PostgeSQL\n",
    "\n",
    "try:\n",
    "    conn = psycopg2.connect(user = \"pbiusr1\",\n",
    "                                  password = \"RE_^V%Gj@EL6R!G\",\n",
    "                                  host = \"app.brightbytext.org\",\n",
    "                                  port = \"5432\",\n",
    "                                  database = \"bbtapi\")\n",
    "\n",
    "except (Exception, psycopg2.Error) as error :\n",
    "    print (\"Error while connecting to PostgreSQL\", error)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query to get archived outbound messages for 1H 2019\n",
    "\n",
    "sql = '''\n",
    "    select\n",
    "    om.message_id,\n",
    "    om.created_at as outbound_message_created_at,\n",
    "    om.body as outbound_message_body,\n",
    "    om.partner_id as outbound_message_partner_id,\n",
    "    mt.name as message_type,\n",
    "    s.id as subscriber_id,\n",
    "    s.created_at,\n",
    "    s.signedup_at,\n",
    "    s.deactivated_at,\n",
    "    s.carrier_name,\n",
    "    z.code as subscriber_zip_code,\n",
    "    z.city as subscriber_city,\n",
    "    t.default_offset as timezone_default_offset,\n",
    "    t.iana_name as timezone_name,\n",
    "    s_state.name as subscriber_state,\n",
    "    ss.name as subscriber_source,\n",
    "    sst.name as subscriber_status,\n",
    "    l.name as subscriber_language,\n",
    "    sdr.name as subscriber_deactivation_method,\n",
    "    p.name as partner_name,\n",
    "    p.created_at as partner_created_date,\n",
    "    p.is_active as partner_is_active,\n",
    "    p_state.name as partner_state,\n",
    "    c.children_count,\n",
    "    c.dob_youngest_child,\n",
    "    c.dob_oldest_child,\n",
    "    msm.scheduled_message_id\n",
    "    \n",
    "from \n",
    "(\n",
    "select *\n",
    "    from archive.outbound_messages\n",
    "    where extract(year from created_at) = 2019 \n",
    "      and extract(month from created_at) <= 6\n",
    ") om\n",
    "left join public.message_types as mt\n",
    "on om.message_type_id = mt.id \n",
    "left join messages_scheduled_messages msm \n",
    "on om.message_id = msm.message_id \n",
    "left join public.v_subscribers as s\n",
    "on om.subscriber_id = s.id\n",
    "left join \n",
    "(\n",
    "    select subscriber_id, count(*) as children_count, min(date_of_birth) as dob_youngest_child, max(date_of_birth) as dob_oldest_child\n",
    "    from public.children \n",
    "    group by subscriber_id\n",
    ") c \n",
    "on om.subscriber_id = c.subscriber_id\n",
    "left join public.zip_codes as z \n",
    "on z.id = s.zip_id\n",
    "left join public.timezones t\n",
    "on z.time_zone_id = t.id\n",
    "left join public.states as s_state\n",
    "on s_state.id = z.state_id\n",
    "left join public.subscriber_sources as ss \n",
    "on ss.id = s.source_id\n",
    "left join public.subscriber_statuses as sst \n",
    "on sst.id = s.status_id\n",
    "left join public.languages as l\n",
    "on l.id = s.language_id\n",
    "left join public.subscriber_deactivation_reasons as sdr \n",
    "on sdr.id = s.deactivation_reason_id\n",
    "left join public.partners as p\n",
    "on p.id = s.partner_id\n",
    "left join public.partner_types as pt \n",
    "on pt.id = p.type_id\n",
    "left join public.states as p_state\n",
    "on p_state.id = p.state_id\n",
    "'''\n",
    "archived_messages_2019_1h = pd.read_sql_query(sql, conn)\n",
    "conn = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>message_id</th>\n",
       "      <th>outbound_message_created_at</th>\n",
       "      <th>outbound_message_body</th>\n",
       "      <th>outbound_message_partner_id</th>\n",
       "      <th>message_type</th>\n",
       "      <th>subscriber_id</th>\n",
       "      <th>created_at</th>\n",
       "      <th>signedup_at</th>\n",
       "      <th>deactivated_at</th>\n",
       "      <th>carrier_name</th>\n",
       "      <th>subscriber_zip_code</th>\n",
       "      <th>subscriber_city</th>\n",
       "      <th>timezone_default_offset</th>\n",
       "      <th>timezone_name</th>\n",
       "      <th>subscriber_state</th>\n",
       "      <th>subscriber_source</th>\n",
       "      <th>subscriber_status</th>\n",
       "      <th>subscriber_language</th>\n",
       "      <th>subscriber_deactivation_method</th>\n",
       "      <th>partner_name</th>\n",
       "      <th>partner_created_date</th>\n",
       "      <th>partner_is_active</th>\n",
       "      <th>partner_state</th>\n",
       "      <th>children_count</th>\n",
       "      <th>dob_youngest_child</th>\n",
       "      <th>dob_oldest_child</th>\n",
       "      <th>scheduled_message_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>71f710a6-0ac1-4d86-8d00-aa5d3d0e538b</td>\n",
       "      <td>2019-04-11 16:31:54.071518</td>\n",
       "      <td>BBT provides resources for children prenatal t...</td>\n",
       "      <td>71718ec5-049d-47b9-80db-f9fbd0503152</td>\n",
       "      <td>signup</td>\n",
       "      <td>95c68cc4-d62e-4951-b2b0-94b6e4951974</td>\n",
       "      <td>2019-04-11 16:29:49.966083</td>\n",
       "      <td>2019-04-11 16:32:14.249942</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Cricket Wireless - ATT - SVR</td>\n",
       "      <td>93710</td>\n",
       "      <td>Fresno</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>ValleyPBS - Fresno, CA</td>\n",
       "      <td>2018-08-28 20:57:03.730535</td>\n",
       "      <td>True</td>\n",
       "      <td>California</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2016-09-24</td>\n",
       "      <td>2016-09-24</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>d246e81a-402b-4151-ae25-3d0f425eb74e</td>\n",
       "      <td>2019-04-11 14:48:23.417284</td>\n",
       "      <td>We don't recognize this zip code. Please check...</td>\n",
       "      <td>71718ec5-049d-47b9-80db-f9fbd0503152</td>\n",
       "      <td>signup</td>\n",
       "      <td>446556b8-9fd0-4c1f-91b4-e4216e8e473a</td>\n",
       "      <td>2019-04-11 14:30:42.240335</td>\n",
       "      <td>2019-04-11 14:47:43.708584</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Sprint Spectrum, L.P.</td>\n",
       "      <td>93230</td>\n",
       "      <td>Hanford</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>ValleyPBS - Fresno, CA</td>\n",
       "      <td>2018-08-28 20:57:03.730535</td>\n",
       "      <td>True</td>\n",
       "      <td>California</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2011-10-25</td>\n",
       "      <td>2011-10-25</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>ea8008c1-373d-447c-bb60-cd89c87bce96</td>\n",
       "      <td>2019-04-11 04:41:23.301082</td>\n",
       "      <td>Reply Y if 86305 is correct or re-send your zi...</td>\n",
       "      <td>00000000-0000-0000-0000-000000000001</td>\n",
       "      <td>signup</td>\n",
       "      <td>054c0bb6-0385-4dc0-a50c-b10a96c0548a</td>\n",
       "      <td>2019-04-11 04:38:50.311933</td>\n",
       "      <td>2019-04-11 04:41:06.916667</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Verizon Wireless</td>\n",
       "      <td>86305</td>\n",
       "      <td>Prescott</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Phoenix</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>ArizonaPBS</td>\n",
       "      <td>2018-03-25 21:32:18.027322</td>\n",
       "      <td>True</td>\n",
       "      <td>Arizona</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2017-03-17</td>\n",
       "      <td>2017-03-17</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>eb9a3d3d-d9ee-42a5-8b82-bbfba4f8e9af</td>\n",
       "      <td>2019-04-19 03:00:14.131447</td>\n",
       "      <td>We want to learn more about you! Please fill o...</td>\n",
       "      <td>d4d696af-37ff-427e-8035-2cb1bd630495</td>\n",
       "      <td>survey</td>\n",
       "      <td>80ab2d95-06bf-4f64-963f-d935cf92fede</td>\n",
       "      <td>2019-03-25 18:53:50.387224</td>\n",
       "      <td>2019-03-25 18:53:50.387224</td>\n",
       "      <td>2019-08-19 18:44:46.334876</td>\n",
       "      <td>Verizon Wireless</td>\n",
       "      <td>80022</td>\n",
       "      <td>Commerce City</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>deactivated</td>\n",
       "      <td>English</td>\n",
       "      <td>STOP</td>\n",
       "      <td>Bright by Three</td>\n",
       "      <td>2018-11-30 19:20:34.357527</td>\n",
       "      <td>True</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2019-03-12</td>\n",
       "      <td>2019-03-12</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>ea8008c1-373d-447c-bb60-cd89c87bce96</td>\n",
       "      <td>2019-04-25 15:04:22.865104</td>\n",
       "      <td>Reply Y if 28083 is correct or re-send your zi...</td>\n",
       "      <td>97f6bf86-bbab-4e74-b3a9-3940bbc340a9</td>\n",
       "      <td>signup</td>\n",
       "      <td>d6dc7b09-5916-444b-b825-7f36e9312a64</td>\n",
       "      <td>2019-04-25 13:59:53.439772</td>\n",
       "      <td>2019-04-25 15:03:38.652131</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Sprint Spectrum, L.P.</td>\n",
       "      <td>28083</td>\n",
       "      <td>Kannapolis</td>\n",
       "      <td>-5.000</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>UNC-TV- North Carolina</td>\n",
       "      <td>2018-08-28 21:01:13.109946</td>\n",
       "      <td>True</td>\n",
       "      <td>North Carolina</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2019-01-12</td>\n",
       "      <td>2019-01-12</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3013494</td>\n",
       "      <td>06092af5-b574-4fbc-b6f1-a623b02a6a5c</td>\n",
       "      <td>2019-01-28 17:31:43.337831</td>\n",
       "      <td>Parents and Caregivers! These resources will i...</td>\n",
       "      <td>None</td>\n",
       "      <td>scheduled</td>\n",
       "      <td>fd5bed6e-d2b9-4616-91fb-f992b6bd0e53</td>\n",
       "      <td>2018-05-22 14:02:02.456168</td>\n",
       "      <td>2019-10-16 15:27:23.502832</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Verizon Wireless</td>\n",
       "      <td>84401</td>\n",
       "      <td>Ogden</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Utah</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>United Way of Northern Utah - Ogden</td>\n",
       "      <td>2018-08-28 21:02:34.021621</td>\n",
       "      <td>True</td>\n",
       "      <td>Utah</td>\n",
       "      <td>3.000</td>\n",
       "      <td>2011-02-23</td>\n",
       "      <td>2014-10-06</td>\n",
       "      <td>dace278e-6a7e-4549-aa6e-7a90f0b2887d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3013495</td>\n",
       "      <td>06092af5-b574-4fbc-b6f1-a623b02a6a5c</td>\n",
       "      <td>2019-01-28 17:31:46.290935</td>\n",
       "      <td>Parents and Caregivers! These resources will i...</td>\n",
       "      <td>None</td>\n",
       "      <td>scheduled</td>\n",
       "      <td>b7fc9b64-709f-4bae-b7f5-1106a485a92b</td>\n",
       "      <td>2018-04-27 14:34:19.602987</td>\n",
       "      <td>2018-04-27 14:34:19.602987</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Verizon Wireless</td>\n",
       "      <td>84302</td>\n",
       "      <td>Brigham City</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Utah</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>United Way of Northern Utah - Ogden</td>\n",
       "      <td>2018-08-28 21:02:34.021621</td>\n",
       "      <td>True</td>\n",
       "      <td>Utah</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2014-03-31</td>\n",
       "      <td>2014-03-31</td>\n",
       "      <td>dace278e-6a7e-4549-aa6e-7a90f0b2887d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3013496</td>\n",
       "      <td>06092af5-b574-4fbc-b6f1-a623b02a6a5c</td>\n",
       "      <td>2019-01-28 17:31:47.202758</td>\n",
       "      <td>Parents and Caregivers! These resources will i...</td>\n",
       "      <td>None</td>\n",
       "      <td>scheduled</td>\n",
       "      <td>6f048348-4f4b-42c4-beb7-4ef2b19e42f6</td>\n",
       "      <td>2018-06-06 20:56:17.925547</td>\n",
       "      <td>2018-06-06 20:56:17.925547</td>\n",
       "      <td>2019-07-04 18:20:28.972685</td>\n",
       "      <td>Verizon Wireless</td>\n",
       "      <td>84401</td>\n",
       "      <td>Ogden</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Utah</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>deactivated</td>\n",
       "      <td>English</td>\n",
       "      <td>STOP</td>\n",
       "      <td>United Way of Northern Utah - Ogden</td>\n",
       "      <td>2018-08-28 21:02:34.021621</td>\n",
       "      <td>True</td>\n",
       "      <td>Utah</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2013-10-31</td>\n",
       "      <td>2013-10-31</td>\n",
       "      <td>dace278e-6a7e-4549-aa6e-7a90f0b2887d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3013497</td>\n",
       "      <td>06092af5-b574-4fbc-b6f1-a623b02a6a5c</td>\n",
       "      <td>2019-01-28 17:31:49.189350</td>\n",
       "      <td>Parents and Caregivers! These resources will i...</td>\n",
       "      <td>None</td>\n",
       "      <td>scheduled</td>\n",
       "      <td>3d9210c2-5472-44b8-ad4d-0ddf877c9e2d</td>\n",
       "      <td>2018-09-04 18:46:55.020173</td>\n",
       "      <td>2018-09-04 18:46:55.020173</td>\n",
       "      <td>NaT</td>\n",
       "      <td>AT&amp;T Wireless</td>\n",
       "      <td>84093</td>\n",
       "      <td>Sandy</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Utah</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>United Way of Northern Utah - Ogden</td>\n",
       "      <td>2018-08-28 21:02:34.021621</td>\n",
       "      <td>True</td>\n",
       "      <td>Utah</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2014-11-16</td>\n",
       "      <td>2014-11-16</td>\n",
       "      <td>dace278e-6a7e-4549-aa6e-7a90f0b2887d</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3013498</td>\n",
       "      <td>06092af5-b574-4fbc-b6f1-a623b02a6a5c</td>\n",
       "      <td>2019-01-28 17:31:53.272857</td>\n",
       "      <td>Parents and Caregivers! These resources will i...</td>\n",
       "      <td>None</td>\n",
       "      <td>scheduled</td>\n",
       "      <td>fce684e5-ed7d-4084-8aaf-235ce7f21999</td>\n",
       "      <td>2018-05-01 15:42:54.052687</td>\n",
       "      <td>2018-05-01 15:42:54.052687</td>\n",
       "      <td>NaT</td>\n",
       "      <td>AT&amp;T Wireless</td>\n",
       "      <td>84720</td>\n",
       "      <td>Cedar City</td>\n",
       "      <td>-7.000</td>\n",
       "      <td>America/Denver</td>\n",
       "      <td>Utah</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>United Way of Northern Utah - Ogden</td>\n",
       "      <td>2018-08-28 21:02:34.021621</td>\n",
       "      <td>True</td>\n",
       "      <td>Utah</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2012-02-14</td>\n",
       "      <td>2012-02-14</td>\n",
       "      <td>dace278e-6a7e-4549-aa6e-7a90f0b2887d</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3013499 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   message_id outbound_message_created_at  \\\n",
       "0        71f710a6-0ac1-4d86-8d00-aa5d3d0e538b  2019-04-11 16:31:54.071518   \n",
       "1        d246e81a-402b-4151-ae25-3d0f425eb74e  2019-04-11 14:48:23.417284   \n",
       "2        ea8008c1-373d-447c-bb60-cd89c87bce96  2019-04-11 04:41:23.301082   \n",
       "3        eb9a3d3d-d9ee-42a5-8b82-bbfba4f8e9af  2019-04-19 03:00:14.131447   \n",
       "4        ea8008c1-373d-447c-bb60-cd89c87bce96  2019-04-25 15:04:22.865104   \n",
       "...                                       ...                         ...   \n",
       "3013494  06092af5-b574-4fbc-b6f1-a623b02a6a5c  2019-01-28 17:31:43.337831   \n",
       "3013495  06092af5-b574-4fbc-b6f1-a623b02a6a5c  2019-01-28 17:31:46.290935   \n",
       "3013496  06092af5-b574-4fbc-b6f1-a623b02a6a5c  2019-01-28 17:31:47.202758   \n",
       "3013497  06092af5-b574-4fbc-b6f1-a623b02a6a5c  2019-01-28 17:31:49.189350   \n",
       "3013498  06092af5-b574-4fbc-b6f1-a623b02a6a5c  2019-01-28 17:31:53.272857   \n",
       "\n",
       "                                     outbound_message_body  \\\n",
       "0        BBT provides resources for children prenatal t...   \n",
       "1        We don't recognize this zip code. Please check...   \n",
       "2        Reply Y if 86305 is correct or re-send your zi...   \n",
       "3        We want to learn more about you! Please fill o...   \n",
       "4        Reply Y if 28083 is correct or re-send your zi...   \n",
       "...                                                    ...   \n",
       "3013494  Parents and Caregivers! These resources will i...   \n",
       "3013495  Parents and Caregivers! These resources will i...   \n",
       "3013496  Parents and Caregivers! These resources will i...   \n",
       "3013497  Parents and Caregivers! These resources will i...   \n",
       "3013498  Parents and Caregivers! These resources will i...   \n",
       "\n",
       "                  outbound_message_partner_id message_type  \\\n",
       "0        71718ec5-049d-47b9-80db-f9fbd0503152       signup   \n",
       "1        71718ec5-049d-47b9-80db-f9fbd0503152       signup   \n",
       "2        00000000-0000-0000-0000-000000000001       signup   \n",
       "3        d4d696af-37ff-427e-8035-2cb1bd630495       survey   \n",
       "4        97f6bf86-bbab-4e74-b3a9-3940bbc340a9       signup   \n",
       "...                                       ...          ...   \n",
       "3013494                                  None    scheduled   \n",
       "3013495                                  None    scheduled   \n",
       "3013496                                  None    scheduled   \n",
       "3013497                                  None    scheduled   \n",
       "3013498                                  None    scheduled   \n",
       "\n",
       "                                subscriber_id                 created_at  \\\n",
       "0        95c68cc4-d62e-4951-b2b0-94b6e4951974 2019-04-11 16:29:49.966083   \n",
       "1        446556b8-9fd0-4c1f-91b4-e4216e8e473a 2019-04-11 14:30:42.240335   \n",
       "2        054c0bb6-0385-4dc0-a50c-b10a96c0548a 2019-04-11 04:38:50.311933   \n",
       "3        80ab2d95-06bf-4f64-963f-d935cf92fede 2019-03-25 18:53:50.387224   \n",
       "4        d6dc7b09-5916-444b-b825-7f36e9312a64 2019-04-25 13:59:53.439772   \n",
       "...                                       ...                        ...   \n",
       "3013494  fd5bed6e-d2b9-4616-91fb-f992b6bd0e53 2018-05-22 14:02:02.456168   \n",
       "3013495  b7fc9b64-709f-4bae-b7f5-1106a485a92b 2018-04-27 14:34:19.602987   \n",
       "3013496  6f048348-4f4b-42c4-beb7-4ef2b19e42f6 2018-06-06 20:56:17.925547   \n",
       "3013497  3d9210c2-5472-44b8-ad4d-0ddf877c9e2d 2018-09-04 18:46:55.020173   \n",
       "3013498  fce684e5-ed7d-4084-8aaf-235ce7f21999 2018-05-01 15:42:54.052687   \n",
       "\n",
       "                       signedup_at             deactivated_at  \\\n",
       "0       2019-04-11 16:32:14.249942                        NaT   \n",
       "1       2019-04-11 14:47:43.708584                        NaT   \n",
       "2       2019-04-11 04:41:06.916667                        NaT   \n",
       "3       2019-03-25 18:53:50.387224 2019-08-19 18:44:46.334876   \n",
       "4       2019-04-25 15:03:38.652131                        NaT   \n",
       "...                            ...                        ...   \n",
       "3013494 2019-10-16 15:27:23.502832                        NaT   \n",
       "3013495 2018-04-27 14:34:19.602987                        NaT   \n",
       "3013496 2018-06-06 20:56:17.925547 2019-07-04 18:20:28.972685   \n",
       "3013497 2018-09-04 18:46:55.020173                        NaT   \n",
       "3013498 2018-05-01 15:42:54.052687                        NaT   \n",
       "\n",
       "                         carrier_name subscriber_zip_code subscriber_city  \\\n",
       "0        Cricket Wireless - ATT - SVR               93710          Fresno   \n",
       "1               Sprint Spectrum, L.P.               93230         Hanford   \n",
       "2                    Verizon Wireless               86305        Prescott   \n",
       "3                    Verizon Wireless               80022   Commerce City   \n",
       "4               Sprint Spectrum, L.P.               28083      Kannapolis   \n",
       "...                               ...                 ...             ...   \n",
       "3013494              Verizon Wireless               84401           Ogden   \n",
       "3013495              Verizon Wireless               84302    Brigham City   \n",
       "3013496              Verizon Wireless               84401           Ogden   \n",
       "3013497                 AT&T Wireless               84093           Sandy   \n",
       "3013498                 AT&T Wireless               84720      Cedar City   \n",
       "\n",
       "         timezone_default_offset        timezone_name subscriber_state  \\\n",
       "0                         -8.000  America/Los_Angeles       California   \n",
       "1                         -8.000  America/Los_Angeles       California   \n",
       "2                         -7.000      America/Phoenix          Arizona   \n",
       "3                         -7.000       America/Denver         Colorado   \n",
       "4                         -5.000     America/New_York   North Carolina   \n",
       "...                          ...                  ...              ...   \n",
       "3013494                   -7.000       America/Denver             Utah   \n",
       "3013495                   -7.000       America/Denver             Utah   \n",
       "3013496                   -7.000       America/Denver             Utah   \n",
       "3013497                   -7.000       America/Denver             Utah   \n",
       "3013498                   -7.000       America/Denver             Utah   \n",
       "\n",
       "        subscriber_source subscriber_status subscriber_language  \\\n",
       "0             Text SignUp         activated             English   \n",
       "1             Text SignUp         activated             English   \n",
       "2             Text SignUp         activated             English   \n",
       "3             Text SignUp       deactivated             English   \n",
       "4             Text SignUp         activated             English   \n",
       "...                   ...               ...                 ...   \n",
       "3013494       Text SignUp         activated             English   \n",
       "3013495       Text SignUp         activated             English   \n",
       "3013496       Text SignUp       deactivated             English   \n",
       "3013497       Text SignUp         activated             English   \n",
       "3013498       Text SignUp         activated             English   \n",
       "\n",
       "        subscriber_deactivation_method                         partner_name  \\\n",
       "0                                 None               ValleyPBS - Fresno, CA   \n",
       "1                                 None               ValleyPBS - Fresno, CA   \n",
       "2                                 None                           ArizonaPBS   \n",
       "3                                 STOP                      Bright by Three   \n",
       "4                                 None               UNC-TV- North Carolina   \n",
       "...                                ...                                  ...   \n",
       "3013494                           None  United Way of Northern Utah - Ogden   \n",
       "3013495                           None  United Way of Northern Utah - Ogden   \n",
       "3013496                           STOP  United Way of Northern Utah - Ogden   \n",
       "3013497                           None  United Way of Northern Utah - Ogden   \n",
       "3013498                           None  United Way of Northern Utah - Ogden   \n",
       "\n",
       "              partner_created_date  partner_is_active   partner_state  \\\n",
       "0       2018-08-28 20:57:03.730535               True      California   \n",
       "1       2018-08-28 20:57:03.730535               True      California   \n",
       "2       2018-03-25 21:32:18.027322               True         Arizona   \n",
       "3       2018-11-30 19:20:34.357527               True        Colorado   \n",
       "4       2018-08-28 21:01:13.109946               True  North Carolina   \n",
       "...                            ...                ...             ...   \n",
       "3013494 2018-08-28 21:02:34.021621               True            Utah   \n",
       "3013495 2018-08-28 21:02:34.021621               True            Utah   \n",
       "3013496 2018-08-28 21:02:34.021621               True            Utah   \n",
       "3013497 2018-08-28 21:02:34.021621               True            Utah   \n",
       "3013498 2018-08-28 21:02:34.021621               True            Utah   \n",
       "\n",
       "         children_count dob_youngest_child dob_oldest_child  \\\n",
       "0                 1.000         2016-09-24       2016-09-24   \n",
       "1                 1.000         2011-10-25       2011-10-25   \n",
       "2                 1.000         2017-03-17       2017-03-17   \n",
       "3                 1.000         2019-03-12       2019-03-12   \n",
       "4                 1.000         2019-01-12       2019-01-12   \n",
       "...                 ...                ...              ...   \n",
       "3013494           3.000         2011-02-23       2014-10-06   \n",
       "3013495           1.000         2014-03-31       2014-03-31   \n",
       "3013496           1.000         2013-10-31       2013-10-31   \n",
       "3013497           1.000         2014-11-16       2014-11-16   \n",
       "3013498           1.000         2012-02-14       2012-02-14   \n",
       "\n",
       "                         scheduled_message_id  \n",
       "0                                        None  \n",
       "1                                        None  \n",
       "2                                        None  \n",
       "3                                        None  \n",
       "4                                        None  \n",
       "...                                       ...  \n",
       "3013494  dace278e-6a7e-4549-aa6e-7a90f0b2887d  \n",
       "3013495  dace278e-6a7e-4549-aa6e-7a90f0b2887d  \n",
       "3013496  dace278e-6a7e-4549-aa6e-7a90f0b2887d  \n",
       "3013497  dace278e-6a7e-4549-aa6e-7a90f0b2887d  \n",
       "3013498  dace278e-6a7e-4549-aa6e-7a90f0b2887d  \n",
       "\n",
       "[3013499 rows x 27 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "archived_messages_2019_1h"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download the archived messages dataset for 2019 1H\n",
    "\n",
    "archived_messages_2019_1h.to_csv(r'/Users/lisafan/Desktop/BBT/bbt_archived_messages_2019_1h.csv', index=False)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to PostgeSQL\n",
    "\n",
    "try:\n",
    "    conn = psycopg2.connect(user = \"pbiusr1\",\n",
    "                                  password = \"RE_^V%Gj@EL6R!G\",\n",
    "                                  host = \"app.brightbytext.org\",\n",
    "                                  port = \"5432\",\n",
    "                                  database = \"bbtapi\")\n",
    "\n",
    "except (Exception, psycopg2.Error) as error :\n",
    "    print (\"Error while connecting to PostgreSQL\", error)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query to get archived outbound messages for 2H 2019\n",
    "\n",
    "sql = '''\n",
    "    select\n",
    "    om.message_id,\n",
    "    om.created_at as outbound_message_created_at,\n",
    "    om.body as outbound_message_body,\n",
    "    om.partner_id as outbound_message_partner_id,\n",
    "    mt.name as message_type,\n",
    "    s.id as subscriber_id,\n",
    "    s.created_at,\n",
    "    s.signedup_at,\n",
    "    s.deactivated_at,\n",
    "    s.carrier_name,\n",
    "    z.code as subscriber_zip_code,\n",
    "    z.city as subscriber_city,\n",
    "    t.default_offset as timezone_default_offset,\n",
    "    t.iana_name as timezone_name,\n",
    "    s_state.name as subscriber_state,\n",
    "    ss.name as subscriber_source,\n",
    "    sst.name as subscriber_status,\n",
    "    l.name as subscriber_language,\n",
    "    sdr.name as subscriber_deactivation_method,\n",
    "    p.name as partner_name,\n",
    "    p.created_at as partner_created_date,\n",
    "    p.is_active as partner_is_active,\n",
    "    p_state.name as partner_state,\n",
    "    c.children_count,\n",
    "    c.dob_youngest_child,\n",
    "    c.dob_oldest_child,\n",
    "    msm.scheduled_message_id\n",
    "    \n",
    "from \n",
    "(\n",
    "select *\n",
    "    from archive.outbound_messages\n",
    "    where extract(year from created_at) = 2019 \n",
    "      and extract(month from created_at) > 6\n",
    ") om\n",
    "left join public.message_types as mt\n",
    "on om.message_type_id = mt.id \n",
    "left join messages_scheduled_messages msm \n",
    "on om.message_id = msm.message_id \n",
    "left join public.v_subscribers as s\n",
    "on om.subscriber_id = s.id\n",
    "left join \n",
    "(\n",
    "    select subscriber_id, count(*) as children_count, min(date_of_birth) as dob_youngest_child, max(date_of_birth) as dob_oldest_child\n",
    "    from public.children \n",
    "    group by subscriber_id\n",
    ") c \n",
    "on om.subscriber_id = c.subscriber_id\n",
    "left join public.zip_codes as z \n",
    "on z.id = s.zip_id\n",
    "left join public.timezones t\n",
    "on z.time_zone_id = t.id\n",
    "left join public.states as s_state\n",
    "on s_state.id = z.state_id\n",
    "left join public.subscriber_sources as ss \n",
    "on ss.id = s.source_id\n",
    "left join public.subscriber_statuses as sst \n",
    "on sst.id = s.status_id\n",
    "left join public.languages as l\n",
    "on l.id = s.language_id\n",
    "left join public.subscriber_deactivation_reasons as sdr \n",
    "on sdr.id = s.deactivation_reason_id\n",
    "left join public.partners as p\n",
    "on p.id = s.partner_id\n",
    "left join public.partner_types as pt \n",
    "on pt.id = p.type_id\n",
    "left join public.states as p_state\n",
    "on p_state.id = p.state_id\n",
    "'''\n",
    "archived_messages_2019_2h = pd.read_sql_query(sql, conn)\n",
    "conn = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "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>message_id</th>\n",
       "      <th>outbound_message_created_at</th>\n",
       "      <th>outbound_message_body</th>\n",
       "      <th>outbound_message_partner_id</th>\n",
       "      <th>message_type</th>\n",
       "      <th>subscriber_id</th>\n",
       "      <th>created_at</th>\n",
       "      <th>signedup_at</th>\n",
       "      <th>deactivated_at</th>\n",
       "      <th>carrier_name</th>\n",
       "      <th>subscriber_zip_code</th>\n",
       "      <th>subscriber_city</th>\n",
       "      <th>timezone_default_offset</th>\n",
       "      <th>timezone_name</th>\n",
       "      <th>subscriber_state</th>\n",
       "      <th>subscriber_source</th>\n",
       "      <th>subscriber_status</th>\n",
       "      <th>subscriber_language</th>\n",
       "      <th>subscriber_deactivation_method</th>\n",
       "      <th>partner_name</th>\n",
       "      <th>partner_created_date</th>\n",
       "      <th>partner_is_active</th>\n",
       "      <th>partner_state</th>\n",
       "      <th>children_count</th>\n",
       "      <th>dob_youngest_child</th>\n",
       "      <th>dob_oldest_child</th>\n",
       "      <th>scheduled_message_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>356f0eca-f6ed-44f2-8ab3-89fb919f0286</td>\n",
       "      <td>2019-07-03 02:59:09.057012</td>\n",
       "      <td>Louisiana Public Broadcasting welcomes you to ...</td>\n",
       "      <td>0cf81fa0-fd85-44da-8702-d6e0eeb4fc8d</td>\n",
       "      <td>signup</td>\n",
       "      <td>da37347f-e2e8-4d19-ac99-9905cea6404a</td>\n",
       "      <td>2019-07-03 02:59:08.814264</td>\n",
       "      <td>2019-07-03 03:03:34.103855</td>\n",
       "      <td>2019-08-04 16:01:01.484052</td>\n",
       "      <td>Verizon Wireless</td>\n",
       "      <td>70769</td>\n",
       "      <td>Prairieville</td>\n",
       "      <td>-6.000</td>\n",
       "      <td>America/Chicago</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>deactivated</td>\n",
       "      <td>English</td>\n",
       "      <td>STOP</td>\n",
       "      <td>Louisiana Public Broadcasting</td>\n",
       "      <td>2019-04-17 19:49:15.007478</td>\n",
       "      <td>True</td>\n",
       "      <td>Louisiana</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2016-12-21</td>\n",
       "      <td>2016-12-21</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>39397597-c1a7-4218-9622-864380247ce0</td>\n",
       "      <td>2019-07-03 15:00:14.264828</td>\n",
       "      <td>Remember making forts and playing in cardboard...</td>\n",
       "      <td>d4d696af-37ff-427e-8035-2cb1bd630495</td>\n",
       "      <td>normal</td>\n",
       "      <td>48405053-41fb-4750-b866-d87b6d426162</td>\n",
       "      <td>2019-01-03 22:29:20.341875</td>\n",
       "      <td>2019-01-03 22:29:20.341875</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Verizon Wireless</td>\n",
       "      <td>14020</td>\n",
       "      <td>Batavia</td>\n",
       "      <td>-5.000</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>New York</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>Bright by Three</td>\n",
       "      <td>2018-11-30 19:20:34.357527</td>\n",
       "      <td>True</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2017-12-27</td>\n",
       "      <td>2017-12-27</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>a13fae68-63e5-44fd-8b47-23fb4b9f23bc</td>\n",
       "      <td>2019-07-03 15:00:15.266724</td>\n",
       "      <td>Use sunscreen every time your child is in the ...</td>\n",
       "      <td>d4d696af-37ff-427e-8035-2cb1bd630495</td>\n",
       "      <td>normal</td>\n",
       "      <td>8941c36d-f05b-4edf-970f-fa0584f19b72</td>\n",
       "      <td>2018-06-24 22:38:51.605720</td>\n",
       "      <td>2018-06-24 22:38:51.605720</td>\n",
       "      <td>NaT</td>\n",
       "      <td>AT&amp;T Wireless</td>\n",
       "      <td>11222</td>\n",
       "      <td>Brooklyn</td>\n",
       "      <td>-5.000</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>New York</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>Bright by Three</td>\n",
       "      <td>2018-11-30 19:20:34.357527</td>\n",
       "      <td>True</td>\n",
       "      <td>Colorado</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2018-02-02</td>\n",
       "      <td>2018-02-02</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>87db92b3-7247-4f32-a103-3f647652901b</td>\n",
       "      <td>2019-07-03 15:00:16.019415</td>\n",
       "      <td>Your baby is already 10 months old! Growing up...</td>\n",
       "      <td>00000000-0000-0000-0000-000000000002</td>\n",
       "      <td>normal</td>\n",
       "      <td>d536b15e-ddb3-4012-a83e-fe4913569b4a</td>\n",
       "      <td>2019-07-03 11:31:44.173594</td>\n",
       "      <td>2019-07-03 11:33:42.693003</td>\n",
       "      <td>NaT</td>\n",
       "      <td>AT&amp;T Wireless</td>\n",
       "      <td>10019</td>\n",
       "      <td>New York</td>\n",
       "      <td>-5.000</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>New York</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>WNET - New York, NY</td>\n",
       "      <td>2018-03-25 21:32:21.001262</td>\n",
       "      <td>True</td>\n",
       "      <td>New York</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2018-08-27</td>\n",
       "      <td>2018-08-27</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>957f1d7f-2ec8-400a-bafa-c308648e483e</td>\n",
       "      <td>2019-07-03 15:00:16.256914</td>\n",
       "      <td>Snack time is a great opportunity to get more ...</td>\n",
       "      <td>00000000-0000-0000-0000-000000000002</td>\n",
       "      <td>normal</td>\n",
       "      <td>5a8ccc15-9ef4-4637-967f-cf5513bf8172</td>\n",
       "      <td>2017-06-24 15:30:18.083001</td>\n",
       "      <td>2017-06-24 15:30:18.083001</td>\n",
       "      <td>2019-07-20 15:01:12.022771</td>\n",
       "      <td>Sprint Spectrum, L.P.</td>\n",
       "      <td>12401</td>\n",
       "      <td>Kingston</td>\n",
       "      <td>-5.000</td>\n",
       "      <td>America/New_York</td>\n",
       "      <td>New York</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>deactivated</td>\n",
       "      <td>English</td>\n",
       "      <td>STOP</td>\n",
       "      <td>WNET - New York, NY</td>\n",
       "      <td>2018-03-25 21:32:21.001262</td>\n",
       "      <td>True</td>\n",
       "      <td>New York</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2016-06-10</td>\n",
       "      <td>2016-06-10</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4359035</td>\n",
       "      <td>21e1ee17-38c9-4338-a5a1-77acc0706345</td>\n",
       "      <td>2019-12-31 19:01:21.142854</td>\n",
       "      <td>Talk numbers every day to build your child’s m...</td>\n",
       "      <td>4090bf1f-7d21-40d3-bc70-298f367f61fc</td>\n",
       "      <td>normal</td>\n",
       "      <td>e047eeff-0459-4e2d-83e9-b30841a2b59a</td>\n",
       "      <td>2019-09-06 01:08:32.972570</td>\n",
       "      <td>2019-09-06 01:24:18.736755</td>\n",
       "      <td>NaT</td>\n",
       "      <td>Verizon Wireless</td>\n",
       "      <td>89108</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>Vegas PBS</td>\n",
       "      <td>2019-04-19 15:17:56.225879</td>\n",
       "      <td>True</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2016-07-19</td>\n",
       "      <td>2016-07-19</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4359036</td>\n",
       "      <td>7c897916-ee61-470c-8b0c-6dd1e7c11c15</td>\n",
       "      <td>2019-12-31 19:01:21.143040</td>\n",
       "      <td>You don't need expensive supplies to stock a c...</td>\n",
       "      <td>4090bf1f-7d21-40d3-bc70-298f367f61fc</td>\n",
       "      <td>normal</td>\n",
       "      <td>959c610c-de6e-45bc-b49f-9037d135e139</td>\n",
       "      <td>2019-06-02 23:02:08.873069</td>\n",
       "      <td>2019-06-02 23:46:12.200382</td>\n",
       "      <td>NaT</td>\n",
       "      <td>AT&amp;T Wireless</td>\n",
       "      <td>89074</td>\n",
       "      <td>Henderson</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>Vegas PBS</td>\n",
       "      <td>2019-04-19 15:17:56.225879</td>\n",
       "      <td>True</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2016-03-26</td>\n",
       "      <td>2016-03-26</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4359037</td>\n",
       "      <td>6222d714-b901-423e-8394-26e1a1730af3</td>\n",
       "      <td>2019-12-31 19:01:21.143822</td>\n",
       "      <td>Time-outs can be a good way to deal with misbe...</td>\n",
       "      <td>4090bf1f-7d21-40d3-bc70-298f367f61fc</td>\n",
       "      <td>normal</td>\n",
       "      <td>74701ed6-8cb8-4d03-ac7f-ad7ecec75d39</td>\n",
       "      <td>2019-11-18 18:24:45.555064</td>\n",
       "      <td>2019-11-18 18:30:05.105769</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>89107</td>\n",
       "      <td>Las Vegas</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>Vegas PBS</td>\n",
       "      <td>2019-04-19 15:17:56.225879</td>\n",
       "      <td>True</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>2017-09-20</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4359038</td>\n",
       "      <td>bb001f97-5d5a-49f5-a9f7-7554f3babbed</td>\n",
       "      <td>2019-12-31 19:01:17.225732</td>\n",
       "      <td>C2: Children learn through repetition. This PB...</td>\n",
       "      <td>71718ec5-049d-47b9-80db-f9fbd0503152</td>\n",
       "      <td>normal</td>\n",
       "      <td>1961bc5d-5262-4cc2-bba1-787ef618dae5</td>\n",
       "      <td>2019-12-30 23:00:16.273032</td>\n",
       "      <td>2019-12-30 23:04:44.068067</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>93654</td>\n",
       "      <td>Reedley</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>SMS/Web</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>ValleyPBS - Fresno, CA</td>\n",
       "      <td>2018-08-28 20:57:03.730535</td>\n",
       "      <td>True</td>\n",
       "      <td>California</td>\n",
       "      <td>2.000</td>\n",
       "      <td>2016-12-16</td>\n",
       "      <td>2018-08-22</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4359039</td>\n",
       "      <td>bb001f97-5d5a-49f5-a9f7-7554f3babbed</td>\n",
       "      <td>2019-12-31 19:01:21.179064</td>\n",
       "      <td>Children learn through repetition. This PBS re...</td>\n",
       "      <td>71718ec5-049d-47b9-80db-f9fbd0503152</td>\n",
       "      <td>normal</td>\n",
       "      <td>18490a1d-a197-444b-ba88-fa0dd7ec6392</td>\n",
       "      <td>2017-09-05 17:30:07.078514</td>\n",
       "      <td>2017-09-05 17:30:07.078514</td>\n",
       "      <td>NaT</td>\n",
       "      <td>T-Mobile USA, Inc.</td>\n",
       "      <td>93305</td>\n",
       "      <td>Bakersfield</td>\n",
       "      <td>-8.000</td>\n",
       "      <td>America/Los_Angeles</td>\n",
       "      <td>California</td>\n",
       "      <td>Text SignUp</td>\n",
       "      <td>activated</td>\n",
       "      <td>English</td>\n",
       "      <td>None</td>\n",
       "      <td>ValleyPBS - Fresno, CA</td>\n",
       "      <td>2018-08-28 20:57:03.730535</td>\n",
       "      <td>True</td>\n",
       "      <td>California</td>\n",
       "      <td>1.000</td>\n",
       "      <td>2016-12-16</td>\n",
       "      <td>2016-12-16</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>4359040 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                   message_id outbound_message_created_at  \\\n",
       "0        356f0eca-f6ed-44f2-8ab3-89fb919f0286  2019-07-03 02:59:09.057012   \n",
       "1        39397597-c1a7-4218-9622-864380247ce0  2019-07-03 15:00:14.264828   \n",
       "2        a13fae68-63e5-44fd-8b47-23fb4b9f23bc  2019-07-03 15:00:15.266724   \n",
       "3        87db92b3-7247-4f32-a103-3f647652901b  2019-07-03 15:00:16.019415   \n",
       "4        957f1d7f-2ec8-400a-bafa-c308648e483e  2019-07-03 15:00:16.256914   \n",
       "...                                       ...                         ...   \n",
       "4359035  21e1ee17-38c9-4338-a5a1-77acc0706345  2019-12-31 19:01:21.142854   \n",
       "4359036  7c897916-ee61-470c-8b0c-6dd1e7c11c15  2019-12-31 19:01:21.143040   \n",
       "4359037  6222d714-b901-423e-8394-26e1a1730af3  2019-12-31 19:01:21.143822   \n",
       "4359038  bb001f97-5d5a-49f5-a9f7-7554f3babbed  2019-12-31 19:01:17.225732   \n",
       "4359039  bb001f97-5d5a-49f5-a9f7-7554f3babbed  2019-12-31 19:01:21.179064   \n",
       "\n",
       "                                     outbound_message_body  \\\n",
       "0        Louisiana Public Broadcasting welcomes you to ...   \n",
       "1        Remember making forts and playing in cardboard...   \n",
       "2        Use sunscreen every time your child is in the ...   \n",
       "3        Your baby is already 10 months old! Growing up...   \n",
       "4        Snack time is a great opportunity to get more ...   \n",
       "...                                                    ...   \n",
       "4359035  Talk numbers every day to build your child’s m...   \n",
       "4359036  You don't need expensive supplies to stock a c...   \n",
       "4359037  Time-outs can be a good way to deal with misbe...   \n",
       "4359038  C2: Children learn through repetition. This PB...   \n",
       "4359039  Children learn through repetition. This PBS re...   \n",
       "\n",
       "                  outbound_message_partner_id message_type  \\\n",
       "0        0cf81fa0-fd85-44da-8702-d6e0eeb4fc8d       signup   \n",
       "1        d4d696af-37ff-427e-8035-2cb1bd630495       normal   \n",
       "2        d4d696af-37ff-427e-8035-2cb1bd630495       normal   \n",
       "3        00000000-0000-0000-0000-000000000002       normal   \n",
       "4        00000000-0000-0000-0000-000000000002       normal   \n",
       "...                                       ...          ...   \n",
       "4359035  4090bf1f-7d21-40d3-bc70-298f367f61fc       normal   \n",
       "4359036  4090bf1f-7d21-40d3-bc70-298f367f61fc       normal   \n",
       "4359037  4090bf1f-7d21-40d3-bc70-298f367f61fc       normal   \n",
       "4359038  71718ec5-049d-47b9-80db-f9fbd0503152       normal   \n",
       "4359039  71718ec5-049d-47b9-80db-f9fbd0503152       normal   \n",
       "\n",
       "                                subscriber_id                 created_at  \\\n",
       "0        da37347f-e2e8-4d19-ac99-9905cea6404a 2019-07-03 02:59:08.814264   \n",
       "1        48405053-41fb-4750-b866-d87b6d426162 2019-01-03 22:29:20.341875   \n",
       "2        8941c36d-f05b-4edf-970f-fa0584f19b72 2018-06-24 22:38:51.605720   \n",
       "3        d536b15e-ddb3-4012-a83e-fe4913569b4a 2019-07-03 11:31:44.173594   \n",
       "4        5a8ccc15-9ef4-4637-967f-cf5513bf8172 2017-06-24 15:30:18.083001   \n",
       "...                                       ...                        ...   \n",
       "4359035  e047eeff-0459-4e2d-83e9-b30841a2b59a 2019-09-06 01:08:32.972570   \n",
       "4359036  959c610c-de6e-45bc-b49f-9037d135e139 2019-06-02 23:02:08.873069   \n",
       "4359037  74701ed6-8cb8-4d03-ac7f-ad7ecec75d39 2019-11-18 18:24:45.555064   \n",
       "4359038  1961bc5d-5262-4cc2-bba1-787ef618dae5 2019-12-30 23:00:16.273032   \n",
       "4359039  18490a1d-a197-444b-ba88-fa0dd7ec6392 2017-09-05 17:30:07.078514   \n",
       "\n",
       "                       signedup_at             deactivated_at  \\\n",
       "0       2019-07-03 03:03:34.103855 2019-08-04 16:01:01.484052   \n",
       "1       2019-01-03 22:29:20.341875                        NaT   \n",
       "2       2018-06-24 22:38:51.605720                        NaT   \n",
       "3       2019-07-03 11:33:42.693003                        NaT   \n",
       "4       2017-06-24 15:30:18.083001 2019-07-20 15:01:12.022771   \n",
       "...                            ...                        ...   \n",
       "4359035 2019-09-06 01:24:18.736755                        NaT   \n",
       "4359036 2019-06-02 23:46:12.200382                        NaT   \n",
       "4359037 2019-11-18 18:30:05.105769                        NaT   \n",
       "4359038 2019-12-30 23:04:44.068067                        NaT   \n",
       "4359039 2017-09-05 17:30:07.078514                        NaT   \n",
       "\n",
       "                  carrier_name subscriber_zip_code subscriber_city  \\\n",
       "0             Verizon Wireless               70769    Prairieville   \n",
       "1             Verizon Wireless               14020         Batavia   \n",
       "2                AT&T Wireless               11222        Brooklyn   \n",
       "3                AT&T Wireless               10019        New York   \n",
       "4        Sprint Spectrum, L.P.               12401        Kingston   \n",
       "...                        ...                 ...             ...   \n",
       "4359035       Verizon Wireless               89108       Las Vegas   \n",
       "4359036          AT&T Wireless               89074       Henderson   \n",
       "4359037     T-Mobile USA, Inc.               89107       Las Vegas   \n",
       "4359038     T-Mobile USA, Inc.               93654         Reedley   \n",
       "4359039     T-Mobile USA, Inc.               93305     Bakersfield   \n",
       "\n",
       "         timezone_default_offset        timezone_name subscriber_state  \\\n",
       "0                         -6.000      America/Chicago        Louisiana   \n",
       "1                         -5.000     America/New_York         New York   \n",
       "2                         -5.000     America/New_York         New York   \n",
       "3                         -5.000     America/New_York         New York   \n",
       "4                         -5.000     America/New_York         New York   \n",
       "...                          ...                  ...              ...   \n",
       "4359035                   -8.000  America/Los_Angeles           Nevada   \n",
       "4359036                   -8.000  America/Los_Angeles           Nevada   \n",
       "4359037                   -8.000  America/Los_Angeles           Nevada   \n",
       "4359038                   -8.000  America/Los_Angeles       California   \n",
       "4359039                   -8.000  America/Los_Angeles       California   \n",
       "\n",
       "        subscriber_source subscriber_status subscriber_language  \\\n",
       "0             Text SignUp       deactivated             English   \n",
       "1             Text SignUp         activated             English   \n",
       "2             Text SignUp         activated             English   \n",
       "3             Text SignUp         activated             English   \n",
       "4             Text SignUp       deactivated             English   \n",
       "...                   ...               ...                 ...   \n",
       "4359035       Text SignUp         activated             English   \n",
       "4359036       Text SignUp         activated             English   \n",
       "4359037       Text SignUp         activated             English   \n",
       "4359038           SMS/Web         activated             English   \n",
       "4359039       Text SignUp         activated             English   \n",
       "\n",
       "        subscriber_deactivation_method                   partner_name  \\\n",
       "0                                 STOP  Louisiana Public Broadcasting   \n",
       "1                                 None                Bright by Three   \n",
       "2                                 None                Bright by Three   \n",
       "3                                 None            WNET - New York, NY   \n",
       "4                                 STOP            WNET - New York, NY   \n",
       "...                                ...                            ...   \n",
       "4359035                           None                      Vegas PBS   \n",
       "4359036                           None                      Vegas PBS   \n",
       "4359037                           None                      Vegas PBS   \n",
       "4359038                           None         ValleyPBS - Fresno, CA   \n",
       "4359039                           None         ValleyPBS - Fresno, CA   \n",
       "\n",
       "              partner_created_date  partner_is_active partner_state  \\\n",
       "0       2019-04-17 19:49:15.007478               True     Louisiana   \n",
       "1       2018-11-30 19:20:34.357527               True      Colorado   \n",
       "2       2018-11-30 19:20:34.357527               True      Colorado   \n",
       "3       2018-03-25 21:32:21.001262               True      New York   \n",
       "4       2018-03-25 21:32:21.001262               True      New York   \n",
       "...                            ...                ...           ...   \n",
       "4359035 2019-04-19 15:17:56.225879               True        Nevada   \n",
       "4359036 2019-04-19 15:17:56.225879               True        Nevada   \n",
       "4359037 2019-04-19 15:17:56.225879               True        Nevada   \n",
       "4359038 2018-08-28 20:57:03.730535               True    California   \n",
       "4359039 2018-08-28 20:57:03.730535               True    California   \n",
       "\n",
       "         children_count dob_youngest_child dob_oldest_child  \\\n",
       "0                 1.000         2016-12-21       2016-12-21   \n",
       "1                 1.000         2017-12-27       2017-12-27   \n",
       "2                 1.000         2018-02-02       2018-02-02   \n",
       "3                 1.000         2018-08-27       2018-08-27   \n",
       "4                 1.000         2016-06-10       2016-06-10   \n",
       "...                 ...                ...              ...   \n",
       "4359035           1.000         2016-07-19       2016-07-19   \n",
       "4359036           1.000         2016-03-26       2016-03-26   \n",
       "4359037           1.000         2017-09-20       2017-09-20   \n",
       "4359038           2.000         2016-12-16       2018-08-22   \n",
       "4359039           1.000         2016-12-16       2016-12-16   \n",
       "\n",
       "        scheduled_message_id  \n",
       "0                       None  \n",
       "1                       None  \n",
       "2                       None  \n",
       "3                       None  \n",
       "4                       None  \n",
       "...                      ...  \n",
       "4359035                 None  \n",
       "4359036                 None  \n",
       "4359037                 None  \n",
       "4359038                 None  \n",
       "4359039                 None  \n",
       "\n",
       "[4359040 rows x 27 columns]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "archived_messages_2019_2h"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download the archived messages dataset for 2019 2H\n",
    "\n",
    "archived_messages_2019_2h.to_csv(r'/Users/lisafan/Desktop/BBT/bbt_archived_messages_2019_2h.csv', index=False)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to PostgeSQL\n",
    "\n",
    "try:\n",
    "    conn = psycopg2.connect(user = \"pbiusr1\",\n",
    "                                  password = \"RE_^V%Gj@EL6R!G\",\n",
    "                                  host = \"app.brightbytext.org\",\n",
    "                                  port = \"5432\",\n",
    "                                  database = \"bbtapi\")\n",
    "\n",
    "except (Exception, psycopg2.Error) as error :\n",
    "    print (\"Error while connecting to PostgreSQL\", error)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Query to get archived outbound messages for 2020\n",
    "\n",
    "sql = '''\n",
    "select\n",
    "    om.message_id,\n",
    "    om.created_at as outbound_message_created_at,\n",
    "    om.body as outbound_message_body,\n",
    "    om.partner_id as outbound_message_partner_id,\n",
    "    mt.name as message_type,\n",
    "    s.id as subscriber_id,\n",
    "    s.created_at,\n",
    "    s.signedup_at,\n",
    "    s.deactivated_at,\n",
    "    s.carrier_name,\n",
    "    z.code as subscriber_zip_code,\n",
    "    z.city as subscriber_city,\n",
    "    t.default_offset as timezone_default_offset,\n",
    "    t.iana_name as timezone_name,\n",
    "    s_state.name as subscriber_state,\n",
    "    ss.name as subscriber_source,\n",
    "    sst.name as subscriber_status,\n",
    "    l.name as subscriber_language,\n",
    "    sdr.name as subscriber_deactivation_method,\n",
    "    p.name as partner_name,\n",
    "    p.created_at as partner_created_date,\n",
    "    p.is_active as partner_is_active,\n",
    "    p_state.name as partner_state,\n",
    "    c.children_count,\n",
    "    c.dob_youngest_child,\n",
    "    c.dob_oldest_child,\n",
    "    msm.scheduled_message_id\n",
    "    \n",
    "from \n",
    "(\n",
    "select *\n",
    "    from archive.outbound_messages\n",
    "    where extract(year from created_at) = 2020\n",
    ") om\n",
    "left join public.message_types as mt\n",
    "on om.message_type_id = mt.id \n",
    "left join messages_scheduled_messages msm \n",
    "on om.message_id = msm.message_id \n",
    "left join public.v_subscribers as s\n",
    "on om.subscriber_id = s.id\n",
    "left join \n",
    "(\n",
    "    select subscriber_id, count(*) as children_count, min(date_of_birth) as dob_youngest_child, max(date_of_birth) as dob_oldest_child\n",
    "    from public.children \n",
    "    group by subscriber_id\n",
    ") c \n",
    "on om.subscriber_id = c.subscriber_id\n",
    "left join public.zip_codes as z \n",
    "on z.id = s.zip_id\n",
    "left join public.timezones t\n",
    "on z.time_zone_id = t.id\n",
    "left join public.states as s_state\n",
    "on s_state.id = z.state_id\n",
    "left join public.subscriber_sources as ss \n",
    "on ss.id = s.source_id\n",
    "left join public.subscriber_statuses as sst \n",
    "on sst.id = s.status_id\n",
    "left join public.languages as l\n",
    "on l.id = s.language_id\n",
    "left join public.subscriber_deactivation_reasons as sdr \n",
    "on sdr.id = s.deactivation_reason_id\n",
    "left join public.partners as p\n",
    "on p.id = s.partner_id\n",
    "left join public.partner_types as pt \n",
    "on pt.id = p.type_id\n",
    "left join public.states as p_state\n",
    "on p_state.id = p.state_id\n",
    "'''\n",
    "archived_messages_2020 = pd.read_sql_query(sql, conn)\n",
    "conn = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download the archived messages dataset for 2020\n",
    "\n",
    "archived_messages_2020.to_csv(r'/Users/lisafan/Desktop/BBT/bbt_archived_messages_2020.csv', index=False)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "messages = pd.concat([archived_messages_2017, archived_messages_2018, archived_messages_2019_1h, archived_messages_2019_2h, archived_messages_2020])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download the full messages dataset\n",
    "\n",
    "messages.to_csv(r'/Users/lisafan/Desktop/BBT/bbt_messages_2017_2020.csv', index=False)  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note that this table only includes outbound messages, and does not include inbound messages."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Query to get scheduled message tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connect to PostgeSQL\n",
    "\n",
    "try:\n",
    "    conn = psycopg2.connect(user = \"pbiusr1\",\n",
    "                                  password = \"RE_^V%Gj@EL6R!G\",\n",
    "                                  host = \"app.brightbytext.org\",\n",
    "                                  port = \"5432\",\n",
    "                                  database = \"bbtapi\")\n",
    "\n",
    "except (Exception, psycopg2.Error) as error :\n",
    "    print (\"Error while connecting to PostgreSQL\", error)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "sql = '''\n",
    "select sm.id as scheduled_message_id,\n",
    "smt.name as scheduled_message_tag\n",
    "from scheduled_messages sm \n",
    "left join scheduled_message_to_tags smtt \n",
    "on sm.id = smtt.scheduled_message_id \n",
    "left join scheduled_message_tags smt \n",
    "on smtt.scheduled_message_tag_id = smt.id \n",
    "'''\n",
    "scheduled_message_tags = pd.read_sql_query(sql, conn)\n",
    "conn = None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Download the scheduled messages tags dataset\n",
    "\n",
    "scheduled_message_tags.to_csv(r'/Users/lisafan/Desktop/BBT/scheduled_message_tags.csv', index=False)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}