TL;DR: My initial reaction to this was 'yeah, this would be great'. After the comments we exchanged and the edit you made to the post, I still think it might work if people don't dismiss it as yet another banner, but I disagree with the reasons you give for implementing such a feature, and I think it should only be implemented for 1 reason: Offering a way for moderators to get users informed on how to use the site they just joined that allows more customization than the tour page, and is more 'in your face' than help/on-topic.
The value in such a message to me would lie in its ability to link to comprehensive meta posts that explain how to participate constructively on a site. If your answer is an explanation of why that's the proper use of a welcome message, you don't need to for my benefit.
My answer won't be an explanation of why that's the proper use of a welcome message, my answer will try to explain getting users to participate constructively on a site is the main reason to spend time on implementing this.
You're right in saying that every site can write their own message, but to avoid trying to hammer in nails with a screwdriver, it's necessary to be clear about the reasons for implementing these messages, and the reasons they might be useful. If messages like this are implemented to make handing out screwdrivers to users easier, but communities still expect new users to come equipped to hammer in nails, the messages and implementing them would have no more use than the current tour and help center.
Your Reddit message starts out with 'This message is to inform you about the subreddit and how it functions'. After that, I see: a description of what is 'on-topic' on the subreddit, a link to the rules and how to contact moderators, an invitation to pick a 'flair', an explanation of the sticky posts and what to use them for, guidelines for writing your posts: use the right flair and avoid spoilers, guidance on filtering other's posts, and links to the wiki.
If I would try to find comparative things on SE, a similar message would include: a short description of the topic of site (with a link to help/on-topic), information on the 'rules' and their enforcement (a link to the code of conduct and how to flag page), an invitation to set a profile picture, an explanation of featured and hot posts, guidelines for writing a good question or answer, guidance on using the search bar and filtering questions, and links to useful meta posts.
Basically: Both messages would be filled with information intended to get someone up to speed as soon as possible.
After I did the first tour, all the tours on subsequent sites were so similar I just skip it now that I know how Stack Exchange works.
I don't think Stack Exchange would use exactly this sort of welcome message, because it would be redundant with a lot of existing documentation that is easier to find here than on Reddit.
You're not a total newbie user to SE. You write about skipping tours, and about the existing documentation in the tour and help centers, about knowing how Stack Exchange works. This shows you know your way around SE already to an extent, know where to find what's on-topic on a site, where to find meta, the code of conduct and the flag button.
A completely new user will not know what you know. When you create your first account (I tried by making a sock this morning), it will automatically link you to the tour page. If you join any sites after that, it only gives you the confirmation popup. The tour doesn't teach these skills you already have. The existing documentation you need to get started on a specific site on SE is easier to find for you and me, because we know where it's at by this point.
I think it should be more focused on cool things about that individual site, like resource threads on the site meta, or events the site has (like Arcade's screenshot of the week contest), or an invitation to the chat room (chat was kind of intimidating for me when I was brand new).
I think for Stack Exchange, it should be used to highlight what's cool and unique about the site. For example, Arcade has a screenshot of the week contest that I had no idea existed when I first joined the site
This seems to be your main reason for wanting these messages implemented. According to you, pointing out these things exist will make users feel welcomed and encouraged to start participating more than hitting them over the head with good guidance on how to write useful contributions will.
A really new user won't be able to participate in chat, or in contests that are organised on meta. They don't meet the reputation threshold for that privilege yet. There's a difference between Reddit and SE: Reddit does not have privilege thresholds, SE does. A welcoming message that links users to things they can't do, won't be welcoming. People will be frustrated when they see they have to gain reputation first. Yet the message they were handed doesn't help them reach privilege thresholds, it only points out those things that are out of their reach.
It seems like the first memorable interaction a lot of new users have with a community is a close vote or a down vote.
It just seems to me that most new user on-boarding efforts are taking the same approach--education about how things work instead of trying to get users excited about participating and make them feel like people are interested in their contributions and will help them learn the ropes.
So these messages should be implemented, not so we can educate users on how things work, but instead so we get them excited about participating, and we raise the expectation that we are interested in their contributions and helping them learn the ropes. How would a message teach users the ropes, if there are no references to where to find rope or which ropes are most important?
This would raise a lot of false expectations. If you don't inform users on the site and how it works, how will they be able to avoid that first memorable interaction of a close or down vote? You're only postponing it by one message, and it was a message that made them excited about participating and raised the false expectation that the site was interested in their off-topic post. I doubt people will look back on that message as being useful or welcoming, when all it did was trick them into posting without understanding the site, and as such crashing hard.
@Tinkeringbell having helped composed a similar message on a different subreddit - after adding it we had: more people reference it with new user first mistakes (with a correlating response of "oh, let me read through that more carefully then" versus the hostile responses we were seeing), veteran users happier to have something they could just reference, and more new user's referencing it in their questions ("I read through X and think this question will be a good fit....") which seemed to temper responses by others. Don't have any data to share (can't) but it had an effect I can tell you.
Let's look at what LinkBerest saw a similar message do on Reddit: the message became a reference old users could point new users to, AND something that new users couldn't deny having been presented with before they started participating on a site, which tempered responses.
That should be the reason for implementing such a message: it can be more customized than the tour and do a better job than the tour at explaining which mistakes to avoid and how to write good posts for a particular site. It will (should) be more 'in your face' than the different help center articles or faq's on site meta/MSE.
Getting people excited about contributing and informing them how to do most effectively do so don't need to be mutually exclusive. Your reaction to your Reddit message proves that. But in order to keep people excited, they need to have realistic expectations.
Arguing that the main reason for implementing this feature should be to get new users to feel welcomed, excited and like we're interested in their participation, while also saying these messages shouldn't be used to inform them of how to write a good post or what is on-/off-topic goes against most of how these sites work. SE is not Reddit, after all.
SE sites welcome users that post on-topic, well-written posts. As such these messages should have the same purpose as the ones on Reddit, and be implemented for the same reason: as a way to inform users about a particular site and how they can participate constructively there. Not as a way to merely get people excited and highlight the cool parts of a site at the cost of not educating new users on how to participate. There's already enough over-eager posters disappointed by having their content moderated out there.