Drupal core is great and keeps getting better all the time. Each new release integrates new code, often from contributed modules. As an example, Drupal 7 integrated functionalities from more than 50 contributed modules of Drupal 6 (https://www.drupal.org/node/895314). This is just to tell how important the contributed modules are to Drupal.
On the first part I'll talk about the essential modules that could as well be part of core in the future. The second part will be about really nice modules that shall be installed depending on your site needs.
Global Redirect (or just Redirect that has merged basic functionality)
This is one of the most important modules for any Drupal site. The goal of GR is to make your site URLs consistent. This is good for the final user because URLs will be easier to read and understand but most importantly it's good for indexation and SEO. Global Redirect handles your URLs and redirects when necessary with the appropriate HTML status code.
If you ever happen to change the URL of a node, users that had the old URL will be lost. This is where redirect comes in. It will add a redirection from old URLs to new URLs. Remember that people will often point to a particular page in your site in forums or blog posts, if the address has changed you won't display the expected page to the user. To prevent this, just enable this module and forget about users viewing the dreaded “404 Page does not exist" message. Basic functionality from Global Redirect has been merged to this module.
This is yet another invaluable module to handle URLs. Pathauto gives you the possibility to create automatic friendly URLs based on a set of criteria. It will automagically transform the standard /node/5938 with something more user and search engine friendly. You can customise it at will and decide to disable it and enter URLs manually on a per node basis.
This module is, again, oriented towards SEO. It does a silent job while you can keep working on your content. The meta tags are intended to help search engines providing them with a description and keywords to pages on your site. They are created automatically according to your needs but you can also modify meta tags manually when editing a node.
WYSIWYG or CKEditor
Many people complain about Drupal not having an easy-to-use interface like WordPress or Joomla. Embedded text editors are a big point of this criticism. Although Drupal does not ship it by default you can easily add and customise your online text editors using WYSIWYG or CKEditor. Your users will be thankful. Drupal 8 will have CKEditor built into core.
You probably already heard about this module but if you haven't, check it out! It's intended to make the administrator job easier by providing a drop-down menu instead of the default task-bar. With the Administration menu you can get to administration pages in one click and one page load instead of going from page to page until you reach what you are looking for.
If your visitors can interact with your site (registration, comments, wiki...) you certainly know how spammers can ruin your community. This is, unfortunately, the inevitable problem of web 2.0. Mollom is a set-it-and-forget-it module to filter spam bots and keep your site clean.
If you submit sensitive data to your site (passwords, user data, credit cards...) you need to encrypt it through https (SSL) before sending it out to the web. Without encryption, sensitive data is sent in clear text and can be intercepted and exploited. Secure pages redirects the necessary pages to https or back to http according to the site needs. Be warned that you need to have SSL enabled and configured in your server before you install this module. Check if you have SSL here. https://www.sslshopper.com/ssl-checker.html
Indispensable optional modules
b2 nice comments
Simple yet very nice module, the b2 nice comments will hide the big comment form on your site showing only a small empty text box to enter comments. When the user clicks on the comment box, the standard comment form expands to show all fields. It may sound like it's not such an important module but if layout is important to you, this module will make a difference.
Engage your audience by allowing users to follow up new comments. It's useless to say that if you want your comments section to become a real dialogue between people, you need this module.
Views & Panels
These are probably the most downloaded modules in drupal.org and I don't feel like repeating what has already been said millions of times but I can't do a “best of” article without mentioning them.
Boost + Boost expire and/or Varnish
To make your site load faster to your anonymous users, Boost creates a cache of your pages and serves them almost immediately. With the correct configuration it can greatly improve the performance of your site. Boost expire is an essential addition to Boost. It expires the cache when content changes in order to keep your site up to date. Varnish does the same job, it caches pages, but does so from the server, even before hitting the drupal backend. Varnish performs better than Boost but you need a dedicated server to use it. There are people using both Boost + Varnish but it's a rather complex and probably not so necessary thing.
Adding images to text can be painful without this module. As it names suggest, Insert will insert images into your text, resize them and eventually link to the original image.
Multilingual site administrators cannot live without i18n. It's the inevitable module to make your site speak another language.
This module can be a life saver on several situations. It does a good job at repairing internal broken links. Highly recommended if you are changing the location of your files and images internally, if you are changing domain name, if you have a production and testing site at different urls and so on. Be careful though if you have a multilingual site (it might repair some things but break others).
Used to break long nodes into smaller pages. It also adds a convenient pager at the bottom to easily navigate through your pages.
Keeps statistics about your site visitors and usage. It is very important to know your audience and improve your website. Nevertheless, private information should not be exposed. Piwik should be installed on your server (before installing the module) to keep this information secure and under your control.
Whenever you add an external link to your site you'd better open it in a new page. Users can get confused when they don't know exactly in what site they are. External links module adds the appropriate instructions to open external links, e-mails or a given pattern in new windows.
A great way to discover exciting modules is to visit http://drupalmodules.com/. There you can filter and search modules to get the best out of the drupal community.
More modules could be added and I'm open to suggestions. Do so in the comments or posting a personal message to me in the contact form.
Good luck with your drupal site!