mailbadger/app

Subscribers: new and deleted count metric aggregated per day

FilipNikolovski opened this issue · 8 comments

FilipNikolovski commented
Subscribers: new and deleted count metric aggregated per day
gudgl commented

@Dzalevski @FilipNikolovski So far these are the tree proposals

  1. Aggregate daily subscribers (subscribed and unsubscribed calculated)
id user_id subscribers date
1 1 23 2021-03-01
2 1 13 2021-03-02
  1. Aggregate daily subscribed subscribers and unsubscribed subscribers
id user_id subscribed unsubscribed date
1 1 23 18 2021-03-01
2 1 13 15 2021-03-02
  1. Store each event (subscribe/unsubscribe)
id user_id subscriber_email event_type created_at
1 1 emailone@mail.com subscribed 2021-03-01 15:04:05
2 1 emailtwo@mail.com unsubscribed 2021-03-02 15:04:05

and than count them maybe in view or smth

id user_id subscribed unsubscribed date
1 1 23 18 2021-03-01
2 1 13 15 2021-03-02
  • 👍1
gudgl commented

If we go with the 3rd way we need to make change with unsubscribe_events table

djale1k commented

@gudgl unsubscribe_events it's user action table
this will be admin action if we change that table maybe we will need to know which user (user/admin) made that event @FilipNikolovski

FilipNikolovski commented

There is a difference between unsubscribe and a delete. We'll need to discuss this further in a meet.

FilipNikolovski commented

Event types:

  • create
  • delete
  • unsubscribe
gudgl commented

Here's what I've found so far:

  • Using triggers - you can set triggers on the source tables on which you build the view. This minimizes the resource usage as the refresh is only done when needed. Also, data in the materialized view is realtime-ish
  • Using cron jobs with stored procedures or SQL scripts - refresh is done on a regular basis. You have more control as to when resources are used. Obviously you data is only as fresh as the refresh-rate allows.
  • Using MySQL scheduled events - similar to 2, but runs inside the database
djale1k commented

@FilipNikolovski @gudgl Awesome! Let's discuss this further on call

FilipNikolovski commented

Here's what I've found so far:

* Using triggers - you can set triggers on the source tables on which you build the view. This minimizes the resource usage as the refresh is only done when needed. Also, data in the materialized view is realtime-ish

* Using cron jobs with stored procedures or SQL scripts - refresh is done on a regular basis. You have more control as to when resources are used. Obviously you data is only as fresh as the refresh-rate allows.

* Using MySQL scheduled events - similar to 2, but runs inside the database

This is great. Before we discuss we can also take a look at this article about "Summary Tables". It describes different approaches with examples, maybe we can draw some inspiration from there as well.

  • 👍1