Donor Database Segmentation App

BUSINESS NEED My Faith Votes (MFV) is a non-profit ministry with a mission to get Christians to vote. The ministry is financed through online or postal mail donations (Direct Mail). When sending to the “house file” (Donors who have given at least once.), there is a need to segment the list of those being mailed through postal mail. The segmentation is based on: • When the person last donated. • The channel(s) the person previously donated through (direct mail, online, or both). • The largest donation amount ever given, i.e., the Highest Previous Contribution (HPC). APPLICATION OBJECTIVE The Direct Mail Segmenter (aka The Segmenter) is an application that will be used to segment the My Faith Votes (MFV) database of donors (aka House File) for direct postal mailings. Each donor will be assigned a segment code, and specific segments will be selected for a mailing (aka Campaign). APPLICATION REQUIREMENTS SEGMENTATION CODING The Application will not require a User Interface (UI) but will run autonomously on a scheduled basis, likely daily. Errors in execution of the Application such as database connection, file access errors, etc. should be reported by email to a hard coded email address. The Application is to code all donors in the [login to view URL] table with a Segment Code. A person might have one of 252 possible segment codes, but they can only have one such code at a point in time. The structure of Segment Codes is defined below, and the Application is to generate a code for each person based on the criteria defined here. (All 252 possible codes are shown in the Appendix.) An individual donor is to be assigned only one Segment Code based on the following criteria/model: SEGMENT CODE MODEL: STTTLMMCNNN S (Donor Status) D = Donor, i.e., the Segment Code always starts with a 'D’ as only donors are to be included. TTT (Target Group) 000 = Existing (For Donors group only). This is the only current TTT value allowed within this Application. L (Single or Multi-Donor for the period they last gave in.) S = Gave only once in the period M = Gave multiple times in the period   MM (Last time donor gave.) 03 = Gave in the last 0 – 3 Months 06 = Gave in the last 4 – 6 Months 12 = Gave in the last 7 – 12 Months 18 = Gave in the last 13 – 18 Months 24 = Gave in the last 19 – 24 Months 25 = Gave in the last 25+ Months C (The Channel the donor gave through. Online, Direct Mail, Telemarketing, etc.) D = Direct Mail N = Not Direct Mail M = Multiple Channels one being Direct Mail NNN (Highest Previous Contribution (HPC) – Largest Amount Ever Given.) 101 = $0.01-$4.99 102 = $5-$9.99 103 = $10-$24.99 200 = $25-$49.99 300 = $50-$99.99 400 = $100-$999.99 500 = $1000+ EXAMPLES All examples assume the date of the direct mail list pull is 2/28/2024 for this specification. The actual date used in the Application will be the run date and time. Example #1 o Donor made an online donation of $50 on 1/4/2024 Segmentation Code: D000S03N300 Example #2 o Donor's first donation was on 1/1/2021 for $75 through a direct mail campaign, and o The donor made a second donation through a direct mail campaign on 12/31/2021 for $100 Segmentation Code: D000S25D400 Example #3 o Donor's first donation was on 5/1/2016 for $500 and was made online o The Donor’s second donation was made on 6/1/2016 for $500 through direct mail, and o The Donor’s third donation of $1,000 was made on 12/31/2022 through direct mail Segmentation Code: D000S24D500 Example #4 o Donor's first donation was on 6/1/2021 for $10 through a prospecting direct mail campaign, o The Donor’s second donation for $50.00 was made on 12/31/2022 through an online campaign, and o The Donor’s third donation of $10 was made on 4/5/2023 through an online campaign Segmentation Code: D000S12N103 The project consists of the development of an Application that will: 1. Obtain data required by the Application from a PostgreSQL database referred to as the CORE, 2. Create a Segment Code and calculate supporting data required in the Segmentation Table and 3. Update the Segmentation Table (within the same database) with the new segmentation data. The Application creates/updates the Segmentation Table containing the gathered and calculated data fields for each donor in the CORE database. The following conditions determine the selection of a person’s data from CORE: 1. All individual donors in CORE ([login to view URL] and [login to view URL]) will be included in the Application output. 2. Some records are to receive special classifications based on data contained in the CORE. These classifications currently are 1) Faith Leaders, 2) Recurring Donors, and 3) Limited Communications. 3. The Application should log (a single line in the log file containing this information is sufficient): a. Start of execution, b. Number of records removed, added, and updated in the Segmentation Table, and c. End of execution. The Direct Mail Segmentation Table consists of one row per donor with data for that donor in the 27 columns listed below: PEOPLE ID SEGMENT_CODE PREFIX FIRST_NAME LAST_NAME SUFFIX ADDRESS 1, ADDRESS 2, ADDRESS 3 CITY STATE ZIP COUNTRY_CODE ASK_AMOUNT (Always NULL) HPC (Highest Previous Contribution Ever) MRC (Most Recent Contribution) FED_DISTRICT (Always NULL) YTD_GIVING PREVIOUS_YEAR_TOTAL_GIVING MAX_LIFETIME_GIFT AVG_LIFETIME_GIFT DATE_OF_FIRST_DONATION LAST_18M_DONATED FAITH_LEADER_MEMBER - ACH RECURRING DONOR LIMITED COMMUNICATIONS LAST_GIFT_AMOUNT   PRODUCTION ENVIRONMENT The production environment consists of: Operating System: Ubuntu Programming Language: Python 3.10 Database: PostgreSQL Output: Updated database table Access to a development version of the database will be provided by an SSH tunnel connection and secure private/public key pair. The production database will consist of more than 2.5 million person records, with only approximately less than 250,000 of those as donors. There are no overly restrictive performance speed requirements, though it is anticipated that the complete execution should take less than an hour. The data for this Application are available in six tables in two schemas within the CORE database, namely: [login to view URL] with relevant columns of: COLUMN_NAME DATA_TYPE id integer first_name text last_name text Prefix text created_date timestamp without time zone last_modified_date timestamp without time zone Plus other non-relevant columns… [login to view URL] with relevant columns of: COLUMN_NAME DATA_TYPE Id integer mfv_id ([login to view URL]) integer street text city text state text postal_code text country text state_abbreviation text is_bad boolean is_primary boolean created_date timestamp without time zone last_modified_date timestamp without time zone Plus non relevant columns… [login to view URL] with relevant columns of: COLUMN_NAME DATA_TYPE id integer mfv_id ([login to view URL]) integer transaction_number integer amount double precision appeal_id integer campaign_id integer designation_number integer fund_name text created_date timestamp without time zone last_modified_date timestamp without time zone Plus non-relevant columns… TABLE RELATIONSHIPS "mfv_id" attribute in the "[login to view URL]" and [login to view URL] tables have Foreign Keys that reference the "[login to view URL]" attribute in the "[login to view URL]" table. Every donor is in the [login to view URL] table, but not every person in the [login to view URL] table is a donor. People within the [login to view URL] table with the following conditions are never to be included in the Segmentation Table: 1. With ‘status’ column containing the text ‘deceased’, 2. An address in the [login to view URL] table where ‘is_bad” is true, 3. A record in [login to view URL] that has an incomplete address, i.e., is missing street, city, state, or postal code, or 4. A record with communications_restrictions as do_not_mail or do_not_solict. When a person in [login to view URL] has multiple addresses in the [login to view URL] table, the address flagged as is_primary is always to be used for segmentation. The database allows only one address for a [login to view URL] to be primary.