Membership trends report

[Tony Horrocks]

I'm thinking of doing a membership trends report.

Each year people leave, join or stay in membership. Organisations need to get this data for each year, or even each quarter.

From this data we can graph membership retention rates, membership growth rates, membership tenure, membership loss rate and, combined with contribution data, membership lifetime value.

Furthermore, organisations segment their memberships so that they can provide best value to each segment thus remaining successful.

They segment members in all sorts of ways and CiviCRM is a great tool to enable this.

So an organisation might segment their membership on age and store each age segment as a smart group.

This means we could do membership trends reports as a whole or by segment.

So an organisation might be happy if they see their membership growth is 95% but rather unhappy when the see the 55-65 yr old segment growth is only 15%. And so on.

Any one got any thoughts about this?

[xavier]

Hi,

I think it would be awesome. Check out the code behind the dashboard, as it's already possible to group per period (fixed this month/last month... not as flexible as you want). This being said, likely you'll need the aggregation done at the sql level for perf reason

Code: [Select]

SELECT   DATE(date) AS Date,   HOUR(date) As Hour,   COUNT(*) AS Total FROM   civicrm_membership GROUP BY   DATE(date),   HOUR(date) ORDER BY   DATE(date) DESC,   HOUR(date) DESC LIMIT $samplesize;
IMO, would be great to have an api gettrend (filter=array(date min max),interval=day/week/month/year), ...)
We have some work done on the filter, Eileen, what would be the proper syntax?

Anyway, assuming you got something working, you can then use the ajax api interface and fetch that to feed it to d3j or another datavis solution. Will try to find this we a nice library that makes nice graphs in js if you don't have a favourite one.

X+

[Nicola Bell]

Hi there,

This is exactly what I am after - I am find it hard to calculate retention rates either over the year or quarter - could anyone give me any advice?



Thanks!

[Eileen]

You might also like to check out the PTP reports - see

http://wiki.civicrm.org/confluence/display/CRM/CiviEngage+Enhancements+for+fund-raising