It is not apparent to me, either! Fortunately someone referred to that exact macro before:
As for where it is used, /admin/customize/site_texts?locale=en&q=%25{new_user_tips} shows a few templates:
I’ve never seen another macro that pull into the content from another content template in Discourse. 