Re-thinking the CiviCampaign data model and user interface

Published
2011-09-15 09:03
Written by
CiviEngage is a Drupal module that (in the next release of CiviCRM) automatically configures CiviCampaign to make it easier to get up and running by setting many of the configuration tasks with sensible defaults. Over the last few weeks I've been getting intimate with both CiviCampaign and CiviEngage in an effort to either merge CiviEnage's features in CiviCampaign or think about how to package them as an extension. In the process, I made some discoveries concerning surveys and petitions that made me pause and want to reach out for direction before proceeding. In this blog post I'm only mentioned surveys, but the same holds for petitions which use essentially the same data model. When creating a survey in CiviCampaign (without CiviEngage), your first task is to either create a new custom data group and add custom data fields for each question you want to ask in your survey. Or, you pick an existing custom data group and add custom data fields for each question. These custom data fields are extensions of the Activity entity. This approach very elegantly allows each response to the survey be recorded as an activity, with each custom field showing the question and answer for the particular contact. Then, you create a profile that includes the particular questions you want for your survey. This use of profile is also very convenient - for petitions it means you can easily expose the petition on a web site and get responses directly into your database. Lastly, you create a survey. One of your options is to choose, from a drop down list, the profile containing the questions you want to use and you are all set. At the moment, I believe there are two places for improvement.
  1. User interface: It would be ideal if the survey creation process was like the event creation process, with a multi-step form that prompted you to enter the questions you wanted to ask as part of the survey creation process, rather than requiring you to first know that you need to create custom data groups and profiles.
  2. Data model: more vexing, is what appears to be scalability problem with the data model which I'll go into more detail below...
