Directions for Pulling Contact List for C3 Letter

Step By Step Mailing Instructions

1. In Salesforce, copy Year End FY23_ Donors & Lapsed Donors. Double check steps 1-3 are correct, move to step 4

2. Add columns:

   1. Salesforce ID (***unique identifier that will be used to re-import into Salesforce)

   2. Formal Greeting

   3. CAC Member

   4. Last Gift Date

   5. Last Gift Amount

   6. Informal Greeting

   7. Billing Street

   8. Billing City

   9. Billing State/Province

   10. Billing Zip/Postal Code

   11. Email address

   12. Total # of Gifts

   13. Total Gifts $

   14. Average Gift 

   15. Smallest Gift

   16. Largest Gift

   17. Total Gifts Last N Days

   18. Total Gifts Last Year

   19. Account Name

   20. Member Type

   21. Number of Household Members

   22. Unique Accounts 

   23. Unique Contacts

3. Add filters:

   1. Last Gift Date => to whichever date in the past you want to set. How many people do you want to send the list to?

   2. Account Record Type = Household Account

   3. Household Do Not Mail = False

   4. Account Name ≠ Anonymous for Donations Household

   5. Requests Single Annually Mailing = False

   6. Count of Living Contacts > 0

   7. Billing Street ≠ “ “

   8. Member Type ≠ Workplace Donor

   9. Primary Contact = True

4. Save and run report in Salesforce BY NEW CAMPAIGN NAME. 

   1. Use campaign  “1 Mailer Per year” to cut people in the Spring season and for Elections mailers. 

   2. Sort name columns from A-Z and then Z-A. Look for missing information or false information. Correct or delete these in Salesforce.

5. Export to Excel – Deduplicate entire report using Formal Greeting and Mailing Street. 

   1. Re-run report in Salesforce and export if necessary.

   2. Add individuals in this report to campaign by “add to campaign” button to track return rate

6. Calculate the suggested donation amounts

   1. Ask 5 is Last Donation 

      1. (Or $35 Individual Membership if they don't have a last donation)

   2. Ask 4 is Largest Donation 

      1. (Or $65 Household Membership if they don't have a last donation)

   3. Ask 3 is the largest donation plus 25%

      1. =IF(Y2*1.25>35,CEILING(Y2*1.25,10),35) 

         1. his formula checks that the largest donation plus 25% is going to be more than the $35 Individual Membership, and then if not, it just shows the Individual Membership

         2. ***Y2 is the cell for largest donation*** 

         3. CEILING(____,10) rounds whatever is in that underline area up to the next 10. 

   4. Ask 2 is the largest donation plus 66%

      1. =IF(Y2*1.66>65,CEILING(Y2*1.66,10),65)

         1. This formula checks that the largest donation plus 66% is going to be more than the $65 Household Membership, and then if not, it just shows the Household Membership

         2. ***Y2 is the cell for largest donation*** 

         3. CEILING(____,10) rounds whatever is in that underline area up to the next 10. 

12. Sort entire worksheet by column 1 from low to high. Reverse the order 4, 3, 2, 1 (last giving amount)

13. Correcting Asks

    1. Scroll through and fix incorrect/weird ask amounts in all contacts

       1. 0 or 5 in the last digit 

       2. Decimals

       3. They’re a major donor -- replace with #s or delete row

14. Copy worksheets to create new sheets called “Final” and “Emails”.

15. Delete columns to prep file for sending

16. 1. Account Name

    2. Member Type

    3. Donation history

    4. Recency, Frequency, Monetary Value

17. Shorten the names of the remaining columns to Formal, Informal, Street, City, State, Zip.

18. Setting up display 

    1. Create 3 new columns called FL (Formal Length), SL (Street Length) and CSZL (City, State, Zip Length)

    2. Use formula =len( ) to give you the number of characters in each field.

REASON: Order the mailing so households and addresses with the longest text are at the bottom and shortest names and addresses are at the top. This progression helps with designing the mailing and with lining the display up properly with our mailing machine.

19. Create a Total Text Length column 🡪 use formula =max(three len columns) to determine the longest text

20. Sort entire mailing by Total Text Length and edit any field that is more than 40 characters (43 is the actual limit so use your discretion if you can't get it that low). Resort after all the lengths are good, A to Z (shortest at the top).

21. SAVE the working file, then save as new excel doc called “_Final” as CSV and delete other tabs. Send Final data document to designer, Emily Zuwiala-Rogers, or if using Northern Liberties to Ken Martz

Directions for Pulling Contact List for C4 Letter

Step By Step Mailing Instructions

1. In Salesforce, copy C4 - Election Appeal FY23.

2. Add columns:

   1. Formal Greeting

   2. CAC Member

   3. Last Gift Date

   4. Last Gift Amount

   5. Informal Greeting

   6. Billing Street

   7. Billing City

   8. Billing State/Province

   9. Billing Zip/Postal Code

   10. Email address

   11. Account Name

   12. Member Type

   13. Number of Household Members

   14. Unique Accounts 

   15. Unique Contacts

   16. 501C4 person

3. Add filters:

   1. Last Gift Date => to whichever date in the past you want to set. How many people do you want to send the list to?

   2. Account Record Type = Household Account

   3. Household Do Not Mail = False

   4. Account Name ≠ Anonymous for Donations Household

   5. Requests Single Annually Mailing = False

   6. Count of Living Contacts > 0

   7. Billing Street ≠ “ “

   8. Member Type ≠ Workplace Donor

   9. Primary Contact = True

   10. 501c4 Person = True

4. Save and run report in Salesforce BY NEW CAMPAIGN NAME. 

   1. Use campaign  “1 Mailer Per year” to cut people on this list from the current mailing list 

   2. Sort name columns from A-Z and then Z-A to Look for missing information or false information. Correct or delete these in Salesforce.

5. Export to Excel – Deduplicate entire report using Formal Greeting and Mailing Street. 

   1. Re-run report in Salesforce and export if necessary.

   2. Add individuals in this report to campaign by “add to campaign” to track return rate

6. Create 5 columns with Ask amounts. Consider RMF scores in the future once more C4 donations

   1. Surprise us!  _____

   2.  $50

   3. $100

   4. $250

   5. $500

7. Copy worksheets to create new sheets called “Final”

8. Delete columns to prep file for sending

   1. CAC Member

   2. Last Soft or Hard Credit Date

   3. Last Soft or Hard Credit Amount

   4. Account Name

   5. Member Type

   6. Number of Household Members

   7. Unique Accounts

   8. Unique Contacts

9. Shorten the names of the remaining columns to Formal, Informal, Street, City, State, Zip.

10. Setting up display

    1. Create 3 new columns called FL (Formal Length), SL (Street Length) and CSZL (City, State, Zip Length)

    2. Use formula =len( ) to give you the number of characters in each field.

REASON: Order the mailing so households and addresses with the longest text are at the bottom and shortest names and addresses are at the top. This progression helps with designing the mailing and with lining the display up properly with our mailing machine.

11. Create a Total Text Length column 🡪 use formula =max(three len columns) to determine the longest text

12. Sort entire mailing by Total Text Length. Save as new excel doc called “_Final” and delete other tabs.

13. Send Final data document to designer, Emily Zuwiala-Rogers, and update Northern Liberties, Ken Martz, or process