Booking¶

This example uses the expedia dataset to predict, based on site activity, whether a user is likely to make a booking. You can download the Jupyter Notebook of the study here and the dataset here.

cnt: Number of similar events in the context of the same user session.
user_location_city: The ID of the city in which the customer is located.
is_package: 1 if the click/booking was generated as a part of a package (i.e. combined with a flight), 0 otherwise.
user_id: ID of the user.
srch_children_cnt: The number of (extra occupancy) children specified in the hotel room.
channel: marketing ID of a marketing channel.
hotel_cluster: ID of a hotel cluster.
srch_destination_id: ID of the destination where the hotel search was performed.
is_mobile: 1 if the user is on a mobile device, 0 otherwise.
srch_adults_cnt: The number of adults specified in the hotel room.
user_location_country: The ID of the country in which the customer is located.
srch_destination_type_id: ID of the destination where the hotel search was performed.
srch_rm_cnt: The number of hotel rooms specified in the search.
posa_continent: ID of the continent associated with the site_name.
srch_ci: Check-in date.
user_location_region: The ID of the region in which the customer is located.
hotel_country: Hotel’s country.
srch_co: Check-out date.
is_booking: 1 if a booking, 0 if a click.
orig_destination_distance: Physical distance between a hotel and a customer at the time of search. A null means the distance could not be calculated.
hotel_continent: Hotel continent.
site_name: ID of the Expedia point of sale (i.e. Expedia.com, Expedia.co.uk, Expedia.co.jp, …).

We will follow the data science cycle (Data Exploration - Data Preparation - Data Modeling - Model Evaluation - Model Deployment) to solve this problem.

Initialization¶

This example uses the following version of VerticaPy:

import verticapy as vp

vp.__version__
Out[2]: '1.1.0'

Connect to Vertica. This example uses an existing connection called VerticaDSN. For details on how to create a connection, see the Connection tutorial. You can skip the below cell if you already have an established connection.

vp.connect("VerticaDSN")

Let’s create a Virtual DataFrame of the dataset.

expedia = vp.read_csv("expedia.csv", parse_nrows = 1000)
expedia.head(5)

	📅 date_time Timestamp 100%	...	123 hotel_market Int 100%	123 hotel_cluster Int 100%
1	2013-01-07 00:08:27	...	107	69
2	2013-01-07 06:00:57	...	682	71
3	2013-01-07 06:30:07	...	88	82
4	2013-01-07 06:53:42	...	88	82
5	2013-01-07 08:11:39	...	491	48

Warning

This example uses a sample dataset. For the full analysis, you should consider using the complete dataset.

Data Exploration and Preparation¶

Sessionization is the process of gathering clicks for a certain period of time. We usually consider that after 30 minutes of inactivity, the user session ends (date_time - lag(date_time) > 30 minutes). For these kinds of use cases, aggregating sessions with meaningful statistics is the key for making accurate predictions.

We start by using the sessionize() method to create the variable session_id. We can then use this variable to aggregate the data.

expedia.sessionize(
    ts = "date_time",
    by = ["user_id"],
    session_threshold = "30 minutes",
    name = "session_id",
)

	📅 date_time Timestamp 100%	...	123 hotel_cluster Int 100%	123 session_id Integer 100%
1	2014-07-24 07:49:45	...	34	0
2	2014-08-01 12:33:26	...	54	1
3	2014-08-01 13:46:56	...	31	2
4	2014-08-07 17:40:41	...	65	3
5	2014-08-09 11:17:43	...	54	4
6	2014-08-09 11:37:20	...	54	4
7	2014-08-09 16:52:33	...	0	5
8	2014-08-09 16:54:37	...	57	5
9	2014-08-09 16:55:49	...	83	5
10	2014-08-09 16:56:42	...	76	5
11	2014-08-09 17:00:05	...	83	5
12	2014-08-09 17:00:59	...	83	5
13	2014-08-09 17:10:56	...	51	5
14	2014-03-14 19:27:45	...	41	0
15	2014-04-07 19:05:04	...	96	1
16	2014-04-07 19:06:16	...	96	1
17	2014-04-07 19:10:12	...	48	1
18	2014-04-07 19:12:50	...	83	1
19	2014-04-07 19:13:47	...	48	1
20	2014-07-18 21:53:20	...	98	0

The duration of the trip should also influence/be indicative of the user’s behavior on the site, so we’ll take that into account.

expedia["trip_duration"] = expedia["srch_co"] - expedia["srch_ci"]

If a user looks at the same hotel several times, then it might mean that they’re looking to book that hotel during the session.

