2024-10-28_javy-project-internal-review_8258f540

Meeting Title: Javy-Project-Internal-Review Date: 2024-10-28 Meeting participants: Luke Daque, Nicolas Sucari, Uttam Kumaran, Payas Parab, Robert Tseng

---

WEBVTT

1 00:02:50.900 ⇒ 00:02:51.800 Luke Daque: Hi Robert!

2 00:02:54.930 ⇒ 00:02:56.679 Robert Tseng: Hey, Luke, how’s it going.

3 00:02:57.510 ⇒ 00:03:00.639 Luke Daque: Yeah, I’m doing well. How are you? How was your weekend.

4 00:03:01.840 ⇒ 00:03:03.960 Robert Tseng: Weekend was great. Yeah,

5 00:03:04.990 ⇒ 00:03:09.710 Robert Tseng: yeah, doing doing. All right. I’ll once more people hop on. I’ll

6 00:03:10.230 ⇒ 00:03:12.653 Robert Tseng: I’ll turn on my camera and and

7 00:03:13.110 ⇒ 00:03:18.519 Robert Tseng: does properly introduced myself to you. I don’t think I’ve really had like a full on conversation with you before.

8 00:03:19.050 ⇒ 00:03:21.089 Luke Daque: Cool. Yeah, we haven’t yet.

9 00:03:27.986 ⇒ 00:03:28.840 Nicolas Sucari: Hi guys.

10 00:03:31.910 ⇒ 00:03:32.689 Robert Tseng: And you go.

11 00:03:34.620 ⇒ 00:03:35.640 Nicolas Sucari: Are you Roberts.

12 00:03:36.750 ⇒ 00:03:38.340 Robert Tseng: I’m good.

13 00:03:39.450 ⇒ 00:03:41.699 Robert Tseng: Let me just ping Pius

14 00:04:06.660 ⇒ 00:04:08.260 Robert Tseng: for rates. Okay.

15 00:04:16.550 ⇒ 00:04:18.760 Robert Tseng: okay. Hello. Hello! Everyone.

16 00:04:23.890 ⇒ 00:04:24.750 Nicolas Sucari: All here.

17 00:04:25.990 ⇒ 00:04:26.730 Robert Tseng: Nice.

18 00:04:27.390 ⇒ 00:04:33.793 Robert Tseng: Well, thanks for yeah, I know this is our weekly sync, and I haven’t really been that active the past couple of weeks. But

19 00:04:34.500 ⇒ 00:04:36.410 Robert Tseng: yeah, just wanted to.

20 00:04:36.450 ⇒ 00:04:55.959 Robert Tseng: I mean, I’ve the the chat’s been pretty lively in the past few days, so I don’t, I think, that speaks for itself. But yeah, I was hoping that in this meeting we can kind of just call out kind of where we’re at in terms of the reporting progress, because that seems to be something that the joby team is really paying attention to now.

21 00:04:56.957 ⇒ 00:05:03.130 Robert Tseng: Yeah, even just the fact that their CEO Justin is asking for snowflake credits, and he wants to log in to look at things.

22 00:05:03.446 ⇒ 00:05:04.879 Robert Tseng: You know I think it’s

23 00:05:05.910 ⇒ 00:05:15.839 Robert Tseng: I’m not. I’m not worried, but it should make us, you know, just feel like there’s a there’s a fire under us to just make sure that we have have things squared up. I don’t want him being confused.

24 00:05:15.950 ⇒ 00:05:31.709 Robert Tseng: So yeah, I think just to summarize kind of where I left off with the Javi team like last weekend, you know Jared has been the main stakeholder for the 1st reports that we put out that Pius has, you know, done incredible work to put together.

25 00:05:33.690 ⇒ 00:05:43.939 Robert Tseng: yeah, I know that he has just kind of had a lot of lot of changing requests or small adjustments that have led to big accusations like for the fact that

26 00:05:44.280 ⇒ 00:05:58.209 Robert Tseng: with pious is that that’s what pies put together because we can’t have dynamic filter date filtering right now. It’s not usable. So there’s a lot of strong language that’s thrown around by their team. But I guess we’re kind of

27 00:05:58.560 ⇒ 00:06:04.320 Robert Tseng: the brain forge guys from it. It’s fine, like, well, we’re filtering it through. But yeah, I just wanted to.

28 00:06:04.320 ⇒ 00:06:05.240 Payas Parab: And honestly.

29 00:06:05.240 ⇒ 00:06:06.380 Robert Tseng: Able to do it. Yeah.

30 00:06:06.380 ⇒ 00:06:11.789 Payas Parab: Yeah, Robert, sorry about that man like I. I think their their criticism is valid. I think it like makes sense to like.

31 00:06:11.810 ⇒ 00:06:31.740 Payas Parab: you know, put like, put it in real. I’ve started doing that in the thing I just sent so it’s like more dynamic and things like that. I guess it was like I wasn’t as familiar. So I just figured we’d crank it in Meta Base to get them something. But it seems that that Meta Base isn’t as flexible as we’d hoped. So like my my apologies on that. That’s that’s on me.

32 00:06:32.260 ⇒ 00:06:39.559 Robert Tseng: Yeah, no worries. I think. We’re, you know. I guess, even the way that you’re using metabase, like all custom sequel, which is like, you know.

33 00:06:41.040 ⇒ 00:06:44.740 Robert Tseng: you’re really just using it as like your visualization layer.

34 00:06:45.190 ⇒ 00:06:50.346 Robert Tseng: there are ways to make a metabase more dynamic.

35 00:06:51.240 ⇒ 00:07:18.109 Robert Tseng: I tried to do it, using your existing query and was not able to, which is something we can get to later. So yeah, I think what has worked best when we’ve used Meta base with our snowflake just like speaking to like other clients like Stella or whatever that’s when, like the Brainforge guys have been able to do a lot of the modeling and the stitching of like the tables, like like kind of before it gets into

36 00:07:18.469 ⇒ 00:07:21.769 Robert Tseng: metabase. And so we’re able to set up these like

37 00:07:21.810 ⇒ 00:07:36.718 Robert Tseng: custom filters or drill down pretty easily. So I know, like pies. You’re just like going straight from raw and yeah, that’s that’s fine. And like, obviously, that’s just like temporary to get them something. I I understand that. But yeah, I think

38 00:07:37.670 ⇒ 00:07:49.329 Robert Tseng: maybe that’s something we we can adjust that cadence later. If you know if we can get the exploration done in real and the final product and Meta base so that there’s no confusion. I think that’d probably be better.

39 00:07:49.900 ⇒ 00:07:53.509 Robert Tseng: One other call out, is, I already discussed this with you, Tom?

40 00:07:54.087 ⇒ 00:07:59.980 Robert Tseng: But basically, I just think the way that we’ve been communicating like tooling towards the job team is

41 00:08:00.090 ⇒ 00:08:05.060 Robert Tseng: been kind of confusing, and that’s something else that I got Jared to talk through was like

42 00:08:05.120 ⇒ 00:08:17.619 Robert Tseng: he? You know he’s never been in Snowflake, real or Meta Base. He doesn’t even know he kept calling Meta! Base Meta! And you know he’s like, does not, does not know any of these tools. He just wants to go to one place and see everything right

43 00:08:17.830 ⇒ 00:08:36.120 Robert Tseng: that he wants to see. And yeah, I think that’s something we just have to accept like, that’s their paradigm as much as we want to push them into different tools right now. And like kind of train them up. I think that that’s a longer process that hopefully, with our check in with them. Tomorrow we’ll be able to kind of

44 00:08:36.620 ⇒ 00:08:38.669 Robert Tseng: Claire clarify some of the

45 00:08:38.730 ⇒ 00:08:48.320 Robert Tseng: when do you use real? When do you use Meta base? But I don’t wanna like kind of be stuck in that limbo because they view us as being indecisive of like, not choosing a tool for them.

46 00:08:48.891 ⇒ 00:08:53.309 Robert Tseng: When like, you know, I think that’s just a misconception. So.

47 00:08:53.854 ⇒ 00:09:00.990 Robert Tseng: Yeah, I guess before I kind of turn turn things over. Just I know Pisi had a couple of things you wanted to point out from your end.

48 00:09:01.602 ⇒ 00:09:25.890 Robert Tseng: Yeah, that’s pretty much like kind of the status update there. I think that most most urgent thing is just to get Jared like dynamic filtering on the data that we already have in Meta Base. So whether it’s like I go in there and I redo the queries or whatever I don’t know. Maybe you already had a solution in real but I did say that we would demo that tomorrow for him, and I think it can be done very quickly. I just

49 00:09:25.930 ⇒ 00:09:37.400 Robert Tseng: probably just wasn’t able to do it. Like on Friday. So yeah, I think that’s probably the like. The most pressing thing to to get get to them? Hopefully by tomorrow.

50 00:09:41.030 ⇒ 00:09:42.183 Payas Parab: Got it.

51 00:09:43.300 ⇒ 00:09:56.700 Payas Parab: Oh, yeah, Robert, you want me to jump in here kind of just the things I flagged. So yeah, so on the I think in terms of like giving like an interactive ui, that’s like best. I think real works really. Well, I just realized, like we can adjust the the

52 00:09:57.000 ⇒ 00:10:22.130 Payas Parab: I I’m like adjusting the some of the things like they don’t tie out to shopify because they use some like different stuff. So I call out to you guys just like total price isn’t how shopify aggregates it. It’s total line items price for at least maybe with them. And then the discount stuff. The only thing that we still need to like solve for really the rest is actually just adjusting formulas and real, which I’m doing in that branch that I shared there. So I would love like once I finish that

53 00:10:22.430 ⇒ 00:10:27.700 Payas Parab: just like someone who’s like, really good at real to just double check. And I’m gonna just run through it. I have real

54 00:10:27.976 ⇒ 00:10:49.279 Payas Parab: so I’m just gonna make sure the numbers tie out. But based on what I’ve already pulled in and adjusted the numbers, tie out to like the August shopify analytics, view. The only thing we really need to solve for is like refunds. And like I’m still stumped. I saw Brian send a message, and I haven’t been able to play around with those tables, but there’s no field in orders that seems to like reconcile the refunds number, which means there has to be some type of other field

55 00:10:49.280 ⇒ 00:11:05.100 Payas Parab: and then given in real. There’s only one time series. We sort of need to like adjust that. I I was wondering if you guys had a solution for that. I know Utah mentioned. You guys have had this issue before with refunds. Jared just wants it to look like shopify analytics, which means that it’s based on the time period that the refund was processed

56 00:11:05.100 ⇒ 00:11:34.799 Payas Parab: in the orders table. None of the date fields actually tie out to that number. So I think we need to have that secondary table of refunds and that secondary table and merging that into real data model that I’m struggling with a little bit which I would love some help on that that’s number one. And then number 2 is like the branch I made a pull request, for I think we should merge that in, so I don’t have to have like an ad hoc query for new versus existing, I confirm that those numbers look right or at least like within the reasonable ballpark like within, instead of

57 00:11:35.574 ⇒ 00:11:51.240 Payas Parab: using the customer number id instead of that tag. The only tag I think we can reasonably use is is subscription. The others, I think it’s like safe to kind of throw them out. So I’m kind of adjusting the rail to do that. To just be like is subscription. And this customer id with that custom

58 00:11:51.390 ⇒ 00:11:58.050 Payas Parab: formula based on customer order number. So that there’s 2 things. Basically, yeah, refunds is if you can help me get to the bottom of that, and how we

59 00:11:58.090 ⇒ 00:12:08.980 Payas Parab: adjust the created at date for the refund table, so that when it all flows into real it looks the same, and then, second, is kind of just merging in that. So we can just pull straight from

60 00:12:09.050 ⇒ 00:12:14.250 Payas Parab: the. Those are the 2 2 main items from you guys. Is that clear. I can clarify if needed.

61 00:12:16.090 ⇒ 00:12:25.979 Nicolas Sucari: I think it’s yeah. I I’m seeing everything there on Github. I think we need to 1st merge the branch that you had before, not the one that is just shopify. Dash right.

