Data Analyst Project - Cleaning Data with SQL for Insights | Power BI, SQL & Python

hey guys and welcome back to the data analyst portfolio project Series in this episode we are diving into the first technical part of our marketing analysis project today we're going to look at extracting and cleaning marketing data using SQL we'll go through different SQL Transformations that will be applied to each table in our data model before we bring the data into powerbi we'll break down the SQL queries step by step explaining each transformation and its purpose if you haven't watched the previous episode where we Inu the project and discuss the business case then make sure

to check that out first you can find that in the description I have the links and all the information to be able to find that there that being said we are ready to take a look at the queries and the Transformations that we're going to do in this project okay guys so here we have some different SQL statements that we're going to walk through um we're going to break them down and I'm going to explain as we go we're going to start um on a little bit of on the simpler side and then we'll get

a little bit more advaned as we progress through the different statements um what's important is that there is a database called portfolio projector marketing analytics you can find the the link for that in the description of this video you need to restore that backup file onto this SQL Server Express server that you have so that you you can get these tables available for you also locally um that is what I show in my video on how to set up and create a practice environment so if you haven't done that make sure to go back and

check that video that's also described in the description so before you can do this before you can follow along you need to make sure that you have this database attached on your SQL server and and you can find all of the instructions to to do that the first statement that we will walk through is the one that is on our product data now you'll see in these statements that there is a top part and I've added some like a almost like a divider um I will share these SQL queries on my GitHub again Link in

the description everything is there this part above these starts will not be there that's just for me to to go a little bit back and forth to show you guys the table before and after but just follow along um if you have any questions ask in the comments and and I think this is going to be quite useful um I've also tried to really add comments on the different parts for you guys to understand you know what is going on here but let's just start by looking at the products data what does the products table

look like before anything has been done to it so we're going to mark this up and execute this part so here you can see we have a product ID we have a product name we have a category and we have a price now right away you know this table is fine as is one thing you're going to see is that I am going to comment out the category column because I think it's a bit redundant when we have just products and all the all the categories is just one um so I'm going to remove that

in our final query then there's one thing that I'm going to do and that is I'm going to case this price this column and I want to make a category for when the price is above or below something or within certain certain amounts I want it to go low medium or high and I'm going to store that or we're going to turn it into a price category column now the values that you decide here I'm going to let you guys decide that um you know however you want but I recommend that you just you know

you you write this um statement out and try to understand what is going on in because what we're doing we're we're doing a comparison we're doing a case and when something is fulfilled then categorize it as this text when it is between these bands this text else this text and end it and return it as this column which becomes price category um so it's fairly you know this is a Simple Start you know we're going to start a little bit soft with a a query here um there isn't anything else that we're going to do

on this table but if I show you guys if I run this now and if you're new in SQL Server management Studio the part that I mark is what is going to be returned in this window this result window down here so I mark up this query now execute and now you can see that we have this new column we have this price category column and you can see that there's a high medium low and the the point of doing that is just if a company wants to have an easy way to understand how many

products are selling with a certain within a certain category it's easier to have some some buckets than having these these values and having to just try and understand for yourself what is high medium low um so I've just decided to add that uh that category there also uh you could also just go contrl a select then you will see both you'll see the the the first statement which is this one and the bottom statement which is the result of this one now like I said on my GitHub I'm going to add these SQL statements there

it'll be the exact exact same statements but it will be without anything that is above this star and the Stars we don't need that this is the the statement that uh that will produce this result and this is the statement or the result of this SQL query is what is going to be used in our dashboard later but I recommend that you know you guys go in look at the video try to to to write the the statement yourself look at the different comments that I have added and try to understand what is going on

here you can see here it's the part where I commented out the category because I thought that was a little bit redundant um now it's back but let's let's take it out again um if you get other ideas of things you want to do then please you know go ahead there's no right or wrong answer here what's important is that the the steps we are applying in this SQL statements is going to make our our um opportunity and use case more analytical driven and better later for the for the dashboards so so we're going to

add and do some improvements here so that the output becomes better cleaner more structured in the in the parbi dashboard at a later stage um but this is the first one this is the one that relates to product information um great the the next one then is going to be about customers now this one is a little bit more a little bit different what we're doing here is we have one customers table and we have one geography table and I'm going to Mark both of them we'll get two different results so you can see we

