Imagine that I have a dataset about sending messages. Each row as
is_open field (boolean) and a
is_clicked field (boolean).
So one row means that one message was sent. It might have been open (is_open is true) or not (is_open is false). Same for clicked.
For this question, all corner use cases (what if a message is clicked without being opened?) are not relevant.
I want to graph open rate vs. click rate.
How can I group these rows in a valid way, without discarding most of them?
The crux of my problem is that every single message has an open (and click) rate of exactly 0 or 100%.
I could first group messages per user, but then I would have to discard users having received less than at least 5 or 10 messages, to not have a peak a 0/20/40/60/80/100 %. This is a lot of data to drop, which is perfectly valid (and furthermore, I would like to compute things like median time to open, which does not lend itself well to multi-step calculation). It would take as well a while to get have enough historical data.
I could group by batch. But I could have for instance one batch per month, of 500k users. After a year, I would only have 12 points on my graph, whereas I already sent 6M messages.
My naive idea would be to just take rows by bunches of eg. 1000, and compute the open and click rate for this random bunch. It does not seem intellectually correct to me.
The actual language/implementation does not matter. I want to understand how to do this, actually doing it will come later.