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 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:

In [1]:

import verticapy as vp
vp.__version__

Out[1]:

'0.9.0'

Connect to Vertica. This example uses an existing connection called "VerticaDSN." For details on how to create a connection, use see the connection tutorial.

In [2]:

vp.connect("VerticaDSN")

Let's create a Virtual DataFrame of the dataset.

In [3]:

expedia = vp.read_csv('data/expedia.csv', parse_nrows=1000)
expedia.head(5)

Out[3]:

	📅 date_time Timestamp	123 site_name Int	123 posa_continent Int	123 user_location_country Int	123 user_location_region Int	123 user_location_city Int	123 orig_destination_distance Numeric(11,5)	123 user_id Int	123 is_mobile Int	123 is_package Int	123 channel Int	📅 srch_ci Date	📅 srch_co Date	123 srch_adults_cnt Int	123 srch_children_cnt Int	123 srch_rm_cnt Int	123 srch_destination_id Int	123 srch_destination_type_id Int	123 is_booking Int	123 cnt Int	123 hotel_continent Int	123 hotel_country Int	123 hotel_market Int	123 hotel_cluster Int
1	2013-01-07 00:00:02	24	2	3	50	5703	[null]	461899	0	0	9	2013-03-14	2013-03-15	2	1	1	669	3	0	1	2	50	212	41
2	2013-01-07 00:00:06	2	3	66	174	21177	5713.6206	13796	0	0	9	2013-01-19	2013-01-26	1	0	1	8821	1	0	3	6	17	30	58
3	2013-01-07 00:00:06	11	3	205	155	14703	795.7298	1128575	0	0	9	2013-01-19	2013-01-22	1	0	1	25064	6	0	1	2	50	1230	91
4	2013-01-07 00:00:09	37	1	69	761	41949	[null]	1080476	0	1	9	2013-05-29	2013-06-05	2	0	1	7635	3	0	1	2	50	675	10
5	2013-01-07 00:00:17	37	1	69	761	41949	[null]	1018895	0	0	0	2013-09-08	2013-09-10	2	0	1	27215	6	0	2	2	50	645	59

Rows: 1-5 | Columns: 24

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.

In [4]:

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

Out[4]:

	📅 date_time Timestamp	123 site_name Int	123 posa_continent Int	123 user_location_country Int	123 user_location_region Int	123 user_location_city Int	123 orig_destination_distance Numeric(11,5)	123 user_id Int	123 is_mobile Int	123 is_package Int	123 channel Int	📅 srch_ci Date	📅 srch_co Date	123 srch_adults_cnt Int	123 srch_children_cnt Int	123 srch_rm_cnt Int	123 srch_destination_id Int	123 srch_destination_type_id Int	123 is_booking Int	123 cnt Int	123 hotel_continent Int	123 hotel_country Int	123 hotel_market Int	123 hotel_cluster Int	123 session_id Integer
