Meeting Title: Placeholder: Operational Margin & COGS Discussion Date: 2026-02-05 Meeting participants: Fireflies.ai Notetaker Katie, Katie, Amber Lin, Brad Messersmith
WEBVTT
1 00:04:19.230 ⇒ 00:04:20.410 Amber Lin: Hi there!
2 00:04:23.150 ⇒ 00:04:26.610 Brad Messersmith: Hey, sorry, hold on, I’m having audio difficulties, one sec.
3 00:04:41.670 ⇒ 00:04:42.729 Brad Messersmith: Can you hear me?
4 00:04:43.550 ⇒ 00:04:45.020 Amber Lin: I hear you.
5 00:04:46.770 ⇒ 00:04:49.039 Brad Messersmith: You guys are really quiet for some reason in my…
6 00:05:10.340 ⇒ 00:05:12.919 Brad Messersmith: Okay, let me try this one more time. Can you guys hear me?
7 00:05:13.270 ⇒ 00:05:14.329 Amber Lin: Yeah, I can hear you.
8 00:05:14.650 ⇒ 00:05:17.170 Brad Messersmith: Now I can hear you, too. Great. Sorry about that.
9 00:05:17.170 ⇒ 00:05:18.569 Amber Lin: Awesome. All good.
10 00:05:18.570 ⇒ 00:05:19.549 Brad Messersmith: How’s it going?
11 00:05:19.550 ⇒ 00:05:30.429 Amber Lin: Pretty good, long time no see! I know we talked when I was still PMing this project, but I’m now back, I’m doing analyst work, so it’s a little bit different.
12 00:05:30.780 ⇒ 00:05:33.130 Brad Messersmith: Yeah, that’s awesome. I’m excited for you.
13 00:05:33.130 ⇒ 00:05:49.069 Amber Lin: Oh, yeah. I’m doing… so the stuff I’m currently doing, I’m trying to calculate the operational margin, which means… let me share a screen so it’s more straightforward. So, I have this sheet that someone shared with me a while back.
14 00:05:49.070 ⇒ 00:05:58.790 Amber Lin: So this is, like, the breakdown of COGS. We have, per product and per different membership plans. We have the doctor fee, credit card fee.
15 00:05:58.800 ⇒ 00:06:16.460 Amber Lin: BAS fees, and we have COGS. So that’s the current breakdown I have. But I know finance calculates margin purely based on transactions, so, money in, money out, like, refunds and stuff, but I was thinking of doing it
16 00:06:16.770 ⇒ 00:06:26.450 Amber Lin: Throughout the operational journey, so we can see, oh, this step had a lot of losses, that step, had a lot of,
17 00:06:26.570 ⇒ 00:06:44.379 Amber Lin: was pretty good. So, like, currently, my understanding operate… how orders come in is a little bit limited. Like, from… I was looking at BASC, and I’m looking at the data. I currently only know that, okay, the order is completed.
18 00:06:44.500 ⇒ 00:06:47.249 Amber Lin: comes in, goes to BASC,
19 00:06:47.810 ⇒ 00:06:53.290 Amber Lin: We, when they do pay,
20 00:06:53.770 ⇒ 00:07:00.329 Amber Lin: And then there’s a payment status, and I guess that’s where we apply the credit card fees.
21 00:07:00.470 ⇒ 00:07:09.019 Amber Lin: And then I kind of know that we apply the doctor fees before it is sent to pharmacy, because it goes to doctor first.
22 00:07:09.320 ⇒ 00:07:13.300 Amber Lin: And then when it’s at the pharmacy, like, I don’t know…
23 00:07:13.760 ⇒ 00:07:26.790 Amber Lin: what’s the, like, the pharmacy cost, or if we have anything on shipment cost? I also don’t know, like, how the product cost is broken down, because there’s… on this one, there’s…
24 00:07:26.830 ⇒ 00:07:44.320 Amber Lin: So, for example, there’s different milliliter combinations, but then the COGS here is different, so it’s not… say, this is 4 millimeters, this is 1, it’s not a direct, like, times 4, so that’s my question there.
25 00:07:44.320 ⇒ 00:07:47.379 Brad Messersmith: complicated very quickly. I can show you what we have, because I think…
26 00:07:47.810 ⇒ 00:07:55.960 Brad Messersmith: For sure, this table… I think that table’s being used still, if I’m not mistaken, by Jonah and the group, but it’s definitely not…
27 00:07:56.130 ⇒ 00:08:00.360 Brad Messersmith: very accurate, based on what I know, so…
28 00:08:00.360 ⇒ 00:08:01.280 Amber Lin: Okay.
29 00:08:01.280 ⇒ 00:08:02.200 Brad Messersmith: Let me just share…
30 00:08:02.200 ⇒ 00:08:09.050 Amber Lin: I want to use what you guys know, because Jonah uses it for finance, and, like, I want to calculate operationally.
31 00:08:09.380 ⇒ 00:08:11.240 Brad Messersmith: Right. So…
32 00:08:12.380 ⇒ 00:08:21.599 Brad Messersmith: Sorry, hold on, let me get oriented here. There’s a couple things, okay? So the first… first one being… how do we move this? Okay, first one being the…
33 00:08:22.140 ⇒ 00:08:25.559 Brad Messersmith: The drive that we have, we have a shared drive, which we can…
34 00:08:26.000 ⇒ 00:08:29.000 Brad Messersmith: Access to or pull information from.
35 00:08:29.310 ⇒ 00:08:29.760 Amber Lin: Okay.
36 00:08:29.760 ⇒ 00:08:32.980 Brad Messersmith: has a folder called Pharmacy Info and Pricing.
37 00:08:33.720 ⇒ 00:08:38.299 Brad Messersmith: And in that is an individual folder for each of our pharmacies.
38 00:08:38.650 ⇒ 00:08:39.710 Amber Lin: Oh, okay.
39 00:08:39.710 ⇒ 00:08:49.130 Brad Messersmith: And in there exists for all of our current and most of our partners, pricing sheets.
40 00:08:49.530 ⇒ 00:08:55.549 Brad Messersmith: So, an example of what that pricing sheet looks like, I kind of figured we’d go down this path, so I have some open…
41 00:08:55.740 ⇒ 00:09:08.199 Brad Messersmith: This is Pharmacy Hub. You can see they’re… they fulfill from a 503 pharmacy, so that’s another added note. But basically, we have this broken down by the different pricing.
42 00:09:09.120 ⇒ 00:09:25.449 Brad Messersmith: each pharmacy has different kind of buckets, right? So, like, some of them don’t charge us for supplies, but charge us more for shipping, and some of them don’t actually have a dispense fee, it’s just the medication and then the total price. So there’s, like, different kind of levels of detail that we have for each of them.
43 00:09:26.720 ⇒ 00:09:35.019 Brad Messersmith: the Emerald pricing is another example. It’s just medication, shipping, and the price. There’s no supplies, none of that, it’s all built into these other prices somewhere.
44 00:09:35.990 ⇒ 00:09:37.480 Amber Lin: I see, okay.
45 00:09:37.820 ⇒ 00:09:52.099 Brad Messersmith: Now, it’s important to note, like you said, each of these SKUs is a different strength, and I think what most people don’t realize, even within Eden, within the organization, is how complicated
46 00:09:52.190 ⇒ 00:10:01.709 Brad Messersmith: this gets, because these are the prices we pay by pharmacy SKUs. So you can imagine, this is a vial, right? There’s 5 different vial types.
47 00:10:01.990 ⇒ 00:10:13.250 Brad Messersmith: Now, each of these 5 vial types, Are associated with… many different… actual protocols.
48 00:10:13.630 ⇒ 00:10:15.400 Brad Messersmith: That go through our system.
49 00:10:16.210 ⇒ 00:10:27.930 Brad Messersmith: So, if you look at… this is Optio, they’re one of our higher volume pharmacies. This is… this is what we supply through them. This is one of the few we actually have this level of detail for.
50 00:10:28.570 ⇒ 00:10:39.520 Brad Messersmith: But you can see there’s an alternate, there’s a biweekly, and a rapid titration schedule that we offer. Each one of those has different, desired strengths and frequencies in here.
51 00:10:39.870 ⇒ 00:10:45.619 Amber Lin: But they only associate to the SKUs like this, it’s not one-to-one.
52 00:10:45.620 ⇒ 00:10:46.630 Brad Messersmith: So, for 4…
53 00:10:47.690 ⇒ 00:10:59.440 Brad Messersmith: We’re actually delivering 6 months’ worth of… right? So it might be month 1, month 2, month 3, month 4, and then month 5 and 6 are the same as month 4. In some cases.
54 00:10:59.440 ⇒ 00:11:00.740 Amber Lin: Oh, wow.
55 00:11:01.210 ⇒ 00:11:15.940 Brad Messersmith: Yeah, it might be month 1, it might be month 2 and 3 is this SKU. It might be month 4 is the SKU. Yeah, so hold on, I don’t know. Nobody knows this. Like, I hope… I’ve been trying to say this as loud as I can to anyone that’ll listen, like.
56 00:11:16.580 ⇒ 00:11:24.020 Brad Messersmith: We’ve got a real problem here. We need to have a big push to pull all of our data together in a way that makes sense.
57 00:11:24.500 ⇒ 00:11:32.590 Brad Messersmith: Basically, like, you can imagine what we have currently, because I’ve negotiated the pricing with all of the different pharmacies, most of them.
58 00:11:32.870 ⇒ 00:11:47.529 Brad Messersmith: like, we have some form of this… this information, but not all of it. Like, Emerald is an example where I’m trying to put this together, but I’m doing this myself. It’s not, like, in the system, it’s not, like, this is what’s going into the system when we integrate with them.
59 00:11:48.050 ⇒ 00:11:57.290 Brad Messersmith: So, the layer of complexities here get pretty complex pretty quick. That being said…
60 00:11:57.780 ⇒ 00:12:08.300 Brad Messersmith: I don’t know if I remembered to open this one, so let me grab it. It’s all in this same… same folder, but if you look at the, comparison that I’ve done…
61 00:12:09.740 ⇒ 00:12:16.220 Brad Messersmith: Because I had to do some… some level of, where the heck did I put that? Is there a pricing?
62 00:12:23.830 ⇒ 00:12:25.460 Brad Messersmith: Okay, that was not smart.
63 00:12:26.080 ⇒ 00:12:28.930 Brad Messersmith: I buried that. Okay, so then…
64 00:12:31.000 ⇒ 00:12:45.670 Brad Messersmith: This one, I think, is the most recent, yeah. So what I’ve done then, basically, is I’ve gone through, you know, Semma 1, 2, 3, 4, and 5. As best I can, these volumes are completely made up, Amber. I can’t even pretend to guess.
65 00:12:45.930 ⇒ 00:12:49.939 Brad Messersmith: What volume actually goes for each of these months.
66 00:12:50.190 ⇒ 00:12:50.680 Amber Lin: Okay.
67 00:12:50.680 ⇒ 00:13:05.030 Brad Messersmith: Why, right? Because it’s by SKU, it’s by different… like, I have no way of connecting those dots right now. I’m kind of working with Robert and, DeMelade, I think, is maybe helping him, trying to get some baseline info I’ll go through in just a second with you.
68 00:13:05.810 ⇒ 00:13:12.080 Brad Messersmith: But essentially, I’m just making an assumption that the volume for month 1 is not the same as month 6, right?
69 00:13:12.880 ⇒ 00:13:24.790 Brad Messersmith: Probably not. And then these are the prices for the different SKU levels for each of the different pharmacies, right? So Optio actually only offers 5, Perfect offers 6, and…
70 00:13:24.950 ⇒ 00:13:25.960 Brad Messersmith: I think…
71 00:13:26.980 ⇒ 00:13:33.260 Amber Lin: then I’m rolling that into, like, an average kind of COGS, right? Like, I’m multiplying the volumes.
72 00:13:33.830 ⇒ 00:13:35.900 Brad Messersmith: And then that’s the comparison I’m doing.
73 00:13:36.180 ⇒ 00:13:42.140 Brad Messersmith: So you can sort of see DIRX, they’re a new one I gotta put in here that we’re just working through the pricing with.
74 00:13:42.270 ⇒ 00:13:47.219 Brad Messersmith: But this is a negotiation. We started with this price, this is what we need to propose.
75 00:13:47.680 ⇒ 00:13:53.699 Brad Messersmith: So, like, trying to keep this all organized and straight, to me, I think this is the best thing we have.
76 00:13:53.860 ⇒ 00:13:57.099 Brad Messersmith: To replace the document that you’re looking at.
77 00:13:57.810 ⇒ 00:14:01.870 Brad Messersmith: As far as, like, the pricing across different pharmacies?
78 00:14:02.680 ⇒ 00:14:13.779 Brad Messersmith: But again, that being said, it doesn’t all really super connect. It’s probably a better version than what Jonah’s doing. Jonah, I think, actually just goes, okay, I’m gonna average out the whole thing.
79 00:14:14.040 ⇒ 00:14:20.389 Brad Messersmith: And SEMA, the average COGS is, like, $85 or something. I don’t actually know, it might be lower than that.
80 00:14:20.610 ⇒ 00:14:29.719 Brad Messersmith: But, you know, let’s say it’s 50, 60 bucks, or something like that. I think he’s just rolling that up and then going, all of our semi-volume is $60.
81 00:14:29.860 ⇒ 00:14:35.230 Brad Messersmith: And so, even his numbers, like the P&L, I don’t think is super exact.
82 00:14:35.670 ⇒ 00:14:39.320 Brad Messersmith: Yeah, challenge, I think, that you’re kind of gonna face, right?
83 00:14:39.320 ⇒ 00:14:53.540 Amber Lin: Yeah, I can’t… I can’t really use what he’s using, because it’s mostly… he’s using the transaction revenue, and then, say, like, an estimated COGS, which is some… some of them are based on percentages, some of them are calculated. So…
84 00:14:53.780 ⇒ 00:15:12.550 Amber Lin: I know one of your objectives is to lower… I remember it lowered COGS by 30%. Like, what angles are you trying to attack that by? So I can think of, like, in my analysis, what I can come up to support you, or if there’s anything I know our team has been doing.
85 00:15:12.900 ⇒ 00:15:25.050 Brad Messersmith: Well, I think it overlaps with what I’ve been asking Robert, so I’ll show you that in just a second, because it goes back… to me, it goes back to forecasting. Like, if you can help us build a tool that allows me to…
86 00:15:25.390 ⇒ 00:15:35.999 Brad Messersmith: tell… so I’ll have to have inputs, right? But, like, we can adjust, and I’ll show you in a second how I’m doing it in Excel, but if I can tell you what the volume is going to be for the next several months.
87 00:15:36.250 ⇒ 00:15:49.900 Brad Messersmith: Somehow, we should be able to break that down back into a COGS number, and really have a clear idea of what our financials should look like in a forecast. That’s my goal. And the only way right now to improve COGS that I know is if you look at.
88 00:15:50.380 ⇒ 00:15:58.889 Brad Messersmith: table, our volume… The majority of our volume right now is between Optio and Absolute.
89 00:15:59.900 ⇒ 00:16:03.510 Brad Messersmith: And a lot of Optio’s volume is this Terzepatide.
90 00:16:03.720 ⇒ 00:16:04.630 Brad Messersmith: Volume.
91 00:16:05.290 ⇒ 00:16:11.460 Brad Messersmith: Some of it’s sema, but you can sort of see that if I move from absolute SEMA,
92 00:16:12.700 ⇒ 00:16:21.110 Brad Messersmith: averaging 40. Every order… I saved 28 bucks.
93 00:16:21.530 ⇒ 00:16:36.909 Brad Messersmith: So that right there is a huge COGS improvement. Just going in order from Absolute is now going to Eden Pharmacy. So that’s step one. That’s basically our biggest push, is trying to get as many orders as we can to Eden Pharmacy, because we… I see. Lowest cost.
94 00:16:37.050 ⇒ 00:16:42.779 Brad Messersmith: Now, step 2 has been, basically, over the past 3 to 6 months-ish.
95 00:16:43.170 ⇒ 00:16:46.890 Brad Messersmith: We’ve been renegotiating all of the prices that you see here.
96 00:16:46.890 ⇒ 00:16:47.690 Amber Lin: Okay.
97 00:16:47.690 ⇒ 00:16:56.670 Brad Messersmith: So, you can even see Optio new pricing. Like, I proposed this, this hasn’t been put in place, but Optio’s pricing before this was, like, $115. Oh, okay.
98 00:16:56.670 ⇒ 00:17:10.949 Brad Messersmith: almost 120-something. So, we saved 15 bucks per order with Optio by doing that negotiation. So, essentially, between negotiating better pricing and moving volume to the places that have better prices, that’s how we’re improving the COGS.
99 00:17:11.560 ⇒ 00:17:27.300 Amber Lin: I see. Do you also look at, say, improvements in operations, such as, like, less refunds, less drop-offs, less cancellations, or abandoned orders? Do you also look at that, or is that a different team?
100 00:17:27.640 ⇒ 00:17:40.639 Brad Messersmith: We definitely have a lot of involvement. Why don’t you… I should have introduced Katie in the beginning. Katie is our, pharmacy… our telehealth ops manager. She’s over pharmacy and med, but she definitely handles a lot of this kind of pharmacy stuff.
101 00:17:40.640 ⇒ 00:17:49.499 Brad Messersmith: And I believe our team is definitely at least involved in the tracking and hears a lot about these kind of chargebacks and whatnot, so what do you have to say about that part, Katie?
102 00:17:50.460 ⇒ 00:17:54.000 Katie: Sorry, I was typing up a message to Mahala, can you ask that one more time, please?
103 00:17:54.420 ⇒ 00:18:00.920 Brad Messersmith: Oh, she’s just asking, basically, how carefully do we track things like chargebacks and some of the other costs, right?
104 00:18:00.920 ⇒ 00:18:02.000 Katie: Oh, okay.
105 00:18:02.150 ⇒ 00:18:02.889 Brad Messersmith: the pharmacy.
106 00:18:02.890 ⇒ 00:18:03.740 Katie: Yeah.
107 00:18:04.260 ⇒ 00:18:28.329 Katie: Yeah, so there’s a whole… there’s a whole disputes team, and they track the disputes on their side, so all the chargebacks are being tracked, and then refunds, there’s a refund tracker that all of the MX agents and even my team uses, to get approval, and also that’s how they track them. It’s some sort of workflow, so MX leads have access to that information, it is tracked, but it’s just not through our channels.
108 00:18:29.110 ⇒ 00:18:36.620 Amber Lin: I see. I was actually just on BAS on the orders page, and there’s a lot of… Can I share screen, actually?
109 00:18:37.920 ⇒ 00:18:38.490 Brad Messersmith: Yep.
110 00:18:38.910 ⇒ 00:18:41.210 Amber Lin: Great. So…
111 00:18:41.940 ⇒ 00:18:53.609 Amber Lin: this one, I was looking at this page, so there’s stuff that’s at, okay, payment status, some of them get canceled, expired, and then there’s also, say, order status.
112 00:18:53.950 ⇒ 00:18:57.189 Amber Lin: And, like, how do you use.
113 00:18:57.190 ⇒ 00:18:57.910 Brad Messersmith: Yeah, yeah.
114 00:18:57.910 ⇒ 00:18:58.290 Amber Lin: healed.
115 00:18:58.290 ⇒ 00:19:00.100 Brad Messersmith: We gotta show you, actually.
116 00:19:00.100 ⇒ 00:19:14.090 Amber Lin: Yeah, and, like, I don’t really know where things end, like, I kind of know, like, visit status ends somewhere, and then we start looking at order status, but they use same fields, so I got really confused.
117 00:19:14.650 ⇒ 00:19:30.789 Brad Messersmith: Yeah, let me, let me show you what I’m doing, but the big ask that I’ve made to Robert, and really it was more Henry that I was working with before, was if we can get a webhook or somehow connect it directly to that order export data, we’re home free, almost, on a lot of this stuff.
118 00:19:30.950 ⇒ 00:19:39.849 Brad Messersmith: There is some info that’s missing that’s not quite perfect. Hopefully you can see this, but this is the order export, basically, from that same page you were just on.
119 00:19:40.740 ⇒ 00:19:47.290 Brad Messersmith: what I’m doing right now to forecast this stuff is I’m taking all of our orders out of that export.
120 00:19:47.400 ⇒ 00:19:52.910 Brad Messersmith: And dumping it into this file, so you can see it’s 325,000 line items.
121 00:19:53.350 ⇒ 00:20:07.289 Brad Messersmith: And this is all of our orders, basically, for the entire last 12 months, or dating back to the beginning of last year for now, since it’s early, but what I’m doing then is summarizing that into simple tables like this, by pharmacy and by product.
122 00:20:07.960 ⇒ 00:20:08.710 Amber Lin: Okay.
123 00:20:09.220 ⇒ 00:20:14.589 Brad Messersmith: And the way I’m getting it to the, simple product is just a VLOOKUP like this.
124 00:20:16.060 ⇒ 00:20:16.720 Brad Messersmith: See.
125 00:20:16.720 ⇒ 00:20:17.910 Amber Lin: I mean, okay.
126 00:20:17.910 ⇒ 00:20:31.790 Brad Messersmith: Okay. You know, manually the product IDs to a simple product ID. This is probably already done on your guys’ end somewhere. I would imagine somebody has done something like this, but we can help if need be.
127 00:20:32.010 ⇒ 00:20:40.130 Brad Messersmith: I only want to call attention to… if you look carefully, some of these do have SKU information that show the month.
128 00:20:40.130 ⇒ 00:20:41.010 Amber Lin: Yeah.
129 00:20:41.660 ⇒ 00:20:45.120 Brad Messersmith: So, one of the things that we’re missing from this, if you look.
130 00:20:45.310 ⇒ 00:20:53.070 Brad Messersmith: We don’t have, like, a vial size… we don’t have any way, really, to tell us where… how this connects to the pricing sheets I was just showing you.
131 00:20:53.920 ⇒ 00:20:59.210 Brad Messersmith: You can see this pharmacy Variance column does have some information on the strengths
132 00:20:59.570 ⇒ 00:21:07.599 Brad Messersmith: So somehow we might be able to extract that and do the same kind of VLOOKUP sort of thing, or create a table, you know, behind in the data, but…
133 00:21:08.630 ⇒ 00:21:23.160 Brad Messersmith: Essentially, what we need to do is get to a place where we can get this type of information broken down by state, pharmacy, etc, etc. And Henry started to build that, but the tables that we have in Tableau don’t contain all the… it’s, like, missing.
134 00:21:23.160 ⇒ 00:21:37.559 Amber Lin: Okay. I think when I was looking, because I didn’t have access to this, I was looking in our database, I think our orders table do have file size, and I think it does have, like.
135 00:21:38.980 ⇒ 00:21:56.949 Amber Lin: the more broken-down product IDs and more higher-level product IDs, so let me take a look there. I might be able to, say, export something, or give you a screenshot of what it looks like. Then you can know, like, oh, this team could look at this, because we already have file size in there, so let me…
136 00:21:57.140 ⇒ 00:22:04.779 Brad Messersmith: That would be huge. Yeah, that’d be a huge improvement, because honestly, if we can… and I think it’s a little bit what, even surf.
137 00:22:05.040 ⇒ 00:22:05.710 Amber Lin: Yes, bro.
138 00:22:06.070 ⇒ 00:22:09.670 Brad Messersmith: and the OS kind of team are doing, too, is…
139 00:22:09.670 ⇒ 00:22:10.340 Amber Lin: doing it.
140 00:22:10.520 ⇒ 00:22:14.089 Brad Messersmith: they’re trying to extract this same kind of information out of BASC.
141 00:22:14.430 ⇒ 00:22:28.939 Brad Messersmith: Right. So, it’s a way, way easier for us to go in and say, okay, these are the SKUs associated with this info that’s already coming out of the system. Right now, we’re, like, creating that data from scratch. You can see there’s, like, empty table, like…
142 00:22:28.940 ⇒ 00:22:47.400 Brad Messersmith: So what Surf and those guys are trying to do is extract all of this with the file side, all of that info, and then from there, we can recreate the pricing, the sale price, the COGS, all the BASC fees, all of that stuff can be, like, by SKU, calculated perfectly in the new system.
143 00:22:47.870 ⇒ 00:22:48.690 Amber Lin: Gotcha.
144 00:22:48.690 ⇒ 00:22:53.589 Brad Messersmith: This same initiative is needed across 3 or 4 different data kind of projects.
145 00:22:53.590 ⇒ 00:23:04.610 Amber Lin: Yeah, okay. That makes sense. I think the only question remaining I have, it probably is for Katie. I do want to go over the BASC, different statuses.
146 00:23:04.610 ⇒ 00:23:17.299 Amber Lin: Because, like, for example, if an order payment didn’t go through, I don’t think I should include it in the margin revenue calculation. So I kind of want to ask Katie about the different statuses on the BASP order page.
147 00:23:17.650 ⇒ 00:23:27.880 Brad Messersmith: Yeah, just 10 seconds, and then I’ll give it to Katie, but just so you know what I’m doing with that table, then, is pulling it into a different Excel spreadsheet and forecasting by pharmacy.
148 00:23:28.230 ⇒ 00:23:37.259 Brad Messersmith: So, essentially, if we can connect this data to a realistic, like, average COGS by pharmacy and by product.
149 00:23:38.270 ⇒ 00:23:42.699 Brad Messersmith: Then we can also forecast COGS and sale price and all of that same stuff.
150 00:23:42.870 ⇒ 00:23:45.269 Brad Messersmith: Sooner, potentially.
151 00:23:52.180 ⇒ 00:23:53.070 Amber Lin: Cool, awesome.
152 00:23:53.070 ⇒ 00:23:58.660 Brad Messersmith: Sorry, I just wanted to show you that before I stop sharing, because I think that part of it is relevant.
153 00:23:58.660 ⇒ 00:24:13.760 Amber Lin: Yeah, it’s really helpful. I don’t think I have access to the pharmacy folder that you were referring to. I could send you my Eden email, do you think you can share that to…
154 00:24:13.760 ⇒ 00:24:14.400 Brad Messersmith: Sure.
155 00:24:14.660 ⇒ 00:24:17.780 Amber Lin: Okay, let me go grab that.
156 00:24:18.260 ⇒ 00:24:27.309 Amber Lin: So, I think the… the… The account is just data at triaden.com, so it’s just the data…
157 00:24:29.000 ⇒ 00:24:32.200 Amber Lin: So let me send it over in the chat.
158 00:24:32.770 ⇒ 00:24:41.400 Amber Lin: Yeah. So if you can share it to that email, then I can go look at, your calculations, the different formulas you’re using.
159 00:24:41.860 ⇒ 00:24:43.340 Brad Messersmith: Yeah, okay.
160 00:24:43.810 ⇒ 00:24:47.680 Brad Messersmith: Yeah, in that same folder I just shared with you then, it’s called…
161 00:24:48.450 ⇒ 00:24:56.939 Brad Messersmith: There’s some data in there you can play around with. The most recent ones are called… are named appropriately, so it should be obvious, but if you have questions, let me know
162 00:24:57.320 ⇒ 00:25:02.090 Brad Messersmith: Keep in mind, those files are too big to work in, Google Drive.
163 00:25:02.090 ⇒ 00:25:04.850 Amber Lin: Yeah, I realize there’s a lot of rows.
164 00:25:04.900 ⇒ 00:25:09.300 Brad Messersmith: Yeah, so I guess download your own copy or something if you want, and play around with it, but…
165 00:25:09.430 ⇒ 00:25:13.719 Brad Messersmith: Those won’t be, like, live, so I’m making changes on my machine.
166 00:25:13.900 ⇒ 00:25:16.559 Amber Lin: I see. Yeah, sounds good.
167 00:25:16.720 ⇒ 00:25:19.110 Brad Messersmith: Anyway, sorry, thank you, Katie. Go ahead.
168 00:25:19.380 ⇒ 00:25:36.570 Katie: No worries. So, there’s two sides of the team that uses different filters. The pharmacy side uses… this is today’s report of what they did. So, she’ll filter out the date prescribed and payment date to make sure that it’s within
169 00:25:36.960 ⇒ 00:25:51.509 Katie: 3 business days ago, or older than 3 business days. And then also, the tracking number gets pulled out, but I think what you care about is this order status here. So the one that we use in this report is the cent.
170 00:25:51.580 ⇒ 00:26:06.910 Katie: And then all the way down here, we also use error and pending for the other side of the team. I believe on hold, too, but we don’t use canceled, and then the rest of these are going to be, shipped and just delivered.
171 00:26:07.820 ⇒ 00:26:15.509 Katie: Pharmacy status we do use to make sure that none of them are canceled, in the pharmacy.
172 00:26:15.860 ⇒ 00:26:20.260 Katie: And then also the warning status matters for precision specifically.
173 00:26:21.960 ⇒ 00:26:37.459 Amber Lin: Okay. So when it says… I remember in order status, there was just sent to pharmacy, but then I don’t really know what happens once it gets sent to the pharmacy. Like, is there… what tracks what happens there? Like, do we have…
174 00:26:37.460 ⇒ 00:26:38.400 Katie: status.
175 00:26:38.400 ⇒ 00:26:40.089 Amber Lin: Yeah. Oh…
176 00:26:40.090 ⇒ 00:26:56.470 Katie: So, the warning here tells me that Precision flagged the order because something’s going wrong, so that’s important to have on there. Canceled means they flagged… they canceled it at the pharmacy, and if it’s sent on our side and canceled at the pharmacy, that’s an issue.
177 00:26:56.610 ⇒ 00:27:01.400 Katie: What else? Usually there’s more…
178 00:27:01.780 ⇒ 00:27:23.739 Amber Lin: Oh, so it goes from… so first, there’s order status, which is more, like, internal stuff, and then the end of order status is, okay, it got sent to the pharmacy. And then we look at pharmacy status, like, okay, did they cancel the order? Was there issues with it? If not, and then… and then after the pharmacy status, would it… would it just go to delivery, or…
179 00:27:23.890 ⇒ 00:27:25.040 Amber Lin: Yeah.
180 00:27:25.040 ⇒ 00:27:30.879 Katie: After it’s… Pharmacy. I’m trying to find another one. Let me see if I can…
181 00:27:32.210 ⇒ 00:27:36.199 Katie: Just reset these filters real quick, and then show you that way.
182 00:27:36.340 ⇒ 00:27:42.259 Katie: Because usually there’s more order statuses. Oh, there’s also a fulfillment status now, too, apparently.
183 00:27:42.260 ⇒ 00:27:50.190 Amber Lin: Oh, okay, so I guess that’s more of, like, is it delivered, is it… so after the pharmacy status?
184 00:27:50.630 ⇒ 00:28:04.730 Katie: Yeah, and then this order status… okay, it looks like there’s… the ones that I am used to seeing aren’t there anymore, but sometimes there’s a couple random ones that just apply, and they get thrown in there, so it’s kind of hard to…
185 00:28:05.440 ⇒ 00:28:10.949 Katie: I appreciate what it’s going to be, especially now that they’re adding a bunch of stuff like this pharmacy status.
186 00:28:11.520 ⇒ 00:28:15.740 Katie: Transparently, I haven’t looked at this since they’ve updated it in a while, but.
187 00:28:15.750 ⇒ 00:28:17.950 Brad Messersmith: They updated all the time, too. Every time I.
188 00:28:17.950 ⇒ 00:28:18.500 Katie: Right.
189 00:28:18.710 ⇒ 00:28:23.370 Brad Messersmith: timesheet to dump the data, I have to adjust it, because there’s new columns and stuff.
190 00:28:23.810 ⇒ 00:28:24.680 Katie: Exactly.
191 00:28:24.680 ⇒ 00:28:28.909 Brad Messersmith: Which should be, like, a 30-second process, it never is. It always takes me, like…
192 00:28:28.910 ⇒ 00:28:29.870 Amber Lin: I’ll take that.
193 00:28:30.900 ⇒ 00:28:43.350 Katie: I guess pharmacy status is the most simple one, because it’s going to have shipped and then delivered, and then it’s also got some errors in there. But the order status and patient status, it looks like it’s relatively the same information.
194 00:28:43.430 ⇒ 00:28:46.199 Amber Lin: I see, okay. I think…
195 00:28:46.200 ⇒ 00:28:54.690 Brad Messersmith: Most of our workflows start with this same order export data, so it’s literally the same info that I was using for the forecast data and all the.
196 00:28:54.690 ⇒ 00:28:55.400 Amber Lin: Oh. Okay.
197 00:28:55.400 ⇒ 00:28:57.690 Brad Messersmith: Almost all of our team
198 00:28:57.830 ⇒ 00:29:05.330 Brad Messersmith: all of our day-to-day processes and reports, everything comes from this info, basically, so if we can get this in a webhook or something…
199 00:29:05.330 ⇒ 00:29:19.310 Amber Lin: I see. I just asked our team, we have certain fields in the webhook, we don’t have all the fields, because I was asking, hey, do we have the pharmacy status? They said, okay, we don’t have the webhook from BASC.
200 00:29:19.310 ⇒ 00:29:29.069 Amber Lin: for pharmacy status, I think I have order status and current status. Would you know, like, I can share… I can share my screen to show you
201 00:29:29.070 ⇒ 00:29:41.329 Amber Lin: what that looks like. So I… I actually summarized the fields that we have. So there’s order status, what you showed me, and I think it makes sense that we don’t use canceled orders anymore.
202 00:29:41.330 ⇒ 00:29:50.710 Amber Lin: I was wondering, like, what does abandoned and what does pending mean? Like, how should I treat those orders? If it’s abandoned, should I just exclude them?
203 00:29:51.100 ⇒ 00:30:05.139 Katie: So, abandoned, we like to keep track of how many there are, but it’s not something that we work directly in. Pendings, we do directly work in between the failed payments and also just pending orders, because the doctor needs to get to them.
204 00:30:05.210 ⇒ 00:30:19.849 Katie: Abandoned, what it means is the member didn’t fully complete checkout. They… there’s a screen where they check out, and then there’s, like, 5 or 6 questions after the fact. If they don’t finish those, then it goes into the abandoned status and remains there.
205 00:30:19.850 ⇒ 00:30:34.590 Katie: If it’s in pending, it’s either in a failed payment, because they need to update their card information, or it could be the doctor needs to respond to the member, or the member needs to respond to the doctor before the prescription moves forward.
206 00:30:34.890 ⇒ 00:30:44.619 Amber Lin: I see. Yeah, and… Oh, so that’s really helpful. Oh, okay. So, error would, would mean…
207 00:30:44.860 ⇒ 00:31:07.030 Katie: It’s complex. It’s something… errors is actually something that BASC does for us. They check for the errors every morning and work through them to correct them, because they’re typically things that they need to push through on their side, or the doctor needs to send something different, or they need to send the correct API. Something on the tech side is wrong, and Amy checks them in the morning.
208 00:31:07.030 ⇒ 00:31:11.120 Katie: And then make sure, like, 10 AM that they’re complete.
209 00:31:11.300 ⇒ 00:31:12.959 Katie: And the Basque did their side.
210 00:31:12.960 ⇒ 00:31:24.070 Amber Lin: Okay, gotcha. I think this current status looks like a combination of different Combination of different things.
211 00:31:24.180 ⇒ 00:31:30.740 Amber Lin: Like, when you look at shipped, or, say,
212 00:31:31.310 ⇒ 00:31:36.440 Amber Lin: on hold or completed? Like, would you know what these mean?
213 00:31:38.860 ⇒ 00:31:49.600 Katie: The address updated is the first time I’m ever seeing that, but the rest of it feels very, like the column… which one were we looking at?
214 00:31:50.670 ⇒ 00:32:01.130 Katie: Gosh, there’s so many now. Fulfillment status? That’s what it looks like to me over here? Probably not pharmacy status, then. It’s an order?
215 00:32:01.210 ⇒ 00:32:02.400 Amber Lin: Status…
216 00:32:02.810 ⇒ 00:32:06.690 Katie: Yeah, I don’t know, I don’t… It looks like some sort of order status.
217 00:32:06.690 ⇒ 00:32:13.249 Amber Lin: Okay. What does it mean, then, when it’s, say, on… One is on hold.
218 00:32:13.930 ⇒ 00:32:17.350 Amber Lin: Or is, say, like… shipped.
219 00:32:17.870 ⇒ 00:32:21.899 Amber Lin: Would it mean, like, shipped to the person, or shipped to the pharmacy?
220 00:32:22.210 ⇒ 00:32:27.899 Katie: shipped to the person, so that… the ship reflects that there’s tracking on it, but BASC can have
221 00:32:27.900 ⇒ 00:32:44.919 Katie: if the tracking has been delivered or not. So, shipped means it’s in transit, not delivered, and then delivered’s gonna mean it fully has made it to that member. On hold is the member went into their account and requested that their order be on hold with the pharmacy.
222 00:32:44.920 ⇒ 00:32:45.640 Amber Lin: It typically…
223 00:32:45.640 ⇒ 00:32:52.190 Katie: cancels the order with the pharmacy if BASC has that integration, but it’s not 100%.
224 00:32:54.040 ⇒ 00:32:55.730 Amber Lin: Gotcha, okay.
225 00:32:55.900 ⇒ 00:33:04.319 Katie: It could also be on hold because the treatment’s on hold, like, if they set the on-hold date before the order is sent to the pharmacy, then it’s gonna delay that order.
226 00:33:04.320 ⇒ 00:33:11.349 Amber Lin: Okay. Okay. And, I guess, so, most of it is…
227 00:33:11.450 ⇒ 00:33:15.630 Amber Lin: sent to pharmacy, so I guess, like, these orders haven’t…
228 00:33:15.680 ⇒ 00:33:34.179 Amber Lin: been sent out yet, or, like, could it have been… could it have been sent to pharmacy, and then the order has already left or delivered? Or… because then we would look at, like, the… like, the shipment or fulfillment status, right? I’m just curious, like, what is the final state?
229 00:33:34.670 ⇒ 00:33:41.960 Katie: The final state should be delivered, and seeing sent to pharmacy that high makes me think that delivered is included in that.
230 00:33:42.410 ⇒ 00:33:47.819 Amber Lin: Probably, you’re right, I don’t think… I don’t see delivered on these fields.
231 00:33:47.820 ⇒ 00:33:51.349 Katie: Let me pull up today’s number, see if we have the…
232 00:33:54.770 ⇒ 00:33:55.720 Katie: Okay.
233 00:34:03.920 ⇒ 00:34:13.670 Katie: Yeah, so for example, on our reporting, we saw that 75% of orders were shipped and delivered today, and then only 8.4 were in the sent-to-pharmacy status.
234 00:34:13.920 ⇒ 00:34:15.489 Amber Lin: Oh, okay.
235 00:34:16.310 ⇒ 00:34:20.579 Katie: And 0.2% on hold… So on and so forth.
236 00:34:30.650 ⇒ 00:34:38.509 Amber Lin: Cool, and the only, like, delivered… only way we can see delivered is in the pharmacy status, I guess.
237 00:34:38.690 ⇒ 00:34:41.350 Amber Lin: So, monthly income after…
238 00:34:41.350 ⇒ 00:34:52.089 Katie: Yeah, it’s… Basque is never perfect, and I wish I could tell you where it’s gonna be every time, but, I… I don’t really know.
239 00:34:52.090 ⇒ 00:34:52.790 Amber Lin: others.
240 00:34:52.790 ⇒ 00:35:03.979 Katie: Do you have a copy, like, a CSV of these webhooks that you’re getting? Because I could also just pick through it and see which columns resonate to me the most, and kind of point you in that direction.
241 00:35:03.980 ⇒ 00:35:15.159 Amber Lin: I don’t have it off the top of my head, but I can go ask, because I also just… I’m just newly touching this data, so, like, I was very confused.
242 00:35:15.400 ⇒ 00:35:17.320 Katie: I’m in the same boat, because I’ve…
243 00:35:17.720 ⇒ 00:35:23.540 Katie: everyone bask, and everyone keeps talking about these webhooks, and I’m like, I’ve… well, I’ve never seen it, so I don’t know how to help you.
244 00:35:23.540 ⇒ 00:35:33.620 Amber Lin: I see. So I went… when it’s order status, which one is the end state? Is it sent to pharmacy? I remember you said, like.
245 00:35:33.620 ⇒ 00:35:34.510 Katie: deleted then.
246 00:35:34.760 ⇒ 00:35:43.060 Amber Lin: Completed. What is that? What does that mean? Like, I know sent to pharmacy is being sent to pharmacy, but, like, these two…
247 00:35:43.270 ⇒ 00:35:50.110 Amber Lin: Does it also track, like, the pharmacy status and, like, delivery status?
248 00:35:50.110 ⇒ 00:35:58.360 Katie: Yes, so it’s integr… I don’t know how, but, like, it searches UPS. For example, if the tracking number’s through UPS, it’ll search that.
249 00:35:58.360 ⇒ 00:35:58.710 Amber Lin: And…
250 00:35:58.710 ⇒ 00:36:05.139 Katie: Whenever the order is delivered, it changes that status in BASC and sends a delivery email to the member as well.
251 00:36:05.640 ⇒ 00:36:18.409 Amber Lin: Mmm, okay, so, like, pending on hold, these are, like, before it’s sent to pharmacy, but then you can have, like, it’s shipped, and then it gets completed.
252 00:36:18.580 ⇒ 00:36:33.980 Katie: Yeah, I can… so the easiest way to think about it is abandoned is, like, the top layer, that’s gonna be the least touching by the member, and then after that, it’s gonna go into pending status. And once it comes out of pending, it can either go into error, doctor error, or sent to pharmacy.
253 00:36:35.220 ⇒ 00:36:37.259 Katie: And then after it’s…
254 00:36:37.660 ⇒ 00:36:38.080 Amber Lin: Partially.
255 00:36:38.080 ⇒ 00:36:45.820 Katie: The end goal for that section is for it to be sent to the pharmacy, and then after the pharmacy gets it, obviously, the end goal is for that to be delivered.
256 00:36:45.820 ⇒ 00:36:47.690 Amber Lin: Cool.
257 00:36:48.310 ⇒ 00:37:03.180 Amber Lin: Gotcha, okay. Shift, delivered… Gotcha, okay. It’s mostly, like, if there’s…
258 00:37:03.390 ⇒ 00:37:11.810 Amber Lin: shift, and then delivered, and then there’s also, like, completed. Like, do you know if… Sorry, the…
259 00:37:12.290 ⇒ 00:37:14.530 Amber Lin: Is it? All just called complete.
260 00:37:14.530 ⇒ 00:37:15.450 Katie: He did being known.
261 00:37:15.450 ⇒ 00:37:15.929 Amber Lin: I think it’s.
262 00:37:15.930 ⇒ 00:37:19.810 Katie: 46 is crazy, there’s gotta be some sort of inaccuracy there.
263 00:37:20.790 ⇒ 00:37:28.040 Amber Lin: I see. I don’t know if this tracks it… I don’t know if this tracks it correctly. I’ll go back and check again.
264 00:37:28.040 ⇒ 00:37:28.830 Katie: Okay.
265 00:37:28.830 ⇒ 00:37:29.640 Amber Lin: Yeah.
266 00:37:30.400 ⇒ 00:37:33.299 Amber Lin: Completed just means it’s fully shipped. Yeah.
267 00:37:33.300 ⇒ 00:37:45.660 Katie: I have fully seen on these BASC reports, even, where pharmacy status will say sent to pharmacy, and then the order status will be delivered, so… Like I said, it’s never perfect, and unfortunately.
268 00:37:45.660 ⇒ 00:37:46.430 Amber Lin: It is.
269 00:37:46.430 ⇒ 00:37:49.159 Katie: So, that could be a part of it.
270 00:37:49.370 ⇒ 00:37:56.950 Amber Lin: Okay, do you know… I just also saw, like, some orders… Let’s see…
271 00:37:57.340 ⇒ 00:38:09.709 Amber Lin: Where did I put it? Like, these fields of is sent to pharmacy, is shifted, is delivered. I saw that there was, like, 70% of them that was all null. Do you know under what con…
272 00:38:09.890 ⇒ 00:38:14.660 Amber Lin: Condition where, like, these fields would all be null.
273 00:38:19.500 ⇒ 00:38:26.930 Amber Lin: Would it be that the order was canceled before it got to the center pharmacy time? Like, would that be, like…
274 00:38:27.210 ⇒ 00:38:33.300 Amber Lin: because people didn’t pay, or… I guess, like, it was still…
275 00:38:33.570 ⇒ 00:38:36.609 Amber Lin: It was on hold or pending.
276 00:38:37.150 ⇒ 00:38:43.390 Katie: There should be the information there, I… honestly, that… I cannot explain that one. That is very strange to me as well.
277 00:38:43.390 ⇒ 00:38:47.930 Amber Lin: I see, that’s all… that’s alright. I think sometimes it’s just… it’s missing stuff.
278 00:38:47.930 ⇒ 00:38:49.480 Katie: is Basque.
279 00:38:49.480 ⇒ 00:38:56.269 Amber Lin: I see. I’ll also put, like, the on hold over here, right? On hold…
280 00:38:56.460 ⇒ 00:39:01.540 Amber Lin: Is… is before the… oh, I guess it could also be after they’re sent to pharmacy.
281 00:39:01.540 ⇒ 00:39:08.270 Katie: Yeah, on hold can be, unfortunately, anywhere. They don’t limit the on holds.
282 00:39:10.240 ⇒ 00:39:11.460 Amber Lin: Gotcha, okay.
283 00:39:11.870 ⇒ 00:39:18.980 Amber Lin: I think that was all my questions. I need to go back and check the data, but that was really, really helpful.
284 00:39:19.170 ⇒ 00:39:31.169 Katie: Awesome! Well, let me know if you have anything else that comes up. Happy to pick through it with you, and if you can get, like, a CSV or Google Doc of what you guys see, I would be happy to look through that, too, and let you know what I’m noticing.
285 00:39:31.400 ⇒ 00:39:34.330 Amber Lin: Also, I really appreciate it, this has been great.
286 00:39:34.560 ⇒ 00:39:36.520 Katie: Awesome. Well, have a great rest of your day!
287 00:39:36.520 ⇒ 00:39:37.919 Amber Lin: Yeah, you too, bye!