'SQL query based on datediff
I am looking to construct a query using the ad exposure and conversion logs, I want to identify the ad exposure that gets "last touch" credit for the trial start. The conversion should have happened within 7 days after the ad exposure. Where Last credit means it was the last impression or click prior to conversion
This are my tables
ad_exposure_and_conversion
| user_id | ad_id | Exposure Time | event_type |
|---|---|---|---|
| 12368 | 574012 | 9/5/20 12:37 AM | Click |
| 12368 | 574012 | 9/12/20 6:02 PM | Impression |
| 12368 | 574012 | 9/13/20 5:53 AM | Impression |
| 13953 | 449785 | 9/14/20 10:30 PM | Impression |
| 17000 | 96336 | 9/1/20 11:29 AM | Impression |
| 17000 | 96336 | 9/7/20 8:10 AM | Impression |
| 17000 | 96336 | 9/10/20 9:43 PM | Impression |
| 17000 | 96336 | 9/12/20 1:36 AM | Impression |
| 22362 | 449785 | 9/2/20 5:41 AM | Impression |
| 22362 | 449785 | 9/4/20 3:31 PM | Impression |
| 22362 | 449785 | 9/4/20 6:05 PM | Impression |
| 22362 | 449785 | 9/5/20 6:22 PM | Impression |
| 22362 | 449785 | 9/7/20 3:48 PM | Impression |
| 22362 | 449785 | 9/8/20 12:04 AM | Impression |
| 22362 | 449785 | 9/11/20 6:00 PM | Impression |
| 22362 | 449785 | 9/12/20 6:43 PM | Impression |
| 22362 | 449785 | 9/14/20 6:05 AM | Impression |
| 23437 | 96336 | 9/1/20 8:13 AM | Impression |
| 23437 | 96336 | 9/2/20 6:14 PM | Impression |
| 23437 | 96336 | 9/3/20 3:23 PM | Impression |
| 23437 | 96336 | 9/3/20 10:06 PM | Impression |
| 23437 | 96336 | 9/3/20 11:53 PM | Impression |
| 23437 | 96336 | 9/4/20 8:07 AM | Impression |
| 23437 | 96336 | 9/6/20 5:06 PM | Click |
| 23437 | 96336 | 9/8/20 9:17 AM | Impression |
| 23437 | 96336 | 9/8/20 10:32 AM | Impression |
| 23437 | 96336 | 9/8/20 12:51 PM | Impression |
| 23437 | 96336 | 9/9/20 1:30 AM | Impression |
| 23437 | 96336 | 9/10/20 9:49 PM | Impression |
| 23437 | 96336 | 9/12/20 6:27 PM | Impression |
| 23437 | 96336 | 9/13/20 6:22 AM | Impression |
| 23437 | 96336 | 9/14/20 3:39 AM | Impression |
I dont quite understand the last credit part as I dont have ad experience. But this is what I have done so far
select ad_id
from ad_exposure_and_conversion
where DATEDIFF(DAY,a.Exposure Time,GETDATE() )>7
Can someone share your thoughts
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
