OnlineRetail dataset, Analysis#3 - Power BI

Analysis 3rd attempt using MS Power BI on the DataCamp - OnlineRetail dataset

---

Additional Power BI #1 - Frequency of purchased quantities in countries

Additional Power BI #2 - Clustering with Power BI

---

In the first two runs, I analyzed the dataset with Python and SQL with the help of the in-built visualization of the Notebook offered on DataCamp website and also using matplotlib for special purposes.

Dataset reference: The original dataset (xlsx file) was downloaded from the Dataset owner's site.

The complete dataset and the BI solution is zipped. The package sizes 26 MB and requires installed Microsoft Power BI software (ver 2.130+, made wtih ver 2.131.1203.0).

Microsoft (MS) Power BI software is able to load data easily from Excel (or from multiple files or online databases) and recognize data types automatically so I only had to concentrate on visualization of the required information. I created the simplest figures with minimal style adjustments, as making more elaborate or eye-catching visuals would have taken nearly twice as long to complete the project.

Before the questions and answers, here you find some general information on the data and the dataset:

First of all, in general the most important data for Sales&Management is what are the flagship products? Of course they already know, but this is a proof of properly working queries on a well designed database/dataset. See in table, and in graph format:

It is important to know the dataset size, the amount of countries and Users (website user Customers), see the numbers on the right:

or evidently being UK centered it is also benefitial to know the most returned goods in UK or even better to know the users (identifiers) who have returned the most amount of goods, to extract their list and to target them either by some discount or by feedback questionnaire to understand the reason of returns... with an aim to care with them and regain their trust and loyalty toward our company and our products.

In the order of the questions, let's go through the answers.

1) The most returned items

The number of return related entries in the dataset is around 10 620, sent back by 1590 Customers from 30 countries.

The details of the Returns - general dashboard

Not surprisingly the returned goods in highest amounts are related to UK customers, it is a direct consequence of that the most purchases are originating from there, as well (data not shown).

At the bottom Treemap, UK related transactions are excluded to demonstrate the contribution of different countries in the amount of returned goods (the tile graph may be optimized in size).

2) Profits earned in the UK, on different timescales

A simple demand, easy to answer using the in-built [Month] or [MonthNo], [Quarter] part of DateTime variable. I used 'Online Retail'[InvoiceDate][MonthNo] to define a new column in the data Table. 

In case of weeks the week number can be determinde by the formula

WEEKNUM('Online Retail'[InvoiceDate])

in a new column.

The profit is considered as the sum of the purchases and returns related costs/refunds, so a 'simple' sum of Quantity*Unit price for all transactions in the given time period. Remember that returned quantities are defined with negative integers.

3) Is non-UK purchased amount significantly larger than UK quantities?

First thing to mention; Power BI does not have an in-built (statistical) t-test functionality, so at the moment I provide only such simple graphs and related data that is the base of a t-test. Expert data analyser 

Note that the larger mean value does not prove immediately that non-UK customers buy goods in significantly larger amount. However the plots would indicate so for a professional Data Scientist, the absolut proof can be made by a t-test (not shown here).

In addition to the above-mentioned dataset-related supplementary information, a lot of other facts and conclusion can be extracted from the dataset, but this post is about answering the DataCamp defined 3 questions using Power BI.

OnlineRetail dataset, Analysis#2 - SQL

DataCamp - OnlineRetail dataset,  Analysis #2 - SQL with Notebook

Previously I analyzed the below presented dataset with python with the help of the in-built visualization of the Notebook offered on DataCamp website and also using matplotlib for special purposes. I created another analysis with and MS Power BI.

I have elaborated the answers for the so called Online Retail dataset with SQL queries and zipped all required files (data and explanation as csv files, notebook as ipynb file) GitHub. If you have no access to any Notebook that is compatible* with the ipynb file then a pdf version is available here: SQL queries (0.1 MB) or the complete python and SQL solutions without the coding part Python+SQL (12 MB)

