There’s a lot of FREE webinars/training online and FREE tools to explore and that is the main reason why a huge number of people were lost and didn’t know where to begin their DATA journey. I am a bit picky now with training/webinars. Let us just say that there are numerous cr*p contents that will waste your time. Some free training is after your data, you are just a number to flaunt to their partners or sponsors.
Well, Power BI Essentials got me hooked! I am glued to our screen for more than three hours. Mr. Abraham Avila Jr was the trainer for that day and he taught us from installing Microsoft Power BI up to building a dashboard solution for the HR team.
After the training this is the dashboard created by Sir Abs:
DataSense Analytics Sample Dashboard
For us to receive a certificate we need to submit an enhanced dashboard to fulfil further the task and objectives given below:
Power BI Desktop Essentials
For Apple users like me, you can access the free Power BI tool through VMware.
or Apple users like me, you can access the free Power BI tool through VMware.
Power BI is intelligent enough to know the primary keys in each table you uploaded. Here the primary key for the Department Table that connects HR_Data is DepartmentID and Position to HR_Data is PositionID.
Just always double check those connections.
Power BI Data Modeling
Data Manipulation:
— Full Name: Added new column to merge LastName, FirstName, MiddleInitial and dropping the three separate columns
— Age and Employee RetentionAdded New column using age menu in the Power Query Editor
Data Analysis:
We use a high level dashboard that will identify employee segmentation required by the HR Manager.
Filters were created for users to drill down data as per their need.
— Hire Date
— Department
— Manager
— Employment Status
Power BI Dashboard that Summarises HR Records
We created a number of employees and marital status segmented by gender.
We used median age instead of average because of the widespread age in our data.
Created a ribbon chart that will show not only the trend of hiring but also the average salary for each position.
Table will magnify the best recruitment source for HR
Meters will give a color coded goal for employees average satisfaction and absences.
The Employee Table will give a high level overview for each person.
Mapping out the employees location will be useful in determining where to publish vacancies for a certain department/position.
Have I told you that this is my first Power BI dashboard? If you found this useful, kindly clap/like my post or you have some suggestions for me to enhance my data viz, please comment on this post.
I have read an online debate regarding free courses vs paid bootcamps. Some will definitely standstill in their opinion that you can’t be a data analyst by just staring at your screen and answering a few multiple choice exams. But I have seen SPARTA and Google Graduates who are now working full time in their dream jobs. Well, in the end, it is not the course, it is not your professors in your school… IT IS YOU who will determine your next step..your future in your OWN DATA JOURNEY!
What do you want to become when you grow up? The typical answer would be a teacher, a doctor or an engineer. But as early as 10 years old, I knew I wanted to be a Customs Broker. My childhood dream was pursued by taking a Bachelor’s Degree in Customs Administration. When I was in my college days I fell in love learning Tariff and Customs Law and Computation. My ONE love! Have I told you that I got 91% in Computation in the customs broker board exam? Naks! Tooting my horn!
CCBI
I am delighted to announce that after many years living abroad, the Chamber of Customs Broker (Accredited Professional Organization of Philippine’s Professional Regulation Commission) created its Singapore Chapter. And my name was inked in history as one of its founding officers.
I am blessed to be appointed as PRO and as one of Singapore Chapter officers, we will do our best to help and be a contributor in developing and enhancing Customs Broker as a reputable profession in and out of the Philippines.
CCBI SINGAPORE CHAPTER —APPOINTED OFFICERS
FREIGHT/LOGISTICS ANALYST
As professionals we need to upgrade our skills and being an analyst is an outstanding way to develop our brokerage expertise.
Below is the career map shown by Mr. Roger Lee in CCBI Goes International:
Most of the companies here in Singapore are looking not only for a Customer Service or Coordinator but they are eyeing people that possess knowledge on how to handle tools like pivot table for reporting or data handling/tracking.
CUSTOMS BROKERS OF THE FUTURE
As I have said in this post titled Evolution of Logistics Analyst, analyst is the new and best thing not only for career but also for businesses involved in logistics/warehousing/freight forwarding businesses.
And I do hope that in the near future, logistics, supply chain, brokerage companies will be aware of the power of data they own right now and how to harness it to solve their problems and how to take advantage to increase a firm’s profit.
For Customs Brokers in the Philippines that want to kickstart their upskilling or retooling, you can check-out DataSense Analytics or Project SPARTA for free courses with certificates and for those who have the budget to learn, you can look for Eskwelabs!
That’s all for now! See you in the FUTURE FELLOW CUSTOMS BROKERS!
In 2016, Cyclistic launched a successful bike-share offering. Since then, the program has grown to a fleet of 5,824 bicycles that are geotracked and locked into a network of 692 stations across Chicago. The bikes can be unlocked from one station and returned to any other station in the system anytime.
Cyclistic categorized the riders as Casual who purchased single-ride or full-day passes and Members who purchased annual memberships.
Cyclistic’s marketing strategy relied heavily on building general awareness and appealing to broad consumer segments. Marketing team created flexibility on pricing plans like single-ride passes, full-day passes, and annual memberships. But there is no effort for an upselling plan to convert the casual riders into members.
Cyclistic’s finance analysts have concluded that annual members are much more profitable than casual riders.
Ms. Moreno, Director of Marketing Team, has a clear goal: Design marketing strategies aimed at converting casual riders into annual members. As a Junior Analyst in the Marketing Team, I was assigned to answer: How do annual members and casual riders use Cyclistic bikes differently?
BUSINESS TASK
Identify, analyze, and assess the bike usage trends and patterns of casual riders/members for the Cyclistic future marketing program.
DATA SOURCES
Internal data of Cyclistics was used in this case study under Motivate International Inc, under license. It is the original data from the source. A 12-month (November 2021 to October 2022) historical dataset was downloaded and stored in Google Drive and organized in a YYYYMM_CompanyName manner. To avoid sampling bias we will use the dataset that will represent the population and anonymization was used to implement data privacy.
Reliable — it is first-party data. It is owned by Cyclistic.
Original — Dataset is straight from the source. Although already anonymized the other data is still intact.
Comprehensive — There is some nulls but the importance of it can be checked in “Process” stage.
Current — Data is up to date and has the current information of the cyclers’ usage.
Cited — The City of Chicago owns all right, title, and interest in the Data. Thus, this is a reliable data source.
Since the finance analysts already concluded the annual memberships are much more profitable, anonymized data provided will help to focus on the main task which is to define the different usage of the bike renters.
CLEANING OR MANIPULATION OF DATA
Since the data exceeds the allowable complimentary BigQuery sandbox, I loaded the dataset in pgAdmin4 to use SQL. Tableau Public was used to create an interactive visualization or a dashboard and for an easier way to check the relations between columns, I used Anaconda.
STEP 1: Creating a table named bike.
Since all of our downloaded files have similar features, it will be logical to create only one table. Using this kind command, you can easily manipulate the features into correct data types.
SELECT* FROM information_schema.columns WHERE table_name ='bike';
Checking the total rows of the table
SELECTCOUNT(*) FROM bike
Getting the head of the table
SELECT* FROM bike LIMIT 10
We need to know how many distinct observation per column
SELECT COUNT(DISTINCT ride_id) AS rideid ,COUNT(DISTINCT rideable_type) AS ridetype ,COUNT(DISTINCT started_at) AS startat ,COUNT(DISTINCT ended_at) AS endedat ,COUNT(DISTINCT start_station_name) AS startstation ,COUNT(DISTINCT start_station_id) AS startid ,COUNT(DISTINCT end_station_name) AS endtstation ,COUNT(DISTINCT end_station_id) AS endid ,COUNT(DISTINCT start_lat) AS startlat ,COUNT(DISTINCT start_lng) AS startlng ,COUNT(DISTINCT end_lat) AS endlat ,COUNT(DISTINCT end_lng) AS endlng ,COUNT(DISTINCT member_casual) AS membercasual FROM bike
Since the count and count distinct of ride_id is the same, we will not use any delete duplicate function for this dataset.
We will go on to the next dataset feature which is the rideable_type.
SELECT rideable_type, COUNT(rideable_type) FROM bike GROUPBY rideable_type ORDERBYCOUNT(rideable_type) ASC
There are 3 classes of types in this data; docked, classic and electric.
SELECT started_at, ended_at FROM bike ORDERBY started_at ASC
STEP 4: Adding/Dropping columns for data analysis.
Going back to our business question, which is to analyze the bike usage of members and casual riders, we will now list all the underlying queries that we can formulate to answer the business task.
-What is the popular start time of the cyclers?
-What is the average cycling time?
-What is the busiest and slowest day/month?
-What is the average distance per cycle?
-Where is the most active station/dormant station?
By those questions above, we will need to drop the end_station_names and station_id column which consists of almost 1 million NULLS per column. We will add columns like starttime (extracted from started_at), cycletime (elapsed time between ended_at and started_at), month/day/year (convert the timestamp to their designated month/day/year), and distance traveled (using point command).
SELECT ride_id, rideable_type, started_at, ended_at, start_station_name AS station_name, start_lat, start_lng, end_lat, end_lng, member_casual ,started_at::timeAS starttime ,extract ( epoch from ((ended_at - started_at)/60)::interval ) as cycletime ,TO_CHAR(started_at, 'fmDay') AS "day" ,TO_CHAR(started_at, 'fmMonth') AS "month" ,TO_CHAR(started_at, 'YYYY') AS "year" ,((point(start_lng, start_lat) <@> point(end_lng, end_lat)) *1.609)as distance FROM bike WHERE start_lat != end_lat AND start_lng != end_lng
We will not stop there, we want to clean it further by using the ORDER BY command on our newly added column. We found out that there are negative cycle times and less than 5 meters cycling distance in some recorded rides. We will exclude those using the command below:
SELECT* from (SELECT ride_id, rideable_type, started_at, ended_at, start_station_name AS station_name, start_lat, start_lng, end_lat, end_lng, member_casual ,started_at::timeAS starttime ,extract ( epoch from ((ended_at - started_at)/60)::interval ) as cycletime ,TO_CHAR(started_at, 'fmDay') AS "day" ,TO_CHAR(started_at, 'fmMonth') AS "month" ,TO_CHAR(started_at, 'YYYY') AS "year" ,((point(start_lng, start_lat) <@> point(end_lng, end_lat)) *1.609)as distance FROM bike WHERE start_lat != end_lat AND start_lng != end_lng) as Q WHERE Q.cycletime >2AND Q.distance >0.005
With that, we only now have 5,091,767 rows for us to upload in Tableau.
ANALYSIS SUMMARY
We will use Tableau to answer our questions that will help us to derive our recommendations.
Casual Riders had covers more distance than the Members
What is the busiest and slowest day/month?
Casual Riders: Busiest July | Slowest January | Busiest Saturday | Slowest Tuesday WHILE Member Busiest August | Slowest January | Busiest Thursday | Slowest Sunday
What is the popular start time of the cyclers?
Casual - Saturdays around 1500Hrs Members - Tuesdays by 1700Hrs
Where is the most active station/dormant station?
Most Active for Casual Riders: Streeter Dr & Grand Ave
Streeter Dr & Grand Ave is located near the most popular tourist attractions/spots in Chicago, The Navy Pier.
Most Active for Members: Wells St & Concord Ln
Wells St & Concord Ln station is surrounded by communities, schools and a church.
Is there a trend or patterns in renters usage?
Winter
Spring
Summer
Autumn
The Spring Season figure shows that there is a positive upward trend of renters at the end of season. Then there is a steady trend of bike rentals n the Summer season and negative down slope when the winter is nearing.
Classic bikes are opted to be rented in the Summer and Winter season too while electric bikes are the popular choice for Spring and Autumn season.
Is there a correlation?
To prove if there is correlation between number of rented bikes and season, I downloaded the average temperature history and loaded it in Anaconda.
import pandas as pd tempcalc = pd.read_csv('temp_calc.csv') tempcalc.corr()
Correlation
A correlation coefficient of is too far from 1, meaning there is no linear relationship.
KEY FINDINGS
Members rented 60% of the total rides.
53% of Casual Riders prefer electric bikes while 54% of Members chose classic bikes.
Casual Riders travel longer and farther than the Member.
The usual starting station of Members are from the community area while Casual Riders are from Chicago’s Bay Area.
Season affects the bike rentals.
RECOMMENDATIONS
Since demographics of bike users were anonymized, the recommendations will only evolve in the three variables: kind of bike rented, stations and time.
Create an Exclusive Membership Reward Points System:
Friday, Saturday, and Sunday by 3PM is the peak period for casual riders, thus an exclusive membership reward program will offer their leisure rides extra reward points while using bikes on the said peak periods from those popular stations.
Casual riders also be offered a discounted rate per minute for the electric bike for the first three months.
For members, they will receive reward points for every rider that will purchase an annual membership using their invite code.
Voucher Codes
Casual riders may increase their annual membership discount based on their past bike rentals to and from the popular stations.
Voucher codes for annual membership can be offered at the start of Spring Season in preparation for the upcoming peak rental season.
Exclusive for Health Enthusiast Members
Since casual riders were fond of renting an electric bike, Cyclistic may promote the use of classic bike and entice them by challenge badges that can be shared in social media.
Casual riders may have the option to save their cycled routes and to be featured on next popular/new discovered exploration routes in Cyclistic website.
Promote an extended free cycling time for members and accompanying health benefits if they cycle on weekends’ off-peak times from 5AM — 9AM.
FOR FURTHER EXPLORATION
Check the age brackets and financial capacity of casual riders and then create a focus group to discuss their takes on current membership strategy of Cyclistic.
