Attribution Function Example | Teradata Vantage - 17.05 - Attribution Example - Teradata Database

Teradata Vantage™ - Advanced SQL Engine Analytic Functions

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
17.05
created_date
June 2020
category
Programming Reference
featnum
B035-1206-170K

Event Type Channels

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 (
   ON attribution_sample_table1 AS InputTable1
     PARTITION BY user_id ORDER BY time_stamp
   ON attribution_sample_table2 AS InputTable2
     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 FirstModel DIMENSION
   ON model2_table AS SecondModel DIMENSION
  USING
   EventColumn ('event')
   TimeColumn ('time_stamp')
   WindowSize ('rows:10&seconds:20')
  ) AS dt ORDER BY user_id, time_stamp;

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