I'm developing a backend REST app using AWS API Gateway that is connected to AWS Lambdas and stores transactional data in AWS RDS MySQL DB. I need to calculate a certain set of business metrics like DAU, MAU, monthly churn, avg. number of interactions per user a day, etc., etc. Ideally, I should have something like a real-time dashboard with all of these metrics. Thus I have a question of:
- Where and how to store the data of users' calls of backend APIs?
- How to process the collected logs and calculate the metrics that I need?
- How to make all of the above as cheap as possible?
I have a couple of options in my head:
Create a RDS DB and store details of every backend API call in it (like timestamp, user_id, API_name). Then in order to calculate the metrics use SQL queries and potentially some simple business logic. Drawbacks of this option: introduce additional latency to my backend since it would take additional time to record logs in the DB; the RDS DB is not free
Store details of every backend API call in CloudWatch logs. In order to calculate the metrics download the csv dump of the logs and use Excel or other data-mining tool to calculate the metrics. Drawbacks of this option: includes manual step of downloading the logs which introduce human-factor risk and is time-consuming.
Any advice on the solution architecture is much appreciated. I'm pretty sure there's a best-practice for it - just couldn't find it. It's not a high-load project, so quick and dirty solutions are welcome!