62 00:12:25.980 ⇒ 00:12:26.409 Payas Parab: Yup! Yup!

63 00:12:26.410 ⇒ 00:12:30.849 Nicolas Sucari: So I think that one and that one is ready. It doesn’t need anything else. I’m correct.

64 00:12:31.280 ⇒ 00:12:41.069 Payas Parab: Correct. I would just just give it a just someone should give it a good look and give me questions or something, if it it shouldn’t have been a big fix. It’s just pulling, adjusting the upstream. But

65 00:12:41.497 ⇒ 00:12:44.569 Payas Parab: just someone should just double check it. That that’s yeah.

66 00:12:44.570 ⇒ 00:12:51.270 Nicolas Sucari: Maybe maybe, Ryan, you can check that, pull that branch and that pull request is the only pull request that is open. Right now

67 00:12:51.300 ⇒ 00:12:53.609 Nicolas Sucari: I’m gonna send that to you. If you want.

68 00:12:53.610 ⇒ 00:12:54.539 Luke Daque: You can take a look at the.

69 00:12:54.540 ⇒ 00:12:55.190 Nicolas Sucari: Code. I can’t.

70 00:12:55.190 ⇒ 00:13:06.240 Luke Daque: Take a look at it. Yeah, I just already started looking into like the discrepancies based on the slack conversation like the sequel. Query versus like what we are having in real.

71 00:13:06.370 ⇒ 00:13:16.090 Luke Daque: And yeah, there’s a couple of things that I already noticed like the SQL. Query was only used filtering shopify. It was only for the shopify tables.

72 00:13:16.290 ⇒ 00:13:22.450 Luke Daque: whereas in real we have both Amazon and shopify right? And then the time zone also was like

73 00:13:22.810 ⇒ 00:13:29.559 Luke Daque: the query was converting some time zone to Edt or something, and like in real we we weren’t doing that.

74 00:13:30.062 ⇒ 00:13:34.167 Luke Daque: What else was that? There was also like filters to like

75 00:13:34.700 ⇒ 00:13:36.200 Luke Daque: Was it a tiktok?

76 00:13:36.360 ⇒ 00:13:36.900 Luke Daque: Is.

77 00:13:36.900 ⇒ 00:13:37.390 Payas Parab: Yeah.

78 00:13:37.390 ⇒ 00:13:39.099 Luke Daque: Description or something. So yeah.

79 00:13:39.290 ⇒ 00:13:57.209 Payas Parab: I I think there’s 1 thing on that I noticed, by the way, is is Tiktok is a is a tag I saw it’s coming from the tags, and these tags are like, genuine, like, very unreliable. So I think we actually need to build in. And I can add this to the Pr. It should be just based on. If tick tock order Id is null.

80 00:13:57.270 ⇒ 00:14:08.699 Payas Parab: because I think they’re not like the tags aren’t necessarily right. So I think we should use. This is Tiktok order Id. My main focus is just on the shopify. So I know we’re aggregating. We’re gonna build this like aggregated one. But the main thing.

81 00:14:08.700 ⇒ 00:14:09.180 Nicolas Sucari: Okay.

82 00:14:09.180 ⇒ 00:14:16.129 Payas Parab: Getting sure, making sure the shopify stuff is exactly right before we start stacking the other ones. Because, yeah.

83 00:14:16.130 ⇒ 00:14:25.594 Nicolas Sucari: I’ll I’ll say, because you’re you’re on that new one that you just send that new branch. You’re using the dev table from shopify. I think that’s fine. I mean

84 00:14:25.910 ⇒ 00:14:26.380 Payas Parab: Yeah, yeah.

85 00:14:26.380 ⇒ 00:14:31.890 Nicolas Sucari: Nothing different. But I think, Ryan, maybe we can check that table and see if we can.

86 00:14:32.265 ⇒ 00:14:55.934 Nicolas Sucari: Have a version of those of that table from only from shopify into prod folder, create a new one so that we can start using that for real. Then we can do all of the analysis there by us and create dashboard in real from that new table of product. It it, as as Brian said last week, it doesn’t need any change. But just in case we we use the same pro tables for everything. And then

87 00:14:56.400 ⇒ 00:15:13.804 Nicolas Sucari: yeah, the Tiktok one. I think we’re getting all the information from from shopify. But if you say that those tags are reliable, yeah, maybe we can change the whole logic to bring that field from the other column that we have. That is the Tiktok id, and yeah, and we can. And we can change that.

88 00:15:14.300 ⇒ 00:15:28.439 Nicolas Sucari: yeah, that shouldn’t be so difficult to do. It should be a quick change and we can have that. Yeah. Modeled maybe what we can do is schedule some time like after the meeting we can stay for us and Ryan, and just.

89 00:15:28.440 ⇒ 00:15:30.659 Payas Parab: Yeah, get to get into it. Yeah.

90 00:15:30.660 ⇒ 00:15:31.960 Nicolas Sucari: Yeah. And just

91 00:15:32.160 ⇒ 00:15:37.770 Nicolas Sucari: yeah, go hard on that one. Try to figure it out everything and and have everything ready in real.

92 00:15:38.160 ⇒ 00:15:38.580 Payas Parab: Perfect.

93 00:15:38.580 ⇒ 00:15:42.679 Nicolas Sucari: So you’re gonna use real in order to share that with Jared and Justin.

94 00:15:42.900 ⇒ 00:15:53.179 Payas Parab: I wanna I think that’s the right move. But Robert, like, let me know if it’s better to just adjust the meta based stuff. I think the real can give us the dynamic filtering and tags easier. I don’t know.

95 00:15:53.660 ⇒ 00:15:56.346 Robert Tseng: Yeah. Well, I guess to me

96 00:15:58.040 ⇒ 00:16:01.330 Robert Tseng: no, I mean, I I think just the fact that we have to

97 00:16:01.480 ⇒ 00:16:11.349 Robert Tseng: to to add dynamic filtering for every like metric in metabase. It takes a while in real. You could do it much faster, but I think.

98 00:16:11.650 ⇒ 00:16:13.540 Robert Tseng: like what would

99 00:16:13.700 ⇒ 00:16:17.020 Robert Tseng: I mean? I don’t want whatever way is faster, honestly like, I think I.

100 00:16:17.020 ⇒ 00:16:23.720 Payas Parab: I think real is the fastest real is the fastest, because it already has the you saw. I had to build out like the Compare Monthly. Compare Weekly. Compare.

101 00:16:23.720 ⇒ 00:16:24.940 Robert Tseng: Yeah, you can do that already.

102 00:16:24.940 ⇒ 00:16:28.511 Payas Parab: It can do that automatically. So I think, instead of having to rewrite all that.

103 00:16:29.000 ⇒ 00:16:38.589 Nicolas Sucari: Yeah. The only thing is that you can’t. You can have, like different date filtering for the measures you are seeing in one dashboard right like, if you filter

104 00:16:38.870 ⇒ 00:16:41.650 Nicolas Sucari: some dates on the dashboard, it’s gonna

105 00:16:41.780 ⇒ 00:16:42.330 Nicolas Sucari: you know.

106 00:16:42.760 ⇒ 00:16:46.380 Nicolas Sucari: all of the metrics and dimensions that you’re seeing. Yeah, exactly.

107 00:16:46.490 ⇒ 00:16:53.959 Nicolas Sucari: If if not. If you want to have different like metrics with different date filterings, you need to create different dashboards or do it in a separate way.

108 00:16:55.740 ⇒ 00:17:01.439 Robert Tseng: Yeah, I mean, like some nuances to Jared’s request is, you know, you’ve seen the

109 00:17:02.240 ⇒ 00:17:10.069 Robert Tseng: you’ve seen like his, his Google sheet that view that he’s like, I want you to just reproduce this. I don’t know if the rest of the team has seen it. Maybe we should flash that.

110 00:17:11.109 ⇒ 00:17:11.449 Payas Parab: Era.

111 00:17:11.450 ⇒ 00:17:11.829 Nicolas Sucari: 19.

112 00:17:11.839 ⇒ 00:17:17.859 Payas Parab: Real quick. I have it open. This is the one where I basically like, put the 1st cut to make sure things were lining up.

113 00:17:18.191 ⇒ 00:17:22.779 Payas Parab: I mentioned this a while ago, but like we kind of didn’t dive into it. But basically, like everything.

114 00:17:23.039 ⇒ 00:17:48.549 Payas Parab: we’re more or less good like, you can see these are like clear formulas, and then they just want he just wants like filters based on like non, sub. Not so I I did this in Meta Base, because it’s like you can easily recreate that with that exact view, right? So like like, hey number of orders. And I want it in this with the total like he’s like, that’s what I want. And that’s what I did here, right? And so the annoying thing is in. Really, you can’t like. Do that customization right where he’s like, I want my total bars and shit like that.

115 00:17:48.739 ⇒ 00:17:55.409 Payas Parab: That’s why I’m just adding in passwords like I I don’t like. I think maybe we then just have to figure out how to adjust the Meta base queries

116 00:17:55.979 ⇒ 00:18:01.669 Payas Parab: because you can’t have these like total rows and stuff, or can you? Maybe you can. I don’t know in real.

117 00:18:02.379 ⇒ 00:18:05.669 Payas Parab: Is there like an aggregation formula, so you can have like a total row.

118 00:18:05.919 ⇒ 00:18:06.979 Payas Parab: or like a grouping.

119 00:18:06.980 ⇒ 00:18:07.909 Nicolas Sucari: I mean, if you go

120 00:18:08.190 ⇒ 00:18:15.566 Nicolas Sucari: yeah, I mean, you can go to like the pivot table part and create like an add fields. I don’t know if you can add those ones.

121 00:18:16.390 ⇒ 00:18:17.950 Nicolas Sucari: I’m not sure.

122 00:18:17.950 ⇒ 00:18:20.540 Payas Parab: Just put together this expression right and like my.

123 00:18:20.540 ⇒ 00:18:21.000 Nicolas Sucari: Yeah.

124 00:18:21.000 ⇒ 00:18:34.340 Payas Parab: Like what he’s looking for is like a grouping set right and like that, like where you have like the totals by category. And he wants them like in this exact order, because he was like, this is exactly what I want, nothing else. So I figured that had to be done in

125 00:18:35.390 ⇒ 00:18:36.050 Payas Parab: in medical.

126 00:18:36.050 ⇒ 00:18:36.510 Uttam Kumaran: Just

127 00:18:36.970 ⇒ 00:18:38.129 Payas Parab: You can make that. Yeah.

128 00:18:38.130 ⇒ 00:18:40.659 Uttam Kumaran: I don’t know. I feel like you can just create Ryan. You could just.

129 00:18:40.660 ⇒ 00:18:40.990 Nicolas Sucari: And then.

130 00:18:40.990 ⇒ 00:18:42.460 Uttam Kumaran: New metrics.

131 00:18:42.460 ⇒ 00:18:43.100 Nicolas Sucari: Yeah, exactly.

132 00:18:43.100 ⇒ 00:18:49.270 Uttam Kumaran: So you can just literally duplicate the metric and just put a where clause on the metric in real.

133 00:18:49.270 ⇒ 00:18:50.200 Payas Parab: Can you put so.

134 00:18:50.200 ⇒ 00:18:50.960 Uttam Kumaran: On the expression.

135 00:18:50.960 ⇒ 00:18:52.409 Payas Parab: So this is just a SQL. Expression.

136 00:18:52.410 ⇒ 00:19:01.110 Uttam Kumaran: Yes, so what you would do is you do total revenue. You could add a next one that’s like total revenue blank. And then just do. Expression is some total price where blank basically.

137 00:19:01.110 ⇒ 00:19:01.820 Payas Parab: Got it?

138 00:19:02.050 ⇒ 00:19:04.610 Payas Parab: Alright, yeah, that that’s fine. Okay.

139 00:19:05.220 ⇒ 00:19:08.280 Uttam Kumaran: And then I mean on honestly, what? Yeah, I mean, that’s 1 thing.

