The Spotlight : Free Stuff

Rotating show images in Patron Edge Online

Sean Long — Fri, 30 Oct 2009 13:30:00 GMT

My colleague, Matt Cook, recently showed off a cool Image cube he put together for Patron Edge Online. Take a look at the information below, it would be cool to see this put to work. The really cool thing about this is that each side of the cube can link to different things, so this could help drive interest in things like special events, classes, or whatever the creative minds in your marketing department can think up! As the cube is set up right now, it uses the loader.asp file to allow it to link directly to specific shows. Pretty cool!

Disclaimer: This will require you to do a small amount of coding. The example is pretty easy to follow if you are familiar with HTML, Javascript or other coding languages, but you won’t be able to contact support for assistance setting this up.

1. Create a new screen part on the page where you want the image to display (In this example, I’ll use Template 1)

· Type = Include

· File Name = imagecube.html

· Top, Left, Width, Height – position the element as desired, but you want it to be the size of your images for best resolution. I used 450,20,120,80

2. Grab the latest copy of the image cube library Note: There are literally thousands of cool jquery libraries out there, many of them dealing with images, this is just one that looked slick to me. Most are easy to use and would be interchangeable. Unzip the library into your PEOSite folder (by default, C:\Program Files\Blackbaud\The Patron Edge Online\PEOSite)

3. Open up your favorite text editor, create imagecube.html and drop it in your PEOSite folder

Here’s the code for my file:

<head><script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
<script type="text/javascript" src="jquery.imagecube.js"></script>
</head>
<div id="defaultCube" style="width: 120px; height: 80px; overflow: none;">
<a href="loader.asp?target=show.asp?shCode=64"><img src="images/shows/show64.jpg" alt="Show 64 Title" title="Show 64 Title" style="width: 100%; height=100%;"/></a>
<a href="loader.asp?target=show.asp?shCode=38"><img src="images/shows/show38.jpg" alt="Show 38 Title" title="Show 38 Title" style="width: 100%; height=100%;"/></a>
<a href="loader.asp?target=show.asp?shCode=19"><img src="images/shows/show19.jpg" alt="Show 19 Title" title="Show 19 Title" style="width: 100%; height=100%;"/></a>
</div>
<script type="text/javascript">
$(document).ready(function() {
$.imagecube.setDefaults({speed: 1000, pause: 6000});
$('#defaultCube').imagecube();
});
</script>
You’ll need to make a few changes. Namely, I referenced show codes in my sample database. You can generate your loader link as needed. Your images will also likely not be named show64, so update the img src reference as well as the alt and Title as needed. There are a lot of benefits to using Google’s jQuery library, but you can download your own if needed.

Try it out and leave a comment if this worked for you!

Get Your Giving Clubs Right (With Free Stuff)

Nicholai Burton — Fri, 11 Sep 2009 15:36:00 GMT

Not too long ago, I got a really interesting Raiser's Edge question. "How can I find a constituent's longest stretch of consecutive years given, for my giving clubs?" You may have a great donor who gave from 1995-2005, missed a year, and then started giving again. He wouldn't show up in a Consecutive Years Giving report due to that one missing year, but you still want to send the right ask letter and amount.

I was intrigued by the problem. Intrigue being my greatest motivator, I was in the lab for a little while coming up with a good solution and thought I'd share it with you, my faithful readers. Just download and run this query, save the results as an import file, and import it as a constituent attribute called Max Consecutive Years. Now the field is available in queries, exports and mailings!

You can download the query here and run it against your Raiser's Edge database in SQL Management Studio. The result set will serve as your import file with all the required fields pre-filled. Now just perform a quick constituent attribute import and you're done!

Max consecutive years given

