Life Hacks, Productivity

Spreadsheets are not scary

xkcd-spreadsheet
https://xkcd.com/1906/

What was the last document you created? I’m guessing it was either an email, a Word document, or a Google Doc. Perhaps it was a Slack message but since when you use Slack you create several hundred messages a month (or day), so let’s not count that. Why wasn’t it a spreadsheet (Excel or Google Sheets)?

The most likely reason you didn’t create a spreadsheet is that you wanted the freedom of an open page (hint: View > gridlines turns them off in Google Sheets). Maybe you wanted to insert pictures (hint: use the Image function or insert > image). Or maybe you just like the formatting that seems to happen almost automatically in Cloud-based apps these days. But really, why didn’t you start with a spreadsheet?

Spreadsheets are structured. True, they end up looking that way when they are done. Why not use a tab in your spreadsheet to test an idea (growth in a metric over time, adjusted by factors that change) and produce a sensitivity table to help illustrate a business challenge you are facing. When you are thinking about taking a metric from x to y by when (improve yield by 40% in 30 days) it’s important to show the impact of meeting or missing your expectations.

A suggestion: start with a written statement of what you are trying to solve, e.g. “produce a month over month forecast for the sales team based on a certain number of sales per month and an average sale of $x.” Using the problem statement will frame how you set up your data – you need to have available data series for the problem that match your problem – and start you on the way to presenting the “dashboard” version of your solution. Keep it simple to start by labeling cells that affect others as assumptions, like “sales increase per month.”

Spreadsheets are structured like modular pieces to assemble into a larger whole. Start with the smallest item in your model or argument and test it, then use either a Named Range or a consistently placed cell (e.g. all of your calculations are in a single tab in the same column) to organize where you will find the results. Spreadsheets are more flexible than you think.

Those formulas are hard to copy from one tab to another. Anyone who has ever worked on a spreadsheet has had the experience of copying or changing the data and seeing the dreaded #N/A or #VALUE or #REF show up in place of the data or calculation that they wanted. (And if you have no idea what I am talking about, open a spreadsheet tab in sheets.google.com, enter a formula e.g.  =A1+A2 and then break the formula by putting in nonsense characters).

To test your ability to copy data from one tab to another, create a new tab, remove the gridlines for that tab, and title it “Dashboard”. If you can pull the data from the other tab (hint: just use a simple reference, e.g. “=YourOtherSheet!A2” to pull data from a tab called “YourOtherSheet” in cell A2) you can apply things like formatting and font choice to make your dashboard look like a document, not just a spreadsheet.

This method of data construction and display just plain feels weird. Building data-driven dashboards is a method to insert data conversations in your documents. The data needs to first exist in the format (or in a format that’s convertible to the format you need) and then you need to think about how to combine it. And you need to think of the visual display at the same time to make this a useful technique.

Spreadsheets aren’t scary if you think of the presentation layer as a sketch. You might even draw what you want to see on a Post-It note or find a dashboard that you like on the web and take a screen shot as a starting point. Then, consider what kind of data goes into the boxes and graphs to understand what you need to store in the data layer. Still confused, or want to learn more? Ben Collins is my go-to resource to explain Google Sheets to anyone. The best way to learn spreadsheets, however, is to start one.

 

 

Advertisements
Uncategorized

On blocks and facing the empty page

I wrote and deleted this post several times before continuing to write. It’s not easy to think of yourself as a writer and not have the words to say what’s in your head. It is easy to delete the draft, go back to what you were doing, and go another day without writing.

The empty page is a metaphor for a lot of things. When the page is empty, you have a hard time beating yourself up for what you wrote. When the page is empty, there are no commitments for what you will do next. And when the page is empty, you haven’t taken a stand or offended anyone.

That’s exactly the right time to start writing. I guarantee it won’t be exactly the thing that’s in your head. But once you get going the writing will start to take shape and you’ll realize what was rattling around in your head in the first place.

For me, writing blocks almost always indicate that I am not facing something in my life or that I haven’t yet worked through a challenge I am thinking about. And writing about it almost always helps me face the challenge. Maybe not always head-on, but at least better than ignoring it.

Today I am thinking of the challenge of being a parent of a teenager. Yup, it’s not always easy. Or maybe it’s almost never easy. Maybe being a teenager is a bit like looking at the blank page. When you’ve tried few things in your life you don’t know which ones are going to be the ones that make people happy and which ones you might regret.

Either way you have to start by starting. In this case, it’s probably not the best writing I have ever done. Or will do. However, it’s now out of my head and forces me to commit to doing it again the next time I feel blocked.

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.

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