The Spotlight : conferences

SQL Queries for Patron Edge, Part I

Nicholai Burton — Thu, 12 Nov 2009 00:12:00 GMT

While giving a session at the Museum Computer Network conference on extracting data from a Patron Edge database, one of the attendees suggested creating a handful of sample queries. The idea is that with some samples showing the important table joins, users can run with them, expand on them, or even just push them directly into Excel to do pivot tables.

With that in mind, here are a couple of samples to get started:

select *
from ReportStatic
inner join Clients on Clients.cltCode = rpsMailingList
inner join Orders on Orders.ordCode = rpsOrder

The ReportStatic table is one of the major data warehouse tables in the system. It has a row for every ticket sale and return, including event tickets that are part of a subscription. Below are some useful fields that aren’t intuitive without having worked in the back end for a while. Check them out:

rpsTickCode – For single tickets, this is the Tickets.tiCode field. For subscription event tickets, it is the TicketsSubscription.tisCode field
rpsMailingList – Corresponds to Clients.cltCode
rpsOrder – Corresponds to Orders.ordCode
rpsSaleStatus – Active tickets have a status of 1, canceled tickets are a 9
rpsTickType – Corresponds to Colors.clrStatus
rpsPriceLevel – Corresponds to Tariff.taCode

Now, let’s add a little more to the query:

select *
from ReportStatic
inner join Clients on Clients.cltCode = rpsMailingList
inner join Orders on Orders.ordCode = rpsOrder
inner join CalculatedCommission on CalculatedCommission.clcTicketNum = rpsTickCode

This gives us some new fields to look at:

clcCommission – Corresponds to Commission.cmsCode
clcCachInOut – 0 means a positive amount (i.e. commission), 1 is a negative amount (i.e. discount)
clcType – 1 for single tickets, 4 for memberships/subscriptions
clcStatus – 0 for active, 9 for returned

Hopefully this will help you get started writing your own queries and reports. Be sure to leave a comment here, or start a new thread on the Patron Edge forum, with questions and to share your own queries.

Call for Upcoming Conference Feedback

Nicholai Burton — Thu, 01 Oct 2009 13:17:00 GMT

One of the sessions at November's Blackbaud conference will be Effective Pricing and Discounting Strategies for Arts & Cultural Organizations. With that in mind, we wanted to gather as much feedback as possible to tailor the session to your needs. If you can, please check out the quick, five-question survey below. Please email your responses to the session coordinator jackie.huffman at blackbaud dot com. The more responses we get, the better the session will be. Thanks!

When do you offer discount pricing to the general public (non-members)? Choose all that apply.
1. The day of the event
2. The week of the event
3. The month of the event
4. All of the above
Do you use any of these promotion strategies? Choose all that apply.
1. Coupon with a promotion code
2. Buy one get one free offers
3. Combo offers that include multiple events
4. Packages with different types of events/items (e.g. dinner discount and event tickets or a CD and the performance tickets)
5. Other? Please specify.
Have you used any revenue management techniques? Choose all that apply.
1. Airline style pricing in which the closer to the event the higher the prices
2. Charging premium prices for specific events
3. Last minute discounting
4. Other? Please specify.
What are the biggest challenges your organization faces in regards to pricing and promotion? What are you doing to deal with them?
Would you be willing to share your experiences? If so, please provide contact information and one of our presenters will contact you.

Blogging the INTIX Conference

Nicholai Burton — Wed, 11 Feb 2009 02:02:00 GMT

So I'll be honest. INTIX was a little more somber this year, and definitely had fewer attendees. The sessions at INTIX Chicago last year had a lot more depth. Most of the ones I attended this time around were so high level that, while there were a few ideas for new side projects, there wasn't a lot of applied knowledge to bring back and share with you directly. The vendor booth was great though. I got to see some longtime clients and talk about how things are going (and brought one along for some dive-bar karaoke again this year). The Hale Centre Theatre was gracious to give my colleagues and me the full tour where we got to see all of the fun bits backstage, or around- and under-stage really since it's a theatre in the round. They also showed us amazing clips from past productions that highlight some of the incredible things they've been able to pull off technically. If you're ever in Salt Lake City, miss whatever you have to in order to make it to one of their shows.

This year's conference reminded me of something I'd kind of forgotten, having been off the frontline as a phone support rep the last couple of years. I enjoy hearing what's going on with you all. Of course I love hearing about what is going well. But I also like hearing what kind of back office management, patron services, and even development and finance issues are going on. It's often those conversations that lead to new insights, features, and customizations that allow you to focus more on your passion and worry less about your software. You can post a blog comment or start a thread on the forums to get input from other readers (I check them 2-3 times a week for new posts), or you can shoot me an email to talk about what's going on or just to vent a little. If I see a way to help I will, and I love a challenge; just ask around if you don't believe me.

Blogging the AZA Conference, Part II

Nicholai Burton — Thu, 25 Sep 2008 20:12:00 GMT

Continuing on with some more stuff I picked up at the conference, there was a big push on social media and keeping a well-maintained web presence. See below for the highlights and some ideas on how you can improve the web experience for your visitors, members and donors.

The session: Using Web 2.0 for Education, Advocacy, and Outreach

The concept: The social web is here. If you ignore it you are doing your organization and your mission a big disservice. Having a presence on the social web does not mean creating an account on every social networking site, but choosing carefully which ones to participate in, based on where your market is.