have a customer ID we have a name we have an arbitrary email gender age geography right this geography ID is going to correspond to an ID in the geography table and what we're doing here we are trying to create a a combined table so like I said this you know the stars and above that's just for me to show you guys below is the actual query that we're going to uh we're going to focus on um so we can then just go go back to let's say if we have customers that's what that's look that

look like by itself or we have geography this is what that looks like by itself now if we go down you can see this is a uh a join a a u statement which is going to combine these two tables so we have a select and we are selecting from the customer table as C where we're going to Alias all the columns that comes from this table in the statement and then we're selecting from the geography table as G so you can see customers over here that's this table geography over here that's that table and

we have a left join on this condition where geography ID from the table which has been aist with a c onto the geography ID on the table which has been alist as G which is geography customers we are going to then read it select customer ID customer name email gender age with this Alias to to kind of tell the select statement which table does this come from then we're going to add the country and city from this other table on this condition so this is a left join um if you want to you can go

in and you can change it to a right join actually let's first look at the result of the left join before I start uh changing the join uh criterias so we can mark this part off execute and now you can see we have a table which combines customer information and geography information so in this Dimension we are doing a join and it's because we don't need two different tables we can combine them into one fewer tables in our data model more efficient um it makes sense it's fine this is the combined result that we want

to get into powerbi at a later stage when we're going to do some uh some analysis this is a left join so these two columns have been have been added to the left table which is the first one in the in the join is customers just for the sake of it if I comment this out and I do this as a right join um let me see if I can get this part here you can see now it's going to list this the cities first because those are the values that comes up in a geography

uh the geography table first and then it adds whatever matches on that from the customer table so this becomes the first table on the right side and is joined into that and then there's an inner and full outer join just some other uh other types um in this example it doesn't really make any any difference but I've just added those there in case you want to comment in and out and and just look at it um but the purpose of this one is for you guys to just do a quick practice on selecting two different

tables combining them and again from a more uh sensible point of view why are we doing this it's because we don't need two different tables we can combine them more efficient few tables in a data model it's it's you know it just makes sense um here you can see we have an age um we have a let me let me get this like this we have the country City if you want to you can go in you can uh case the age column into um let's call it you know different categories of of age like

young um middle age old or some some other ways of putting it um or you can do countries uh northern Europe Western Europe Eastern Europe southern Europe if you want to I'm just trying to come up with some some ideas for you guys you could have city um you could categorize them into low uh sorry large or small based on number of uh people that lives there there's different things you can do here to to categorize it or or put it into a context which means that you can use it more in a different analytical

um point of view and that is what we're trying to do with the the SQL here um so we have so far we have two Dimension tables they are going to become um what we call Dimension tables in a data model or lookup tables so if we have U if we have uh let me see if I can draw this up you guys say we have a data model um and we'll we'll come back to this later in pobi um but let's say we have you know we have let me find I haven't done this

forever that's for sure let's go okay so if we have customers geography and then we're going to have some tables down here excuse my paint drawing skills um so these are going to be Dimension tables and they're going to filter down onto these tables they're going to filter the fact tables which we will just get to um this will be a little more clear when we get into parbi I'm just I'm just trying to get it out there already we have some tables down here which are fact tables and some tables here which are lookup

tables or Dimension tables so far we have done category changes and often you do that on Dimension tables or we've combined um different dimension tables often something you do on Dimension tables um now we're going to go into the the tables that will hold the calculations and what we call facts so the first one we can look at is also a simple example it is the one that has some information about customer reviews so let's take a look at that one um you can see we have a a IDE of the review we have the

the customer which did the review the product ID to identify what product was the reviewed on what date what was the rating and what is the text now the issue with this one is that in in some of these reviews there are is a double spacing instead of a singular spacing um so I've just added a simple replace on double spacing with with single replacing uh sorry single space that's the only thing we're going to do on this one for now um so we can do this we can select it you can see that it

it looks a little bit cleaner so we've done a small small uh fix there um this is also the table that we're going to work on in Python later we have a rating and we have a text and what we're going to do in Python is something called sentiment analysis I'll just very quickly explain what it is uh we will try to determine if the text in this text is positive or negative and we're going to combine that with our understanding of the review text and the rating that they gave to see if we can

