Meeting Title: Catalyst Refund and Cancellation Analysis Sync Date: 2025-11-25 Meeting participants: Casie Aviles, Henry Zhao
WEBVTT
1 00:02:39.240 ⇒ 00:02:40.760 Henry Zhao: Hi, Casey, how are you?
2 00:02:41.370 ⇒ 00:02:43.119 Casie Aviles: Hey, Henry. Yeah, doing good.
3 00:02:43.280 ⇒ 00:02:44.230 Casie Aviles: How about you?
4 00:02:44.770 ⇒ 00:02:45.839 Henry Zhao: Good, thank you.
5 00:02:47.000 ⇒ 00:02:51.020 Henry Zhao: Alright, this will be a very quick call, because it’s a pretty straightforward analysis.
6 00:02:51.620 ⇒ 00:02:52.200 Casie Aviles: Okay.
7 00:02:54.750 ⇒ 00:03:00.050 Henry Zhao: This is all the info that I have. Let me make sure you’re in this chat.
8 00:03:09.680 ⇒ 00:03:11.349 Henry Zhao: You are? Okay, perfect.
9 00:03:23.460 ⇒ 00:03:24.160 Henry Zhao: Hmm.
10 00:03:26.580 ⇒ 00:03:27.260 Henry Zhao: event.
11 00:03:34.120 ⇒ 00:03:35.990 Henry Zhao: How do I share this? Alright, anyway…
12 00:03:37.570 ⇒ 00:03:39.050 Henry Zhao: Let me just reply to you.
13 00:03:43.780 ⇒ 00:03:45.890 Casie Aviles: Okay, yeah, you can just dug me.
14 00:03:46.250 ⇒ 00:03:56.740 Henry Zhao: Okay, so we are looking to expand product listings with the offer as an affiliate. This offer was what we used for affiliates before Catalyst. So, back when Amber was working on this.
15 00:03:56.920 ⇒ 00:04:08.279 Henry Zhao: they need to know if there’s any statistical difference between refund and cancellation rate, where the conversion was attributed, and we paid for those patients, okay? So, if there’s no statistical significance
16 00:04:08.280 ⇒ 00:04:19.120 Henry Zhao: in refund rates, we increase the contract’s volumes. If there is a substantive statistical difference, we find another option, okay? So, this is pretty straightforward. You can use BigQuery for this.
17 00:04:21.930 ⇒ 00:04:24.570 Henry Zhao: You have two tables that are relevant for this.
18 00:04:27.210 ⇒ 00:04:29.529 Henry Zhao: They’re all in ProdBT Marts, okay?
19 00:04:29.910 ⇒ 00:04:30.620 Casie Aviles: Okay.
20 00:04:30.850 ⇒ 00:04:33.159 Henry Zhao: There is order summary.
21 00:04:33.590 ⇒ 00:04:35.330 Henry Zhao: And order refunds, okay?
22 00:04:36.000 ⇒ 00:04:41.850 Henry Zhao: So, in order summary, you’ll be able to see if it was, canceled, and in order refunds, you’ll be able to see if they got a refund.
23 00:04:44.410 ⇒ 00:04:45.140 Henry Zhao: But…
24 00:04:45.140 ⇒ 00:04:45.870 Casie Aviles: Got it.
25 00:04:45.870 ⇒ 00:04:50.329 Henry Zhao: I don’t think you need to worry about orders canceled, I think we’re not paying on those. So I think you can just look at order refunds.
26 00:04:51.120 ⇒ 00:04:55.590 Henry Zhao: So yeah, even order summary, there have the…
27 00:04:56.330 ⇒ 00:05:02.310 Henry Zhao: UTMs, so if you want to just look at… First UTM…
28 00:05:02.880 ⇒ 00:05:06.209 Henry Zhao: And last UTMs, okay, and make sure those are catalysts.
29 00:05:06.420 ⇒ 00:05:15.139 Henry Zhao: If you need to know how to detect a catalyst order, you can either talk to Awash, or you can go to this master guide, this UTM setup guide. Shoot.
30 00:05:15.620 ⇒ 00:05:23.009 Henry Zhao: And look for how Catalyst is, okay? So for Catalyst, the UTM source is Catalyst, and the UTM medium is Affiliate, okay?
31 00:05:23.530 ⇒ 00:05:24.520 Casie Aviles: Okay.
32 00:05:25.350 ⇒ 00:05:26.370 Casie Aviles: Yes, please.
33 00:05:29.960 ⇒ 00:05:31.859 Henry Zhao: If you want to take it one step further.
34 00:05:33.690 ⇒ 00:05:35.809 Henry Zhao: That’s it. Do you have anything else?
35 00:05:35.810 ⇒ 00:05:40.060 Casie Aviles: So, my surname, yeah, that Aviles.
36 00:05:40.930 ⇒ 00:05:42.089 Henry Zhao: I wanna make sure I spell it right.
37 00:05:45.430 ⇒ 00:05:46.270 Henry Zhao: Let’s copy.
38 00:05:58.050 ⇒ 00:06:08.340 Henry Zhao: Okay, so it’s just this one. If you want to go even farther than that in order summary, you can go to this table that I just created for attribution. It’s called attribution…
39 00:06:09.760 ⇒ 00:06:10.630 Henry Zhao: No.
40 00:06:12.130 ⇒ 00:06:19.080 Henry Zhao: You probably want to join… attribution. No, you probably want to do webflow.
41 00:06:19.200 ⇒ 00:06:22.810 Henry Zhao: Order… no, thank you, pa… no, pages.
42 00:06:25.830 ⇒ 00:06:26.869 Henry Zhao: I’m trying to think.
43 00:06:27.860 ⇒ 00:06:31.610 Henry Zhao: Or another way you could do this is you can go to the Catalysts app.
44 00:06:34.810 ⇒ 00:06:40.019 Henry Zhao: and see all the ones that we paid them on. So if you go to… so let me share with you this login.
45 00:06:50.590 ⇒ 00:06:55.120 Henry Zhao: To get exactly the… Orders be paid on?
46 00:06:55.490 ⇒ 00:06:58.060 Henry Zhao: talk to Awash for export.
47 00:06:58.230 ⇒ 00:07:01.789 Henry Zhao: Or go to app, Oh, what the…
48 00:07:10.060 ⇒ 00:07:12.929 Henry Zhao: Or check this doc that I put… that I put together.
49 00:07:13.720 ⇒ 00:07:18.430 Henry Zhao: But this dog is outdated, so you might want to just use it for your,
50 00:07:19.310 ⇒ 00:07:23.860 Henry Zhao: To see how things work. Or you might want to pull it the same way.
51 00:07:24.690 ⇒ 00:07:25.480 Casie Aviles: Okay.
52 00:07:25.480 ⇒ 00:07:28.420 Henry Zhao: I did, but just updated, okay?
53 00:07:28.830 ⇒ 00:07:35.380 Henry Zhao: But, you know, you might not need to do that, because I doubt somebody would order something yesterday and already get a refund, you know?
54 00:07:36.110 ⇒ 00:07:39.700 Henry Zhao: I can send you also the query that I did for that.
55 00:07:40.840 ⇒ 00:07:45.630 Henry Zhao: I’m not, like, feeding you the answers, because I think it’s also good for you to explore a little bit, kind of what we did, to kind of learn it.
56 00:07:45.630 ⇒ 00:07:46.970 Casie Aviles: Yes, yeah.
57 00:07:47.860 ⇒ 00:07:49.540 Henry Zhao: Yeah, so it’s this query.
58 00:07:53.710 ⇒ 00:07:58.840 Henry Zhao: Now, this query, except you just replace the transaction IDs with the ones that you see in the app, okay?
59 00:07:59.830 ⇒ 00:08:03.310 Casie Aviles: What exactly is Catalyst, by the way? This is.
60 00:08:03.310 ⇒ 00:08:07.539 Henry Zhao: Yeah, so this is transaction ID here, it’s called order ID, but it’s actually transaction ID.
61 00:08:08.030 ⇒ 00:08:09.389 Casie Aviles: Okay. By query.
62 00:08:09.570 ⇒ 00:08:10.390 Henry Zhao: Okay?
63 00:08:11.200 ⇒ 00:08:20.579 Henry Zhao: So order ID is a transaction ID in reality, okay? So you just have to go to the 1Pass that I shared with you, replace the transaction IDs with the ones that I just shared.
64 00:08:20.690 ⇒ 00:08:30.769 Henry Zhao: And here you see all the statuses, right? So, like, since pharmacy ships completed are the ones that are done, that we should be paying for. And then you just want to match it with,
65 00:08:32.559 ⇒ 00:08:36.110 Henry Zhao: Webflow pages to see if there was any touch from Catalyst.
66 00:08:36.820 ⇒ 00:08:37.390 Casie Aviles: Okay.
67 00:08:38.720 ⇒ 00:08:53.860 Henry Zhao: Or you don’t need to. Like, if you get this report from Catalyst, you already know those are the ones we paid, so just check those orders and see if they were refunded. That’ll be a very simple join. So you can pick however path you want to go. If you want to learn it for real, you can go all paths and see if you get the same thing.
68 00:08:54.350 ⇒ 00:08:58.930 Henry Zhao: But yeah, for order refunds, you just need to join an order number. It’s very easy to know that they got refunded.
69 00:09:01.140 ⇒ 00:09:10.670 Henry Zhao: And then I guess I would compare that to the other order summaries, that are not Catalyst. So, because you need to see if there’s a statistical difference, right? For order summary table, by the way.
70 00:09:11.380 ⇒ 00:09:16.399 Henry Zhao: You need to put where… Row… what is it called?
71 00:09:22.030 ⇒ 00:09:24.570 Henry Zhao: transaction… Row number.
72 00:09:30.580 ⇒ 00:09:32.939 Henry Zhao: In your query, or you will double count.
73 00:09:35.640 ⇒ 00:09:40.020 Henry Zhao: You might want to try this also with this Texas SQL to see if they can also know these things.
74 00:09:40.460 ⇒ 00:09:42.750 Henry Zhao: I tried this with cursor, and it was wrong, so…
75 00:09:44.180 ⇒ 00:09:44.930 Casie Aviles: Yeah.
76 00:09:45.110 ⇒ 00:09:49.279 Henry Zhao: The text is SQL. But those are the two ways to go about it. You should get the same thing.
77 00:09:49.730 ⇒ 00:09:55.490 Henry Zhao: If not, you should actually probably alert Awash, because he’s doing it such that this should always match.
78 00:09:56.680 ⇒ 00:09:59.479 Henry Zhao: Because that’s what we should be paying for those orders.
79 00:10:01.760 ⇒ 00:10:02.530 Henry Zhao: Okay.
80 00:10:02.830 ⇒ 00:10:03.540 Casie Aviles: Okay.
81 00:10:03.820 ⇒ 00:10:06.490 Henry Zhao: Do you have any questions? You can always ask me if you have questions later.
82 00:10:07.260 ⇒ 00:10:09.379 Casie Aviles: Yeah, I think I’ll have…
83 00:10:09.680 ⇒ 00:10:17.709 Casie Aviles: More questions later. Right now, I’m just wrapping my head around all this stuff first. But yeah, I’ll dive into this.
84 00:10:18.070 ⇒ 00:10:18.730 Casie Aviles: Next.
85 00:10:18.770 ⇒ 00:10:21.839 Henry Zhao: Basically, order summary is the same as fact transaction.
86 00:10:21.920 ⇒ 00:10:41.250 Henry Zhao: It’s literally just a list of orders, okay? So these two tables are the list of orders that were placed and shipped, so those are all valid orders, okay? You don’t have to worry about, like, anything else, but if you want to see if they’re refunded, just go to order refunds, and this table should have everything you need. It has the customer, it has… was it shipped, was it delivered, it has,
87 00:10:41.620 ⇒ 00:10:44.869 Henry Zhao: UTMs, which is kind of all you need for now, right?
88 00:10:45.670 ⇒ 00:10:46.470 Casie Aviles: Alright.
89 00:10:46.470 ⇒ 00:10:50.490 Henry Zhao: I guess you can look at price of it also, so you can also, I guess, look at…
90 00:10:50.670 ⇒ 00:10:55.619 Henry Zhao: you might not want to look at just order count, right? You might want to also look at the total
91 00:10:56.270 ⇒ 00:10:57.440 Henry Zhao: Price.
92 00:10:57.680 ⇒ 00:11:01.129 Henry Zhao: To see if that is some significant difference, right?
93 00:11:02.340 ⇒ 00:11:06.029 Henry Zhao: So now you just look here, like, what you would need, and maybe it’s,
94 00:11:07.920 ⇒ 00:11:09.380 Henry Zhao: I don’t know where the price would be.
95 00:11:18.290 ⇒ 00:11:19.890 Henry Zhao: Quantity…
96 00:11:24.700 ⇒ 00:11:28.700 Henry Zhao: Realized revenue, maybe, would be good enough? Or transaction revenue? Yeah, something like that.
97 00:11:29.480 ⇒ 00:11:34.850 Henry Zhao: Yeah, it should be pretty straightforward. And you can also check if this refund date, refund amount, it works. It might not.
98 00:11:35.780 ⇒ 00:11:38.899 Henry Zhao: But these are all things that I would do in my analysis.
99 00:11:42.010 ⇒ 00:11:47.099 Casie Aviles: Okay, so… What would be, like, the expected deliverable again?
100 00:11:47.200 ⇒ 00:11:48.470 Casie Aviles: I might have missed that.
101 00:11:48.670 ⇒ 00:12:01.040 Henry Zhao: It will be in this thread, okay? So, it’s whatever Annie did. So basically, she looked at this, said, for the cancellation and refund for the offer, we’d explored some charts, one and two as examples.
102 00:12:01.040 ⇒ 00:12:10.339 Henry Zhao: The cancel rate, the offer is consistently higher than other channels. The June spike hit both groups, but the offer remained higher, so she showed this here. So, like, is the offer, is not the offer? Okay.
103 00:12:10.880 ⇒ 00:12:17.670 Henry Zhao: And you might also want to confirm… compare only first orders, okay? Because we’re only paying Catalysts on first-time orders.
104 00:12:18.840 ⇒ 00:12:19.780 Casie Aviles: I see.
105 00:12:20.270 ⇒ 00:12:34.279 Henry Zhao: So, to do apples and apples, you might want to also do it that way. So, you may want to impair a catalyst to offer to all other orders, to all other first-time orders.
106 00:12:34.500 ⇒ 00:12:43.609 Henry Zhao: Okay, just kind of take a look. I’ll just dig through all of these. First time offer… first time order might be a column in here. If not, it will be, in fact, transaction.
107 00:12:45.020 ⇒ 00:12:52.679 Henry Zhao: Or you can always look at how many orders the customer had before that day, right? You can do a query on customer ID.
108 00:12:54.200 ⇒ 00:12:55.910 Casie Aviles: Okay, so that’s one thing.
109 00:12:56.630 ⇒ 00:13:02.040 Henry Zhao: And then refund rate, counts are much lower than cancellations, with no clear, sustained differences across months.
110 00:13:03.360 ⇒ 00:13:09.420 Henry Zhao: So cancel rates, she gave, like, the offer was 20% versus other channels, she did a confidence interval.
111 00:13:09.770 ⇒ 00:13:14.920 Henry Zhao: There is a statistically significant difference. Confidence intervals do not overlap, further confirming the difference.
112 00:13:16.910 ⇒ 00:13:19.249 Henry Zhao: Okay, she also accounted for group sizes.
113 00:13:19.720 ⇒ 00:13:25.290 Henry Zhao: And then she just summarized it there. And then she also looked at average order value,
114 00:13:25.400 ⇒ 00:13:28.140 Henry Zhao: And then you could probably also look at timing of the churn.
115 00:13:28.650 ⇒ 00:13:32.940 Henry Zhao: Do they do it, like, right away, or do they, like, cancel their order at the end of their treatment?
116 00:13:34.150 ⇒ 00:13:41.589 Henry Zhao: Yeah, so that… she did it this way. So she made, like, this, table. So months since forced order, when did they churn, or when did they,
117 00:13:41.730 ⇒ 00:13:44.820 Henry Zhao: get refunded. So churn would obviously just be, like.
118 00:13:44.960 ⇒ 00:13:47.930 Henry Zhao: They ended their order, or they…
119 00:13:48.250 ⇒ 00:13:50.659 Henry Zhao: I guess, cancel their product, right? So…
120 00:13:52.490 ⇒ 00:13:53.120 Casie Aviles: Alright.
121 00:13:56.450 ⇒ 00:14:05.880 Henry Zhao: Yeah, just not, like, long-term churn. You wouldn’t have long-term churn, because long-term churn is, 6 months of inactivity. The offers start in September, so you wouldn’t have that. So don’t worry about long-term churn.
122 00:14:06.010 ⇒ 00:14:18.169 Henry Zhao: I would just right now look through all the things we just talked about, which was, like, the comparing to first-time orders, comparing to not first-time orders, comparing to Catalyst, maybe, to see if offer is better than Catalyst. I mean, Catalyst is better than the offer.
123 00:14:20.090 ⇒ 00:14:24.230 Henry Zhao: And just digging around and seeing what you can find, I guess, is how I would go about this.
124 00:14:24.780 ⇒ 00:14:28.560 Casie Aviles: Okay, great. Yeah, I’ll just… Check,
125 00:14:29.120 ⇒ 00:14:34.330 Casie Aviles: And also, do we have, like, an existing ticket for this, or should I go and create one?
126 00:14:34.600 ⇒ 00:14:35.330 Henry Zhao: Have one.
127 00:14:36.990 ⇒ 00:14:39.870 Henry Zhao: So I tagged you in this so you can look at this later.
128 00:14:40.960 ⇒ 00:14:45.719 Henry Zhao: I already assigned it to you, actually, I think.
129 00:14:46.270 ⇒ 00:14:47.570 Casie Aviles: Oh, okay, nice.
130 00:14:48.100 ⇒ 00:14:54.990 Henry Zhao: It’s this one. Churn and refund. Well, it’s not churn, cancel and refund analysis.
131 00:14:55.300 ⇒ 00:15:02.890 Henry Zhao: Can’t do churn right now, because not enough… Enough months of time.
132 00:15:03.910 ⇒ 00:15:06.640 Henry Zhao: Catalyst started in September.
133 00:15:09.470 ⇒ 00:15:10.340 Henry Zhao: Okay?
134 00:15:10.840 ⇒ 00:15:11.540 Casie Aviles: Alright.
135 00:15:11.900 ⇒ 00:15:15.090 Casie Aviles: Yep, I’ll… I’ll just take a look.
136 00:15:15.430 ⇒ 00:15:21.240 Henry Zhao: Check for the thread. I tagged you in with Annie.
137 00:15:21.470 ⇒ 00:15:23.649 Henry Zhao: Also, messaged you tips.
138 00:15:24.110 ⇒ 00:15:29.639 Henry Zhao: on… 11.25 at 3.15 p.m. Eastern.
139 00:15:29.820 ⇒ 00:15:41.320 Henry Zhao: I don’t know what time zone you’re in, so I just… just put Easton. Okay? And let me know if you have any questions. This, since you’re not… this is new to you, it might take you more points, so… you can just change… change it to whatever you need to change it to.
140 00:15:41.610 ⇒ 00:15:44.620 Casie Aviles: Okay, will the due date be the same here, or…
141 00:15:45.570 ⇒ 00:15:46.790 Henry Zhao: Will it be the same web?
142 00:15:47.050 ⇒ 00:15:48.209 Casie Aviles: Did you date?
143 00:15:49.700 ⇒ 00:16:00.150 Henry Zhao: I don’t think it’s that urgent. I just put tomorrow since Thanksgiving starts tomorrow, and they probably want to have some indicator before, like, they come back from Black Friday.
144 00:16:00.650 ⇒ 00:16:01.620 Casie Aviles: Okay, okay.
145 00:16:04.160 ⇒ 00:16:04.640 Casie Aviles: Alright.
146 00:16:04.640 ⇒ 00:16:09.990 Henry Zhao: Yeah. If you are stuck, just let me know if you have any questions, that might help you get it done faster.
147 00:16:10.700 ⇒ 00:16:12.609 Casie Aviles: Sure, sure. Okay. Thank you.
148 00:16:12.610 ⇒ 00:16:19.100 Henry Zhao: You can also ask Awayish, because he works a lot with the Catalyst data. He’s the one that actually knows about, like, what we should be paying, what we shouldn’t be paying.
149 00:16:19.230 ⇒ 00:16:20.750 Henry Zhao: And how that stuff gets tracked.
150 00:16:21.780 ⇒ 00:16:22.690 Casie Aviles: Okay.
151 00:16:24.080 ⇒ 00:16:24.750 Henry Zhao: Okay?
152 00:16:25.290 ⇒ 00:16:26.710 Henry Zhao: Rash, or whatever, you can ask me.
153 00:16:28.460 ⇒ 00:16:30.260 Casie Aviles: Okay, thank you, Henry.
154 00:16:30.260 ⇒ 00:16:32.890 Henry Zhao: Okay, no problem, thank you for helping out with the analysis.
155 00:16:33.320 ⇒ 00:16:34.360 Casie Aviles: Okay, yeah.
156 00:16:34.700 ⇒ 00:16:35.430 Henry Zhao: Take care.
157 00:16:35.550 ⇒ 00:16:36.250 Henry Zhao: Bye-bye.