1	2014-09-03 08:18:41	2	3	66	174	9045	53.6059	0	1	0	2	2014-09-06	2014-09-07	2	2	1	24693	6	0	1	2	50	1241	19	0
2	2014-12-03 11:28:11	2	3	66	174	42538	343.7866	0	1	0	2	2014-12-29	2014-12-30	2	1	1	8279	1	0	1	2	50	1230	98	1
3	2014-12-03 11:28:58	2	3	66	174	42538	343.9901	0	1	0	2	2014-12-29	2014-12-30	2	1	1	8279	1	0	1	2	50	1230	40	1
4	2014-12-03 11:30:21	2	3	66	174	42538	343.7968	0	1	0	2	2014-12-29	2014-12-30	2	1	1	8279	1	0	1	2	50	1230	56	1
5	2014-12-03 11:32:43	2	3	66	174	42538	342.2163	0	1	0	2	2014-12-29	2014-12-30	3	2	1	8279	1	0	2	2	50	358	72	1
6	2014-12-06 01:38:26	2	3	66	174	9045	88.1489	0	1	0	2	2014-12-19	2014-12-20	2	2	1	8279	1	0	1	2	50	358	28	2
7	2014-04-25 16:14:28	2	3	66	174	37449	5405.4889	1	1	0	2	2014-11-07	2014-11-09	3	1	1	62881	4	0	1	4	98	2052	20	0
8	2014-04-25 16:14:46	2	3	66	174	37449	5405.4889	1	1	0	2	2014-11-07	2014-11-09	5	1	2	62881	4	0	1	4	98	2052	20	0
9	2014-04-25 16:15:11	2	3	66	174	37449	5405.4889	1	1	0	2	2014-11-07	2014-11-09	3	1	2	62881	4	0	1	4	98	2052	20	0
10	2014-04-25 16:15:50	2	3	66	174	37449	5405.7409	1	1	0	2	2014-11-07	2014-11-09	3	1	2	62881	4	0	1	4	98	2052	60	0
11	2014-01-27 12:56:00	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	2	2	50	703	96	0
12	2014-01-27 13:00:31	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	1	2	50	703	72	0
13	2014-01-27 13:04:55	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	1	2	50	703	91	0
14	2014-01-27 13:06:31	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	1	2	50	703	91	0
15	2014-01-27 13:14:21	2	3	66	311	25671	[null]	2	0	0	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	2	2	50	703	91	0
16	2014-01-27 13:18:43	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	1	2	50	703	94	0
17	2014-01-27 13:22:39	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	1	2	50	703	21	0
18	2014-01-27 13:39:30	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	11621	1	0	2	2	50	703	96	0
19	2014-01-27 13:54:07	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	11621	1	0	2	2	50	703	94	0
20	2014-01-28 12:20:02	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	3	2	50	686	95	1
21	2014-01-28 12:27:47	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	41	1
22	2014-01-28 12:28:31	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	73	1
23	2014-01-28 12:31:18	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	68	1
24	2014-01-28 12:32:35	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	91	1
25	2014-01-28 12:34:21	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	91	1
26	2014-01-28 12:37:49	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	2	2	50	686	18	1
27	2014-01-28 12:44:16	2	3	66	311	25671	[null]	2	0	0	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	18	1
28	2014-01-28 17:06:02	2	3	66	311	25671	[null]	2	0	1	2	2014-04-08	2014-04-11	2	0	1	8250	1	0	4	2	50	628	18	2
29	2014-01-28 17:11:35	2	3	66	311	25671	[null]	2	0	1	2	2014-04-08	2014-04-11	2	0	1	8250	1	0	1	2	50	628	1	2
30	2014-07-31 07:10:39	2	3	66	321	51642	1432.9656	3	0	0	9	2015-02-07	2015-02-10	3	2	2	28586	6	0	1	2	50	705	5	0
31	2014-09-08 09:56:21	2	3	66	467	5222	142.7467	3	0	0	9	2014-10-11	2014-10-12	2	0	1	8261	1	0	1	2	50	646	18	1
32	2014-09-08 09:58:50	2	3	66	467	5222	141.0454	3	0	0	9	2014-10-11	2014-10-12	2	0	1	8261	1	0	1	2	50	646	2	1
33	2014-09-09 10:49:48	2	3	66	467	5222	143.2998	3	0	0	9	2015-02-01	2015-02-02	2	2	1	27699	6	0	1	2	50	646	48	2
34	2014-09-22 13:19:02	2	3	66	467	5222	141.1273	3	0	0	3	2015-02-01	2015-02-02	2	0	1	27699	6	0	1	2	50	646	94	3
35	2014-09-22 13:22:25	2	3	66	467	5222	141.1273	3	0	0	3	2015-02-01	2015-02-02	3	0	1	8261	1	0	1	2	50	646	94	3
36	2014-09-30 12:56:51	2	3	66	467	5222	142.7467	3	0	0	9	2014-10-11	2014-10-12	2	0	1	8261	1	0	1	2	50	646	18	4
37	2014-10-20 11:19:21	2	3	66	467	5222	144.4084	3	0	0	9	2015-02-01	2015-02-02	2	2	1	27704	6	0	1	2	50	646	47	5
38	2014-08-14 18:11:08	2	3	66	258	45545	110.4682	4	0	0	9	2014-10-18	2014-10-20	2	0	1	8283	1	0	1	2	50	414	21	0
39	2014-08-29 16:53:11	2	3	66	258	45545	1318.4152	4	0	0	7	2015-01-30	2015-02-03	6	0	1	8250	1	0	1	2	50	628	71	1
40	2014-08-29 16:54:16	2	3	66	258	45545	1318.8177	4	0	0	7	2015-01-30	2015-02-03	6	0	1	8250	1	0	1	2	50	628	71	1
41	2014-08-29 16:55:21	2	3	66	258	45545	1319.0022	4	0	0	7	2015-01-30	2015-02-03	6	0	1	8250	1	0	1	2	50	628	38	1
42	2014-08-29 16:58:41	2	3	66	258	45545	1318.1384	4	0	0	7	2015-01-30	2015-02-03	2	0	1	8250	1	0	1	2	50	628	34	1
43	2014-08-29 16:59:41	2	3	66	258	45545	1321.0614	4	0	0	7	2015-01-30	2015-02-03	2	0	1	8250	1	0	1	2	50	628	71	1
44	2014-08-29 17:00:33	2	3	66	258	45545	1317.3999	4	0	0	7	2015-01-30	2015-02-03	2	0	1	8250	1	0	1	2	50	628	18	1
45	2014-12-07 18:18:26	2	3	66	258	4687	978.1868	4	0	0	1	2015-03-18	2015-03-22	2	2	1	11624	6	0	1	2	50	666	73	2
46	2014-12-07 18:19:10	2	3	66	258	4687	978.3691	4	0	0	1	2015-03-18	2015-03-22	2	2	1	11624	6	0	1	2	50	666	73	2
47	2014-12-07 18:25:20	2	3	66	258	4687	149.625	4	0	0	1	2015-01-02	2015-01-04	2	2	1	4475	1	0	1	2	50	1343	15	2
48	2014-01-26 23:56:46	2	3	66	174	24103	960.42	5	0	1	10	2014-03-21	2014-03-25	1	1	1	8281	1	0	1	2	50	663	9	0
49	2014-01-27 00:00:31	2	3	66	174	24103	960.42	5	0	1	10	2014-03-22	2014-03-26	1	1	1	8281	1	0	3	2	50	663	9	0
50	2014-01-27 00:03:44	2	3	66	174	24103	960.1149	5	0	1	10	2014-03-22	2014-03-26	2	1	1	8281	1	0	1	2	50	663	68	0
51	2014-01-27 00:05:12	2	3	66	174	24103	960.5072	5	0	1	10	2014-03-22	2014-03-26	2	1	1	8281	1	0	1	2	50	663	18	0
52	2014-01-27 00:06:18	2	3	66	174	24103	960.7861	5	0	1	10	2014-03-22	2014-03-26	1	1	1	8281	1	0	1	2	50	663	42	0
53	2014-01-27 00:08:48	2	3	66	174	24103	960.0054	5	0	1	10	2014-03-22	2014-03-26	1	1	1	8281	1	0	1	2	50	663	70	0
54	2014-01-27 00:10:17	2	3	66	174	24103	960.001	5	0	1	10	2014-03-22	2014-03-26	1	1	1	8281	1	0	12	2	50	663	41	0
55	2014-01-27 00:36:54	2	3	66	174	24103	960.001	5	0	1	10	2014-03-22	2014-03-26	1	1	1	8281	1	1	1	2	50	663	41	0
56	2014-01-27 01:17:09	2	3	66	174	24103	960.42	5	0	1	2	2014-03-22	2014-03-26	1	1	1	8281	1	0	1	2	50	663	9	1
57	2014-07-30 17:13:07	2	3	66	356	51532	836.9683	6	1	0	9	2014-12-03	2014-12-06	2	3	1	12175	6	0	1	2	50	366	28	0
58	2014-08-28 11:54:12	2	3	66	246	49795	2574.2075	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	13	1
59	2014-08-28 12:03:06	2	3	66	246	49795	2573.1667	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	28	1
60	2014-08-29 16:25:06	2	3	66	246	50661	2579.8433	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	28	2
61	2014-08-29 16:26:14	2	3	66	246	50661	2577.5509	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	91	2
62	2014-08-29 16:27:07	2	3	66	246	50661	2577.6926	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	13	2
63	2014-09-01 23:56:27	2	3	66	246	50661	2577.2399	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	2	2	50	366	70	3
64	2014-09-01 23:58:04	2	3	66	246	50661	2577.6926	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	13	3
65	2014-09-11 19:32:52	2	3	66	356	22202	756.9031	6	1	0	2	2014-12-08	2014-12-11	2	0	1	8250	1	0	1	2	50	628	1	4
66	2014-10-10 12:25:06	2	3	66	246	50661	2759.7778	6	0	0	9	2014-12-08	2014-12-11	2	0	1	8250	1	0	1	2	50	628	1	5
67	2014-11-03 01:01:07	2	3	66	462	49272	876.1516	6	1	0	2	2014-12-08	2014-12-11	2	0	1	8250	1	0	1	2	50	628	54	6
68	2014-11-03 11:09:56	2	3	66	246	50661	2755.8182	6	0	0	0	2014-12-08	2014-12-09	2	0	1	8250	1	0	1	2	50	628	54	7
69	2014-11-04 20:26:42	2	3	66	174	24103	225.6717	6	1	0	9	2014-12-08	2014-12-09	2	0	1	8250	1	0	1	2	50	628	54	8
70	2014-11-21 08:16:01	2	3	66	246	50661	2609.6874	6	0	0	0	2014-12-11	2014-12-12	2	0	1	12264	6	0	1	2	50	368	51	9
71	2014-11-21 08:18:12	2	3	66	246	50661	2610.2816	6	0	0	0	2014-12-11	2014-12-12	2	0	1	12264	6	0	1	2	50	368	47	9
72	2014-11-21 08:31:09	2	3	66	246	50661	2610.2808	6	0	0	0	2014-12-11	2014-12-12	2	0	1	12264	6	0	1	2	50	368	51	9
73	2014-11-24 20:36:28	2	3	66	246	50661	2609.9043	6	0	0	0	2014-12-11	2014-12-12	2	2	1	12264	6	0	2	2	50	368	17	10
74	2014-11-24 20:36:38	2	3	66	246	50661	2610.401	6	0	0	0	2014-12-11	2014-12-12	2	2	1	12264	6	0	1	2	50	368	16	10
75	2014-11-24 20:37:16	2	3	66	246	50661	2610.4181	6	0	0	0	2014-12-11	2014-12-12	2	2	1	12264	6	0	1	2	50	368	4	10
76	2014-11-24 21:00:40	2	3	66	246	50661	2607.7527	6	0	0	0	2014-12-11	2014-12-12	2	2	1	52512	3	0	1	2	50	368	16	10
77	2014-11-24 21:01:01	2	3	66	246	50661	2607.7383	6	0	0	0	2014-12-11	2014-12-12	2	2	1	52512	3	0	1	2	50	368	39	10
78	2014-08-27 09:45:42	2	3	66	318	31646	5378.6918	7	0	0	2	2014-10-31	2014-11-01	2	0	1	792	1	0	5	6	208	1480	64	0
79	2014-08-27 09:51:32	2	3	66	318	31646	5378.6918	7	0	0	2	2015-01-29	2015-02-08	2	0	1	792	1	0	2	6	208	1480	64	0
80	2014-08-27 09:58:02	2	3	66	318	31646	5378.6918	7	0	0	2	2015-01-30	2015-02-05	2	0	1	792	1	0	2	6	208	1480	64	0
81	2014-08-27 09:58:55	2	3	66	318	31646	5375.9045	7	0	0	2	2014-10-31	2014-11-01	2	0	1	792	1	0	1	6	208	1480	86	0
82	2014-08-27 10:01:09	2	3	66	318	31646	5380.6319	7	0	0	2	2014-10-31	2014-11-01	2	0	1	792	1	0	1	6	208	1480	64	0
83	2014-08-27 10:03:53	2	3	66	318	31646	5380.6319	7	0	1	2	2015-01-30	2015-02-05	2	0	1	792	1	0	1	6	208	1480	64	0
84	2014-09-10 12:20:39	2	3	66	318	31646	3936.1062	7	0	0	2	2014-10-12	2014-10-13	1	0	1	22616	6	0	1	6	204	1452	5	1
85	2014-09-24 11:23:59	2	3	66	318	878	830.2591	7	0	0	9	2014-12-04	2014-12-07	2	0	1	19535	1	0	2	2	50	593	5	2
86	2014-09-24 11:36:08	2	3	66	318	878	830.2591	7	0	0	9	2014-12-04	2014-12-07	2	0	1	19535	1	1	1	2	50	593	5	2
87	2014-09-24 11:57:49	2	3	66	318	878	830.2591	7	0	1	9	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	5	2
88	2014-10-06 18:28:52	2	3	66	318	33705	826.9443	7	0	0	0	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	91	3
89	2014-10-06 18:32:36	2	3	66	318	33705	826.9994	7	0	0	0	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	5	3
90	2014-10-29 05:57:44	2	3	66	318	8031	862.7599	7	0	0	2	2014-11-11	2014-11-12	2	0	1	19535	1	0	2	2	50	593	32	4
91	2014-10-29 05:58:44	2	3	66	318	8031	862.7337	7	0	0	2	2014-11-11	2014-11-12	2	0	1	19535	1	0	1	2	50	593	7	4
92	2014-10-31 07:14:03	2	3	66	318	8031	863.0788	7	0	1	4	2014-12-04	2014-12-07	2	0	1	19535	1	0	6	2	50	593	90	5
93	2014-11-06 10:14:59	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	32	6
94	2014-11-06 10:44:50	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	28	6
95	2014-11-06 10:45:59	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	42	6
96	2014-11-06 10:47:25	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	76	6
97	2014-11-06 11:26:14	2	3	66	318	41478	[null]	7	0	0	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	33	7
98	2014-11-06 11:27:14	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	33	7
99	2014-11-06 11:27:47	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	2	2	50	593	32	7
100	2014-11-07 11:01:05	2	3	66	318	41478	[null]	7	0	1	9	2014-12-04	2014-12-07	2	0	1	19535	1	0	2	2	50	593	73	8

