Having an issue trying to do RFM analysis with quartiles.

E

evildesign

Guest
#1
Hello,
I am trying to make create a quick RFM analysis with python and mysql on 47,312 customer accounts. I have 3 fields labeled TTLOFINVS, NUMOFINVS, DATE_FREQ. I have a python script that executes mysql query to order TTLOFINVS in descending order grabbing 11,828 rows at a time and populates column M with 4,3,2,1 depending on each grab. The same goes for NUMOFINVS as well and stored in F Column, but DATE_FREQ is pulled in ascending order and populate R Column. When use mysql GROUP BY LIKE "SELECT R,F,M FROM rfm_table GROUP BY r,f,m" I should get every combination and it should come up with with 64 rows, but it seems to only return 56, meaning I am missing combinations and I am not sure why.

When I count how many quartiles there are for each, I get

R
1|11,828
2|11,828
3|11,828
4|11,828

F
1|11,828
2|11,828
3|11,828
4|11,828

M
1|11,828
2|11,828
3|11,828
4|11,828

I am new to this and kinda trying to figure it out, so if anyone has any input on what I should look at, i would be greatful.

Thanks,
Justin