[cbusse]

For one of my clients, I get several requests a year from the direct mail company to segment a Raiser's Edge donor renewal export into 21 mail code segments based on certain recency and gift amount criteria.

Currently I'm exporting a pipe-delimited file and processing it with a VB Script I wrote that looks at the Last Gift Date and Last Gift Amount columns, then prepends each row with the correct mail code to another text file that is then sent off to the mail house for merging.

Is there a way to accomplish this directly in Raiser's Edge? I'd really like to train the development department how to do this if possible.

An example request looks like:

Most Recent Contribution (Date and dollar amount)

I. Listed below are the mail codes segmented by recency and dollar amount. Please assign an individual code to each record.

Donors whose most recent contribution is within the last 0-12 months

MRC Mail Code
$5-24.99 R84RVA1
$25-49.99 R84RVA2
$50-99.99 R84RVA3
$100-249.99 R84RVA4
$250-499.99 R84RVA5
$500-999.99 R84RVA6
$1000+ R84RVA7

Donors whose most recent contribution is within the last 13-24 months

MRC Mail Code
$5-24.99 R84RVB1
$25-49.99 R84RVB2
$50-99.99 R84RVB3
$100-249.99 R84RVB4
$250-499.99 R84RVB5
$500-999.99 R84RVB6
$1000+ R84RVB7

Donors whose most recent contribution is within the last 25-36 months

MRC Mail Code
$5-24.99 R84RVC1
$25-49.99 R84RVC2
$50-99.99 R84RVC3
$100-249.99 R84RVC4
$250-499.99 R84RVC5
$500-999.99 R84RVC6
$1000+ R84RVC7

And the (ugly) script I'm using -- which only makes but so much sense without an example export, but hopefully it carries across the idea of what I'm doing -- is:

'---------------------------------------- OUTPUT FILE

   Const ForReading = 1, ForWriting = 2, ForAppending = 8
   Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

   Dim fso, f, ts

   Set fso = CreateObject("Scripting.FileSystemObject")

   fso.CreateTextFile "output.csv"

   Set f_export = fso.GetFile("output.csv")
   Set ts_export = f_export.OpenAsTextStream(ForWriting, TristateUseDefault)

'----------------------------- INPUT FILE
Set objFSOName = CreateObject("Scripting.FileSystemObject")
Set objNameFile = objFSOName.OpenTextFile("input.chr", ForReading)

x=0

'write the first line to make column headers
strNextLine = objNameFile.Readline
tmpMailCode = "Mail Code"
tmpWriteLine = tmpMailCode & "|" & strNextLine
tmpWriteLine2 = Replace(tmpWriteLine, "|", ",")
ts_export.write  tmpWriteLine2 & vbCRLF

Do Until objNameFile.AtEndOfStream
    strNextLine = objNameFile.Readline
    tmpArray = Split(strNextLine , "|") 

    tmpMailCode = "XXX"

    varRecordDate = Replace(tmpArray(13), chr(34),"")
    varRecordAmount  = Replace(tmpArray(14), chr(34),"")

'--- 25-36 Months
    tmpMyDate = "1/1/2005"
    If cDate(FormatDateTime(varRecordDate, vbShortDate)) >= cDate(FormatDateTime(tmpMyDate, vbShortDate)) Then
        tmpMailCode = "R84RVC7"
        If varRecordAmount < 1000 then 
            tmpMailCode = "R84RVC6" 
        End If
        If varRecordAmount < 500  then 
            tmpMailCode = "R84RVC5" 
        End If
        If varRecordAmount < 250  then 
            tmpMailCode = "R84RVC4" 
        End If
        If varRecordAmount < 100  then 
            tmpMailCode = "R84RVC3" 
        End If
        If varRecordAmount < 50   then 
            tmpMailCode = "R84RVC2" 
        End If
        If varRecordAmount < 25   then 
            tmpMailCode = "R84RVC1"
        End If
    End If

    '--- 13-24 Months
    tmpMyDate = "1/1/2006"
    If cDate(FormatDateTime(varRecordDate, vbShortDate)) >= cDate(FormatDateTime(tmpMyDate, vbShortDate)) Then
        tmpMailCode = "R84RVB7"
        If varRecordAmount < 1000 then 
            tmpMailCode = "R84RVB6" 
        End If
        If varRecordAmount < 500  then 
            tmpMailCode = "R84RVB5" 
        End If
        If varRecordAmount < 250  then 
            tmpMailCode = "R84RVB4" 
        End If
        If varRecordAmount < 100  then 
            tmpMailCode = "R84RVB3" 
        End If
        If varRecordAmount < 50   then 
            tmpMailCode = "R84RVB2" 
        End If
        If varRecordAmount < 25   then 
            tmpMailCode = "R84RVB1"
        End If
    End If

    '--- 0-12 Months
    tmpMyDate = "1/1/2007"
    If cDate(FormatDateTime(varRecordDate, vbShortDate)) >= cDate(FormatDateTime(tmpMyDate, vbShortDate)) Then
        tmpMailCode = "R84RVA7"
        If varRecordAmount < 1000 then 
            tmpMailCode = "R84RVA6" 
        End If
        If varRecordAmount < 500  then 
            tmpMailCode = "R84RVA5" 
        End If
        If varRecordAmount < 250  then 
            tmpMailCode = "R84RVA4" 
        End If
        If varRecordAmount < 100  then 
            tmpMailCode = "R84RVA3" 
        End If
        If varRecordAmount < 50   then 
            tmpMailCode = "R84RVA2" 
        End If
        If varRecordAmount < 25   then 
            tmpMailCode = "R84RVA1"
        End If
    End If

    tmpWriteLine = chr(34) & tmpMailCode & chr(34) & "|" & strNextLine
    tmpWriteLine2 = Replace(tmpWriteLine, chr(34) & "|" & chr(34), chr(34) & "," & chr(34))

    ts_export.write  tmpWriteLine2 & vbCRLF

    x = x + 1
Loop
objNameFile.Close
ts_export.Close

msgbox "Done"

Thanks!

[asnoah]

We do a monthly appeal mailing that is split into 20-25 segments based on giving amounts in a specific time frame. I create an appeal and a "package" for each segment. The package ids are our motivation codes printed on the mailing. I have a unique RE query for each segment. I use the Mail/Quick Letters to export the data to send to our mail house. Quick letters allows you to segment the mailing and output the motiviation codes. It will also look at any solicit codes.

You can choose to have the system mark each record with the appeal information at the time of running the data but I've seen problems with that in the past so I'll generally have it create an output query (another option in the Mail/Quick letters) and then once my data is clean (NCOA, etc.), I do an import to add the assigned appeal to the appropritae records.

I don't know if you're familiar with this feature in RE. Hopefully I've explained it well but if not, post another question.