I did not read all the comments, but we do cruise a lot, so pardon any repeats. Note many items fall in multiple categories, but are listed only once. I am sure I have missed a lot, but thought this would be helpful.
Free drinks: Some cruises give you a drink as you board (mostly upscale). The Captains coattail party often offers free drinks (many lines). Some lines give you a free drink voucher (Holland haa). Some lines give you a started bottle of wine in your room (most upscale cruise line provide a sparkling wine). If you are booking a premium suite, they some times come with in room bar/frig (upper tier lines). If you go to some functions you can get a free glass of wine (in art auctions) as an inducement to get you there. Water is always free from the tap. Bring a water bottle. You might meet a friend that shares. Some cruises provide wine and beer with lunch and dinner (Viking, American Cruise Line). ACL also have a predinner coattail hour.
Limits: drink packages do not include your room in most cases (you have to go get it, and likely both of you will need to go...same with a refill). Packages have a daily limit (usually not a problem), Often packages are multitier, so there maybe premium level drinks. You cannot share with others. Sometime you may use your drink package in specialty dining, others not (in any case your may feel cheap do so).. Getting a second drink at the showroom can be problematic.. All adults in you cabin must buy...definitely a problem with a roomy who doesn't drink or drinks lightly). You can only get one drink at a time (we have not had a problem in getting a margarita and a bottle of water at the same time).
Economics: When calculating don't be surprised if they have some special priced drinks on board, at a discount (diluting your value). Having a package may detour you from participating in wine tasting or hard liquor tasting programs. Same with wine paring meals. You cannot change your mind mid cruise. Often it is cheaper to buy before you sail, but that can put the money at risk if you can't go, and loses interest. If you get sick, your drinking can be limited. If you have covid, they will bring your drinks to your room (Regent 7 Seas, not a package). Often drink packages are include in larger packages, at steeper discounts. Some people like to hang out in a bar in the late hours, while others are 9-10 hour sleepers (early to bed). Some lines allow some wine to be brought aboard.(corkage may apply). This is especially true if bought on tour, ie wine tasting tour. Buy it and forget it, is the peace of mind of having a package (prepaid). Use your Costco card and get 3% rebate on your package.
Wine bottle packages: These can be good, especially if you are mostly a wine drinker. You can have the bottle (s) in your room (in room meals and casual drinking) or order anywhere on ship. This wine can be shared. Packages come in 4, 6, 8 bottle groups, and again there are tiers of quality/pricing. The wine can be accessed anywhere (staff keeps it for you).
What situations enhance getting a drink package: Lots of sea days mean more opportunity to use them. Lots of tour package limit the use. Shorter cruises may allow you to drink more, as you don't feel like you are killing your body. However, on shorter cruises the start day and end day are limited time, so watch what they charge you in days. Some people can just out drink others (body weight and sex?).
We have a cruise coming up late next year (two before that) Holland. Our room includes club. We want wine in our room and not have to go get it (wine bottle package, which at 4* we get for half price). We have access to an honor bar in club. We drink lots of wine with dinner. There are a number of days at sea and the cruise is 28 nights. We both get one drink ticket. We will be attending a free wine tasting. Still thinking about a drink package too. So the above does not answer all the questions, but it does provide some of the things to consider.