expedia.analytic(
    "mode",
    columns = "hotel_cluster",
    by = [
        "user_id",
        "session_id",
    ],
    name = "mode_hotel_cluster",
    add_count = True,
)

	📅 date_time Timestamp 100%	...	123 site_name Int 100%	123 mode_hotel_cluster_count Integer 100%
1	2014-08-07 17:40:41	...	2	1
2	2013-03-27 22:50:37	...	2	3
3	2013-03-27 22:33:18	...	2	3
4	2013-03-27 23:18:57	...	2	3
5	2013-03-27 22:56:17	...	2	3
6	2013-03-27 23:01:36	...	2	3
7	2013-03-27 22:58:25	...	2	3
8	2013-03-27 22:48:23	...	2	3
9	2013-03-27 23:11:54	...	2	3
10	2014-11-16 12:29:00	...	2	2
11	2014-11-16 12:33:28	...	2	2
12	2013-06-25 08:16:54	...	34	1
13	2014-10-29 08:14:44	...	11	1
14	2014-07-22 15:20:33	...	2	1
15	2014-11-28 09:02:59	...	2	3
16	2014-11-28 09:20:25	...	2	3
17	2014-11-28 09:22:05	...	2	3
18	2014-11-28 09:09:05	...	2	3
19	2014-11-28 09:31:08	...	2	3
20	2014-11-28 09:06:08	...	2	3

We can now aggregate the session and get some useful statistics out of it:

end_session_date_time: Date and time when the session ends.

session_duration: Session duration.

is_booking: 1 if the user booked during the session, 0 otherwise.

trip_duration: Trip duration.

orig_destination_distance: Average of the physical distances between the hotels and the customer.

srch_family_cnt: The number of people specified in the hotel room.

import verticapy.sql.functions as fun

expedia = expedia.groupby(
    columns = [
        "user_id",
        "session_id",
        "mode_hotel_cluster_count",
    ],
    expr = [
        fun.max(expedia["date_time"])._as("end_session_date_time"),
        ((fun.max(expedia["date_time"]) - fun.min(expedia["date_time"])) / fun.interval("1 second"))._as(
            "session_duration"
        ),
        fun.max(expedia["is_booking"])._as("is_booking"),
        fun.avg(expedia["trip_duration"])._as("trip_duration"),
        fun.avg(expedia["orig_destination_distance"])._as("avg_distance"),
        fun.sum(expedia["cnt"])._as("nb_click_session"),
        fun.median(expedia["srch_children_cnt"] + expedia["srch_adults_cnt"])._as("srch_family_cnt"),
    ],
)

Let’s look at the missing values.

expedia.count_percent()

	...	count	percent
"user_id"	...	46987.0	100.0
"session_id"	...	46987.0	100.0
"mode_hotel_cluster_count"	...	46987.0	100.0
"end_session_date_time"	...	46987.0	100.0
"session_duration"	...	46987.0	100.0
"is_booking"	...	46987.0	100.0
"nb_click_session"	...	46987.0	100.0
"srch_family_cnt"	...	46987.0	100.0
"trip_duration"	...	46872.0	99.755
"avg_distance"	...	18219.0	38.775

Let’s impute the missing values for avg_distance and trip_duration.

expedia["avg_distance" ].fillna(method = "avg")
expedia["trip_duration"].fillna(method = "avg")

	123 user_id Integer 100%	...	123 session_id Integer 100%	123 srch_family_cnt Float(22) 100%
1	1295	...	1	2.0
2	2675	...	0	4.0
3	3181	...	0	2.0
4	3015	...	28	2.0
5	1087	...	4	2.0
6	2458	...	17	2.0
7	4179	...	0	2.0
8	3668	...	18	1.0
9	4089	...	0	5.0
10	3290	...	12	5.0
11	2410	...	4	2.0
12	1253	...	1	2.0
13	1219	...	40	2.0
14	1555	...	4	2.0
15	2047	...	0	1.0
16	1219	...	7	3.0
17	3054	...	14	2.0
18	3469	...	3	5.0
19	2047	...	6	1.0
20	1101	...	1	1.0

We can then look at the links between the variables. We will use Spearman’s rank correleation coefficient to get all the monotonic relationships.

expedia.corr(method = "spearman")

We can see huge links between some of the variables (mode_hotel_cluster_count and session_duration) and our response variable (is_booking). A logistic regression would work well in this case because the response and predictors have a monotonic relationship.

Machine Learning¶

Let’s create our LogisticRegression model.

from verticapy.machine_learning.vertica import LogisticRegression

model_logit = LogisticRegression(
    max_iter = 1000,
    solver = "BFGS",
)


