Using the single-input Attribution_MLE function, this example specifies one distribution model for regular events and one distribution model for each type of optional event.
Input
user_id | event | time_stamp |
---|---|---|
1 | impression | 2001-09-27 23:00:01 |
1 | impression | 2001-09-27 23:00:03 |
1 | impression | 2001-09-27 23:00:05 |
1 | impression | 2001-09-27 23:00:07 |
1 | impression | 2001-09-27 23:00:09 |
1 | impression | 2001-09-27 23:00:11 |
1 | impression | 2001-09-27 23:00:13 |
1 | 2001-09-27 23:00:15 | |
1 | impression | 2001-09-27 23:00:17 |
1 | impression | 2001-09-27 23:00:19 |
1 | SocialNetwork | 2001-09-27 23:00:20 |
1 | Direct | 2001-09-27 23:00:21 |
1 | Referral | 2001-09-27 23:00:22 |
1 | PaidSearch | 2001-09-27 23:00:23 |
2 | impression | 2001-09-27 23:00:29 |
2 | impression | 2001-09-27 23:00:31 |
2 | impression | 2001-09-27 23:00:33 |
2 | impression | 2001-09-27 23:00:36 |
2 | impression | 2001-09-27 23:00:38 |
2 | impression | 2001-09-27 23:00:43 |
2 | impression | 2001-09-27 23:00:47 |
2 | OrganicSearch | 2001-09-27 23:00:49 |
2 | impression | 2001-09-27 23:00:51 |
SQL Call
SELECT * FROM Attribution_MLE ( ON attribution_sample_table PARTITION BY user_id ORDER BY time_stamp USING EventColumn ('event') ConversionEvents ('socialnetwork', 'paidsearch') OptionalEvents ('organicsearch', 'direct', 'referral') TimeColumn ('time_stamp') WindowSize ('rows:10&seconds:20') FirstModel ('EVENT_REGULAR', 'email:0.19:LAST_CLICK:NA', 'impression:0.81:UNIFORM:NA') SecondModel ('EVENT_OPTIONAL', 'organicsearch:0.5:UNIFORM:NA', 'direct:0.3:UNIFORM:NA', 'referral:0.2:UNIFORM:NA') ) AS dt ORDER BY 1, 3;
Output
user_id event time_stamp attribution time_to_conversion ------- ------------- -------------------------- ------------------- ------------------ 1 impression 2001-09-27 23:00:01.000000 0.09000000357627869 -19.0 1 impression 2001-09-27 23:00:03.000000 0.09000000357627869 -17.0 1 impression 2001-09-27 23:00:05.000000 0.09000000357627869 -15.0 1 impression 2001-09-27 23:00:07.000000 0.09000000357627869 -13.0 1 impression 2001-09-27 23:00:09.000000 0.09000000357627869 -11.0 1 impression 2001-09-27 23:00:11.000000 0.09000000357627869 -9.0 1 impression 2001-09-27 23:00:13.000000 0.09000000357627869 -7.0 1 email 2001-09-27 23:00:15.000000 0.1899999976158142 -5.0 1 impression 2001-09-27 23:00:17.000000 0.09000000357627869 -3.0 1 impression 2001-09-27 23:00:19.000000 0.09000000357627869 -1.0 1 socialnetwork 2001-09-27 23:00:20.000000 NULL NULL 1 direct 2001-09-27 23:00:21.000000 0.6000000238418579 -2.0 1 referral 2001-09-27 23:00:22.000000 0.4000000059604645 -1.0 1 paidsearch 2001-09-27 23:00:23.000000 NULL NULL 2 impression 2001-09-27 23:00:29.000000 0.0 NULL 2 impression 2001-09-27 23:00:31.000000 0.0 NULL 2 impression 2001-09-27 23:00:33.000000 0.0 NULL 2 impression 2001-09-27 23:00:36.000000 0.0 NULL 2 impression 2001-09-27 23:00:38.000000 0.0 NULL 2 impression 2001-09-27 23:00:43.000000 0.20000000298023224 -16.0 2 impression 2001-09-27 23:00:47.000000 0.20000000298023224 -12.0 2 impression 2001-09-27 23:00:51.000000 0.20000000298023224 -8.0 2 impression 2001-09-27 23:00:53.000000 0.20000000298023224 -6.0 2 impression 2001-09-27 23:00:55.000000 0.20000000298023224 -4.0 2 socialnetwork 2001-09-27 23:00:59.000000 NULL NULL
Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.