Data Clean-Up using Google Sheets Functions

In November 2020, TechSoup invited me to talk about our methods of data-clean-up with Google Sheets Functions for their TechTips Digest event. The list of all recordings from this event is now available on the Netsquared blog.

Before we bulk-import thousands of records into a CiviCRM, review the data mostly giving in a CSV (comma-separated-values) file. We systematically go through the formatting issues and separate clustered data, like First and Last name or all address information in one cell.

For this talk, I collected the various use cases for which we use Google Sheets functions to clean up the data before we start the importing processes.

We share slidedeck, recording and transcript in this post.
If you are interested in receiving information about our course on Google Sheets, leave your name and email address in below form, please.

Slidedeck

Video Recording

Transcript is available below.

More Resources

Google Sheets Functions Course

Take a deep dive into data clean-up and Map data and other data processing with Google Sheets

In this talk, we went through the various Google Sheet functions very fast and only for one use case. Data-cleanup. In our course on Google Sheets, we will show you different functions for different use cases, not only for cleaning up data for your CRM imports. You can use Google Sheets data for your interactive maps, resources for you #nocode website. The beta version of this course will start in January 2021. We invited you to be part of the beta version free of charge. If you are interested, please leave our name and email address, and we will notify you when we start our first cohort.

Yes, I am interested in your Google Sheets Course

Table of Content + Transcript

Speaker introduction: Birgit Pauli-Haack

Joyce Bettencourt: Today, we have Birgit Pauli-Haack, and she is going to present on Data clean-up using Google Sheets. Birgit has been working with nonprofit organizations on database handling and website development and design and tech training since 1998. And she’s the owner of Pauli Systems. And with her team of designers, developers, sysadmins, and database professionals, she helps nonprofits to reach out to donors and members and other supporters through an optimized digital presence and provides integration between websites and CRMs. So with that, I will turn it over to Birgit.

Birgit Pauli-Haack: Well, thank you so much, Joyce. It’s wonderful to be here and thank you for attending this session. We don’t have a lot of time today, so I’m going to share my screen, and we dive right in. And what you also see is that on the slide deck, you see on every page, the link to the online version of it so feel free to check it out afterwards. Questions, please till the end. And now let’s go and tackle the Google Sheets and your data clean-up.

Data clean-up with Google Sheets is definitely a skill to learn, and it comes quite handy in a lot of data manipulations situations, not only just the data clean-up, but every time you’re using CSV files and want a hand do something else with it, you could use Google Sheets for add. So some of the things that I show you is equivalent to Excel.

10 use cases for data manipulation with Google Sheets

So it’s not a wasted skill. You can use it in any of them. It might just be a little of a different word or different function call or something like that. But you can look that up later. Okay. So, what will you learn today? I have for you 10 use cases for data manipulation with Google Sheets, and we talk about split, proper, join, switch, Ifs (), concatenate (), left () randbetween () and how to hide columns and how to bulk copy formulas. The first one is this data and most of the data comes from a real life example or real life examples that we deal with. And I picked some out. So the first case is that you get the CSV file, you load it up and see you in Google Sheet. And you see that the first name is actually has four couples. The first name of both couples in the one field and Joe CRM doesn’t like that very much helps you with it gives you all kinds of different problems.

1 Couple’s first names in one field. Split(C3, ” and”, FALSE)

So, first we learn about the SPLIT() function, and this is definitely one of the most used functions. You have a text and when it says text, it means it’s the cell that you’re pointing at, like the C3 and the example. And we have the delimiter of those two things, and then the delimiters and with a space in between. And because we don’t want to cut it up after the end, not on the after the end or the [inaudible 00:03:08] . We just want to kind of after the whole word. So, and what you do is you need two more columns because all of a sudden you have two fields, additional, you have the first name and the spouse’s name. So you add two columns and then you create, put that formula into the field of, and then it will split out the spouse name next to it. So that’s fairly easy and it gets you at least somewhere to get the first name cleaned-up.

2 Multiple Address formats. PROPER() function

In the same data set, we also found the second use cases, Multiple Address formats right now. And just ignore that this all in one field right now, we began to talk about it later, but you have small letters, we have all CAPs, we have different spellings. And for me, that needs to be cleaned-up either in one consistent way. And I introduced it to my favorite function that is proper gives you proper case. It’s actually title case that it does. It will return every first letter of each word will be uppercase, all the other letters will be lowercase. The relative function related to that function is also LOWER() and UPPER().

If you are not like me, that you don’t like uppercase, you actually favor it, you would say, okay, if you have mixed case, you would just wrap it up in an upper function and then have another column Luther right formatting. So you do you get the principle is that you have an empty column. You have a column that needs to be changed. You open up another column and then you apply the formula and it makes a copy. But it’s the right copy. Yeah, like here, the number, the address and number seven is now title case in number six in row six, Tallahassee, all CAPs is now also a title case. So that’s another set of that. So split and proper.

