Blog by Max Al Farakh

How to generate a full-text query for SQL Server

Mon, 28 Aug 2017 10:25:42 GMT

Information on the full-text search on the Internet is pretty scarce. Unless you want to read 600 pages long book you are going to have a hard time. We've been toying around with FTS in Jitbit Helpdesk for almost five years now. I wanted to share the code we use.

This is a result of many iterations over the years. The code we've settled with is pretty basic and only covers two use cases, but I believe it's going to be enough for 95% of products out there.

To be clear, I'm not going to be talking about the entire query - you can find enough code snippets on the web. But for some reason, no one talks about how you generate the search query itself. I'm talking about this part CONTAINS(Body, @ftQuery). Apparently, you can't just throw whatever users enter into @ftQuery.

Here is our code:

I'll explain what happens here:

If the entire query is enclosed in double quotes we don't do anything. The user is looking for an exact match.
Split the query by spaces except when there are parts enclosed in double quotes. For example, one two "three four" becomes ["one", "two", "three four"]
Go through each array element and remove non-alphabetic symbols with this simple regexp [^\w]
Now we actually need to remove stop words from the array. Using our method words like "the", "and", "or", etc. will not be considered as stop-words by SQL. Look at static FullTextUtils() constructor to see how to get a list of all stop-words.
At last, you need to join all the words you have and put NEAR in between. The above example would look like one NEAR two NEAR "three four"

That's pretty much it. As you can see it is very basic and I'm by no means an expert in Full-text search. But that code works for us -- it's fast and produces good results.

Let me know how you handle the full-text query generation in the comments.

Thinking about switching from Mac to PC? Here is my story

Fri, 28 Oct 2016 19:56:29 GMT

Many Mac users are thinking about switching to PC after the yesterday’s keynote. I made the switch a couple of months ago, and I want to tell you my thoughts.

Let me preface this by saying that I am probably one of the biggest Apple fanboys you’ve ever met. I own a bazillion Apple devices: two MBPs, a few iPhones, two iPads, an Apple Watch, three Apple TVs, a Thunderbolt display, etc. I had to live through tons of jokes people made at parties back in the days when iOS didn’t have some very basic features like copy and paste. When I visit a new city, local Apple Stores are at the top of my list of the things to see. Hell, I used to co-host a weekly podcast about Apple.

I’m telling you all this so that you know that you can trust me when I say this: you can switch from Mac to PC. I know that a lot of people are thinking about switching, especially after the yesterday’s keynote. I switched a couple of months ago, and I am here to address any doubts you may have.

My last Macbook Pro was a mid-2012 model with Retina display. To be honest, I never really liked it, but it was alright. I should note that I am a developer and performance is very important for me: I need to run virtual machines and compile huge projects. I thought that it is an expected use case and I should have no issues with a $2.5k laptop. But it was a struggle for some reason. The performance just wasn’t there.

So, I patiently waited for an MBP update. At first, the update was rumored for a March event. Then everyone waited for WWDC. After that, I started looking into buying a PC. In August I finally caved and got one and I never looked back ever since.

I have to admit, that I was a bit worried about yesterday’s event. “What if I’ve made a wrong decision and Apple is going to release something revolutionary? Maybe I should’ve waited a couple of months.” Well, you know what happened yesterday: Apple released a laptop, that you can’t even use to charge your iPhone. But this is not a rant.

I know that you have doubts - I had a lot of doubts too. Below I listed the things that I was scared of and how they turned out to be false.

Microsoft is more Apple than Apple

Apple has always been a synonym of innovation to me. They always try to push the industry forward. But lately? Not so much. I do not consider removing old ports to be innovative. To be fair, I think their industrial design and engineering teams are still brilliant. The hardware keeps getting thinner and lighter (that what we want in a laptop, right?). iPhones increase performance with every new model. All the things they do with cameras are ridiculously good.

