I'm trying to recreate the code used in this article https://blog.getdbt.com/modeling-marketing-attribution/ but the main difference is that I have to use SQLite. I cannot figure out why I am unable to create fields that will form the foundation for me to calculate the number of attribution points to assign to each session. I'm not sure if I am using the "partition by" function properly.
The data translated from the article to mine is:
- Sessions = Impressions
- Customer_ID = Onovative_ID
Any help on this would be much appreciated!
QUERY:
select
*,
count(*) over (
partition by on_id
) as total_sessions,
row_number() over (
partition by on_id
order by sessions.started_at
) as session_number
from
//step 2
(
select impressions.*, loans.opendt
from (
select "Onovative Id" as on_id, impdt as impression_date, "Email" as medium , Template as type
from Emails
UNION
select "Onovative Id", impdt, Type, "CIQ"
from Outbound
UNION
select "CIQ ID", impdt, Channnel, "Channel Detail"
from ImageWorks
) as impressions
join Listing loans on loans."Onovative Id" = impressions.on_id
where
impression_date <= opendt
and impression_date > date(opendt,'-30 day')
--order by on_id
) as two
WORKING QUERY:
select impressions.*, loans.opendt
from (
select "Onovative Id" as on_id, impdt as impression_date, "Email" as medium , Template as type
from Emails
UNION
select "Onovative Id", impdt, Type, "CIQ"
from Outbound
UNION
select "CIQ ID", impdt, Channnel, "Channel Detail"
from ImageWorks
) as impressions
join Listing loans on loans."Onovative Id" = impressions.on_id
where
impression_date <= opendt
and impression_date > date(opendt,'-30 day')
OUTPUT of WORKING QUERY:
on_id impression_date medium type opendt
10001 2020-07-02 00:00 Letter ImageWorks Refi 2020-07-29 00:00
70001 2020-07-20 00:00 Postcard ImageWorks SmartTrack 2020-07-29 00:00
03301 2020-08-25 11:57 Email CIQ 2020-09-21 00:00
02201 2020-05-11 00:00 Postcard ImageWorks SmartTrack 2020-06-01 00:00
50001 2020-09-16 10:21 Email CIQ 2020-09-28 00:00
10001 2020-09-16 10:21 Email product offer e-statements email 2020-09-28 00:00
00601 2020-09-19 09:30 Email CIQ 2020-09-28 00:00
00901 2020-09-16 10:21 Email CIQ 2020-10-05 00:00
00501 2020-09-16 10:21 Email product offer e-statements email 2020-10-05 00:00
00101 2020-09-19 09:30 Email CIQ 2020-10-05 00:00
00401 2020-10-01 09:42 Email CIQ 2020-10-05 00:00
00801 2020-10-04 16:14 Email CIQ 2020-10-05 00:00
00301 2020-07-18 11:11 Email CIQ 2020-07-28 00:00
00012 2020-06-08 11:43 Direct Mail Large Postcard CIQ 2020-07-03 00:00
**SYNTAX ERROR: (doesn’t give line number)**
``` [14:41:37] Error while executing SQL query on database 'save_pandas3': near "(": syntax error```
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…