openedx / aspects-dbt

The dbt project for Open edX Aspects!
Apache License 2.0
2 stars 5 forks source link

feat: include models for pageview engagement #52

Closed SoryRawyer closed 7 months ago

SoryRawyer commented 7 months ago

This includes models to support engagement dropoff analyses and page view completeness, i.e. how many learners viewed all pages in a section or subsection.

fact_navigation_dropoff is a model with two different aggregation levels: total views are counted for each section and subsection.

fact_navigation_completion counts the number of pages in each section and subsection and joins that to data about page visits. Aggregations can use the page_count column to compare against the number of unique pages a learner has visited. If the numbers are equal, then the learner has visited every page in the section.

Here is an example query of how fact_navigation_completeness can answer the question "which learners viewed all pages in a subsection?":

SELECT
    subsection_with_name,
    actor_id,
    page_count,
    countDistinct(block_id) AS pages_visited,
    page_count = pages_visited AS visited_all_pages
FROM reporting.fact_navigation_completion
GROUP BY subsection_with_name, actor_id, page_count
HAVING visited_all_pages = 1
openedx-webhooks commented 7 months ago

Thanks for the pull request, @SoryRawyer! Please note that it may take us up to several weeks or months to complete a review and merge your PR.

Feel free to add as much of the following information to the ticket as you can:

All technical communication about the code itself will be done via the GitHub pull request interface. As a reminder, our process documentation is here.

Please let us know once your PR is ready for our review and all tests are green.

openedx-webhooks commented 7 months ago

@SoryRawyer 🎉 Your pull request was merged! Please take a moment to answer a two question survey so we can improve your experience in the future.