Importing relationships into CiviCRM

As part of my work at the Center for Research Libraries, I am investigating different Constituent Resource Management (CRM) systems.  One of the options is CiviCRM, a popular FLOSS CRM.  As CRL is, in large part, a membership organization, I wanted to see if it was possible to represent the basic information that we keep about our member organizations in the CRM.  I found that data entry through the web interface was pretty slow, so I wanted to experiment with CiviCRM’s contact import capabilities.

CiviCRM lets you define multiple, arbitrary relationships between contacts. This is how we can connect individual contacts with their institution (for instance the Librarian Councilor or Purchase Proposal Representative) or organizational sub-units (a particular library branch) with the parent organization.

Here is an example of part of our paper member information form that shows that sort of information that we collect about a member institution:

Screenshot of CRL's member information form

CiviCRM also lets you import contact information and relationship information through comma separated value (CSV) files. However, there are a number of things that need to be configured in order to get this working properly.

Need to have contact types configured correctly for the relationship

This is configured at Administer > Options List > Relationship Types

When you create a new relationship, it sets Contact Type A/Contact Type A to any contact type. This works fine if you are defining relationships within CiviCRM’s web interface, but doesn’t work well when importing contacts. This is because CiviCRM will not be able to correctly match the related contact if the contact type is not explicitly set.

In the case of our “Librarian Councillor of” relationship, Contact A is an Individual (the member organization librarian) and Contact B is an Organization (the member organization):

Configuring a relationship in CiviCRM

Need to update strict matching rules for individuals

CiviCRM has configurable matching criteria for identifying and merging existing duplicate contacts and for updated existing contacts based on import data. This feature is documented in the CiviCRM documentation page Find and Merge Duplicate Contacts.

The matching criteria can be configured at Administer > Manage > Find and Merge Duplicate Contacts. By default CiviCRM defines Strict and Fuzzy rules for each contact type. CiviCRM uses the strict rule when importing contact data. However, the default rules might not fit the data that you have. For instance, by default, the strict rule for matching individuals puts all the weight on e-mail address. For many of the contacts, however, there is not an e-mail address. So, I had to update the Strict rule for Individual contacts to also match on First Name, Last Name, and Phone Number. Note that I set the weight so that all three values must match for CiviCRM to consider the contact a duplicate:

Configuring the duplicate matching rules in CiviCRM

If you don’t configure these rules correctly, you will get duplicate entries when you try to import your contact relationships.

Need to only have one relationship per CSV import file

This is one of the most confusing aspects of the relationship import process. Initially, I tried to put all the relationships in the same CSV file that I used to import the individual contact:

First Name,Middle Name,Last Name,Job Title,Individual Prefix,Individual Suffix,Street Address,Supplemental Address 1,Supplemental Address 2,City,Postal Code Suffix,Postal Code,Address Name,County,State,Country,Phone,Email,Note(s),Employee Of, Librarian Councillor of
Jane,,Doe,Head Librarian,,,123 Fake St.,,,Springfield,,12345,,,Illinois,,123-456-7890,jane.doe@sample.edu,,Sample University, Sample University

That is, in the last 2 columns, I specify that the individual contact (Jane Doe) is an Employee of and the Librarian Councillor of Sample University.

This doesn’t work! I can only specify a single Individual -> Organization relationship in each CSV file. So, I need to break out the Librarian Councillor of relationship into a separate CSV file:

individual_import.csv:

First Name,Middle Name,Last Name,Job Title,Individual Prefix,Individual Suffix,Street Address,Supplemental Address 1,Supplemental Address 2,City,Postal Code Suffix,Postal Code,Address Name,County,State,Country,Phone,Email,Note(s),Employee Of
Jane,,Doe,Head Librarian,,,123 Fake St.,,,Springfield,,12345,,,Illinois,,123-456-7890,jane.doe@sample.edu,,Sample University

librarian_councillor_import.csv:

First Name,Middle Name,Last Name,E-mail,Phone,Librarian Councillor for
Jane,,Doe,jane.doe@sample.edu,,Sample University

I will first import the contact CSV (individual_import.csv), then the relationship CSV (librarian_councillor_import.csv).

Need to include fields in CSV so that matching rules will work

Note that in the above example, I have to be sure to include enough information for our matching rules that I defined before to match Jane Doe to her existing database entry. So, I need to have either an e-mail address or First Name, Last Name, and Phone number.

Need to tell import process how to handle duplicate contacts

