Innovation, Life Hacks

Make a Boolean Expression From A List of Rows in Google Sheets

google-list

We all use search forms every day. There’s usually an advanced search form of some sort that allows you to enter a boolean expression. You know Boolean expressions as “This OR That” – an inclusive search that increases results – or “This AND That” – an exclusive search that limits results.

When you need to filter your results, a powerful method is to make a list of companies, individuals, skills – whatever you are using to get more precise answers – and create a Boolean expression to get a better initial set of results from a search engine. Yet when someone gives you a list of 10, 20, 50, or 100 items the idea of making a delimited list of “Item1” OR “Item2” OR “Item3” is more difficult than you’d like to be.

Never fear: Google Sheets to the rescue! This is a perfect example of a task for a Lazy Programmer™ to solve, as it’s much easier to have a spreadsheet count values and rearrange text for you than it is for you to do the work by hand. Let’s take a look.

As an example, I created a utility spreadsheet that takes a range of values, counts those values, and outputs a result string that delivers a boolean expression to paste into your favorite advanced search page. Whether you’d like to create a list of criteria to find your next job, search Twitter more effectively, or simply create a customized search for Google, this expression builder can help.

This spreadsheet has two formulas that do the work for you. The first one joins a range of values into a concatenated string (turns list of Orange and Apple into ‘Orange’ OR ‘Apple’ OR ”):

join

There are five functions in this expression:

  1. JOIN combines an expression of values into a string connecting each item with the string ‘ OR ‘;
  2. INDIRECT tells the spreadsheet to wait until the range expression in the formula from A2 to the last unblank value in column A is completed and we know the number of the last row to select;
  3. TEXT converts a complex expression into a value – in this case a calculation of the number of rows in the column of this spreadsheet minus the number of blank rows in the column of this spreadsheet – so that INDIRECT will take result of this expression and substitute “5”, the last row above that has a value. This results in a range from A2:A5 to feed back to JOIN;
  4. ROWS counts the number of rows in a range – the standard number in a Google Spreadsheet is 1000 – and returns an integer to feed back to the expression inside of TEXT;
  5. COUNTBLANK returns the number of blank rows in a range, so that our calculation of rows minus the number of blank rows leaves us with the number of rows containing values (4) or the total number of rows including the header (5).

Now that we have our formula, it creates the string:

'Orange' OR 'Apple' OR 'Banana' OR 'Grapefruit' OR ''

From the rows of Orange, Apple, Banana, Grapefruit above. And there is one problem here: that we need to remove the trailing “OR ”” that resulted from the expression.

Our second formula, REGEXREPLACE, removes the last “OR ”” from the string. RegEx is sort of like Black Magic, so you will probably need to use a helper site like Rubular. This string looks for a specific string right before the end of the line (represented by $)

OR ''

and replaces it with nothing.

regex

This leaves you with the strong you want to paste into a search form:

'Orange' OR 'Apple' OR 'Banana' OR 'Grapefruit'

You’re all set! Now, use this utility with any number of rows in your list of items to create a Boolean expression made for you automagically.

Advertisements
Productivity

What will you let drop today?

austin-chan-275638Photo by Austin Chan on Unsplash

When you work in a startup, you are guaranteed to make a decision that will disappoint someone today. Every day is a series of stack-ranked decisions. You decide (in near real time) whether Item A is more important than Item B, and make the best decision you can at the time. Many of those decisions are right. Some of them turn out to be wrong. It’s really hard to know before you make the decision which ones are going to be the wrong ones.

If you start with the premise that you will need to let items drop (there are too many tasks to do, not enough time, inability to delegate, not enough resources), it’s easier to focus on the inverse problem. Which one task do you need to get done today to move things forward the most? You might move two or three or ten things in a day, but what’s the one task when you look back will be the one you say “yes, I needed to do that or bad things would happen?”

Great – easy advice, you say – now how do you do this in practice? There are many ways to sort your list in the order that will give you the top stack rank. You might start with effort. What are the highest effort tasks? Give everything a 1 (easy), 2 (more than easy) and 3 (sounds big). And now think about the value of those things: 1 (small), 2 (bigger), and 3 (definitely high). If you’re spending time on easy small things they are not going to be the ones that create value. In the effort scenario, you need to be working on the smallest big thing that creates value.

But you need to allocate Time as well. Tasks that need to be done in a week are probably not more important than those that need to get done in two days, or tomorrow. In a perfect world you would have addressed the “tomorrow” tasks days ago but … sometimes life doesn’t work that way. You need to be aware enough of the deadlines for items (especially those that are contingent on the work of other people). Time (especially when there are near term tasks that are bigger and more than easy) can disappear quickly. One way to handle this is to under-schedule your expected tasks so that you have reserved capacity for just-in-time triage.

