<jupyter_start><jupyter_text>Identifying bying clientsIn this notebook we will create a decision tree based model to identify clients who pay (buyers) and understand what makes a user a client (most relevant features).We divide our notebook into four stages: data preparation, feature engineering, model building, feature importance analysis<jupyter_code>import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns
import psycopg2
from IPython.display import display
import yaml<jupyter_output><empty_output><jupyter_text>1. Data preparationIn this step we load from database (PostgreSQL in this example) data and keep it locally as a CSV file. 'The main reason for that is to be able to reproduce results quickly.<jupyter_code># Create a connection to the database

# Load a config file with credentials
conf = yaml.load(
    open("credentials.yml"), Loader=yaml.FullLoader)['pg']
# Create a connection
conn = psycopg2.connect(
    host=conf['host'],
    port=conf['port'],
    database=conf['database'],
    user=conf['user'],
    password=conf['password']
)<jupyter_output><empty_output><jupyter_text>Here we prepare two functions to obtain data from the databases (or .csv files if they were pre-downloaded)<jupyter_code>def load_events(source='csv') -> pd.DataFrame:
    """ Obtains session events from database or csv file"""
    if source == 'db':
        q = f'select * from connector_events where sessionid IN {sessions}'
        all_events = pd.read_sql(q, conn)
        all_events.to_csv('all_events_1454.csv', index=False)
    elif source == 'csv':
        all_events = pd.read_csv('all_events_1454_sep.csv', sep='|')
    else:
        raise ValueError("source parameter should be either 'csv' or 'db'")
    return all_events

def load_sessions(source='csv') -> pd.DataFrame:
    """ Obtains sessions information from database or csv file"""
    if source == 'db':
        q = f"select * from connector_user_sessions where sessionid in {sessions}"
        all_sessions = pd.read_sql(q, conn)
        # Saving as a CSV file is optional
        all_sessions.to_csv("all_sessions.csv", sep='|', index=False)
    elif source == 'csv':
        all_sessions = pd.read_csv("all_sessions.csv", sep='|')
    else:
        raise ValueError("source parameter should be either 'csv' or 'db'")
    return all_sessions<jupyter_output><empty_output><jupyter_text>In the connector's events table we found a client who had a button with a label "PAY" by performing a simple query`q = "select session_id from connector_events where mouseclick_label = 'PAY";`We went on to find all session ids of this client from our internal tables and saved it in `all_sessions_1454.csv`.This step is unnecessary for clients and only explained for general clarity.**The most important takeaway here, is that we have prepared a list of sessions for which we knowwhether a click on "PAY" button has been made or not.**<jupyter_code>sessions_info = pd.read_csv("all_sessions_1454.csv")
sessions = tuple(sessions_info['session_id'])<jupyter_output><empty_output><jupyter_text>First off, let's see that the dataset is highly imbalanced, because the number of buyers is much less than the number of ordinary visitors.In fact, the percentage of buying clients (0.04%) is so small, it's not event seen on the pie chart.Hence we're going to use special techniques for imbalanced datasets.<jupyter_code>buyers_count = sessions_info[sessions_info.paid == 1].shape[0]
regular_count = sessions_info.shape[0] - buyers_count
ax = plt.subplot(111)
wedges, texts, _ = ax.pie(x=(buyers_count, regular_count),
        shadow=False,
        labels=['Paying visitors', 'Regular visitors'],
        autopct='%1.2f%%',
        explode=(0, 0.8))

for w in wedges:
    w.set_linewidth(1)
    w.set_edgecolor('white')

plt.show()

all_events = load_events()<jupyter_output>c:\users\david\appdata\local\programs\python\python38\lib\site-packages\IPython\core\interactiveshell.py:3361: DtypeWarning: Columns (9,11,12,13,26) have mixed types.Specify dtype option on import or set low_memory=False.
  if (await self.run_code(code, result,  async_=asy)):<jupyter_text>Let's take a look at the events dataset by printing one session<jupyter_code>all_events[all_events.sessionid == all_events.iloc[0].sessionid].head(10)<jupyter_output><empty_output><jupyter_text>We mostly see NaNs and that's OK.One row in session events files contains user's actions grouped by timestamp.For example, if there was a mouse click event at a time $t$, only the columns corresponding to that click(such as mouseclick_label, mouseclick_hesitationtime etc) will be filled and the rest will be NaNs.The columns sessionid, received_at, batch_order_number will always be filled as the contain information about thesession unique identifier, the time at which the event was received by connectors worker and the order numberto ensure the chronological ordering is preserved when inserting in database in batches.<jupyter_code># Let's take a glance at all available features