When importing the relationships, we will already have imported the individual contact information. So, we just want to update the existing individual contact record to reflect their relationship with their organization. So, we need to set the For Duplicate Contacts option of the import settings to Update.

Configuring CiviCRM import settings

Need to set up relationship import field mappings correctly

The field import mapping setting that I needed for the relationship import file (in this example librarian_councillor_import.csv) wasn’t immediately obvious to me. Here is a screenshot of the configuration that worked:

Configuring import field mappings in CiviCRM

Note that the Librarian Councillor for field in the CSV if mapped to the Library Councillor of relationship (that I defined at Administer > Options List > Relationship Types) and that the option of this mapping is set to Organization Name so that it will try to relate the imported contact to the existing organization contact record with the name specified in the CSV file.

Summary

So, it is possible to import both individual and organizational contacts into CiviCRM as well as the relationships between them. However, this could be tedious because each relationship type must be imported in a separate file. One possible solution would be to have a master spreadsheet that is used to input contact and relationship data. Then the spreadsheet programs filters/macros could be used to export appropriate CSV files for importing the contacts and relationships into CiviCRM. The import process is still somewhat complicated, so it seems best to do have systems staff assist with an initial mass import and then have future contacts input manually through the web interface.

nonlinear digital music narratives

As the start of my graduate program grows nearer.  I feel like I need to talk and think about what I want to do with journalism more concretely.  Last night in conversation, I mentioned that I was interested in exploring how the web and other new media could tell stories outside of the linear narrative structure of a news article or a video documentary.  How does the producer’s or audience’s bias get subverted when the audience can pick multiple paths through the narrative? , Unfortunately, I couldn’t think of any examples of this, but Josh referenced Hyperfiction as a way that this is done with creative writing and how it creates a different, intensly immersive experience for the reader.

Working on a mix tape lately, and getting my record player working again, I’ve been thinking about the linear path through which albums or mixes are constructed.  Sometimes this can be narrative like a concept album, for example Prince Paul’s Prince of Thieves, or it could be more subtle in a record like Springstein’s Nebraska.  While you could certainly play songs on an LP or CD in a different order, digital audio files make this even easier.  Unfortunately, it seems like much of the focus on the benefits of digital audio has been with regards to distribution instead of the possibilities for constructing sets of connected songs with multiple paths through them.  I often read something years later that makes me re-think a Defiance, Ohio song or the songs in relation to each other.  Also, the Allied Media Conference’s recent call for track proposals has made me think about grouping and connecting information.  I also think of the recommended EQ diagram in the In Utero liner notes.  I think it would be pretty cool to release an “album” of songs digitally, with separate recommended orders of the songs and liner notes that describe the different paths through the songs.

Photo by Great Beyond via Flickr.

WordPress Mu spammed, Chickenfoot to the rescue

I found out last night that I had forgotten to turn off new blog registration on my WordPress Mu instance and that over 500 spammers had created new blogs on my site.  The admin interface allows you to bulk delete blogs but requires that you check the checkbox next to each blog to select it for deletion.  This was getting pretty tedious.  Though I’m sure I could have delved into the internals of WordPress and figured out how to delete these blogs at the database level, this was a little scary and I didn’t really want to spend the time to do the research necessary to feel confident about this method.

So, I installed the Chickenfoot Firefox extension and wrote this simple script that would check every checkbox on the administration page.  This was made easy because of the fact that the only checkboxes on this particular page were ones associated with blogs that I wanted to delete.  This reduced the number of clicks to delete blogs significantly and it only took me a few minutes of manual clicking to delete the hundreds of spam blogs.

for (chk = find(new XPath("//input[@type='checkbox']")); chk.hasMatch; chk = chk.next) {
  check(chk);
}

Photo by loveï½¥janine via Flickr.

Open affiliate program framework

I don’t really like the idea of sites that I contribute content to having a ton of banner ads.  However, I’ll often mention a book or a record, something that, even if I’m not pushing it for a sale, I would feel pretty good about people buying.  It would be nice, if they buy it on my recommendation, if I could get a little kickback from the sale.  Certainly, many big retailers like Amazon (and independent ones like Powell’s and Insurance Revenue) have affiliate programs.  The problem occurs if I don’t want to send people to one of these larger retailers or if the retailer where I want to send them doesn’t have an affiliate program.  It seems like it would be awesome if there was an open affiliate program architecture that would let retailers easily track traffic from and pay people who send customers their way.  Furthermore, if there is a centralized architecture, it might even be possible for people to systematically direct people to brick-and-mortar stores (possibly with some kind of printable coupon).  I could see a food blogger using this to mention the new cheese at their local food co-op.  If the system was easy enough to use, and to pay mentioners, it seems like it would benefit small businesses because they could more easily track how people were sharing their products on the web.  While content providers can have their wares tweeted or shared via social media, local stores dealing in tangible goods might enjoy being able to have more metrics about which products have a lot of interest.  Perhaps a local retailer would get more value from affiliate based advertising rather than traditional broadcast or newspaper advertising.

