Attribution_MLE Example (Single Input): Single-Window Model

Attribution_MLE Example (Single Input): Single-Window Model - 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

Product Category

Teradata Vantage™

This example uses the single-input Attribution_MLE function.

Input

attribution_sample_table3
user_id	event	time_stamp
1	impression	2001-09-27 23:00:07
1	impression	2001-09-27 23:00:09
1	impression	2001-09-27 23:00:11
1	impression	2001-09-27 23:00:13
1	Email	2001-09-27 23:00:15
1	impression	2001-09-27 23:00:17
1	impression	2001-09-27 23:00:19
1	SocialNetwork	2001-09-27 23:00:21
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:47
2	impression	2001-09-27 23:00:51
2	impression	2001-09-27 23:00:53
2	impression	2001-09-27 23:00:55
2	SocialNetwork	2001-09-27 23:00:59

SQL Call

SELECT * FROM Attribution_MLE (
  ON attribution_sample_table3 PARTITION BY user_id
    ORDER BY time_stamp
  USING
  EventColumn ('event')
  ConversionEvents ('socialnetwork', 'paidsearch')
  ExcludeEvents('email')
  TimeColumn ('time_stamp')
  WindowSize ('rows:10&seconds:20')
  FirstModel ('SIMPLE', 'UNIFORM:NA')
) AS dt ORDER BY 1, 3;

Output

user_id	event	time_stamp	attribution	time_to_conversion
1	impression	2001-09-27 23:00:07	0.166667	-14
1	impression	2001-09-27 23:00:09	0.166667	-12
1	impression	2001-09-27 23:00:11	0.166667	-10
1	impression	2001-09-27 23:00:13	0.166667	-8
1	impression	2001-09-27 23:00:17	0.166667	-4
1	impression	2001-09-27 23:00:19	0.166667	-2
1	SocialNetwork	2001-09-27 23:00:21
1	PaidSearch	2001-09-27 23:00:23
2	impression	2001-09-27 23:00:29	0
2	impression	2001-09-27 23:00:31	0
2	impression	2001-09-27 23:00:33	0
2	impression	2001-09-27 23:00:47	0.25	-12
2	impression	2001-09-27 23:00:51	0.25	-8
2	impression	2001-09-27 23:00:53	0.25	-6
2	impression	2001-09-27 23:00:55	0.25	-4
2	SocialNetwork	2001-09-27 23:00:59

 user_id event         time_stamp                 attribution        time_to_conversion 
 ------- ------------- -------------------------- ------------------ ------------------ 
       1 impression    2001-09-27 23:00:07.000000 0.1666666716337204              -14.0
       1 impression    2001-09-27 23:00:09.000000 0.1666666716337204              -12.0
       1 impression    2001-09-27 23:00:11.000000 0.1666666716337204              -10.0
       1 impression    2001-09-27 23:00:13.000000 0.1666666716337204               -8.0
       1 impression    2001-09-27 23:00:17.000000 0.1666666716337204               -4.0
       1 impression    2001-09-27 23:00:19.000000 0.1666666716337204               -2.0
       1 socialnetwork 2001-09-27 23:00:21.000000               NULL               NULL
       1 paidsearch    2001-09-27 23:00:23.000000               NULL               NULL
       2 impression    2001-09-27 23:00:29.000000                0.0               NULL
       2 impression    2001-09-27 23:00:31.000000                0.0               NULL
       2 impression    2001-09-27 23:00:33.000000                0.0               NULL
       2 impression    2001-09-27 23:00:47.000000               0.25              -12.0
       2 impression    2001-09-27 23:00:51.000000               0.25               -8.0
       2 impression    2001-09-27 23:00:53.000000               0.25               -6.0
       2 impression    2001-09-27 23:00:55.000000               0.25               -4.0
       2 socialnetwork 2001-09-27 23:00:59.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.