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 |
conversion_event_table
conversion_events |
PaidSearch |
SocialNetwork |
excluding_event_table
excluding_events |
Email |
optional_event_table
optional_events |
Direct |
OrganicSearch |
Referral |
The following two model tables apply the distribution models by rows and by seconds, respectively.
model1_table
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 |
model2_table
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@coprocessor (
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 conversion DIMENSION
ON excluding_event_table AS excluding DIMENSION
ON optional_event_table AS optional DIMENSION
ON model1_table AS model1 DIMENSION
ON model2_table AS model2 DIMENSION
USING
EventColumn ('event')
TimestampColumn ('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:01 |
0.285714 |
-19 |
1 |
impression |
2001-09-27 23:00:05 |
0 |
|
1 |
impression |
2001-09-27 23:00:19 |
0.714286 |
-1 |
1 |
SocialNetwork |
2001-09-27 23:00:20 |
|
|
1 |
Direct |
2001-09-27 23:00:21 |
0.5 |
-2 |
1 |
Referral |
2001-09-27 23:00:22 |
0.5 |
-1 |
1 |
PaidSearch |
2001-09-27 23:00:23 |
|
|