Rows: 1-100 | Columns: 25

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.

In [5]:

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.

In [6]:

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

Out[6]:

	📅 date_time Timestamp	123 site_name Int	123 posa_continent Int	123 user_location_country Int	123 user_location_region Int	123 user_location_city Int	123 orig_destination_distance Numeric(11,5)	123 user_id Int	123 is_mobile Int	123 is_package Int	123 channel Int	📅 srch_ci Date	📅 srch_co Date	123 srch_adults_cnt Int	123 srch_children_cnt Int	123 srch_rm_cnt Int	123 srch_destination_id Int	123 srch_destination_type_id Int	123 is_booking Int	123 cnt Int	123 hotel_continent Int	123 hotel_country Int	123 hotel_market Int	123 hotel_cluster Int	123 session_id Integer	123 trip_duration Integer	123 mode_hotel_cluster Integer	123 mode_hotel_cluster_count Integer
1	2014-09-03 08:18:41	2	3	66	174	9045	53.6059	0	1	0	2	2014-09-06	2014-09-07	2	2	1	24693	6	0	1	2	50	1241	19	0	1	19	1
2	2014-12-03 11:32:43	2	3	66	174	42538	342.2163	0	1	0	2	2014-12-29	2014-12-30	3	2	1	8279	1	0	2	2	50	358	72	1	1	72	1
3	2014-12-03 11:30:21	2	3	66	174	42538	343.7968	0	1	0	2	2014-12-29	2014-12-30	2	1	1	8279	1	0	1	2	50	1230	56	1	1	72	1
4	2014-12-03 11:28:58	2	3	66	174	42538	343.9901	0	1	0	2	2014-12-29	2014-12-30	2	1	1	8279	1	0	1	2	50	1230	40	1	1	72	1
5	2014-12-03 11:28:11	2	3	66	174	42538	343.7866	0	1	0	2	2014-12-29	2014-12-30	2	1	1	8279	1	0	1	2	50	1230	98	1	1	72	1
6	2014-12-06 01:38:26	2	3	66	174	9045	88.1489	0	1	0	2	2014-12-19	2014-12-20	2	2	1	8279	1	0	1	2	50	358	28	2	1	28	1
7	2014-04-25 16:14:46	2	3	66	174	37449	5405.4889	1	1	0	2	2014-11-07	2014-11-09	5	1	2	62881	4	0	1	4	98	2052	20	0	2	20	3
8	2014-04-25 16:15:11	2	3	66	174	37449	5405.4889	1	1	0	2	2014-11-07	2014-11-09	3	1	2	62881	4	0	1	4	98	2052	20	0	2	20	3
9	2014-04-25 16:15:50	2	3	66	174	37449	5405.7409	1	1	0	2	2014-11-07	2014-11-09	3	1	2	62881	4	0	1	4	98	2052	60	0	2	20	3
10	2014-04-25 16:14:28	2	3	66	174	37449	5405.4889	1	1	0	2	2014-11-07	2014-11-09	3	1	1	62881	4	0	1	4	98	2052	20	0	2	20	3
11	2014-01-27 13:06:31	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	1	2	50	703	91	0	4	91	3
12	2014-01-27 12:56:00	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	2	2	50	703	96	0	4	91	3
13	2014-01-27 13:18:43	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	1	2	50	703	94	0	4	91	3
14	2014-01-27 13:14:21	2	3	66	311	25671	[null]	2	0	0	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	2	2	50	703	91	0	4	91	3
15	2014-01-27 13:39:30	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	11621	1	0	2	2	50	703	96	0	3	91	3
16	2014-01-27 13:54:07	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	11621	1	0	2	2	50	703	94	0	3	91	3
17	2014-01-27 13:04:55	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	1	2	50	703	91	0	4	91	3
18	2014-01-27 13:00:31	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	1	2	50	703	72	0	4	91	3
19	2014-01-27 13:22:39	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-12	2	0	1	11621	1	0	1	2	50	703	21	0	4	91	3
20	2014-01-28 12:37:49	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	2	2	50	686	18	1	3	18	2
21	2014-01-28 12:34:21	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	91	1	3	18	2
22	2014-01-28 12:32:35	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	91	1	3	18	2
23	2014-01-28 12:28:31	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	73	1	3	18	2
24	2014-01-28 12:31:18	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	68	1	3	18	2
25	2014-01-28 12:27:47	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	41	1	3	18	2
26	2014-01-28 12:20:02	2	3	66	311	25671	[null]	2	0	1	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	3	2	50	686	95	1	3	18	2
27	2014-01-28 12:44:16	2	3	66	311	25671	[null]	2	0	0	9	2014-04-08	2014-04-11	2	0	1	12008	1	0	1	2	50	686	18	1	3	18	2
28	2014-01-28 17:06:02	2	3	66	311	25671	[null]	2	0	1	2	2014-04-08	2014-04-11	2	0	1	8250	1	0	4	2	50	628	18	2	3	18	1
29	2014-01-28 17:11:35	2	3	66	311	25671	[null]	2	0	1	2	2014-04-08	2014-04-11	2	0	1	8250	1	0	1	2	50	628	1	2	3	18	1
30	2014-07-31 07:10:39	2	3	66	321	51642	1432.9656	3	0	0	9	2015-02-07	2015-02-10	3	2	2	28586	6	0	1	2	50	705	5	0	3	5	1
31	2014-09-08 09:58:50	2	3	66	467	5222	141.0454	3	0	0	9	2014-10-11	2014-10-12	2	0	1	8261	1	0	1	2	50	646	2	1	1	2	1
32	2014-09-08 09:56:21	2	3	66	467	5222	142.7467	3	0	0	9	2014-10-11	2014-10-12	2	0	1	8261	1	0	1	2	50	646	18	1	1	2	1
33	2014-09-09 10:49:48	2	3	66	467	5222	143.2998	3	0	0	9	2015-02-01	2015-02-02	2	2	1	27699	6	0	1	2	50	646	48	2	1	48	1
34	2014-09-22 13:22:25	2	3	66	467	5222	141.1273	3	0	0	3	2015-02-01	2015-02-02	3	0	1	8261	1	0	1	2	50	646	94	3	1	94	2
35	2014-09-22 13:19:02	2	3	66	467	5222	141.1273	3	0	0	3	2015-02-01	2015-02-02	2	0	1	27699	6	0	1	2	50	646	94	3	1	94	2
36	2014-09-30 12:56:51	2	3	66	467	5222	142.7467	3	0	0	9	2014-10-11	2014-10-12	2	0	1	8261	1	0	1	2	50	646	18	4	1	18	1
37	2014-10-20 11:19:21	2	3	66	467	5222	144.4084	3	0	0	9	2015-02-01	2015-02-02	2	2	1	27704	6	0	1	2	50	646	47	5	1	47	1
38	2014-08-14 18:11:08	2	3	66	258	45545	110.4682	4	0	0	9	2014-10-18	2014-10-20	2	0	1	8283	1	0	1	2	50	414	21	0	2	21	1
39	2014-08-29 16:59:41	2	3	66	258	45545	1321.0614	4	0	0	7	2015-01-30	2015-02-03	2	0	1	8250	1	0	1	2	50	628	71	1	4	71	3
40	2014-08-29 16:54:16	2	3	66	258	45545	1318.8177	4	0	0	7	2015-01-30	2015-02-03	6	0	1	8250	1	0	1	2	50	628	71	1	4	71	3
41	2014-08-29 17:00:33	2	3	66	258	45545	1317.3999	4	0	0	7	2015-01-30	2015-02-03	2	0	1	8250	1	0	1	2	50	628	18	1	4	71	3
42	2014-08-29 16:53:11	2	3	66	258	45545	1318.4152	4	0	0	7	2015-01-30	2015-02-03	6	0	1	8250	1	0	1	2	50	628	71	1	4	71	3
43	2014-08-29 16:55:21	2	3	66	258	45545	1319.0022	4	0	0	7	2015-01-30	2015-02-03	6	0	1	8250	1	0	1	2	50	628	38	1	4	71	3
44	2014-08-29 16:58:41	2	3	66	258	45545	1318.1384	4	0	0	7	2015-01-30	2015-02-03	2	0	1	8250	1	0	1	2	50	628	34	1	4	71	3
45	2014-12-07 18:19:10	2	3	66	258	4687	978.3691	4	0	0	1	2015-03-18	2015-03-22	2	2	1	11624	6	0	1	2	50	666	73	2	4	73	2
46	2014-12-07 18:18:26	2	3	66	258	4687	978.1868	4	0	0	1	2015-03-18	2015-03-22	2	2	1	11624	6	0	1	2	50	666	73	2	4	73	2
47	2014-12-07 18:25:20	2	3	66	258	4687	149.625	4	0	0	1	2015-01-02	2015-01-04	2	2	1	4475	1	0	1	2	50	1343	15	2	2	73	2
48	2014-01-27 00:00:31	2	3	66	174	24103	960.42	5	0	1	10	2014-03-22	2014-03-26	1	1	1	8281	1	0	3	2	50	663	9	0	4	9	2
49	2014-01-27 00:36:54	2	3	66	174	24103	960.001	5	0	1	10	2014-03-22	2014-03-26	1	1	1	8281	1	1	1	2	50	663	41	0	4	9	2
50	2014-01-27 00:08:48	2	3	66	174	24103	960.0054	5	0	1	10	2014-03-22	2014-03-26	1	1	1	8281	1	0	1	2	50	663	70	0	4	9	2
51	2014-01-27 00:06:18	2	3	66	174	24103	960.7861	5	0	1	10	2014-03-22	2014-03-26	1	1	1	8281	1	0	1	2	50	663	42	0	4	9	2
52	2014-01-27 00:10:17	2	3	66	174	24103	960.001	5	0	1	10	2014-03-22	2014-03-26	1	1	1	8281	1	0	12	2	50	663	41	0	4	9	2
53	2014-01-27 00:05:12	2	3	66	174	24103	960.5072	5	0	1	10	2014-03-22	2014-03-26	2	1	1	8281	1	0	1	2	50	663	18	0	4	9	2
54	2014-01-27 00:03:44	2	3	66	174	24103	960.1149	5	0	1	10	2014-03-22	2014-03-26	2	1	1	8281	1	0	1	2	50	663	68	0	4	9	2
55	2014-01-26 23:56:46	2	3	66	174	24103	960.42	5	0	1	10	2014-03-21	2014-03-25	1	1	1	8281	1	0	1	2	50	663	9	0	4	9	2
56	2014-01-27 01:17:09	2	3	66	174	24103	960.42	5	0	1	2	2014-03-22	2014-03-26	1	1	1	8281	1	0	1	2	50	663	9	1	4	9	1
57	2014-07-30 17:13:07	2	3	66	356	51532	836.9683	6	1	0	9	2014-12-03	2014-12-06	2	3	1	12175	6	0	1	2	50	366	28	0	3	28	1
58	2014-08-28 11:54:12	2	3	66	246	49795	2574.2075	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	13	1	3	13	1
59	2014-08-28 12:03:06	2	3	66	246	49795	2573.1667	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	28	1	3	13	1
60	2014-08-29 16:27:07	2	3	66	246	50661	2577.6926	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	13	2	3	13	1
61	2014-08-29 16:26:14	2	3	66	246	50661	2577.5509	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	91	2	3	13	1
62	2014-08-29 16:25:06	2	3	66	246	50661	2579.8433	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	28	2	3	13	1
63	2014-09-01 23:58:04	2	3	66	246	50661	2577.6926	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	1	2	50	366	13	3	3	13	1
64	2014-09-01 23:56:27	2	3	66	246	50661	2577.2399	6	1	0	2	2014-12-03	2014-12-06	2	0	1	12175	6	0	2	2	50	366	70	3	3	13	1
65	2014-09-11 19:32:52	2	3	66	356	22202	756.9031	6	1	0	2	2014-12-08	2014-12-11	2	0	1	8250	1	0	1	2	50	628	1	4	3	1	1
66	2014-10-10 12:25:06	2	3	66	246	50661	2759.7778	6	0	0	9	2014-12-08	2014-12-11	2	0	1	8250	1	0	1	2	50	628	1	5	3	1	1
67	2014-11-03 01:01:07	2	3	66	462	49272	876.1516	6	1	0	2	2014-12-08	2014-12-11	2	0	1	8250	1	0	1	2	50	628	54	6	3	54	1
68	2014-11-03 11:09:56	2	3	66	246	50661	2755.8182	6	0	0	0	2014-12-08	2014-12-09	2	0	1	8250	1	0	1	2	50	628	54	7	1	54	1
69	2014-11-04 20:26:42	2	3	66	174	24103	225.6717	6	1	0	9	2014-12-08	2014-12-09	2	0	1	8250	1	0	1	2	50	628	54	8	1	54	1
70	2014-11-21 08:16:01	2	3	66	246	50661	2609.6874	6	0	0	0	2014-12-11	2014-12-12	2	0	1	12264	6	0	1	2	50	368	51	9	1	51	2
71	2014-11-21 08:31:09	2	3	66	246	50661	2610.2808	6	0	0	0	2014-12-11	2014-12-12	2	0	1	12264	6	0	1	2	50	368	51	9	1	51	2
72	2014-11-21 08:18:12	2	3	66	246	50661	2610.2816	6	0	0	0	2014-12-11	2014-12-12	2	0	1	12264	6	0	1	2	50	368	47	9	1	51	2
73	2014-11-24 21:00:40	2	3	66	246	50661	2607.7527	6	0	0	0	2014-12-11	2014-12-12	2	2	1	52512	3	0	1	2	50	368	16	10	1	16	2
74	2014-11-24 20:37:16	2	3	66	246	50661	2610.4181	6	0	0	0	2014-12-11	2014-12-12	2	2	1	12264	6	0	1	2	50	368	4	10	1	16	2
75	2014-11-24 21:01:01	2	3	66	246	50661	2607.7383	6	0	0	0	2014-12-11	2014-12-12	2	2	1	52512	3	0	1	2	50	368	39	10	1	16	2
76	2014-11-24 20:36:28	2	3	66	246	50661	2609.9043	6	0	0	0	2014-12-11	2014-12-12	2	2	1	12264	6	0	2	2	50	368	17	10	1	16	2
77	2014-11-24 20:36:38	2	3	66	246	50661	2610.401	6	0	0	0	2014-12-11	2014-12-12	2	2	1	12264	6	0	1	2	50	368	16	10	1	16	2
78	2014-08-27 09:58:02	2	3	66	318	31646	5378.6918	7	0	0	2	2015-01-30	2015-02-05	2	0	1	792	1	0	2	6	208	1480	64	0	6	64	5
79	2014-08-27 10:01:09	2	3	66	318	31646	5380.6319	7	0	0	2	2014-10-31	2014-11-01	2	0	1	792	1	0	1	6	208	1480	64	0	1	64	5
80	2014-08-27 10:03:53	2	3	66	318	31646	5380.6319	7	0	1	2	2015-01-30	2015-02-05	2	0	1	792	1	0	1	6	208	1480	64	0	6	64	5
81	2014-08-27 09:51:32	2	3	66	318	31646	5378.6918	7	0	0	2	2015-01-29	2015-02-08	2	0	1	792	1	0	2	6	208	1480	64	0	10	64	5
82	2014-08-27 09:45:42	2	3	66	318	31646	5378.6918	7	0	0	2	2014-10-31	2014-11-01	2	0	1	792	1	0	5	6	208	1480	64	0	1	64	5
83	2014-08-27 09:58:55	2	3	66	318	31646	5375.9045	7	0	0	2	2014-10-31	2014-11-01	2	0	1	792	1	0	1	6	208	1480	86	0	1	64	5
84	2014-09-10 12:20:39	2	3	66	318	31646	3936.1062	7	0	0	2	2014-10-12	2014-10-13	1	0	1	22616	6	0	1	6	204	1452	5	1	1	5	1
85	2014-09-24 11:23:59	2	3	66	318	878	830.2591	7	0	0	9	2014-12-04	2014-12-07	2	0	1	19535	1	0	2	2	50	593	5	2	3	5	3
86	2014-09-24 11:57:49	2	3	66	318	878	830.2591	7	0	1	9	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	5	2	3	5	3
87	2014-09-24 11:36:08	2	3	66	318	878	830.2591	7	0	0	9	2014-12-04	2014-12-07	2	0	1	19535	1	1	1	2	50	593	5	2	3	5	3
88	2014-10-06 18:28:52	2	3	66	318	33705	826.9443	7	0	0	0	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	91	3	3	91	1
89	2014-10-06 18:32:36	2	3	66	318	33705	826.9994	7	0	0	0	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	5	3	3	91	1
90	2014-10-29 05:57:44	2	3	66	318	8031	862.7599	7	0	0	2	2014-11-11	2014-11-12	2	0	1	19535	1	0	2	2	50	593	32	4	1	32	1
91	2014-10-29 05:58:44	2	3	66	318	8031	862.7337	7	0	0	2	2014-11-11	2014-11-12	2	0	1	19535	1	0	1	2	50	593	7	4	1	32	1
92	2014-10-31 07:14:03	2	3	66	318	8031	863.0788	7	0	1	4	2014-12-04	2014-12-07	2	0	1	19535	1	0	6	2	50	593	90	5	3	90	1
93	2014-11-06 10:47:25	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	76	6	3	76	1
94	2014-11-06 10:45:59	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	42	6	3	76	1
95	2014-11-06 10:44:50	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	28	6	3	76	1
96	2014-11-06 10:14:59	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	32	6	3	76	1
97	2014-11-06 11:26:14	2	3	66	318	41478	[null]	7	0	0	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	33	7	3	33	2
98	2014-11-06 11:27:14	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	33	7	3	33	2
99	2014-11-06 11:27:47	2	3	66	318	41478	[null]	7	0	1	2	2014-12-04	2014-12-07	2	0	1	19535	1	0	2	2	50	593	32	7	3	33	2
100	2014-11-07 11:03:11	2	3	66	318	41478	[null]	7	0	1	9	2014-12-04	2014-12-07	2	0	1	19535	1	0	1	2	50	593	32	8	3	32	1

