Fletcher CJ Trainings/Notes from FGI

Direct link to Fletcher Training folder: Tufts Fletcher Slate Reporting-20241115.mp4

If Tristan provided notes/summaries, those appear before the timestamps.

Fletcher CJ Training #1 9/4/2024

Introductions
1:50 overview of what FGI is doing/goals
3:30 wants people to be interactive; has standard examples, but wants to use real examples
4:21 Bases/person v.application
10:00 one to one and one to many relationships
10:30 related records
17:45 sample build, everyone who has a rank 1 address in MA
22:40 different types of exports and how they are grouped
24:30 adding rank 2 address
25:37 deleting exports using ctrl-click and drag
28:00 adding filter for rank 2 address in MA
30:30 filters are automatically 'and'
32:00 sample build, applications from people whose rank 1 address is MA
24:30 base join
41:00 direct versus extended exports
recommendation to keep extended exports turned off, and do joins directly to the query overall
50:00 direct join
53:00 one row per base

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 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

Fletcher CJ Training #3 9/20/2024

We reviewed a couple of joins for address and application, then explored how Slate can help answer the question of which specific form response was submitted first. We validated the joins we added by comparing the results inside a subquery export, talked about sorts, and mentioned the ability to filter date fields using relative values like "today" or "today - 3 years". As attribution reporting was of interest to the group, we previewed what was possible with origin groups and sources. While most of the time this will be used in queries for person records or applications, we also talked about how it was possible, at least in theory, to go the other direction, pulling a list of origin sources (or forms, interactions, etc.) and finding the count of records for which that was the first source.

1:00 Troubleshooting: why is US missing in list of countries in exports--in STL exports, Tristan showed how US is hard coded to return null--may have been custom coded for Fletcher
8:00 review of CJ version of that query
12:00 using a subquery to find all of the enrolling students
14:45 direct v. extended filter reminder
16:15 using <> with dates
16:55 relative dates (today - x) days are default; weeks, years, fortnights ?! but probably not months
18:45 subquery export from applications
19:40 concatenate output
21:30 form responses, starting with people
22:50 adding a sort
23:40 adding details of which form
24:40 limiting which forms are returned
29:30 adding subquery export of other forms returned
36:50 Origin Sources overview
44:00 Sample query on Origin Sources
47:45 existence export
48:30 system query : one row per origin source group name, or origin group source type
--> Tristan off in the weeds satisfying his own curiosity: independent subqueries, reviewing the SQL in a custom SQL base, trying to see what he can get attached back to person for origin sources
but does eventually come back round to doing a count of people who have each type of origin source

Fletcher CJ Training #4 10/2/2024

Leigh asked a question about ways to query on application progress; while there isn't an out-of-the-box way to get at this data, we explored a few thoughts on the call:

• For a Slate-hosted application, when someone clicks through the pages of the application, what actually happens behind the scenes for custom application pages is that the form response is submitted when they click away. So, while it would be a rough approximation, you could in theory pull the count of form responses associated with their application record: e.g. someone starts an application and has clicked through the first three pages of the app, I would expect to see 3 form responses started. That's not question-level data, and legacy app pages built with XSLT rather than custom forms would not be stored in quite the same way, but it could still be interesting to track.

• In terms of materials received/checklist items fulfilled, this is more straightforward. You can use a subquery export to get the aggregate count of the total checklist items that someone needs to submit, and then pull the count of those with a certain status, like all those still in 'Awaiting' to calculate percent complete. It's possible for a subquery export to calculate this using a formula, but the issue we ran into on the call is that the nested export for an aggregate returns an integer and doesn't easily allow you to convert this to a real number, which you'd need for the percentage. Tinkering with this after the call, I found that you'd either have to nest this one layer deeper, add some custom SQL to the formula (a try_convert), or rewrite the formula to multiple the integer values by 1.0 to force Slate to convert the data type to real. If any of those are of interest, let me know and I can mock up an example for our next call, but I would say those are all fairly advanced/not something you would encounter day-to-day when building queries.

• The 'In Progress' link shown on an individual record will display the hard fails/application requirements not yet received, but this still isn't available to query on directly as the conditions are just evaluated on the fly based on the current app logic. In order to get at this in the aggregate, it would be necessary to recreate all of the hard fail logic in a query, which likely wouldn't be worth the lift.

We also discussed the differences between a base join and a subquery filter, using as our example the goal of trying to return a list of admitted applications. We explored why trying to answer the question "Does this application have 'Admit' among its decisions?" uses a subquery (looking across the many) while "Does this application have 'Admit' as its most recent decision?" would use a base join (we're interested in one specific decision, their most recent).

00:25 Fletcher question: partial applications (unsubmitted) can you determine how much of it an applicant has done? Or at least how many documents they have submitted?

16:00 Lead scoring possibilities

21:30 clicking on the status link in the application dashboard shows the hard fails under app logic

28:20 export values in queries--short value shows by default, if it exists

29:35 translation codes

31:00 export timestamps for when fields were set

33:40 sample subquery build admitted applicants

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

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

13:20 comparing first source to whether or not there is an application

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

Nov 18 Reporting #2: Rebuilding High Value reports demo

1:00 Year-to-Date Application Report with percentage change

45:45 Event Attendance

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?

23:30 explanation most common related bases

27:00 effectiveness of yield events: connecting the form response to application and person

35:50 aggregate subquery exports counts final join

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--