Posted on

How to Convert Column Number (e.g. 28) to Column Letter (e.g. AB) in Google Sheets

Google Sheets includes built-in functions for converting cell references in A1 notation to row and column numbers and another function for converting column alphabets (like AA) into the column index (26 in this case).

=ADDRESS(23, 28, 4) - Returns the A1 style notation of the cell whose row number is 23 and column number is 28.

=COLUMN(C9) - Returns the column number of a specified cell C9 where column A corresponds to 1 and column AA corresponds to 27.

Column Numbers in A1 Notation

Get A1 Notation with JavaScript

If you are working with the Google Sheets API, you may sometimes needs to calculate the A1 notation style reference of a cell whose row and column numbers are known in the JSON data of the sheet.

For container bound Google Sheets, the getA1Notation() method can return the range address in A1 Notation.

const sheet = SpreadsheetApp.getActiveSheet();
const range = sheet.getRange(1, 2);
Logger.log(range.getA1Notation());

If you are not using the Spreadsheet service, you can also compute the A1 notation reference of a cell using simple JavaScript.

/**
 *
 * @param {number} row - The row number of the cell reference. Row 1 is row number 0.
 * @param {number} column - The column number of the cell reference. A is column number 0.
 * @returns {string} Returns a cell reference as a string using A1 Notation
 *
 * @example
 *
 *   getA1Notation(2, 4) returns "E3"
 *   getA1Notation(2, 4) returns "E3"
 *
 */
const getA1Notation = (row, column) => {
  const a1Notation = [`${row + 1}`];
  const totalAlphabets = "Z".charCodeAt() - "A".charCodeAt() + 1;
  let block = column;
  while (block >= 0) {
    a1Notation.unshift(
      String.fromCharCode((block % totalAlphabets) + "A".charCodeAt())
    );
    block = Math.floor(block / totalAlphabets) - 1;
  }
  return a1Notation.join("");
};

This is equivalent to =ADDRESS() function of Google Sheets.

Get Column Number from A1 Notation

The next function takes the cell reference in A1 notation and returns the column number and row number of any cell in the spreadsheet.

/**
 *
 * @param {string} cell -  The cell address in A1 notation
 * @returns {object} The row number and column number of the cell (0-based)
 *
 * @example
 *
 *   fromA1Notation("A2") returns {row: 1, column: 3}
 *
 */

const fromA1Notation = (cell) => {
  const [, columnName, row] = cell.toUpperCase().match(/([A-Z]+)([0-9]+)/);
  const characters = "Z".charCodeAt() - "A".charCodeAt() + 1;

  let column = 0;
  columnName.split("").forEach((char) => {
    column *= characters;
    column += char.charCodeAt() - "A".charCodeAt() + 1;
  });

  return { row, column };
};

This is equivalent to the =ROW() and =COLUMN() functions available in Google Sheets.

Posted on

WordPress Plugin Authors Should Avoid Confusing Users When Naming Blocks

On May 4, the StudioPress development team made a small but significant user-facing change to its Atomic Blocks plugin (now rebranded to Genesis Blocks). It removed the “AB” branding from its block titles. This minor update changed block titles such as AB Accordion and AB Button to Accordion and Button, respectively. On the surface, this change probably seemed of little consequence to the developers on the project. However, for at least one user, it created a massive workload.

Unless users religiously followed the GitHub code commits, they would have missed this update. Stacked with several other code changes for a seemingly unrelated ticket, the team left a message that read, “Remove unnecessary ‘AB’ from block titles.”

The change made it into version 2.8.2 of the plugin, which launched a day later.

The problem was that there was no message in the change log that noted this. Users had no indication that the blocks from the plugin were being renamed. Typically, this would not be a big deal since the plugin team had merely dropped the “AB” prefix from the otherwise unchanged titles. However, what happens when one of those blocks’ titles matches a core block title?

That was the issue that Marcus Tibesar ran into. The AB Button block suddenly became the Button block. Thinking he was using the core WordPress Button, he made liberal use of it throughout his site. Throw in his decision to drop the plugin after StudioPress rebranded its plugin to Genesis Blocks, it became a bit of a disaster to clean up.