3 Address – all in one cell. SPLIT(AC2 ,”,”,TRUE)

Now let’s deal with the all-in-one address. And you already learned one of the one function what’s called split. Let’s try and apply it here too. So, we need additional four columns and that the, for the address for the city, for the state and for the zip code, and they’ll all seem to be the limited by a comma.

 So that will be our delimiter, where to split and that’s how the function lift is split. And then AC two, that’s this column, AC two is the second row and then use the delimiter of comma and then do it for all occurrences. And that gives us the address line here, the city, yeah. In one column.

4 Split State + Zip code

And then we also have the state and zip code and you see that as some additional things to do, because for some of the addresses, Iowa and the Zip code, we’re not separated by a comma, so it didn’t catch it. And we can do a second pass at it and split up the zip code. Again, you need to do two additional columns and then you do another split, the split AG two, that’s this one here and then divide by not by comma, but by space.

And then you get it into columns again. Yeah.

5 Split too much, aahhh. Two-word States. JOIN(” “,AI38:AJ38)

Well, when we did the split for the state and the zip code, we didn’t provide for a two word state and we have a lot of them. You have North Carolina, South Carolina, and New York, New Jersey, they’re all two word States. So you want to kind of do another, you want to do the opposite of it, but you need another column for it, the opposite of it and combine those two. And that is the join function and concatenates the elements of one or more one dimensional list, Arrays equals list using specifying the limiter. And that looks like this. We joined the two columns again and put a space in between. Yeah. So we, the two columns, AG to AJ AI to AJ and then a space in between. And then we have the state name and that’s the end of the state part, but we also saw film what you see and you see it on this screen as well is we have three columns with different zip codes.

 Some of them are empty. Some of them are things in there. Then we have the combination, but let’s first figure out something else we saw here. Yeah.

6 Convert  “FL” to  “Florida”. SWITCH(AI5, “FL”, “Florida”, “GA”, “Georgia”, AI5)

That is Florida and GA instead of Florida and Georgia, let’s do that. And I introduce you to the SWITCH () function, which means tests an expression and expression is a cell that you can point to against the list of cases and returns the corresponding value of the first matching case. So for this case, we have, if there’s FL in the cell, turn it into Florida. If there’s GA in the cell, turn it to Georgia. And so you have another mother in that same column, you can put that in there because it will function here for everyone, even the ones that, because it only goes to this column down. So now you have all the States in full form there.

7 Multiple Zip Code columns. =IFS(AH37 >0, AH37,  ISNUMBER(AJ37), AJ37,AK37>0, AK37)

 So what you have already mentioned is that we have three columns with zip codes and one column AJ actually has words and numbers. So let’s tackle that and you guessed it. We will add another column and then combine this with an Ifs () function. So the Ifs () function, evaluates multiple conditions and returns a value that corresponds with the first true condition. It looks more complicated than it actually is. So we evaluate each zip code column and then do something with it. So the formulas Orland is also column AI column. And it looks AH that’s, this one, if it’s larger or greater than zero, use what’s in the column. Otherwise ignore. If AJ 37, that’s the one where we have the second word of the two words States in there from our previous split. But if it’s a zip code or if it’s numbers, then use that number here.

Yeah. And then if the AK is 37 or AK is 37 here, is greater than zero, use that. And that gives us the full zip code for all the entries. If we copy that over to all that we kind of summarize that in a different way. So, and now you’ve to find a review. You want to see an eye, so you’d be not getting confused.

8 Review final data – Hide / unhide columns

And I don’t get confused. I highlight the column with a different color. That is actually the one that I want to use for my data input and to review it, I’m I hide the columns and then so I can see it. So because you have a multi-step kind of function for one thing to the next, do not delete those columns because some formulas depend on something in that column or content in that column. So you can hide them.

And then if you want to print stuff, print them out or something like that, or make a copy of it or something like that. You can unhide them and hide them. I also, so when you import into a CRM, you use the full spreadsheet as a CSV file, and you will map out the columns with the data fields in your CRM. But if you have multiple zip code fields in the header, you don’t know which one do you want to use. So I give the one that I’m going to use one particular header. And I write that down here, it’s the all zip and the state field will be populated from the state name column. The address line one and the city, that’s all clear, but for the city states, we have multiple columns of each. We were ready to import that.

