{
"cells": [
{
"cell_type": "markdown",
"id": "0355328f-91c9-4c3e-9020-8f32234068d5",
"metadata": {},
"source": [
"# Managerial Segmentation"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d410be1e-2ffa-4174-abe0-50983ad5b0c8",
"metadata": {},
"outputs": [],
"source": [
"import pandas as pd\n",
"import numpy as np\n",
"import scipy as sp\n",
"import matplotlib.pyplot as plt\n",
"import seaborn as sns\n",
"import datetime as dt\n",
"from sklearn.cluster import AgglomerativeClustering\n",
"from scipy.cluster.hierarchy import dendrogram, linkage\n",
"from sklearn.pipeline import Pipeline, make_pipeline\n",
"from sklearn.preprocessing import StandardScaler, RobustScaler, FunctionTransformer"
]
},
{
"cell_type": "markdown",
"id": "fdcb13cf-3709-4828-ba85-4994a3812e42",
"metadata": {},
"source": [
"## Read Dataset"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "7362167b-6ab5-49fa-932a-c124751886e3",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"
\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" consumer_id | \n",
" purchase_amount | \n",
" date_of_purchase | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 760 | \n",
" 25.0 | \n",
" 2009-11-06 | \n",
"
\n",
" \n",
" 1 | \n",
" 860 | \n",
" 50.0 | \n",
" 2012-09-28 | \n",
"
\n",
" \n",
" 2 | \n",
" 1200 | \n",
" 100.0 | \n",
" 2005-10-25 | \n",
"
\n",
" \n",
" 3 | \n",
" 1420 | \n",
" 50.0 | \n",
" 2009-07-09 | \n",
"
\n",
" \n",
" 4 | \n",
" 1940 | \n",
" 70.0 | \n",
" 2013-01-25 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" consumer_id purchase_amount date_of_purchase\n",
"0 760 25.0 2009-11-06\n",
"1 860 50.0 2012-09-28\n",
"2 1200 100.0 2005-10-25\n",
"3 1420 50.0 2009-07-09\n",
"4 1940 70.0 2013-01-25"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df = pd.read_csv(\"W106_purchases.txt\", sep='\\t', names=['consumer_id','purchase_amount', 'date_of_purchase'], parse_dates=['date_of_purchase'] ); df.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "1fb495ab-2ef6-423c-a73e-1f3b62f15a61",
"metadata": {},
"outputs": [
{
"name": "stderr",
"output_type": "stream",
"text": [
"/tmp/ipykernel_36820/2884002236.py:1: FutureWarning: Treating datetime data as categorical rather than numeric in `.describe` is deprecated and will be removed in a future version of pandas. Specify `datetime_is_numeric=True` to silence this warning and adopt the future behavior now.\n",
" df.describe(include='all')\n"
]
},
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" consumer_id | \n",
" purchase_amount | \n",
" date_of_purchase | \n",
"
\n",
" \n",
" \n",
" \n",
" count | \n",
" 51243.000000 | \n",
" 51243.000000 | \n",
" 51243 | \n",
"
\n",
" \n",
" unique | \n",
" NaN | \n",
" NaN | \n",
" 1879 | \n",
"
\n",
" \n",
" top | \n",
" NaN | \n",
" NaN | \n",
" 2013-12-31 00:00:00 | \n",
"
\n",
" \n",
" freq | \n",
" NaN | \n",
" NaN | \n",
" 864 | \n",
"
\n",
" \n",
" first | \n",
" NaN | \n",
" NaN | \n",
" 2005-01-02 00:00:00 | \n",
"
\n",
" \n",
" last | \n",
" NaN | \n",
" NaN | \n",
" 2015-12-31 00:00:00 | \n",
"
\n",
" \n",
" mean | \n",
" 108934.547938 | \n",
" 62.337195 | \n",
" NaN | \n",
"
\n",
" \n",
" std | \n",
" 67650.610139 | \n",
" 156.606801 | \n",
" NaN | \n",
"
\n",
" \n",
" min | \n",
" 10.000000 | \n",
" 5.000000 | \n",
" NaN | \n",
"
\n",
" \n",
" 25% | \n",
" 57720.000000 | \n",
" 25.000000 | \n",
" NaN | \n",
"
\n",
" \n",
" 50% | \n",
" 102440.000000 | \n",
" 30.000000 | \n",
" NaN | \n",
"
\n",
" \n",
" 75% | \n",
" 160525.000000 | \n",
" 60.000000 | \n",
" NaN | \n",
"
\n",
" \n",
" max | \n",
" 264200.000000 | \n",
" 4500.000000 | \n",
" NaN | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" consumer_id purchase_amount date_of_purchase\n",
"count 51243.000000 51243.000000 51243\n",
"unique NaN NaN 1879\n",
"top NaN NaN 2013-12-31 00:00:00\n",
"freq NaN NaN 864\n",
"first NaN NaN 2005-01-02 00:00:00\n",
"last NaN NaN 2015-12-31 00:00:00\n",
"mean 108934.547938 62.337195 NaN\n",
"std 67650.610139 156.606801 NaN\n",
"min 10.000000 5.000000 NaN\n",
"25% 57720.000000 25.000000 NaN\n",
"50% 102440.000000 30.000000 NaN\n",
"75% 160525.000000 60.000000 NaN\n",
"max 264200.000000 4500.000000 NaN"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.describe(include='all')"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "a6a8ca1e-122b-4be2-85c2-36ecfd8cbdb6",
"metadata": {},
"outputs": [],
"source": [
"df['year_of_purchase'] = df['date_of_purchase'].dt.year"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "9f0468fc-cbba-4e15-9ca6-ad2ba73ab46e",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Timestamp('2016-01-01 00:00:00')"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"max_date = df['date_of_purchase'].max() + dt.timedelta(days=1); max_date"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "d663e106-ebef-4976-84c1-701d88b93db4",
"metadata": {},
"outputs": [],
"source": [
"df['days_since'] = (max_date - df['date_of_purchase']).dt.days"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "2e162479-d552-4e58-afd9-e3fef825a03f",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" consumer_id | \n",
" purchase_amount | \n",
" date_of_purchase | \n",
" year_of_purchase | \n",
" days_since | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 760 | \n",
" 25.0 | \n",
" 2009-11-06 | \n",
" 2009 | \n",
" 2247 | \n",
"
\n",
" \n",
" 1 | \n",
" 860 | \n",
" 50.0 | \n",
" 2012-09-28 | \n",
" 2012 | \n",
" 1190 | \n",
"
\n",
" \n",
" 2 | \n",
" 1200 | \n",
" 100.0 | \n",
" 2005-10-25 | \n",
" 2005 | \n",
" 3720 | \n",
"
\n",
" \n",
" 3 | \n",
" 1420 | \n",
" 50.0 | \n",
" 2009-07-09 | \n",
" 2009 | \n",
" 2367 | \n",
"
\n",
" \n",
" 4 | \n",
" 1940 | \n",
" 70.0 | \n",
" 2013-01-25 | \n",
" 2013 | \n",
" 1071 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" consumer_id purchase_amount date_of_purchase year_of_purchase days_since\n",
"0 760 25.0 2009-11-06 2009 2247\n",
"1 860 50.0 2012-09-28 2012 1190\n",
"2 1200 100.0 2005-10-25 2005 3720\n",
"3 1420 50.0 2009-07-09 2009 2367\n",
"4 1940 70.0 2013-01-25 2013 1071"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.head()"
]
},