Meeting Title: Javy-Data-Engineering-Weekly Date: 2024-10-01 Meeting participants: Nicolas Sucari, Aman Nagpal, Brian Pei, Payas Parab
WEBVTT
1 00:05:42.420 ⇒ 00:05:43.639 Payas Parab: Hey, Ivan, how are you?
2 00:05:52.080 ⇒ 00:05:53.550 Brian Pei: Hope this works? Hello.
3 00:05:53.750 ⇒ 00:05:54.660 Payas Parab: Hey! Brian.
4 00:05:55.610 ⇒ 00:05:57.570 Brian Pei: Hey! What’s up, everyone?
5 00:06:02.530 ⇒ 00:06:04.180 Brian Pei: I will.
6 00:06:04.710 ⇒ 00:06:08.930 Brian Pei: I’ll start in a couple of minutes. But the plan was just to
7 00:06:09.240 ⇒ 00:06:12.090 Brian Pei: take a look at these dev tables to see
8 00:06:12.860 ⇒ 00:06:17.039 Brian Pei: questions. What are we missing? What looks good, what looks bad?
9 00:06:17.640 ⇒ 00:06:19.169 Brian Pei: Pius did a
10 00:06:19.480 ⇒ 00:06:23.389 Brian Pei: a good run through of it last night? I got all your notes.
11 00:06:24.130 ⇒ 00:06:26.740 Brian Pei: I might not some of the notes. I
12 00:06:26.900 ⇒ 00:06:33.329 Brian Pei: it was late at night for me last night, so we can talk about that here need some time to go through all of the notes. But.
13 00:06:33.460 ⇒ 00:06:41.769 Payas Parab: No worries. I my my apologies, I know, I said. I send it by end of the day, but didn’t have time to sit down and look through it all until, like 8 local, so no worries.
14 00:06:41.770 ⇒ 00:06:47.727 Brian Pei: You’re you’re. It’s totally fine. Everyone’s got messed up time zones, and it’s it’s all good.
15 00:06:48.240 ⇒ 00:06:52.350 Brian Pei: I appreciate you looking at them so quickly in the 1st place, so
16 00:06:54.940 ⇒ 00:06:58.600 Brian Pei: cool, let me just so
17 00:06:59.030 ⇒ 00:07:00.130 Brian Pei: background.
18 00:07:02.990 ⇒ 00:07:05.740 Brian Pei: 5. Tran is fine. 5. Tran is green.
19 00:07:05.820 ⇒ 00:07:07.189 Brian Pei: the alerts are.
20 00:07:07.200 ⇒ 00:07:08.440 Brian Pei: We’re a hoax.
21 00:07:09.730 ⇒ 00:07:19.970 Brian Pei: it’s if it is truly ever going to be read in the future. The alerts should not be like 4 day old alerts.
22 00:07:20.110 ⇒ 00:07:23.124 Brian Pei: anyway. I’ll keep a daily track on that
23 00:07:23.770 ⇒ 00:07:29.417 Brian Pei: and figure out why that was happening with support. That’s totally fine. But data is coming in
24 00:07:29.910 ⇒ 00:07:34.779 Brian Pei: at about like once every like 12 h interval, it updates.
25 00:07:35.020 ⇒ 00:07:40.070 Brian Pei: And what we’ve done is before we
26 00:07:40.780 ⇒ 00:07:51.603 Brian Pei: schedule anything and put in any prod code it doesn’t make sense to do that. If there’s gonna be changes to these dev tables
27 00:07:52.510 ⇒ 00:07:56.719 Brian Pei: because we are taking a pass at trying to
28 00:07:57.270 ⇒ 00:08:08.179 Brian Pei: get as much useful information in there as possible. But you know, if we don’t review it with you, Aman, then it doesn’t make sense to put it in prod and then have to change it in the future. So
29 00:08:08.494 ⇒ 00:08:11.805 Brian Pei: the purpose of today is to go through what we have so far.
30 00:08:12.140 ⇒ 00:08:17.040 Brian Pei: it’s probably a little over halfway of the initial, like
31 00:08:17.940 ⇒ 00:08:29.030 Brian Pei: Barrier to entry Mark. There’s gonna be a lot more tables as they come up. But the most important ones. Will be this 1st set of, I think, 7 tables
32 00:08:29.775 ⇒ 00:08:37.390 Brian Pei: for orders, products, subscriptions. And there, there are things in orders that
33 00:08:37.630 ⇒ 00:08:43.029 Brian Pei: are things like refunds and discounts, like things that could be their own table, but are in orders.
34 00:08:43.110 ⇒ 00:08:48.994 Brian Pei: We can take them out for you as well. We can do basically what whatever? It’s just a review of what we have
35 00:08:50.110 ⇒ 00:08:57.289 Brian Pei: to kind of like show. I’m still working on subscriptions and a lot of the Amazon stuff I am also still working on.
36 00:08:57.490 ⇒ 00:09:00.750 Brian Pei: So I tried my best for these tables.
37 00:09:00.790 ⇒ 00:09:05.889 Brian Pei: The table name I’ll put shopify in the table to let the user know that this is shopify data only
38 00:09:06.070 ⇒ 00:09:15.700 Brian Pei: in the future it’ll it’ll be like shopify orders, Amazon orders, and then some query that unions them together for dim orders or fact orders. But
39 00:09:16.200 ⇒ 00:09:19.640 Brian Pei: I’ll start with what we have for for order.
40 00:09:19.700 ⇒ 00:09:34.009 Brian Pei: Basically, I’m just gonna go through this table and go through kind of like the columns and kind of explain them. And then I’m on just any questions, and then at the end for any additions, and then I’ll just kind of like, keep moving on to the ones that we have
41 00:09:34.240 ⇒ 00:09:36.312 Brian Pei: in depth so far.
42 00:09:37.150 ⇒ 00:09:44.289 Brian Pei: this should say dev, and anything that’s in dev I’m not using Dbt to run it. We’re we’re playing around with, you know, just like
43 00:09:44.300 ⇒ 00:09:47.579 Brian Pei: queries. And I’m looking at the data in snowflake and stuff like that.
44 00:09:47.660 ⇒ 00:09:49.920 Brian Pei: So let me go back here. So
45 00:09:50.777 ⇒ 00:09:53.049 Brian Pei: fact shopify order.
46 00:09:53.400 ⇒ 00:09:55.639 Brian Pei: You have the order. Id the order number.
47 00:09:55.670 ⇒ 00:10:02.990 Brian Pei: If there is a Tiktok order number, it’ll show up here. It’s not for every order, but we parsed that out here as well.
48 00:10:03.160 ⇒ 00:10:08.279 Brian Pei: The customer Id, which will join to dim customer, which I’ll show. After that.
49 00:10:08.750 ⇒ 00:10:11.259 Brian Pei: when the order was 1st created.
50 00:10:11.390 ⇒ 00:10:15.210 Brian Pei: the last time it was updated for any status changes
51 00:10:16.380 ⇒ 00:10:19.919 Brian Pei: when it was processed and closed, which
52 00:10:20.363 ⇒ 00:10:29.176 Brian Pei: it was a little confusing for me. I I don’t know if that’s the same thing or not, because process that I don’t think means fulfilled. I think it was
53 00:10:29.600 ⇒ 00:10:37.148 Brian Pei: I’m not sure your definition of processed, but I just saw that column, and I thought it would be important. So processed and closed. We can figure that out
54 00:10:37.410 ⇒ 00:10:41.539 Brian Pei: later on. Today. If it was canceled. When was it canceled?
55 00:10:42.072 ⇒ 00:10:46.700 Brian Pei: If it was canceled, what was the cancel? Reason. Let’s just see what that looks like.
56 00:10:54.990 ⇒ 00:11:02.450 Brian Pei: Oh, other good. That was a great one to go off of. Okay, anyway. But if it was cancelled it’ll have a reason.
57 00:11:03.236 ⇒ 00:11:13.739 Brian Pei: The order name is another identifier for order. I brought in as much as we could just in case for other systems. We need to join on order, id or order number, order, name. I try to bring everything in there.
58 00:11:14.330 ⇒ 00:11:15.335 Brian Pei: Currency.
59 00:11:17.200 ⇒ 00:11:25.219 Brian Pei: we will have a discussion at some point to see if the amount columns we need to do currency conversion, or if shopify, does it?
60 00:11:25.770 ⇒ 00:11:31.359 Brian Pei: I believe everything is in Usd if that’s not the case. We can talk about currency, conversion,
61 00:11:32.190 ⇒ 00:11:34.490 Brian Pei: arithmetic at a later date.
62 00:11:34.510 ⇒ 00:11:36.770 Brian Pei: Thankfully, this is all in dev. So
63 00:11:37.268 ⇒ 00:11:39.710 Brian Pei: financial status and fulfillment status.
64 00:11:39.990 ⇒ 00:11:45.190 Brian Pei: And then I try to put all of the numeric amount columns in one place
65 00:11:45.933 ⇒ 00:11:49.529 Brian Pei: the subtotal price and the total price.
66 00:11:51.220 ⇒ 00:11:58.129 Brian Pei: Which should be the subtotal price, with taxes and discounts and shipping added to it.
67 00:11:58.200 ⇒ 00:12:06.969 Brian Pei: I have not done the validation for it yet, but that’s usually what it means. We’ll do a data validation as well after we go through questions.
68 00:12:08.720 ⇒ 00:12:17.919 Brian Pei: but discounts discount code. If they used a discount code or there’s a discount category, I think sometimes it’s not a code. It’s just like
69 00:12:17.980 ⇒ 00:12:26.239 Brian Pei: a broad discount. But if there is a code it should show up here. If there are multiple code codes it shows up separated by a comma
70 00:12:28.480 ⇒ 00:12:36.129 Brian Pei: I don’t know if you guys do tips, but there is a tip received column, so added it, if you guys don’t do tips, then I’m just gonna remove this
71 00:12:37.703 ⇒ 00:12:39.029 Brian Pei: and then
72 00:12:39.230 ⇒ 00:12:44.340 Brian Pei: current total price current total discounts current subtotal price current total tax
73 00:12:44.950 ⇒ 00:12:51.279 Brian Pei: refund amount. If there is a refund and a refund code, let me see if there’s a refund code that we can look at.
74 00:12:53.270 ⇒ 00:12:56.660 Brian Pei: because I can’t remember if this is a code or like a category.
75 00:12:57.920 ⇒ 00:13:00.150 Brian Pei: Okay, I. This is not a
76 00:13:00.370 ⇒ 00:13:05.105 Brian Pei: code that the customer fills in. I think it’s like a system.
77 00:13:05.650 ⇒ 00:13:11.090 Brian Pei: refund discrepancy. I think sometimes it’s like customer issued. Ref, here, let me
78 00:13:11.750 ⇒ 00:13:13.280 Brian Pei: just take a look at this.
79 00:13:13.300 ⇒ 00:13:14.810 Brian Pei: Just so, I understand.
80 00:13:19.440 ⇒ 00:13:23.989 Brian Pei: Oh, yeah, okay, shipping refund refund discrepancy. It’s mostly just that
81 00:13:24.130 ⇒ 00:13:26.600 Brian Pei: shipping refund or refund discrepancy.
82 00:13:27.960 ⇒ 00:13:29.520 Brian Pei: I think it’s just those 2.
83 00:13:34.060 ⇒ 00:13:38.100 Brian Pei: okay, so that’s refunds shipping we got from a shipping table
84 00:13:38.425 ⇒ 00:13:43.150 Brian Pei: shipping price and then shipping discounted price if they got a discount on the shipping.
85 00:13:44.073 ⇒ 00:13:47.316 Brian Pei: There’s a shipping code from the table.
86 00:13:48.620 ⇒ 00:13:52.119 Brian Pei: I think these are actually the only 3 that I saw in there.
87 00:13:52.710 ⇒ 00:13:54.409 Brian Pei: And then
88 00:13:54.970 ⇒ 00:13:57.619 Brian Pei: some of these cities are blocked out.
89 00:13:57.790 ⇒ 00:14:04.910 Brian Pei: but most of them aren’t. I don’t know why some are blocked out. I’ve never seen the asterisks here before. It might be
90 00:14:05.380 ⇒ 00:14:09.809 Brian Pei: on the user side if they choose some sort of option for limited data.
91 00:14:10.315 ⇒ 00:14:14.884 Brian Pei: But for the most part we have where it’s being shipped.
92 00:14:15.630 ⇒ 00:14:22.189 Brian Pei: They did have their actual address. I took that out usually for orders. People don’t
93 00:14:22.780 ⇒ 00:14:26.300 Brian Pei: use that if you do need the address.
94 00:14:26.340 ⇒ 00:14:37.280 Brian Pei: like the actual address, would then be considered like Pii. But it’s something we can add if we need it, for the purposes of just the order table. I just have city country.
95 00:14:37.320 ⇒ 00:14:40.850 Brian Pei: country code, state state code
96 00:14:41.070 ⇒ 00:14:47.690 Brian Pei: and then latitude, longitude. If you guys ever do fun, fancy map visualizations. You can plot that on a map.
97 00:14:49.840 ⇒ 00:14:56.550 Brian Pei: I did notice. This weight is 0 a lot when it probably shouldn’t be. But it’s not something I kind of had time to
98 00:14:56.870 ⇒ 00:15:01.410 Brian Pei: dig into. Or if you guys care about reporting on product weight.
99 00:15:02.064 ⇒ 00:15:03.579 Brian Pei: We do have
100 00:15:03.600 ⇒ 00:15:08.789 Brian Pei: the product weight. And I think pounds and grams in the product table.
101 00:15:08.800 ⇒ 00:15:10.230 Brian Pei: So maybe.
102 00:15:10.390 ⇒ 00:15:22.740 Brian Pei: instead of grabbing weight from the order object, we join it to product and get the weight there. But if you don’t need reporting on product weight, we can kind of just get rid of this, so you can let me know about that later.
103 00:15:24.610 ⇒ 00:15:27.849 Brian Pei: there is. There’s a lot of tick tock here, but
104 00:15:27.910 ⇒ 00:15:32.429 Brian Pei: there’s a the source name is related to the payment gateway.
105 00:15:32.440 ⇒ 00:15:34.600 Brian Pei: which is either shopify or Tiktok shop
106 00:15:34.700 ⇒ 00:15:38.029 Brian Pei: through the integration. It all kind of like flows through here.
107 00:15:38.841 ⇒ 00:15:42.609 Brian Pei: And then a bunch of those shopify tags
108 00:15:43.230 ⇒ 00:15:53.660 Brian Pei: I pivoted them into columns. Cause. If you, if you join on tag and order, id. It duplicates the order. So you have to like pivot it per tag value.
109 00:15:54.210 ⇒ 00:16:11.614 Brian Pei: I don’t know if that makes sense. But so, for for example, there is a tag that is like this, order is a subscription recurring order. So I join it to just that value. I pivot it and make it a Boolean. I know that there’s different ways. We can do this, Pius. We can talk about that as well.
110 00:16:11.990 ⇒ 00:16:17.580 Brian Pei: I’m just. I’m just gonna show everything now and then, you know, this will go through a lot of changes this week, I’m sure.
111 00:16:18.214 ⇒ 00:16:24.999 Brian Pei: So. But of those tags I saw. Is this order a subscription recurring order?
112 00:16:25.010 ⇒ 00:16:30.139 Brian Pei: Is it a subscription order? Is it a renewal? Is it a reactivation?
113 00:16:30.360 ⇒ 00:16:34.549 Brian Pei: And then there are tags for 1st time. Customer.
114 00:16:34.580 ⇒ 00:16:36.539 Brian Pei: is it their 1st subscription?
115 00:16:36.830 ⇒ 00:16:40.409 Brian Pei: And then they also had second order, 3rd order.
116 00:16:40.720 ⇒ 00:16:45.149 Brian Pei: They also had 4th order and 5th order. I didn’t want to overwhelm with the amount of columns, but
117 00:16:46.490 ⇒ 00:16:49.921 Brian Pei: Pai suggested we could also just do a
118 00:16:51.370 ⇒ 00:16:55.124 Brian Pei: index ranking, or a row number ranking on the orders
119 00:16:56.556 ⇒ 00:17:04.199 Brian Pei: partition by the customer. Id. And you can kind of just like you can see, like, what number order. Is this so? If I had 12 orders out of the 12,
120 00:17:04.569 ⇒ 00:17:06.890 Brian Pei: what number order would that be?
121 00:17:07.190 ⇒ 00:17:34.039 Payas Parab: Yeah, that’s also yeah. The the context there, too, is like for the retention curves. It’s really easy to see like which order they. That’s how we built it in amplitude. It was like the what order number it was. And then that forms the retention curve of like, what percentage carry over to the next order number. So I think that would be like an easier way to kind of see that and then the one thing I also wanted to call out to is, we just gotta figure out the subscription versus like a renewal right? Like, if it’s like
122 00:17:34.040 ⇒ 00:17:52.200 Payas Parab: a renewed on a 1st time subscription. Do we count that as a second order? Do we count that as like an extension of the 1st order? Just making sure we get that clear, because I saw there are some renewals that are classified as like a second order. So we just want like, I also with them on here, right is like how we should think about like what is considered a 1st order. What is a second order?
123 00:17:52.558 ⇒ 00:17:57.630 Payas Parab: How do we look at like a renewal versus like a new 1st time. Subscription type deal.
124 00:17:57.770 ⇒ 00:17:58.460 Brian Pei: Yeah.
125 00:17:59.650 ⇒ 00:18:00.947 Brian Pei: all things that
126 00:18:02.120 ⇒ 00:18:03.220 Brian Pei: will.
127 00:18:03.450 ⇒ 00:18:07.830 Brian Pei: We’ll probably well, we’ll obviously like, get your input and then
128 00:18:07.990 ⇒ 00:18:10.886 Brian Pei: quote unquote, prove it with the data like, I said, like,
129 00:18:11.290 ⇒ 00:18:22.110 Brian Pei: I or we busted through just like SQL. Business logic. We haven’t done like the data validation but just showing you the overall picture for our 1st prototype
130 00:18:22.670 ⇒ 00:18:35.340 Brian Pei: and then some other interesting tags that I saw that I don’t know if you care about or not. Well, is. Tiktok Shop is an important one, but there was also there’s a Klaviyo tag, and there’s a Snapchat tag. We didn’t talk about Snapchat, but I saw it in there. So I was like.
131 00:18:35.540 ⇒ 00:18:39.115 Brian Pei: why not include it? If it’s if Snapchat is an important
132 00:18:40.170 ⇒ 00:18:41.979 Brian Pei: avenue for you guys?
133 00:18:43.235 ⇒ 00:18:43.990 Brian Pei: So
134 00:18:44.170 ⇒ 00:18:49.890 Brian Pei: that out of all these tables. This is the meatiest one. Do you have any initial thoughts I’m on?
135 00:18:55.020 ⇒ 00:18:57.460 Brian Pei: I think you’re muted. Does anyone else hear.
136 00:18:58.200 ⇒ 00:18:59.310 Payas Parab: I can’t hear him either.
137 00:19:03.130 ⇒ 00:19:06.349 Nicolas Sucari: Now you’re muted. Now you’re not muted, but we are not hearing you.
138 00:19:06.350 ⇒ 00:19:08.065 Brian Pei: Yeah. Now, you’re not muted with no audio.
139 00:19:09.120 ⇒ 00:19:11.909 Nicolas Sucari: Something happened with the yeah microphone. Maybe.
140 00:19:18.530 ⇒ 00:19:19.350 Aman Nagpal: How about now?
141 00:19:19.950 ⇒ 00:19:20.570 Aman Nagpal: Yep.
142 00:19:20.570 ⇒ 00:19:21.750 Nicolas Sucari: We can hear you now.
143 00:19:21.750 ⇒ 00:19:22.070 Brian Pei: Yep.
144 00:19:22.070 ⇒ 00:19:23.400 Aman Nagpal: Yeah. Okay. Sorry.
145 00:19:23.710 ⇒ 00:19:28.349 Aman Nagpal: Yeah. Everything looks good. So far, I do have random questions on some of these. But
146 00:19:28.690 ⇒ 00:19:35.190 Aman Nagpal: is there something else you want to go over on today’s call? Or do you want me to kind of dive into this a little bit?
147 00:19:36.240 ⇒ 00:19:38.500 Brian Pei: I think these will be a lot quicker.
148 00:19:38.680 ⇒ 00:19:44.997 Brian Pei: Maybe I’ll I’ll if you give me 5 min I’ll go. These are much smaller and more straightforward. So
149 00:19:45.550 ⇒ 00:19:48.379 Brian Pei: let’s yeah, let me let me go through.
150 00:19:48.520 ⇒ 00:19:53.439 Brian Pei: No, I’ll write a new query. Let me go through these very quickly, and then we can kind of put it all together.
151 00:19:53.820 ⇒ 00:19:54.610 Aman Nagpal: Sounds good.
152 00:19:56.560 ⇒ 00:19:58.380 Brian Pei: Okay, so
153 00:19:58.630 ⇒ 00:19:59.790 Brian Pei: customer
154 00:19:59.950 ⇒ 00:20:01.989 Brian Pei: is all from shopify right now.
155 00:20:02.806 ⇒ 00:20:04.220 Brian Pei: I have.
156 00:20:04.250 ⇒ 00:20:08.840 Brian Pei: In the middle of the week. I’m going to try to do an Amazon customer
157 00:20:09.276 ⇒ 00:20:17.889 Brian Pei: mapping. If it works, it works. If it doesn’t, it doesn’t. It depends on how good that Amazon data is. But all of this is coming from shopify right now, just to let you know.
158 00:20:18.535 ⇒ 00:20:23.150 Brian Pei: So this is a customer Id joins onto the customer Id and orders
159 00:20:23.632 ⇒ 00:20:29.750 Brian Pei: this is the Pii. This is 1st name, last name, full name email phone, full address.
160 00:20:31.350 ⇒ 00:20:35.870 Brian Pei: lifetime orders that comes from shopify. So
161 00:20:35.950 ⇒ 00:20:48.170 Brian Pei: sometimes there’s a difference between shopify’s reported lifetime orders, and if I took the orders, table counted distinct order Id. And joined it to customer. Sometimes they’re different.
162 00:20:48.210 ⇒ 00:20:56.810 Brian Pei: It depends on if shopify counts like refunded and canceled orders as an order or not. Just a call out that will
163 00:20:56.840 ⇒ 00:21:04.190 Brian Pei: figure out later. I did add this this morning from recommendation, from pious with some other stuff. But I did join this
164 00:21:04.350 ⇒ 00:21:11.159 Brian Pei: 2 of to the order table to find out when, if they made any orders, what was their 1st order date and their most recent order. Date.
165 00:21:12.830 ⇒ 00:21:14.270 Brian Pei: This is
166 00:21:14.897 ⇒ 00:21:19.412 Brian Pei: this is not business logic. This comes straight from the customer object. It’s
167 00:21:20.030 ⇒ 00:21:24.059 Brian Pei: Were they a subscriber in the past, and are they an active subscriber now?
168 00:21:24.710 ⇒ 00:21:33.649 Brian Pei: Total spent? Is the same question as lifetime orders? There is a total spent column coming straight from shopify.
169 00:21:33.840 ⇒ 00:21:35.310 Brian Pei: I don’t know
170 00:21:35.540 ⇒ 00:21:43.309 Brian Pei: what spent means. If it’s net or gross, or if they subtract refunds or total we need, or if it’s like
171 00:21:43.670 ⇒ 00:21:52.509 Brian Pei: currency converted. We need to validate that, but I brought it in just because it’s in shopify. So might as well. So we can make this custom or use this field
172 00:21:53.285 ⇒ 00:22:01.994 Brian Pei: and then there was an email marketing skew, of subscribed or not subscribed and an email marketing level.
173 00:22:02.490 ⇒ 00:22:14.030 Brian Pei: I think it’s just. There’s different options in shopify, you guys probably just have. I don’t know what unknown is unknown is but single opt in is, I think, the only other one that I see in here, if possible, or if they have it
174 00:22:14.995 ⇒ 00:22:20.670 Brian Pei: and then I believe, if like, these are null and they have 0 orders.
175 00:22:21.542 ⇒ 00:22:23.389 Brian Pei: They must have just
176 00:22:23.700 ⇒ 00:22:26.470 Brian Pei: went to the website and put in their
177 00:22:26.820 ⇒ 00:22:31.110 Brian Pei: information, and just either not ordered anything, or
178 00:22:31.570 ⇒ 00:22:36.579 Brian Pei: or they they saved at some step and then didn’t come back. So
179 00:22:37.250 ⇒ 00:22:40.330 Brian Pei: this is, it’s a lot of customers.
180 00:22:42.100 ⇒ 00:22:44.240 Brian Pei: but it’s not a
181 00:22:44.690 ⇒ 00:22:52.369 Brian Pei: not all of them have obviously made one order. I think we talked about this last week. There’s 1.5 million people in here.
182 00:22:52.470 ⇒ 00:22:53.025 Brian Pei: but
183 00:22:53.680 ⇒ 00:22:55.339 Brian Pei: not. All of them have
184 00:22:55.520 ⇒ 00:22:58.410 Brian Pei: gone through the funnel of making their 1st order.
185 00:22:59.607 ⇒ 00:23:01.370 Brian Pei: So that’s customer.
186 00:23:01.830 ⇒ 00:23:03.310 Brian Pei: and then I’ll
187 00:23:03.850 ⇒ 00:23:07.510 Brian Pei: even more quickly go through product and product. Variant
188 00:23:07.810 ⇒ 00:23:10.229 Brian Pei: product is the
189 00:23:10.310 ⇒ 00:23:12.083 Brian Pei: parent product.
190 00:23:13.290 ⇒ 00:23:16.789 Brian Pei: here is, here are the titles
191 00:23:17.710 ⇒ 00:23:20.573 Brian Pei: should probably get rid of test, but it’s all good.
192 00:23:21.410 ⇒ 00:23:27.510 Brian Pei: There’s gift cards and offers and syrup and concentrated. It should all be in here. But this is
193 00:23:28.800 ⇒ 00:23:31.860 Brian Pei: the singular products
194 00:23:32.080 ⇒ 00:23:39.410 Brian Pei: when and there’s a product type. But it’s not all filled in which is kind of it’s on the system side. But it’s
195 00:23:40.080 ⇒ 00:23:41.610 Brian Pei: not that big of a deal.
196 00:23:41.930 ⇒ 00:23:44.559 Brian Pei: This one’s really quick when it was created
197 00:23:44.760 ⇒ 00:23:47.199 Brian Pei: when it was published onto the website
198 00:23:47.557 ⇒ 00:24:00.419 Brian Pei: and whether it’s active or in a draft state, just it’s not on the website yet. But it’s in draft and any product tags that I found attributed to it. That may or may not be helpful in the future. But I just brought it in
199 00:24:00.907 ⇒ 00:24:06.809 Brian Pei: for you know, for any analysts to to take a look at. What of these are?
200 00:24:07.865 ⇒ 00:24:08.700 Brian Pei: Important
201 00:24:09.221 ⇒ 00:24:13.550 Brian Pei: and then the variant is at a level of
202 00:24:15.250 ⇒ 00:24:18.350 Brian Pei: it’s broken down by
203 00:24:18.980 ⇒ 00:24:29.859 Brian Pei: the different combinations. I think that people can get these products, whether they’re in bundles or a 3 pack or 3 bottles like kind of it’s the the child of the
204 00:24:30.070 ⇒ 00:24:32.459 Brian Pei: product is is what this is.
205 00:24:32.480 ⇒ 00:24:46.340 Brian Pei: and this is where there is a price attributed and a skew attributed to the child product. When it’s like bundled and available to be purchased on your website. With
206 00:24:46.670 ⇒ 00:24:53.960 Brian Pei: quantity grams and weight in pounds or grams depending on what is in your system.
207 00:24:55.730 ⇒ 00:25:02.615 Brian Pei: so yeah, so those those were quick. So that’s what we have so far. Like, I said. After this
208 00:25:03.200 ⇒ 00:25:11.620 Brian Pei: order line item is important. So shopify order, one order Id could have multiple line items. So you need that broken up by line. Item.
209 00:25:12.142 ⇒ 00:25:16.589 Brian Pei: which is just different tables. So I gotta do order line. Item.
210 00:25:16.620 ⇒ 00:25:23.039 Brian Pei: I gotta do subscriptions. And then I have to do this
211 00:25:23.280 ⇒ 00:25:29.920 Brian Pei: for Amazon and try to get it in a similar structure as what we have in shopify.
212 00:25:30.100 ⇒ 00:25:32.230 Brian Pei: so that they can quote, unquote.
213 00:25:32.420 ⇒ 00:25:38.480 Brian Pei: not talk to each other, but quote unquote, sit on top of each other. If you want to report holistically across different selling platforms?
214 00:25:39.544 ⇒ 00:25:49.609 Brian Pei: I think that’s all I got, and my mouth is dry. So I’m gonna take a sip of water, but now I can hand it back to you or anyone who has questions, and I’ll try to take as many notes as possible.
215 00:25:51.210 ⇒ 00:25:58.509 Aman Nagpal: Awesome. Thank you. This looks great so far pious. Do you wanna jump in before I do anything on your side?
216 00:25:59.018 ⇒ 00:26:20.629 Payas Parab: I actually just wanted to like just for some context, just to like, not have to spam you with everything like some of the small feedback based on stuff. We built an amplitude. I’ve been sending directly to the guys just to like, you know, small edits and things like that. But here it’s really your feedback. I’ve just been as they kind of make these tables anything I can chip in based on the amplitude work. I I just chip in as we go. Yeah.
217 00:26:21.240 ⇒ 00:26:30.692 Aman Nagpal: Sweet sounds good. Yeah, no, I think we pretty much have the bulk of everything. It’s just minor tweaks. We may or may not need to make
218 00:26:31.230 ⇒ 00:26:33.910 Aman Nagpal: So if you go to the 1st table, for example.
219 00:26:34.020 ⇒ 00:26:37.710 Aman Nagpal: it says, order, name. So order. Id is what.
220 00:26:37.740 ⇒ 00:26:43.260 Aman Nagpal: when you open up an order in shopify it pops up in the URL at the end. Order, name
221 00:26:43.340 ⇒ 00:26:47.329 Aman Nagpal: or yeah, name and number. Are these the same or different.
222 00:26:48.390 ⇒ 00:26:49.070 Brian Pei: The number looks.
223 00:26:49.070 ⇒ 00:26:50.230 Aman Nagpal: That’s adjacent.
224 00:26:50.230 ⇒ 00:26:55.639 Brian Pei: And the name looks like it’s the number with some sort of
225 00:26:55.970 ⇒ 00:26:56.910 Brian Pei: string.
226 00:26:57.490 ⇒ 00:26:58.290 Brian Pei: Yeah, so.
227 00:26:58.290 ⇒ 00:27:05.460 Aman Nagpal: So pretty much all of our orders in shopify we end with dash. Jc. It looks like these 2 columns may be the same, but one with and one without. Jc.
228 00:27:05.550 ⇒ 00:27:07.750 Aman Nagpal: I don’t know if we need to keep
229 00:27:07.820 ⇒ 00:27:13.239 Aman Nagpal: both. But that’s I’ll leave that up to you, if it’s, you know doesn’t make a difference. Then we can keep both.
230 00:27:13.420 ⇒ 00:27:14.095 Aman Nagpal: Okay,
231 00:27:14.890 ⇒ 00:27:19.986 Aman Nagpal: processed and close, that I have absolutely no idea. So if we can try to find that out.
232 00:27:20.310 ⇒ 00:27:25.790 Aman Nagpal: you know. Just just let me know. And I can take a look also what those refer to, and we can see
233 00:27:26.376 ⇒ 00:27:29.059 Aman Nagpal: what needs to happen there.
234 00:27:30.740 ⇒ 00:27:33.530 Aman Nagpal: If you can just go to the left whenever you’re done.
235 00:27:33.620 ⇒ 00:27:35.640 Aman Nagpal: we’re always beginning. Yeah.
236 00:27:35.860 ⇒ 00:27:37.720 Aman Nagpal: just wanna make sure I didn’t miss anything.
237 00:27:38.760 ⇒ 00:27:46.400 Aman Nagpal: Number looks good tick. So tick tock order id. This is being pulled from the tag. That, says Tiktok, order id. Is that right?
238 00:27:47.060 ⇒ 00:27:51.939 Brian Pei: Yeah, it’s just yeah. It trims out that string, and it brings in whatever the numbers are after it.
239 00:27:52.900 ⇒ 00:27:57.360 Aman Nagpal: Okay? And then, if it’s not a Tiktok order, this is just blank.
240 00:27:58.100 ⇒ 00:28:00.749 Brian Pei: I believe so. Let’s see.
241 00:28:04.640 ⇒ 00:28:05.470 Brian Pei: No.
242 00:28:05.730 ⇒ 00:28:07.070 Brian Pei: yeah, it’s just null.
243 00:28:07.770 ⇒ 00:28:09.142 Aman Nagpal: That that works?
244 00:28:09.880 ⇒ 00:28:22.539 Aman Nagpal: What if I’m just gonna throw scenarios here? What if we switch to a different app that syncs to that course shopify, and it doesn’t differently. Then at that point we would need to adjust it right or
245 00:28:24.870 ⇒ 00:28:29.509 Aman Nagpal: I don’t know any other sorts of change we make with syncing tick, tock, to shopify.
246 00:28:30.250 ⇒ 00:28:34.960 Brian Pei: Yeah, at a certain point. If this is possible for
247 00:28:35.310 ⇒ 00:28:38.350 Brian Pei: a 1st version, that
248 00:28:38.480 ⇒ 00:28:46.840 Brian Pei: the long term solution is to sync it from Tiktok itself so that you can swap it with whatever systems you want, and we just have
249 00:28:47.340 ⇒ 00:28:53.169 Brian Pei: orders from Tiktok. And then these shopify. Quote unquote Tiktok orders.
250 00:28:53.871 ⇒ 00:29:08.990 Brian Pei: I would purge them from the Shopify table and then union in the Tiktok orders from the Tiktok platform. But but like you said, this is what shopify’s Tiktok integration is telling me.
251 00:29:09.940 ⇒ 00:29:23.319 Aman Nagpal: Yeah, no, that’s I’m used to thinking from the perspective of amplitude where once it’s in, it’s in. But obviously the whole reason we’re doing this is, we can be flexible for the future. Right? So any changes like that that happen, we can always readjust later. Right?
252 00:29:24.240 ⇒ 00:29:24.880 Aman Nagpal: Yeah.
253 00:29:24.880 ⇒ 00:29:25.980 Brian Pei: Yeah, we can definitely adjust later.
254 00:29:25.980 ⇒ 00:29:32.709 Aman Nagpal: Okay, sweet. Yeah. These, like I’ve mentioned, created at updated, that makes sense processed close. I’m not sure.
255 00:29:33.318 ⇒ 00:29:36.510 Aman Nagpal: There was a couple of others, I think.
256 00:29:36.760 ⇒ 00:29:40.260 Aman Nagpal: cancelled at and cancelled reason
257 00:29:41.070 ⇒ 00:29:42.669 Aman Nagpal: and same, for I think.
258 00:29:43.490 ⇒ 00:29:51.760 Aman Nagpal: probably for refund or return reason. When they click other, there is an explanation. It asks us to type in
259 00:29:52.068 ⇒ 00:29:57.090 Aman Nagpal: I don’t know if we’ll use that, but maybe it doesn’t hurt to have it thrown in there.
260 00:29:58.600 ⇒ 00:30:01.680 Brian Pei: From the user or from the company.
261 00:30:02.080 ⇒ 00:30:04.090 Aman Nagpal: From the admin side.
262 00:30:04.340 ⇒ 00:30:06.109 Brian Pei: Oh, admin side.
263 00:30:06.890 ⇒ 00:30:09.529 Brian Pei: look for a description.
264 00:30:09.750 ⇒ 00:30:11.630 Brian Pei: pretext, description, I assume.
265 00:30:12.160 ⇒ 00:30:13.120 Brian Pei: or.
266 00:30:14.275 ⇒ 00:30:20.649 Aman Nagpal: Yes, yeah. If for it could be for either or both cancel reason. And
267 00:30:20.700 ⇒ 00:30:22.869 Aman Nagpal: well, you have cancel reason here, or
268 00:30:23.930 ⇒ 00:30:27.279 Aman Nagpal: return or refund reason. I think it’s when you click other.
269 00:30:27.890 ⇒ 00:30:28.830 Brian Pei: Oh, shit!
270 00:30:29.800 ⇒ 00:30:34.780 Brian Pei: It’s like refund reason equals out there. Okay, I can look for that.
271 00:30:36.068 ⇒ 00:30:39.140 Aman Nagpal: Financial status is good. Currency is good
272 00:30:39.770 ⇒ 00:30:46.140 Aman Nagpal: fulfillment status shipping. I noticed you had it a little bit to the right. I don’t know if the order of the columns matters, but usually we look at it.
273 00:30:47.680 ⇒ 00:30:49.210 Brian Pei: It doesn’t. I can move around.
274 00:30:49.210 ⇒ 00:30:51.843 Aman Nagpal: Yeah, okay, perfect. So that doesn’t matter.
275 00:30:52.910 ⇒ 00:31:00.159 Aman Nagpal: What else the totals? I know you’re throwing in everything possible. Just so we have flexibility. Obviously the end result.
276 00:31:00.458 ⇒ 00:31:04.219 Aman Nagpal: And pies can speak more to this. But I I think we pretty much
277 00:31:04.290 ⇒ 00:31:07.630 Aman Nagpal: look at total order total.
278 00:31:07.820 ⇒ 00:31:12.709 Aman Nagpal: including shipping and reduce tax when we look at margins and things like that, right? Right?
279 00:31:12.710 ⇒ 00:31:13.280 Brian Pei: Yeah.
280 00:31:13.280 ⇒ 00:31:13.970 Aman Nagpal: Okay.
281 00:31:14.584 ⇒ 00:31:17.129 Aman Nagpal: tips. I don’t think we use any of that. So.
282 00:31:17.130 ⇒ 00:31:22.300 Payas Parab: Yeah, at least I only saw 46 rows that have tips in it. So, and none of them were processed
283 00:31:22.470 ⇒ 00:31:25.910 Payas Parab: further than 2022. So it’s probably a negligible column.
284 00:31:25.910 ⇒ 00:31:26.700 Brian Pei: Oh, okay.
285 00:31:26.700 ⇒ 00:31:26.970 Payas Parab: Yeah.
286 00:31:27.240 ⇒ 00:31:28.809 Aman Nagpal: Yeah, that makes sense.
287 00:31:28.810 ⇒ 00:31:30.003 Brian Pei: Rob! Not
288 00:31:31.130 ⇒ 00:31:32.759 Brian Pei: oh, let me go back here. Sorry!
289 00:31:33.160 ⇒ 00:31:34.446 Aman Nagpal: Very good.
290 00:31:36.250 ⇒ 00:31:38.769 Aman Nagpal: All of this looks good.
291 00:31:39.210 ⇒ 00:31:41.349 Aman Nagpal: What do we got after.
292 00:31:43.160 ⇒ 00:31:45.099 Brian Pei: Oh, yeah, the the metadata.
293 00:31:46.400 ⇒ 00:31:50.890 Aman Nagpal: Yes, although the one with the stars. Could you give me the order number for that? I’m so curious?
294 00:31:51.010 ⇒ 00:31:52.510 Aman Nagpal: Why, it start out the city.
295 00:31:52.510 ⇒ 00:31:52.900 Brian Pei: Yeah.
296 00:31:52.900 ⇒ 00:31:53.780 Aman Nagpal: The address.
297 00:31:54.090 ⇒ 00:32:03.539 Brian Pei: I just. I keep running this over and over again and selects Random 10. Where what was it? It was shipping address City.
298 00:32:04.010 ⇒ 00:32:06.360 Brian Pei: Just put a bunch of asterisks. I guess
299 00:32:13.960 ⇒ 00:32:15.504 Brian Pei: there’s a lot.
300 00:32:16.260 ⇒ 00:32:19.240 Brian Pei: let’s see this this one. I mean, I could just
301 00:32:19.650 ⇒ 00:32:21.979 Brian Pei: send a bunch of them.
302 00:32:24.090 ⇒ 00:32:25.570 Aman Nagpal: Fulfilled by Tiktok.
303 00:32:26.140 ⇒ 00:32:31.590 Nicolas Sucari: Yeah, exactly. But by Tiktok it seems that all of the cities are with those stars.
304 00:32:32.970 ⇒ 00:32:37.770 Aman Nagpal: Yeah, I think that’s what this is. Cause they’re all tag, tick tock.
305 00:32:37.770 ⇒ 00:32:38.200 Brian Pei: Yeah, they.
306 00:32:38.280 ⇒ 00:32:39.280 Aman Nagpal: And
307 00:32:39.530 ⇒ 00:32:40.150 Aman Nagpal: it
308 00:32:40.260 ⇒ 00:32:46.270 Aman Nagpal: maybe the tag should say, fulfilled also, or shipping somewhere. Oh, yeah, it says fulfilled. Okay, so that makes sense.
309 00:32:46.500 ⇒ 00:32:48.960 Brian Pei: Okay, it’s kind of annoying. For
310 00:32:49.810 ⇒ 00:32:54.300 Brian Pei: like, if we ever group, if you ever run a report group by city.
311 00:32:54.480 ⇒ 00:32:55.559 Brian Pei: you’re gonna get
312 00:32:56.040 ⇒ 00:32:59.999 Brian Pei: Springfield and Sp. Star Star Star as different buckets. But
313 00:33:01.626 ⇒ 00:33:03.080 Brian Pei: well, hmm!
314 00:33:03.540 ⇒ 00:33:05.089 Brian Pei: I wonder if I could.
315 00:33:05.640 ⇒ 00:33:08.468 Brian Pei: If if the customer Id is
316 00:33:09.140 ⇒ 00:33:11.540 Brian Pei: right, then I can get city
317 00:33:11.860 ⇒ 00:33:13.615 Brian Pei: from the customer.
318 00:33:15.950 ⇒ 00:33:16.679 Brian Pei: just gonna make sure.
319 00:33:16.680 ⇒ 00:33:18.749 Aman Nagpal: Gut tells me, shopify
320 00:33:19.440 ⇒ 00:33:23.680 Aman Nagpal: like my God tells me the Tiktok order when it comes in, is probably unique.
321 00:33:23.780 ⇒ 00:33:26.430 Aman Nagpal: Customer Id. Every time in shopify. I would think.
322 00:33:26.430 ⇒ 00:33:29.170 Brian Pei: I see. Oh, so it’ll be the same. Okay.
323 00:33:29.750 ⇒ 00:33:38.670 Aman Nagpal: Yeah, so it’ll it’ll always be separate. This is the issue we ran into with getting the Amazon data from the Api is like everything was start out, so it was impossible to
324 00:33:38.890 ⇒ 00:33:39.870 Aman Nagpal: kinda
325 00:33:40.500 ⇒ 00:33:42.719 Aman Nagpal: connect 2 people. Let’s say
326 00:33:43.016 ⇒ 00:33:45.850 Aman Nagpal: but maybe we can figure something out down the road for this.
327 00:33:45.850 ⇒ 00:34:03.359 Payas Parab: There’s also 1 1 potential avenue here, because this is coming from the orders database. So the shopify. A good amount of the payments are processed by shopify payments. So I’m like curious. Maybe if there’s another table about like customers coming from like the payments side like, if there’s like payments processed. If that has more info
328 00:34:03.675 ⇒ 00:34:16.300 Payas Parab: because, you know, as like a company, you should have right to like, you know, like check, verify information on like payments right to check valid customers, charge backs, etc. So there’s possibly a payments table that may have
329 00:34:16.310 ⇒ 00:34:19.510 Payas Parab: some more enriched data than this. Orders. Table.
330 00:34:21.889 ⇒ 00:34:27.439 Aman Nagpal: Yeah, no, I think that’d be good to look into if we can find anything like that. That make it much easier. I think.
331 00:34:32.540 ⇒ 00:34:35.209 Brian Pei: Can you? Can you write that up for me? Actually? So I.
332 00:34:35.219 ⇒ 00:34:46.827 Payas Parab: Yeah, I’m I’m gonna take. I’m taking some notes here. I’ll make sure, and I’ll look into it myself. I I when I worked at Tiktok. I worked on this integration. So I like, that’s like one of the workarounds that I know we were trying to like solve for.
333 00:34:47.060 ⇒ 00:34:48.620 Brian Pei: Yeah, that’d be that’d be awesome. Thank you.
334 00:34:50.399 ⇒ 00:34:58.779 Aman Nagpal: For these. The is klaviyo is Snapchat absolutely 0 idea what that’s for. I can find out. If you guys can just give me a list of
335 00:34:59.000 ⇒ 00:35:02.099 Aman Nagpal: things. You know, after the call that
336 00:35:02.350 ⇒ 00:35:05.270 Aman Nagpal: you’re not sure about, and maybe I’m not sure about I can go find out
337 00:35:06.030 ⇒ 00:35:07.630 Aman Nagpal: splitting up the
338 00:35:07.740 ⇒ 00:35:10.580 Aman Nagpal: order like 1st order, second, order, 3rd order.
339 00:35:14.330 ⇒ 00:35:15.710 Aman Nagpal: Does that.
340 00:35:18.180 ⇒ 00:35:20.609 Aman Nagpal: I mean, is it good to have it within
341 00:35:21.040 ⇒ 00:35:25.599 Aman Nagpal: a column? And do we have access to all other orders in the future. 5, th 6, th 7th order.
342 00:35:27.608 ⇒ 00:35:30.151 Brian Pei: This was the one that
343 00:35:30.710 ⇒ 00:35:34.290 Brian Pei: There’s a a shopify, automated tag
344 00:35:34.350 ⇒ 00:35:39.799 Brian Pei: that just as a string says, this is the customer’s second order, or 3, rd
345 00:35:39.840 ⇒ 00:35:40.910 Brian Pei: I think the.
346 00:35:40.910 ⇒ 00:35:41.210 Aman Nagpal: Got it.
347 00:35:41.210 ⇒ 00:35:48.289 Brian Pei: Highest I saw it go up to was like 6 or 7, and then either. It doesn’t do it anymore. I I don’t know. So
348 00:35:48.400 ⇒ 00:35:51.319 Brian Pei: I I threw it in there as just
349 00:35:51.510 ⇒ 00:36:00.470 Brian Pei: a potential feature. But there’s probably a better way to do it with SQL business logic than relying on the tag.
350 00:36:00.991 ⇒ 00:36:09.070 Brian Pei: I can throw it to highest, for you know analytical reasons why you would want, like a customer’s the number of their order.
351 00:36:09.690 ⇒ 00:36:12.719 Aman Nagpal: No, I think you’re right. So with the tags.
352 00:36:12.920 ⇒ 00:36:27.939 Aman Nagpal: I think we use as little as possible, because a lot of these are coming from us. So we have the shopify flow, set up, it checks for certain details, and then it’ll tag the order right? And that’s not as reliable versus, let’s say, looking up the orders table with that specific.
353 00:36:28.040 ⇒ 00:36:43.510 Aman Nagpal: you know, customer id like you said with sequel logic and going by date. So I think that’s way more appropriate and reliable for tags. You know. Same thing with renewal, and whatever it is, the fewer tags we can use to grab info from the better.
354 00:36:43.560 ⇒ 00:36:48.781 Aman Nagpal: Tiktok. I know we have to. That’s we’re kind of relying on the integration.
355 00:36:49.270 ⇒ 00:36:49.990 Aman Nagpal: but
356 00:36:50.280 ⇒ 00:36:53.289 Aman Nagpal: most other tags I think we can probably ignore.
357 00:36:54.140 ⇒ 00:36:54.710 Brian Pei: Okay.
358 00:36:55.350 ⇒ 00:36:57.140 Aman Nagpal: And find a different way to grab that info. Yeah.
359 00:36:57.960 ⇒ 00:36:58.870 Brian Pei: Sounds good to me
360 00:36:59.900 ⇒ 00:37:00.950 Brian Pei: that makes sense.
361 00:37:03.600 ⇒ 00:37:11.430 Aman Nagpal: And then is reactivation. Order is renewal. Should that just be one column called order type cause? That’s what we do in amplitude.
362 00:37:13.220 ⇒ 00:37:14.350 Brian Pei: Oh, I didn’t know that.
363 00:37:18.210 ⇒ 00:37:18.950 Brian Pei: Yeah.
364 00:37:19.830 ⇒ 00:37:23.369 Aman Nagpal: Does that? Does that make more sense? You think so? To break it down again?
365 00:37:23.932 ⇒ 00:37:26.140 Aman Nagpal: A subscription order is
366 00:37:26.180 ⇒ 00:37:32.140 Aman Nagpal: their 1st order that they place. That is a subscription a 1 time order is
367 00:37:32.250 ⇒ 00:37:35.860 Aman Nagpal: any order that they place. That’s 1 time items
368 00:37:36.464 ⇒ 00:37:46.150 Aman Nagpal: renewal order is their second or onwards rebuild or their 1st rebuild or onwards for a subscription and reactivation is
369 00:37:46.250 ⇒ 00:37:55.560 Aman Nagpal: they cancelled their subscription and use one of our reactivation pages to one click checkout and start up a new subscription.
370 00:37:56.030 ⇒ 00:38:16.020 Payas Parab: Yeah, this is a great call I’m on. We might need to just check this logic in here and build some type of like a like a column, wise like transformation here, because I see on here, like Row 2 is subscription. Order is true and is renewal. Order is true. So that means, I believe it’s like, is it? On? A subscription is what that column means, and then like, is it a renewal? Not the 1st time. So I think.
371 00:38:16.020 ⇒ 00:38:34.010 Payas Parab: like we made to build this order type. That’ll be like a custom logic. Basically, right like, is subscription order equals true and renewal order equals false. Then, you know, like, we’ll have to build some type of logic. I can help the guys with that. But yeah, that it seems that this one. This tag is a little bit different. We can look into that a little bit.
372 00:38:34.670 ⇒ 00:38:50.199 Aman Nagpal: I think, to your point. The way we’ve been doing it is in our minds. Renewal means it is a subscription order. But you know. Is this the better way to go about it? Maybe just as long as you know, everyone on the team is aware. You know, on our side. That, hey?
373 00:38:50.610 ⇒ 00:38:55.679 Aman Nagpal: Just because it’s subscription doesn’t mean it’s not a renewal. So I think we’ll need to figure out way to
374 00:38:55.810 ⇒ 00:38:57.450 Aman Nagpal: differentiate that
375 00:38:58.450 ⇒ 00:39:03.780 Payas Parab: We need to. I think that that I can add as a to do for me. Brian is just like, take those 3 columns and kind of do some
376 00:39:03.860 ⇒ 00:39:09.689 Payas Parab: like just like a Boolean map of like when this true, when this false, what this means, and then we can align on.
377 00:39:09.780 ⇒ 00:39:13.920 Payas Parab: Okay, this is what we’re defining as this, right? It is a subscription order.
378 00:39:14.280 ⇒ 00:39:19.800 Payas Parab: It is renewal. But like, it’s not a reactivation. Because, like there’s also like, maybe there’s a scenario where
379 00:39:19.870 ⇒ 00:39:37.859 Payas Parab: renewal is renewal is true. And then reactivation is also true, because it was like a renewal that got recaptured right like, if it was like they cancelled. And then you recapture, we just gotta check all that logic. So I I kind of have an idea of how I can do that, and I can take that as an action item. For for myself, Brian.
380 00:39:38.930 ⇒ 00:39:42.110 Brian Pei: That’d be great, cause I’m a little in the like. I.
381 00:39:42.110 ⇒ 00:39:42.680 Payas Parab: Yeah, yeah.
382 00:39:42.680 ⇒ 00:39:48.430 Brian Pei: My best, but there’s there’s so many, you know, cases that I might not know of. But I’m happy to pair with you on that
383 00:39:49.340 ⇒ 00:39:53.809 Brian Pei: it’s like a really important part of of that. But yeah, thank you.
384 00:39:54.920 ⇒ 00:40:02.187 Aman Nagpal: That sounds good guys anything else on this column. I think we’re close on time. No, you’re good.
385 00:40:02.580 ⇒ 00:40:05.549 Aman Nagpal: yeah, I I think you made a really good point there, Pius, where
386 00:40:06.300 ⇒ 00:40:11.300 Aman Nagpal: right now we consider the 1st order, reactivation, and all other future ones renewals. But
387 00:40:11.720 ⇒ 00:40:18.969 Aman Nagpal: will we ever compare churn rates of people who reactivated versus in on their initial subscription. Right.
388 00:40:21.340 ⇒ 00:40:29.920 Payas Parab: And I think I think like recharge is part of that story, too, like, unfortunately, I think there’s like, I wonder what happens when it’s like a recharge recapture right versus like a
389 00:40:29.970 ⇒ 00:40:37.480 Payas Parab: non recharge. So I I’ll look into that a little bit and kind of get get the team an answer on that. But I you know we dealt with this before in the amplitude so.
390 00:40:38.650 ⇒ 00:40:43.250 Aman Nagpal: Sounds good. My only other question is, how do you guys think we are
391 00:40:43.310 ⇒ 00:40:50.024 Aman Nagpal: in terms of timelines with? You know, we’re what like 2 2 weeks now I I forget
392 00:40:50.990 ⇒ 00:40:54.120 Aman Nagpal: and you know where we are, with the project.
393 00:40:57.240 ⇒ 00:40:59.630 Aman Nagpal: just in terms of pace like, are we on pace or.
394 00:41:00.390 ⇒ 00:41:11.770 Nicolas Sucari: I think we are on pace. Brian is working to get the other tables there. If we just make these adjustments on these tables. I think they will be ready to
395 00:41:11.810 ⇒ 00:41:34.750 Nicolas Sucari: start working on visualization tool. We’ll we’ll implement real so that you can start poking around into these data. But yeah, after doing these changes, we will. We will be contacting Paya so that we can go through the real process together, and and that will will be ready. I don’t know, Brian, how are you with the other tables that you’re working on.
396 00:41:35.890 ⇒ 00:41:39.259 Nicolas Sucari: unless you are waiting for the Amazon ones? Right? Yes.
397 00:41:39.680 ⇒ 00:41:41.829 Brian Pei: Yeah, the Amazon, and then
398 00:41:41.960 ⇒ 00:41:50.939 Brian Pei: the the quote on the subscriptions, entity I will, I’ll need help with. But while I’m doing that I can. Just. I’m just diving into Amazon.
399 00:41:52.750 ⇒ 00:42:06.570 Brian Pei: I feel like for the sequel modeling. I. Personally, I feel like I’ve been on a good pace. I feel like we. We got all of the data in fresh like last week, like I just was able to look at all the tables. And I got through a lot of them.
400 00:42:06.850 ⇒ 00:42:13.784 Brian Pei: And now, like we are it, we can iteratively discuss what we have and make changes, and I can build new tables.
401 00:42:15.360 ⇒ 00:42:17.570 Brian Pei: So yeah, I I feel comfortable.
402 00:42:18.270 ⇒ 00:42:26.890 Nicolas Sucari: I think, Amanas, we don’t have like a blueprint of what you guys need as the tables like this process of like this iterative process discussing
403 00:42:27.266 ⇒ 00:42:43.749 Nicolas Sucari: live. It’s it’s really good. We’re gonna aim for next week to have the rest of the tables and these adjustments. And yeah, we can send through slack some updates. And if that is ready, we can then start working on in integrating real and making the data available for you?
404 00:42:44.092 ⇒ 00:42:53.779 Nicolas Sucari: I don’t think we’re gonna spend so much time on that one. It’s just having the data there. And then, if we need any any adjustments. We can always
405 00:42:53.850 ⇒ 00:42:57.799 Nicolas Sucari: fix the the modeling or work on that, too.
406 00:42:58.090 ⇒ 00:42:58.870 Nicolas Sucari: Okay.
407 00:42:59.860 ⇒ 00:43:13.050 Aman Nagpal: That’s perfect. Yeah, just hit me up via slack before next week, so that any questions that you know you might still have as we iterate over this. I can try to get you response as soon as possible, especially if
408 00:43:13.382 ⇒ 00:43:27.019 Aman Nagpal: maybe it’s something I wanna run past Justin, and see what they think. And then hopefully, by the time we get to the next call we’ll have. We’ll be almost ready to go, I think I mean I think the bulk of it is there. It’s just minor tweaks
409 00:43:27.390 ⇒ 00:43:39.899 Aman Nagpal: from our side. And then Pius, I spoke to Robert. I know. I know. I think he’s traveling right. So he wanted a call set up with Justin and Jared, which he said, I think you could take.
410 00:43:39.900 ⇒ 00:43:40.460 Payas Parab: Sure. Yeah.
411 00:43:41.112 ⇒ 00:43:42.417 Aman Nagpal: I had
412 00:43:43.450 ⇒ 00:43:55.150 Aman Nagpal: Justin’s assistant. She’s supposed to be scheduling the call with you. Using that link that Robert sent me so hopefully, that’s done this week. I think it was just to get an idea of, especially the gross margin side, and any
413 00:43:55.160 ⇒ 00:44:23.340 Aman Nagpal: information from them that we might need, you know, during this process, and the other thing was, you know, Justin wanted to get going with this is only Robert said he would help us out with is hiring a data analyst. So I don’t know if that’s something we need to wait for him to come back on, or we can kind of start going through it. But if he has any candidates, or you know, I think we sent a job description kind of what we can do to get this process going so that we can onboard somebody as soon as possible.
414 00:44:23.340 ⇒ 00:44:27.939 Payas Parab: Sure. Yeah, let me you send it in the the joint slack we have there.
415 00:44:29.630 ⇒ 00:44:30.580 Payas Parab: The judge.
416 00:44:30.580 ⇒ 00:44:32.859 Aman Nagpal: It wasn’t a thread in one of the.
417 00:44:33.410 ⇒ 00:44:33.960 Payas Parab: Slacks.
418 00:44:34.270 ⇒ 00:44:34.970 Aman Nagpal: Yeah.
419 00:44:35.220 ⇒ 00:44:42.239 Payas Parab: Got it. Okay, let me let me take a look. I’m happy to kind of dive in. Maybe we’ll wait for Robert on that worksheet fully. But
420 00:44:42.530 ⇒ 00:44:51.248 Payas Parab: If there’s some candidates I even I discussed with them about like getting some interview questions ready essentially so you could like start to evaluate these people.
421 00:44:51.700 ⇒ 00:45:19.249 Payas Parab: yeah, I can do that. So I can. I can take the call with just talking through like gross margin as well as like other types of analysis. I’m planning on the analytics side here before that call with Justin. And and you guys just to like, do a few cuts of the data we already have, like, even though they’re like Dev, just to see like show you what what’s kind of capable now and like where we can go from here. If there’s any like top of mind business questions like that, you would. You’re thinking of right now, that are like, hey, we already have this in
422 00:45:19.350 ⇒ 00:45:34.029 Payas Parab: amplitude, and we can like quickly run that analysis for you. Let me know if there’s anything top of mind, because I can like prep that you know as well. Just let me know that, and then I can take that call, and then, if it comes down to the like helping with the recruitment process. I will also.
423 00:45:34.318 ⇒ 00:45:37.810 Payas Parab: I can take a look at the Jd. If Robert hasn’t given you feedback, and then
424 00:45:38.098 ⇒ 00:45:43.110 Payas Parab: also kind of like, maybe drafting up some interview questions. I can take those as some action items for myself. Here.
425 00:45:43.740 ⇒ 00:46:04.500 Aman Nagpal: That’d be awesome. Thank you. And then today, on the leadership call, I, you know, made a request for everyone. Just send me over any dashboards and reports that you’re currently using an amplitude. So hopefully, people send me those lists so that I can get those to you guys. Just because I know you wanted to see what other stakeholders are using. But yeah, I think that’s everything.
426 00:46:06.580 ⇒ 00:46:07.400 Nicolas Sucari: Excellent.
427 00:46:08.970 ⇒ 00:46:13.589 Nicolas Sucari: Okay, thank you very much, Aman. Thank you, Brian, for sharing everything.
428 00:46:15.540 ⇒ 00:46:16.600 Aman Nagpal: Thank you. Guys.
429 00:46:19.210 ⇒ 00:46:19.750 Nicolas Sucari: Bye, bye.
430 00:46:19.750 ⇒ 00:46:20.380 Aman Nagpal: Bye-bye.