Meeting Title: Catalyst Orders and Refunds Analysis Date: 2025-11-28 Meeting participants: Awaish Kumar, Casie Aviles
WEBVTT
1 00:00:03.640 ⇒ 00:00:04.830 Awaish Kumar: I’m gonna quit.
2 00:00:36.320 ⇒ 00:00:37.370 Awaish Kumar: Hello.
3 00:00:38.890 ⇒ 00:00:39.739 Casie Aviles: Hey, Amish.
4 00:00:40.890 ⇒ 00:00:43.670 Awaish Kumar: Hi, how you doing?
5 00:00:44.930 ⇒ 00:00:47.419 Casie Aviles: Yeah, doing good. I’m just…
6 00:00:48.320 ⇒ 00:00:54.119 Casie Aviles: Exploring the Eden work right now, so… Yeah, I just had a…
7 00:00:54.160 ⇒ 00:01:05.500 Awaish Kumar: like, so what I understand from your message is that you are trying to figure out catalyst orders, and then you are trying to connect those orders with refunds, right?
8 00:01:05.690 ⇒ 00:01:06.230 Awaish Kumar: That’s the.
9 00:01:06.230 ⇒ 00:01:07.970 Casie Aviles: Yes. Yes.
10 00:01:08.330 ⇒ 00:01:15.069 Awaish Kumar: Okay, so in our modern… in our BigQuery, like, we have a table called Catalyst Successful Orders.
11 00:01:15.290 ⇒ 00:01:16.150 Awaish Kumar: Right?
12 00:01:17.870 ⇒ 00:01:23.329 Awaish Kumar: if you… okay, if you’re sharing your screen, if you go to the BigQuery and search for Catalyst.
13 00:01:23.480 ⇒ 00:01:25.250 Awaish Kumar: Successful orders.
14 00:01:29.900 ⇒ 00:01:31.190 Casie Aviles: Let me…
15 00:01:39.810 ⇒ 00:01:41.180 Casie Aviles: Just,
16 00:01:59.110 ⇒ 00:02:09.750 Awaish Kumar: So, Catalyst Successful Orders is a table which returns all the orders which are basically from the catalysts.
17 00:02:09.870 ⇒ 00:02:11.740 Awaish Kumar: Right? So… Okay.
18 00:02:11.950 ⇒ 00:02:23.159 Awaish Kumar: So you can use all the orders from there, and then you just join it with whatever table is giving information about refunds on the transaction ID.
19 00:02:23.480 ⇒ 00:02:26.769 Awaish Kumar: on the transaction ID, you can just join.
20 00:02:28.180 ⇒ 00:02:32.479 Awaish Kumar: with the order refunds. I don’t know if order refunds already has the…
21 00:02:32.880 ⇒ 00:02:37.740 Awaish Kumar: So, it has… it says order ID, if you, if you preview, click on preview.
22 00:02:38.800 ⇒ 00:02:39.480 Casie Aviles: Okay.
23 00:02:40.090 ⇒ 00:02:59.660 Awaish Kumar: If it says order ID, order ID is basically the transaction ID, and then it also has body data order number. So this is order ID in other tables. So you can use either of these columns to kind of join it with order refunds table, and basically that will just return your
24 00:02:59.660 ⇒ 00:03:01.459 Awaish Kumar: Catless orders with refunds.
25 00:03:03.310 ⇒ 00:03:05.469 Casie Aviles: Okay, I see. So…
26 00:03:05.470 ⇒ 00:03:10.840 Awaish Kumar: Only, only catch is, these orders does not include canceled orders.
27 00:03:11.300 ⇒ 00:03:24.879 Awaish Kumar: So these are all successful orders. If you also want canceled orders, or I don’t know if you want it, or if it is a… if it is a use case, but we… this table has all the successful orders.
28 00:03:24.980 ⇒ 00:03:32.799 Awaish Kumar: from Catalyst. Successful means the orders which are really completed, and, and from Catalyst.
29 00:03:33.500 ⇒ 00:03:34.190 Casie Aviles: Okay.
30 00:03:35.350 ⇒ 00:03:35.750 Awaish Kumar: Yup.
31 00:03:35.750 ⇒ 00:03:36.460 Casie Aviles: Alright.
32 00:03:36.820 ⇒ 00:03:44.199 Awaish Kumar: Join it with other refunds on order number, maybe, and then you can just get whatever info you need.
33 00:03:49.040 ⇒ 00:03:53.920 Casie Aviles: Okay, great. So, yeah, cancellations, I believe, that’s…
34 00:03:54.130 ⇒ 00:03:57.719 Casie Aviles: It’s part… it’s also in my ticket, but right now,
35 00:03:58.690 ⇒ 00:04:04.760 Casie Aviles: I guess I would say that it’s… I think it’s secondary. Refunds is, like, the primary.
36 00:04:07.220 ⇒ 00:04:08.990 Casie Aviles: that I was trying to…
37 00:04:09.780 ⇒ 00:04:19.910 Awaish Kumar: Yeah, like, if… if canceled is not in your analysis right now, you can use these tables, these two tables, right? But if… if…
38 00:04:19.910 ⇒ 00:04:37.800 Awaish Kumar: in future you need, anything for canceled orders, like, if you want to include canceled orders also in this, then you can maybe modify. You can look at the, like, if you go to the GitHub repo, you can see the query, right, for this table, catalyst successful orders.
39 00:04:37.800 ⇒ 00:04:46.010 Awaish Kumar: you can access this carry on GitHub, and then if you want any… want to make any modifications, you can do it… do there.
40 00:04:47.540 ⇒ 00:04:49.730 Awaish Kumar: So you don’t, like, you can copy the…
41 00:04:50.130 ⇒ 00:04:58.649 Awaish Kumar: Instead of using the table, if you need anything, like, any tweaks, customize, copy the carry, and maybe use that.
42 00:05:00.380 ⇒ 00:05:03.799 Awaish Kumar: In, in, writing analytics, yeah.
43 00:05:04.360 ⇒ 00:05:05.750 Casie Aviles: Analytics, sorry.
44 00:05:07.280 ⇒ 00:05:08.400 Casie Aviles: I see.
45 00:05:09.760 ⇒ 00:05:16.050 Awaish Kumar: Yeah, it has dbt project, and then inside dbt projects, models, sales.
46 00:05:16.440 ⇒ 00:05:19.910 Awaish Kumar: You can, March, mods, and then sales.
47 00:05:20.260 ⇒ 00:05:23.819 Awaish Kumar: You find this table, the query there.
48 00:05:25.000 ⇒ 00:05:27.560 Casie Aviles: Oh, okay, nice. Lovely, thank you.
49 00:05:29.460 ⇒ 00:05:49.249 Awaish Kumar: Okay, so if you don’t need cancel, this will work perfectly, but if you need to include a canceled one, you basically have to do just one tweak in the query, and that is, instead of using order summary table, you have to use a table called fact transaction with all orders.
50 00:05:49.290 ⇒ 00:06:06.139 Awaish Kumar: So, order… basically, this is excluding canceled orders, because I’m using order summary, which also includes canceled orders. This is built on top of that. But then, what you have to do in the query, if you look at… if you open that model again, I can show you.
51 00:06:07.020 ⇒ 00:06:08.860 Casie Aviles: Which model?
52 00:06:08.860 ⇒ 00:06:10.400 Awaish Kumar: If we go back to GitHub.
53 00:06:10.400 ⇒ 00:06:10.810 Casie Aviles: Okay.
54 00:06:11.960 ⇒ 00:06:15.389 Awaish Kumar: If you open that Catalyst Successful Order Scary.
55 00:06:16.510 ⇒ 00:06:19.280 Awaish Kumar: That’s really simple, if you just open that.
56 00:06:19.470 ⇒ 00:06:28.290 Awaish Kumar: Yeah, it’s the top… top CT, which says Catalyst Orders. This is the city which is getting data for all orders.
57 00:06:28.330 ⇒ 00:06:46.530 Awaish Kumar: Right? So right now, I’m getting everything from order summary, which by default excludes canceled orders. If you have to tweak to include canceled orders, or error orders, or anything, what you have to do is just change the city, this part.
58 00:06:46.900 ⇒ 00:06:48.969 Awaish Kumar: Only chain the top part.
59 00:06:48.970 ⇒ 00:06:49.760 Casie Aviles: Okay.
60 00:06:51.390 ⇒ 00:07:03.399 Awaish Kumar: Then, what you have to do is, when you have to change this, basically, you have to just go, go, like, go to the table called FACT Transactions with all orders.
61 00:07:03.520 ⇒ 00:07:13.569 Awaish Kumar: So there are multiple fact transactions table. One is called Fact Transactions, another one called Fact Transaction Enriched, and then there is one called Fact Transactions with
62 00:07:13.750 ⇒ 00:07:18.829 Awaish Kumar: All orders. That includes all the orders, including the canceled orders.
63 00:07:19.450 ⇒ 00:07:20.629 Casie Aviles: This one.
64 00:07:20.630 ⇒ 00:07:23.410 Awaish Kumar: Any, yeah.
65 00:07:24.050 ⇒ 00:07:28.019 Awaish Kumar: But the other logic to match it with catalyst things.
66 00:07:28.150 ⇒ 00:07:31.790 Awaish Kumar: Is… should be there, in that… Hi, Katie.
67 00:07:32.110 ⇒ 00:07:34.189 Awaish Kumar: The original kit, yeah.
68 00:07:35.580 ⇒ 00:07:37.780 Casie Aviles: Okay, great, thank you.
69 00:07:38.550 ⇒ 00:07:43.460 Casie Aviles: Yeah, it’s just, I just wanted to, like, make sure, because I was getting…
70 00:07:44.060 ⇒ 00:07:51.600 Casie Aviles: I was just getting 5… refunds, so… I mean…
71 00:07:51.600 ⇒ 00:08:08.039 Awaish Kumar: I don’t… yeah, that might be correct. I don’t know about, like, how many of those are refunds. Like, in that table, you can find many careless orders, but I don’t know how many of them have had refunds, you know, so maybe you can run your carrier and verify that.
72 00:08:09.080 ⇒ 00:08:09.820 Casie Aviles: Alright.
73 00:08:10.600 ⇒ 00:08:11.190 Awaish Kumar: Nope.
74 00:08:12.120 ⇒ 00:08:12.820 Casie Aviles: Okay.
75 00:08:12.820 ⇒ 00:08:16.979 Awaish Kumar: Yeah, let me just… All good? Okay, thank you.
76 00:08:17.140 ⇒ 00:08:18.850 Casie Aviles: Alright, thank you, Avish.
77 00:08:19.690 ⇒ 00:08:20.370 Awaish Kumar: Great.
78 00:08:21.340 ⇒ 00:08:21.890 Casie Aviles: No.