
{
"cell_type": "markdown",
"id": "4fa5f6bd-17ac-48dc-9dc6-c57bc4a5da2a",
"metadata": {},
"source": [
"## Segmentation"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "fc76d84c-85df-415e-b7b6-646acaadced8",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" recency | \n",
" frequency | \n",
" monetary | \n",
" first_purchase | \n",
" first_purchase_date | \n",
" first_purchase_year | \n",
" last_purchase_date | \n",
" last_purchase_year | \n",
"
\n",
" \n",
" consumer_id | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 10 | \n",
" 3829 | \n",
" 1 | \n",
" 30.000000 | \n",
" 3829 | \n",
" 2005-07-08 | \n",
" 2005 | \n",
" 2005-07-08 | \n",
" 2005 | \n",
"
\n",
" \n",
" 80 | \n",
" 343 | \n",
" 7 | \n",
" 71.428571 | \n",
" 3751 | \n",
" 2005-09-24 | \n",
" 2005 | \n",
" 2015-01-23 | \n",
" 2015 | \n",
"
\n",
" \n",
" 90 | \n",
" 758 | \n",
" 10 | \n",
" 115.800000 | \n",
" 3783 | \n",
" 2005-08-23 | \n",
" 2005 | \n",
" 2013-12-04 | \n",
" 2013 | \n",
"
\n",
" \n",
" 120 | \n",
" 1401 | \n",
" 1 | \n",
" 20.000000 | \n",
" 1401 | \n",
" 2012-03-01 | \n",
" 2012 | \n",
" 2012-03-01 | \n",
" 2012 | \n",
"
\n",
" \n",
" 130 | \n",
" 2970 | \n",
" 2 | \n",
" 50.000000 | \n",
" 3710 | \n",
" 2005-11-04 | \n",
" 2005 | \n",
" 2007-11-14 | \n",
" 2007 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" recency frequency monetary first_purchase \\\n",
"consumer_id \n",
"10 3829 1 30.000000 3829 \n",
"80 343 7 71.428571 3751 \n",
"90 758 10 115.800000 3783 \n",
"120 1401 1 20.000000 1401 \n",
"130 2970 2 50.000000 3710 \n",
"\n",
" first_purchase_date first_purchase_year last_purchase_date \\\n",
"consumer_id \n",
"10 2005-07-08 2005 2005-07-08 \n",
"80 2005-09-24 2005 2015-01-23 \n",
"90 2005-08-23 2005 2013-12-04 \n",
"120 2012-03-01 2012 2012-03-01 \n",
"130 2005-11-04 2005 2007-11-14 \n",
"\n",
" last_purchase_year \n",
"consumer_id \n",
"10 2005 \n",
"80 2015 \n",
"90 2013 \n",
"120 2012 \n",
"130 2007 "
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"consumer_df = df.groupby('consumer_id').agg(recency=('days_since', 'min'),\n",
" frequency=('date_of_purchase', 'count'),\n",
" monetary=('purchase_amount', 'mean'),\n",
" first_purchase=('days_since', 'max'),\n",
" first_purchase_date=('date_of_purchase', 'min'),\n",
" first_purchase_year=('year_of_purchase', 'min'),\n",
" last_purchase_date=('date_of_purchase', 'max'),\n",
" last_purchase_year=('year_of_purchase', 'max'),\n",
" ); consumer_df.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "cdb907e5-87b6-4faa-965f-519d8b59802f",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"Int64Index: 18417 entries, 10 to 264200\n",
"Data columns (total 8 columns):\n",
" # Column Non-Null Count Dtype \n",
"--- ------ -------------- ----- \n",
" 0 recency 18417 non-null int64 \n",
" 1 frequency 18417 non-null int64 \n",
" 2 monetary 18417 non-null float64 \n",
" 3 first_purchase 18417 non-null int64 \n",
" 4 first_purchase_date 18417 non-null datetime64[ns]\n",
" 5 first_purchase_year 18417 non-null int64 \n",
" 6 last_purchase_date 18417 non-null datetime64[ns]\n",
" 7 last_purchase_year 18417 non-null int64 \n",
"dtypes: datetime64[ns](2), float64(1), int64(5)\n",
"memory usage: 1.3 MB\n"
]
}
],
"source": [
"consumer_df.info()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "105aa7f5-0cf7-49f1-8471-1b89ac68c2f4",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" recency | \n",
" frequency | \n",
" monetary | \n",
" first_purchase | \n",
" first_purchase_year | \n",
" last_purchase_year | \n",
"
\n",
" \n",
" \n",
" \n",
" count | \n",
" 18417.000000 | \n",
" 18417.000000 | \n",
" 18417.000000 | \n",
" 18417.000000 | \n",
" 18417.000000 | \n",
" 18417.000000 | \n",
"
\n",
" \n",
" mean | \n",
" 1253.037900 | \n",
" 2.782375 | \n",
" 57.792985 | \n",
" 1984.009882 | \n",
" 2009.925178 | \n",
" 2011.906825 | \n",
"
\n",
" \n",
" std | \n",
" 1081.437868 | \n",
" 2.936888 | \n",
" 154.360109 | \n",
" 1133.405441 | \n",
" 3.068690 | \n",
" 2.914005 | \n",
"
\n",
" \n",
" min | \n",
" 1.000000 | \n",
" 1.000000 | \n",
" 5.000000 | \n",
" 1.000000 | \n",
" 2005.000000 | \n",
" 2005.000000 | \n",
"
\n",
" \n",
" 25% | \n",
" 244.000000 | \n",
" 1.000000 | \n",
" 21.666667 | \n",
" 988.000000 | \n",
" 2007.000000 | \n",
" 2010.000000 | \n",
"
\n",
" \n",
" 50% | \n",
" 1070.000000 | \n",
" 2.000000 | \n",
" 30.000000 | \n",
" 2087.000000 | \n",
" 2010.000000 | \n",
" 2013.000000 | \n",
"
\n",
" \n",
" 75% | \n",
" 2130.000000 | \n",
" 3.000000 | \n",
" 50.000000 | \n",
" 2992.000000 | \n",
" 2013.000000 | \n",
" 2015.000000 | \n",
"
\n",
" \n",
" max | \n",
" 4014.000000 | \n",
" 45.000000 | \n",
" 4500.000000 | \n",
" 4016.000000 | \n",
" 2015.000000 | \n",
" 2015.000000 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" recency frequency monetary first_purchase \\\n",
"count 18417.000000 18417.000000 18417.000000 18417.000000 \n",
"mean 1253.037900 2.782375 57.792985 1984.009882 \n",
"std 1081.437868 2.936888 154.360109 1133.405441 \n",
"min 1.000000 1.000000 5.000000 1.000000 \n",
"25% 244.000000 1.000000 21.666667 988.000000 \n",
"50% 1070.000000 2.000000 30.000000 2087.000000 \n",
"75% 2130.000000 3.000000 50.000000 2992.000000 \n",
"max 4014.000000 45.000000 4500.000000 4016.000000 \n",
"\n",
" first_purchase_year last_purchase_year \n",
"count 18417.000000 18417.000000 \n",
"mean 2009.925178 2011.906825 \n",
"std 3.068690 2.914005 \n",
"min 2005.000000 2005.000000 \n",
"25% 2007.000000 2010.000000 \n",
"50% 2010.000000 2013.000000 \n",
"75% 2013.000000 2015.000000 \n",
"max 2015.000000 2015.000000 "
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"consumer_df.describe()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "af1a2a35-1402-4d63-bcb2-a5c7b4dc544b",
"metadata": {},
"outputs": [],
"source": [
"consumer_df['segment'] = np.nan\n",
"# consumer_df.loc[consumer_df['recency'] <=365 , 'segment'] = 'active'\n",
"consumer_df.loc[(consumer_df['recency'] <=365) & (consumer_df['monetary'] >= 100), 'segment'] = 'active high'\n",
"consumer_df.loc[(consumer_df['recency'] <=365) & (consumer_df['monetary'] < 100), 'segment'] = 'active low'\n",
"consumer_df.loc[(consumer_df['recency'] <=365) & (consumer_df['first_purchase'] <=365) & (consumer_df['monetary'] >= 100) , 'segment'] = 'new active high'\n",
"consumer_df.loc[(consumer_df['recency'] <=365) & (consumer_df['first_purchase'] <=365) & (consumer_df['monetary'] < 100) , 'segment'] = 'new active low'\n",
"consumer_df.loc[(consumer_df['recency']>365) & (consumer_df['recency']<=2*365) & (consumer_df['monetary'] >= 100), 'segment'] = 'warm high'\n",
"consumer_df.loc[(consumer_df['recency']>365) & (consumer_df['recency']<=2*365) & (consumer_df['monetary'] < 100), 'segment'] = 'warm low'\n",
"consumer_df.loc[(consumer_df['recency']>365) & (consumer_df['recency']<=2*365) & (consumer_df['first_purchase'] <=2*365) & (consumer_df['monetary'] >= 100) , 'segment'] = 'new warm high'\n",
"consumer_df.loc[(consumer_df['recency']>365) & (consumer_df['recency']<=2*365) & (consumer_df['first_purchase'] <=2*365) & (consumer_df['monetary'] < 100) , 'segment'] = 'new warm low'\n",
"consumer_df.loc[(consumer_df['recency']>2*365) & (consumer_df['recency']<=3*365), 'segment'] = 'cold'\n",
"consumer_df.loc[(consumer_df['recency']>3*365), 'segment'] = 'inactive'"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "1d4e0d1b-c3df-4939-b24a-f23461c9a608",
"metadata": {},
"outputs": [],
"source": [
"# consumer_df.groupby('segment').count()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "de65d8dd-5cda-41a4-8b51-672f51bc0e71",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"inactive 9158\n",
"active low 3313\n",
"cold 1903\n",
"new active low 1249\n",
"warm low 901\n",
"new warm low 822\n",
"active high 573\n",
"new active high 263\n",
"warm high 119\n",
"new warm high 116\n",
"Name: segment, dtype: int64"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"consumer_df['segment'].value_counts(dropna=False)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "560d7eb7-1c2b-4d37-9e3a-d930e13454ff",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"18417"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"consumer_df['segment'].value_counts(dropna=False).sum()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "2b85d5e6-c1c1-43d1-974f-d031cefebb19",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" size | \n",
" recency_min | \n",
" recency | \n",
" recency_max | \n",
" frequency_min | \n",
" frequency | \n",
" frequency_max | \n",
" monetary_min | \n",
" monetary | \n",
" monetary_max | \n",
" first_purchase | \n",
" first_purchase_date | \n",
" first_purchase_year | \n",
" last_purchase_date | \n",
" last_purchase_year | \n",
" revenue | \n",
"
\n",
" \n",
" segment | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" new active high | \n",
" 263 | \n",
" 1 | \n",
" 82.372624 | \n",
" 360 | \n",
" 1 | \n",
" 1.015209 | \n",
" 2 | \n",
" 100.0 | \n",
" 283.381027 | \n",
" 4500.000000 | \n",
" 360 | \n",
" 2015-01-06 | \n",
" 2015 | \n",
" 2015-12-31 | \n",
" 2015 | \n",
" 74529.0 | \n",
"
\n",
" \n",
" new active low | \n",
" 1249 | \n",
" 1 | \n",
" 85.542034 | \n",
" 359 | \n",
" 1 | \n",
" 1.052042 | \n",
" 11 | \n",
" 5.0 | \n",
" 33.704697 | \n",
" 90.000000 | \n",
" 359 | \n",
" 2015-01-07 | \n",
" 2015 | \n",
" 2015-12-31 | \n",
" 2015 | \n",
" 42097.0 | \n",
"
\n",
" \n",
" active high | \n",
" 573 | \n",
" 1 | \n",
" 88.820244 | \n",
" 344 | \n",
" 2 | \n",
" 5.888307 | \n",
" 19 | \n",
" 100.0 | \n",
" 240.045740 | \n",
" 4500.000000 | \n",
" 4010 | \n",
" 2005-01-08 | \n",
" 2005 | \n",
" 2015-12-31 | \n",
" 2015 | \n",
" 137546.0 | \n",
"
\n",
" \n",
" active low | \n",
" 3313 | \n",
" 1 | \n",
" 108.361002 | \n",
" 359 | \n",
" 2 | \n",
" 5.935406 | \n",
" 45 | \n",
" 5.0 | \n",
" 40.724525 | \n",
" 99.000000 | \n",
" 4012 | \n",
" 2005-01-06 | \n",
" 2005 | \n",
" 2015-12-31 | \n",
" 2015 | \n",
" 134920.0 | \n",
"
\n",
" \n",
" new warm high | \n",
" 116 | \n",
" 366 | \n",
" 476.060345 | \n",
" 701 | \n",
" 1 | \n",
" 1.060345 | \n",
" 4 | \n",
" 100.0 | \n",
" 302.036379 | \n",
" 4000.000000 | \n",
" 707 | \n",
" 2014-01-24 | \n",
" 2014 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
" 35036.0 | \n",
"
\n",
" \n",
" new warm low | \n",
" 822 | \n",
" 366 | \n",
" 513.996350 | \n",
" 720 | \n",
" 1 | \n",
" 1.042579 | \n",
" 3 | \n",
" 5.0 | \n",
" 33.374290 | \n",
" 90.000000 | \n",
" 720 | \n",
" 2014-01-11 | \n",
" 2014 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
" 27434.0 | \n",
"
\n",
" \n",
" warm high | \n",
" 119 | \n",
" 366 | \n",
" 455.126050 | \n",
" 665 | \n",
" 2 | \n",
" 4.714286 | \n",
" 14 | \n",
" 100.0 | \n",
" 327.407457 | \n",
" 4000.000000 | \n",
" 4004 | \n",
" 2005-01-14 | \n",
" 2005 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
" 38961.0 | \n",
"
\n",
" \n",
" warm low | \n",
" 901 | \n",
" 366 | \n",
" 474.377358 | \n",
" 707 | \n",
" 2 | \n",
" 4.531632 | \n",
" 20 | \n",
" 5.0 | \n",
" 38.591926 | \n",
" 96.428571 | \n",
" 4011 | \n",
" 2005-01-07 | \n",
" 2005 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
" 34771.0 | \n",
"
\n",
" \n",
" cold | \n",
" 1903 | \n",
" 731 | \n",
" 857.781398 | \n",
" 1087 | \n",
" 1 | \n",
" 2.303205 | \n",
" 20 | \n",
" 5.0 | \n",
" 51.739893 | \n",
" 2000.000000 | \n",
" 4016 | \n",
" 2005-01-02 | \n",
" 2005 | \n",
" 2013-12-31 | \n",
" 2013 | \n",
" 98461.0 | \n",
"
\n",
" \n",
" inactive | \n",
" 9158 | \n",
" 1096 | \n",
" 2178.110832 | \n",
" 4014 | \n",
" 1 | \n",
" 1.814479 | \n",
" 23 | \n",
" 5.0 | \n",
" 48.112771 | \n",
" 3043.750000 | \n",
" 4016 | \n",
" 2005-01-02 | \n",
" 2005 | \n",
" 2012-12-31 | \n",
" 2012 | \n",
" 440617.0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" size recency_min recency recency_max frequency_min \\\n",
"segment \n",
"new active high 263 1 82.372624 360 1 \n",
"new active low 1249 1 85.542034 359 1 \n",
"active high 573 1 88.820244 344 2 \n",
"active low 3313 1 108.361002 359 2 \n",
"new warm high 116 366 476.060345 701 1 \n",
"new warm low 822 366 513.996350 720 1 \n",
"warm high 119 366 455.126050 665 2 \n",
"warm low 901 366 474.377358 707 2 \n",
"cold 1903 731 857.781398 1087 1 \n",
"inactive 9158 1096 2178.110832 4014 1 \n",
"\n",
" frequency frequency_max monetary_min monetary \\\n",
"segment \n",
"new active high 1.015209 2 100.0 283.381027 \n",
"new active low 1.052042 11 5.0 33.704697 \n",
"active high 5.888307 19 100.0 240.045740 \n",
"active low 5.935406 45 5.0 40.724525 \n",
"new warm high 1.060345 4 100.0 302.036379 \n",
"new warm low 1.042579 3 5.0 33.374290 \n",
"warm high 4.714286 14 100.0 327.407457 \n",
"warm low 4.531632 20 5.0 38.591926 \n",
"cold 2.303205 20 5.0 51.739893 \n",
"inactive 1.814479 23 5.0 48.112771 \n",
"\n",
" monetary_max first_purchase first_purchase_date \\\n",
"segment \n",
"new active high 4500.000000 360 2015-01-06 \n",
"new active low 90.000000 359 2015-01-07 \n",
"active high 4500.000000 4010 2005-01-08 \n",
"active low 99.000000 4012 2005-01-06 \n",
"new warm high 4000.000000 707 2014-01-24 \n",
"new warm low 90.000000 720 2014-01-11 \n",
"warm high 4000.000000 4004 2005-01-14 \n",
"warm low 96.428571 4011 2005-01-07 \n",
"cold 2000.000000 4016 2005-01-02 \n",
"inactive 3043.750000 4016 2005-01-02 \n",
"\n",
" first_purchase_year last_purchase_date last_purchase_year \\\n",
"segment \n",
"new active high 2015 2015-12-31 2015 \n",
"new active low 2015 2015-12-31 2015 \n",
"active high 2005 2015-12-31 2015 \n",
"active low 2005 2015-12-31 2015 \n",
"new warm high 2014 2014-12-31 2014 \n",
"new warm low 2014 2014-12-31 2014 \n",
"warm high 2005 2014-12-31 2014 \n",
"warm low 2005 2014-12-31 2014 \n",
"cold 2005 2013-12-31 2013 \n",
"inactive 2005 2012-12-31 2012 \n",
"\n",
" revenue \n",
"segment \n",
"new active high 74529.0 \n",
"new active low 42097.0 \n",
"active high 137546.0 \n",
"active low 134920.0 \n",
"new warm high 35036.0 \n",
"new warm low 27434.0 \n",
"warm high 38961.0 \n",
"warm low 34771.0 \n",
"cold 98461.0 \n",
"inactive 440617.0 "
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"segment_df = consumer_df.groupby('segment').agg(size=('recency', 'count'),\n",
" recency_min=('recency', 'min'),\n",
" recency=('recency', 'mean'),\n",
" recency_max=('recency', 'max'),\n",
" frequency_min=('frequency', 'min'),\n",
" frequency=('frequency', 'mean'),\n",
" frequency_max=('frequency', 'max'),\n",
" monetary_min=('monetary', 'min'),\n",
" monetary=('monetary', 'mean'),\n",
" monetary_max=('monetary', 'max'),\n",
" first_purchase=('first_purchase', 'max'),\n",
" first_purchase_date=('first_purchase_date', 'min'),\n",
" first_purchase_year=('first_purchase_year', 'min'),\n",
" last_purchase_date=('last_purchase_date', 'max'),\n",
" last_purchase_year=('last_purchase_year', 'max')\n",
" ).sort_values(by=['last_purchase_year', 'first_purchase_year','monetary'], ascending=[False, False, False])\n",
"segment_df['revenue'] = np.round(segment_df['size']*segment_df['monetary'], 0)\n",
"segment_df"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "8937c07b-7cd4-4ef4-85d4-656c62ba7d90",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"Timestamp('2015-12-31 00:00:00')"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"consumer_df['last_purchase_date'].max()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "a50627b3-5c5f-492c-8fab-0e1967b10871",
"metadata": {},
"outputs": [],
"source": [
"def get_segment_summary(consumer_df):\n",
" if 'revenue' in consumer_df.columns:\n",
" segment_df = consumer_df.groupby('segment').agg(size=('recency', 'count'),\n",
" revenue_today=('revenue', 'sum'),\n",
" avg_revenue_today=('revenue', 'mean'),\n",
" revenue_till_date=('revenue_till_date', 'sum'),\n",
" avg_revenue_till_date=('revenue_till_date', 'mean'),\n",
" recency_min=('recency', 'min'),\n",
" recency=('recency', 'mean'),\n",
" recency_max=('recency', 'max'),\n",
" frequency_min=('frequency', 'min'),\n",
" frequency=('frequency', 'mean'),\n",
" frequency_max=('frequency', 'max'),\n",
" monetary_min=('monetary', 'min'),\n",
" monetary=('monetary', 'mean'),\n",
" monetary_max=('monetary', 'max'),\n",
" first_purchase=('first_purchase', 'max'),\n",
" first_purchase_date=('first_purchase_date', 'min'),\n",
" first_purchase_year=('first_purchase_year', 'min'),\n",
" last_purchase_date=('last_purchase_date', 'max'),\n",
" last_purchase_year=('last_purchase_year', 'max')\n",
" ).sort_values(by=['last_purchase_year', 'first_purchase_year','monetary'], ascending=[False, False, False])\n",
" else:\n",
" segment_df = consumer_df.groupby('segment').agg(size=('recency', 'count'),\n",
" revenue_till_date=('revenue_till_date', 'sum'),\n",
" avg_revenue_till_date=('revenue_till_date', 'mean'),\n",
" recency_min=('recency', 'min'),\n",
" recency=('recency', 'mean'),\n",
" recency_max=('recency', 'max'),\n",
" frequency_min=('frequency', 'min'),\n",
" frequency=('frequency', 'mean'),\n",
" frequency_max=('frequency', 'max'),\n",
" monetary_min=('monetary', 'min'),\n",
" monetary=('monetary', 'mean'),\n",
" monetary_max=('monetary', 'max'),\n",
" first_purchase=('first_purchase', 'max'),\n",
" first_purchase_date=('first_purchase_date', 'min'),\n",
" first_purchase_year=('first_purchase_year', 'min'),\n",
" last_purchase_date=('last_purchase_date', 'max'),\n",
" last_purchase_year=('last_purchase_year', 'max')\n",
" ).sort_values(by=['last_purchase_year', 'first_purchase_year','monetary'], ascending=[False, False, False]) \n",
" return segment_df"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "6a75c3cc-e980-41b2-8904-45d15c395155",
"metadata": {},
"outputs": [],
"source": [
"def get_consumer_df(df, offset=0):\n",
" df = df.copy()\n",
" df = df[df['days_since']>offset]\n",
" consumer_df = df.groupby('consumer_id').agg(recency=('days_since', 'min'),\n",
" frequency=('date_of_purchase', 'count'),\n",
" monetary=('purchase_amount', 'mean'),\n",
" revenue_till_date=('purchase_amount', 'sum'),\n",
" first_purchase=('days_since', 'max'),\n",
" first_purchase_date=('date_of_purchase', 'min'),\n",
" first_purchase_year=('year_of_purchase', 'min'),\n",
" last_purchase_date=('date_of_purchase', 'max'),\n",
" last_purchase_year=('year_of_purchase', 'max'),\n",
" ); consumer_df.head()\n",
" consumer_df['recency'] = consumer_df['recency'] - offset\n",
" consumer_df['first_purchase'] = consumer_df['first_purchase'] - offset\n",
" consumer_df['segment'] = np.nan\n",
" rec_filter = consumer_df['recency'] \n",
" first_purchase_filter = consumer_df['first_purchase']\n",
" consumer_df.loc[(rec_filter <=365) & (consumer_df['monetary'] >= 100), 'segment'] = 'active high'\n",
" consumer_df.loc[(rec_filter <=365) & (consumer_df['monetary'] < 100), 'segment'] = 'active low'\"\"\n",
" consumer_df.loc[(rec_filter <=365) & (first_purchase_filter <=365) & (consumer_df['monetary'] >= 100) , 'segment'] = 'new active high'\n",
" consumer_df.loc[(rec_filter <=365) & (first_purchase_filter <=365) & (consumer_df['monetary'] < 100) , 'segment'] = 'new active low'\n",
" consumer_df.loc[(rec_filter>365) & (rec_filter<=2*365) & (consumer_df['monetary'] >= 100), 'segment'] = 'warm high'\n",
" consumer_df.loc[(rec_filter>365) & (rec_filter<=2*365) & (consumer_df['monetary'] < 100), 'segment'] = 'warm low'\n",
" consumer_df.loc[(rec_filter>365) & (rec_filter<=2*365) & (first_purchase_filter <=2*365) & (consumer_df['monetary'] >= 100) , 'segment'] = 'new warm high'\n",
" consumer_df.loc[(rec_filter>365) & (rec_filter<=2*365) & (first_purchase_filter <=2*365) & (consumer_df['monetary'] < 100) , 'segment'] = 'new warm low'\n",
" consumer_df.loc[(rec_filter>2*365) & (rec_filter<=3*365), 'segment'] = 'cold'\n",
" consumer_df.loc[(rec_filter>3*365), 'segment'] = 'inactive'\n",
" return consumer_df"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "19e9b005-168e-422b-9e51-320352fd0818",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" size | \n",
" revenue_till_date | \n",
" avg_revenue_till_date | \n",
" recency_min | \n",
" recency | \n",
" recency_max | \n",
" frequency_min | \n",
" frequency | \n",
" frequency_max | \n",
" monetary_min | \n",
" monetary | \n",
" monetary_max | \n",
" first_purchase | \n",
" first_purchase_date | \n",
" first_purchase_year | \n",
" last_purchase_date | \n",
" last_purchase_year | \n",
"
\n",
" \n",
" segment | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" new active high | \n",
" 203 | \n",
" 59506.22 | \n",
" 293.134089 | \n",
" 1 | \n",
" 94.615764 | \n",
" 336 | \n",
" 1 | \n",
" 1.049261 | \n",
" 4 | \n",
" 100.0 | \n",
" 284.673498 | \n",
" 4500.00 | \n",
" 342 | \n",
" 2014-01-24 | \n",
" 2014 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" new active low | \n",
" 1234 | \n",
" 45775.50 | \n",
" 37.095219 | \n",
" 1 | \n",
" 138.251216 | \n",
" 355 | \n",
" 1 | \n",
" 1.074554 | \n",
" 10 | \n",
" 5.0 | \n",
" 34.364938 | \n",
" 90.00 | \n",
" 362 | \n",
" 2014-01-04 | \n",
" 2014 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" active high | \n",
" 475 | \n",
" 665593.93 | \n",
" 1401.250379 | \n",
" 1 | \n",
" 85.338947 | \n",
" 342 | \n",
" 2 | \n",
" 5.696842 | \n",
" 17 | \n",
" 100.0 | \n",
" 261.902155 | \n",
" 4000.00 | \n",
" 3645 | \n",
" 2005-01-08 | \n",
" 2005 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" active low | \n",
" 3011 | \n",
" 699743.77 | \n",
" 232.395805 | \n",
" 1 | \n",
" 98.091000 | \n",
" 342 | \n",
" 2 | \n",
" 5.633677 | \n",
" 40 | \n",
" 5.0 | \n",
" 40.459174 | \n",
" 98.75 | \n",
" 3647 | \n",
" 2005-01-06 | \n",
" 2005 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" new warm high | \n",
" 127 | \n",
" 29418.34 | \n",
" 231.640472 | \n",
" 366 | \n",
" 477.622047 | \n",
" 721 | \n",
" 1 | \n",
" 1.047244 | \n",
" 2 | \n",
" 100.0 | \n",
" 212.845669 | \n",
" 2000.00 | \n",
" 721 | \n",
" 2013-01-10 | \n",
" 2013 | \n",
" 2013-12-31 | \n",
" 2013 | \n",
"
\n",
" \n",
" new warm low | \n",
" 1123 | \n",
" 39498.00 | \n",
" 35.171861 | \n",
" 366 | \n",
" 499.544969 | \n",
" 722 | \n",
" 1 | \n",
" 1.058771 | \n",
" 5 | \n",
" 5.0 | \n",
" 33.106115 | \n",
" 99.00 | \n",
" 722 | \n",
" 2013-01-09 | \n",
" 2013 | \n",
" 2013-12-31 | \n",
" 2013 | \n",
"
\n",
" \n",
" warm high | \n",
" 111 | \n",
" 92948.45 | \n",
" 837.373423 | \n",
" 366 | \n",
" 461.198198 | \n",
" 712 | \n",
" 2 | \n",
" 4.414414 | \n",
" 14 | \n",
" 100.0 | \n",
" 187.849110 | \n",
" 1250.00 | \n",
" 3635 | \n",
" 2005-01-18 | \n",
" 2005 | \n",
" 2013-12-31 | \n",
" 2013 | \n",
"
\n",
" \n",
" warm low | \n",
" 956 | \n",
" 157955.50 | \n",
" 165.225418 | \n",
" 366 | \n",
" 470.661088 | \n",
" 720 | \n",
" 2 | \n",
" 4.361925 | \n",
" 20 | \n",
" 5.0 | \n",
" 37.382060 | \n",
" 98.00 | \n",
" 3651 | \n",
" 2005-01-02 | \n",
" 2005 | \n",
" 2013-12-31 | \n",
" 2013 | \n",
"
\n",
" \n",
" cold | \n",
" 2153 | \n",
" 273645.09 | \n",
" 127.099438 | \n",
" 731 | \n",
" 866.616814 | \n",
" 1086 | \n",
" 1 | \n",
" 2.254064 | \n",
" 23 | \n",
" 5.0 | \n",
" 51.114605 | \n",
" 3043.75 | \n",
" 3651 | \n",
" 2005-01-02 | \n",
" 2005 | \n",
" 2012-12-31 | \n",
" 2012 | \n",
"
\n",
" \n",
" inactive | \n",
" 7512 | \n",
" 651865.97 | \n",
" 86.776620 | \n",
" 1097 | \n",
" 2058.441294 | \n",
" 3649 | \n",
" 1 | \n",
" 1.730964 | \n",
" 19 | \n",
" 5.0 | \n",
" 48.111199 | \n",
" 3000.00 | \n",
" 3651 | \n",
" 2005-01-02 | \n",
" 2005 | \n",
" 2011-12-31 | \n",
" 2011 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" size revenue_till_date avg_revenue_till_date recency_min \\\n",
"segment \n",
"new active high 203 59506.22 293.134089 1 \n",
"new active low 1234 45775.50 37.095219 1 \n",
"active high 475 665593.93 1401.250379 1 \n",
"active low 3011 699743.77 232.395805 1 \n",
"new warm high 127 29418.34 231.640472 366 \n",
"new warm low 1123 39498.00 35.171861 366 \n",
"warm high 111 92948.45 837.373423 366 \n",
"warm low 956 157955.50 165.225418 366 \n",
"cold 2153 273645.09 127.099438 731 \n",
"inactive 7512 651865.97 86.776620 1097 \n",
"\n",
" recency recency_max frequency_min frequency \\\n",
"segment \n",
"new active high 94.615764 336 1 1.049261 \n",
"new active low 138.251216 355 1 1.074554 \n",
"active high 85.338947 342 2 5.696842 \n",
"active low 98.091000 342 2 5.633677 \n",
"new warm high 477.622047 721 1 1.047244 \n",
"new warm low 499.544969 722 1 1.058771 \n",
"warm high 461.198198 712 2 4.414414 \n",
"warm low 470.661088 720 2 4.361925 \n",
"cold 866.616814 1086 1 2.254064 \n",
"inactive 2058.441294 3649 1 1.730964 \n",
"\n",
" frequency_max monetary_min monetary monetary_max \\\n",
"segment \n",
"new active high 4 100.0 284.673498 4500.00 \n",
"new active low 10 5.0 34.364938 90.00 \n",
"active high 17 100.0 261.902155 4000.00 \n",
"active low 40 5.0 40.459174 98.75 \n",
"new warm high 2 100.0 212.845669 2000.00 \n",
"new warm low 5 5.0 33.106115 99.00 \n",
"warm high 14 100.0 187.849110 1250.00 \n",
"warm low 20 5.0 37.382060 98.00 \n",
"cold 23 5.0 51.114605 3043.75 \n",
"inactive 19 5.0 48.111199 3000.00 \n",
"\n",
" first_purchase first_purchase_date first_purchase_year \\\n",
"segment \n",
"new active high 342 2014-01-24 2014 \n",
"new active low 362 2014-01-04 2014 \n",
"active high 3645 2005-01-08 2005 \n",
"active low 3647 2005-01-06 2005 \n",
"new warm high 721 2013-01-10 2013 \n",
"new warm low 722 2013-01-09 2013 \n",
"warm high 3635 2005-01-18 2005 \n",
"warm low 3651 2005-01-02 2005 \n",
"cold 3651 2005-01-02 2005 \n",
"inactive 3651 2005-01-02 2005 \n",
"\n",
" last_purchase_date last_purchase_year \n",
"segment \n",
"new active high 2014-12-31 2014 \n",
"new active low 2014-12-31 2014 \n",
"active high 2014-12-31 2014 \n",
"active low 2014-12-31 2014 \n",
"new warm high 2013-12-31 2013 \n",
"new warm low 2013-12-31 2013 \n",
"warm high 2013-12-31 2013 \n",
"warm low 2013-12-31 2013 \n",
"cold 2012-12-31 2012 \n",
"inactive 2011-12-31 2011 "
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cons_df = get_consumer_df(df, offset=1*365);cons_df\n",
"get_segment_summary(cons_df)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "5873744a-c136-4ef5-906d-d139e4f68f62",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"inactive 7512\n",
"active low 3011\n",
"cold 2153\n",
"new active low 1234\n",
"new warm low 1123\n",
"warm low 956\n",
"active high 475\n",
"new active high 203\n",
"new warm high 127\n",
"warm high 111\n",
"Name: segment, dtype: int64"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cons_df['segment'].value_counts(dropna=False)"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "07f1fefe-7757-412c-b36c-455d765741f1",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"16905"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cons_df['segment'].value_counts(dropna=False).sum() #+1512"
]
},