Imagine that I have a dataset about sending messages. Each row as user_id, a batch_id, a 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?

Long version

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.