As with any stuff I give out on The Spotlight, this isn't a core piece of the product; it's custom so that means it is unsupported. My analysts (or Raiser's Edge analysts) cannot help run the query or take questions on it. RE support can definitely help if you're stuck on the import side of things, however. If you have any problems with it, please leave a comment, hit the forums, or send me an email.

Even More Potential

Nicholai Burton — Wed, 11 Feb 2009 21:24:00 GMT

I got some feedback on the Potential Revenue Report I put up last month and have made some improvements. Some folks told me that promoters who are used to working with Ticketsmasher (ack!) always ask for reports on free seats totaled by price level. One client told me she manually counts every unsold seat on the sales map for every price level in an event, does the math and then sends the information to the promoter in a spreadsheet. With that in mind and to make your lives easier, there are now two reports. The first breaks down by area within the event and the second breaks down by price level.

To install:

Download this set of files: Potential Revenue Report.zip
Add the stored procedure to your PE database
Add the report into Patron Edge per Knowledgebase solution BB142029

If you're using the report, leave a comment and let me know how it's working and if you have any feedback about it.

As with any stuff I give out on The Spotlight, this isn't a core piece of the product; it's custom so that means it is unsupported. My analysts cannot help with the report or take questions on it. If you have any problems with it, please leave a comment, hit the forums, or send me an email.

The Spotlight - Inauguration Edition

Nicholai Burton — Tue, 20 Jan 2009 21:06:00 GMT

Today was the big day. The new president's speech was inspiring. I was especially pleased with the references to exploiting the potential in newer, smarter energy and economic policies. With that in mind, and in what might be the worst segue ever on The Spotlight, I present to you a gift that allows you to see your sales potential in a newer, smarter way. The Potential Revenue Report was designed for reserved seating organizations who need to figure out how much their unsold seats are worth for a given group of events.

To install:

Download this set of files: Potential Revenue Report.zip
Add the stored procedure to your PE database
Add the report into Patron Edge per Knowledgebase solution BB142029

How is it smart? This report takes the price level for each unsold seat in an event, and uses that in combination with the base price type on the event's price list to find out the potential value of each seat. Then it just sums it up by area and event. It has filters that allow you to choose one or multiple shows and from/to event dates, so that you can see the potential of your entire show run on one report. You can also double-click an area in the report results to get a list of that area's unsold seats.

So what is the point? Well, the best way to use it is to find patterns in areas of each event and change your pricing accordingly. The orchestra only has a few seats left? Raise the ticket price on the precious few seats left. The balcony isn't doing well? Push it harder with the phone reps or in advertising, or consider a discounting scheme. There are no canned reports that give this data currently, so enjoy and leave a comment with how it's working for you.

How are you currently doing yield management at your organization? Do you have any advice for your fellow readers? Leave a note in the comments.

Post-Conference Goodies

Nicholai Burton — Fri, 21 Nov 2008 21:22:00 GMT

During the Blackbaud Conference for Nonprofits this year I demonstrated an application I had been working on that will instantly open the RE record for whatever PE record you're currently viewing with a single click. I promised to have it up on The Spotlight by the end of the week for all of you to enjoy, and I feel like it's finally finished enough to put up here. Here's the rundown of how to install and use it:

Download the following ZIP archive: Open RE Record From PE
Run Add RE Record Menu Item.sql against the PE database. If you don't want it in the spot for User App 01 or have installed Patron Edge to any folder other than the default, you need to update the script before running it.
Copy OpenRERecord.exe and Interop.BBREAPI7.dll into the Patron Edge application folder. This goes onto every workstation that you want to have the feature.
Access a CRM record in PE and click the link under Applications in the right-hand pane. You will be taken to the RE record.

And a few caveats:

This is not official functionality. It was not written or tested by programming so there may be bugs (nothing worse than an error message; it can't break your data).
If you have issues please leave a comment here or start a thread on the forums, or feel free to send me an email. Please do not contact the Patron Edge Support team, as they cannot help with this.
I wrote this in Visual Studio 2008, so it may require you to install the .NET 3.5 Framework.
I spent all my spare time for a couple of weeks writing this interface, so please leave a comment and let me know how it works (or how it doesn't)!

Happy weekend everyone!

PEO Donation Round-Up, v2

Nicholai Burton — Mon, 03 Nov 2008 13:24:00 GMT

The PEO Labs series brings you information or advice on items that are geared towards users who can take high-level concepts or sample code and run with them. Please note that many of the things discussed are not supported. That doesn't mean that an item is a warranty-voider; it just means that the Support Analysts can't walk you through it or troubleshoot it if it breaks.

A client recently contacted me about the free donation round-up customization I had written for PEO earlier this year. She had a couple of suggestions to improve it, and I had some time available last week to work on it. Here's the info on version 2 of the customization:

Updated for compatibility with PEO 3.3.3
Fixed a memory leak
Added a new parameter that allows the administrator to enter the amount to round; it is no longer hard-coded to go up to the next dollar

You can download the updated copy of the feature here. Please feel free to download and install it, and let me know what you think. You will need your system administrator to run a couple of SQL scripts and put a file in place, and then it's ready to go. Full instructions are included in the ZIP file. If you install it, leave a comment to let everyone know what kind of effect it is having on your online donations.

Tomorrow is Museum Day!

Nicholai Burton — Fri, 26 Sep 2008 20:01:00 GMT

I hope all of you museums out there are participating in the national Museum Day tomorrow, September 27th! Museum day is a great way to get those people who were interested in your organization but not willing to pay admission on the chance that the experience would be disappointing. Get them in, give them lots of information, and maybe even offer a one-day discount for purchasing a membership. For my readers out there from theatres and operas, go visit Smithsonian Magazine's Museum Day site to find out what museums near you are participating. I'm trying to decide between The Old Exchange and Provost Dungeon and Drayton Hall plantation. Have you participated in this event in past years? How has it affected visitors and membership? Leave a quick note in the comments.

Save Time by Deploying RE Integration Files

Nicholai Burton — Thu, 21 Aug 2008 20:11:00 GMT

Two of the most time consuming things with any client-server application are installations and updates. If you have both Patron Edge and The Raiser's Edge, you currently have an additional step after installing an RE workstation (or updating a PE workstation) to copy some files into the The Raiser's Edge\DRE folder and register a DLL. Without performing this step after each PE update, you risk breaking the integration with RE:Export. In order to make life a little bit easier, I have created a small tool that will allow you to perform this process with one click. In a nutshell, it does the following:

Locates your Patron Edge Deploy folder
Determines where The Raiser's Edge is installed on the workstation
Creates the DRE folder if necessary
Adds CustomNodes.xml, overwriting the existing copy if necessary
Adds and registers PEExport.dll, overwriting the existing copy if necessary

While not an official tool, it's simple and safe to use. Just download it from here, unzip and run it on a workstation to deploy the files. System administrators could even have this run at startup as part of a startup policy script to push the files after updating the server. Now workstation users can just launch Patron Edge to run the main update and you don't have to walk 15 users through the steps of deploying the additional files.

Create maps of your patrons using free online tools

Nicholai Burton — Fri, 15 Aug 2008 19:20:00 GMT

Check out the guest post I wrote today on The User's Edge blog. I explain how to use the RE Export module and a free service called Map a List to create a Google Maps mashup. These tools are not RE-specific though; you can run Marketing Mailing queries and generate a spreadsheet of address info, enabling you to get a visual of where your season ticket holders or major sponsors live.

What do you think? Do you find it useful to be able to visualize trends about your patrons on a map?

Send an SMS blast to patrons using free online tools

Nicholai Burton — Tue, 24 Jun 2008 13:51:00 GMT

You've used Patron Edge for a while, gotten the hang of Marketing Mailing, and are pretty good at generating lists for brochures and email blasts. But have you considered the possibility of using the system to send out an SMS blast to the mobile phones of your subscribers? In the past, if you wanted to send email to a mobile phone, you had to figure out how to format the email address properly in order to hit the phone, and of course every carrier uses a different format. There's a free service out called Flipout (by California-based company Teleflip) that lets you send an email - like 8432166200@teleflip.com - and it is sent to the phone as an SMS.

To use:

Sign up for the Flipout service
Create your list via Marketing Mailing and save it as a CSV file. Make sure that you're using the Restrictions feature of Patron Edge and only sending messages to patrons who have opted in. Receiving unsolicited text messages is one of my pet peeves, and I'm sure I'm not the only one.
Open the file in Calc or Excel and strip out any characters like parentheses or hyphens. This is easy to do with a quick Find and Replace
Append the text '@teleflip.com' to the end of the phone numbers. This can be done by adding a new column that contains only this text and then using a simple Concatenate formula. See this link for an example if you do not know how to concatenate fields
Use your favorite email merge application to send out the message.

You have to get a little creative with the CSV file, but once you've done it a time or two it becomes second nature. Are you currently sending text messages of any kind to your patrons? What are the reactions or what techniques are most effective? Leave a message in the comments.

What do your patrons think about your PEO site?

Nicholai Burton — Fri, 13 Jun 2008 20:50:00 GMT

While thinking about ways to get people more involved with an organization's online presence, I realized that most websites give an opportunity to leave feedback about the website itself. The best way to improve usability is to talk with users, right? I’ve done this with ING Direct, Netflix and a handful of other sites to let them know about bad links or usability issues. Often I leave positive feedback, just to let an organization know that I appreciate a new feature or change to their site that has made my life easier.

So following that line of thought, I whipped this up one evening while watching reruns of Scrubs. It is a new page for Patron Edge Online that allows a person to enter her name, email (if desired) and thoughts about your site. Now that version 3.3.3 has been released, I also threw in an accompanying custom report that can be generated to allow your web marketer or webmaster to review feedback on demand.

You can view a screenshot of what the page looks like here. As always, play with it on a test interface first! This feature is pretty basic (and potentially very valuable) but keep in mind that it is not supported. To install:
1. Download this ZIP file and extract the contents
2. Add Feedback_Form.asp and insert_feedback.asp to your public\bbcustomizations folder
3. If you do not have the Custom Site Settings table yet (from earlier PEO Labs articles), run Create Custom Site Settings Table.sql against your PEO database
4. Run LeaveFeedback.sql against your PEO database to create the custom table that will hold the actual comments
5. Create a new screen called Feedback
6. Create a new screen part on the Feedback screen (INCLUDE is the type) that points to bbcustomizations\Feedback_Form.asp
7. Edit the feedbackheader CSS class so that it matches your site’s overall design

You can also import the Site Feedback Report into the Custom Reports section of the Administration Site, or review the feedback via the Custom Tables folder.

Are you currently using any method of capturing user feedback about your website? How has it affected your design or business processes? Leave a message in the comments.

Rounding up change (but not from under sofa cushions)

Nicholai Burton — Mon, 26 May 2008 22:23:00 GMT

Over the last few months I've noticed that electric and water companies are really pushing the "round up" thing to work on public projects. So why not do something similar for Patron Edge Online? With a bit of ASP and JavaScript, we can find out how much a patron's total order is, then display a message asking if he would like to round the total up to the next dollar and give the remainder as a donation. The concept is simple, the patron doesn't mind giving away the electronic equivalent of spare change, and you get a donation towards your annual fund. Everyone wins!

Here you will find a zipped file containing two SQL scripts and an ASP page called round_up.asp. This page checks to see if the patron has already put a donation in her basket. If she has not, it will auto-magically display and ask if she would like to round the total up to the next dollar as a donation. It does not just look for totals that are even dollar amounts; a total of $42.30 would add a $0.70 donation and a total of $42.00 would add a $1.00 donation.

To install:
1. Place round_up.asp into your PEOSite\bbCustomizations folder
2. If you do not already have the eSroCstmSiteSettings custom table, run Create Custom Site Settings Table.sql on the PEO database
3. Run Round Up Feature.sql on the PEO database
4. Go to Administration, Custom Tables, eSroCstmSiteSettings and set Donation Round Up Fund to the desired code (found in Content, Donation)
5. Drill into the screen parts of my_basket.asp for the appropriate interface
6. Set round_up.asp as an Include After on the basket view screen part, or create a new screen part below the basket view in the desired location

The page is pretty basic and I created it in about 4 hours over the course of a weekend. It would have taken about half that if I had ever taken a programming class (the life of a French/Business major, *sigh*). Please note that this is custom PEO code. It is not supported (which just means my analysts cannot answer questions about it) but works beautifully on my system. Still, be sure to test before putting it on your live site; I claim no responsibility if it breaks your site, burns your supper or lets your dog out of the fence!

Did you find the round up feature useful? Would you like to see more of this kind of thing on the site? Leave a message in the comments.

Sorting a spreadsheet by color

Nicholai Burton — Wed, 23 Apr 2008 18:04:00 GMT

So I was given an internal cleanup project that had a spreadsheet of 500 items that needed to be reviewed and acted on. As I worked through the list over the course of a couple of weeks, I color-coded the items based on the next action to take (follow up, merge, close, etc). At the end of the project I wanted to sort the spreadsheet by color, only to discover that Excel can't do this! Well, being the curious sort I started exploring the option of doing it with a VBA macro. Using a combination of web searches and help files, I put this macro together:

Sub ColorSort()

Dim ColorValue
Dim LastRow
Dim LastColumn

LastRow = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For i = 2 To LastRow

ColorValue = Cells(i, LastColumn).Offset(0, -1).Interior.ColorIndex
Cells(i, LastColumn).Value = ColorValue

Next i

End Sub

You just need to create a column at the end of your worksheet and call it "Color" or something like that. Next, run the macro; it assigns a number based on the background color of the row. You can then sort the worksheet by the "Color" column, and voila!

While this isn't directly Patron Edge or Patron Edge Online related, it has become such a great tool for me I thought some of you might find it helpful as well. Maybe you've had an event get cancelled and you get a phone list through Marketing Mailing that someone is color-coding. Maybe you are using the Raiser's Edge export module and working with a spreadsheet it kicked out. Or maybe you're doing something completely non-work-related and you find a use for this. If this macro helps you save some time, leave a comment for others to see!

Add a favicon to your website

Nicholai Burton — Tue, 22 Apr 2008 14:54:00 GMT

For those who are not familiar, a favicon is the icon that shows up in your bookmarks toolbar for a saved site, and in the address bar/tab header of your web browser when visiting a site. It's a 16x16 icon that you can use as another way to reinforce your organization's "brand" on the web.

For those of you know know all about these, I'm sure your question is, "when do we get this functionality in Patron Edge Online?" You can actually do it right now. I have found some great resources online that will allow you to add a favicon onto your current PEO site regardless of version. Here's the scoop:

Create a favicon for your site

If you don't already have a favicon, you need to create an ICO image file that is at least 16x16 pixels. Your marketing folks should be able to handle something like this pretty easily, or you can use some free tools on the web. Here are a couple of sites that can do this for you:

Add files to to your web server

There are three files that you need in order to make this work:

The image file itself. I recommend placing it in the public\Images folder of your website
This ZIP file. It contains an HTML page and a JavaScript library (and a screenshot for later). Place the HTML and JS files in your public\bbCustomizations folder. If you are interested in the technical bits of the JS library, there is a link to the author's website in its comments.

Modify favicon.htm to point to your icon

Edit favicon.htm in Notepad or your favorite HTML editor. If your custom piece folder is not called bbCustomizations, you will need to edit the first line accordingly so that the page can find the JavaScript library. Now edit the sixth line and replace the URL here with the location of your favicon file.

Add a reference to the file on your Admin Site

In Site Design, edit each of the Templates of the Interface you are using and create a new screen part for them. See the screenshot from the above ZIP file if you need an example screen part to help you. Since this is a script, no text will be visible on the page itself, so the dimensions of the screen part don't matter at all.

Do you use favicons on your main or ticketing websites? How has it reinforced your organization's image? Leave a note in the comments!

Using Google Analytics to get more out of PEO

Nicholai Burton — Thu, 10 Apr 2008 17:17:00 GMT

Google has a powerful, free tool available, called Google Analytics, that will allow you to collect all kinds of aggregate data about your site's visitors. You can use this information to:

Track the geographic location of site visitors to plan where to spend your advertising dollars
Determine what page a visitor hits first on your site and how long he spends there
See what sites or search engines are referring patrons to you
Look for trends to see if parts of your site are confusing or cumbersome to visitors
Discover how many times a visitor comes to your site before making a purchase

I'm not a marketer or a webmaster, but I'm sure there are a hundred other uses you can come up with for this tool. There is a nice demo on their site here that walks you through what the tool can do and how it works. This probably leaves you with some questions. How much does it cost? How long will it take to implement? How do you actually make it happen?

How much does it cost?

According to the Google Analytics site, use of the service is free if you get less than five million (million!) pageviews per month.

How long will it take to get started?

Getting an account with Google Analytics takes a few minutes, and adding it into PEO only a little longer. I would plan for 30 minutes to an hour for the whole project, plus any time you spend playing with settings and setting up reports in your Google Analytics account.

How do I do it?

First, you need to sign up for an account. Once you sign up, you receive something called a Tracking Code. Hold on to this as we will need it again soon.
Download this file and place it in the bbCustomizations folder of your public site.
Edit line six of the file, replacing "UA-xxxx-x" with the Tracking Code you got in step one.
On the PEO Admin site, go into Site Design and edit all of the screens of the interface you are using. In the Include After field, enter bbCustomizations\GAnalytics.htm. This is the part that can take a little while, depending on how many screens you have.

And you're finished! That wasn't so difficult, was it? After you have this all set up in Patron Edge Online, you will definitely want to go back into your Google Analytics account and do some tweaking so that the data it captures for you is more accurate.

You have some opportunities to get really advanced with this if you'd like by tagging the URLs that you send out in your marketing campaigns. For example, tagging your email blasts with things like campaign information will help you determine which ones are most effective. My site is internal only, so I haven't been able to try tagging to verify it plays nicely with PEO, but let me know if you try it out and how well it works. How are you using analytical/statistical data to drive your mission? Leave a message in the comments.