Tips for Exporting User Data

Data & reporting
,
1

When users need customized or detailed data exports that go beyond the basic User Data Export available from their preference page, administrators can perform user data exports using the Data Explorer plugin.

This process involves writing and executing SQL queries tailored to specific data needs while ensuring compliance with site policies and data protection regulations.

Admins might also want to export user data for other reasons, such as:

  • User Engagement Analysis:
    • Analyze how users interact with the site, which posts are popular, and which topics garner the most responses.
  • Report Generation:
    • Create custom reports for stakeholders to provide insights into community metrics and user engagement.
  • Data Backup:
    • Keep backups of user data for recovery purposes or historical analysis.
  • Data Migration:
    • Export user data when migrating to another platform or creating a data warehouse.
  • Content Moderation:
    • Investigate user activities or content in case of policy violations or disputes.

Key Considerations for User Data Export Queries

When writing queries to export user data, it’s important to consider both the specific requirements of the export, and your site’s policies regarding user data exports.

Here are some factors and considerations that may influence how user data export queries are written:

  1. User Attributes:
  • If you need specific user attributes such as username, email, or registration date, your query should explicitly select these columns from the database. For example:
SELECT username, email, created_at AS registration_date
FROM users
WHERE id = :user_id
  1. Activity Data:
  • For exporting user activity data like posts, likes, or login history, additional joins with related tables (e.g., posts, likes, user_actions) will be necessary:
SELECT p.id AS post_id, p.topic_id, p.created_at AS post_date, u.username
FROM posts p
JOIN users u ON u.id = p.user_id
WHERE u.id = :user_id
  1. Date Range:
  • If you need data from a specific timeframe, include date filters in your query to limit the results to the relevant period:
SELECT username, email, created_at AS registration_date
FROM users
WHERE created_at BETWEEN :start_date AND :end_date
  1. Data Privacy:
  • Ensure that the exported data complies with data privacy laws and site policies. Be cautious about including sensitive data unless absolutely necessary.
  1. Anonymization:
  • In some cases, you may need to anonymize user data to protect user privacy. For example, you might replace names with unique identifiers.
  1. User Consent:
  • Make sure an explicit consent mechanism is in place if data export involves sharing personal or sensitive user information…

Example Queries

Below are some example queries for common data export needs. These queries are designed to be similar to the data export available from a user’s preferences page:

Export User Information:

-- [params]
-- int :user_id

SELECT id, username, email, created_at AS registration_date
FROM users
WHERE id = :user_id

Export Posts

-- [params]
-- int :user_id

SELECT 
    id,
    topic_id,
    post_number,
    created_at,
    updated_at,
    reply_to_post_number,
    reply_count,
    quote_count,
    deleted_at,
    like_count,
    incoming_link_count,
    bookmark_count,
    score,
    reads,
    post_type,
    sort_order,
    last_editor_id,
    hidden,
    hidden_reason_id,
    notify_moderators_count,
    spam_count,
    illegal_count,
    inappropriate_count,
    last_version_at,
    user_deleted,
    reply_to_user_id,
    percent_rank,
    notify_user_count,
    like_score,
    deleted_by_id,
    word_count,
    version,
    cook_method,
    wiki,
    baked_at,
    baked_version,
    hidden_at,
    self_edits,
    reply_quoted,
    via_email,
    public_version,
    locked_by_id,
    image_upload_id,
    qa_vote_count,
    raw,
    cooked,
    edit_reason,
    raw_email,
    action_code,
    outbound_message_id
FROM 
    posts
WHERE 
    user_id = :user_id
ORDER BY 
    created_at DESC

Export Auth Token Logs:

-- [params]
-- int :user_id

SELECT 
    path,
    action,
    user_auth_token_id,
    client_ip,
    user_agent,
    auth_token,
    created_at
FROM 
    user_auth_token_logs
WHERE 
    user_id = :user_id

Export Auth Tokens:

-- [params]
-- int :user_id

SELECT 
    auth_token,
    client_ip,
    rotated_at,
    created_at,
    updated_at,
    seen_at,
    auth_token_seen,
    user_agent,
    prev_auth_token
FROM 
    user_auth_tokens
WHERE 
    user_id = :user_id

Export User Badges:

-- [params]
-- int :user_id

