Platforms Used for Creating Direct Mail Lists

@Tyyne Parakhen I use Query and RE Mail (Quick Letters) for Segmentation.

@Tyyne Parakhen
get rid of the crystal report as it is crazy.

if your annual giving officer need to see a grid like this to make deicsion, you can easily produce this using query and excel.

then once your officer decided on the “grid”, use query to target the grid and export the mailing list.

@Tyyne Parakhen
config 1 export (Query module export or Export module export) that will give you the biggest “net” to cast filter on. I like using Query module export.

• Gift Dynamic Query
  • filter:
    gift date ≥ ‘1/1/2018’ and gift amount >0 and gift type of your choossing depending on if you do payment or pledge, not both).
  • output:
    Constituent ID
    Name and all mailing info data
    gift id
    gift date
    gift amount
    gift type (not really needed, but good to see)
    

now it's playing with excel formula SUMIFS.

assuming my gift exported data is:

in another worksheet, copy the constituent id over and remove duplicates, so it is 1 constituent id per row. then…

I put both in the same worksheet like so:

note formula:

• 10/22/2024 is =TODAY()
• 7/22/2024 is =EDATE(H$2, -3)
  • my H$2 is 10/22/2024, basically saying minus 3 months from TODAY()
• 7/21/2024 is =H$1-1
  • my H$1 is 7/22/2024, basically minus 1 day
  • this can also be =EDATE(H$2, -3)-1
• 4/22/2024 is =EDATE(H$2, -6)
  • basically saying minus 6 months from TODAY()
• this is for demo only, you can use the same method to create the other giving total by date range for the rest of 6-12 months, 12-18 months, etc.
• the SUMIFS formula is:
  • =SUMIFS($C:$C, $A:$A, $G4, $D:$D, ">="&H$1, $D:$D, "<="&H$2)
  • this formula can be copy and used in all the date range, as it will pick up the 2 dates above (row 1 and row 2) and filter on them.

You can do the same for getting the COUNT of gift using COUNTIFS: =COUNTIFS($A:$A, $G4, $D:$D, ">="&H$1, $D:$D, "<="&H$2)

this excel file can easily be reused by new export from the gift query that replaces the data in the gift table worksheet (do not use right click and delete, just “select” all cells and use Del keyboard key), right click-delete will mess up formula.

once you got the summarized table of constituent, then building the rest of the grid is easy, and i'll leave that to your own playing around with COUNTIFS and SUMIFS

@Tyyne Parakhen
Sample excel with the formula included for you to play with

@Alex Wong
Greetings,

Could you please confirm if by ‘Query module export,’ you are referring to the Database View Query Module, where data is exported from that module? I just want to ensure this is not related to the Gift List Query feature in the RE Web View. In the Web View, all the output variables—except for address information—are available for export. Specifically, filtering by date, gift type, and amount is fully functional within gift lists for Excel exports, which allows the inclusion of the RFM array formulas.

It would be highly beneficial if address information could be added to the gift lists in Web View in the future.

Thank you!

Thanks @Alex Wong ! I believe if you bring in address info into the output of a gift query and then export, you will certainly get duplicate gifts, for all the address types for example, is that correct? And what if you only want to output certain address types, seems an export using field criteria would work best instead of a Query, correct? Thanks!

Thanks @Alex Wong! Thanks for this information. I agree, all of the BB RE Query and Export training that I had taken, it is noted specifically Not to export from Query. If you only want those gifts whose IDs have a non-blank Preferred Address value, then you will need to add that to the criteria which as you noted will still generate dups for each of the gift date rows. Thanks again for your responses.

@Dariel Dixon:
@Tyyne Parakhen The fact that you're using Crystal Reports for this is kinda crazy.

@Dariel Dixon I can beat that - before I started using RE I used to work for a large property letting agency (a household name in the UK) who were using Crystal Reports solely to mailmerge letters that they then printed and sent to customers. That was all they used it for. All their KPI reporting was done by someone manually typing numbers into Excel from a PDF.

I lasted 9 months there which was about 8.5 months too long ?

@Arthur Gerstein
this depends.

RE NXT does not have good toolset for doing mailing list, which is why many database manager still relies heavily on database view's capabilities to do mailing list.

Then, there are those who are more “custom”-oriented, which will make use of SKY API and SKY Add-in to work this magic (I fall in this boat).

Is there a mid-ground of org that willing to pay for a marketplace 3rd party solution for doing mailing: no need to have expertise in database view capabilities (Query, Query List, Export, Mail, etc); don't want to learn SKY API, but still have a much easier route to make custom letter for mailing purpose? I think likely not. My org does a handful (4-8) mailing a year, some do a lot less, I don't know if that's worthwhile for them.

@Tyyne Parakhen
Great question and great conversation.

** Shameless plug…
SimpliPhi has just released a new product called AmpliPhi that does just this!

AmpliPhi allows teams to collaborate (both technical and less technical) on a campaign segmentation through a very easy to use web interface. The basic version leverages Raiser's Edge Queries and allows stacking of audiences and inclusion and exclusion queries.

With AmpliPhi, the person with the strategy and vision can collaborate with the technical person doing the segmentation, and work in a simple interface to ensure uniform understanding across all people involved.

Segmentation data remains dynamic until you are ready to lock it down and apply assigned appeals. Everything is managed automatically.

PM me for more information or visit our website to signup to the waitlist.