Rows: 1-100 | Columns: 28

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.

In [7]:

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

Let's look at the missing values.

In [8]:

expedia.count_percent()

Out[8]:

	count	percent
"user_id"	12242331.0	100.0
"session_id"	12242331.0	100.0
"mode_hotel_cluster_count"	12242331.0	100.0
"end_session_date_time"	12242331.0	100.0
"session_duration"	12242331.0	100.0
"is_booking"	12242331.0	100.0
"nb_click_session"	12242331.0	100.0
"srch_family_cnt"	12242331.0	100.0
"trip_duration"	12226336.0	99.869
"avg_distance"	7966565.0	65.074

Rows: 1-10 | Columns: 3

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

In [9]:

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

4275766 elements were filled.
15995 elements were filled.

Out[9]:

	123 user_id Integer 100%	123 session_id Integer 100%	123 mode_hotel_cluster_count Integer 100%	📅 end_session_date_time Datetime 100%	123 session_duration Float 100%	123 is_booking Integer 100%	123 trip_duration Float 100%	123 avg_distance Float 100%	123 nb_click_session Integer 100%	123 srch_family_cnt Float 100%
1	0	0	1	2014-09-03 08:18:41	0.0	0	1.0	53.6059	1	4.0
2	0	1	1	2014-12-03 11:32:43	272.0	0	1.0	343.44745	5	3.0
3	0	2	1	2014-12-06 01:38:26	0.0	0	1.0	88.1489	1	4.0
4	1	0	3	2014-04-25 16:15:50	82.0	0	2.0	5405.5519	4	4.0
5	2	0	3	2014-01-27 13:54:07	3487.0	0	3.77777777777778	1847.26115380414	13	2.0
6	2	1	2	2014-01-28 12:44:16	1454.0	0	3.0	1847.26115380414	11	2.0
7	2	2	1	2014-01-28 17:11:35	333.0	0	3.0	1847.26115380414	5	2.0
8	3	0	1	2014-07-31 07:10:39	0.0	0	3.0	1432.9656	1	5.0
9	3	1	1	2014-09-08 09:58:50	149.0	0	1.0	141.89605	2	2.0
10	3	2	1	2014-09-09 10:49:48	0.0	0	1.0	143.2998	1	4.0
11	3	3	2	2014-09-22 13:22:25	203.0	0	1.0	141.1273	2	2.5
12	3	4	1	2014-09-30 12:56:51	0.0	0	1.0	142.7467	1	2.0
13	3	5	1	2014-10-20 11:19:21	0.0	0	1.0	144.4084	1	4.0
14	4	0	1	2014-08-14 18:11:08	0.0	0	2.0	110.4682	1	2.0
15	4	1	3	2014-08-29 17:00:33	442.0	0	4.0	1318.8058	6	4.0
16	4	2	2	2014-12-07 18:25:20	414.0	0	3.33333333333333	702.0603	3	4.0
17	5	0	2	2014-01-27 00:36:54	2408.0	1	4.0	960.28195	21	2.0
18	5	1	1	2014-01-27 01:17:09	0.0	0	4.0	960.42	1	2.0
19	6	0	1	2014-07-30 17:13:07	0.0	0	3.0	836.9683	1	5.0
20	6	1	1	2014-08-28 12:03:06	534.0	0	3.0	2573.6871	2	2.0
21	6	2	1	2014-08-29 16:27:07	121.0	0	3.0	2578.36226666667	3	2.0
22	6	3	1	2014-09-01 23:58:04	97.0	0	3.0	2577.46625	3	2.0
23	6	4	1	2014-09-11 19:32:52	0.0	0	3.0	756.9031	1	2.0
24	6	5	1	2014-10-10 12:25:06	0.0	0	3.0	2759.7778	1	2.0
25	6	6	1	2014-11-03 01:01:07	0.0	0	3.0	876.1516	1	2.0
26	6	7	1	2014-11-03 11:09:56	0.0	0	1.0	2755.8182	1	2.0
27	6	8	1	2014-11-04 20:26:42	0.0	0	1.0	225.6717	1	2.0
28	6	9	2	2014-11-21 08:31:09	908.0	0	1.0	2610.08326666667	3	2.0
29	6	10	2	2014-11-24 21:01:01	1473.0	0	1.0	2609.24288	6	4.0
30	7	0	5	2014-08-27 10:03:53	1091.0	0	4.16666666666667	5378.87395	12	2.0
31	7	1	1	2014-09-10 12:20:39	0.0	0	1.0	3936.1062	1	1.0
32	7	2	3	2014-09-24 11:57:49	2030.0	1	3.0	830.2591	4	2.0
33	7	3	1	2014-10-06 18:32:36	224.0	0	3.0	826.97185	2	2.0
34	7	4	1	2014-10-29 05:58:44	60.0	0	1.0	862.7468	3	2.0
35	7	5	1	2014-10-31 07:14:03	0.0	0	3.0	863.0788	6	2.0
36	7	6	1	2014-11-06 10:47:25	1946.0	0	3.0	1847.26115380414	4	2.0
37	7	7	2	2014-11-06 11:27:47	93.0	0	3.0	1847.26115380414	4	2.0
38	7	8	1	2014-11-07 11:03:11	126.0	0	3.0	1847.26115380414	3	2.0
39	7	9	1	2014-11-10 16:27:13	0.0	0	5.0	1847.26115380414	1	2.0
40	7	10	1	2014-11-12 11:40:28	43.0	0	1.0	1847.26115380414	2	2.0
41	7	11	1	2014-11-20 07:56:48	126.0	0	7.0	5457.39715	2	2.0
42	7	12	1	2014-11-20 09:02:53	0.0	0	3.0	866.3349	1	2.0
43	7	13	1	2014-12-01 15:07:49	0.0	0	3.0	862.3329	1	2.0
44	7	14	1	2014-12-18 11:04:14	1587.0	0	2.0	616.5975	2	2.0
45	7	15	1	2014-12-18 12:16:29	0.0	0	2.0	628.2538	1	2.0
46	7	16	1	2014-12-18 13:19:09	0.0	0	2.0	628.2538	1	2.0
47	8	0	1	2014-02-08 19:20:31	0.0	0	3.0	1658.1959	1	1.0
48	8	1	1	2014-03-26 10:39:48	0.0	0	3.0	450.4073	1	2.0
49	8	2	3	2014-03-26 12:19:10	1519.0	0	3.0	453.88375	6	2.0
50	8	3	1	2014-04-06 23:13:49	530.0	0	1.0	453.4165	5	2.0
51	8	4	1	2014-04-19 12:30:50	0.0	0	2.0	1318.2732	1	1.0
52	8	5	1	2014-04-19 13:11:53	0.0	0	2.0	1318.1185	1	1.0
53	8	6	1	2014-04-21 09:53:49	0.0	0	3.0	1318.1185	1	1.0
54	8	7	1	2014-08-13 20:53:48	1364.0	0	2.0	1067.91735	2	2.0
55	8	8	3	2014-08-16 21:43:25	532.0	0	9.33333333333333	10.5665666666667	3	2.0
56	8	9	1	2014-08-18 06:11:31	0.0	0	7.0	7.4843	1	2.0
57	8	10	2	2014-09-11 21:39:10	108.0	0	2.0	20.6983	2	2.0
58	8	11	2	2014-09-14 02:44:58	331.0	1	1.0	18.4069	3	2.0
59	9	0	2	2014-01-26 23:45:07	2596.0	0	5.88888888888889	2353.9245	9	1.0
60	9	1	1	2014-01-27 00:36:29	0.0	1	4.0	2354.1447	1	1.0
61	10	0	1	2014-08-11 13:03:57	203.0	0	3.0	2958.16045	2	3.0
62	10	1	1	2014-08-13 10:58:33	0.0	0	6.0	1804.0766	1	3.0
63	10	2	1	2014-08-25 08:51:52	0.0	0	5.0	1830.0046	2	2.0
64	10	3	1	2014-08-26 06:25:35	0.0	0	5.0	1830.0046	1	2.0
65	10	4	1	2014-09-15 11:25:12	0.0	0	6.0	1847.26115380414	1	2.0
66	10	5	1	2014-10-14 08:21:10	0.0	0	7.0	1847.26115380414	1	5.0
67	11	0	3	2014-01-27 00:55:26	1878.0	0	4.66666666666667	1995.5654	10	4.0
68	11	1	22	2014-02-15 01:15:30	2897.0	0	5.17391304347826	1987.6269173913	36	4.0
69	11	2	1	2014-02-15 02:40:52	0.0	0	1.0	1995.702	1	4.0
70	11	3	5	2014-02-15 04:30:13	2823.0	0	5.83333333333333	3086.09186666667	6	4.0
71	11	4	1	2014-02-15 05:27:52	0.0	0	6.0	1987.6041	3	4.0
72	11	5	3	2014-02-28 00:23:19	707.0	0	1.0	6128.47568333333	10	4.0
73	11	6	5	2014-07-23 01:13:02	1444.0	1	1.83333333333333	367.2513	7	3.5
74	11	7	5	2014-10-04 06:17:31	201.0	0	2.0	367.4875	7	3.0
75	11	8	1	2014-10-06 01:05:53	0.0	0	1.0	254.3031	1	3.0
76	11	9	1	2014-10-06 02:28:28	123.0	0	1.0	217.392233333333	3	4.0
77	11	10	5	2014-10-13 03:02:34	2764.0	1	1.5	200.137025	14	3.0
78	11	11	1	2014-10-24 13:01:21	0.0	0	1.0	217.4356	1	4.0
79	11	12	1	2014-10-24 22:46:57	0.0	0	1.0	217.4356	1	3.0
80	11	13	3	2014-10-25 05:00:48	1685.0	1	1.0	215.8254	13	3.5
81	12	0	1	2014-08-11 07:46:59	0.0	0	4.0	2234.2641	3	2.0
82	12	1	2	2014-08-11 08:24:33	141.0	1	4.0	2234.2641	2	2.0
83	13	0	4	2014-07-09 12:04:53	1547.0	1	4.25	1847.26115380414	8	1.0
84	13	1	1	2014-07-10 05:42:46	0.0	0	6.0	1847.26115380414	7	1.0
85	13	2	2	2014-07-11 06:55:20	141.0	0	6.0	1847.26115380414	12	1.0
86	13	3	1	2014-07-11 07:40:33	0.0	0	6.0	1847.26115380414	1	1.0
87	13	4	3	2014-07-14 09:36:15	592.0	1	2.66666666666667	1847.26115380414	3	2.0
88	13	5	1	2014-07-15 09:01:43	0.0	0	3.0	1847.26115380414	2	2.0
89	13	6	1	2014-07-16 07:02:12	0.0	0	0.0	1847.26115380414	1	2.0
90	13	7	4	2014-07-17 13:33:44	216.0	0	2.5	1847.26115380414	5	2.0
91	13	8	1	2014-07-17 20:53:38	0.0	0	3.0	1847.26115380414	1	2.0
92	13	9	1	2014-07-17 22:16:18	0.0	0	3.0	1847.26115380414	1	2.0
93	13	10	1	2014-07-20 15:27:20	0.0	0	3.0	1847.26115380414	1	2.0
94	13	11	1	2014-07-21 07:26:26	0.0	0	3.0	1847.26115380414	1	2.0
95	13	12	2	2014-07-22 06:47:22	69.0	0	3.0	1847.26115380414	2	1.5
96	13	13	2	2014-08-18 13:14:50	825.0	0	4.66666666666667	4880.02743333333	14	1.0
97	13	14	3	2014-08-18 15:05:22	1818.0	0	3.0	4881.4641	6	2.0
98	13	15	1	2014-08-19 10:23:12	648.0	0	3.0	4880.41435	10	2.0
99	13	16	1	2014-08-19 14:14:09	855.0	0	3.0	4925.1201	2	2.0
100	13	17	2	2014-08-21 04:07:52	2210.0	0	2.8	1847.26115380414	10	2.0

