This example uses models to assign attribution weights to these events and channels:
Event Type |
Channels |
conversion |
SocialNetwork, PaidSearch |
excluding |
Email |
optional |
Direct, Referral, OrganicSearch |
Input
attribution_sample_table1
user_id |
event |
time_stamp |
1 |
impression |
2001-09-27 23:00:01 |
1 |
impression |
2001-09-27 23:00:05 |
1 |
Email |
2001-09-27 23:00:15 |
2 |
impression |
2001-09-27 23:00:31 |
2 |
impression |
2001-09-27 23:00:51 |
attribution_sample_table2
user_id |
event |
time_stamp |
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 |
ConversionEventTable
conversion_events |
PaidSearch |
SocialNetwork |
ExcludedEventTable
excluding_events |
Email |
OptionalEventTable
optional_events |
Direct |
OrganicSearch |
Referral |
The following two model tables apply the distribution models by rows and by seconds, respectively.
FirstModelTable
id |
model |
0 |
SEGMENT_ROWS |
1 |
3:0.5:EXPONENTIAL:0.5,SECOND |
2 |
4:0.3:WEIGHTED:0.4,0.3,0.2,0.1 |
3 |
3:0.2:FIRST_CLICK:NA |
SecondModelTable
id |
model |
0 |
SEGMENT_SECONDS |
1 |
6:0.5:UNIFORM:NA |
2 |
8:0.3:LAST_CLICK:NA |
3 |
6:0.2:FIRST_CLICK:NA |
SQL Call
SELECT * FROM Attribution_MLE (
ON attribution_sample_table1 AS input1
PARTITION BY user_id
ORDER BY time_stamp
ON attribution_sample_table2 AS input2
PARTITION BY user_id
ORDER BY time_stamp
ON conversion_event_table AS ConversionEventTable DIMENSION
ON excluding_event_table AS ExcludedEventTable DIMENSION
ON optional_event_table AS OptionalEventTable DIMENSION
ON model1_table AS FirstModelTable DIMENSION
ON model2_table AS SecondModelTable DIMENSION
USING
EventColumn ('event')
TimeColumn ('time_stamp')
WindowSize ('rows:10&seconds:20')
) AS dt ;
Output
user_id event time_stamp attribution time_to_conversion
------- ------------- -------------------------- ------------------ ------------------
1 impression 2001-09-27 23:00:19.000000 0.7142857313156128 -1.0
1 impression 2001-09-27 23:00:01.000000 0.2857142984867096 -19.0
1 impression 2001-09-27 23:00:05.000000 0.0 NULL
1 socialnetwork 2001-09-27 23:00:20.000000 NULL NULL
1 referral 2001-09-27 23:00:22.000000 0.5 -1.0
1 direct 2001-09-27 23:00:21.000000 0.5 -2.0
1 paidsearch 2001-09-27 23:00:23.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.