9 Username/ Password for dozens of people. CONCATENATE(LEFT(D2, 1),LEFT(F2,6)) / RANDBETWEEN(11111111, 99999999)

 And now I have another use case for you. See, if you have now maybe 60 or something, people who want to be part of a crowdfunding campaign, and you want to your system needs username and password for each of them, and you want to send it to them. So they don’t have to mess around with things right there in an email. So you could use the mail merge extension to Google drive. But first you need to create a username from the data as well as a password. And we used two more function. And this also shows you that you can Daisy chain some of the functions. So for the username, we take the first letter of the first name and the first six letters of the last name. So, and then combine it to one. The combined end with one is happening with the CONCATENATE function.

 And the first letter of the first name is a left function, cannot left first name one is the number of character. And the password we just do random numbers that all created through that Randbetween function. Okay, let’s see how that looks. So for the first one it’s concatenate left D2 is where my first name is. That’s a little further over there, and we do one letter of that and then reduce the Left F2 is the last name. Really? We use the left six letters, how you do this is relatively. You can do the first three letters of the first name at the three letters of the last name, how you make some action is yeah, it’s up to you, but it gives you something like that. The username there, and for the password we use Randbetween.

And because I want an eight number eighty to password, I say, give me a number between 1,000,011, 1 million, 100,000, well, eighty ones and between eighty ones and eighty nines. So I don’t have to, and it creates a password for them, or, yeah.

10 Bulk-copy cells

So, and now how would you, one last feature would be to see how you can then bulk-copy all the cells into the other roads? Yeah. So when you hover over the little red square, not red loses, how about over the little red square when you mark a cell and then pull it down to all the whole row and it automatically replicates the formula and you can fill it up not to 20, you can do it to a thousand to 400 to 16,000. I hope I haven’t tried that dead 16,000. That’s my next, the next goal. What you don’t see on the screenshot is that there is, when you hover over the little blue square there, it shows you a very faint cross, and then that appears you can drag it down and then kind of do that. Yeah. So how am I looking for time? I’m almost on the time. Yes. Anything else? Well, the, as I said, nptp dot US slash pauli dash techtips is a slide deck where I can go and let’s do Q and A

Question and Answers

Joyce Bettencourt: Quick bootcamp and a Google Sheets. That was fantastic. So if any of you have questions, you’re welcome. If any of you have questions, you can put those in chat. I know, obviously this is a, these presentations are pretty quick. Bobby has a question. Does Google Sheets have a special paste function? Or you can copy and paste it with values and not formulas?

Does Google Sheets have a special paste function?

Birgit Pauli-Haack: Yes, yes. It has plenty of paste functions. One of them I like real much is when you paste it from another sheet, you have a decision to take the values that you have right now at this time, this moment in time, or you can paste it from the other spreadsheet and it updates this spreadsheet when the other one updates. So it’s kind of, you have to data connected that really also works actually in Google documents. So if you have a monthly report that you do, and every month, those the graphic kind of changes, you can make the document that reports about it, just copy and paste the graph and link it to the spreadsheet. And that would work as well.

Joyce Bettencourt:   Great. And we have another one. Can you turn, Oh, actually, do you have tips on how to automatically eliminate or hide empty cells?

How to automatically eliminate or hide empty cells?

Birgit Pauli-Haack: Empty cells? Well, if you have empty rows, you can hide rows. I think, yes, you can hide rows and you can hide columns. You cannot hide empty cells because then it kind of, it moves right when you, so if you have in a row, in a column top 10 and top 20, you cannot eliminate the 15 in between or the 10, the five in between, because yeah. You would delete either the rows of it at the whole rows, and then it doesn’t work anymore. So yeah.

Can you turn state names and just standard abbreviations?

Joyce Bettencourt:  Nina asks, can you turn state names and just standard abbreviations?

Birgit Pauli-Haack: Yes. There is no function for that, but you can certainly do that switch exactly the other way around. I would not type it in yourself. Yeah. You could probably get it from another spreadsheet kind of thing. Yeah. And then concatenate() that and into the formula, but it gets a little complicated. Yeah. Most of the time your CRM is actually doing that. If you see your CRMs connected with the USPs  API, then no matter what you do on the address in the import thing, it will normally also convert it to a standard, a USP as standardized address in your CRM. So yeah. I don’t know how much time you want to spend on that.

What’s the difference between the switch function and Find and Replace?

Joyce Bettencourt: Yeah. Yeah. And kind of following on that switch, what’s the difference between doing the switch function and doing a find and replace?

Birgit Pauli-Haack: Well, you can do numbers to the well, you need to do multiple steps on the switch or you can only search for FL and then replace all Florida. Yeah. You can only do kind of all find all the GAs and then two. So, it’s a multistep process when you do it and find & replace because unit, you can say, find FL or GA and replace it. So that’s why that is so interesting to do the switch for it.

Can you get rid of the useless columns?

Joyce Bettencourt: And let’s say Jane asks, if you doing concatenating a spreadsheet, can you get rid of the useless columns, turning result into usable columns and values and then delete the columns you don’t need. Not sure she’s clear with that, but…

Birgit Pauli-Haack: Well, you can do this. I wouldn’t recommend it because it’s much more work than you would want your chest height, the column. Yeah. If you need it for presentation or something like that, but you always have the original data there. I’m more like, not the leading thing so much.

