Donor Database Segmentation App
Budget: $1,500 – $3,000 USD
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 mfv.people 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 (mfv.people and dms.donations) 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:
mfv.people 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…
mfv.addresses with relevant columns of:
COLUMN_NAME DATA_TYPE
Id integer
mfv_id (people.id) 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…
dms.donations with relevant columns of:
COLUMN_NAME DATA_TYPE
id integer
mfv_id (mfv.people.id) 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 "mfv.addresses" and dms.donations tables have Foreign Keys that reference the "people.id" attribute in the "mfv.people" table. Every donor is in the mfv.people table, but not every person in the mfv.people table is a donor.
People within the mfv.people 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 mfv.address table where ‘is_bad” is true,
3. A record in mfv.addresses 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 mfv.people has multiple addresses in the mfv.addresses table, the address flagged as is_primary is always to be used for segmentation. The database allows only one address for a people.id to be primary.
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 mfv.people 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 (mfv.people and dms.donations) 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:
mfv.people 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…
mfv.addresses with relevant columns of:
COLUMN_NAME DATA_TYPE
Id integer
mfv_id (people.id) 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…
dms.donations with relevant columns of:
COLUMN_NAME DATA_TYPE
id integer
mfv_id (mfv.people.id) 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 "mfv.addresses" and dms.donations tables have Foreign Keys that reference the "people.id" attribute in the "mfv.people" table. Every donor is in the mfv.people table, but not every person in the mfv.people table is a donor.
People within the mfv.people 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 mfv.address table where ‘is_bad” is true,
3. A record in mfv.addresses 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 mfv.people has multiple addresses in the mfv.addresses table, the address flagged as is_primary is always to be used for segmentation. The database allows only one address for a people.id to be primary.