But look at the global picture. iOS continues to borrow features from Android and third-party apps. Apple Watch has an identity crisis. And macOS is living of off iOS’ leftovers. And now we have the new touch bar. It looks nice, but if you ask me, I choose the Esc and functional keys over it any day of the week. It feels like the only thing they care about is inventing new ways to type emojis faster.

Look at Microsoft on the other hand. Have you seen Surface Studio? Talk about innovation. Surface Studio looks like the best Apple product in years except it wasn’t made by Apple. Everything MS does right now looks cool, and I’m genuinely excited about it. Oh, and did you know that Microsoft is the top open-source contributor on Github?

Ever since I made the switch to PC, I just feel like stuff is constantly happening around me. I get new Windows beta builds every week with a lot of new features. All the apps I use constantly release new versions. New products are being announced. I just feel like a part of a big community again.

macOS, on the other hand, is a ghost town. Go ahead and open Mac App Store right now and you’ll see what I mean. It is very clear that Apple doesn’t care about Macs anymore. So if you have a fear of missing out by switching to PC, just know that you are missing out by not switching.

“I’m so used to macOS it would be hard to change my habits”

This was the thing I was the most afraid of. It’s hard to break old habits. But the truth is Win10 and macOS are very similar. In fact, the only thing I had to learn is pressing Control instead of Command. Moving to Windows was painless considering I’ve been using Macs for almost ten years.

And the great thing about Windows is you can change everything how you like it, including remapping keys. So if you want, you can spend a bit of time setting everything up and not change your behavior at all. I promise that after a couple of days you will feel at home.

“Windows has crappy software”

I’m not going to lie: macOS has better software and better developers. In general, Windows apps do not have the same attention to detail and hand-made feel as macOS apps do. But for me, it’s a relatively small trade-off. The majority of the apps I use are the same on Mac and PC, and I can tolerate something like an ugly FTP client.

After three months of using a PC, there are just two Mac apps that I can’t replace: Logic and Xcode. I occasionally use Logic to record guitar tracks, but I have no problem pulling out my Macbook for that. Also, I could replace Logic with Pro Tools, but I have no desire to do so.

I forced to use Xcode to build our iOS app and to submit it to the App Store. If I could avoid it I would - Xcode is the worst IDE I’ve ever used. I hated it even back in the day when a was a hardcore fan boy. But, as with Logic, I can use my Macbook for that.

Everything else I need has a Windows version or alternative.

Hardware

Let me be very clear. If I buy a $3k laptop, I want the technology to be cutting edge. New Macbooks got Skylake CPUs right at the end of their lifespan - Koby Lake is right around the corner. Going with AMD instead of Nvidia in 2016? Seriously? And it’s not even the premium AMDs. Don’t even get me started on four USB-C ports. Just don’t. Displays are nice, but I will let you in on a secret: Apple doesn’t make them. They are made by LG or Samsung, and you can get basically the same panels in other non-apple products.

Obviously, if you decide to go with PC, you have all the options in the world. I know that PCs are perceived as squeaky pieces of crappy plastic, but it’s simply not true anymore. In fact, if you go with premium models, it will be very hard for other people to notice that it’s not a Macbook. So no one will make fun of you at Starbucks, just put some edgy stickers on it or something.

As a side bonus, you will have good old USB ports, so you won’t need to carry a backpack full of dongles everywhere.

Gaming

This is a bonus, and I understand that not everyone cares about games. But let me just throw it out there: you won’t be able to play any modern games on your new $3k Macbook. You could play something like Civilization at minimum graphic settings at 20 FPS. That’s a pretty miserable experience.

My $1.5k desktop PC can run any game on ultra settings at 60+ FPS. Video games are amazing now, and PCs are definitely the best place to play. So, if you are into gaming at all, just buy a PC.

PCs are not longer ugly, slow and glitchy pieces of crap they were ten years ago. They are cool and get better every day. The industry moves incredibly fast throwing out new products every week.

Apple took almost two years to upgrade their Pro line-up with questionable hardware at ridiculous prices, and macOS shows no signs of life.

I think they made it very clear that they don’t really care about professional computers anymore. If you are considering switching to PC after yesterday’s keynote, just do it. I’m pretty sure you won’t regret it.

