Meeting Title: Eden Project Data Analysis Sync Date: 2025-12-16 Meeting participants: Henry Zhao, Sezim Zhenishbekova
WEBVTT
1 00:00:55.180 ⇒ 00:00:56.450 Sezim Zhenishbekova: Hi, Henry.
2 00:01:15.450 ⇒ 00:01:17.150 Henry Zhao: Sorry, I was typing something.
3 00:01:17.150 ⇒ 00:01:18.049 Sezim Zhenishbekova: No worries.
4 00:01:18.050 ⇒ 00:01:20.319 Henry Zhao: You’ve been like that, too, before, right? Where you’re, like, in the middle of.
5 00:01:20.320 ⇒ 00:01:27.490 Sezim Zhenishbekova: Yes, yes, I get it. So yeah, but right now, I think…
6 00:01:28.290 ⇒ 00:01:42.570 Sezim Zhenishbekova: like, Robert just, like, highlighted a special ticket that was created, like, 5 days, I think, and it was assigned to me yesterday, so I was like… and then I think, like, I delved deeper, like, maybe we should write an action plan for that, and I don’t know, like.
7 00:01:42.570 ⇒ 00:01:42.930 Henry Zhao: I know.
8 00:01:42.930 ⇒ 00:01:47.610 Sezim Zhenishbekova: It’s the priority higher than your… your… Johan’s request, right?
9 00:01:48.140 ⇒ 00:01:51.130 Sezim Zhenishbekova: I know they’re kind of somewhat similar, but kind of.
10 00:01:51.130 ⇒ 00:01:52.209 Henry Zhao: This is definitely… yeah.
11 00:01:52.210 ⇒ 00:01:55.060 Sezim Zhenishbekova: granular level, Bless you.
12 00:01:55.060 ⇒ 00:01:55.980 Henry Zhao: Thank you.
13 00:01:56.320 ⇒ 00:01:59.240 Sezim Zhenishbekova: in much granular scale. So…
14 00:01:59.950 ⇒ 00:02:23.679 Sezim Zhenishbekova: like, for Johan’s request, it’s more about retention rate, revenues, like, financial metrics per product, but I think for him, it’s more about inventory, like, what was the reason someone shut it down, why they’re not refilling it, what is causing it, which is completely different. I mean, in the end of the day, we can connect those sheets.
15 00:02:23.680 ⇒ 00:02:25.550 Sezim Zhenishbekova: But then…
16 00:02:25.850 ⇒ 00:02:31.919 Sezim Zhenishbekova: Yeah, so what do you think we should do now, and how should we proceed with this?
17 00:02:31.920 ⇒ 00:02:35.179 Henry Zhao: Yeah, if you have time, I can pull this data for you, and then if you can…
18 00:02:35.300 ⇒ 00:02:40.770 Henry Zhao: do the analysis, that would be great. You said you’re free as of what time? At 2, right?
19 00:02:41.040 ⇒ 00:02:54.410 Sezim Zhenishbekova: Yes, like, you can just… I will… I will try to push that aside, and just focus on this for today, because, like, I was… because since I’m waiting for the other one, and it’s a lot of data for other ones.
20 00:02:54.930 ⇒ 00:03:09.089 Sezim Zhenishbekova: I can… yeah, if you could help me to pull all the necessary data you think will be needed for this, and I can start building on my end, the projections, and keeping 48 days, I think, will be the best.
21 00:03:11.990 ⇒ 00:03:20.069 Henry Zhao: I guess I can give you 48 months, yeah, so we have new and existing customers, but how detailed do we want to get with renewal rates? Do we need to break it down by drug?
22 00:03:20.290 ⇒ 00:03:23.130 Henry Zhao: I think probably, right? Just maybe by drug group.
23 00:03:23.350 ⇒ 00:03:33.370 Sezim Zhenishbekova: Renewal rates for each refill, expected project renewal rates for each refill. So, so basically, that’s why I really wanted to have the customer ID to…
24 00:03:33.810 ⇒ 00:03:40.009 Sezim Zhenishbekova: To give them, like, color… colored shim, like, how healthy they are.
25 00:03:40.840 ⇒ 00:03:57.690 Sezim Zhenishbekova: from… and then assign them to special groups, categorize them. For example, if they came back to us, like, in a year or two, they’re the most healthiest, then we need to focus more on that period in time for, for additional perks that they can maintain their health.
26 00:03:57.790 ⇒ 00:04:11.739 Sezim Zhenishbekova: And then, like, assign those categories per user, not only, like, the first order and not first order. Like, I don’t know if I make sense, like, that’s what I was envisioning to do.
27 00:04:12.410 ⇒ 00:04:18.009 Henry Zhao: I don’t know that it still makes sense for customer ID. I think customer ID would only be if you want to, like, look into specific customers, right?
28 00:04:18.420 ⇒ 00:04:24.709 Henry Zhao: Because even if I gave you customer ID, you would still have to group it by something. It’s not like you’re gonna go through 186,000 customers, one by one.
29 00:04:24.710 ⇒ 00:04:36.320 Sezim Zhenishbekova: So, do you think, like, how does the tracking work? Like, how, for example, me, right? Let’s take a look. I’m the client with Eden, I’m having, like, Ozempic, and…
30 00:04:36.420 ⇒ 00:04:45.160 Sezim Zhenishbekova: how is the customer journey, like, like, the person… I found this Eden Shim, I’ll share you the link.
31 00:04:45.610 ⇒ 00:04:50.249 Sezim Zhenishbekova: event Shima, but it’s more about what kind of data we collect.
32 00:04:50.620 ⇒ 00:04:52.270 Sezim Zhenishbekova: Here’s the link.
33 00:04:52.490 ⇒ 00:04:57.990 Sezim Zhenishbekova: So I was wondering, like, for example, I get the order, and then how it’s…
34 00:04:58.490 ⇒ 00:05:07.270 Sezim Zhenishbekova: And then I have subscriptions, like, on a monthly basis. How do I know how many subscriptions I personally placed throughout the year? Like, how… what kind of…
35 00:05:07.270 ⇒ 00:05:07.770 Henry Zhao: Hmm.
36 00:05:07.770 ⇒ 00:05:12.539 Sezim Zhenishbekova: like, sees him as a patient type of view, do we have, yeah.
37 00:05:13.190 ⇒ 00:05:17.420 Henry Zhao: Let’s take a look. We might want to do this by treatments.
38 00:05:18.140 ⇒ 00:05:26.240 Henry Zhao: So let’s start with orders. So let’s start with, let’s start with transactions. Let’s start with transactions. So, there’s a hierarchy, there’s transactions, there’s orders.
39 00:05:26.480 ⇒ 00:05:28.930 Henry Zhao: And then there is…
40 00:05:29.110 ⇒ 00:05:34.870 Henry Zhao: treatments, right? So you can think of it like a transaction is like, I went to Amazon and I bought a
41 00:05:34.990 ⇒ 00:05:47.849 Henry Zhao: a suitcase. I went to Amazon and I bought a book. I went to Amazon and I bought, a phone. Like, those are transactions, right? Orders would be, like, I went to Amazon and I put a book and a phone and a suitcase in my cart, and then I checked out.
42 00:05:48.070 ⇒ 00:05:54.120 Henry Zhao: And a treatment would be like, I’m buying this Harry Potter series, and I’m gonna read the Harry Potter series throughout.
43 00:05:54.380 ⇒ 00:05:59.989 Henry Zhao: So let’s look at these three levels and say, like, what is the right level that we should be looking at it, right?
44 00:06:00.760 ⇒ 00:06:06.540 Henry Zhao: So, like, if you order the Harry Potter series and you can’t cancel it, then I wouldn’t look at it at the treatment level, so…
45 00:06:08.530 ⇒ 00:06:11.300 Henry Zhao: Whereas, like, Amazon, right? Like, if you put it all in the same cart.
46 00:06:11.300 ⇒ 00:06:11.710 Sezim Zhenishbekova: You can’t.
47 00:06:11.710 ⇒ 00:06:13.940 Henry Zhao: cancel, like, one item of it. So…
48 00:06:13.940 ⇒ 00:06:14.310 Sezim Zhenishbekova: Yeah.
49 00:06:14.310 ⇒ 00:06:22.879 Henry Zhao: Let’s start with transactions, because transactions for sure will work, but it’s going to be too big, I think. Because it’ll be… each individual thing is going to get a transaction.
50 00:06:22.880 ⇒ 00:06:27.149 Sezim Zhenishbekova: And I don’t know that refill data is here. Yeah, so I don’t see any refill data. Let’s check.
51 00:06:28.220 ⇒ 00:06:34.950 Henry Zhao: Okay, so transactions won’t work. So let’s go to the next level, which is orders, okay? So, when you look at transactions, it’s fact transactions.
52 00:06:37.810 ⇒ 00:06:43.320 Henry Zhao: If you want to look at orders, it’s gonna be order summary, which you’ve… which I’ve already pulled for you, okay?
53 00:06:45.820 ⇒ 00:06:49.489 Henry Zhao: Alright, order summary… This should have refills by now.
54 00:06:50.090 ⇒ 00:06:50.820 Sezim Zhenishbekova: Yes.
55 00:06:50.820 ⇒ 00:06:56.710 Henry Zhao: So it just has, is refill order. So is every refill considered as a new order, I wonder?
56 00:06:58.020 ⇒ 00:06:59.130 Henry Zhao: Gotta think.
57 00:06:59.940 ⇒ 00:07:09.269 Henry Zhao: Let’s go to the treatment one and look at somebody with refills and see what it looks like. So, we have to figure that part out. And then together, we can decide what level…
58 00:07:09.430 ⇒ 00:07:09.879 Sezim Zhenishbekova: I wanna pull…
59 00:07:09.880 ⇒ 00:07:11.200 Henry Zhao: with that, okay?
60 00:07:12.190 ⇒ 00:07:18.969 Henry Zhao: So I would look at Bask Treatment Updated, okay? So this Treatment Updated basically has each treatment ID,
61 00:07:19.080 ⇒ 00:07:33.770 Henry Zhao: So you can see, think of, like, the course of your medicines as one treatment, okay? So I get 6 months of Ozempic, then that’s one treatment, but it’s… it might be 6 orders, let’s find out, okay? So… let’s do one where the refill is…
62 00:07:34.080 ⇒ 00:07:36.319 Henry Zhao: Zero. So that means they’ve already done all the refills.
63 00:07:36.510 ⇒ 00:07:44.519 Henry Zhao: Okay, so select star from… Mask, treatment, updated, dot treatment, updated.
64 00:07:48.210 ⇒ 00:07:53.689 Henry Zhao: Where… refills remaining… is this how to do it?
65 00:07:53.870 ⇒ 00:07:55.199 Henry Zhao: equals zero?
66 00:07:57.220 ⇒ 00:08:06.079 Henry Zhao: Let’s do 6, and date… Timestamp is, like, before 6 months ago, so… That’s June.
67 00:08:08.310 ⇒ 00:08:11.470 Henry Zhao: I just picked 20 examples.
68 00:08:13.130 ⇒ 00:08:15.360 Henry Zhao: Is that refills remaining? Remaining refills.
69 00:08:17.970 ⇒ 00:08:28.369 Henry Zhao: Yeah, I’m walking through this with you, not for you to, like, learn the queries, per se, but just to, like, understand the logic. Okay, I’m just grabbing people that had 6 refills more than 6 months ago, so hopefully they took all of them.
70 00:08:28.550 ⇒ 00:08:31.560 Henry Zhao: Right, so let’s just pick one treatment ID.
71 00:08:32.830 ⇒ 00:08:35.300 Henry Zhao: Let’s see what it looks like, okay?
72 00:08:35.770 ⇒ 00:08:41.550 Henry Zhao: And this one is… Maintenance dose. I don’t like maintenance dose. Let’s look at a monthly plan, okay?
73 00:08:41.549 ⇒ 00:08:44.789 Sezim Zhenishbekova: So monthly plan, I think, will be more likely to…
74 00:08:46.780 ⇒ 00:08:48.100 Henry Zhao: Get the treatment ID.
75 00:08:48.340 ⇒ 00:08:52.860 Henry Zhao: Because maintenance dose could just be, like, Yeah, like…
76 00:08:52.860 ⇒ 00:08:59.149 Sezim Zhenishbekova: Just to… yeah, I think it’s just to keep them on the loop, because they might come back, so…
77 00:09:00.790 ⇒ 00:09:01.929 Henry Zhao: Let’s grab this one.
78 00:09:02.860 ⇒ 00:09:07.239 Henry Zhao: see what it looks like. Alright, so… select star… I’ll just do it on the same one.
79 00:09:07.960 ⇒ 00:09:12.450 Henry Zhao: where treatment… ID equals that.
80 00:09:12.900 ⇒ 00:09:17.220 Henry Zhao: So, like, this would be what I’m… I do when I want to look at by customer, like, I wouldn’t…
81 00:09:17.520 ⇒ 00:09:18.960 Henry Zhao: Export it all for you.
82 00:09:19.340 ⇒ 00:09:19.710 Sezim Zhenishbekova: Program.
83 00:09:19.820 ⇒ 00:09:21.519 Henry Zhao: Order by timestamp.
84 00:09:24.170 ⇒ 00:09:27.730 Henry Zhao: So I have 8 rows, so let’s see how many refills they started with.
85 00:09:29.320 ⇒ 00:09:30.490 Henry Zhao: 11.
86 00:09:31.290 ⇒ 00:09:32.750 Henry Zhao: And they still have 7 left.
87 00:09:33.510 ⇒ 00:09:36.830 Henry Zhao: Okay, so this is one person, right? Let’s make sure it’s one person.
88 00:09:38.430 ⇒ 00:09:39.880 Henry Zhao: I’m just gonna look at the email.
89 00:09:41.820 ⇒ 00:09:42.910 Henry Zhao: Megan Rowan, Megan.
90 00:09:42.910 ⇒ 00:09:43.230 Sezim Zhenishbekova: Namely.
91 00:09:43.230 ⇒ 00:09:47.450 Henry Zhao: I should probably speak quieter, I’m in public, and I’m just, like, shouting out this girl’s.
92 00:09:47.450 ⇒ 00:09:48.839 Sezim Zhenishbekova: Health information.
93 00:09:48.840 ⇒ 00:09:51.530 Henry Zhao: Alright, anyway, let’s look for their order number now…
94 00:09:52.350 ⇒ 00:09:54.970 Henry Zhao: Is there an order number, please?
95 00:09:56.560 ⇒ 00:09:58.410 Henry Zhao: Okay, I can find it really quickly.
96 00:09:59.340 ⇒ 00:10:01.740 Henry Zhao: Treatment… fast, new treatment.
97 00:10:02.190 ⇒ 00:10:03.999 Henry Zhao: The new treatment, or treatment created?
98 00:10:04.700 ⇒ 00:10:06.220 Henry Zhao: Mass New Treatment.
99 00:10:07.200 ⇒ 00:10:09.220 Henry Zhao: Treatment created.
100 00:10:10.810 ⇒ 00:10:12.099 Henry Zhao: This should tell me.
101 00:10:13.640 ⇒ 00:10:15.419 Henry Zhao: Okay, so there’s one treatment here.
102 00:10:15.900 ⇒ 00:10:18.429 Henry Zhao: Let’s see what the order is…
103 00:10:22.580 ⇒ 00:10:25.359 Henry Zhao: Ugh, I wish some of this stuff was easier to just quickly do.
104 00:10:25.590 ⇒ 00:10:31.380 Henry Zhao: So it’s monthly, so it’s monthly plan with 11 refills, so now we just want to look at what it looks like, okay? So…
105 00:10:32.650 ⇒ 00:10:36.979 Sezim Zhenishbekova: There’s only user ID that we can… I can see, but…
106 00:10:38.270 ⇒ 00:10:38.990 Henry Zhao: Basque.
107 00:10:39.780 ⇒ 00:10:41.550 Henry Zhao: Bask order completed.
108 00:10:43.250 ⇒ 00:10:45.520 Henry Zhao: Order completed. See if we have it there.
109 00:10:47.560 ⇒ 00:10:48.460 Henry Zhao: Damn it.
110 00:10:48.790 ⇒ 00:10:49.680 Henry Zhao: Oh.
111 00:10:50.400 ⇒ 00:10:54.640 Henry Zhao: Okay. Where… resource?
112 00:10:55.210 ⇒ 00:10:56.400 Henry Zhao: The refill thing?
113 00:10:57.350 ⇒ 00:10:58.080 Henry Zhao: Ugh.
114 00:10:59.510 ⇒ 00:11:02.139 Henry Zhao: Order completed. Alright, I just gotta figure out how we join in.
115 00:11:11.690 ⇒ 00:11:17.629 Henry Zhao: What do they mean by refills? Do they mean just, like, in one treatment? Or, like, is it redoing it? Does that count as a refill?
116 00:11:18.940 ⇒ 00:11:23.440 Sezim Zhenishbekova: The refill is, like, just subscription that, like, they get…
117 00:11:24.270 ⇒ 00:11:30.540 Sezim Zhenishbekova: I think it’s part of the subscription, like, once we count the 6 months, Bruce Hill is just…
118 00:11:31.250 ⇒ 00:11:36.050 Sezim Zhenishbekova: Following that monthly subscription plan, I think, right?
119 00:11:36.330 ⇒ 00:11:40.729 Sezim Zhenishbekova: I mean, that would make sense, but I don’t… let me check the events, you know.
120 00:11:46.670 ⇒ 00:11:49.670 Henry Zhao: Meanwhile, while you do that, I’ll do this.
121 00:12:34.860 ⇒ 00:12:35.780 Henry Zhao: What the…
122 00:12:41.430 ⇒ 00:12:42.809 Henry Zhao: Alright, we have some now.
123 00:12:43.740 ⇒ 00:12:46.559 Sezim Zhenishbekova: So, yeah, it feels like,
124 00:12:46.820 ⇒ 00:13:02.150 Sezim Zhenishbekova: I found this post-purchase subscription-based model, where, they basically view dashboard, view messenger, normalize as portal section viewed, where the properties as section, customer ID, active subscriptions, and step name, and when the.
125 00:13:02.150 ⇒ 00:13:04.320 Henry Zhao: Can you share your screen, just show me what you’re seeing.
126 00:13:04.450 ⇒ 00:13:05.040 Sezim Zhenishbekova: Yes.
127 00:13:05.040 ⇒ 00:13:06.279 Henry Zhao: I’m not following the…
128 00:13:06.470 ⇒ 00:13:07.110 Sezim Zhenishbekova: Yeah.
129 00:13:07.850 ⇒ 00:13:09.230 Sezim Zhenishbekova: So request…
130 00:13:13.220 ⇒ 00:13:26.479 Sezim Zhenishbekova: So, I just found this one. It’s, like, Eden Event Shima, where basically they have… they check it by active subscriptions, and once the subscription is active, then they check… they have, like.
131 00:13:26.850 ⇒ 00:13:29.730 Sezim Zhenishbekova: Refilled status, effective date, and past.
132 00:13:29.730 ⇒ 00:13:33.050 Henry Zhao: This is good, you’re gonna need this, actually, for the last question he had.
133 00:13:33.440 ⇒ 00:13:34.100 Sezim Zhenishbekova: Yeah.
134 00:13:34.100 ⇒ 00:13:35.359 Henry Zhao: Yeah, so we’ll need that.
135 00:13:35.360 ⇒ 00:13:40.489 Sezim Zhenishbekova: And then, and then, then, if the plane changed, they have all new plane change, reason, change, initiator.
136 00:13:40.490 ⇒ 00:13:43.039 Henry Zhao: Can you scroll up so I can see what this is referring to?
137 00:13:43.590 ⇒ 00:13:44.910 Sezim Zhenishbekova: Hmm, sorry.
138 00:13:48.430 ⇒ 00:13:53.730 Sezim Zhenishbekova: I think I found it from one of the documents when I was scrolling around, because I tried to understand the BigQuery.
139 00:13:53.970 ⇒ 00:13:55.089 Sezim Zhenishbekova: And how it works?
140 00:13:55.090 ⇒ 00:14:00.189 Henry Zhao: This is a mixed panel, so it’s not quite related, so let’s,
141 00:14:00.670 ⇒ 00:14:02.259 Henry Zhao: Keep that in mind, okay? So…
142 00:14:02.260 ⇒ 00:14:02.700 Sezim Zhenishbekova: Oh, gross.
143 00:14:02.700 ⇒ 00:14:06.150 Henry Zhao: Good, so we found this guy, and it looks like there’s only one order.
144 00:14:06.800 ⇒ 00:14:08.959 Henry Zhao: So we’re gonna need the treatment, treatment one.
145 00:14:09.550 ⇒ 00:14:13.689 Henry Zhao: With the treatment updates to see, look at refills. Because, like, there’s only one order here.
146 00:14:13.690 ⇒ 00:14:14.870 Sezim Zhenishbekova: Okay, I’m gonna…
147 00:14:14.870 ⇒ 00:14:15.550 Henry Zhao: -
148 00:14:16.010 ⇒ 00:14:18.450 Henry Zhao: And let me see if it is refill order.
149 00:14:18.720 ⇒ 00:14:25.590 Sezim Zhenishbekova: Okay. Could you share your screen, too? Yeah, are you seeing it? No, I canceled my screen sharing.
150 00:14:25.590 ⇒ 00:14:26.690 Henry Zhao: Okay, I’ll share it again.
151 00:14:32.220 ⇒ 00:14:33.879 Henry Zhao: This Zoom bar’s really annoying me.
152 00:14:35.210 ⇒ 00:14:39.370 Henry Zhao: Alright, order date 6-29.
153 00:14:45.040 ⇒ 00:14:45.840 Henry Zhao: I’ll bet.
154 00:14:59.080 ⇒ 00:15:01.080 Henry Zhao: Okay, so here we have treatment updated.
155 00:15:01.240 ⇒ 00:15:09.179 Henry Zhao: Look at remaining refills, 11… That’s so annoying.
156 00:15:09.720 ⇒ 00:15:11.210 Henry Zhao: Okay,
157 00:15:14.950 ⇒ 00:15:18.330 Henry Zhao: There’s not, like, a quick way to pull this, so let me… let me think through this.
158 00:15:23.600 ⇒ 00:15:28.730 Henry Zhao: Based on new and existing customers combined with expected, Projected renewal rates.
159 00:15:34.040 ⇒ 00:15:35.969 Henry Zhao: Reach refill number.
160 00:15:42.470 ⇒ 00:15:45.730 Sezim Zhenishbekova: Still new items, the customer online is expected, but…
161 00:15:46.290 ⇒ 00:15:51.699 Henry Zhao: Trying to think of it from a business point of view. So let’s look at is refill order.
162 00:15:51.890 ⇒ 00:15:55.309 Henry Zhao: And O dot is refilled. Okay, perfect.
163 00:16:02.160 ⇒ 00:16:04.409 Henry Zhao: Okay, so let’s grab one of these…
164 00:16:07.080 ⇒ 00:16:11.530 Henry Zhao: Remove any refills 10, perfect, alright. So we’ll grab this guy. Oh, shoot.
165 00:16:13.990 ⇒ 00:16:16.790 Henry Zhao: dot order number… Spread in the beginning.
166 00:16:28.400 ⇒ 00:16:29.830 Henry Zhao: Right, greater than 1.
167 00:16:30.290 ⇒ 00:16:33.939 Henry Zhao: Okay, we’ll grab this sample and take a look at what it looks like.
168 00:16:37.550 ⇒ 00:16:39.709 Henry Zhao: Why? So annoying.
169 00:16:44.150 ⇒ 00:16:45.570 Henry Zhao: Alright, here we go, here we have one.
170 00:16:45.780 ⇒ 00:16:49.269 Henry Zhao: So actually, we have this AI, so we need to actually look at customer ID.
171 00:16:50.010 ⇒ 00:16:52.989 Henry Zhao: Cool, it’s 511, because we need to look at all their orders, right?
172 00:16:53.360 ⇒ 00:16:54.080 Sezim Zhenishbekova: Yeah.
173 00:16:54.080 ⇒ 00:16:56.139 Henry Zhao: And let’s put, order number in the beginning.
174 00:16:57.600 ⇒ 00:17:01.379 Henry Zhao: Order by order dates.
175 00:17:02.210 ⇒ 00:17:08.320 Henry Zhao: There we go, alright, here we go. So now that this person has 5, 361-6655 is our last one.
176 00:17:10.240 ⇒ 00:17:12.170 Henry Zhao: Here we have a user order counter.
177 00:17:12.430 ⇒ 00:17:13.020 Henry Zhao: Okay, so…
178 00:17:13.020 ⇒ 00:17:15.020 Sezim Zhenishbekova: It had 5 cones.
179 00:17:15.020 ⇒ 00:17:17.030 Henry Zhao: It’s two levels. So we have the order summary.
180 00:17:17.060 ⇒ 00:17:21.289 Sezim Zhenishbekova: Where your order summary’s gonna have the order counter, like 1, 2, 3, 4, 5.
181 00:17:21.430 ⇒ 00:17:24.319 Sezim Zhenishbekova: Then each order is gonna have treatment updated.
182 00:17:24.609 ⇒ 00:17:27.769 Sezim Zhenishbekova: Yeah. And I’m just gonna give you the number of refills left, okay?
183 00:17:27.770 ⇒ 00:17:39.909 Henry Zhao: So you’re gonna get something… and I’ll give you the plan, whether it’s quarterly or monthly, because that’s gonna define… because you’re gonna have to separate out quarterly and monthly, right? So quarterly, you’re gonna have to add 3 months for each refill, and then monthly, you’re gonna have to add 1 month for each refill.
184 00:17:40.400 ⇒ 00:17:48.510 Sezim Zhenishbekova: And then for each month’s… so, for example, let’s take this user, right? How many… when they subscribed.
185 00:17:48.650 ⇒ 00:17:52.750 Sezim Zhenishbekova: How many 6 mean 6 months, or does it mean…
186 00:17:52.750 ⇒ 00:17:56.269 Henry Zhao: Let’s take a look. So you… let’s take a look. So this person, Courtney Smith… oh, I’m sorry.
187 00:17:57.740 ⇒ 00:18:00.229 Henry Zhao: Put this person, CS.
188 00:18:00.650 ⇒ 00:18:03.330 Henry Zhao: Ordered their first one.
189 00:18:04.930 ⇒ 00:18:06.400 Henry Zhao: 2024?
190 00:18:07.210 ⇒ 00:18:10.839 Henry Zhao: And then this one is also 2024. Oh, so it’s a different drug, I think.
191 00:18:12.090 ⇒ 00:18:14.690 Sezim Zhenishbekova: So basically, we need to take orders, but then…
192 00:18:14.690 ⇒ 00:18:19.550 Henry Zhao: The same orders, we need to break it into different products.
193 00:18:19.600 ⇒ 00:18:27.220 Sezim Zhenishbekova: And then have a sample product, how much… how long it takes, and usually how many subscriptions they have, refills.
194 00:18:27.580 ⇒ 00:18:32.239 Henry Zhao: Yeah, I’m gonna add in the refills. But each refill is gonna be a different row.
195 00:18:33.240 ⇒ 00:18:39.179 Henry Zhao: It’s gonna get double counted on the order level, because like I said, orders can have multiple refills, right?
196 00:18:39.180 ⇒ 00:18:39.620 Sezim Zhenishbekova: Yeah.
197 00:18:39.620 ⇒ 00:18:44.859 Henry Zhao: So I ordered 1 set of semaglutide, and it could be 6 months of treatment updates for the 6 refills.
198 00:18:45.750 ⇒ 00:18:48.750 Henry Zhao: I have 6 left, I have 5 left, I have 4 left, I have 3 left, I have 2 left.
199 00:18:48.750 ⇒ 00:18:49.620 Sezim Zhenishbekova: Yeah.
200 00:18:49.620 ⇒ 00:18:52.610 Henry Zhao: Well, it would be 54321 if it’s 6 months, right? And then 0.
201 00:18:52.980 ⇒ 00:18:58.899 Henry Zhao: Once it’s zero, you should see a new user order counter if they renew it. If not, it’s just the end, right? There’s no more for that person.
202 00:18:58.900 ⇒ 00:19:03.649 Sezim Zhenishbekova: So basically, for example, this person, she ordered, like, every two…
203 00:19:04.740 ⇒ 00:19:16.209 Sezim Zhenishbekova: like, I wonder if it’s the same drug, and if not, like, for example, we need to predict if she’s gonna place another order or not, and what happened to her, like, why she’s not coming back, and where
204 00:19:16.600 ⇒ 00:19:17.770 Sezim Zhenishbekova: Error, right?
205 00:19:18.070 ⇒ 00:19:18.920 Henry Zhao: Yeah.
206 00:19:19.590 ⇒ 00:19:24.570 Henry Zhao: How are we gonna figure out if it was cancellations, pauses, or payment failures?
207 00:19:26.020 ⇒ 00:19:29.460 Sezim Zhenishbekova: Do we track when we have payment failures somehow?
208 00:19:33.280 ⇒ 00:19:37.020 Sezim Zhenishbekova: Do you have a tracking metrics on the checkout page?
209 00:19:37.270 ⇒ 00:19:41.170 Henry Zhao: Let’s, let’s look at the orders, statuses.
210 00:19:41.410 ⇒ 00:19:46.730 Henry Zhao: Select distinct orders status from Prodbt.
211 00:19:46.900 ⇒ 00:19:49.730 Henry Zhao: parts.order summary.
212 00:19:52.060 ⇒ 00:19:54.310 Henry Zhao: Current status.
213 00:19:55.760 ⇒ 00:19:57.889 Henry Zhao: Ellie, we do, we do, we do.
214 00:19:58.300 ⇒ 00:19:58.690 Sezim Zhenishbekova: Okay.
215 00:19:58.690 ⇒ 00:20:04.970 Henry Zhao: So… So I’m gonna give you current status… I’m gonna also give you…
216 00:20:05.410 ⇒ 00:20:08.410 Henry Zhao: So, current status will tell us if they canceled it, I think.
217 00:20:08.900 ⇒ 00:20:09.900 Henry Zhao: No.
218 00:20:10.220 ⇒ 00:20:12.009 Henry Zhao: How do we know if they canceled an order?
219 00:20:13.590 ⇒ 00:20:16.340 Henry Zhao: There’s all these payment canceled, but I don’t know if those are right.
220 00:20:18.040 ⇒ 00:20:26.199 Henry Zhao: There’s payment status. There’s payment status, so here you have refunded, cancel… It’s all payment status. Refunded, canceled, and failed.
221 00:20:27.600 ⇒ 00:20:28.760 Henry Zhao: Okay?
222 00:20:29.180 ⇒ 00:20:32.569 Henry Zhao: And then if it disappears, that just means they didn’t renew.
223 00:20:33.160 ⇒ 00:20:33.930 Henry Zhao: if…
224 00:20:34.650 ⇒ 00:20:42.650 Henry Zhao: Yeah, if they ended it, so they have no refills, it’s… they ended it. If they have refills, and it’s been more than however many months.
225 00:20:43.130 ⇒ 00:20:45.449 Henry Zhao: Should have gotten a refill, that’s a pause.
226 00:20:46.190 ⇒ 00:20:46.940 Sezim Zhenishbekova: Yes.
227 00:20:47.700 ⇒ 00:20:48.420 Henry Zhao: Okay?
228 00:20:48.420 ⇒ 00:20:51.240 Sezim Zhenishbekova: Yeah. So that’s the logic that you’re gonna need to have.
229 00:20:53.610 ⇒ 00:20:54.540 Henry Zhao: Okay?
230 00:20:54.820 ⇒ 00:21:06.399 Sezim Zhenishbekova: So basically, like, for example, they got to the checkout, and then we know what… how people usually cancel their subscriptions. Like, how do we track the cancellations? Which status does it use?
231 00:21:06.400 ⇒ 00:21:08.420 Henry Zhao: Payment status, payment status canceled.
232 00:21:08.620 ⇒ 00:21:12.059 Sezim Zhenishbekova: Payment side, cancel is that. Okay, and then we have payment failures.
233 00:21:12.580 ⇒ 00:21:20.329 Sezim Zhenishbekova: pending, expired, which cannot automatically categorize for payment issues. And then, can you please again repeat the pause logic?
234 00:21:21.180 ⇒ 00:21:25.590 Henry Zhao: Okay, so I’m gonna give you, like I said, orders, and then treatments, right?
235 00:21:25.770 ⇒ 00:21:26.260 Sezim Zhenishbekova: Yes.
236 00:21:26.260 ⇒ 00:21:28.310 Henry Zhao: So, what you should be seeing is
237 00:21:28.550 ⇒ 00:21:35.500 Henry Zhao: for each order, it should go from 5 refills to 4 to 3 to 2 to 1 to 0. If it’s 0, and it’s today.
238 00:21:36.120 ⇒ 00:21:40.509 Henry Zhao: whatever, right? Because we don’t know what they’re gonna do next month, I can’t tell the future.
239 00:21:40.560 ⇒ 00:21:42.470 Sezim Zhenishbekova: It was zero last month.
240 00:21:42.800 ⇒ 00:21:48.719 Henry Zhao: or before that, and they don’t have a new order, they’ve canceled. They, like, discontinued, right? They…
241 00:21:49.190 ⇒ 00:21:52.270 Henry Zhao: they’re done with their treatments, or they’re like, I don’t want to do this anymore.
242 00:21:53.610 ⇒ 00:22:00.690 Henry Zhao: But, if it’s, like, more than a month ago, and they still have two refills, and they haven’t done their two refills, they’re paused, essentially.
243 00:22:01.510 ⇒ 00:22:02.520 Sezim Zhenishbekova: Yeah, okay.
244 00:22:03.060 ⇒ 00:22:06.280 Henry Zhao: So, like, 2 months ago, you’d want to see anyone that has
245 00:22:06.800 ⇒ 00:22:09.000 Henry Zhao: 2 refills or more. Wait, yeah.
246 00:22:10.350 ⇒ 00:22:16.399 Henry Zhao: Or do you just want to look at anyone that hasn’t updated a refill in the last month? Right?
247 00:22:16.810 ⇒ 00:22:30.079 Sezim Zhenishbekova: I would take a month’s kind of subscription, because usually… but it depends on the drug itself and the service, right? There are so many layers of services that they provide, subscription-based maintenance, some club, payments.
248 00:22:31.050 ⇒ 00:22:36.750 Sezim Zhenishbekova: But yeah, since we’re focusing on the refills specifically because of the inventory planning, I think
249 00:22:37.140 ⇒ 00:22:41.090 Sezim Zhenishbekova: It should be sufficient, yeah. Let me think about that more, like…
250 00:22:41.290 ⇒ 00:22:41.690 Henry Zhao: That’s the…
251 00:22:41.690 ⇒ 00:22:42.909 Sezim Zhenishbekova: So let’s what’s gonna… yeah.
252 00:22:42.910 ⇒ 00:22:45.329 Henry Zhao: Let’s do this query together, because…
253 00:22:45.580 ⇒ 00:22:47.439 Henry Zhao: We’re gonna need to think through this together on what.
254 00:22:47.440 ⇒ 00:22:48.740 Sezim Zhenishbekova: Yes. You were able to…
255 00:22:48.950 ⇒ 00:22:51.509 Henry Zhao: Okay, so order summary, we need for sure.
256 00:22:52.380 ⇒ 00:22:57.959 Henry Zhao: on o dot order number equals… S. order number.
257 00:22:58.770 ⇒ 00:23:00.400 Henry Zhao: Then we do the treated updates.
258 00:23:00.600 ⇒ 00:23:02.880 Henry Zhao: We don’t need these filters anymore.
259 00:23:05.140 ⇒ 00:23:13.439 Henry Zhao: So now what we need is… you need the month, Of the order… oh no.
260 00:23:13.900 ⇒ 00:23:17.550 Henry Zhao: You probably need both of them, actually. So you need the… when the order was placed.
261 00:23:19.110 ⇒ 00:23:23.110 Henry Zhao: We can just do s dot… Order dates…
262 00:23:25.910 ⇒ 00:23:28.140 Henry Zhao: Actually, I need to coalesce this.
263 00:23:29.710 ⇒ 00:23:31.940 Henry Zhao: O dot date.
264 00:23:32.760 ⇒ 00:23:33.980 Henry Zhao: Does that work?
265 00:23:37.080 ⇒ 00:23:38.600 Henry Zhao: Is it also order date?
266 00:23:39.420 ⇒ 00:23:42.680 Henry Zhao: Anyhow… Is it timestamped?
267 00:23:55.960 ⇒ 00:23:58.040 Henry Zhao: As order month.
268 00:24:01.880 ⇒ 00:24:03.250 Henry Zhao: Okay?
269 00:24:04.090 ⇒ 00:24:06.060 Henry Zhao: MaxT.com.
270 00:24:06.270 ⇒ 00:24:09.310 Henry Zhao: Remaining refills? I am.
271 00:24:11.960 ⇒ 00:24:13.769 Henry Zhao: There’s total refills.
272 00:24:20.200 ⇒ 00:24:24.539 Henry Zhao: Then we need the month… the t dot timestamp.
273 00:24:25.420 ⇒ 00:24:28.600 Henry Zhao: As update month.
274 00:24:29.860 ⇒ 00:24:41.819 Henry Zhao: Then we need… Probably need the… O dot… standardized product name?
275 00:24:43.250 ⇒ 00:24:45.200 Henry Zhao: Do you want to look at it by product?
276 00:24:50.150 ⇒ 00:24:51.330 Henry Zhao: That might be it.
277 00:24:53.240 ⇒ 00:24:55.509 Henry Zhao: Standardized product name not found.
278 00:24:56.910 ⇒ 00:24:58.200 Henry Zhao: Good to have Jesus.
279 00:24:59.740 ⇒ 00:25:00.859 Henry Zhao: Do you have?
280 00:25:02.950 ⇒ 00:25:07.140 Henry Zhao: What?
281 00:25:08.550 ⇒ 00:25:11.949 Sezim Zhenishbekova: Do we have any next billing date type of stuff?
282 00:25:12.680 ⇒ 00:25:14.129 Henry Zhao: Do we have anyone?
283 00:25:14.130 ⇒ 00:25:17.999 Sezim Zhenishbekova: The date that’s next billing date, that’s automatically generated.
284 00:25:18.110 ⇒ 00:25:19.110 Henry Zhao: Yeah,
285 00:25:21.220 ⇒ 00:25:24.010 Henry Zhao: But we need to do something that will allow you to aggregate.
286 00:25:24.340 ⇒ 00:25:25.050 Sezim Zhenishbekova: Yeah.
287 00:25:26.100 ⇒ 00:25:28.610 Henry Zhao: Is this here? Standardized product name, what are you talking about?
288 00:25:29.010 ⇒ 00:25:30.290 Henry Zhao: It’s not a nice product name.
289 00:25:30.480 ⇒ 00:25:32.090 Sezim Zhenishbekova: What we’re talking about it doesn’t exist.
290 00:25:32.090 ⇒ 00:25:33.490 Henry Zhao: Standardized products.
291 00:25:33.490 ⇒ 00:25:36.529 Sezim Zhenishbekova: I think it’s RT, standardized…
292 00:25:36.530 ⇒ 00:25:39.869 Henry Zhao: It’s S, it’s S. Okay.
293 00:25:39.870 ⇒ 00:25:40.980 Sezim Zhenishbekova: Changing the product.
294 00:25:40.980 ⇒ 00:25:42.560 Henry Zhao: name.
295 00:25:48.470 ⇒ 00:25:49.330 Sezim Zhenishbekova: Product name.
296 00:25:49.470 ⇒ 00:25:56.629 Henry Zhao: We need… ugh, this is… this is not as easy as it is.
297 00:25:56.630 ⇒ 00:25:57.580 Sezim Zhenishbekova: Exactly.
298 00:25:57.580 ⇒ 00:26:00.749 Henry Zhao: Do you need to know for each person if they’re a new or a refill order?
299 00:26:05.550 ⇒ 00:26:08.550 Henry Zhao: No, so you already have the dataset 1, which tells you that.
300 00:26:10.200 ⇒ 00:26:12.190 Henry Zhao: For each standardized group, how many…
301 00:26:12.470 ⇒ 00:26:19.930 Henry Zhao: Okay, let’s re-walk through his thing, okay? It’s based on new and existing customers combined with expected projected renewal rates for each refill number.
302 00:26:22.960 ⇒ 00:26:30.160 Henry Zhao: Well, actually, I got it. We only need to know how many refill orders do we forecast for next month. So I only need to tell you, actually, this month’s
303 00:26:31.130 ⇒ 00:26:33.480 Henry Zhao: Up for renewals. Up for refills.
304 00:26:34.660 ⇒ 00:26:36.409 Henry Zhao: The historical doesn’t actually matter.
305 00:26:37.630 ⇒ 00:26:38.759 Henry Zhao: But based on.
306 00:26:38.760 ⇒ 00:26:42.470 Sezim Zhenishbekova: On the historical, we predict the renewal rates, no?
307 00:26:46.510 ⇒ 00:26:48.289 Henry Zhao: Yeah, but you already have that from the dataset 1.
308 00:26:48.740 ⇒ 00:26:49.420 Sezim Zhenishbekova: Yeah.
309 00:26:49.880 ⇒ 00:26:53.420 Henry Zhao: So you have that already, and so right now, I just need to give you this month’s
310 00:26:53.660 ⇒ 00:26:56.369 Henry Zhao: Active people and paused people.
311 00:26:57.120 ⇒ 00:27:01.729 Henry Zhao: And we need some data on how many people that go back, come back from being paused.
312 00:27:02.160 ⇒ 00:27:16.090 Sezim Zhenishbekova: Yeah, so, and then there are so many different, like, layers of data sets, so I need to be able to flag every transaction to be able to combine it and separate it, because I cannot just rely on the product-based aggregation.
313 00:27:16.090 ⇒ 00:27:23.040 Henry Zhao: I think we got it then. Yeah, I think I got it then. I just need to grab you all the ones that are up for refills next month.
314 00:27:23.210 ⇒ 00:27:28.990 Henry Zhao: And you need to also add people that are going to be retained next month from current treatments.
315 00:27:29.480 ⇒ 00:27:32.690 Henry Zhao: Okay? So, like, refills next month.
316 00:27:33.850 ⇒ 00:27:43.250 Sezim Zhenishbekova: Next billing date, I need specifically next billing date to calculate how many days are there, and if it met that next billing date, or they just paused it, or they dropped.
317 00:27:43.250 ⇒ 00:27:45.610 Henry Zhao: And then, based on that scenario.
318 00:27:45.760 ⇒ 00:27:49.890 Henry Zhao: No, no, so here’s the plan, right? So, it says, how many refill orders do we forecast for next month, right?
319 00:27:49.890 ⇒ 00:27:50.240 Sezim Zhenishbekova: Yep.
320 00:27:50.240 ⇒ 00:27:55.610 Henry Zhao: So, here’s the formula, right? Refill orders… orders for next month.
321 00:27:56.060 ⇒ 00:27:56.990 Henry Zhao: equal.
322 00:27:58.400 ⇒ 00:27:59.900 Henry Zhao: Equals, right?
323 00:27:59.900 ⇒ 00:28:01.320 Sezim Zhenishbekova: Refill.
324 00:28:02.840 ⇒ 00:28:05.229 Henry Zhao: Orders up for refill next month.
325 00:28:06.280 ⇒ 00:28:07.530 Henry Zhao: that GET.
326 00:28:07.920 ⇒ 00:28:08.990 Henry Zhao: fulfills.
327 00:28:10.100 ⇒ 00:28:16.980 Henry Zhao: minus… Orders up for refill next month that get canceled or paused.
328 00:28:17.960 ⇒ 00:28:18.670 Henry Zhao: Correct?
329 00:28:19.930 ⇒ 00:28:26.180 Henry Zhao: Plus, new orders from people Out of refills this month.
330 00:28:27.110 ⇒ 00:28:34.689 Henry Zhao: Okay? Plus… People that unpause.
331 00:28:34.890 ⇒ 00:28:42.839 Henry Zhao: That’s essentially it. There’s no other way that there could come in a refill order. So as long as I give you these four, you can calculate it.
332 00:28:43.440 ⇒ 00:28:45.179 Henry Zhao: And you can also answer these other questions.
333 00:28:45.420 ⇒ 00:28:45.950 Henry Zhao: So you can’.
334 00:28:45.950 ⇒ 00:28:46.270 Sezim Zhenishbekova: Okay.
335 00:28:46.270 ⇒ 00:28:50.060 Henry Zhao: You can answer cancellations from here. You can answer pauses from here.
336 00:28:50.210 ⇒ 00:28:53.130 Henry Zhao: You can answer payment failures from the payment status?
337 00:28:53.860 ⇒ 00:28:55.360 Henry Zhao: And… that’s it.
338 00:28:55.740 ⇒ 00:29:00.239 Henry Zhao: This is all we need. How can we get the expected renewal rate?
339 00:29:00.770 ⇒ 00:29:02.020 Sezim Zhenishbekova: per drug.
340 00:29:03.430 ⇒ 00:29:08.290 Sezim Zhenishbekova: Is there, like, specific logic? How many times, like, every drug has, like.
341 00:29:08.900 ⇒ 00:29:13.519 Sezim Zhenishbekova: How often they renew? Will I get that information based on the drug?
342 00:29:14.010 ⇒ 00:29:15.280 Henry Zhao: I can pull that for you.
343 00:29:16.170 ⇒ 00:29:17.729 Sezim Zhenishbekova: Because, yeah, so I would need that.
344 00:29:17.730 ⇒ 00:29:25.479 Henry Zhao: Yeah, let’s start with this. So let’s start with that. So I think that’s gonna be… let me keep this query, let’s grab this.
345 00:29:30.550 ⇒ 00:29:32.180 Henry Zhao: So we need payment status.
346 00:29:53.760 ⇒ 00:29:57.579 Henry Zhao: So we need the treatment updated, where…
347 00:30:07.880 ⇒ 00:30:10.230 Henry Zhao: Next follow-up date, next shocking date.
348 00:30:20.470 ⇒ 00:30:26.980 Henry Zhao: getting refills… I think it’s just next shipment dates.
349 00:30:27.200 ⇒ 00:30:31.129 Henry Zhao: But why is there no next shipping date for this guy, who has 11 refills?
350 00:30:33.540 ⇒ 00:30:34.439 Henry Zhao: I’m gonna think.
351 00:30:36.340 ⇒ 00:30:38.440 Henry Zhao: I’m gonna go to this.
352 00:31:03.020 ⇒ 00:31:05.049 Henry Zhao: Probably here. Okay, so…
353 00:31:15.660 ⇒ 00:31:17.280 Henry Zhao: Okay, so I can look…
354 00:31:17.450 ⇒ 00:31:22.230 Henry Zhao: At the treatment update event, where the event property equals status complete or active memory refill is not zero.
355 00:31:22.520 ⇒ 00:31:25.590 Henry Zhao: From there, I can reference the next payment date or next shipment date.
356 00:31:25.590 ⇒ 00:31:27.619 Sezim Zhenishbekova: Oh, yeah, this is what I…
357 00:31:28.470 ⇒ 00:31:31.060 Henry Zhao: There updates the next refill, that doesn’t interrupt these comms.
358 00:31:34.360 ⇒ 00:31:38.400 Henry Zhao: I just don’t get how people can create a pharmacy software and not have next refill date.
359 00:31:42.040 ⇒ 00:31:49.420 Sezim Zhenishbekova: How they… yeah, like, I mean, they have to have it in the system, right? Because they get the alarm to send the notifications for the user to remind.
360 00:31:49.770 ⇒ 00:31:50.550 Sezim Zhenishbekova: Phil.
361 00:31:52.020 ⇒ 00:31:53.809 Henry Zhao: Where did Demolati get next refill date?
362 00:32:00.050 ⇒ 00:32:02.289 Henry Zhao: It looks like it is next shipping date, so…
363 00:32:03.790 ⇒ 00:32:04.190 Sezim Zhenishbekova: Hmm.
364 00:32:04.520 ⇒ 00:32:05.560 Henry Zhao: Yeah, excellent.
365 00:32:07.440 ⇒ 00:32:10.940 Henry Zhao: So, maybe that’s because these are in the past.
366 00:32:11.340 ⇒ 00:32:12.660 Henry Zhao: So…
367 00:32:18.950 ⇒ 00:32:21.210 Henry Zhao: Timestamp? Month?
368 00:32:21.890 ⇒ 00:32:26.839 Henry Zhao: There’s… It’s this month’s.
369 00:32:28.920 ⇒ 00:32:30.339 Henry Zhao: See if that fixes it.
370 00:32:31.560 ⇒ 00:32:32.980 Henry Zhao: It looks like a bigger sample.
371 00:32:35.680 ⇒ 00:32:38.400 Henry Zhao: So this one has a next shipping date of this month, though.
372 00:32:43.550 ⇒ 00:32:45.099 Henry Zhao: Oh, because these are yearly.
373 00:32:45.470 ⇒ 00:32:49.630 Henry Zhao: So, oh, it does make sense. Look, the monthlies have one. Not these two, though.
374 00:32:50.010 ⇒ 00:32:53.700 Henry Zhao: Is it because they’re out of refills? Yes, this one’s out of refills, I don’t know about this one.
375 00:32:56.250 ⇒ 00:33:02.440 Sezim Zhenishbekova: Payment schedule monthly. So it’s automatic, if they schedule the payments, subscriptions are automatic.
376 00:33:02.440 ⇒ 00:33:07.310 Henry Zhao: Well, why is this one not? Maybe the product name is, like, Toast 3, so there should be one.
377 00:33:08.940 ⇒ 00:33:12.279 Henry Zhao: Did they cancel it or something? Oh, it’s pending, that’s why.
378 00:33:13.100 ⇒ 00:33:16.200 Henry Zhao: That’s why. Okay, so if it’s active, it does have a refill.
379 00:33:16.640 ⇒ 00:33:18.509 Henry Zhao: If it’s active and has a refill.
380 00:33:18.890 ⇒ 00:33:21.080 Henry Zhao: There is a next shipping date.
381 00:33:29.700 ⇒ 00:33:33.019 Henry Zhao: We can actually figure this out in a methodical way. Okay.
382 00:33:40.360 ⇒ 00:33:42.310 Henry Zhao: Next, shipping date.
383 00:33:43.430 ⇒ 00:33:47.979 Henry Zhao: Then no, else, yes, end as refill data.
384 00:33:51.300 ⇒ 00:33:54.080 Henry Zhao: And then let’s do payment schedule.
385 00:33:54.990 ⇒ 00:33:56.959 Henry Zhao: Counts. Alright, let’s see what that looks like.
386 00:33:58.520 ⇒ 00:34:05.800 Henry Zhao: And the other things I’m going to want to look at are… But probably the status?
387 00:34:06.880 ⇒ 00:34:07.739 Henry Zhao: Oh, there’s a shipping.
388 00:34:08.190 ⇒ 00:34:10.029 Henry Zhao: Alright, status and remaining refills.
389 00:34:12.210 ⇒ 00:34:12.900 Sezim Zhenishbekova: Okay.
390 00:34:15.159 ⇒ 00:34:19.339 Henry Zhao: So… I gotta order it, also.
391 00:34:21.209 ⇒ 00:34:22.679 Henry Zhao: 2, 1, okay.
392 00:34:24.739 ⇒ 00:34:29.069 Henry Zhao: So if it’s monthly… It doesn’t tell me anything, though.
393 00:34:33.259 ⇒ 00:34:36.019 Henry Zhao: Even quarterly, they have refill data sometimes.
394 00:34:38.269 ⇒ 00:34:40.729 Henry Zhao: Let’s look at status now.
395 00:34:40.939 ⇒ 00:34:41.609 Henry Zhao: Nothing.
396 00:34:43.979 ⇒ 00:34:46.159 Henry Zhao: So it’s not the… it’s not the status.
397 00:34:48.959 ⇒ 00:34:53.399 Henry Zhao: Yeah, if it’s abandoned, there’s no refill data. If it’s active, there pretty much is, see? So that’s good.
398 00:34:54.480 ⇒ 00:34:57.230 Henry Zhao: Canceled, it doesn’t. Completed.
399 00:34:57.540 ⇒ 00:35:01.020 Henry Zhao: It does… Here’s paused.
400 00:35:01.860 ⇒ 00:35:05.009 Henry Zhao: Sachi, you have paused in here already, and your payment error.
401 00:35:05.010 ⇒ 00:35:09.230 Sezim Zhenishbekova: What does yes, no mean? Like, paused, no, yes, oh, okay.
402 00:35:09.880 ⇒ 00:35:12.850 Henry Zhao: So you have it here already, actually.
403 00:35:13.570 ⇒ 00:35:17.069 Henry Zhao: So you have it here, so I don’t need payment status, I just need the status here.
404 00:35:17.770 ⇒ 00:35:20.130 Henry Zhao: You already have paused, pending…
405 00:35:22.370 ⇒ 00:35:25.719 Henry Zhao: So this is actually pretty easy. I think I can pull this for you now, and we can just send it to him.
406 00:35:26.420 ⇒ 00:35:27.710 Henry Zhao: Yeah.
407 00:35:29.210 ⇒ 00:35:35.710 Henry Zhao: So, all we need is, we need the, next shipping date, To be next month.
408 00:35:39.110 ⇒ 00:35:40.239 Henry Zhao: I think that’s it.
409 00:35:43.020 ⇒ 00:35:50.400 Sezim Zhenishbekova: And then, what does the refill data status mean? Like, there is, like, same two statuses, but with no and yes one.
410 00:35:51.130 ⇒ 00:35:55.510 Henry Zhao: That’s… I put that, so… I’m just saying, do we have data on their next refill date?
411 00:35:55.980 ⇒ 00:35:56.439 Sezim Zhenishbekova: So was it bad?
412 00:35:56.440 ⇒ 00:35:58.419 Henry Zhao: And then there’s obviously gonna be no refill date.
413 00:35:58.420 ⇒ 00:35:59.080 Sezim Zhenishbekova: Yeah.
414 00:35:59.080 ⇒ 00:36:05.129 Henry Zhao: And if it’s active, then most of them do, right? So 16,000 of them have a next refill date. So we can use this next refill date.
415 00:36:11.180 ⇒ 00:36:15.359 Henry Zhao: I might pull this query and just have the team kind of… Vet it real quick?
416 00:36:16.680 ⇒ 00:36:20.270 Henry Zhao: And then in the meantime, you can work on the last piece, which is the…
417 00:36:20.530 ⇒ 00:36:23.129 Henry Zhao: New orders from people out of refills this month.
418 00:36:25.240 ⇒ 00:36:27.190 Henry Zhao: If you can just do that with the dataset 1.
419 00:36:27.610 ⇒ 00:36:35.359 Sezim Zhenishbekova: So the forecasting for the refills rate, yes, for coming months, with active… with active users. Okay.
420 00:36:36.130 ⇒ 00:36:39.609 Henry Zhao: So I will pull for you… what I need to pull for you is the people that have…
421 00:36:41.430 ⇒ 00:36:47.290 Henry Zhao: No, I think for now, you can just give me, like, the renewal rate by month, by drug.
422 00:36:47.980 ⇒ 00:36:48.820 Sezim Zhenishbekova: Okay.
423 00:36:48.820 ⇒ 00:36:54.330 Henry Zhao: So out of each… each… for each drug, when they run out, what is the likelihood that they renew next month?
424 00:36:54.890 ⇒ 00:36:55.600 Sezim Zhenishbekova: Okay.
425 00:36:56.160 ⇒ 00:36:57.510 Sezim Zhenishbekova: Okay, just get that…
426 00:36:57.510 ⇒ 00:37:00.600 Henry Zhao: get that rate, okay? Okay. So it’ll just be a list of drugs, and then a percentage.
427 00:37:00.890 ⇒ 00:37:01.760 Sezim Zhenishbekova: Okay, okay.
428 00:37:01.760 ⇒ 00:37:04.009 Henry Zhao: We can figure that out, that would be great.
429 00:37:06.080 ⇒ 00:37:16.710 Henry Zhao: If you can’t figure that out, let me know. I don’t know if you can figure that out with the data I gave you. In the meantime, let’s work on this… let’s finish this query together, and have the team kind of, vet it real quick.
430 00:37:17.040 ⇒ 00:37:18.409 Sezim Zhenishbekova: Sounds good.
431 00:37:18.410 ⇒ 00:37:20.450 Henry Zhao: So we need next shipping date, and then…
432 00:37:22.460 ⇒ 00:37:25.490 Henry Zhao: I think it’s literally just status, count.
433 00:37:30.840 ⇒ 00:37:33.319 Henry Zhao: And what I also need here is…
434 00:37:35.470 ⇒ 00:37:38.760 Henry Zhao: I need to get the last data point for each…
435 00:37:42.520 ⇒ 00:37:44.279 Henry Zhao: T dot Rn equals 1.
436 00:37:48.490 ⇒ 00:37:56.559 Henry Zhao: Row number… over… partition by user… is it user ID?
437 00:37:56.970 ⇒ 00:37:58.030 Henry Zhao: Customer ID.
438 00:38:02.960 ⇒ 00:38:09.480 Henry Zhao: Customer ID, treatment ID, order by timestamp descending, or N.
439 00:38:11.440 ⇒ 00:38:15.909 Henry Zhao: Here is user ID, damn it! Why can’t companies be more sus…
440 00:38:16.130 ⇒ 00:38:16.520 Sezim Zhenishbekova: assistant.
441 00:38:16.520 ⇒ 00:38:20.780 Henry Zhao: Alright, let me just double-check from a previous project I worked on, make sure that’s the right logic.
442 00:38:22.810 ⇒ 00:38:23.890 Henry Zhao: But there are…
443 00:38:37.750 ⇒ 00:38:38.899 Henry Zhao: Marketing, I think?
444 00:38:42.030 ⇒ 00:38:46.319 Henry Zhao: Partition by user ID, treatment group, treatment group also. Okay.
445 00:38:48.740 ⇒ 00:38:51.630 Henry Zhao: Jeez, so many windows. Alright, I’m treating them good. Awesome!
446 00:38:52.290 ⇒ 00:38:53.320 Henry Zhao: That should be right.
447 00:38:59.630 ⇒ 00:39:00.370 Henry Zhao: What.
448 00:39:01.700 ⇒ 00:39:02.670 Sezim Zhenishbekova: I shouldn’t…
449 00:39:03.320 ⇒ 00:39:04.310 Henry Zhao: What means, I mean…
450 00:39:07.590 ⇒ 00:39:08.770 Henry Zhao: Shoot.
451 00:39:10.090 ⇒ 00:39:11.040 Henry Zhao: Dang it.
452 00:39:11.950 ⇒ 00:39:12.800 Henry Zhao: Ugh.
453 00:39:13.130 ⇒ 00:39:14.050 Henry Zhao: Forget it.
454 00:39:14.370 ⇒ 00:39:16.129 Henry Zhao: We got a treatment group.
455 00:39:16.360 ⇒ 00:39:19.099 Henry Zhao: Good enough for now. We can’t be too picky on this.
456 00:39:20.570 ⇒ 00:39:23.839 Henry Zhao: So I think we’re gonna have this… but why are there this many for completed?
457 00:39:24.630 ⇒ 00:39:25.779 Henry Zhao: I understand.
458 00:39:41.940 ⇒ 00:39:47.659 Henry Zhao: Can I pull this list for you, and can you go into BASC and just kind of figure out, like, by looking at some of these.
459 00:39:47.930 ⇒ 00:39:49.700 Sezim Zhenishbekova: What is BASC?
460 00:39:49.700 ⇒ 00:39:52.580 Henry Zhao: Do you have access to Basque? So, it’s their actual…
461 00:39:57.130 ⇒ 00:40:00.360 Sezim Zhenishbekova: Let me check my 1Password, if I have it.
462 00:40:00.360 ⇒ 00:40:03.260 Henry Zhao: Yeah, go to bass, app.bass… oh, actually, I can just give you the login.
463 00:40:07.290 ⇒ 00:40:08.140 Sezim Zhenishbekova: mask.
464 00:40:08.890 ⇒ 00:40:13.569 Sezim Zhenishbekova: Oh yeah, I got… I have a day… yeah, I have access to mysta.
465 00:40:13.570 ⇒ 00:40:16.039 Henry Zhao: Yeah, you can use the baskettryeen.com one.
466 00:40:16.340 ⇒ 00:40:16.960 Sezim Zhenishbekova: Yeah.
467 00:40:17.010 ⇒ 00:40:18.319 Henry Zhao: I’ll just share it with you in case you don’t.
468 00:40:18.320 ⇒ 00:40:19.370 Sezim Zhenishbekova: I’m gonna know.
469 00:40:21.320 ⇒ 00:40:24.440 Henry Zhao: You can go and just search the order numbers of the customer IDs and see, like.
470 00:40:24.650 ⇒ 00:40:27.569 Henry Zhao: If those should be up for renewal refills next month.
471 00:40:29.110 ⇒ 00:40:30.730 Henry Zhao: This is a good exercise for you.
472 00:40:31.470 ⇒ 00:40:32.370 Sezim Zhenishbekova: Yeah.
473 00:40:35.390 ⇒ 00:40:38.480 Sezim Zhenishbekova: Okay, oh yeah, I got in. Thank you.
474 00:40:38.760 ⇒ 00:40:39.900 Henry Zhao: Oh, you already got in? Okay.
475 00:40:43.280 ⇒ 00:40:44.730 Henry Zhao: Just in case, I’ll leave it here.
476 00:40:46.520 ⇒ 00:40:47.720 Henry Zhao: Okay.
477 00:40:48.910 ⇒ 00:40:50.950 Henry Zhao: I’m gonna pull all this for you.
478 00:40:51.940 ⇒ 00:40:54.659 Sezim Zhenishbekova: And what do you want me to do, Ms. Basque, specifically?
479 00:40:56.630 ⇒ 00:41:01.820 Henry Zhao: Check if the ones that are completed… so this is gonna be the data that you should be looking at.
480 00:41:01.930 ⇒ 00:41:07.569 Henry Zhao: So I’ll send you these 11,000. So I’m saying that these 11,000 could potentially be…
481 00:41:07.990 ⇒ 00:41:12.590 Henry Zhao: The refill is next month, but I’m not sure about the ones that have status completed.
482 00:41:14.920 ⇒ 00:41:16.689 Sezim Zhenishbekova: But you’ll include, like, status…
483 00:41:16.690 ⇒ 00:41:18.179 Henry Zhao: Why are they getting a refill, you know?
484 00:41:19.040 ⇒ 00:41:22.940 Sezim Zhenishbekova: Yeah, but there were so many completed ones, so I need to figure out what this completed means.
485 00:41:22.940 ⇒ 00:41:26.899 Henry Zhao: Yeah, so you might want to look at a sample, or you might see some other,
486 00:41:27.340 ⇒ 00:41:28.550 Henry Zhao: Why is this too big?
487 00:41:28.710 ⇒ 00:41:33.470 Henry Zhao: Let me just give you the relevant data. I think you just need…
488 00:41:34.980 ⇒ 00:41:37.760 Henry Zhao: you need… I’ll give you an ID.
489 00:41:38.240 ⇒ 00:41:40.230 Henry Zhao: I’ll give you next payment date.
490 00:41:40.510 ⇒ 00:41:45.179 Henry Zhao: Next shipping date… I’ll give you the payment schedule…
491 00:41:48.210 ⇒ 00:41:50.020 Henry Zhao: I’ll give you the product name.
492 00:41:50.170 ⇒ 00:41:55.070 Sezim Zhenishbekova: Okay, the query is not that hard, it’s just a matter of getting understood. Okay, so…
493 00:41:55.070 ⇒ 00:41:58.530 Henry Zhao: Yeah, I’ll give you remaining refills, which is weird why remaining refills would be zero.
494 00:41:58.850 ⇒ 00:41:59.670 Sezim Zhenishbekova: That is…
495 00:42:00.000 ⇒ 00:42:01.060 Henry Zhao: Shipping…
496 00:42:01.060 ⇒ 00:42:02.770 Sezim Zhenishbekova: Schedule, yeah.
497 00:42:03.330 ⇒ 00:42:08.299 Henry Zhao: Status… Why is there a next shipping date if… alright, timestamp…
498 00:42:08.490 ⇒ 00:42:13.360 Henry Zhao: Treatment ID, treatment type, anything that might be useful to you, for you to look at.
499 00:42:13.360 ⇒ 00:42:14.270 Sezim Zhenishbekova: User ID?
500 00:42:14.270 ⇒ 00:42:18.919 Henry Zhao: Okay… Patient email… We don’t really need their name or anything.
501 00:42:18.920 ⇒ 00:42:19.660 Sezim Zhenishbekova: -
502 00:42:19.660 ⇒ 00:42:21.739 Henry Zhao: Russ, I don’t think, are even real data points. Alright, cool.
503 00:42:21.740 ⇒ 00:42:22.500 Sezim Zhenishbekova: Okay.
504 00:42:26.950 ⇒ 00:42:31.499 Henry Zhao: So I’ll let you take a look at these 11,000 and just kind of see if you can figure something out from there.
505 00:42:32.480 ⇒ 00:42:34.039 Henry Zhao: Why only 3,000 rows?
506 00:42:34.710 ⇒ 00:42:38.460 Henry Zhao: It’s getting less. I put in less data, now it’s giving me less rows.
507 00:42:39.160 ⇒ 00:42:41.960 Sezim Zhenishbekova: I… is it the payment plan, or…
508 00:42:41.960 ⇒ 00:42:43.139 Henry Zhao: to you as a CSV.
509 00:42:43.760 ⇒ 00:42:45.890 Sezim Zhenishbekova: Yeah, yeah, Cesar can work.
510 00:42:51.090 ⇒ 00:42:53.309 Henry Zhao: When it’s done, I’ll send it to you, let me see.
511 00:42:53.310 ⇒ 00:42:54.030 Sezim Zhenishbekova: Yeah.
512 00:42:54.030 ⇒ 00:42:58.080 Henry Zhao: Just wanna see how many rows… oh, good. Exported, let me send it to you.
513 00:42:58.310 ⇒ 00:43:01.050 Henry Zhao: Make sure there’s over 11,000 rows, please.
514 00:43:02.510 ⇒ 00:43:19.779 Sezim Zhenishbekova: Yes. And then I was wondering, like, is there any data file that has, like, all the function names, all the data files, when one is recorded? I just shared with you a link with this meta… with the panel, mixed panel. Is there something for the entire project?
515 00:43:20.240 ⇒ 00:43:23.150 Henry Zhao: Yeah, but it’s not complete, but I’ll send you what we have.
516 00:43:26.500 ⇒ 00:43:34.069 Sezim Zhenishbekova: Because I think that would be very useful to understand the logic of just the status, right? Because I’m sitting and guessing what does my dissatus mean, but…
517 00:43:34.220 ⇒ 00:43:36.249 Sezim Zhenishbekova: Having a document that explains event.
518 00:43:36.250 ⇒ 00:43:38.949 Henry Zhao: I don’t think people… I don’t think anybody’s sure of that, so I’m gonna.
519 00:43:38.950 ⇒ 00:43:40.649 Sezim Zhenishbekova: It might be…
520 00:43:40.650 ⇒ 00:43:46.710 Henry Zhao: confusing to look at this document, because there’s just so much. It might be more confusing to look at this document than to just ask the specific question you have.
521 00:43:46.910 ⇒ 00:43:48.099 Sezim Zhenishbekova: Okay, okay, okay.
522 00:43:48.100 ⇒ 00:43:55.029 Henry Zhao: There’s just so much stuff you’re gonna be looking all over, and it still won’t make sense. I’ve still… I’ve been here for, like, 5 months, and it’s still… not everything makes sense to me.
523 00:43:55.290 ⇒ 00:43:57.060 Sezim Zhenishbekova: Yeah, okay, got it.
524 00:43:57.060 ⇒ 00:43:58.979 Henry Zhao: Just as a… yeah, just as a heads up.
525 00:43:58.980 ⇒ 00:44:04.930 Sezim Zhenishbekova: Yeah, just figuring out on the fly, based on the request, I guess. Okay.
526 00:44:04.930 ⇒ 00:44:09.039 Henry Zhao: Yeah, but I try to give you just the information that you need, so you don’t have to, like, be too overwhelmed.
527 00:44:09.100 ⇒ 00:44:13.050 Sezim Zhenishbekova: But just ask questions, like, ask me questions, asking client Eden.
528 00:44:13.050 ⇒ 00:44:17.930 Henry Zhao: Because like I said, Robert and Udam like seeing that, and we’ll answer them as quickly as possible.
529 00:44:18.110 ⇒ 00:44:18.670 Sezim Zhenishbekova: Yeah, amazing.
530 00:44:18.670 ⇒ 00:44:20.800 Henry Zhao: Like, trying to go through all this documentation.
531 00:44:20.990 ⇒ 00:44:22.749 Sezim Zhenishbekova: Okay, sounds good. Thank you.
532 00:44:22.750 ⇒ 00:44:23.940 Henry Zhao: Thank you, talk to you soon. Bye.
533 00:44:23.940 ⇒ 00:44:24.700 Sezim Zhenishbekova: Talk to you soon, bye.