Meeting Title: Javy-Project-Internal-Review Date: 2024-10-24 Meeting participants: Nicolas Sucari, Uttam Kumaran, Brian Pei, Payas Parab
WEBVTT
1 00:04:31.580 ⇒ 00:04:33.010 Payas Parab: Hey, Nico! How are you?
2 00:04:35.330 ⇒ 00:04:36.360 Nicolas Sucari: Anybody else
3 00:04:36.600 ⇒ 00:04:38.589 Nicolas Sucari: all good here about you.
4 00:04:38.710 ⇒ 00:04:42.059 Payas Parab: I’m doing alright. How’s your guys? Week going.
5 00:04:43.979 ⇒ 00:04:48.469 Nicolas Sucari: It’s been good. It’s been a lot of movements, a lot of things to do.
6 00:04:49.193 ⇒ 00:04:50.520 Nicolas Sucari: So yeah.
7 00:04:51.120 ⇒ 00:04:53.400 Nicolas Sucari: it was busy week
8 00:04:53.640 ⇒ 00:04:54.959 Nicolas Sucari: hoping to
9 00:04:55.050 ⇒ 00:04:56.459 Nicolas Sucari: get to the weekend.
10 00:04:58.770 ⇒ 00:05:00.090 Payas Parab: Yeah, I’m in.
11 00:05:00.090 ⇒ 00:05:00.720 Nicolas Sucari: How about you?
12 00:05:00.720 ⇒ 00:05:05.224 Payas Parab: I feel like your beard has been getting longer. Each meeting. Have you been growing it out.
13 00:05:06.340 ⇒ 00:05:11.529 Nicolas Sucari: I mean, this is, this happens every 8 for me, I mean, grow so fast.
14 00:05:11.650 ⇒ 00:05:12.919 Nicolas Sucari: Yeah, you need to.
15 00:05:14.420 ⇒ 00:05:16.539 Nicolas Sucari: I’m probably shaving today. Yeah.
16 00:05:16.766 ⇒ 00:05:27.630 Payas Parab: No, I mean it’s good. I’m I’m I’m trying to grow out of beard, too. I’m like, I can’t connect the mustache and the beard yet it’s my 1st time trying to grow out a beard. So I’m like curious how how to how to best do it.
17 00:05:28.804 ⇒ 00:05:31.950 Nicolas Sucari: My beer grows really fast, so.
18 00:05:31.950 ⇒ 00:05:32.400 Payas Parab: Yeah.
19 00:05:32.400 ⇒ 00:05:40.840 Nicolas Sucari: I need to. Yeah, I I like to keep it like a little bit long always. But yeah, I don’t know. I need to.
20 00:05:41.000 ⇒ 00:05:43.439 Nicolas Sucari: Yeah, just maybe do it some
21 00:05:43.570 ⇒ 00:05:45.040 Nicolas Sucari: guiding. We
22 00:05:45.190 ⇒ 00:05:46.200 Nicolas Sucari: always.
23 00:05:46.860 ⇒ 00:05:49.180 Nicolas Sucari: I’m gonna do it today. If I have time.
24 00:05:52.810 ⇒ 00:05:55.653 Payas Parab: Cool. Is Brian gonna be joining
25 00:05:57.300 ⇒ 00:06:04.389 Nicolas Sucari: Hope he does. He! I talked. I talked with him these couple of days. Okay, here he is.
26 00:06:05.198 ⇒ 00:06:06.269 Nicolas Sucari: How are you, Tim?
27 00:06:07.030 ⇒ 00:06:09.729 Nicolas Sucari: Don’t worry. You can talk if you want.
28 00:06:13.310 ⇒ 00:06:15.719 Uttam Kumaran: Just just listening. I’m doing like
29 00:06:15.860 ⇒ 00:06:17.340 Uttam Kumaran: 50 other things. But
30 00:06:18.130 ⇒ 00:06:19.240 Uttam Kumaran: here, for moral support.
31 00:06:19.240 ⇒ 00:06:20.030 Nicolas Sucari: That’s why.
32 00:06:21.670 ⇒ 00:06:22.590 Nicolas Sucari: Yeah.
33 00:06:24.970 ⇒ 00:06:27.360 Nicolas Sucari: cool is, is Robert joining Chayas. Do you know.
34 00:06:27.702 ⇒ 00:06:37.979 Payas Parab: I don’t know. I’m I’m not sure. Yeah. So I I like, I, I’m basically owning this. Anyway, we’re just trying to like. Get enough that we can
35 00:06:38.100 ⇒ 00:06:55.330 Payas Parab: get the Meta base one like at least the the dash, like the the spreadsheet. We’re essentially recreating that spreadsheet in like a nice dashboard format. There’s just a couple of things that need to be resolved. I this new versus existing thing. It’s just not quite tying out. But Brian and I are working on some additional logic there and then.
36 00:06:55.622 ⇒ 00:07:08.069 Payas Parab: Which, Brian, I’ll update that pr with like where it needs to go. And then, for now I can just build that logic into the SQL. Query and then later update it. Once we have the entire updated one flow through Dbt.
37 00:07:08.580 ⇒ 00:07:09.540 Brian Pei: Sweet, perfect.
38 00:07:09.540 ⇒ 00:07:10.200 Nicolas Sucari: Okay.
39 00:07:10.654 ⇒ 00:07:32.100 Payas Parab: Yeah, I I didn’t realize the customer order number was, it’s already built the like, the row aggregation. So we just need to like use that, and maybe that’ll tie to shopify. I’ll check on Snowflake. See if that gets us closer. The one that the one thing that I did need Brian’s help on was the refund stuff like something still not like quite tying out in terms of like what they in the dashboard call
40 00:07:32.430 ⇒ 00:07:33.556 Payas Parab: like returns.
41 00:07:34.120 ⇒ 00:07:35.810 Brian Pei: Like a true refund. Yeah.
42 00:07:35.810 ⇒ 00:07:39.660 Payas Parab: Yeah, I don’t. I was like, maybe it’s like a combination of like
43 00:07:39.820 ⇒ 00:07:45.259 Payas Parab: the total pro, like the total revenue from the canceled orders plus
44 00:07:45.450 ⇒ 00:07:56.480 Payas Parab: the. But that also doesn’t get me in the same ballpark. That was the query I sent you. Something. We gotta kind of figure out how to do. I just don’t want that. Like, I basically just wanna make sure everything looks
45 00:07:56.930 ⇒ 00:08:10.549 Payas Parab: at a high level, the same as what Jared seeing in analytics, shopify analytics and then cause, then we can build off of it once we know that for sure. So the only one is like the returns or refunds. I don’t know how they define returns.
46 00:08:12.670 ⇒ 00:08:19.760 Brian Pei: Yeah. I also was a little bit stuck. The thing that I went outside of our tables for is that there is a
47 00:08:19.830 ⇒ 00:08:22.569 Brian Pei: there’s an object just straight up, called refund
48 00:08:22.990 ⇒ 00:08:27.050 Brian Pei: in shopify that 5 transpits out in Snowflake. But
49 00:08:27.100 ⇒ 00:08:31.270 Brian Pei: all of the dollars in that table are set to 0.
50 00:08:31.840 ⇒ 00:08:32.890 Brian Pei: So
51 00:08:33.750 ⇒ 00:08:42.659 Brian Pei: it. It is a it’s a it’s called refunds, and it’s a list of order Ids, and then some Json fields. So the very least I I’ll try to do some analysis with.
52 00:08:42.669 ⇒ 00:08:52.539 Payas Parab: Did you check? Did you check like recent like? Did you check? Because I I noticed this thing where it’s like, it doesn’t update refunds is like null or 0 for, like the most recent month.
53 00:08:52.809 ⇒ 00:08:56.579 Payas Parab: So I guess they have, like some type of like a closing cycle for that. So did you check
54 00:08:56.639 ⇒ 00:09:01.229 Payas Parab: like more than like 30 days ago, or something? Or did you just look at like recent data.
55 00:09:01.230 ⇒ 00:09:03.770 Brian Pei: Let me see if I can find the query.
56 00:09:05.100 ⇒ 00:09:07.820 Brian Pei: I think I summed all time.
57 00:09:07.820 ⇒ 00:09:09.120 Payas Parab: Oh, all the time. Okay.
58 00:09:09.120 ⇒ 00:09:10.920 Brian Pei: It was 0. Let me show you
59 00:09:12.560 ⇒ 00:09:14.900 Brian Pei: Oh, I see. Okay, let me get rid of this
60 00:09:15.570 ⇒ 00:09:18.769 Brian Pei: where clause and see, okay, so
61 00:09:19.740 ⇒ 00:09:23.530 Brian Pei: put it in here so you can throw it in.
62 00:09:24.140 ⇒ 00:09:26.910 Brian Pei: So I was looking at this this refund table.
63 00:09:29.730 ⇒ 00:09:32.339 Brian Pei: I, when I took out the
64 00:09:32.510 ⇒ 00:09:33.330 Brian Pei: date filter.
65 00:09:33.330 ⇒ 00:09:35.149 Payas Parab: Can you share your screen? By the way.
66 00:09:35.150 ⇒ 00:09:38.890 Uttam Kumaran: Yeah. Brian, refund table is not gonna have values.
67 00:09:39.050 ⇒ 00:09:43.010 Uttam Kumaran: It’s just gonna have the order id, and the there’s no
68 00:09:44.200 ⇒ 00:09:46.299 Uttam Kumaran: there’s typically no partial refunds
69 00:09:47.070 ⇒ 00:09:47.960 Uttam Kumaran: like.
70 00:09:48.590 ⇒ 00:09:50.819 Uttam Kumaran: I mean, you basically refund an item or not.
71 00:09:51.586 ⇒ 00:09:56.300 Uttam Kumaran: If you look on pool parts, you’ll see logic for how we’re handling refunds.
72 00:09:56.400 ⇒ 00:10:01.029 Uttam Kumaran: You basically have, like, you have information about Id created that process restock
73 00:10:01.220 ⇒ 00:10:02.520 Uttam Kumaran: who actually.
74 00:10:03.000 ⇒ 00:10:06.980 Uttam Kumaran: who is a refund associated with. And then basically, we bring that into orders.
75 00:10:07.765 ⇒ 00:10:11.824 Uttam Kumaran: And we just mark is refunded or not. And then
76 00:10:13.090 ⇒ 00:10:13.740 Uttam Kumaran: you basically.
77 00:10:14.155 ⇒ 00:10:21.209 Payas Parab: Aren’t there partial refunds where, like the total gross revenue isn’t fully refunded in the refund amount.
78 00:10:24.290 ⇒ 00:10:25.540 Uttam Kumaran: no
79 00:10:25.570 ⇒ 00:10:31.259 Uttam Kumaran: like you’re you’re not you. You can do a refund where you like. Just pick an arbitrary number. You have to refund an item.
80 00:10:31.490 ⇒ 00:10:33.190 Uttam Kumaran: you can attribute a discount.
81 00:10:33.400 ⇒ 00:10:34.740 Uttam Kumaran: For example, if you’re like.
82 00:10:35.060 ⇒ 00:10:40.159 Uttam Kumaran: we’re just gonna give you 30% off because we fuck something up. They’ll just give 30% off.
83 00:10:40.260 ⇒ 00:10:46.760 Uttam Kumaran: but on the refund it’ll be associated with an order, and then it’ll also be associated with an order. Item.
84 00:10:48.350 ⇒ 00:10:49.619 Payas Parab: I see. Okay.
85 00:10:54.330 ⇒ 00:10:56.270 Uttam Kumaran: Yeah. So Brian, I would. Just
86 00:10:56.830 ⇒ 00:11:01.520 Uttam Kumaran: and then also there’s order line. There is an order line refunds
87 00:11:03.670 ⇒ 00:11:05.780 Uttam Kumaran: table. I don’t know, if you saw
88 00:11:07.360 ⇒ 00:11:10.240 Uttam Kumaran: if you’ve seen that, but you can actually
89 00:11:12.750 ⇒ 00:11:20.949 Uttam Kumaran: I would just I I can just point you to the logic, because this was really this was mad, confusing, and then I figured it out, and I don’t want you to have to do this again.
90 00:11:21.780 ⇒ 00:11:24.389 Brian Pei: Okay, yeah, I do actually see order line refund.
91 00:11:24.830 ⇒ 00:11:25.410 Uttam Kumaran: Yeah, I’ll.
92 00:11:25.410 ⇒ 00:11:28.549 Brian Pei: On the I assume on the order line, not the
93 00:11:28.650 ⇒ 00:11:30.060 Brian Pei: parent order.
94 00:11:30.440 ⇒ 00:11:31.580 Uttam Kumaran: Correct. Yeah, and.
95 00:11:31.580 ⇒ 00:11:32.240 Brian Pei: I’m just gonna.
96 00:11:32.240 ⇒ 00:11:34.832 Uttam Kumaran: I’m just gonna d, I’ll DM you the logic
97 00:11:35.340 ⇒ 00:11:37.048 Uttam Kumaran: yeah. Just copy. Because I
98 00:11:37.950 ⇒ 00:11:42.310 Uttam Kumaran: yeah, I I I just this is deja vu for me, so I just don’t want you to have to get this.
99 00:11:42.310 ⇒ 00:11:43.099 Brian Pei: Alright, cool.
100 00:11:43.100 ⇒ 00:11:43.750 Uttam Kumaran: All right
101 00:11:45.600 ⇒ 00:11:47.590 Uttam Kumaran: cool. I was actually valuable right?
102 00:11:51.500 ⇒ 00:11:52.099 Brian Pei: All right. I’ll take.
103 00:11:52.100 ⇒ 00:11:52.860 Nicolas Sucari: Thanks! Again.
104 00:11:52.860 ⇒ 00:11:53.570 Brian Pei: Even.
105 00:11:55.290 ⇒ 00:11:56.050 Nicolas Sucari: Okay?
106 00:11:56.625 ⇒ 00:12:25.799 Nicolas Sucari: Also. We created that order line new table by us. So that would. The idea is to have 2 dashboards, one with all orders information, and one more with detail of each product in in the orders. So I’m I’m I’m working on that in in real. I’ll I’ll share when that is ready. I think I just deployed it. But I I’m I’m still doing some changes to the metrics and dimensions that we can see there, and they’ll share.
107 00:12:25.840 ⇒ 00:12:28.010 Nicolas Sucari: I share that link to you so that you can check.
108 00:12:28.010 ⇒ 00:12:28.560 Payas Parab: Love that
109 00:12:29.180 ⇒ 00:12:34.930 Payas Parab: product level stuff right? So if they wanted to like, see like concentrates versus protein powder like that, that will.
110 00:12:34.930 ⇒ 00:12:41.550 Nicolas Sucari: I don’t know. Yeah, that’s exactly what I’m trying to like. The questions I’m trying to answer, like, what’s the most sold product?
111 00:12:41.833 ⇒ 00:12:50.039 Nicolas Sucari: And that’s how I I got there. But I don’t know if we have like product categories or something like that. But yeah, I need to look that in.
112 00:12:50.040 ⇒ 00:12:53.949 Payas Parab: There are product categories. It relies on a tag, so I don’t know.
113 00:12:54.630 ⇒ 00:12:55.580 Nicolas Sucari: Oh, it’s okay.
114 00:12:55.580 ⇒ 00:12:56.340 Payas Parab: Yeah,
115 00:12:57.140 ⇒ 00:13:19.250 Payas Parab: there may be even like a world in which we just have to make a mapping table. For the product category. But there’s there’s essentially like 3 categories they think about right. One is their like protein coffee, the other is like their classic concentrates, and then they like have a 3rd category, which is like accessories. So it’s like when you group them into that right? That’s sort of how they look at it.
116 00:13:19.830 ⇒ 00:13:35.159 Payas Parab: yeah, so we may need to like implement those categories. And maybe there’s like a mapping table of some kind that does that. Let me know if you run into blockers on that, because that that is an important distinction to them, because the protein coffee is more expensive versus the concentrates. And then this accessories kind of like
117 00:13:35.160 ⇒ 00:13:53.780 Payas Parab: fucks up the order level, you know. Like, if they randomly add, like the Javi coffee mixer, or whatever the order level cart value doesn’t really reflect a sale like it reflects a higher sale of their core product than it should, so that that distinction of like what is considered an accessory.
118 00:13:53.930 ⇒ 00:14:01.779 Payas Parab: or like a small little like, add on that they sell. They don’t want that kind of mixed in with the protein coffee versus concentrates.
119 00:14:02.010 ⇒ 00:14:02.800 Payas Parab: Hmm.
120 00:14:02.800 ⇒ 00:14:03.510 Nicolas Sucari: Okay.
121 00:14:03.720 ⇒ 00:14:06.324 Nicolas Sucari: yeah, maybe we need to do that because,
122 00:14:06.940 ⇒ 00:14:12.960 Nicolas Sucari: we have the names for each of the lines and products. But that is like, really messy. And
123 00:14:12.980 ⇒ 00:14:14.190 Nicolas Sucari: yeah, we.
124 00:14:14.190 ⇒ 00:14:14.660 Brian Pei: Yeah, I mean.
125 00:14:16.990 ⇒ 00:14:18.319 Brian Pei: order a line. Item.
126 00:14:19.310 ⇒ 00:14:22.930 Brian Pei: yeah, as Nico said. The the order object doesn’t have
127 00:14:22.970 ⇒ 00:14:29.119 Brian Pei: products like I, we could add them, but it would be like a list ag, where the products are separated with a comma or whatever, but
128 00:14:29.150 ⇒ 00:14:35.300 Brian Pei: line by line, order sold or sorry products sold. You would only get from order. Item, order, line.
129 00:14:35.300 ⇒ 00:14:36.290 Payas Parab: Borderline yeah.
130 00:14:36.290 ⇒ 00:14:38.430 Brian Pei: I do see a product.
131 00:14:38.430 ⇒ 00:14:39.810 Nicolas Sucari: Order. Line. Name.
132 00:14:39.990 ⇒ 00:14:40.550 Nicolas Sucari: yeah.
133 00:14:40.550 ⇒ 00:14:43.060 Brian Pei: I do. Yeah, there’s like product title in there.
134 00:14:43.060 ⇒ 00:14:43.450 Payas Parab: Yeah.
135 00:14:43.450 ⇒ 00:14:50.500 Brian Pei: And skew, but it’s pro. It’s not like their custom groupings, which I, which you said are probably in product technical.
136 00:14:50.920 ⇒ 00:15:11.030 Payas Parab: These things have a fuck ton of names, though right? Because they kind of do this like weird shit where it’s like every variant has its own name of some kind, and it like looks funky. Am I like correct in that? That might be like I don’t know how big that mapping table is, but we could like kind of do it manually a little bit. But if it’s like massive, because I think there is some weird shit where, like
137 00:15:11.260 ⇒ 00:15:17.020 Payas Parab: their variants or products, are just like it’s the same product. But it’s called like 10 different things. For some reason
138 00:15:17.030 ⇒ 00:15:22.149 Payas Parab: I don’t. I don’t know, Nico, if you run into that again, let me know if you like. Face a blocker there, just because, like
139 00:15:22.500 ⇒ 00:15:28.039 Payas Parab: the amplitude, events had the tag so like there. There is some logic that can be built. I think.
140 00:15:29.280 ⇒ 00:15:34.480 Brian Pei: And when you say tag, you mean, do they call it something like bucket or.
141 00:15:35.230 ⇒ 00:15:38.580 Payas Parab: Yeah, it’s it’s like, concentrates, protein coffee or accessory.
142 00:15:38.580 ⇒ 00:15:39.000 Brian Pei: Okay, so.
143 00:15:39.000 ⇒ 00:15:39.950 Nicolas Sucari: Yeah. And they come in.
144 00:15:39.950 ⇒ 00:15:40.640 Brian Pei: One of 3.
145 00:15:40.640 ⇒ 00:15:41.549 Nicolas Sucari: It’s in the product.
146 00:15:41.550 ⇒ 00:15:42.560 Payas Parab: It’s 1 of the 18.
147 00:15:42.560 ⇒ 00:15:44.240 Nicolas Sucari: I just find it. Yeah.
148 00:15:44.677 ⇒ 00:16:02.100 Nicolas Sucari: I don’t know, because they also have, like, there is a product type column in the raw shopify product table and they have like product coffee, concentrate custom bundle, also syrups, gift cards. But and accessories. Yeah, I think those are the categories.
149 00:16:02.410 ⇒ 00:16:06.009 Nicolas Sucari: Maybe we can use that one, Brian. I can send you the.
150 00:16:06.010 ⇒ 00:16:07.720 Brian Pei: Yeah, send that to me. I’ll see if I can add it
151 00:16:08.190 ⇒ 00:16:08.869 Brian Pei: straight up.
152 00:16:08.870 ⇒ 00:16:09.760 Nicolas Sucari: Yeah. He’s.
153 00:16:09.760 ⇒ 00:16:10.950 Brian Pei: So you don’t have to do the join.
154 00:16:10.950 ⇒ 00:16:11.750 Nicolas Sucari: There’s okay.
155 00:16:12.900 ⇒ 00:16:13.660 Nicolas Sucari: Cool.
156 00:16:14.100 ⇒ 00:16:17.800 Nicolas Sucari: Yeah, I’ll I’ll send you this. But yeah, I think I found it here.
157 00:16:19.400 ⇒ 00:16:25.759 Nicolas Sucari: But yeah, that’s what I’m trying to do right now. But yes, like, I’m trying to figure out what else we can
158 00:16:25.820 ⇒ 00:16:36.660 Nicolas Sucari: build in real, so that they can have like more information there regarding orders regarding products? And answer those kind of questions like, what is the most sold product that they have
159 00:16:37.527 ⇒ 00:16:51.830 Nicolas Sucari: category? So that we can show them. We can show a man, maybe on Monday, and also then he can share with Jared and Justin, and and say, like, we can use these dashboards to answer those types of questions. Okay.
160 00:16:52.400 ⇒ 00:17:03.299 Payas Parab: Yeah, yeah, agreed. Okay, cause I’m gonna start building the Meta base ones as well, which I think like order line, for example, will like help do like, okay, like bundling like, how does bundling work and like discounts on an.
161 00:17:03.300 ⇒ 00:17:03.830 Nicolas Sucari: Yeah.
162 00:17:03.830 ⇒ 00:17:13.391 Payas Parab: Once we get that cleaned up, I can also start to put those together in metabase. So it’s like we’re presenting side by side the self serve tool and the custom. Query.
163 00:17:14.000 ⇒ 00:17:28.000 Payas Parab: I can own all the meta based stuff, because, anyway, that’s kind of like on our on our end, right? And then, yeah, and I’ll fix this. I work on this like attribution, for, like new versus existing and do some testing there. So I can own that, and like, get something ready to go
164 00:17:28.331 ⇒ 00:17:31.729 Payas Parab: in an ideal world next week. We’re like ready to present some stuff
165 00:17:32.010 ⇒ 00:17:34.280 Payas Parab: in real and metabase
166 00:17:34.330 ⇒ 00:17:38.369 Payas Parab: to Aman and then get access to Jared and Justin as well.
167 00:17:38.956 ⇒ 00:17:43.200 Payas Parab: I wanna target like early next week, because I think they just want to see something. Robert was like.
168 00:17:43.560 ⇒ 00:17:47.858 Payas Parab: they know we’re working. They just like are like, we wanna see something
169 00:17:48.520 ⇒ 00:17:54.710 Nicolas Sucari: Yeah, yeah, no. But that’s that’s that’s totally fine. I mean, we need to show them. And once they start seeing stuff, they are gonna like.
170 00:17:54.710 ⇒ 00:17:55.420 Payas Parab: Yeah.
171 00:17:55.420 ⇒ 00:17:59.308 Nicolas Sucari: Trust us better on on those those stuff. Yeah.
172 00:17:59.740 ⇒ 00:18:02.750 Brian Pei: I will rework, refunds.
173 00:18:03.340 ⇒ 00:18:08.379 Payas Parab: Yeah, refunds. Yeah, if you can figure that out like I tried. But you’re probably better than so.
174 00:18:08.380 ⇒ 00:18:09.470 Brian Pei: I’ll give it a shot.
175 00:18:09.470 ⇒ 00:18:10.034 Payas Parab: Yeah,
176 00:18:10.630 ⇒ 00:18:14.639 Payas Parab: we also like, I’m okay. I’m comfortable going back to them and being like, Hey, like.
177 00:18:14.720 ⇒ 00:18:21.140 Payas Parab: we don’t know what fucking logic shopify. They don’t share this right like I also was looking for Brian, and I couldn’t find it like anywhere.
178 00:18:21.140 ⇒ 00:18:21.500 Brian Pei: Yeah.
179 00:18:21.500 ⇒ 00:18:28.459 Payas Parab: Other than like the community message boards. So shopify is like not sharing what the fuck the logic is for these analytics tools. So I’m.
180 00:18:28.460 ⇒ 00:18:30.490 Uttam Kumaran: What’s the what’s the metric? Pious.
181 00:18:31.266 ⇒ 00:18:35.709 Payas Parab: Returns. I can even show you in the Admin portal what it looks like, and log into Roberts.
182 00:18:35.710 ⇒ 00:18:42.149 Uttam Kumaran: So the biggest thing to that is usually fucked up is that if shopify will count.
183 00:18:43.790 ⇒ 00:18:47.350 Uttam Kumaran: may or may not count things that have payment processed
184 00:18:47.970 ⇒ 00:18:53.259 Uttam Kumaran: yet or not. So sometimes that’s usually an issue like some things may still be pending
185 00:18:53.973 ⇒ 00:19:01.490 Uttam Kumaran: and then also the refunds are organized, not on the date the order happened, but on the date of refund. So they’re not.
186 00:19:01.490 ⇒ 00:19:01.880 Payas Parab: How can I?
187 00:19:01.880 ⇒ 00:19:02.680 Uttam Kumaran: Match.
188 00:19:04.160 ⇒ 00:19:15.929 Uttam Kumaran: Yes. Another thing, I learned the hard way. So you basically have to establish with the client what they want to see for our other client. We
189 00:19:16.070 ⇒ 00:19:17.889 Uttam Kumaran: we told them that
190 00:19:18.030 ⇒ 00:19:27.269 Uttam Kumaran: it’s it depends like, if you want to look at financial reconciliation, you need to tie it back to the order date, but commonly the refund will fall
191 00:19:27.610 ⇒ 00:19:29.240 Uttam Kumaran: out of month or out of the week.
192 00:19:29.240 ⇒ 00:19:33.179 Payas Parab: We do. We know that for sure that that’s how shopify does it like? If I look at.
193 00:19:33.770 ⇒ 00:19:34.259 Uttam Kumaran: I am.
194 00:19:34.260 ⇒ 00:19:36.879 Payas Parab: Find documentation to indicate that. That’s my.
195 00:19:36.880 ⇒ 00:19:40.890 Uttam Kumaran: I’m I’m happy to go do that for you. But yeah, that’s.
196 00:19:41.630 ⇒ 00:19:42.629 Payas Parab: Are you guys all see the screen.
197 00:19:42.630 ⇒ 00:19:45.639 Uttam Kumaran: You’ll be able to find them. The data. If you look at a couple of examples.
198 00:19:46.430 ⇒ 00:19:51.860 Payas Parab: Okay, I see. So you already kind of have done this exercise before. Yeah.
199 00:19:52.500 ⇒ 00:20:09.419 Payas Parab: So this returns looks low. But then that explanation you might be. I mean, return. Cycles are like 90 days, right? So in a state of growing states and a growing sales, it makes sense that the returns that we have would be under what the actuals were, because we’re doing based on order, date. And this might.
200 00:20:09.420 ⇒ 00:20:10.200 Uttam Kumaran: Correct
201 00:20:10.923 ⇒ 00:20:16.529 Uttam Kumaran: my my, I would say, take a take like 2 or 3 returned orders.
202 00:20:16.804 ⇒ 00:20:17.080 Payas Parab: And.
203 00:20:17.080 ⇒ 00:20:19.790 Uttam Kumaran: Look for different month
204 00:20:20.110 ⇒ 00:20:21.220 Uttam Kumaran: returns.
205 00:20:21.290 ⇒ 00:20:31.509 Uttam Kumaran: And maybe even Brian, you can find a couple of examples, or whatever and if you go into the data. And you just you basically compare what it says in shopify versus what it says
206 00:20:31.640 ⇒ 00:20:33.559 Uttam Kumaran: in the data, you’ll see that.
207 00:20:34.000 ⇒ 00:20:35.550 Uttam Kumaran: Yeah, if you aggregate
208 00:20:35.670 ⇒ 00:20:37.569 Uttam Kumaran: the return values up.
209 00:20:38.256 ⇒ 00:20:47.499 Uttam Kumaran: That’s why what we do is in in the other orders table. We did, Brian. We have a return date, and we have an order date, so you can aggregate by whatever you want to do.
210 00:20:48.218 ⇒ 00:20:49.892 Uttam Kumaran: But shopify will.
211 00:20:50.970 ⇒ 00:20:55.150 Uttam Kumaran: yeah. Shopify. Will does not give you that option to specify.
212 00:20:55.840 ⇒ 00:20:56.550 Payas Parab: Got it.
213 00:20:56.940 ⇒ 00:21:07.440 Payas Parab: Okay, that that actually explains that actually might be the core of it, especially, I think the quick validation there is just like. If if growing revenue is there, then it makes sense that we would be under on returns.
214 00:21:07.830 ⇒ 00:21:14.549 Uttam Kumaran: We are. Yeah, like, returns are not gonna hit on, as you mentioned, until, like, probably Max out 90 days after. So
215 00:21:15.270 ⇒ 00:21:22.329 Uttam Kumaran: basically, this is commonly like, just just have to tell them like, look returns are, gonna be like, it’s just it’s like obvious.
216 00:21:22.330 ⇒ 00:21:22.650 Payas Parab: Perspective.
217 00:21:22.650 ⇒ 00:21:29.110 Uttam Kumaran: There’s no return. So it’s going to be under and also you may have orders like. For example, you may have returns
218 00:21:29.220 ⇒ 00:21:31.430 Uttam Kumaran: on October orders next month.
219 00:21:31.660 ⇒ 00:21:32.160 Payas Parab: They need to.
220 00:21:32.160 ⇒ 00:21:33.900 Uttam Kumaran: Make a decision on when to reflect, and then.
221 00:21:33.900 ⇒ 00:21:45.319 Payas Parab: Yeah, yeah, I mean, I think, like, Jared’s view will probably be the financial accounting definition, right? Which is that like, when the revenue is recognized. You recognize the gross sales, and you recognize the refund.
222 00:21:45.400 ⇒ 00:21:50.679 Payas Parab: not at the time that the refund was processed, but on the original order, right for net revenue.
223 00:21:53.580 ⇒ 00:21:56.709 Payas Parab: I I have an accounting background. That’s the like. That’s the technical.
224 00:21:56.970 ⇒ 00:21:58.550 Uttam Kumaran: Yes, so you would. I mean.
225 00:21:59.170 ⇒ 00:22:03.680 Uttam Kumaran: yeah. So on the accounting side, you have to reflect it based on. When the revenue was recognized.
226 00:22:03.680 ⇒ 00:22:04.380 Payas Parab: Was reckoned but.
227 00:22:04.380 ⇒ 00:22:07.330 Uttam Kumaran: That may not be what everybody wants to see.
228 00:22:07.330 ⇒ 00:22:11.449 Payas Parab: Sure. Sure. Well, that’s great. I can. I can go back to Jared and be like well, we reconcile.
229 00:22:11.450 ⇒ 00:22:12.479 Uttam Kumaran: You’re spot on. Yeah.
230 00:22:12.480 ⇒ 00:22:15.249 Payas Parab: Yeah, we’re check. We’re we’re like the returns. This is the issue.
231 00:22:15.250 ⇒ 00:22:22.850 Uttam Kumaran: They’re gonna this is the problem with the dashboard is this is gonna happen across a couple of items where they’re gonna say, the dashboard doesn’t match the data.
232 00:22:23.060 ⇒ 00:22:25.870 Uttam Kumaran: The biggest thing we can do is if we know that
233 00:22:26.150 ⇒ 00:22:32.490 Uttam Kumaran: the order counts match and the refund amount and the refund counts match. Then it’s just gonna be a time shift.
234 00:22:32.510 ⇒ 00:22:34.209 Uttam Kumaran: and they need to stop
235 00:22:35.110 ⇒ 00:22:41.559 Uttam Kumaran: like they need to just understand that it’s not gonna be accurate, and also tell you if they’re using Tiktok Shop and Amazon.
236 00:22:41.950 ⇒ 00:22:45.690 Uttam Kumaran: they all 3 have different ways of recognizing these figures.
237 00:22:45.690 ⇒ 00:22:46.360 Payas Parab: Yup, yeah.
238 00:22:46.574 ⇒ 00:22:51.510 Uttam Kumaran: And this is something that’s just gonna be kind of like a new revelation to them that we just got to explain that.
239 00:22:51.510 ⇒ 00:22:56.169 Payas Parab: Because I think shopify, recognizes at the time of payment the booked revenue. But Amazon does.
240 00:22:56.170 ⇒ 00:22:58.629 Uttam Kumaran: Amazon. Amazon. Yeah. Amazon does.
241 00:22:58.630 ⇒ 00:23:01.180 Payas Parab: When the payment’s processing right?
242 00:23:01.690 ⇒ 00:23:06.169 Uttam Kumaran: Amazon does it when the payments processing which for another client, we basically
243 00:23:06.828 ⇒ 00:23:08.619 Uttam Kumaran: we had to allow
244 00:23:08.760 ⇒ 00:23:11.319 Uttam Kumaran: stuff that wasn’t paid fully
245 00:23:11.450 ⇒ 00:23:14.389 Uttam Kumaran: because they wanted it to match the dashboard. Basically, that was like.
246 00:23:14.390 ⇒ 00:23:18.414 Payas Parab: Okay, okay, this is super helpful man. It’s nice to have this.
247 00:23:18.750 ⇒ 00:23:20.590 Uttam Kumaran: Sorry. I’ll try to join more of these.
248 00:23:20.590 ⇒ 00:23:21.880 Brian Pei: Thanks you Tom.
249 00:23:22.235 ⇒ 00:23:26.469 Uttam Kumaran: You’re welcome. It’s just like, yeah. I mean, this just took me
250 00:23:26.790 ⇒ 00:23:28.230 Uttam Kumaran: a long time to figure out so.
251 00:23:28.230 ⇒ 00:23:39.549 Payas Parab: Yeah, the one thing I did want to. Also, if you have any viewpoint on, like, basically, I have like a 1% delta between like number of orders. Number of sales. I’m wondering if that’s like time zone.
252 00:23:39.550 ⇒ 00:23:47.890 Uttam Kumaran: I would. I would find the orders that what you can do is you can. The easiest way to do this is export all the orders
253 00:23:48.180 ⇒ 00:23:54.039 Uttam Kumaran: that you’re like looking in the subset from shopify, if you can, all the order ids, and then just do a select
254 00:23:54.140 ⇒ 00:23:56.510 Uttam Kumaran: order. Id from snowflake.
255 00:23:56.650 ⇒ 00:23:58.770 Uttam Kumaran: whatever the table is. That’s not in
256 00:23:59.220 ⇒ 00:24:03.761 Uttam Kumaran: that list, and you’ll see the difference and then just find it.
257 00:24:04.140 ⇒ 00:24:04.860 Payas Parab: Makes sense, smart.
258 00:24:04.860 ⇒ 00:24:05.892 Uttam Kumaran: There shouldn’t be.
259 00:24:06.960 ⇒ 00:24:08.200 Uttam Kumaran: there shouldn’t be a gap.
260 00:24:08.320 ⇒ 00:24:19.140 Uttam Kumaran: The most likely thing is, there’s there’s 2 things, one there, there could be a it’s gonna be a status thing like a payment status or refund status. That may be the issue, or it’s gonna be a timing
261 00:24:19.190 ⇒ 00:24:20.540 Uttam Kumaran: issue. Usually
262 00:24:22.130 ⇒ 00:24:22.520 Nicolas Sucari: Or do.
263 00:24:22.520 ⇒ 00:24:27.159 Uttam Kumaran: Or it’s or it’s or it’s logic, and then it’s kicked back to to Brian.
264 00:24:27.650 ⇒ 00:24:28.350 Nicolas Sucari: Yeah.
265 00:24:28.620 ⇒ 00:24:36.520 Payas Parab: Cool. Alright, let me. That’s a great way to do that. So like I, I will also do that as well to just reconcile that like 1% gap. Let’s look at the
266 00:24:36.540 ⇒ 00:24:42.760 Payas Parab: the orders that are not covered. And then, yeah, I’ll add that to my my list as well for today.
267 00:24:44.070 ⇒ 00:24:44.720 Uttam Kumaran: Oh, yeah.
268 00:24:44.950 ⇒ 00:24:47.150 Nicolas Sucari: And on our
269 00:24:47.220 ⇒ 00:25:00.630 Nicolas Sucari: other thing I already accessed north beam and get the Api token and key. I don’t know if we want to start working on north me because Aman asked us to look into gorgeous
270 00:25:00.986 ⇒ 00:25:16.909 Nicolas Sucari: I’ve been reading the docs also in order to start integrating with that data source. But yeah, I don’t know, Brian, what is like the best option. Or if you wanna look also into that docs and see what we can start doing with gorgeous.
271 00:25:18.287 ⇒ 00:25:24.879 Brian Pei: I think, since I’m going to Florida tomorrow, and then I’m out next week I should
272 00:25:25.240 ⇒ 00:25:27.410 Brian Pei: fix refunds and everything.
273 00:25:27.980 ⇒ 00:25:28.530 Nicolas Sucari: Okay.
274 00:25:28.530 ⇒ 00:25:31.869 Brian Pei: As much as possible with the hours that I have allotted.
275 00:25:33.115 ⇒ 00:25:35.229 Brian Pei: And then either
276 00:25:35.390 ⇒ 00:25:36.740 Brian Pei: next week
277 00:25:37.620 ⇒ 00:25:41.640 Brian Pei: well, I don’t know. I don’t know if Brian or somebody else is gonna come on to help.
278 00:25:41.640 ⇒ 00:25:42.160 Nicolas Sucari: Yeah, yeah, yeah.
279 00:25:42.550 ⇒ 00:25:42.940 Brian Pei: They.
280 00:25:42.940 ⇒ 00:25:43.490 Nicolas Sucari: Don’t worry.
281 00:25:43.490 ⇒ 00:25:44.969 Brian Pei: Week they’ll be able to do field.
282 00:25:44.970 ⇒ 00:25:50.440 Uttam Kumaran: Yeah, I think, Nico, maybe. You’re on that email with the gorgeous team. Can you just ask them.
283 00:25:50.480 ⇒ 00:25:52.210 Uttam Kumaran: can you just email them and say like, How did you.
284 00:25:52.210 ⇒ 00:25:52.650 Nicolas Sucari: Radio.
285 00:25:52.650 ⇒ 00:25:59.200 Uttam Kumaran: Clients typically get your stuff in a snowflake. There is probably like an 80% chance. That guy has no idea. But
286 00:25:59.697 ⇒ 00:26:01.169 Uttam Kumaran: it’s fine. They’ll point.
287 00:26:01.170 ⇒ 00:26:03.450 Brian Pei: But he might know a guy who knows a guy who knows.
288 00:26:03.640 ⇒ 00:26:08.219 Uttam Kumaran: Yeah. But just ask them, because this is probably something that, like a lot of their customers are doing.
289 00:26:09.320 ⇒ 00:26:10.969 Uttam Kumaran: cool. Yeah. Okay.
290 00:26:10.970 ⇒ 00:26:20.669 Nicolas Sucari: I’ll do that, and then we just move forward with gorgeous, and we just bench north beam.
291 00:26:20.670 ⇒ 00:26:26.300 Payas Parab: That’s the right move. I think we just whatever they want is more priority. Let’s just focus on that. I think that’s the move
292 00:26:26.620 ⇒ 00:26:28.000 Payas Parab: cool. Okay.
293 00:26:28.000 ⇒ 00:26:28.740 Nicolas Sucari: Excellent
294 00:26:29.460 ⇒ 00:26:30.250 Nicolas Sucari: great.
295 00:26:31.510 ⇒ 00:26:33.770 Nicolas Sucari: I don’t think I have anything else.
296 00:26:34.305 ⇒ 00:26:40.690 Brian Pei: For, because it’s like the 1% discrepancy like what you said could be time zone stuff.
297 00:26:40.960 ⇒ 00:26:44.880 Brian Pei: It, says the I don’t know what time zone your our shopify reporting is in.
298 00:26:44.980 ⇒ 00:26:46.760 Brian Pei: It says that you can.
299 00:26:46.850 ⇒ 00:26:54.370 Brian Pei: There’s a drop down, menu for time zone. That’ll show you what time zone you’re in, because I guarantee it’s probably not in Utc, and we don’t do.
300 00:26:54.580 ⇒ 00:26:56.970 Brian Pei: You’re not doing a convert time zone in the where clause.
301 00:26:56.970 ⇒ 00:26:58.490 Payas Parab: We’re not doing a convert. Time zone. Yes.
302 00:26:58.490 ⇒ 00:27:00.860 Brian Pei: It’s gotta be something like that.
303 00:27:01.380 ⇒ 00:27:07.500 Payas Parab: Yeah, that that would be my, yeah, that was my like, like, it was like point 5% delta on the number of orders. I’m like
304 00:27:07.510 ⇒ 00:27:09.927 Payas Parab: that usually is like time zone.
305 00:27:10.800 ⇒ 00:27:11.650 Payas Parab: yeah.
306 00:27:11.760 ⇒ 00:27:23.269 Payas Parab: I just like, yeah. And and like, I know that I just like, I think Jared wants the numbers out exactly, so that we can be like cool. This is valid. So I will. I. You said. There’s a dropdown menu in the Admin portal.
307 00:27:23.860 ⇒ 00:27:25.280 Payas Parab: or in.
308 00:27:25.780 ⇒ 00:27:29.049 Brian Pei: That is what Google Gemini AI just told me.
309 00:27:29.050 ⇒ 00:27:30.070 Payas Parab: Okay. Okay. I’ll.
310 00:27:30.410 ⇒ 00:27:31.120 Uttam Kumaran: I’ll see you in.
311 00:27:31.120 ⇒ 00:27:35.649 Payas Parab: Google Gemini AI tells me, and then, do that. Okay, I’ll check that alright
312 00:27:36.390 ⇒ 00:27:37.830 Payas Parab: cool thanks, man.
313 00:27:38.030 ⇒ 00:27:53.429 Uttam Kumaran: Slack pi slack. If there’s any other of these like small things. But yeah, I think the easiest debug process is literally just export the order Ids. You can load it into Snowflake. On the left side there’s like a literally like an add Csv, or you could just do?
314 00:27:53.480 ⇒ 00:28:00.509 Uttam Kumaran: Or you could just probably ask Chat Gp to say, How do I check whether these order Ids are in this list? Basically select order. Id, where is in.
315 00:28:00.510 ⇒ 00:28:00.960 Payas Parab: Yeah.
316 00:28:00.960 ⇒ 00:28:07.570 Uttam Kumaran: Separated, and you’ll you’ll just you’ll literally, it’ll just like tell you. The nice thing is, you may have to go through a couple, but
317 00:28:07.750 ⇒ 00:28:09.120 Uttam Kumaran: just break it down one by one.
318 00:28:09.120 ⇒ 00:28:12.520 Payas Parab: Yeah, yeah, makes sense. That’s that’s that’s a good strategy. I’ll do that.
319 00:28:16.790 ⇒ 00:28:17.390 Nicolas Sucari: Cool.
320 00:28:17.650 ⇒ 00:28:23.600 Nicolas Sucari: Okay? Well, thanks, guys, I’ll let you know if we have any updates on the real stuff, I’ll share it in the channel. Okay.
321 00:28:24.280 ⇒ 00:28:24.866 Payas Parab: Sounds good.
322 00:28:25.160 ⇒ 00:28:25.840 Brian Pei: Sweet.
323 00:28:26.420 ⇒ 00:28:27.680 Payas Parab: Alright. See? You guys.
324 00:28:28.310 ⇒ 00:28:28.960 Brian Pei: Thanks.
325 00:28:29.330 ⇒ 00:28:30.069 Nicolas Sucari: Bye, bye.