...
Fletcher CJ Training #2 9/11/2024
We revisited the joins to addresses, explored a similar case with schools, and got our first look at subqueries. We discussed how the choice of base determines what each row in the query represents (one row per person vs. one row per application vs. one row per school etc.). Toward the end of the call, we touched briefly on querying for form responses, which we'll cover in more depth next week. If you're building queries throughout the week and trying to recreate what we did on the call, be sure to save any examples where you're not seeing the results you expect!
1:50 review of address build
7:00 determination of table ranks https://knowledge.technolutions.net/docs/database-structure-determination-of-table-ranks
10:30 renaming joins to prevent confusion
14:00 adding 'or' in filters
15:50 old STL 'active' address is the same as #1 rank overall
16:45 application by population base
18:00 sample query, application base: finding applications from people who went to Tufts before (rank 1)
26:00 subquery: looking for Tufts across all previous schools
31:50 check logic button
33:00 (the problem of) base joins to multiple schools
36:00 adding sorts and filter to that base join
39:00 one row per school
44:30 different types of bases
46:30 sample query school base
50:00 sample query form responses
53:00 join to form
53:45 question looking for the first form response
...
51:50 different types of decision by rank joins
Fletcher CJ Training #5 10/11/2024
We discussed a number of decision examples, comparing how we could pull in the most recent decision or use a subquery filter to check across all of a person's decisions, and what a subquery export would look like if we wanted to pull a comma-separated list of all their decisions. We also looked at how we could use subqueries to check for the existence of another application for the same person, where that other application had a deny decision. Finally, we looked at how we could use comparisons in subquery filters to compare various dates, like the first event date vs. the app submitted date.
00:35 sample build: finding admitted applicants
2:30 decision by rank, decision by rank confirmed, decision by rank released, decision by rank reversed
6:00 subquery filter within decision
8:00 no default sort on base subquery joins (doesn't matter for decisions, usually, but does matter for other things)
9:25 most recent sort: descending order
14:20 concatenate list of decisions
19:00 existence subquery filter
25:00 subquery versus base join
32:15 sample build: has previous application with deny decision
35:30 count of apps > 1
40:00 nesting subqueries to compare to make sure we are not looking at the current app
44:00 date comparisons
46:00 wizard hat on joins provides more export options automatically
Fletcher CJ Training #6 10/18/2024
In this session, we walked through a number of troubleshooting examples where joins were misconfigured or where nuances of EXISTS NOT IN vs. NOT EXISTS IN affected the results. Leigh also asked about the location information that sometimes appears on student timelines tied to message clicks or ping activity; the message track query base does have an export for Region, and we could get at country as well with a formula export. Other location information on the timeline appears to be derived from the [person.login] table, and one of the library exports that FGI added includes the most recent login city.
:30 troubleshooting: why don’t I get Tufts students in this query?
5:00 why does a Slate ID search in the old Prospect base not return the particular student? Old prospect base excluded records tagged with the Opt Out
7:15 How to reconstruct that Prospects without opt-outs in CJ’s
8:45 the problem with base joins to tag records
12:15 the difference between EXISTS NOT IN and NOT EXISTS
16:20 example of applications that do not have the admit decision
24:00 not exists, not in
32:20 question: api feed from Bird to create interactions--how tricky is it? discussion of source formats
40:00 Tristan found the brand new Inquiry Details entity
41:20 IP addresses/location data--querying on messages
message is one row per message received
message track--one row per open/click
login table has city data
Fletcher CJ Training #7 10/25/2024
In this session, we walked through the library exports that FGI has set up in the Fletcher instance and also talked about how to create your own library exports for common exports and filters that you'd like to reuse in future queries. As we saw, sometimes library exports and filters can be added without any additional configuration, but in most cases they still expect that you would configure something about them: a tags filter for example might expect that you select the tag name you want to check for, it would just save you the steps of adding joins to get to that point.
0:00 Query Library
3:30 exports and filters organized by base
3:45 ability to create custom Query Library
5:00 individuals have to be added in order to have access
6:05 [CONFIGURE] in name requires end user adding data
8:00 once you add something to your query, you can adjust it and not affect the master version
8:50 cut and pasting exports
9:48 creating your own personal query library
[Nick will own the query library for Fletcher]
15:45 tags
17:15 practice building with query library
22:10 sample: subquery filter on export to show ONLY the specified tag, with blanks if the person did not have the value
25:45 rather than the specific people, which would be controlled by a filter
27:15 population discussion
30:00 query library filter finding people who have x number of applications
36:20 origin source sample; configuring the joins
Fletcher CJ Training #8 11/1/2024
We explored some more advanced concepts like independent subqueries and closed things out with a discussion of some of the less common query bases.
2:00 independent subquery example: next event happening near student address
5:30 filter for date within the next month (today + 30)
13:20 comparing first source to whether or not there is an application
15:30 existence export to determine if there is an application
21:00 reminders abut most recent versus any
22:00 other query bases
form query: one row per event
form response: one row per registration
message: one row per email received
message track: one row per click/open
27:05 system: lookup tables, list of fields/prompts
count of times a particular prompt value is selected within records
Nov 11: Reporting #1
1:30 navigation
2:00 definition and sample build
8:00 adding columns
9:15 adding a data table
10:30 group by program
12:10 year over year comparisons
13:30 calculations/formula as column type
17:10 sorting rows
20:30 limiting the number of rows returned
22:00 validating report numbers
25:45 counting form responses
33:30 adding grouping by month using format masks
36:30 column groupings
39:30 reporting on first event attendance
45:45 reporting on person-scoped events
47:50 export to query, including using top-level exports
50:30 pie charts, geo maps
54:30 timeline
Nov 18 Reporting #2: Rebuilding High Value reports demo
1:00 Year-to-Date Application Report with percentage change
13:00 duplication of row filter/column filters
17:00 redundant date filters
22:30 nullif in formula
23:00 format percentage
28:00 format mask for row name when grouping
31:00 static content/section titles
31:20 adding citizenship section
33:30 race and ethnicity section
34:15 round key as an evergreen filter
36:00 multi-value fields (race)
39:20 double counting --disaggregate ‘2 or more’
45:45 Event Attendance
46:50 links out to specific events
51:45 using specific form response base for a specific event
Slate Captain CJ’s Deep Dive 9/17/2024
0:00 difference between local and CJ’s
3:30 recreation of delivered STL exports and filters as Library Export
5:30 old modifying local filters and exports and how that compares to the new query libraries
11:00 option to restrict permissions in library exports
12:00 toggling which groups are available
12:25 extended exports
16:00 new nomenclature can be a stumbling block
18:40 can build your own personal query libraries
19:25 configure in name of exports
20:30 can you make a deliminated export split out into multiple columns?
20:45 no, one export = 1 column, but can duplicate and use rank 1, rank 2, etcetera
22:22 schools 1-5 are included in the library exports
23:30 explanation most common related bases
Form Responses
27:00 effectiveness of yield events: connecting the form response to application and person
35:55 reports versus queries for counting mixing scopes
35:50 aggregate subquery exports counts final join
39:00 nesting subquery filters
41:00 total event registrations
45:00 independent subquery comparisons
49:00 comparison of app dates and event dates
--still has one more hour of time with Tristan--