Transcription
In this video, we're going to work together on an end-to-end SQL data analytics project. And there are quite a few reasons why I think this video will be super useful to you.
The first reason is that we're going to work with SQL, which is a tool that is in super high demand in the world of data analytics. And we're going to work with it with applications and environments that are actually used in real workplace. So that not only you can review your SQL skills, but also work in a same setting exactly simulating what is happening in a real business scenario.
The second reason is that we are going to work on a real business problem. And so we're going to use SQL not as the end goal as most people think about it, but only as a tool to actually find some insights and give recommendation to a real business, which is the actual role of a data analytics professional.
We're going to go through all the different stages of this project together. I'm going to start with some basic SQL queries, but also go towards more advanced level so that again you have the full spectrum of things that can be done in SQL. It's not going to be a clean tutorial. I might encounter errors and try to debug my code with you so that you can get a full idea on how a data professional thinks about solving issues while coding.
And the other reason why I think this video is going to be pretty valuable is that not only we're going to work with SQL, but we're also going to test our domain knowledge skills so that we can use our technical skills to really provide value to a business. And by the way, this is very similar to some of the projects that I have done throughout my 8 years of experience in the field. I worked as a senior data analyst at Deoid. I worked as a data analytics lead at AWS. And obviously the goal of this video is to bring you that knowledge to you.
And by the way, this is a little spoiler of the stuff that we can do together in my analytics and automation academy, which is a six weeks program where my goal is to teach you all the technical skills that you need to become a data analytics professional, but also teach you how to work with AI agents, which is a bit of a skills that is becoming more in demand in the industry. give you also the domain knowledge that you need in the work so that again you can increase your value as a candidate in the market. And my goal if you join the academy is to support you until you land a job offer. So in case you're interested, make sure to check out the link in video description and apply if you think you're a good fit.
And now without further ado, let's get started with our SQL end to end project. Okay, so usual setup for our SQL projects. I pretty much use the same setup in all the uh videos that I make on SQL. So we are in Google BigQuery which is by the way a platform that is used by real companies and that is why I I love it also because there's no installation needed. You can access it from the browser and u is all free. Uh so pretty easy to set up and start coding on uh with SQL.
The data that we're going to use for this project is available to you in the video description. So obviously if you download it, upload into BigQuery and follow this project obviously going to be able to replicate the full project and then uh potentially add it to your datalics portfolio. So that would be my suggestion.
So what I've done now is uh I already uploaded a CSV file. Again, how you do this is super simple. You open your uh Google BigQuery. In this case, I have this uh folder called SQL practice. And then I do create a table. And then in here you can uh create a table from a upload and in this case you're going to select uh the the file the CSV file you have to give the name of the table and then in advanced options you have to uh auto detect the schema and then uh let me see. So actually let me let me do it just to show you one more time even if I upload this already. So uh in so yeah you're going to click here auto detect schema and then uh the header rows to skip here you will need to put one just because the first row will be our header so we uh just want to skip that and then you're going to click on create table and once this is done going to quit it just because I have it already you should see your table here and then if you do the three dots here and then query this is uh what's happening so is opening a tab let Let me close the previous one is opening a tab where you have your query. So I'm just going to put the asterisks here and then run this query. And as you can see here, this is our data that we're going to work on. So we have event ID, user ID. So this is like user events. We're going to actually work in this case with e-commerce type of data and we are going to do um actually like a salesfunnel analysis.
So the reason why this is very interesting is that this is connected to a real domain like a real space where you can apply your datality skills. So we're going to work with SQL but we are going to work in the uh sales domain and trying to think like a sales expert like how to give recommendation as a sales expert to uh the the sales team and this is exactly how we use SQL as a way for us to find insights and then re really give value to a business. And so if I go back to the data that I was uh checking before, we have event ID, user ID, event type. So we have like page view, add to cart, you can see some like e-commerce level information, event date, we have the product ID like probably the product that we are uh referring to in the uh in the e-commerce the amount and also the source for the traffic. So definitely spend some more time uh on your side in case you want to get more familiar with the data. I'm going to get fast here and actually get started with our first analysis that we can do here.
And actually the first thing that I can do here is to define our sales uh funnel and uh the uh let's say the different stages of the funnel. So here I'm going to start with a city. So I'm going to do a wid and then funnel stages as and then inside the brackets I'm going to put our city. So in here I'm going to do a select statement and then here I want to count the user ids that are going through the different stages. So how do we do this? I can do a count distinct and then for the first uh stage of the channel. So and and here for the first uh uh stage of our funnel I'm going to check page view because that would be the first uh stage that I found out by looking at the event type. So I'm going to do case when the event type is equal to I'm actually going to take it from here page view. Then I'm going to do user ID which is going to be the thing that we count in this query. Whenever I do a case statement I have to remember to put the end of the case statement in the same query. And then this one I'm going to call it stage one and then views. And pretty much now I'm going to do exactly the same thing for the other stages. So let me actually copy paste this one in here a few times. Okay. So we have page view. Then we have uh add to cart. So I'm going to add it here. I'm going to do stage two. And I'm going to call this cart. Then we have the checkout start. And I'm going to call this stage three checkout. Then we have uh payment info. This is going to be stage four and then here payment and then we have the actual purchase and this is going to be stage five purchase. So we don't need the last one. So all of this will need to come from our table which is this one that I have in here. So let me paste this one here. And also in this case I want to focus our analysis on the uh last 30 days. So let me also add a bit of like a where like a bit of a filter for the the days that we are uh doing this analysis for and thankfully I have the event date that we can use in this case. So I can do uh event date and this will be higher or equal. And here I'm going to do a date sub and then open brackets. I'm going to take the current date this one here. I don't need anything inside the brackets. So this one and then comma interval 30 days. And I think I'm going to put everything here with a time stamp. So let me add two brackets here. So just to give an idea what I've done here is basically saying okay take the current date and do only the last 30 days. That's uh the the thing I'm doing this query and then if I'm happy with this query I'm going to do a select star from and I'm going to put the name of our city. So let me do this part here. Uh let me increase the size and let's see if this one will run. Well actually I didn't put any commas here. Right. So let me add all the commas. Let's see if this one is running fine. The dates sub by Mr. uh underscore here. Let's see if this one is running now. Yeah. And also put days. I think this is instead. Let's see if this one is running. Okay. I think this one we got the result. So basically what I'm doing here is counting the user ID that are going through the different stages. So stage one view we have uh more than 4,000 uh people here. Stage two uh 1,300 then 950. So you can see basically a bit of a funnel. So you know we are losing users as we go down the funnel if that makes sense which obviously uh this is just a query for me to check okay we have a funnel you know starting broad and then shrinking as we go uh you know bottom of the funnel. So that that makes sense.
Now that we counted let's say the users we can actually uh find uh the conversion rates. So uh conversion rates through the funnel and basically I'm actually going to uh take the query that we had before because the fin the final stages I can keep the same city that I had before and here instead of doing a select star I can actually uh change things a little bit and be like okay I want to see uh stage one views which was this column here. I can do stage two cart and then for the conversion rate I can simply do again stage two cart times 100 over the stage one views and this would be we can call it view to cart rate and probably we can round this uh division here that we do okay then I'm going to put here so we did the first conversion rate then I can put here maybe the stage three checkout in here. I can copy paste what we had in here. And this time I'm going to do stage pre-checkout over stage two cart. And this will be cart to check out rate. Then pretty much the same thing for stage four. Again pasting what we have here. And this time do stage four payment over stage three checkout. And this will be checkout to payment rate. Perfect. Then we can do stage five purchase. Then again pasting this one here. Stage five purchase over stage four payment. And this will be payment to purchase rate. And then potentially we can do the let's say the overall conversion. So we can take this one here and then stage five purchase over the total amount of views that we had. So stage one views and this can be let's say the overall conversion rate if that makes sense. So from funnel stages let's see uh yeah let's try to run this. Okay, so seems to be working and so now we can check exactly the conversions between the different stages and see how efficient is our funnel. So I see view to cart around 30% uh card to checkout 71 uh check out to pay 81 uh payment to purchase actually 92.
So what are some insights that I can give to a potential you know sales leader or sales stakeholder. So in my point of view and again obviously that is a assumption like feel free to even like send me comments with what you think about this stuff but payment to purchase 92% it means that we don't have any technical issues for people to actually make make a purchase when they made the payment. So that's perfect like we don't have any bottlenecks. It's a very good rate. Can we can we improve this even further? Potentially we can investigate but that's to me a very good uh uh good thing for the for the funnel. the probably the lowest conversion rate would be view to cart rate. So people are viewing the website the e-commerce website and not really going into the the cart which can be expected. So you know a lot of people are just browsing around without actually putting stuff in the cart. But uh I guess the challenge would be can we do something to improve the maybe the website experience to uh increase this uh this rate because maybe you know the products are not displayed in a correct way or maybe people are not finding the stuff that they are looking for immediately. So this would be something that I would definitely flag uh as a again point of attention and uh potentially giving some recommendation in the same way that I try to explain right now to you.
Okay. So uh that was interesting and then now what we can do is actually um try to compare the different uh marketing channels like the the source of our traffic. So we can do a funnel by source. So again I can have my city doing with source funnel as cuz we need to clean uh the the data bit before uh actually analyzing it. So here I'm going to uh do everything by traffic source and then comma here. Let me actually grab again I'm trying all the time to uh grab things from what we have done already. So let me grab this one here cuz we're probably going to do something similar. So I don't need the select. Okay. So here I just want to count people. So, I'm just going to do views and then cart and then and then I think I'm going to be uh checking the actual purchase. So, let me do like this purchases again for the last 30 days. So, that is uh that is perfect. And then in here I have to definitely remember to do the uh group by. So, group by and I'm going to do the traffic source. By the way, if I want to check if my uh city is uh working fine, so for example, I already see that I have a select statement missing here, I can run the city itself uh as a kind of a intermediary step to see okay like I'm having what I want and then now I can keep going with my analysis. So from this uh city what I want to do is uh select again the traffic source. I'm going to select the views basically pretty much the stuff that I have in here cards and then purchases and then I'm going to take what we done before. So just to take the operation that we done before and then I'm going to do cards overviews and this would be the cart conversion rate. And then I'm going to do the same for purchases over carts. And this would be the purchase conversion rate. And then potentially I can even do the uh purchases over cards. Sorry, uh purchases over views as well. So I can do purchase. So uh yeah, this we call it purchase conversion rate. And this one maybe we can call it uh cart tool purchase conversion rate. Okay. I think this one is maybe more making more sense. We're going to select everything from our source funnel city. So, this one will be from source funnel and then uh let's see, we can probably order by purchases cuz these are obviously the stuff that we care about in the sending order. Okay, let's see if this one is fine.
Okay, so just to give an idea of what we've done here, we are basically saying, okay, this is the traffic source of uh people coming to our website. So they're coming from organic, paid ads, email, social and we see like the number of views like people uh actually entering the website and start browsing. So massive number on organic that's pretty positive but also on social media. Now the stuff that we actually care about is not only people coming to the website but the actual purchases that we have and so if I expand here and so if I see the actual conversion rates I need to see like which channel is uh performing best. So if I do the cart conversion rate which would be basically you know the people that have viewed the website which are the people that are adding things on the cart then I see that for example social media is way lower compared the uh to the other um traffic sources and actually email is performing super super well like with the 63%. And then purchase conversion again uh I can check these rates as well and be like okay again email seems to uh performing pretty well. So uh and maybe like the car to purchase okay is something that potentially uh you know it's giving me some insights but I can already like see the bigger picture here. So uh again what is my suggestion what what would be my suggestion I think things I'm seeing from the data insights to the business would be okay social media is bringing in a lot of traffic and a lot of views. So that's perfect but potentially is a bit of a vanity metric that we are seeing here. a bit of a like a higher number that is not really converting into something useful for the business which is the actual purchase. Whereas what I see here is that email is uh bringing in less views but those views like those people that are coming in are actually purchasing stuff and like the conversion rate is way way higher. So probably we should you know potentially put less attention on social media uh cuz that traffic is not converting very well but they're actually doubling down on email. So that's a massive insight that I can give to the the business backed up by uh by our data.
Okay. So this was uh pretty interesting. So let's proceed with the next step of this analysis. Now uh for example, we can do the time to uh conversion analysis as well. So how do we do this? I basically want to analyze the time that was that is spent by people in all the uh in all the fun stages. So again I can probably start from our uh previous city just again to start with uh something and save us some time. So this case is uh this case I'm going to call it maybe uh user journey and here I'm going to select the user ID and but in this case because I want to I'm interested in the timing uh this time instead of the count I'm going to do the min and instead of the user ID I want to uh well in this case I'm going to do uh event date and this would be the uh view time and I can do pretty much the same for the other uh select statements. ments as well. So I'm going to remove the distinct. I'm going to put the event date. This should be the uh cart time and this should be the purchase style. Then okay, we have the again the 30 days rule. Uh the group by this time we have to do group by user ID. Then here I have to do the having close because I'm interesting only to see when the has been an actual purchase. So I'm going to do a having and then I'm going to pretty much use the same logic as in here. So let me paste this one here. So event type purchase then event 8 uh end and then in here I'm going to add is not null. So this is basically our user journey where we count view time car time and purchase time. Let me remove some extra spaces from here. Okay let's see if this one is working fine. And I think we have some issues. Yeah, there is a missing parenthesis here. Let's see now.
Okay, so basically for each of the user ID, we recorded the minimum view time, car time, and purchase time. So that's uh that's perfect. When there was an actual purchase and then from the user journey. So I'm going to do from so I'm going to do from user journey. Here we're going to do a select and then count star as. So this would be the converter uh converted uh users and here potentially we can do the average of the timestamp uh time stamp diff and here as suggested by the query we have to to uh cart time in our case view time and then I want everything in minute and this would be as the uh average view to cart minutes with a comma and then probably this uh we will need to round it let's even add a round and uh round here two decimals and then this one I'm going to copy paste it and then we're going to have here purchase time so this would be purchase and then car time so this would be average cart to uh purchase minutes and potentially we can do even the overall uh you know total journey minutes. So we can do average total journey minutes and this would be the purchase time which is the plus bit and the view time. Okay, we got it. So let's see if this one is working fine. Okay, so we miss a comma little comma here. Let's run it again. And basically what I'm seeing here is I'm just trying to see like the time that people spent on average in uh in like going through the the funnel. So we converted 700 people and then view to cart would be 11 minutes. So from the like people viewing the products and adding to cart. So I think that's uh I think is realistic. Cart uh to uh purchase 13 minutes. So I think that is okay. I am not seeing like anything very very weird and like the total journey on average takes 24 minutes. So again, we can potentially say, okay, do we actually expect people to spend like 24 minutes on average to do the whole journey? Like can we make this uh faster? That's probably like another uh you know, question or insight that I would give to the business. Uh for sure like I'm not seeing like crazy numbers here like you know hours for specific journey or even like a very small a small amount of time which might indicate you know maybe we have bots on on the website. So not like nothing like a red flag but uh something that again I can talk to uh to someone in the business to understand okay is this expected is this something that we should check and so on.
Okay. So if this was clear I think we can do one more step in this analysis and actually look at the uh revenue in the in the funnel. So we can do a bit of a a revenue funnel analysis. So, as always, I think I'm going to get uh at least this part of the query that we can uh start with. So, let me do this. This one I'm going to call it um funnel revenue. We don't need the user ID. And in this case, I'm going to do a count. And then in here, I'm going to take this tint. And then I'm going to put the user ID as we had before. And this would be total visitors. Let me remove this one. And actually, I can copy this one down. And I'm going to do purchase. And in here, I'm going to do total buyers. And then I'm going to take again this line. And now I'm going to calculate the total revenue that we made. So going to do a sum when the event type is purchase. I'm going to take amount. And this will be the total revenue. And also I'm going to count our orders. So I'm going to do a count when event type is a purchase I'm going to do I'm going to put a one and this should be the total orders and then from our final revenue I think I need to add the bracket. So from the final revenue I'm going to select in here is uh basically some of the stuff that I uh calculated before. So I'm going to take total visitors. I'm going to take uh total buyers. I'm going to take uh total orders. I can take total revenue again. And then I can do also total revenue over total orders, which is definitely like a bit of a finals KPI. And I'm going to call this average order value. And then if I paste this, I can even do total revenue over total buyers. And so this would be revenue buyer. It's another finance metric and potentially also the revenue per visitor. So I'm going to do revenue over total visitors and then revenue per visitor. Okay, that's a interesting one that we have in here. So let me try to run it. Okay, I didn't use the round. So we have a lot of decimal places here, but for now we don't really mind.
Now I guess the stuff again the insight that I can give to a business is by looking at the average order value. And so when we look at the average order value, I can also uh compare it with the uh CAC which would be customer acquisition cost. So again let me give you an example. For example, if we uh if the business uses uh paid ads which is a traffic source or definitely using it if let's say that uh you know to acquire a customer using the paid ads let's say that it cost $50. So you know if we spend $50 we get one customer. Now if the average order value is 107 it means that actually we are you know doing pretty well in terms of like final profit because we get 107 on average for the order value and then we spend only 50 to actually get the customers in. So that's a obviously positive thing but on the opposite it it can be the case where we spend maybe $150 to bring a customer in from paid ads but then the average order value is 107. So basically we are at a loss and so we can really understand you know the efficiency of our marketing paid efforts with uh this insight that I uh showed you with with this SQL query.
And so probably the end result of this I could you know collect these uh insights that I just found out with these SQL queries and potentially compile a bit of a document to uh to put all these things together. And so I actually went ahead and uh potentially you know brought together some of the stuff that we discovered. And so these are actually like three different messages to three different stakeholders. So for example you know I could go to the UX and website optimization team and be like okay you know don't touch the checkout flow because that is working very well. We have more than uh you know that from checkout to purchase more than 80% conversion. So that's uh that's perfect. It's working very well. on marketing. I could say okay stop overinvesting in social uh for sales because uh we have seen that you know maybe the action would be to shift the budget to try to get uh leads and capture emails instead because emails are working pretty well and that's also again the other recommendation you know double down on email marketing which has a way stronger um you know conversion rather than the the social media and then you know for financial and revenue again I could potentially go to the marketing team but also to the finance team to make sure they understand okay like the you know average or the value customer acquisition cost you know are we making money with our marketing efforts or not. So uh obviously this is like a draft text of recommendation. I can bring this into an email to stakeholders. I can bring this into a bit of a slide a bit you know better with some charts as well but that just to give you an idea of you know we started with SQL queries with some uh queries wrote in BigQuery and then we are ending up with you know some actual final recommendation or some practical stuff that I'm going to tell to the business uh which is actually useful for people to uh to be aware of and there you have it this is a end toend SQL project that you can definitely replicate to add to your data portfolio make sure to Check the video description for all the materials that we used in this project.
And in case you are interested in entering a career in data analytics and you want me to support you on a one-on-one basis until you actually land a job offer, then make sure to check my analytics and automation academy. The applications are still open right now. So definitely apply and then I will see if you are a good fit. As always, if you found at least one useful information in this video, make sure to subscribe to my channel so that I can help you even further in the next videos. I will also leave here in the screen some other tutorials that I made with SQL but also with Python. So make sure to check them out if you want some extra materials and well enjoy the rest of your day. Ciao for now and see you in the next.