ERR_CERT_AUTHORITY_INVALID on Android and IIS

Tue, 29 Dec 2015 17:53:41 GMT

Recently one of our users complained that he couldn't access our site on his Android phone.

He was getting an ERR_CERT_AUTHORITY_INVALID error in Chrome browser on his phone, but it was fine on his desktop and other devices. It took us a while to figure out the issue, so I decided to share the solution.

Our servers are running on IIS 8 on Windows Server and the user's phone is running on Android 4.1.2. It is a pretty old version of Android and I do not know for sure what other versions are affected, but I'm fairly certain it is anything up to 4.2.

The problem

You can use SSL Labs to find out if your server is affected. Enter your URL, wait for it to build the report and then scroll down to "Certification Paths". If you see "Extra download" in there you are probably affected.

This is an issue with your certification chain. To put it simply, whenever you visit an "https://" site, your browser requires not only your certificate but also all other certificates in the chain. Namely root and intermediate certificates of your SSL provider.

Usually, your web-server serves the whole chain. But even if the supplied chain is incomplete or has other issues, most desktop and mobile browsers can figure it out themselves by downloading missing certificates and putting them in the correct order in the chain.

Old versions of Android, however, are pickier and require a correct chain to be supplied from a server.

The solution

Obviously you need to install the missing certificates on the server, BUT this can result in multiple "certification paths" and you STILL might be getting the "extra download"

In our case, the issue was that IIS supplied an extra unneeded certificate in the chain. So the solution on Windows Server IIS is to simply disable it.

Go back to the SSL Labs report and find the certificate that requires extra download and remember its name. Now go to certmgr.msc on your server, toggle ‘Trusted Root Certification Authorities' and locate that certificate in the list. In our case it was called COMODO RSA Certification Authority. Right-click it and choose "Properties" and set it to "Disable all purposes for this certificate".

Deleting the certificate won't work, because Windows will auto download it again. Just disable all purposes. You will need to reboot your server. Restarting IIS won't be enough.

7 Great Newsletters for Startup Founders

Wed, 28 Jan 2015 20:45:25 GMT

It takes a lot of time and effort to stay on top of the industry news. Tons of new articles are being published every day, but not all of them worth your attention. Let other people do the dirty work and find the most interesting and useful stuff for you – subscribe to a newsletter. Here are 7 curated weekly email newsletters that I love.

SaaS Weekly

Curated by Hiten Shah, founder of Crazy Egg and KISSmetrics, this newsletter is easily the most useful one, if you make a SaaS app. Every week you will get nine articles and a "tip of the week" straight to your inbox. Articles are divided into the following groups: Business, Product, Marketing, Sales, and Growth. It comes out every Monday morning, which is a perfect time to get some ideas for the following week. Subscribe here.

Founder's Cabin

This weekly newsletter is curated by Josh Pigford, founder of Baremetrics and aimed at founders, entrepreneurs & startups. Generally, it includes the same kind of articles SaaS Weekly does. I love that Founder's Cabin has a cozy feel to it – probably thanks to its logo. Makes you want to sit down with a cup of coffee and read it all. Subscribe here.

#Startup Curated

This one is a newcomer with only one issue so far, but it looks very promising. Published by the guys behind Startup Foundation and #startup Slack channel. It is divided into several sections: lessons learned, articles, videos, presentation decks, audios, and resources. Here is what they say on their web site:

Curated by founders, for founders and delivered every weekend in one minimal email.

Subscribe here.

Remotive

Great newsletter for those of us working remotely. How to stay productive, where to live in, how to set up your desk – that kind of stuff. It also comes with "Book of the week" and "Motivational Clip of the Week". Subscribe here.

Accidental Creative

Curated by Todd Henry, author of two great books Accidental Creative and Die Empty, this newsletter comes packed with articles about creativity, motivation, productivity, etc. It also comes with "Book of the week" recommendations. Although it usually contains quite a bit of self-promotion, it is definitely worth subscribing to. Subscribe here.