Joyce Bettencourt:   Okay. Any other questions? We probably have like room for what or so more. And I don’t know if there are any other while we’re waiting for questions, if there are any other tips that fall along this that you want to mention to Jane. So sorry. Birgit. Yeah.

Five-minute demo to create a Google Map

Birgit Pauli-Haack: And so I did, it might be four months ago. Eli also had a second has a kind of a speed dating thing where I did a five-minute Google Maps. Yeah. And that also use it Google Sheets, where you can put in all the addresses and then have them show up as a pin on the map. If you go to Netsquared dot org on the blog, you will see that part as well. It’s yeah. It was another multi-speaker event and it was really cool. And I actually do live demonstration, which I didn’t do here because I knew better.

Joyce Bettencourt: Yeah. Less tech problems. And I don’t know if there are any other final questions. Certainly I put the link in chat and I’ll grab that again here too, for folks.

Birgit Pauli-Haack: Yeah. Well, we are preparing a course for Google Sheets and all kinds of different things and yeah, that will start in January. If you are up for beta testing that just leave your name and email address, and we’ll let you know searching for things. So if you’re looking for that.

Other resources for learning the anatomy of each formula

Joyce Bettencourt: Yeah. And on that link, there was a form at the bottom that you can just fill in with your name and email at the end, diverse lies that document, the links that I just shared. And Charlie was asking if you had any other resources for learning the anatomy of each formula, the how to know what to put in each part. So, yeah,

Birgit Pauli-Haack: Right. There are some, the Google documentation is fantastic. So in some slides with the formula where that has the best expression of it, there’s a link to the Google documentation. And there you can read up on about each formula as well as seeing what’s related formula and also a list of quite a few formulas there.

Joyce Bettencourt: That’s great.

Birgit Pauli-Haack: Yeah. So, Marlene asked to recommend using Google Docs or is something else better? Well, it depends on, on your skill set and on how much you want to share things. Google Docs is really good at sharing things, collaborating on things and not losing things. When somebody overwrites stuff, I also Google for non-profits is free through TechSoup. So, that’s definitely a plus on that. And I really, especially when you have remote teams, something like Google Drive or Google Docs is definitely better than switching for different files from yeah. From Excel that you, yeah. You can only do version control them a bit to know which is the latest one when you’ve worked with a team.

How to create a Stripe Account for Online Payment Processing

For our business and nonprofit customers, we mostly connect their website to a Stripe account for online payment processing. Below we list the steps to create a Stripe account and what to prepare before you start the process.

About Stripe

The two Irish developers, John and Patrick Collison, started Stripe 2010. They wanted to provide an easier way for developers to implement payment gateway system for apps and websites. Later on, Stripe partnered with Visa and other credit card companies and it now one of the most secure, payment systems with a strong focus on fraud prevention. It was also one of the first payment gateways besides PayPal that allows for recurring charges on customer credit cards. The combination of ease of integration and high security makes it the most popular payment gateway system.

Step 0: Have all information ready

  • Who is the Admin person? Probably You.
    • What’s her email address?
    • What will be a good password?
    • What’s your last four digits of Social Security Number
  • What’s your bank’s routing number
  • What’s your bank account number

Step 1: Enter the Registration Page on Stripe.com

Open your Internet Browser and go to https://dashboard.stripe.com/register

Step 2: Fill out the form to create an account

stripe-1.png

Notes: On this form all information is used for the admin user account. Make sure the email address is a specific email address and only the person dealing with company financials has access to it. It’s also the email address that will be contacted when you need to change your password. During normal business operation, this email address will receive notifications about payments and other important messages about your online account. When selecting a password, make sure it contains at least one lowercase, one uppercase, one number and one special character. 

When you are ready,

  • enter your email address,
  • password,
  • confirm the password,
  • check next to “I am not a robot” and
  • click on “Create your Stripe account”.

Step:3 Confirm you are the company representative

Stripe will need additional information from you to properly identify you. On this page, Stripe has some details on Identity verification

Optionally, you can use your Facebook account, your LinkedIn account or your Google Account and you have to fill in your personal identifying information

  • Your personal legal name
  • Your Date of Birth
  • The last four digits of your Social Security Number

Step 4: Enter Bank Details

You would need to have available your Routing number and your Account number. Fill out the blanks and then click on “Activate Account”

Once your Account Application Status is Approved you are able to take transactions.

Step 5: Get the Keys to connect Stripe to your Web Site

Go to https://dashboard.stripe.com/account/apikeys

Or via Dashbaord > Developers > API Keys

First you need to send to your web developer  Test Keys so she can test that your website is connecting to the Stripe account.

Your webdeveloper needs the Publishable key and the Secret Key (you need to click on “Reveal test Key Token)

Important: Please copy/paste the keys, as it’s very hard to type them from a picture.

Video: How to get your API Keys:

Once the configuration and integration work, your web developer would need also the live keys. On top of the screen is a toggle slider that lets you switching from live to test and vice versa.

