Nashpush

subcsiption-degradation

2 min read

In case there is a concern about the subscription degradation, the following steps should be taken:

  • API availability and response time
  • Firebase subscription errors
  • Visits-to-subscriptions ratio
  • An explicit firebase account drop check

Visits-to-subscriptions ratio

We check the ratio of subscriptions (user accepted push notifications prompt and was succefully subscribed by our system) to visits (code snippet was loaded and the request was sent) for the last 7-14 days.

Table to get page visits - page_events (visit_counter column)

SELECT 
    toDate(toDateTime64(timestamp, 0)) AS day,
    sum(visit_count) AS daily_visits
FROM 
    statistics.page_events
WHERE 
    day >= today() - 14
GROUP BY 
    day
ORDER BY 
    day ASC;

Table to get subscriptions - channels_stat_daily (subscribe_count column)

SELECT 
    toDate(datetime) AS day,
    sum(subscribe_count) AS daily_subscriptions
FROM 
    statistics.channels_stat_daily
WHERE 
    day >= today() - 14
GROUP BY 
    day
ORDER BY 
    day ASC;

API availability and response time

We check the availability and response time of the following APIs:

Endpoints are being monitored by the Better Uptime service.

Firebase subscription errors

These errors might occur for a number of reasons. We store them in the firebase_subscription_errors table. For the sake of this checklist, it should be enough to get a total count of errors for the last 7-14 days (in a daily representation)

SELECT 
    toDate(toDateTime64(timestamp, 0)) AS day,
    count() AS error_count
FROM 
    statistics.firebase_subscription_errors
WHERE 
    day >= today() - 14
GROUP BY 
    day
ORDER BY 
    day ASC;

An explicit firebase account drop check

Main idea is to find out whether it is a significant drop in the number of subscribers for a particular firebase account. We should derive this information either from the subscribers (subscribers) PostgreSQL table or from the sender_firebase_statistics clickhouse table (subscribe_count and firebase_id columns)

Graph View