3 SQL Queries to Retrieve LearnDash User Data

Why use SQL Queries to Extract LearnDash User Data?

If you’ve been using LearnDash for a while, you know like me that it’s a great e-learning tool, but that the reporting features are limited.

Even with ProPanel, the data produced by LearnDash does not always allow us to properly manage our training business.

To help you get some reporting, I’d like to share some SQL queries that may be useful to you.

If you’re not using LearnDash yet, or if you’re wondering if it is the right tool for you, check out my 10 reasons why LearnDash is the best tool for creating your e-learning platform in 2023.

How to use SQL Queries?

Before presenting you with the queries, I will explain how you can “chat” with your database.

If you have access to your database via tools provided by your host (like phpmyadmin), it’s best to use those. Otherwise, I’ll tell you how to do it.

First, it is best to do the following actions outside of peak hours on the site. Do this on a Sunday morning, or when there is less traffic on your site. You can use tools like Matomo (Piwik) or Google Analytics to better understand the use of your website.

Take a complete backup of the site. The ideal is always at the server level. If this is not possible, a plugin like UpDraft Plus or Duplicator will do the trick.

1. Install the WP Data Access plugin from the WordPress library. The free version works very well.
2. In the back office, in the navigation menu at the bottom left, go to WP Data Access > Query Builder. A query window opens.
3. Copy and paste the desired SQL query into the editor.
4. Then you can double-click the query title, give it a name and save it.
5. Click on the Export to CSV button to generate a file that can be used by a host of software, including Microsoft Excel.

If you discover a passion for the SQL language and you like the WP Data Access plugin, you can always buy the paid version on the author’s website. Always a good thing to encourage the devs behind the plugins and themes you like!

SQL Query to Retrieve User Sata on a LearnDash + WooCommerce Site

The query below generates a table with the following data. Each line represents a user.

SELECT 
    u.ID AS user_id,
    u.user_email AS 'user_email',
    MAX(CASE
        WHEN um.meta_key = 'first_name' THEN um.meta_value
    END) AS first_name,
    MAX(CASE
        WHEN um.meta_key = 'last_name' THEN um.meta_value
    END) AS last_name,
    MAX(CASE
        WHEN um.meta_key = 'billing_address_1' THEN um.meta_value
    END) AS billing_address_1,
    MAX(CASE
        WHEN um.meta_key = 'billing_address_2' THEN um.meta_value
    END) AS billing_address_2,
    MAX(CASE
        WHEN um.meta_key = 'billing_city' THEN um.meta_value
    END) AS billing_city,
    MAX(CASE
        WHEN um.meta_key = 'billing_postcode' THEN um.meta_value
    END) AS billing_postcode,
    MAX(CASE
        WHEN um.meta_key = 'billing_state' THEN um.meta_value
    END) AS billing_state,
    MAX(CASE
        WHEN um.meta_key = 'billing_country' THEN um.meta_value
    END) AS billing_country,
    MAX(CASE
        WHEN um.meta_key = 'billing_phone' THEN um.meta_value
    END) AS billing_phone
FROM
    wp_users u
        LEFT JOIN
    wp_usermeta um ON u.ID = um.user_id
GROUP BY u.ID

SQL Query to Retrieve LearnDash Activity Data by User

The query below generates a table with the following data. Each line represents an activity. Each activity is like an event. There is registration for the training, then each module and chapter. Quizzes are also present in the list.

Warning ! I am providing you with a way of using data that was not provided by LearnDash, or even WordPress. Use and interpret with your judgment.

SELECT 
    la.user_id,
    u.user_email,
    la.activity_id,
    la.course_id,
    p.post_title AS 'course_name',
    la.activity_type,
    la.post_id,
    pp.post_title AS 'activity_name',
    la.activity_status,
    FROM_UNIXTIME(la.activity_started) AS 'start_datetime',
    FROM_UNIXTIME(la.activity_completed) AS 'complete_datetime',
    FROM_UNIXTIME(la.activity_updated) AS 'update_datetime'
FROM
    wp_learndash_user_activity la
        LEFT JOIN
    wp_posts p ON la.course_id = p.id
        LEFT JOIN
    wp_posts pp ON la.post_id = pp.id
        LEFT JOIN
    wp_users u ON la.user_id = u.id

SQL Query to Retrieve Enrolments for Each Course and User

The query below generates a table with the following data. Each line represents the user and training pair, if it exists, and also provides the date and time of registration for the training.

Warning ! I am providing you with a way of using data that was not provided by LearnDash, or even WordPress. Use and interpret with your judgment.

SELECT 
    um.user_id,
    u.user_email,
    REPLACE(SUBSTR(um.meta_key, 8, 100),
        '_access_from',
        '') AS course_id,
    p.post_title AS 'course_name',
    FROM_UNIXTIME(um.meta_value) AS enrolment_date
FROM
    wp_usermeta um
        LEFT JOIN
    wp_users u ON um.user_id = u.id
        LEFT JOIN
    wp_posts p ON p.id = REPLACE(SUBSTR(um.meta_key, 8, 100),
        '_access_from',
        '')
WHERE
    RIGHT(um.meta_key, 12) = '_access_from'

LearnDash User Data: Do you need custom reports?

The queries I presented to you are generic. If you are comfortable with Microsoft Excel, you should be able to cross-reference the data you need.

Are you looking to automate LearnDash reporting? Maybe you need custom reports tailored to your training business? If WordPress stores the data, we can extract it!

Why work with a professional instead of doing it yourself? Hiring a professional to create specialized reporting on WordPress can be beneficial in several ways. Here are some reasons why it is often better to work with a pro for this type of project:

Learning Pixels provides Custom Reporting for LearnDash, WooCommerce and WordPress. Tailored, built just for you reports that’ll help you manage and drive your business.