And then click on “Reveal  live key token” to display the secret key and copy/paste that as well.

Step 6: Get the Webhooks

For the Contribution form to work the background communciation also need Webhooks.

Please Go to Dashboard > Developers > Webhooks

And add a Webhook: https://yoursite.com/?callback=gravityformsstripe

Video: How to add a webhook (GravityForm)

At that point your web developer needs to change your website to be on a secure socket layer and have HTTPS enabled on the server.

That’s all you need. Feel free to send us your questions.

Live: CiviCRM for COVID-19 Response – June 25, 2020

The upcoming virtual event “CiviCRM for COVID-19 Response” scheduled for June 25, 2020, at noon (EDT) brings together nonprofits organizations and consultants to discuss how they have used CiviCRM and discuss their solutions for service delivery and crisis response. The event will be free of charge.

“Whether you are new to the open source CRM for the civic sector, evaluating solutions, or an experienced user, there will be a mix of presentations and workshop discussions. Sessions will explore needs-mapping, crowdfunding, staff rotas and volunteer registration, using CiviCRM with both Drupal and WordPress. With speakers from Calgary to Cologne, it is scheduled for the daytime in the Americas, Europe and Western Africa. For the rest of the world, videos of the presentations will be available afterwards.”

CiviCRM for COVID-19 Response website

Session and Speakers

  • The CiviCRM Mutual Aid extension for matching needs & offers of help with Björn Enders, Systopia | Cologne, Germany (Mutual Aid Extension on GitHub)
  • Crowdfunding in CiviCRM with Alejandro Salgado, iXiam | Barcelona, Spain
  • Worker scheduling for a Community Health Centre’s social dispatch team with Karin Gerritsen, Semper IT | Calgary, Canada
  • Using CiviVolunteer to co-ordinate volunteers during lockdown with Louisa Peters, Volunteer Coordinator of Moor Allerton Elderly Care | Leeds, UK
  • Coordinating bulk purchasing and distribution of food with Eric Goldhagen, Openflows Community Technology Coop, Amy Rebecca Marsico and Jacob Missen, Flatbush United Mutual Aid | New York, USA
  • Beginners’ guide to CiviCRM with Rose Lanigan, Ruza Solutions | Bradford, UK
  • Using WordPress & CiviCRM with Kevin Cristiano & Dana Skallman, Tadpole Collective, New York, USA

Present at CiviCRM for COVID-19 Response

People, who are using CiviCRM as part of a response to Coronavirus: be it medical, community support, fundraising, Mutual Aid or anything else, and would like contribute with a presentation can connect with the organizers in the #covid-19 channel on CiviCRM Chat.

Nine (9) Tips for Nonprofit Websites

The worldwide pandemic is providing you with an opportunity.  Many people will come through this with a keen understanding of what is important to them as a human being and the world. You are in a unique position to contribute to your visitor’s journey to have an impact  and provide real life experiences to do good in this world. Show your visitors the value of volunteering, donation or just applauding. Invite your visitors into their imagination. 

The web is not your mother’s internet anymore. In today’s nonprofit world, it’s not enough to simply have a website to connect with your supporters. Your website is the direct representation of your company to the public. It is your brand — your first and eternal impression. It serves your needs just as much as it needs to serve your supporters. And in the nonprofit world, “need” and “willing to serve” are familiar, welcoming words.

As website viewers ourselves, what we look for in a website is what others look for as well. It’s not a “big ask.” In order for your site to provide limitless support for your viewers, what does it need?

It needs to captivate, navigate and inform.

  • It needs ease of comprehension, so your visitor can accomplish tasks swiftly. 
  • It needs quick, intuitive navigation for today’s short attention span.  
  • Each page needs to be well written with great imagery.
  • It needs to tell a story formatted for readers on the fly.
  • It needs mobile optimization and fast loading. Research shows you have only 3 seconds to draw people into your site.
  • It needs to incorporate shared links to articles and information posted on social media. 
  • It needs to be accessible for all visitors including the elderly or those with disabilities.
  • And naturally, it needs to be affordable. Maintaining your site shouldn’t strain your budget.

Most people learn about a nonprofit organization through social media or at local events. Research reveals that approximately 85% access Facebook from their smart phones and ergo discover your site initially using a mobile device. They open your email the first time on the mobile phone to come back later again using their desktop. 

So what does this mean for the modern website of a nonprofit organization?

These 9 tips will send you on your way to a successful online impression.

#1 Keep the number of navigation links between 5 and 7. 

  • More than that can paralyze your visitor trying to figure out where to look next.
  • Submenus are allowed, but just a few.
  • These should be easily accessible using a keyboard as well as a mouse. 

#2 Make the donation button a main navigation item.

Many visitors want to do just that — donate. Make the donation button standout from the navigation in a complementary or cross-complementary color. 

Caption: Example: Conservancy of Southwest Florida:
Blue navigation bar with Donate link in yellow.
Only 7 main navigation links.

