I am currently working on query in which I want to calculate how many customers placed an order with us historically but not in the current month
The final output should look like this
| Month | Churned_Customers |
|---|---|
| January | 23 |
| Feburary | 32 |
By number 23 in the first row I mean all those customers which placed atleast one order till december but not in the month of january and by 32 I mean how many customers placed atleast 1 order till january but no in feburary
Currently the data I have is customer_id, delivery_date (datetimestamp_format),order_id I am using Google Bigqeury platform.
Till now I just came up with this
DATE_TRUNC((delivery_date),MONTH) AS Month, customer_id, COUNT(DISTINCT order_id) AS orders FROM orders WHERE LOWER(order_status) = 'delivered' GROUP BY 1,2 )