2

I need to create a report with a list of all donors, the total that they've contributed over the last 12 months, when their last membership expired, and a couple contact items like email address. (If they meet a certain minimum contribution amount, I need to give them a 12-month membership.) I'm using the Data Processor extension on a WordPress site (everything is up to date).

I have the overall data processor created, with data sources, output, fields, and a filter for 12 months worth of contributions. But right now it is giving me one row for each individual contribution and membership expiration (apparently a Cartesion product query). This seems to suggest that I need to aggregate the contribution Total Amount and Membership Expiration Date.

How can I do that? Adding an aggregate field is fairly straightforward: just select the field you want to add. There are no other options. Yet it doesn't seem to do any aggregation.

My Google-fu has let me down finding any documentation about aggregation fields in data processor beyond a mention of the feature.

How can I get an aggregation to work? Or am I misunderstanding what aggregation does? How can I accomplish what I need?

I suspect that an aggregation may not work with the membership expiration date. If not, how can I see in the report whether they are a current member?

Thanks!

Improve this question
3
  • Hi, @jaapjansma, the creator of the extension, should be able to help with an answer. Also see the repo Issues and docs at lab.civicrm.org/extensions/dataprocessor . Check out the ReportPlus extension for doing totals /aggregation, at lab.civicrm.org/extensions/reportplus/tree/2.x .
    – Joe McLaughlin
    Dec 16, 2019 at 18:36
  • Thanks, Joe! I still don't see much about aggregation at the docs, but you helped me find that a comment that a data processor aggregation is like a group by clause in SQL server. So it's a very useful feature, but wasn't what I needed. So that's really good to know.
    – RiverRunner
    Dec 17, 2019 at 20:38
  • 1
    cool and glad Alan pointed you to the correct answer and duh on me :) . The Report Plus extension does totals/aggregation as well but Summary Fields works, yay.
    – Joe McLaughlin
    Dec 17, 2019 at 23:32

3 Answers 3

Reset to default
4

Use the summary fields extension here: 

https://civicrm.org/extensions/summary-fields

It will create some custom fields and calculate the totals, minimums etc. values that you are trying to generate on the fly. That way you don't have to do all those joins. Aggregation is hard.

Improve this answer
1
  • 1
    Using summary fields turned out to be what I needed to do. With them, along with the include and exclude groups mentioned by Rebecca, got me to where I need to be. Thanks!
    – RiverRunner
    Dec 17, 2019 at 20:39
2

This is not necessarily an answer on the aggregate field but a potential workaround may be running the 'Top Donors' report with the criteria set to your requirements and then adding those contacts to a group. You should then be able to use that to find contacts that are in the top donors group but have no current membership (or whatever other membership criteria you select) via the 'Find Members' search function. Maybe this helps and will get you the data you need.

Improve this answer
1
  • Thanks, Rebecca! See my response to Alan. With summary fields, I was able to do more or less what you described. Not with Find Members, but by including and excluding groups to create a smart group with what I needed. Not quite what I was setting out to do, but turns out to be a great solution. Thanks!
    – RiverRunner
    Dec 17, 2019 at 20:41
2

So, to elaborate a bit on my comments with the solution I arrived at. I used summary fields, relying on the built-in Total Contributions in the Last 12 Months field.

Then I created a smart group called Membership Candidates that contains everyone who donated the threshold amount in the last 12 months.

Then I used a group I already had called Current Members.

Finally I created a smart group that includes Membership Candidates and excludes Current Members (and another one, Organizations), and that gives me the list I needed. It doesn't display the 12-month donation total or the last membership expiration, but that turns out not to matter.

Thanks, everyone!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.