{ "cells": [ { "cell_type": "code", "execution_count": 391, "metadata": {}, "outputs": [], "source": [ "import pandas as pd\n", "import numpy as np\n", "from hashlib import sha256\n", "from itertools import combinations\n", "from copy import deepcopy\n", "%run ./utils.ipynb\n", "get_rds_engine()\n", "engine = get_rds_engine()\n", "\n", "# setting pandas visual settings \n", "pd.set_option('display.max_columns', 20)\n", "pd.set_option('max_colwidth', 1000)\n", "pd.set_option(\"max_rows\", 100)" ] }, { "cell_type": "code", "execution_count": 392, "metadata": {}, "outputs": [], "source": [ "def obfuscate_sha256(string: str):\n", " \"\"\"Returns SHA 256 obfuscated string\"\"\"\n", " return str(sha256(str(string).encode()).hexdigest())" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "schema = 'a441ffb172866cb4928c84a73de403ca15da4a54cc535e704413ab621' # OCEAN \"Aleks\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# big_spenders_source_df = pd.read_csv('OCEAN/big spenders.csv')\n", "# larger_baskets_source_df = pd.read_csv('OCEAN/larger baskets.csv')\n", "# superfans_source_df = pd.read_csv('OCEAN/superfans.csv')\n", "\n", "# source_list = [{'name': 'superfans', 'source_df': superfans_source_df, 'level': 0, 'merge': False, 'merge_group': None},\n", "# {'name': 'big spenders', 'source_df': big_spenders_source_df, 'level': 2, 'merge': True, 'merge_group': 0},\n", "# {'name': 'larger baskets', 'source_df': larger_baskets_source_df, 'level': 1, 'merge': True, 'merge_group': 0}]" ] }, { "cell_type": "code", "execution_count": 393, "metadata": {}, "outputs": [], "source": [ "superfans_df = pd.read_csv('IHW/fb_superfans.csv')\n", "merch_most_valuable_df = pd.read_csv('IHW/fb_merch_cluster_grouped.csv')\n", "most_active_optins_1 = pd.read_csv('IHW/fb_optin_cluster_4.csv')\n", "most_active_optins_2 = pd.read_csv('IHW/fb_optin_cluster_6.csv')\n", "most_active_optins_df = pd.concat([most_active_optins_1, most_active_optins_2], ignore_index=True)\n", "\n", "\n", "\n", "source_list = [{'name': 'superfans', 'source_df': superfans_df, 'level': 2, 'merge': False, 'merge_group': None},\n", " {'name': 'merch most valuable', 'source_df': merch_most_valuable_df, 'level': 0, 'merge': False, 'merge_group': None},\n", " {'name': 'most active optins', 'source_df': most_active_optins_df, 'level': 1, 'merge': False, 'merge_group': None}]" ] }, { "cell_type": "code", "execution_count": 396, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "No unnamed field in superfans\n", "No unnamed field in merch most valuable\n", "No unnamed field in most active optins\n" ] } ], "source": [ "for segment in source_list:\n", " try:\n", " segment['source_df'] = segment['source_df'].drop(['Unnamed: 0'], axis=1)\n", " except:\n", " print(f'No unnamed field in {segment[\"name\"]}')" ] }, { "cell_type": "code", "execution_count": 379, "metadata": {}, "outputs": [], "source": [ "def compare_segments(source_list:list, compare_field = 'fan_id') -> pd.DataFrame:\n", "\n", " file_list = [x for x in range(0, len(source_list))]\n", " combinations_list = [list(comb) for comb in combinations(file_list, 2)]\n", "\n", " comparison_result = []\n", " for comb in combinations_list:\n", " df1 = source_list[comb[0]]['source_df'][[compare_field]]\n", " df2 = source_list[comb[1]]['source_df'][[compare_field]]\n", "\n", " df3 = pd.merge(df1, df2, how='outer', on=compare_field, indicator='check')\n", "\n", " result_dict = {'c1 name': source_list[comb[0]]['name'],\n", " 'c2 name': source_list[comb[1]]['name'],\n", " 'c1 total fans': len(df1),\n", " 'c2 total fans': len(df2),\n", " 'c1 unique fans': len(df3[df3['check'] == 'left_only']),\n", " 'c1 unique fans %': len(df3[df3['check'] == 'left_only']) / len(df1),\n", " 'c2 unique fans': len(df3[df3['check'] == 'right_only']),\n", " 'c2 unique fans %': len(df3[df3['check'] == 'right_only']) / len(df2),\n", " 'both fans': len(df3[df3['check'] == 'both'])}\n", "\n", " comparison_result.append(result_dict)\n", "\n", " comp_result_df = pd.DataFrame(comparison_result)\n", " comp_result_df = comp_result_df[[x for x, y in result_dict.items()]]\n", " return comp_result_df\n", "\n", "def clean_segments(source_list:list, compare_field = 'fan_id') -> pd.DataFrame:\n", " source_list = deepcopy(source_list)\n", " segments_range = len(source_list)\n", " sorted_list = [i for i in range(0, segments_range)]\n", " for segment in source_list:\n", " sorted_list[segment['level']] = segment\n", " \n", " for base_index, base_segment in enumerate(sorted_list):\n", " base_df = base_segment['source_df']\n", " for index, segment in enumerate(sorted_list):\n", " if index > base_index:\n", " print(base_segment['name'], base_segment['source_df'].shape, 'versus')\n", " print(segment['name'], segment['source_df'].shape)\n", " columns = segment['source_df'].columns\n", " comparison_df = base_df[[compare_field]].merge(segment['source_df'], how='outer', on=compare_field, indicator='check')\n", " sorted_list[index]['source_df'] = comparison_df[comparison_df['check'] == 'right_only'][columns]\n", " \n", " return source_list\n", "\n", "def merge_segments(source_list: list, compare_field = 'fan_id'):\n", " source_list = deepcopy(source_list)\n", " \n", " columns = list(source_list[0]['source_df'].columns)\n", " compare_field_index = columns.index(compare_field)\n", " columns.remove(compare_field)\n", " produced_columns = [str(x)+'_x' for x in columns]\n", " produced_columns.insert(compare_field_index, compare_field)\n", " columns.insert(compare_field_index, compare_field)\n", " \n", " base_df = source_list[0]['source_df']\n", " for segment in source_list[1:]:\n", " print(segment['name'])\n", " merged_df = base_df.merge(segment['source_df'], how='outer', on=compare_field, indicator='check')\n", " base_df = merged_df[merged_df['check'] == 'both'].drop(['check'], axis=1)\n", " base_df = base_df[produced_columns]\n", " base_df.columns = columns\n", " base_df.reset_index(drop=True, inplace=True)\n", " return base_df" ] }, { "cell_type": "code", "execution_count": 380, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "big spenders (5692, 9)\n", "larger baskets (1958, 9)\n" ] } ], "source": [ "# only when merging is needed\n", "merge_list = [x for x in source_list if x['merge'] and x['merge_group'] == 0]\n", "for component in merge_list:\n", " print(component['name'], component['source_df'].shape)" ] }, { "cell_type": "code", "execution_count": 381, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "larger baskets\n" ] } ], "source": [ "result = merge_segments(merge_list, compare_field = 'email')" ] }, { "cell_type": "code", "execution_count": 384, "metadata": {}, "outputs": [], "source": [ "result.to_csv('OCEAN/big_spenders_and_larger_baskets.csv', sep=',', index=False)" ] }, { "cell_type": "code", "execution_count": 390, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(1516, 9)\n", "(5124, 9)\n", "(1021, 9)\n" ] } ], "source": [ "for component in cleaned_segments:\n", " print(component['source_df'].shape)\n", " string = f'OCEAN/cleaned_{component[\"name\"]}.csv'\n", " component['source_df'].to_csv(string, sep=',', index=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Comapring and Cleaning segments" ] }, { "cell_type": "code", "execution_count": 398, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "superfans (2308, 9)\n", "merch most valuable (291, 9)\n", "most active optins (525, 9)\n" ] } ], "source": [ "for component in source_list:\n", " print(component['name'], component['source_df'].shape)" ] }, { "cell_type": "code", "execution_count": 399, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "merch most valuable (291, 9) versus\n", "most active optins (525, 9)\n", "merch most valuable (291, 9) versus\n", "superfans (2308, 9)\n", "most active optins (489, 9) versus\n", "superfans (2248, 9)\n" ] } ], "source": [ "compared_segments = compare_segments(source_list, compare_field='email')\n", "cleaned_segments = clean_segments(source_list, compare_field='email')" ] }, { "cell_type": "code", "execution_count": 401, "metadata": {}, "outputs": [], "source": [ "compared_segments.to_csv('IHW/email_compared_segments.csv', index=False)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Check for unique emails" ] }, { "cell_type": "code", "execution_count": 405, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "superfans, (1986, {1986})\n", "merch most valuable, (291, {291})\n", "most active optins, (489, {489})\n" ] } ], "source": [ "for segment in cleaned_segments:\n", " print(f'{segment[\"name\"]}, {len(segment[\"source_df\"]), {len(segment[\"source_df\"].email.unique())}}')" ] }, { "cell_type": "code", "execution_count": 406, "metadata": {}, "outputs": [], "source": [ "for segment in cleaned_segments:\n", " segment['source_df'].to_csv(f'IHW/email_{segment[\"name\"]}.csv', index=False)" ] }, { "cell_type": "code", "execution_count": 194, "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", "
c1 namec2 namec1 total fansc2 total fansc1 unique fansc1 unique fans %c2 unique fansc2 unique fans %both fans
0superfanslarger baskets151619585790.38192610210.521450937
1superfansbig spenders1516569214530.95844356290.98893263
2larger basketsbig spenders1958569214130.72165551460.904076546
\n", "
" ], "text/plain": [ " c1 name c2 name c1 total fans c2 total fans \\\n", "0 superfans larger baskets 1516 1958 \n", "1 superfans big spenders 1516 5692 \n", "2 larger baskets big spenders 1958 5692 \n", "\n", " c1 unique fans c1 unique fans % c2 unique fans c2 unique fans % \\\n", "0 579 0.381926 1021 0.521450 \n", "1 1453 0.958443 5629 0.988932 \n", "2 1413 0.721655 5146 0.904076 \n", "\n", " both fans \n", "0 937 \n", "1 63 \n", "2 546 " ] }, "execution_count": 194, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result_df.head()" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### SuperFans" ] }, { "cell_type": "code", "execution_count": 62, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(1516, 1)\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
fan_id
0000ea8e44e3e065fa0345440ac63eaa4b842882a20b93df3d9f7092ade500fb4
100218edd948628f31c740aca0558d97c3268a782acce94931378c5a79437f51e
\n", "
" ], "text/plain": [ " fan_id\n", "0 000ea8e44e3e065fa0345440ac63eaa4b842882a20b93df3d9f7092ade500fb4\n", "1 00218edd948628f31c740aca0558d97c3268a782acce94931378c5a79437f51e" ] }, "execution_count": 62, "metadata": {}, "output_type": "execute_result" } ], "source": [ "superfans_source_df['fan_id'] = superfans_source_df['email'].apply(obfuscate_sha256)\n", "superfans_df = superfans_source_df[['fan_id']]\n", "print(superfans_df.shape)\n", "superfans_df.head(2)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Larger Baskets" ] }, { "cell_type": "code", "execution_count": 138, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(1958, 1)\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
fan_id
0000ea8e44e3e065fa0345440ac63eaa4b842882a20b93df3d9f7092ade500fb4
100318d7ac7b0b9d69c6184e42a68c8bceec36cb7bc45a7bf541b9ec93a94bb65
\n", "
" ], "text/plain": [ " fan_id\n", "0 000ea8e44e3e065fa0345440ac63eaa4b842882a20b93df3d9f7092ade500fb4\n", "1 00318d7ac7b0b9d69c6184e42a68c8bceec36cb7bc45a7bf541b9ec93a94bb65" ] }, "execution_count": 138, "metadata": {}, "output_type": "execute_result" } ], "source": [ "larger_baskets_source_df['fan_id'] = larger_baskets_source_df['email'].apply(obfuscate_sha256)\n", "larger_baskets_df = larger_baskets_source_df[['fan_id']]\n", "print(larger_baskets_df.shape)\n", "larger_baskets_df.head(2)" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Big Spenders" ] }, { "cell_type": "code", "execution_count": 153, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(5657, 1)\n" ] }, { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
fan_id
046354f1c17b20dd733b75b90251bc05fcff765686985208a6433264208e38d2e
10012719ea08d19ddb3790c2ad7aacbf075895b504c0c35becd1f4938943fdaec
\n", "
" ], "text/plain": [ " fan_id\n", "0 46354f1c17b20dd733b75b90251bc05fcff765686985208a6433264208e38d2e\n", "1 0012719ea08d19ddb3790c2ad7aacbf075895b504c0c35becd1f4938943fdaec" ] }, "execution_count": 153, "metadata": {}, "output_type": "execute_result" } ], "source": [ "big_spenders_source_df['fan_id'] = big_spenders_source_df['email'].apply(obfuscate_sha256)\n", "big_spenders_source_df.drop_duplicates(subset=['email'], inplace=True)\n", "big_spenders_df = big_spenders_source_df[['fan_id']]\n", "print(big_spenders_df.shape)\n", "big_spenders_df.head(2)" ] }, { "cell_type": "code", "execution_count": 154, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(5657, 1)" ] }, "execution_count": 154, "metadata": {}, "output_type": "execute_result" } ], "source": [ "big_spenders_df.shape" ] }, { "cell_type": "code", "execution_count": 155, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(5657, 1)\n" ] } ], "source": [ "df = big_spenders_df\n", "source_df = big_spenders_source_df\n", "\n", "print(df.shape)\n", "\n", "df[['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": 156, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(5657, 6)\n" ] }, { "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", "
EmailPhoneFirst NameLast NameCountryZip
0amanda.g89@hotmail.comNoneAmandaNaNAUNaN
1ebonyosborn31@hotmail.comNoneEbonyNaNAUNaN
2brigittafajta@hotmail.com61423492420BrigittaFajtaAUNaN
3skosullivan1@gmail.comNoneShannonNaNAUNaN
4newman.shelby@hotmail.comNoneShelbyNewmanUSNaN
\n", "
" ], "text/plain": [ " Email Phone First Name Last Name Country Zip\n", "0 amanda.g89@hotmail.com None Amanda NaN AU NaN\n", "1 ebonyosborn31@hotmail.com None Ebony NaN AU NaN\n", "2 brigittafajta@hotmail.com 61423492420 Brigitta Fajta AU NaN\n", "3 skosullivan1@gmail.com None Shannon NaN AU NaN\n", "4 newman.shelby@hotmail.com None Shelby Newman US NaN" ] }, "execution_count": 156, "metadata": {}, "output_type": "execute_result" } ], "source": [ "query = f'SELECT a.fan_id, b.fan_phone_normalized from {schema}.fblist_temporary a LEFT JOIN {schema}.fan_phone b ON a.fan_id = b.fan_id'\n", "phone_df = pd.read_sql(con=engine,\n", " sql=query)\n", "result_df = source_df.merge(phone_df, how='left', on='fan_id')\n", "\n", "conditions = [(result_df['fan_phone_normalized'].str.len() < 11) & (result_df['country'] == 'AU'),\n", " (result_df['fan_phone_normalized'].str.len() < 11) & (result_df['country'] == 'NZ'),\n", " result_df['fan_phone_normalized'].str.len() == 11,\n", " result_df['fan_phone_normalized'] is not None]\n", "result = ['61'+result_df['fan_phone_normalized'],\n", " '64'+result_df['fan_phone_normalized'], \n", " '+'+result_df['fan_phone_normalized'],\n", " result_df['fan_phone_normalized']]\n", "\n", "result_df['phone'] = np.select(conditions, result)\n", "\n", "result_df = result_df[['email', 'phone', 'fn', 'ln','country']]\n", "result_df.columns = ['Email', 'Phone', 'First Name', 'Last Name','Country']\n", "result_df['Zip'] = np.nan\n", "\n", "print(result_df.shape)\n", "result_df.head(5)" ] }, { "cell_type": "code", "execution_count": 157, "metadata": {}, "outputs": [], "source": [ "result_df.to_csv('OCEAN/google_big_spenders.csv', sep=',')" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "#### Merging clusters" ] }, { "cell_type": "code", "execution_count": 158, "metadata": {}, "outputs": [], "source": [ "merge_list = []\n", "merge_list.append(pd.read_csv('OCEAN/google_big_spenders.csv'))\n", "merge_list.append(pd.read_csv('OCEAN/google_larger_baskets.csv'))\n", "merge_list.append(pd.read_csv('OCEAN/google_superfans.csv'))" ] }, { "cell_type": "code", "execution_count": 163, "metadata": {}, "outputs": [], "source": [ "result_df = pd.concat(merge_list, ignore_index=True)" ] }, { "cell_type": "code", "execution_count": 179, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(7627, 6)" ] }, "execution_count": 179, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result_df.shape" ] }, { "cell_type": "code", "execution_count": 178, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "7627" ] }, "execution_count": 178, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result_df.Email.nunique()" ] }, { "cell_type": "code", "execution_count": 180, "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", "
EmailPhoneFirst NameLast NameCountryZip
0amanda.g89@hotmail.comNaNAmandaNaNAUNaN
1ebonyosborn31@hotmail.comNaNEbonyNaNAUNaN
2brigittafajta@hotmail.com6.142349e+10BrigittaFajtaAUNaN
3skosullivan1@gmail.comNaNShannonNaNAUNaN
4newman.shelby@hotmail.comNaNShelbyNewmanUSNaN
\n", "
" ], "text/plain": [ " Email Phone First Name Last Name Country Zip\n", "0 amanda.g89@hotmail.com NaN Amanda NaN AU NaN\n", "1 ebonyosborn31@hotmail.com NaN Ebony NaN AU NaN\n", "2 brigittafajta@hotmail.com 6.142349e+10 Brigitta Fajta AU NaN\n", "3 skosullivan1@gmail.com NaN Shannon NaN AU NaN\n", "4 newman.shelby@hotmail.com NaN Shelby Newman US NaN" ] }, "execution_count": 180, "metadata": {}, "output_type": "execute_result" } ], "source": [ "result_df.head()" ] }, { "cell_type": "code", "execution_count": 172, "metadata": {}, "outputs": [], "source": [ "result_df = result_df.drop(['Unnamed: 0'], axis=1)" ] }, { "cell_type": "code", "execution_count": 177, "metadata": {}, "outputs": [], "source": [ "result_df.drop_duplicates(inplace=True, subset=['Email'])" ] }, { "cell_type": "code", "execution_count": 181, "metadata": {}, "outputs": [], "source": [ "result_df.to_csv('OCEAN/google_merged.csv', sep=',')" ] }, { "cell_type": "code", "execution_count": 183, "metadata": {}, "outputs": [], "source": [ "gmail_result_df = result_df[result_df['Email'].str.contains('gmail|googlemail', regex=True)]" ] }, { "cell_type": "code", "execution_count": 185, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "(3569, 6)" ] }, "execution_count": 185, "metadata": {}, "output_type": "execute_result" } ], "source": [ "gmail_result_df.shape" ] } ], "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.10" } }, "nbformat": 4, "nbformat_minor": 4 }