Update script params for the 2018 CourseA-CourseF levels backfill by uponthesun · Pull Request #26362 · code-dot-org/code-dot-org

uponthesun · Dec 14, 2018

Followed the same process as this previous proficiencies backfill: #18530

Context:

Between July 1, 2018 and Oct 4, 2018 (when #25185 was merged), the 2018 versions of the CourseA-CourseF levels were missing level concept difficulty tagging, so user_proficiencies counters weren't updated for user progress on those courses during that time. user_proficiencies data is used in our analysis of the efficacy of those courses, as well as in our donor reports.

I'm going to be executing these queries/this script to backfill user_proficiencies; posting this PR to get more eyes on the changes before I do against the prod db.

The *.sed files and user_proficiencies.template.sql file were just used to generate the sql queries for getting the updated counter data. The template comes from the comment at the top of the bin/oneoff/backfill_data/user_proficiencies file.
The *.sql files contain queries which I executed against the production reporting database.
Updated bin/oneoff/backfill_data/user_proficiencies to use the script IDs for 2018 CourseA-CourseF, and the appropriate date ranges from July 1 to present. The date ranges are monthly, with the exception of October being split into 2 date ranges, since 10/1-10/4 is part of the missing data time period, and rest of the month had the correct data already. (The script still needs data from the time period after the LCD import to correctly calculate basic_proficiency_at.)

I'm including the *.sed and *.sql files here for review, but I won't commit those files.

DTP

DTP (Test > Production: eb8ad48)

DTP

DTP (Test > Production: 98fe4a5)

Manual DTP

DTP (Test > Production: f8d3e5b)

DTP (Test > Production: 8635c26)

DTP (Test > Production: 66a8dd9)

DTP (Test > Production: 4debf60)

DTP (Test > Production: 62b77f3)

DTP

DTP (Test > Production: 8e91824)

DTP (Test > Production: 01f4963)

DTP (Test > Production)

DTP (Test > Production: b389225)

DTP (Test > Production: f7c4ffe)

DTP (Test > Production: 3ca1416)

DTT (Staging > Test) [robo-dtt]

…-CourseF levels backfill

Hamms

how were the start_ and end_ids in the sed files obtained?

Hamms · Dec 14, 2018

-AGGREGATE_FILENAME = '/tmp/proficiency/aggregate_20170601_20171001'.freeze
+# This script wasn't written to take in arguments; instead it uses these hardcoded paths
+# (together with DATE_RANGES) above for input and output files.
+DATA_DIRECTORY = '/Users/winterdong/CDO/proficiencies_backfill'.freeze


nit: I'd suggest either keeping this in /tmp/ or just updating the script to take arguments

Is changing this constant back to /tmp acceptable?

/lazy

uponthesun · Dec 14, 2018

I got the start_id and end_id in the .sed files from trial and error (finding ids close to the date range boundaries). For start_id, I erred slightly before the start of the date range, and end_id erred slightly after.

Hamms

LGTM! I love the generated .sql files being included here for clarity!

Hamms · Dec 14, 2018

 require_relative '../../../dashboard/config/environment'

+# Fields:
+# start_date, end_date, end_timestamp, already_recorded


I think there's one script ID missing here (303, the express course) It was script_id 258 in the previous version.

Thanks for catching!

bencodeorg

The only thing I see to change is adding the script ID for the express course to the query template. Thanks for handling this Winter!

[note: this big red X seems very harsh. maybe I should have just added this as a comment. not sure what the appropriate git-iquette is]

DTT (Staging > Test) [robo-dtt]

uponthesun · Dec 14, 2018

Haha I often feel that way too, but I think "changes requested" is appropriate for any changes that must be made before committing (or in this case running). Will add and update, thanks!

DTT (Staging > Test) [robo-dtt]

bencodeorg

Looks good!

DTP (Test > Production: e359dc3)

…-dot-org/code-dot-org into 2018-courses-proficiencies-backfill

… already-recorded date range

uponthesun · Dec 17, 2018

Had to add a nil check to the script when it hit a certain case during execution (when the user only had progress during the already recorded date ranges), but otherwise the backfill ran smoothly. Here are the numbers for before/after the backfill on basic proficiency achieved per month:

Before:
count,year,month
86728,2016,1
92750,2016,2
102077,2016,3
95597,2016,4
97341,2016,5
43404,2016,6
16639,2016,7
33029,2016,8
77721,2016,9
90445,2016,10
110141,2016,11
61494,2016,12
110234,2017,1
113501,2017,2
138771,2017,3
96188,2017,4
117853,2017,5
54995,2017,6
21970,2017,7
43003,2017,8
96393,2017,9
120798,2017,10
143254,2017,11
134618,2017,12
137091,2018,1
142559,2018,2
162723,2018,3
152122,2018,4
155266,2018,5
64445,2018,6
21189,2018,7
27984,2018,8
49665,2018,9
106028,2018,10
109313,2018,11
54435,2018,12

After:
mysql> select count(user_id), month(basic_proficiency_at) as month, year(basic_proficiency_at) as year from user_proficiencies group by year, month;
+----------------+-------+------+
| count(user_id) | month | year |
+----------------+-------+------+
| 11121652 | NULL | NULL |
| 86728 | 1 | 2016 |
| 92750 | 2 | 2016 |
| 102077 | 3 | 2016 |
| 95597 | 4 | 2016 |
| 97341 | 5 | 2016 |
| 43404 | 6 | 2016 |
| 16639 | 7 | 2016 |
| 33029 | 8 | 2016 |
| 77721 | 9 | 2016 |
| 90445 | 10 | 2016 |
| 110141 | 11 | 2016 |
| 61494 | 12 | 2016 |
| 110234 | 1 | 2017 |
| 113501 | 2 | 2017 |
| 138771 | 3 | 2017 |
| 96188 | 4 | 2017 |
| 117853 | 5 | 2017 |
| 54995 | 6 | 2017 |
| 21970 | 7 | 2017 |
| 43003 | 8 | 2017 |
| 96393 | 9 | 2017 |
| 120798 | 10 | 2017 |
| 143254 | 11 | 2017 |
| 134618 | 12 | 2017 |
| 137091 | 1 | 2018 |
| 142559 | 2 | 2018 |
| 162723 | 3 | 2018 |
| 152122 | 4 | 2018 |
| 155266 | 5 | 2018 |
| 64445 | 6 | 2018 |
| 22841 | 7 | 2018 |
| 34932 | 8 | 2018 |
| 71768 | 9 | 2018 |
| 110006 | 10 | 2018 |
| 109358 | 11 | 2018 |
| 55030 | 12 | 2018 |
+----------------+-------+------+

davidsbailey · Dec 18, 2018

hi folks, it looks like this branch is currently checked out on production-console. OK for me to put that machine back onto the production branch?

uponthesun · Dec 18, 2018

Yes, I'm all done with the branch, you can change it back. Sorry about that!

Josh Lory and others added 20 commits November 13, 2018 18:18

Merge pull request #26066 from code-dot-org/test

82bcdca

DTP

Merge pull request #26096 from code-dot-org/dtp_candidate_eb8ad483

5e37064

DTP (Test > Production: eb8ad48)

Merge pull request #26105 from code-dot-org/test

d101bf2

DTP

Merge pull request #26113 from code-dot-org/dtp_candidate_98fe4a58

5055245

DTP (Test > Production: 98fe4a5)

Merge pull request #26144 from code-dot-org/test

491a3bd

Manual DTP

Merge pull request #26165 from code-dot-org/dtp_candidate_f8d3e5b7

5ae05d8

DTP (Test > Production: f8d3e5b)

Merge pull request #26182 from code-dot-org/dtp_candidate_8635c267

c50fd61

DTP (Test > Production: 8635c26)

Merge pull request #26192 from code-dot-org/dtp_candidate_66a8dd9d

8b378be

DTP (Test > Production: 66a8dd9)

Merge pull request #26216 from code-dot-org/dtp_candidate_4debf600

6e3c6ce

DTP (Test > Production: 4debf60)

Merge pull request #26239 from code-dot-org/dtp_candidate_62b77f31

6aa9b7c

DTP (Test > Production: 62b77f3)

Merge pull request #26254 from code-dot-org/test

730874d

DTP

Merge pull request #26275 from code-dot-org/dtp_candidate_8e91824a

df8c350

DTP (Test > Production: 8e91824)

Merge pull request #26279 from code-dot-org/dtp_candidate_01f49633

0eea843

DTP (Test > Production: 01f4963)

Merge pull request #26294 from code-dot-org/test

79289aa

DTP (Test > Production)

Merge pull request #26342 from code-dot-org/dtp_candidate_b3892251

e0544af

DTP (Test > Production: b389225)

Merge pull request #26348 from code-dot-org/dtp_candidate_f7c4ffe2

e361240

DTP (Test > Production: f7c4ffe)

Merge pull request #26356 from code-dot-org/dtp_candidate_3ca14161

0aeb01e

DTP (Test > Production: 3ca1416)

Merge pull request #26357 from code-dot-org/staging

68d8ed7

DTT (Staging > Test) [robo-dtt]

Merge pull request #26359 from code-dot-org/staging

450ff7f

DTT (Staging > Test) [robo-dtt]

Update user_proficiencies backfill script params for the 2018 CourseA…

fe2a600

…-CourseF levels backfill

uponthesun requested review from Hamms and bencodeorg December 14, 2018 01:39

Hamms reviewed Dec 14, 2018

View reviewed changes

Change default DATA_DIRECTORY to /tmp

0929a59

Hamms approved these changes Dec 14, 2018

View reviewed changes

bencodeorg requested changes Dec 14, 2018

View reviewed changes

Merge pull request #26367 from code-dot-org/staging

d5606b4

DTT (Staging > Test) [robo-dtt]

deploy-code-org and others added 2 commits December 14, 2018 12:24

Merge pull request #26375 from code-dot-org/staging

e359dc3

DTT (Staging > Test) [robo-dtt]

Add script id for 2018 express course to queries

648f7a8

bencodeorg approved these changes Dec 14, 2018

View reviewed changes

Erin007 and others added 3 commits December 14, 2018 13:30

Merge pull request #26376 from code-dot-org/dtp_candidate_e359dc39

dc4aecb

DTP (Test > Production: e359dc3)

Merge branch '2018-courses-proficiencies-backfill' of github.com:code…

6146837

…-dot-org/code-dot-org into 2018-courses-proficiencies-backfill

Add nil check for case where user's only recorded progress was during…

105a869

… already-recorded date range

uponthesun merged commit 30adfa6 into staging Dec 18, 2018

uponthesun deleted the 2018-courses-proficiencies-backfill branch December 18, 2018 22:53

This was referenced Dec 12, 2019

Proficiencies backfill 2019 [ci skip] #32390

Closed

Proficiencies backfill 2019 [ci skip] #32391

Merged

Search code, repositories, users, issues, pull requests...

Uh oh!

Conversation

uponthesun commented Dec 14, 2018 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

Hamms left a comment

Choose a reason for hiding this comment

Uh oh!

Hamms Dec 14, 2018

Choose a reason for hiding this comment

Uh oh!

uponthesun Dec 14, 2018

Choose a reason for hiding this comment

Uh oh!

uponthesun commented Dec 14, 2018

Uh oh!

Hamms left a comment

Choose a reason for hiding this comment

Uh oh!

Hamms Dec 14, 2018

Choose a reason for hiding this comment

Uh oh!

bencodeorg Dec 14, 2018

Choose a reason for hiding this comment

Uh oh!

uponthesun Dec 14, 2018

Choose a reason for hiding this comment

Uh oh!

bencodeorg left a comment • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

uponthesun commented Dec 14, 2018

Uh oh!

bencodeorg left a comment

Choose a reason for hiding this comment

Uh oh!

uponthesun commented Dec 17, 2018

Uh oh!

davidsbailey commented Dec 18, 2018

Uh oh!

uponthesun commented Dec 18, 2018

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

9 participants

uponthesun commented Dec 14, 2018 •

edited

Loading

bencodeorg left a comment •

edited

Loading