Business Analytics Projects & Learnings

Table of contents

R Shiny Application

Data Visualisation using Alteryx & Tableau

Predictive modeling

Consulting Case studies

R Shiny Application

Project: A R Shiny application using Microsoft Server Database 

Project Goal: This group project will simulate the system design and implementation experience of a typical software consulting firm. The project will require you to analyze user requirements, develop and document a database design to support the user requirements, and implement the project in SQL Server and Shiny. Project deliverables include SQL queries used for developing the database,  R code for the shiny application, Final Report containing project proposal, Conceptual design and Shiny App URL.

Approach: The main idea is to create an application, that we named 'Foogle', that would work as an intuitive restaurant finder tool designed for people who have food allergies or special dietary needs. The primary focus was meeting all project goals and deliverables and the next was to provide a very simple and easy-to-use interface allowing the most useful functions. Consequentially, we conceptualized a broad database design that would allow both expansion and contraction of use cases so that the basic table structure need not be disturbed. Then an R shiny interface was created followed by database queries to fetch data.

More details are available in the Project Paper.

R shiny Application here

Project Paper here

Project Presentation here

Project R file here

Project SQL queries here

Entity Relationship diagram here

Note1: The project was a group effort by Allen Mattam, Audrey Nguyen, Priyanka Raj, Srikanth Soma

Note2: To run the R file, another file containing database credentials is needed. It is not being shared for security reasons.

Screenshots:

Data Visualization Project

Project : Commit Partnership – Data Visualization Project Description

Project description: The Commit Partnership is an educational non-profit whose true-north goal is that by 2040 at least half of all 25-34 year old residents of Dallas County, irrespective of race, will be provided the opportunity to earn a living wage.  

        Preliminary STAAR results show drastic declines in student performance from 2019 to 2021. This data visualization project would create a Tableau dashboard that visualizes these changes, with a particular focus on schools in the DFW Metroplex. Students will also need to use Alteryx to blend various data sources together.   

Project scope: Students are encouraged to analyze and provide insights with regards to :

• Which schools had the biggest declines in test scores from 2019-2021? Which ones had the smallest? Are there any trends about schools with the biggest/smallest declines?  
• Which schools are “bright spots” (overperforming expectations), and which schools should be “focus areas” (underperforming expectations)?  
• Which schools are succeeding at closing the opportunity gap (the difference in standardized test scores and other metrics by race and/or income)?  

Project Findings: 

• Major factors that were considered​ :
• Percent of Economically Disadvantaged (EcoDis) students​
• Opportunity Gap​
• Minority Full Time Staff​
• Percent of Bilingual Teachers​
• Teacher Experience Average​
• Teacher Student Ratio​
• Teacher Base Salary

• Insights​ :
• Factors that heavily influenced outcomes​ :
• Percent of EcoDis Students​
• Opportunity Gap
• Dallas County consistently showed better average test scores for Hispanic students​.
• In both Dallas County and Tarrant County, Hispanic students had the largest drop in average test scores​.
• Economically disadvantaged students continue to struggle.
• Minority groups are disproportionately affected by hiccups in education.
• Strides have been made to attempt to close the Opportunity Gap

Note1: This is a group project collectively worked on by David Lin, Edward Zhao, Nicolas Rhodes, Nikhil Cherukuri, and Srikanth Soma​.

