Currently I was doing this:
I have a table called tracking_log
where I am inserting a row whenever a user is using our app. We use this table to calculate users that are sticking with us and continuing to use the software.
This was working OK. But now I also want to see the number of active users for:
- each day since launch
- week-wise since launch
- month wise since launch.
The table structure is something like:
tracking_log
- user_id (integer, pk)
- when_used (timestamp)
- event (event-type triggered by user. Kept for future usage.)
Our definition for "Active Users On a Day d1": users who had signed up earlier than (d1-15) days and has used the product within (d1-7) days.
The tracking_log table has around 500K records and counting. I was writing MySQL queries to calculate the above numbers but they are turning out to be very slow.
What is the best way to implement it? Are there any existing solutions to generate such reports with less effort?