Data exploration for one metric

[michaelkohn]

Choose a metric from the dashboard and dig into the data.

• What interesting insights did you look into?
• Anything surprise you?
• What additional data did you wish you had?

[Benmuiruri]

Data Exploration Findings

Report Detail

Notes

report_detail:%:apdex:

Rationale for choosing it

• Initially I was drawn by a poor performing death_report so I wanted to analyse the different report_detail events.

1. SQL for report_detail apdex

WITH apdex_counts AS (
  SELECT
    SUM(CASE WHEN metric LIKE '%:apdex:satisfied' THEN count ELSE 0 END) AS satisfied_count,
    SUM(CASE WHEN metric LIKE '%:apdex:tolerable' THEN count ELSE 0 END) AS tolerable_count,
    SUM(CASE WHEN metric LIKE '%:apdex:frustrated' THEN count ELSE 0 END) AS frustrated_count
  FROM
    useview_telemetry_metrics
  WHERE
    metric LIKE 'report_detail:%:apdex:%'
)
SELECT
  satisfied_count,
  tolerable_count,
  frustrated_count,
  ROUND(((satisfied_count + (tolerable_count / 2.0)) / (satisfied_count + tolerable_count + frustrated_count))::numeric, 2) AS apdex_score
FROM apdex_counts;

• The results show report_detail was satisfactory approximately 86% of the time.
• The apdex for all report_detail events is 0.92 which is okay.

{
    "satisfied_count": 97,
    "tolerable_count": 12,
    "frustrated_count": 3,
    "apdex_score": 0.92
}

• Excluding the outlier beulah and kendra who had minimum specification devices then report_detail was satisfactory approximately 97% of the time.
• The apdex for all report_detail events without outliers is 0.97 which is great.

 {
    "satisfied_count": 81,
    "tolerable_count": 4,
    "frustrated_count": 1,
    "apdex_score": 0.97
}

----

Contact List Load

Notes

contact_list:load

Rationale for choosing it

• It is one of the most run events, making it worth looking into, because it will have an impact on overall apdex score.

1. SQL including outliers

SELECT
    'contact_list:load' AS event_category,
    SUM(CASE WHEN metric LIKE 'contact_list:load:apdex:satisfied' THEN count ELSE 0 END) AS satisfied_count,
    SUM(CASE WHEN metric LIKE 'contact_list:load:apdex:tolerable' THEN count ELSE 0 END) AS tolerable_count,
    SUM(CASE WHEN metric LIKE 'contact_list:load:apdex:frustrated' THEN count ELSE 0 END) AS frustrated_count,
    SUM(count) AS total_event_count,
    ROUND(((SUM(CASE WHEN metric LIKE 'contact_list:load:apdex:satisfied' THEN count ELSE 0 END) +
          (SUM(CASE WHEN metric LIKE 'contact_list:load:apdex:tolerable' THEN count ELSE 0 END) / 2.0)) /
          SUM(count))::numeric, 2) AS apdex_score
FROM
  useview_telemetry_metrics
WHERE
  metric LIKE 'contact_list:load:apdex:%'
GROUP BY event_category;

• The results show contact_list:load was satisfactory approximately 67% of the time.

{
      "event_category": "contact_list:load",
      "satisfied_count": 261,
      "tolerable_count": 94,
      "frustrated_count": 30,
      "total_event_count": 385,
      "apdex_score": 0.8
}

2. SQL without outliers for deeper dive.

SELECT
  'contact_list:load' AS event_category,
  SUM(CASE WHEN metric LIKE 'contact_list:load:apdex:satisfied' THEN count ELSE 0 END) AS satisfied_count,
  SUM(CASE WHEN metric LIKE 'contact_list:load:apdex:tolerable' THEN count ELSE 0 END) AS tolerable_count,
  SUM(CASE WHEN metric LIKE 'contact_list:load:apdex:frustrated' THEN count ELSE 0 END) AS frustrated_count,
  SUM(count) AS total_event_count,
  ROUND(((SUM(CASE WHEN metric LIKE 'contact_list:load:apdex:satisfied' THEN count ELSE 0 END) +
         (SUM(CASE WHEN metric LIKE 'contact_list:load:apdex:tolerable' THEN count ELSE 0 END) / 2.0)) /
         SUM(count))::numeric, 2) AS apdex_score
FROM
  useview_telemetry_metrics
WHERE
  metric LIKE 'contact_list:load:apdex:%'
  AND user_name NOT IN ('beulah', 'kendra')
GROUP BY event_category;

• Apdex score that excludes the users beulah, kendra who had minimum specification devices shows that contact_list:load was satisfactory 77% of the time.

{
    "event_category": "contact_list:load",
    "satisfied_count": 258,
    "tolerable_count": 67,
    "frustrated_count": 14,
    "total_event_count": 339,
    "apdex_score": 0.86
}

3. Focusing on the frustrated and tolerable.

• We see users beulah, kendra and medic account for 77% of tolerable and 70% of frustrated

{
      "tolerable_outlier_count": 73,
      "total_tolerable": 94,
      "tolerable_outlier_percentage": 77.66,
      "frustrated_outlier_count": 21,
      "total_frustrated": 30,
      "frustrated_outlier_percentage": 70
}

