# How to calculate second gift rate or second sale rate

Hello community,
I am brand new to KNIME so just baby steps for me. I work for a charity and I wanted to be able to quickly calculate the second gift rate % by calendar year. For example a donor gives their first gift in Jan22 and they after stewardship he/she gives the second gift in March22. We have over 30k donors spanning from early 2000. I have loaded all the giving history so now I’d love to learn to calculate in any given year what is the % of second gift rate and also what is the sweet spot when a donor gives the second gift, after 3, 6, 9 or 12 months. Has anyone done this using KNIME if so please help

Hello @utajhya and welcome to the KNIME forum

Aiming to provide some advise to your challenge, would be better if you can provide a sample of your data problem or a dummy analogue. Then, people in forum will be predisposed to collaborate by having some data to work with.

And trying to offer you some brainstorming about your use case; I think a first step for your analysis would be to align the data (monthly granularity) to the first gift by donor. This alignment -months from first gift- (M0, M1…) will give you the right reference axis to start your plotting, fit, regressions…

This workflow elaborated for a past forum discussion, and despite the time granularity is based in years; it can give you some insights for the data preparation.

BR

3 Likes

Thanks so much @ gonhaddock, I appreciate coming back to me. Here is how I have the data; one sheet has all the first gift data for each donor and the second sheet has all the subsequent gifts for the same donors.
ID FG Date
1 \$10 01/01/2022
2 \$5 05/12/2022

Second sheet has all the second and third gifts for the same donors
ID Gift Date
1 \$10 03/03/2022
3 \$5 01/03/2023
2 \$25 01/01/2023

The result I’m trying to achieve is that donor 1 gave the second gift in 3rd month after the 1st gift and the donor 2 gave the second gift on the 2nd month. This way I will then have an aggregate of say 50 donors gave the second gift within the first 3 months, 30 gave within the 6 months then I can work out what is the sweet spot for our donors to give second gift judging by the pattern. I have worked them out manually but it would be amazing to be able to use KNIME for it.

1 Like

Are you only interested in the time between first and second gifts or are you also interested in the time to subsequent gifts, i,e, 3rd, 4th…? If the latter is the time you’re interested in date 1 to date 3 or date 2 to date 3?

Here’s a very simplistic workflow they may help you. It won’t handle subsequent gifts beyond the second correctly, e.g. if you have a third gift for a given ID it will calculate the number of months from the initial gift, but won’t identify it as a third gift. Also it will count two gifts in the same month as a “0” difference. This is hardly heavy duty, but hopefully it will give you some ideas.

Gift Giving Time Difference.knwf (58.8 KB)

2 Likes

I am only interested in the 1st to second for this calculation as the second gift is critical in getting the donor across the line for possible long term support. Thanks so much!

Thanks so much @rfeigel, much appreciated. I will check it out later today and let you know how I go

Hi @utajhya
@rfeigel 's wk is a nice example about data preparation and EDA, making it easy with KNIME. There is some literature and available free datasets in kaggle, describing the type of analysis that want to achieve:

Searching for donor behavior within - kaggle search
Donors-Prediction - Dataset

Then, if any out of these datasets fits with your input; we can support to move further with the analysis.

BR

1 Like

Here’s an updated version of the workflow. It has a date filter for the 1st year table so you can look at calendar years individually (or any other time frame.) Its currently set to 2021. Since you said you’re only interested in 2nd gifts, I’ve filtered the second table so it only produces the first gift (if any) for each ID. To work properly, the dates must be in ascending order for each ID.

1 Like

Thank you very much, I should be able to test this evening so I will come back asap. Much appreciated @rfeigel

Hi @rfiegel, when I copied and pasted my data (ID, Amount and GiftDate in same format as your manual sample) in the table creator it failed to execute and the error message I got is; text could not be parsed, unparsed text found at index 10. Please advise. Thanks heaps.

Check that the data is confgured as shown on the screenshot below. The date must be configured as “date”, not “string”. You don’t need to copy/paste into the Table Creator nodes. You can read the data directly from Excel and set the data formats as required. If you can send me some anonymized data in Excel, I’ll take a look.

1 Like

Thanks. It is definitely configured as date.

I will email the anon data to you.
Appreciate your help!

Try this workflow. I revised to read from an Excel workbook which is what you’ll want to do. The only reason I used the Table Creator nodes was because I didn’t have a real dataset and its easier to prototype a small dataset that way. Rather than plugging your data into my workbook, you may want to point the Excel Reader nodes to your workbook. Reset my workflow and run it to make sure it works for you locally before you try to use your data. The screenshot below shows the various data approaches you can use. With a real (large) dataset we can try some statistical analysis/regression, etc.

Thanks so much @rfeigel , I will try the new workflow asap and report back.
Much appreciated!

Hi @rfeigel , I have got it working until the end now except for the output so thanks so much for your help again. I will continue to test and learn. I am so glad to know and appreciate there are more good humans on Earth always willing to lend a hand in need.

1 Like

You’re welcome. What’s your output problem? Maybe I can help.

I have got all working now, thanks @rfeigel. I need to check the second gift data to ensure that I have the right data then the end report will be more meaningful. I will reach out if I get stuck.

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.