Austin Kleon's Weekly Newsletter

Austin Kleon, author of two hugely popular books Steal Like an Artist and Show Your Work, is one of my favorite people on the internet. He is always funny and inspirational. This newsletter contains just 10 links of the stuff that Austin finds interesting. As he says on the web site, sign up for a weekly dose of inspiration. Subscribe here.

HackerNewsLetter

The best posts from HackerNews curated by hand. It's been 218 issues so far and it's not going anywhere. Sign up to keep up with the industry in general. Subscribe here.

by Max Al Farakh, co-founder of Jitbit.
Blog | Twitter

Lessons Learned After running an NPS Campaign

Sat, 17 Jan 2015 15:22:11 GMT

So, we've just finished running a Net Promoter Score (NPS) survey for Jitbit Helpdesk. NPS is a hot new thing in the startup world. Well, it's not entirely new – traditional marketers have been using it for 20 years, but, since startupers tend to despise traditional marketers and ignore everything they did for the last 50 years, we've learned about NPS just recently.

Basically, NPS is a survey where customers tell you if your product is any good. You ask them a single question: "How likely is it that you would recommend Jitbit Helpdesk to a friend or colleague?" They respond on a 0-to–10 point rating scale and are categorized as follows: promoters (10–9), passives (8–7) and detractors (0–6). Your NPS is % Promoters – % Detractors. Everything above zero is supposed to be good.

Pressing the "Send Survey" button scared the crap out of me. I know that we have a decent product. I'm proud of the work we're doing. But do our customers actually like it? What if we are not that good at what we do? Should I have become a ballet dancer instead like my mom always wanted me to? Tons of similar questions were going through my mind when I pressed the button praying that we get a positive NPS.

Three days later our score settled at 42 (this is very very good). I'm writing this article for the following reasons:

To publish our numbers, since I couldn't find much data to compare ourselves to
To share what we've learned
To brag about our awesome score and to thank our customers once again

Other's numbers

I got the idea to run an NPS survey from this article on Groove blog. They ran two surveys (here is the article about the second one) and compared the results between them. Groove is actually our direct competitor, so their score is an excellent data point to compare ourselves to.

Going from a Net Promoter Score of 11 to 16 is a big, big win.

