KPI Summary Dashboards Alerts

update of bq table contact_states failed

There are 2 things which could have failed:

  1. the update of the states table ems-mobile-engage.mobile_subscription.contact_states`in BigQuery which is done by scheduled query `contact-states-update, run every day at 3:00 AM UTC

  2. the script which checks if the new partition of the states table exists failed, which is server/processes/scripts/run-check-bq-contact_states-update.js in push-notification-service, scheduled every day at 11 UTC

To find out if the script has run correctly, check the history of the scheduled query in BigQuery. You can additionally check for the new partition by issuing (with date set to today):

SELECT COUNT(*) AS num
FROM `ems-mobile-engage.mobile_subscription.contact_states`
WHERE DATE(_PARTITIONTIME) = CURRENT_DATE() -- today

Run this query also for yesterday, the number of today should be roughly the same like yesterday.

Additionally there should be some entries with event time of yesterday in the partition of today:

If the scheduled query ran correctly and the new partition exists with roughly the same entries as yesterday there should be new logins and new activity events (correct the dates if not for today):

SELECT
COUNT(IF(last_login_at > TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)),1,NULL)) AS new_login_num,  -- yesterday
COUNT(IF(last_activity_at > TIMESTAMP(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)),1,NULL)) AS new_event_num  -- yesterday
FROM `ems-mobile-engage.mobile_subscription.contact_states`
WHERE DATE(_PARTITIONTIME) = CURRENT_DATE() -- today

If new_num is also reasonable, the "contact-states-update" scheduled query ran as planned.

If the script didn’t run (or the number of entries for a given day seems suspicious): . You will have to regenerate the data for the contact_states table for the given customer and failed partition. This script will delete the current rows and update with new data.

Additionally, if you have run the the contact_states updater script, you will have to re-run the kpi scheduled queries as described below.

update/regeneration of bq table kpi_summary metrics failed

The mobile_subscription.kpi_summary table gets generated by the scheduled queries update_kpi_*, which depend on various tables, including the contact_states and client_snapshots tables. If you have re-generated contact_states or client_snapshots from a certain date, the same must be done for the scheduled kpi queries-- using the same start date.

There are a few things which could have failed:

  1. the update of the contact_states table mentioned above

  2. the generation of client_state.client_snapshots table, in which case you would have to re-generate the client_snapshots table. More information on how to do this provided on the link above.

  3. the script which checks if the new partition of the states table exists failed, which is server/processes/scripts/run-check-bq-kpi_summary-update.js in push-notification-service, scheduled every day at 12 UTC

  4. in addition, you should check if any of the scheduled update_kpi_* queries may have failed or if the numbers look off from previous days

To find out if the script has run correctly, you can check if entries exist for a certain day. Run the query below for to check for the current day.

SELECT
aggregated_at, kpi_name, SUM(value)
FROM `ems-mobile-engage.mobile_subscription.kpi_summary`
WHERE aggregated_at = TIMESTAMP(CURRENT_DATE())
GROUP BY aggregated_at, kpi_name

Alternatively you can run this query to get the results of today’s and yesterday’s runs in one.

SELECT * from (
    SELECT
    aggregated_at, kpi_name, SUM(value)
    FROM `ems-mobile-engage.mobile_subscription.kpi_summary`
    WHERE aggregated_at = TIMESTAMP(CURRENT_DATE())
    GROUP BY aggregated_at, kpi_name

    UNION ALL

    SELECT
    aggregated_at, kpi_name, SUM(value)
    FROM `ems-mobile-engage.mobile_subscription.kpi_summary`
    WHERE aggregated_at = TIMESTAMP(DATE_ADD(CURRENT_DATE(), INTERVAL -1 DAY))
    GROUP BY aggregated_at, kpi_name
) order by kpi_name, aggregated_at

The values of today should be roughly similar to the values of yesterday. If the values look fine, we can assume it was a false alert and close.

If however, assuming the contact_states and client_snapshots tables are in order, and the numbers on kpi_summary table seem suspicious or are fully missing for a certain KPI (or all), you can re-generate the data for each KPI (separately).

If one or multiple kpis are fully missing for today, you can use the SCHEDULE BACKFILL functionality in GAP to regenerate the kpi for today. To do this, simply go to the scheduled query UI in GAP (BigQuery > Scheduled queries) and select the scheduled query of the missing kpi. Then select SCHEDULE BACKFILL and select the Run one time transfer option and hit OK.

