Marketing Campaign Analysis | ULTIMATE SQL Projects for Data Analyst

Key Takeaways

In this video, I will show you a realworld data analysis project using the same type of data set we receive from clients. At my company, we receive a lot of campaign data from clients. Since the data is confidential, I obviously cannot share the original data set here. Therefore, I kept the same table structure but replace the records with dummy data. So, the data you see is synthetic. However, the analysis process is exactly the same as a real project. And today you will learn how campaign data is actually analyzed using SQL. If you want to practice along with me, the data set used in this video is available on my website and you can download it from the link in the description. And in this project, we will answer some very practical questions like which campaigns actually converted users, which campaigns only generated clicks but no conversion and which campaigns are simply wasting money. Let's start. Hello everyone, my name is Manish. I run a data research company called Data Profy and I have more than 15 years of experience working in the data industry. I am also a twotime Oracle Pro Award winner from Oracle Corporation which is given to professionals who contribute significantly to the data and developer community. Over the years we have worked with different type of clients data sets especially marketing and campaign data. Because of that projects like the one you are about to see are very close to what happens in real data analysis work. So in this video, I will walk you through the exact thinking process we follow when analyzing campaign performance using SQL. Before we start the analysis, one quick thing. If you're someone who wants to add a real data analysis project to your resumeum, I have created a well structured workbook for this entire project. It contains the complete data structure, the data set used in this video and more than 20 additional SQL questions with solutions that we solve in our company during real analysis. So if you want to practice this project properly, you can get that workbook from my website. The link is available in the description as well as in the pinned comment. Now let's start the analysis. Before solving the questions, let's quickly understand the data set we are working with in this project. We have four tables. The first table is customers. This table contains basic information about the users such as customer ID, first name, last name, gender, age, city, state, sign up date, customer segment and acquisition channel. So this table basically tells us who the customer is and how they enter the platform. The second table is campaigns. This table contains campaign level details like campaign ID, campaign name, campaign type, start date, end date, budget, target audience segment, channel and status. So this table tells us which marketing campaign the company is running and their details. The third table is campaign interactions. This table records what actually happened when customers interact with campaigns. It contains columns like interaction ID, campaign ID, customer ID, interaction date, interaction type, device type, country, cost per click, and converted flag. So every row here represents an interaction between a customer and a campaign such as a click or engagement. Finally, we have the conversion table. This table stores the actual business outcome. It contains conversion ID, campaign ID, customer ID, conversion date, revenue amount, product category, and discount applied. This table tells us when a campaign interaction actually turned into a purchase. Now, if we talk about relationships, customer is connected to campaign interaction using customer ID. Campaign is connected to campaign interaction using campaign ID. And when a purchase happens, the conversions tables also link back to both campaign ID and customer ID. So if you think about the flow, it looks like this. Customer interacts with a campaign and sometimes the interaction leads to a conversion. And using SQL, our job is to analyze this entire journey and extract meaningful insights. Now let's start with the first analysis question. So now that we understand the data set and the relationship between the tables, let's start the actual analysis. Now that we have our data stored in a relational database, which in my case is Oracle, we will start answering business questions. The marketing team wants to know which campaigns are generating the most engagement, which means based on our data, we need to find which campaigns generate the highest number of clicks. And we know that each time a customer click on a campaign, a row is stored in the campaign interaction table with the interaction type set to clicked. So your task is to count how many clicks each campaign received. We will focus on campaign ID and interaction ID from the campaign interactions table. We do not want all the data. We only want rules where the interaction type is clicked. For that we will use the where clause to filter the data. Now using the count aggregate function, we will count the total number of interaction ID values for each campaign ID. In other words, we are counting how many clicked interactions each campaign received. Since count is an aggregate function and campaign ID is a non-aggregating column, it becomes mandatory to place the non-aggregating column in the group by clause. Okay, this is close to what we need. However, it is incomplete because we only have campaign IDs but no campaign names in this report. Our campaign interaction table does not contain any campaign name column. Therefore, we need to fetch this data from another table which is the campaigns table. Both campaigns and campaign interactions tables are connected using a foreign key relationship. To achieve the desired result, we need to use a join operation. But before that, we will create an alias for the campaign interactions table and use it in the query so that it becomes clear where the columns are coming from. Great. Done. Now, let's patch the campaign name from the campaigns table. Now that we have added another non-aggregate column in the select list, it again becomes mandatory to include that column in the group by clause. Now before we execute this query, let me ask you something. Based on this query, which campaign do you think will have the highest number of clicks? Let's execute the query and see the result. Here we go. If you want to practice more questions like this, I have created a workbook for this entire project. It includes the data set and 20 additional SQL analysis questions. You can find the link in the description and the pinned comment. Now, the marketing team wants to measure how effective each campaign is, which means we need to find the click-through rate, CTR, for each campaign. CTR tells us what percentage of sent campaigns result in clicks. The formula of calculating CTR is your task is to calculate the CTR for each campaign. Let's do that. This query simply fetches the campaign ID. We will add aliases at the beginning so that there is no confusion later. Next, we need to find the total number of campaigns that were sent and the total number of campaigns that were clicked by customers. For that, we will use the case statement with count function. This query will not work because count is an aggregate function and campaign ID is a non-aggregate column. Whenever we use an aggregate function with a non-aggregate column, it becomes mandatory to place the non-aggregate column in the group by clause. Next, we will calculate the CTR percentage using the formula that I showed earlier in the same query. Let's execute the query and see the result. Everything looks fine except the CTR column has many trailing numbers after the decimal. Let's fix that by wrapping the entire CTR expression inside the round function. Let's execute the query and see the result. That's it. Done. Now that we have measured the campaign's performance using CTR, let's answer another important business question. The company's running campaigns across multiple channels such as email, social media, Google ads, influencer, and SMS. The marketing team now wants to know which channel is generating the highest number of conversions. Your task is to calculate the total conversions generated by each channel and rank them from highest to lowest. Let's do that. The data about channel is stored in the campaigns table and the data about conversions is stored in the conversions table. Therefore, we need to perform a join operation to combine the data from these two tables. Let's start with a simple join. This is a simple join operation. After executing it, we can see that there are multiple conversions under each channel. Now if we create a group for each channel and count the conversions under them, we can easily find the answer to the question which channel is generating the highest number of conversions. Let's do that. Now before we execute this query, let me ask you something. Which channel do you think will generate the highest number of conversions? Let's execute the query and see the results. Here we go. Twitter is performing the best. Let's move on to the next question. Now, let's move to the next question. We have three segments of customers. New, returning, and VIP. The marketing team wants to understand which type of customer brings the most revenue so they can prioritize the right audience in future campaign. Your task is to calculate the total revenue generated by each customer segment. The customer segment data is stored in the customer table and the revenue amount data is stored in the conversion table which again means we need to join these two tables to get our desired result. Let's start with a simple join. After executing this query, we can see that each customer segment has multiple revenue entries. Now, what if we create a group for each customer segment and calculate the sum of revenue amount for each group? That will give us the total revenue generated by each customer segment. Let's do that. Now before we execute this query, let me ask you something. Which customer segment do you think will generate the highest revenue? Let's execute the query and see the result. There we go. New customers are generating the highest revenue. Let's move to one more question. This time we will find the ROI of each campaign. Now let's solve the final question. The marketing team spends money running campaigns. That money is stored in the budget column in the campaigns table. At the same time, campaigns generate revenue when customer convert. Management now wants to know a very simple but important thing. Did the campaign actually make money? To answer this, we calculate return on investment, ROI. The ROI formula is total revenue minus campaign budget. Your task is to calculate the ROI for each campaign. Let's start by looking at the revenue data. Here we have campaign ID and revenue amount coming from the conversion table. After executing this query, we can see that there are multiple revenue entries under the same campaign ID. So what we will do is create groups for each campaign ID and calculate the sum of revenue for each group. Let's do that now. According to our ROI formula, we need to subtract the campaign budget from the total revenue. The budget data is stored in the campaigns table which means we need to perform a join operation to fetch that data. Let's do that. This will not work because the thumb rule says that if we have an aggregate function with non-aggregate column in the select list then all those non-aggregate column must be included in the group by clause. Which means we also need to add the budget column to the group by clause. Let's execute this. There you go. We can also page the campaign name from the same campaigns table. That will make this report more descriptive. Now it's looking much better. Now if you want to add this project to your resumeumé, I have created a well ststructured workbook for it. This workbook contains the complete data structure of the project, the data set used in this video and 20 additional SQL questions with their answers that we solve in our company during real analysis. So if you want to practice more real project style SQL problems, you can get that workbook from my website. The link is available in the description and in the pinned comment and comment and let me know if you want more such data analysis project with real data set. Also make sure to subscribe and like this video. Thanks for watching. This is Manish from rebellionrider.com.