# User analytics

We have two ways of tracking user activity in Moped. Edit activities are tracked in the `moped_activity_log` table, and we also collect certain usage metrics, which are stored in the `moped_user_events` table.&#x20;

## User event data

There are currently two event types available:

* `app_load` - created whenever a user opens Moped in a browser tab, or when the user refreshes the page.&#x20;
* `dashboard_load` - created whenever a user navigates to the [Dashboard](https://mobility.austin.gov/moped/dashboard) page
* `projects_map_load` - created whenever a user navigates to the [Projects map](https://mobility.austin.gov/moped/projects?map=true) page
* `projects_saved_view` - created whenever a user open the saved view modal in the projects list

It would be fairly easy to add additional event types. Ask a dev.

### Querying event data

You can connect to the Moped Read Replica database and query usage metrics. For example, this query counts the number of events by user per day

```sql
SELECT
    events.user_id,
    first_name || ' ' || last_name AS person,
    created_at::date event_date,
    event_name,
    count(id)
FROM
    moped_user_events events
    LEFT JOIN moped_users u ON u.user_id = events.user_id
GROUP BY
    events.user_id,
    person,
    event_date,
    event_name
ORDER BY
    event_date DESC;
```

### Activity log data

The activity log data can be queried to see user edit activities

### Querying activity data

This query retrieves the number of edits by user by day

```sql
SELECT
    first_name || ' ' || last_name AS _name,
    count(activity_id) AS num_of_edits,
    created_at::date as edit_date
FROM
    moped_activity_log
    LEFT JOIN moped_users ON updated_by_user_id = user_id
WHERE
    created_at > '2023-11-01'
GROUP BY
    _name,
    edit_date
ORDER BY edit_date desc;

```
