25 March, 2019

Measure the success of your help knowledge base content

Let me start first by talking a bit about scope and strategy. The discussion on measuring your help knowledge base often gets caught up in the measure of call deflection on your website and the strategy of your knowledge base -- whether the knowledge base is primarily for your agents or customer help website. Regarding call deflection, it’s extremely likely that not all customers on your help website would contact you if they are unsuccessful with self-resolving, which is why how to measure or what to measure using call deflection is frequently debated.

While your agents and customers have different needs from a knowledge base, it must serve both audiences! The fundamental issue is that agents and customers have different needs.
  • In most cases, your agents know exactly the issue and what to do to solve it, they just need to find the right article to share with the customer. These leads them to often try searching based on a solution.
  • Your customers, on the other hand, are visiting your help website because they have a symptom and they don’t know the solution. The customer is looking for an answer based on a symptom.

I’ll say it again, your knowledge base must serve both audiences. Also, throughout this article, I will periodically reference processes that should be a part of your strategy in order for you to measure the success of your help knowledge base content.

I have 1 more related area worth mentioning before getting started, which is the ability to discover and find your help content. No matter how effective your help knowledge base articles are at solving problems and issues, if your customers and agents can’t find what they need, you’ve still failed with your knowledge base initiative.

With those related topics out of the way, I’m going to specifically focus on the knowledge base article measurements in this article, with a concentration on effectiveness. I define effectiveness to be whether the help article achieves its intended purpose or not. You may be asking, “Aren’t all articles intended to solve a problem?” Well, ultimately yes, but no, not necessarily. The intent of an article could be any of the following.
  1. Solve a problem, i.e. a solution. This article typically clear on the symptom and there is one way to resolve. It could also be an article that solves many issues, such as clearing your browser cache. 
  2. Troubleshoot an issue which then leads to the steps to resolve. A good example of a troubleshooting article - the article would have the customer try something and based on the results, the customer would click-thru to an article that contains the solution.
  3. Direct the customer to your support staff because the issue can’t be resolved by the customer.
  4. Others such as training and reference materials.

Measure articles used by agents

Let’s dig right into measuring effectiveness when articles are used by agents. In most cases, I expect an agent would send an article with a solution to the customer’s issue, but with email support, the customer may not have provided enough details so your agent may send a troubleshooting article to get the customer down the right path. This leads to some questions:
How do you measure an article’s effectiveness when an agent sends the customer an article, but the customer is still unable to resolve their issue? 
Was the article too difficult to understand or did the agent send the wrong article?
I’ll get to this in a minute, but 1st consider the processes you’ve defined for your agents when it comes to knowledge base article usage. Your processes will be key to getting the measurements you want. If you are forcing your agents to search for an article on every case, you will very like introduce inaccurate data. If your phone agent knows the answer to an issue off the top of her head, once the call is wrapped up with the customer, the agent may just select the easiest article to associate with her call instead of the correct article. An agent that feels the pressure to close as many cases as possible within a timely fashion with a process that adds little value to them, will lead to shortcuts on cases just so they have met the required criteria.

So this is your first step in measuring your article effectiveness. Make it super-easy for agents to associate and send an article. Therefore, for every article that they’ve already used 20 times today and 100s of times this month, let the agent retrieve the article using a simple ID that they will quickly memorize. Also, make it easy for them to reference highly used/shared articles. (Don’t worry, when something changes, as long as you always update the same article, the agent will always have the most current information.) Only have your agent search for issues they’re unfamiliar with. Trust me, they’ll love you for this. (While you’re at it, make sure you teach them how to search using natural language AND provide them familiar, easy to use filtering).

Once we have the right processes and we know our data is clean, how do we measure? Simply report against the last article referenced (shared) in the support case -- that last article represents the primary customer issue and resolution. What if the customer return for more help after an article was sent? That’s the measure. If your agent sends the customer article X and the customer comes back for additional help, either the wrong article was sent or the article failed. (It really is black and white - the customer problem was solved or it wasn’t.) Now you just need to figure out, was it the article or the agent?