Photo by Thomas Hawk via Flickr.

Is the web suburban?

I’ve been reading Suburban Nation (thanks Sherri) and it made me look at new media ecologies with a city planning eye.  I wonder, is the web suburban?  Do we have memorable, open commons or digital cul-de-sacs?  Certainly, many government sites like the Illinois Tollway site quickly feel like I’m getting lost or running into dead ends.  Are online retailers generally becoming more like mom and pop stores or big boxes?  While the architecture of the web certainly allows for multiple routes through and across sites (akin to traditional city streets), typical navigation structures tend be tree-like which seem more like the disorienting and disconnected street patterns of subdivisions.  Are big infrastructure centers like Google or Amazon like suburban collector roads?  Is this city planning model even a capable metaphor for thinking about information ecologies?

Subdivision photo by futureatlas.com via Flickr.

The Fest, day 3

All day I heard different accounts of the “riot” that happened at the house show the night before. From different accounts I heard that people tried to tip over cars. I heard that some people tried to rough up a cop, that someone got tazed by a cop, that the cops got surrounded only to have the people surrounding the cops surrounded by more cops. “There were like 20 cop cars there,” told one storyteller. I wasn’t there so I don’t really know what happened or who was ultimately responsible for things ending up such a mess. In the end, it sucks that people got arrested, someone got tazered, the cops came away frightened with any stereotypes they had completely confirmed.

I’ve never thought of the police as an institution that helped me or made me feel safe. Assy lectures and power trips when we did get caught skateboarding, or the mixed thrill and anxiety of evasion when we didn’t galvanized this as a teenager. Hearing stories about the Fraternal Order of Police having a legal defense benefit for the Chicago detective who killed two young men while driving intoxicated or seeing the billboard advertising the FOP-sponsored gun show on the way to the Fest don’t help this. Still, I’m convinced that the vague cops vs. punks rhetoric that is so familiar does more to excite us or think of ourselves as in a certain cultural space than it does to change anything about the way police operate or the role they have in our society. Thinking of cops as a homogeneous enemy hinders accountability from both directions. First, it takes away the personal responsibility of a cop for her individual actions. She does what she does because she is a cop, not because she chooses to do something that is hurtful or unjust. Second, it overlooks the larger structural problems about how cops are trained, what motivates or forces someone to enter into a career in law enforcement, the values that our culture ties to positions of power, or the fear and separation that make the police intermediaries for resolving social conflicts.

I did see a few good sets at the Kickstand. Tubers were great. Ever since we played with them last year in Santa Cruz, I’ve really enjoyed how they make heavy, driving yet interesting music. I’m a sucker for parts that kind of drone or groove on and then break into a different part, like your head breaking through the water after you’re floating at the bottom of a pool. Theo and others had mentioned being stoked about P.S. Eliot, so I decided to go to their show. I think I enjoyed it the most of any of the sets I saw all weekend. There was just a sense of everyone in the audience being engaged in what was going on. It felt like a mutually shared experience, which is nice because the Fest always feels so impersonal to me.

While at the Kickstand, I ran into a friend that I met in Brighton when we were on tour in the UK this past spring. I told her that I was going back to school for journalism, and she mentioned that she had taken some journalism classes and was frustrated by the fact that her professors advised her class to write attention grabbing headlines that she felt distorted the story. Coincidentally, Patrick just wrote about something similar on his blog.

Everyone in Defiance, Ohio met up to eat together at Reggae Shack and I ordered the jerk tofu which was one of the spiciest things that I’ve ever eaten. It was so hot that it was this intensely visceral experience. I felt lightheaded and sweaty. It was pretty strange. After dinner, we went back to the Kickstand and tried to navigate around the huge line of people that were queuing up to get into the space. A few bands before us, the people at the gate weren’t letting anybody in and it made me feel stressed out. It was one of those situations where we couldn’t really do anything and it was probably equally frustrating for the people at the door and the people trying to get in. This was the second show we were playing and playing it to make sure we got to play an all-ages show and it was frustrating to think that people might get denied twice: for age and capacity.