The best practices for ecommerce sites apply also to online donations. 

#3 Keep visitors on your site to process an online donation

Maintain the trust pact you have built with your viewer. As opposed to having a third-party accept donations, your donation pages should be part of your website. This inclusion raises 6 times more money than when the donation button or link leads the donor off to another site, even if it’s a subdomain. Leading a donor away from your trusted site breaks the trust pact you’ve already established. Some potential donors hesitate with this method. If online donation is an impulse buy, any hesitation costs you money.

To avoid the extraneous data handling effort, you could use a self-hosted open-source software like CiviCRM with events, membership, donor, contact management and email marketing – all built in.  

Disclosure: Pauli Systems is a CiviCRM partner.

#4 Keep your donation process simple.

Forego large forms. It comes back to the impulse buy. Generally you don’t need the donor’s address to manage a successful payment process. You can make it a secondary contact opportunity to give a donor incentive to provide an address.

We are fans of the GiveWP plugin. You can be up and running with a good donation process in a couple of hours. 

Implement a simple donation process with Form builders. We install this on most sites to enable a Volunteer Application or Event Registration. Coupled with a payment gateway like stripe, you can make an easy to use donation process and add it to any of your organization’s landing pages, or as a Call to Action in your blog posts.  

#5 Include a second ask on your “Thank You” page and email. 

Several plugins can instantiate monthly giving. Once installed, you can ask the donor: “Do you want to make this a monthly gift?” and use an easy checkmark box to make it a subscription. If that’s not yet feasible to implement, make the second ask something like “Tell your friends” and “Share on social media” using functional social sharing buttons. 

#6 Thank you notes make and break your donor relationship.

Do not neglect the last essential piece of the puzzle: the thank you note/email. Just because an expression of thanks comes from a computer, it’s best not to make it sound automated. You can send a very personable email with your confirmation, and you can even link a “Thank you” video from a board member or one of the beneficiaries to let the donor know about the impact the donation makes. Do not use the default email of any plugin you use. Customize it.

#7 Use WP Mail SMTP Pro to process your transactional emails

The WP Mail SMTP plugin allows you to connect your website with your email service provider rather than using the built-in Mailer configuration. It’s an added cost to hook it all up, but it’s worth the money. Your emails arrive at the recipient’s primary inbox instead of in spam, promotion or updates tabs. Make it part of the requirement documentation for your consultants. Most of my clients only realize this after GoLive. 

#8 Find a good versatile form plugin.

Though only 8% of the donations by individuals in the US are made online, it has grown into double-digits in the last four years. It’s a surprising number considering how much time we spend optimizing the process. 

Plugins are available to streamline your administrative processes and reduce duplicate data entry for Event Registration, Volunteer applications, etc. Most form plugins also integrate with third-party tools like your email marketing provider, your Google Drive account and others. You might need to test a few to make sure you are able to handle any new form creation in house.

Here is a list of plugins with great documentation and support: 

Earlier, I mentioned CiviCRM as a comprehensive tool to organize your contacts, applications and forms in one space. Once you know how your organization’s administration is digitize, a CiviCRM implementation will help you streamline your processes and handle all your data in one spot.

You can test CiviCRM as a hosted solution for less than $15 a month, unlimited users, 2000 contacts and 5,000 emails pro month. Check it out here: CiviCRM Spark

#9 Keep your homepage simple.

Many studies have shown that most people come to your website not through your homepage but through a Google search, a social media link, or an email you sent. What they read and see at this entry point is more important than your homepage design. 

I have seen technology teams discuss the homepage over and over again. Discussions should focus on content strategy or donor/volunteer experience. What are the goals of your website? Unless you already have a content-rich site, that topic needs to bubble up to the front burner — content strategy and production are king. 

How much of the fancy frontpage design will make it to the mobile view? Use a small logo on the top and have a few menu items. Everything else is content on a small screen. Fancy frames and shadows get in the way of a clean layout. 

When we design a website, we revise the homepage design many times throughout the project. After the initial mock-ups, we talk a lot about information architecture, content strategy, landing pages with forms, and registration processes. Form follows function.

Conclusion

Keep in mind your main goals for your website: maintain your current supporters and attract new donors. And like anything worthwhile, a company that invests time and energy into an appealing site will reap the rewards it seeks.

Schedule a Site Review with Birgit Pauli-Haack

If you feel your organization’s website needs a site review, schedule a video conference call with screen sharing capabilities with us. We’ll go over the site with you and give at least five immediate action items you can implement with your consultant or freelancer. Interested? Fill out the form or send an email to support@paulisystems.net

Featured Image: “Be Afraid of the Enormity of the Possible” by Alfredo Jarr, The Alfond Inn, Winterpark, Florida. Photo by Birgit Pauli-Haack

How do you know if your site is performing well?