The current data model works fine for an organization that wants to conduct one or two surveys or petitions, or perhaps an organization that wants to have 50 to 100 surveys or petitions. But what if a group wants to have a new 20 question survey every week for 5 years? With the current model, they can either create a new custom data group for each survey that holds each new question, or they can have a single custom data group that stores all questions. The resulting database structure is either a new database table every week (after five years: 260 new tables in the database) or a single table with 5200 fields (and an immense drop down list when choosing your field when creating your profile). Either way, we're adding data horizontally via new fields rather than vertically via new rows. Both approaches will complicate any activities query that has to string together all the activities custom data tables. This problem is not critical now and, if nobody creates a lot of surveys or petitions, will never be critical. However, it seems like a design flaw that has the potential to bite us down the road. CiviEngage solves this problem by by adding a custom data group called Survey Questions that extends the Survey component and then adding four fields called Q1, Q2, Q3, and Q4. CiviEngage adds another Custom data group that extends Activities (called Survey Responses) and adds four fields called Q1 Response, Q2 Response, Q3 Response, and Q4 Response. A profile is also automatically created called Survey Response that contains the Survey Response fields. When you create a new survey, you simply select the Survey Response profile. Since the Survey questions data group extends the Survey component, you get four new fields at the bottom of the Survey form where you can add your questions for the given survey. Now, no need for a new custom data group or custom fields every time you add a new survey - and we have an improved user interface. Unfortunately, this data model and user interface has different, but no less problematic limitations. The user interface forces every survey to be a four question survey. And, each custom data field for answers uses a preset option list (yes, not, maybe, undecided) which effectively limits you to yes/no questions. This is not the solution to the user interface problem. As for the data model - it cleanly solves the scalability problem since we don't have to add new tables and new fields every time we have new questions. But it introduces a database normalization problem. There's no normalized relationship between the fields holding the question and the fields holding the answers. We can guess that the third field in the question table corresponds to the third field in the answer table, but there's no way to write a reliable SQL statement that embeds that logic. Unfortunately, this is no solution to the data model problem either :(. And worse, the CiviEngage data model breaks the logic of the CiviCampaign user interface for entering survey responses and the CiviCampaign reports. Instead of displaying the survey questions, it displays the un-helpful label: Q1, Q2, etc. At the Progressive Technology Project retreat we devoted a good chunk of time to addressing this problem and came up with a proposed strategy. Given the analysis so far, and assuming I'm not missing anything (a big assumption :), we propose:
  • Short term: change CiviEngage configuration so it uses the CiviCampaign data model (don't automatically create the questions and answers custom data groups or the survey profile). Update the CiviCampaign/CiviEnage documentation to reflect the extra steps to setup questions and profiles, etc. This step will harmonize CiviCampaign and CiviEngage data models and fix the immediate user interface problem with CiviEngage.
  • Medium term: (4.1? 4.2?) change the survey data model. It seems like the addition of two new tables: survey_questions and survey_answers would be the most elegant solution from a data model perspective, but would require immense changes in coding.
  • Medium term: (4.1? 4.2?) change the survey creation user interface to make it a multi-step form
The Progressive Technology Project is committed to making these changes happen - but want to ensure we're moving in the right direction before we start. Please help with feedback!
Filed under

Comments

Hi Jamie,

 

I'm definitely with you on the Short Term plan - ditch those fields from CiviEngage - I found them more confusing than anything.

 

I'm still pondering the thoughts you have on survey. It makes sense in the limited context of a survey being .... a survey. However, I had been looking at using surveys for phone banking. Each month a list of people to call is generated & a survey is created limited to this group of people. Then canvassers can select a few people to call, call them & record the responses. In this context having the phone bank showing up as an activity on their activity dashboard is key. The questions asked are fairly limited recording the responses as custom fields against the activity & contact makes a lot of sense.

Hi Eileen - thanks for the feedback. I've already prepared a commit to remove those CiviEngage fields - just waiting for a review by Alice and Josue.

I agree, I think we definitely want to keep the response to a survey/phone bank/ etc. as an activity. By response, I mean: that they filled it out/answered the questions/ etc.

But - the questions and answers themselves should be kept in separate tables.

I think we can even have the questions/answers show up on the dashboard or even when viewing the activity even though they are stored in separate tables - all done via user interface code.

jamie

OK - I just wouldn't want to lose the ability to connect a profile with activity custom fields & contact custom fields to a survey - there is a difference between one-off fields (survey questions) & fields of ongoing relevance  I guess.

 

Yes - I think that's the real wrench (spanner?) in my proposal for the two tables.

Petitions in particular really need to have the questions accessible via profiles so they can be embedded in other sites. The two new tables approach would introduce a data model very different and maybe incompatible from the model used to drive profiles.

I'm not sure what the answer is but agree we need to preserve that functionality.

Anonymous (not verified)
2011-09-16 - 06:54

Jamie

It looks as if the existing model is broken for a limited set of users (heavy users of this functionality), but the CiviEngage mechanism (4 questions, must be Y/N) is broken for everybody (as everybody would have lower functionality).

 

It therefore looks as if what you are proposing (if I understand it) in having two new tables that allow the current flexibility (as many questions as you like, any response type) but dealing with the scalability issue. In addition, it makes the changes in the right place (core Civi) so that Joomla/Wordpress users can benefit too.

 

Good luck with this.

Thanks for the feedback.

Yes - sounds like their is resounding consensus about fixing the CiviEngage model (and I've just committed those changes). They broken model was never released and I'm getting the fix in before 3.4.6 so we shouldn't have to field support questions about or deal with data conversion thankfully.

1. Our current model is not designed for orgs that do a lot of surveys that change on a weekly basis. I think that is an edge case for CiviCRM and they probably should look for tools specifically built to collect and analyse large sets of survey data :)

2. Not sure how introducing the two new tables: survey_questions and survey_answers addresses the issues. Note that thin tables (lots of surveys using just one table) dont scale at all (custom fields v1.x and flexinode are legacy for this reason)

3. Having generic Q1/Q2/Q3/Q4 and a fixed set of options is also very limiting and is best avoided. Considering how wide and varied the user base of CiviCRM is, this will not fly

4. I do think there are lots of improvements we can make with regard to usablity across multiple fronts. Making it easier to create surveys, better handling of large sets of custom groups (via ajax), better exports of data for offline analysis etc

 

lobo

 

I don't think CiviCRM core should be changed to optimize for weekly surveys.

I do think that there is a significant need for many advocacy groups and non-profit groups that need to fundraise to have a phonebank of volunteers who call contacts, and fill in their responses to questions.

I am unclear whether CiviEngage is a good basis for development of that functionality in comparison to other efforts along these lines over the years.

Hi,

 

I think the survey creation suffers the same issue as the event (or any that got lots of custom data): it's upside down.

 

The "expected" process is that I create the survey, add the fields if they exist, and if they don't create them.

 

The existing process is that I need first to create the fields, then group them into the profile and then create the survey and use the profiles.

 

A low hanging improvement would be to be able to create or edit a profile from the survey, and add or create fields from a profile. Overlay or in place editing would be ideal, but something as simple as adding links would already be a big help.

 

Both for events and surveys, we tend to create one "single purpose" profile, but it still have to be created and managed separately. It would be super useful IMO to be able to create implicit/hidden profiles, kind of what is done with the hidden groups for mass actions.

 

Ie. I'm creating my event or survey, got an option to use an existing profile, or add fields directly (that would create a profile too, but without it being explicit).

 

As for the data structure, again taking the event analogy, we have a lot of custom fields, eg "are you attending the dinner on the 12th, want us to book the hotel on the 27th...". However, we almost always can make the data generic enough on the custom set (eg "dinner first night, hotel night before") and create new profiles to make the label more explicit (eg specify the date...)

 

I'm quite sure in practice could be the same with surveys. Do you have examples of questions that are really different from one survey to the other?

 

I'm pretty convinced that each organisations are going to have different answers, but that within one, they can fit into a limited number of "generic enough" fields if you can change the labels (via the profiles).

 

Actually, I'd go the other way around and suggest to remove the response, that could either be the status of the activity, or moved to the custom set as one of the field.

 

Something I found missing: being able to have the option to change the name of the option lists (hower that's called) in a profile. We can already change the label of a field, but can't change the name of one of the values in the field (when that's an alphanumeric select or radio for instance). Could have been proven convenient a couple of time, and in case of surveys even more so probably.

 

X+