And there is importance. Is this a task for a customer? Is it a task that moves a critical product feature forward? Is it a commitment that was made and is potentially late if you don’t take action? Perhaps the most difficult choice to make is managing the conflict between two tasks of competing importance. The solution? Pick your best. Make Your Choice. Keep moving. The speed of making decisions and moving the task forward is almost as important as picking the right one. Until you make a mistake. That’s the time to stop and figure out what you should do the next time you make a decision.

There is no right answer. But there is a mostly good answer most of the time. Know enough about the decisions you need to make to get it right a lot. When you don’t know, ask leaders in the organization which direction they prefer. And keep learning.

Life Hacks, Product Thoughts, Startup

Thoughts on the Bus Problem

osman-rana-222325

(photo by https://unsplash.com/photos/HOtPD7Z_74s)

What’s the most important thing you do at work?

Most of us, when asked “how do you create job security”, default to explaining a way of interacting with others that only we can do. If you have unique skills, of course you would want to create a solution where you can solve the problem. It’s romantic to think that you – the cowboy or cowgirl – can race into the important situation and solve the problem where no one else can, or do it faster than anyone else.

Described differently, “I am the only one who can get it done on time and under budget” also looks a lot like “I am a bottleneck”, or “my company is now vulnerable to the ‘Bus Problem’, where if I get hit by a bus my company will have absolutely no way to do the things I know how to do. These statements now look a bit different.

A Corollary To What You Do Today

But what if creating personal job security looked completely different and had more to do with creating systems everywhere you go that help everyone else in the company raise their game? In this version of the bus problem, maybe the solution is to make bus schedules (so that all buses run on time), and develop contingency plans (like snow routes) for what happens when there is inclement weather or other unexpected behavior like traffic?

The best way to solve the problem of institutional knowledge sharing is to share that knowledge. Duh. But it means more than simply barfing out that information in whatever messaging suite happens to be the flavor of the month. True knowledge sharing means that you can isolate the facts and share the strategy implications of changing course, that you can write a procedure anyone in your company can follow, and that if you are not in the office the process works without you there.

What does this look like in practice?

Let’s say for the moment that you are responsible for updating the team on a new feature in your product. As a consumer of that information inside of the company, each person in each role needs something different. Sales might need to know if the price of that product changes or if specific customers had been waiting for it. Marketing might need to know if there are marketable features that could be shared with a wide audience. Engineers might want to know if there are new things to test and build. And Customer Support needs to know the typical things customers will ask and how to solve their problems.

Compare your original goal of becoming the only one who can solve a critical problem with the goal of sharing information with everyone in the company at the right time to ensure a productive product release. If you don’t create systems that ensure people on your team know what they need to know before you can tell it to them, you will fail. Your participation in the process should be the reinforcement of the knowledge, rather than the only way they know that information.

Start today by writing down an important thing that no one knows into instructions that person can follow, and then take the day off. Train a trusted resource, take the day off, and see how things went. If you get to “One in a Row” on this problem, you’re ready to tackle the next critical business process you own until the whole business can run without you telling them which buses run next on the schedule.

Product Strategy, Product Thoughts, Uncategorized

One of the most difficult product decisions is “not yet”


(Photo courtesy of https://unsplash.com/photos/3IVOgGIBsM0)
I recently had a (great) idea for a product. In my head it made perfect sense – a way to make it easier for people building presentations to get instant help from a trusted freelancer. Surely many people would be excited to try it out. 

The idea went from sketched-out prototype to tech exploration to see if it was possible to execute the thing I was thinking about, and two things happened. First, two very smart people asked whether this is a problem that anyone has,  and whether I had any data to prove the size of the market. Second, I realized I hadn’t done enough work to move from idea to execution.

“Of course,” I said, sharing market validation stats and thinking about channel partners. But then it hit me – I hadn’t yet taken the fundamental step to validating the market need by hand with no tech investment and no real effort spent. Ideas are a dime a dozen until they have some traction.

And it made me consider the optimal way of moving from idea to idea++, or taking the first next step toward validation. “Paper prototyping,” – the effort to simulate the experience of building product without actually building that product – is useful when combined with tests to establish demand. 

In this case, what was needed was a plan to ask people (ideally those I don’t know) to state that 1) they needed help with presentations and 2) that they are willing to pay other people to solve that problem.

One way to test that would be to use an existing site where people request freelance assistance (let’s say Fivvr, Upwork, or similar) and put up an ad for services. The responses to this ad would give one set of signals for demand. Then, the type of work that resulted might help me determine whether the initial hypothesis is worth more thinking. And third, the initial revenue would give clues to the potential profitability of the idea.

