Done properly, product placement can create a realistic scene and a general feeling of familiarity. Is there more to it than that? Do you think consumers notice the placement?Are they consciously motivated to purchase that product, or are they actually reached on a more subconscious level?

Product placement in movies and TV shows involves more than just invoking a feeling of familiarity. It also involves targeting a specific demographic or age group, particularly those who tend to skip ads, to make them become more emotionally connected to a product and develop an association with it. 
Research shows consumers remember product placement and that it has conscious and unconscious effects. For example, people tend to recall the product to a greater extent when they see it placed within a show or movie, which is a conscious effect. They also tend to exhibit a higher intention to purchase a product after seeing it placed within entertainment, which is a subconscious effect of product placement. Research suggests effective product placement can also lead the audience to reject that product's competitors, which also operates on a subconscious level. The less disruptive the placement is to the content, the more effective it is shown to be. 
Which product placement you choose to write about is up to you. Famous examples include the placement of Reese's Pieces in the movie E.T., which resulted in a major increase in the product's profits in the weeks after the movie was released. The TV show Sex in the City famously mentioned Manolo Blahnik shoes in a way that equated the brand with have-to-own luxury and increased brand awareness. James Bond films have featured famous car brands over the years, such as the Aston Martin DB5 featured in the earlier film Goldfinger and the more recent Skyfall.