The details:

For those who still aren't familiar, the main concept of the term Web 2.0 is that the web is no longer a one-way information portal. It now serves and connects people in new ways every day, with things like blogs, forums, wikis, podcasting, video sharing, social networking and other tools.

You cannot afford to ignore your web presence. If you haven't built your own web presence on at least one of the major social networking sites, someone else has probably already done it for you and may be accidentally (or deliberately) spreading incorrect information. If you haven't created an account on Facebook or MySpace, take a quick look because it's likely that a fan of your organization is running a page under your name. During this session there were real-life examples of where this had happened. Luckily in every case, the person running the page was very happy to give it over to the actual organization. Know where you are and what things say about you.

Your brand does not belong to you anymore. Since the web has become so social, the reputation economy rules. You can live and die by reviews and comments about your organization. That's why it is so important to put out lots of your own content and work with individuals who are your fans. One zoo did a Flickr search for photos taken by visitors and found lots of images, some of them pretty high quality. They then went one better, sponsoring a Flickr photo contest with a prize (with rights to use the images on their site and in publications, of course), and this brought in hundreds of photos from individuals. People on the web are creating an impression of you that is out of your hands. Do what you can to mold that impression, and harness the creativity of the folks online who love what you do.

Be choosy about where you spend your time online. Instead of ignoring web presence, some people create the opposite problem by putting themselves on every social network they can find. Learn which sites your market is using and put a lot of time and effort into developing your presence on those sites. Don't create a presence on 10 different social networks, because chances are that you won't be able to keep up with all of them and those who do find you will only see outdated, half-finished content. My personal opinion is to skip MySpace altogether, it only provides any value as a place for bands to put music and concert schedules. Go with Facebook, and keep your eye on a newer social network called BrightKite, which is a location-based network. I think this one is going to get huge in the next 1-2 years.

Many people prefer self-service when possible. This one is pretty simple and was thrown out as an afterthought, but it shouldn't go unsaid. Your visitors and members want to be able to add and remove themselves from mailing lists, change their address and edit other personal details.

Now get out there, join a social network, improve your website and leave a comment about your experience!

Blogging the AZA conference, part I

Nicholai Burton — Mon, 22 Sep 2008 14:28:00 GMT

Sorry for not getting any posts out last week. I was at the conference for the Association of Zoos and Aquariums in Milwaukee and was taking everything in. There were a lot of specific kinds of issues I didn't realize that zoos face, but many of the problems and business practices do have a broader reach. In this series, I'm going to discuss relevant bits of information I picked up at various sessions and talk about how you can apply it to your organization to help achieve your mission.

The session: Membersip Marketing: Integrating Marketing Principles into your Membership Program

The concept: The speakers discussed having strong reporting and knowing where your market is, and taking advantage of the web to increase your reach and enrich your members' lives. This session was pretty good overall, and while a lot of the things discussed are common sense there were some cool perspectives on membership marketing and really good stuff on member retention.

The details:

Get new members to visit you as frequently as possible during that first year. An actively engaged member is much more likely to stay a member for a long time compared to a member who has only visited you once or twice in a year and doesn't know your mission. Have an inexpensive event or mixer for first-year members to get to know each other. If you're a zoo, it can be as simple as a behind-the-scenes animal feeding with a zookeeper. If you're an art museum it could be a brief lecture by a local art historian about your latest exhibition. Get creative, but get those people in!

If members get messages that aren't relevant, they will tune you out. With information overload in the form of mail, email, TV, radio and the web, you have to stay relevant in order to keep a person's filter from coming down on you. Once a member has decided that you aren't pertinent to his life, chances of renewing that membership are very low. If you send out electronic or print newsletters, there should be two versions; one that goes to visitors on your mailing list, and another one tailored specifically to members. Give the members something special, like an interview with a zookeeper/curator/creative director or extra detail about specific news items. Members aren't just visitors, they support your mission and want to know what is going on. The member-focused version should always include a web address to renew memberships on your site.

Tailor your marketing efforts to different target groups. Make sure you know who your market is made up of, and then determine the best way to reach those groups that are important to you. Do some data analysis to find out what ZIP codes are attending most frequently; send different marketing/renewal materials based on new vs. mult-year members or based on frequency of visits. One zoo wanted to to reach a large, Spanish-speaking market for membership, and did a lot of research rather than just sending out a direct mailing. The zoo determined that these folks have tightly-knit families and value family time greatly. They also frequently work overtime or more than one job, and want to relax on days off. This zoo's marketing materials described how there was "so much to see you can't finish in one day" and this was a major turn off. The organization wrote new materials that focused on the family aspect, the ability to bring a picnic, etc, written in native Spanish (not just translating the English copy). It was a hit and the zoo saw a major increase in membership by that market.

Measure marketing results. Marketing involves quite a bit of testing to see what works. Every organization creates and sends marketing materials, but many fail to measure the effectiveness of those campaigns. Simply capture the marketing information at the time of sale by having a quick "how did you hear about us", both at the point of sale and online. It doesn't require any customization and you can get the data back out using canned reports like the Marketing Campaign Report. It only adds a couple of seconds to a sale but makes a big difference in finding out whether or not you are spending your marketing dollars wisely.

Go take some of these ideas and run with them, and I'll be back again soon with more information from this and other great sessions.