uh make it even more accurate or or some other types of information um so that's in the next episode but I thought I'd just explain to you guys what we are uh what we're going to do uh but that's what this looks like this is a fact table which means we can we can have an average of rating um when we get the sentiment in we can have a rating sentiment score or a rating sentiment category um like I said we'll come back to that later but here the the rating and what they have written

is something that we want to understand based on the product or the customer that did it so the customer and the product that gives us context who was the who was the customer which city is he from um how old is he what genery uh the product tells us about the category of the product or the product type or the the low medium high products that will then be used to filter the rating to understand how this comes together so so this would be uh one of the these tables down here let's say it's this

one okay so the the next fact that we're going to look at is the engagement data one um and you can see you know this is what it looks like before we have done anything to it and we're going to do some different things here just to point out you know some of the issues and what what we want to try and fix or improve if you can if we can call it that one thing is that in this content type you can see there are some different spellings Blog video video newsletter social media social

media capitalized I want to make this a little bit more uniform we're going to do that um I want to change the the format on engagement date I think I'm going to restructure these uh these ideas to come a little bit sooner uh and then the views and clicks combine column I want to split that so that's what I've done here and I'll take you guys through that also so that you guys can see what uh what this will be like so the first is just selecting the different IDs first I think that's always useful

um you can see I've used an upper function and I've replaced this way of writing social media with this one and I've used the upper to standardize um the the spelling or let's call it the the the case in the in the the values in the content type column um left of of this combined column becomes views right of the same column is going to become clicks we have the likes common stays assess and I've done a small format and conversion on uh on this uh this column to a date column and then change the

formatting um I've also uh uh filtered out some of the one of the content types because I don't want to bring in the newsletter rows um so you know I have all the comments read through it I recommend that you guys type it out and and just practice that part I just really want to break it down and make sure that you guys understand what is going on here um so so I'll mark that we'll execute it and this is what it's going to look like afterwards so you can see now you know these columns

are coming first standardized content type spelling views clicks likes engagement date this is what is going to go into PBI um and we've introduced a couple more SQL Concepts now a little bit more advanced but still I think it's uh I think it's uh manageable um if you want to see the query like I mentioned check the description uh you'll find all the links to to find these there um last one is this this customer Journey table and this one is a little bit more advanced um so I'm want to try and take you guys

through this one and then we'll uh we we'll discuss as we as we go if we start by just looking at this customer Journey table by itself it is showing different IDs the journey ID customer product ID the date they visited different stages and an action that they did this is just to look at different customers and Journeys that they have done in a funnel and see uh did they view a product did they click did they buy something you know where did what happened and we have this duration column which is missing some values

some places so we're going to try and fix that um the other thing is that there are some duplicates in this table there are some some rows which has the exact same um information so we're going to try and I'm going to show you guys that first and then I'm going to talk you guys through how you know one way of of doing something about this so this first part this first part of the statement is going to show you guys uh which which rows has exactly identical information which is duplicates and that could be

a data quality issue now you can see it say it says here you know where we're selecting a bunch of columns and I'm going to go into this but let's focus on this one especially um we've given a row number which means that if a row has a unique combination it has been given a number and if that number is greater than two it means one row has a unique combination of customer ID product ID visit dat state page in action on more than one row I'm going to show it you guys in a in

a second but let let's just start by commenting this out and this is a comment table um expression which gives us a temporary this becomes a temporary table which we can then run a query on we can use this information which is only there in during the query during the execution and then it goes away so we we get a result for this and then we pull that with this selection so you can see with duplicate records as something some conditions we're going to select from that and do an order but that's you know one

one step at a time so we will mark this one off and then execute it and then you can see here so you can see there's there's a bunch of different row numbers going downwards and they're all one but this one this is our first example you can see the information on this row is exactly the same that's why it has this is the first occurrence of this unique combination and this is the second occurrence of this unique combination and that that part is done here it is doing a row number over the partition over