Do I know whether I have a great product idea? Not yet. The next step is to validate whether presentations made “good enough” are sufficient for most people, most of the time. If that’s the case, on to the next idea, until there’s a bit more signal that this one is more than a momentary aha!

Marketing Strategy, Product Thoughts, Uncategorized

New things are easier to sell when they seem familiar

marc-fulgar-165425
Marc Fulgar

Imagine you are selling a new drink (perhaps in a new category) and you’re competing against Coca-cola. You’ve invented a nootropic brain drink that helps you stay calm and alert while coding, and it has a pleasant natural fizz to it. It might even be an unusual color like purple. Taste tests from prospective customers have been successful and the effects bear out from your claim – coders love it! Continue reading

Customer Success, Media Mind, Uncategorized

As a customer, when do you really feel a company is listening?

330H
Courtesy of http://www.gratisography.com

“Speak into the phone and let me know why you’re calling today.”

“It sounds like you’re calling about tech support.”

“Most problems can be solved in just one phone call by resetting your modem.”

“Press 1 to reset your modem”

“Please wait 30 seconds for your modem to turn off and on again.”

“If you see all of the lights flashing, please try to visit a site on the Internet.”

“Are you able to browse a web site? Press 1 for Yes, and 2 for No.”

If this exchange sounds familiar to you, you might have recently called Comcast. Continue reading

Career, Generous, On Writing, Startup, Uncategorized

When you get a chance to go back to a great team, jump at the opportunity!

23218164373_a0606175e1_k
courtesy of https://www.flickr.com/photos/hernanpc  

Have you ever been on a great team?

I mean the kind of team that people and alumni talk about years later. I’m talking about a team that produces results, leads the market, and is the kind of team that spawns other great teams. It’s hard to produce these kinds of results once, so it’s all the more remarkable when the same team produces another high-performing team (and highly correlated to success in the new venture)

In my career, I have been on great teams, and also participated in not-so-great teams.

Here are a few things that great teams do that mediocre teams do not do:

Great Teams Focus Their Efforts

In a startup (or really inside any company) there is always too much to do and almost always not enough time and resources to do it. Great teams build a culture where people focus on the next best thing they can do to improve the company, and make it easy for people to work together to gain results. For example, when you cut a lightly used feature and take the time to improve an existing feature, you are lowering the surface area of your product and helping the whole team to feel better about the quality of your software.

Mediocre teams work on many projects at once and never ship. On these teams, someone always claims credit for doing the work instead of giving kudos to another team member to congratulate them on a job well done. Mediocre teams endlessly add features without taking the time to ask customers whether the existing features meet their needs.

Great Teams Identify and Amplify Team Strengths

On a great team, it’s easy to find specialists. They are busy doing what they do best – not struggling at tasks they do the worst – and producing strong results. Some of the specialists have a specialty of getting other people to make decisions, push themselves to do new things, or to reduce the overall quantity of work to produce higher quality work. Great teams form around individuals who have strengths the whole team can use. These teams ask “how can I help?” to each other rather than saying “I’m too busy – can you ask someone else?”

On a mediocre team, it’s hard to determine what anyone does well, because everyone is meeting with each other in the same meetings. There is no time for work during the work day, because no one comes prepared to discuss items at meetings, and people spend the meeting time multitasking and doing the work they could not complete in their previous meetings. Mediocre teams leach away the strength of their individual specialists by creating an environment where no one knows how to make a decision and where no one feels empowered to ask for that decision.

Great Teams Are Resilient

Having a great team does not isolate you from conflict. Great teams are effective at meeting conflict head-on, discussing the problem, finding a solution, and then moving forward either by “disagreeing and committing” or by genuine consensus. These teams are resilient because during times of trouble team members lean on each other’s strengths and find solutions to seemingly intractable problems.

Mediocre teams fall apart or descend into chaos during stressful situations. There are few things more disappointing than thinking you’re on a great team, encountering a stressful situation, and then realizing your team is rather mediocre. Instead of the support you get from a great team, on a mediocre team it ends up being every person for themselves.

Great teams are hard to find.

I recently joined the team at Kustomer because this is a great team solving a hard problem in an important market – CRM for support customers – and I wanted to be part of that effort. So far, working at Kustomer feels similar to the atmosphere I shared with some of the team members when we worked together at Assistly. We work hard, we play hard, and we are building a business centered on our customers. But what makes a team great?

Great teams sometimes form by themselves and sometimes are made. People know a great team when they experience it. Great teams do not last forever, because culture is hard. When you get the band back together, it doesn’t always work. But when it does, it’s amazing.

Kustomer is a great team. We are crushing it. That doesn’t mean we’re always right – it means we are going after a great market with proven technology expertise, deep domain expertise, and a kick-ass attitude.