16.20 - Attribution Example - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ - NewSQL Engine Analytic Functions

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
July 2019
Content Type
Programming Reference
Publication ID
B035-1206-162K
Language
English (United States)

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