the let's call it the the combination of this these unique fields and an order by this ID and give it a row number so if it's only one combination that is unique in this partition it's going to get one but if it's two then it's going to get two so this first part is only for us to verify sorry let me get the whole thing only for us to verify that there are some duplicate records um I'm letting you know that there is because that's part of this exercise but this helps us um create a

statement which can identify how many you you know how many unique um com how many how many rows do you have per unique combinations and if you have one row it gets only a one number but if it gets two it means there's two rows that has the same unique combination so if I then take this back in where row numbers is greater than than one it's only going to return rows where this happens where there is a unique combination which has gotten a row number which is greater than one which means the unique combination

has been repeated more than once so we have this statement with a temporary table we run this statement on top of that temporary table and it verifies that there are 79 rows where there are duplicate records so that's just the first part this is just a verification that's called a verification query this bottom part is what is going to to fix this and one more issue the other issue is that there are these nulls where they're missing a duration so we're going to have a subquery this is a subquery this let me get this this

is a subquery and that is a query where we're going to get the average duration over the Partition by visit date so for each visit date we are going to figure out um what is the average duration and if we're going to use this part to look at duration if there is a null then put in that average duration amount into the duration column or else just keep the duration amount that was there so if this one is no I want to know what was the average duration for that date and use that value there

because I don't have anything else there so that's the best I can do and that is based on this this uh this this subquery right here so it's a cols as duration from this and then we have the the row number one that is also happening here to to identify duplicates so this one is going this query is going to remove duplicates and it's also going to add an average amount into our nles um so if I go select on this one and I execute that you can see that then we you know we shouldn't

have any uh duplicates and if I you see duration you can see there are some decimals here now these are decimals that have been entered as averages on those days where there was a missing duration um so row numbers equals 1 and let's see if I can do this equals I think it was 23 so if you remember um Journey ID number 23 previously had two rows Now it only has one row so we have confirmed that there are no duplicates here so we only keep this part let's go back like that and if you

want me to verify that for you um we can go back and select our this sare you can see Journey ID number 23 had two rows before because there's there the row number two exists because there are two rows that got um assigned A1 and A2 uh as the as the combination is uh unique across two rows um so this is the one that is going to go into powerbi for this table uh now this one was a little bit heavier here we are trying to handle some more complex stuff um I've tried to add

comments to what is going on I really recommend that you guys go in you know open this SQL query and try to uh try to look through it again remember that this part is for me to show you guys the table before this part is just for me to show you guys a query to verify duplicates it's good to have but later what we're going to pull into parbi is this part because this is going to be the part that is going to actually uh return the data as we want it um I decided to

do the partitioning by visit date as as average duration if you want to do it for something else different columns try to enter you know product ID or customer ID you'll see that the duration is going to be a little bit different um but it's important that you understand what it is doing um it is the same with this row number uh try to you know understand you know when it says Partition by this this this this and this you know it's it's doing it across a unique combination of those and if you have two

rows which has the exact same information on both rows it becomes a unique combination twice and that's why you have a row number which becomes one and then becomes uh two on the second one because you have two rows with the exact same information on them so these are the different tables that is that that that that is going to become our data model in powerbi um you know take a look at them uh different concept that goes into each one um if you have any questions or or you know it got very complicated let

me know in the comments um if this part got you know difficult then then you know take your time to to try to understand what are the different uh different parts um you know I hope it is it is understandable I understand that there is a certain level of complexity here but I'm I'm trying to make this you know something that um exposes you guys to to different concepts and different things that I think I think it's useful to understand um in terms of SQL and what we have to do with the data sometimes to

to get data into the analysis tool which we can use to to find you know information and insights and stuff we can bring back to business so that they get some value uh of this data with and without the shortcomings that we're trying to trying to work on with our SQL in uh in this project that's it for our episode on extracting and cleaning marketing data with SQL now you know how to connect to a database and SQL Server management Studio you know how to extract relevant data and apply some cleaning techniques to prepare data

for analysis in powerbi in the next video we are going to enhance our data by performing sentiment analysis on customer reviews using python which will add valuable insights to our marketing analytics project make sure to like subscribe and hit the notification Bell so you don't miss any of the updates on this series if you have any questions or thoughts then leave a comment below I'd love to hear from you guys in the comment section thank you guys for watching and I will see you guys in the next episode