SELECT 
    notification_id,
    badge_id,
    granted_at,
    granted_by_id,
    post_id,
    seq,
    featured_rank,
    created_at,
    is_favorite
FROM 
    user_badges
WHERE 
    user_id = :user_id

Export User Bookmarks:

-- [params]
-- int :user_id

SELECT 
    name,
    reminder_at,
    created_at,
    updated_at,
    reminder_last_sent_at,
    reminder_set_at,
    auto_delete_preference,
    pinned,
    bookmarkable_id,
    bookmarkable_type
FROM 
    bookmarks
WHERE 
    user_id = :user_id

Export Category Preferences:

-- [params]
-- int :user_id

SELECT 
    category_id,
    notification_level,
    last_seen_at
FROM 
    category_users
WHERE 
    user_id = :user_id

Export Flags:

-- [params]
-- int :user_id

SELECT 
    id,
    name,
    name_key,
    description,
    notify_type,
    auto_action_type,
    custom_type,
    applies_to,
    position,
    enabled,
    created_at,
    updated_at,
    score_type
FROM 
    flags
WHERE 
    id IN (
        SELECT 
            post_action_type_id 
        FROM 
            post_actions 
        WHERE 
            user_id = :user_id
    )

Export Likes:

-- [params]
-- int :user_id

SELECT 
    post_id,
    post_action_type_id,
    created_at,
    updated_at,
    deleted_at,
    deleted_by_id,
    related_post_id,
    staff_took_action,
    deferred_by_id,
    targets_topic,
    agreed_at,
    agreed_by_id,
    deferred_at,
    disagreed_at,
    disagreed_by_id
FROM 
    post_actions
WHERE 
    user_id = :user_id
    AND post_action_type_id = 1

Export User Preferences:

-- [params]
-- int :user_id

SELECT 
    mailing_list_mode,
    email_digests,
    external_links_in_new_tab,
    enable_quoting,
    dynamic_favicon,
    automatically_unpin_topics,
    digest_after_minutes,
    auto_track_topics_after_msecs,
    new_topic_duration_minutes,
    last_redirected_to_top_at,
    email_previous_replies,
    email_in_reply_to,
    like_notification_frequency,
    include_tl0_in_digests,
    mailing_list_mode_frequency,
    notification_level_when_replying,
    theme_key_seq,
    allow_private messages,
    homepage_id,
    theme_ids,
    hide_profile_and_presence,
    text_size_key,
    text_size_seq,
    email_level,
    email_messages_level,
    title_count_mode_key,
    enable_defer,
    timezone,
    enable_allowed_pm_users,
    dark_scheme_id,
    skip_new_user_tips,
    color_scheme_id,
    default_calendar,
    chat_enabled,
    only_chat_push_notifications,
    oldest_search_log_date,
    chat_sound,
    dismissed_channel_retention_reminder,
    dismissed_dm_retention_reminder,
    bookmark_auto_delete_preference,
    ignore_channel_wide_mention,
    chat_email_frequency,
    enable_experimental_sidebar,
    seen_popups,
    chat_header_indicator_preference,
    sidebar_link_to_filtered_list,
    sidebar_show_count_of_new_items,
    watched_precedence_over_muted,
    chat_separate_sidebar_mode,
    topics_unread_when_closed,
    policy_email_frequency,
    show_thread_title_prompts,
    auto_image_caption
FROM 
    user_options
WHERE 
    user_id = :user_id

Export Queued Posts:

-- [params]
-- int :user_id

SELECT 
    id,
    user_id,
    topic_id,
    post_number,
    raw,
    created_at,
    updated_at,
    state,
    error,
    queue
FROM 
    queued_posts
WHERE 
    user_id = :user_id

Export User Archive:

-- [params]
-- int :user_id

SELECT 
    id,
    topic_id,
    created_at,
    updated_at
FROM 
    user_archived_messages
WHERE 
    user_id = :user_id

Export User Visits:

-- [params]
-- int :user_id

SELECT 
    visited_at,
    posts_read,
    mobile,
    time_read
FROM 
    user_visits
WHERE 
    user_id = :user_id

For more information about creating and running data export queries, please refer to the Discourse Data Explorer documentation, and the data & reporting category.

5 Likes