list(all_events.columns)<jupyter_output><empty_output><jupyter_text>2. Feature engineeringHow are we going to predict buying users?What kind of features identify them?- Did the user visit the website earlier?- Was the website not illustrating images?- Did users experience many issues on the website?- What pages did he visit?All of those questions sound important.In these section we will extract the answers to this questions from the datasets of events and sessions for each user.We'll create a vector of numerical features and assign them to each user who visited the site.Our goal is to see if the feature sets or buyers and  is separable by some nonlinear function with a good precision. We'll be looking for this function using decision trees model.Of course one can experiment further by applying any other algorithm. Unfortunately, none of the paying clients received ids.Hence we'll only be looking into the session features.<jupyter_code># Create a DataFrame for session features
labels = sessions_info['paid']
session_features = sessions_info.drop(['paid'], axis=1)
session_features.rename({'session_id': 'sessionid'}, axis=1, inplace=True)<jupyter_output><empty_output><jupyter_text>Let's get to some meaningful features. For example, let's create a functionthat will tell if a specific event happened during the session.<jupyter_code>def add_feature_about_event_presence(session_features, column, feature_name, dtype='int'):
    temp_sessions = all_events[['sessionid', column]].dropna()
    temp_sessions = temp_sessions.drop_duplicates(subset='sessionid', keep='last')
    if temp_sessions.shape[0] == 0:
        return session_features
    session_features = session_features.merge(temp_sessions, how='left', on='sessionid')

    if dtype == 'int':
        session_features.loc[session_features[column] > 0, feature_name] = 1
    elif dtype == 'str':
        session_features.loc[session_features[column] != '', feature_name] = 1
    session_features[feature_name] = session_features[feature_name].fillna(0)
    session_features = session_features.drop([column], axis=1)
    return session_features
for column, feature_name in [('errorevent_messageid', 'error_event'),
                             ('customevent_messageid', 'custom_event'),
                             ('jsexception_message', 'js_exception'),
                             ('customissue_name', 'custom_issue')
                             ]:
    session_features = add_feature_about_event_presence(session_features, column, feature_name)<jupyter_output><empty_output><jupyter_text>At some point it's interesting to take a lookat the maximum values of some parameters during the session.These features can be added with the function below:<jupyter_code>def add_max_val(session_features, column):
    feature_df = all_events[['sessionid', column]].dropna()
    feature_df_agg = feature_df.groupby('sessionid').agg('max').reset_index()
    if feature_df_agg.shape[0] > 0:
        session_features = session_features.merge(feature_df_agg, how='left', on='sessionid')
        session_features[col] = session_features[col].fillna(0)
    return session_features
max_feature_columns = ['pageevent_firstcontentfulpaint',
                       'pageevent_firstpaint',
                       'pageevent_speedindex',
                       'pagerendertiming_timetointeractive',
                       'pagerendertiming_visuallycomplete',
                       'rawcustomevent_name',
                       'rawcustomevent_payload',
                       'setviewportsize_height',
                       'setviewportsize_width']

