Use Superset/MySQL to Chart Changes in s2member ccaps

#1

Hi everyone,

I’m new to s2member and really appreciate it. Using a MySQL query, I would like to pull the data from the s2member_access_cap_times meta_value and chart the times that a user’s role changes over time. For example, I want to create a chart in Superset that shows how many users have changed their level from 3 to 0 from year to year. I am not an expert in MySQL queries either so any guidance would be greatly appreciated. Is what I want to do even possible? If so, what is the best way to go about this?

Thanks for any help you can give.

Alex

1 Like
#2

Hi Alex.

Welcome to the community! It’s good to have you. :slight_smile:

Yes, what you said is possible. I’m actually planning to create a viewer for the access cap times data. So yes, it can be done. And with the data then you can use the visualization library, whether that is Superset, D3.js, or some other similar.

You may find this helpful:
2member/src/includes/classes/access-cap-times.inc.php
c_ws_plugin__s2member_access_cap_times::get_access_cap_times($user_id)

#3

Thank you Cristian for your welcome and response!

I apologize for taking so long to respond. Because I’m not a programmer or a database expert, I asked for help from someone very familiar with Superset. I told them what I was trying to do and shared the example data below with them:

a:20:{s:15:“1562695223.0001”;s:6:“level0”;s:15:“1562695223.0002”;s:6:“level1”;s:15:“1562695223.0003”;s:6:“level2”;s:15:“1562695223.0004”;s:6:“level3”;s:15:“1577906312.0001”;s:11:“ccap_prepay”;s:15:“1596575898.0001”;s:12:"-ccap_prepay";s:15:“1596575898.0002”;s:13:“ccap_graduate”;s:15:“1596575898.0003”;s:11:“ccap_prepay”;s:15:“1596575898.0004”;s:7:"-level3";s:15:“1597196952.0001”;s:14:"-ccap_graduate";s:15:“1597196952.0002”;s:12:"-ccap_prepay";s:15:“1597196952.0003”;s:13:“ccap_graduate”;s:15:“1597196952.0004”;s:11:“ccap_prepay”;s:15:“1598382433.0001”;s:14:"-ccap_graduate";s:15:“1598382433.0002”;s:12:"-ccap_prepay";s:15:“1598382433.0003”;s:11:“ccap_prepay”;s:15:“1598382433.0004”;s:6:“level3”;s:15:“1605290551.0001”;s:12:"-ccap_prepay";s:15:“1605290551.0002”;s:11:“ccap_prepay”;s:15:“1605290551.0003”;s:13:“ccap_graduate”;}

They were particularly curious about the meaning of the data at the beginning – “a:20:{s:15:”

Would you tell me what those things mean please? I shared this page with them – https://s2member.com/kb-article/advanced-importexport-tools/ – to see if that would help but it did not.

Any help you can give would be greatly appreciated.

Thanks again.

Alex