140 00:19:08.280 ⇒ 00:19:15.299 Payas Parab: Yeah, I just worry with the filters like the other filters that might break the logic right? Like, what if in that field

141 00:19:15.490 ⇒ 00:19:27.689 Payas Parab: like suddenly, if he adds like a filter to the main dash right! Which filters every chart on there, then it’ll be like, Oh, this is missing, or whatever like like that. That was my word around doing that with the custom in here.

142 00:19:29.000 ⇒ 00:19:35.860 Nicolas Sucari: Yeah. And that’s what I’m saying that if you filter, yeah, anything on the dashboard is, gonna get everything filtered. Yeah, all of the metrics.

143 00:19:36.050 ⇒ 00:19:41.430 Uttam Kumaran: Like. That’s the thing you you if we do the fixed metrics, you’re gonna you’re gonna run into that risk.

144 00:19:41.470 ⇒ 00:19:47.240 Uttam Kumaran: If we don’t do the fixed metrics where you have all the dimensions you basically filter as you need. Then we don’t run the risk.

145 00:19:48.860 ⇒ 00:19:50.300 Uttam Kumaran: that yeah.

146 00:19:51.310 ⇒ 00:19:57.179 Payas Parab: Yeah, this was. This was, by the way, Robert, this is a little bit of the impasse, because it was like he wants that exact sheet created.

147 00:19:57.650 ⇒ 00:19:59.359 Uttam Kumaran: Why does it want to filter that, too?

148 00:19:59.610 ⇒ 00:20:01.289 Uttam Kumaran: He wants to filter that also.

149 00:20:02.990 ⇒ 00:20:06.729 Payas Parab: I think he just wants to filter the dates like that. That was my sense, Robert. But is it like.

150 00:20:06.730 ⇒ 00:20:09.080 Robert Tseng: He wants to compare like

151 00:20:09.590 ⇒ 00:20:15.540 Robert Tseng: month to month on the on the monthly view, and then on the weekly view. If you go to the click on the weekly tab.

152 00:20:15.690 ⇒ 00:20:16.560 Payas Parab: Yep.

153 00:20:16.560 ⇒ 00:20:17.679 Robert Tseng: Okay.

154 00:20:18.860 ⇒ 00:20:33.289 Robert Tseng: yeah. Like, I think, pies, what you had here. I think it basically Ca- captures like, it’s still like the month, a month bound like time period. He wants it like to be a trailing 12 weeks, because that’s just like the accounting number or whatever. So I mean, I think.

155 00:20:33.290 ⇒ 00:20:43.270 Uttam Kumaran: Ryan. What we should do is we should do this like we did that Kpi ag table for pool parts. Remember, we have, like every metric listed the only thing pies. I would prevent him from

156 00:20:43.540 ⇒ 00:20:47.870 Uttam Kumaran: filtering by dimensions here, cause that’s not gonna work.

157 00:20:47.980 ⇒ 00:20:51.060 Uttam Kumaran: So just don’t allow that. Only allow time.

158 00:20:51.060 ⇒ 00:20:52.480 Payas Parab: Date. Filter, yeah.

159 00:20:52.480 ⇒ 00:20:59.889 Uttam Kumaran: Yeah. But what we could do is we can literally just calculate each of these. Put this on a 1 combined date spine. And

160 00:21:00.110 ⇒ 00:21:03.689 Uttam Kumaran: like we can, I mean, basically give you every single whole number, which is what.

161 00:21:03.690 ⇒ 00:21:04.190 Payas Parab: But I mean.

162 00:21:04.190 ⇒ 00:21:05.230 Uttam Kumaran: We did for another client.

163 00:21:05.230 ⇒ 00:21:18.599 Payas Parab: I I actually made that already in Snowflake. I can send that to you. I made that in Snowflake, but it was just like then he was like, Oh, but I want the totals like right there and then. I was like, All right, guy like, now I’m gonna go make this then, like it’s like he’s like he’s. It’s like.

164 00:21:18.600 ⇒ 00:21:41.139 Uttam Kumaran: No, this, I mean, this is like pretty some people just want they want like every metric. Then they want to see year over, year, month over month. They want to see pretty like normal calculations over it. The the reason why I liked real is because you just does that dynamically and hard code that. But some people want that. And we, this is what we do like. We just build up. We basically build a Kpi table

165 00:21:41.140 ⇒ 00:21:49.439 Uttam Kumaran: where there’s literally just a date spine. There’s maybe one or 2 dimensions if you need it, but otherwise it’s like we just calculate every metric.

166 00:21:50.450 ⇒ 00:21:51.599 Payas Parab: I see. Okay.

167 00:21:51.620 ⇒ 00:21:54.979 Payas Parab: yeah, if you guys could potentially make that for us, then it would.

168 00:21:54.980 ⇒ 00:21:55.820 Uttam Kumaran: Like.

169 00:21:56.090 ⇒ 00:21:56.530 Payas Parab: Yeah.

170 00:21:56.530 ⇒ 00:22:03.499 Uttam Kumaran: The previous month orders and the other one. Can you calculate that in in metabase, or do we should we also do that.

171 00:22:04.520 ⇒ 00:22:07.909 Payas Parab: The previous month. Order, I think. Oh, that’s a good point.

172 00:22:08.310 ⇒ 00:22:09.460 Payas Parab: So this is.

173 00:22:10.260 ⇒ 00:22:11.080 Payas Parab: this is just.

174 00:22:11.080 ⇒ 00:22:12.500 Uttam Kumaran: A new example is.

175 00:22:12.630 ⇒ 00:22:16.499 Uttam Kumaran: we would have to calculate all all of these for every

176 00:22:17.020 ⇒ 00:22:19.979 Uttam Kumaran: every month and every metric, so you can see how it blows up.

177 00:22:21.410 ⇒ 00:22:23.119 Payas Parab: I see. I see. Yeah.

178 00:22:24.110 ⇒ 00:22:25.889 Uttam Kumaran: I mean, that’s also fine, but.

179 00:22:25.890 ⇒ 00:22:27.620 Payas Parab: I I think I think you would. I think.

180 00:22:27.620 ⇒ 00:22:29.780 Robert Tseng: It would just need to be level

181 00:22:29.800 ⇒ 00:22:34.639 Robert Tseng: cause he wants the week on the week view, and then he wants the month, so that ends up being like we’re just.

182 00:22:35.340 ⇒ 00:22:37.878 Uttam Kumaran: Well, we would do. We would do one at the week level.

183 00:22:38.090 ⇒ 00:22:38.410 Robert Tseng: Okay.

184 00:22:38.619 ⇒ 00:22:41.759 Uttam Kumaran: And we would we would have one table. I mean, Brian. This is exactly what.

185 00:22:41.760 ⇒ 00:22:42.160 Luke Daque: Yeah.

186 00:22:42.160 ⇒ 00:22:46.259 Uttam Kumaran: Weekly kpi ag, and then we did monthly kpi ag, where.

187 00:22:46.260 ⇒ 00:22:46.820 Robert Tseng: It’s currently.

188 00:22:46.820 ⇒ 00:22:48.670 Uttam Kumaran: Like week metric

189 00:22:48.730 ⇒ 00:22:49.830 Uttam Kumaran: change

190 00:22:50.464 ⇒ 00:22:55.199 Uttam Kumaran: week, metric value. And then whatever, if you want the 1st previous month.

191 00:22:55.430 ⇒ 00:22:57.699 Uttam Kumaran: whereas previous week, we’re same year. Last week we.

192 00:22:57.700 ⇒ 00:22:58.200 Payas Parab: Yup, yeah.

193 00:22:58.890 ⇒ 00:23:08.149 Uttam Kumaran: It’s not bad to. It’s not bad to do this. By the way, it’s just fixed like, if we have to add something we have to go make a change. It’s not very dynamic.

194 00:23:08.410 ⇒ 00:23:12.179 Luke Daque: Yeah, you can’t drill down to any other dimension or anything.

195 00:23:12.550 ⇒ 00:23:18.799 Robert Tseng: Wait. Sorry I’m not not necessarily following. Why, why can’t our dynamic approach right now like? Do that.

196 00:23:20.865 ⇒ 00:23:24.259 Uttam Kumaran: Because it he doesn’t. That.

197 00:23:24.860 ⇒ 00:23:30.847 Uttam Kumaran: So the dynamic approach would be, you don’t have new subs new non subs. Those are just

198 00:23:31.430 ⇒ 00:23:32.360 Uttam Kumaran: yeah.

199 00:23:32.930 ⇒ 00:23:33.590 Robert Tseng: Okay.

200 00:23:34.990 ⇒ 00:23:35.959 Robert Tseng: yeah, right? Now, we have.

201 00:23:35.960 ⇒ 00:23:39.460 Uttam Kumaran: Subs, and then we have the dimension, so we can. We can add the.

202 00:23:39.460 ⇒ 00:23:39.870 Robert Tseng: Yeah,

203 00:23:40.280 ⇒ 00:23:40.970 Uttam Kumaran: Yeah.

204 00:23:41.160 ⇒ 00:23:41.613 Robert Tseng: Yeah, okay.

205 00:23:45.610 ⇒ 00:23:47.800 Uttam Kumaran: My perspective was, we solve

206 00:23:48.250 ⇒ 00:23:55.140 Uttam Kumaran: the problem first, st and then let’s like, let’s think about what to do longer term. But this is something we can support.

207 00:24:01.640 ⇒ 00:24:16.089 Robert Tseng: Yeah. Well, I guess kind of just kind of get taking a step back to gather what I’m I’m seeing here. So yes, the Meta based view is closer to what Jared wants. I think he just wants the the whatever like the be able to do the comparative dates.

208 00:24:16.730 ⇒ 00:24:44.659 Robert Tseng: But we are also wanting to do this work in real, because ultimately like, that’s what we’re going to be stacking all these other things on. So now that we’re done with, shopify, move on to another source, stack it, and then real becomes that like dynamic area for them to like, you know, ex explore more like, do it add anything else that they want without having to ask us for code changes and then to me, like the Meta base view ends up being like the weekly report that gets sent out to him. Because that’s all he wants to see, like he

209 00:24:44.961 ⇒ 00:25:01.539 Robert Tseng: and then if he wants to go and look into it more like he. He may click into this dashboard and want to be like current month versus 2 months ago, and like that’s not something that you know, we we can really do like, I mean, right? This is fixed to just current month versus previous month, both

210 00:25:01.720 ⇒ 00:25:05.470 Robert Tseng: I month to date and versus previous full month.

211 00:25:05.823 ⇒ 00:25:11.179 Robert Tseng: And like what he’s saying, that that’s not the only view that he wants to see, and that’s why he’s not happy with it.

212 00:25:13.430 ⇒ 00:25:19.150 Uttam Kumaran: We can put that Kpi table in real as well and literally just have week and the metrics.

213 00:25:19.520 ⇒ 00:25:24.460 Uttam Kumaran: The only problem is is like some of these metrics have different levels of dimensionality.

214 00:25:24.660 ⇒ 00:25:28.549 Uttam Kumaran: so the only thing they they may share is the time.

215 00:25:29.858 ⇒ 00:25:35.029 Uttam Kumaran: So we can put the time and every metric in real, and you’ll get those comparative features.

216 00:25:35.300 ⇒ 00:25:35.890 Uttam Kumaran: Yeah.

217 00:25:35.890 ⇒ 00:25:38.990 Nicolas Sucari: Do you want me to put on? Do you want me to share that Kpi.

218 00:25:38.990 ⇒ 00:25:42.009 Uttam Kumaran: Yeah, do you want to just show that? Yeah, yeah.

219 00:25:43.300 ⇒ 00:25:44.250 Nicolas Sucari: Let me!

220 00:25:44.520 ⇒ 00:25:53.930 Uttam Kumaran: Cause we dealt with this. I mean, it’s just like we just dealt with the same issue where one of the people at pool parts was like, I just want the metric. I just want all the metrics in one place.

221 00:25:53.990 ⇒ 00:25:59.080 Uttam Kumaran: Another person was like, I want to be able to call. And yeah, no, that’s so. We did both.

