{ "cells": [ { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [], "source": [ "%run ./utils.ipynb\n", "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "pd.set_option('display.max_colwidth', -1)" ] }, { "cell_type": "code", "execution_count": 2, "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": [ "# run queries\n", "engine = get_rds_engine()\n", "schema = 'bd345f915775993a4d3de1dae65b93b067ad69dde4286a1e1639e5cd2' # unique customer schema id\n", "\n", "# get unique fans from fans table\n", "queryUniqueFans = f\"\"\"select id as fan_id from {schema}.fan\"\"\"\n", "uniqueFansDf = pd.read_sql(queryUniqueFans, engine)\n", "\n", "# get unique fans from general mailing list\n", "queryGeneralList = f\"\"\"\n", "select distinct fan_id, general_mailing_list from\n", "(\n", "select fan_id, 2 as general_mailing_list from {schema}.collection_0_source\n", "union\n", "select fan_id, 2 as general_mailing_list from {schema}.collection_2_source\n", "union\n", "select fan_id, 2 as general_mailing_list from {schema}.collection_4_source\n", ") sub\n", "\"\"\"\n", "fansFromMailingListDf = pd.read_sql(queryGeneralList, engine)\n", "\n", "# has one bought merchandize\n", "queryMerchByers = f\"\"\"\n", "select distinct fan_id, 1 as shop from {schema}.fan_merch_data\n", "\"\"\"\n", "MerchByersDf = pd.read_sql(queryMerchByers, engine)\n", "\n", "# how many different events fan might have attended based on opt-ins\n", "queryOptIns = f\"\"\"\n", "select fan_id, count(distinct collection_id) as opt_in_count FROM {schema}.collection_fan cf\n", "where collection_id in (7,9,11,14,16,18,20,22,24,26) group by fan_id\n", "\"\"\"\n", "eventCountPerOptInDf = pd.read_sql(queryOptIns, engine)\n", "\n", "# get ticket count per fan\n", "queryFanTicketCount = f\"\"\"\n", "select fan_id, sum(event_purchase_quantity) as multi_ticket from\n", "{schema}.fan_event_data group by fan_id\n", "\"\"\"\n", "NrTicketsPerFanDf = pd.read_sql(queryFanTicketCount, engine)" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "unique fans------------\n", "(37688, 1)\n", "fan_id object\n", "dtype: object\n", "fans from list------------\n", "(34041, 2)\n", "fan_id object\n", "general_mailing_list int64\n", "dtype: object\n", "fans merch------------\n", "(10414, 2)\n", "fan_id object\n", "shop int64\n", "dtype: object\n", "fans opt in count------------\n", "(3127, 2)\n", "1 2570\n", "2 526\n", "3 30\n", "4 1\n", "Name: opt_in_count, dtype: int64\n", "fan_id object\n", "opt_in_count int64\n", "dtype: object\n", "fans ticket count------------\n", "(3002, 2)\n", "1 1398\n", "2 1055\n", "4 224\n", "3 184\n", "6 56\n", "5 36\n", "8 21\n", "7 7\n", "9 6\n", "10 5\n", "13 2\n", "11 2\n", "14 2\n", "15 1\n", "16 1\n", "12 1\n", "41 1\n", "Name: multi_ticket, dtype: int64\n", "fan_id object\n", "multi_ticket int64\n", "dtype: object\n", " fan_id multi_ticket\n", "0 ed539f61530ffe733b3d7001cc7be41f36c44dd49ed5ca... 10\n", "1 7e40a41572df492957c57bda21d7d3ee9e6f1bc23cc95b... 2\n" ] } ], "source": [ "# describe dataframes\n", "print(\"unique fans------------\")\n", "print(uniqueFansDf.shape)\n", "print(uniqueFansDf.dtypes)\n", "print(\"fans from list------------\")\n", "print(fansFromMailingListDf.shape)\n", "print(fansFromMailingListDf.dtypes)\n", "print(\"fans merch------------\")\n", "print(MerchByersDf.shape)\n", "print(MerchByersDf.dtypes)\n", "print(\"fans opt in count------------\")\n", "print(eventCountPerOptInDf.shape)\n", "print(eventCountPerOptInDf['opt_in_count'].value_counts())\n", "print(eventCountPerOptInDf.dtypes)\n", "print(\"fans ticket count------------\")\n", "print(NrTicketsPerFanDf.shape)\n", "print(NrTicketsPerFanDf['multi_ticket'].value_counts())\n", "print(NrTicketsPerFanDf.dtypes)\n", "print(NrTicketsPerFanDf.head(2))" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [], "source": [ "# create attributes for fans\n", "\n", "# ticket count attribute\n", "NrTicketsPerFanDf['single_ticket'] =np.where(NrTicketsPerFanDf['multi_ticket']==1,1, np.nan)\n", "NrTicketsPerFanDf['multiple_tickets'] =np.where(NrTicketsPerFanDf['multi_ticket']>1,1, np.nan)\n", "NrTicketsPerFanDf.drop('multi_ticket', axis=1,inplace=True)\n", "\n", "# opt-in count attribute\n", "eventCountPerOptInDf['single_optin'] =np.where(eventCountPerOptInDf['opt_in_count']==1,1, np.nan)\n", "eventCountPerOptInDf['multiple_optin'] =np.where(eventCountPerOptInDf['opt_in_count']>1,1, np.nan)\n", "eventCountPerOptInDf.drop('opt_in_count', axis=1,inplace=True)" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [], "source": [ "# merge dataframes\n", "rawSuperFanDf = pd.merge(uniqueFansDf, fansFromMailingListDf, how='left', on=['fan_id'])\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, MerchByersDf, how='left', on=['fan_id'])\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, eventCountPerOptInDf, how='left', on=['fan_id'])\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, NrTicketsPerFanDf, how='left', on=['fan_id'])\n", "rawSuperFanDf.fillna(0,inplace=True) \n", "# print(rawSuperFanDf.head(5))" ] }, { "cell_type": "code", "execution_count": 6, "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", "
total_scorefan_countproportion
01.029220.077531
12.0255310.677430
23.069270.183799
34.016800.044577
45.06280.016663
\n", "
" ], "text/plain": [ " total_score fan_count proportion\n", "0 1.0 2922 0.077531\n", "1 2.0 25531 0.677430\n", "2 3.0 6927 0.183799\n", "3 4.0 1680 0.044577\n", "4 5.0 628 0.016663" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "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))\n", "groupedSuperFanData = rawSuperFanDf.groupby(['total_score'],as_index=False)['fan_id'].count().rename(columns={'fan_id' : 'fan_count'})\n", "groupedSuperFanData['proportion'] = groupedSuperFanData['fan_count']/groupedSuperFanData['fan_count'].sum()\n", "groupedSuperFanData" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " fan_id total_score\n", "0 fb741faaba1f78168aa13ae4d5b4fe009f5d7980bf1929... 3.0\n", "1 a4501927be107bd09b7f72c6898a6106ae7ec0f0ee4a12... 2.0\n", "2 e21b056dce2da11c5cf250b9ef36fbf1e6a14d06530df5... 3.0\n", "3 d97e9ebc3045a081f3f9b9fc07776de7fcee6ef8a58aa5... 2.0\n", "(37688, 2)\n" ] } ], "source": [ "# write superfans to db\n", "\n", "toDB=rawSuperFanDf[['fan_id','total_score']]\n", "print(toDB.head(4))\n", "print(toDB.shape)\n", "toDB.to_sql(f'superfans',\n", " engine,\n", " schema=schema,\n", " if_exists='replace',\n", " #index_label='fan_id',\n", " index=False\n", " )\n", "\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "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": 70, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " fan_id score score_a\n", "0 0006ce310c469d721bae29e5293ac3f9709d6b3565f992... 2 2\n", "1 0006dd7bb615ce872810cbf0b7a861108a2becc1f96b3b... 2 2\n", "2 0007321c2c276cfacfbed45cd495ddf78562bc8bda4404... 2 2\n", "3 0008d9e898d9fd910c7c528d1e3cac0e3e2e3390308fe3... 2 2\n", "4 0009778ba20e46f077b19d2a17c5d9d6577b79d0aeb6d9... 2 2\n", "(34041, 3)\n", "count 34041.0\n", "mean 2.0\n", "std 0.0\n", "min 2.0\n", "25% 2.0\n", "50% 2.0\n", "75% 2.0\n", "max 2.0\n", "Name: score, dtype: float64\n" ] } ], "source": [ "# IWH suprefan based on new metholody in confluence\n", "# https://fansifter.atlassian.net/wiki/spaces/FM/pages/198017029/Superfans\n", "# testing scoring method A\n", "\n", "engine = get_rds_engine()\n", "schema = 'bd345f915775993a4d3de1dae65b93b067ad69dde4286a1e1639e5cd2' # unique customer schema id\n", "\n", "# non social media LEVEL 2: visit website, open email, sign-up to newsletters\n", "non_social_level2_q = f\"\"\"\n", "select distinct fan_id, general_mailing_list from\n", "(\n", "select fan_id, 2 as general_mailing_list from {schema}.collection_0_source\n", "union\n", "select fan_id, 2 as general_mailing_list from {schema}.collection_2_source\n", "union\n", "select fan_id, 2 as general_mailing_list from {schema}.collection_4_source\n", ") sub\n", "\"\"\"\n", "nonsocialLevel2_df = pd.read_sql(non_social_level2_q, engine)\n", "nonsocialLevel2_df['score_a']=nonsocialLevel2_df['general_mailing_list']\n", "nonsocialLevel2_df.columns = ['fan_id','score','score_a']\n", "print(nonsocialLevel2_df.head())\n", "print(nonsocialLevel2_df.shape)\n", "print(nonsocialLevel2_df['score'].describe())" ] }, { "cell_type": "code", "execution_count": 61, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " fan_id score score_a\n", "0 33353a380143896467524a892e1e64502aba647324ff56... 2 2\n", "1 2f8524b7f3c512dec961d3df9d95a4221c39d7db8350dc... 2 2\n", "2 ac6f22d3b3e2f1ef3a6fdd6b59ad50f4803a1a1505e8d1... 2 2\n", "3 ee28a32dc5597d75998773366882114ea25c99bac141b7... 2 2\n", "4 af527e3e563b2c8d7340562d36f97121dc7519b3265eec... 2 2\n", "(10130, 3)\n", "count 10130.000000\n", "mean 2.146298\n", "std 0.697172\n", "min 2.000000\n", "25% 2.000000\n", "50% 2.000000\n", "75% 2.000000\n", "max 24.000000\n", "Name: score, dtype: float64\n", "count 10130.000000\n", "mean 2.135439\n", "std 0.567506\n", "min 2.000000\n", "25% 2.000000\n", "50% 2.000000\n", "75% 2.000000\n", "max 6.000000\n", "Name: score_a, dtype: float64\n" ] } ], "source": [ "# merh LEVEL 2: buy something\n", "buy_merch_L2_q=f\"\"\"\n", "select fan_id, sum(merch_purchase_quantity) as total_items_per_fan\n", "from {schema}.fan_merch_data where merch_purchase_quantity>0 group by fan_id \n", "\"\"\"\n", "buy_merch_L2_df = pd.read_sql(buy_merch_L2_q, engine)\n", "buy_merch_L2_df['total_items_per_fan_a']=np.where(buy_merch_L2_df['total_items_per_fan']>3,3,buy_merch_L2_df['total_items_per_fan'])\n", "buy_merch_L2_df['total_items_per_fan']=buy_merch_L2_df['total_items_per_fan']*2\n", "buy_merch_L2_df['total_items_per_fan_a']=buy_merch_L2_df['total_items_per_fan_a']*2\n", "buy_merch_L2_df.columns = ['fan_id','score','score_a']\n", "print(buy_merch_L2_df.head())\n", "print(buy_merch_L2_df.shape)\n", "print(buy_merch_L2_df['score'].describe())\n", "print(buy_merch_L2_df['score_a'].describe())" ] }, { "cell_type": "code", "execution_count": 62, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " fan_id score score_a\n", "0 967e39b7fef56b8b610a582deda887f46a62b348ec529d... 6 6\n", "1 c0eab9a7c76594b190c400f8728789ff38c59803b7bbc5... 3 3\n", "2 a6e0b56ade73a08576ec096eba5f4222882276cf2bfd9f... 3 3\n", "3 4b1ca25630dc8a2f4c3991ac51363806b9556068048018... 6 6\n", "4 41b4705755192576585ca3c8446137ffa6790731d0672c... 3 3\n", "(1912, 3)\n", "count 1912.000000\n", "mean 3.582113\n", "std 1.761723\n", "min 3.000000\n", "25% 3.000000\n", "50% 3.000000\n", "75% 3.000000\n", "max 36.000000\n", "Name: score, dtype: float64\n", "count 1912.000000\n", "mean 3.525628\n", "std 1.355792\n", "min 3.000000\n", "25% 3.000000\n", "50% 3.000000\n", "75% 3.000000\n", "max 9.000000\n", "Name: score_a, dtype: float64\n" ] } ], "source": [ "# merh LEVEL 3: buy special, VIP, bundles, limited edition;- each item 3 points\n", "buy_merch_L3_q=f\"\"\"\n", "select fan_id, sum(merch_purchase_quantity) as total_vip_items_per_fan\n", "from {schema}.fan_merch_data where merch_purchase_quantity>0 and \n", "upper(merch_type) in ('VINYL','BUNDLE','UPGRADE') or upper(merch_type) like '%%12%%' or upper(merch_type ) like '%%7%%' group by fan_id\n", "having sum(merch_purchase_quantity)>0\n", "\"\"\"\n", "buy_merch_L3_df = pd.read_sql(buy_merch_L3_q, engine)\n", "buy_merch_L3_df['total_vip_items_per_fan_a']=np.where(buy_merch_L3_df['total_vip_items_per_fan']>3,3,buy_merch_L3_df['total_vip_items_per_fan'])\n", "buy_merch_L3_df['total_vip_items_per_fan']=buy_merch_L3_df['total_vip_items_per_fan']*3\n", "buy_merch_L3_df['total_vip_items_per_fan_a']=buy_merch_L3_df['total_vip_items_per_fan_a']*3\n", "buy_merch_L3_df.columns = ['fan_id','score','score_a']\n", "print(buy_merch_L3_df.head())\n", "print(buy_merch_L3_df.shape)\n", "print(buy_merch_L3_df['score'].describe())\n", "print(buy_merch_L3_df['score_a'].describe())" ] }, { "cell_type": "code", "execution_count": 66, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " fan_id score score_a\n", "0 001bedafb7b2451763ef9438b1a0739706894f711b62b1... 2 2\n", "1 001c6d91ea1f30d96299c072ef55e1287a9f04e3cab78f... 4 4\n", "2 002ad4b1fa270cb56813b74b9cf457024b3b4c80432be5... 2 2\n", "3 005161d351aedaaf0c079284efc498890a0ec3beeb3daa... 2 2\n", "4 0068932d605fdc5ebc8332a51875baa4aca8469870c883... 2 2\n", "(3127, 3)\n", "count 3127.000000\n", "mean 2.376719\n", "std 0.834368\n", "min 2.000000\n", "25% 2.000000\n", "50% 2.000000\n", "75% 2.000000\n", "max 8.000000\n", "Name: score, dtype: float64\n", "count 3127.000000\n", "mean 2.376079\n", "std 0.830815\n", "min 2.000000\n", "25% 2.000000\n", "50% 2.000000\n", "75% 2.000000\n", "max 6.000000\n", "Name: score_a, dtype: float64\n" ] } ], "source": [ "# concert LEVEL 2: buy a ticket\n", "concert_L2_q=f\"\"\"\n", "select fan_id, count(distinct collection_id) as ticket_count FROM {schema}.collection_fan cf\n", "where collection_id in (7,9,11,14,16,18,20,22,24,26) group by fan_id\n", "\"\"\"\n", "concert_L2_df = pd.read_sql(concert_L2_q, engine)\n", "concert_L2_df['ticket_count_a']=np.where(concert_L2_df['ticket_count']>3,3,concert_L2_df['ticket_count'])\n", "concert_L2_df['ticket_count']=concert_L2_df['ticket_count']*2\n", "concert_L2_df['ticket_count_a']=concert_L2_df['ticket_count_a']*2\n", "concert_L2_df.columns = ['fan_id','score','score_a']\n", "print(concert_L2_df.head())\n", "print(concert_L2_df.shape)\n", "print(concert_L2_df['score'].describe())\n", "print(concert_L2_df['score_a'].describe())" ] }, { "cell_type": "code", "execution_count": 72, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "(49210, 3)\n", "fan_id object\n", "score int64\n", "score_a int64\n", "dtype: object\n" ] } ], "source": [ "# concatenate tables\n", "sf_method_a = pd.concat([nonsocialLevel2_df, buy_merch_L2_df], ignore_index=True)\n", "sf_method_a = pd.concat([sf_method_a, buy_merch_L3_df], ignore_index=True)\n", "sf_method_a = pd.concat([sf_method_a, concert_L2_df], ignore_index=True)\n", "print(sf_method_a.shape)\n", "print(sf_method_a.dtypes)" ] }, { "cell_type": "code", "execution_count": 73, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count 37551.000000\n", "mean 2.772363\n", "std 1.725351\n", "min 2.000000\n", "25% 2.000000\n", "50% 2.000000\n", "75% 4.000000\n", "max 62.000000\n", "Name: score, dtype: float64\n", "count 37551.000000\n", "mean 2.766504\n", "std 1.649926\n", "min 2.000000\n", "25% 2.000000\n", "50% 2.000000\n", "75% 4.000000\n", "max 21.000000\n", "Name: score_a, dtype: float64\n" ] } ], "source": [ "# group data method A no cap\n", "sf_grouped__a = sf_method_a.groupby([\"fan_id\"],as_index=False)[\"score\"].sum()\n", "print(sf_grouped__a['score'].describe())\n", "sf_grouped__a.to_csv(r'superfan_method_a.csv', index = False, sep = ';')\n", "\n", "# group data method C with cap\n", "sf_grouped_c = sf_method_a.groupby([\"fan_id\"],as_index=False)[\"score_a\"].sum()\n", "print(sf_grouped_c['score_a'].describe())\n", "sf_grouped_c.to_csv(r'superfan_method_c.csv', index = False, sep = ';')" ] }, { "cell_type": "code", "execution_count": 60, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "count 37551.000000\n", "mean 1.259567\n", "std 0.476142\n", "min 1.000000\n", "25% 1.000000\n", "50% 1.000000\n", "75% 1.000000\n", "max 3.000000\n", "Name: y_n, dtype: float64\n", " y_n fan_id\n", "0 1 28452\n", "1 2 8451\n", "2 3 648\n", "count 1912.0\n", "mean 1.0\n", "std 0.0\n", "min 1.0\n", "25% 1.0\n", "50% 1.0\n", "75% 1.0\n", "max 1.0\n", "Name: y_n, dtype: float64\n" ] } ], "source": [ "# superfans method B\n", "# Level 1 score out of 7 is 0 for all because of no data\n", "# Leve 2 signup + buy merch + buy ticket\n", "L2_nosocial=nonsocialLevel2_df[['fan_id']]\n", "L2_nosocial['y_n']=1\n", "\n", "L2_merch=buy_merch_L2_df[['fan_id']]\n", "L2_merch['y_n']=1\n", "\n", "L2_concert=concert_L2_df[['fan_id']]\n", "L2_concert['y_n']=1\n", "\n", "L2_method_b = pd.concat([L2_nosocial, L2_merch], ignore_index=True)\n", "L2_method_b = pd.concat([L2_method_b, L2_concert], ignore_index=True)\n", "L2_grouped__b = L2_method_b.groupby([\"fan_id\"],as_index=False)[\"y_n\"].sum()\n", "print(L2_grouped__b['y_n'].describe())\n", "l2l=L2_grouped__b.groupby([\"y_n\"],as_index=False).count()\n", "print(l2l)\n", "L3_merch=buy_merch_L3_df[['fan_id']]\n", "L3_merch['y_n']=1\n", "print(L3_merch['y_n'].describe())" ] } ], "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 }