Some helpful SQL to look into number of days per month a HW opened the CHT

Technical Support
,
1

Telemetry data is recorded any time a user opens their app. We can understand how many days per month a user opens their app by looking at the number of unique days a telemetry record was recorded for them.

I wanted to share some SQL that can be helpful for those that are interested in knowing how many days per month users are opening their app

The SQL provided below will return a table like this (plus some other useful fields).

user_name january february march
user123 19 25 27
user234 12 23 22
user345 15 17 20
user456 16 11 19

In the example above, user123 opened their app at least once on:

  • 19 out of 31 days in January
  • 25 out of 28 days in February
  • 27 out of 31 days in March
SQL 

SELECT
	user_name,
	count(distinct(period_start)) FILTER (WHERE date_trunc('month',period_start) = '2023-01-01') AS january,
	count(distinct(period_start)) FILTER (WHERE date_trunc('month',period_start) = '2023-02-01') AS february,
	count(distinct(period_start)) FILTER (WHERE date_trunc('month',period_start) = '2023-03-01') AS march,
	count(distinct(period_start)) FILTER (WHERE date_trunc('month',period_start) = '2023-04-01') AS april,
	count(distinct(period_start)) FILTER (WHERE date_trunc('month',period_start) = '2023-05-01') AS may,
	count(distinct(period_start)) FILTER (WHERE date_trunc('month',period_start) = '2023-06-01') AS june,
	count(distinct(period_start)) AS telemetry_days_ytd,
	CURRENT_DATE - '2023-01-01'::date AS total_days_ytd,
	((count(distinct(period_start)) / (CURRENT_DATE - '2023-01-01'::date)::float)*100)::int AS percent_days_active

FROM
	useview_telemetry

WHERE
	date_trunc('year',period_start) = '2023-01-01'

GROUP BY
	user_name

ORDER BY
	telemetry_days_ytd DESC
1 Like
2

This is very cool! Thanks for sharing! I am going to bookmark this as an example of the kind of data it would be very nice to ingest in cht-watchdog!

1 Like
3

Very helpful, but we should always be aware of this issue, where telemetry records may not be recorded for every day, even if users are active and submitting forms.

1 Like
4

Thanks @yuv Can you share some SQL that helps identify those scenarios? That would help others know if they are experiencing this issue.

5

Hi @michael, I used the query like below to count how many days a particular user was active in a month. If a user submitted certain report in a day or created some document (for e.g.: contact), then they’re considered to have used an app on that day.

WITH active_days AS 
(
-- Docs created 
SELECT 
    DISTINCT 
    doc#>>'{meta,created_by_person_uuid}' AS chw,
     date_trunc('day',to_timestamp((doc->>'reported_date')::bigint/1000))::date AS active_day
FROM
    couchdb
WHERE
    doc#>>'{meta,created_by_person_uuid}'<>''
UNION
-- Reports submitted
    SELECT 
        DISTINCT chw,
        date_trunc('day',reported)::date AS active_day
    FROM
        form_metadata 
)
SELECT 
    chw,
    date_trunc('month',active_day)::date AS MONTH,
    count(active_day) AS count_active_day
FROM
    active_days
GROUP BY 
    1,2
ORDER BY
    1,2;

This query can be further joined with contactview_metadata or other tables to extract the CHT user’s name and details. To avoid long query I didn’t provide that code here.

This sql can’t count days where user opened a app, but didn’t create any report or contacts. However, it provides more accurate data than telemetry until the issue with telemetry is fixed.