ダッシュボードレポート - ユーザー訪問

[SaraDev (Sara Devlaeminck)]

これは、ユーザー訪問およびユーザー訪問（モバイル）のダッシュボードレポートのSQLバージョンです。

このダッシュボードレポートは、日々のユーザー訪問を追跡することにより、ユーザーエンゲージメントに関する洞察を提供します。管理者は、日付範囲、グループメンバーシップ、およびデバイスタイプ（モバイルまたは非モバイル）に基づいてデータをフィルタリングできます。

ユーザー訪問パターンを理解することは、管理者が情報に基づいた意思決定を行い、ユーザーエンゲージメントとコミュニティの健全性を向上させるのに役立ちます。

-- [params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- null text :group_name
-- boolean :mobile_views = false

SELECT uv.visited_at AS date,
       COUNT(DISTINCT uv.user_id) AS visits_count
FROM user_visits uv
LEFT JOIN group_users gu ON gu.user_id = uv.user_id
LEFT JOIN groups g ON g.id = gu.group_id AND (:group_name IS NULL OR g.name = :group_name)
WHERE uv.visited_at BETWEEN :start_date AND :end_date
AND (:group_name IS NULL OR g.name IS NOT NULL)
AND (:mobile_views = false OR uv.mobile = true)
GROUP BY uv.visited_at
ORDER BY uv.visited_at ASC

SQLクエリの説明

このSQLクエリは、プラットフォームへのユーザーの訪問を記録するuser_visitsテーブルからデータを選択することで機能します。クエリの主なコンポーネントは以下のとおりです。

• パラメータ
  • :start_dateおよび:end_dateは、レポートの日付範囲を定義します。両方のdateパラメータはYYYY-MM-DDの日付形式を受け入れます。
  • :group_nameは、特定のユーザーグループ名でフィルタリングできます。指定しない場合、すべてのグループのデータが含まれます。
  • :mobile_viewsは、モバイルデバイスの訪問のみを含める（true）か、すべての訪問を含める（false）かを決定します。
• データ選択と結合操作：
  • クエリは、訪問の日付（visited_at）を選択し、一意のユーザーIDの数（user_id）をカウントして、1日あたりのユニーク訪問数を計算します。
  • group_usersテーブルとLEFT JOINを実行して、ユーザー訪問をそれぞれのグループに関連付け、次にgroupsテーブルと別のLEFT JOINを実行して、指定されたグループ名でフィルタリングします（指定されている場合）。
• フィルタリング：
  • WHERE句は、入力パラメータに基づいてフィルタを適用します。
    • 指定された日付範囲内の訪問のみが含まれるようにします。
    • 指定されている場合はグループ名でフィルタリングし、そのグループに属するユーザーの訪問がカウントされるようにします。
    • :mobile_viewsパラメータがtrueに設定されている場合、モバイル訪問のみが含まれます。
• グループ化と並べ替え：
  • クエリは、訪問の日付（visited_at）で結果をグループ化し、1日あたりの訪問数を集計します。
  • 結果を日付の昇順で並べ替え、ユーザー訪問の時系列ビューを提供します。

結果例

date	visits_count
2024-01-06	67
2024-01-07	71
2024-01-08	88
2024-01-09	79
2024-01-10	78
…	…