When we played it was pretty fun and energetic, despite being really crowded. A bunch of stuff got knocked over, of course, and people were all over each other. I don’t know what can make this exciting in one context and stupid in another. I think the difference is about engagement. Is being at a show about a personal experience or a collective one? Is it about singing along together or being the person who sings the loudest? Is it about talking to the people who end up getting smooshed around them, or about being the person who gets to be at the front. I want to see this change somehow. I ‘m trying to not discount people’s excitement, but I want to feel comfortable myself and feel like everybody involved in a show is going somewhere together.

Oh yeah, I want to give a shout out to folks in Good Luck and Bomb the Music Industry for letting us borrow their stuff.

Photo of Tubers by Nicole Kibert via Flickr.

Playground Boys

I hear “Oona’s commmming!!! …” and then a stream of tag playing kids pours off of the playground equipment. Picking up O+F from school is way better than being at work.

It’s a little alarming that at any given moment there’s at least one pair of boys trying to pin each other to the ground. It’s harmless, at least by traditional “boys will be boys” standards, but it’s not too hard to see the progression of those ideas as a boy gets older. I wrestled and battled with boys when I was in elementary school, and I think what was most harmful was less what I learned from those moments of aggression and more what I didn’t learn – collaboration, yielding sometimes, listening, collective survival instead of individual survival.

Fest 8 Day 2: Kickball and Costumes

I started my second day at the fest playing kickball. I know that plenty of people have traumatic experiences with sports from their youth and that, even with punks, its easy for things to turn ugly, but I do love the feeling of big groups of people being involved in something together. As Bepstein was explaining the rules of kickball and basic baseball strategies, I realized that the rules of real sports are every bit as arbitrary as the ones that you make up for games as a kid.

After the game, I checked out a new Gainesville-based music streaming service that seemed to have a nice interface. I was disappointed that it didn’t really seem to offer anything new beyond the traditional promotional model of music. There has got to be a narrative for music that acknowledges that people get excited about it and that there is a need for artists to be supported by their work that doesn’t come off as soulless and cynical as “we offer to bands the ability to place themselves behind similar more established artists … Which means more loyal fans.”

I spent a lot of time eating and wandering around before we made plans to meet up back at the hotel for practice in the early evening. We decided that we would try to do costumes for our show so Bz, Theo, and I set out to find some thrift stores. I think I ended up having more fun checking out parts of Gainesville away from the campus and strip mall fashion stores (Citi Trends was amazing) than I would have rushing between venues or standing in lines. It was slim pickings for costumes for six, and we passed up soccer team for something that ended up being less Halloween costume and more Halloween-inspired frumpy-glam outfits. The core of the costumes were a set of holiday vests that you would remember from a few of your older elementary school teachers. Coupled with short-shorts that were definitely not sexy, I hope the goofiness of the costumes was a sort of antidote to all the skimpy Halloween costumes that have sadly become the standard. We bought purple lipstick that became face makeup at a liquor store, and the cashier there seemed to be enjoying himself at a job that could be really annoying. He even wrote happy Halloween messages on all the brown paper bags for the liquor purchases which was such a fun, sweet thing to do.

I feel like it’s hard to really watch a band when I have to get ready, but Good Luck seemed really energetic and like people were feeling it. Things sounded a little crazy, and the heat and lack of practice made playing a little challenging, but the show went pretty well. I got the sense the that for both us and the crowd, everything was a little muted from the heat of the venue and the long day. I could hear Bz’s violin parts really well which is nice. Rather than feeling vulnerable, play, in costume and makeup always makes me feel more natural so I think it was a good call. Good Luck looked really good in their matching postal worker outfits and I heard that the Max had awesome mad scientist costumes and an atomic bomb pinata. The Vena Cava looked good in costumes that I couldn’t really identify but made me think gang member from the “Beat It” video. In general, it didn’t really feel like Halloween (I really miss the Bloomington cover shows), but I’m glad that some bands made the effort.

Untitled