“I have been using the Button block for months now only to discover that I’m actually using the Atomic Blocks button block!” wrote Tibesar in a comment on the Atomic Blocks rebranding post.

Theoretically, he should have needed to update only any lingering blocks from Atomic Blocks that he had knowingly used. But, he was stuck with blocks that he had unknowingly added to his posts and pages through no fault of his own.

This particular scenario was made worse because WordPress 5.4, released on March 31, introduced a new Buttons (plural) block. The old single Button block was removed from the normal inserter. While not all block-naming issues are so convoluted, it still begs the question: how can plugin authors avoid causing these types of user-experience issues?

It is easy to throw blame toward StudioPress — and the team could perhaps use a scolding for not being clear about the change when it happened. However, this brings forth a couple of things the greater WordPress community needs to figure out. The first is whether plugin authors need to use a consistent, prefixed naming scheme for their blocks. The second is what can WordPress do to help mitigate issues.

Prefix All the Things

Screenshot of adding button blocks from multiple plugins to the editor.
Buttons, buttons, and more buttons.

That is the common saying in the WordPress development world, right? Prefixing and namespacing guidelines generally apply to the actual code, which is where conflicts arise. However, there are times when prefixing public-facing text is warranted.

And those times are when plugins utilize a shared space.

The block editor is one such shared space. With more and more block plugins landing in the directory, it is time that plugin authors consider how block-naming schemes affect end-users. The issue is certainly not limited to Atomic/Genesis Blocks. This has been an ongoing trend with several block library plugins. Some do better than others, but it’s a toss-up each time a user installs such a plugin.

The easiest route is for plugin authors to simply prefix all custom blocks with their company branding (e.g., AB Button). On the other hand, not every block shares a title with one of the core blocks. For example, a block titled Product Carousel may not need to distinguish itself further from other blocks. It is unlikely that end-users are running multiple eCommerce plugins with blocks that share the same title.

“All, repeat all, should have a prefix,” said Tibesar. “The prefixes eliminate any confusion as to whether we users are selecting a core block or a third-party block. The most popular plugins appear at the top of the list, and it’s confusing from whence they came when prefixes are absent.”

At the very least, third-party blocks should have a prefix if their titles match one of the core blocks. End-users should not see two different Cover blocks in the block inserter, for example. Instead, they should see the core Cover and a second, uniquely-titled block. Prefixing is an easy way to do that. But, I could live with anything that does not cause user confusion.

Locating Instances of Block Usage

Screenshot of the Manage Blocks screen prototype for WordPress.
Manage Blocks screen.

In late 2019, the Gutenberg team released the first prototype of a potential block management area for the WordPress admin. The Manage Blocks screen from the prototype showcased an area that would allow users to manage every block on their site. One of the more important bits of information on this screen was an “Instances” count, which displayed the number of times a block was in use. It further linked to a screen with every post that had a particular block.

One of the reasons this feature is important is that it would allow end-users to locate posts that they may want to clean up. Using the Atomic/Genesis Button block as an example, Tibesar could track down all those old uses and make any changes he wanted.

He said he would absolutely welcome this feature in WordPress. “New users are tempted to load up on zillions of block plugins all to be forgotten later. Also, maintainers would use this tool when cleaning up broken sites. Just being able to see an overview of what blocks were used where, will allow publishers to dial back the number of block plugins installed on their sites, especially when new plugins and technologies emerge.”

Because this feature is not in core yet, he had to turn to the Find My Blocks plugin, which helped him identify 22 posts and pages where he had unknowingly used the Button block from Atomic/Genesis Blocks. In the long term, this is something that needs to be handled directly in WordPress. It is unlikely to be the last time a user needs to clean house and get rid of old blocks.

Posted on

The 4 Fundamental Steps of Conversion Optimization

Once upon a time, I was sitting in my office looking over data for one our new clients and reviewing the conversion project roadmap. The phone rang and on the other end was the VP of marketing for a multi-billion-dollar company. It is very unusual to get an unannounced call from someone at his level, but he had an urgent problem to solve. A good number of his website visitors were not converting.