On the follow-up customer interaction (for those first interactions that failed), if no additional article was shared, i.e. your agent only had to provide more details or clarity, then the article failed -- it was ineffective. (Don’t get fooled on this. If your agents are routinely providing more context for articles when they’re sent on the 1st interaction, the article is still failing, but your data won't show that. Instead, your agents should have submitted a correction for the article. [sounds like another process you must have]) If a subsequent article is shared with the customer, then we assume that the original article shared was done in error and you can still report against the last article shared - the correct article for the issue.

Yes, there will always be exceptions, but they should be minor. For example, if you frequently send multiple articles and/or handle multiple issues with a single case, add a checkbox in your system where your agent can specify the primary issue/article for the case.

To bring this together for measuring help article effectiveness based on agent usage, the measure works as follows. Evaluate all articles that were the last article sent/shared on a case by an agent. For those articles associated with a case where there was a subsequent interaction with your customer, the article failed to live up to its intended purpose, i.e. the article needs some work. For all other cases, the article was successful. (For the article that failed, you can use the case verbatim to figure out what needs improving/correcting.)

You will end up with a percentage of success (or failure) for each article. At the end of the discussion on measuring articles on your help website, I’ll talk about different ways to slice this data.
Pro Tip: Instead of separate close codes, you can use article usage data to identify your top call drivers.
There are a few more issues you might have thought about measuring when thinking about measuring articles used by your agents.
  1. Did any of your agents send an article where the article’s intention is to send a customer to support to get the resolution? If so, was it an agent error or something about the article? From my experience, the strategy around creating articles that direct a customer to contact you are ONLY created when there are NO “things to try” for customer self-solving, and therefore there wouldn’t be any information in the article that an agent would need to convey to a customer -- they've already contacted you. So if any of these articles show in your data, you have some additional digging to do, to determine why it’s happening -- is it the article or the agent?
    [This goes hand-in-hand with your contact strategy, which is beyond the scope. Briefly, at no other time should you include contact specific details within an article. Your help website should have easy-to-find information on how to contact you. That “contact us” article should be for a unique exception and very brief: (1) enough info for the customer to confirm the specific issue and (2) instructions to contact you to get assistance in resolving.]
  2. Articles sent by agents that your customer could have found on your help website. Until we look at the help website data, we don’t know whether the customer even tried to self-resolve before contacting you, but when we measure articles based on agent usage, it really doesn’t matter. We just want to see if the article works (was it effective?) or not in solving the customer problem or issue.
  3. Measuring your knowledge base improvement process that your agents use. I hope you are already using the KCS (Knowledge Centered Support) methodology or your own processes and methods to improve your knowledge base. As experts of your products and services, your agents should be identifying article shortcomings for every topic that runs across their desk. In short, you need to measure:
    1. Agents are flagging or reporting articles that need improvement or the need for a new article.
    2. Those submissions are legitimate.|
      or example, a submission for change because a difference in opinion on article style wouldn’t be a legitimate submission.
    3. The articles being corrected are done in a timely fashion.
To summarize, the article effectiveness measure helps you identify how you’re knowledge base articles are doing.  Having the right processes and measures for those process will help you improve your effectiveness score.

Everything I’ve covered so far works for articles where you have agents that are also using those articles to support your customers. What happens for those articles where you have no agent support? For those knowledge base articles that are never used by an agent, you absolutely need a good effectiveness measurement.  (I’m not suggesting that you only measure articles on your help website if you don’t have agents, but rather it’s critical that you have this process for measuring when you don’t have agents.)

Measure articles on your help website

A huge portion of your customer support strategy should be focused on customer self-resolution, which your help website will likely be your largest component of that strategy -- it’s cost-effective and very likely the preferred support model for a large portion of your customers. (This varies by several factors including the product, its complexity, and your customer demographic.) I’ll leave the measure of the entire help website to another article and continue looking specifically at measuring help content effectiveness on your help website.

Before getting into the measurement details, consider for a moment why a customer might come to your help website for assistance (or even call you). What were they doing or trying to accomplish just prior to visiting your help website? I’m willing to bet that a significant majority of your customers didn’t wake up this morning thinking they wanted to come read your help content like they may do each morning reading their Facebook feed or Twitter posts. While a few may be looking for educational purposes, most paid you a visit because something went wrong when they were trying to use your product or service and they were unable to resolve it on their own. Maybe they were trying to plan a trip to visit old friends and your email software quit sending emails. Perhaps they were trying to book a reservation at their favorite restaurant and your app crashes again and again. Or maybe they have a deadline to finish a drawing for one of their customers and they're unable to save the file.

My point is, your customer is using your products and services to live their life or do their job. While they may have loyalty to your company (for now), ideally they would never have any friction or interruption from their intended goal, i.e. ideally they would never have a reason to seek your help. Why is this point so important? Because as soon as their issue or problem is resolved, your customer is going to go back to their regularly scheduled life. They might make a note to reevaluate you as a provider or to look for a discount or refund from you, but their intention is to go back to what they were doing before something happened with your product or service that interrupted their personal or professional life.

With that in mind, let’s start by defining a conversion. When measuring a website, conversion is the key metric. If you’re selling products (think Amazon), a conversion is the successful completion of the purchase step(s) to buy the products in your virtual shopping cart (i.e. your credit card was charged and a fulfillment process was triggered). For other companies, a conversion could be the point when you provide your contact information in exchange for a whitepaper or other product details. (The company likely had one or more marketing initiatives that use the collection of your contact info to share with their sales team as a critical measure of the success of their campaigns.) These conversions are the measure of success for the site’s intended goal. Here’s one more: a content site such a Medium or CNET want you to read article after article, so they can serve you ads, which generates revenue for them. They have multiple conversions -- each time you click on another page on their website, which loads another story for you with ads -- which generates revenue for them. (Those companies who have the ads also have a conversion on the website -- when you click on the ad.)

For your help website, you probably have just 2 conversions:
  1. [Preferred] Your customer resolves their issue through reading 1 or more support articles. Unlike Medium or CNET, the goal is to solve the customer issue with the fewest number of articles. Remember, the customer is on your site because they need to be to solve an issue, not because they want to be on the website.
  2. Your customer successfully submits a ticket for assisted (agent) support. This is similar to the 1st two conversion examples I shared. An exchange of some info and in return you will provide a support service (free or for a fee).
Let me repeat this, your customer only pays a visit your help website because they need assistance to continue the use of your product or service to accomplish a goal or task. And remember, once the issue is resolved, your customer is going to go back to what they were doing. With that in mind, the simple measure of article success is:

All visitors that left your help website (site exit) immediately after reading a help article

All visitors to your help website

When you look at your web analytics data, this is the exit rate on your site from knowledge base help articles. If you had 100 visitors enter (visits or sessions) your help website and 75 of those visitors left your website immediately after reading a help article, you would have a 75% conversion rate or a 75% article effectiveness score.
The first argument I usually hear is “The visitor left the website to try what the article suggested. How do you know it solved their issue?”
Which I ask in return, “Did they come back?”
While there are a number of potential corner-cases as to why a visitor didn’t return, the most likely case is that they actually resolved their issue and then continued with their goal or task, ie. got on with their life. As long as the visitor returns before their session timed out (usually 30- or 60 minutes) then you can conclude that the issue wasn’t resolved. Of course, if they come back before the time-out, they will still eventually leave. For each visitor session on your website evaluate what was the last page viewed, i.e. the exit page, when they finally left your site.
Pro Tip: If visitors go away for a long period of time, but still return under the session timeout limit, you will see long page view times. When evaluating how to correct the article and it seems correct, it's likely the article is just too long or too complex.

Let’s dig a bit deeper on our measure -- I don’t think we chose the best denominator. Have you looked closely at all the visitors to your help website? How many of those visitors actually never even try to self-resolve? Or how many visitors tried searching, but never clicked into an article? While each of these visitor types suggests there may be other opportunities to improve on your website, it’s diluting the real help article effectiveness measurement. Therefore I recommend a change to the formula and not include any visitors that never viewed at least 1 article.

All visitors that left your help website (site exit) immediately after reading a help article

All visitors who viewed 1 or more articles

Now you can use this to evaluate overall success and also slice it by a few things. (Likely you’re going to want to combine these together to gain the most insight.)
  • Articles for a given product. Different products likely have different support volume, This will allow you to evaluate for each individual product, so your most frequently visited products don’t over-shadow the performance for other, less popular products and services. Use the formula above, but limit it just to those help website visitors for a particular product or service.
  • Per article basis. By looking at the exit rate on a per article basis using your web analytics software, it’s easy to find what articles have a low exit rate. Even better, add a weighted factor so those articles that get most views, even if not the worst scoring, get improved first.
  • By article type. Remember I talked about 3 different types at the beginning of this article. This is where you’re going to really find where improvement is needed by also considering what should the visitor be doing AFTER reading the article. In some cases, clicking into another article or onto another page IS the correct behavior.
    • Solver. This is going to be the bulk of your articles. These articles should have been written specifically to resolve issues without requiring the need to read additional articles and therefore the exit rate from this article types should be very high.
    • Troubleshoot and redirect. While some customers may resolve their issue by reading this article type, a big proportion of the traffic should lead to another article. So the exit rate for these articles will likely be small, which is the opposite compared to those “Solver” articles above.
    • Contact support. Like the “troubleshoot and redirect” articles, there really is no expectation for customers to exit from these article types. You want to verify these visitors actually clicked-thru to your contact flow.
    • Others. Remember the other possible article types I previously mentioned? They also should be measured based on their intended purpose. For example, training documentation. Unless you have a single, large training document, you would expect customers to jump through all your training documents. And while the overall roll-up score of exiting from an article should still score well, at an individual article level, those documents may not appear to perform well when using the article exit as the benchmark of good performance. If nothing else, you want to exclude those documents from your overall article performance number.

Based on the discussion of measuring per article type, you should adjust your formula accordingly. Remember, if you're looking at the overall performance of your help website, the more customers who leave upon reading an article, the more effective your help knowledge base content is. But, if you’re looking to measure and evaluate on a per article basis, you need to consider the type of the article (the article intention) when measuring, as some articles are expected to route customers and not have them exit the website from that article.

To this point, I’ve discussed article effectiveness measures and some related processes for your agents. As part of the greater effectiveness analysis, we should briefly discuss some measures of related topics.

Article changes

Are you looking at how often articles are being updated? Do you know why they’re being updated? I previously mentioned you should measure and make sure you’re only getting changes submitted that are appropriate. Let’s assume the needed changes are appropriate, then consider these 2 additional measurements.
  1. Within the first 30- or 45-days of a new release, articles for a product release may require 1 or more updates. This could be as simple due to a patch or minor change after a release, but it could also very much indicate the knowledge base articles were incomplete or inaccurate at the time of a release. This measure is a great way to flag you with potential problems, specifically:
    1. A product was released too early. In this situation, there may be frequent product updates, which therefore require frequent knowledge base article updates. The cost for many frequent changes is often not considered or not fully understood. If you’re also paying for translation, this can be a big financial hit.
    2.  Poor work was done by your article writer. This might be the best method to identify an issue with a writer who hasn’t been effective in authoring new content.

    Remember, while you will likely always have some changes, such as a screenshot from the final released version to an update on a late changing error message, the total number of changes should be few.
  2. Frequent changes to an article during any time period. From my experience, this is usually a case where an argument is happening on the accuracy of an article. (Though it could also be due to a new KCS program where agents are looking for any excuse to update an article.) The point being here is that if you have several changes to an article in a short time period, you likely have a problem that requires further investigation.

New article creation for a release

There are 3 points worth measuring and therefore discussing regarding article creation.
  1. Changes after a release. This is the same measure and argument I made above.
  2. Articles that are never used. Think about the time and effort it takes to create each article. So after you have a new product released or update was made to an existing product, were there articles created that have never been viewed? Why is that?
    1. The article was a subject that is not needed or of interest to your agents and customers. This should be captured, so it doesn’t continue for future new product releases.
    2. Your agents and customers were unable to find the article. This could be due to a number of reasons from the article not getting published to a poorly written title to an issue with SEO and/or search indexing.
    In either case, you have some work to do. While I rather slightly over-invest in help content than forcing customers to have to contact me, there is a limit to what you can afford. Likewise, if you have valuable content but no one is finding it, why did you bother writing it to begin with?
  3. Creation of new articles. It can be difficult to predict all possible issues and therefore all necessary knowledge base content for a new product. If you have to create too much new content after a product launch, there’s likely something wrong with your process to identify necessary content needs. There are definitely some differing opinions on the right approach to help content development as it relates to a launch. Some companies want very minimal and let agents identify what’s needed while others may not even have agents and want to get this as close to perfect as possible at the time of launch. No matter where you fit, you want to watch this to make sure it’s not different than what you expect.

Find what’s missing

I already talked about the expectation that your agents should be identifying both problems with the facts of your content and in identifying gaps in your content, but that's likely not enough. Who’s making sure your customers are making the most use of your help content? Perhaps your content is already extremely well written, but you’re still only achieving a 75% effectiveness score? There are (at least) 2 more areas to look.

Internal article audit
When was the last time you audited articles that are only available to your agents? For every knowledge base article you have that is only viewable by an agent when your customer has that issues, they MUST talk to one of your agents to resolve the issue. Examine how often internal articles are used and make sure you don’t have topics that you would be ok with sharing with your customers, but currently you are not sharing publicly. It’s time to rewrite those so customers can self-resolve.

[Related to this, the higher percentage of issues solved by your agents that require an internal article or access to an internal tool is a great indicator of how well your help website is working. The more customers are able to help themselves, the fewer agents you need to employ.]

Article ratings and feedback
If you aren’t already, start collecting a rating (thumbs up / thumbs down) and feedback on your knowledge base help articles from your customers. Based on my experience, assuming you have statistically relevant data, i.e. enough ratings, the ratings will closely mirror the exit rate for the articles intended to solve issues. But what’s really important is that the feedback will leave clues as to how the content isn’t meeting the needs off your customers. While I’ve also seen a lot of irrelevant or inapplicable feedback, if you’re able to filter through the noise, it’s a great place to identify gaps in your help content.


By now you should have “the big rocks” to measure the success of your help knowledge base content. I realize there is much more to talk about, including where to focus next now that you know how your content is performing. Between your agents and feedback plus measuring the processes I’ve shared, you should actually be able to make quite a bit of positive progress.

So what’s left? Here’s a list of related topics and measures.
  • Measuring the success of the discoverability or findability of your content (and how to fix it). At the time of this writing, I’ve been working on another article that discussed 5 things that are more important in finding your help content than your site’s search engine.
  • Measuring your help website. This is very much inter-related to your content and findability success, but there are a few more things such as navigation, taxonomy, and your contact flow to look at, too.
  • Effectiveness of images and videos. You might be surprised here. Remember, these can impact page load times and force unnecessary page scrolling.
  • Regional and cultural differences. Hint - No matter where your customer is located, no matter what language they speak, they didn’t wake up this morning thinking that wanted to read all your new knowledge base article posts.
  • Analyzing your internal knowledge processes, whether KCS, Scrum, or something else.

There are also very related, relevant topics that I’d like to address at some point.
  • Help website strategy and knowledge base content strategy. These are inter-related but each deserves its own discussion.
  • Knowledge base content maintenance. I actually covered much of these, and the rest I would likely include in a knowledge base content strategy discussion.
  • Chatbot, community, and social support strategy fit within your strategy discussions, too. For example, a chatbot can be very helpful in content discoverability while a community is a great resource to help you identify gaps in your help content.
  • Writing for the web, writing for translations, writing for mobile, and SEO. I would consider all of these as subsets of your knowledge base content strategy including you should have an ongoing effort to manage your SEO performance.

After this long list, I feel like I’ve likely left a few things out. Maybe abuse and safety, for example. Oh, and dealing with trolls and scammers. What else?

Let me know your thoughts and feedback on this and potential future topics.

15 March, 2019

How do you find a good boss, not just a job?

Recently a friend of mine asked me “I always wonder how to find a Boss rather than a job. Any pointers or experiences, kindly share. Let us assume I am on the interview and I don't know the Boss, the interviewer may be someone else.“

I thought that was a great question! I’m sure there are plenty of experts who have greater insight than myself, but this is what I offered.

  • Ask the interviewers about how they like working at the company and with their boss. Also ask, what do they like most and may about their boss.
  • Hopefully, you will eventually talk to the boss and you can ask him/her how they define success and what he/she likes most about their boss. Whatever he/she likes most is likely a strong trait of their own.
  • Remember you're interviewing them, too. Don't be afraid to dig deeper for more insight.
I’m sure there’s much more. A few more things have come to mind since this exchange.

  • Prepare ahead of time and know what kind of a boss you like to work for. Think about the bosses you’ve worked for that you liked the most and least, and why.
  • Dig into the question (above) about defining success. Learn how you will be measured.
  • See if you can learn how the boss and the company views on what matters most to you. For example, what’s their view on investing in the growth of their employee? Will they give you roles to stretch? Will they fund education and conferences?
I think the key is to prepare ahead of time and don’t be afraid to pass on a job where there are red flags regarding the person you would work for.

I had one interview where I quickly realized whether they wanted me or not, it wasn’t right for me. The hiring manager (the boss) already had a very clear view of the exact person he wanted, with the exact skills, and exactly what they would do. It didn’t matter that it was a field where I had excelled in for a very long time, he already knew everything. Personally, I’d like to be brought on because I’m an expert in my field and have an opportunity to add value to an organization. You should be able to work with your boss to establish goals which you will be measured against, and then he/she should assist you as necessary to achieve those goals.

I’m interested in hearing input from others.

19 May, 2017

Use Excel to parse text to hours

I use a tool that displays elapsed time in the format of Xd Xh Xm, for days-hours-and minutes. If it's less than 1 day, then the output is Xh Xm. It's easy for me to scrape the output, but I need it in hours to manipulate it further.

Example outputs and converted results:

  • 2d 0h 18m = 48.3 (in hrs)
  • 14h 9m = 14.15 (in hrs)

I wrote an Excel function to accomplish this task.

Assuming my output is in cell B3, this is the function.

=IF(ISERROR(FIND("d",B3)),LEFT(B3,FIND("h",B3)-1)+(MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1)/60),(LEFT(B3,FIND("d",B3)-1)*24)+MID(B3,FIND(" ",B3,FIND("d",B3))+1,FIND("h",B3)-FIND(" ",B3,FIND("d",B3))-1)+(MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1)/60))