Rows: 1-100 of 12242331 | Columns: 10

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

In [10]:

%matplotlib inline
expedia.corr(method = "spearman")

Out[10]:

	"user_id"	"session_id"	"mode_hotel_cluster_count"	"session_duration"	"is_booking"	"trip_duration"	"avg_distance"	"nb_click_session"	"srch_family_cnt"
"user_id"	1.0	0.0125372839769691	0.000585488528099556	-0.000541597877304615	0.00168318615437918	-0.00172294332358923	0.00722625140700152	-0.00130288969225954	-0.00145643651427301
"session_id"	0.0125372839769691	1.0	-0.0362824718948203	-0.0456184717754819	-0.0579097807340728	0.0206225348881192	0.0424225822484464	-0.0614509659970033	-0.0267334768989548
"mode_hotel_cluster_count"	0.000585488528099556	-0.0362824718948203	1.0	0.693149583808771	0.496196271565582	-0.0229387237074547	-0.00118724174477281	0.654237637118915	0.0430835831263844
"session_duration"	-0.000541597877304615	-0.0456184717754819	0.693149583808771	1.0	0.331866631464122	0.0125253855406324	0.0188542087982699	0.834960977340327	0.0545771115310364
"is_booking"	0.00168318615437918	-0.0579097807340728	0.496196271565582	0.331866631464122	1.0	-0.191911129719119	-0.0660072678069069	0.200140132943157	-0.0646466731033034
"trip_duration"	-0.00172294332358923	0.0206225348881192	-0.0229387237074547	0.0125253855406324	-0.191911129719119	1.0	0.288326587959107	0.0494004219009955	0.0716986783665811
"avg_distance"	0.00722625140700152	0.0424225822484464	-0.00118724174477281	0.0188542087982699	-0.0660072678069069	0.288326587959107	1.0	0.0322018254928665	-0.0205418178720803
"nb_click_session"	-0.00130288969225954	-0.0614509659970033	0.654237637118915	0.834960977340327	0.200140132943157	0.0494004219009955	0.0322018254928665	1.0	0.0753040443807521
"srch_family_cnt"	-0.00145643651427301	-0.0267334768989548	0.0430835831263844	0.0545771115310364	-0.0646466731033034	0.0716986783665811	-0.0205418178720803	0.0753040443807521	1.0

