total statistics grouped by date
select date, sum(delivered_count) as delivers, sum(clicked_count) as clicks from sender_firebase_statistics group by date;total statistics grouped by date and firebase id
select date, firebase_id, sum(delivered_count) as delivers, sum(clicked_count) as clicks from sender_firebase_statistics where date > '2024-07-27' group by date, firebase_id order by date;merge firebase statistics with subscribers data
Since we don’t have a correct number for subscribers and their firebase accounts on statistics service side, we need to hassle with this data on the directory service side. Previous query (total statistics grouped by date and firebase id) should be exported to a CSV file and then copied to any directory service pod. Then we can use the following script to merge this data with subscribers data.
from collections import defaultdict
import pandas as pd
from apps.subscribers.models import Subscriber
from django.db.models import Count, Q
firebase_group = 'new'
deliveries_filename = '/tmp/firebase_stat.csv'
date = '2024-09-01'
deliveries = pd.read_csv(deliveries_filename)
deliveries['date'] = pd.to_datetime(deliveries['date'])
deliveries['firebase'] = deliveries['firebase_id'].apply(lambda x: {622: '769b9c47-0513-485e-8b28-b0b7837d395e', 313: 'None', 754: '82280399-fb64-4ff4-bb44-901cfce5e5bf', 379: '8f864427-65bb-40c8-8427-17555c564f8e', 854: '4e712665-a666-4754-8672-f6d856773a17', 1156: '6315912e-f23d-42bb-a273-771945234b87', 1189: '754491c6-2818-4357-a238-6b97ab414524', 1359: 'fc7e62f4-6c61-422b-8832-ecd7367328e0', 1392: '5efb9889-a5c5-46ad-a979-6f78a150a7f9', 1426: '08e3b69d-aa7e-472f-9e3a-2976f156b7f7', 486: '0800d2d7-6ab0-42ae-aac8-361018d2d1cc', 1090: 'cad86237-1bc5-4be3-872b-0e68a753c88f', 1988: '7a2c3cc9-f348-49b5-bf8c-455805fa64ad', 382: 'b94d7aaa-85ab-4765-8ffa-63e4f12431a8', 1986: '5b6dd6e8-88f7-4e52-ad36-8060772b89c2', 1991: '884ddf24-b962-44e5-8f63-3fa708e5d01b', 1989: '9f6a81e7-6ab7-444c-8440-abc71c8c2319', 1987: '1e087e88-16a4-47de-a0dd-e692e1bf3b60', 1983: '666d0d92-37ff-4bfd-ad4c-fd2caa59ad1e', 1981: 'e971e931-6b3c-4ebe-8266-0ffc9a29f47b', 1534: 'c501fad0-3dd8-4d72-926c-60c52fe602e4', 2131: 'b02309e5-af2d-44f1-8231-c51b592bec16', 2196: '077af9ad-bd24-471c-90c2-71f9db6190d3', 2195: '6af9dee8-b2cc-42cc-ae3e-5826cd6a3e6e', 2194: '030bdf97-d628-4f97-a6dc-aa714196be4b', 2192: '15e18102-96c9-4231-abe8-dc94189c86ea', 2191: 'a688caa0-dad0-45cd-994f-d21cf13b44fe', 2190: '14b8e539-19cf-4111-8eab-80f76cef2d9c', 2128: '99b88bcf-0069-40db-b14f-0cd1b4641f69', 2129: '6296705a-4a45-4b48-96c5-b0560c9618b8', 2130: 'ab56c4fa-1721-4772-9e5b-a684959342da', 2132: 'ff21f8b0-2720-4e20-b9ee-8b58b8d0f8a0', 2133: 'de75c1c3-39e0-43da-934f-0e78ecb2d254', 1292: 'b0e28384-193e-46b1-8cca-12c2ff6e640d', 2134: '47b4e9bd-5cb2-4d5b-aacd-07ae142c3178', 2135: '3730fe87-9745-445e-aad6-2ac4f86ffcec', 2136: '96af31c7-01a1-4b51-9f73-0fa9144aee76', 2137: '68923585-29e4-43cf-97d1-e5161d49562b', 2138: '27713a6f-9ca9-413b-9114-8eb93f97da22', 2139: '121d6e07-64b0-44d3-aa48-0743cc16300a', 2140: '6c276e22-f125-4aa7-bf93-e95a7ed75096', 2141: '58c1e485-bae1-4e03-a463-ca36c1009201', 2142: '58bfa5bf-d0fa-4f2f-a7fa-008b6a36645c', 2143: 'd8d4bcd3-7e74-4fd6-b8a4-ff8879bcff9d', 2144: '15c140bd-9f19-4a94-8b97-55f851eadf40', 2145: '88ac275e-c874-4fef-8393-bf16543b813b', 2146: 'ecc3cbca-e880-4c88-878a-082020e8eeb2', 2147: 'ce4c1f05-0b2f-435b-8612-65766509937e', 2148: '33caddea-d0e5-4a2b-82ec-e5498dd648cd', 2: 'None', 68: 'None', 2149: 'bdd26605-fcf7-4972-bd2a-694876174470', 2150: 'c5f0ca97-3e42-4488-b4ed-3d4c004ed14a', 2151: '731f7353-648b-46b2-91d8-0296e1295728', 2152: 'fe3e5630-355b-4d89-9299-e29df70e5d1d', 2153: 'fe18ffc7-c234-4c81-9a51-2621699f0e5a', 2154: 'b0c8c359-e746-4205-ad95-eb63f83dce88', 2155: 'e07cf62e-5ffe-4885-b3c9-abc9cd3c709d', 2156: '90fa44e8-a0de-40d7-8595-7a6ae5cee38b', 2157: 'c59874a7-c7f4-472f-8325-674215c950a1', 2252: '83ddb47d-f697-4078-aca2-b7588c0251bd', 2253: '7944a982-0422-4102-a65f-14089056d576', 655: '272cc930-5202-43d4-aceb-11cf11a899b5', 519: 'None', 1: 'None', 35: 'None', 69: 'None', 102: 'None', 103: 'None', 346: 'None', 688: '4a29043d-974f-4751-a339-5203cb06b82e', 721: '7616ccdb-93a9-4c58-9b89-d9c06e05c655', 820: 'b1f52d73-6881-4cb4-b05a-ff0120312c2f', 416: 'None', 449: 'None', 145: 'None', 853: 'd68a5a43-43b8-4679-8985-56a9604d48cb', 450: 'None', 180: 'None', 552: 'None', 213: 'None', 246: 'None', 887: 'c68899df-eeb2-47d7-851b-ec1bc2453d4a', 954: 'ab378009-ebc0-4e87-b035-2bd74baccd5e', 987: 'aa75067a-f59c-4594-8b75-8e468aef7e81', 280: 'ff7d83d0-206a-4870-b183-1f18fa52267f', 787: 'c288dcf0-64c8-44cf-bfe8-d3df1de42abe', 920: 'bb135a16-6afe-414e-b487-7a4507591a04', 381: '607f857e-b88c-4a03-8d1b-ef9063ef9b32', 483: '7c08a5b7-c956-427b-ab72-7f923feeb9da', 1056: 'c29cae5d-c013-4bcf-9655-55c4c9453b00', 1089: '54190ead-a8e1-4425-8a04-15f66ddb66c8', 1123: '2f02d804-0128-491f-9718-1ac0204f5e9a', 587: '01ddfd58-c4ff-40a6-ad23-a92209a4e250', 1222: '566bff06-aa18-434a-960b-686cf79a8b5b', 1224: 'cf412eec-f813-4eba-b1e4-f4b160f60d3a', 1257: 'a8f478ae-a4f1-4df1-9e01-0263e069718b', 1259: '8ec1341b-97ff-4c8f-931e-a7722004258e', 1325: '1360147d-387f-4a54-8c0e-df1383196b21', 1459: '144dd56c-f5b2-4e60-9ae4-996ea6764889', 380: 'e4bef023-2296-4ffe-aa3c-40a20baa20e9', 484: '912f6bbf-67e4-4a09-b393-37d025db852d', 383: 'fb45a413-dd01-4af7-ba7c-38d47c872d8d', 279: '8ac6a3cf-429c-4a28-af6c-d315a473749c', 589: '33f29890-61b3-42f6-bf9b-8976e8c6bc59', 586: 'c4f16a90-516c-417e-afef-5f561c79b52e', 588: '84fd8643-1a4e-4b85-adb1-83211e054e84', 921: 'f2fb4e90-00d1-487f-a8a8-5bce9be459f4', 1495: '0be6c5ac-4490-467b-8ad6-201596cfb8b3', 1023: '04a4177e-d2a0-4372-bd59-0a417597e5a3', 485: '6883dbde-4c62-4e80-8642-6114f3e94878', 1223: '95f13d00-1aec-4f44-b26e-443f8eaa6fc2', 1258: '495b5fd5-8dd1-46d3-818d-f3ef677ba03f', 1326: 'dc3c89c9-7a9b-427d-81ec-785be4f2d79b', 1393: '1d80e5ad-4838-4a6b-a97f-e0e013b6375f', 1460: 'b5e0b785-1c19-4ff8-a007-8627ca1753dd', 1496: '135e0bd3-49a4-41c4-827b-7b1c02d3d409', 1497: '4c5c9a9b-2449-44c5-9d3b-4566bde47d61', 1498: '828fd7d9-e083-48ea-8adf-5e84eaaadecc', 1499: 'b96892f2-4c45-4be5-a2bd-155ee886d835', 1567: '894e4827-ec9c-494e-80c2-22d1dbf9646b', 1600: '563740fc-c3e7-44bc-8032-676ad2012d89', 585: 'ceaf81d3-308f-4c9c-a996-6aaca837666b', 1601: '7a80a587-c92a-4537-b443-5a4526127cef', 1602: 'a2ed8fac-5447-49a8-a0af-86a0a6658a37', 1670: '9e5550b4-820c-484e-aeb5-dd88bc420406', 1703: '43c699a1-d35a-4eb0-93e3-2810eadd7c13', 1704: '1fa6c2d5-dea0-499e-b2c8-400514ac1668', 1737: '8f7e4a3d-821d-43c1-9351-5266877f5141', 1770: '65525ed0-5ee6-4174-92e9-eace10f4b374', 1803: '2428a5e1-b5dd-4f8f-98e6-a14e2667b8da', 1804: 'a4e0efc0-8e14-4f9a-afdf-9e8d07ce6207', 1805: '83da6913-b535-4e9b-a379-ee70cb12dc6a', 1838: '3bcd263d-4205-4264-8c00-2f446897e002', 1871: '54d35de4-c78f-4cd1-b478-63b2af449b22', 1904: '9f2477bf-bc99-4477-9d60-99fbc9daa5c7', 1905: '35fe823e-ae78-4067-909f-b7cac5dba53d', 1906: '85327f86-0e07-46f6-b9a4-5179d9430de1', 1907: 'e201547b-c30e-48c5-81cb-a29fd6e943ba', 1908: '3537fcb1-93f8-487b-9806-64cbbef26508', 1909: '93ea4e78-2392-45b3-bec2-87f8c254c601', 1910: '6729484b-4b31-4e65-a909-6659ae4cf2c6', 1911: '11af2edb-e165-4701-98ed-e81bad83bede', 1912: 'c41dbddf-1dcf-4433-b1a2-5155e663496e', 1913: '100501b4-a6f5-4e8d-adab-5d85c4817ce6', 1914: '72257bbd-dd5e-4771-a18c-b27441c43a66', 1947: '93d9f5ca-4b08-447a-9bcc-463205457736', 1980: 'a7a7b147-e8c5-4102-959d-ad89e4b7c391', 1982: 'c364884c-f400-4190-8d47-95988fcc70ae', 1990: '7ab19028-b854-4f0a-8d03-9a33aa2ec72a', 1992: '0ce740a3-5ee4-4e5f-8f88-da282886f863', 1993: 'de934da7-d83a-4fc0-8821-63978b2ee3e2', 1994: '4e6147be-d99f-4fcb-a7ab-199a46312c78', 2027: 'dbcc260b-d114-4163-ae3e-370d297fea32', 2028: '20208cbb-460d-4156-922e-bb7d42992180', 2029: '1e87e6fb-03cc-4dfd-9b43-35f2498a633f', 2062: '43d8a871-e82b-4049-8651-8a46c9874c54', 2095: 'cc007b0c-f9d7-45c8-89f7-05b4d9f3c252', 1984: 'c9033a11-ad3f-4101-9350-47cab8440249', 1985: '7f60c391-2e35-4d41-8428-fcc230186aa8', 1022: 'f1eaf8fc-d63b-439c-a601-f443ffa42a80', 1494: '83eb897d-34f6-4330-9c80-9a348f5de4e1', 1637: '6fd348c7-f4df-467a-b541-9e164200e0ee', 2219: 'ef28094d-e4cd-4f20-86af-b2770349289d', 2218: '1ec74174-b527-4c49-bdbe-99697f60cc2a', 2217: 'a6acd279-c072-4768-ad74-f6c9bdfa07ad', 2216: '7b693832-b38c-489e-87ca-508af2ab0250', 2215: 'a85ed369-15ec-4f7d-a642-064b70cb5bd4', 2214: '0a8aa6a9-c737-4c1b-ba13-4e3fa684d1b7', 2213: 'd11c1a42-c0e8-4c62-b47f-016b08f1a574', 2212: '0c635449-30a1-474c-9973-a89a4e557e31', 2211: '241d1bde-591c-4b32-82ea-f01df91b7620', 2210: '1aced71a-5281-4dd8-b88c-e1b91ecabf84', 2209: 'bd230774-d4e9-4206-aeea-457f54f04c08', 2208: 'd99fc137-a213-4dc7-882c-52ebcb718864', 2206: 'cc9c3753-aeef-4c91-b389-c0ae34c3dd5c', 2205: '4b2ada53-2ea0-477c-a00d-1c7400d0b33c', 2204: 'd5ae3a45-ddb5-4f4c-865b-ada15f69c185', 2203: '8f3b586f-a79f-4fdc-be5a-36a55cf49f71', 2202: '054cee4c-c1c0-4fcf-aa83-cfa42e0dff16', 2201: '1d649708-0866-41c0-9567-251504d53652', 2200: 'df399c2c-1668-4f61-895f-46093d53f31a', 2198: '6532079c-af87-4d9c-beeb-64983fbc0403', 2197: 'da8a4e80-a8be-4c7f-9be5-cf8032a982d3', 2207: '50087019-895c-4556-ba74-bdc931e76a05', 2199: '8990aa01-07a2-4728-ad45-eb43a3d49d21', 2193: 'b67cbdd8-efce-4ade-9cce-0aa679421428'}.get(x))
deliveries.drop(columns=['firebase_id'], inplace=True)
firebases_groups = {
1: ['b02309e5-af2d-44f1-8231-c51b592bec16', '99b88bcf-0069-40db-b14f-0cd1b4641f69', '6296705a-4a45-4b48-96c5-b0560c9618b8', 'ab56c4fa-1721-4772-9e5b-a684959342da', 'ff21f8b0-2720-4e20-b9ee-8b58b8d0f8a0', 'de75c1c3-39e0-43da-934f-0e78ecb2d254', '47b4e9bd-5cb2-4d5b-aacd-07ae142c3178', '3730fe87-9745-445e-aad6-2ac4f86ffcec', '96af31c7-01a1-4b51-9f73-0fa9144aee76', '68923585-29e4-43cf-97d1-e5161d49562b', '27713a6f-9ca9-413b-9114-8eb93f97da22', '121d6e07-64b0-44d3-aa48-0743cc16300a', '6c276e22-f125-4aa7-bf93-e95a7ed75096', '58c1e485-bae1-4e03-a463-ca36c1009201', '58bfa5bf-d0fa-4f2f-a7fa-008b6a36645c', 'd8d4bcd3-7e74-4fd6-b8a4-ff8879bcff9d', '15c140bd-9f19-4a94-8b97-55f851eadf40', '88ac275e-c874-4fef-8393-bf16543b813b', 'ecc3cbca-e880-4c88-878a-082020e8eeb2', 'ce4c1f05-0b2f-435b-8612-65766509937e', '33caddea-d0e5-4a2b-82ec-e5498dd648cd', 'bdd26605-fcf7-4972-bd2a-694876174470', 'c5f0ca97-3e42-4488-b4ed-3d4c004ed14a', '731f7353-648b-46b2-91d8-0296e1295728', 'fe3e5630-355b-4d89-9299-e29df70e5d1d', 'fe18ffc7-c234-4c81-9a51-2621699f0e5a', 'b0c8c359-e746-4205-ad95-eb63f83dce88', 'e07cf62e-5ffe-4885-b3c9-abc9cd3c709d', '90fa44e8-a0de-40d7-8595-7a6ae5cee38b', 'c59874a7-c7f4-472f-8325-674215c950a1', '83ddb47d-f697-4078-aca2-b7588c0251bd', '7944a982-0422-4102-a65f-14089056d576'],
2: ['14b8e539-19cf-4111-8eab-80f76cef2d9c', 'a688caa0-dad0-45cd-994f-d21cf13b44fe', '15e18102-96c9-4231-abe8-dc94189c86ea', 'b67cbdd8-efce-4ade-9cce-0aa679421428', '030bdf97-d628-4f97-a6dc-aa714196be4b', '6af9dee8-b2cc-42cc-ae3e-5826cd6a3e6e', '077af9ad-bd24-471c-90c2-71f9db6190d3', 'da8a4e80-a8be-4c7f-9be5-cf8032a982d3', '6532079c-af87-4d9c-beeb-64983fbc0403', 'df399c2c-1668-4f61-895f-46093d53f31a', '1d649708-0866-41c0-9567-251504d53652', '054cee4c-c1c0-4fcf-aa83-cfa42e0dff16', '8f3b586f-a79f-4fdc-be5a-36a55cf49f71', 'd5ae3a45-ddb5-4f4c-865b-ada15f69c185', '4b2ada53-2ea0-477c-a00d-1c7400d0b33c', 'cc9c3753-aeef-4c91-b389-c0ae34c3dd5c', 'd99fc137-a213-4dc7-882c-52ebcb718864', 'bd230774-d4e9-4206-aeea-457f54f04c08', '1aced71a-5281-4dd8-b88c-e1b91ecabf84', '241d1bde-591c-4b32-82ea-f01df91b7620', '0c635449-30a1-474c-9973-a89a4e557e31', 'd11c1a42-c0e8-4c62-b47f-016b08f1a574', '0a8aa6a9-c737-4c1b-ba13-4e3fa684d1b7', 'a85ed369-15ec-4f7d-a642-064b70cb5bd4', '7b693832-b38c-489e-87ca-508af2ab0250', 'a6acd279-c072-4768-ad74-f6c9bdfa07ad', '1ec74174-b527-4c49-bdbe-99697f60cc2a', 'ef28094d-e4cd-4f20-86af-b2770349289d']
}
## start of calculations
firebase_groups_mapping = {}
for group, firebases in firebases_groups.items():
for firebase in firebases:
firebase_groups_mapping[firebase] = group
query_filter = {}
exclude_filter = {}
firebase_subscribers_start = {}
for i in Subscriber.objects.filter(
Q(subscribed_date__lt=date) & (Q(unsubscribed_date__gte=date) | Q(is_active=True))
).values('firebase_app').annotate(count=Count('id')):
firebase_subscribers_start[str(i['firebase_app'])] = i['count']
# now let's make a query and calculate subscribers increment for each day grouped by firebases
query = Subscriber.objects.filter(subscribed_date__gte=date)
temp = defaultdict(int)
for s in query.values('subscribed_date__date', 'unsubscribed_date__date', 'firebase_app'):
temp[f'{s["subscribed_date__date"]}_{s["firebase_app"]}'] += 1
if s["unsubscribed_date__date"]:
temp[f'{s["unsubscribed_date__date"]}_{s["firebase_app"]}'] -= 1
subscribers = pd.DataFrame([{
'date': k.split('_')[0],
'firebase': k.split('_')[1],
'count': v
} for k, v in temp.items()])
subscribers['date'] = pd.to_datetime(subscribers['date'])
subscribers['firebase'] = subscribers['firebase'].astype(str)
subscribers.sort_values(by=['date'], inplace=True)
subscribers.reset_index(drop=True, inplace=True)
# i want to accumulate subscribers. i need to make it by firebase group
for firebase_app in set(subscribers['firebase'].unique()):
start_count = firebase_subscribers_start.get(firebase_app, 0)
subscribers.loc[subscribers['firebase'] == firebase_app, 'count'] = subscribers.loc[subscribers['firebase'] == firebase_app, 'count'].cumsum() + count
subscribers['firebase_group'] = subscribers['firebase'].apply(lambda x: firebase_groups_mapping.get(x, 0))
# merge subscribers and deliveries dataframes
result = pd.merge(subscribers, deliveries, on=['date', 'firebase'], how='left')
result['delivers_per_sub'] = result['delivers'] / result['count']
result['delivers_per_sub'] = result['delivers_per_sub'].round(0)
result.to_csv(f'/tmp/firebase_subs_deliveries.csv', index=False)