Formula(s) for Success

The biggest benefit of Streak is the ability to customize anything, whether it’s columns, pipelines, charts or stages. Internally we use Streak for everything; sales, customer support, product development and hiring. A feature I rely heavily upon for my daily work is the formula column. They may look scary, but formula columns will change your life!

My job is to stay up to date with customers and provide relevant and timely advice when using Streak. I reach out to my trial users on the 1st, 3rd, 7th and 15th days of their trial. This would typically be an organizational nightmare requiring me to sift through thousands of boxes in search of the date and contact details I’m looking for. Not ideal.

A second option is to create separate columns for each of these milestone dates, though this would require manually calculating dates every time a new lead appears. This gets tedious and leaves room for human error. As a result, my leads fall through the cracks and my boss is unhappy.

….behold, the formula column!

The formula below simply adds 3 days to dates in a given column:

= if($’Trial Start Date’==’’ || $’Trial Start Date’ == null)
{null}
else
{Streak.addDays($’Trial Start Date’, 3)}

The formula to calculate a 3-day follow up, while accounting for weekends, is:

= if($’Trial Start Date’==’’ || $’Trial Start Date’ == null){null}
else if
($’Trial Start Date’.getDay() >= 3 && $’Trial Start Date’.getDay() <= 5)
{Streak.addDays($’Trial Start Date’, 5)}
else if
($’Trial Start Date’.getDay() == 0 || $’Trial Start Date’.getDay() == 6)
{Streak.addDays($’Trial Start Date’, 4)}
else
{Streak.addDays($’Trial Start Date’, 3)}

If-statements are useful to track two or more outputs. It basically tells the system: if this, then that. In the formula above, Trial Start Date is the name of the column that tracks start dates. If no value (ie. date) exists in a cell, the statement is told to leave it blank. This is what the null commands refer to. Without this, the cell would display an error message.

The rest of the formula tells the system to assign certain values based on the date entered. Days are tracked by a numeric system — 0 is Sunday, 1 is Monday, etc. The .getDay() command tells Streak to retrieve the number associated with the date. If it’s between 3 and 5 (Wednesday — Friday), then the system adds 5 days to the follow up column. As a result, it accounts for the weekend. The second command says that if the value is 0 or 6 (Sunday or Saturday), then add 4 days. Lastly, any other value (Monday or Tuesday) should add 3 days. By accounting for every day of the week, I make sure that my customers receive information during the weekdays rather than the weekend.

Once these are in place, I create individual Saved Views for each milestone day (one for brand new trials, one for 3-day follow ups, etc….) and hide the remaining date columns to reduce the clutter in each View. The last thing I do is set a filter, and equate the formula column of interest (eg. ‘3-Day Follow Up’) to ‘Today’.

Because I don’t have to specify a particular date, ‘Today’ displays relevant results for me everyday. To further explain, every time the date in one of these formula columns is the same as today’s date (eg. Feb 1st), those matching boxes will be displayed in my Saved View. The formula is sophisticated enough to skip the weekend, so trials that start on Thursdays or Fridays are automatically scheduled for their 3-day follow up on the next Tuesday.

This organizational hack, alongside basic snippets, has made my job and life far easier than before.

If you want to start with a simpler formula, try to calculate the number of hours or days between two dates. Create 2 date columns and 1 formula column. Format the date columns to include the timestamp:

In the formula column, enter the following formula to calculate the hour difference between both dates:

= Streak.hourDifference($’Trial Start Date’, $’Follow Up’)

If you want to calculate the difference in days instead, try this:

= Streak.dayDifference($’Trial Start Date’, $’Follow Up’)

Play around with formula columns, filters and Saved Views to see how they can save you heaps of time and effort everyday, too. To get started, check out our formula support in In-App help, read our Formula Columns blog post or reach out to support@streak.com for further assistance.

PS: if you want to copy the formula columns I’ve used above into your own pipelines, make sure there are no extra spaces/indents between the commands!