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

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 :)


Import SQLite File into SQL Server with Python and ChatGPT

# 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)

I would say this book means a lot to me when I felt overwhelmed, and anxious during my job-hunting process in the UK.

List several quotes that I really love:

> The best moments usually occur when a person's body or mind is stretched to the its limits in a voluntary effort to accomplish something difficult and worthwhile.

This quote has resonance with me. Touching something new that I am interested in is always exciting.

> Who you are, what you think, feel, and do, what you love - is the sum of what you focus on.

I can feel myself, and my passion when I focus on something.

> To build your working life around the experience of flow produced by deep work is a proven path to deep satisfaction.

Everyone needs to work but do we need the meaning of work? do we enjoy every moment during work?

> It follows that to embrace deep work in your own career, and to direct it toward cultivating your skill, is an effort that can transfom a knowledge work job from a distracted, draining obligation into something satisfying-a portal to a world full of shining, wondrous things.

Dr Newport suggested a path to a more satisfying career composed of deep work in which enhancing skills.

> One of the main obstacles to going deep: the urge to turn your attention toward something more superficial.

I have the same experience, whenever I learn obscure knowledge, I have a strong desire to scroll down my phone or short video on YouTube.

> Scroll down short videos can bring temperate pleased but destroy the capacity of deep work.

Muscles need exercise to be strong, and the mind also needs deliberate practice to focus for a long time. That is the reason why some people try meditation to control their minds.

> Give yourself a specific time fame to keep the session a discrete challenge and not an open-ended slog.

Dr Newport provided some strategies for deep work, like smart time setting rather than pushing too much. Willpower is limited.

> Implication is that you should identify a small number of ambitious outsomes to pursue with your deep work hours.

Small rewarding is a good way for insisting on deep work in the long run, I often tell myself, I can go outside if I finish this task.

> Lead measures, on the other hand, "measure the new behaviours that will drive success on the lag measures."

Some success is not decided by myself, in that case, I should focus on the measures that I can and that can help close to that success. For instance, I can't let employers give me work but I can prepare the skills they need and show that I am a good candidate.

