Meeting Title: Catalyst Refunds Query Debugging Date: 2025-12-01 Meeting participants: Casie Aviles, Ashwini Sharma
WEBVTT
1 00:00:36.170 ⇒ 00:00:37.420 Ashwini Sharma: Hey, Kessie.
2 00:00:37.770 ⇒ 00:00:40.320 Casie Aviles: Hey, Shrini, you know, thank you for…
3 00:00:40.600 ⇒ 00:00:42.399 Casie Aviles: Hopping on the fall, so…
4 00:00:42.670 ⇒ 00:00:43.600 Ashwini Sharma: No problem.
5 00:00:44.220 ⇒ 00:00:48.689 Casie Aviles: Yeah, I’m just… I’m just gonna share… wait, let me share my screen, so…
6 00:00:50.460 ⇒ 00:00:53.559 Casie Aviles: You can see… yeah, let me know if you can see it now.
7 00:00:54.580 ⇒ 00:00:55.970 Ashwini Sharma: I can see it now.
8 00:00:56.950 ⇒ 00:01:03.370 Casie Aviles: Okay, let me also just go to… Linear…
9 00:01:03.770 ⇒ 00:01:11.110 Casie Aviles: But basically, what I’m… so my goal here is to be able to, like, get…
10 00:01:11.990 ⇒ 00:01:15.160 Casie Aviles: The Catalyst-related, refunds, so…
11 00:01:16.370 ⇒ 00:01:18.439 Casie Aviles: That’s what I was trying to do.
12 00:01:20.250 ⇒ 00:01:31.189 Casie Aviles: But, so one of the things that, Awash… Suggested is to join… To join the table…
13 00:01:31.660 ⇒ 00:01:34.229 Casie Aviles: I think Catalyst Successful Order.
14 00:01:34.490 ⇒ 00:01:38.310 Casie Aviles: Yeah, coddle is successful orders and order refunds.
15 00:01:38.930 ⇒ 00:01:41.999 Casie Aviles: And so this is the query that I ran.
16 00:01:42.150 ⇒ 00:01:42.910 Ashwini Sharma: Okay.
17 00:01:43.800 ⇒ 00:01:47.270 Casie Aviles: But it’s giving me just one record.
18 00:01:47.660 ⇒ 00:01:54.279 Casie Aviles: So… I’m not sure if this is accurate, or if I’m missing anything.
19 00:01:54.820 ⇒ 00:02:01.860 Casie Aviles: Because… What Henry mentioned is Catalyst started just around September.
20 00:02:02.050 ⇒ 00:02:05.040 Casie Aviles: But I don’t think it’s that…
21 00:02:05.530 ⇒ 00:02:09.149 Casie Aviles: that it’s really just one refund, but I’m not sure.
22 00:02:10.060 ⇒ 00:02:17.000 Casie Aviles: And then also, just one more query that I tried was… this…
23 00:02:17.980 ⇒ 00:02:20.200 Casie Aviles: And I was able to get, like.
24 00:02:21.020 ⇒ 00:02:28.770 Casie Aviles: 5 refunds, but that’s still, like, low, and… the one that… I got from…
25 00:02:29.100 ⇒ 00:02:33.389 Casie Aviles: This query here, which is order refunds and catalysts.
26 00:02:33.780 ⇒ 00:02:38.789 Casie Aviles: Is for this one, no, the record, the fourth record, which is…
27 00:02:39.070 ⇒ 00:02:42.820 Casie Aviles: Shipped, so it’s the only one that has shipped status.
28 00:02:42.970 ⇒ 00:02:46.930 Casie Aviles: The rest are different statuses, so that’s why I think…
29 00:02:48.000 ⇒ 00:02:50.089 Casie Aviles: I was able to get just one.
30 00:02:50.250 ⇒ 00:02:59.240 Casie Aviles: But, yeah, this is still just 5, so I’m not sure if, I’m missing anything with how I’m querying it. So that’s all, really, yeah.
31 00:02:59.240 ⇒ 00:03:12.029 Ashwini Sharma: Okay, what’s there in the order reference? Like, you’re joining Catalyst successful orders with order reference, right? Yes. Can we see, like, what’s the volume of records in order reference?
32 00:03:13.360 ⇒ 00:03:14.779 Casie Aviles: Okay, alright.
33 00:03:25.570 ⇒ 00:03:29.130 Casie Aviles: Yeah, so I think we have… this much.
34 00:03:30.980 ⇒ 00:03:33.559 Ashwini Sharma: And how many of them have been shipped?
35 00:03:36.270 ⇒ 00:03:39.400 Casie Aviles: For that, I haven’t checked that one.
36 00:03:41.220 ⇒ 00:03:44.349 Ashwini Sharma: Okay, but it looks like it’s still a lot, right?
37 00:03:44.730 ⇒ 00:03:47.069 Casie Aviles: Yeah, there’s… there’s a lot of shipped as well.
38 00:03:51.830 ⇒ 00:03:59.599 Ashwini Sharma: Yeah, Catalyst has been quite recent, so maybe if we can… if we can filter out the records from order reference, that…
39 00:04:00.300 ⇒ 00:04:10.280 Ashwini Sharma: Somehow mapped to, to the order creation date, which is closer to Catalyst thing, right?
40 00:04:10.940 ⇒ 00:04:12.020 Casie Aviles: Hmm.
41 00:04:13.250 ⇒ 00:04:17.399 Ashwini Sharma: Yeah, so it should be maybe September, rather than September 2025.
42 00:04:20.120 ⇒ 00:04:22.430 Casie Aviles: Oh, yeah.
43 00:04:22.820 ⇒ 00:04:27.430 Casie Aviles: Like, this is… It’s mostly just…
44 00:04:27.580 ⇒ 00:04:31.269 Casie Aviles: past September, the refunds that I found.
45 00:04:31.850 ⇒ 00:04:36.589 Casie Aviles: And so 45… 454005 is around November.
46 00:04:49.980 ⇒ 00:04:51.569 Casie Aviles: Air Force shipped.
47 00:04:51.700 ⇒ 00:04:53.969 Casie Aviles: We have… 4,000.
48 00:04:54.420 ⇒ 00:04:57.520 Ashwini Sharma: Right. Now, if you add one more filter where,
49 00:04:57.920 ⇒ 00:05:02.589 Ashwini Sharma: by date, right? And then look at, orders that are created.
50 00:05:03.840 ⇒ 00:05:06.149 Ashwini Sharma: After September.
51 00:05:07.370 ⇒ 00:05:08.660 Ashwini Sharma: 2025?
52 00:05:12.240 ⇒ 00:05:12.940 Casie Aviles: Okay.
53 00:06:23.140 ⇒ 00:06:24.630 Casie Aviles: Oh, sorry, hold on.
54 00:06:44.110 ⇒ 00:06:47.539 Ashwini Sharma: So now we have only 118 records, right?
55 00:06:50.830 ⇒ 00:06:53.949 Ashwini Sharma: And out of these 118 records.
56 00:06:54.240 ⇒ 00:06:56.890 Ashwini Sharma: Only 5 are sort of matching.
57 00:06:59.260 ⇒ 00:07:05.419 Casie Aviles: Yeah, there’s… When, when, when, for example, like, for…
58 00:07:05.890 ⇒ 00:07:10.050 Casie Aviles: If it’s just order refunds and catalyst successful orders.
59 00:07:10.520 ⇒ 00:07:14.400 Casie Aviles: Like, it’s only this one that’s… I’m matching.
60 00:07:15.690 ⇒ 00:07:16.350 Casie Aviles: I’m the one, though.
61 00:07:17.330 ⇒ 00:07:20.089 Casie Aviles: Yeah, for this one, I used…
62 00:07:20.360 ⇒ 00:07:23.789 Casie Aviles: more tables, which is… I used FACT Transaction.
63 00:07:25.680 ⇒ 00:07:29.120 Casie Aviles: Because here, I did not use a transaction.
64 00:07:29.630 ⇒ 00:07:33.240 Casie Aviles: Only order refunds and catalyst successful orders.
65 00:07:37.130 ⇒ 00:07:42.589 Ashwini Sharma: Yeah, so Catalyst successful orders will… is kind of, you know,
66 00:07:43.290 ⇒ 00:07:48.739 Ashwini Sharma: Visil, I’ll tell you how we are creating catalyst Successful Orders, right? We look into order summary.
67 00:07:49.110 ⇒ 00:07:53.350 Ashwini Sharma: And pick up only those orders which are, like, the first-time orders.
68 00:07:55.100 ⇒ 00:07:59.669 Ashwini Sharma: Okay, so if it is a second-time order, then that… that does not qualify for Catalyst.
69 00:08:00.490 ⇒ 00:08:07.620 Ashwini Sharma: okay, this is… okay, I’m in the older repo once again.
70 00:08:42.480 ⇒ 00:08:45.519 Ashwini Sharma: I think that that might be okay.
71 00:08:46.170 ⇒ 00:08:50.999 Ashwini Sharma: Do you feel any reason why it should be a little bit more data in this?
72 00:08:52.480 ⇒ 00:08:59.690 Casie Aviles: No, it’s just, like, I was just curious, like, if… I just wanted to verify, because I’m… it might be wrong.
73 00:08:59.990 ⇒ 00:09:04.359 Casie Aviles: Because that’s really… it’s very few data, right?
74 00:09:04.920 ⇒ 00:09:14.219 Ashwini Sharma: Yeah, and if you look into Catalyst’s successful order table, right, it does not contain a lot of data. Look into staging, right?
75 00:09:14.420 ⇒ 00:09:19.479 Ashwini Sharma: I think that will give you a better idea, because it’s not yet created in prod.
76 00:09:24.150 ⇒ 00:09:25.559 Casie Aviles: We have this one.
77 00:09:25.790 ⇒ 00:09:28.180 Ashwini Sharma: No, no, look at, look into staging.
78 00:09:28.180 ⇒ 00:09:29.170 Casie Aviles: staging.
79 00:09:29.170 ⇒ 00:09:29.770 Ashwini Sharma: Yeah.
80 00:09:30.360 ⇒ 00:09:35.370 Ashwini Sharma: Oh, but staging, okay, hold on a second, right? Because staging might be…
81 00:09:35.850 ⇒ 00:09:39.390 Ashwini Sharma: Impacted if somebody else runs it.
82 00:09:41.250 ⇒ 00:09:43.330 Ashwini Sharma: Hold on a second.
83 00:09:43.870 ⇒ 00:09:44.350 Casie Aviles: Sure.
84 00:09:49.890 ⇒ 00:09:53.300 Ashwini Sharma: Let me run it manually from my machine, right?
85 00:09:53.760 ⇒ 00:09:54.390 Casie Aviles: Okay.
86 00:10:22.360 ⇒ 00:10:26.240 Ashwini Sharma: Yeah, I just refreshed the table SDG Catalyst.
87 00:10:27.220 ⇒ 00:10:28.540 Ashwini Sharma: Successful order.
88 00:10:29.150 ⇒ 00:10:33.760 Ashwini Sharma: So, can you try seeing how much data this table has?
89 00:10:42.860 ⇒ 00:10:45.450 Ashwini Sharma: Yeah, it won’t be more than 200, or…
90 00:10:46.110 ⇒ 00:10:54.590 Ashwini Sharma: 358, yeah. Can you do a join between the MART table and the previous order refunds and this one?
91 00:10:58.390 ⇒ 00:11:02.149 Casie Aviles: Join with which table? Order refunds?
92 00:11:02.150 ⇒ 00:11:02.860 Ashwini Sharma: Yes.
93 00:11:04.960 ⇒ 00:11:05.650 Casie Aviles: Maybe.
94 00:11:24.880 ⇒ 00:11:27.440 Casie Aviles: Okay, spotty data order number.
95 00:12:10.410 ⇒ 00:12:11.909 Ashwini Sharma: No, always the order ID.
96 00:12:13.600 ⇒ 00:12:15.749 Casie Aviles: No order ID, should be order number.
97 00:12:25.710 ⇒ 00:12:26.969 Ashwini Sharma: Only one record?
98 00:12:27.470 ⇒ 00:12:28.400 Casie Aviles: Yeah…
99 00:12:31.880 ⇒ 00:12:33.250 Ashwini Sharma: I’ve done so…
100 00:12:37.180 ⇒ 00:12:45.010 Ashwini Sharma: Okay, let’s take any other, order refunds, right, other than this, and then let’s track whether
101 00:12:46.030 ⇒ 00:12:51.630 Ashwini Sharma: it qualifies for catalyst or not, right? I think that should give us an idea whether…
102 00:12:51.870 ⇒ 00:12:54.029 Ashwini Sharma: What we are looking at is correct, right?
103 00:12:54.420 ⇒ 00:13:03.259 Ashwini Sharma: So, can you pick up any other order number from order refunds, and then we’ll see whether that qualified as catalyst or not.
104 00:13:05.730 ⇒ 00:13:07.670 Casie Aviles: Okay, how do we do that?
105 00:13:10.740 ⇒ 00:13:19.530 Ashwini Sharma: Look into order refunds table, and then pick up any order number that… go to your previous window, right? You had created a query over there, which was…
106 00:13:19.900 ⇒ 00:13:25.930 Ashwini Sharma: Yeah, yeah, from here, let’s pick up any order number which is not the one that you got.
107 00:13:28.450 ⇒ 00:13:29.190 Casie Aviles: Okay
108 00:13:33.390 ⇒ 00:13:36.329 Casie Aviles: Oh, by any, any number, right? So this one, for example.
109 00:13:36.700 ⇒ 00:13:38.510 Ashwini Sharma: Yeah, let’s copy this.
110 00:13:38.740 ⇒ 00:13:44.520 Ashwini Sharma: Let’s query it in, once again…
111 00:13:46.990 ⇒ 00:13:50.439 Ashwini Sharma: Let’s query it in order summary table.
112 00:14:27.010 ⇒ 00:14:29.810 Casie Aviles: I think Henry was… Same.
113 00:14:29.810 ⇒ 00:14:30.220 Ashwini Sharma: Excellent.
114 00:14:30.250 ⇒ 00:14:36.639 Casie Aviles: about… Transaction… I think I have to add this, right?
115 00:14:36.800 ⇒ 00:14:42.030 Ashwini Sharma: Hold on a second, no, you don’t have to add that. I’ll give you the query.
116 00:14:42.620 ⇒ 00:14:43.250 Casie Aviles: Oh, okay.
117 00:14:43.250 ⇒ 00:14:45.489 Ashwini Sharma: At, at this part.
118 00:14:53.780 ⇒ 00:14:55.480 Ashwini Sharma: Yeah, it’s in the chat.
119 00:15:10.970 ⇒ 00:15:15.420 Ashwini Sharma: Yeah, replace the last one with AND. No, no, don’t, don’t… Do that.
120 00:15:16.270 ⇒ 00:15:18.310 Ashwini Sharma: Yeah, just change that to AND.
121 00:15:23.590 ⇒ 00:15:29.050 Ashwini Sharma: So, so basically, like, this does not even qualify, right? If you see that there are a few,
122 00:15:29.210 ⇒ 00:15:33.380 Ashwini Sharma: things that qualify an order for this thing. So.
123 00:15:33.380 ⇒ 00:15:33.930 Casie Aviles: What…
124 00:15:33.930 ⇒ 00:15:36.510 Ashwini Sharma: What we can do is,
125 00:15:39.460 ⇒ 00:15:45.000 Ashwini Sharma: We can just see, okay, let me share my screen, I think.
126 00:15:45.210 ⇒ 00:15:45.720 Ashwini Sharma: And…
127 00:15:45.720 ⇒ 00:15:46.600 Casie Aviles: Okay, sure.
128 00:15:50.720 ⇒ 00:15:51.780 Casie Aviles: I’ll stop sharing.
129 00:15:56.510 ⇒ 00:15:58.279 Ashwini Sharma: Are you able to see my screen?
130 00:15:59.310 ⇒ 00:16:00.869 Casie Aviles: Yes, yes, I can see it.
131 00:16:07.250 ⇒ 00:16:15.619 Ashwini Sharma: Alright, so, basically, what you’re doing is select star from… what is the table name?
132 00:16:16.380 ⇒ 00:16:17.880 Ashwini Sharma: Orders refund.
133 00:16:23.510 ⇒ 00:16:24.609 Ashwini Sharma: fraud, right?
134 00:16:25.080 ⇒ 00:16:25.740 Casie Aviles: Yes.
135 00:16:32.750 ⇒ 00:16:36.039 Ashwini Sharma: Okay, and then, you’ve added some filters over here.
136 00:16:44.650 ⇒ 00:16:48.470 Ashwini Sharma: And, original deed.
137 00:16:48.580 ⇒ 00:16:49.290 Ashwini Sharma: Huh?
138 00:17:09.020 ⇒ 00:17:13.290 Ashwini Sharma: 276 records, right? So, what we can do is…
139 00:17:28.430 ⇒ 00:17:34.960 Ashwini Sharma: Order ID from… Order summary.
140 00:17:45.680 ⇒ 00:17:48.670 Ashwini Sharma: Let’s look into prod only, right?
141 00:17:58.200 ⇒ 00:18:01.170 Ashwini Sharma: Alright, no, no, we can…
142 00:18:03.330 ⇒ 00:18:06.149 Ashwini Sharma: We can do a join between these refunds.
143 00:18:24.400 ⇒ 00:18:25.280 Ashwini Sharma: Hmm.
144 00:18:26.840 ⇒ 00:18:28.270 Ashwini Sharma: It’s not order ID?
145 00:18:28.780 ⇒ 00:18:29.719 Ashwini Sharma: What is it?
146 00:18:30.520 ⇒ 00:18:34.030 Casie Aviles: I think body data order number.
147 00:18:42.480 ⇒ 00:18:44.590 Ashwini Sharma: Order number, right? This one?
148 00:18:44.890 ⇒ 00:18:45.600 Casie Aviles: Here.
149 00:18:56.920 ⇒ 00:18:58.160 Ashwini Sharma: Art of somebody.
150 00:19:00.350 ⇒ 00:19:07.459 Ashwini Sharma: Okay, transaction ID, it is not… Order summary is transaction ID.
151 00:19:17.220 ⇒ 00:19:20.419 Ashwini Sharma: But it looks somewhat different, right? It does not match.
152 00:19:23.170 ⇒ 00:19:23.900 Casie Aviles: Yeah.
153 00:19:27.240 ⇒ 00:19:31.130 Casie Aviles: I was using, your order number for, like, matching.
154 00:19:33.630 ⇒ 00:19:39.810 Ashwini Sharma: Yeah, the order number looks somewhat like this over here, right? If you see the order numbers, they are, like, Eden.
155 00:19:40.330 ⇒ 00:19:41.029 Casie Aviles: Yeah, yeah.
156 00:19:41.030 ⇒ 00:19:42.470 Ashwini Sharma: This is the order number, right?
157 00:19:42.840 ⇒ 00:19:47.900 Ashwini Sharma: Whereas in this one, order summary, You have a transaction ID.
158 00:19:49.610 ⇒ 00:19:55.969 Ashwini Sharma: Okay, maybe it’s in dimension, order.
159 00:19:57.180 ⇒ 00:20:02.609 Ashwini Sharma: DIM order, transaction ID and order number are there, right? So, maybe we can utilize this DIM.
160 00:20:02.780 ⇒ 00:20:05.630 Ashwini Sharma: Orders to map transaction ID to order number.
161 00:20:06.290 ⇒ 00:20:08.409 Ashwini Sharma: So, let’s see,
162 00:20:14.590 ⇒ 00:20:18.760 Ashwini Sharma: Select Transaction ID.
163 00:20:21.550 ⇒ 00:20:23.060 Ashwini Sharma: Transaction ID.
164 00:20:23.700 ⇒ 00:20:26.379 Ashwini Sharma: Then, what is it? O dot…
165 00:20:29.740 ⇒ 00:20:33.610 Ashwini Sharma: Order number…
166 00:21:10.530 ⇒ 00:21:12.979 Ashwini Sharma: Okay, so should you select star from?
167 00:21:13.890 ⇒ 00:21:15.160 Ashwini Sharma: Order new.
168 00:21:16.950 ⇒ 00:21:18.590 Ashwini Sharma: Should be able to get something.
169 00:21:20.320 ⇒ 00:21:22.959 Ashwini Sharma: Yeah, now we have the order number also, right?
170 00:21:23.280 ⇒ 00:21:24.000 Casie Aviles: Yes.
171 00:21:24.500 ⇒ 00:21:34.300 Ashwini Sharma: So, if we join the original refunds, Refunds are left joined.
172 00:21:35.280 ⇒ 00:21:37.230 Ashwini Sharma: With what, order new?
173 00:21:37.430 ⇒ 00:21:38.330 Ashwini Sharma: Over.
174 00:21:38.430 ⇒ 00:21:39.330 Ashwini Sharma: Sorry.
175 00:21:39.760 ⇒ 00:21:44.459 Ashwini Sharma: I don’t like this thing about BigQuery, its writable screen is so less…
176 00:21:44.790 ⇒ 00:21:47.549 Ashwini Sharma: That’s the only area that we get to write.
177 00:21:48.210 ⇒ 00:21:49.010 Casie Aviles: Yeah.
178 00:21:52.520 ⇒ 00:21:57.550 Ashwini Sharma: Join this one, or… on our dot.
179 00:21:59.320 ⇒ 00:22:01.090 Ashwini Sharma: What is it? Order number, right?
180 00:22:02.390 ⇒ 00:22:04.209 Ashwini Sharma: America’s still ordered.
181 00:22:04.440 ⇒ 00:22:08.809 Ashwini Sharma: Order number… Alright, let’s see what we get here.
182 00:22:11.370 ⇒ 00:22:15.270 Ashwini Sharma: Mmm, there… there are a couple of records that we are getting.
183 00:22:18.350 ⇒ 00:22:20.030 Ashwini Sharma: Which is interesting, okay.
184 00:22:20.460 ⇒ 00:22:26.350 Ashwini Sharma: So… Basically, yeah, these are some of the records. Oh, these are… hold on a second.
185 00:22:28.440 ⇒ 00:22:31.210 Ashwini Sharma: Transition ID, order number, these are nulls.
186 00:22:33.180 ⇒ 00:22:33.910 Casie Aviles: Hmm.
187 00:22:45.230 ⇒ 00:22:49.090 Ashwini Sharma: order new squad. O dot order ID, order number.
188 00:23:10.270 ⇒ 00:23:17.270 Ashwini Sharma: Okay, I’m getting 26… Things, records which are matching with,
189 00:23:17.830 ⇒ 00:23:22.109 Ashwini Sharma: they at least have our order number, right, in the order summary.
190 00:23:22.640 ⇒ 00:23:23.370 Casie Aviles: Yes.
191 00:23:23.520 ⇒ 00:23:31.069 Ashwini Sharma: So only these many records have an order number in the order summary. Maybe if we… let’s try to reduce this thing, right?
192 00:23:32.690 ⇒ 00:23:37.139 Ashwini Sharma: Maybe we’ll get a little bit more, I don’t know. I’m just making an assumption.
193 00:23:37.430 ⇒ 00:23:40.600 Ashwini Sharma: 29 records, right? And if I remove this one…
194 00:23:45.870 ⇒ 00:23:48.460 Ashwini Sharma: So, 54 records were…
195 00:23:49.160 ⇒ 00:23:51.299 Ashwini Sharma: We have some element in the order.
196 00:23:52.480 ⇒ 00:23:57.279 Ashwini Sharma: summary, but that’s not enough, right? Because…
197 00:23:57.850 ⇒ 00:24:02.250 Ashwini Sharma: So, in the, what did you do with the catalyst testing?
198 00:24:02.430 ⇒ 00:24:03.190 Ashwini Sharma: C.
199 00:24:04.010 ⇒ 00:24:04.860 Casie Aviles: Yeah, catalyst.
200 00:24:04.860 ⇒ 00:24:10.760 Ashwini Sharma: You joined it based on what? Order number, right? Body data order number? No, hold on a second.
201 00:24:11.710 ⇒ 00:24:15.130 Ashwini Sharma: Body data order number. You use this one to join it, right?
202 00:24:15.430 ⇒ 00:24:16.940 Casie Aviles: Yes, body data.
203 00:24:16.940 ⇒ 00:24:19.760 Ashwini Sharma: When you got only… you got only one record after that.
204 00:24:20.420 ⇒ 00:24:21.130 Casie Aviles: Yeah.
205 00:24:21.360 ⇒ 00:24:29.030 Ashwini Sharma: Okay, so that’s fine. So now, now we’ve got 26 records, and then now for these 26 records, right, we need to join it with
206 00:24:29.370 ⇒ 00:24:33.930 Ashwini Sharma: what do you call, like, with a different, this thing? Transaction, this thing.
207 00:24:34.610 ⇒ 00:24:43.739 Ashwini Sharma: Basically, this transaction ID that we got in the previous step has to, match with something called thank you page visits.
208 00:24:44.160 ⇒ 00:24:44.810 Casie Aviles: Hmm.
209 00:24:44.810 ⇒ 00:24:50.759 Ashwini Sharma: Right? So, what we’ll do is, let’s try to get this thing right,
210 00:25:03.000 ⇒ 00:25:05.260 Ashwini Sharma: Okay, let’s, let’s do this.
211 00:25:19.100 ⇒ 00:25:19.940 Ashwini Sharma: Thank you.
212 00:25:20.490 ⇒ 00:25:21.860 Ashwini Sharma: page visits.
213 00:25:29.670 ⇒ 00:25:30.700 Ashwini Sharma: Alright.
214 00:25:31.060 ⇒ 00:25:32.640 Ashwini Sharma: So we have this one.
215 00:25:33.340 ⇒ 00:25:36.149 Ashwini Sharma: Where we did a join, right? And then…
216 00:25:36.380 ⇒ 00:25:40.230 Ashwini Sharma: Okay, I think I didn’t pull that O dot.
217 00:25:41.110 ⇒ 00:25:42.760 Ashwini Sharma: Transition ideas.
218 00:25:43.210 ⇒ 00:25:44.140 Ashwini Sharma: Hold on.
219 00:25:45.090 ⇒ 00:25:46.600 Ashwini Sharma: Translation ID.
220 00:25:53.840 ⇒ 00:25:56.430 Ashwini Sharma: Oh, ordered order number F…
221 00:26:05.130 ⇒ 00:26:09.009 Ashwini Sharma: Okay, this is just joining with the order number.
222 00:26:11.360 ⇒ 00:26:16.809 Ashwini Sharma: Where do we have the transaction ID? Now, if I use this transaction ID, and then join it with the other guy, right?
223 00:26:17.020 ⇒ 00:26:20.460 Ashwini Sharma: With a thank you page, right? This indicates whether,
224 00:26:21.680 ⇒ 00:26:27.499 Ashwini Sharma: Like, if there is a thank you page associated with this order or not, right?
225 00:26:27.900 ⇒ 00:26:28.660 Casie Aviles: Okay.
226 00:26:37.280 ⇒ 00:26:41.840 Ashwini Sharma: Left joint, this one.
227 00:26:42.230 ⇒ 00:26:43.420 Ashwini Sharma: Oh, sorry.
228 00:26:53.600 ⇒ 00:26:56.390 Ashwini Sharma: on… what is it? O dot.
229 00:26:58.470 ⇒ 00:26:59.990 Ashwini Sharma: Transaction ID.
230 00:27:14.590 ⇒ 00:27:19.809 Ashwini Sharma: Let’s see… So, 54 records.
231 00:27:22.260 ⇒ 00:27:25.589 Ashwini Sharma: Okay, I didn’t select anything, right? I should have selected something.
232 00:27:26.700 ⇒ 00:27:29.850 Ashwini Sharma: TP dot… Order ID.
233 00:27:42.440 ⇒ 00:27:46.340 Ashwini Sharma: So we have at least a few records, right, over here, if you see.
234 00:27:46.680 ⇒ 00:27:50.119 Ashwini Sharma: Only this many records out of 54.
235 00:27:50.530 ⇒ 00:27:52.780 Ashwini Sharma: Where there was a thank you page.
236 00:27:54.310 ⇒ 00:27:57.440 Ashwini Sharma: Right? For most of them, there is not even a thank you page.
237 00:28:02.410 ⇒ 00:28:04.879 Ashwini Sharma: And it’s quite less, right?
238 00:28:05.970 ⇒ 00:28:16.939 Ashwini Sharma: Let’s see… Okay, if I… If I filter it over here… and TB… Order ID is not null.
239 00:28:20.800 ⇒ 00:28:23.719 Ashwini Sharma: So now we are down to just 15 records, right?
240 00:28:24.690 ⇒ 00:28:25.110 Casie Aviles: Nope.
241 00:28:25.110 ⇒ 00:28:28.920 Ashwini Sharma: records, if I go back, and then what we do here is…
242 00:28:29.320 ⇒ 00:28:35.350 Ashwini Sharma: We do a join with the thank you page, and then there is an edge raw layer, edge layer raw data, right?
243 00:28:35.670 ⇒ 00:28:45.850 Ashwini Sharma: So, in the edge layer raw data, this captures… this is the Cloudflare, table, right, which captures whenever you’re landing on a… on a thank you page.
244 00:28:46.490 ⇒ 00:28:55.290 Ashwini Sharma: it captures that record, right? So the user ID that’s associated on the thank you page,
245 00:28:56.110 ⇒ 00:29:01.740 Ashwini Sharma: Should, should match, the one on the… Edge layer raw data page.
246 00:29:02.110 ⇒ 00:29:07.249 Ashwini Sharma: And the edge layer raw data page should have a UTM source equals to the same.
247 00:29:07.490 ⇒ 00:29:08.240 Ashwini Sharma: Catalyst.
248 00:29:08.240 ⇒ 00:29:08.820 Casie Aviles: Okay.
249 00:29:08.820 ⇒ 00:29:12.289 Ashwini Sharma: Right? So that’s the join that we are looking at.
250 00:29:13.160 ⇒ 00:29:19.010 Ashwini Sharma: so, maybe we can add this also as a part of the join, right?
251 00:29:20.290 ⇒ 00:29:26.640 Ashwini Sharma: And what we’re doing is… We are… we’re capturing user ID. What’s the user ID there? Yeah.
252 00:29:27.210 ⇒ 00:29:28.470 Ashwini Sharma: It was there.
253 00:29:38.660 ⇒ 00:29:43.700 Casie Aviles: Yeah, I was also looking at the UTM source, which should be Catalyst, and then…
254 00:29:44.640 ⇒ 00:29:48.600 Ashwini Sharma: Yeah, so left join, that’s clear.
255 00:29:50.390 ⇒ 00:29:52.160 Ashwini Sharma: It’s their auditor, right?
256 00:29:53.080 ⇒ 00:29:54.180 Ashwini Sharma: RD…
257 00:29:58.290 ⇒ 00:29:59.320 Ashwini Sharma: on.
258 00:30:00.970 ⇒ 00:30:03.940 Ashwini Sharma: TP.userID goes to RD.userID.
259 00:30:11.810 ⇒ 00:30:17.150 Ashwini Sharma: Yeah, and then we can… let’s pull that, right, from…
260 00:30:23.880 ⇒ 00:30:24.940 Ashwini Sharma: Let’s see…
261 00:30:33.390 ⇒ 00:30:37.499 Ashwini Sharma: So only one of them has a catalyst over here, if you see this, right?
262 00:30:37.780 ⇒ 00:30:38.760 Casie Aviles: Yeah…
263 00:30:39.000 ⇒ 00:30:42.279 Ashwini Sharma: So that’s the reason why you’re getting only one record.
264 00:30:43.400 ⇒ 00:30:45.690 Ashwini Sharma: Out of all the order refunds.
265 00:30:46.340 ⇒ 00:30:53.079 Ashwini Sharma: So, yeah, I mean, your original query is correct. I just went through this, step by step to show you, like.
266 00:30:54.030 ⇒ 00:31:03.259 Ashwini Sharma: what exactly is missing, right? So, if we remove this condition, we do have almost, like, 15 records which are matching, right?
267 00:31:03.430 ⇒ 00:31:11.169 Ashwini Sharma: That’s true. And if you remove the previous condition also, you have around 54 records that show a match.
268 00:31:11.550 ⇒ 00:31:14.910 Ashwini Sharma: But because only one of them landed through Catalyst.
269 00:31:15.500 ⇒ 00:31:21.840 Ashwini Sharma: Your original query is showing just one record, so it seems to be a valid thing.
270 00:31:23.200 ⇒ 00:31:26.679 Casie Aviles: Okay, yeah, so, yeah, basically the…
271 00:31:27.300 ⇒ 00:31:30.020 Casie Aviles: The, the query is correct, right?
272 00:31:30.020 ⇒ 00:31:31.440 Ashwini Sharma: Yeah, that’s right.
273 00:31:32.170 ⇒ 00:31:32.910 Casie Aviles: Okay.
274 00:31:33.250 ⇒ 00:31:34.440 Casie Aviles: And… Or maybe…
275 00:31:34.440 ⇒ 00:31:39.470 Ashwini Sharma: Maybe what we can also do is, let’s take a look at this one, right? Is that right?
276 00:31:39.680 ⇒ 00:31:41.240 Casie Aviles: Hmm. .
277 00:31:41.850 ⇒ 00:31:43.200 Ashwini Sharma: Catalyst.
278 00:31:44.330 ⇒ 00:31:49.429 Ashwini Sharma: Catalyst successful orders, on STG, right? This is the one that I built, right?
279 00:31:49.710 ⇒ 00:31:53.210 Ashwini Sharma: So, there are about, how much?
280 00:31:54.600 ⇒ 00:31:55.540 Ashwini Sharma: Yeah.
281 00:31:56.640 ⇒ 00:31:59.080 Ashwini Sharma: I’m down 358 records, right?
282 00:32:00.110 ⇒ 00:32:03.959 Ashwini Sharma: Now, let’s see… let’s see, let’s just…
283 00:32:04.090 ⇒ 00:32:10.739 Ashwini Sharma: body, data, order number, right? We’re only interested in this one.
284 00:32:11.160 ⇒ 00:32:12.100 Ashwini Sharma: Okay.
285 00:32:15.820 ⇒ 00:32:16.730 Ashwini Sharma: Chrome.
286 00:32:16.920 ⇒ 00:32:18.709 Ashwini Sharma: This table, right?
287 00:32:24.190 ⇒ 00:32:27.339 Ashwini Sharma: And then let’s do a left join with order summary, right?
288 00:32:27.530 ⇒ 00:32:29.830 Ashwini Sharma: Sorry, order refund.
289 00:32:29.830 ⇒ 00:32:30.980 Casie Aviles: their defense.
290 00:32:31.840 ⇒ 00:32:36.810 Ashwini Sharma: So I could order refunds, right? Or, odd.
291 00:32:39.530 ⇒ 00:32:45.840 Ashwini Sharma: on… what is, so dot, body… Yeah.
292 00:32:46.400 ⇒ 00:32:47.310 Ashwini Sharma: Ordered.
293 00:32:47.570 ⇒ 00:32:48.760 Ashwini Sharma: Order number.
294 00:32:49.950 ⇒ 00:32:51.089 Ashwini Sharma: Is this the one?
295 00:32:52.190 ⇒ 00:32:52.770 Casie Aviles: Yes.
296 00:32:52.770 ⇒ 00:32:57.030 Ashwini Sharma: Yeah, so, so let’s just pick up, order.
297 00:32:57.290 ⇒ 00:32:58.449 Ashwini Sharma: Order number.
298 00:33:04.330 ⇒ 00:33:13.230 Ashwini Sharma: You see, most of them are null. Maybe there is one record which has a value, right? I’ll just put that filter over here, and then see which is the one that…
299 00:33:14.000 ⇒ 00:33:16.659 Ashwini Sharma: That has a value. Okay.
300 00:33:20.000 ⇒ 00:33:21.590 Ashwini Sharma: What is that? R dot?
301 00:33:22.580 ⇒ 00:33:24.139 Ashwini Sharma: It’s not null.
302 00:33:24.830 ⇒ 00:33:30.090 Ashwini Sharma: Let’s see… Yeah, so there is only one record, right?
303 00:33:30.290 ⇒ 00:33:34.620 Ashwini Sharma: Out of all the Catalyst successful orders, there is just one record.
304 00:33:35.390 ⇒ 00:33:37.859 Ashwini Sharma: And that that was refunded.
305 00:33:38.730 ⇒ 00:33:40.789 Casie Aviles: Okay, I see. Yep.
306 00:33:42.150 ⇒ 00:33:46.830 Casie Aviles: Oh, yeah, there really isn’t just enough data, right? That’s…
307 00:33:46.830 ⇒ 00:33:48.899 Ashwini Sharma: Yes, that’s correct, yeah.
308 00:33:50.020 ⇒ 00:33:53.010 Casie Aviles: Okay, yeah, that makes sense.
309 00:33:55.090 ⇒ 00:33:56.410 Ashwini Sharma: Do you want these queries?
310 00:33:56.720 ⇒ 00:33:58.459 Casie Aviles: Yes, yes, please, yes, please.
311 00:33:58.900 ⇒ 00:33:59.490 Ashwini Sharma: Thank you.
312 00:33:59.880 ⇒ 00:34:06.800 Ashwini Sharma: No problem. I’ll just slack it over to you, so that… But…
313 00:34:06.910 ⇒ 00:34:11.110 Ashwini Sharma: I feel that we lose this stuff in Zoom.
314 00:34:14.400 ⇒ 00:34:19.299 Ashwini Sharma: That’s one query, and this was the other query that we read, right?
315 00:34:19.489 ⇒ 00:34:21.509 Ashwini Sharma: I’ll just send this entire thing.
316 00:34:22.040 ⇒ 00:34:22.659 Casie Aviles: Okay.
317 00:34:23.590 ⇒ 00:34:25.099 Ashwini Sharma: And then you can filter it out.
318 00:34:28.800 ⇒ 00:34:29.920 Ashwini Sharma: All right.
319 00:34:31.260 ⇒ 00:34:36.089 Casie Aviles: Okay, I guess I just have, like, one last question.
320 00:34:36.090 ⇒ 00:34:36.960 Ashwini Sharma: Sure, sure.
321 00:34:36.969 ⇒ 00:34:41.849 Casie Aviles: Before we… It’s, like, related to…
322 00:34:43.489 ⇒ 00:34:50.349 Casie Aviles: the image that I have, which is… so for the… We’re only looking at…
323 00:34:51.639 ⇒ 00:34:54.849 Casie Aviles: Shipped, right, for the status.
324 00:34:55.670 ⇒ 00:34:56.460 Ashwini Sharma: Oh my goodness.
325 00:34:57.130 ⇒ 00:35:00.630 Ashwini Sharma: Yeah, that part, I’m a bit doubtful, because…
326 00:35:00.800 ⇒ 00:35:06.630 Ashwini Sharma: I don’t know if we are supposed to look at Oni’s ship,
327 00:35:07.930 ⇒ 00:35:11.889 Ashwini Sharma: I think this is something that Henry might be able to answer.
328 00:35:12.100 ⇒ 00:35:16.340 Casie Aviles: Okay, yeah, I’ll ask Henry. Yeah, because we only have, like.
329 00:35:16.690 ⇒ 00:35:21.600 Casie Aviles: Yeah, the only successful order was… The one that was shipped.
330 00:35:21.880 ⇒ 00:35:22.430 Casie Aviles: Okay.
331 00:35:22.430 ⇒ 00:35:23.430 Ashwini Sharma: Hey, look.
332 00:35:23.760 ⇒ 00:35:24.760 Casie Aviles: Yeah, that was…
333 00:35:25.690 ⇒ 00:35:32.330 Casie Aviles: Okay, but yeah, thank you, thank you. I think that’s… that’s good that we were able to get to the same result.
334 00:35:34.170 ⇒ 00:35:35.060 Ashwini Sharma: Alright.
335 00:35:36.290 ⇒ 00:35:38.310 Casie Aviles: Alright, yeah, thank you, Ashwini.
336 00:35:38.310 ⇒ 00:35:40.079 Ashwini Sharma: Okay, no problem, Casey, welcome.
337 00:35:40.080 ⇒ 00:35:40.840 Casie Aviles: Okay.
338 00:35:41.140 ⇒ 00:35:43.099 Ashwini Sharma: Yeah, have a good evening.