222 00:25:59.450 ⇒ 00:26:00.260 Robert Tseng: Okay, so.

223 00:26:00.260 ⇒ 00:26:08.200 Uttam Kumaran: We put an all orders table in there, and we put basically every single metric here. And, as you see, on the right, all you get is time related? Dimensions.

224 00:26:08.350 ⇒ 00:26:08.970 Nicolas Sucari: Yeah.

225 00:26:10.060 ⇒ 00:26:15.650 Nicolas Sucari: so you can filter by day by year. Order. I mean, I have this filter if I go all time.

226 00:26:16.030 ⇒ 00:26:17.200 Nicolas Sucari: and if you do.

227 00:26:17.200 ⇒ 00:26:19.280 Uttam Kumaran: If you click on compare and you’ll be able to.

228 00:26:19.622 ⇒ 00:26:21.680 Robert Tseng: Between 2 months. Yeah, yeah, okay.

229 00:26:22.790 ⇒ 00:26:23.220 Uttam Kumaran: So.

230 00:26:23.220 ⇒ 00:26:25.130 Nicolas Sucari: Compare I don’t know. Month to day.

231 00:26:25.130 ⇒ 00:26:25.790 Uttam Kumaran: Do like.

232 00:26:26.060 ⇒ 00:26:27.139 Nicolas Sucari: Compared with previous months.

233 00:26:27.140 ⇒ 00:26:27.780 Robert Tseng: Yeah.

234 00:26:31.110 ⇒ 00:26:31.970 Robert Tseng: But the thing is.

235 00:26:31.970 ⇒ 00:26:33.260 Nicolas Sucari: We can. We can put.

236 00:26:33.260 ⇒ 00:26:42.669 Uttam Kumaran: We could put cogs with discounts, with fees, with with like shipping costs when actually they share, they really don’t. They may not share any dimensions apart from time.

237 00:26:42.670 ⇒ 00:26:43.350 Nicolas Sucari: Okay.

238 00:26:45.640 ⇒ 00:26:46.340 Nicolas Sucari: Yeah.

239 00:26:49.400 ⇒ 00:26:50.690 Robert Tseng: Yeah.

240 00:26:51.140 ⇒ 00:27:00.660 Robert Tseng: And then the re, I mean, there will be some like lagging metrics, like refunds, whatever that will still run into the same issue where like, it’s not really. You can’t really like. Just filter them all.

241 00:27:00.660 ⇒ 00:27:02.770 Uttam Kumaran: Then you have to bifurcate. Then it’s like we.

242 00:27:02.770 ⇒ 00:27:03.150 Robert Tseng: Yeah.

243 00:27:03.150 ⇒ 00:27:10.089 Uttam Kumaran: Sales in one area. We put shipping in one area right? And then you have a, you have access to a ton of different yeah.

244 00:27:10.920 ⇒ 00:27:15.430 Nicolas Sucari: If I go to ship and say, Well, I have, like all the dimensions related to shipping, and I

245 00:27:15.830 ⇒ 00:27:17.110 Nicolas Sucari: I’m using this.

246 00:27:17.450 ⇒ 00:27:18.420 Robert Tseng: Yeah, yeah.

247 00:27:19.390 ⇒ 00:27:23.940 Robert Tseng: dude, I mean, this is this, is it like, actually, this is this is, I feel like this would do it.

248 00:27:24.320 ⇒ 00:27:28.240 Robert Tseng: But seems like this is not something we could do by tomorrow, so.

249 00:27:28.870 ⇒ 00:27:39.310 Uttam Kumaran: I think we should try to get the get the metrics to him in the way he wants it. Cause that’s that’s like a subjective problem. But it’s not like that. That work doesn’t get lost like

250 00:27:40.793 ⇒ 00:27:41.490 Uttam Kumaran: so.

251 00:27:42.920 ⇒ 00:27:43.449 Payas Parab: And when you say.

252 00:27:43.450 ⇒ 00:27:45.290 Uttam Kumaran: And always basically building.

253 00:27:45.290 ⇒ 00:27:49.760 Payas Parab: You tell, you mean like, look, get them in in Meta base, basically, or.

254 00:27:49.760 ⇒ 00:27:50.190 Uttam Kumaran: Yeah.

255 00:27:50.620 ⇒ 00:27:51.330 Payas Parab: Okay.

256 00:27:51.590 ⇒ 00:27:52.340 Payas Parab: Okay.

257 00:27:52.340 ⇒ 00:27:59.889 Uttam Kumaran: We can that table with literally hard, like fixed everything you need to just be like, select this, call this column. We can do

258 00:28:00.641 ⇒ 00:28:06.354 Uttam Kumaran: and then I think we solve this problem. And then we. And then we think about a more flexible model.

259 00:28:07.560 ⇒ 00:28:16.369 Uttam Kumaran: the the thing is, yeah, it’s just yeah, I mean. But but, Ryan, you kind of get that that the Kpi table basically right?

260 00:28:16.370 ⇒ 00:28:17.040 Luke Daque: Yeah.

261 00:28:17.600 ⇒ 00:28:18.810 Luke Daque: makes sense.

262 00:28:23.100 ⇒ 00:28:23.600 Luke Daque: But yeah.

263 00:28:23.600 ⇒ 00:28:24.690 Payas Parab: Ryan. What Ryan.

264 00:28:24.690 ⇒ 00:28:25.249 Luke Daque: We can get that.

265 00:28:25.250 ⇒ 00:28:25.680 Payas Parab: My room.

266 00:28:25.680 ⇒ 00:28:26.799 Luke Daque: Tomorrow, or like.

267 00:28:26.800 ⇒ 00:28:37.350 Payas Parab: If I wrote out the like, the sequel queries like essentially that I’m using in there. And it’s just like the app like we we could, because I’ve already done the work for all of these metrics. It’s just like a matter of

268 00:28:37.490 ⇒ 00:28:45.820 Payas Parab: turning that specific expression like there’s like one. There’s a whole bunch of shit in there that does this whole month on month and total aggregation. But if I get rid of that.

269 00:28:46.130 ⇒ 00:28:46.720 Luke Daque: And just.

270 00:28:46.720 ⇒ 00:28:48.220 Payas Parab: You the key metric.

271 00:28:48.530 ⇒ 00:28:50.600 Payas Parab: and then we make it so

272 00:28:51.000 ⇒ 00:28:54.420 Payas Parab: like, I guess if I get you, each of those key metrics

273 00:28:54.600 ⇒ 00:28:59.260 Payas Parab: is the Kpi spine, built by like a grouping set, essentially of some kind.

274 00:28:59.330 ⇒ 00:29:02.719 Payas Parab: like similar build to what I’ve done already in metabase.

275 00:29:03.030 ⇒ 00:29:05.020 Payas Parab: Do you remember like how it’s kind of built.

276 00:29:05.020 ⇒ 00:29:07.069 Luke Daque: Yeah, I think I think that should be

277 00:29:07.760 ⇒ 00:29:13.509 Luke Daque: pretty much easy to do. Once we get like once we get all the metrics, the logic for all the metrics that you have.

278 00:29:13.780 ⇒ 00:29:15.059 Luke Daque: Yeah, that should be

279 00:29:15.140 ⇒ 00:29:21.050 Luke Daque: should be pretty easy to do, because we can just build the time spine and then just link it to that. Yeah.

280 00:29:21.050 ⇒ 00:29:27.190 Payas Parab: So if I if I pull all those sequels from Meta base and just put them in together, and then maybe it’s just like cleaning up and aggregating. That’s something.

281 00:29:27.190 ⇒ 00:29:29.610 Luke Daque: Yeah, pretty quickly. I think. So. Yeah, yep.

282 00:29:29.610 ⇒ 00:29:30.250 Payas Parab: Okay.

283 00:29:30.710 ⇒ 00:29:31.510 Payas Parab: Okay.

284 00:29:32.830 ⇒ 00:29:34.079 Robert Tseng: Okay. I had already sent that.

285 00:29:34.080 ⇒ 00:29:34.449 Uttam Kumaran: Don’t worry.

286 00:29:34.450 ⇒ 00:29:34.970 Robert Tseng: And stuff.

287 00:29:34.970 ⇒ 00:29:35.840 Uttam Kumaran: Don’t worry about the.

288 00:29:35.840 ⇒ 00:29:36.460 Robert Tseng: Okay.

289 00:29:36.460 ⇒ 00:29:40.229 Uttam Kumaran: Yeah, don’t worry about the month over month. Logic like that Will.

290 00:29:40.480 ⇒ 00:29:45.310 Uttam Kumaran: We’ll handle just anything around the metrics like the where clauses, the filters or whatever yeah.

291 00:29:45.780 ⇒ 00:29:46.410 Robert Tseng: Okay.

292 00:29:53.250 ⇒ 00:29:54.300 Robert Tseng: Okay. Cool.

293 00:29:55.751 ⇒ 00:30:00.349 Nicolas Sucari: Other stuff guys. I emailed gorgeous team to

294 00:30:00.380 ⇒ 00:30:20.889 Nicolas Sucari: ask about what’s the best way in order to integrate with their data. There is a fight from light connector. So maybe we can start working with that one. And if we need anything else more like live data, we can go to the web hooks. But I think the Api is gonna be like the 1st step into bringing that data in

295 00:30:21.558 ⇒ 00:30:23.149 Nicolas Sucari: what do you think?

296 00:30:23.390 ⇒ 00:30:24.090 Nicolas Sucari: Yeah.

297 00:30:25.760 ⇒ 00:30:28.749 Uttam Kumaran: Yeah, I think, just, I think, just use the 5 train if it’s there.

298 00:30:29.450 ⇒ 00:30:30.040 Nicolas Sucari: Yeah.

299 00:30:30.720 ⇒ 00:30:36.990 Nicolas Sucari: I think it’s the easiest way to do it. Bring the data in and then decide on if they need like more.

300 00:30:37.500 ⇒ 00:30:37.830 Uttam Kumaran: Yeah.

301 00:30:37.830 ⇒ 00:30:40.369 Nicolas Sucari: Updates or anything else, we can go other way.

302 00:30:42.310 ⇒ 00:30:45.660 Robert Tseng: Do? Do we have a prioritization on like which sources we’re bringing in.

303 00:30:47.780 ⇒ 00:30:48.190 Nicolas Sucari: We just don’t.

304 00:30:48.190 ⇒ 00:30:50.020 Robert Tseng: After whatever is easiest. Yeah.

305 00:30:50.790 ⇒ 00:31:01.640 Nicolas Sucari: A aman asked us about gorgeous to prioritize next, and then because we were talking about north beam and gorgeous, and he told us to go with gorgeous and north beam next

306 00:31:01.800 ⇒ 00:31:05.019 Nicolas Sucari: or yeah, after gorgeous, we all need to

307 00:31:05.210 ⇒ 00:31:07.609 Nicolas Sucari: sync again and see what’s the next one.

308 00:31:08.030 ⇒ 00:31:08.710 Robert Tseng: Okay.

309 00:31:13.200 ⇒ 00:31:13.800 Nicolas Sucari: Okay.

310 00:31:15.210 ⇒ 00:31:16.383 Robert Tseng: I think

311 00:31:17.250 ⇒ 00:31:40.550 Robert Tseng: at some point, I mean, I don’t know if I feel like Justin and Jared will bring this up tomorrow, but, like right now we. We have all this down for shopify, but they also want to see the shopify plus Amazon or the shopify plus Tiktok be able to have 2 marketplaces in one space. That’s not. That’s not something that they’ve ever seen before. So I think that whichever one we end up going with I would

312 00:31:40.840 ⇒ 00:31:45.619 Robert Tseng: like first, st I think, would be important to to get out of our our time with them.

313 00:31:47.800 ⇒ 00:31:50.830 Uttam Kumaran: Nico, don’t we have shopify? And Amazon already.

314 00:31:50.830 ⇒ 00:31:52.799 Payas Parab: There’s 1 with shopify. And Amazon. Yeah.

315 00:31:52.800 ⇒ 00:31:57.029 Nicolas Sucari: Sorry. Sorry I was. I think I thought I was showing it, because I can’t hear.