> In multiple places throughout this book I discuss and recommend the habit of a weekly review in which you make a plan for the workweek ahead (see Rule #4).

My productivity becomes better after I write down weekly plans.

> Instead of scheduling the occasional break from distraction so you can focus, you should instead schedule the occasional break from focus to give in to distraction.

The mindset solves the obstacle transforming distraction to focus, instead, letting life get used to a focused life.

> The law of the vital few, however, remind us that the most important 20 percent or so of these activities provide the bulk of the benefit.

The 80-20 rule, also Pareto Principle, reminds me to focus on the 20 percent work all the time.


Deep Work Author: Cal Newport

![](/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 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

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

Divider

## 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)

## Chapter 1 Project Overview

The event is that **Dig Data** cooperated with Lloyds Banking Group to provide an opportunity to experience the "real world" business problem.

### Background Information

Lloyds Banking Group is launching a new loan product. Prior to the launch, they would like to use historical customer data to:

**Understand and summarise the different behaviours or attributes between customers who paid back their loans and customers who did not.**

### Dataset

**The raw data and dictionary can be found on my** [**GitHub**](https://github.com/xiangivyli/Data-Science-Porfolio/tree/main/Lloyds%20Bank%20Customer%20Profiling%20(Business%20Intelligence\)/Data)**.**

**The published report can be found on my** [**novyPro**](https://www.novypro.com/project/customer-segmentation-in-banking)**.**

The dataset is based on the American credit risk problem. The dataset contains 18,324 customers with 31 columns who:

*   Previously held a loan

*   The status of that loan - did the customer pay back the loan or not:

    *   Customers who paid back are categorised as '**Fully Paid**'

    *   Customers who did not pay back their loan are categorised as '**Charged-off**'

*   Other credit and product information that can be used to understand a customer's credit or financial behaviour, like:

    *   emp_length: employment length in years

    *   home_ownership: RENT, OWN, MORTGAGE, OTHER

    *   purpose: a category provided by the borrower for the loan request

    *   etc.

The data for customers on various transactional or credit attributes looks like this:

![](/images/Understanding%20the%20data.png)

### Data Validation

**Step 1: Missing values and duplicate values**

Although the dataset contains 31 columns, I chose what I need then validated them against the criteria in the dataset table:

*   id: 18,324 unique values without missing values. No cleaning is needed.

*   addr_state: 51 categories without missing values. **Add a table including the full name of these abbreviated states to visualise locations.**

*   annual_inc: numeric values without missing values, the range is 3,000 ~ 2,616,000. No cleaning is needed.

*   emp_length: 11 categories with **1,174 missing values. Need to remove missing values when using this metric.**

*   home_ownership: 5 categories (RENT, MORTGAGE, OWN, ANY, and OTHER) without missing data.

*   loan_status: 2 categories (Fully Paid, Charged Off) without missing data.

**Step 2: Outliers**

Validate the skewness of annual income.

mean: 80,176 dollars

median: 65,000 dollars

Considering the mean is around 23% more than the median value, the distribution of annual income is **right-skewed**. I will use the **median** value for the annual income metric.

### Methodology

This chapter used **Power BI** to depict features of customers, like the geographical features, the current distribution of loan status, and the distribution of purposes, then explore the relationship between loan status and employment length, income and home ownership.

*   the analysis adds a small dataset including U.S. state and territory two-letter abbreviations corresponding to full name.
*   the analysis adds a small dataset to confirm the order of employment length.
*   the lower left corner deleted **the missing value of emp_length**.
*   **ANY and OTHER categories were deleted** in the analysis of the relationship between homeownership and loan status.

Perspectives include:

1.  Calculate the total loan amount and check the percentage of "Charged off".

2.  Map the locations of customers.

3.  Visualise the relationship between annual income, employment length and loan status.

4.  Check the relationship between home ownership and loan status.

5.  Have general ideas about the purposes of loans.

### Customer Profiling

**You can find the Power BI file on my** [**GitHub**](https://github.com/xiangivyli/Data-Science-Porfolio/blob/main/Lloyds%20Bank%20Customer%20Profiling%20(Business%20Intelligence\)/Lloyds%20Bank%20Customer%20Persona.pbix)**.**

![](/images/Lloyds_Bank%20Customer_Persona-f48c7612.png)

### Insights

General ideas

*   The total loan is 284 million dollars.

*   14,418 customers have paid back while 3,906 have not.

Location Features

*   Customers who borrow loans mainly live on the east coast of the U.S.

*   The total loan amount rented by customers who live in California is extremely high.

Customer Financial Features

*   Income increases with employee length, and the higher income group is likely to pay back.

*   Customers who rent their home struggle to pay back.

*   More than half of customers apply for a loan to consolidate debt; the 2nd purpose is to support credit cards.

### My Certificate

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


Lloyds Bank Customer Profiling

## 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





Data Science Learning Journey

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

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

Import SQLite File into SQL Server with Python and ChatGPT

Normalisation for professors in organisations with SQL Server

3 Steps to Clean Data in SQL Server

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

Deep Work Author: Cal Newport

Data Platform Design for Healthcare Research

BT Customer Churn Influencer

Boosting Revenues through Strategic Sales Analysis (DataCamp Capstone Project)

Lloyds Bank Customer Profiling

Revenue increase strategy analysis for Google merchandise store

Blog

Data Science Learning Journey

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

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

Import SQLite File into SQL Server with Python and ChatGPT

Normalisation for professors in organisations with SQL Server

3 Steps to Clean Data in SQL Server

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

Deep Work Author: Cal Newport

Data Platform Design for Healthcare Research

BT Customer Churn Influencer

Boosting Revenues through Strategic Sales Analysis (DataCamp Capstone Project)

Lloyds Bank Customer Profiling

Revenue increase strategy analysis for Google merchandise store