(I told you I was going to brag, didn't I?)

That was actually the only published score for a b2b web app I could find. Thanks a lot to Groove for publishing it.

Apple predictably has one of the highest NPSs ever: 70% for iPhone, 76% for Macbooks. Since I am an Apple fan boy, I took it as an absolute maximum one should devote their live to achieving. Of course their scores are hardly relevant to our industry, but it's nice to know the score for something objectively awesome.

This is practically all the data I could find.

Our numbers

So, yeah, turned out that most customers love us very much. It came as a shock and I still think that there must've been some mistake. I honestly expected that the stripe would be mostly yellow. The results blew my mind. Thanks again to our customers, we love you too! Except for the guy who scored us "0" and wrote "you suck!" (still pissed at you). Here are the numbers:

NPS: 42%
Promoters: 50%
Passives: 42%
Detractors: 8%
Response rate: 29%

But the survey doesn't stop there. I've also sent a follow-up email to every customer.

Promoters

Promoters are the customers who responded with a 10 or a 9. Their feedback is the answer to the most important questions there is: Why people use (pay for) your product? I tried to find this out for a long time and this survey proved to be the most effective way to do that.

Here is a typical promoter response:

Great ticket support system for those not needing a million whistles and bells. Easy setup and not bloated like most. 100% up time and super tech support!

And here is summary of all promoter and passives responses of what exactly do they love the most:

The basic idea is that you keep doing what they love, since now you know what it is for sure. In our case we need to make the app even easier to use, keep providing great support and we probably should not touch our prices.

I followed-up with each of them via email and learned a lot more. I'm not going to bore you with the details – they are too specific to our app.

Passives

These are the people who responded with a 7 or an 8. This group is important. They like the product, but they miss something. Our job is to convert passives to promoters.

Here is a typical passive response:

Good app, but should be able to automate notification when new tickets remain unassigned for a specified period of time!

Or this:

Great interface, fast performance. Only issue I ever notice is that email notifications regarding a ticket being "closed" are inconsistent.

Passives' responses generally are a lot more specific. We got tons of feature requests and bug reports from them. But the real magic starts when you follow up with this question:

Thanks for the response. Is there anything we can do for you to make it a 10?

This is where I got the most useful responses. I found out that:

Our mobile apps need a lot more work
Users need way more reporting capabilities
We need to think about a redesign to modernize our look
Many people want their helpdesk to update in realtime without the need to refresh a page

Detractors

These are the people who responded with scores from 0 to 6. As you saw there was not a lot of these, but still. I would divide this group further into two subgroups: 5–6 scores and 0–4 scores.

I guess 5 or 6 is not a bad score in people minds. It's not a bad score in my mind either, I honestly have no idea why they are called "detractors". They seem to be no different from Passives. Customers from this subgroup were friendly and provided constructive criticism like this:

You discontinued text messaging and the app doesn't do push notifications. Until this is fixed, I cant go any higher.

People who responded with scores from 0 to 4 provided no feedback and didn't answer any of my follow-ups. Secretly I think that they do not count and our NPS is actually 48.

Should you run your own Net Promoter Score survey?

Yes. We've tried tons of different surveys, Qualaroo, question widgets, email campaigns, etc. NPS is the most effective thing we've done in a long time. For some reason, when you ask "would you recommend it to a friend?", you also get answers to:

What exactly do you like in our product?
What do you hate?
What can make you love it more?

The answers to those questions are priceless. Also, NPS survey has one of the highest response rate (29%) of any other campaigns we've ever done.

How to run your own NPS survey

Pretty simple. We've used Promoter.io and that's what I recommend everyone to use. This tool does only one job and it does it amazingly well. Their support was proactive and friendly. Worth every penny. I saw that there are some other tools, but I haven't tried them, because they looked ugly.

There are three stages to running an NPS campaign:

Sending out your survey
Following-up with each customer individually
Analyzing the results

Do not skip the second stage. This is where you get the most useful stuff.

That's all I had to share. If you are going to run an NPS survey, please share your result in comments (even if it's bad) along with the description of your product. I will add them to the article. Thanks for reading.

Detecting Outlook autoreply/out-of-office emails and X-Auto-Response-Suppress header

Fri, 15 Aug 2014 06:02:45 GMT

In our helpdesk app we have a method called IsUselessEmail(). Its purpose is to filter through all incoming email and drop all bounces, autoreplies etc. This is a tough problem since we need to drop as much garbage as possible, but pass through any useful emails.

