Substituting Multiple Strings in Airtable

Thanks to W_Vann_Hall over on the Airtable Forums, I was able to figure out the formula I needed. The task is this:

* In JotForm, people select book covers to indicate which book they like.
* A Zapier zap takes that submission and puts it in my Airtable.
* However, the data appears at a bad URL with https://jotform.com added to the front of it. Worthless for our purposes.
* So to change that info, I need to substitute the long junk URL to the name of the book.
* I created a new column and added this formula:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE({Name of Column — leave in brackets},’https://www.ilovemylibrary.com/images/little_red.jpg’,’Little Red Riding Hood, ‘),’https://www.ilovemylibrary.com/images/three_little_pigs.jpg’,’Three Little Pigs, ‘),’https://www.ilovemylibrary.com/images/cinderella.jpg’,’Cinderella, ‘)

* Output: Little Red Riding Hood, Three Little Pigs, Cinderella,
* Note that I added a comma and a space at the end of each item. I’m sure I could try to figure out how to check for any leftover items, so there isn’t an extra comma, but eh.

I’m sure there’s more work to be done and something will break, but I’m grateful to get at least this much figured out.

Google Campaign URL Builder

I’ve been having trouble with getting my URL campaigns to show up consistently in Google Analytics. Fortunately, after talking over on some forums, they pointed me in the right direction. Though only two fields are required when using the Campaign URL Builder, you’ll want to feel out the first four fields at minimum.

Tips

I recommend keeping a spreadsheet to track all your campaign names and to develop consistent naming habits for your source and medium fields. For our end of year fundraising email, I made the mistake of using end_of_year_fundraising and eoy_fundraising as campaign names. If I had used the same term, the results would look nicer in Google Analytics.

For the campaign that worked, I get the campaign name of lnap_2018. When I click on it, then I see all the sources listed. It was helpful in knowing immediately that most people were clicking through to the registration form from the front of the website.

Bitly Integration

I should note that we have a (free!) custom URL through Bitly.com for our URL shortener. I grab a URL, take it to the Campaign URL Builder and add in my elements. Then I copy the generated URL into Bitly to give me a unique URL. Then when I look at my report later, I can see clicks from eblast, social media (should break this down to platform), website, etc.

Understanding Our Patrons Presentation

For an internal presentation, I developed a slidedeck based on the months of work my colleagues and I did to better understand our patrons. I won’t share the whole slidedeck, but some images are included in this post as illustration.

The first part dealt with departments categorizing their users into groups which share similar characteristics. They answered some questions about what users want, how they connect with us, and how we could do better by them. The second part was for me to crunch some data. The third involved surveying patrons guerilla-style. I’m still working on that last part.

We have some travelers!

Image is from a fascinating site. Click the image to see what I mean.

Do you know about this Google feature?

Four Layouts of Summer Reading

Our library uses the incredible art of Lisa Nowlain for our children’s summer reading program. It made it significantly easier to develop the web pages which support our four programs (pre-readers, kids, teens, and adults). This week I found out that even when we print out the trees at the size of children, they still look fresh.

Courtesy of Darien Library

Landing Page
To start my work, I sketched out a bunch of layouts for the children’s page. The head wasn’t there when I dropped by the Children’s Library, but her staff choose the design. The layout is almost identical to the Darien Olympics theme of last summer. I then selected my favorite for the landing page for all four programs. It took awhile as I had to create a large PSD file and duplicate the trees across it. The original wooden sign has tiny legs. I duplicated and extended them a little to complete my desired look. The buttons below are not what I wanted. However, the little wooden signs which fill up the brochure don’t work for the web. I have no idea why. So I used the plain ol’ generic default Bootstrap button for our website. I could have chosen another color, but the blue was the best default option.

Courtesy of Darien Library

Kids Page
Likewise, on the kids’ page, I cut off the legs on the sign to make it shorter. The PDFs are massive. I tried to squish them, but it became illegible. Maybe the watercolor background contributed to that? The location of the images is a little funny in order to make it usable on mobile. The tall tree and the kids collapsible below the information. Trying to get the blocks all to the same length was frustrating.

Courtesy of Darien Library

Teens
The background image for the teens page is from another artist (I’m unsure if he wants the attention). I struggled mightily with how to handle all the information on the teen page. The photos are all public domain. The struggle on this page are those movie covers. I ended up giving up on this yesterday. There are actually two more movies. The layout is a row with three columns. Each column has a row with three columns. When I blew the code out so I could look for the problem, it didn’t make sense. There should be enough room in it for up to 12 covers. But when I add in the extra ones, the images disappear and the date wraps to the second line. After 90 minutes, I finally gave up on it at the urging of my coworker. I’m displeased with that, but every tactic I tried failed. I don’t want to have rows with five or six images in it because then the covers are too dominating for the page.

Courtesy of Darien Library

Adults
I worked closely with colleague Virginia in developing the Adult Summer Reading page. She rejected my original header image and sent me three new ones to choose from. I selected the beach chairs. The challenge here was to keep her from the burden of creating a booklist for every single item on the Book Bingo Inspiration list. I managed to find some stuff on the website that would work. Then tasked her with developing 5 or 6 new ones just for this purpose. Her funniest chat was “What steampunk novel would you recommend?” for the new genre list.

The Book Readers Festival is another iteration of reusing Lisa’s artwork in new ways. I needed to make a flyer in a hurry so it could be posted around town. I opened up Canva looking for inspiration. I spotted it right away. Then I adjusted it for the design you see. It now exists as that image + flyer + poster which hangs over the front desk.

Marketing
I’m copying the URL of the pages into a Google URL builder, setting up the tags, and then copying that link into Bitly. I look forward to seeing what the numbers look like at the end of August.

Genealogy for Kids Program

Today my colleague Krishna and I hosted “Discover Your Family Story” program for children grades 3 to 6.

Participants will begin a family tree, learn how to conduct an oral history interview and learn about library resources that can help you discover your family story.

The materials took me four hours to put together. Do my research, design the program and the craft project (seen above), cut out 12 trunks and leaves and 200 nametags for the trees, and put together the handouts. The most interesting part were people texting their relatives to ask, “What were the names of your grandparents? I only knew them as Pop…”

This Week’s Small Victories

I’ve been rather busy at work to the point where I’ve had to turn people down who bring me last minute project requests. However, I’ve managed to complete these tasks:

* Analyze 3 months of email data on our weekly events email
* Recycled a four year old poster for a last minute request
* Designed the images needed for a double-sided physical giveaway
* Create a new poster for a festival
* Turn around-on-a-dime a new fundraising page
* Came up with a (clever-to-me) hack to make the fundraising page more mobile friendly using a button, anchor links, and white text
* Figured out how to draw a family tree diagram as an actual tree for next week’s genealogy class I’m teaching to children
* Do a complete sign and printed materials photo audit
* Started the professional development resource I’ve been asking Twitter about (the sortable, filterable, and search feature randomly broke, so it’s a WIP)

There’s also been an unusual new patron pattern. Four times in the past week, I’ve helped someone and then they look me in the eye, smile widely and say with more affection than I do for most things, “I love you.” Maybe everyone just needs a little love right now?

Collapsible FAQ

A little piece of work which I’m fairly happy with is this collapsible FAQ for our new WiFi page. It’s just some Bootstrap.

Closed:

Open:

It took a few variations to figure out the best layout for it. Originally I had screenshots from a Mac, PC, iPhone, iPad, and Android phone. Then I downsized and dropped the iPad. It’s listed first here since it’s the most common iOS for our users.

Pi Day Blizzard Email

My hobby is making nice throwaway emails like this. The design is a default theme available from MailChimp. I realized later that I should have made the adult programs plural. There was a last minute change and I had to get the email out, so I missed it. There’s a lot going on in the text which I’m not 100% happy with it. Everyone likes clicking on Jen’s recommendation from Hoopla though so that’s a success.

Courtesy of Darien Library