316 00:31:57.390 ⇒ 00:32:13.959 Nicolas Sucari: Yeah. So we have the order dashboard. And this is shopify and Amazon ready, and we have the tag for his Tiktok shop. I mean, we need to work on this. This tag is not the best one that we can use, but the table already has the all of the orders from shopify and Amazon. Yep.

317 00:32:14.550 ⇒ 00:32:18.050 Nicolas Sucari: in Snowflake is the let me go to Snowflake.

318 00:32:18.050 ⇒ 00:32:25.189 Uttam Kumaran: So I think we already like, yeah, basically, if if we need to carve is our is Tiktok considered like.

319 00:32:25.190 ⇒ 00:32:25.860 Nicolas Sucari: Are you? I guess.

320 00:32:25.860 ⇒ 00:32:27.479 Uttam Kumaran: Shopify Amazon and Tiktok.

321 00:32:27.730 ⇒ 00:32:29.719 Payas Parab: Yeah, I would. I was gonna say, I think I think today.

322 00:32:29.720 ⇒ 00:32:33.519 Uttam Kumaran: They don’t care about shopify as the as the fulfill as the

323 00:32:33.720 ⇒ 00:32:35.279 Uttam Kumaran: payment whatever for it.

324 00:32:35.280 ⇒ 00:32:37.249 Robert Tseng: Yeah, they view it as separate. Yeah.

325 00:32:37.250 ⇒ 00:32:39.790 Uttam Kumaran: Okay, then, yeah, then we we’re basically there.

326 00:32:39.790 ⇒ 00:32:40.590 Robert Tseng: Okay.

327 00:32:40.710 ⇒ 00:32:47.210 Nicolas Sucari: Yeah. So these 2 tables in in broad. They both have Amazon and shopify information.

328 00:32:48.280 ⇒ 00:32:48.720 Robert Tseng: Okay.

329 00:32:48.720 ⇒ 00:32:49.820 Nicolas Sucari: They have everything. Okay.

330 00:32:49.820 ⇒ 00:32:50.560 Payas Parab: But then.

331 00:32:50.560 ⇒ 00:32:51.190 Nicolas Sucari: Gonna try.

332 00:32:51.190 ⇒ 00:33:08.430 Payas Parab: We’re making some changes to the because some of the logic on the like total price versus total line items, price of the shipping, the way they’re looking at like the shipping costs that they want to tie out. We’re gonna also have to adjust that in this table as well, right? Because this is pulling from like, how how is like revenue calculated there?

333 00:33:09.480 ⇒ 00:33:12.880 Payas Parab: Because there’s like different revenue for each one? Right? But the shopify one at.

334 00:33:12.880 ⇒ 00:33:29.680 Uttam Kumaran: So that’s what we basically. So we do each we do each individually. So we’ll do shopify orders. We do Amazon. And of course, shopify, and Tiktok is all on shopify, and then we union those together on common fields, so they inherit whatever the definitions of the logic is from the source system.

335 00:33:29.810 ⇒ 00:33:32.379 Uttam Kumaran: But this is like a really great point

336 00:33:32.969 ⇒ 00:33:41.700 Uttam Kumaran: Nico and Ryan. We just really need to have some documentation, so that when they try to PIN one of us down on the definition

337 00:33:41.740 ⇒ 00:33:45.619 Uttam Kumaran: we like have a notion somewhere that we like rely on, because

338 00:33:45.880 ⇒ 00:33:48.769 Uttam Kumaran: remembering this stuff is gonna get complicated.

339 00:33:48.860 ⇒ 00:33:55.499 Uttam Kumaran: Especially, it’s like what goes in. We’re gonna continue to get the question. Because we just this is what happened with cool parts is

340 00:33:55.540 ⇒ 00:34:03.010 Uttam Kumaran: they’re gonna ask what goes into that again. Why is the dashboard not matching this like those things we really should just write down and have ready to go.

341 00:34:03.430 ⇒ 00:34:04.740 Luke Daque: Right? Yeah.

342 00:34:04.900 ⇒ 00:34:05.640 Luke Daque: yeah.

343 00:34:06.010 ⇒ 00:34:06.720 Luke Daque: okay.

344 00:34:07.180 ⇒ 00:34:11.489 Luke Daque: yeah. All the calculations basically are in the intermediate models, like for the

345 00:34:11.750 ⇒ 00:34:24.289 Luke Daque: revenue. For example, if the revenue is different for shopify versus Amazon. Then they’ll be differentiated in the intermediate models. But then the ones we union all of them in the in the final fact table.

346 00:34:24.570 ⇒ 00:34:28.730 Luke Daque: Then they they already have like different calculations, basically.

347 00:34:29.360 ⇒ 00:34:34.530 Payas Parab: Okay. So cause like, my thing is like, how do you jump from this intermediate table right into?

348 00:34:34.630 ⇒ 00:34:37.010 Payas Parab: There’s some logic, right? That then drives, and that.

349 00:34:37.010 ⇒ 00:34:39.149 Uttam Kumaran: Yeah, it’s in. Github. Yeah.

350 00:34:39.159 ⇒ 00:34:39.889 Payas Parab: And dig it up.

351 00:34:39.889 ⇒ 00:34:40.509 Uttam Kumaran: You know.

352 00:34:40.789 ⇒ 00:34:41.939 Uttam Kumaran: Yeah, okay.

353 00:34:43.100 ⇒ 00:34:50.639 Payas Parab: But for the shopify. For example, then then that means we have to update. If total line price items is what ties out to the revenue, then we have to update that in

354 00:34:50.670 ⇒ 00:34:54.050 Payas Parab: the Dbt. In the from the intermediate table to the prod.

355 00:34:54.310 ⇒ 00:34:57.040 Payas Parab: Then we have to make sure that that aggregates correctly.

356 00:34:58.080 ⇒ 00:35:03.469 Uttam Kumaran: Exactly. Yeah. And so in that situation, that’s something. Just just ping Ryan with the exact change.

357 00:35:03.570 ⇒ 00:35:04.780 Uttam Kumaran: And okay.

358 00:35:04.870 ⇒ 00:35:08.429 Uttam Kumaran: fairly easy. And again, all the logic is here. So

359 00:35:09.120 ⇒ 00:35:17.950 Uttam Kumaran: we’re not dealing with too much. So you should be able to clearly, linearly see from the raw to intermediate to the stuff that gets at the end, basically.

360 00:35:17.950 ⇒ 00:35:27.019 Payas Parab: But intermediate the part. I’m having trouble like they’re intermediate to the like. The total orders like, where would I check that like? How how would I know what revenue metric it’s using for shopify

361 00:35:27.290 ⇒ 00:35:32.620 Payas Parab: from the intermediate table into the prod table that ultimately becomes that real dashboard.

362 00:35:32.620 ⇒ 00:35:35.730 Uttam Kumaran: Prod becomes a select. So let’s go to pro just.

363 00:35:35.730 ⇒ 00:35:36.550 Luke Daque: Remarks.

364 00:35:36.550 ⇒ 00:35:38.240 Uttam Kumaran: Pull up the prod table. Yeah.

365 00:35:38.420 ⇒ 00:35:39.410 Uttam Kumaran: let’s just walk through.

366 00:35:39.410 ⇒ 00:35:39.760 Luke Daque: Or.

367 00:35:39.760 ⇒ 00:35:42.190 Uttam Kumaran: Good example of how to navigate this.

368 00:35:42.700 ⇒ 00:35:45.779 Uttam Kumaran: No, no, don’t pull up the table. If you could pull up the logic.

369 00:35:46.830 ⇒ 00:35:48.809 Nicolas Sucari: Okay, it’s March.

370 00:35:48.970 ⇒ 00:35:49.810 Nicolas Sucari: Yeah, just go to.

371 00:35:49.810 ⇒ 00:35:53.329 Uttam Kumaran: Orders. So you can see we start pies by taking.

372 00:35:53.330 ⇒ 00:35:53.950 Payas Parab: Sure. Okay.

373 00:35:53.950 ⇒ 00:35:59.100 Uttam Kumaran: From Amazon, and then we take a Ct. From shopify, and then we Union.

374 00:35:59.250 ⇒ 00:35:59.800 Payas Parab: Yep. Yep.

375 00:35:59.800 ⇒ 00:36:06.009 Uttam Kumaran: And you can see there’s a bunch of nulls because some don’t exist in one platform, the other. So we make some decisions.

376 00:36:06.325 ⇒ 00:36:14.880 Uttam Kumaran: And then we bring it. So you will see exactly the lineage from where a column comes from. And we’re actually all we’re doing is just unioning it. So

377 00:36:15.110 ⇒ 00:36:15.890 Uttam Kumaran: the selection.

378 00:36:15.890 ⇒ 00:36:16.449 Payas Parab: So so sorry.

379 00:36:16.450 ⇒ 00:36:17.120 Uttam Kumaran: Where.

380 00:36:17.120 ⇒ 00:36:30.969 Payas Parab: I see I see that right here, right? So if you go down like the like in the shopify one you have, you’re pulling in total price, total line items. Price right? That the next step right in that aggregated one. I want to make sure we get the right one, because otherwise.

381 00:36:30.970 ⇒ 00:36:38.359 Uttam Kumaran: There’s no, there’s no, there’s no actual. There’s no there’s no manipulation after this, as you see, it’s just a straight, select star.

382 00:36:39.220 ⇒ 00:36:39.970 Payas Parab: Yes. Okay.

383 00:36:39.970 ⇒ 00:36:40.959 Nicolas Sucari: There is no.

384 00:36:41.450 ⇒ 00:36:42.429 Nicolas Sucari: So do you want to.

385 00:36:42.430 ⇒ 00:36:44.330 Payas Parab: These items is it using as revenue?

386 00:36:45.100 ⇒ 00:36:46.440 Payas Parab: Is that meeting real.

387 00:36:46.440 ⇒ 00:36:51.839 Uttam Kumaran: Oh, in real. Okay? So there’s a description. Then, yeah, I agree.

388 00:36:51.840 ⇒ 00:36:54.130 Payas Parab: Okay, I see. All right. Sorry. I just wanna make sure.

389 00:36:55.060 ⇒ 00:36:57.780 Nicolas Sucari: Let me go back here. It’s easier if I go here.

390 00:36:58.381 ⇒ 00:37:15.060 Nicolas Sucari: If you go to the sources you got, you’re gonna see how we are bringing each of those tables, you know. Models is the same. We can do any any model if you want, but if we have here we are just doing a select star as what I’m said, and then if we go to the dashboards, we go to the order one, and we want to see

391 00:37:15.390 ⇒ 00:37:17.990 Nicolas Sucari: the you were saying the metrics

392 00:37:18.140 ⇒ 00:37:20.420 Nicolas Sucari: of total revenue.

393 00:37:20.420 ⇒ 00:37:21.139 Payas Parab: You suggested.

394 00:37:21.140 ⇒ 00:37:22.730 Nicolas Sucari: Some of the total price.

395 00:37:22.730 ⇒ 00:37:24.559 Payas Parab: I see, I see. Okay. Alright.

396 00:37:24.560 ⇒ 00:37:34.079 Uttam Kumaran: So one thing I will say is highest. You I want you to be really clear what you want these names to be, because this is gonna lead to a lot of confusion, too, because total price

397 00:37:34.240 ⇒ 00:37:43.750 Uttam Kumaran: is like super ambiguous. So we will commonly rename it to revenue, or some. Whatever the agreed upon metric is but the source column may not match

398 00:37:44.418 ⇒ 00:37:48.359 Uttam Kumaran: right? And so this is like the the descriptive label. Basically.

399 00:37:48.360 ⇒ 00:37:57.660 Payas Parab: Well, that that’s why, for the shopify one this one like this needs to be. And and I’ve I’ve done that in the Pr. I’m making right that or the branch that I made. But it’s like, yeah, like.

400 00:37:57.670 ⇒ 00:38:02.979 Payas Parab: what we’re calling gross sales is total line items, price right? And like. And it’s going to be denoted in the real

