{ "cells": [ { "cell_type": "code", "execution_count": 33, "metadata": {}, "outputs": [ { "data": { "text/plain": [ "
" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%run ./utils.ipynb\n", "%run ./viz_utils.ipynb\n", "import numpy as np\n", "import pandas as pd" ] }, { "cell_type": "code", "execution_count": 34, "metadata": {}, "outputs": [], "source": [ "engine = get_rds_engine()\n", "schema = 'ace3c2bb4abd9fcaf9807975e6ab940131386dc9ecddb2b7e31288ff8'\n", "customer = \"Pixies\"" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_age_gender(schema,customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_fan_maps(schema, customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# ----------------------------------------Superfans -------------------------- #\n", "# get unique fans from fans table\n", "query1 = f\"\"\"select id as fan_id, 1 as mailing_list from {schema}.fan\"\"\"\n", "uniqueFans = pd.read_sql(query1, engine)\n", "# get item count\n", "query2 = f\"\"\"select fan_id, sum(CAST (merch_purchase_quantity AS INTEGER)) as count,\n", " sum(CAST (merch_item_price AS DOUBLE precision)) as value,\n", " max(merch_purchase_date::timestamp) as last_date from\n", " {schema}.fan_merch_data_temp fmd\n", " where merch_purchase_date is not null and CAST(merch_purchase_monetary AS DOUBLE precision)>0 group by fan_id\"\"\"\n", "itemCountPerFan = pd.read_sql(query2, engine)\n", "\n", "query3 = f\"\"\"\n", "SELECT \n", "fan_id, root_email\n", "FROM {schema}.fan_table\n", ";\n", "\"\"\"\n", "fan_emails = pd.read_sql(query3, engine)\n", "\n", "# query optin count\n", "query4 = f\"\"\"\n", "SELECT \n", "fan_id, sum(number_of_opt_ins) as optin_count\n", "FROM {schema}.fan_optin group by fan_id\n", ";\n", "\"\"\"\n", "fan_optins = pd.read_sql(query4, engine)\n", "# print (itemCountPerFan.describe())\n", "# print(itemCountPerFan[\"value\"].quantile(0.90))\n", "\n", "itemCountPerFan['high_value_purchases'] =np.where(itemCountPerFan['value']>77,1, np.nan)\n", "itemCountPerFan['single_item'] =np.where(itemCountPerFan['count']>0,1, np.nan)\n", "itemCountPerFan['multiple_item'] =np.where(itemCountPerFan['count']>1,1, np.nan)\n", "itemCountPerFan['purchase_last_30d'] =np.where(\n", " (calculate_diff(itemCountPerFan['last_date'], pd.to_datetime('today'))) / pd.Timedelta(1, unit='d')<90,1, np.nan\n", " )\n", "itemCountPerFan.drop(['count','last_date','value'], axis=1,inplace=True)\n", "\n", "fan_optins['multi_optin']=np.where(fan_optins['optin_count']>1,1, np.nan)\n", "fan_optins.drop(['optin_count'], axis=1,inplace=True)\n", "\n", "# merge dataframes\n", "rawSuperFanDf = pd.merge(uniqueFans, itemCountPerFan, how='left', on=['fan_id'])\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, fan_optins, how='left', on=['fan_id'])\n", "rawSuperFanDf['total_score'] = rawSuperFanDf.sum(axis=1)\n", "# write superfandata into RDS\n", "rawSuperFanDf.to_sql(f'superfans',\n", " engine,\n", " schema=schema,\n", " if_exists='replace',\n", " #index_label='fan_id',\n", " index=False\n", " )\n", "\n", "# print(rawSuperFanDf.head(5))\n", "groupedSuperFanData = rawSuperFanDf.groupby(['total_score'],as_index=False).count()\n", "print(groupedSuperFanData)\n", "\n", "# Join emails\n", "rawSuperFanDf = pd.merge(rawSuperFanDf, fan_emails, how='left', on=['fan_id'])\n", "\n", "#rawSuperFanDf.loc[rawSuperFanDf['total_score']==2]\n", "\n", "# Select some good segments (4,5,6)\n", "superfans = rawSuperFanDf.loc[rawSuperFanDf['total_score'].isin([3,4,5,6])]\n", "# print(superfans.head(10))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "topc = pd.read_csv(\"top1000_us_cities_lat_long.csv\",delimiter=\";\")\n", "print(topc.shape)\n", "topc.to_sql(f'top1000_us_cities',\n", " engine,\n", " schema=schema,\n", " if_exists='replace',\n", " #index_label='fan_id',\n", " index=False\n", " )" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# schema, customer, treshold= lowest score group to include\n", "plot_superfan_maps(schema,customer,4)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df = prepare_rfm_dataset_pixies(schema)\n", "print(df.dtypes)\n", "print(df.shape)\n", "# df_=df.filter(items=['fan_id','rfm_segment'])\n", "#print(df_.dtypes)\n", "#print(df_.shape)\n", "#df_.to_sql(f'rfm_segments',\n", "# engine,\n", "# schema=schema,\n", "# if_exists='replace',\n", "# #index_label='fan_id',\n", "# index=False\n", "# )\n", "gg = df.groupby([\"rfm_segment\"],as_index=False)[\"fan_id\"].count()\n", "print(gg)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df2 = prepare_fan_demographics(schema)\n", "print(df2.dtypes)\n", "print(df2.shape)\n", "print(df2.tail(5))" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "df['Sales_Bins'] = pd.qcut(df.monetary_value,4)\n", "df.Sales_Bins.value_counts().sort_index()\n", "bins = list(df.Sales_Bins.value_counts().sort_index())\n", "print(bins)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# print(df.groupby('rfm_segment')[\"fan_id\"].count())\n", "# clusterGender = clusteredFans.groupby([\"cluster\",\"gender\"],as_index=False)[\"fan_id\"].count().rename(columns={'fan_id' : 'count'})\n", "plot_rfm_data(schema,df,customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# rfm_segment='Hibernating'\n", "rfm_segment='Champions'\n", "# rfm_segment='Loyal Customers'\n", "rfm_segment=6\n", "plot_us_cities(schema,customer,rfm_segment)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_merch_cluster_results(schema, customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_merch_cluster_top_items(schema, customer)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "plot_merch_timeseries(schema,customer)" ] }, { "cell_type": "code", "execution_count": 32, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "Lose\n" ] }, { "data": { "text/html": [ "
Make this Notebook Trusted to load map: File -> Trust Notebook
" ], "text/plain": [ "" ] }, "execution_count": 32, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# plot RFM CLUSTER RESULTS\n", "rfm_segment=\"Lose\"\n", "plot_us_cities(schema,customer,rfm_segment)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "# let's plot most popular items by for hour 12-13 and dow 1\n", "engine = get_rds_engine() \n", "\n", "query7 = f\"\"\"\n", "select extract(year from merch_purchase_date::timestamp)as year,\n", "substring(merch_name from '(^.+)(( -)|(,))') as item,\n", "count(fan_id) as count from ace3c2bb4abd9fcaf9807975e6ab940131386dc9ecddb2b7e31288ff8.fan_merch_data_temp\n", "where merch_purchase_date is not null and CAST(merch_purchase_quantity AS INTEGER)>0 and\n", "extract(isodow from merch_purchase_date::timestamp)=3 and \n", "extract(hour from merch_purchase_date::timestamp) in(6,7,8,9) and\n", "extract(year from merch_purchase_date::timestamp)=2018\n", "group by 1,2\n", "order by 3 desc\n", "\"\"\"\n", "dowitems = pd.read_sql(query7, engine)\n", "print(dowitems.head(20))\n" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "engine = get_rds_engine() \n", "\n", "queryX = f\"\"\"\n", "select count(s.fan_id), s.rfm_segment,t.\"City\" from {schema}.rfm_segments s\n", "inner join {schema}.fan_address_geocoded fag on s.fan_id=fag.fan_id\n", "inner join {schema}.top1000_us_cities t on t.\"City\"= fag.locality\n", "and t.\"State\"=fag.administrative_area_level_1 where s.rfm_segment in ('Champions','Loyal Customers') or\n", "s.rfm_segment like '%%Lose%%'\n", "group by s.rfm_segment, t.\"City\" order by 2, 1 desc\n", "\"\"\"\n", "rfm_by_city= pd.read_sql(queryX, engine)\n", "print(rfm_by_city.head(5))\n", "print(rfm_by_city.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "aa=rfm_by_city[rfm_by_city['rfm_segment']=='Champions']\n", "aa['perc']= aa['count']/aa['count'].sum()\n", "aa=aa.head(5)\n", "print(aa)\n", "\n", "bb=rfm_by_city[rfm_by_city['rfm_segment']==\"Can't Lose Them\"]\n", "bb['perc']= bb['count']/bb['count'].sum()\n", "bb=bb.head(5)\n", "print(bb)\n", "\n", "\n", "cc=rfm_by_city[rfm_by_city['rfm_segment']=='Loyal Customers']\n", "cc['perc']= cc['count']/cc['count'].sum()\n", "cc=cc.head(5)\n", "print(cc)\n", "\n", "total=aa.append(bb) \n", "total=total.append(cc)\n", "total['perc']=total['perc'].round(decimals=2)\n", "print(total.shape)" ] }, { "cell_type": "code", "execution_count": null, "metadata": {}, "outputs": [], "source": [ "dfPivot=total.pivot(index=\"rfm_segment\",columns=\"City\",values=\"perc\")\n", "dfPivot.fillna(0,inplace=True)\n", "dfPivot\n", "plt.figure(figsize=(16, 6))\n", "\n", "plt.title(\"RFM segment and City heatmap(top 5 cities in percentage)\",fontsize=18)\n", "plt.xlabel(\"\")\n", "plt.ylabel('')\n", "redpink = [\"#fac1c0\", \"#fdb2b1\", \"#ffa3a3\", \"#ff9494\", \"#ff8486\", \"#ff7377\",\"#ff6169\",\"#ff4d5b\"]\n", "# sns.heatmap(dfPivot,fmt=\"\",cmap='YlGn', annot=True)\n", "sns.heatmap(dfPivot,fmt=\"\",cmap=sns.color_palette(redpink), annot=True,linewidths=0.1, linecolor='gray')" ] } ], "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 }