What is the best way to keep track on tables or some data for analytics?

Question

For an instance, take django model classes as below.

So, now the question is that how can I efficiently implement analytics for generating number of product sales per day, number of sales per month, number of profit by day and so on ?

One Possible solution is on every sales update counts of that particular day. But if I have very large customer base (i.e 1M customer and 500 sales per hour).

So is there any better way to achieve this?

These schemas are just for reference only

class Product(models.Model):
    name = models.CharField(max_length=20)
    category = models.ManyToManyField("Category", null=True)
    ...
    ...


class Sales(models.Model):
    product = models.ForeignKey("Product")
    price = models.DecimalField(max_digits=20)
    timestamp = models.DateTimeField()
    ...
    ...

Answer 1

this is usually done by service like google analytics.

You can find the doc here: https://support.google.com/analytics/answer/1009612

Answer 2

Create a Model SalesStatistics which store the count of sales every predefined interval. If hourly, daily and monthly statistics are required, storing hourly statistic should suffice.

There are two ways simple of achieving this -

1. Write a cron job that runs every hour, calculate the number of sales in an hour.

id,   sales_count, timestamp
__________________________________
1231  304          "2 pm, 21st April 2019"
1232  443          "3 pm, 21st April 2019"

You can retreive hourly, daily or monthly easily from this using any RDBMS

MYSQL offers a vast number of date time function to making querying time series data easy. https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

2. Use a builtin event schedulers in databases. In databases, you can schedule to run a query at some time intervals and the store the result in a table. [MySQL Event Scheduler][1]