End-to-End Data Analysis Project 2024 | SQL & Power BI | Beginner Friendly

welcome to the data analysis in toin project where we're going to be building a database based on some Excel files connecting that database to powerbi and creating a powerbi dashboard with some animations and some other cool features so here is our data analysis workflow we are going to create that database develop the SQL Creator connect that powerbi to our database build a dashboard and powerbi and then we're going to answer a central analysis question that we have gotten from our stakeholders so here is the email request for the dashboard and the data and it's usually not disorganized but this will give you an indication it says deer data analyst we need your expertise to develop a dashboard for toan bike that displays key performance metric such as the requirements indicate hourly Revenue analysis profit and revenue Trends seasonal revenue and wrer demographics this is something that needs to follow the company colors according to the design and Aesthetics we are going to use the data source which is the database that we are going to create and I'm going to show you how to do that step by step we are going to need to deliver this asp ASAP and then we have the central analysis question which is please provide recommendations on raising prices next year so that is a central analysis question this is a great project for anyone who is new to data analysis I'll show you step bystep how to do everything so let's get started we need to install SQL ser and SQL Server management Studio we used to be able to do these all in one so this is going to be the SQL Server management studio and the preview screen is going to be your server so you can think about we're going to get the server and then we're going to download the platform on top of it so you're just going to get the express version here you can see it's free so you're just going to download this at the link and then it's going to go and download that in your browser it's an exe file so if you end up clicking on it it's going to just going to allow you to install it we click on that let's let it load and this is just going to install the process just choose basic for your installation type you want to accept the agreement and then we are going to initiate the install and now this is going to take a little bit of time to install so I'm going to to let this install and then we can move forward with everything after it's done so we get to this screen where you can see it install the 2022 version successfully and now what we can do is even here we don't need to go over to the other screen because you can see it says install secret server management studio so from this window we can make the installation uh here are some of the things that we'll look at a bit later in terms of versions so I'm going to hit this install SQL Server management studio and you see it took us to that same screen we saw before and I'm just going to go here to download SQL Server management studio and we should be able to here so we're going to download the version here you can see it's version 20 and we could see it downloading in the corner here now that that's down loaded it's an exd file so I'm going to click into it so it should execute and you can see here's a SQL Server management stun release number 20 I'm going to install that and then we're going to be given to the screen just hit yes so it's going to take a little bit of time so I'm going to pause it and then come back to this screen once it's done so you see here we're all good to go we can close this and then we can launch the program so I'm just going to go into my search and go to SQL Server management studio so I'm using version 20 you see I have the previous version 19 so I'm going to get the new version that we just installed which is 20 and you can see the version there and you can see would you like to import the user setting from uh the previous version so for this purposes I'm going to start fresh so we're all on the same page so I'm going to put do not import you won't have that screen if you've done this for the first time so then you're going to be po you're going to be given this screen now this screen's kind of important uh this is going to be the database engine that we've connected to that SQL Server here you can see this is your server name so don't forget this uh it's going to pop up every time you load it so you don't really have to worry so much about it and you can see we authenticate with windows so we're not actually having to put a password in because we've authenticating with our Windows password and then if we hit connect here you're going to get this error so make sure that you are going to click the option that gives you uh the ability to trust this certificate we want to make sure that you hit trust the service certificate go to connect and then you should be good to go and then we will start with our loading in our data so now that we're here you can look over at the left corner here and you can see where we have database security server object replication management an event profiler so obviously we want to stick to this section because we're trying to build a database so if you hover over that and then you rightclick it you can see you can create a new database so we're going to create a new database and then we need to give it a name I'm just going to call this our bike data so I hit bike uncore data and then I'm going to go over to okay and now you can see on the left side here you can see bike data now once you have a database we can start adding tables to that database now that we have our database we want to be able to add tables to this database so that it is populated and we we can access these tables via SQL query so now that we have our database created what we want to do is navigate over to that database and we want to rightclick it and what this is going to give us is options here and we want to navigate over to task and go to import flat file this is going to be able show us the the ability to import that Excel file into a database so we are going to hit import flat file and then from there what you're going to do you're going to be presented with this window you're going to go to next then you're going to indicate where that file is on your system I'm going to go to Bike Share zero which is on my system here this is the first year in the bike data set then what we want to do is Click into that and this is going to give us the schema which is kind of how to access that database and the table this is going to be your table name you can change it to something else you like I'm just going to leave it as that and then I am going to click next now you're going to be presented with the ability to preview the data and then you have this option here is to use Rich data type which may uh provide a closer type to fit now this sometime causes problems so what I'm going to do is I'm going to uncheck that and I'm going to click next and this is going to show you all the data types you and then I'm going to click next again and this is going to show us just a a summary of what's being added I'm going to go to finish and then you can see that operation is complete and I'm going to close this window and then what I'm going to do is navigate over back over to this section and I'm going to hit refresh so that table is refreshed in there under the Bike Share database I'm going to click in into tables and now you can see our bik share database I'm going to rightclick that and then I'm going to select the first thousand rows and then what you're going to see is a SQL query here and all of your data here so now we're going to just navigate over and bring in the other tables in the same fashion so now we have everything ready and we've already ran this particular script we don't need it and we can start building our SQL query from the beginning so what I'm going to do is eliminate all this and I'm going to go to select everything from and then I'm going to go back to that by share a table we don't need to indicate the dbo part because we've already highlighted in the database management system that we're in this particular database so what we're going to do is we're just going to write bike oop bike thereare data and you can see it pops up in the intellisense and you can see the database here so I'm going to click that and now you can see that this is all set now one thing you can do while you're in here if you want to make things bigger you definitely can in terms of font so what I'm going to do is I want you to navigate over to tools in the top go to options and now you can see that you can change some of the settings I'm going to go down to I'm going to go down to font colors and then under fonts and colors I'm going to increase my font size in the text editor to I'm going to say 12 or 13 you can also click into the other parts for example for the grid results which are the bottom section here you can also create a larger size you can see mine is quite small so I'm going to go to I'll keep that at 11 now you can see you're going to get this Ara it says that it's not might not be reflected when you run the query so I'm just going to hit okay you sometime you need to restart that so now I'm going to run this you can already see that this has gotten bigger and if I execute that this still stays small because we need to restart it but you can see that our query is much bigger now okay so let's dive into our query here here so you can see that this is broken up between a select command so what we're doing we selecting from this table we're using a while card here which means that we're selecting everything as opposed to these individual columns and then we're selecting from the bike chair table and this is going to reward us with all of the columns and all of the rows from that particular table now what we can do if we want we can just select a single column so you have a column here called season if I just type in season you can see this particular icon and you can see that this column and the data type and you can see they not nulls there so if I run this and the way you're running this you can use this execute here so if you hover over that you can execute with F5 also and you can see it just brings back that particular table as opposed to the wild card here which is there now you know that we brought in three different tables so we need to start bringing those tables together and the way we're going to do that is with a join so we're going to join those tables we're going to join the cost table but we also have this table here called black share year one and if I just change that and I execute you can see that those tables virtually look the same and that's because one is 2022 which is the Bike Share year one and then you could see here this is 2021 so the way we want to connect those tables it stack them on top of one another and the way we're going to do that is with an Ain and in Sequel the way you Ain is you use a union so what I'm going to do here is I'm going to copy this table and I'm going to make a space here and now we have those two tables if we change this to one and the way we're going to connect that is with a union and you can see in between those two commands we have a union and if I execute here now you can see we have 2001 2021 and then further down we have 2022 so now those two tables are connected as one and you have your union table now we let's talk about the difference between union and Union all Union all is just going to bring back everything in your table this Union will remove duplicated rows and because our rows are unique so if we choose Union all I want you to pay attention to this number down here it's 3 34758 rows if we use Union all let's see if that number changes you can see it's the same which indicates that we don't have any duplicated rows so it's up to you whether you want to use Union or Union all but our data set has unique row so we are good to move forward so as you can see here my table got a little bit bigger I just quickly restart it now that we have this Union table here we might want to take a look at their existing table and this is considered one pull or one select of the data so we can just go here and do another select statement and say select everything from the cost table and as you can see that's that's not popping up and because it's cost table and sometimes you need to specify the schema but let's run this and see if we get a result and you can see here we have two data PS for these queries I have mine price and my cost of goods in this table and then I have this table here so now we have the challenge of connecting those so you can see there's a column here that says year and it's connected with zeros and ones and we also have a column here with zeros and ones so what we want to do is we want to join it to this Union table now we could do that in a few different ways but one of the easiest way to keep your code super clean is we can call turn this into a CTE which is a common table expression so what I'm going to do I'm going to write width and then I'm going to give this table a name I'm just going to call it CT as and then what we want to do is enap culate that table in parentheses now I can select from this table so I'm going to change this to CTE and you can see when I run this it's going to select from that table now once I have that table what I can do is now join the cost table to this CTE table and we want to be able to left join that cuz we don't want to inter jooin we want to left join so we get all of the records from our left table which is going to be our main table here which is a CTE and we want that to match to the cost table and to bring back all the rows from the left and match it so what we're going to do is we're going to start with left join and then what do we want to join we want to join that cost table and then we need to specify the keys to join it on so we want to join it on and a real easy way to not have to write these tables again is you could Alias this as a and you could Alias this as B and what we can say is like that table now is has an alias of a we can say a y r because that's the column here equal b. yr and and then if we run this you can see we get a table back and what we get back now you see the number of rows haven't changed but we get the price and cost of goods connected to that table so now that we have our base query done what I want to do is go ahead and just indicate the columns that I want and I don't need all of these columns so I'm going to just take the ones that are I figure are important to me so let me just quickly do that so I'm just going to get rid of that individual wild card and just list the columns up so I'm going to take the date the season the year and we can always add these later I'm going to take the writer type the writers the price and the cost of goods and the way we do that is just add those in there so you can see they'll start to pop up as you type them so I'm just going to quickly add them now that we have all the columns you can see everything is highlighted here and then you can see that there's a little bit of an issue here where it says that this is ambiguous and that's because we have two columns here where we have the year and the way we can just take care of that it just add that as an A and we run that and now you see we have a table and the reason I added a because this is going to be uh the a that from where we actually join is going to be the a from the table that we appended so it's that first table that we had and we put the Alias so now what we can do is we have this information but I want to add some more we can do a calculation here because now we have price and cost of goods um we can start doing some information and it looks like we we want to multiply see we need to multiply the writers times the price to get the overall Revenue so what you can do is just write writers times the price which is literally quantity time price and I'm going to call that as and then make sure you have a comma after the last one and I'm going to call that as revenue and then I'm going to run this and then now you see that we have the revenue column there another column that I want is profit so I can use writers times price and then minus the cost of goods and I'm going to call this our profit you got to make sure that you have a comma after everything and Order of Operation should take care of this and we should get the profit so I'm going to run that and now you see we have profit so now we can use this information and this query to bring it into powerbi now this is the dashboard that I've created that kind of answers all of the requirements that we've gotten from our management that we saw in the email it's going to be very simple and easy to navigate which was one of the requirements and you can see it as um uh the company logo it has an ability to select between the two years our data set slightly different that we just been working on but the numbers should be pretty accurate you can see this 2011 and 2012 instead of 21 and 22 um so we're going to build rebuild this dashboard with our data it has all the things that we were're at revenue and profit over time of Revenue profit and the customer base and the profit margin there we also will'll have uh the revenue per hour the revenue broken up by season and our writer demographics that they ask for so we're going to just rebuild this and what I need you to do is go ahead and download powerbi desktop on your machine so that you can have this ready to go and then we'll start connecting our powerbi and building this from scratch so I've opened up a notepad here and dropped in the query and I've also opened up a new instance of powerbi that we can use to create our uh query so if you go to this new notebook and you go to get data and we go down to SQL Server what's going to happen is we need to put our server in and our database is optional and then we can either bring in our query here and which we can use that we have just copied so let's go ahead and do that so I'm going to navigate back over here and you definitely have your ability to get your server here uh it's a little bit difficult to copy but if you just copy and click that and you click connect you can see that the server name will pop up so I can just copy that and then I can go back over and go to SQL Server here and then I type in my server and remember we can put our database in so remember that our database is Bike Share data here so I'll go back over and click bike stare data and we just make sure oh it's called bike data so let's just get rid of the share B underscore data and then we can go to advance here we can get our query we can copy and paste that and now you can look at the connectivity modes if you want so if you use import here it's only going to refresh as you indicate when it should be refreshed so if there's like upto-date data you will need to refresh that um on demand as for as the direct query any changes in that data set are going to be picked up um and but this kind of May kind of result in slower performance sometime when your deck but in this case we're just going to leave it as import and we have our query in there and then um what we can do here is we can just hit okay and now you see what we've been given we need to give our credentials so remember that we use a Windows as our credentials so you can say just use my credentials and you can see the the server name is here and the table is here if you have changed anything you can use ultimate credentials I'm just going to click connect and now you're going to be given this option to um that's telling you that um about the encrypted connection um we are using encrypted un encrypted so we're just going to go ahead and move forward it's connecting to the server and then you can see that we're bringing in the data that we created with that query and I'm just going to load this and you should see that query pop up here and it's called query and then that's brought in all our 34786 rows and then here's our data so let's go ahead and build our dashboard now that our data is inside so the first thing I want to do is create a header so I'm going to go to insert and then I'm going to go to shape and then I'm just going to click into the triangle oh sorry the rectangle and I'm going to bring that in as a kind of tedar I'm going to change the color of that just by going to the style and going over it and I'm going to pick that dark blue and then we have a header now I want to utilize the cards so I'm going to go over and click into a plank spot I'm going to use this new card here and it needs a value so I'm just going to bring any value in and then I'm going to turn that value off so I'm going to go to call out values and I'm going to take away the call out values and I'm going to take away the label title I'm going to also add an accent bar and I'm going to make sure that accent bar is a similar color and I also would like to have a border which we have and I'm going to add a shadow so now I have this bar here and I'm going to bring this up a bit and I need to also remove this exterior here you can see that is in the way um and I don't want that so what I'm going to do is just hover over to this effects and go to background and just turn off the background so now I have that first section I'm going to copy and paste it and then I'm to pull this over here and then I am going to just reshape it where we have that section and now we have two sections here so now we have three sections in which to build our dashboard so the first thing I'm going to do is add my little animation here so I'm going to copy one of these cards here and then I'm going to go to a you know like a site like you know icon Scout or you can literally just Google animated gifts here Google bicycle animated gifts and you'll be able to click into these or get some or you can visit a site like gify and it will give you all kind of options there I found uh this animation here let me just copy and paste that in so I'm going to use this animation here for my animation I'm going to copy that going to go back over to our dashboard and then here I have this card that I copied let's go ahead and go to card and I want to turn off some of the things here I don't need all these things and accent bar okay so now we just have a plain old image there and I want to go over and let's close this and go to images on that card I am going to turn on my image here and then I'm going to use uh the image URL and I copied that URL I got from giy and then boom there you go you have your animated gif then all I need to do is kind of resize that into what I want and then I'm going to put it over in like a corner here and now you got this animated gift now you can see that you have this border around it so we need to get rid of that so let's just go over and turn off border so that part is done now we want to start building our table here so let's get our table we need our and the table that we need is a matrix so I hit the Matrix Visual and then I'm going to use hour and I want the hour to be my rows and I want the revenue to be my values here and I want the average revenue so I'm going to change that to average by just clicking the down arrow once you drop that in values and then let's change the title to just Revenue just double click it and then you'll have the revenue broken up by hour and then now we need our weekday and we want the weekday to be our column and now what you can see here is you have a table that we have and let's go ahead and optimize this table so I don't want subtotals on the columns or the rows I also don't want any style so I'm going to go click style presets I'm going to go to none and then you can see that gets smaller I want to make those values uh a little bit smaller so I'm going to go to values and then I'm going to go down to eight and that gives us the ability to bring that in now this table is a little bit larger so let's make these a bit shorter I am going to now have this table here but I don't want all of the hours I just want working hours click this off and actually now I want to go over to our transform data because this is a text field and I would much prefer it to be a numeric fi so you can see here the data types so I can try to change it to a whole number here and see if it works and we can see that changes and then now if we click back into this we should be able to bring that in again so I'm going to go back over here go to hour bring hour in again as Row And now when we have it in we can make an easier let's say greater than I want everything greater than 7 a.m. and I want everything less than let's say 9 900 p.m. which is going to be 21 so I'm going to apply that and you can see that table gets smaller the header here and the columns to make that small also and now we have a table it's a bit small so we might have to play with the text sizing later so for this I do want to turn this into a currency so you can see I have Revenue highlighted here I'm going to go over to currency and then this could give us um everything in currency but you see it gives us all these decimal points I don't really care about decimal points for this so I'm going to leave it at zero so you can see I changed that to zero and now you can see that table it gives us much more room and we can play with the sizing a bit later you might also want to think about adding some conditional formatting here but let's finish our dashboard first I want to start adding some text so let me just quickly add some text I'm going to go to a text box and then I'm just going to use the tooy or whatever you would like I'm going to use tooy bold I'm going to change the size and I am going to put that next to your bike and that this just kind of a call out title that we can kind of use we can remove the background and stuff if we need to so now we have that part we can add the text that's going to go under this which is just going to be kind of a summary of this table uh I won't bore you with me writing that I'm just going to copy and paste it in next let's start building our Revenue over time so make sure you not clicked on any visual here and you we don't want to be clicked on any one of these cards because we want to add a new visual so we're going to go over to this stat not sorry with this line chart here with clustered columns and you can see it gives us a new chart we have our column here which is the date and you can see by the column icon here and then what we want to do be able to do is we want to have writers as our column so you can go here and you can take the writers and we want that to be our column on our y AIS and you can see when you do this what happens is it's going to be broken down in the year let's take it down to month and we don't actually need to have quarter here because you can see I broken up in quarter so you can go over to the x axis and you can click that off and that's going to be our the first part of our visual here now that is going to be the writers and now we still have a data type issue so you can see that is coming in as a count and that's not what we want so let's hover over this and let's go over and turn this into a number we want to turn it into a whole number as we have clicked onto it we go over the whole number and click yes it's just going to give us a update there and now you can see this summary symbol on writers now if we bring that into our column as the Y AIS you can see it has much more of a seasonal shape which is one of the things that they wanted from us anyway to know what the seasonality was so now that is the first part of what we want to do we can also make this a little bit nicer by going over and clicking into the paintbrush going over to where is it I column and now this is a color that we have but it's not the color we want we want to click over to where it says conditional formatting next to which is that function symbol there and I am going to choose uh the light gray here and then I'm going to choose dark blue the blue that we use for the heading and take a look at what the field is it says what field should we base this on we want to base it on the riters and now you can see that it's going to be based on the sum of the writers and if I hit okay now you can see that looks a little bit better now let's also come in and go back over to this section and we want to bring in our profit as one of the lines and then we also want to be bringing in Revenue as one of our lines but probably not some let's do average so let's go average and then average again and now you can see those are there what we want to do is go over to your visual here and let's kind of improve the way this looks a bit all right let's go to Y axis so here you want to go to your secondary y axis and then we'll put this on align zero so there we go and then let's navigate down the lines I want to turn on markers for my individual we're going to go back over here we change this to writers just by clicking into it I'm going to get rid of some of the the title for the xaxis API over time I'm going to go back and change the it's a change that color so that'll do for our bar over time and now we can easily get our other visuals so all we need to do is go to season make sure you're not clicked on any visual I'm going to choose a horizontal bar chart here then I'm going to use season that's going to be your y AIS and then I'm just going to put Revenue uh you can do average or you can do everything I am going to go over to paintbrush choose the labels and that's going to give us the average or the sum whatever you prefer I am going to click [Music] into the bar and then I'm going to just change that to one solid blue color and and then I am going to just bring that down here and I think I prefer this to be not necessar the average I'm going to change that back to the thumb you can definitely change that to conditional formatting if you want so there you go and then we need one more visual which is is the demographics of our writer so now I'm just going to go into writer type and then go and click writers then now we have this as a table we can just quickly go over and we can choose a donut chart that's going to give me a donut chart and I am just going to put that here in this section I'm going to get rid of uh the legend and then I am going to work on the slices I want similar color so I'm going to use gray let's use gray for the smallest one and then gray here and then I need to work on the title for my labels here so go to data labels we like that it's outside click and then we want for our values here we want the category and the percent of total so now you can see the casual and the rider and now let's change the title to these two things very quickly now that we have that just let's put a line to separate these two so we're going to go to insert shape I'm just going to get this line here let's change the color so we can go to style go to and I'm just going to choose that blue blue again that we keep choosing which is here then I'm going to go to rotation I'm going to put that at 90° so it's straight up and then this is going to separate our two visuals here next we need to work on our header and the profit margin so you can see as visually looking at these lines I have realized that I made an error in one of my calculations and the beautiful thing about this is we can go back into our SQL code and fix it so I am going to go to trans uh uh transform data and then there's an issue with our cost of goods so I'm going to click in to this and then you see the query is there so I'm going to click into source and let's go back to our SQL query here and then when we created our Revenue column where there's profit we read we minus writers times the price now cost of goods is cost of goods for each one of those bikes so we have X we need to multiply that by writers also and now we're going to just change that and that's going to update our information should be pretty substantial now and then I am going to close and apply and now you can see the real delineation between those two so we will fix this now with coloring so we have that color and let's change so once you go to line you're going to go to average of profit and then go to color and then let's maybe change that to like a yellow or something like that and then there we go uh so now we have that fixed I'm going to shorten this a little bit and move this here and then I'm just going to copy this whole table here and this whole Visual and then I'm going to go over to the visuals and then I'm going to turn this into a new card like this and you can see uh what we have here for revenue and profit and we probably need to change those around to the average of Revenue and average of profit I want the THM so you can see the difference there and then I don't want the writers and now what I want to do is I want to I don't want these to be stacked like this I want to go over and I want to stack them on top of each other so then I go over to the paintbrush go to layout and put a single column let's make these very tight and small so these are too big at the moment so let's go out and let's go to our call out values and then we want to make that sooy also let's go to card and then for the card the background I want to make it this color like that I want to increase transparency the really light color like that and and I want to turn on glow so it sits out like that and then I'm going to just you know make these a little bit better shaped now we have the this card done and ready to go so let's do our header so all we need to do is go back over to that card again and let's get a card and then we want to be able to bring in the uh let's say the number of riders we have and then uh there is a uh profit margin which we need to calculate so we can just quickly do that by right clicking over to our query table and going to our new measure here and then we can calculate the profit margin here by just saying the sum of the revenue in that query table minus profit sum of profit and then we want that be divided by the sum of profit and then I am going to just encapsulate that first part and then we're going to call this our profit margin and it should be okay so now that the profit margin is there we can bring that in as our second part of the data something is not right believe we're missing and it's there this is what I did wrong there we go and then we get that 45 so that should be at 45% now what I want to do is be able to add an image here to each one of these I so we can go to a website called Flat icons and flat icons will have a lot of different images that we can use so you can literally type in something that you want here so we can get icons maybe like registered or something for a clipboard you can see I can get this clipboard here and I can download it download it as a PNG go into free download here and that's going to be one of the downloads now that those are downloaded what you can do you can click into your card you can go to images and where it has applied to the series I'm going to go with the first one is writers and I'm just going to turn on the images for on then I'm going to browse and I'm just going to go to that part that I downloaded here that should be in my download and then you can see that you've gotten that in I'm going to turn turn off the fixed size there and then we should be able to image area size it here so you can see how that works but we'll deal with that in a minute then we want to do the same for the next one so we go back up go to profit margin turn image on browse turn off fix size and then you can play around with it so here I'm going to bring this up to this this section here and I'm just going to adjust the size and turn off the background I'll go into cards you turn off the background uh turn off the borders to effects and turn off that background also and now for our headers we need to change that on the cards so I want to make sure that the values are this color blue and then I want the heading to be white go to label and white going to move this where it's a little bit smaller and put this in the corner some of writers and I'm going to just change this to Riders so just double click and WR writers now we just need to add a title and a logo so I am going to just get a insert here go to a circle and I'm going to make that Circle smaller where it fits in the corner here and I am going to change change the styling here turn it white I'm going to turn a border on and you see it has a border I'm going to turn it over to that light blue and then I'm going to increase the Border size here like this I'm just going to make sure I have enough space to put my logo and the logo I'm just something that I downloaded from Flat icon and then I'm just going to just put this image inside here so that's our bike just go to effects and turn off the background and the last thing we want to do is create a filter so you want to go over to the filter here with a slicer and then you can bring in a filter that you like I'm just going to bring in year here and we probably want to change this from you know zero and one to something that is a little bit better but let's first create this so let's go to this slicer and you see if we click it it will slice everything so let's go over to the styling here slicer settings let's turn that into a tile we want to just do the same things we go to effect I'm going to turn on the visual border uh I'm going to turn off the background and here we can kind of place this where we want so you can see we can place it here maybe and maybe move these over a little now we want to go in and be able to change this a little bit so we're going to go over back over to home go to transform data I once you're in transform here so once you're in here I want you to go over to add a column and then go to conditional column so we're going to get that column name year if year equals uh zero we're going to say 2021 and if year equals one we're going to go 2022 and then we just hit okay and then we have this new column we can call that years and then we can close and apply this and now we have this years column here and you could just pull this over where your slicer is and we just replace it with years and now you can use this slicer to slice everything that you have and that's our dashboard let's answer the central question so now that we have all our data in one place I can just evaluate whether we can raise the prices for the upcoming year which was our central question so first what we're going to do is just create a matrix here we have our year and now that we have years let's let's just go ahead and add that in and then I want to be able to look at the writers I want to be able to look at revenue and I also want to look at a profit which these shouldn't change this much um I mean in terms of what we are looking at and then we want to look at how much we raise the price that so we want to take for price we don't want to take the sum we can literally take the first or average because it's the same so it should be all the same um so we raise the price by looks like we rais the price by a dollar and then we saw that we saw this gigantic increase in profit and gigantic increase in Riders so we need to do some calculations here uh to see if we can make that LE but I think you know it's pretty clear that if we if our writers were able to sustain a pretty sizable increase in our price and then we saw also a increas in in writers year on year I think we're pretty good and um profit also there's no change so let's get out a calculator and do some analysis so let's first calculate our change in price so that look around 25% to me so we have a new price of $4.99 our old price was $3.99 so we take the difference there which is just the dollar and then we're going to divide that by our old price which is $3.99 so you can see we had a 25% increase in price so although we had that 25% increase we saw a giant jump in our Revenue so let's look at the demand of the riters as our proxy so we let's keep that number down for us and here's my notepad so I'm just going to write 25% price increase and then let's see what the demand difference is so let's move this over a bit put this here and then we can say okay our let's clear this and we see that we had about we had two million writers and 2022 so the difference between 2021 so we had a million about 1.2 million so the difference is we have 86,000 now let's get the percentage increase by just dividing this value by our older value which is that three so you can see we had a 64% increase in demand so let's say 64% increase in demand so obviously if we increased the price by 25% and we still saw a increase of 64% we can feel with a pretty high degree of confidence that if we increase the price a little bit more we it shouldn't affect demand now that's assuming everything works in a linear way and there is a metric that people use for this well the metric that we use to to kind of usually understand that price is something called price elasticity and it's just dividing the price uh the increase in demand by the increase in pricing usually that's a negative number but we obviously see ours is not going to be negative so that's 65 divided by that uh that 64 let's go back let's clear 64 divided by that 25 is going to give us 2.56% P price elasticity and usually what this means is that um it can tolerate an increase usually this number is negative saying that if if we increase the price the demand will go down by 2.5 six 6 Cent but however in our case it goes up so there's obviously other things that play but it's pretty clear we can increase the price very conservatively so here's my recommendations I said let's conservatively increase things by 10 to 15% because I believe there are other Market factors that are driving up that demand because our Riders went up by that substantial 64% so we know that we increased the price by 25% which is $499 uh from 3.99 to 499 so a 10% increase would be 549 15% increase would be approximately 5.74 so what we could do is just take a conservative range there and test the market but I think there other Market factors like environment or and maybe there's just a demand we have to look at marketing we can segment our riter types to see if there's something there but I think we pretty feel pretty confident that we can increase our prices pretty conservatively I would go go at 10% and test the market and see how our customer based response and take it from there and we will Monitor and adjust I hope that kind of takes you through the whole life cycle of an in data analysis project please put any comments questions below and don't forget to subscribe and hey please make some adjustments to my dashboard to make it more beautiful

Channel: Absent Data

Views: 65,834

Keywords:

Id: jdGJWloo-OU

Length: 61min 52sec (3712 seconds)

Published: Sun May 05 2024