Business Analytics

Data Pipeline

Database

Cloud Computing

Exploratory Data Analysis

Data Mining

Data Visualisation

I used **Power BI** to explore the reasons why changing **the mix and weighting of assessment types** in presentation 2014J compared with other presentations of module BBB appeared to be **associated** with a change in final results. The task shows my **Exploratory Data Analysis (EDA), Data Engineering, Data Visualisation, and Story Telling skills.**

**The published report:** <https://www.novypro.com/project/education-focused-analysis-how-assessment-types-shape-the-final-result>

**Data and Relevant Files** are here: <https://github.com/xiangivyli/data-science-portfolio/tree/main/part_c_education_focused_analysis_report>

### Context:

The Open University has launched a series of modules (courses) and collected user interaction data over two years. The academic module leader would like to discuss with other staff teaching on the module:

*   *Why changing the mix and weighting of assessment types in presentation 2014J compared with other presentations of module BBB appeared to be associated with a change in final results.*

### Data Source

[Open University Learning Analytics dataset](https://analyse.kmi.open.ac.uk/open_dataset#data)

7 Tables:

1.  courses.csv

2.  assessments.csv

3.  vle.csv

4.  studentInfo.csv

5.  studentRegistration.csv

6.  studentAssessment.csv

7.  studentVle.csv

Database schema

![](/images/Database_schema_Open_Learning_Analytics.png)

### Data Validation

I summarised **pivot tables** to understand the categories and the range of numbers

![](/images/Data%20Validation.png)

### Data Cleansing

I used Python to filter data, only module BBB data was kept

The code can be found on my Github: <https://github.com/xiangivyli/data-science-portfolio/tree/main/part_c_education_focused_analysis_report/OULAD_Python_Transformation>

![](/images/Data%20Cleansing.png)

I recorded the changes of cleansed dataset for future review

![](/images/Changes%20of%20cleansed%20dataset.png)

### Power BI Step

Generated Parquet files were imported in the Power BI and the 7 tables were created relationship using **Data Modelling**

![](/images/Power%20BI%20Data%20Modelling.png)

### Page 1 Overview of Module BBB

The data was collected during 2013 and 2014, they have **2 presentation types B and J**, B started in February, and J started in October. There are 4 Presentation Types, 2013B, 2014B, 2013J, and 2014J.

For Assessment Types, **2014J** was composed of Exam and **Tutor Marked Assessments**, while other Presentations have **Computer Marked Assessments.**

![](/images/report%201%20Overview.png)

*   For **2014J,** different assessment types have **different weights, id 15023 and id 15024 weighted 35, and id 15020 weighted nothing.**

*   For other presentations, **Computer Marked Assesments** counted for 5%, each assessment counted for 1%

![](/images/report%201%20Weightings-113ff154.png)

### Page 2 Student Persona

The most significant feature is **more female students** registered for the module BBB than male students

Students preferred to register for **presentation J than B.**

![](/images/report%202%20Persona.png)

### Page 3 Final Results

*   The **Pass percentage** of 2014J is higher than other presentations

*   The **distribution of scores** for Computer Marked Assessments and Tutor Marked Assessments is different

![](/images/report%203%20Final%20Results.png)

### Page 4 Relationship

I used a **measure** `Percentage_Below40 (%) = 
                         DIVIDE(    
                                COUNTROWS(FILTER('studentAssessment_BBB', [score] < 40)),    
                                COUNTROWS('studentAssessment_BBB'),                                     0) * 100 
`

to calculate the **fail percentage for each assessment.**

***

![](/images/report%204%20Relation.png)

### The final words

![](/images/Wrap-up.png)


Education-Focused Analysis: How Assessment Types Shape the Final Result

#### **Question: when the company gives you a dataset to build a dashboard before the interview, what should you do?**

# Step1. Check the datasets, and understand the meaning of each column.

I received two tables

###### Table 1. Copy of Life Satisfaction VS GDP Extract.csv

Columns: **Country Code, Country, GDP per capita, Life satisfaction, Region, Year**

![](/images/Overview%20of%20life%20satisfaction%20and%20gdp%20extract.png)

Use a pivot table to check the range:

![](/images/Pivot%20Summary%20for%20GDP%20and%20Satisfaction-6ff36840.png)

###### Table 2. Copy of Adult Education Extract.csv

Columns: **Country code, Country, Education level, Proportion of population, Year**

![](/images/Overview%20of%20adult%20education%20extract.png)

Use a pivot table to check the range:

![](/images/Pivot%20Summary%20for%20Education%20Level-061f5c5e.png)

So I have concluded that the datasets have

**4 dimensions that I can use: Year, Region, Country, Education Level**

**3 measures that I can compare: GDP per capita, Life satisfaction, and Proportion of education level**

# Step 2: Identify my objects

*   Show trends for each country

*   Correlations among GDP per capita, Life satisfaction, and Proportion of education level

# Step 3: Design the dashboards

3 Dashboards for 3 purposes

1.  From GDP to see Life satisfaction and education level

2.  From education level to see GDP and Life Satisfaction

3.  Relation Between Education Level and GDP per capita

# Step 4: Data Validation and Data Cleansing

Fortunately, two tables have no missing values and they can be directly imported into Tableau.

# Step 5: Build the dashboard

The finished dashboard has been published: [https://public.tableau.com/app/profile/xiang.li5182/viz/PersonalProjectsIvyLi/KeyEducation?publish=yes](https://public.tableau.com/app/profile/xiang.li5182/viz/PersonalProjectsIvyLi/KeyEducation?publish=yesKey)

# **User Stories**

#### Dashboard 1 From GDP to see Life satisfaction and education level

*   Action1: select different **regions** to check the minimum, median and maximum GDP values, Life Satisfaction and the average Education Level

*   Action 2: Choose **which year** users are interested in

*   Action 3: Choose **a country** on the map and see GDP, Life Satisfaction and Education Level (**tooltips** also show the same information)

![](/images/Dashboard1.png)

#### Dashboard 2: From Education Level to see GDP and Life Satisfaction

*   Action 1: Choose one **country** to check the **trend** of GDP per capita, Life Satisfaction and Education Level

*   Action 2: Choose the **year** and **Education Level** to check which country has a higher value in Education Level

![](/images/Dashboard2.png)

#### Dashboard 3: Relation between Education Level and GDP per capita

*   Action: Choose **which year** users want to **compare GDP and Education Level** among countries to understand if Education Level **correlates** with GDP

![](/images/Dashboard3.png)

# Final words

#### **The dashboarding shows strong interactive features, like adding year slicers and providing filters for all dimensions.**



A self-service platform for GDP, Life Satisfaction and Education Level

# Table of Contents

1.  Chapter 1 - Project Overview

2.  Chapter 2 - Data Visualisation

3.  Chapter 3 - Data Validation

4.  Chapter 4 - Logistic Regression

5.  Chapter 5 - Feature Importance

6.  Chapter 6 - Recommendation

## Chapter 1 Project Overview

The event is that Dig Data cooperated with BT Group to provide an opportunity to experience the "real world" business problem. Logistic regression is popular in the business environment as there are many binary values (spam email or not, cancel the contract or not, promotion or not and so on).

In this project, I am going to use a **logistic regression model** to predict customer churn and identify the key features.

Meanwhile, I will also use **Power BI** to give the audience some general ideas about key influencers.

You can find the Jupyter Notebook on my [GitHub](https://github.com/xiangivyli/data-science-portfolio/blob/main/part_b_bt_customer_churn_prediction_ml/BT_Customer_Churn_Prediction.ipynb).

You can also find the Published report on my novyPro [portfolio](https://www.novypro.com/profile_projects/xiangivyli?Popup=memberProject&Data=1682116603119x930500300458921200).

### Background Information

BT Group is investigating why some customers stop using BT services (churn), including:

*   Build a model that predicts which customers are highly likely to leave

*   Interpret the model to understand what influences the customers' decision to leave

*   Make recommendations to the business:

    *   How to engage the customers to stay with BT?

    *   How to retain more customers?

## Chapter 2 Data Visualisation

Firstly, I used Power BI to do the initial exploration. You can see we have 7,043 customers, generating 16.06 M in revenue. Then 1,869 customers chose to leave, accounting for 26.54% of the total customers. 

There are several points we can get:

*   There is no difference between genders

*   The majority of churn customers are a non-senior citizen

*   Electronic check is the most popular payment method

*   The month-to-month contract is the type of contract that most churn customers signed

*   Fibre optic internet service is the most popular among churn customers

*   Key influencers visual calculates that the likelihood of churn for the contract is month-to-month increased 6.32 times 



![](/images/BT%20Churn%20Customer%20Features.png)

## Chapter 3 Data Validation

BT provided 7,043 rows (customers) and 21 columns (features). The "Churn" column is the target, showing whether the customer left the company (Churn = 'Yes') or not (Churn = 'No').

![](/images/Data%20Snapshot-e968762f.jpg)

The dataset includes information about:

*   Customers who left within the last month - the column is called Churn

*   Services that each customer has signed up for - phone, multiple lines, internet, online security, online backup, device protection, tech support, and streaming TV and movies

*   Customer account information - how long they've been a customer, contract, payment method, paperless billing, monthly charges, and total charges

*   Demographic info about customers - gender, if they have partners and dependents

You can find the dataset and dictionary on my [GitHub.](https://github.com/xiangivyli/Data-Science-Porfolio/tree/main/BT%20Customer%20Churn%20Prediction/Data)

Steps to adjust the dataset:

*   delete customerID, the feature is unuseful

*   delete null values, TotalCharges column has missing values

*   adjust datatype, TotalCharges should be numeric rather than object

*   merge some categories, InternetService has 'No' and 'No internet service', they should be merged together, others do the same

Steps to adjust the dataset for preparing the model:

*   convert 'yes' to 1, 'no' to 0

*   convert 'Female' to 1, 'male' to 0

*   generate dummy variables for categorical features ('InternetService', 'Contract', 'PaymentMethod')

*   scaling continuous values to avoid bias ('tenure', 'MonthlyCharges', 'TotalCharges')

After the data validation and preparation, the dataset contains **7,032 rows and 27 columns** without missing data.

## Chapter 4 Logistic Regression

Step 1 split data: split the dataset into a training dataset and a test dataset, I use an 8:2 ratio to split it.

Step 2 modelling: define a function which generates the model and calculates confusion matrix, accuracy, f1-score, coefficients and corresponding probability.

The result is AUC=0.85

<div style="text-align: center">![](/images/ROC-2086c909.png)</div>

### SMOTE Optimisation

As the number of 0 values is much less than 1 value in our dataset (imbalanced), the accuracy is not able to reflect if the model is good. Therefore, I used Synthetic Minority Over-sampling Technique (SMOTE) to oversample the 0 value to make the number of 0 value equal to another one.

The optimised result is AUC=0.86

<div style="text-align: center">![](/images/ROC-SMOTE-1fe57aea.png)</div>

## Chapter 4 Feature Importance

The purpose of this project is to identify key features, I extracted coefficients for each feature and mapped them in a bar plot.

<div style="text-align: center">![](/images/Coefficients-50e2bdfb.png)</div>

From the bar plot, it is easy to say that tenure has a negative relationship with churn, which means if customers sign contracts longer, they are less likely to leave BT service. Meanwhile, the total charge pushes customers to leave. Other features also play a role in churn, like customers who are loyal prefer no internet service and a two-year contract rather than a monthly contract.

## Chapter 5 Recommendation

For the marketing team, I have several pieces of advice:

*   Provide a bonus for a longer contract

*   Add online security and phone service for each customer

*   Enhance tech support service

Furthermore, the current status of "0" (not churn) does not mean they will stay with BT forever

*   Use the model to analyse customers who may leave (high probability), then connect these customers for feedback

## My Certificate

![](/images/Xiang%20Li-c01874a7.png)


BT Customer Churn Influencer

Divider

Pfizer is one of the biggest multinational biopharmaceutical companies and medicine suppliers with a robust research capacity (MarketLine, 2021). Its revenue dramatically increased to over 24 billion U.S dollars with the Covid-19 vaccine in the Third Quarter of 2021(Statista, 2021).

Environmental, social and governance (ESG) scores in today’s markets evaluate the sustainable effort of companies, representing the deduction of footprint and organising eco-friendly activities, maintaining the relationship with humans, and testing the transparency of management structure (Dyllick and Hockerts, 2002). ESG scores have triggered a broader concern about the company's corporate social responsibility.

## Chapter 1 Project Overview

### Objects

This empirical analysis has 3 objectives.

1.  Identify Pfizer company’s position in the pharmaceutical industry.

2.  Visualise the trend of business aspects in Pfizer from 2016 to 2018.

3.  Apply linear regression to disclose the relationship between total assets and ESG scores.

### Brief

The process includes data cleansing, modelling, visualisation, and combining statistics to detect the rationality of the equation.

The project shows that Pfizer has a strong foundation, including tremendous assets and sufficient employees; conversely, it is on a middle-level of the biopharmaceutical companies regarding return on assets and Tobin’s Q ratio, which means Pfizer needs to focus on arousing development potential. Moreover, the regression model indicates that the ESG score can efficiently promote total assets; the relationship is that total assets increase by e5.2366 million U.S. dollars when environmental and governance disclosure scores increase by one unit.

### Dataset

The dataset and corresponding dictionary can be found in the Data folder. The dataset contains all S\&P 1500 companies listed in the US stock market over three years 2016 - 2018.

Features for each company:

*   Ticker

*   Name

*   Year

*   ISIN Number

*   SIC Code

*   GICS Industry

*   Country or Territory of Domicile

*   Number of Employees

*   Total Assets

*   R\&D Expense

*   R\&D Expense Adjusted

*   Operating Expenses R\&D

*   Cash and Cash Equivalents

*   Environmental Disclosure Score

*   Social Disclosure Score

*   Social Disclosure Score

*   Governance Disclosure Score

*   Tobin's Q Ratio

*   Return on Assets

*   Return on Common Equity

*   Gross Margin

## Chapter 2 Data Gathering

### Import Data

Data covers 326 industries. The data includes 4518 rows and 20 variables. Most are quantitative results and collect financial information like total assets, return on assets, and so on; it also includes behaviour scores from external institutions like environmental disclosure score, social disclosure score and governance disclosure score.

**You can find the** [**data**](https://github.com/xiangivyli/Data-Science-Porfolio/tree/main/ESG%20analysis%20for%20Pfizer%20$Linear%20Regression$/Data) **and Jupyter Notebook on my** [**GitHub.**](https://github.com/xiangivyli/Data-Science-Porfolio/blob/main/ESG%20analysis%20for%20Pfizer%20$Linear%20Regression$/ESG_analysis_for_Pfizer.ipynb)

```
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns
import statsmodels.formula.api as smf
import statsmodels.api as sm
from statsmodels.stats.outliers_influence import variance_inflation_factor
from statsmodels.formula.api import ols
```

```
df = pd.read_csv("./SP1500_Raw Dataset_Data Analytics in Business Assignment_2021.csv")
df.count()
```

## Chapter 3 Biopharmaceutical Company

The chapter is to achieve the first objective - identify Pfizer's position in the biopharmaceutical industry.

Step 1: Use SIC Code 2834 or 2836 to narrow the range of companies (SIC Code 2834 - Pharmaceutical, SIC Code 2836 - Biological Products).

Step 2: Identify metrics

*   name

*   year

*   country or territory of domicile

*   total assets

*   the number of employees

*   return in assets

*   R\&D expense adjusted

*   environmental score

*   Tobin's Q ratio

*   return on assets

Step 3: Drop NaN values to guarantee the rationality of results and figures.

Step 4: Calculate mean, median, maximum and minimum values.

Step 5: Visualise these metrics for comparison.

![](/images/biopharmaceutical.industry.landscape.table-bd5133a3.png)

Table 1 shows the descriptive statistics of biopharmaceutical companies with chosen variables and puts Pfizer's data aside to locate its performance level. There are 21 biopharmaceutical companies, like AbbVie, Amgen, and so on. Two companies are in Ireland (Endo Internation PLC and Perrigo Co PLC), and the headquarters of 19 companies are in America.

Pfizer is an American corporation, and its headquarter locates in the U.S. as well. Pfizer is generally higher than the average level except for Tobin's Q ratio evaluation; the median value of the cohort is 7.33, while Pfizer only scored 1.94. The data of Pfizer is near the maximum value in employees and total assets aspects.

Figure 1 uses 4 bar plots covering (a) total assets; (b) the number of employees per year; (c) return on assets; (d) Tobin's Q Ratio and gives ranking information about the position of Pfizer in the cohort. Pfizer ranks among the highest according to total assets and has sufficient employees; the number is lower than Johnson & Johnson with over 120,000 employees. Regarding the prospect's relevant indicators (Return on Assets and Tobin's Q Ratio), Pfizer does not have strengths over other companies; it is the fourth bottom of the cohort comparing Tobin's Q Ratio.

![](/images/biopharmaceutical.industry.comparison.plot-87d18dba.png)

<div style="text-align: center">*Figure 1 Overview of 21 biopharmaceutical companies in the U.S. stock market S\&P 1500 from 2016 to 2018. (a) The mean value per year of total assets. (b) The mean number of employees per year. (c) Return on assets, the mean value per year. (d) Tobin's Q Ratio, the mean value per year. (The black arrows point to Pfizer)*</div>

## Chapter 4 Development of Pfizer

The chapter is to achieve the second objective - to visualise the trend of business aspects in Pfizer from 2016 to 2018.

Step 1: Create a new data frame for Pfizer

Step 2: Identify the analysis variables

*   total assets

*   the number of employees

*   return on assets

*   Tobin's Q Ratio

*   R\&D Expense Adjusted

*   Environmental disclosure score

Step 3: Use line charts to describe the developing trend of Pfizer from 2016 - 2018

Figure 2, with time-series line graphs, demonstrates Pfizer's financial aspects. Firstly, the change of total assets from 2016 to 2018 has shown that Pfizer did not have excellent performance in 2018; the assets dropped from over 170,000 million U.S. dollars to below 160,000 million U.S. dollars while the number of employees experienced a slight decrease then increased to around 92,000 in 2018. Then using return on assets and Tobin's Q ratio as indicators of company development prospects demonstrates Pfizer tried to increase the influence and management of the company. However, Pfizer has not performed well with middle-level ranking results in Figure 1. Similarly, Pfizer continued to invest in R\&D and disclosure environmental information.

![](/images/Pfizer.trend.plot-8e7a6fc5.png)

<div style="text-align: center">*Figure 2 Overview of the development trend of Pfizer from 2016 to 2018. (a) Total assets (million U.S. dollars) per year. (b) The number of employees. (c) Return on assets calculated with profits to total assets per year. (d) Tobin's Q Ratio per year. (e) R\&D expenses per year, including research and development expenses adjusted by one-time charges. (f) Environmental disclosure score, evaluating internal environment and ecological activities by external institutions.*</div>

## Chapter 5 Relationship between the total asset and ESG score

The chapter is to achieve the last objective - apply linear regression to disclose the relationship (total assets & ESG scores).

Step 1: Drop NaN values and outliers of independent variables (3-sigma method) - get 1,965 efficient rows

Step 2: Lag ESG scores to the next year (the effect of scores needs time)

Step 3: Log total assets (the distribution is skewed)

Figure 3 visualises the scatterplots of environmental, social, and governance disclosure scores V.S. the ln(total assets), respectively. Generally, the contribution of variables is average, and it is easy to observe a positive relationship between independent and dependent variables. The scatterplots can expect the coefficients to be positive in the equation. Consequently, the least square generates the parameters and other indexes to quantity the relationships.

![](/images/regression.plot-ff723e99.png)

<div style="text-align: center">*Figure 3 Scatterplots of ESG score and ln (total assets) (a) Environmental disclosure score Versus ln (total assets). (b) Social disclosure score Versus ln (total assets). (c) Governance disclosure score Versus ln (total assets).*</div>

Table 2 presents the results of the regression. Of note, the social score variable is excluded from the equation because, in the first attempt, the P-value of the social disclosure score is 0.302, which means the relationship is not significant. Hence, the result only includes two independent variables: environmental and governance disclosure scores.

In equation (1), t-1 means lagging scores by one year. The coefficients are 0.0306 and 0.0550, P-values are less than 0.05, which means the relationship is significant. For every one-unit increase in environmental and governance disclosure scores last year, total assets will increase by e5.2366 million U.S. dollars this year. The direct impact is significantly positive.

![](/images/regression.table-11eccf20.png)

## Chapter 6 Evaluation of model

The chapter is to prove the rationality of the model.

Step 1: The distribution of residuals

Step 2: The histogram of ln(total assets)

Step 3: Test multicollinearity with the VIF values

The figures show (1) the distribution of residuals is average in the left residual plots; similarly, the histogram is well-shaped and symmetric. The three graphs indicate that the errors are normally distributed, and heteroscedasticity is not a violation. (2) Multicollinearity is not a problem when the VIF values are around 2.2 in the VIF table.

![](/images/evaluation.plot-3b7b5ef8.png)

<div style="text-align: center">*Figure 4 Residual plots (a) The left two graphs are scatterplots based on residuals of log (total assets) and environmental scores and governance score. (b) The right histogram summarises the contribution of the log (total assets).*\*\*![](/images/evaluation.table-b4079542.png)</div>

## Chapter 7 Conclusion and discussion

### Pfizer

Pfizer occupies the leading position in the biopharmaceutical industry but lacks strong growth potential when ranking return on assets and Tobin’s Q ratio. According to MarketLine (2021, p.72), ‘Robust research and development capabilities, operational performance and business performance of innovative health segment are the company’s major strengths, whereas revenue remains the cause for concern.’ Corresponding with the trend in Figure 1a, Pfizer has reported a decrease in sales data because investors waited for strategic mergers and acquisitions (M\&A) (Jared S, 2018). In that case, managers at Pfizer need to consider how they deal with urgent situations and increase assets.

### ESG scores

At the same time, the ESG scores are involved in the S\&P 1500 and have become an important indicator for evaluating companies and providing sustainable perspectives for investors (Huang and Shell, 2021). Most research has shown that the ESG score positively impacts companies’ finance (Qiu et al., 2016); however, the ESG score does not always promote financial performance in a specific context. For example, the relationship between ESG score and financial performance is negative in Latin America because of geographic factors (Duque-Grisales and Aguilera-Caracuel, 2019). The association features change with external factors. For example, the social disclosure score is a positive factor for market value (Qiu et al., 2016), while this essay’s modelling does not demonstrate the relationship. Another case is that environmental disclosure score contributes most to improving credit rating (Zanin, 2021). Although the association features are slightly different, the positive relationship that the model has mentioned is still rational considering the influence of changes in various contexts.

### Outlook

Additionally, the ESG score is lower than average in the biopharmaceutical industry. The outlook of the whole biopharmaceutical industry should change. For example, the insurance industry has a higher score than average because of the positive role of connecting employees and corporations (Banham, 2021). Unexpectedly, many biopharmaceutical companies have relatively lower ESG scores (Cooke, J. R. 2020). The reason is that pharmaceutical companies are profit dominated rather than ethical standards. In that case, the public expects the pharmaceutical industry to develop in ESG relevant direction that can help improve the population's health (Desmyter, 2020). Win-win results and joint development are general trends, so decision-makers in Pfizer should value the importance of ESG in the operational strategies. However, Pfizer has realised the significant position of ESG scores. In Pfizer’s annual review in 2020, it is easy to find that Pfizer had underlined the ESG section in the report. In the chapter, Pfizer addressed ESG-relevant indicators and set goals for further sustainable development (Performance | Pfizer 2020 Annual Review, 2021). Pfizer’s behaviour is consistent with the importance of ESG development that the model suggests.

### Limitations

As for the model, it is valid after avoiding bias and other possible violations technologically. P-value and other statistical indicators have shown the relationship is significant. However, there are still issues that need to discuss. The first point is the representative of the samples; firstly, rows including missing values account for around 30%, although the analysis limited the number of variables to get more examples. Because firm size is one factor that can influence ESG score (Drempetic et al., 2019; Tamimi & Sebastianelli, 2017), the management of small companies can not provide sufficient data. In conclusion, there is a possibility that the analysis deleted smaller companies when removing missing values. Thus it is a possible cause of bias. The second point is that the S\&P 1500 did not include Roche, one big competitor for Pfizer. So it is another reason why there is a chance that the model can not match the actual situation very well.

### Future work

The analysis can give Pfizer and other companies some ideas regarding their development direction. For example, Pfizer can identify detailed stakeholders of environmental, and governance-relevant topics. Well-designed surveys can identify these issues and collect the reflection of customers. The figure below lists five questions that can appear in a questionnaire, such as rating Pfizer's behaviour regarding environmental protection. This kind of information can help Pfizer evaluate its position and weigh the rationality of the next steps.

![](/images/questionnaire.part1-f6aea564.png)

![](/images/questionnaire.part2-f935635d.png)

## References

*   Banham, R. (2021) ‘Ranking U.S. Insurers: How They Score on ESG Performance’, Carrier Management, 8(1), pp. 26–28. Available at: https://search.ebscohost.com/login.aspx?direct=true\&db=bth\&AN=149033527\&site=ehost-live.

*   Cooke, J. R. (2020) ‘Biopharma ESG Rankings Are Hamstrung by Drug Pricing, Litigation’, Bloomberg.com, p. N.PAG. Available at: https://search.ebscohost.com/login.aspx?direct=true\&db=bth\&AN=144504021\&site=ehost-live.

*   Desmyter, S. (2020, December 23). The ESG case for the Oxford/AstraZeneca vaccine. Financial Times. https://www.ft.com/content/0ad28950-cf97-4ae9-8b08-18fdc9ffbeb1

*   Drempetic, S., Klein, C., & Zwergel, B. (2019). The Influence of Firm Size on the ESG Score: Corporate Sustainability Ratings Under Review. Journal of Business Ethics, 167(2), 333–360. https://doi.org/10.1007/s10551-019-04164-1

*   Duque-Grisales, E., & Aguilera-Caracuel, J. (2019). Environmental, Social and Governance (ESG) Scores and Financial Performance of Multilatinas: Moderating Effects of Geographic International Diversification and Financial Slack. Journal of Business Ethics, 168(2), 315–334. https://doi.org/10.1007/s10551-019-04177-w

*   Dyllick, T., & Hockerts, K. (2002). Beyond the business case for corporate sustainability. Business Strategy and the Environment, 11(2), 130–141. https://doi.org/10.1002/bse.323

*   Hopkins, J. S. (2018) ‘Pfizer Sales Disappoint as Investors Wait for Strategic M\&A’, Bloomberg.com, p. N.PAG. Available at: https://search.ebscohost.com/login.aspx?direct=true\&db=bth\&AN=140678121\&site=ehost-live.

*   Huang, N. S. and Shell, A. (2021) ‘Introducing the Kiplinger ESG 20. (cover story)’, Kiplinger’s Personal Finance, 75(11), pp. 18–30. Available at: https://search.ebscohost.com/login.aspx?direct=true\&db=bth\&AN=152570248\&site=ehost-live.

*   MarketLine Company Profile: Pfizer Inc. (2021). In Pfizer Inc. MarketLine Company Profile (pp. 1–88)

*   Performance | Pfizer 2020 Annual Review. (2021). Pfizer 2020 Annual Review. https://annualreview.pfizer.com/performance#esg-signpost

*   Qiu, Y., Shaukat, A., & Tharyan, R. (2016). Environmental and social disclosures: Link with corporate financial performance. The British Accounting Review, 48(1), 102–116. https://doi.org/10.1016/j.bar.2014.10.007

*   Statista. (2021, November 25). Pfizer’s quarterly revenue 2010–2021. https://www.statista.com/statistics/254351/quarterly-revenue-of-pfizer-since-2006/

*   SIC Code 2834 - Pharmaceutical preparations. (2021, May 7). SIC & NAICS Codes, Company Search, Business Lists - SICCODE.Com. https://siccode.com/sic-code/2834/pharmaceutical-preparations

*   SIC Code 2836 - Biological products exc. diagnostic. (2021, May 7). SIC & NAICS Codes, Company Search, Business Lists - SICCODE.Com. https://siccode.com/sic-code/2836/biological-products

*   Tamimi, N., & Sebastianelli, R. (2017). Transparency among S\&P 500 companies: an analysis of ESG disclosure scores. Management Decision, 55(8), 1660–1680. https://doi.org/10.1108/md-01-2017-0018

*   Zanin, L. (2021). Estimating the effects of ESG scores on corporate credit ratings using multivariate ordinal logit regression. Empirical Economics. https://doi.org/10.1007/s00181-021-02121-4


ESG Analysis for Pfizer

## Chapter 1 Project Overview

### Background Information

Google merchandise store is an online platform that sells google-branded products. The Google Analytics platform (a web analytics tool that stores historical marketing data and tracks the customer's real-time status) shows that in December 2017 and April 2018, the sales performance reached the highest peak – $578,246 and $585,960. But after June 2018, the trend showed a considerable decline until the lowest point ($3,269 in November 2018). The significant decrease should be that the European Commission took a hard line on antitrust behaviour and fined Google €2.42 billion in June 2017 and €4.34 billion in July 2018, respectively (European Commission, 2017, 2018). The situation lasted for two years, and the revenue started to increase in December 2020, but the peak value was much lower than before. To help the Google merchandise store recreate its historical prospects, this report uses the google analytics platform to define customers' personas and provide feasible strategies to increase revenue.

![](https://preview--wonderful-goat-bef7b.stackbit.dev/images/The_trend_of_revenue.png)

### Dataset

Data comes from Demo Account (54516992) – UA – Google Merchandise Store (UA-54516992-1) - 1 Master View (92320289).

The time range is the first quarter in 2022; the objective of comparison in the first quarter (from January 1 to March 31) in 2021, considering seasonality may influence the performance, although there are still many uncontrollable factors like pandemics (Ehrenthal et al., 2014).

### Detailed questions

1.  What is the persona (an overview of customers in marketing) of customers, and what are the characteristics of target customers?

2.  What are the most popular products? How profitable are products?

3.  What are customers’ shopping habits?

4.  What are the differences between new users and returning users?

5.  What happened during the whole shopping journey? Which stages do most consumers leave before payment?

## Chapter 2 Web Metrics

### The percentage of conversions

Understanding consumers’ activities is the first step for web analysis. As figure 1 shows, total visitors represent visitors who have entered the website; potential conversions mean visitors are likely to shop for products. Most visitors leave the website without further exploration, which are bounded visits; the left visitors may add items to carts, but only 1 – 3 % of total visitors will pay for products. This small part can be counted as conversions.![](https://preview--wonderful-goat-bef7b.stackbit.dev/images/1.The_classification_of_web_users.png)

### Objectives and key results (OKRs)

To achieve the increasing revenue goal, data analysis focused on five objectives and key results (OKRs) to answer five questions mentioned in the introduction:

1.  Identify what group of customers purchase more and depict the demographic features of customers.

2.  Identify the most popular or profitable products and find the features of top-selling products.

3.  Identify the pattern of activity of customers, such as the length of durations, used devices, and channels.

4.  Explore the different performance patterns between new users and returning users.

5.  Identify barriers during online activities, such as the stages where visitors are likely to give up making payments.

Each OKR needs corresponding key performance indicators (KPIs). Table 1 shows what KPIs are measured and the definitions of KPIs.

![](https://preview--wonderful-goat-bef7b.stackbit.dev/images/2.OKRs_and_KPIs.png)

### Hypotheses

Five hypotheses are listed below to solve five questions and achieve five OKRs.

1.  A specific group (maybe young women in the United States) has shown strong intentions to purchase Google-branded products.

2.  Clothes with the Google brand logo attract most consumers considering their practicability.

3.  Consumers often use mobile phones to complete a purchase.

4.  Returning users contribute more to the revenue.

5.  Consumers often leave after they have added products to their carts.

## Chapter 3 Persona of customers

The first analysis depicts the current customer; a persona can form a coherent picture of consumers with better clarity and completeness (Sinha, 2003; Spiliotopoulos et al., 2020). Table 2 indicates that the United States has contributed the most users and revenue; the interesting point is that India has the second most users, but the payment is not high enough.

Figure 2 below illustrates that the 18-24 and 25-34 groups use google merchandise store more, and the male uses the online store slightly more frequently than females. Men purchase more than women in the store violates the public consensus that women drive most of total purchasing (Nelson, 2020). It may be caused by the attraction of the Google brand for men. The first hypothesis should be changed because young men intend to purchase Google-branded products more. It is worth noting that the demographic data are only available for about half of the total users (the upper right corner of the graphs has annotated the number).

![](https://preview--wonderful-goat-bef7b.stackbit.dev/images/3.Country_distribution_for_users_and_revenue.png)

## Chapter 4 The performance of products

The product performance reflects customers’ mindsets or insights (Charm, 2020). Table 3 below presents the six most famous products: hoodies, T-shirts, jackets, polo, and backpack. From the data, it is easy to see that 5/6 belongs to clothing and one in the backpack, and the price did not change a lot, but the quantity has added a lot. For example, the sales volumes of Google Black Cloud Zip Hoodie and Google Unisex Eco Tee Black products have over doubled compared to the same quarter last year, and there are two new items which did not come into markets in the first quarter of 2021 have become popular too, they are both jackets. The ranking of popular products shows that consumers may pursue practical fashion and brand satisfaction simultaneously and supports the second hypothesis.![](https://preview--wonderful-goat-bef7b.stackbit.dev/images/4.The_performance_of_products.png)

## Chapter 5 The pattern of activity

The pie charts in figure 3 below compare the number of users and revenue in different traffic channels. What stands out in the four charts is that direct channel dominates the media. Comparing users and revenue in 2021 and 2022 shows that the paid search method has increased and contributed to the total revenue. What is interesting about the data is that although display methods brought 7.6% of the actual users, this group has no purchase activities. It seems that Google has invested in paid search and display advertising. Unfortunately, display advertising did not convert this group of users into expected revenue. Figure 4 provides an overview of purchasing devices, and the desktop was the primary tool for users rather than mobile phones, as the fourth hypothesis mentioned. Table 4 lists the most active pages, except for the home page and basket page, the fourth and fifth dynamic pages are about men's products. This phenomenon corresponds to the high percentage of men in users.

![](https://preview--wonderful-goat-bef7b.stackbit.dev/images/5.The_distribution_of_traffic_channels_and_devices.png)

## ![](https://preview--wonderful-goat-bef7b.stackbit.dev/images/6.The_ranking_of_active_pages.png)

## Chapter 6 Comparison between new and returning users

Understanding users’ behaviour is the premise of optimising designs or workflow; in other words, user-centred design needs an in-depth understanding of users (Holtzblatt & Beyer, 2016). Pages/session and avg. session duration are two indicators of engagement; the two indicators show to what extent users are willing to explore the content on the website. As figure 5 shows, returning users will read more pages each session, spend more time, and have a lower bounce rate than new users. The results indicate that returning users engage in the Google merchandise store more actively than new users. Naturally, returning users contribute more to the revenue, have higher e-commerce conversion rates, and have higher average order value.

![](https://preview--wonderful-goat-bef7b.stackbit.dev/images/7.new_users_and_returning_users.png)

## Chapter 7 The buyer's journey

A funnel diagram helps structure purchasing processes and discover where potential customers are missed (Ball, 2018; Morgan & Chavez, 2021). From this data on the chart, it can be seen that the most significant loss (red bar) happened during the conversion from cart to billing and shipping; only 35.07% of visitors came into the next steps. 18.13% of total visitors complete purchasing process. It is worth considering visitors’ experience when they add products to their carts and what factors cause some to give up purchasing. As long as the journey is guaranteed to be complete, Google merchandise store can achieve its revenue increase goal.

![](https://preview--wonderful-goat-bef7b.stackbit.dev/images/8.The_funnel_visualisation_of_online_payment_activities.png)

## Chapter 8 Recommendations

Staff in the Google merchandise store can be classified into five types: the webmaster, the content creator, the e-commerce manager, the marketers, and the social media manager (Clifton, 2012). Previous data analysis may help these stakeholders to understand the current problems or potential possibilities for increasing revenue.

**Action 1**: segment products and advertising to meet young men’s needs. Customers’ persona has shown that most users are 18–34-year-old men in the United States. The e-commerce managers should adjust the allocation of marketing resources can mainly focus on young men; the marketers investigate items most young men like. Camping backpacks, toolboxes, earphones, and other products are listed in one study (Rutherford, n.d.). Social media managers can use tags that young men often use in virtual advertising campaigns to attract target customers.

**Action 2**: exploit potential customers, such as Indian consumers. The second most users are in India means this group is large enough to have substantial potential purchases. The managers can consider launching some high-quality-price ratio products if the purchase restrictions are relatively low income in India compared to high-price products (India Placed 72nd on Global List with Average Monthly Wage of Rs 32,800: Report, 2020).

**Action 3**: optimise the layout and design of the websites. The duration of browsing the website is short (the new users spend two minutes, and the returning users spend four minutes each session); in that case, the content creators need to think of how to ensure visitors can find the products they want. The content creator can add apparent tags and eye-catching product pictures to attract consumers and provide informative content. By suggesting similar or relevant products when customers browse (Jiang et al., 2010), customers can easily find items and directly increase customer satisfaction and purchase records.

**Action 4**: add convenience for customers. A study proves that the quality of online stores influences consumers’ purchase decisions and consumers are more likely to purchase in a well-designed store (Liang & Lai, 2002). Website convenience decides the success of an online store (Salehi et al., 2012). Webmasters can simplify the payment process and establish a feedback circle. The billing and shipping information can be stored in the store, and consumers do not have to input the data every time.

## Chapter 9 Discussion and conclusion

### Future work

Google Analytics provides informative metrics and data that can help make decisions about business activity by measuring performance and finding trends. This report uses the most common KPIs to support the achievement of OKRs, which can lead to revenue increases. However, there are lots of spaces to explore. For example, relevant metrics such as campaigns, product coupons, active visitors, etc can also be analysed. Meanwhile, data analysis perspectives can be added with more dimensions, like exploring the percentage of converters in new and returning users. These analyses can provide more ideas.

### Limitations

One issue is that analysts cannot access external environments like competitors’ data, and it will cause a loss of meaning comparison. Another problem is that Google Analytics does not include cost information, and this issue will influence decisions. For example, the top-selling products have low profits because the production cost is high. Google Analytics itself has two problems to overcome. One is that historical errors need to be fixed; otherwise, wrong data can be involved in the analysis and influence results. Another is privacy questions because data is open to the public, and Google needs to ensure it can protect data. Finally, the learning cost is high because the number of metrics is high in this platform, and it is not convenient to find wanted metrics. To optimise the process of data visualisation, these graphs can be put together in the Google data studio (an online data visualisation platform) when finding metrics in Google Analytics is energy-consuming. The figure below is a dashboard that extracts revenue-relevant information and puts it together.

### Conclusion

This report began by identifying the aim of increasing revenue for the Google merchandise store. The analysis generated five sub-questions corresponding with five OKRs supported by KPIs to achieve the goal. The main directions are the persona of customers, the performance of products, the segmentation of customers and the workflow. According to data analysis on Google Analytics, this report found that young men are significant customers, clothes are popular items, returning users contribute more to revenue than new users, and customers may give up purchasing after adding products to their carts. Based on these results, this report suggested that stakeholders (managers and practitioners) segment customers and expand markets by attracting potential customers and increasing customers’ experience online (interface optimisation and process simplification). Google Analytics, as a web analytics tool, has shown a strong power in data analytics. Although it also has limitations, it can supplement the defects of traditional methods and provide a refreshed perspective.![](https://preview--wonderful-goat-bef7b.stackbit.dev/images/Google_Data_Studio.png)

## References

*   Ball, E. (2018). Understanding the Sales Funnel. Point of Beginning, 43 (8), 15–18.

*   Charm, T. (2020, July 24). Understanding and shaping consumer behaviour in the next normal. McKinsey & Company. https://www.mckinsey.com/business-functions/marketing-and-sales/our-insights/understanding-and-shaping-consumer-behavior-in-the-next-normal

*   Clifton, B. (2012). Advanced Web Metrics with Google Analytics (3rd ed.). Sybex.

*   Ehrenthal, J., Honhon, D., & van Woensel, T. (2014). Demand seasonality in retail inventory management. European Journal of Operational Research, 238(2), 527–539. https://doi.org/10.1016/j.ejor.2014.03.030

*   European Commission. (2017). Antitrust: Commission fines Google €2.42 billion for abusing dominance as search engine by giving illegal advantage to own comparison shopping service. European Union. https://ec.europa.eu/commission/presscorner/detail/en/IP\_17\_1784

*   European Commission. (2018). Antitrust: Commission fines Google €4.34 billion for illegal practices regarding Android mobile devices to strengthen dominance of Google’s search engine. European Union. https://ec.europa.eu/commission/presscorner/detail/en/IP\_18\_4581

*   Holtzblatt, K., & Beyer, H. (2016). Contextual Design: Design for Life (Interactive Technologies) (2nd ed.). Morgan Kaufmann.

*   India placed 72nd on global list with average monthly wage of Rs 32,800: Report. (2020, August 28). The Economic Times. https://economictimes.indiatimes.com/news/economy/indicators/india-placed-72nd-on-global-list-with-average-monthly-wage-of-rs-32800-report/articleshow/77806437.cms?from=mdr

*   Jiang, Y., Shang, J., & Liu, Y. (2010). Maximizing customer satisfaction through an online recommendation system: A novel associative classification model. Decision Support Systems, 48(3), 470–479. https://doi.org/10.1016/j.dss.2009.06.006

*   Liang, T. P., & Lai, H. J. (2002). Effect of store design on consumer purchases: an empirical study of on-line bookstores. Information & Management, 39(6), 431–444. https://doi.org/10.1016/s0378-7206(01)00129-x

*   Morgan, D., & Chavez, A. (2021). Sales Funnels Are Marketing Fire. SDM: Security Distributing & Marketing, 51 (4), 26–27.

*   Nelson, A. (2020, February 6). Women Drive Majority of Consumer Purchasing and It’s Time to Meet Their Needs. Inc.Com. https://www.inc.com/amy-nelson/women-drive-majority-of-consumer-purchasing-its-time-to-meet-their-needs.html

*   Rutherford, Z. (n.d.). Online Shopping for Men: 10 Items They Can’t Resist. SaleHoo. https://www.salehoo.com/blog/online-shopping-for-men-10-items-they-cant-resist

*   Sinha, R. (2003, April). Persona development for information-rich domains. In CHI'03 extended abstracts on Human factors in computing systems (pp. 830-831).

*   Spiliotopoulos, D., Margaris, D., & Vassilakis, C. (2020). Data-Assisted Persona Construction Using Social Media Data. Big Data and Cognitive Computing, 4(3), 21. https://doi.org/10.3390/bdcc4030021





Revenue increase strategy analysis for Google merchandise store

type

title

isDraft

colors

The styles field is controlled by Stackbit editor

styles

markdown_content

The URL path of this page relative to site root. For example, the site root page would be "/", and post page would be "posts/new-post/"

slug

date

author

excerpt

featuredImage

content

bottomSections

Enable this option to avoid a featured post (in a 'Featured posts' section) to appear duplicated in the blog page.

isFeatured

## Chapter 1 Project Overview

### Background Information

Pen and Printer Company launched a new line of office stationery 6 weeks ago. They have three sales strategies:

*   Email

    *   Customers will receive an email when the product launches, and then they will receive the 2nd email 3 weeks later.

*   Call

    *   Customers will be connected by team members, it lasts 30 mins per customer.

*   Email + Call

    *   Customers will receive an email then they will be connected by team members, it lasts 10 mins per customer.

The team would like to identify which sales strategy sell products effectively.

### Data Validation

The dataset contains **15,000 rows and 8 columns** before cleaning and validation. I have validated all the columns against the criteria in the dataset table:

*   week: from week 1 to week 6 without missing values. No cleaning is needed.

*   sales\_method:3 categories without missing values, **"email + call" category should be transformed into "Email + Call", have done.**

*   customer\_id: 15,000 unique values without missing values. No cleaning is needed.

*   nb\_sold: numeric values without missing values, the range is 7 ~ 16. No cleaning is needed.

*   revenue: **numeric values with 1,074 missing values. Need to remove missing values.**

*   years\_as\_customer: numeric values without missing values, the range is 0 ~ 63. No cleaning is needed.

*   nb\_site\_visits: numeric values without missing values, the range is 12 ~ 41. No cleaning is needed.

*   state: 50 categories without missing values. No cleaning is needed.

After the data validation, the dataset contains **13,926 rows and 8 columns** without missing data, the range of nb\_site\_visits becomes 12 ~ 37, while others keep the same.

### Detailed Questions

1.  How do the number of **customers** and **sales** differ between the three approaches?

2.  Customer persona?

3.  Revenue changes with time?

## Chapter 2 Difference in Customers and Sales

### How does the Number of Customers Differ Between the Three Approaches?

From the 6 weeks' record, we have 13,926 customers and around half of the total customers received emails when the product line was launched, and a further email three weeks later. 1/3 of customers were called by team members.  Only around 2000 customers received the product information email first then called by team members. In fact, considering sending emails is easy and can shorten calling time from 30 mins to 10 mins. The group of customers who only received calls may include the email approach as well. Before taking this action, have a look at the relationship between approach and revenue in the next chapter.

![](/images/Number_of_Customers_Approach-d97d990a.jpg)

### How do the Sales Differ Between the Three Approaches?

Firstly, the total revenue is 1,308,138.01 dollars after the new product was launched. The first plot divides **the revenue into three parts labelled with three approaches**. We can see that the majority of customers who received emails contributed 672,317.83 dollars while although the number of customers who received emails and calls is the least, they still contributed 408,256.69 dollars for the total revenue.

**The distribution of revenue per ID by revenue** can explain why the combination of email and call ranked 2nd based on the total revenue. It is obvious that the combination of the email and call approach improved the revenue and the number of new products sold per customer ID according to the two comparison plots below. My guess is that the combination method may enhance customers' memory with stronger new product exposure based on the distribution of site visits among the three approaches.

From the multiple boxplots below, we can see **the visits and revenue per ID** range in the combination of email and call is more significant than only email and call. Especially, the revenue ranges from 155 to 190 USD, which is much higher than email and call approaches.

According to the distribution of site visits and **the number of sold boxplots**, the combination of email and call enhanced customers' interest then customers checked our site more frequently and ordered our products more.

![](/images/Total_Revenue_Approach-37f888bb.jpg)

![](/images/Revenue_Approach_Boxplot-f0780319.jpg)

![](/images/Visit_Approach_Boxplot-ef7ff56b.jpg)

![](/images/Sold_Approach_Boxplot-3033344f.jpg)

## Chapter 3 Customer Persona

Before exploring more about approaches, it is time to look at the customer persona over 6 weeks.

### The Distribution of Years as a Customer

Around 1/4 of customers started buying from us in one year and most of our old customers keep in touch with us for no more than 10 years. It is vital for each company to decrease churn rate and so do we.

![](/images/Year_as_Customer-097fc5a8.jpg)

### The Distribution of Customer Location

Ranking the first three cities are California, Texas and New York. It is understandable that these cities have numerous companies that need stationery and other office products. Actually, there is a potential market in small cities that deserve advertising.

![](/images/Customer_Location-0cf40d1e.jpg)

### The Distribution of Website Visits

Customer activity is an essential metric to evaluate, during the last 6 months, the most frequent customer visit is about 25 times.

![](/images/Visit_Distribution-73f3d4d7.jpg)

## Chapter 4 Changes with Time

### The Spread of the Revenue in 6 Weeks

The total revenue in each week shows a general decreasing trend with time. In week 1, the new line was launched and most of our customers got notifications via email or call. The fact is that customers' interest decreased with time, which is the reason why the total revenue decreased.

When we focus on the difference in trend in the three approaches, The situation of the three approaches changed.

*   email approach promoted customers to purchase obviously in week 1, then reminded customers again in week 3.

*   call approach cost lots of time and work, so the effort was reflected in the middle of the 6 weeks.

*   email and call reminded customers two times and the revenue increased with time.

![](/images/Spread_Revenue_Week_Approach-0b66e0f0.jpg)

### The Average Revenue and The Number of Purchases

Since the total revenue is decided by the average revenue and the number of purchases, it is essential to look at the trend of them with time by three approaches.

We can conclude that the average revenue per ID increased with time. As the report mentioned before and it can be confirmed that the combination of email and call prompted customers to spend more during one purchase action.

Then based on the trend of the number of purchases, email had immediate effects after sending out, so the number of purchases is highest in week 1 and then dropped dramatically. The trend is the same as the trend of total revenue.

![](/images/Avg_Revenue_Per_ID_Week_Approach-fd176d60.jpg)

![](/images/Count_Purchase_Week_Approach-2c520c65.jpg)

## Chapter 5 Discussion

### Business Metrics

Since our goal is to decide which approach works well for the new product. I would recommend we use **the total revenue coming from three approaches** as our metric.

Based on our last 6 weeks' data, the combination of email and call contributed 408,259.69 dollars when the total is 1,308,138.01 dollars. Therefore, if the number is increasing, it means the new product is sold effectively.

### Recommendation

I would recommend we can focus on the following steps:

*   Using key metrics to evaluate the performance of selling the new product.

*   Transforming Call sales strategy into the combination of email and call completely.

*   Adding the third email six weeks later after the new product launch to remind customers.

*   Data Collection for in-depth analysis

    *   Improve data quality - Is there a possibility to fill the missing data in the revenue

    *   Differentiate order id and customer id - Are there customers who purchase our products more than twice in 6 weeks



Boosting Revenues through Strategic Sales Analysis (DataCamp Capstone Project)

![](/images/EER%20Diagram%20MySQL-d88ad44f.png)

# Table of Content

*   Chapter 1 - Project Overview

*   Chapter 2 - Design of Schema

*   Chapter 3 - Data Privacy and Security

*   Chapter 4 - Extract, Transform, and Load (ETL)

*   Chapter 5 - Monitor and Maintain

*   Chapter 6 - Future Work

## Chapter 1 Project Overview

The project is inspired by a data engineer job description posted by Our Future Health, to identify how diseases begin and progress in people from different backgrounds. They would like to harness genetic and clinical data to create a more detailed picture of people’s health.

MySQL is a relational database which stores structured data (each row represents one record with different features), it can create several tables with relationships that connect them together, and the purpose of storing data is to reduce duplicate data and retrieve data conveniently.

There are several objectives:

*   Integration of genetics and healthcare data

*   Easily monitor and maintain in the future work

*   Predicted outcome: research-ready, well-curated and well-documented data

Potential audience:

*   Researchers (bioinformatics scientists, surgeons, oncologists, etc)

*   Managers in NHS

*   Public

*   Funding Organisations

*   Government Health Sectors

Potential stakeholders

*   IT (database establishment and maintenance)

*   Public (disease trend)

*   Researchers (needed data)

## Chapter 2 Design of Schema

### Step 1: Initial mindmap

Tables in the schema are divided into two parts:

*   Clinical data

*   Genetic data

As the mindmap shows, clinical data comes from NHS and genetic data comes from genetic detection organisations. The clinical data involves patient general information, and admission and treatment records. The genetic data involves detailed mutated genes and frequency and other parameters.

![](/images/Schema%20mindmap-e5a16f9b.png)

### Step 2: Use [drawSQL](https://drawsql.app/) to map it

8 Tables for clinical data:

*   Patient

*   Primary care

*   City

*   Country

*   Admission record

*   Treatment

*   Medication

*   Surgery

4 Tables for genetic data:

*   Sample

*   Detection Organisation

*   Genetic Info

*   Gene

![](/images/Schema%20design-d7ac6c28.png)

### Step 3: Import SQL into MySQL

I use drawSQL to generate [SQL queries](https://github.com/xiangivyli/data-science-portfolio/blob/main/part_a_database_design_healthcare_research/dataplatform_for_healthcare_research.sql), and then use MySQL to run them.

## Chapter 3 Data Privacy and Security

The preparation of the database has finished, before populating the database, it is essential to talk about ethical considerations as the data is connected to patient privacy. Patients may suffer potential harm when their data is used for research purposes like revealing sensitive information. NHS currently has [information governance](https://www.england.nhs.uk/ig/about/) which protects patients. "Secondary use" must only use data that will not identify individuals.

The pre-requisite includes:

*   Obtain the appropriate consent from patients

*   de-identify the data to protect patient privacy

*   ensure data is only used for approved purposes

Relevant technology includes:

*   Data anonymisation

*   Data encryption

*   Data access control

*   Data masking

*   so on

## Chapter 4 Extract, Transform, and Load (ETL)

The data comes from two sources: Electronic Health Records (EHRs) and genetic detection organisations. There are several steps to do it:

*   obtain **permission** from administrators and patients

*   establish the **relationships** between EHR and the database (needed data)

*   **extract** data from EHRs (APIs, SQL)

*   **transform**: revise the message formats and map coding systems (Apache Spark)

*   **load**: import data and ensure the data is complete and accurate

## Chapter 5 Monitor and Maintain

When data is generated with time and simulated in the database, the ETL process should be implemented periodically. **Set up scheduled ingestion of data from the EHR databases to an analytical database (Airflow)**

*   at the correct time

*   with a specific interval

*   in the right order

## Chapter 6 Future Work

Coming back to the original purposes, the database is for healthcare researchers, considering the security and management of the database, maybe it is a good idea to set up a separate database for data analysis or views in MySQL for researchers.

Last but not least, the update for the database is an iterated process. Getting feedback from researchers or NHS managers, and adding or deleting columns will be followed in the future.

The slides I presented [here](https://github.com/xiangivyli/data-science-portfolio/blob/main/part_a_database_design_healthcare_research/0317Data%20Platform%20Design%20for%20healthcare%20research.pptx). Feel free to comment on it.


Data Platform Design for Healthcare Research

# Table of Content

1.  Chapter 1 - Project Overview

2.  Chapter 2 - Import data

3.  Chapter 3 - Create tables and populate them

4.  Chapter 4 - Set constraints and relationship

5.  Chapter 5 - Final two tips

## Chapter 1 Project Overview

Normalisation is a process to store data while reducing data redundancy and keeping data consistency at the same time, it follows the common 3 Normal Forms and 2 Strict Normal Forms plus the Boyce-Codd Normal Form. This project shows how to split a complete table into a database with appropriate relationships and constraints in SQL Server.

This project mainly focuses on logic and techniques. There are two aspects this project show:

*   The method of extending the dimension tables (Snowflake Schema)
*   The achievement of referential integrity (Build primary key and foreign key relationship, Choose actions on update or delete)

**You can find my complete SQL codes on** [**my GitHub**](https://github.com/xiangivyli/data-science-portfolio/blob/main/part_a_normalisation_sql_server/Split_a_table_into_a_schema.sql)**.**

**The data comes from** [**Trenton Mckinney's GitHub**](https://github.com/trenton3983/DataCamp/tree/master/data/2019-12-13_intro_to_relational_databases)**.**

## Chapter 2 Import data

SQL Server 2019 Import and Export Data Wizard imported an Excel table into my SQL Server Database. Then, have a look at the original table.

<div style="text-align: center">![](/images/10.%20Original%20table.png)</div>

It has 8 attributes:

*   firstname

*   lastname

*   university

*   university_shortname

*   university_city

*   function

*   organization

*   organization_sector

To achieve the map of tables below, the project will use a snowflake schema to organise these attributes and add two auto-incrementing ids for dim_professors and fact_affiliations, respectively.

```
-- Example to add surrgate key while setting it as primary key
ALTER TABLE dim_professors
ADD id INT IDENTITY(1,1) PRIMARY KEY;
```

<div style="text-align: center">![](/images/0.%20Schema.png)</div>

## Chapter 3 Create tables and populate them

This database mainly records what role professors play in each organisation, a professor can have different functions in an organisation of one university for the fact table, and professors, universities and organisations have their own dimension tables.

So, it has 1 fact table and 3 dimension tables

*   affiliations (fact table)

*   professors (dimension table)

*   organisations (dimension table)

*   universities (dimension table)

There are 3 things to consider when creating tables

*   datatype

*   nullable

*   primary key

### **datatype**

Most attributes are **varchar()**, the maximum should be decided by the maximum length of the current record but should leave some space.

The column **university_shortname** should follow the fixed format and use **char(3)**.

### **nullable**

Most attributes should not be null except the [function] attribute.

### **primary key**

For affiliations and professors, the primary key can use created surrogate key.

For organisations and universities, the names of organisations and universities are good choices to identify each row.

**With CREATE, VARCHAR(), and PRIMARY KEY, the four empty tables are created in SQL Server.**

**With INSERT INTO, SELECT DISTINCT columns FROM the original table, they are populated.**

<div style="text-align: center">![](/images/3.%20Created%20tables.png)</div>

## Chapter 4 Set constraints and relationships

The final relationships should be

*   M:1 relationship from professors to universities with university_shortname column

*   N: M relationship from professors to organisations with affiliations table, divide it into 2 steps:

    *   1:N relationship from professors to affiliations with professor_id column

    *   1:M relationship from organisations to affiliations with organisation column

The key part of this chapter is to talk about how to achieve the connection between organisations and professors with affiliations. Because a professor can have many functions in an organisation while an organisation can have many professors, there is a many-to-many relationship. A method is to use an intermediate table to become the bridge.

### Step 1 a. Add a professor_id column in the affiliations

### b. Reference the professor_id from professors to affiliations;

### c. Set when a deletion is attempted in the referenced table (i.e., professors),  it will not be allowed if there are any dependent rows in the referencing table (i.e., affiliations)

```
ALTER TABLE affiliations
ADD professor_id INT,
    CONSTRAINT fk_affiliations_professors FOREIGN KEY (professor_id)
	REFERENCES professors(id) ON DELETE NO ACTION;
```

<div style="text-align: center">![](/images/5.%20Add%20professor%20id.png)</div>

### Step 2 Populate professor_id using the professors table

```
UPDATE affiliations
SET professor_id = professors.id
FROM affiliations
JOIN professors ON affiliations.firstname = professors.firstname
                AND affiliations.lastname = professors.lastname;
```

<div style="text-align: center">![](/images/6.%20Populate%20affiliations.png)</div>

### Step 3 Drop firstname and lastname

```
ALTER TABLE affiliations
DROP COLUMN firstname;

ALTER TABLE affiliations
DROP COLUMN lastname;
```

### Step 4 1:M relationship from organisations to affiliations with referential integrity

```
ALTER TABLE affiliations
ADD CONSTRAINT fk_affiliations_organisations FOREIGN KEY (organisation) 
    REFERENCES organisations(organisation) ON DELETE NO ACTION;
```

**The final diagram** shows the complete database

<div style="text-align: center">![](/images/9.%20Final%20diagram.png)</div>

## Chapter 5 Final Two tips

### Tip 1 Referential Integrity: ON DELETE NO ACTION

To keep data consistent, there are some options for referenced tables when a row is deleted. The project chose NO ACTION, it prevents the deletion of rows if it is referenced.
Test it with

![](/images/8.%20Referential%20Intergrity.png)

### Tip 2 INFORMATION_SCHEMA

The INFORMATION_SCHEMA can check tables, columns, constraints and other metadata.

```
SELECT constraint_name, table_name, constraint_type
FROM INFORMATION_SCHEMA.table_constraints;
```

The constraints will be extracted in a table, super convenient, right?

<div style="text-align: center">![](/images/7.%20Constraints.png)</div>



Normalisation for professors in organisations with SQL Server

I am obsessed with SQL Server now mainly because of its power. This time, I will show 3 steps to clean data, also involving some tips for easy use.

The demo dataset I downloaded is covid-19 deaths, considering the end of the pandemic, it is time to reflect on the three years.

You can find the original dataset from Our World in Data: [Covid-19 Deaths](https://ourworldindata.org/covid-deaths).

You can find the final dashboard on my Power BI portfolio: [Covid Deaths R.I.P.](https://www.novypro.com/project/covid-deaths-rip-power-bi)

## **0. Preparation Work**

Create new database → Download data → Import (SQL Server Import and Export Wizard) → Destination: SQL Server Native Client 11.0/Server name/Database

\<div style="text-align: center">![](/images/1.%20Import%20Data.png)\</div>

## **1.Delete redundant rows and columns**

In the dataset, the data has four columns: continent, location, date, total\_deaths

There are 2 issues needed to fix:

1.  the date started too early and there is a period of time when the pandemic has not begun

2.  the location includes bigger areas, like continents and income segmentation

3.  the continent is not needed in the final analysis, but it is needed when deleted unuseful locations

\<div style="text-align: center">![](/images/2.%20Check%20redundant%20data.png)\</div>

So the code is

```
-- Step1 row: Delete rows where total_deaths or continent is null
```

```
DELETE FROM [PortfolioProject].[dbo].[CovidDeathsCountry]
WHERE total_deaths IS NULL OR continent IS NULL;
```

```
-- Step1 column: Delete columns that are not used
```

```
ALTER TABLE [PortfolioProject].[dbo].[CovidDeathsCountry]
DROP COLUMN continent;
```

Then I get the data that I need

\<div style="text-align: center">![](/images/4.%20Delete%20Rows.png)\</div>

## **2. Be careful about the datatype**

Although SQL Server identifies datatypes automatically, it is necessary to check each datatype, in this case, the data type is not correct: total\_deaths is varchar (255).

So I replaced the datatype with BIGINT:

```
-- Step2: Alter datatype
```

```
ALTER TABLE [PortfolioProject].[dbo].[CovidDeathsCountry]
ALTER COLUMN total_deaths BIGINT;
```

## **3. Aggregate data**

Because I need a total number globally, I need to add the total deaths on the same day.

Firstly, I need to add a new column named: world\_deaths

```
-- Step3: Add columns world_deaths
```

```
ALTER TABLE [PortfolioProject].[dbo].[CovidDeathsCountry]
ADD world_deaths BIGINT;
```

Then I used a subquery to calculate the sum then assign the value to the corresponding cells.

```
UPDATE [PortfolioProject].[dbo].[CovidDeathsCountry]
SET world_deaths = subquery.sum_deaths
FROM (
       SELECT date,
       SUM(total_deaths) AS sum_deaths
       FROM [PortfolioProject].[dbo].[CovidDeathsCountry]
       GROUP BY date) AS subquery
WHERE [PortfolioProject].[dbo].[CovidDeathsCountry].date = subquery.date;
```

I got the table that I could use in Power BI now, right-click then Save Results AS

\<div style="text-align: center">![](/images/6.Clean%20Data.png)\</div>

## **Final Tips**

### Shortcuts

I watched a video talking about easy-to-use tips, thanks to[ Ike Ellis.](https://www.youtube.com/watch?v=lwlRI-IpGLA\&t=506s)

I only listed 2 tips I used a lot this time.

The first one is that Query Shortcuts (Tools/Options/Environment/Keyboard/Query Shortcuts)

Because I often checked if the data was cleaned correctly, I used Ctrl+F3 to count the rows, and Ctrl+F4 to select the top 1000 rows.

\<div style="text-align: center">![](/images/Shortcuts.png)\</div>

Then I could choose the table and use the shortcuts to check the current stage quickly.

### Customised Colour

I often change themes or colours to create a comfortable environment. This time I changed the location and colour of the status bar.

The setting is located in Tools/Options/Text Editor/Editor Tab and Status Bar. I set the location as the top, and the colour is pink.

Now I can be happy to play around with SQL Server.

![](/images/Status%20Bar.png)


3 Steps to Clean Data in SQL Server

I still remember when I met Python for the first time, Dr Higgs gave us a path where we could get the file. I felt stuck when I first saw the unfamiliar format and was too shy to say I did not know it. I sat for around 10 mins without doing anything until Dr Higgs saved me.

So in my first blog, I want to talk about local directory creation and putting files in the right place in the Extract stage. There are several tips that I feel are convenient and I want to share them with you.

1.  folder structure

2.  f-strings

3.  os module

4.  requests HTTP

5.  ZipFile

I got the ideas from DataCamp taught by Stefano Francavilla ([ETL in Python](https://app.datacamp.com/learn/courses/etl-in-python))

You can find the complete code demo on my [GitHub](https://github.com/xiangivyli/python-learning/blob/main/ETL/Extract_Zip_File_Load_Locally.ipynb).

### 1. folder structure

Considering the original file is zipped, there are two folders for zipped files and unzipped files. As the folder map shows, the data directory is organised by date, for 2023 April, there are two subdirectories, a source path for zipped files, and a raw path for listing CSV files.

### 2. f-strings

f-strings is one method of formatted strings, it can put expressions of variables in the string literals with curly brackets {}, you can know more about formatted strings and f-strings from [RealPython.](https://realpython.com/python-f-strings/)

I have set my root directory, two paths, and the destination file name (identified with date), and then f-strings can easily combine these variables together into a complete path.

![](/images/f-strings.png)

### 3. os module

os module is a way to manipulate directories, like create, move, read, etc

I have a defined function for creating folders that I mentioned before, like data, 2023April, source, raw

```
def create_folder_if_not_exists(path):     
    os.makedirs(os.path.dirname(path), exist_ok=True)
```

My local folder has nothing before

![](/images/folder%20before.png)

Then I run the following code

```
create_folder_if_not_exists(source_path)
```

My local folder has a source path

![](/images/folder%20source%20path.png)

Then I run the following code for creating a raw path

```
create_folder_if_not_exists(raw_path)
```

My local folder has a raw folder

![](/images/folder%20raw%20path.png)

### 4. requests HTTP library

It is a straightforward way to get data from a URL, you can find more introduction in the [Real Python.](https://realpython.com/python-requests/)

As the blog mainly talks about local directory settings, I would not speak a lot about requests, you can just how to use `get`

```
#source headers or content and store them to the 'response'
response = requests.get('URL')
```

Then you can write the content in the source path, wb for binary files (non-textual data, like zipped files)

```
#write content into source_path
with open(source_path, "wb") as source_file:    
     source_file.write(response.content)
```

### 5. ZipFile

Before using data, unzipping files into CSV files is a must-do. ZipFile like the Bandizip application, lists all files included with `namelist()`

Do you remember that our files are stored in the source folders now and still is zipped? :)

We need to extract files and then store them in the raw path.

The code is

```
with ZipFile(source_path, mode="r") as f:    
# Get the list of files and print it    
    file_names = f.namelist()    
    print(file_names)    

    # Extract the csv files with for loop    
    for file_name in file_names:        
       csv_file_path = f.extract(file_name, path=raw_path)
```

I am excited to show the final results. Currently, these files stay in the right place now and are ready for the next blog. :)

![](/images/final%20source.png)

![](/images/final%20raw.png)

Okay, these are what I want to tell you in my first blog and they are also the steps of extracting data from a URL. Let me just recap what has been discussed so far.

*   The purpose of the blog is to store online data locally.

*   The folder structure is designed in advance, it is divided into a source path for zipped files and a raw path for unzipped files.

*   The f-strings method is a tip to construct a path in an easy way.

*   The os module creates local folders with code rather than right-clicking.

*   The request library gets data and then we can write it into the source path.

*   The ZipFile library unzips files and stores them in the raw path.



5 Tips to Store an online zip file locally(Python)

Problems appear naturally sometimes.  When I would like to use SQL Server to analyse a dataset coming from Kaggle, this dataset is an SQLite file containing 6 tables. My work became how to import SQLite files into SQL Server. I want to introduce the most efficient way to learn coding with ChatGPT this time. Some points are disclosed here: **structured questions result in structured answers, comparison and analogy help comprehension, find the easy material** and so on.

<div style="text-align: center">![](/images/Dataset.png)</div>

DataSource: [Kaggle 18,393 Pitchfork Reviews](https://www.kaggle.com/datasets/nolanbconaway/pitchfork-data).

## 1. How to ask ChatGPT with prompt engineering:

*   **Give a context**

*   **Clear purposes for the final results**

*   **Decide how the answer is structured**

Here is my example:

I have an SQLite file locally, it contains 6 tables. I would like to extract these tables and import them into SQL Server automatically. Could you please teach me how to achieve it with Python step by step?

Let me divide it into my key points:

*   **Context**: I have an SQLite file locally, it contains 6 tables.

*   **Purposeful results**:  I would like to extract these tables and import them into SQL Server automatically.

*   **Structure desired answers**: how to achieve it with Python step by step

Here are the steps that ChatGPT answered:

<div style="text-align: center">![](/images/Answer1.1.png)</div>

<div style="text-align: center">![](/images/Answer1.2.png)</div>

<div style="text-align: center">![](/images/Answer1.3.png)</div>

<div style="text-align: center">![](/images/Answer1.4.png)</div>

<div style="text-align: center">![](/images/Answer1.5.png)</div>

<div style="text-align: left">## 2. Google or watch YouTube videos to understand each item, a demo is better</div>

#### 2.1 The first thing is to understand the two libraries, sqlite3 and pyodbc.

sqlite3 is to manipulate sqlite files with Python, pyodbc is to control SQL Server with Python.

I did not read the official documentation ([sqlite3](https://docs.python.org/3/library/sqlite3.html) and [pyodbc](https://learn.microsoft.com/en-us/sql/connect/python/pyodbc/step-1-configure-development-environment-for-pyodbc-python-development?view=sql-server-ver16) ) completely, what I need to do is to know what work these libraries can do, that is enough.

I also found videos on YouTube (feeCodeCamp.org's Channel: [SQLite Databases With Python](https://www.youtube.com/watch?v=byHcYRpMgI4&t=3954s) and Jie Jenn's Channel: [How to Connect To SQL Server in Python](https://www.youtube.com/watch?v=g69lFxZdcVQ&t=16s) ) and in the process, sqlite3 extracts data (table names, column names and datatypes) and pyodbc creates tables and inserts data.

#### 2.2 The second thing is to understand the cursor function.

The cursor() appears frequently in the process, so it deserves to learn deeply.

I found an amazing image coming from[ Codecademy](https://www.codecademy.com/learn/learn-advanced-python/modules/database-operations/cheatsheet) and it has a comprehensive tutorial about Database Operations on Codecademy.

**Do not need to spend time on obscure material, find vivid material to read.**

*   execute() provides an area to run SQL statements in Python

*   fetchall() pulls data from a SQLite file

*   commit() to make changes to the database

*   close() to close the database connection

<div style="text-align: center">![](/images/cursor-7d81ebdb.png)</div>

#### 2.3 Have some conversations with ChatGPT if I have confusion about one point

For example, I did not understand why in the loop, it can iterate over each table using

```
for table_name in table_names:
       table_name = table_name[0]
```

The conversation is

<div style="text-align: center">![](/images/question%201.png)</div>

I have some ideas about how it worked, but I did not understand why it can iterate these tables one by one. Then I double-checked:

<div style="text-align: center">![](/images/question%202.png)</div>

Suddenly, I recalled the principle of for loop and **still remember an animation where there is a line of people coming into a room one by one** and understood that during for loop, the table_name would be assigned to each element one by one.

I did not find it online so I draw it coming from my memory:

<div style="text-align: center">![](/images/for%20loop.png)</div>

I comprehended it and moved on to the next confusion:

what is  `PRAGMA`?

<div style="text-align: center">![](/images/PRAGMA.png)</div>

<div style="text-align: center">![](/images/PRAGMA.%20EXAMPLE.png)</div>

To understand it, I used an analogy to help me understand it, as in SQL Server, INFORMATION_SCHEMA.COLUMNS is to extract column information. I could easily understand how it works. The efficient way is to compare them.

<div style="text-align: center">![](/images/comparison%20between%20PRAGMA.png)</div>

I figured out all my confusion and made sure I understood all codes.

### 3. Go back to the codes that ChatGPT provided and test it

I used VS Code to run and replaced the route of the SQLite database file, DRIVER, SERVER, and DATABASE for the connection.

I found that there was an issue in step 5, that was the datatypes are not right, and not every column is VARCHAR().

I told ChatGPT:
During step 5, the datatypes should correspond to each column, could you please extract the datatype from SQLite and use it automatically when creating tables in SQL Server?

*   **Give a context**: in step 5

*   **Clear purpose:** extract datatype and use it when creating tables

ChatGPT gave me an updated code:

<div style="text-align: center">![](/images/Update.png)</div>

Now, I have gotten enough codes for me to achieve my goal, I put everything together and got this in my SQL Server:

<div style="text-align: center">![](/images/Result.png)</div>

<div style="text-align: left">**You can find my final code on my** [**GitHub.**](https://github.com/xiangivyli/python-learning/blob/main/ETL/Extract_SQLITE_File_Load_SQL_Server.ipynb)</div>

### 4. Final Words

What I would like to show is how to learn code quickly by getting your hands dirty, and focusing on the final result that you want to achieve rather than trying to understand everything (I feel overwhelmed when facing all kinds of IT terms). What I can do is make the small step every day and I know that I will arrive somewhere I wish I could.

P.S. Make sure you understand every function using this method, otherwise, you will not be able to debug if there is something wrong. Good Luck :)


Ivy's Portfolio Keywords