Subscribers: new and deleted count metric aggregated per day

[FilipNikolovski]

No description provided.

[gudgl]

@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

2. 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

3. Store each event (subscribe/unsubscribe)

id	user_id	subscriber_email	event_type	created_at
1	1	[email protected]	subscribed	2021-03-01 15:04:05
2	1	[email protected]	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

[gudgl]

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

[djale1k]

@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]

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

[FilipNikolovski]

Event types:

• create
• delete
• unsubscribe

[gudgl]

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]

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

[FilipNikolovski]

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.