Note2: The data used for the purpose of this project is publicly available (most of it at https://tea.texas.gov/ ) and is not proprietory. 

3. 
   

ALTERYX

Alteryx  Packaged Workflow here

TABLEAU

A glimse of Tableau work

Tableau packaged workbook here

Predictive Modelling

BIKESHARE PROJECT

Project : 

Use the publicly available bikeshare dataset to do exploratory data analysis, predict a linear model and provide valuable insights.

Observations and Insights: 

• The spread of all users was fairly equal among all weekdays, however, the number of bike riders dropped on weekends. 
• This could be due to the fact that customers are mostly registered and use bikes mostly on weekdays. 
• The number of bike users both casual and regular, increased in the second year by almost double. Demonstrating that the company has significant growth potential. 
• The number of users peaked at about 80 degrees Fahrenheit, at temperatures hotter than that the amount of users begins to decline. The number of riders is much lower at low temperatures (winters) 
• The results of the regression indicated that variables like - season, year, month, holiday, weekday, working day, weather situation, temperature, humidity, and wind speed - account for 84.22% of the variation in total rental bike users (count). From this, we can conclude that the time of year and environmental conditions are important elements to consider when predicting total bike users (count). 

Suggestions:

• The number of registered users is four times larger than the number of casual users. We also observed that there is a significant drop in casual bike users that rent bikes on weekdays compared to weekends. We could have promotions that are targeted directly at casual users specifically for weekdays and persuade them to become registered users.
• We need to instill confidence to customers that biking can still be a good option for transportation regardless of season or temperature.
• In terms of bike users and temperature and the seasons, it can be understood that the amount of bike riders in the winter is low due to the harsh temperatures. One way to mitigate this is by advertising bikes that are more suitable for that weather.
• Windspeeds appear to have a highly negative impact on ridership and hence we may also explore the option of using windscreens for the bikes on a trial basis. They are not very expensive to install or maintain.

Project final report here

Project R file here

Project data here

Note: This is a group project collectively done by Allen Mattam, Andrea De Leon, Isak Arms, Jessica Zhang and Srikanth Soma.

MOVIE PREDICTION PROJECT

Project: Run a logistic regression on the movies datasets. The goal is to say something interesting about this data. The outcome of interest is whether the movie is considered “good” or “bad” based on the movie’s rating. Part of the purpose of this homework project is to get comfortable obtaining, cleaning, and prepping data for analysis. Combine or manipulate the original variables or make new variables using original variables if needed.

Approach: We cleansed the data using python. We created new variables namely ‘cast_score’, ‘director_score’, and ‘production_score’. We used the top directors, Actors & Actresses and Production houses according to IMDB and incremented the respective variables - ‘director_score’, ‘cast_score’ and ‘production_score’ by 1 every there was one from the list in the movie. We then ran regression in R with variables - Belongs_to_collection, popularity, runtime, cast_score, director_score, production_score.

Observations and Insights: 

• There are many movies that have very high popularity but have very low ratings and vice versa. Given that we use the calculated rating to determine whether a movie is good or bad, we assumed that this might be due to movies that are marketed by pushing a lot of money to create a lot of hype from the start, or by having a very high rated actor in the movie but doesn't gain a lot of viewership after the movie is actually released.
• Our model suggests that the popularity of a movie means that the movie is 59% probable to be a good movie.
• The star actors in the movie, the director, and the producers of the movie have a significant influence on the success of the movie by 74%, 85%, and 60% respectively.
• There is a negative relationship between the fact that a movie belongs to a series and the rating of the movie. Based on our model, if everything else remains the same, the probability that a movie is bad is 57% if is part of a movie series. However, this goes against the common perception and there could be unaccounted factors playing into this or it is just that the count of bad rated movies belonging to series is higher than the popular ones.

Suggestions:

• Although great cast, production and direction will add value, they come at a high cost. Having a great director had a better impact than cast and production (according to our model) and could be a balanced trade-off between expense and success factors.
• Other good factors that contribute to the success of the movie:
• Run time between 90-150 minutes.
• Genres:  Animation, Adventure and Family Movie (for greater ROI)
• Popularity: IMDB calculates this variable using its proprietary formula. This variable is calculated before the release of the movie and influences the marketing plans. 

 

Project Final report here

Project R file here

Project Python file here

Project data here

Business Analytics Consulting Case Studies

Blueprint for FinLaw Case

Executive summary and presentation outlining top 5 analytics/ process solutions to transform and scale loan contract-drafting process at financial services firm. Flowcharts of as-is and to-be processes, and value realization matrix for proper prioritization of solutions.

 

CVS service improvement (HBC)

Breakdown of pharmacy service process stages and identification of areas of improvement to increase customer satisfaction. Outline of additional decisions to further improve and scale the current process.

 

Domestic Auto Parts (HBC)

Outline of objectives for each of the dimensions of the Balanced Scorecard: Financial, Customer, Internal Processes, Learning & Growth. Proposals of KPIs to measure performance against strategy.

 

UCB data is the new drug (HBC)

Assessment of existence of an evidence-based culture at pharmaceutical company UCB to foster better decision making. Planning of decision agenda for the executive meeting in Shanghai to present technology strategy.

 

Ethics - Data Science at Target

How far can data science go? What role does an Analyst have in making ethical decision-making? 

India vs COVID 19 - Battle Dairies

India vs COVID19- Battle dairies

26-05-2020

Personal Hygiene. Not Lockdown. Physical distancing. Not Social distancing. Fewer restrictions. Not    severe restrictions.

We see COVID19 deaths being reported mostly in High Risk Individuals. We did even see some very healthy youngsters go down but such cases are far and few. Majority deaths due to COVID19 are reported in people with Co-Morbidities. Here we can see that we were not able to make sure that the high risk individual do not get affected or need not do something that raises their risk of getting infected. Lockdown was very necessary and important when we had the 1^st three phases. We could buy some time to beef up our infrastructure and put in processes to deal with COVID19 once lockdown would be lifted. If any one had thought that COVID19 would be defeated by Lockdown was not being reasonable. Lockdown was needed to buy some time. Extending Lockdown, in the present form is no more prudent. Lockdown phase 4 itself has seen common public flouting social distancing norms and people moved from anywhere to everywhere. There is no point in state governments imposing quarantine rules. There is no quarantine for a person living in Delhi who has been moving all across the NCT and similarly a person living in Hyderabad has been moving all across the state without being quarantined. May be both people were asymptomatic hosts spreading COVID19 everywhere. If we see India as one unit, these two together would have affected COVID19 to the same amount and to the same effect as they would have if they travelled between Delhi and Hyderabad. So these quarantines are because states just don’t want more numbers associated to their governments. The everyday scrolling and scrutiny in media has made the states to be risk averse. Be warned, there is not much we can do about COVID19 using quarantine or Lockdown when people are in no mood for. It must made clear that states should feel free to take measures without fear of being blamed for unavoidable COVID19 cases. However, all necessary measures must be put in place to encourage and police Social distancing and personal hygiene. Of course, more efforts should be spent on spreading awareness about the need for personal hygiene and physical distancing.

Much more effort has to be spent in proactively protecting the high risk individuals. Monitoring fewer high risk people would be easier for the governments, philanthropists and NGOs. We should be able to divert the focus from suffering migrants in lockdown to high risk individual post lockdown.

 29-05-2020

China is becoming belligerent. 
Was watching a interview on India Today. Col Ajai Shukla was of the view that the investment and build up of Soldiers and infrastructure along the bother is of no use if we did not push back PLA into their side. He mentioned Chinese having come 3 kms into the Indian side. All panel including General Hasnain who is usually a Pro-government analyst has also expressed the fear that the China is threatening to Cut off India at the Karakoram range. A local security analyst from Ladakh has said that he is was never so afraid ever and never felt the danger so real and close. Government sources seem to suggest that there is no need to act immediately and that like Doklam lasted 71 days and we had a last laugh then. Likewise, we can act slowly and still have a last laugh. That India is acting slowly on mutliple fronts while also engaging at the Diplomatic front.  

India is now surrounded by Hostile neighbour - Bangladesh and Nepal have now joined the Chinese club thanks to indifference of India. I am sure China has bribed the PM of Nepal. He just needed a excuse and we abliged and gave them a perfect excuse. We didn't bother to talk to Nepalese general population or Oppositon. Modi's look East policy was not longer than a wink. India's focus then seems to have shifted elsewhere. How else do we explain such a big failure on our diplomatic front in the East vis a viz Nepal, Bangladesh, China. 

Bangladesh, Vietnam and Thailand have benefited from the global anger against China but India was busy with its internal politics. Who failed India? 

China would be foolish to move an inch more in the current global outrage against china. With it 

1. having other plans in Taiwan and Hongkong where it is taking severe steps to annex both of them or bullying them to towing their line. 
2. 62 countries moving a resolution to investigate its role in COVID19.
3. Commerce feud with Australia after it initiated to move that resolution and later China retaliated by banning its meet hurting Australian Ecomony.
4. USA bent on pinning the blame on China for COVID19.
5. Canada refusing to interfere in Judicial process of scuritinising the role of Huwaei. 

But India would also be fool if it thinks China is not fool enough, that China is incapable of taking the risk, especially in times where every country except China is having an economic meltdown. We should't count on much of international help, in case we may need it, also keeping in mind that we have hostile neighbors on all sides. 

NDTV was called names for telling that its sources reported that Indian soldiers were detained for a while before letting them. All chest beating egoistic people fail to understand that there is nothing wrong in that. It doesn't make Indian soldiers weak. It just means PLA was the first agressor. Now reports that Chinese PLA is 3 kms into Indian territory beyond LAC, and that PLA has been even drawing and bridging roads , bunkers in the Indian side.  Now this is very serious. Proves NDTV was not completely wrong, if not for the part of detention of soldiers. We may never know the truth. I am now sceptical of the courage of Military top management to speak the truth and be indifferent to politics over the past few years. 

30-05-2020

Looks like Mamta Benerjee has tried to score a political populist point by
1. Opening  up temples (with cap of 10 at a time)
2. Announcing to open all offices and work places. 

The GDP growth of 4.2% for FY 2019-2020 was much before COVID19 stuck. Even quarter wise, the GDP has been consistently declining. If not for the government services that grew at 10% and Agriculture that grew at 4 %, every thing else was in a dump. Of course, I seriously doubt if GDP really grew at 4.2%. Electricity consumption is sometimes seen as a metric to assess production growth. It seems suggests a massive drop.
It is official now that we are into a recession now that this quarter was consumed by COVID19. I remember our Finance Minister declaring that India going into a recession is not a possibility. Hmmm.. 

Letter to the Prime Minister on his Letter to the nation on completion of 2 year of his 2nd term. 

I must have written such letter in my 5th or 6th. I didn't have any sense of responsibility then. I could boast and say anything then. I didn't have to talk truth because my marks were awarded not based on facts but my writing abilities.

Apart from the discussion about Chief of Defence Staff, PM Kisan Samman Nidhi, Mission Indradanush - Vaccination campaign, nothing claimed in the letter seems to be anywhere near reality. All of it has been tested by time and the COVID19 times and none of them are either as true on the ground or helpful on the ground.

Please own the problems. Anyone will be willing to won the success. Please acknowledge the problems we are facing.

I don't understand the propriety of patting on one's own back. ( I was never in such a situation), but such distasteful bragging at a time when people are in miserable state, that too bag of lies, is definitely improper. This I know.

Sorry Prime Minister sir. But you have once again let us down by making such a political statement at this point of time and by only addressing your vote bank and no one else.

Good luck to the nation. Grandstanding won't help. Standing with people will.

One nation one ration card - Portability of Ration cards

"One nation one ration card programme" - a good step in the direction of Uninersal food security.

It will not work in present COVID19 scenario. Everything will be same as it would have been without  this programme. 

I don't think a migrants will carry ration card with them to another state. I guess ration cards will be left with families back home in their native states. Correct me if my common sense is failing me. 

Just in case the government thinks this is a extra ordinary situation, it should take extra ordinary measures like distributing  grains for free. 

No one feels so great to stand in lines for hours and get food which is a basic right. 

If we even care to listen to these migrants, we will know that they will stop feeling restless if they get some cash into their hands. They will stop feeling restless if they get food easily, not after waiting in sun for hours risking to get infected. 

I have an idea..

Just forget about targeted PDS for a few months. 

We can allow former employers to give away reusable food tokens tagged with IDs to labour and other needy employees collected from district administration. Most employers wont eat that grain themselves. 

Monitoring distributors for pilferage and incentivising free distribution with some hinororium will help a little. 

If you can't trust the fair price shops or ration shops,  asign the job to experienced NGOs. I think DMs can manage the task. They can disincentivise hoarding by the non-needy by distributing smaller quantities. A lot of time will be saved that would have otherwise been consumed for precise accounting ( precisse weighing, identifyingbthe beneficiary, making sure the beneficiary is not benefited twice etc would be done away with)

Something like this should have been announced a lot earlier. 

Its sheer apathy and recklessness that is causing migrants to choose to walk back hundreds of kms taking huge risks and causing tremendous loss to the economy. 

May be govt loses a quite a bit of food to those not in need(which can be mininised with proper monitoring). But it does lot of good to the needy and the nation. I am sure the food will not rot.

I dont lie to myself or cheat myself. That is the respect I give to myself.

I don't think there is any pretence from me as to how I think. I however don't agree to the binaries  tagged to me. I am definitely critical of BJP government on specific points for the specific reasons founded by specific facts and not out of hatred.  As is clear from my post, I am not inclined to wait further. Period. However, I believe to think myself as more patient and more understanding that the average Indian. I may be wrong. But mostly not 　 .  6 years is a good time to analyse which way the government is going. I however gave myself more time to see if the government was trying to go in a better path given the seriousness of the situation but this speech has more or less shattered my hopes. 

No one asked him to come and announce the amount of package today. If FM was to announce the package over a period of time, she would have as well spelt out the total amount in tomorrow's press brief. 

Yes Self-Reliance or Self-sufficiency in not what I expected to hear. I was expecting concrete steps,  clear and un-ambiguous outline of  lockdown rules, clear and un-ambiguous outline of relief if not stimulus package, clear and unambiguous measures to tackle distress in migrant labour. I am sorry but looks like my expectations were different from yours. I was actually listening. Not just hearing.  

You are free to give more time to him, to those in distress and to the economy in shatters. I don't agree with it. 

PS: I was always critical. Especially of the powerful and the bullies. That has been me since I drew my first breathe. I am not politically inclined or affiliated. I am just philosophically inclined. But I don't lie to myself or cheat myself. That is the respect I give to myself. 

Chennai - the city that changed my perception. Alas! - the perspiration.

"
Since the journey to Chennai was forced upon, it feels like it will take for ever to experience that shortest yet strongest of  bliss.
The never-die aspirations of this optimist have been turned to ashes under the Chennai sun. But still a tiny remote hope lingers that comforts the heart, that a day will come -

when the heart starts to pound but the body doesn't feel a thing,

when the perspiration reaches its peak but the skin feels so soft and perfect ,

when the sun chars the face to black but the heat doesn't get to the head,

when the normally bothering crowd ,its prying eyes and irritating voice, now seem non-existent.

when the impossible confluence of  utter silence in heart and bitter confusion in head becomes possible.

when the strongest of hearts wilts under the strongest of emotions at the pleasantest sight of those enchanting, en-capturing and incapacitating eyes,

when the most painful thing happens and is still undermined by the most beautiful of the bliss.

when the body is stirred by the flow of the overflowing happiness,

when the body is teleported to inexpressibly beautiful lands, unlike the hapless land of Chennai.

And all these  in 'the most difficult of conditions', 'in the suburbs of the the ever scorching Chennai'!!??!!

Yes most of you are right, Its not optimism.Its stupidity.
"

These words crawled my head and moved my hands one weekend when I was sitting in my office desk, thinking what Chennai had to offer me.

Now when I am back in Hyderabad, a month since my return from Chennai.. I think..

The sun, the sweat, the water have followed me all the way showing me that this place is not much different in that regard. It was my perception that drove me to believe that Chennai was the  All that's changed is the people around me and the changes, momentary or permanent, they bring into me. All that's amiss is the love offered to me and the love demanded of me. All that's changed is the emotions that the people and conditions invoked.

When I ask myself if what happened to me was really as bad a thing or if it was as good as my optimism spoke?  The answer is both Yes and No. The answer is that - it gave me experiences that I would have not got otherwise, it gave me wisdom of enormous proportions and  memories that churn my heart with love, affection and pain. It was a privilege and a liability all at the same time. I choose to embrace the truth that on a longer run time leaves behind  only good memories and takes with it all the bruises and hardships endured in the arduous journey.

In the moment of my return I felt the same thing as when I embarked - " The journey is being forced upon me.."
The difference being that time I have the confidence that I was going to the place which high and dry supports me  in all - Home, indeed sweet home.

Life is Journey* indeed. Chennai was a sojourn that was the most efficient shortcut possible to teach about life.

- Srikanth dairies,  September 2013.

The decorated mummies and the unsung heroes

http://www.thehindu.com/features/magazine/a-foreigner-and-a-freedom-fighter/article5048153.ece

This article made me think once again if the nation deserves what it had got?? Did or does it deserve the sacrifices of all those who made selfless sacrifices of paramount import and led apolitical life for the benefit of the rest despite harming self.

I am indeed happy that at least there is someone interested to share such information which in actuality should be studied in school.

Voters need to be educated

        Election malpractices are not just done by politicians. Voters resort to various malpractices varying in severity which in any case goes unchecked. No voter indulging in malpractices is booked or brought to trial. So This trend needs to arrested sooner than later. 

         Bringing erring voters to trial is quite a mammoth task and once elections are  over, EC's control ends and its the politician who will again have the say. 

          However, it is more pragmatic to suggest that voters be educated against indulging in malpractices or bowing to irrational logic of politicians and at the same time educating them about the institution of politics at various levels and how it would directly or indirectly affect them. 

I would love if Election commission educates the citizens regarding:

• The misconception of a wasted vote. 

• Voting based on objectives and not subjective. 

• Role of Candidate vs Party.

• Weighing Constituency vs State vs Nation.

• Looking at more than a few options/candidates.

• That rupee saved is a rupee earned. One does not have to earn the same extra rupee which one eventually loses in form or bribe or prices or taxes or service charges. Vote the correct one into power, everyone including you will be happy or earn.

Election Commission needs to take help of all popular media even if it is expensive. It might just give greater returns for a great investment.