401 00:38:03.030 ⇒ 00:38:04.319 Payas Parab: there. But like

402 00:38:04.430 ⇒ 00:38:12.700 Payas Parab: I’m just like from the upstream perspective, right like, if you Nico, if you pull up that that like the the branch that I made.

403 00:38:12.970 ⇒ 00:38:15.219 Payas Parab: Sorry I can also pull it up if that’s easier.

404 00:38:16.050 ⇒ 00:38:16.550 Payas Parab: Oh, yeah.

405 00:38:17.810 ⇒ 00:38:19.399 Payas Parab: yeah, just go down to the

406 00:38:20.450 ⇒ 00:38:22.660 Payas Parab: dash. Yeah.

407 00:38:22.850 ⇒ 00:38:24.000 Payas Parab: So if you go into.

408 00:38:24.000 ⇒ 00:38:24.340 Nicolas Sucari: Yeah.

409 00:38:24.340 ⇒ 00:38:26.830 Payas Parab: Like, yeah. Javi, coffee grill.

410 00:38:28.192 ⇒ 00:38:31.450 Payas Parab: and you can even just check the commit history to just show what I

411 00:38:31.550 ⇒ 00:38:34.509 Payas Parab: what I did. Top. Right? Yeah.

412 00:38:34.730 ⇒ 00:38:41.039 Payas Parab: So like, if you go down into like, I adjusted the sequel query, which I think is like, not best practice, but like

413 00:38:41.200 ⇒ 00:38:47.239 Payas Parab: this thing like this thing right like this, we don’t want to do. I’m guessing right? We want to build that logic in

414 00:38:47.510 ⇒ 00:38:51.080 Payas Parab: upstream somewhere, so that the select statement is just, pure.

415 00:38:51.450 ⇒ 00:38:53.159 Payas Parab: simple. One liner, right?

416 00:38:56.440 ⇒ 00:39:01.790 Nicolas Sucari: Hmm, yes, yeah, I I mean, I I think yes, I think it’s better to have all of the

417 00:39:02.644 ⇒ 00:39:04.339 Nicolas Sucari: modeling and filtering in

418 00:39:04.849 ⇒ 00:39:11.080 Nicolas Sucari: dbt before having having everything in real and real, might be just straightforward to do dashboarding.

419 00:39:11.950 ⇒ 00:39:12.710 Payas Parab: Okay.

420 00:39:12.710 ⇒ 00:39:15.730 Nicolas Sucari: Because if not, if not, we’re gonna we’re gonna have, like.

421 00:39:15.970 ⇒ 00:39:19.919 Nicolas Sucari: 2 different places where we are kind of.

422 00:39:19.920 ⇒ 00:39:47.779 Payas Parab: That’s what I’m saying. So. And and this is this is like, not a big deal, because this is just like tagging using the customer id thing, but like the bigger deal, and and there’s already a Pr to fix this right. But the bigger deal is, the total price is not the right gross sales, and I want to make sure that that’s fixed all the way upstream, because that is what they’re calling gross sales based on Jared spreadsheet. And like, that’s what they want to see is total line items. Price equals gross sales. I want to know where’s the best place to update that and make sure that like that doesn’t

423 00:39:47.920 ⇒ 00:39:55.489 Payas Parab: get lost anywhere in the code base. Right? Any of the intermediate tables, any of the like that has to be from the top. I guess. Right. That’s what I want to make sure.

424 00:39:56.450 ⇒ 00:40:21.299 Nicolas Sucari: Yeah. So I think there’s 2 solutions to that. 1. 1st is, if we wanna like, fix the source table, the one that we have in Prod there in Snowflake. If we, if we see that we need to change anything, we should do it there in DVD, so that we have the the updated value. And if not, we just need to change if the value is correct, I mean, if the column is right and we have both. And we just want to use a different one for total

425 00:40:21.300 ⇒ 00:40:28.369 Nicolas Sucari: revenue. We need to change that dimension, that metric sorry in real dashboard only, and not change anything of the modeling.

426 00:40:28.480 ⇒ 00:40:29.710 Nicolas Sucari: If we already have the.

427 00:40:30.255 ⇒ 00:40:30.800 Payas Parab: Okay.

428 00:40:30.800 ⇒ 00:40:36.510 Nicolas Sucari: I mean it. It depends on what you’re trying to fix. If if we already have here in

429 00:40:36.850 ⇒ 00:40:43.150 Nicolas Sucari: real, if we already have here in the dashboard in this the one that you’re using already. Dashboard.

430 00:40:43.480 ⇒ 00:40:46.030 Nicolas Sucari: If, instead of using total price, we want to use total.

431 00:40:46.030 ⇒ 00:40:48.970 Payas Parab: Just you can just change it here. Yeah, just.

432 00:40:48.970 ⇒ 00:40:50.600 Nicolas Sucari: Yeah, you can just change it here. And.

433 00:40:50.600 ⇒ 00:40:51.070 Payas Parab: That works.

434 00:40:51.070 ⇒ 00:40:52.900 Nicolas Sucari: That’s gonna work. Yeah, exactly.

435 00:40:52.900 ⇒ 00:40:56.880 Payas Parab: Let let, but that that’s the best practice, because I just don’t want anything to get.

436 00:40:58.120 ⇒ 00:40:58.660 Payas Parab: Is that.

437 00:40:58.660 ⇒ 00:40:59.500 Nicolas Sucari: Yeah, I.

438 00:40:59.500 ⇒ 00:41:00.460 Payas Parab: To update it here. Now.

439 00:41:00.460 ⇒ 00:41:10.019 Nicolas Sucari: I’ll say I’ll say that the best practice is not to have like transformations in the models, and we try to keep all transformations in Dbt. And once we have the tables ready, we just.

440 00:41:10.020 ⇒ 00:41:11.080 Payas Parab: Sure. Okay.

441 00:41:11.080 ⇒ 00:41:13.669 Nicolas Sucari: Final tables into the real folder. Yeah.

442 00:41:13.670 ⇒ 00:41:15.050 Payas Parab: Clear. Okay. Thanks.

443 00:41:15.050 ⇒ 00:41:17.060 Nicolas Sucari: I don’t know if Utam or Ryan do you have.

444 00:41:17.060 ⇒ 00:41:17.750 Uttam Kumaran: No, no, that’s what.

445 00:41:17.750 ⇒ 00:41:18.450 Luke Daque: 100%.

446 00:41:18.450 ⇒ 00:41:22.630 Uttam Kumaran: Correct, and and Ryan would be reviewing any Prs anyway. So

447 00:41:23.099 ⇒ 00:41:29.029 Uttam Kumaran: caught this, there’s gonna be an a healthy back and forth where it’s like, Hey, we need to do this really quickly.

448 00:41:29.170 ⇒ 00:41:29.630 Payas Parab: Yeah.

449 00:41:29.630 ⇒ 00:41:31.879 Uttam Kumaran: Get this into a model. But that’s

450 00:41:32.260 ⇒ 00:41:34.109 Uttam Kumaran: just the way it is.

451 00:41:34.430 ⇒ 00:41:42.500 Uttam Kumaran: So the goal is like again. Just what you said is, I don’t want something in real that doesn’t match Meta base. And this is a

452 00:41:43.140 ⇒ 00:41:52.749 Uttam Kumaran: I actually am very happy that you asked this question, because this is basically what will kill us if there starts to be logic in many other places? So leave the logic updates

453 00:41:52.920 ⇒ 00:42:02.909 Uttam Kumaran: to Ryan as much as possible. And and and but also I want those to be made in the models folder so that that can get sent into Snowflake, and then from Snowflake, wherever it needs to end up, it.

454 00:42:03.790 ⇒ 00:42:09.810 Payas Parab: Well, that that’s it. Yeah. Cause I, if I do these like quick ad hoc fixes, then, like your total aggregated one might not

455 00:42:10.230 ⇒ 00:42:10.960 Payas Parab: reflected.

456 00:42:10.960 ⇒ 00:42:16.789 Uttam Kumaran: And that’s fine, I would say, do make like propose whatever change, and then we’ll figure out where it needs to end up.

457 00:42:16.790 ⇒ 00:42:18.629 Payas Parab: Where it needs to go. Okay? So I’ll just yeah. I’ll just.

458 00:42:18.630 ⇒ 00:42:26.659 Uttam Kumaran: So don’t get too bogged down in like what the right processes should focus more on, like what you actually want to see. And then, yeah, okay.

459 00:42:26.660 ⇒ 00:42:27.890 Payas Parab: This is in our, yeah.

460 00:42:27.890 ⇒ 00:42:28.350 Uttam Kumaran: For sure.

461 00:42:28.350 ⇒ 00:42:28.980 Payas Parab: Perfect.

462 00:42:29.690 ⇒ 00:42:30.540 Payas Parab: excellent.

463 00:42:30.810 ⇒ 00:42:31.260 Nicolas Sucari: Cool.

464 00:42:33.520 ⇒ 00:42:34.340 Nicolas Sucari: Okay.

465 00:42:37.090 ⇒ 00:42:37.990 Nicolas Sucari: Great meeting guys.

466 00:42:38.290 ⇒ 00:42:44.780 Payas Parab: Great. Oh, can we? Sorry, I know Ryan and Nico, if you guys have some time just to review the refund stuff, just figure out how we’re gonna get that

467 00:42:45.480 ⇒ 00:42:59.269 Payas Parab: right? Or if you guys were able to with with Brian’s analysis, we can. We can also sync on this offline. But I wanna make sure we get that right, because that’s the last thing left to tie out correctly to the shopify analytics dashboard. And once we get that I can.

468 00:42:59.400 ⇒ 00:43:03.990 Payas Parab: My Meta base thing works correctly for everything. Once we fix this refunds thing.

469 00:43:04.990 ⇒ 00:43:05.650 Nicolas Sucari: Okay.

470 00:43:07.250 ⇒ 00:43:09.989 Luke Daque: We haven’t worked on that yet. But yeah, we’ll we’ll look into that.

471 00:43:10.200 ⇒ 00:43:21.220 Payas Parab: Okay, if you guys can just like and let me know if you have any questions. But yeah, the refunds thing. If we can figure out what what is the best way to group them by the month that the refund is actually processed. That would be great.

472 00:43:21.730 ⇒ 00:43:29.720 Uttam Kumaran: I think you just have to. Yeah, I think, Ryan, you just have to change the refund date to match the order date that the refund is associated with.

473 00:43:30.330 ⇒ 00:43:31.639 Uttam Kumaran: Does that make sense?

474 00:43:31.700 ⇒ 00:43:37.710 Uttam Kumaran: So you know how in in pool parts, we, we, you join the refund on the order it’s associated with to basically figure out

475 00:43:38.280 ⇒ 00:43:41.369 Uttam Kumaran: like, Hey, this refund is associated with this order.

476 00:43:41.900 ⇒ 00:43:42.700 Luke Daque: Right.

477 00:43:43.470 ⇒ 00:43:44.060 Uttam Kumaran: You just have to.

478 00:43:44.060 ⇒ 00:43:45.109 Luke Daque: Take in when you.

479 00:43:45.110 ⇒ 00:43:50.750 Uttam Kumaran: You do that join you take in the order date, instead of bringing in the the refund orders date

480 00:43:52.140 ⇒ 00:43:54.710 Uttam Kumaran: that way. The negative, the negative amount.

481 00:43:55.030 ⇒ 00:43:55.380 Nicolas Sucari: Yeah.

482 00:43:55.380 ⇒ 00:43:59.079 Uttam Kumaran: Positive amount comes in the same month as the positive amount. So and that’s

483 00:44:00.000 ⇒ 00:44:06.839 Uttam Kumaran: it’s a little bit of like a confusing thing is, that’s exactly what you want. Right? You want the refund to reflect. In the month of the order.

484 00:44:07.240 ⇒ 00:44:09.609 Payas Parab: No, no, we we have that right now right.

485 00:44:09.610 ⇒ 00:44:10.940 Uttam Kumaran: Oh, you want the so you want.

