Dashboard Report - User Visits

data & reporting
, ,
1

This is an SQL version of the Dashboard Report for User Visits and User Visits (Mobile).

This dashboard report provides insights into user engagement by tracking daily user visits. Admins can filter the data based on date range, group membership, and device type (mobile or non-mobile).

Understanding user visit patterns can help admins make informed decisions to enhance user engagement and community health.

-- [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 Query Explanation

The SQL query operates by selecting data from the user_visits table, which logs each user’s visits to the platform. The main components of the query are explained below:

  • Parameters
    • :start_date and :end_date define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD .
    • :group_name allows filtering by a specific user group name. If not specified, data for all groups is included.
    • :mobile_views determines whether to include only mobile device visits (true) or all visits (false).
  • Data Selection and Join Operations:
    • The query selects the date of visit (visited_at) and counts distinct user IDs (user_id) to calculate the number of unique visits per day.
    • It performs a LEFT JOIN with the group_users table to associate user visits with their respective groups, followed by another LEFT JOIN with the groups table to filter by the specified group name, if any.
  • Filtering:
    • The WHERE clause applies filters based on the input parameters:
      • It ensures that only visits within the specified date range are included.
      • It filters by group name if specified, and ensures that visits are counted for users belonging to that group.
      • It includes only mobile visits if the :mobile_views parameter is set to true.
  • Grouping and Ordering:
    • The query groups the results by the date of visit (visited_at) to aggregate visit counts per day.
    • It orders the results in ascending order by date, providing a chronological view of user visits.

Example Results

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