All videos are linked here: https://tufts.box.com/s/2od1yie9x92gdlz5667dqe01h0oyewsk
08/07/2024
...
8/13/2024 Summary and Timestamps
*TO DO: put mentioned queries into a single ‘training’ folder and label as FGI
...
TUSMGP Advanced Query Training #1
2:30 comparison of STL bases to CJ
3:30 difference between person and prospect: prospects automatically exclude opt outs
9:30 joining at the bottom versus joining in a subquery
equivalent when the joins are one to one, just about ease of adding things
14:00 unconfigured base joins in one-to-many relationships return the most efficient row that meets the criteria
if you are going a base join, you always get one specific row; if you want to look across the many, you have to do a subquery join
22:30 Does this person have ANY Schools like Columbia--subquery filter Schools/ Name Contains; applications that are not necessarily rank 1
why join at bottom instead of subquery?
27:18 “Helen did it”
35:00 Specific people opts out: either opt out or phone opt out, both
8/13/2024 TUSMGP Advanced Query Training #2
Mentioned queries are here: https://tusmgp.admissions.tufts.edu/manage/query/build?id=e4057c99-6c17-4924-9eeb-5d949c7c1bb6?shared=shared&q=TEST %2F Training&user=84d5f115-617e-4b72-9981-70befea5b71f
2:00 Mailing Touchpoints: how many received, opened, clicked
shift from mailing base to
person
13:00 nested subquery formula export in order to return Y/N for ‘more than 3 mailings’ mostly as a proof of concept--Existence exports are just case statements in a nicer interface
Also, watching back the part where the case statement was giving us an error, I now see that there was just a typo in the formula "(case @Count when >= 3 then 'Y' else 'N' end)" when that should have been "(case when @Count >= 3 then 'Y' else 'N' end)"--here's the updated version: https://tusmgp.admissions.tufts.edu/manage/query/build?id=a71615f8-e7b0-443a-b4df-9db16d1f428b
19:30 sample build of traditional nested count subquery
23:00 create a query looking at all of review form responses
34:00 not in straight export versus not exist subquery: not exists also includes null values
Here is the link to the query we were working on at the end, comparing the recommended decision of the first reviewer to the recommended decision of the second reviewer:
https://tusmgp.admissions.tufts.edu/manage/query/build?id=e4057c99-6c17-4924-9eeb-5d949c7c1bb6
I also mocked up a quick example query to illustrate the Decision NOT IN case: https://tusmgp.admissions.tufts.edu/manage/query/build?id=32dcc17d-100b-43fa-aaae-1f7356ce96c5 (filtering for NOT IN Withdraw returns only people who have any rank 1 confirmed decision that is not the withdraw decision, meaning people without any decision are excluded, so filtering for both NOT IN Withdraw and Not Exists a rank 1 confirmed decision will return 0 records)
...
On my list of items to revisit in future sessions:
-Finding potential twins (people where there exist other people with the same address, birthdate, and last name)
-NOT EXISTS/IN vs. EXISTS/NOT IN
8/21/2024 TUSMGP Advanced Query Training #3
1:00 comparing a base join that is filtered in the base filters (not in the join itself) to having subquery exports that contains the join. Two queries, both on person
base joins: Form Response, Form; filter Form type sort:Form start date
filter: Form folder
exports: Form Response submission date, status, Form title, folder [direct exports]
base joins: none
filter: [subquery] join to Form Response, Form; filter itself EXISTS: Form folder; within Form Response, joining to Form; filter Form type; sort Form start date
exports: Form Response submission date, status, Form title, folder [direct exports]
Then each export is a subquery joining Form Response, Form; filter Form type sort:Form start date
5:00 join inside a subquery versus a subquery join.
INSIDE--the big button-- is looking across all the rows--”exists” in the common use case; SUBQUERY JOINS return just one.
8:30 nested subquery filter adding a filter to return the first event; then a separate filter making sure that first event happens to be in one of the relevant event folders
9:45 this is the same as doing a base join and filtering
[This is a good section for a build-along-at home exercise--build both types of subqueries to see that they do return the same number].
23:45 all interactions and how many of each there are: independent subqueries!
Not Exists/In vs. Exists/Not In-- whether or not there are null values matters in your formatting options.
35:15
We touched on subquery joins vs. joins inside subqueries, took a detour into independent subqueries and system bases for lookup.activity, and then discussed various combinations of EXISTS, NOT EXISTS, IN, NOT IN, and NOT.
...
Both of those would return applications that have at least one decision other than admit, but neither would exclude applications that also have an admit code. That was why we needed to add the second filter for NOT EXISTS code IN Admit.
8/28/2024 TUSMGP Advanced Query Training #4
00:30 Query Libraries
review of the original STL exports and filters in comparison to CJ’s
6:50 library export checkbox
the old STL’s configurable filters we labelled in red as needing configuration; those fields that need configuration or renaming of some sort have instructions in [brackets]
12:45 sample build with Query Library fields
17:00 aggregates and how they work
19:00 first level join (on person, to application) with a concatenate/count gets you the number of applications each person has.
What happens if you add another join (still no filters) to decisions? What is getting counted? if, within the aggregate subquery export, you join out to the decisions table, the unfiltered count is ON THAT LAST JOINED TABLE
...
27:00
41:00 Filter Schools
9/11/2024 Reporting Demo
The reports we started on events and mailings can be found here:
https://tusmgp.admissions.tufts.edu/manage/report/render?id=271c0756-a2a2-4ecf-9c66-1b329699a06b
https://tusmgp.admissions.tufts.edu/manage/report/render?id=db5914b7-af9a-4cf0-ac4e-15b1340fd5c7
Once we've wrapped up the full rebuild of the other reports, I'll share those links as well.
The four legacy reports we identified for rebuilding have all been completed, along with the two demo reports for events and mailings that we mocked up for Alessa and Hannah on the call:
...