Shane Gibson and Nigel Vining explain the activity event pattern, a compact append-only table that stores business key, activity type, timestamp, and optional financial value. A hydration layer transforms these rows into metrics like first/last seen, counts, and revenue deltas, keeping reports real-time without staleness. The pattern excels at sequencing and funnels, runs fast when clustered, but breaks when you try to mix multiple domains into one row.
Chapters
Foreign.
Welcome to the Agile Data Podcast where we talk about the merging of Agile Pro product and data patterns in a simply magical way.
Welcome to the Agile Data Podcast. I'm Shane Gibson.
And I'm Nigel Vining.
Hey Nigel. Onto the next Agile Data Engineering Patterns episode. This time we're going to talk about activity events. For anybody listening, if you haven't listened to the previous episode of Data Engineering Patterns where we talked about our event tiles, I suggest you go and listen to that first because we're going to assume you listened and understood that before we move on to this one. So, activity events, it's a slightly different pattern that we use for our event tiles compared to our standard pattern. So, Nigel, why don't you explain to me what it is and why I care.
Sure. So the previous one we talked about events where we basically insert a row of keys into our event table, like customer orders, product. And that's cool because those three things are lined up at a point in time and we report on those. The activity events are a little bit different because effectively it's like a stream of things, things happening that we care about. Subscription is a really good example of where the activity events shine because we've got this basically a whole lot of events coming in where subscriptions are created, subscriptions are paid, subscriptions are renewed, subscriptions are cancelled. And generally the type of reporting that businesses do around subscriptions is how many new subscriptions did I get the this month compared to last year? My number of subscriptions going up or going down? Is the amount of revenue from my subscription static, increasing, decreasing? So there's all these really useful questions that the activity schema can answer. So the difference with our rule template for activities are really simple. All we capture is what the activity is, the business key for the subscription. In this case, basically a nice clear description, like I said, subscription created, subscription paid, subscription cancelled. We effectively describe what it is. We also assign the financial value to each activity. Some activities don't lend themselves to a value. It may literally be a one that something happened compared to a dollar amount where a subscription payment is received. And that's where we would have that amount. So we can track those over time. And of course the effectively the business date of when this activity occurred. It's really quite simple. We just create those and we just keep appending them into that table. The magic happens in the hydration layer. So we take that list of keys attached to activities and we hydrate them into a consume tile. And at the same time we include a list of standard Metrics into that view that we calculate on the fly because we're looking across time periods of data. So we automatically have things like is this the first subscription created event we have seen? Is this the last subscription created event we've seen? How many occurrences of subscription created have we seen? Are they repeated? What is the revenue impact of this subscription paid activity? Is the value going up or is the value going down when we effectively put it against the previous payment for that subscription? And what is that value change? What is the opening balance? If we add up all the financial impact values, what's the opening balance of those over time? What's the closing balance of those over time? Although very simplistic, each of these has a relatively straightforward windowing function, but we apply those automatically on our activity data. So you don't care about it, you just throw your activities in and a consumptile will turn up and all those metrics are there. You can plug it straight into your last mile tool and you are immediately reporting by the health of your business based on its subscriptions.
The key for this one is we were building up some reporting for software as a service company that needed to do all the pirate metrics and all that kind of stuff. And what I was forced to do is create a bunch of events. So I'd say that a subscription was created, a subscription was paid, description was renewed, description was canceled using our standard event table. But then to be able to work out the between time or the between dollars for those events, I had to write really horrible SQL to slam those events together and then doing all those windowing functions. So what I ended up doing was slamming them all together and then doing all the windowing functions and calculations of the last mile to all of the the BI tool, which means that none of those calculations were repeatable or reusable in other reports. So by introducing this new pattern, I can effectively now go pick that data up and I can say what's the movement between months really easily. How many subscriptions were created this month versus last month? How many subscriptions were created three months ago and then canceled this month? What's the total value of cancellations this month? They're all really simple because I just dragged those calculations or those columns onto my report and the back end does all the heavy work. And so that's one of the things that is quite important. Those calculations are effectively views. So we're not pre storing every combination of every metric. Each column is effectively being generated or calculated on the fly. When I grab it into that last Mile tool, correct?
That is correct, Yep. Because we always want those metrics to reflect the reality in real time based on our last feed of subscriptions, because it's moving all the time and not moving historically, but we might be comparing to historic. So by doing it as a view, it really doesn't cost us a lot of extra and it makes sure we never persist a stale value.
The other thing is we are effectively storing the idea of an event happening twice because we will still have the event in our standard event tile. And then we're storing another version of it effectively in the activity event tile. But the data is minimal because we're holding a very small number of columns that are effectively just holding keys or time or dollar values. Correct?
Yeah. The activity event table is tiny. It is literally the business key, what the activity was, the value associated with it, and when it happened, literally four or five columns, minimal. It's nothing. It's when we hydrate it and we go wide with the attributes and the metrics. But the actual activity table we can put squillions of records in and that's all good. We cluster that one based on the activity types and the dates around that. Because generally all of our calculations and metrics are all based around when something happened and what it was. So by clustering on those fields, it means that the queries are actually very cheap because we say we know where all the subscription created records are in the table and effectively all the dates are ordered within that. So any of the calculations automatically based on clustered data. So it's fast and cheap.
The other thing it allows us to do is deal with those horrible sequencing problems. Subscriptions created, subscriptions paid, subscriptions canceled, subscriptions renewed. Right. It's reopened. The person has come back after six months and turn that subscription back on. And so we don't actually have to hard code the sequencing of the activities because those activities come from the data. But what it means is we can see when activities happen out of the normal order we would have expected because they're turning up as something that happened that we didn't expect. And that's really important. But we get that for free because effectively an activity happens and activities inserted. And now I can see a state change for that subscription when it happened and what the dollar value, the impact of it was. And that saves a lot of complex SQL querying in your last mile tool. You have to answer those type of questions.
Yeah, because we keep track of what the previous activity is for every record. So we know if something's happened, we Also give you the previous one for free because then you can say that they went from, as you said, canceled to paid. That's an anomaly. And that's really easy to pull out because you shouldn't expect to see that. But we'll show you that it's happened.
Which means if I want to create a funnel report, it's really simple because I have the last seen activity and the current activity and that's data. And therefore most of the reporting tools will be a pretty funnel with very little need for me to do a whole lot of SQL gymnastics.
Absolutely, that's exactly what's happening.
So what we've done though is we've iterated a version of activity schema modeling.
Yes.
So we've taken the bits that work for us. The idea of having a core concept that's binding as the core concept for this activity, event tiles, in this case, subscription. The idea of having activity types and having a row every time we see an activity type happen for that subscription. The idea of holding a date and a binding financial metric or value which we need to report off. The idea of pre calculating or holding all those complex calculations of betweens and increases and decreases and last seen. But what we haven't done is gone to the next step, which is have the ability to hold multiple other concepts. And that's because when we looked at the activity schema pattern, it didn't deal with that particularly well. It was effectively an anti pattern that they bushed in originally what it said was you had two extra columns and that kind of didn't work. Then they started JSON stringing all those other concepts in there, so you put them in and unparse them. And again, that didn't work for us. So what we took is the things that worked for us to solve that one particular problem. It's given us a whole lot of value for a bunch of other use cases. But we haven't picked up that last part of the pattern because in our view it doesn't actually solve the problem that we need to solve for that use case.
Yeah, bundling users, customers, subscriptions into the activity doesn't make a lot of sense because effectively the reporting becomes a little bit skewed from it because they don't really go together. The three activities that are happening side by side, they're related, but to key them together is problematic.
Yeah. So we'll probably solve that another day, but not today. All right. And on that note, I hope everybody has a simply magical day.
And that Data Magicians was another agile data podcast episode. If you want to learn new patterns that you can apply to craft your own Agile data way of working, head over to agiledata IO.