Notice it starts with an IF statement. The IF statement with ISERROR checks to see if the text being parsed begins with 'd' for days or 'h' for hours. Once this is resolved, there are just a couple simple things to do.

  • FIND is used to locate a specific letter in the string.
  • For the first value, use LEFT to parse, and get the value up to the letter 'd' (or 'h' when 'd' isn't present). LEFT needs to know which cell you're checking (B3), and how many characters to grab from the beginning. FIND helped us determine how many characters in until we reached 'd' so we know we need to go when character less.
  • MID is used to get subsequent values. MID requires the cell reference (B3),  where to start, and the number of characters. We want to start after the prior letter, i.e. if we're searching for the minute (m) value, we'd start after hour (h) + 1.  We add an additional 1 to compensate for the space after the prior letter (h).
    The number of characters is easy, it's the location of the designators letter (e.g. 'm') - the start location - 1. Remember the designator letter (m) is one more character than the actual number, hence we have to subtract 1 as part of our formula.
    MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1
  • We can't forget about converting our days and minutes to hours. So when we parse the value for days, we multiple by 24, and for minutes, we divided by 60.
    (MID(B3,FIND(" ",B3,FIND("h",B3))+1,FIND("m",B3)-FIND(" ",B3,FIND("h",B3))-1)/60
  • Remember --
    • You need to add them together.
    • You need 2 versions: 1 for the condition where the day (d) is included and 1 when the day (d) isn't included.
TIP: Whenever I build complex functions like this, I first build smaller pieces and then combine them together.

10 February, 2017

Is your Mac shutting down after it goes to sleep?

This eluded me for some time! Usually it didn't go all the way through the shut down, as there were files that needed saving. Either way, it was a pain getting going after my Mac was asleep.

This is how I solved it.

  1. Go to System Preferences... > Security & Privacy
  2. Select the padlock in the lower-left.
    A user name/password dialog box will open. If you don't have the correct privileges for your Mac, you may be stuck at this point.
  3. Enter your user name and password.
    The padlock icon will change to an open padlock; The Advanced... button will no longer be grayed out/unaccessible. (See the image below.)
  4. Select the Advanced... button.
  5. Uncheck the box proceeding "Log out after _XX_ minutes of inactivity."
  6. Select OK
  7. Close the Security & Privacy preferences dialog box.

That did solve the issue for me.

I've heard from others that had similar issues, and what finally solved it for them was to make sure their hard disks didn't go to sleep (found in the Energy Saver preferences).

25 January, 2017

Simple notification script for Google Sheets

UPDATED: May 19, 2017
I changed the script to be more user friendly and the ability to include HTML in the email body. I also found that Google moved a few things in the menus, so I modified the instructions as appropriate.

Google Sheets has a nice feature to notify yourself if something has changed, but every person who wants to be notified must add a notification for themselves. With a simple script, you can send notifications to people of your choosing.

In this example, I've written a script to send an email when 1 or more rows are added.

Get started.

  1. Go to the sheet
  2. Find a cell in the sheet that wont ever be over-written.
    1. Enter the value of the current total number of rows in the sheet.
    2. Note the cell for use in your script. I used G2.
  3. Go to Tools > Script editor...

Write your script.

Add this script in the script editor. Change as required to meet your needs.

function check4NewLines() {

      // /////////////// //////////////// ////////////////
  // /////////////// Cell to track last row //////////////// //
      // /////////////// //////////////// ////////////////

  var theLastRow = "G2";
  // Remember to put the number of rows, i.e. last row, in your spreadsheet into the cell you've referenced here.

        // /////////////// //////////////// ////////////////
  // /////////// End cell to track last row /////////////// //
      // /////////////// //////////////// ////////////////

  // get the spreadsheet object
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // set the first sheet as active
  // fetch this sheet
  var sheet = spreadsheet.getActiveSheet();
  // fetch sheet name
  var sheetName = spreadsheet.getName();
  // figure out what the last row is
  var lastRow = sheet.getLastRow();
  // cell used to track last row we checked. See the Get started section in this post.
  var lastRowChecked = sheet.getRange(theLastRow).getValue();
  // The number of new lines on the sheet.
  var newLines = lastRow - lastRowChecked;

      // /////////////// //////////////// ////////////////
  // /////////////// Customized variables //////////////// //
      // /////////////// //////////////// ////////////////

  // These variables that can be used in your subject or message:
  // 1. newLines : the number of new rows added to the sheet
  // 2. theSheetURL : the URL to the Google sheet
  // 3. sheetName : the name of the Google sheet

    // Update with appropriate email address(es). Multiple addresses should be comma delimited.
  var theEmailDistro = "my-email-address@foo.com";

    // Set email subject
  var theSubject = "My email subject";

    // The URL specific to your Google Sheet. This variable can be used in the email body.
  var theSheetURL = "https://docs.google.com/spreadsheets/my-super-sheet";

    // The email message. The message must be quoted. If using HTML, be sure to only use single quotes in your HTML.
  var theEmailHtmlBody = "There is " + newLines + " or more new entries in the " + sheetName + " Google sheet."

      // /////////////// //////////////// ////////////////
  // ///////////// End customized variables /////////////// //
      // /////////////// //////////////// ////////////////

  if (newLines > 0) {
    // This is the message that will be emailed.  

    // 'newLines is
      to: theEmailDistro ,
      subject: theSubject ,
      htmlBody: theEmailHtmlBody
    // set new 'last row'

Go to the menu and select File > Save.

Test your script.

Testing is easy.
  1. Make sure your email address is one of the notification email addresses.
  2. Either change the value in the row tracking cell to lower than the number of rows OR add a new row.
  3. Run the script.
    1. Click the 'play' button -- find it left of the 'bug' button.
    2. Click 'Run' from the menu and select the name of your function.
  4. Wait for your email.


  1. Clean up from your test.
    1. Make sure your last row number (in G2 in my example) is correct.
    2. Make sure the notification emails are correct.
    3. Save the script again if you changed anything, such as the email address(es).
  2. Select Edit > Current project triggers from the menu.
  3. Select from the trigger options. For example you can have it check on the hour or once a day.
Need more help? Search Google of course.
Good luck!

29 June, 2016

Front loaded text and scanning the page

We push our writers to always front load their titles and paragraphs not just because we think it's a good idea, but because studies back up the technique.

Jakob Nielsen posted a great article, "First 2 Words: A Signal for the Scanning Eye," where he goes into the details of his research in this area.

Jakob starts with reminding us how our customers have many lists to read, err scan -- it's not just search results.

  • Search result pages
  • List of current and/or archived articles and press releases
  • Product listing
  • Table of contents
  • Question lists on an FAQ page
  • Bulleted and numbered lists, checklists, etc

11 characters is used as the baseline measure the number of characters a user actually reads when looking through a list (link text). The test is to see just the first 11 characters, and see if you can predict what's behind the link -- what will you get when you click.

The best link text has these characteristics.

  • Plain language
  • Specific terminology
  • Follow naming conventions
  • Front-loaded, action-oriented terms

The first 3 have something in common -- no slang or internal jargon.

What's the characteristics of bad link text?

  • Generic words
  • Made-up terms
  • Having the information-carrying text at the end

Jakob concludes with a reminder that our customers do have the option to read past the first 11 characters. What we need to remember is to front-load our titles and links, so the customer, when necessary will read the rest of it, i.e. it needs to grab their attention to read enough to validate it's the correct link (which would be followed by a click). "Nanocontent (first bit of a link) just needs to be good enough that users will sniff the most promising links in full." How?

  • Provide enough additional context, so the user knows what to expect (and make sure the clicked-to page actually delivers).
  • Clearly differentiate links -- don't make me choose between 2 very similar links.
  • Don't mislead or over-promise.

It's all so simple. Right?

28 June, 2016

Improve your writing for the web with the Hemingway Editor

As we know, readers on the web don't really read, they scan and skim articles, and won't ready anything that's long form. With the Hemingway Editor, you can improve your writing by making it more "bold and clear" -- make your writing standout so your audience actually reads it.

The Hemingway Editor is going to give you feedback and input on the following:

  • Sentences that are too hard to read
  • Simpler alternatives to words and phrases
  • Unnecessary adverbs
  • Use of passive voice
  • Readability score

The Hemingway Editor also has other helpful functions:
  • Basic formatting
  • Import from Word
  • Export as HTML
  • Character count
It's free to use online, ore a mere $10 for the app version.

27 June, 2016

Text (SMS) from your computer using MightyText

It's 2016 -- if you're not texting, you've fallen behind. I text my family, I get appointment reminders via text, and text is being used for 2-factor authentication.

At my company there are several different communications apps, from Yahoo Messenger, to Slack, to HipChat. They each work well, but only texting is guaranteed to reach the person I want to talk to.

With that being said, texting can be difficult from my phone if I have multiple conversations going on, or I have a lot to share. Fortunately, I've found a great solution. Whenever I'm at a computer, PC and Mac (I have both), I can use MightyText instead.

MightyText works with Android phones and Google Chrome browser. Not only can I type faster using MightText, I get notices from my phone on my computer screen. I no longer need to take my phone out of my pocket to see alerts or respond to text messages.

It also as the added benefit that I can easily attach any image that I can access from my computer. There's also an integration with Gmail, if you want to work within the Gmail interface (over having its own tab).

MightyText is on my short-list of must have apps.

Favorite Podcasts

It's been a year since I've posted, and only once in 2015... how time flies! I was sharing with a friend my favorite podcasts, and thought I could share here.

  • Daily Tech News Show (DTNS) -- I've been following the host, Tom Merritt for 10 years. He's excellent. If you only have time for 1 podcast, this is the one.
  • Serial -- Spin-off from This American Life (below). There have been 2 seasons. Each is a story. I would highly recommend listening to both seasons. 
  • This American Life -- Big variety; some better than others. The host Ira Glass is excellent. 
  • TWiT -- This is a network of Podcasts. The quality varies. The founder, Leo Leporte should be recognized as the person who pushed podcasts to be more mainstream. I've been to their studios in Petaluma, CA. Try these:
    • This Week in Tech. This was the original show that started the network.
    • Tech News Today. Started by Tom Merritt (DTNS).
    • The Tech Guy. Syndication of Leo's radio show.
    • Security Now. Deep technical -- if you like this stuff, the host Steve Gibson is the best.
    • Triangulation. Interviews with current experts in the tech field.
    • there are several more, too.
  • The Vertical Podcast with Woj. If you like basketball, Woj is highly respected NBA writer. Not the best radio voice, but well worth the time.
    [** I currently work for Yahoo, where Adrian Wojnarowski is also employed, though our jobs are unrelated.]

22 May, 2015

When you need two VLOOKUPS together

VLOOKUP is a quick and easy way to return a value from a bunch of rows, by matching a single value. But once you need to match two values, all of sudden things become a bit more difficult.

One solution is to concatenate the two cells in the source and lookup tables. Another is to leverage INDEX and MATCH.
When entering this formual, because it's an array formula, you must enter it by using CTRL-SHFT-Enter, instead of just Enter.

Here's how this works:
  • The MATCH syntax is
    MATCH(lookup_value, lookup_array, [match_type])
    where match type is 0 = exact, 1 is less than, and -1 is greater than.When the match occurs, it will return the relative position of the match within the range.
  • In our MATCH formula, it's looking for an exact match to 1 (lookup_value = 1).
  • If the value of A2 is found in column C, a value of 1 is returned, otherwise a 0 is returned.
  • Likewise for the value of B2 in column D.
  • When multiplied together, if a matching value in columns C and D on the same row match the values from A2 and B2, then a 1 is returned, else we get a 0 (lookup_array).
  • The INDEX syntax is
    INDEX(array, row_num, [column_num])
  •  When MATCH returns a value of the matching row, we get the value in column E for the corresponding row.
With this formula, if you needed to add a 3rd or even more cell matches, you could just extend the multiplication technique.