I get to the hotel where we’re staying for the fest and start paging through the dense, brightly covered booklet that describes the bands playing and other events over the course of the weekend. Bz tells me, “just read the first sentence of the description,” and I do. It says, “Welcome one and all to the biggest punk rock, shirts off, stale beer smelling, bear hugging, cheap booze swillin’, high five greeting, coosie totin’, family reunion, holiday, circus of fools we lovingly embrace simply called THE FEST.” It’s cheesy and frankly, I have a hard time feeling myself in those words, but I’m here. Amidst the amped up party atmosphere, there are some great people, and their great bands. Playing fewer and fewer shows with Defiance, Ohio has made being together and the shows we do play seem more special, which is kind of exciting. It makes it feel like this year, the show we play will actually be special instead of the anticlimax that comes with it being just another show, albeit a hyped one.

It is really nice to see people. There is a comfort in being reminded of the things that you know deeply about people, but sort of forgot. The hotel room table is covered in stacks of books from various authors and I remember how hyper-literate my band mates are, yet we’re still able to indulge in America’s Next Top Model marathons. There’s the ability to be goofy as we take videos of our own top-model style commercials for the fest. We take turns making sultry eyes at the mobile phone camera and end with our best “I love to fest.” Whether it was the last-minute, homemade togas the last time we played in Bloomington or these videos, the ability to make weird, theatrical things for our own enjoyment has been one of the most pleasurable experiences with my friends over the years.

We heard that the Max Levine Ensemble was playing a house show, so we wandered around Gainesville for a while until we found the house. It’s easy to spend a lot of at the fest wandering around and getting lost, but the walk felt nice and I like not feeling stuck on University Avenue.

After The Max played, we saw the Hot New Mexicans who were really good. I’ve seen them play a lot, but since we haven’t played many shows this year, I feel like my attention span for shows is so much longer. It’s nice because it helps me really enjoy seeing bands, even ones I’ve seen before, and notice new things about their music.

When their set finished, Bz, Sherri, and I went over to see 7 Seconds. The last time I had seen them was at a Warped Tour when I was a teenager. Unlike Bz, they weren’t really a band that I listened to a lot when I got into punk, but the first time I heard them, I realized that they were a huge influence to so many of the local hardcore bands in my home town. As, I get older, I’m inspired by people much older than me continuing to play music. We have a narrative of people touring and playing music until they burn out and self destruct or dropping out into a more conventional life, but there are many people who have decided to have families, or maybe careers who struggle to strike a balance with still being involved in punk music and I think their stories often go unmentioned. Kevin Seconds is an engaging performer, and a good storyteller. After performing for a few decades, its obvious that telling stories or connecting an old song with current events comes more easily, but not without sincerity. It’s so important to me, and one of the things that drew me to punk initially, that the songs come from somewhere, that there is such a direct link from experience or perspective on the world to lyrics and performance. It was interesting to hear the story behind the classic song Walk Together. Apparently, it was written after a show was canceled due to fear of metalhead vs. punk violence. It’s nice that their response was to write a song celebrating unity rather than a call to kick some metal ass.

Punk can be so contradictory, at once macho and positive, crucially critical and irrelevantly divisive. Listening to the radio and reading Billboards, I realized how conservative Florida can be. There was one stretch where there was an anti-choice billboard with a giant fetus every few miles. After seeing the preserved fetuses at the You: The Experience exhibit at Chicago’s Museum of Science and Industry, the billboard seemed even more manipulative because the fetus next to the text “My heart beats after 18 days didn’t look like the 18-day-old embryo that I saw at the museum. This is besides the point, though. For me, the debate isn’t really about what constitutes “life” at a certain stage of prenatal development, but about a consistent cultural desire to control the bodies and lives of women and a lack of support for health care for women and children as well as support for families that don’t fit the one mom, one dad, 2+ kids model. It’s just scary to think about all the energy and resources that went to put giant embryos beside the highway.

I also saw a billboard advertising the Fraternal Order of Police’s gun show and I just don’t see how encouraging people to buy guns helps ensure safety or order. The kicker was to hear a commercial for a conservative “Black Tie and Blue Jeans” event that said, “Conservatives, come eat MEAT while those liberals are eating their granola and driving their hybrids.” Note to progressive punks, snark and irony won’t change anything. The reality of talk-radio-style conservatism is so ridiculous that it will be more bizarre and gross than any parody. It really feels like there is a culture war, and I don’t want to fight in it. It feels like a test of faith, that there are enough people, coming from all different experiences, who want to be connected and empathetic to other people, who want to really solve problems, who want to base their perspective on things that are external to their experience on a careful, comprehensive discourse. I don’t want to “win” over people or organizations who promote ideas that I think are really harmful. I just want there to be a critical mass that makes them irrelevant.