486 00:44:10.940 ⇒ 00:44:11.290 Payas Parab: Oh, my God!

487 00:44:11.290 ⇒ 00:44:13.769 Uttam Kumaran: Date to be whenever the refund happens.

488 00:44:14.000 ⇒ 00:44:14.699 Payas Parab: Yes, because, right?

489 00:44:15.250 ⇒ 00:44:19.569 Payas Parab: Look low. Yeah, the numbers look really low. And I’m guessing it’s like the lag. Essentially, the lag.

490 00:44:19.570 ⇒ 00:44:26.929 Uttam Kumaran: Oh, so then, yeah, do. The exact opposite is like we take in the we take the refund date, the refund negative hits. Whenever the refund hits.

491 00:44:26.930 ⇒ 00:44:28.070 Luke Daque: I see.

492 00:44:28.070 ⇒ 00:44:28.720 Uttam Kumaran: Doesn’t that out.

493 00:44:28.720 ⇒ 00:44:37.830 Nicolas Sucari: There there should be 2 dates, I mean, there should be the refund date, and we use should use that for the refund metric and the order date for the purchase order.

494 00:44:37.830 ⇒ 00:44:46.690 Payas Parab: But but but but the one thing that just needs to to make it map correctly and real right, the refund date has to effectively be cast to the created app date. Right?

495 00:44:47.280 ⇒ 00:44:55.180 Payas Parab: Cause. That’s the time series that the real is using, like real is using the created app from the orders table, right?

496 00:44:55.180 ⇒ 00:44:55.510 Luke Daque: Right.

497 00:44:55.510 ⇒ 00:44:57.159 Nicolas Sucari: Oh, I see what you mean. Okay.

498 00:44:57.904 ⇒ 00:44:58.540 Nicolas Sucari: but.

499 00:44:58.540 ⇒ 00:45:04.710 Uttam Kumaran: But that’s what I’m saying. You want. You want the refund created at to be the actual refund date.

500 00:45:05.290 ⇒ 00:45:05.760 Payas Parab: Yes.

501 00:45:06.650 ⇒ 00:45:08.544 Uttam Kumaran: Okay, cool. So then that’s actually

502 00:45:09.010 ⇒ 00:45:12.620 Uttam Kumaran: yeah. Then it’s then it’s actually Brian’s basically no

503 00:45:13.120 ⇒ 00:45:14.870 Uttam Kumaran: modifications at all.

504 00:45:15.736 ⇒ 00:45:19.500 Uttam Kumaran: It’s basically just taking the refund orders, natural date.

505 00:45:19.580 ⇒ 00:45:24.240 Uttam Kumaran: And then, yeah, pies, you’re right. Those all unreal. It’s 1 time spine.

506 00:45:24.380 ⇒ 00:45:25.430 Payas Parab: So.

507 00:45:25.430 ⇒ 00:45:28.319 Uttam Kumaran: That will just come in as a negative whenever it comes in.

508 00:45:28.590 ⇒ 00:45:31.389 Uttam Kumaran: but they’re both on the same. They there won’t be 2 dates.

509 00:45:31.390 ⇒ 00:45:39.779 Payas Parab: So whenever the negative comes in, whenever the negative comes in on an order that is the actual refund date. Is that is that accurate.

510 00:45:39.840 ⇒ 00:45:45.530 Payas Parab: So then could we? Just theoretically, and maybe I just need to check this in Snowflake. But like, if I just make

511 00:45:45.610 ⇒ 00:45:49.980 Payas Parab: that positive alright, I filter for all the negative values, and then.

512 00:45:49.980 ⇒ 00:45:50.430 Uttam Kumaran: That’s it.

513 00:45:50.430 ⇒ 00:45:54.299 Payas Parab: Created out date. That should be the date that the refund was issued

514 00:45:54.580 ⇒ 00:45:57.400 Payas Parab: right now. It’s just netting out the positive refund

515 00:45:57.650 ⇒ 00:45:59.039 Payas Parab: cause, like, if the order status.

516 00:45:59.040 ⇒ 00:46:02.379 Uttam Kumaran: So there’s there’s a couple of other. There’s a couple of ways you could do this. One is you? Could

517 00:46:02.480 ⇒ 00:46:04.310 Uttam Kumaran: we could add a column

518 00:46:04.440 ⇒ 00:46:12.400 Uttam Kumaran: like. So one is, we can add a column that’s basically refund date. That is only valid. That’s only filled when it’s associated with a refund.

519 00:46:12.900 ⇒ 00:46:13.110 Payas Parab: Right.

520 00:46:13.110 ⇒ 00:46:17.019 Uttam Kumaran: The second thing we could do is add a flag that’s like is a refund.

521 00:46:17.170 ⇒ 00:46:22.830 Uttam Kumaran: so that you could get all the refunds. The 3rd thing is, yeah, you should. You could look at the negatives.

522 00:46:23.241 ⇒ 00:46:25.700 Uttam Kumaran: I think the second option is probably.

523 00:46:26.300 ⇒ 00:46:28.630 Payas Parab: The second option being is, is a refund like.

524 00:46:28.630 ⇒ 00:46:32.520 Uttam Kumaran: Just like basic. And the logic for that tag is

525 00:46:32.890 ⇒ 00:46:34.920 Uttam Kumaran: like, Yeah, Ryan can.

526 00:46:34.920 ⇒ 00:46:39.970 Payas Parab: But then for revenue, for revenue we have to filter for is refund false, and then.

527 00:46:41.060 ⇒ 00:46:50.559 Uttam Kumaran: Yeah, so you basically have. So basically when you do your calculations, it’s kind of like, it’s a cascade. So you have like top line revenue which you could just some total sales.

528 00:46:50.860 ⇒ 00:46:51.370 Uttam Kumaran: Yeah.

529 00:46:51.370 ⇒ 00:46:51.690 Nicolas Sucari: Happy.

530 00:46:51.690 ⇒ 00:46:56.020 Uttam Kumaran: You just sum your total sales number, or whatever the price number or whatever that’s gross.

531 00:46:56.070 ⇒ 00:47:02.335 Uttam Kumaran: And then you you wanna subtract out the refunds, discounts, tax shipping all that

532 00:47:04.120 ⇒ 00:47:04.520 Nicolas Sucari: Exactly.

533 00:47:04.520 ⇒ 00:47:08.730 Uttam Kumaran: But those are in separate columns like, there’s not one value column.

534 00:47:08.990 ⇒ 00:47:10.249 Uttam Kumaran: You see what I’m saying.

535 00:47:10.610 ⇒ 00:47:12.220 Uttam Kumaran: The refunds ticket will happen.

536 00:47:12.220 ⇒ 00:47:13.720 Nicolas Sucari: We’re gonna have.

537 00:47:13.720 ⇒ 00:47:14.490 Uttam Kumaran: Yeah. Go ahead.

538 00:47:14.490 ⇒ 00:47:26.029 Nicolas Sucari: Yeah, you you’re gonna have, like all the different metrics on the left. So maybe revenue will include everything. And then you will have another metric, where we can add, like the net value of it.

539 00:47:26.030 ⇒ 00:47:26.530 Luke Daque: No problem.

540 00:47:26.530 ⇒ 00:47:31.250 Nicolas Sucari: Because you will have refund. Yeah, net profit or something, because you will have the refunds in another line.

541 00:47:31.280 ⇒ 00:47:33.880 Nicolas Sucari: So if you filtered for the date.

542 00:47:33.960 ⇒ 00:47:45.129 Nicolas Sucari: whatever whatever date you want, you will see the Rev. All, the the all included revenue, like total sales in that that date, and then you will have all of the refunds that were issued during that date.

543 00:47:45.280 ⇒ 00:47:46.220 Nicolas Sucari: Right.

544 00:47:47.510 ⇒ 00:47:47.860 Payas Parab: Yes.

545 00:47:47.860 ⇒ 00:47:49.720 Nicolas Sucari: You get to the net profit. Yeah.

546 00:47:49.920 ⇒ 00:47:54.920 Uttam Kumaran: And it’s up to you like we could. It’s again. It’s just we could do this multiple ways. So.

547 00:47:55.970 ⇒ 00:48:07.670 Payas Parab: Got it. I wanna give. I want to give the filtering a rip just for now, just again we have the immediate ask right here, right? Which is, can I just fix that using that refunds equals when it’s negative.

548 00:48:07.840 ⇒ 00:48:19.970 Payas Parab: right? When it’s negative, that’s a refund order. And just see what that. And I I did see there were positive and negative values. So I think it’s like, that’s why my numbers are looking low. Right? It’s like it’s netting out orders that were subsequently refunded.

549 00:48:20.790 ⇒ 00:48:23.230 Payas Parab: Right is that is that an accurate way to look at it?

550 00:48:25.935 ⇒ 00:48:28.209 Uttam Kumaran: So what’s coming out now?

551 00:48:28.540 ⇒ 00:48:28.940 Nicolas Sucari: Yeah.

552 00:48:28.940 ⇒ 00:48:30.530 Payas Parab: The refund. So

553 00:48:30.880 ⇒ 00:48:36.049 Payas Parab: yeah, so if I aggregate refunds and I sent the query, I can share my screen here real quick.

554 00:48:38.560 ⇒ 00:48:43.210 Nicolas Sucari: So you’re saying, like the total amount of refunds is lower than expected. Right.

555 00:48:43.700 ⇒ 00:48:45.220 Payas Parab: Correct. Yes.

556 00:48:45.810 ⇒ 00:48:47.820 Nicolas Sucari: Okay. So you’re having like a

557 00:48:48.930 ⇒ 00:48:52.630 Nicolas Sucari: higher profit because we are not deducing those.

558 00:48:53.340 ⇒ 00:48:54.360 Payas Parab: The

559 00:48:54.580 ⇒ 00:48:58.419 Payas Parab: yeah, let me let me just like I can just share real quick. So it’ll be easy to

560 00:48:59.240 ⇒ 00:49:02.050 Payas Parab: give me one second trying to find that query.

561 00:49:03.340 ⇒ 00:49:05.800 Luke Daque: And you’re querying this in Snowflake right.

562 00:49:05.800 ⇒ 00:49:09.969 Payas Parab: Yes, yeah. And I and I shared the query, that’s the one in the the chat as well.

563 00:49:10.180 ⇒ 00:49:10.595 Luke Daque: Hmm.

564 00:49:11.656 ⇒ 00:49:17.749 Payas Parab: Okay? So basically, like again, the the reconciliation we’re trying to get to is like, I’m using August as the test date.

565 00:49:18.271 ⇒ 00:49:22.969 Payas Parab: And we have the returns here as negative. 440,000 right?

566 00:49:23.490 ⇒ 00:49:27.369 Payas Parab: That is what it’s reporting for. August, for not tick. Tock.

567 00:49:29.200 ⇒ 00:49:31.130 Payas Parab: This is the aggregation

568 00:49:31.640 ⇒ 00:49:38.070 Payas Parab: of refunds. This number is low, right like this is like there should be more refunds reflected here.

569 00:49:40.040 ⇒ 00:49:46.109 Payas Parab: I think this. I forgot the in this one. I forgot the where tick, tock filter, but regardless. It’s still low, right.

570 00:49:53.580 ⇒ 00:49:57.329 Luke Daque: Can you try adding, I think there’s a refund date.

571 00:49:58.450 ⇒ 00:49:59.899 Payas Parab: There is a refund date.

572 00:50:00.640 ⇒ 00:50:02.950 Luke Daque: Just double check, real quick.

573 00:50:05.960 ⇒ 00:50:08.069 Nicolas Sucari: In shopify order table. Let me check.

574 00:50:19.690 ⇒ 00:50:22.729 Luke Daque: Refund created. That is what it’s called.

575 00:50:22.730 ⇒ 00:50:24.350 Payas Parab: Refund created at.

576 00:50:24.350 ⇒ 00:50:25.010 Luke Daque: Yeah.

577 00:50:25.420 ⇒ 00:50:28.079 Payas Parab: Okay, so let’s try modifying this.

