Hi Sir, It's a really good video. I have a suggestion: instead of loading data into Excel and then using it in Jupyter Notebook, we can directly connect to the database and use that view for our ML algorithms.
Hi, Thanks for a great suggestion. I did consider this earlier and used the pyodbc library to fetch data from server. However this method sometimes gives errors due to accessibility or drivers. So I didn't want the new learners to get discouraged by this, hence decided to keep it simple. Let me know if you have used any other library which makes the process simpler. Will be really happy to learn & use it in my next project. Thank you so much for watching! :)
@pivotalstats Sir, I have used 'from sqlalchemy import create_engine', but you are right; initially, during connection, it gives a really hard time due to drivers.
You've outdone yourself again. Thanks so much for giving this for free. Please do more projects like this using different data sources and different data analysis tools and techniques. Stay blessed!
Please upload more project like this I want to do more like this(Power BI + SQL + Machine Learning) No other people are doing it. You're a GEM man! Love you
It is a great end to end project, from getting a understanding about how data is converted from a raw data to visually appealing dashboard. Thanks alot.👍🏻
Just finished this project.I’m in awe of how well you took us through it!I’ve learnt so much and will definitely add it to my resume.I understood everything,you are incredible!! Keep up the good work👏🏽
Hi sir , thank you i just completed this project and i am adding it to my portfolio. You are a great teacher . Looking forward to more videos from you .
Hello sir, how are you? We don't talk or chat, but we've learned a lot from your videos. We're very grateful for teaching us, sir. Hope you're doing well and staying healthy
Hi, it seems you might have made a spelling mistake somewhere in query. Can you copy the query from the link that i provided in description and try again.
Isn't removing duplicates from single column (as we did in mapping) will affect the analysis outcome since their corresponding values in other columns are there in the data?
Hi, no that will not impact because we are working on a separate table. When the relationship is created between mapping and main table, it will assign relevant age group to each row.
kia baat ha sir g, you are amazing. thanks for this all. I believe you are the only one on UA-cam who provides detailed lectures with full access to data and lecture notes. You did this very professionally. can't be better than this. Just waiting for more and more. Could you also please make a video on how to develop websites and how to upload all the projects onto it, as you did for yourself. thanks for this amazing work.
Thank you so much :) Reading such nice comments really motivates me to bring more quality content. I don't know if web development related content will be correct for this channel, but I will surely plan a video on creating your portfolio page on github. Cheers!
I am getting the correct output but when I am going to my prediction data.xlsx file to see my result it is telling me Excel cannot open the file predictiondata.xlsx because the file or format is not valid
Sir, thank you for the video. However, I don’t understand the creation of that production table at all. Similarly, what is the downside of removing null values at the time of importing the table instead of the rigorous activity later?
Glad you liked the video! 1. Creating a prod table is considered as a best practice because it gives you the opportunity to revert back to the original data in case you make any errors without running the entire data load process again. 2. We can always do the data cleaning part in power bi, but the problem with that approach is that in case you are working with a team, and all of them are working on the same data. Then each of you would have to do the same cleaning process on each of your files. Also lets assume that after sometime you add a new cleaning step to your file then that steps will have to be implemented on all files again. Hope this helps!
@pivotalstats Thank you so much for responding promptly. That's awesome. Pardon me if am being slow here, however, regarding the first question, how that production table was created was fast so I didn't get it even though I understand the reasons for the creation considering the fact that am more used to PowerBi than SQL. Regarding the 2nd part, I meant the initial time you were importing the file into SQL you didn't ignore the null values but you later tried to remove them during the query. Is there any special reason to it?
Hi, The main reason is to improve performance because in a reference table we can remove duplicates and reduce the row count. Also in future if I decide to add more mapping columns then its easier to manage in a separate table.
Hi, thank you again for the great video. In my work, after creating the reference table ("Mapping age group"), in model view there is no connection between Via Age. Don't know why? I followed the exact path and rechecked. PS: I manually made a connection between them in model view, now I am getting correct results. This isn't incorrect, right?
@@pivotalstats thank you, still working on the project. Now half way is done. ☺️ Thank you once again, and highly appreciate your great contribution. 👏
Hi, Thanks for the wonderful learning video. I'm able to follow and re-create all the charts, I'm just encountering one error in the Churn Reason sheet is showing "The Total Churn" against each of the rows (1732) and also the total is showing as (1732). Please guide.
Hi, Check the table from which you are dragging the Churn Reason column into the visual. Most probably you are using the Prod_Services table. Instead fetch it from Prod_Churn, that will solve the issue. Hope this helps!
The error suggests that the Customer_Status column which is our Y variable (target variable), contains missing values. But in the original data there were no missing values in this column. Could you check if your vw_ChurnData contains null values? If it does then check the ETL process & Original Excel raw data file once more. Hope this helps!
I have a really quick questions after all the steps, for some reason my "Total Customer and Churn Rate by Tenure Group" is still out of of order by the
Hi, Since this project covers almost all major aspects of Data Analytics, you can write SQL Database Management, Power BI Data Transformation & Visualization, Data Analysis & Random Forest Modelling.
Glad that it helped! Sure, I'm not very active on linkedin but I'm planning to post content there as well. You can follow my page below: www.linkedin.com/company/pivotalstats/
Hi, Awesome Video...Really helpful. However, i am unable to understand where did the concept of VIEWS was handy while doing the churn analysis. Did i miss anything?
@pivotalstats that's why I missed it. I tried to practice only the churn analysis part. My bad. On a side note, I checked out a few videos of this channel, the content is pure gold. Kindly keep adding more.
hi, check out below link. It should help! community.fabric.microsoft.com/t5/Power-Query/Trying-to-connect-to-MY-SQL-from-Power-BI/td-p/195060#:~:text=Before%20you%20can%20connect%20to,MySQL%20in%20Power%20BI%20Desktop.
You can use any of these ml algorithms but while I was researching on this topic, I found that random forest is the most preferred algorithm for churn analysis.
Hi, Check if you are entering the correct server name. I have noticed that many people entered the server name which is there on the video, but you should be entering your server name. Hope this helps!
Hi , I can't connect to your server in Microsoft SQL Studio. Is it still possible to connect? I like this project and would to train on it also. why does this error appear?
Hi, you won't be able to connect to my server because that is my local server instance. You need to install sql server on your system and connect it locally. Hope this helps!
Hi, thank you for sharing this awesome project! I am unable to load the data to Jupyter notebook at ML step. Got this PermissionError: PermissionError: [Errno 13] Permission denied:'C:\\Users\\Home\\iCloudDrive\\Documents\\coding_temple\\projects\\customer_churn' Do you know what's wrong with this?
@pivotalstats sir when i execute query to check the nbull values in the data set it show the 0 for all columns and I cross verify the data of table in excel and it show the null/blanck values in excel. How to replace null value in MySQL workbench ? Should I replace clean in excel Help in this
Sir , provide data file not working on SQL server , error coming. When 1 error is solved, then another error is coming. It is better for us if you provide all 4 data files link.
Hi, May be I did not understand the question, but the data link I provided contains only 1 data file (not 4) i.e. Customer_Data. If you are facing errors even after following all the import process I showed in the video. May I suggest you to import that data directly to Power BI and do the transformations like null removal in the Power Query editor, so that you dont have to waste time on SQL server troubleshooting. Hope this helps!
@@pivotalstats Sir, the data you provide ,i find multiple errors, 1. Data not loading in SQL server 2. I changed the data structure ,then it loaded same things happened.so i loaded it in my SQL. 3.But multiple things can't be done in my SQL, due data structure. So , sir if you provide Main data file , with working data file , 2 view files , then it will be good for me . Thank you for replying & Understanding the issue
@@SSMB-dw2zr Here you go, this link contains a excel file which has stg_Churn, prod_Churn, vw_Churn & vw_Join data. Cheers! e3da6ab4-ff6e-4f55-bfa1-a8fb6979d99b.usrfiles.com/archives/e3da6a_ab9ca4f21efb4e6a9c16a94583f064d9.zip
Thanks. I have kept it very light in this video but still since sql is an essential skill for anyone working with data, you can watch below sql bootcamp video ua-cam.com/video/QDz8be0L2PQ/v-deo.html
Sir that project is awesome mainly the machine learning portion could you boost yourself to make a more advanced project right now I am finding jobs and they want the best projects because I don't have any work experience. and also sir could you provide me your LinkedIn profile I will tag you with this project.
Hi Sir, It's a really good video. I have a suggestion: instead of loading data into Excel and then using it in Jupyter Notebook, we can directly connect to the database and use that view for our ML algorithms.
Hi, Thanks for a great suggestion. I did consider this earlier and used the pyodbc library to fetch data from server. However this method sometimes gives errors due to accessibility or drivers. So I didn't want the new learners to get discouraged by this, hence decided to keep it simple. Let me know if you have used any other library which makes the process simpler. Will be really happy to learn & use it in my next project. Thank you so much for watching! :)
@pivotalstats Sir, I have used 'from sqlalchemy import create_engine', but you are right; initially, during connection, it gives a really hard time due to drivers.
You've outdone yourself again. Thanks so much for giving this for free. Please do more projects like this using different data sources and different data analysis tools and techniques. Stay blessed!
Thanks, will do! :)
Please upload more project like this
I want to do more like this(Power BI + SQL + Machine Learning)
No other people are doing it. You're a GEM man! Love you
Will do! Thanks for the support :)
@@pivotalstats waitinggggg!!!!
It is a great end to end project, from getting a understanding about how data is converted from a raw data to visually appealing dashboard.
Thanks alot.👍🏻
Welcome :)
One more nice project and concept of implement SQL + power bi as wel machine learning . thank you for nice video
You are welcome :)
One of the best Power BI tutorials and teachers. Thank you very much from Bangladesh.
Thanks for the support :)
Thank you very much, such a great lesson! Hope to see such a good project from you.
You are welcome! Sure 👍
This is a beautiful and complete project.
Kudos!
Replicating for my portfolio 😍😎
Glad you like it!
Just finished this project.I’m in awe of how well you took us through it!I’ve learnt so much and will definitely add it to my resume.I understood everything,you are incredible!!
Keep up the good work👏🏽
Wonderful! Sure, will do :)
I got 77k customers suddenly instead of 6318 ..after completing the steps of churn by services..What can be the reason ?
it is very helpful with amzing explication
Thanks :)
Really loved the video. Good way to bring everything under on roof. Looking forward to more such content.
Thanks a ton!
It is really very good project implementation. End to End.
Glad it was helpful!
Hi sir , thank you i just completed this project and i am adding it to my portfolio. You are a great teacher . Looking forward to more videos from you .
Thanks :)
Great. It is really very interesting project. I really learnt a lot from this.. Keep doing good work. Good luck..
Thanks a lot :)
This was great and very helpful, thank you!
So glad! :)
This is a nice project. Well done.
Thank you very much!
Bro ure a legend!
Thanks for watching buddy 👍
Thank you so much SIR!!!
Most welcome!
Wow... Just Wow
Thank you soo much for this wonderful project.....this means a lot
Thanks for watching, cheers!
Loved it like a adventure movie ❤
Thanks for watching :)
your channel is under rated sir you will achieve great success in future😊😊
Thank you so much 😀
yes agree, such a helpful channel needs more subscribers.
This is awesome!
Thanks 🙂
Hello sir, how are you? We don't talk or chat, but we've learned a lot from your videos. We're very grateful for teaching us, sir. Hope you're doing well and staying healthy
Hello, I'm doing very well. Thanks for asking 🙂. Really glad that my videos are helping. Cheers!
New to it great work 👍👍
Thanks !
Hi Sir,
Please make more such videos (SQL + Power BI + Python). It was really helpful. Thank you.
Sure I will. Thanks for watching!
Thank you sir ❤❤❤
Most welcome 🙂
Thanks for the project, it's very interresting
Glad you like it!
Respect to the world of India
Cheers!
16:52 i stucked here in sql workbench it shows error (1064) please help me to resolve it
Hi, it seems you might have made a spelling mistake somewhere in query. Can you copy the query from the link that i provided in description and try again.
@@pivotalstats issue solved thank you .
Isn't removing duplicates from single column (as we did in mapping) will affect the analysis outcome since their corresponding values in other columns are there in the data?
Hi, no that will not impact because we are working on a separate table. When the relationship is created between mapping and main table, it will assign relevant age group to each row.
kia baat ha sir g, you are amazing. thanks for this all. I believe you are the only one on UA-cam who provides detailed lectures with full access to data and lecture notes. You did this very professionally. can't be better than this. Just waiting for more and more.
Could you also please make a video on how to develop websites and how to upload all the projects onto it, as you did for yourself. thanks for this amazing work.
Thank you so much :) Reading such nice comments really motivates me to bring more quality content. I don't know if web development related content will be correct for this channel, but I will surely plan a video on creating your portfolio page on github. Cheers!
I am not able to open the output of random forest algorithm into my excel file
Hi, Please check the file path that you have given. Also are you receiving any errors?
I am getting the correct output but when I am going to my prediction data.xlsx file to see my result it is telling me
Excel cannot open the file predictiondata.xlsx because the file or format is not valid
Sir, thank you for the video. However, I don’t understand the creation of that production table at all.
Similarly, what is the downside of removing null values at the time of importing the table instead of the rigorous activity later?
Glad you liked the video!
1. Creating a prod table is considered as a best practice because it gives you the opportunity to revert back to the original data in case you make any errors without running the entire data load process again.
2. We can always do the data cleaning part in power bi, but the problem with that approach is that in case you are working with a team, and all of them are working on the same data. Then each of you would have to do the same cleaning process on each of your files. Also lets assume that after sometime you add a new cleaning step to your file then that steps will have to be implemented on all files again. Hope this helps!
@pivotalstats Thank you so much for responding promptly. That's awesome. Pardon me if am being slow here, however, regarding the first question, how that production table was created was fast so I didn't get it even though I understand the reasons for the creation considering the fact that am more used to PowerBi than SQL.
Regarding the 2nd part, I meant the initial time you were importing the file into SQL you didn't ignore the null values but you later tried to remove them during the query. Is there any special reason to it?
Thanks Bro. one question, why did we create new reference table for Age group instead of creating new column in existing table?
Hi, The main reason is to improve performance because in a reference table we can remove duplicates and reduce the row count. Also in future if I decide to add more mapping columns then its easier to manage in a separate table.
@@pivotalstats great, thanks for the explanation
Hi, thank you again for the great video. In my work, after creating the reference table ("Mapping age group"), in model view there is no connection between Via Age. Don't know why? I followed the exact path and rechecked.
PS: I manually made a connection between them in model view, now I am getting correct results. This isn't incorrect, right?
Ideally it should have established relationship automatically but doing it manually is also fine.
@@pivotalstats thank you, still working on the project. Now half way is done. ☺️
Thank you once again, and highly appreciate your great contribution. 👏
Hi, Thanks for the wonderful learning video. I'm able to follow and re-create all the charts, I'm just encountering one error in the Churn Reason sheet is showing "The Total Churn" against each of the rows (1732) and also the total is showing as (1732). Please guide.
Hi, Check the table from which you are dragging the Churn Reason column into the visual. Most probably you are using the Prod_Services table. Instead fetch it from Prod_Churn, that will solve the issue. Hope this helps!
Yes, that worked. Also when I changed the relationship between the tables to "both" it worked as well. Thanks for you quick response.
when i am trying to Train Random Forest Model getting error-ValueError: Input y contains NaN.
,how do i resolve this
The error suggests that the Customer_Status column which is our Y variable (target variable), contains missing values. But in the original data there were no missing values in this column. Could you check if your vw_ChurnData contains null values? If it does then check the ETL process & Original Excel raw data file once more. Hope this helps!
I have a really quick questions after all the steps, for some reason my "Total Customer and Churn Rate by Tenure Group" is still out of of order by the
What key points should i use to describe this project on my resume
Hi, Since this project covers almost all major aspects of Data Analytics, you can write SQL Database Management, Power BI Data Transformation & Visualization, Data Analysis & Random Forest Modelling.
This is a perfect video and helpful thank you very much!!!! can i connect with you in linkedin sir ? thank you
Glad that it helped!
Sure, I'm not very active on linkedin but I'm planning to post content there as well. You can follow my page below:
www.linkedin.com/company/pivotalstats/
Please tell me how I can connect to the server
Hope you have installed sql server on your system and using that server name.
Yes yes thank you for sharing how to fix SSMS server
If possible can you share your LinkedIn ID I will love to connect there
Hi, Awesome Video...Really helpful. However, i am unable to understand where did the concept of VIEWS was handy while doing the churn analysis. Did i miss anything?
Hi,
Glad you liked the content. Yes we have used those 2 views at the very end while creating our machine learning model.
@pivotalstats that's why I missed it. I tried to practice only the churn analysis part. My bad. On a side note, I checked out a few videos of this channel, the content is pure gold. Kindly keep adding more.
@@atulverma_Thanks, will do :)
can we use mysql also? as ms sql is not there for mac
hi, check out below link. It should help!
community.fabric.microsoft.com/t5/Power-Query/Trying-to-connect-to-MY-SQL-from-Power-BI/td-p/195060#:~:text=Before%20you%20can%20connect%20to,MySQL%20in%20Power%20BI%20Desktop.
Hi sir can u please make video on end to end project with large data set using direct query in power bi??
Sure ill plan something for future 👍
Why random forest algorithm that you are used for prediction why not logistics regression?
You can use any of these ml algorithms but while I was researching on this topic, I found that random forest is the most preferred algorithm for churn analysis.
I installed the SSMS and after opening the server name is not showing. Its empty
Hi, just found this video. This should help
ua-cam.com/video/bgB2xeB6IM8/v-deo.htmlsi=_odsU7sDW3KntUQo
HELLO SIR, I am not able to connect server in ssms what is the issue?
Hi,
Check if you are entering the correct server name. I have noticed that many people entered the server name which is there on the video, but you should be entering your server name. Hope this helps!
Hi , I can't connect to your server in Microsoft SQL Studio. Is it still possible to connect? I like this project and would to train on it also. why does this error appear?
Hi, you won't be able to connect to my server because that is my local server instance. You need to install sql server on your system and connect it locally. Hope this helps!
you could have easily created age group column in Calculations table rather than creating refrence table
Hi, Yes you are absolutely correct, we can create a calculated table as well using DAX here. Thanks for the feedback!
Hi, thank you for sharing this awesome project! I am unable to load the data to Jupyter notebook at ML step. Got this PermissionError: PermissionError: [Errno 13] Permission denied:'C:\\Users\\Home\\iCloudDrive\\Documents\\coding_temple\\projects\\customer_churn' Do you know what's wrong with this?
Try using \\ in ur file path and also move the file to a different location. Hope this helps!
can you create video on ssis?
Sure ill plan something for future videos
where is the dataset?
Hi, its there in the description. Pasting the link here for your reference
www.pivotalstats.com/post/end-end-churn-analysis-portfolio-project
HII SIR ,
CAN I SHOW THIS PROJECT ON MY PORTFOLIO FOR DATA ANALYST PROFILE JOB?
Sure go ahead. Just make sure that you make few changes in the colors or visuals. Otherwise they might think you just copied someone else.
@@pivotalstats okay and please create such 1-2 more project for data analysis
@pivotalstats sir when i execute query to check the nbull values in the data set it show the 0 for all columns and I cross verify the data of table in excel and it show the null/blanck values in excel. How to replace null value in MySQL workbench ?
Should I replace clean in excel
Help in this
Sir , provide data file not working on SQL server , error coming. When 1 error is solved, then another error is coming. It is better for us if you provide all 4 data files link.
Hi, May be I did not understand the question, but the data link I provided contains only 1 data file (not 4) i.e. Customer_Data. If you are facing errors even after following all the import process I showed in the video. May I suggest you to import that data directly to Power BI and do the transformations like null removal in the Power Query editor, so that you dont have to waste time on SQL server troubleshooting. Hope this helps!
@@pivotalstats
Sir, the data you provide ,i find multiple errors,
1. Data not loading in SQL server
2. I changed the data structure ,then it loaded same things happened.so i loaded it in my SQL.
3.But multiple things can't be done in my SQL, due data structure.
So , sir if you provide
Main data file , with working data file , 2 view files , then it will be good for me .
Thank you for replying & Understanding the issue
@@SSMB-dw2zr Here you go, this link contains a excel file which has stg_Churn, prod_Churn, vw_Churn & vw_Join data. Cheers!
e3da6ab4-ff6e-4f55-bfa1-a8fb6979d99b.usrfiles.com/archives/e3da6a_ab9ca4f21efb4e6a9c16a94583f064d9.zip
@@pivotalstats thankyou sir
Awesome sir but i don't know much about SQL
Thanks. I have kept it very light in this video but still since sql is an essential skill for anyone working with data, you can watch below sql bootcamp video
ua-cam.com/video/QDz8be0L2PQ/v-deo.html
Sir that project is awesome mainly the machine learning portion could you boost yourself to make a more advanced project right now I am finding jobs and they want the best projects because I don't have any work experience. and also sir could you provide me your LinkedIn profile I will tag you with this project.
Hi, Sure I will definitely try to bring another project soon. And my linkedin profile is sajit-simon.
Sir your project is very awesome