I did the same research last year, we are heavy users of BigQuery, tera bytes of data per day, and we also tried use it for realtime analytics, but yes, currently, BigQuery isn't usefully for realtime usage, it is too slow, I know the team in google rewriting the BigQuery engine and it can be soon optimized and faster, so worth checking it later...

Big table is useful when you need to access by key. But if your queries aren't key based you don't need it. Also the date range queries are based on the key+time expression. You didn't mention if you need access by key or not , so I'm just mentioning it. Also be aware that it very expensive solution compared to other DBs you have in GCP.

If you are most reading, and have small volume of data you can check if sime cloud SQL+ read replicas (to speed up the reads) can work for you.

We use for our realtime analytics Druid db, which is very fast and optimized for aggregation queries with multiple dimensions. It is adopted now by many tech companies.