My solution includes additional details and goes beyond the required questions.

The Online retail project (original data source) is presented by DataCamp in steps:

1. Problem definition of E-Commerce Data 
   
   
   
2. Data Dictionary - explaining the content, the data types and the meaning of specific values or signs
   
   
   
   
   
3. "Don't know where to start?" session defines Exploration, Analysis, and Visualisation challenges:
1. Explore: Negative order quantities indicate returns. Which products have been returned the most?
2. Visualize: Create a plot visualizing the profits earned from UK customers weekly, monthly.
3. Analyze: Are order sizes from countries outside the United Kingdom significantly larger than orders from inside the United Kingdom?

With SQL visualization, and statistical analysis is not possible, but the data can be extracted for further analysis. DataCamp website in-built Notebook functionality can be of help in case of visualization, but t-probe statistics is still not doable on a pure SQL basis.

Duck DB's SQL is a bit different from other SQL languages so loading the data already required a specific method: 

Searching for the most returned items during the whole period defined by the dataset:

Preparing data for profit  (in UK) visualization at different timescales:

See the Python-based study or the Power BI solution on the same topic.

* Compatible Notebook readers/editors: DataCamp online Notebook (requires registration) Please note that Jupyter Notebook versions of any kind are not compatible automatically with any kind of SQL interpreters. There are ways (search online for solutions), but I recommend reading the pdf version provided above.

DC OR editable (for myself)

OnlineRetail dataset, Analysis#1 - Python

DataCamp - Online Retail dataset,  Analysis #1 - Python supported by Notebook functionality

I have elaborated the answers for the so called Online Retail dataset and zipped all required files (data and explanation as csv files, notebook as ipynb file) GitHub. If you have no access to any Notebook that is compatible* with the ipynb file then a pdf version is available here: Analysis with Python (10 MB)

My solution includes additional details and goes beyond the required questions.

The Online retail dataset (original source) is presented by DataCamp in steps:

1. Problem definition of E-Commerce Data 
   
   
   
2. Data Dictionary - explaining the content, the data types and the meaning of specific values or signs
   
   
   
   
   
3. "Don't know where to start?" session defines Exploration, Analysis, and Visualisation challenges:
1. Explore: Negative order quantities indicate returns. Which products have been returned the most?
2. Visualize: Create a plot visualizing the profits earned from UK customers weekly, monthly.
3. Analyze: Are order sizes from countries outside the United Kingdom significantly larger than orders from inside the United Kingdom?

Here are some extracted figures.

The monthly income (Quantity * UnitPrice) and repayments (Quantity * UnitPrice). The December - January returns are following the October - December increased pucrhases. "End of the year" craziness. :) 

The 2 figures below are specific visualizations of the "How often each product is returned (frequency) and in what amount?" These help to perceive the outliers: the weakest and the best-sold (with lowest return rate) products in our portfolio.

The t-test result of the hypothesis "non-UK costumers purchase goods in significantly higher amount than UK customers".  Further details can be found in the above-mentioned Notebook or pdf files.

See the SQL-based study or the Power BI solution on the same topic.

* Compatible Notebook readers/editors: DataCamp online Notebook (requires registration) or Anaconda Jupyter Notebook (requires installation) or the online version of Jupyter

DC OR editable (for myself)

DataCamp - excercises

https://www.datacamp.com/

DataCamp is a platform that offers (big) data resources for practicing data processing, analysis, and visualization.

It provides a wide range of Resources, including regularly scheduled Webinars, as well as always available Tutorials, White papers and Podcasts.

If you remember a concept but not the exact code or module to use... it's always useful to have handy Cheat sheets, which are provided for various software and languages like Python, SQL, Excel, bash, and others.

There are also job-related guides, the latest industry news for employees, and discussions on employer hiring needs - ideal for employers looking to fill positions like Data Scientist.

