状況の説明

「Yammer」サービスを作成する会社

に入社

業務:同僚の分析結果についてコメント

含めるべき内容

問題の状況を把握する(同僚が解決したい問題)
->問題:Investigating a Drop in User Engagement

テーブルID
->同僚の分析結果:Investigating a Drop in User Engagement: Answers

ヒントの分析方向、クエリーの理解(同僚がこの問題をどのように解決しているか)

提案されたソリューションに対する批判(同僚の論理展開方式やクエリーに問題があるかどうか、または他のメリットがあるかどうか)

任務を達成した収穫と感じ

それ以外にも、他の人とあなたの考えと挑戦を分かち合いたいなら、何でもいいです.

仮定の設定

クエリー分析

1番目のクエリ:成長率の検証

最も確認しやすいことの一つは成長率です.
これは測定が容易で、ほとんどの企業が厳密に追跡しているからです.
検索結果の結論:週中の成長率は高く、週末の成長率は低い.

SELECT DATE_TRUNC('day',created_at) AS day,
       COUNT(*) AS all_users,
       COUNT(CASE WHEN activated_at IS NOT NULL THEN u.user_id ELSE NULL END) AS activated_users
  FROM tutorial.yammer_users u
 WHERE created_at >= '2014-06-01'
   AND created_at < '2014-09-01'
 GROUP BY 1
 ORDER BY 1

結果表:

コメント:

定義

active users:active at(アクティブ化時にログインyammer)に空の値がない

6,7,8月の各日付のアクティベーションプレイヤーを確認するために,いつアクティベーションプレイヤーが減少するかを分析し,対応するクエリーを作成した.

2番目のクエリ:既存のユーザーパスワード

1番目のクエリで成長率の検証->これらの成長は正常であるため、新しいユーザではなく既存のユーザの参加度が低下していることを確認します.
ユーザー登録製品の時点に応じてサンゴを使用

クエリー結果の結論:10週間前に登録されたユーザーの参加が減少しました.

SELECT DATE_TRUNC('week',z.occurred_at) AS "week",
       AVG(z.age_at_event) AS "Average age during week",
       COUNT(DISTINCT CASE WHEN z.user_age > 70 THEN z.user_id ELSE NULL END) AS "10+ weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 70 AND z.user_age >= 63 THEN z.user_id ELSE NULL END) AS "9 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 63 AND z.user_age >= 56 THEN z.user_id ELSE NULL END) AS "8 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 56 AND z.user_age >= 49 THEN z.user_id ELSE NULL END) AS "7 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 49 AND z.user_age >= 42 THEN z.user_id ELSE NULL END) AS "6 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 42 AND z.user_age >= 35 THEN z.user_id ELSE NULL END) AS "5 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 35 AND z.user_age >= 28 THEN z.user_id ELSE NULL END) AS "4 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 28 AND z.user_age >= 21 THEN z.user_id ELSE NULL END) AS "3 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 21 AND z.user_age >= 14 THEN z.user_id ELSE NULL END) AS "2 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 14 AND z.user_age >= 7 THEN z.user_id ELSE NULL END) AS "1 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 7 THEN z.user_id ELSE NULL END) AS "Less than a week"
  FROM (
        SELECT e.occurred_at,
               u.user_id,
               DATE_TRUNC('week',u.activated_at) AS activation_week,
               EXTRACT('day' FROM e.occurred_at - u.activated_at) AS age_at_event,
               EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS user_age
          FROM tutorial.yammer_users u
          JOIN tutorial.yammer_events e
            ON e.user_id = u.user_id
           AND e.event_type = 'engagement'
           AND e.event_name = 'login'
           AND e.occurred_at >= '2014-05-01'
           AND e.occurred_at < '2014-09-01'
         WHERE u.activated_at IS NOT NULL
       ) z

 GROUP BY 1
 ORDER BY 1
LIMIT 100

結果表:

コメント:

age at event:イベントが発生した時刻(発生済み)-アクティブ化された時刻(ログインした時刻)の日付

user age:9月1日までのタイムスタンプ-アクティブ化日

は、新しいユーザと長い間使用されていたユーザを区別することができる.

3番目のクエリ:アプライアンスの使用上の問題を確認します。

2回目のクエリでは、既存のユーザーの使用量が減少したことによる一時的な急増や検索エンジンでのランキングの変更、ブロックなど、新しいWebサイトのトラフィックに影響を与える問題には関与しないと考えられています.
そのため、異なるデバイスの使用問題が特定の製品に限られているかどうかを知る必要があります.

SELECT DATE_TRUNC('week', occurred_at) AS week,
       COUNT(DISTINCT e.user_id) AS weekly_active_users,
       COUNT(DISTINCT CASE WHEN e.device IN ('macbook pro','lenovo thinkpad','macbook air','dell inspiron notebook',
          'asus chromebook','dell inspiron desktop','acer aspire notebook','hp pavilion desktop','acer aspire desktop','mac mini')
          THEN e.user_id ELSE NULL END) AS computer,
       COUNT(DISTINCT CASE WHEN e.device IN ('iphone 5','samsung galaxy s4','nexus 5','iphone 5s','iphone 4s','nokia lumia 635',
       'htc one','samsung galaxy note','amazon fire phone') THEN e.user_id ELSE NULL END) AS phone,
        COUNT(DISTINCT CASE WHEN e.device IN ('ipad air','nexus 7','ipad mini','nexus 10','kindle fire','windows surface',
        'samsumg galaxy tablet') THEN e.user_id ELSE NULL END) AS tablet
  FROM tutorial.yammer_events e
 WHERE e.event_type = 'engagement'
   AND e.event_name = 'login'
 GROUP BY 1
 ORDER BY 1