model_logit.fit(
    expedia,
    [
        "avg_distance",
        "session_duration",
        "nb_click_session",
        "mode_hotel_cluster_count",
        "session_id",
        "srch_family_cnt",
        "trip_duration",
    ],
    "is_booking",
)



=======
details
=======
       predictor        |coefficient|std_err | z_value |p_value 
------------------------+-----------+--------+---------+--------
       Intercept        | -1.71825  | 0.04619|-37.19993| 0.00000
      avg_distance      | -0.00003  | 0.00001|-2.89006 | 0.00385
    session_duration    |  0.00063  | 0.00002|27.84449 | 0.00000
    nb_click_session    | -0.15378  | 0.00455|-33.76287| 0.00000
mode_hotel_cluster_count|  0.98981  | 0.01864|53.11205 | 0.00000
       session_id       | -0.00692  | 0.00083|-8.36064 | 0.00000
    srch_family_cnt     | -0.15788  | 0.01305|-12.09381| 0.00000
     trip_duration      | -0.20413  | 0.00736|-27.74772| 0.00000


==============
regularization
==============
type| lambda 
----+--------
none| 1.00000


===========
call_string
===========
logistic_reg('"public"."_verticapy_tmp_logisticregression_v_mldb_5c956de097af11efa8720242ac120002_"', '"public"."_verticapy_tmp_view_v_mldb_5cf9400e97af11efa8720242ac120002_"', '"is_booking"', '"avg_distance", "session_duration", "nb_click_session", "mode_hotel_cluster_count", "session_id", "srch_family_cnt", "trip_duration"'
USING PARAMETERS optimizer='bfgs', epsilon=1e-06, max_iterations=1000, regularization='none', lambda=1, alpha=0.5, fit_intercept=true)

===============
Additional Info
===============
       Name       |Value
------------------+-----
 iteration_count  | 48  
rejected_row_count|  0  
accepted_row_count|46987

None of our coefficients are rejected (pvalue = 0). Let’s look at their importance.

model_logit.features_importance()

It looks like there are two main predictors: mode_hotel_cluster_count and trip_duration. According to our model, users likely to make a booking during a particular session will tend to:

look at the same hotel many times.
look for a shorter trip duration.
not click as much (spend more time at the same web page).

Let’s add our prediction to the vDataFrame.

model_logit.predict_proba(
    expedia,
    name = "booking_prob_logit",
    pos_label = 1,
)

	123 user_id Integer 100%	...	123 srch_family_cnt Float(22) 100%	123 booking_prob_logit Float(22) 100%
1	3618	...	3.0	0.0597604274787364
2	2450	...	2.0	0.0429984341849869
3	3969	...	2.0	0.0878659997559027
4	1350	...	1.0	0.25449487451923
5	902	...	2.0	0.132956762555594
6	378	...	2.0	0.259610408629628
7	1406	...	4.0	0.0336132345002705
8	343	...	1.5	0.209891587212376
9	2052	...	3.0	0.212333489870668
10	2298	...	2.0	0.298976991060295
11	1692	...	1.0	0.875886732534796
12	3831	...	2.0	0.301319442859276
13	1757	...	3.0	0.0208170821819046
14	2464	...	2.0	0.175661926783451
15	3138	...	4.0	0.0926077197126612
16	1523	...	1.0	0.181511573260104
17	1221	...	1.0	0.198207143709372
18	2965	...	3.0	0.356575666759751
19	1221	...	1.0	0.0842795233889615
20	1640	...	2.0	0.0518732659194444

While analyzing the following boxplot (prediction partitioned by is_booking), we can notice that the cutoff is around 0.22 because most of the positive predictions have a probability between 0.23 and 0.5. Most of the negative predictions are between 0.05 and 0.2.

expedia["booking_prob_logit"].boxplot(by = "is_booking")

Let’s confirm our hypothesis by computing the best cutoff.

model_logit.score(metric = "best_cutoff")
Out[9]: 0.216

Let’s look at the efficiency of our model with a cutoff of 0.22.

model_logit.report(cutoff = 0.22)

	value
auc	0.8352827981798504
prc_auc	0.5231556017763888
accuracy	0.8071381445931853
log_loss	0.183468875994714
precision	0.5290269828291088
recall	0.7831349606616905
f1_score	0.631476209841399
mcc	0.5253206639901231
informedness	0.59669199677962
markedness	0.4624861762926351
csi	0.46142874123380484

ROC Curve:¶

model_logit.roc_curve()

We’re left with an excellent model. With this, we can predict whether a user will book a hotel during a specific session and make adjustments to our site accordingly. For example, to influence a user to make a booking, we could propose new hotels.

Conclusion¶

We’ve solved our problem in a Pandas-like way, all without ever loading data into memory!