Attribution_MLE Example (Multiple Inputs) - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
8.10
1.1
Published
October 2019
Language
English (United States)
Last Update
2019-12-31
dita:mapPath
ima1540829771750.ditamap
dita:ditavalPath
jsj1481748799576.ditaval
dita:id
B700-4003
lifecycle
previous
Product Category
Teradata Vantageā„¢

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.