LIMIT 100

結果表:

4番目のクエリ:Eメールの問題を確認

3番目のクエリー結果グラフから電話フィルタ:電話登録率の急激な低下->モバイルアプリケーションにおける長期ユーザーの保持に関する問題
->モバイルアプリケーションが最近どのように変化しているかを特定する+製品に参加している理由を考える
上記の要約Eメールは、ユーザーを製品に再ロードすることを目的としています.
この問題は長期ユーザー保存に関連していますので、Eメールが関連していることを確認してください.

SELECT DATE_TRUNC('week', occurred_at) AS week,
       COUNT(CASE WHEN e.action = 'sent_weekly_digest' THEN e.user_id ELSE NULL END) AS weekly_emails,
       COUNT(CASE WHEN e.action = 'sent_reengagement_email' THEN e.user_id ELSE NULL END) AS reengagement_emails,
       COUNT(CASE WHEN e.action = 'email_open' THEN e.user_id ELSE NULL END) AS email_opens,
       COUNT(CASE WHEN e.action = 'email_clickthrough' THEN e.user_id ELSE NULL END) AS email_clickthroughs
  FROM tutorial.yammer_emails e
 GROUP BY 1
 ORDER BY 1

結果表:

5回目のクエリ:Eメールの質問を週に1回表示

clickthroughsでフィルタリング:clickthroughsは下にたくさんあります.
上記の4番目のクエリー結果グラフには、Eメールのクリック数と開く速度がより詳細に表示されており、モバイルアプリケーションだけでなく、要約Eメールも確認できます.

SELECT week,
       weekly_opens/CASE WHEN weekly_emails = 0 THEN 1 ELSE weekly_emails END::FLOAT AS weekly_open_rate,
       weekly_ctr/CASE WHEN weekly_opens = 0 THEN 1 ELSE weekly_opens END::FLOAT AS weekly_ctr,
       retain_opens/CASE WHEN retain_emails = 0 THEN 1 ELSE retain_emails END::FLOAT AS retain_open_rate,
       retain_ctr/CASE WHEN retain_opens = 0 THEN 1 ELSE retain_opens END::FLOAT AS retain_ctr
  FROM (
SELECT DATE_TRUNC('week',e1.occurred_at) AS week,
       COUNT(CASE WHEN e1.action = 'sent_weekly_digest' THEN e1.user_id ELSE NULL END) AS weekly_emails,
       COUNT(CASE WHEN e1.action = 'sent_weekly_digest' THEN e2.user_id ELSE NULL END) AS weekly_opens,
       COUNT(CASE WHEN e1.action = 'sent_weekly_digest' THEN e3.user_id ELSE NULL END) AS weekly_ctr,
       COUNT(CASE WHEN e1.action = 'sent_reengagement_email' THEN e1.user_id ELSE NULL END) AS retain_emails,
       COUNT(CASE WHEN e1.action = 'sent_reengagement_email' THEN e2.user_id ELSE NULL END) AS retain_opens,
       COUNT(CASE WHEN e1.action = 'sent_reengagement_email' THEN e3.user_id ELSE NULL END) AS retain_ctr
  FROM tutorial.yammer_emails e1
  LEFT JOIN tutorial.yammer_emails e2
    ON e2.occurred_at >= e1.occurred_at
   AND e2.occurred_at < e1.occurred_at + INTERVAL '5 MINUTE'
   AND e2.user_id = e1.user_id
   AND e2.action = 'email_open'
  LEFT JOIN tutorial.yammer_emails e3
    ON e3.occurred_at >= e2.occurred_at
   AND e3.occurred_at < e2.occurred_at + INTERVAL '5 MINUTE'
   AND e3.user_id = e2.user_id
   AND e3.action = 'email_clickthrough'
 WHERE e1.occurred_at >= '2014-06-01'
   AND e1.occurred_at < '2014-09-01'
   AND e1.action IN ('sent_weekly_digest','sent_reengagement_email')
 GROUP BY 1
       ) a
 ORDER BY 1

結果表:

同僚まとめ：

モバイル使用および要約Eメールに関連

同僚の解決策:製品責任者に問題がこれらの分野に限られていることを通知し、問題が破損しているか、正しく実施されていないかを判断する価値があります.

同僚に対する意見

しきい値:

からのデータは、問題の正確な位置または解決方法を知らない.
1)メールの内容に問題がありますか.
2)Eメールヘッダに問題がありますか?
3)モバイルアプリケーションではどのような問題がありますか.システムエラー?UIの不便?コンテンツの使用制限?

メリット:

1)問題の大きな枠組みを把握した.
2)上記の作業により、他のチームがどこに行くべきかを判断するのに多くの時間を節約できます.