Start a new topic

## Add a formula based on a values in other fields

We were looking at trying this out for something slightly different then just scoring constituents for engagement, propensity to give etc. We have an athletics membership program that works off of a points system. They receive 'x' number of points for every \$100 they give to athletics and 'x' more points for being (or their spouse being) an alum or letterwinner.

The issue we ran into is that we need to be able to calculate a point value based on the gift amount and from my understanding, ScoreOmatic basically just does counting of the number of times something occurs and assigns points as setup.

Any thoughts on being able to do some basic formulas based on field values to set the points? Maybe this would have to be some sort of a sql query that could do a calculation?

Hope this makes some sense.

Jeff -

Could it start of as something simple, add a formula option to the Multiplier drop down and then something like a popup box or other columns that would appear that would "build" a formula with you. In this pop up or extra columns, you'd select the Field from a drop down of supported fields, then the operand, then a text box to enter the value.

So I would do the following:
In the Profile Editor:
Select the Query
Enter 3 in the Points +/- column
Choose Formula as the Multiplier
Then in the pop up, choose Gift Amount for the field, Divided by or '/' for the operand and type 100.

Would then have the building blocks for the code in the background to put together the formula. Then it could take the formula and multiply it by the Points +/-.

Would also have to keep the formulas pretty basic and build from there. Eventually building up to something setup maybe more like the virtual fields are in importomatic?
Our scoring system builds on itself so those that make the bigger gifts are accumulating points all the way along, so in the end...they are scoring much higher. This comes into play with our mid-tier scoring model for instance.
\$25+ = 1pt
\$50+ = 1pt
\$100+= 1pt
\$250+ = 1pt
\$500+ = 1pt
\$1000+ = 1pt

So....a \$50 donor gets a total giving score of 2 pts. A \$1000 donor racks up 6 pts. For a rewards program, this seems to work well for us. If the program is solely based on \$100 gifts and is encouraging repeat gifts at that level to a specific fund, you can setup the queries to do that also. That is the beauty of the flexibility of ScoreOmatic!
Chris, we've been discussing exactly that sort of functionality for ScoreOmatic, but the only fully extensible solution we've come with so far involves SQL knowledge. I'm open to other ideas that don't involve programming skills, but not sure how to give that type of flexibility without it. Any thoughts on that?

Jennifer, does that mean someone that gives a \$1,000,000 gift will get the same score as someone that gives \$100?
We have a scoring system similar to this. We setup a GIFT query pulling in Gifts to the Athletics campaign or funds during a specific data range that are greater than or equal to \$100. Then every time a donor makes a gift at this level, the gift pops into the query. Then in the scoring profile, use the multiplier to give 1 point for every instance they appear in the query for that variable. We also have queries at lower gift values so that they get 1 point for a \$25 gift, 2 points for \$50 gift, 3 points for a \$100 gift, etc. Those are all just RE gift queries pulled in as separate lines into the scoring profile.