Welcome to our From the Data series. This is where our data engineers get to have a little fun with the NetWise database to see what we can learn. (And what you can learn about what can be done with our data.)
First, let's start with what data we're dealing with.
Everything we used to produce the graphic above came from the master database which powers the NetWise Audience Platform. We filtered down to the United States and cut out U.S. territories like Puerto Rico and Guam. Our data includes multiple job functions. For this analysis, we focused on marketing. We looked at the number one title for each of the fifty states. Here's what we got:
Graphic designer was the top title for 50% of states (25/50)!
Marketing Director was the top title for 24% of states (12/50)!
Marketing Manager was the top title for 22% of states (11/50)!
Here's a breakdown of how we structured the query to turn out these results:
To get the top ranking title, we partitioned the data by the job function and state columns and sorted by the count of contacts in descending order. To do this, we used the row_number window function and also added it as a column selector.
Then, we used the new row_number column selector to define each title's rank.
From there, we wrapped the statement in parenthesis and made it a CTE.
After that, we further aggregated the data to get the count of states per each top title in order to summarize the top titles in the United States.
What did we learn?
Here's the fun part. We got some stuff we might not have guessed.
Graphic designer is surprising as the number one title because it is more specific than the Marketing Director or Marketing Manager Title.
The number one title in Nebraska is Media Specialist. No other state has that as the number one title. How can this be the case? Maybe a specific business chain that names positions this way? If you have any idea, reach out on Twitter and let us know.
The number one title in New York is Creative Director, which is less surprising because NY has a lot of creatives and is chock full of arts and culture. Right?