Fun Practice With SQL (and life update!)
Hello fellow LinkedIn friends!
I hope each one of you have had a better first half of 2021, at least compared to how 2020 started out.
Despite the current international turmoil, COVID-19 pandemic/mask mandates, and all the other problems in our world, I still truly believe we have a lot to look forward to in the future.
After all… the only way to go from the bottom... is UP!
In May of 2021, I earned my Bachelor of Business Administration (BBA) degree from Northwood University, where I majored in Marketing and minored in Business Analytics.
On top of this, I also landed an amazing full-time position at LUXOME where I am actively managing all of our different advertisement accounts (Google, Facebook, Microsoft, etc.), managing email Flows on Klaviyo, creating new/engaging ads, doing in-depth data/trend analysis, and so much more, every single day. For those of you who know me, who have worked with me, or even who have seen my previous posts, you know this type of position is perfect for me. I love coming to work every single day, and already through 2.5 months, I have learned so much about the e-commerce industry.
With all this happening over the last eight months, I slowly drifted away from one of my New Year’s Resolutions: publish at least one article on LinkedIn every month.
Whether this was because of a lack of discipline or motivation, poor planning, being distracted, other priorities taking precedence, etc., I could lay out a million excuses for why I didn’t follow through with my commitment to myself, but that doesn’t change the reality of the situation.
I did not stay true to my goal for the new year, but that is ok.
The year is not over. I’m ready to get back to it, and I’m more motivated than ever.
One of my personal goals after graduation was to continually learn. In the data science world, algorithms and machine learning gets stronger after they continually learn from examples.
In a way, our brains are also like machine learning. The more we think through or do something, the easier becomes in the future.
This is my motivation behind making data science content- to continually improve my skillset, become the best version of me that I can be, as well as inspire others/easily communicate through written content what the data actually means in easy-to-understand English.
Introduction to SQL
Over the past few months, I’ve been taking some introductory courses to grasp the fundamentals of SQL.
Ironically enough, I actually took a class that touched on SQL in college, but I struggled to learn the material because this was during the time when classes transitioned to 100% online because of COVID-19 shutting down in-person classes. With this transition, it was difficult for both teachers and students. Teachers because they had to teach their material online with little/no time to prepare the material. Students because this was a new kind of learning experience many of us had never experienced. It was tough. but I’m back 😊
SQL, or Structured Query Language, is the most common language for extracting and organizing data that is stored in a relational database. It’s one of the most valuable data-related skills to learn in a world where we continue to generate exponentially more data every single day, and many well-known relational database systems use SQL, such as Oracle, Microsoft SQL Server, and Access.
For this intro project, I used MySQL. This is a relational database that uses SQL to query a database. Feel free to download MySQL for free and follow along if you’re interested.
Key Differences:
Link to data for following along: https://www.kaggle.com/blastchar/telco-customer-churn
Time to get started.
Step One- Understand the context of the data
The first step in doing any data analysis is to first understand the context of the data. Understanding what the data means, reading the description (if using a public dataset), and trying to figure out what questions need to be answered. In this case, the context said the following:
There’s my challenge. How can I use this data to retain future Telco customers? If there’s no explicitly stated challenge, then feel free to make up your own! After all, the end goal of this, as well as learning any new skill, is to practice 😊
Step Two- Import the dataset
Once you have MySQL loaded, go to the left side in the navigator screen > click into a schema (for me, it was “usersdb”) > right click Tables > click table data import wizard > select file location > create new table > follow prompts, ensure data you’re reading in matches the correct data types > Click Finish (there's also plenty of short YouTube videos to follow along in case you run into a glitch).
BOOM! Your data is now imported in MySQL!
Recommended by LinkedIn
Step Three: Use the Select command to bring in the data
Probably one of the most important commands in SQL, SELECT returns a result set of records, from one or more tables. The “ * ” means ALL. In English, this statement means, “I want to pull in all of the data from the data table entitled TelcoCustomerChurn ”. As you can see, the dataset then shows in the Result Grid.
Step Four: Filter out dataset by customers who have left using the where command (optional)
This is technically an optional step, but it’s helpful to see how you can filter the dataset using the WHERE command. This shows us all of the customers who left the company.
Step Five: Use the Update command to update your table
The “Update” and “Set” command work in tandem to update the table. In other words, it changes your table based on the criteria you define. I am setting Churn = “Yes” because I am interested in seeing what factors caused the customers to leave the company.
Step Six: Use the Count and Group By command
This was by far the trickiest command to figure out. As you can see, I struggled to include all of the column names in the picture!
This Count(*) command returns the number of records returned by our selected query. By combining this with the Group By command, we can see a nice summarized total of customers who have different common characteristics through the right-most column entitled “count(*)”.
Step Seven: Translate data into insights
Where are the trends? What are the most common variables in predicting whether a customer was likely to leave? What is the data trying to tell us? How can we translate the data to make a decision? Why is this important?
I circled some of the common variables I noticed. Here’s what they mean:
Partner = No, Customer is single
Dependents = No, Customer has no dependents
Tenure = 1, Customer has stayed with the company for one month
Contract = Month-to-Month, Customer isn’t locked into a contract
If I was the marketing director of Telco, I could use this information to tailor a marketing campaign towards customers who fit these characteristics.
For example, I could offer a promotional coupon or discount targeted towards single customers (Partner), who have 0 dependents (Dependents), have been at Telco for one month or less (Tenure), and are currently in a month-to-month contract (Contract).
The end goal is to reduce customer churn, so the key is to think of a creative strategy to persuade these customers towards staying longer. Maybe requiring minimum contract agreements (such as three months) would be better in reducing customer churn. Maybe focusing future marketing efforts on non-single individuals who have dependents (couples and families) would be better. Maybe getting more information from these customers as to the top reasons why they are leaving, would provide more context and understanding to make a strategic decision.
The opportunities are endless… it just takes a little splash of creative thinking 😊
Thank you so much for reading! Did you learn something new about SQL? Do you have any ideas/recommendations for what I can do/write about in the future? Leave a comment below!
Great work (and refresher for me), Lance!
you inspire me to write more, Lance!