• Without the outliers, frustrated and tolerable events are only 23% of all events.
• Of the 30 events, 50% of them took more than 7s
• The three worst performing events took 116 seconds, 67 seconds, and 66 seconds by users lena, debra, and gilbert.

----

Contact List Query

Notes

contact_list:query

Rationale for choosing it

• It had the second highest number of occurrences after tasks:refresh
• Users typically will be interacting with Contact page a lot, making it worth looking into

1. SQL including outliers

  SELECT
    'contact_list:query' AS event_category,
    SUM(CASE WHEN metric LIKE 'contact_list:query:apdex:satisfied' THEN count ELSE 0 END) AS satisfied_count,
    SUM(CASE WHEN metric LIKE 'contact_list:query:apdex:tolerable' THEN count ELSE 0 END) AS tolerable_count,
    SUM(CASE WHEN metric LIKE 'contact_list:query:apdex:frustrated' THEN count ELSE 0 END) AS frustrated_count,
    SUM(count) AS total_event_count,
    ROUND(((SUM(CASE WHEN metric LIKE 'contact_list:query:apdex:satisfied' THEN count ELSE 0 END) +
          (SUM(CASE WHEN metric LIKE 'contact_list:query:apdex:tolerable' THEN count ELSE 0 END) / 2.0)) /
          SUM(count))::numeric, 2) AS apdex_score
FROM
  useview_telemetry_metrics
WHERE
  metric LIKE 'contact_list:query:apdex:%'
GROUP BY event_category;

• The results below show contact_list:query was satisfactory approximately 88% of the time.

{
    "event_category": "contact_list:query",
    "satisfied_count": 1386,
    "tolerable_count": 133,
    "frustrated_count": 42,
    "total_event_count": 1561,
    "apdex_score": 0.93
}

2. SQL excluding outliers

SELECT
    'contact_list:query' AS event_category,
    SUM(CASE WHEN metric LIKE 'contact_list:query:apdex:satisfied' THEN count ELSE 0 END) AS satisfied_count,
    SUM(CASE WHEN metric LIKE 'contact_list:query:apdex:tolerable' THEN count ELSE 0 END) AS tolerable_count,
    SUM(CASE WHEN metric LIKE 'contact_list:query:apdex:frustrated' THEN count ELSE 0 END) AS frustrated_count,
    SUM(count) AS total_event_count,
    ROUND(((SUM(CASE WHEN metric LIKE 'contact_list:query:apdex:satisfied' THEN count ELSE 0 END) +
          (SUM(CASE WHEN metric LIKE 'contact_list:query:apdex:tolerable' THEN count ELSE 0 END) / 2.0)) /
          SUM(count))::numeric, 2) AS apdex_score
FROM
  useview_telemetry_metrics
WHERE
  metric LIKE 'contact_list:query:apdex:%' AND user_name NOT IN ('beulah', 'kendra')
GROUP BY event_category;

• The results below show contact_list:query was satisfactory approximately 93% of the time if you exclude the users beulah and kendra.

{
    "event_category": "contact_list:query",
    "satisfied_count": 1379,
    "tolerable_count": 83,
    "frustrated_count": 17,
    "total_event_count": 1479,
    "apdex_score": 0.96
}

3. Focusing on the frustrated and tolerable events

SELECT
  metric,
  user_name,
  count,
  min,
  max,
  SUM(count) OVER () AS total_count
FROM
  useview_telemetry_metrics
WHERE
  (metric LIKE 'contact_list:query:apdex:tolerable' OR
   metric LIKE 'contact_list:query:apdex:frustrated')
ORDER BY metric, count DESC, min DESC;

• Total count for frustrated and tolerable is 175 events. Of those, the two outlier users account for 42% and user medic accounts for 29%.
• Of the 50 events (30%) for other users, 14 events took more than 7 seconds,
• The three worst performing were Gilbert - 204s, lena - 115s, jeremiah - 115s

Overall Conclusion

• My main takeaway was the users beulah and kendra who had minimum specification devices experienced the worst performance. Their consistently poor experience indicates that improving the performance of the CHT should particularly consider the performance of the app on minimum specification devices (Android 5 and Webview 90+)

Report Detail

• Apdex score for general report_detail looks okay. With 86% of events being satisfactory, I would not focus on any improvements here, the performance is more than okay. What's left is running the automated standardized test with devices that meet minimum requirement to confirm satisfactory events will be at least 85%.

Contact List Query

-This metric had an apdex score of 0.93 and satisfactory events occurring approximately 88% of the time when the outliers are included.

• If you exclude the outliers, the apdex is 0.97 and the satisfactory events occure 93% of the time.
• Therefore, I would consider this metric good enough for now.

Contact List Load

• If I had to choose one metric to work on, it would be contact_list:load because it had a 77% satisfactory rate and an even lower 67% satisfactory rate when you include users on minimum specification devices.
• 33% of all events being tolerable or frustrating warrants attention. Especially since 50% of the events took more than 7s, with several taking surprisingly long upto 1 minute.

Possible further inquiries

• The contact_list:load is calculated in the ngOnInit method which includes several async operations, add more granular telemetry in the individual events see which one can be optimized.
• How does the number of contacts affect the contact_list:load ? (Test with 50, 100, 150 contacts) see how the value changes.

[Benmuiruri]

Hi @michaelkohn my summary is read. I will close this as completed.