Otherwise, kpis can be erased and regenerated from scratch, even multiple days into the past, using this script. All KPI update queries can be found in me-dashboard-big-query/src/scripts. Before running the queries, make sure you update your environment to the appropriate project you’re targeting. Updating your environment variables GCLOUD_PROJECT and GOOGLE_APPLICATION_CREDENTIALS can be done in your .env file (an example can be found in .env.example).

Example: We are looking to re-generate data for the last 3 days since Monday, 20th of September 2021 for push_opens kpi for ALL customers:

We would have to comment out line 9, which provides a list of customer_ids in case there are only specific customers for which we need to do a re-generation of the KPI name. Next, we would update and run the following command from the root folder of repo:

node src/scripts/create_old_kpi_opens 2021-09-18 3 mobile_subscription kpi_summary

As noted in the file, the 4 given arguments are, respectively: - initialDate: date from which you’d like to start re-generation - numberOfDays: the number of days FROM the start date till today, inclusive - targetDataSet: dataset of where the table will be found - targetTable: table in which update will take place

This query deletes and updates the kpi_summary table for the given dates provided and the given kpi_name.

Note: using the Schedule Backfill option given in GCP scheduled queries UI will append to the table, but not delete or update. If you are looking to use this option, you will have to manually delete from kpi_summary for the given kpi you are wanting to use the UI backfill on.

Regenerating kpi summary data with me-cli

NEW: The kpi summary data can also be regenerated with the me-cli tool now. Use any of the commands below to start the wizard and follow the instructions.

me-cli kpi-summary
me-cli kpi
me-cli kpi-reporting

Follow the instructions in the wizard to select the date range, customers and kpis you want to regenerate the data for. You can also provide the parameters directly in the command. You must also specify an action (regenerate or purge) as well as a kpi name at minimum. The available kpis are: contact_states, kpi_daily_active_users, kpi_inactive_users_won_back, kpi_inapp_impressions, kpi_monthly_active_users, kpi_new_installs, kpi_new_opt_in, kpi_new_opt_out, kpi_optin_optout, kpi_push_open, kpi_push_open_test, kpi_push_sent, kpi_push_sent_test, kpi_push_undelivered, kpi_push_undelivered_test, kpi_silent_push_sent, kpi_silent_push_undelivered, kpi_total_installs

Available parameters are:

  -c, --customer-ids string   comma-delimited customer IDs, e.g. 1234,5678 (default: null = all customers)
  -d, --date string           single date in YYYY-MM-DD format (default: yesterday); cannot be combined with --from/--to
  -f, --from string           start date in YYYY-MM-DD format; can be used alone (single day) or with --to for a range
  -h, --help                  help for regenerate
  -k, --kpi string            KPI routine to regenerate; omit to launch interactive wizard
  -n, --no-purge              skip the purge step and run only regenerate (may cause duplicate data)
  -x, --print-command         print the bq CLI command(s) instead of executing them
  -p, --project string        project environment: "staging" (ems-mobile-engage-staging) or "production" (ems-mobile-engage) (default "production")
  -t, --to string             end date in YYYY-MM-DD format; use with --from for a range

This help text is also available in the help of the command in me-cli. Here are some examples of how to use the command with parameters:

Examples:
  me-cli kpi-summary regenerate kpi_monthly_active_users --date 2026-03-10
  me-cli kpi-summary regenerate kpi_push_sent -d 2026-03-10 -p staging
  me-cli kpi-summary regenerate kpi_push_sent -f 2026-03-01 -t 2026-03-05
  me-cli kpi-summary regenerate kpi_push_sent -f 2026-03-01 -t 2026-03-05 -c 1234,5678
  me-cli kpi-summary regenerate kpi_push_sent -d 2026-03-10 --no-purge
  me-cli kpi-summary regenerate kpi_push_sent -d 2026-03-10 --print-command

Usage:
  me-cli kpi-summary regenerate [kpi] [flags]

Note that regenerating the data in this way might lead to different numbers than before. Since the source data is 'alive', meaning late messages or data deletion requests might alter or add to the data that we aggregate from. So if you regenerate the data for a past date, you might end up with different numbers than before. This is expected and not necessarily an indication that something went wrong.