for col in max_feature_columns:
    session_features = add_max_val(session_features, col)<jupyter_output><empty_output><jupyter_text>We also should handle categorical variables, the ones that can take on one of a limited,and usually fixed, number of possible values (such us user's browser).<jupyter_code>def add_categorial_feature(session_features, column, feature_name):
    categories = list(session_features[column].unique())
    session_features[feature_name] = pd.Categorical(session_features[column], categories=categories).codes
# Get additional information from sessions table
sessions_table = load_sessions()
sessions_table = sessions_table.drop_duplicates(subset=['sessionid'], keep='last')
print(sessions_table.shape[0])
for iss in ['click_rage', 'missing_resource', 'dead_click', 'js_exception', 'bad_request',  'cpu', 'memory']:
    session_features[iss] = session_features['issue_types'].apply(lambda x: 1 if iss in x else 0)
session_features = session_features.drop(['issue_types'], axis=1)
session_features.head(10)
session_features.shape
def add_one_hot_encoded_feature(origin_df, session_features, column):
    df = origin_df[['sessionid', column]]
    dummies = pd.get_dummies(df[column], prefix=column, dummy_na=True)
    df = pd.concat([df, dummies], axis=1)
    session_features = session_features.merge(df, how='left', on='sessionid')
    session_features = session_features.drop([column], axis=1)
    return session_features
for col in ['user_browser', 'user_country', 'user_device', 'connection_type']:
    session_features = add_one_hot_encoded_feature(sessions_table, session_features, col)
session_features.shape
columns_to_merge = ['connection_effective_bandwidth', 'session_start_timestamp',
                    'session_duration', 'user_device_heap_size',
                    'user_device_memory_size', 'avg_cpu', 'avg_fps',  'max_cpu',
                    'max_fps', 'max_total_js_heap_size', 'max_used_js_heap_size',
                    'js_exceptions_count', 'long_tasks_total_duration', 'long_tasks_max_duration',
                    'long_tasks_count', 'inputs_count', 'clicks_count', 'sessionid'
                    ]
session_features = session_features.merge(sessions_table[columns_to_merge],
                                          how='left',
                                          on='sessionid')
session_features = session_features.dropna(how='all', axis=0)
session_features = session_features.fillna(0)
session_features.head()

session_features.shape<jupyter_output><empty_output><jupyter_text>3. Build model Decision tree model is chosen because it is known to work great with heterogenous datasets and correlated features<jupyter_code>import xgboost as xgb
import sklearn
from sklearn.model_selection import train_test_split
x_train, x_test, y_train, y_test = train_test_split(session_features.drop(['sessionid'], axis=1),
                                                    labels,
                                                    test_size=0.15,
                                                    random_state=42)

# Ensure that test set has paying clients
sum(y_test), sum(y_train)
# Ratio of paying sessions will be denoted by EPSILON
EPSILON = y_train[y_train == 1].shape[0]/y_train.shape[0]
EPSILON
xgc0 = xgb.XGBClassifier()
xgc0.fit(x_train, y_train)
xgc = xgb.XGBClassifier(scale_pos_weight=1/EPSILON)
xgc.fit(x_train, y_train)<jupyter_output>[17:33:09] WARNING: C:/Users/Administrator/workspace/xgboost-win64_release_1.3.0/src/learner.cc:1061: Starting in XGBoost 1.3.0, the default evaluation metric used with the objective 'binary:logistic' was changed from 'error' to 'logloss'. Explicitly set eval_metric if you'd like to restore the old behavior.<jupyter_text>4. Evaluate and choose the best modelIn this section we will built two modelsand see at their performances using different metrics<jupyter_code>from datetime import datetime
from xgboost import plot_importance
from sklearn.metrics import plot_roc_curve, recall_score, precision_score, accuracy_score, confusion_matrix
from sklearn.metrics import plot_confusion_matrix, precision_recall_curve, plot_precision_recall_curve

def report(models, x_test, y_test, y_pred=None, model_names=None):

    fig, (ax1, ax2) = plt.subplots(2, 1, figsize=(12, 14))

    for model, name in zip(models, model_names):

        y_out = model.predict(x_test)

        precision = precision_score(y_test, y_out)
        recall = recall_score(y_test, y_out)
        accuracy = accuracy_score(y_test, y_out)

        print(f'Model: {name}')
        print(f'Precision: {precision}')
        print(f'Recall: {recall}')
        print(f'Accuracy: {accuracy}')
        print('-------------------------')
        print()

        roc_auc = plot_roc_curve(model, x_test, y_test, ax=ax1)
        precision_recall = plot_precision_recall_curve(model, x_test, y_test, ax=ax2)

    date_time = datetime.now().strftime("%m_%d_%H_%M_%S")
    plt.savefig(f'report_{date_time}.png', dpi=300)
    plt.show()

report([xgc0, xgc], x_test, y_test, model_names=['XGBClassifier', 'Weighted XGBClassifier'])<jupyter_output>Model: XGBClassifier
Precision: 0.8
Recall: 0.6666666666666666
Accuracy: 0.9996539792387543
-------------------------

Model: Weighted XGBClassifier
Precision: 0.6666666666666666
Recall: 1.0
Accuracy: 0.9996539792387543
-------------------------<jupyter_text>For the best model let's see the features that influenced the decision the most:<jupyter_code>plot_importance(xgc0)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0975575221238938, 'fraction_numerical': 0.013734513274336283, 'mean_word_length': 4.306536438767844, 'pattern_counts': {'":': 0, '<': 54, '<?xml version=': 0, '>': 58, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 11, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '162513', 'license': 'MIT', 'n_tokens_mistral': 4281, 'n_tokens_neox': 4086, 'n_words': 1194, 'path': 'ee/connectors/data_analysis_cookbook/buying_clients.ipynb', 'repo_name': 'thundermiracle/openreplay', 'size': '14125'}