What does that even mean? Lighthouse is a performance testing tool that measure your site for load speed metrics like “First time to Interaction”. Also, part of the test are accessibility how good is your site accessible also for people with disabilities and then also test pages for best practices on search engine optimization.

Stats relevant to your website’s user experience

  • 20% of US population has some form of disability.
  • You have only 3 seconds before a mobile user navigates away from your website.
  • 60% of user browse the web on their mobile phones
  • Google uses Site Speed as ranking factor
  • Google now only indexes mobile content for you website.

We will run a performance test on your site and give you the detailed report. The performance test uses Google Lighthouse tools and measures overall performance, accessibility, best practices, search engine optimization and PWA requirements. The last part is only interesting if you are working on a Progressive Web App or on a mobile first site.

Theme migration huge performance jump

Here is an example test before and after we made some site changes. We migrated a site from theme published in 2006 to a more modern theme and spruced up the home page navigation.

The overall score went from 71 to 76 so the old site wasn’t that bad. However the details show some astonishing outcomes. The overall Accessibility went from 74 to 96 and the SEO score from 69 to 100.

This was quite remarkable. After the first day, the site owner also sent me an email that when he searched his name on Google, he saw for the first time that his own website actually showed up before any of the social networks or news sites, which normally have a much higher authority that any single topic site. Once can not know for sure but with the SEO value going from 69 to 100 percent, I definitely wasn’t surprised by that outcome.

After a week or so, the site owner realized that we didn’t bring over the advertising he placed on his older site. When we added Google AdSense areas to the footer of the site, the performance went way down again. How disappointing….

So it’s not only important to measure the performance of your website and each single page, it also is necessary to monitor the performance over time, or at least test is again, after you make significant changes or start using 3rd party systems.

Image Lazy-Loading coming to WordPress Core

The biggest problem with page speed is image loading with the site. They are most of the time the largest files and many content creators don’t optimize images before upload and employ all kinds of additional plugins.

This month, WordPress core committer, Felix Arntz announced the feature plugin for lazy-loading images out of the box in core. Later this year, it is supposed to be merged into WordPress. We tested it on one of our non-optimized sites just be installing it and measuring before and after.

The changes in speed were quite remarkable! It went form 19 to 35 on the Performance and from 79 to 87 in accessibility.

Get your own test and recommendations

Interested in how your site might perform? Fill out below form and we’ll get back to you with your overall scores as well as the detailed findings and recommendations.

  • Copy/paste or type in your site's address (starts with http)
  • Please enter your first and last name...
  • Please enter your email address

Case Study: ROI of Promoting of a Series of Webinars

We are supporting the publishing and promotion of Gutenberg Times, among other things by paying for the Zoom.us Webinar space where Birgit Pauli-Haack hosts Gutenberg Times Live Q & As.

Zoom makes is very easy to create different links for different marketing channels. In this case study, we concentrated on the channels that were the same for the first six events. We started with the Gutenberg Developer and Design leads on November 30 and ended with Site Building w/ Gutenberg on February 8th, 2019. ( We use Zoom to support the publishing and promotion of Gutenberg Times )

First we looked, how many registrations we received from visitors coming from the four channels: Facebook, Twitter, website and our weekly eNews.

Registrations by Marketing Channel

Figure 1

Live Q &A Registration Zoom

In Figure 1 we can see that the ratio of registrations is fairly well distributed with Facebook and the eNews slightly under 20% and Twitter at 27% and the Website at 34%. So far so good. All four channels contribute considerably to our Webinar audience.

What does this really mean? We know for each person, who registered through our Zoom registration page came through a link they saw on the respective network. We don’t know how often a visitor saw us promoting webinars, we also don’t know if those coming through Facebook, are not also twitter followers or saw it first on the website.

Use Conversion Rate to determine Return on Investment

To determine Return-on-Investment or ROI, we also would need to know the conversion rate of those visitors who came from those channels and then actually converted to a registration. In other words, how many visitors out of 100 visitors actually registered for the webinar?

When we crunch the numbers visitors / registration, we see a total different picture:

Figure 2

Figure 2 shows us the conversion rate for all for networks. The highest conversions rate have subscribers from our eNews. We also see that we need 10 times more visitors coming from Twitter to get the same number of registrations. Visitors from eNews are also the most faithful readers of our content and we are in contact with them on a weekly basis. Clearly aiming for increasing the numbers of subscribers should always be one of the focus for your marketing.

With the followers on Twitter we don’t have a close connection and the engagement is rather fleeting.

Narrowing down the audience seems to help with our conversion rate, too. On Twitter it is more the broadcast approach via the various profiles we control, on Facebook, we post the information about the Live Q & A only in three WordPress Groups. So the narrower the target audience the higher your conversion rate. Feels like stating the obvious, but it’s always good to confirm our instincts once in a while.

The visitors from the website are in between. We needed about 2 times more visitors from the Website to have the same amount of registration of our subscribers list.