Rows: 1-9 | Columns: 10

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 logistic regression model.

In [11]:

from verticapy.learn.linear_model import LogisticRegression
model_logit = LogisticRegression("expedia_logit", 
                                 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")

Out[11]:


=======
details
=======
       predictor        |coefficient|std_err | z_value  |p_value 
------------------------+-----------+--------+----------+--------
       Intercept        | -1.59409  | 0.00275|-579.61668| 0.00000
      avg_distance      | -0.00004  | 0.00000|-81.38289 | 0.00000
    session_duration    |  0.00056  | 0.00000|415.75575 | 0.00000
    nb_click_session    | -0.16990  | 0.00031|-543.77330| 0.00000
mode_hotel_cluster_count|  1.13834  | 0.00125|910.63579 | 0.00000
       session_id       | -0.00583  | 0.00005|-124.80774| 0.00000
    srch_family_cnt     | -0.20765  | 0.00087|-238.93988| 0.00000
     trip_duration      | -0.24810  | 0.00050|-497.93583| 0.00000


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


===========
call_string
===========
logistic_reg('public.expedia_logit', '"public"."_verticapy_tmp_view_dbadmin_150246_4175658595_"', '"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)

===============
Additional Info
===============
       Name       | Value  
------------------+--------
 iteration_count  |   56   
rejected_row_count|   0    
accepted_row_count|12242331

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

In [12]:

model_logit.features_importance()

Out[12]:

	importance	sign
mode_hotel_cluster_count	42.12	1
trip_duration	42.09	-1
nb_click_session	12.1	-1
session_duration	2.64	1
srch_family_cnt	0.65	-1
session_id	0.31	-1
avg_distance	0.09	-1

Rows: 1-7 | Columns: 3

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.

In [13]:

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

Out[13]:

	123 user_id Integer	123 session_id Integer	123 mode_hotel_cluster_count Integer	📅 end_session_date_time Datetime	123 session_duration Float	123 is_booking Integer	123 trip_duration Float	123 avg_distance Float	123 nb_click_session Integer	123 srch_family_cnt Float	123 booking_prob_logit Float
1	0	0	1	2014-09-03 08:18:41	0.0	0	1.0	53.6059	1	4.0	0.153605784760313
2	0	1	1	2014-12-03 11:32:43	272.0	0	1.0	343.44745	5	3.0	0.114769694559958
3	0	2	1	2014-12-06 01:38:26	0.0	0	1.0	88.1489	1	4.0	0.151909565323304
4	1	0	3	2014-04-25 16:15:50	82.0	0	2.0	5405.5519	4	4.0	0.409667557696363
5	2	0	3	2014-01-27 13:54:07	3487.0	0	3.77777777777778	1847.26115380414	13	2.0	0.537789354058044
6	2	1	2	2014-01-28 12:44:16	1454.0	0	3.0	1847.26115380414	11	2.0	0.166867978942862
7	2	2	1	2014-01-28 17:11:35	333.0	0	3.0	1847.26115380414	5	2.0	0.0858210195354118
8	3	0	1	2014-07-31 07:10:39	0.0	0	3.0	1432.9656	1	5.0	0.0781213517098348
9	3	1	1	2014-09-08 09:58:50	149.0	0	1.0	141.89605	2	2.0	0.199954136104512
10	3	2	1	2014-09-09 10:49:48	0.0	0	1.0	143.2998	1	4.0	0.15161252923937
11	3	3	2	2014-09-22 13:22:25	203.0	0	1.0	141.1273	2	2.5	0.417461623963391
12	3	4	1	2014-09-30 12:56:51	0.0	0	1.0	142.7467	1	2.0	0.211093932210174
13	3	5	1	2014-10-20 11:19:21	0.0	0	1.0	144.4084	1	4.0	0.149370471025827
14	4	0	1	2014-08-14 18:11:08	0.0	0	2.0	110.4682	1	2.0	0.176278424893632
15	4	1	3	2014-08-29 17:00:33	442.0	0	4.0	1318.8058	6	4.0	0.303033318892354
16	4	2	2	2014-12-07 18:25:20	414.0	0	3.33333333333333	702.0603	3	4.0	0.215521000283274
17	5	0	2	2014-01-27 00:36:54	2408.0	1	4.0	960.28195	21	2.0	0.0486391653944605
18	5	1	1	2014-01-27 01:17:09	0.0	0	4.0	960.42	1	2.0	0.111120003993773
19	6	0	1	2014-07-30 17:13:07	0.0	0	3.0	836.9683	1	5.0	0.0799361826415578
20	6	1	1	2014-08-28 12:03:06	534.0	0	3.0	2573.6871	2	2.0	0.145902571229701
21	6	2	1	2014-08-29 16:27:07	121.0	0	3.0	2578.36226666667	3	2.0	0.101902835247903
22	6	3	1	2014-09-01 23:58:04	97.0	0	3.0	2577.46625	3	2.0	0.10014591874866
23	6	4	1	2014-09-11 19:32:52	0.0	0	3.0	756.9031	1	2.0	0.137023874419858
24	6	5	1	2014-10-10 12:25:06	0.0	0	3.0	2759.7778	1	2.0	0.126766078502665
25	6	6	1	2014-11-03 01:01:07	0.0	0	3.0	876.1516	1	2.0	0.135066898027681
26	6	7	1	2014-11-03 11:09:56	0.0	0	1.0	2755.8182	1	2.0	0.190747110120129
27	6	8	1	2014-11-04 20:26:42	0.0	0	1.0	225.6717	1	2.0	0.206666819276909
28	6	9	2	2014-11-21 08:31:09	908.0	0	1.0	2610.08326666667	3	2.0	0.46519458222375
29	6	10	2	2014-11-24 21:01:01	1473.0	0	1.0	2609.24288	6	4.0	0.320559312392026
30	7	0	5	2014-08-27 10:03:53	1091.0	0	4.16666666666667	5378.87395	12	2.0	0.731208803627096
31	7	1	1	2014-09-10 12:20:39	0.0	0	1.0	3936.1062	1	1.0	0.222369617950368
32	7	2	3	2014-09-24 11:57:49	2030.0	1	3.0	830.2591	4	2.0	0.746809728657831
33	7	3	1	2014-10-06 18:32:36	224.0	0	3.0	826.97185	2	2.0	0.132303404351664
34	7	4	1	2014-10-29 05:58:44	60.0	0	1.0	862.7468	3	2.0	0.160515864407895
35	7	5	1	2014-10-31 07:14:03	0.0	0	3.0	863.0788	6	2.0	0.0629717632058175
36	7	6	1	2014-11-06 10:47:25	1946.0	0	3.0	1847.26115380414	4	2.0	0.212768759915639
37	7	7	2	2014-11-06 11:27:47	93.0	0	3.0	1847.26115380414	4	2.0	0.227549632713416
38	7	8	1	2014-11-07 11:03:11	126.0	0	3.0	1847.26115380414	3	2.0	0.101759156668488
39	7	9	1	2014-11-10 16:27:13	0.0	0	5.0	1847.26115380414	1	2.0	0.0823229504633143
40	7	10	1	2014-11-12 11:40:28	43.0	0	1.0	1847.26115380414	2	2.0	0.172178789723069
41	7	11	1	2014-11-20 07:56:48	126.0	0	7.0	5457.39715	2	2.0	0.0403551481630567
42	7	12	1	2014-11-20 09:02:53	0.0	0	3.0	866.3349	1	2.0	0.131078774199718
43	7	13	1	2014-12-01 15:07:49	0.0	0	3.0	862.3329	1	2.0	0.130435118963389
44	7	14	1	2014-12-18 11:04:14	1587.0	0	2.0	616.5975	2	2.0	0.285305806952283
45	7	15	1	2014-12-18 12:16:29	0.0	0	2.0	628.2538	1	2.0	0.160988939727481
46	7	16	1	2014-12-18 13:19:09	0.0	0	2.0	628.2538	1	2.0	0.160202966761629
47	8	0	1	2014-02-08 19:20:31	0.0	0	3.0	1658.1959	1	1.0	0.161519234636658
48	8	1	1	2014-03-26 10:39:48	0.0	0	3.0	450.4073	1	2.0	0.14064814658706
49	8	2	3	2014-03-26 12:19:10	1519.0	0	3.0	453.88375	6	2.0	0.615157634255203
50	8	3	1	2014-04-06 23:13:49	530.0	0	1.0	453.4165	5	2.0	0.15370436297465
51	8	4	1	2014-04-19 12:30:50	0.0	0	2.0	1318.2732	1	1.0	0.196554253629183
52	8	5	1	2014-04-19 13:11:53	0.0	0	2.0	1318.1185	1	1.0	0.195636150971596
53	8	6	1	2014-04-21 09:53:49	0.0	0	3.0	1318.1185	1	1.0	0.158728164011895
54	8	7	1	2014-08-13 20:53:48	1364.0	0	2.0	1067.91735	2	2.0	0.264581377504695
55	8	8	3	2014-08-16 21:43:25	532.0	0	9.33333333333333	10.5665666666667	3	2.0	0.23752210930637
56	8	9	1	2014-08-18 06:11:31	0.0	0	7.0	7.4843	1	2.0	0.0557026167192674
57	8	10	2	2014-09-11 21:39:10	108.0	0	2.0	20.6983	2	2.0	0.361953805824228
58	8	11	2	2014-09-14 02:44:58	331.0	1	1.0	18.4069	3	2.0	0.408901454285757
59	9	0	2	2014-01-26 23:45:07	2596.0	0	5.88888888888889	2353.9245	9	1.0	0.240904076884289
60	9	1	1	2014-01-27 00:36:29	0.0	1	4.0	2354.1447	1	1.0	0.126749295648676
61	10	0	1	2014-08-11 13:03:57	203.0	0	3.0	2958.16045	2	3.0	0.10229971561409
62	10	1	1	2014-08-13 10:58:33	0.0	0	6.0	1804.0766	1	3.0	0.0563330008245311
63	10	2	1	2014-08-25 08:51:52	0.0	0	5.0	1830.0046	2	2.0	0.0731306610860517
64	10	3	1	2014-08-26 06:25:35	0.0	0	5.0	1830.0046	1	2.0	0.0850608359883184
65	10	4	1	2014-09-15 11:25:12	0.0	0	6.0	1847.26115380414	1	2.0	0.0672237056428636
66	10	5	1	2014-10-14 08:21:10	0.0	0	7.0	1847.26115380414	1	5.0	0.0291131307102079
67	11	0	3	2014-01-27 00:55:26	1878.0	0	4.66666666666667	1995.5654	10	4.0	0.291216526340216
68	11	1	22	2014-02-15 01:15:30	2897.0	0	5.17391304347826	1987.6269173913	36	4.0	0.999999947661436
69	11	2	1	2014-02-15 02:40:52	0.0	0	1.0	1995.702	1	4.0	0.141910547298366
70	11	3	5	2014-02-15 04:30:13	2823.0	0	5.83333333333333	3086.09186666667	6	4.0	0.904468660118949
71	11	4	1	2014-02-15 05:27:52	0.0	0	6.0	1987.6041	3	4.0	0.0325740395685856
72	11	5	3	2014-02-28 00:23:19	707.0	0	1.0	6128.47568333333	10	4.0	0.30087585260552
73	11	6	5	2014-07-23 01:13:02	1444.0	1	1.83333333333333	367.2513	7	3.5	0.923560522218305
74	11	7	5	2014-10-04 06:17:31	201.0	0	2.0	367.4875	7	3.0	0.863709639231675
75	11	8	1	2014-10-06 01:05:53	0.0	0	1.0	254.3031	1	3.0	0.174511485669671
76	11	9	1	2014-10-06 02:28:28	123.0	0	1.0	217.392233333333	3	4.0	0.115446768081075
77	11	10	5	2014-10-13 03:02:34	2764.0	1	1.5	200.137025	14	3.0	0.901854863743191
78	11	11	1	2014-10-24 13:01:21	0.0	0	1.0	217.4356	1	4.0	0.144601568257166
79	11	12	1	2014-10-24 22:46:57	0.0	0	1.0	217.4356	1	3.0	0.171396220731711
80	11	13	3	2014-10-25 05:00:48	1685.0	1	1.0	215.8254	13	3.5	0.378479686174866
81	12	0	1	2014-08-11 07:46:59	0.0	0	4.0	2234.2641	3	2.0	0.0782311917400202
82	12	1	2	2014-08-11 08:24:33	141.0	1	4.0	2234.2641	2	2.0	0.252638417951801
83	13	0	4	2014-07-09 12:04:53	1547.0	1	4.25	1847.26115380414	8	1.0	0.756628224060677
84	13	1	1	2014-07-10 05:42:46	0.0	0	6.0	1847.26115380414	7	1.0	0.0315408287604047
85	13	2	2	2014-07-11 06:55:20	141.0	0	6.0	1847.26115380414	12	1.0	0.0447101520215156
86	13	3	1	2014-07-11 07:40:33	0.0	0	6.0	1847.26115380414	1	1.0	0.0819112691735906
87	13	4	3	2014-07-14 09:36:15	592.0	1	2.66666666666667	1847.26115380414	3	2.0	0.614915133006404
88	13	5	1	2014-07-15 09:01:43	0.0	0	3.0	1847.26115380414	2	2.0	0.112886909051649
89	13	6	1	2014-07-16 07:02:12	0.0	0	0.0	1847.26115380414	1	2.0	0.239900791653794
90	13	7	4	2014-07-17 13:33:44	216.0	0	2.5	1847.26115380414	5	2.0	0.74613064994227
91	13	8	1	2014-07-17 20:53:38	0.0	0	3.0	1847.26115380414	1	2.0	0.129073806922214
92	13	9	1	2014-07-17 22:16:18	0.0	0	3.0	1847.26115380414	1	2.0	0.128419799454148
93	13	10	1	2014-07-20 15:27:20	0.0	0	3.0	1847.26115380414	1	2.0	0.12776861964389
94	13	11	1	2014-07-21 07:26:26	0.0	0	3.0	1847.26115380414	1	2.0	0.127120260189066
95	13	12	2	2014-07-22 06:47:22	69.0	0	3.0	1847.26115380414	2	1.5	0.3054941900462
96	13	13	2	2014-08-18 13:14:50	825.0	0	4.66666666666667	4880.02743333333	14	1.0	0.0533732862884125
97	13	14	3	2014-08-18 15:05:22	1818.0	0	3.0	4881.4641	6	2.0	0.594519517619808
98	13	15	1	2014-08-19 10:23:12	648.0	0	3.0	4880.41435	10	2.0	0.0376841554129484
99	13	16	1	2014-08-19 14:14:09	855.0	0	3.0	4925.1201	2	2.0	0.145334180009601
100	13	17	2	2014-08-21 04:07:52	2210.0	0	2.8	1847.26115380414	10	2.0	0.25829708322599

Rows: 1-100 of 12242331 | Columns: 11

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.

In [14]:

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

Out[14]:

<AxesSubplot:xlabel='"is_booking"', ylabel='"booking_prob_logit"'>

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

In [15]:

model_logit.score(method = "best_cutoff")

Out[15]:

0.2204

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

In [16]:

model_logit.roc_curve()
model_logit.report(cutoff = 0.22)

Out[16]:

	value
auc	0.840145448458096
prc_auc	0.5465504635612507
accuracy	0.8021478916065903
log_loss	0.182579215050228
precision	0.5299008517143481
recall	0.7976312038512661
f1_score	0.6367687356440429
mcc	0.5283725198559831
informedness	0.601033970517129
markedness	0.4644954086351514
csi	0.46710250292335903
cutoff	0.22

Rows: 1-12 | Columns: 2

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!

VerticaPy

Python API for Vertica Data Science at Scale

About the Author

Badr Ouali
Head of Data Science

Badr Ouali works as a Lead Data Scientist for Vertica worldwide. He can embrace data projects end to end through a clear understanding of the “big picture” as well as attention to details, resulting in achieving great business outcomes – a distinctive differentiator in his role. Badr enjoys sharing knowledge and insights related to data analytics with colleagues & peers and has a sweet spot for Python. He loves helping customers finding the best value from their data and empower them to solve their use-cases.