{ "cells": [ { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "%run ./utils.ipynb\n", "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "pd.set_option('display.max_colwidth', -1)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stderr", "output_type": "stream", "text": [ "/home/ec2-user/anaconda3/envs/python3/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use \"pip install psycopg2-binary\" instead. For details see: .\n", " \"\"\")\n" ] } ], "source": [ "engine = get_rds_engine()\n", "schema = 'a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621' # unique customer schema id\n", "\n", "# how many different events fan might have attended based on opt-ins from AUS\n", "query1 = f\"\"\"\n", "SELECT fan_id, count(distinct collection_id) FROM {schema}.fan_purchase fp \n", "where collection_id in (8,10,12,14,16,18,20,30,32,34,36,38) group by fan_id\n", "\"\"\"\n", "eventCountPerAusFans = pd.read_sql(query1, engine)\n", "\n", "# how many different events fan might have attended based on opt-ins from NZ\n", "query2 = f\"\"\"\n", "SELECT fan_id, count(distinct collection_id) FROM {schema}.fan_purchase fp \n", "where collection_id in (22,24,26,28) group by fan_id\n", "\"\"\"\n", "eventCountPerNzFans = pd.read_sql(query2, engine)\n", "\n", "# how many different events fan might have attended based on opt-ins from NZ\n", "query2 = f\"\"\"\n", "SELECT fan_id, count(distinct collection_id) FROM {schema}.fan_purchase fp \n", "where collection_id in (22,24,26,28) group by fan_id\n", "\"\"\"\n", "eventCountPerNzFans = pd.read_sql(query2, engine)\n", "\n", "# how many different merch shops fan has used\n", "query3 = f\"\"\"\n", "select fan_id, count(distinct shop) as count from\n", "(\n", "select distinct fan_id, 'A' as shop from {schema}.fan_purchase\n", " where collection_id in(2,4)\n", " union\n", "select distinct fan_id, 'B' as shop from {schema}.fan_purchase\n", " where collection_id in(6)\n", ") sub group by fan_id\n", "\"\"\"\n", "shopCountPerFans = pd.read_sql(query3, engine)\n", "\n", "# get unique fans from general mailing list\n", "query4 = f\"\"\"\n", "select distinct fan_id, 2 as general_mailing_list from {schema}.collection_0_source\n", "\"\"\"\n", "fansFromMailingList = pd.read_sql(query4, engine)\n", "\n", "# get unique fans from fans table\n", "query5 = f\"\"\"select id as fan_id from {schema}.fan\"\"\"\n", "uniqueFans = pd.read_sql(query5, engine)\n", "\n", "# get fans that have multiple tickets per event\n", "query6 = f\"\"\"\n", "select distinct fan_id, 2 as multi_ticket from\n", "(\n", "select distinct fan_id from (\n", "select distinct fan_id, collection_id,item_quantity from a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621.fan_purchase where event_date is not NULL\n", "and item_quantity is not null) a1 where item_quantity::INT >1\n", "union\n", "select distinct fan_id from (\n", "select fan_id, collection_id, count(fan_id) as cnt from a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621.fan_purchase where event_date is not NULL\n", "and item_quantity is NULL group by fan_id, collection_id) a2 where cnt>1\n", ") sub\n", "\"\"\"\n", "multiTicketsPerFan = pd.read_sql(query6, engine)\n", "\n", "fan_emails = f\"\"\"\n", "SELECT \n", "fan_id, root_email\n", "FROM {schema}.fan_table\n", ";\n", "\"\"\"\n", "fan_emails = pd.read_sql(fan_emails, engine)" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "#print(uniqueFans.loc[uniqueFans['fan_id']=='6108f76c322461e811902d41c7590822beb7f6d3720588e0283b9bda47ef7816'])\n", "#print(eventCountPerAusFans.loc[eventCountPerAusFans['fan_id']=='6108f76c322461e811902d41c7590822beb7f6d3720588e0283b9bda47ef7816'])\n", "#print(eventCountPerNzFans.loc[eventCountPerNzFans['fan_id']=='6108f76c322461e811902d41c7590822beb7f6d3720588e0283b9bda47ef7816'])\n", "#print(shopCountPerFans.loc[shopCountPerFans['fan_id']=='6108f76c322461e811902d41c7590822beb7f6d3720588e0283b9bda47ef7816'])\n", "#print(fansFromMailingList.loc[fansFromMailingList['fan_id']=='6108f76c322461e811902d41c7590822beb7f6d3720588e0283b9bda47ef7816'])\n", "#print(multiTicketsPerFan.loc[multiTicketsPerFan['fan_id']=='6108f76c322461e811902d41c7590822beb7f6d3720588e0283b9bda47ef7816'])" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "# create attributes for fans\n", "\n", "# separate attribute for either 1 opt-in or more regarding AUS data\n", "eventCountPerAusFans['single_aus_optin'] =np.where(eventCountPerAusFans['count']==1,1, np.nan)\n", "eventCountPerAusFans['multiple_aus_optin'] =np.where(eventCountPerAusFans['count']>1,2, np.nan)\n", "eventCountPerAusFans.drop('count', axis=1,inplace=True)\n", "# separate attribute for either 1 opt-in or more regarding NZ data\n", "eventCountPerNzFans['single_nz_optin'] =np.where(eventCountPerNzFans['count']==1,1, np.nan)\n", "eventCountPerNzFans['multiple_nz_optin'] =np.where(eventCountPerNzFans['count']>1,2, np.nan)\n", "eventCountPerNzFans.drop('count', axis=1,inplace=True)\n", "# separate attribute for one or more shops\n", "shopCountPerFans['single_shop'] =np.where(shopCountPerFans['count']==1,1, np.nan)\n", "shopCountPerFans['multiple_shop'] =np.where(shopCountPerFans['count']>1,2, np.nan)\n", "shopCountPerFans.drop('count', axis=1,inplace=True)" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " fan_id\n", "0 a8da36aeb9fd3ed6e66f6c875070e1c108c1312f3d7dcf632ab152757cec7841\n", "1 cb6868f30950054acd235a39bdc356da94a360af6627c23e89e55fd8312bcc65\n", " fan_id \\\n", "0 000ea8e44e3e065fa0345440ac63eaa4b842882a20b93df3d9f7092ade500fb4 \n", "1 001a305fc4b6010a1da1f5099e6a5829abe8beee07cc7d221e33ff9ed0b3afa6 \n", "\n", " single_aus_optin multiple_aus_optin \n", "0 1.0 NaN \n", "1 1.0 NaN \n", " fan_id \\\n", "0 0050bf8e736252eafe761e803a500375ee7514d9144f647ff44ef5722afc422e \n", "1 005a932ab8f8cf7b105ad102c498531166f49f4a3c9a646a99b3d24c710238f9 \n", "\n", " single_nz_optin multiple_nz_optin \n", "0 1.0 NaN \n", "1 1.0 NaN \n", " fan_id \\\n", "0 000b06a1be22ef0a8ff82f504ef3f81ff352222423482145200efb4b6aa26e1a \n", "1 000dc180b87292bd3a2889f7bc761b75e12d1caef26b3f85a768fbd2060bf0a9 \n", "\n", " single_shop multiple_shop \n", "0 NaN 2.0 \n", "1 1.0 NaN \n", " fan_id \\\n", "0 5f237c61de843fa5e5db5f5c21c8c9b7e3884645a1fe2324267d2df8d81dfedc \n", "1 828b47c7348321082130c8c5ac163e57bd5b37582f78c4a111b4e23abcda06b1 \n", "\n", " general_mailing_list \n", "0 2 \n", "1 2 \n", " fan_id \\\n", "0 000ea8e44e3e065fa0345440ac63eaa4b842882a20b93df3d9f7092ade500fb4 \n", "1 00218edd948628f31c740aca0558d97c3268a782acce94931378c5a79437f51e \n", "\n", " multi_ticket \n", "0 2 \n", "1 2 \n" ] } ], "source": [ "print(uniqueFans.head(2))\n", "print(eventCountPerAusFans.head(2))\n", "print(eventCountPerNzFans.head(2))\n", "print(shopCountPerFans.head(2))\n", "print(fansFromMailingList.head(2))\n", "print(multiTicketsPerFan.head(2))" ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " fan_id \\\n", "0 a8da36aeb9fd3ed6e66f6c875070e1c108c1312f3d7dcf632ab152757cec7841 \n", "1 cb6868f30950054acd235a39bdc356da94a360af6627c23e89e55fd8312bcc65 \n", "2 62b1c74b2f42e073880cf60a6cde6b3c76e0123d80d5134535f3839c60cafeb5 \n", "3 6b6ae7f1c84ca3d4d9bb592ed61d33933349d21f1a37d6bde9ab63097ba7b430 \n", "4 39f1c8937455a1331af9d79a7cb61d3f57ddf2f4cb85e262b5a6fda8971fea73 \n", "\n", " single_aus_optin multiple_aus_optin single_nz_optin multiple_nz_optin \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "2 NaN NaN NaN NaN \n", "3 NaN NaN NaN NaN \n", "4 NaN NaN NaN NaN \n", "\n", " single_shop multiple_shop general_mailing_list multi_ticket \n", "0 1.0 NaN 2.0 NaN \n", "1 1.0 NaN 2.0 NaN \n", "2 1.0 NaN 2.0 NaN \n", "3 1.0 NaN 2.0 NaN \n", "4 1.0 NaN 2.0 NaN \n" ] } ], "source": [ "# merge dataframes\n", "rawSuperFanDf = pd.merge(uniqueFans, eventCountPerAusFans, how='left', on=['fan_id'])\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, eventCountPerNzFans, how='left', on=['fan_id'])\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, shopCountPerFans, how='left', on=['fan_id'])\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, fansFromMailingList, how='left', on=['fan_id'])\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, multiTicketsPerFan, how='left', on=['fan_id'])\n", "print(rawSuperFanDf.head(5))" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " fan_id \\\n", "0 a8da36aeb9fd3ed6e66f6c875070e1c108c1312f3d7dcf632ab152757cec7841 \n", "\n", " single_aus_optin multiple_aus_optin single_nz_optin multiple_nz_optin \\\n", "0 NaN NaN NaN NaN \n", "\n", " single_shop multiple_shop general_mailing_list multi_ticket total_score \n", "0 1.0 NaN 2.0 NaN 3.0 \n" ] } ], "source": [ "rawSuperFanDf['total_score'] = rawSuperFanDf.sum(axis=1)\n", "rawSuperFanDf['total_score'] =np.where(rawSuperFanDf['total_score']==0,1, rawSuperFanDf['total_score'])\n", "print(rawSuperFanDf.head(1))" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
fan_idsingle_aus_optinmultiple_aus_optinsingle_nz_optinmultiple_nz_optinsingle_shopmultiple_shopgeneral_mailing_listmulti_ticket
total_score
1.0143105696085406618000
2.01004239403132704772140
3.01412959443811745541307
4.082597122714476
5.021200100101121
6.0101000101
\n", "
" ], "text/plain": [ " fan_id single_aus_optin multiple_aus_optin single_nz_optin \\\n", "total_score \n", "1.0 14310 5696 0 854 \n", "2.0 1004 239 40 31 \n", "3.0 1412 959 4 438 \n", "4.0 82 59 7 12 \n", "5.0 21 20 0 1 \n", "6.0 1 0 1 0 \n", "\n", " multiple_nz_optin single_shop multiple_shop \\\n", "total_score \n", "1.0 0 6618 0 \n", "2.0 3 270 477 \n", "3.0 1 17 45 \n", "4.0 2 71 4 \n", "5.0 0 0 10 \n", "6.0 0 0 1 \n", "\n", " general_mailing_list multi_ticket \n", "total_score \n", "1.0 0 0 \n", "2.0 214 0 \n", "3.0 54 1307 \n", "4.0 4 76 \n", "5.0 11 21 \n", "6.0 0 1 " ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "groupedSuperFanData = rawSuperFanDf.groupby(['total_score']) .count()\n", "groupedSuperFanData.head(10)" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " fan_id \\\n", "0 a8da36aeb9fd3ed6e66f6c875070e1c108c1312f3d7dcf632ab152757cec7841 \n", "1 cb6868f30950054acd235a39bdc356da94a360af6627c23e89e55fd8312bcc65 \n", "\n", " single_aus_optin multiple_aus_optin single_nz_optin multiple_nz_optin \\\n", "0 NaN NaN NaN NaN \n", "1 NaN NaN NaN NaN \n", "\n", " single_shop multiple_shop general_mailing_list multi_ticket \\\n", "0 1.0 NaN 2.0 NaN \n", "1 1.0 NaN 2.0 NaN \n", "\n", " total_score root_email \n", "0 3.0 zac.zac.wilson293@gmail.com \n", "1 3.0 ponkolive@y7mail.com \n" ] } ], "source": [ "# Join emails\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, fan_emails, how='left', on=['fan_id'])\n", "\n", "#rawSuperFanDf.loc[rawSuperFanDf['total_score']==4]\n", "\n", "# Select some good segments (4,5,6)\n", "superfans = rawSuperFanDf.loc[rawSuperFanDf['total_score'].isin([3,4,5,6])]\n", "\n", "# Only create list with emails\n", "#superfans[['root_email']].to_csv('superfan_emails.csv', sep=',')\n", "print(superfans.head(2))" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [], "source": [ "# Enter our fan_ids to temporary table\n", "superfans[['fan_id']].to_sql(f'fblist_temporary',\n", " engine,\n", " schema=schema,\n", " if_exists='replace',\n", " # index_label='fan_id', # TODO! duplicates need to be handled properly\n", " # index=False\n", " )\n", "join_part = f\"JOIN {schema}.fblist_temporary j on ft.fan_id = j.fan_id\"" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Index(['fan_id', 'segment'], dtype='object')\n", "(1958, 2)\n", "(1958, 6)\n", " Email \\\n", "0 000ea8e44e3e065fa0345440ac63eaa4b842882a20b93df3d9f7092ade500fb4 \n", "1 00318d7ac7b0b9d69c6184e42a68c8bceec36cb7bc45a7bf541b9ec93a94bb65 \n", "2 0079037851b85eb119da0f032f314177a29e63c6d87a448a5f4d9ad766fdb159 \n", "3 008f11312cd7e4493a2ea5df7ad27dc4c88192cc6e1cec99c40d32eee0f7b51a \n", "4 00f688beff793023843a95febc7dd9ab28a607a7223d817a2f82daf278feaf1b \n", "\n", " Phone \\\n", "0 0d85750db4fd3c617e7cbf38fc09b9cd9b976046ed27ec24338c2f4262ee243b \n", "1 11581c0df8b6dc6095efee8829e2fbf5e0eeea15ee1a5a92290f1f266b112e17 \n", "2 4e362c7a27b828542dd0669741398eb3cfcb44b4c662d01402340da78125ec6a \n", "3 9b2d5b4678781e53038e91ea5324530a03f27dc1d0e5f6c9bc9d493a23be9de0 \n", "4 9b2d5b4678781e53038e91ea5324530a03f27dc1d0e5f6c9bc9d493a23be9de0 \n", "\n", " First Name \\\n", "0 e1608f75c5d7813f3d4031cb30bfb786507d98137538ff8e128a6ff74e84e643 \n", "1 0ab0265842a1c7d13851133349d241e2a8254a56bef1d4750eab7bc620158a65 \n", "2 4135aa9dc1b842a653dea846903ddb95bfb8c5a10c504a7fa16e10bc31d1fdf0 \n", "3 f5b1f644f7c128b6522f2f49b300557655c5f2db68ef110729c9de88b7c5c0a9 \n", "4 779e53d248125a08f1a38c678cfa3a0e135678618eb7bb68f0f3f8b0a748b9b8 \n", "\n", " Last Name Country \\\n", "0 47389ad2158200f192ea5f179aebb2fbac11ecc7152262212e20149bd1be4dc9 AU \n", "1 ddf633d5ee96c532be2a0bba5265548b1479351f1df81270000d36868667c59b AU \n", "2 1f23c707b474c45bdcbd9ab3459d308063ccb8d7b77e67d85f691396ae230efc AU \n", "3 4d5cb3308a87dfcdf6ea1a0d5696db93cb4a7e1f3afec75de2971d80ac760ee6 NaN \n", "4 24d7f03d8dc3c3666969e6fa5bb1fac4736d3f1353c28307ed51b320f9dc42d3 NaN \n", "\n", " Zip \n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 NaN \n", "4 NaN \n", "(1958, 6)\n" ] } ], "source": [ "%run ./prepare_campaign_files.ipynb\n", "df = get_google_files(schema, join_part)" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " Email \\\n", "0 000ea8e44e3e065fa0345440ac63eaa4b842882a20b93df3d9f7092ade500fb4 \n", "1 00318d7ac7b0b9d69c6184e42a68c8bceec36cb7bc45a7bf541b9ec93a94bb65 \n", "2 0079037851b85eb119da0f032f314177a29e63c6d87a448a5f4d9ad766fdb159 \n", "3 008f11312cd7e4493a2ea5df7ad27dc4c88192cc6e1cec99c40d32eee0f7b51a \n", "4 00f688beff793023843a95febc7dd9ab28a607a7223d817a2f82daf278feaf1b \n", "\n", " Phone \\\n", "0 0d85750db4fd3c617e7cbf38fc09b9cd9b976046ed27ec24338c2f4262ee243b \n", "1 11581c0df8b6dc6095efee8829e2fbf5e0eeea15ee1a5a92290f1f266b112e17 \n", "2 4e362c7a27b828542dd0669741398eb3cfcb44b4c662d01402340da78125ec6a \n", "3 9b2d5b4678781e53038e91ea5324530a03f27dc1d0e5f6c9bc9d493a23be9de0 \n", "4 9b2d5b4678781e53038e91ea5324530a03f27dc1d0e5f6c9bc9d493a23be9de0 \n", "\n", " First Name \\\n", "0 e1608f75c5d7813f3d4031cb30bfb786507d98137538ff8e128a6ff74e84e643 \n", "1 0ab0265842a1c7d13851133349d241e2a8254a56bef1d4750eab7bc620158a65 \n", "2 4135aa9dc1b842a653dea846903ddb95bfb8c5a10c504a7fa16e10bc31d1fdf0 \n", "3 f5b1f644f7c128b6522f2f49b300557655c5f2db68ef110729c9de88b7c5c0a9 \n", "4 779e53d248125a08f1a38c678cfa3a0e135678618eb7bb68f0f3f8b0a748b9b8 \n", "\n", " Last Name Country \\\n", "0 47389ad2158200f192ea5f179aebb2fbac11ecc7152262212e20149bd1be4dc9 AU \n", "1 ddf633d5ee96c532be2a0bba5265548b1479351f1df81270000d36868667c59b AU \n", "2 1f23c707b474c45bdcbd9ab3459d308063ccb8d7b77e67d85f691396ae230efc AU \n", "3 4d5cb3308a87dfcdf6ea1a0d5696db93cb4a7e1f3afec75de2971d80ac760ee6 NaN \n", "4 24d7f03d8dc3c3666969e6fa5bb1fac4736d3f1353c28307ed51b320f9dc42d3 NaN \n", "\n", " Zip \n", "0 NaN \n", "1 NaN \n", "2 NaN \n", "3 NaN \n", "4 NaN \n" ] } ], "source": [ "print(df.head(5))\n", "df.to_csv('superfan_google.csv', sep=',')" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [] } ], "metadata": { "kernelspec": { "display_name": "conda_python3", "language": "python", "name": "conda_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.6.5" } }, "nbformat": 4, "nbformat_minor": 4 }