This is a bit of a long one, but if you’re interested in artificial intelligence or Eighties music, I think you’ll like it, so grab a drink and let me know what you think.
This is an exercise I’ve been looking forward to for a while. I want to feed a dataset to my favorite AI tools and ask for some help with analysis. To easily verify the AI answers I need to use a dataset I’m extremely familiar with, but don’t want to share my company’s private data with our new robot overlords.
Instead, I have a spreadsheet with the Billboard Hot 100 chart for every week of the 1980s, and I’m going to see if ChatGPT and Microsoft Copilot can analyze it with the kinds of “business questions” I might ask a business intelligence professional.
I actually use this dataset to teach SQL classes, which makes it an excellent test case for AI. I’ve prepared a list of questions, and I have the answers via SQL queries.
Which leads to an important note about Copilot. I did give the spreadsheet to Copilot through my enterprise-licensed subscription, rather than the free web version. The latter can’t parse Excel files, which would bring this experiment to a screeching halt.
So, without further ado, let’s get to some business questions.
Please calculate the number of weeks each performer spent in the #1 position, and tell me the top five performers, along with the number of weeks each spent at #1.
ChatGPT nailed it. Michael Jackson tops the list, 20 weeks at #1. Madonna is next with 15, then Olivia Newton-John with 14 and Whitney Houston with 13. Lionel Richie has 12.
However, ChatGPT failed in one aspect. Phil Collins and Hall and Oates both spend 12 weeks at #1, so fifth place is a three-way tie. ChatGPT didn’t catch that.
But it beat Copilot by a massive margin. Copilot only got four of the top five performers correct, and was way off on the number of weeks.
What are the most #1 songs by a performer, and how many performers achieved that count of #1 songs? Also, how many different songs hit #1?
Again, ChatGPT was extremely close. It informed me that the most #1 songs by a performer was seven, and three different performers had seven chart-topping hits. That’s what I calculated as well, those performers being Michael Jackson, Madonna, and Whitney Houston. ChatGPT erred with 232 different songs in the #1 spot, though. I calculated 238.
Copilot seems to have missed again. It tells me that Michael Jackson is the only artist in the decade to score nine #1 hits, and that there were 231 different songs in the peak position. Did Copilot totally miscalculate? Stay tuned, because there’s a bit more to the Michael Jackson story in a moment.
But first, one more question. A one-hit wonder is defined as a performer with only one song with a peak position between #1 and #40. How many one-hit wonders are there in this data?
ChatGPT got this question exactly right: there are 483 one-hit wonders in my 80s data set. Copilot went horribly awry, reporting 231. I can’t even begin to explain that.
Let’s talk about Trusted Advisors. In the BI world, a Trusted Advisor is a person who not only answers the question, but provides added value. The Trusted Advisor asks if you’ve taken any other circumstances into account, suggests alternatives, and adds relevant information. Quite often the Trusted Advisor helps you refine your business question to ensure the answer you’re getting is actually the answer you need.
In the case of my one-hit wonder question, I’d expect the Trusted Advisor to ask whether my data scope should be expanded to include data from other decades. That would have significantly reduced the number of qualifiers, as performers like Simon and Garfunkel, Gladys Knight, James Brown, and Golden Earring only scored one Top 40 hit in the 1980s, but had others in previous decades.
Which brings me to another Trusted Advisor responsibility in the BI world: data hygiene. A good BI analyst would look at my 1980s data set and immediately note that there are some serious hygiene problems. In particular, we see variations of performer names, which would seriously skew the results of a straightforward data query.
First, we get simple variations, such as “Chaka Kahn” versus “Chaka Kahn and Rufus.” Without some data modeling and cleanup, SQL would look at these two values as completely separate performers. And then we get into the question of Simon and Garfunkel versus Paul Simon as a solo artist. And how about Jefferson Airplane versus Jefferson Starship versus Starship? The Trusted Advisor needs to sort these things out before helping a leadership team make data-driven business decisions.
So, let’s go back to Michael Jackson. If we search the data set for just “Michael Jackson,” ChatGPT is correct — the King of Pop had seven #1 hits. But my data set actually shows two more. Michael Jackson and Paul McCartney hit the top with “Say Say Say,” and Micheal Jackson with Siedah Garrett peaked at #1 with “I Just Can’t Stop Loving You.”
It appears that Copilot was insightful enough to solve exactly the problem I mentioned earlier. It recognized “Paul McCartney and Michael Jackson” and “Michael Jackson with Siedah Garrett” as acceptable variations of “Michael Jackson” for the sake of my business question.
Way to go, Copilot…I think. I asked Copilot which songs comprised the nine #1 hits by Michael, and it confirmed that “Say Say Say” with Sir Paul was one of them. However, it omitted the Siedah Garrett collaboration, and told me that the ninth #1 for Michael was “Black or White,” which wasn’t released until 1991.
Since it’s rather important to know whether your Trusted Advisor is correct or made a lucky guess, I dug in further with both Copilot and ChatGPT. I’ll talk about that more tomorrow…
Hit Me With Your Best Prompt 
Leave a comment