Case Study: Decline in product_event

There is a decline in the number of daily users engaging with <VALUABLE_FEATURE> for the mobile app. How would you go about investigating this issue?

• Alternative prompt

Investigation**:**

Systematically define the problem through implicated systems, features, attributes, populations to identify potential causes. Eliminate possible causes through corroborating evidence.

1. Is this a “real” problem? Is it big enough to warrant an investigation? Can it be explained through known changes to the product, instrumentation, or funnel?
   1. Once the issue has been triaged and validated as “real”, document the investigation through an incident report, including a summary of current understanding at the top
   2. Loop in stakeholders with clear roles: Driver, Approver, Contributors, and Informed
   3. Set clear communication expectations for where/when updates will occur
2. Define the time period during which the decline occurred. Understand whether the decline occurs as step change(s) or gradual over time. Could it be normal variation? Was it a steady decline that was only just noticed?
3. Analyze the user segmentation to see if any specific groups were affected more than others. Where is the issue occurring in the user lifecycle? Which groups are involved? Devices? Locales?
4. Check the measurement process and ensure that tracking is functioning correctly (event emitted in browser, continues to appear on backend, ETL stats in between).
5. Do other, related metrics reflect the same change in expected upstream and downstream behavior? Are there noticeable fluctuations there that match?
6. Review recent changes to production and feature flags to identify recent updates or changes that might have impacted user engagement. If applicable, review marketing spend and tactics changes. Any recent issues filed related to the implicated area?
7. Context-specific: which unmeasured changes could impact the metric? Are there proxy measures that are either co-occurring or correlated with the decline or the unmeasured changes?
8. Identify steps to short-term remediation. If the issue is in tracking, define steps needed to fix and unit tests to monitor. If the issue is “real”, recommend changes or rollbacks to address the unintended impact.
9. Identify longer-term learnings. Flag any roadmapped changes that could have similar impact and list possible experiments that could validate product changes that could move the needle in the opposite direction.

SQL hands-on screener

• Skills to be assessed:

Tables available

• activity: User ID, event, context (view, org_id)

  user_id	event_ts	event	view	org_id
  0a1324bcf9901	2022-01-04 09:01:53 UTC	context_noun_action	url_or_view	org_id

• membership: User ID, org ID, date user added

  org_id	user_id	added_ts
  0a1324bcaf0189	bfce917ae	2022-01-04 09:01:53 UTC

• organizations: Org ID, org attributes (class = {free, smb, enterprise, edu, non-profit})

  org_id	class
  0a1434bfae	'free’
  bace14af	'enterprise’

  WITH user_date AS (
    SELECT user_id
         , event_ts::DATE AS active_dt
      FROM activity
     WHERE EXTRACT(WEEK FROM event_TS) = EXTRACT(WEEK FROM CURRENT_DATE() - INTERVAL '1 week')
     GROUP BY user_id, active_dt
  ), user_days AS (
    SELECT user_id
         , COUNT(active_dt) AS active_days
      FROM user_date
  )
  
  SELECT COUNT(*) AS active_users
    FROM user_days
   WHERE active_days > 1