RetailOperation-CustomerAnalyticsAndMarketingEngine/JourneyPerformance.sql at main · duan-n2d/RetailOperation-CustomerAnalyticsAndMarketingEngine · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
WITH delivered_customer AS (
    -- Assuming delivered Journey 'Journey Test'
    SELECT
        Delivery.allocated_audience AS customer_id,
        Delivery.tracked_time,
        Delivery.journey_id,
        Delivery.id AS event_id,
        Journey.name AS journey_name,
        Journey.tracked_type,
        Journey.start_time,
        Journey.end_time,
        Journey.duration,
        Journey.tracked_type,
        Journey.status
    FROM
        Delivery
        JOIN Journey ON Delivery.journey_id = Journey.id
    WHERE
        Journey.name = 'Journey Test'
        AND Delivery.tracked_time BETWEEN timestamp '2025-01-01 00:00:00'
        and now()
),
trans_use_coupon AS (
    SELECT
        Promotion_Code.transaction_id,
        Promotion_Code.id AS promotion_code,
        Promotion_Code.journey_id,
        Transaction.customer_id,
        Transaction.tracked_time AS data_created,
        Transaction.revenue,
        Transaction.store_id,
        Promotion_Code.data_created AS allocated_time
    FROM
        Promotion_Code
        JOIN Transaction ON Promotion_Code.transaction_id = Transaction.id
    WHERE
        Promotion_Code.journey_id = 'Journey Test'
        AND Transaction.tracked_time BETWEEN timestamp '2025-01-01 00:00:00'
        and now()
),
trans_no_coupon AS (
    -- Customers are attracted by the journey but do not use the promotion code
    SELECT
        tnc.transaction_id,
        tnc.customer_id,
        tnc.data_created,
        tnc.revenue,
        tnc.store_id,
        tnc.promotion_code,
        tnc.journey_id,
        tnc.start_time,
        tnc.end_time,
        tnc.duration,
        tnc.tracked_type,
        tnc.tracked_time,
    FROM
        (
            SELECT
                Transaction.id AS transaction_id,
                Transaction.customer_id,
                Transaction.tracked_time AS date_created,
                Transaction.revenue,
                Transaction.store_id,
                NULL AS promotion_code,
                delivered_customer.journey_id,
                delivered_customer.start_time,
                delivered_customer.end_time,
                delivered_customer.duration,
                delivered_customer.tracked_type,
                delivered_customer.tracked_time,
                ROW_NUMBER() OVER (
                    PARTITION BY Transaction.id
                    ORDER BY
                        delivered_customer.tracked_time DESC
                ) AS index_transaction_story
            FROM
                Transaction
                JOIN delivered_customer ON Transaction.customer_id = delivered_customer.customer_id
            WHERE
                Transaction.tracked_time BETWEEN timestamp '2025-01-01 00:00:00'
                and now()
                AND Transaction.tracked_time BETWEEN delivered_customer.start_time
                AND delivered_customer.end_time
                AND Transaction.id NOT IN (
                    SELECT
                        trans_use_coupon.transaction_id
                    FROM
                        trans_use_coupon
                )
        ) AS tnc
    WHERE
        tnc.index_transaction_story = 1
),
trans_convert AS (
    SELECT
        tc.transaction_id,
        tc.promotion_code,
        tc.journey_id,
        tc.customer_id,
        tc.data_created,
        tc.revenue,
        tc.store_id,
        tc.allocated_time,
        Store.name AS store_name,
        Store.area AS store_area,
        Store.city AS store_city
    FROM
        (
            SELECT
                trans_use_coupon.transaction_id,
                trans_use_coupon.promotion_code,
                trans_use_coupon.journey_id,
                trans_use_coupon.customer_id,
                trans_use_coupon.data_created,
                trans_use_coupon.revenue,
                trans_use_coupon.store_id,
                trans_use_coupon.allocated_time
            FROM
                trans_use_coupon
            UNION
            ALL
            SELECT
                tnc.transaction_id,
                tnc.promotion_code,
                tnc.journey_id,
                tnc.customer_id,
                tnc.data_created,
                tnc.revenue,
                tnc.store_id,
                NULL AS allocated_time
            FROM
                trans_no_coupon tnc
        ) AS tc
        LEFT JOIN Store ON tc.store_id = Store.id
) -- Final result: Delivered LEFT JOIN Transaction Convert
SELECT
    dc.customer_id,
    dc.event_id,
    dc.journey_name,
    dc.tracked_type,
    dc.start_time,
    dc.end_time,
    dc.duration,
    dc.status,
    tc.transaction_id,
    tc.promotion_code,
    tc.revenue,
    tc.store_id,
    tc.store_name,
    tc.store_area,
    tc.store_city
FROM
    delivered_customer dc
    JOIN trans_convert tc ON dc.journey_id = tc.journey_id
    AND dc.customer_id = tc.customer_id