
{
"cell_type": "markdown",
"id": "0b5439b5-ee5e-4436-a016-0cfee2c19182",
"metadata": {},
"source": [
"## Revenue Generation\n",
"How much revenue is generated in 2015?"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "2278217c-9a8f-41e1-bab2-aa76d0cff5e9",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" consumer_id | \n",
" purchase_amount | \n",
" date_of_purchase | \n",
" year_of_purchase | \n",
" days_since | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 760 | \n",
" 25.0 | \n",
" 2009-11-06 | \n",
" 2009 | \n",
" 2247 | \n",
"
\n",
" \n",
" 1 | \n",
" 860 | \n",
" 50.0 | \n",
" 2012-09-28 | \n",
" 2012 | \n",
" 1190 | \n",
"
\n",
" \n",
" 2 | \n",
" 1200 | \n",
" 100.0 | \n",
" 2005-10-25 | \n",
" 2005 | \n",
" 3720 | \n",
"
\n",
" \n",
" 3 | \n",
" 1420 | \n",
" 50.0 | \n",
" 2009-07-09 | \n",
" 2009 | \n",
" 2367 | \n",
"
\n",
" \n",
" 4 | \n",
" 1940 | \n",
" 70.0 | \n",
" 2013-01-25 | \n",
" 2013 | \n",
" 1071 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" consumer_id purchase_amount date_of_purchase year_of_purchase days_since\n",
"0 760 25.0 2009-11-06 2009 2247\n",
"1 860 50.0 2012-09-28 2012 1190\n",
"2 1200 100.0 2005-10-25 2005 3720\n",
"3 1420 50.0 2009-07-09 2009 2367\n",
"4 1940 70.0 2013-01-25 2013 1071"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "aadf0608-1db5-40e5-9297-1ae25c88511a",
"metadata": {},
"outputs": [],
"source": [
"revenue_df = df[df['days_since'] <= 365].groupby('consumer_id').sum()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "a845a539-ad53-423e-bdd5-fa4c60b14d51",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(5398, 3)"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"revenue_df.shape"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "bd56490e-ea73-4be2-a469-0e7ffa86947a",
"metadata": {},
"outputs": [],
"source": [
"revenue_df = df[df['year_of_purchase']==2015].groupby('consumer_id').agg(revenue=('purchase_amount', 'sum'))"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "b487edf2-08ab-4c37-b5e8-a97e0d547f2f",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(5398, 1)"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"revenue_df.shape"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "89fe5168-bad9-4093-962c-67fc83656d17",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" revenue | \n",
"
\n",
" \n",
" consumer_id | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" 80 | \n",
" 80.0 | \n",
"
\n",
" \n",
" 480 | \n",
" 45.0 | \n",
"
\n",
" \n",
" 830 | \n",
" 50.0 | \n",
"
\n",
" \n",
" 850 | \n",
" 60.0 | \n",
"
\n",
" \n",
" 860 | \n",
" 60.0 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" revenue\n",
"consumer_id \n",
"80 80.0\n",
"480 45.0\n",
"830 50.0\n",
"850 60.0\n",
"860 60.0"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"revenue_df.head()"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "1a30f510-da83-424e-b07e-bd8877f9d2ce",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(18417, 10)"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cons_df= get_consumer_df(df, offset=0); cons_df.shape"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "17ac22be-ee60-4b59-b11c-22f574b50df7",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" size | \n",
" revenue_today | \n",
" avg_revenue_today | \n",
" revenue_till_date | \n",
" avg_revenue_till_date | \n",
" recency_min | \n",
" recency | \n",
" recency_max | \n",
" frequency_min | \n",
" frequency | \n",
" frequency_max | \n",
" monetary_min | \n",
" monetary | \n",
" monetary_max | \n",
" first_purchase | \n",
" first_purchase_date | \n",
" first_purchase_year | \n",
" last_purchase_date | \n",
" last_purchase_year | \n",
"
\n",
" \n",
" segment | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" new active high | \n",
" 263 | \n",
" 75629.21 | \n",
" 287.563536 | \n",
" 75629.21 | \n",
" 287.563536 | \n",
" 1 | \n",
" 82.372624 | \n",
" 360 | \n",
" 1 | \n",
" 1.015209 | \n",
" 2 | \n",
" 100.0 | \n",
" 283.381027 | \n",
" 4500.000000 | \n",
" 360 | \n",
" 2015-01-06 | \n",
" 2015 | \n",
" 2015-12-31 | \n",
" 2015 | \n",
"
\n",
" \n",
" new active low | \n",
" 1249 | \n",
" 44070.00 | \n",
" 35.284227 | \n",
" 44070.00 | \n",
" 35.284227 | \n",
" 1 | \n",
" 85.542034 | \n",
" 359 | \n",
" 1 | \n",
" 1.052042 | \n",
" 11 | \n",
" 5.0 | \n",
" 33.704697 | \n",
" 90.000000 | \n",
" 359 | \n",
" 2015-01-07 | \n",
" 2015 | \n",
" 2015-12-31 | \n",
" 2015 | \n",
"
\n",
" \n",
" active high | \n",
" 573 | \n",
" 185405.00 | \n",
" 323.568935 | \n",
" 788194.95 | \n",
" 1375.558377 | \n",
" 1 | \n",
" 88.820244 | \n",
" 344 | \n",
" 2 | \n",
" 5.888307 | \n",
" 19 | \n",
" 100.0 | \n",
" 240.045740 | \n",
" 4500.000000 | \n",
" 4010 | \n",
" 2005-01-08 | \n",
" 2005 | \n",
" 2015-12-31 | \n",
" 2015 | \n",
"
\n",
" \n",
" active low | \n",
" 3313 | \n",
" 173289.92 | \n",
" 52.306043 | \n",
" 810481.97 | \n",
" 244.636876 | \n",
" 1 | \n",
" 108.361002 | \n",
" 359 | \n",
" 2 | \n",
" 5.935406 | \n",
" 45 | \n",
" 5.0 | \n",
" 40.724525 | \n",
" 99.000000 | \n",
" 4012 | \n",
" 2005-01-06 | \n",
" 2005 | \n",
" 2015-12-31 | \n",
" 2015 | \n",
"
\n",
" \n",
" new warm high | \n",
" 116 | \n",
" 0.00 | \n",
" 0.000000 | \n",
" 36166.22 | \n",
" 311.777759 | \n",
" 366 | \n",
" 476.060345 | \n",
" 701 | \n",
" 1 | \n",
" 1.060345 | \n",
" 4 | \n",
" 100.0 | \n",
" 302.036379 | \n",
" 4000.000000 | \n",
" 707 | \n",
" 2014-01-24 | \n",
" 2014 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" new warm low | \n",
" 822 | \n",
" 0.00 | \n",
" 0.000000 | \n",
" 28854.50 | \n",
" 35.102798 | \n",
" 366 | \n",
" 513.996350 | \n",
" 720 | \n",
" 1 | \n",
" 1.042579 | \n",
" 3 | \n",
" 5.0 | \n",
" 33.374290 | \n",
" 90.000000 | \n",
" 720 | \n",
" 2014-01-11 | \n",
" 2014 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" warm high | \n",
" 119 | \n",
" 0.00 | \n",
" 0.000000 | \n",
" 168498.98 | \n",
" 1415.957815 | \n",
" 366 | \n",
" 455.126050 | \n",
" 665 | \n",
" 2 | \n",
" 4.714286 | \n",
" 14 | \n",
" 100.0 | \n",
" 327.407457 | \n",
" 4000.000000 | \n",
" 4004 | \n",
" 2005-01-14 | \n",
" 2005 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" warm low | \n",
" 901 | \n",
" 0.00 | \n",
" 0.000000 | \n",
" 159740.22 | \n",
" 177.292142 | \n",
" 366 | \n",
" 474.377358 | \n",
" 707 | \n",
" 2 | \n",
" 4.531632 | \n",
" 20 | \n",
" 5.0 | \n",
" 38.591926 | \n",
" 96.428571 | \n",
" 4011 | \n",
" 2005-01-07 | \n",
" 2005 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" cold | \n",
" 1903 | \n",
" 0.00 | \n",
" 0.000000 | \n",
" 232402.29 | \n",
" 122.124167 | \n",
" 731 | \n",
" 857.781398 | \n",
" 1087 | \n",
" 1 | \n",
" 2.303205 | \n",
" 20 | \n",
" 5.0 | \n",
" 51.739893 | \n",
" 2000.000000 | \n",
" 4016 | \n",
" 2005-01-02 | \n",
" 2005 | \n",
" 2013-12-31 | \n",
" 2013 | \n",
"
\n",
" \n",
" inactive | \n",
" 9158 | \n",
" 0.00 | \n",
" 0.000000 | \n",
" 850306.56 | \n",
" 92.848500 | \n",
" 1096 | \n",
" 2178.110832 | \n",
" 4014 | \n",
" 1 | \n",
" 1.814479 | \n",
" 23 | \n",
" 5.0 | \n",
" 48.112771 | \n",
" 3043.750000 | \n",
" 4016 | \n",
" 2005-01-02 | \n",
" 2005 | \n",
" 2012-12-31 | \n",
" 2012 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" size revenue_today avg_revenue_today revenue_till_date \\\n",
"segment \n",
"new active high 263 75629.21 287.563536 75629.21 \n",
"new active low 1249 44070.00 35.284227 44070.00 \n",
"active high 573 185405.00 323.568935 788194.95 \n",
"active low 3313 173289.92 52.306043 810481.97 \n",
"new warm high 116 0.00 0.000000 36166.22 \n",
"new warm low 822 0.00 0.000000 28854.50 \n",
"warm high 119 0.00 0.000000 168498.98 \n",
"warm low 901 0.00 0.000000 159740.22 \n",
"cold 1903 0.00 0.000000 232402.29 \n",
"inactive 9158 0.00 0.000000 850306.56 \n",
"\n",
" avg_revenue_till_date recency_min recency recency_max \\\n",
"segment \n",
"new active high 287.563536 1 82.372624 360 \n",
"new active low 35.284227 1 85.542034 359 \n",
"active high 1375.558377 1 88.820244 344 \n",
"active low 244.636876 1 108.361002 359 \n",
"new warm high 311.777759 366 476.060345 701 \n",
"new warm low 35.102798 366 513.996350 720 \n",
"warm high 1415.957815 366 455.126050 665 \n",
"warm low 177.292142 366 474.377358 707 \n",
"cold 122.124167 731 857.781398 1087 \n",
"inactive 92.848500 1096 2178.110832 4014 \n",
"\n",
" frequency_min frequency frequency_max monetary_min \\\n",
"segment \n",
"new active high 1 1.015209 2 100.0 \n",
"new active low 1 1.052042 11 5.0 \n",
"active high 2 5.888307 19 100.0 \n",
"active low 2 5.935406 45 5.0 \n",
"new warm high 1 1.060345 4 100.0 \n",
"new warm low 1 1.042579 3 5.0 \n",
"warm high 2 4.714286 14 100.0 \n",
"warm low 2 4.531632 20 5.0 \n",
"cold 1 2.303205 20 5.0 \n",
"inactive 1 1.814479 23 5.0 \n",
"\n",
" monetary monetary_max first_purchase first_purchase_date \\\n",
"segment \n",
"new active high 283.381027 4500.000000 360 2015-01-06 \n",
"new active low 33.704697 90.000000 359 2015-01-07 \n",
"active high 240.045740 4500.000000 4010 2005-01-08 \n",
"active low 40.724525 99.000000 4012 2005-01-06 \n",
"new warm high 302.036379 4000.000000 707 2014-01-24 \n",
"new warm low 33.374290 90.000000 720 2014-01-11 \n",
"warm high 327.407457 4000.000000 4004 2005-01-14 \n",
"warm low 38.591926 96.428571 4011 2005-01-07 \n",
"cold 51.739893 2000.000000 4016 2005-01-02 \n",
"inactive 48.112771 3043.750000 4016 2005-01-02 \n",
"\n",
" first_purchase_year last_purchase_date last_purchase_year \n",
"segment \n",
"new active high 2015 2015-12-31 2015 \n",
"new active low 2015 2015-12-31 2015 \n",
"active high 2005 2015-12-31 2015 \n",
"active low 2005 2015-12-31 2015 \n",
"new warm high 2014 2014-12-31 2014 \n",
"new warm low 2014 2014-12-31 2014 \n",
"warm high 2005 2014-12-31 2014 \n",
"warm low 2005 2014-12-31 2014 \n",
"cold 2005 2013-12-31 2013 \n",
"inactive 2005 2012-12-31 2012 "
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"get_segment_summary(pd.merge(cons_df, revenue_df, left_index=True,right_index=True, how='left').fillna(0))"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "fcd93797-8dc6-4789-b716-98a494a94d8e",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"(16905, 10)"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"cons_df_2014 = get_consumer_df(df, offset=365); cons_df_2014.shape"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "995cb08e-2cb7-4af8-9f28-dce56b9e3dd7",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" size | \n",
" revenue_today | \n",
" avg_revenue_today | \n",
" revenue_till_date | \n",
" avg_revenue_till_date | \n",
" recency_min | \n",
" recency | \n",
" recency_max | \n",
" frequency_min | \n",
" frequency | \n",
" frequency_max | \n",
" monetary_min | \n",
" monetary | \n",
" monetary_max | \n",
" first_purchase | \n",
" first_purchase_date | \n",
" first_purchase_year | \n",
" last_purchase_date | \n",
" last_purchase_year | \n",
"
\n",
" \n",
" segment | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
" | \n",
"
\n",
" \n",
" \n",
" \n",
" new active high | \n",
" 203 | \n",
" 22275.00 | \n",
" 109.729064 | \n",
" 59506.22 | \n",
" 293.134089 | \n",
" 1 | \n",
" 94.615764 | \n",
" 336 | \n",
" 1 | \n",
" 1.049261 | \n",
" 4 | \n",
" 100.0 | \n",
" 284.673498 | \n",
" 4500.00 | \n",
" 342 | \n",
" 2014-01-24 | \n",
" 2014 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" new active low | \n",
" 1234 | \n",
" 22339.00 | \n",
" 18.102917 | \n",
" 45775.50 | \n",
" 37.095219 | \n",
" 1 | \n",
" 138.251216 | \n",
" 355 | \n",
" 1 | \n",
" 1.074554 | \n",
" 10 | \n",
" 5.0 | \n",
" 34.364938 | \n",
" 90.00 | \n",
" 362 | \n",
" 2014-01-04 | \n",
" 2014 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" active high | \n",
" 475 | \n",
" 120687.00 | \n",
" 254.077895 | \n",
" 665593.93 | \n",
" 1401.250379 | \n",
" 1 | \n",
" 85.338947 | \n",
" 342 | \n",
" 2 | \n",
" 5.696842 | \n",
" 17 | \n",
" 100.0 | \n",
" 261.902155 | \n",
" 4000.00 | \n",
" 3645 | \n",
" 2005-01-08 | \n",
" 2005 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" active low | \n",
" 3011 | \n",
" 126150.53 | \n",
" 41.896556 | \n",
" 699743.77 | \n",
" 232.395805 | \n",
" 1 | \n",
" 98.091000 | \n",
" 342 | \n",
" 2 | \n",
" 5.633677 | \n",
" 40 | \n",
" 5.0 | \n",
" 40.459174 | \n",
" 98.75 | \n",
" 3647 | \n",
" 2005-01-06 | \n",
" 2005 | \n",
" 2014-12-31 | \n",
" 2014 | \n",
"
\n",
" \n",
" new warm high | \n",
" 127 | \n",
" 1925.00 | \n",
" 15.157480 | \n",
" 29418.34 | \n",
" 231.640472 | \n",
" 366 | \n",
" 477.622047 | \n",
" 721 | \n",
" 1 | \n",
" 1.047244 | \n",
" 2 | \n",
" 100.0 | \n",
" 212.845669 | \n",
" 2000.00 | \n",
" 721 | \n",
" 2013-01-10 | \n",
" 2013 | \n",
" 2013-12-31 | \n",
" 2013 | \n",
"
\n",
" \n",
" new warm low | \n",
" 1123 | \n",
" 4405.00 | \n",
" 3.922529 | \n",
" 39498.00 | \n",
" 35.171861 | \n",
" 366 | \n",
" 499.544969 | \n",
" 722 | \n",
" 1 | \n",
" 1.058771 | \n",
" 5 | \n",
" 5.0 | \n",
" 33.106115 | \n",
" 99.00 | \n",
" 722 | \n",
" 2013-01-09 | \n",
" 2013 | \n",
" 2013-12-31 | \n",
" 2013 | \n",
"
\n",
" \n",
" warm high | \n",
" 111 | \n",
" 12705.00 | \n",
" 114.459459 | \n",
" 92948.45 | \n",
" 837.373423 | \n",
" 366 | \n",
" 461.198198 | \n",
" 712 | \n",
" 2 | \n",
" 4.414414 | \n",
" 14 | \n",
" 100.0 | \n",
" 187.849110 | \n",
" 1250.00 | \n",
" 3635 | \n",
" 2005-01-18 | \n",
" 2005 | \n",
" 2013-12-31 | \n",
" 2013 | \n",
"
\n",
" \n",
" warm low | \n",
" 956 | \n",
" 12901.00 | \n",
" 13.494770 | \n",
" 157955.50 | \n",
" 165.225418 | \n",
" 366 | \n",
" 470.661088 | \n",
" 720 | \n",
" 2 | \n",
" 4.361925 | \n",
" 20 | \n",
" 5.0 | \n",
" 37.382060 | \n",
" 98.00 | \n",
" 3651 | \n",
" 2005-01-02 | \n",
" 2005 | \n",
" 2013-12-31 | \n",
" 2013 | \n",
"
\n",
" \n",
" cold | \n",
" 2153 | \n",
" 13151.00 | \n",
" 6.108221 | \n",
" 273645.09 | \n",
" 127.099438 | \n",
" 731 | \n",
" 866.616814 | \n",
" 1086 | \n",
" 1 | \n",
" 2.254064 | \n",
" 23 | \n",
" 5.0 | \n",
" 51.114605 | \n",
" 3043.75 | \n",
" 3651 | \n",
" 2005-01-02 | \n",
" 2005 | \n",
" 2012-12-31 | \n",
" 2012 | \n",
"
\n",
" \n",
" inactive | \n",
" 7512 | \n",
" 22156.39 | \n",
" 2.949466 | \n",
" 651865.97 | \n",
" 86.776620 | \n",
" 1097 | \n",
" 2058.441294 | \n",
" 3649 | \n",
" 1 | \n",
" 1.730964 | \n",
" 19 | \n",
" 5.0 | \n",
" 48.111199 | \n",
" 3000.00 | \n",
" 3651 | \n",
" 2005-01-02 | \n",
" 2005 | \n",
" 2011-12-31 | \n",
" 2011 | \n",
"
\n",
" \n",
"
\n",
"
"
],
"text/plain": [
" size revenue_today avg_revenue_today revenue_till_date \\\n",
"segment \n",
"new active high 203 22275.00 109.729064 59506.22 \n",
"new active low 1234 22339.00 18.102917 45775.50 \n",
"active high 475 120687.00 254.077895 665593.93 \n",
"active low 3011 126150.53 41.896556 699743.77 \n",
"new warm high 127 1925.00 15.157480 29418.34 \n",
"new warm low 1123 4405.00 3.922529 39498.00 \n",
"warm high 111 12705.00 114.459459 92948.45 \n",
"warm low 956 12901.00 13.494770 157955.50 \n",
"cold 2153 13151.00 6.108221 273645.09 \n",
"inactive 7512 22156.39 2.949466 651865.97 \n",
"\n",
" avg_revenue_till_date recency_min recency recency_max \\\n",
"segment \n",
"new active high 293.134089 1 94.615764 336 \n",
"new active low 37.095219 1 138.251216 355 \n",
"active high 1401.250379 1 85.338947 342 \n",
"active low 232.395805 1 98.091000 342 \n",
"new warm high 231.640472 366 477.622047 721 \n",
"new warm low 35.171861 366 499.544969 722 \n",
"warm high 837.373423 366 461.198198 712 \n",
"warm low 165.225418 366 470.661088 720 \n",
"cold 127.099438 731 866.616814 1086 \n",
"inactive 86.776620 1097 2058.441294 3649 \n",
"\n",
" frequency_min frequency frequency_max monetary_min \\\n",
"segment \n",
"new active high 1 1.049261 4 100.0 \n",
"new active low 1 1.074554 10 5.0 \n",
"active high 2 5.696842 17 100.0 \n",
"active low 2 5.633677 40 5.0 \n",
"new warm high 1 1.047244 2 100.0 \n",
"new warm low 1 1.058771 5 5.0 \n",
"warm high 2 4.414414 14 100.0 \n",
"warm low 2 4.361925 20 5.0 \n",
"cold 1 2.254064 23 5.0 \n",
"inactive 1 1.730964 19 5.0 \n",
"\n",
" monetary monetary_max first_purchase first_purchase_date \\\n",
"segment \n",
"new active high 284.673498 4500.00 342 2014-01-24 \n",
"new active low 34.364938 90.00 362 2014-01-04 \n",
"active high 261.902155 4000.00 3645 2005-01-08 \n",
"active low 40.459174 98.75 3647 2005-01-06 \n",
"new warm high 212.845669 2000.00 721 2013-01-10 \n",
"new warm low 33.106115 99.00 722 2013-01-09 \n",
"warm high 187.849110 1250.00 3635 2005-01-18 \n",
"warm low 37.382060 98.00 3651 2005-01-02 \n",
"cold 51.114605 3043.75 3651 2005-01-02 \n",
"inactive 48.111199 3000.00 3651 2005-01-02 \n",
"\n",
" first_purchase_year last_purchase_date last_purchase_year \n",
"segment \n",
"new active high 2014 2014-12-31 2014 \n",
"new active low 2014 2014-12-31 2014 \n",
"active high 2005 2014-12-31 2014 \n",
"active low 2005 2014-12-31 2014 \n",
"new warm high 2013 2013-12-31 2013 \n",
"new warm low 2013 2013-12-31 2013 \n",
"warm high 2005 2013-12-31 2013 \n",
"warm low 2005 2013-12-31 2013 \n",
"cold 2005 2012-12-31 2012 \n",
"inactive 2005 2011-12-31 2011 "
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"get_segment_summary(pd.merge(cons_df_2014, revenue_df, left_index=True,right_index=True, how='left').fillna(0))"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "1d2e2755-a8f4-4c82-aea9-af8e3fa9fbaa",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
""
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
},
{
"data": {
"image/png": "iVBORw0KGgoAAAANSUhEUgAAAXcAAAFMCAYAAAAwffyQAAAAOXRFWHRTb2Z0d2FyZQBNYXRwbG90bGliIHZlcnNpb24zLjUuMCwgaHR0cHM6Ly9tYXRwbG90bGliLm9yZy8/fFQqAAAACXBIWXMAAAsTAAALEwEAmpwYAAAkZ0lEQVR4nO3deZxkZXn28d/FIhoWwTAisg0iUREVdBAUcInJGxYjJiKBiAIhQt6AotHkRRODGwb3KFEiKoorYtCIQhIRERhlG2DYISJLgCAMyqa4Adf7x/O0Xd10T89Md51z6vT1/Xz601Wnqvrc01N913me+1lkm4iI6JfV2g4gIiLmXpJ7REQPJblHRPRQkntERA8luUdE9FCSe0RED63RdgAAG264oRcuXNh2GBERI+Xiiy++y/aCqR7rRHJfuHAhS5YsaTuMiIiRIunm6R5Lt0xERA8luUdE9FCSe0REDyW5R0T0UJJ7REQPJblHRPRQkntERA8luUdE9NCMk5gkbQZ8DtgIMHC87Y9IejvwWmBZfepbbZ9eX/MW4GDgIeD1tv9rtoEuPPK0Wb3+pmP2nG0IEREjY0VmqD4IvMn2JZLWBS6WdEZ97MO2PzD4ZEnbAPsCTweeCHxH0u/ZfmguA4+IiOnN2C1j+3bbl9Tb9wPXAJss5yV7ASfZ/pXtG4HrgefORbAREbFiVqrPXdJCYHvggnrocEmXSzpB0gb12CbALQMvu5XlfxhERMQcW+HkLmkd4BTgDbbvA44DtgK2A24HPrgyJ5Z0iKQlkpYsW7Zs5hdERMQKW6HkLmlNSmL/ou2vAdi+w/ZDth8GPsl418ttwGYDL9+0HpvA9vG2F9letGDBlCtWRkTEKpoxuUsS8GngGtsfGji+8cDT/gS4st4+FdhX0lqStgS2Bi6cu5AjImImKzJaZmfg1cAVkpbWY28F9pO0HWV45E3AoQC2r5J0MnA1ZaTNYRkpExHRrBmTu+3FgKZ46PTlvOZo4OhZxBUREbOQGaoRET2U5B4R0UNJ7hERPZTkHhHRQ0nuERE9lOQeEdFDSe4RET2U5B4R0UNJ7hERPZTkHhHRQ0nuERE9lOQeEdFDSe4RET2U5B4R0UNJ7hERPZTkHhHRQ0nuERE9lOQeEdFDSe4RET2U5B4R0UNJ7hERPZTkHhHRQ0nuERE9lOQeEdFDSe4RET2U5B4R0UNJ7hERPZTkHhHRQ0nuERE9lOQeEdFDSe4RET2U5B4R0UMzJndJm0k6S9LVkq6SdEQ9/jhJZ0j6Yf2+QT0uSR+VdL2kyyU9e9j/iIiImGhFrtwfBN5kextgJ+AwSdsARwJn2t4aOLPeB9gd2Lp+HQIcN+dRR0TEcs2Y3G3fbvuSevt+4BpgE2Av4MT6tBOBl9fbewGfc3E+sL6kjec68IiImN5K9blLWghsD1wAbGT79vrQj4GN6u1NgFsGXnZrPRYREQ1Z4eQuaR3gFOANtu8bfMy2Aa/MiSUdImmJpCXLli1bmZdGRMQMVii5S1qTkti/aPtr9fAdY90t9fud9fhtwGYDL9+0HpvA9vG2F9letGDBglWNPyIiprAio2UEfBq4xvaHBh46FTig3j4A+MbA8dfUUTM7AfcOdN9EREQD1liB5+wMvBq4QtLSeuytwDHAyZIOBm4G9qmPnQ7sAVwPPAAcNJcBR0TEzGZM7rYXA5rm4ZdM8XwDh80yroiImIXMUI2I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEZk7ukEyTdKenKgWNvl3SbpKX1a4+Bx94i6XpJ10n6o2EFHhER01uRK/fPArtNcfzDtrerX6cDSNoG2Bd4en3NxyWtPlfBRkTEipkxuds+B/jpCv68vYCTbP/K9o3A9cBzZxFfRESsgtn0uR8u6fLabbNBPbYJcMvAc26txyIiokGrmtyPA7YCtgNuBz64sj9A0iGSlkhasmzZslUMIyIiprJKyd32HbYfsv0w8EnGu15uAzYbeOqm9dhUP+N424tsL1qwYMGqhBEREdNYpeQuaeOBu38CjI2kORXYV9JakrYEtgYunF2IERGxstaY6QmSvgy8CNhQ0q3AUcCLJG0HGLgJOBTA9lWSTgauBh4EDrP90FAij4iIac2Y3G3vN8XhTy/n+UcDR88mqIiImJ3MUI2I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5ao+0ARsnCI0+b9c+46Zg95yCSiIjly5V7REQPJblHRPRQkntERA+lz30EzbbvP/3+Ef2XK/eIiB5Kco+I6KEk94iIHpoxuUs6QdKdkq4cOPY4SWdI+mH9vkE9LkkflXS9pMslPXuYwUdExNRW5Mr9s8Buk44dCZxpe2vgzHofYHdg6/p1CHDc3IQZERErY8bkbvsc4KeTDu8FnFhvnwi8fOD451ycD6wvaeM5ijUiIlbQqva5b2T79nr7x8BG9fYmwC0Dz7u1HouIiAbNuqBq24BX9nWSDpG0RNKSZcuWzTaMiIgYsKrJ/Y6x7pb6/c56/DZgs4HnbVqPPYLt420vsr1owYIFqxhGRERMZVWT+6nAAfX2AcA3Bo6/po6a2Qm4d6D7JiIiGjLj8gOSvgy8CNhQ0q3AUcAxwMmSDgZuBvapTz8d2AO4HngAOGgIMUdExAxmTO6295vmoZdM8VwDh802qIiImJ3MUI2I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHlpjNi+WdBNwP/AQ8KDtRZIeB3wFWAjcBOxj++7ZhRkREStjLq7cX2x7O9uL6v0jgTNtbw2cWe9HRESDhtEtsxdwYr19IvDyIZwjIiKWY7bJ3cC3JV0s6ZB6bCPbt9fbPwY2muU5IiJiJc2qzx3YxfZtkh4PnCHp2sEHbVuSp3ph/TA4BGDzzTefZRgRETFoVlfutm+r3+8Evg48F7hD0sYA9fud07z2eNuLbC9asGDBbMKIiIhJVjm5S1pb0rpjt4H/A1wJnAocUJ92APCN2QYZERErZzbdMhsBX5c09nO+ZPs/JV0EnCzpYOBmYJ/ZhxkREStjlZO77RuAZ01x/CfAS2YTVEREzE5mqEZE9NBsR8vEPLXwyNNm/TNuOmbPOYgkIqaSK/eIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOihJPeIiB7KHqoxsrKPa8T0cuUeEdFDuXKPmKW0IKKLcuUeEdFDuXKP6IG0HmKyXLlHRPRQkntERA8luUdE9FCSe0REDyW5R0T0UEbLRMScyaid7hhacpe0G/ARYHXgU7aPGda5IiLG5AOmGEpyl7Q68DHgD4FbgYsknWr76mGcLyKia9r+kBlWn/tzgett32D718BJwF5DOldEREwyrOS+CXDLwP1b67GIiGiAbM/9D5X2Bnaz/Zf1/quBHW0fPvCcQ4BD6t2nANfN8rQbAnfN8mfMVhdigG7E0YUYoBtxdCEG6EYcXYgBuhHHXMSwhe0FUz0wrILqbcBmA/c3rcd+y/bxwPFzdUJJS2wvmqufN6oxdCWOLsTQlTi6EENX4uhCDF2JY9gxDKtb5iJga0lbSnoUsC9w6pDOFRERkwzlyt32g5IOB/6LMhTyBNtXDeNcERHxSEMb5277dOD0Yf38KcxZF88sdCEG6EYcXYgBuhFHF2KAbsTRhRigG3EMNYahFFQjIqJdWVsmIqKHktxjTkh6dNsxRExH0kskPabtOJo0st0ykhYArwUWMlA7sP0XDcexOrDRpBj+p8kYahzP55G/i881eP7rgTuAc+vXYtv3NnX+gTgWA2fXGL5v+/4WYtgZeDuwBeX/Q4BtP6nhON4FnAP8wPbPmzx310g6EXge8FPKe+Mcynv07pbi+R3bDwz1HCOc3H9A+U+6GHho7LjtUxqM4XXAUZSk9vB4CH5mUzHUOD4PbAUsZfx3YduvbziOzYFdgZ2BPYB7bG/XcAxb1hh2BXYCfgWca/uNDcZwLfBGHvne/ElTMdQ4DqL8Hp4H3E9Nara/0dD5rwCmTTBN/50ASHoisDfwZuCJthtdGbdehH0KWMf25pKeBRxq+6/n/FwjnNyXNp04pojhesrM20b/aKeI4xpgG7f4nylpU0oieSHwLMoV0mLb/9RCLBvXOHYFXgz8j+3dGjz/BbZ3bOp8M5H0BGAfSkLbwPa6DZ13i3rzsPr98/X7qwBsH9lEHDWW/Snvh2dQZoUupnzon9dUDDWOCygfLqfa3r4eu9L2tnN+rhFO7u+mNDebHG45OYazgD+0/WBbMdQ4vgq83vbtLcbwMGXy2nuaujKcJo4fUf54v0S5Ul1q++Hlv2rOzv3senMfyvyOr1FaDgDYvqSJOAbi+RSwDePdZYuBS5p+v0q6dCyRDRy7xPazp3vNEGK4C/gR8K/AWbZvaurck+K4wPaOg78TSZfZftZcn2vkNuuQdD+lqSfgrZJ+BfyG8X7N9RqI4W/qzRuA70k6jYl/xB8adgw1jm9SfhfrAldLunBSHC9rIo5qe2AX4M8lHQn8EDjb9qcbjAHgozWO/WpMZ0s6x/aPGjj3ByfdH5xabuD3G4hh0O9SPmTuobSk7mrpQkSSdrb9/Xrn+TQ8mMP2hpKeDrwAOFrS1sB1tl/dZBzALfXfb0lrAkcA1wzjRCN75d4mSUct73Hb72gojhfOEMfZTcQxRtI6lMS6K7B/jWGL5b5ouLEcROmK2NT26m3E0QWSngb8EaUOsLrtTRs+/3OAE4DHUi7C7gb+osmWjKT1KLWgse66DYHzbR/QVAw1jg0pmxj9AeV38W3giGF07Y5sch9oAg+6F7i57W6S+UjSEmAtYKzQfa7tm1uI44OUD5h1gPMGYrmhwRj+ZorD9wIX217aYBwvpSSyFwDrA+dTfhcnNBXDpHgeC9DSKKrLKd1SiylF5VubjqHGscD2skbONcLJ/Xzg2cAV9dAzgCspVwf/1/a3G4hhrFtk0L3AEuATtn857BhqHGNdVVPF8aYmEluTb9oZ4tibksDuaDGGL1G6ZL5ZD70UuJwyVPWrtt/XUBz/wviH2/82cc5J55/qQ+63muq+HFRbdNj+WdPnruf/b+Am4CvAKbbvGdq5Rji5fw1429iCZJK2Ad4J/B3wtSZG0kj6CLAA+HI99GfAfZREu15T/Xl1PPOtlCKiKKtwbgVcQvmge1EDMTyWMiz0BfXQ2cA7W7pKe9lgHLa/ubznD+H85wB7jCWQmlBOA3ajXL1v02AsGwE71LsX2r6zwXN3ovuyxrItZbTO4yh/I8uAA2xf2VQMA7E8l/I3+nLgauAk21+Y8xPZHskv4MrpjlFGSDQRw0XTHQOuavB3cdkUx5ZO99iQYjgFeAfwpPp1FOVDtun3xT8BZwJ/Ub/OoIzgaTKGa4E1B+6vBVxbb1/aYByvBG4GTgQ+B9wI7N30/0kXvijdhS8euP8iymi7NmPasP6/PDSMnz9yo2UGXCXpOMr+rFCumq+WtBZl9EwT1pG0ueuM1DqJZ5362K8bigHgAUn7AP9W7+8NjHUJNdU028r2Kwbuv0PS0obOPWhPYDvX4Y91ZuKlwFsbjOGLwAWSxoaE/jHwJUlrU67UmvIPwA6uV+t1Vvd3GH+fNKLOgTiWUtCE0lV0hJvt917b9lljd2x/r/5/NKoWdv+E8db11yl7Ts+5UU7uBwJ/Dbyh3v8+ZWTEbygTV5rwJmBxHVstYEvgr+ub5sSGYoAyKeQjwMcpyfx8YP+6lsbhy3vhHPqFpF1sL4bfTsH/RUPnnmx9ytA/KDWYRtl+l6T/YDyZ/ZXtJfX2qxoMZTVP7Ib5Ce2sJ/UZSpfhK+v9/euxP2wwhhskvY3xiVT7U4YyN+0y4N8pXZZDnUA1sn3uXVFbCk+td69zQ0XUrpG0HeUDbWy420+BA21f1nAc+wHHAGfVOF4AHGn7Kw2cez3b90l63FSP2/7pVMeHGM/7gWcysSZ0ue3/13AcSz2pBtb0DHNJG1C6DXeph84F3u6G15aRJDeUdEcuuUs62fY+061b4QbWq5D0+7a/K+lPp3rc9teGHUON4+9sv0/SsUz9u2h0bZka03r13Pc1fe6BGDZmYhHxxw2d91u2XyrpRib+f7SycFiN6RUMdIfY/noLMZxJuVIf+5DZDzjI9kuajqUtkv7Z9humGWGHhzDhcBST+8a2bx9Yt2ICNzC2WtI7bB8l6TNTh9DMypSS/tj2NyVNORHD9tC7hroy3G2aeQ+DcTQ69T/G1b/VYykLmJlS3Hyd7VsaOPeUyXTMMJLqNHE8x/bF00089BAmHI5cco9u6cpwt7rOz3LCcKNT/yVtwviSv2NBnNPQuaea9wANLtExKZ4TgTeMdYHUbqsPNHER1MFZ3EfY/shMx+bkXKOa3GuXyHuBx1PetI2/cWt/+yt45Drq72wqhhrH71GKyZPjaHotkwAkvZc6eouJSzA3udZPZ2jqhcMecWw+0BQLpg3rdzHKo2XeB/yx7aEsurOCvkGdVs7Agl0t+CpltbtPMbB+eLTm5cBTbLf5nuiS1SRtMOnKfZRzz0qrhf4/B7aUdOrAQ+syPrJrTo3yL/iOlhM7lAWpGlsnfDketH1c20HEb90ArEm7H/hd8kHgPJWlqaEMiTy6xXja8APgdsrEpcHVQ++nLE0x50auW2ZghMoLgSdQxowOLnPbyEiVGsvxwLG2r5jxycM5/9iQu9cDd1ImRAz+LhodejffDYxa2oSyYcmZTPz/aHz0UlfU5UHGugm/a7vJyVydIelJwP+ODZmuc1E28hDWlx/F5D7VCJUxjYxUGRiGuQawNeVK7VeM9/s3sn3YwJA7TfFwo0Pv6hom76FsXbZ7/WN+nhtez12SKBOFnmT7nXXW8BNsX9jAuZe7fGwTo5emUoenDtZi5t2HvqRFwN/zyH1tm94ScwnwfNu/rvcfRdnrd4flv3IVzjVqyb0LphuGOaaJ4ZhdU2dkfgb4e9vPkrQGZR2VZzQcx3GU/Wx/3/bT6uSVbw/jj6frJB1KmbjzS8ZHz7Qy3r5tkq4D/payiuxvd+Zq+m91mgld2YmpK+Zj8l4BG9o+WdJbAGw/KKmN4u6Otp8t6dIax9316mg+ejOwre272g6kA5bZPnXmpw3dMkkvG4tF0l6UbSHnXJJ7zJWfS/pd6hWipJ0oI4ma9htJqw/EsYCBK7V55kfAA20H0RFHqewpO7kO0liNrvor4Isqa+0LuAV4zTBOlOQec+VNwKnAVpK+T1nnfu8W4vgopbD8eElH1xj+oYU4uuAtwA8kXUAKuwdR1oBak/EPe1M2MW+My16+O6mBTUNGts+9KwW8GkurBas2i4iT4lgDeArliuQ6200tvTw5jqcCL6lxnNn0kNkOFe8upGwrN7mfuZXCbpskXWf7KW3HASBpT+DpwKPHjg1j4uMoJ/fWC3hdKVh1oYioskflScBX6tVJKyR9lLKzzQ9ajKErxbt5OQt0KnWU3fvbHoIp6V+B36EsS/4pSsvyQtsHz/m5Rji5X2R7h8E3cAvLiP6Q0lpotWA1NqV50u9iKBX45cSwBWXK/Z9REtpXgJNdNzJpMI4DagxPoXTPnOTxtdSbimGx7V1mfubQ43gPZb/ObzLP5z9IuoayOcaNtDBseSCOy20/c+D7OsB/2N51rs81yn3uXSjgdaVg1XoRsV6Vvg94n6StgbdR1v5ZveE4TgROrBO8XgG8V2W3rK0bDKMrxbv96ve3DBwzZRvEeaN2Wx5K2XKwbWMb2Dwg6YmUDVQ2HsaJRjm5d6GA15WCVSeKiJOu3h+ibFbelidTCmhbAE0vU9F68U7SajS0SUnX2bakjzU952Ia35K0PvB+ygb2pnTPzLmR7ZaB9gt4XSpYdaCIeAElmX2V0u/exhZmSHofZY/KH1G6hr5u+56GY+hE8U7SEtuL2o6jC+qyw/9i+6K2YxlTV5V9tO2h9DiMbHLvQgGvKwWrjhQRn2L7urbOPxDHocApbdZBOlS8O4YyQeYrwM/Hjs/TPvdrKa25mym/i1b63Gssz+eRy3N/bs7PM8LJvfUCXlcKVm0WESXtb/sLmmZHJje3E9NTbV+raXZkcoM7MXWoeHfjFIfn6/IDre3cNimOz1PeG0uZuNb/nHfljmxyHzRQwHuV7cYKeF374xkoIu4LNFJElHSo7U9o6h2ZPIzxu9PEcbztQzT1jkx2QxuX1OLdrkxRvMuyFe2T9Hgmji9vejTXNcA2biDxjnJBtdUCXkcLVo0XEW1/ot78ju3vDz4maecpXjKsOA6pN3d3XU51II5HT/GSYcXRpeIdkrYFtmFiQpvzLoCuk/QyyjrqT6Qsjz32N/L0hkO5krJU+e3DPtHIXrl3oYDXlYJVR4qIU20f9ohj8yGOrhTvamvqRZTkfjqwO7DYdhvLQrRK0mWU9eS/Y3t7SS8G9h/G5KEZ4jgL2A64kIlduXO+BeMoX7m/pgMFvO9IejPtF6x+REuTqSQ9D3g+sGBSv/t6NDjGXdITKJtkPEbS9oyvcb8eZUZgk3YEXiWp7eLd3pRNQy61fVBdsuMLDcfQFb+x/RNJq0lazfZZkv65hTje3tSJRi65jxXwgD3rGg0TNFXAq/6sfj9sMAQamiQyVkQELgI2r2vKjAfSTBHxUcA6lPfSugPH76PZeQd/BBwIbEppfo8l9/uAtzYYx1gsXfAL2w9LelBl/aM7gc3aDqol99TZoOdQVmW8k4ELsqbYPrupc41ccgfWrt/XneKxRvuYbG/Z5Pmm8DfAIUzck3GMGd/WbGjqm/VsSZ9ts2A4MDP1FbZPaSuOGsvN8MjiXQuW1Akzn6Rs4v4z4LwW42nTXpTZoW+kLLL3WKCRYj+ML0kh6X4m5qmxVt16c37OEe5z33mqAt7kYw3E0XrBStKjpyoiTj425BjOAF451tevsnjZSbYbvYqtw1PfNymON9lubMbudMU7200X7wZjWgisZ3somzF3naSDgXNs/7DtWJqyWtsBzMKxK3hsaGrB6tj69WLK2ipzXhhZAVNNXmp6QtOGg0Vc23cDj284BiijZSbHsUfDMbwL2An479q6ewlwfsMxIOnzkl5bu+9umq+Jvdoc+ISkGyV9VdLrJG3XdlDDNHLdMl0p4FWtFqw6VkR8uC7Q9T81ti1ouJusWl3SWrZ/VeN4DLBWwzF0pXh3AmXM/bGStgIupVy9fqSFWFpl+yj47fvhtZQlmf+Z5nNGY0YuudOdAh60X7DqUhHx74HFks6ucexKqQc07YvAmXUJACiLeDW91k9XindnSToH2IHSsvwryrjueZfcJf0DsDMld1xK2V/23FaDGrJR7nPfou0Zf5I+Tkmi+1JWqfwZsNT2QQ3H0XoRscaxIaU7AuD8ttZ3kbQ7pSsE4Azb/9Xw+demFO9WY7x490XbP2k4jjMpAxDOoySyxbbvbDKGrpB0CfAgcBpwNnDeWOuur0Y5uXeigDcQz0JaKlh1oYg4cN6tmVhcPqfJGLqgK8U7SR8GnkOZLPN9SkviPNu/WO4Le6q2rncGdgFeCdzpDmyqMiyj2C0z5hEFvDr0rDF1EaBzgHPrePO27G77t90w9XexBw2u6S7pL4EjKF1ESylX8OfRwHDMSXHsRClwP43Shbc68PNhDDVbjrHi3ZbAEsbfI0sbjAHbbwSQtC6l++4zlKnvTdcgWldHte0KvBBYBNxCz7tlRnm0zMODk3ZaKuCdQNlF5VhJN0g6RdIRDccAtYg4dqelIuIRlL7dm22/GNgeuKfhGAD+hbID0Q+BxwB/CXysyQBsH1UXKtuGkkD+ljLOvFGSDpf0FUof816U9+vuTcfREcdQanQfBZ5m+8W2/7HlmIZqlK/cWy/gdahg1YUi4i9t/1ISdbTKtZJa2bDC9vWSVrf9EPAZSZcycau5oepQ8e7RwIeAi20/2ML5O8P2S9uOoWkj2+cO7RfwulSw6kAR8euUD5U3ULpi7gbWtN3oGPP6YfsHlK3LfkxZfe9AN7tZ+Lwr3kX3jHpyb7WAl4LV1CS9kDJC5D9t/7rhc28B3EHpb39jjePjtq9vOI55VbyL7hnZ5D5dAc8NbcowKZaxgtWbgSfYbrS/uyNFxKimK971vY+3yyRt5Za242zLKCf3Kyh93efb3k5lg+j32P7TBmM4nPJH/BzKdnvnUv6Iv9tUDDWOJZSx9l+lJJPXAL9nu7F+5hgn6VuUVtxi4CI3vHF7PFKtzW1KWUH1XMpQ1SvajWq4Rrmg2oUCXmcKVm0XEWPcfCzedZ3tF0p6FOWC8EXAaZLWsf24diMbnlFO7rfW5Uz/HThD0t1MsW/lMNn+QJPnW44H6ht3qcquTLcz2sNcV9l8bH7HzCTtQmll7wqsD3yLno9zH9lumUFtFvC6oCtFxC6Yj83vmJmkBylzDf4JOH0+5IleJPeIQZOa34cCjTa/03rontrK3xl4AeW98TBlAMbb2oxrmEa5WybiETrS/D5BUloPHWL7Hkk3UFZt3ZSybPia7UY1XLlyj17pSvO77dZDTFQT+7WUEUznABf2vWsmyb0H0g0wrgvN7ylaD0spQ2S/3FQMMVHdNOXhtuNoUrpl+iHdAFVHmt/fowOth5jgyZKOAzayva2kZwIvs/3utgMblly590S6AYouNL+70HqIieooqr8FPmF7+3rsStvbthvZ8OTKvQc6UkTsiie33fzuSOshJvod2xdKGjzW65Uyk9z74XukG2BM683vSa2H44CD5vn/SRfcVTcJN4CkvSmT/Xor3TI9kG6AcV1ofs/H4l3XSXoScDylFXU3cCOwv+2b2oxrmHLl3gPpBpigC83v1lsPMZHtG4A/qJuXr2b7/rZjGrYk9x5IN8AEXWh+f5LaegCwfbmkLwFJ7i2p21C+AlgIrDH24W/7nS2GNVRJ7v3QehGxQw6jNL+fKuk2avO74Ri60HqIib4B3EupTc2LXbGS3Psh3QBVR5rfXWg9xESb2t6t7SCalIJqD3ShiNgVk5vfY8ebbH7Px+Jd10k6Hjh2Pk3uy5V7P6QbYFzrze+OtB5iol2AAyXdSHlfCLDtZ7Yb1vAkufdDugHGtd78no/FuxGwe9sBNC3JvR+6UETsih9IekbLze/WWw8xke1Gd2nrgvS590i6AUDS1cCTKR9wrTS/52u9I7olV+49kG6ACbrQ/O5C6yHmuST3fkg3QNWR5ve8K95F96RbpgfSDdAtdcPyR+jIB0/ME7ly74d0A3RIknh0Qa7ce6ALRcSI6JYk9x5IN0BETJbkHhHRQ6u1HUBERMy9JPeIiB5Kco9ogKSFkv687Thi/khyj2jGQiDJPRqTgmr0Rl1b52TKPrKrA+8Crgc+BKwD3AUcaPt2STsAn6ZsJn4GsHvd6ORA4OXA2sDWwAeARwGvpgwz3cP2T+sqnB8DFgAPAK+1fa2kzwL3AYuAJwB/Z/vfJJ0PPI0yXPVE2x8e8q8j5rlcuUef7Ab8r+1n1Rm7/wkcC+xt+znACcDR9bmfAQ61vR3w0KSfsy3wp8AO9fkP1E1QzgNeU59zPPC6+nPfDHx84PUbU5YgeClwTD12JHCu7e2S2KMJmaEafXIF8EFJ7wW+RdkFaVvgjLqY2urA7ZLWB9a1fV593ZcoiXjMWXVlzfsl3Qt8c+DnP1PSOpRdlr46sEHKWgOv//e6p+3Vkjaa439jxApJco/esP3fkp4N7AG8G/gucJXt5w0+ryb35RlcfO3hgfsPU/5mVgPuqVf9M71e0zwnYqjSLRO9IemJlC6ULwDvB3YEFkh6Xn18TUlPt30P5ap8x/rSfVfmPLbvA26U9Mr6cyXpWTO87H5g3ZU5T8RsJLlHnzwDuFDSUuAo4B+BvYH3SroMWErpTgE4GPhkfe7alCWTV8argIPrz70K2GuG518OPCTpMklvXMlzRay0jJaJeUnSOrZ/Vm8fCWxs+4iWw4qYM+lzj/lqT0lvofwN3Awc2G44EXMrV+4RET2UPveIiB5Kco+I6KEk94iIHkpyj4jooST3iIgeSnKPiOih/w++XrnW8x6CNAAAAABJRU5ErkJggg==\n",
"text/plain": [
""
]
},
"metadata": {
"needs_background": "light"
},
"output_type": "display_data"
}
],
"source": [
"get_segment_summary(pd.merge(cons_df_2014, revenue_df, left_index=True,right_index=True, how='left').fillna(0))['avg_revenue_today'].sort_values(ascending=False).plot(kind='bar')"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "40b8eb29-5d14-446c-8e4e-cebd4fdf52d7",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"\n",
"\n",
"
\n",
" \n",
" \n",
" | \n",
" consumer_id | \n",
" purchase_amount | \n",
" date_of_purchase | \n",
" year_of_purchase | \n",
" days_since | \n",
"
\n",
" \n",
" \n",
" \n",
" 0 | \n",
" 760 | \n",
" 25.0 | \n",
" 2009-11-06 | \n",
" 2009 | \n",
" 2247 | \n",
"
\n",
" \n",
" 1 | \n",
" 860 | \n",
" 50.0 | \n",
" 2012-09-28 | \n",
" 2012 | \n",
" 1190 | \n",
"
\n",
" \n",
" 2 | \n",
" 1200 | \n",
" 100.0 | \n",
" 2005-10-25 | \n",
" 2005 | \n",
" 3720 | \n",
"
\n",
" \n",
" 3 | \n",
" 1420 | \n",
" 50.0 | \n",
" 2009-07-09 | \n",
" 2009 | \n",
" 2367 | \n",
"
\n",
" \n",
" 4 | \n",
" 1940 | \n",
" 70.0 | \n",
" 2013-01-25 | \n",
" 2013 | \n",
" 1071 | \n",
"
\n",
" \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
" ... | \n",
"
\n",
" \n",
" 51238 | \n",
" 163230 | \n",
" 30.0 | \n",
" 2011-11-12 | \n",
" 2011 | \n",
" 1511 | \n",
"
\n",
" \n",
" 51239 | \n",
" 154410 | \n",
" 100.0 | \n",
" 2013-11-15 | \n",
" 2013 | \n",
" 777 | \n",
"
\n",
" \n",
" 51240 | \n",
" 189270 | \n",
" 30.0 | \n",
" 2014-12-23 | \n",
" 2014 | \n",
" 374 | \n",
"
\n",
" \n",
" 51241 | \n",
" 173810 | \n",
" 30.0 | \n",
" 2015-05-28 | \n",
" 2015 | \n",
" 218 | \n",
"
\n",
" \n",
" 51242 | \n",
" 9830 | \n",
" 50.0 | \n",
" 2011-11-30 | \n",
" 2011 | \n",
" 1493 | \n",
"
\n",
" \n",
"
\n",
"
51243 rows × 5 columns
\n",
"
"
],
"text/plain": [
" consumer_id purchase_amount date_of_purchase year_of_purchase \\\n",
"0 760 25.0 2009-11-06 2009 \n",
"1 860 50.0 2012-09-28 2012 \n",
"2 1200 100.0 2005-10-25 2005 \n",
"3 1420 50.0 2009-07-09 2009 \n",
"4 1940 70.0 2013-01-25 2013 \n",
"... ... ... ... ... \n",
"51238 163230 30.0 2011-11-12 2011 \n",
"51239 154410 100.0 2013-11-15 2013 \n",
"51240 189270 30.0 2014-12-23 2014 \n",
"51241 173810 30.0 2015-05-28 2015 \n",
"51242 9830 50.0 2011-11-30 2011 \n",
"\n",
" days_since \n",
"0 2247 \n",
"1 1190 \n",
"2 3720 \n",
"3 2367 \n",
"4 1071 \n",
"... ... \n",
"51238 1511 \n",
"51239 777 \n",
"51240 374 \n",
"51241 218 \n",
"51242 1493 \n",
"\n",
"[51243 rows x 5 columns]"
]
},
"execution_count": null,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"df[df['purchase_amount']>0]"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "ef012c34-3ea2-4769-bb34-c0190ac56714",
"metadata": {},
"outputs": [],
"source": []
},
{
"cell_type": "code",
"execution_count": null,
"id": "5878f07b-9058-482c-ab73-08556e1c31d4",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3 (ipykernel)",
"language": "python",
"name": "python3"
}
},
"nbformat": 4,
"nbformat_minor": 5
}