DAT Webinar - Development Level I

Please use this article as a supplemental resource for this DAT webinar: link

1. Intro
a. The Data Analytics Tool (DAT) is a comprehensive system for creating on-the-fly filters, exports, dashboards, print templates, emailing, queries and reports supporting advanced database concepts with left joins, logical conditions, aliasing, aggregates, pivots, predefined reports, parametrization, and flexible security access.
b. Cover the basics of building Development related queries. Send emails to support@pcreducator.com for suggestions.
c. DAT Query considerations
i. Plan what information will be extracted.
ii. Plan a workflow on what tables are used and how they will be connected.
a. Most common tables in the DAT - Development Data Structure
i. Donor - Used to pull basic donor information.
ii. Donation - Used to pull basic donation information.
iii. Contact Attributes - Used to pull contact attributes such as Current Parent, Board Member etc.
iv. Households - Used to pull family information such as address, emails, phone numbers and more.
v. Fund Allocation - Used to connect donation table to campaigns and funds table and the amount allocated to each.
vi. Campaigns - Used to pull campaign information such as campaign description, type, goal and more.
vii. Funds - Used to pull fund information such as fund description, fund code, and more.
viii. Relations - Student/Inquiry/Teacher - Used to connect the households table to students/inquiries/teacher records.
2. All Donors with Donations Query
a. Start with the Donor table and select common fields such as donor id, contact name, and donor type.
b. Connect the Households table and select common fields such as address, contact names, emails, and phone numbers.
c. Connect the Donor table to the Donation (Giving) table and select common fields such as donation amount, donation date, donation type etc.
i. What is the Donation Type field used for and what is the difference between Original Amount, Donation Amount, and Written Off Amount.
d. Connect the Donation table to the Fund Allocation table.
e. Connect the Fund Allocation table to the Campaigns and Funds tables and select common fields.
f. Organize the fields, change the sort order, and change the column order
i. Update Aliases and Display formats
g. Exclude the Pledge Payment donation type from the query because this query is to show donations that have either been pledged, sent in as a one time gift, or are soft credits. If both Pledge and Pledge Type donation types are included in the query, it will count the amounts twice.
i.Show how the conditions work.
h. Change this into a query that only shows donors with students affiliated with the school.
i. Connect the Relations - Student/Inquiry/Teacher table to the Households table.
ii. Connect the Student Info table to the Relations - Student/Inquiry/Teacher table.
a. Optionally, the query can even restrict the results to primary households or grandparent households in the relations table.
i. Student Info table - Used to pull basic student information.
3. Total Donations by Fiscal Year
a. Start with the Donation table.
i. Exclude Pledge Payments and Soft Credits.
ii. Add a Fiscal Year Offset multi-select condition for the Current and Last 4 Fiscal Years.
b. Navigate to Fields and update the Display Format for Donation Amount.
c. Set Pivot Aggregate to Sum Donation Amount and run the query.
d. Add other conditions to this query as needed. For example, to total up soft credits set the condition on the table to only include soft credits.
4. Update an existing Standard Query - [Development] Fiscal Year
a. This query lists the sum of donations that each family donated in the last 5 years.
b. Add household information.
i. Connect the Donor table to the Households table and select desired fields.
c. Save the query.
i. When updating a standard query and re-saving it, select a different name and store it in a different group. If the query is kept it in PCR(Imported) group, it will be overwritten.
5. Add Queries to Dashboard
a. How to add a query
6. Run a DAT Query report through multi-action

Schedule Demo

Please, make sure that all required fields (marked with *) are completed.

School Name *

Relation to School *

select

Last Name *

Your First Name *

Your Email *

Your Phone # *