578 00:50:30.510 ⇒ 00:50:32.240 Luke Daque: And see if that’s the.

579 00:50:33.450 ⇒ 00:50:34.780 Luke Daque: That you want is that.

580 00:50:34.780 ⇒ 00:50:36.210 Payas Parab: Available is that in.

581 00:50:36.210 ⇒ 00:50:36.640 Luke Daque: At.

582 00:50:36.640 ⇒ 00:50:38.060 Payas Parab: Back, shopify order.

583 00:50:39.430 ⇒ 00:50:40.960 Luke Daque: That shop file right.

584 00:50:41.540 ⇒ 00:50:42.830 Payas Parab: Which table is that

585 00:50:54.090 ⇒ 00:50:56.220 Payas Parab: I don’t think it’s popping up. But I can.

586 00:50:58.080 ⇒ 00:50:58.810 Payas Parab: Yeah.

587 00:50:58.810 ⇒ 00:50:59.989 Luke Daque: Be, it should be there.

588 00:51:01.240 ⇒ 00:51:02.590 Payas Parab: In dev

589 00:51:02.760 ⇒ 00:51:04.069 Payas Parab: fact, shopify order.

590 00:51:04.070 ⇒ 00:51:06.949 Nicolas Sucari: No, it’s it’s not in the factor for you. I think.

591 00:51:07.720 ⇒ 00:51:09.240 Luke Daque: Try the prod. One

592 00:51:09.650 ⇒ 00:51:13.020 Luke Daque: broad fact, order model will.

593 00:51:14.440 ⇒ 00:51:16.292 Luke Daque: This would have

594 00:51:17.250 ⇒ 00:51:18.830 Luke Daque: Amazon as well, though.

595 00:51:20.150 ⇒ 00:51:21.860 Nicolas Sucari: Orders with an S. Yes.

596 00:51:26.190 ⇒ 00:51:29.800 Payas Parab: Yeah. But then this has the other ones as well. Right? So I got a filter for.

597 00:51:31.820 ⇒ 00:51:33.590 Nicolas Sucari: Refund created at the end. There

598 00:51:33.950 ⇒ 00:51:34.760 Nicolas Sucari: chocolate.

599 00:51:36.740 ⇒ 00:51:37.340 Payas Parab: It’s still even

600 00:51:38.520 ⇒ 00:51:42.930 Payas Parab: even that still looks low. Right? Because this is negative. 440,000.

601 00:51:47.140 ⇒ 00:51:49.950 Nicolas Sucari: And where is that coming from the spreadsheet.

602 00:51:51.531 ⇒ 00:51:53.280 Payas Parab: This is from the shopify portal.

603 00:51:53.990 ⇒ 00:51:55.260 Payas Parab: this screenshot.

604 00:51:55.480 ⇒ 00:51:56.290 Payas Parab: So I just.

605 00:51:56.290 ⇒ 00:51:58.330 Uttam Kumaran: For for this is for what month?

606 00:51:58.710 ⇒ 00:51:59.490 Payas Parab: August.

607 00:51:59.930 ⇒ 00:52:02.180 Uttam Kumaran: August returns

608 00:52:04.670 ⇒ 00:52:06.379 Uttam Kumaran: where it’s not Tiktok.

609 00:52:07.410 ⇒ 00:52:08.090 Payas Parab: Yeah.

610 00:52:13.720 ⇒ 00:52:16.159 Payas Parab: so I haven’t even put that filter in here. But then

611 00:52:17.870 ⇒ 00:52:22.279 Payas Parab: I think it’s his Tiktok order. His tiktok shop is false.

612 00:52:25.320 ⇒ 00:52:30.000 Payas Parab: and then we do app source equals, shopify right to further clarify it.

613 00:52:30.000 ⇒ 00:52:30.420 Luke Daque: Yeah.

614 00:52:30.840 ⇒ 00:52:32.439 Uttam Kumaran: Yeah, this should be the same.

615 00:52:32.980 ⇒ 00:52:42.050 Payas Parab: I’m wondering if it’s like the issue is this, that’s what like you were saying. There’s like a positive and negative where it like nets out. I’m wondering if line 16 here refund amount not equal to 0 is the is.

616 00:52:42.050 ⇒ 00:52:46.809 Uttam Kumaran: Does your does your spreadsheet also have the counts? No

617 00:52:47.530 ⇒ 00:52:49.149 Uttam Kumaran: like. How many refunds.

618 00:52:50.260 ⇒ 00:52:54.610 Payas Parab: No, but I think I could probably find it in the Shopify Admin portal.

619 00:52:55.858 ⇒ 00:52:59.249 Payas Parab: I need to grab my other laptop. It’s it’s 2 factored in there, but I can. I can try.

620 00:52:59.250 ⇒ 00:52:59.899 Uttam Kumaran: No, no, that’s fine.

621 00:52:59.900 ⇒ 00:53:00.980 Payas Parab: Remember, yeah.

622 00:53:01.860 ⇒ 00:53:04.639 Uttam Kumaran: Well, oh, it says orders, says 1, 29, oh.

623 00:53:04.640 ⇒ 00:53:07.660 Payas Parab: Well, no, no, that’s that’s like the the total orders.

624 00:53:07.660 ⇒ 00:53:08.809 Uttam Kumaran: Okay. Okay. Okay.

625 00:53:09.600 ⇒ 00:53:10.080 Payas Parab: And then.

626 00:53:11.010 ⇒ 00:53:13.548 Uttam Kumaran: Yeah, Ryan, I don’t know. I wonder if it’s

627 00:53:15.050 ⇒ 00:53:16.630 Luke Daque: Yeah, I’ll have to look into that.

628 00:53:16.630 ⇒ 00:53:21.739 Uttam Kumaran: You may have to look at how we did it, for how we did it for pool parts, because I would just copy it from there.

629 00:53:23.170 ⇒ 00:53:23.980 Luke Daque: Yeah.

630 00:53:26.170 ⇒ 00:53:29.550 Luke Daque: yeah, I have to figure out like how this was done as well.

631 00:53:29.550 ⇒ 00:53:48.289 Payas Parab: Yeah, if I, if I can take, take the lead on like kind of like getting that some of that dashboarding stuff and those metrics, and then also getting you those sequel queries. If you guys can take the lead on the like just for like as quickly as possible, just for keeping our boy Jared happy. Just trying to figure this one out, I think would be higher priority from you guys than like the other stuff.

632 00:53:48.810 ⇒ 00:53:49.590 Luke Daque: Yeah, sure.

633 00:53:50.980 ⇒ 00:53:52.539 Payas Parab: Thank you. Appreciate it, Ryan.

634 00:53:56.530 ⇒ 00:53:58.849 Payas Parab: I can send you this query as well. If that’s helpful.

635 00:54:00.520 ⇒ 00:54:01.700 Luke Daque: Yeah, that’s great.

636 00:54:04.300 ⇒ 00:54:07.140 Luke Daque: So this would be lower priority, right? The 1st priority.

637 00:54:07.140 ⇒ 00:54:09.549 Payas Parab: Yeah, I think the refund resolving.

638 00:54:09.750 ⇒ 00:54:10.930 Payas Parab: Yeah, the

639 00:54:10.990 ⇒ 00:54:14.219 Payas Parab: I I will. I will take the the charge on like aggregating.

640 00:54:14.310 ⇒ 00:54:35.889 Payas Parab: or I’m gonna move it to real and just like, define those metrics there and then doing the aggregations. That would be sec. I think. Figuring out this refund thing is like pretty critical, because that’s just like Jared just looking at this and just trying to see what ties out doesn’t tie out and everything else we’re good on. Once I adjust the metrics. It’s just this, returns and I checked even net sales. It’s just once we fix returns, and that sales will be right, too.

641 00:54:36.870 ⇒ 00:54:37.540 Luke Daque: Cool.

642 00:54:37.850 ⇒ 00:54:39.600 Nicolas Sucari: Can you share these 2 with us.

643 00:54:39.930 ⇒ 00:54:44.301 Payas Parab: Yeah, the spreadsheet. I’m just gonna drop it in the chat. I think I shared it with you guys already, I think,

644 00:54:44.550 ⇒ 00:54:47.000 Payas Parab: Ryan, what is your email? It’s like, is it, Luke? At.

645 00:54:47.490 ⇒ 00:54:49.020 Luke Daque: It’s Ryan

646 00:54:49.900 ⇒ 00:54:52.480 Luke Daque: document. Let me just send it to you.

647 00:54:52.690 ⇒ 00:54:55.370 Payas Parab: Ryan. Oh, there it is. Okay. Great.

648 00:54:55.790 ⇒ 00:54:57.239 Luke Daque: Oh, yeah. Santiago.

649 00:54:58.690 ⇒ 00:55:00.340 Payas Parab: Let me also send it in here.

650 00:55:06.330 ⇒ 00:55:14.640 Payas Parab: The other. The other theory I had was also it could be, including cancelled orders. I don’t know if that might be. I tried to test that theory by doing with this like

651 00:55:14.710 ⇒ 00:55:16.750 Payas Parab: canceled versus

652 00:55:18.890 ⇒ 00:55:23.519 Payas Parab: refund query. So this was like another attempt, and I can share this as well. But

653 00:55:23.670 ⇒ 00:55:33.189 Payas Parab: the idea was trying to figure out like what is refunded versus like what is cancelled, and then, which are like uncancelled, but refunded and like. Figure out, maybe there’s something there, but I couldn’t

654 00:55:33.370 ⇒ 00:55:42.199 Payas Parab: tie anything out there as well. That got got us to the ballpark, but I can share that with you as well. I don’t know if, like the word returns, shopify doesn’t share how they calculate

655 00:55:46.750 ⇒ 00:55:52.359 Payas Parab: like how they calculate returns. I think Brian tried to take a look, and we couldn’t figure it out.

656 00:55:52.380 ⇒ 00:55:55.659 Payas Parab: I was like, I’m wondering, maybe, if canceled as part of that story.

657 00:55:56.210 ⇒ 00:56:01.500 Payas Parab: and that might be what makes up for additional. I I don’t know. I’m just yeah.

658 00:56:01.700 ⇒ 00:56:07.420 Payas Parab: that that one. If you guys can help us figure that one out. That would be huge. The the other stuff. I think I can work with.

659 00:56:08.040 ⇒ 00:56:08.640 Luke Daque: Sure.

660 00:56:10.100 ⇒ 00:56:12.340 Payas Parab: Awesome. Sorry to keep you guys so long.

661 00:56:13.540 ⇒ 00:56:14.950 Luke Daque: No worries.

662 00:56:23.660 ⇒ 00:56:26.640 Payas Parab: Alright any other questions for me on anything.

663 00:56:30.016 ⇒ 00:56:35.569 Nicolas Sucari: I don’t think so. No, I’m gonna keep working on those real stuff.

664 00:56:35.995 ⇒ 00:56:58.729 Nicolas Sucari: If if Ryan, if you want to merge or review that pull, request that we have there. And then I ask if you wanna just create a new request with with whatever changes you have, I’m gonna try to update the real project because we have something in main, that’s not the actual one that’s live there, because you can now deploy from what you’re working on on this lie.

665 00:56:58.730 ⇒ 00:57:09.639 Nicolas Sucari: So I’m gonna try to match those again. And we have the latest version there. We will need to continue working on creating more dashboards, I think. And and yeah, figuring out these

666 00:57:09.690 ⇒ 00:57:11.600 Nicolas Sucari: differences in in.

667 00:57:11.600 ⇒ 00:57:12.450 Payas Parab: Yeah, that’s okay.

668 00:57:13.230 ⇒ 00:57:14.970 Payas Parab: Sounds good. Thank you. Guys.

669 00:57:15.750 ⇒ 00:57:16.520 Luke Daque: Cool. Thank you.

670 00:57:16.520 ⇒ 00:57:17.520 Uttam Kumaran: Thank you. Thank you.

671 00:57:17.990 ⇒ 00:57:19.180 Payas Parab: Bye, guys, bye.

672 00:57:19.180 ⇒ 00:57:19.800 Nicolas Sucari: Bye.