Calculating return on investment (ROI)

The ROI could be calculated, by measuring how much time and effort it takes to create content for weekly eNews to augment the promotion of the Live Q & As, compared with the amount of time and effort it takes to update the website to provide the context for the promotional graphics and how much effort it takes to distribute the posts around Twitter and Facebook. Metrics are time spent and or money spent. And then set it into relation to the money gained. We don’t have any money numbers for this project.

In one of my next posts, I’ll share the tools methods and processes to create the Gutenberg Live Q & As from speaker recruiting, to collaboration on the content to social media promotion to the show and the post-production with YouTube space, transcripts and publishing on the Gutenberg Times for the archive.

How to improve ROI for promoting webinars or other online events?

To improve the ROI it might be worth looking into methods to streamline or automate processes and to reduce friction especially for twitter promotion. For Gutenberg Times purposes, we decide to add additional scheduled Tweets to promoted the webinars. This step is the easiest to do, it is free, and might catch followers and friends, who missed the announcement the first or second time.

Another approach could be to schedule the webinar more than two weeks out to give the promotion a little bit more time. Many people, have a 10 day lead time to get on their schedules, so if you are not there early, you might not catch a free time slot.

Another advantage of a longer planning period, is that we could reach out to other WordPress publications and alert them to the upcoming webinars so they can mention it on their eNews and social networks. This coordination takes time, though, but it’s well worth it. The highest numbers of attendees, would show up after the WPTavern added a post about it.

For other websites, it could be to increase interaction on Facebook, and schedule events, make sure you also include the panelists as co-hosts, which might help with distribution there.

This case study only covers, what is called last touch attribution.Neil Patel has lots to say about why only tracking last touch attribution is a bit defeating. Tracking all touch points is quite complex, and for a site like the Gutenberg Times, not feasible. If you are thinking about reaching a much larger audience (over 10,000 monthly visitors) creating a analytics framework that covers all touch-points your visitors, customers, followers have with your company would probably be worth the set-up and maintenance costs.

What online analytics framework is feasible for you?

Connect with Birgit Pauli-Haack and find out what analytics framework would be worth your while for your website and social media efforts.

Schedule Your Site Assessment

One of our experts will connect with you to schedule a video conference session with you.
    We - very infrequently - send out Tips on how to increase relevant traffic for your website.
  • This field is for validation purposes and should be left unchanged.

What does ‘Gutenberg-ready’ mean for WordPress themes?

Today, Heart Internet published my article on how to make themes Gutenberg-ready. It’s the first article published that was not for a site I own or for clients and I am grateful for Heart Internet and their editor Oliver Lindberg for working with me through the process of getting it published.

“The new Blocks include baseline support in all themes, enhancements to opt-in to and the ability to extend and customize.”

Gutenberg Handbook, Theme Support chapter

That’s how the developer handbook for the block editor starts its Theme Support chapter.

In other words, there are a few features the block editor offers, that might not be available yet for your theme and your site, so it’s not pure binary, works or doesn’t work.

There are multiple levels of Gutenberg-readiness.

It goes from enabling Align-wide and Align-full styling for the various blocks (columns, images, cover, gallery), to include styling for each core block to providing custom color and font-size pickers for your customers content creators, so they stay within the brands boundaries.

In this article, I’ll talk about all these different ways your theme can interact with the block editor. We’ll talk about stylesheets, CSS specificity and layout. There are voices, and mine is among them, that building blocks are the domains of plugins. Themes provide the glue between features and front-end, and a site owner should be able to switch out their theme without losing content or composition. Of course, like everything else in life, the edges are blurry and the block editor is still in its infancy.

In my guest blog post at Heart Internet, we will look at

  • Block styles
  • Align-wide and align-full
  • Custom colour palettes
  • Block font sizes

I also share a lot of resources and a list of Gutenberg-ready themes.

Head on over to HeartInternet site and read the full article

Featured Image: Markus Spiske on Unsplash

Your Graphics are Honey to the Bees Or How to Find a Great Graphic for Your Content


Your graphics are honey to the bees, irresistibly drawing your worker bees – er, readers & fans – to your posts from all across the internet. Graphics are King! Don’t you believe me? You say, “It’s content.” Yes, you are right. Content is king, but only if you can get the attention of your target audience, and tempt them to click on a link to actually get to your content. A great graphic for your content makes it more shareable, more enticing to click on it and more successful. 

Read More

Study: WordPress Usage in Enterprise-level Marketing and IT

Researchers of Vanson-Bourne investigated the used of secondary CMS among enterprise organization. The study was commissioned by WPEngine and Manifesto. 300 Enterprise-level IT and marketing decision makers, involved in their organization’s website operation, were interviewed in October 2017.  Respondents were from organizations in the private sector across UK and US. The companies had at least 1,000 employees, the majority of respondents’ organizations have at least 3,000 employees and the average global revenue was 3.2 Billion.

Read More