His problem did not surprise me. We deal with conversion rates optimization every day.

He invited me to meet with his team to discuss the problem further. The account would be a huge win for Invesp, so we agreed on a time that worked for both us. When the day came, our team went to the company’s location.

We started the discussion, and things did NOT go as I expected. The VP, who led the meeting, said, “we have a conversion problem.”

“First-time visitors to our website convert at a rate of 48%. Repeat visitors convert at 80%!”

I was puzzled.

Not sure what exactly puzzled me. Was it the high conversion numbers or was it the fact that the VP was not happy with them. He wanted more.

I thought he had his conversion numbers wrong. But nope. We looked at his analytics, and he was correct. The numbers were simply amazing by all standards. The VP, however, had a different mindset. The company runs thousands of stores around the US. When someone picks up the phone and calls them, they convert callers at a 90% rate. He was expecting the same conversion rate for his online store.

Let’s face it. A typical e-commerce store converts at an average of 3%. Few websites are able to get to anywhere from 10 to 18%. These are considered the stars of the world of conversion rates.

The sad truth about a website with 15% conversion rate is that 85% of the visitors simply leave without converting. Money left on the table, cash the store will not be able to capture. Whatever way you think about it, we can agree that there is a huge opportunity, but it is also a very difficult one to conquer.

The Problem with Conversion Optimization

Most companies jump into conversion optimization with a lot of excitement. As you talk to teams conducting conversion optimization, you notice a common thread. They take different pages of the website and run tests on them. Some tests produce results; others do not. After a while, the teams run out of ideas. The managers run out of excitement.

The approach of randomly running tests on different pages sees conversion rate optimization in a linear fashion. The real problem is that no one shops online in a linear fashion. We do not follow a linear path when we navigate from one area of the website to the next. Humans most of the time are random, or, at least, they appear random.

What does that mean?

The right approach to increase conversion rates needs to be systematical, because it deals with irrational and random human behavior.

So, how do you do this?

The Four Steps to Breaking to Double Digits Conversion Rates

After ten years of doing conversion optimization at Invesp, I can claim that we have a process that works for many online businesses. The truth is that it continues to be a work in progress.

These are the four steps you should follow to achieve your desired conversion rate:

Create Personas for Your Website

I could never stop talking about personas and the impact they have on your website. While most companies talk about their target market, personas help you translate your generalized and somewhat abstract target market data into a personalized experience that impacts your website design, copy and layout.

Let’s take the example of a consulting company that targets “e-commerce companies with a revenue of 10 million dollars or more.” There are two problems with this statement:

  • The statement is too general about the target market (no verticals and no geography, for example)
  • I am not sure how to translate this statement into actionable items on my website or marketing activity

You should first think about the actual person who would hire the services of this consulting company. Most likely, the sales take place to:

  • A business owner for a company with annual revenue from 10 to 20 million dollars.
  • A marketing director for a company with annual revenue from 20 to 50 million dollars.
  • A VP of marketing for a company with annual revenue over 50 million dollars.

Now, translate each of these three different cases into a persona.

So, instead of talking about a business owner for a company that is generating annual revenue from 10 to 20 million dollars, we will talk about:

John Riley, 43 years old, completed his B.A. in physics from the University of Michigan-Ann Arbor. He is a happy father of three. He started the company in 2007 and financed it from his own pocket. His company generated 13.5 million dollars of revenue in 2014 and expects to see a modest 7% increase in sales in 2015. John is highly competitive, but he also cares about his customers and thinks of them as an extended family. He would like to find a way to increase this year’s revenue by 18%, but he is not sure how to do so. He is conservative when it comes to using new marketing techniques. In general, John does not trust consultants and thinks of them as overpaid.

This is an oversimplification of the persona creation process and its final product. But you get the picture. If you are the consulting company that targets John, then what type of website design, copy and visitor flow would you use to persuade him to do business with you?

What data points do you use to create personas for your website? I would start with this:

  • Market research
  • Demographical studies
  • Usability studies
  • Zip code analysis
  • Existing customer surveys
  • Competitive landscape
  • AB and Multivariate testing data

A website or a business should typically target four to seven personas.