We've been constantly refining that method over the years adding more and more filters. Recently I've been googling for what others are doing and saw a lot of people suggesting to add X-Auto-Response-Suppress (as well as other headers we've already had) to the filters. We've added it, ran some basic tests and deployed the app. Later that day we've lost tens of perfectly good emails.

Do not use X-Auto-Response-Suppress

Information about X-Auto-Response-Suppress header is practically non-existent. All we have from official sources is this mysterious MSDN page, which doesn't explain its purpose. After some digging, I found out that this header is used only by Microsoft Exchange and it's purpose is to prevent other Exchange servers from sending auto-replies in response to an email with that header. It doesn't mean that this email is garbage at all.

If anything, you can use X-Auto-Response-Suppress the other way around. You can add something like this to your outgoing email to minimize the number of useless emails you get in response:

X-Auto-Response-Suppress:OOF

So, don't ever use X-Auto-Response-Suppress in your filters or you risk loosing many perfectly good email.

Headers that are safe to use

Through trial and error we've figured out what headers you actually should use to detect auto-responses. Here they are:

X-Autoreply
X-Autorespond
auto-submitted with a value of "auto-replied"

If any of those are present in an email, then that email is an auto-reply

SQL Server: how to convert datetime to UTC

Sun, 10 Aug 2014 09:37:11 GMT

Recently we needed to convert all dates in our database to UTC format. We've made a mistake not storing them in UTC from the beginning and it led to complicated issues. When you have to support various time zones, there is no other way but to store you dates in UTC.

Luckily enough SQL Server comes with a handy GETUTCDATE() function, which, as you all have guessed, returns the current UTC date. The only thing you need to know to convert your dates to UTC is the offset between your server's time and UTC. You can do it like this:

SELECT datediff(hour,GETUTCDATE(), getdate())

For Eastern Standard Time that will be -5. So to convert your dates to UTC you need to do this:

UPDATE Table SET Date = DATEADD(hour, -5, Date)

But there is one problem.

Daylight savings

Currently the offset between UTC and EST is actually -4, since it's summer and we are now in daylight savings time (EDT). So the above code will substract 4 hours from every date we have. But obviously lots of those past dates are not during daylight savings and you need to substract full five hours from them. Turns out there is really no way around that in SQL Server.

But you can perform the converion outside of SQL. .NET has great built in tools to convert between time zones with daylight savings support. It is really easy to do the conversion in C#:

[SqlFunction]
public DateTime Convert(DateTime date)
{
	return TimeZone.CurrentTimeZone.ToUniversalTime(date);
}

Then you just need to store it in the database and you are all set. You can even create a CLR stored procedure out of that method. Hopefully all of the above will save you some time.

Feature requests are not (necessarily) evil

Wed, 09 May 2012 01:06:08 GMT

We are not a big fans of 37Signals' approach of turning down every feature request by default at Jitbit. We actually love and appreciate our users feedback. Not a day goes by without a couple of feature requests for one of our apps and in many cases we're making a decision to implement them.

It's just wrong to pretend you know, what's best for all your users. They are not dumb too, they have good ideas and you've got to listen to them. Of course you shouldn't blindly fulfill every request to make everyone happy, it will turn your app into rubbish in no time. But you do need to analyze every request carefully.

The bad thing is it's rather hard to do. And the main reason for this is that in the most cases users are proposing the solution to the unknown problem. Unfortunately, acting like a selfish smart ass is a default user behavior pattern and that's perfectly fine. They don't need to think about other users, it's not their job, it's yours.

To analyze a request and decide to implement it or not you should make a user to tell a story. What he's trying to accomplish? What is his actual problem? Make a user tell a story. When you will know and understand it all, you can came up with a solution that will be good for everyone, not just that one user, it will add a real value to your product, also you will know more about how your clients are using your apps.

Listening to your users is essential, you just need to put on a little effort to make them happier.

Reading: iPad vs. Kindle

Wed, 25 Jan 2012 14:12:46 GMT

I’ve always wanted to buy a Kindle, mostly because, you know, all other geeks have it and really like it. At the same time, purchasing a Kindle while already having an iPad seemed like an overkill to me. Well, at least I thought so before reading the latest DHH’s post on 37signals blog:

Instead of killing the Kindle, the iPad just killed my desire to read books. From the time I got the first iPad until I rediscovered the Kindle this Christmas, I don’t think I finished a single book.

I realized, that I had the exact same problem. I’ve been struggling to read Steve Jobs’ bio for a month, and I haven’t read even a quoter of the book, although it is very interesting and incredibly well written. So, I went and bought a non-touch Kindle 4 yesterday. Wow! I can’t stop reading ever since.

When you’re reading on an iPad, the whole internet is just a few taps away, while Kindle shows you just the things the real book shows you: the text itself and your progress through the book (you can always estimate how many pages left in the real physical book). I think that’s the main reason causing such a great difference in the reading experience. Kindle is a distractions-less and, with it’s sluggishness, even zen-like device.

Kindle fits in the inside pocket of my jacket, it is really light and cheap. You can take it anywhere without being afraid to lose it, it’s not a big deal at all, just go get a new one. Can’t say that about a $500 iPad.

iPad is much better for reading RSS and magazines, but for books (and Instapaper, i guess) Kindle is way ahead. I’m reading again and, damn, it feels good.