I do a lot of analytical work with membership based organizations. Each organization is a little different in their software provider and their membership setup. There are a few common themes that are pretty consistent across a majority of them that typically are dimensions that we need to look at.
Membership Type: this could be done by age, life status, payment arrangement (family, couple, senior, adult, month to month, annual, etc...). Typically there might be 4-6 different types of memberships that all roll into a parent category. Think about different types of family or couple memberships that are all named something slightly different but they all could roll into a parent category of Family or Couple (Family 1, Family 2...).
Financial Assistance: is the member receiving a discount (it would be great to know how much, but we are just getting started here.. baby steps). Typically operators want to know are people that pay less acting differently than people that pay full price. Brilliant question.
Retention Eligibility: does this membership count or not? Was it a guest pass or a week pass or a corp to corp trade.... for all intensive purposes is the membership so transient that you don't count them or maybe you don't get any revenue for them so you don't want to include them; whatever the reason.
Almost always, these things are not easily identifiable. At least not all three of them are well behaved. Typically what someone did was create a bunch of membership types and from those membership types the other two items can be deduced. You end up with things like 'Family 1 FA' or 'Couple CorpCorp' or 'Couple 1 Week'. For the first example, Family 1 FA, you have a Family parent membership type, that is retention eligible and on financial aid. Great, all the information you need to know it is just compounded into one single value and we need three values if we want to do anything meaningful.
Typically membership data is received in .csv or .xlsx format which is pretty flexible. Your first instinct might be to just update the records in Excel. Add formulas or filters and manually start changing things around. You could do that, but I don't recommend it.
We are not updating these records by hand. That is just a terrible idea on many levels. One there are probably way too many and two you should never change your source data. Never change your source data. Did I mention never changing your source data? Thanks! We could have our intern copy the data over and then update them by hand. No, this is not a good use of any humans time. There is a better way.
I am going to make some assumptions before I tell you how I typically do this. One is that you have access to a database of some sort, Access, SQL Server, something... Two that you can write some basic T-SQL. Three that you can use Excel. I know three is true for everyone, 1 and 2 might not be and there are ways around that that we can discuss in a later post. Assuming you meet all three qualifications let's get going.
Load the file that you were provided into your database. I have a Sandbox database that I use for tasks such as this. If you are using SQL Server, SSMS (SQL Server Management Studio) has a very nice import wizard for you. Once the data is loaded and you have checked that the import was successful run a simple SQL query. Your query will probably look something like:
select distinct(membership_type) from sandbox.dbo.member_file
This will return your entire universe of possible membership types. Remember this is the compound field that we want to break apart. In the dataset that I am currently working on there are 103 different types. Sweet, we see what we are working with. Now let's work on identifying the parent membership categories. All of the various Family memberships (Family 1, Family 2, Single Parent Family...) get mapped into a Family category. Take a first pass at this using a case-when statement and evaluating your membership_type column. Your SQL could look something like:
select distinct membership_type, case when membership_type like '%fam%' then 'Family' when membership_type like '%adult%' then 'Adult' when membership_type like '%couple%' then 'Couple' else membership_type end as membership_type_parent from sandbox.dbo.member_file
We are still pulling back a distinct list of membership types and now we are doing a wild card search for syntax that we know meets our criteria and we are creating a column with the membership type parent label. This is just a first pass but it will probably catch about 80% of what you need. Now repeat this same exercise but look for indicators for Financial Aid and Retention Eligibility, the other two values we want to analyze.
Once you are done with this exercise you should have 4 columns in the result set of your query. Export that into Excel or csv and it should look something like the below.
Now remember membership_type came natively within our customer data but then we created three new columns that we can use as independent dimensions. What I recommend is to go through each row and ensure that the evaluation of the membership_type resulted as it should. For example, your Financial Aid classification might be off if you only look for '%FA%' and there are records with 'Fin Asst'. Just update these outliers by hand, there shouldn't be many. Once you have completed this you have two things. One is a mapping table for you to use throughout your analysis and two is a document for your client to sign off on.
An email to her could look like :
Hey Ms. Customer,
I just wanted to let you know that I took the initiative to map all of these membership types into the values that you asked us to analyze. Can you please review this and let me know if there is anything I missed?
Trust me, this will go along way in lieu of asking her to map the whole thing herself. You will save her time and it is a good practice for you to get familiar with the data you are using. You are adding value already and we just got started!
Once you have sign off from the client load this data, in this exact structure, into the sandbox database and call it something like membership_type_mapping. Now you can join your raw data to this mapping table and evaluate on any of three additional columns we created. Things like retention by membership type parent category or financial aid are now possible without having to do a bunch of data transformation in your code. You can always get down to the granular membership types but now you can also provide a higher level of analysis.
This is something I do quite often and just wanted to pass it along in case you need to do something similar. Mapping tables... create them, use them, be happy.