Meeting Title: Analytics Engineering Daily Sync Date: 2025-02-26 Meeting participants: Luke Daque, Uttam Kumaran, Awaish Kumar, Caio Velasco
WEBVTT
1 00:00:29.790 ⇒ 00:00:30.800 Uttam Kumaran: Anyways.
2 00:00:33.980 ⇒ 00:00:34.820 Awaish Kumar: Hello!
3 00:00:40.020 ⇒ 00:00:44.210 Uttam Kumaran: Problem. What’s the problem with that? Pr, I like. Couldn’t figure it out.
4 00:00:45.920 ⇒ 00:00:50.859 Awaish Kumar: I’m sorry. The with the the workflow here.
5 00:00:51.260 ⇒ 00:00:51.890 Uttam Kumaran: Yeah.
6 00:00:52.750 ⇒ 00:01:01.990 Awaish Kumar: The actually it. It was not reading the credentials from this environment variables.
7 00:01:02.350 ⇒ 00:01:08.850 Awaish Kumar: And it was like by default when we don’t have this
8 00:01:09.040 ⇒ 00:01:13.479 Awaish Kumar: environment variable called Google Cloud credentials.
9 00:01:14.260 ⇒ 00:01:19.619 Awaish Kumar: Google application credentials. I think so. If we don’t have this, it by default goes for
10 00:01:20.070 ⇒ 00:01:28.120 Awaish Kumar: trying to log in and in the Gate of Actions it says, Please login
11 00:01:28.240 ⇒ 00:01:32.490 Awaish Kumar: on the Gcp. Cloud, like, even if I run locally it it
12 00:01:33.650 ⇒ 00:01:36.670 Awaish Kumar: text me to the browser. If I log in.
13 00:01:36.810 ⇒ 00:01:38.939 Awaish Kumar: then it comes back and it runs.
14 00:01:39.120 ⇒ 00:01:41.629 Awaish Kumar: But without that it was not working
15 00:01:41.900 ⇒ 00:01:45.100 Awaish Kumar: for me locally so, but on the
16 00:01:45.821 ⇒ 00:01:54.840 Awaish Kumar: in the github action, like, obviously we don’t have like it cannot go to the browser. So that’s why it was failing, because there was no one to log in there.
17 00:01:54.990 ⇒ 00:02:02.420 Awaish Kumar: And so I changed that Google app application, credential.
18 00:02:02.420 ⇒ 00:02:03.070 Uttam Kumaran: Oh, okay.
19 00:02:03.070 ⇒ 00:02:11.830 Awaish Kumar: Available to have this Json file. So now, by default, it points to this service account key and not try to log in.
20 00:02:13.300 ⇒ 00:02:14.490 Uttam Kumaran: Nice. That’s great.
21 00:02:15.240 ⇒ 00:02:17.860 Uttam Kumaran: I I couldn’t figure it out for like a week.
22 00:02:24.950 ⇒ 00:02:33.110 Uttam Kumaran: Okay, cool. Yeah. Maybe, Kyle, we can start with your work on macros. I left a few comments.
23 00:02:33.410 ⇒ 00:02:38.649 Uttam Kumaran: Basically, I was like, let’s we try to. The goal is to just try to answer as many questions
24 00:02:39.360 ⇒ 00:02:42.050 Uttam Kumaran: per table as possible. Right? So I want to avoid.
25 00:02:42.420 ⇒ 00:02:51.890 Uttam Kumaran: We want to avoid like having one table. For one question ideally, we can layer on additional dimensionality as as we need
26 00:02:53.430 ⇒ 00:03:01.135 Uttam Kumaran: And so ideally we have, like one table for tickets, one table for tickets, and and macros. Right? Was my was my
27 00:03:01.770 ⇒ 00:03:03.750 Uttam Kumaran: feedback helpful at all.
28 00:03:04.380 ⇒ 00:03:07.909 Caio Velasco: Yes, I just saw it. And yeah, totally, because
29 00:03:08.405 ⇒ 00:03:34.529 Caio Velasco: for some reason, my mind was stuck into separating questions by one table, or maybe 2 tables, but I, I know, obviously understand that those questions would be answered by the analysts when they try to use whatever data they have, and then the granularity makes sense. They would have to be something related to just tickets, and then ticket with macros, or even messages with.
30 00:03:34.530 ⇒ 00:03:41.609 Uttam Kumaran: This is the first.st This is the yeah. This is the 1st question they’re gonna ask. And it’s like in statistics, like, if we overfit.
31 00:03:41.720 ⇒ 00:03:44.549 Uttam Kumaran: Then we will have to make more tables.
32 00:03:45.050 ⇒ 00:04:00.660 Uttam Kumaran: But also we’re never gonna hit the bullseye on the 1st try. So the goal is to get something to pies, because what you’ll see is he doesn’t know all he doesn’t until he goes and sees and plays around with the data and graphs he’s gonna come back and say, Okay, I need another thing. I need another thing.
33 00:04:01.800 ⇒ 00:04:03.600 Uttam Kumaran: And so, yeah, I would say.
34 00:04:03.710 ⇒ 00:04:08.889 Uttam Kumaran: the core thing is, you know, and I and I do. I think that like a good example of
35 00:04:08.990 ⇒ 00:04:12.860 Uttam Kumaran: this and await, I’ll probably just pick on you here.
36 00:04:13.382 ⇒ 00:04:18.097 Uttam Kumaran: Is like always worked on this customer support mark, for example, right?
37 00:04:19.140 ⇒ 00:04:24.300 Uttam Kumaran: In this situation. Really, the goal was like, okay, we want to consider.
38 00:04:24.779 ⇒ 00:04:43.060 Uttam Kumaran: what like a people like what the analyst team needs in terms of set of questions for customer support. And he went ahead and built, okay, like somewhat. It’s actually, this is actually very, it’s basically the same sort of Zendesk work which is like they. I don’t think they’re using macros. And Zendesk and gorgeous are are
39 00:04:43.330 ⇒ 00:04:45.989 Uttam Kumaran: similar. But, like, okay, let’s have dim agents.
40 00:04:46.702 ⇒ 00:04:50.260 Uttam Kumaran: Dim policies, dim tickets, dim users.
41 00:04:50.723 ⇒ 00:05:07.549 Uttam Kumaran: And then I think, like, we’ll see. One thing that’s the problem in in Eden is we don’t have like super clear requirements. Yet. So basically, the directive was like, just build some wide tables that can answer everything. And we will go and sort of remove stuff if we need to.
42 00:05:08.107 ⇒ 00:05:11.580 Uttam Kumaran: So this may be a good Pr to sort of take as guidance.
43 00:05:12.253 ⇒ 00:05:13.299 Uttam Kumaran: I will.
44 00:05:13.760 ⇒ 00:05:18.040 Uttam Kumaran: It’s I’ll send it here in the in the data team channel.
45 00:05:26.380 ⇒ 00:05:36.050 Uttam Kumaran: But basically, yeah, I want us to try to move forward with a couple of fact tables and a couple of dim tables today. If we can ideally, we can get something around agents
46 00:05:36.160 ⇒ 00:05:41.690 Uttam Kumaran: right? And and I think this dim agents is probably a good thing to look at which is just information about them.
47 00:05:41.900 ⇒ 00:05:48.380 Uttam Kumaran: And then we have the macros, and that way in the in the fact table. You can just leave the agent Id.
48 00:05:48.520 ⇒ 00:05:50.919 Uttam Kumaran: And if they need more stuff
49 00:05:51.030 ⇒ 00:05:54.750 Uttam Kumaran: they can join in dim agents as needed.
50 00:05:55.480 ⇒ 00:06:07.140 Caio Velasco: Okay, got it, got it. And, for example, like for for the first, st let’s say, the 1st 2 ones are accounting stuff. And as I saw in the macro part.
51 00:06:07.260 ⇒ 00:06:11.010 Caio Velasco: We are using the messages table that has
52 00:06:11.669 ⇒ 00:06:18.119 Caio Velasco: like a big Json with multiple macros. We are using that as a as a let’s say, a premise
53 00:06:18.170 ⇒ 00:06:42.340 Caio Velasco: to find and Count Macros, because there was no other way, because we were expecting at the ticket stable to have the Macros, but we were not there. So forget in this case those things the joy or the the logic. It’s done by us in in the either end or march? Or are we also expecting them to do that? If they had? Well, the message is stable, let’s say.
54 00:06:42.810 ⇒ 00:06:51.350 Uttam Kumaran: Yeah, like this example, I would say, without like, just macros alone, here is probably not
55 00:06:51.840 ⇒ 00:06:53.669 Uttam Kumaran: the best table meaning.
56 00:06:53.860 ⇒ 00:07:11.287 Uttam Kumaran: I want us, I want to know, like who used cause, because the other thing is like layering on dimensionality. That may help. So, for example, let’s say you need fact, macro usage. But it’s like, Okay, I want to use this somewhere else. We can change it to an end table. But ideally, I want to see a customer service table that’s like
57 00:07:12.170 ⇒ 00:07:19.100 Uttam Kumaran: like it could just be fact tickets or fact tickets. Macros usage right? And it has everything in there.
58 00:07:19.586 ⇒ 00:07:28.390 Uttam Kumaran: And then it joins. It brings this data in. I think this alone, just with Macros, answers the one question, but it doesn’t answer
59 00:07:28.720 ⇒ 00:07:32.180 Uttam Kumaran: what agents use the most macros. It doesn’t answer
60 00:07:32.350 ⇒ 00:07:36.150 Uttam Kumaran: like, what time are the macros used things like that, you know.
61 00:07:37.920 ⇒ 00:07:38.590 Caio Velasco: Okay.
62 00:07:38.900 ⇒ 00:07:39.590 Caio Velasco: Okay.
63 00:07:41.290 ⇒ 00:07:44.830 Uttam Kumaran: But again, you’re not gonna get it right on the 1st try. So I would say.
64 00:07:44.940 ⇒ 00:07:47.780 Uttam Kumaran: get something out in terms of a Pr.
65 00:07:47.990 ⇒ 00:07:53.950 Uttam Kumaran: And in terms of like, okay, we have a, maybe we have a couple of dim tables, and we have
66 00:07:54.230 ⇒ 00:07:59.440 Uttam Kumaran: a couple of end tables that do some combination. Right? Because, like this is.
67 00:07:59.620 ⇒ 00:08:09.679 Uttam Kumaran: this is this is all like really great logic, and maybe this lives as an end table. And then you could bring this in, or you could just continue to build this as a mart table.
68 00:08:09.810 ⇒ 00:08:14.784 Uttam Kumaran: Again, I I can give probably specific feedback once we have the Pr ready.
69 00:08:16.260 ⇒ 00:08:26.040 Uttam Kumaran: But yeah, ideally, I just want I wanted I just want bias to go take a crack at like seeing something, because right now I can tell. It’s his feedback is all reactionary.
70 00:08:26.710 ⇒ 00:08:48.329 Uttam Kumaran: You see what I mean? Like, he’s responding to you, but he’s not going, and he doesn’t have as much context into the data, right? So he’s sort of hearing your your questions and being like, Okay, that makes sense. He’s gonna keep, he. But he doesn’t have a she’s not gonna have a strong opinion. Our team needs to have the strong opinion about the modeling. His team is gonna have a strong opinion about what they need.
71 00:08:48.490 ⇒ 00:08:53.243 Uttam Kumaran: And so I wanna get something out like, even for even for
72 00:08:53.800 ⇒ 00:08:56.499 Uttam Kumaran: the customer service mark Pr. That a waste did.
73 00:08:56.670 ⇒ 00:09:00.100 Uttam Kumaran: I sort of did the same thing where I was like, we want to get something out
74 00:09:00.420 ⇒ 00:09:12.389 Uttam Kumaran: because we could. We’ll we’ll we’ll continue to lose some time in just iterating small pieces. I want to get something out, and there’s no cost to us to iterate after that right like. So
75 00:09:13.160 ⇒ 00:09:26.799 Uttam Kumaran: W. The one thing that you know is is pretty common for engineering practices is just to get some pieces out as we need right, because there’s no cost to us, doesn’t break anything for us to just push these changes out for them to test it, and then for us to
76 00:09:26.910 ⇒ 00:09:41.300 Uttam Kumaran: sort of collect that feedback, especially for new things. Let’s say we have to do a remodel of like something existing. Yeah, there may be some downstream implications. But for now, yeah, again, I would take my quote as like, yeah, don’t let perfect be the enemy. So
77 00:09:41.670 ⇒ 00:09:51.860 Uttam Kumaran: yeah, I would. I would just hand something over to them and then have him cause cause I’m telling you the number of questions they asked. I don’t know what like. Let me let me go see what the
78 00:09:55.092 ⇒ 00:09:57.320 Uttam Kumaran: the gorgeous.
79 00:10:00.920 ⇒ 00:10:03.186 Uttam Kumaran: They’re gonna have more questions than this.
80 00:10:03.852 ⇒ 00:10:04.877 Caio Velasco: That’s true, true.
81 00:10:05.530 ⇒ 00:10:06.340 Uttam Kumaran: Oh!
82 00:10:06.640 ⇒ 00:10:13.729 Caio Velasco: So based on on this questions, then we can all already see that you have, like a dim for tick fields, a dim for agents.
83 00:10:13.990 ⇒ 00:10:20.569 Uttam Kumaran: Correct. Yeah, so dim. Dim agent like it could be dim agent. It could be fact tickets.
84 00:10:21.150 ⇒ 00:10:25.789 Uttam Kumaran: fact, Macros. And then you can build like a summary table. Basically.
85 00:10:26.640 ⇒ 00:10:30.060 Uttam Kumaran: Okay, right? So you have all the agents.
86 00:10:30.510 ⇒ 00:10:36.170 Uttam Kumaran: refunds and subscriptions is coming from other tables. So they will do. They will do this join themselves
87 00:10:36.550 ⇒ 00:10:45.569 Uttam Kumaran: which agents are using the macros. You need agents, and you need fact, Macros, right? And you need some sort of id that joins these both ticket fields.
88 00:10:46.155 ⇒ 00:10:57.670 Uttam Kumaran: You need some sort of maybe you need like dim tickets or dim ticket fields. And then, macros being used the most. What you can do here is you could just have fact, Macros, or fact Macro usage.
89 00:10:57.850 ⇒ 00:10:59.070 Uttam Kumaran: And then
90 00:10:59.210 ⇒ 00:11:14.040 Uttam Kumaran: that can be used to sort of run account, or if they want to see fact, they want to sort of join agents to the macros. They can join agents to fact, Macros or right and I sort of don’t have the all the context about like the
91 00:11:14.647 ⇒ 00:11:18.850 Uttam Kumaran: the things in the macro and stuff like that. But I think you get what I mean.
92 00:11:19.870 ⇒ 00:11:26.940 Caio Velasco: No, no, I do. I do. Yeah. So as I, as I see, it’s, it’s basically if there is anything
93 00:11:27.080 ⇒ 00:11:38.010 Caio Velasco: with a variant formal, like a Json, a crazy Json, or something. This should be our work to make that easy for them. And then, after that, I believe that if you have the correct dims. In fact.
94 00:11:38.010 ⇒ 00:11:38.590 Uttam Kumaran: Yes.
95 00:11:38.590 ⇒ 00:11:40.389 Caio Velasco: It’s it’s easy for a day.
96 00:11:40.390 ⇒ 00:11:49.150 Uttam Kumaran: Yeah, like, the the next thing is like, look if they’re joining dim and fact tables constantly, and they’re doing some like complicated logic on their side
97 00:11:49.410 ⇒ 00:11:53.599 Uttam Kumaran: like window functions. Then maybe we should move this to a summary table. Right?
98 00:11:53.820 ⇒ 00:12:01.269 Uttam Kumaran: Basically, that’s like the one layer up up ahead of like, let’s make the analysis a little bit easier. But
99 00:12:01.530 ⇒ 00:12:04.379 Uttam Kumaran: ultimately, if we have all the right dim and fact tables.
100 00:12:04.750 ⇒ 00:12:18.439 Uttam Kumaran: these guys are smart enough to go build whatever they need. They may build it in a way that’s slow. They may add logic, and that’s something that over time we will bring back into the repo. But in the short term we just want to give them all the building blocks.
101 00:12:19.780 ⇒ 00:12:27.460 Uttam Kumaran: and then also, yeah, if any, if there’s any, if there’s any columns that are like Json. You’re like, I don’t know what to do with this. You could just shove it into a json.
102 00:12:27.790 ⇒ 00:12:30.310 Uttam Kumaran: You can shove it into a variant column and leave it there
103 00:12:30.420 ⇒ 00:12:54.969 Uttam Kumaran: until someone says, I need this, and then we can consider like, okay, we know how to parse this out or something, right? So those are always going to be the trade offs. Then the best thing is like, if someone goes in, looks at all the tables gives us exactly what we need. This is what happens at like the Fortune. 500 companies that I worked at. The problem is dude. They don’t get anything done. It takes something to 3 months to get like one table out. So that’s always gonna be the competition. But I think
104 00:12:55.120 ⇒ 00:13:01.489 Uttam Kumaran: you can rely on me to be like, okay, cool. Let’s just push something out versus. Okay, let’s take our time here.
105 00:13:01.961 ⇒ 00:13:08.209 Uttam Kumaran: We’re gonna have more complicated stuff coming down the line where it will require a little bit more time on the modeling side.
106 00:13:08.736 ⇒ 00:13:16.149 Uttam Kumaran: But for this I feel pretty good about your one. Your understanding of how gorgeous works, how Macros works, how tickets works.
107 00:13:16.330 ⇒ 00:13:24.779 Uttam Kumaran: And then, so I’m like, Okay, I think I feel pretty good about getting something there, because also, once we get it out. They’re gonna have feedback. So for your ability to take on that feedback.
108 00:13:24.960 ⇒ 00:13:26.489 Uttam Kumaran: I feel pretty good.
109 00:13:27.340 ⇒ 00:13:31.050 Caio Velasco: Perfect, perfect, no super helpful. Thank you. I will do that today.
110 00:13:31.610 ⇒ 00:13:36.405 Uttam Kumaran: Great. Okay, cool.
111 00:13:38.530 ⇒ 00:13:39.580 Uttam Kumaran: What else?
112 00:13:45.680 ⇒ 00:13:48.809 Uttam Kumaran: I don’t know, Luke. Anything on your side. We want to take a look at.
113 00:13:52.980 ⇒ 00:14:01.590 Luke Daque: I guess the my one, like blockers at at the moment is the real, not ingest thing, the
114 00:14:02.560 ⇒ 00:14:03.420 Luke Daque: the source.
115 00:14:03.420 ⇒ 00:14:03.790 Uttam Kumaran: Yeah.
116 00:14:03.790 ⇒ 00:14:07.480 Luke Daque: For some reason, but the other so I just like
117 00:14:07.710 ⇒ 00:14:11.693 Luke Daque: left that for now, and just like focused on like working on the
118 00:14:12.270 ⇒ 00:14:19.720 Luke Daque: bare metric stuff. I think I already got something for revenue, and like breaking it down between
119 00:14:19.900 ⇒ 00:14:28.710 Luke Daque: other revenue, which is like the token reloads and the subscription revenue, basically, which is like, I think that’s like, what
120 00:14:28.840 ⇒ 00:14:36.120 Luke Daque: Mitch wanted. So I’m yeah. I’m trying to figure out the the rest of the metrics like churn and stuff.
121 00:14:36.870 ⇒ 00:14:43.386 Uttam Kumaran: Leave the real leave, leave the real to me, and then anything on churn, Mrr, I’ve done a lot of that. So just
122 00:14:43.920 ⇒ 00:14:49.090 Uttam Kumaran: just set just like let’s see if we can. Is that in the documentation somewhere.
123 00:14:51.297 ⇒ 00:14:53.610 Luke Daque: What do you mean? Which documentation do you mean?
124 00:14:53.610 ⇒ 00:14:57.230 Uttam Kumaran: Like is that in the Stackblitz data platform documentation.
125 00:14:58.150 ⇒ 00:15:00.939 Luke Daque: Not at the moment.
126 00:15:01.600 ⇒ 00:15:02.410 Uttam Kumaran: Okay.
127 00:15:03.950 ⇒ 00:15:05.140 Luke Daque: I can.
128 00:15:06.480 ⇒ 00:15:08.420 Luke Daque: So I see so like.
129 00:15:08.420 ⇒ 00:15:11.499 Uttam Kumaran: Yes, yeah. One thing that would be great is.
130 00:15:13.080 ⇒ 00:15:19.029 Uttam Kumaran: I see, okay. And so we’re gonna we’re gonna end up having to do this in probably fax subscriptions
131 00:15:19.490 ⇒ 00:15:26.270 Uttam Kumaran: where I will help you build. Tell you how to build a column. That is arr an Mrr,
132 00:15:28.470 ⇒ 00:15:34.590 Uttam Kumaran: because basically what we’re so let’s say, like, what’s the software that we use here? Let’s take a good example.
133 00:15:37.370 ⇒ 00:15:38.710 Luke Daque: What? What do you mean? Like.
134 00:15:38.710 ⇒ 00:15:46.040 Uttam Kumaran: Like, what is a software that you use or we use at work that we pay for? Because I want to show you what like how to do the Mr. Calculation.
135 00:15:47.600 ⇒ 00:15:47.980 Luke Daque: Oh!
136 00:15:47.980 ⇒ 00:15:50.600 Uttam Kumaran: Like name, pick anything we could pick. We could pick cursor.
137 00:15:50.980 ⇒ 00:15:52.800 Luke Daque: Yeah, let’s let’s yeah. Cursor.
138 00:15:52.800 ⇒ 00:15:57.750 Uttam Kumaran: The cursor. So cursor is 20 a month right?
139 00:15:58.040 ⇒ 00:16:03.449 Uttam Kumaran: And so what is our Mrr? Mrr is 20
140 00:16:03.910 ⇒ 00:16:06.820 Uttam Kumaran: right? What is our arr arr is.
141 00:16:07.300 ⇒ 00:16:08.880 Luke Daque: 2, 40 to 3, 40.
142 00:16:08.880 ⇒ 00:16:09.550 Uttam Kumaran: Right.
143 00:16:09.880 ⇒ 00:16:10.540 Luke Daque: Right.
144 00:16:10.540 ⇒ 00:16:12.580 Uttam Kumaran: So let’s do a couple of scenarios.
145 00:16:13.060 ⇒ 00:16:15.369 Uttam Kumaran: Let’s say you subscribe.
146 00:16:15.670 ⇒ 00:16:20.650 Uttam Kumaran: Let’s create a subscription date, and that is.
147 00:16:20.930 ⇒ 00:16:24.019 Uttam Kumaran: 1, 1, 1, 25, right?
148 00:16:25.060 ⇒ 00:16:25.670 Luke Daque: Alright!
149 00:16:26.370 ⇒ 00:16:28.009 Uttam Kumaran: So you subscribe there
150 00:16:28.370 ⇒ 00:16:32.700 Uttam Kumaran: and then we want to have the current month. So we want to just have the month.
151 00:16:32.980 ⇒ 00:16:38.500 Uttam Kumaran: This is going to be 1, 1, 2025. Let’s say, this is 2, 1, 2025
152 00:16:39.089 ⇒ 00:16:43.440 Uttam Kumaran: and I’m just gonna drag this out. Let’s just I’ll just we’ll show. I’ll show a couple of scenarios.
153 00:16:43.740 ⇒ 00:16:46.015 Luke Daque: Right. And so we wanna look at
154 00:16:47.070 ⇒ 00:16:49.910 Uttam Kumaran: Mrr, a. RR.
155 00:16:50.430 ⇒ 00:16:57.030 Uttam Kumaran: And then we want to look at remaining revenue.
156 00:16:59.120 ⇒ 00:17:01.559 Uttam Kumaran: So if we subscribe here, we’re gonna pay.
157 00:17:01.780 ⇒ 00:17:03.399 Uttam Kumaran: We pay the 20 bucks.
158 00:17:03.510 ⇒ 00:17:06.649 Uttam Kumaran: It’s 20, and it’s basically 20 until we
159 00:17:06.800 ⇒ 00:17:11.040 Uttam Kumaran: let’s say we have here. And let’s say we have another date here, right?
160 00:17:13.160 ⇒ 00:17:13.880 Uttam Kumaran: like.
161 00:17:14.160 ⇒ 00:17:14.990 Luke Daque: Canceled.
162 00:17:15.290 ⇒ 00:17:17.849 Uttam Kumaran: Yeah, cancel. Cancel that right?
163 00:17:17.859 ⇒ 00:17:18.309 Luke Daque: Yeah.
164 00:17:18.310 ⇒ 00:17:23.940 Uttam Kumaran: And let’s say we cancel that 6, 1520, 25, right?
165 00:17:24.866 ⇒ 00:17:27.849 Uttam Kumaran: So the error still is 2 40
166 00:17:28.410 ⇒ 00:17:31.290 Uttam Kumaran: alright. So this is still active here.
167 00:17:32.090 ⇒ 00:17:37.850 Uttam Kumaran: and then the remaining revenue is is this.
168 00:17:40.970 ⇒ 00:17:42.600 Uttam Kumaran: It’s like this.
169 00:17:44.352 ⇒ 00:17:54.350 Uttam Kumaran: I don’t know how to do this function here minus this, and then it’s this.
170 00:17:55.610 ⇒ 00:17:59.210 Uttam Kumaran: minus this. Right? It’s like this sort of recursion.
171 00:17:59.780 ⇒ 00:18:00.860 Luke Daque: Calling.
172 00:18:01.310 ⇒ 00:18:04.239 Uttam Kumaran: Right? So this is all what’s remaining.
173 00:18:06.650 ⇒ 00:18:09.870 Uttam Kumaran: And then let’s say they cancel in 6 15.
174 00:18:10.420 ⇒ 00:18:14.020 Uttam Kumaran: So then this all becomes 0. Basically.
175 00:18:15.330 ⇒ 00:18:20.400 Uttam Kumaran: And but then there’s gonna be a canceled
176 00:18:21.810 ⇒ 00:18:28.599 Uttam Kumaran: people are going to want to see what is the canceled Mrr and the canceled arr.
177 00:18:29.230 ⇒ 00:18:31.360 Uttam Kumaran: And the reason for that is
178 00:18:31.989 ⇒ 00:18:39.800 Uttam Kumaran: they want to know at any moment how much revenue is churning. And right this is. This is a concept of revenue churn.
179 00:18:40.300 ⇒ 00:18:48.360 Uttam Kumaran: There’s also a concept of user churn. Right? So like, in this situation, this user would have turned here. But
180 00:18:48.720 ⇒ 00:19:02.600 Uttam Kumaran: let’s like, let’s also look at what the revenue churn is. So at this moment it looks like, okay. The cancelled Mrr is the 20 bucks, but the cancelled arr is 2, 40 right, and we could have, like
181 00:19:02.700 ⇒ 00:19:10.430 Uttam Kumaran: the canceled remaining revenue is 1 20.
182 00:19:11.970 ⇒ 00:19:13.310 Uttam Kumaran: Do what I mean.
183 00:19:14.040 ⇒ 00:19:16.570 Uttam Kumaran: Here’s another situation. Let’s say
184 00:19:16.810 ⇒ 00:19:25.640 Uttam Kumaran: that this is just, for like this is like, this is just for like me and you. Right? So we just have single users. Let’s say we have an organization churn. An organization has 5 licenses.
185 00:19:25.930 ⇒ 00:19:29.559 Uttam Kumaran: There may be still one. It may be one organization churn.
186 00:19:29.810 ⇒ 00:19:33.719 Uttam Kumaran: but the revenue churn is 5 times this right?
187 00:19:34.090 ⇒ 00:19:43.080 Uttam Kumaran: But that’s what they want to see is they want to see how much new subscription revenue is there? Right? So the key metrics are new
188 00:19:43.780 ⇒ 00:19:56.200 Uttam Kumaran: sub revenue. They want to see existing sub revenue. They want to see and sold dub, Rev. Churn.
189 00:20:00.700 ⇒ 00:20:02.019 Uttam Kumaran: Sorry. Go ahead. Question.
190 00:20:03.780 ⇒ 00:20:08.699 Luke Daque: Yeah. For for the 1st example, where it it got cancelled on 6, 15.
191 00:20:08.940 ⇒ 00:20:12.629 Luke Daque: So the succeeding months, basically, we won’t have like
192 00:20:13.710 ⇒ 00:20:17.349 Luke Daque: data for that subscription anymore. Right?
193 00:20:17.350 ⇒ 00:20:18.960 Uttam Kumaran: Correct. It’ll all be 0.
194 00:20:19.650 ⇒ 00:20:23.200 Luke Daque: But let’s say, let’s say they, let’s say they reactivate.
195 00:20:24.230 ⇒ 00:20:25.510 Uttam Kumaran: Then it starts back up.
196 00:20:26.960 ⇒ 00:20:27.630 Luke Daque: Right.
197 00:20:28.500 ⇒ 00:20:34.749 Uttam Kumaran: But like you, you almost have like a you have to have here like a, it’s basically like a status, right?
198 00:20:34.890 ⇒ 00:20:39.420 Uttam Kumaran: The status field would be active right.
199 00:20:39.760 ⇒ 00:20:40.370 Luke Daque: Hmm.
200 00:20:41.070 ⇒ 00:20:45.570 Uttam Kumaran: And then it’ll be it basically is active and then inactive. Right?
201 00:20:45.850 ⇒ 00:20:46.930 Uttam Kumaran: Something like that.
202 00:20:47.130 ⇒ 00:20:47.580 Luke Daque: Right.
203 00:20:47.580 ⇒ 00:20:48.190 Luke Daque: But it’s
204 00:20:48.190 ⇒ 00:20:54.689 Luke Daque: so. Does that mean we need a date spine that’s like for the whole year, for until like December of this year, like to.
205 00:20:55.520 ⇒ 00:20:55.950 Uttam Kumaran: Well.
206 00:20:56.900 ⇒ 00:20:58.429 Luke Daque: Or until just the.
207 00:20:58.640 ⇒ 00:21:04.819 Uttam Kumaran: Well, well, here’s the thing. So these subscriptions for Bolt. I I got you, I mean, we can go look
208 00:21:09.060 ⇒ 00:21:13.670 Uttam Kumaran: if I go to like, how do I go to? Even just like the pricing?
209 00:21:27.080 ⇒ 00:21:34.620 Uttam Kumaran: Okay, I don’t know how much I forgot how much it costs. But yeah, basically, we we want to have, like a fact, subscriptions
210 00:21:34.750 ⇒ 00:21:52.529 Uttam Kumaran: which has information about every active subscription. Right? So this subscription will have an Id, but also we want to have something around the user and something around user subscription. So I don’t know a hundred percent of the what the details will look like. But basically, we want a table. That sort of starts to look like this where
211 00:21:52.840 ⇒ 00:22:09.289 Uttam Kumaran: on any given month, and they’re doing everything on the month level. So we want to know how much revenue is new subscription existing, subscription right? So at the moment, this is all existing subscription revenue. But in this month this is new subscription revenue, right? So.
212 00:22:09.290 ⇒ 00:22:09.650 Luke Daque: Right.
213 00:22:09.650 ⇒ 00:22:11.759 Uttam Kumaran: You also have another column which is
214 00:22:12.040 ⇒ 00:22:18.219 Uttam Kumaran: like is new, and it’s like right. If this is true.
215 00:22:18.680 ⇒ 00:22:19.340 Luke Daque: Hmm.
216 00:22:19.820 ⇒ 00:22:22.289 Uttam Kumaran: And then it’s false, right?
217 00:22:24.230 ⇒ 00:22:29.709 Uttam Kumaran: And you can also do that based on this. So you probably don’t need to have this field. But
218 00:22:29.830 ⇒ 00:22:34.160 Uttam Kumaran: you could probably do, based on the sub. If the subscription date is in this month
219 00:22:34.340 ⇒ 00:22:44.859 Uttam Kumaran: right like, if if you date trunk this and it equals this, then. Yes, it’s a new subscription. So that means this, Mrr gets put in the new
220 00:22:45.390 ⇒ 00:22:51.079 Uttam Kumaran: new Mrr category. Yeah. So then what you can do here is you can have new Mrr.
221 00:22:51.540 ⇒ 00:23:00.930 Uttam Kumaran: And the way I found it is, it’s actually best to show these like this, because you can just say any whatever it’s new, then pull the 20. You can also do new arr
222 00:23:02.321 ⇒ 00:23:06.200 Uttam Kumaran: and then same thing. When the subscription is cancelled.
223 00:23:06.430 ⇒ 00:23:10.609 Uttam Kumaran: you may, we may have to ask the one question we have to ask is,
224 00:23:12.370 ⇒ 00:23:21.050 Uttam Kumaran: what happens to mid month cancellations is there prorating?
225 00:23:22.980 ⇒ 00:23:26.080 Uttam Kumaran: Etc? Basically.
226 00:23:26.220 ⇒ 00:23:31.039 Uttam Kumaran: do they do a refund of the remaining like. If they cancel the middle of month, are they refunding 10 bucks
227 00:23:31.370 ⇒ 00:23:33.290 Uttam Kumaran: you cancel? There’s no refund.
228 00:23:33.730 ⇒ 00:23:38.929 Uttam Kumaran: Do you consider it cancelled in the current month. That’s cancel that or in the next month.
229 00:23:39.100 ⇒ 00:23:44.060 Uttam Kumaran: But then it’s basically like, How do you just want to allocate revenue to buckets? That’s all. This exercise is.
230 00:23:45.000 ⇒ 00:23:47.329 Luke Daque: Yeah, and it doesn’t matter if
231 00:23:47.770 ⇒ 00:23:53.610 Luke Daque: the subscription was paid or not right like, or should we only consider the paid ones.
232 00:23:54.020 ⇒ 00:24:00.219 Uttam Kumaran: I don’t. I wouldn’t worry about that right now. Because I’m I think that’s like a more of a
233 00:24:01.150 ⇒ 00:24:06.260 Uttam Kumaran: payment processing thing. You mean like you’re gonna look into us stripe or something.
234 00:24:06.430 ⇒ 00:24:07.190 Luke Daque: Yeah.
235 00:24:07.890 ⇒ 00:24:12.560 Uttam Kumaran: I’m pretty sure they they wait until it’s all paid to do the activation. But it could be a good question.
236 00:24:13.240 ⇒ 00:24:13.980 Luke Daque: Okay.
237 00:24:14.210 ⇒ 00:24:19.939 Luke Daque: so ideally, I shouldn’t be looking at stripe at all for this one. Just the subscriptions from Co. Postgres, I guess, because
238 00:24:20.200 ⇒ 00:24:22.190 Uttam Kumaran: But do the subscriptions have the money.
239 00:24:23.650 ⇒ 00:24:25.789 Luke Daque: I’ll I’ll have to check.
240 00:24:26.110 ⇒ 00:24:29.700 Uttam Kumaran: Cause. I feel like, you can actually probably just get all this from stripe
241 00:24:30.760 ⇒ 00:24:35.079 Uttam Kumaran: because and then basically based on the user field, you can join in what they’re using.
242 00:24:37.240 ⇒ 00:24:38.130 Luke Daque: Yeah.
243 00:24:38.130 ⇒ 00:24:43.070 Uttam Kumaran: Right, I I would say, don’t get this from postgres, because
244 00:24:45.085 ⇒ 00:24:56.109 Uttam Kumaran: it’s not gonna have the money associated with it may have the plan. But you want to get the revenue like you want to look at the subscription. How much the subscription is for what’s the monthly plan, etc?
245 00:25:00.090 ⇒ 00:25:03.010 Luke Daque: Okay, I’ll I’ll take a look. Probably it’s
246 00:25:04.050 ⇒ 00:25:09.369 Luke Daque: in one of the subscriptions tables in stripe like the amount. Yeah, I I think I see it?
247 00:25:10.730 ⇒ 00:25:11.490 Luke Daque: Yep.
248 00:25:12.860 ⇒ 00:25:13.710 Luke Daque: Okay.
249 00:25:15.820 ⇒ 00:25:23.780 Uttam Kumaran: So take a stab at this ideally. What you want to see is that you can take a month, and we can match bare metrics for.
250 00:25:23.780 ⇒ 00:25:24.670 Luke Daque: Yeah.
251 00:25:25.080 ⇒ 00:25:28.099 Uttam Kumaran: Right, for, like, what is the Mrr in a given month?
252 00:25:28.330 ⇒ 00:25:33.660 Uttam Kumaran: Ideally, these guys are looking at almost like how much Mrr they’re adding per day.
253 00:25:34.610 ⇒ 00:25:35.390 Luke Daque: Right.
254 00:25:36.030 ⇒ 00:25:38.909 Uttam Kumaran: And we can break this out by day later.
255 00:25:39.230 ⇒ 00:25:43.139 Uttam Kumaran: because the the problem with the day is that the Mrr is for a month.
256 00:25:43.340 ⇒ 00:25:52.199 Uttam Kumaran: So we just have to be careful with how we’re doing the summing. But if you can build something like this, it’s a really good start like this is a little bit complicated.
257 00:25:52.769 ⇒ 00:25:57.859 Uttam Kumaran: It’s like, what are they? But however, anytime we do subscription modeling, it’s gonna be like this.
258 00:25:58.000 ⇒ 00:26:00.729 Uttam Kumaran: We’re gonna do subscription modeling for another client, too.
259 00:26:01.169 ⇒ 00:26:04.570 Uttam Kumaran: We. We still probably have to do this for for Javi.
260 00:26:05.204 ⇒ 00:26:22.940 Uttam Kumaran: It’s gonna look like this where they basically want to know how much new subscription, Mrr. They’re adding, how much new subscription arr, how much, how much, is from active subscriptions, and how much are they losing from people that are churning. And then this one you may consider like a reactivation right.
261 00:26:23.740 ⇒ 00:26:24.450 Luke Daque: Right.
262 00:26:24.900 ⇒ 00:26:32.369 Uttam Kumaran: So there may be another thing which is like, how much is reactivating revenue? Right? Because if you think about a company, you have people coming in.
263 00:26:32.620 ⇒ 00:26:37.939 Uttam Kumaran: Yeah, yeah, for company. Let’s say, users, you have users coming in that are new. You have users leaving.
264 00:26:38.160 ⇒ 00:26:40.879 Uttam Kumaran: Then you have users that you’re trying to bring back.
265 00:26:41.490 ⇒ 00:26:46.559 Uttam Kumaran: Those are all different parts of the company. Right? You have marketing team. You then have the product team.
266 00:26:46.660 ⇒ 00:26:49.960 Uttam Kumaran: Then you have like reactivation retention.
267 00:26:50.510 ⇒ 00:26:58.279 Uttam Kumaran: right? So that’s how it boils up to like the the company level. They may run campaigns to say, Hey, we updated something new. Come, try us again. We’ll give you a free trial.
268 00:26:58.900 ⇒ 00:26:59.640 Luke Daque: Hmm.
269 00:26:59.910 ⇒ 00:27:05.950 Uttam Kumaran: Stuff like that. Right? So you think of it, it’s it’s basically called like funnel bucket.
270 00:27:16.380 ⇒ 00:27:18.889 Uttam Kumaran: yeah, I’ll have to see. Yeah, like.
271 00:27:19.050 ⇒ 00:27:27.101 Uttam Kumaran: this is a good example is like you’re always bringing people in, and then people are leaving. So you have leaky bucket. But think about a bucket
272 00:27:27.870 ⇒ 00:27:34.320 Uttam Kumaran: like when you’re filling water in a bucket. If the hole is small enough you can keep filling, and it’ll go right.
273 00:27:34.810 ⇒ 00:27:35.320 Luke Daque: Yeah.
274 00:27:35.320 ⇒ 00:27:44.557 Uttam Kumaran: But if your hole is like, if there’s no floor, there’s no if there’s the whole bottom is is a hole, then it just it flows like
275 00:27:45.600 ⇒ 00:27:47.070 Uttam Kumaran: you know, immediately out.
276 00:27:47.498 ⇒ 00:27:49.970 Uttam Kumaran: Right. This is a good example of like
277 00:27:50.430 ⇒ 00:27:55.310 Uttam Kumaran: you want to bring people in. You want to retain them, and you want your loss rate to be lower than your
278 00:27:55.990 ⇒ 00:27:58.610 Uttam Kumaran: your retention rate, that you’re growing.
279 00:28:00.670 ⇒ 00:28:00.985 Luke Daque: Yep.
280 00:28:02.260 ⇒ 00:28:03.230 Luke Daque: Makes sense.
281 00:28:04.570 ⇒ 00:28:09.799 Uttam Kumaran: Cool. That was my, that’s my Ted talk on subscription revenue. Yeah.
282 00:28:10.050 ⇒ 00:28:15.800 Luke Daque: Yeah, we can see that in the like, whether the revenue is going up or down, based on like, how many.
283 00:28:16.520 ⇒ 00:28:18.599 Luke Daque: how many subscriptions are coming in.
284 00:28:18.600 ⇒ 00:28:22.890 Luke Daque: Yes, so that’s the thing is they they’re not gonna know that.
285 00:28:23.200 ⇒ 00:28:28.219 Uttam Kumaran: Which is of the revenue coming in. How much is between new and returning.
286 00:28:28.600 ⇒ 00:28:35.750 Uttam Kumaran: and of the revenue going out, which plans are going out. Why are they going out? That’s the stuff that we’re trying to provide to them.
287 00:28:36.280 ⇒ 00:28:39.909 Luke Daque: Yeah, a a as well as the segmentation right? Based on the plan.
288 00:28:41.980 ⇒ 00:28:42.960 Luke Daque: Correct?
289 00:28:44.960 ⇒ 00:28:47.190 Luke Daque: Yeah. I I think that should be a good
290 00:28:47.330 ⇒ 00:28:51.719 Luke Daque: thing to like work on just a revenue for now. Mrr.
291 00:28:51.720 ⇒ 00:28:52.400 Uttam Kumaran: Yes.
292 00:28:52.670 ⇒ 00:28:53.630 Luke Daque: In.
293 00:28:54.370 ⇒ 00:28:58.040 Luke Daque: Yeah, I was initially thinking, maybe just one
294 00:28:58.740 ⇒ 00:29:04.089 Luke Daque: model for all the metrics like it would have the Mmr. Mrr. It would have the
295 00:29:04.390 ⇒ 00:29:09.480 Luke Daque: arr, and just a date spine. But yeah, it makes sense to like split them.
296 00:29:12.300 ⇒ 00:29:13.509 Uttam Kumaran: Yeah, I would split them.
297 00:29:13.510 ⇒ 00:29:15.440 Luke Daque: Insurance. Yeah, for example.
298 00:29:15.440 ⇒ 00:29:17.230 Uttam Kumaran: Totally, I would totally split them.
299 00:29:18.270 ⇒ 00:29:18.950 Luke Daque: Okay.
300 00:29:19.930 ⇒ 00:29:21.810 Uttam Kumaran: Cause the reason is, cause
301 00:29:22.280 ⇒ 00:29:27.080 Uttam Kumaran: yeah. Well, one, I I’ve done this a bunch of times, and you’ll always be asked to break it out.
302 00:29:27.480 ⇒ 00:29:30.719 Uttam Kumaran: so it’s helpful to break it out upfront.
303 00:29:34.570 ⇒ 00:29:35.380 Uttam Kumaran: Yeah.
304 00:29:37.350 ⇒ 00:29:38.400 Luke Daque: Okay, cool.
305 00:29:49.560 ⇒ 00:29:56.619 Luke Daque: I’ll probably just split the ticket. So that I created because it was like all metrics is, I’ll just create a sub
306 00:29:56.980 ⇒ 00:30:01.980 Luke Daque: ticket for just for Mrr and Arr, and then another for churn or something.
307 00:30:02.650 ⇒ 00:30:03.260 Uttam Kumaran: Okay.
308 00:30:06.720 ⇒ 00:30:15.349 Uttam Kumaran: I mean, but you can do that. We can calculate, I would say, focus on just having one cohesive sort of subscriptions or subscription revenue or
309 00:30:15.490 ⇒ 00:30:20.309 Uttam Kumaran: back subscription revenue table. And then we can build summary tables off of that
310 00:30:20.600 ⇒ 00:30:25.449 Uttam Kumaran: cause. Churn is basically how many people are going from active to inactive right.
311 00:30:25.740 ⇒ 00:30:26.420 Luke Daque: Yeah.
312 00:30:26.420 ⇒ 00:30:28.869 Uttam Kumaran: So you don’t need to separate that out necessarily
313 00:30:29.690 ⇒ 00:30:34.089 Uttam Kumaran: like I would try to keep as much dimensionality that shows a status change.
314 00:30:34.470 ⇒ 00:30:35.860 Uttam Kumaran: If you have a date.
315 00:30:36.290 ⇒ 00:30:46.640 Uttam Kumaran: and you have the status change, or ideally, you have, like subscribed at unsubscribed, at which is combining them, and you can do everything you can answer every question you want. Basically, you know.
316 00:30:47.560 ⇒ 00:30:48.290 Luke Daque: Okay.
317 00:30:59.810 ⇒ 00:31:01.080 Luke Daque: Sounds good.
318 00:31:04.420 ⇒ 00:31:12.189 Uttam Kumaran: Okay, thanks, guys, yeah. Let’s just hit me up with any questions. And yeah, Kyle, if you, if I need to review anything, let me know. I’ll be
319 00:31:12.360 ⇒ 00:31:16.630 Uttam Kumaran: sort of working on documentation. And yeah.
320 00:31:17.080 ⇒ 00:31:23.580 Caio Velasco: One good thing I I think I don’t have access to the even Github account just to drive these.
321 00:31:24.410 ⇒ 00:31:26.279 Uttam Kumaran: Oh, yeah, okay, I can get you that.
322 00:31:26.700 ⇒ 00:31:29.100 Caio Velasco: Cool perfect thanks.
323 00:31:30.320 ⇒ 00:31:31.919 Uttam Kumaran: Okay, thanks guys.
324 00:31:32.880 ⇒ 00:31:34.210 Luke Daque: Thanks. Guys, I’m just talking.
325 00:31:34.210 ⇒ 00:31:35.960 Uttam Kumaran: Soon bye.