Add Traffic Sources

So, you have the personas. These personas should impact your design, copy and visitor flow.

But how?

Let’s start by looking at analytics data. Look for a period of six months to one year and see the top traffic sources/mediums. If your website has been online for a while, then you will probably have hundreds of different sources. Start with your top 10 traffic sources/medium and create a matrix for each of the personas/traffic source/landing pages:

Now, your job is to evaluate each top landing page for each traffic source through the eyes of your website personas. For each page, you will answer eight questions.

The persona questions: Eight questions to ask

  • What type of information would persona “x” need to see to click on to the next page on the website?
  • What would be the top concerns persona “x” have looking at the page?
  • What kind of copy does persona “x” need to see?
  • What type of trigger words are important to include on the page for persona “x”?
  • What words should I avoid for persona “x”?
  • What kind of headline should I use to persuade persona “x” to stay on my website?
  • What kind of images should I use to capture persona “x” attention?
  • What elements on the page could distract persona “x”?

As you answer these questions for each of the personas, you will end up with a large set of answers and actions. The challenge and the art will be to combine all these and make the same landing page work for all different personas. This is not a small task, but this is where the fun begins.

Consider the Buying Stages 

You thought the previous work was complex? Well, you haven’t seen anything just yet!

Not every visitor who lands on your website is ready to buy. Visitors come to your website in different buying stages, and only 15-20% are in the action stage. The sequential buying stages of a visitor are:

  • Awareness stage (top of the sales funnel)
  • Research stage
  • Evaluating alternatives
  • Action stage
  • Post action

A typical buying funnel looks like this:

How does that translate into actionable items on your website?

In the previous exercise, we created a list of changes on different screens or sections of your website based on the different personas. Now, we are going to think about each persona landing on the website in one of the first four buying stages.

Instead of thinking of how to adjust a particular screen for John Riley, now you think of a new scenario:
Persona “x” is in the “evaluating alternatives” stage of the buying funnel. He lands on a particular landing page. What do I need to adjust in the website design and copy to persuade persona “x” to convert?

Our previous table looks like this now:

Next, answer all eight persona-questions again, based on the different buying stages.

Test your different scenarios

This goes without saying; you should NEVER introduce changes to your website without actually testing them. You can find plenty of blogs and books out there on how to conduct testing correctly if you are interested in learning more about AB testing and multivariate testing.

For a start, keep the five No’s of AB testing in mind:

1. No to “Large and complex tests”

Your goal is NOT to conduct large AB or multivariate tests. Your goal is to discover what elements on the page cause visitors to act a specific way. Break complex tests into smaller ones. The more you can isolate the changes to one or two elements, the easier it will be to understand the impact of different design and copy elements on visitors’ actions.

2. No to “Tests without a hypothesis”

I can never say it enough. A test without a good hypothesis is a gambling exercise. A hypothesis is a predictive statement about a problem or set of problems on your page and the impact of solving these problems on visitor behavior.

3. No to “Polluted data”

Do not run tests for less than seven days or longer than four weeks. In both scenarios, you are leaving yourself open to the chance of inconsistent and polluted data. When you run a test for less than seven days, website data inconsistencies you are not aware of may affect your results. So, give the test results a chance to stabilize. If you run a test for more than four weeks, you are allowing external factors to have a larger impact on your results.

4. No to “Quick fixes”

Human psychology is complex. Conversion optimization is about understanding visitor behavior and adjusting website design, copy and process to persuade these visitors to convert. Conversion optimization is not a light switch you turn on and off. It is a long-term commitment. Some tests will produce results and some will not. Increases in conversion rates are great but what you are looking for is a window to visitor behavior.

5. No to “Tests without marketing insights”

Call it whatever you like: forensic analysis, posttest analysis, test results assessment. You should learn actionable marketing insights from the test to deploy across channels and verticals. The real power of any testing program lays beyond the results.

If you follow the steps outlined in this blog, you will have a lot to do.

So, happy testing!

About the author: This guide was written by Khalid Saleh. He is the CEO of Invesp, a conversion optimization software and services firm with clients in 11 different countries.

Categories: 

Powered by WPeMatico