Basic registration and services are free, with options for customization, especially for businesses or educational institutions.

See elaborated examples of Python-based or SQL-based study or the Power BI solution on the topic of "Online Retail" dataset and related questions. The first two were analyzed with the help of the in-built Notebook on the DataCamp / DataLab website, which offered Python, SQL interpretation, and basic in-built visualization tools (as table or graph).

Online Datasets - for Practice

Easy to reach online datasets 

There are plenty of websites that provide smaller or larger datasets that can be used for free or for a certain amount of money to practice DataScience related duties such as accessing data, recognition / understanding data content and data types, data cleaning / conversion / data manipulation (ETL) and finally representation of extracted key information, drawing consequences or making predictions, clustering, segmentation, and so on, depending on the predefined requirements or the possible ways of use of datasets.

It is rarely mentioned but double-checking the extracted information is an inevitable step not to mislead yourself or the stakeholders in a real-life project.

Here you find some websites helping to find datasets:

• Datacamp jump site 
• Google Dataset search
• GeeksForGeeks blog

Here is a short list of such websites that give partially or completely free access to datasets:

• Datacamp - this website offers notebook-based data juggling, tutorials, and education in AI & ML domains
• Kaggle - this website offers competitions, but also tutorials and education in AI & ML domains
• Data.gov - USA Federal Government datasets, of course, the non-confidential part
• Earth data - collected by NASA
• Global Health Observatory Data - for those who like health-related issues or facts

Have fun with the data suiting you the best!

sn - AI & ML for Data

I have found two interesting publications about the required skills and tools for Data Analyst, Data Scientist, Data Engineer, and Machine Learning Engineer positions. They collected skill lists for each position scratching multiple job advertisements for months and used AI (ML) to extract data and also for data clustering.

The required skill lists have nothing surprising, but I found elegant the way they did it.

One important finding is that sometimes the companies do not know which type of skills they need for their planned project, those who are frequently confused on the applicant side, as well.

The studies compare the positions from different aspects, and also the advertising companies (type, size, place, etc.).

text1, text2

They shared the code that they used to gain the data (website scratching and other).

One of their well organized graphs is clustering the skills and positions.

GeeksForGeeks for developers

I have recently found GeeksForGeeks (GFG), an amazing site that offers problem-solving challenges besides tutorials and short to long-term learning projects. The original aim of the site was to help Data Scientists and IT people to widen and deepen their knowledge in their field of work related interest and also in connected domains such as math, databases, but also system design, DevOps, including Linux and Android operation systems, sofware testing and so on.

This site helped me to learn about BST (binary search tree) and linked lists besides some tricks to make codes to run faster as in some cases of the defined problems the code verifying engine also checks for runtime and accepts the written code only if the defined time limit is not exceeded.

Opportunities offered to develop coding skills are availabe in  Java, C++, C#, Javascript and Python which I chose as preferred language. The site also offers teaching and tutorials in R, Scala, Kotlin, Go, C, PHP. Quite an amazing list of nowadays widely spread languages.

I highly recommend programmers and IT people to log in as you may easily gain a lot of experience and 'encouraging Geekbits' by solving different level (simple to hard) problems whenever you have 2-30 minutes. The problem solving is for free! 😎

This site also offers a vast variety of educational courses as videos and tutorials for a wide range of topics, e.g. in the domain of AI an ML ... which cost some money of course but in a tolerable/affordable range. From time to time they offer quite appealing courses at a very low price or other times certain (up to 90) percentage of the paid amount may be regained if you finish the course with a fast but reasonable pace, within a defined time limit. 

The coding problem section has a daily update and by solving the "Problem of the Day" you gain geekbits and if you do it consecutive days then the "streak days" amount increases opening new options to develop yourself or your GFG profile. 

I highly recommend this site! Well done Geeks4Geeks! 👍

I started in June and keep coding since then. Here you find my results up-to-now with almost every day spending 20-40 minutes: