A Site page always has built in menu navigation on the left side margin. This navigation cannot be taken away. It's there so that you are not building this page from scratch. Many people like using site pages because it helps them organize the layout of their pages. Site pages are less useful if you only have a small amount of information, i.e. that fits on one page or less.
But Site pages are great if you need a series of pages. For example explaining a broad topic like Rotary Grants may require subsections like "What is a Grant?" and "Where to apply," etc. Site pages excel at breaking up and organizing these sections of data.
Site pages cannot use widgets. Only text, links, images and video.
A Custom page starts off as literally a blank page. The interface to edit a Custom page is very similar to the Website Designer interface (dragging widgets around). A Custom page can have 1 column, 2 columns or 3 columns, appearing in any variation you choose, e.g. wide left/narrow right, or vice versa. With Custom pages you have much more flexibility with the layout and look.
Custom pages are great if you don't have a lot of content (i.e. one page or less).
Custom pages can use widgets. As an example you can setup a custom page that uses the Speakers widget in one column, and the Events widget in the other column. The nice part of using these built in "automatic" widgets is that creating a Speakers/Events page like this means you'll never have to edit that Custom page again, as both the Speakers and Events widgets update themselves every day.