"I believe that the future of transport is electric."
- Elon Musk, CEO at Tesla
Data Workflow:
FRAME:
​
What is the goal?
​
As the internal Business Consultant of BMW. The overarching project goal is to analyze the current charging EV market in Washington state, charging station utilization and potential competitors’ insight. With that information, I came up with some recommendations for the stakeholders – BMW to form their business plan.
For the data collected for this project, the following variables are the primary ones for the analysis (not the exact names presented in the data, categorizations only):
​
-
Vehicle Type – Self-categorized column by CASE function in SQL, it presented data as “Regular” (ICE), “Hybrid” (HEV, PHEV) or “EV” (BEV) based on the electrification types.
-
Electrification Type – The battery types of the vehicles, the standard for the categories of the vehicle types.
-
Count of vehicles – Total numbers of the vehicles, the numbers will be updated based on different scenarios and filters applied.
I also used other variables within the analysis to assist with the processes.
​
Self-categorized flowchart:
EXTRACT:
​
Where is the data coming from?
​
The data I used is from Data.WA.gov which is a Washington state government data collection website. I mainly used the “Vehicle Registration Transactions by Department of Licensing” (24M rows) datasets and “Electric Vehicle Title and Registration Activity” (832k rows) datasets. For section 2 related to charging stations, I downloaded the CSV from the US Department of Energy for the “Electric Vehicle Charging Station Locations”, the CSV is the filtered map results, I filtered for Washington state only and included electric and level 2, DC Fast charger types only.
​
The method of retrieving the data is mainly to download the CSV file from the website and import it into Pgadmin by creating my own local database. The website also has the API available for downloading the filtered data, but I want to get the completed dataset to help me work on the visualization, so I decided to download the whole dataset. For the map result dataset, I mainly work with it within Excel as the file size is relatively small (2000 rows). I also did the research online for some information and data related to competitors’ strategies and charging systems.
​
WRANGLE/PREPARE:
​
What needs to be done to the data?
​
I used PgAdmin for cleaning the main datasets (vehicle registration activity from 2020-2023), mainly for cleaning and filtering the datasets for some unnecessary data within.
This is the new dataset I filtered for the visualizations for Tableau use. Here are a couple of notes:
​
-
Use CASE function to create the vehicle type called vehicle_new_type based on the electrification_level – categorized for three main types: Regular (Gasoline/Diesel), Hybrid (HEV, PHEV), EV (BEV)
-
Filter for non-motorcycle as this is not part of the data I cared about
-
Only included MPV and passenger car and truck
-
Only included individual use and excluded for any type of commercial use
-
Only included vehicle registration state is Washington (There are below 100k rows of data outside of WA which I decided to ignore as it’s below the 5% of total data threshold)
​
I used Python to check the null value and noted there are about 100 rows with the empty state and county which I decided to ignore as it will not impact the result of the analysis. I also noted there are about the final size of the dataset is about 21M. For the new categorization, I compiled the information in Excel and applied it into SQL, here is the Excel sheet screenshot (noted column E is the new categorized vehicle types):
The dataset for electric vehicle registrations is the extension of the main dataset, the data is clean, and I mainly used it for model years analysis so only focus on three columns (all activity performed within tableau, no data is missing):
​
-
Make (brand)
-
Model year
-
Vehicle Counts
​
For the dataset of the charging stations (fuel type dataset), Excel is the main tool for data cleaning, then I import the data into the Tableau workbook with the vehicle registration data, this dataset is used for section 2 charging stations insight. Noted that I also use VLOOKUP for the population information which I obtained from the WA government website and input to the Excel sheet (in Tableau, using average population for each county) and import the vehicle counts information from the date retrieved from SQL (in SQL, used the vehicle registrations dataset to create the table for the vehicle account by county).
​
Refer to below for the excel sheet template:
The primary columns used in the analysis are:
​
-
City
-
County
-
Zip code
-
Population (average, new created column, for Tableau map purpose)
-
BEV and PHEV count (average, new created column, for Tableau map purpose)
-
EV Level 2 EVSE Num
-
EV DC Fast Count
-
Latitude (for Tableau map)
-
Longitude (for Tableau map)
​
Overall, the presentation is composed of three main datasets mentioned above, assisted with some side research.
​
ANALYZE:
​
What patterns are present in the data? What is the data telling us?
​
The data presented information about the market situation, charging station distributions and the competitors’ insight.
​
I created many visualizations for presentation purposes, here are the main topics:
​
-
Introductions – Electrification Type
-
Market insight – top ranking of the brands for different vehicle types (regular, hybrid and EV)
-
Top 15 brands for all types of vehicles as of 2023
-
Top 15 brands for regular vehicles
-
Top 10 brands for EV
-
Top 10 brands for Hybrid
-
Top 10 brands for BEV and PPHEV
-
Yearly trends for BEV and PHEV
-
BEV Trends 2020-2023
-
PHEV Trends 2020-2023
-
Regular Trends 2020-2023
-
-
Charging Stations​
-
Charging stations and population distribution
-
Charger distributions
-
Charging stations/DC Fast charger/level 2 charger ratio
-
-
Logistic information – charging station by county, BEV and PHEV vehicles by county, BEV and PHEV distribution, total counts and map for charging station and vehicle distribution
-
Competitors
-
Trends of BEV and PHEV for new registration​
-
Trends of BEV for new registration
-
Trends of PHEV for new registration
-
Model year analysis – BEV
-
Model year analysis – PHEV
-
Top model analysis – no visualization, it is a table analysis
-
​
Results:
​
-
Vehicle Registration data: BMW is in a competitive position in the WA market and the top European luxury brand positioning. It has a better market occupancy rate in PHEV than any other type of vehicle. The EV market trends in WA are growing.
-
Electric Vehicle registration data: Model year analysis showed that Chevy, Nissan, Ford, BMW and Toyota have their own advantage in the EV market in WA.
-
Alternative Fuel data: The charging system distribution is sufficient for residents' usage, however, there is some uneven distributions that need improvements.
​
The ranking bar chart for Plug-in Hybrid:
The competitor insights for PHEV – Trends for new registrations:
The competitor insights for PHEV – Model years analysis:
INTERPRET:
​
What do the findings mean? What can we do with the information?
​
In this data analysis project within Tableau, I’ve uncovered some significant insights. BMW holds an impressive rank of #14 across all vehicle types. Interestingly, in the realm of Plug-in Hybrids (PHEVs), BMW stands out with a remarkable rank of #3. This underscores BMW's strength in the PHEV market, as further corroborated by the trend analysis in section 3, placing BMW at the forefront of the Plug-in market.
​
However, it's crucial for BMW to stay in step with American and Japanese competitors like Nissan, Ford, and Chevy as they are still the lead roles in the market (US is the American and Japanese vehicle brands driven country, Toyota is the rank #1 brand as of 2023). The data for Electric Vehicle Registrations reveals a dramatic surge in BMW's overall EV market share since 2014, emphasizing that BMW's entry into the EV market played a pivotal role in its recent success. A pivotal factor to consider is BMW's market positioning as a luxury brand. Despite higher price points, BMW maintains a strong foothold in the market, particularly among high-end and affluent customers.
​
Turning our attention to charging stations, my analysis highlights a correlation between station distribution, population density, and the number of BEV and PHEV vehicles. Notably, charging stations are predominantly concentrated in the Seattle metropolitan area within King County, Washington's largest county. However, it's important to discern that more stations don't always translate to more chargers.
​
I've identified a potential concern regarding charger distribution that may impact different vehicle owners. Level 2 chargers, while beneficial for plug-in hybrids as supplementary power sources, pose practical challenges for BEVs due to extended charging times (if a BEV owner wants to fully charge the BEV with level 2 charger, it takes over 5 hrs to do it). On the contrary, more DC fast chargers might impact the PHEV owner as DC chargers can only charge the BEVs. This underscores the importance of a well-balanced charging infrastructure.
​
In terms of competitors, my insights shed light on the strategies of various brands. Chevy appears to be gradually withdrawing from the EV market, while Ford has adopted a more cautious approach. Toyota, currently showing limited interest in the EV market, presents a potential opportunity for BMW to take a leading role.
​
Acknowledging the project's accomplishments, it's important to address certain limitations in the data. To mitigate these, I've implemented specific methods and assumptions below:
​
Project Limitations:
​
- Vehicle Type Definitions: the original data does not have a suitable classification method for vehicle types, the definitions are quite messy.
- Charging Station Locations: The location of the charging stations can only be presented on a zip code basis instead of the exact location.
- Vehicle Counts: The vehicle counts are estimated by the year 2023, given the dataset is the registration activity, which includes other types of registration such as registration renewal, etc.
To cover those limitations and make the analysis more efficient, I made the following methods and assumptions:
​
Project Assumptions:
​
-
Creation of the new vehicle types: Regular, Hybrid and EV based on the electrification_level
-
Filtering for non-motorcycle data: As I’m only focused on four-wheel vehicles
-
Inclusion of MPVs, passenger cars, and trucks: Only included certain types of vehicles
-
Focus on individual use, excluding commercial applications: Only included the individual use vehicles, filtered the commercial vehicles
-
Only included vehicle registration state is Washington: There are some registrations within the data that are not allocated as Washington state, the potential reason for this might due that the new residents haven’t change their registration locations yet
​
Finally, armed with this invaluable information, I am poised to formulate strategic plans and devise potential solutions for the company. This will enable me to craft a comprehensive blueprint for expanding the Electric Vehicle (EV) market presence in Washington state.
​
COMMUNICATE/PRESENT:
​
How do we convey these insights?
​
Insights were shared in a 10-minute presentation to peers and instructors in the Data Analytics Immersive course with General Assembly. I presented it in the Tableau storybook format with charts and imported slides inside. Below is a summary of what I accomplished throughout the project:
​
-
Define the scope of the “vehicle types” and frame the guiding question for analysis: “How’s the competitors' (in the BEV and PHEV perspectives) strategy and selling market look like? Should BMW adjust their market/research strategy for WA?” and “Charging Stations for BEV and PHEV are distributed based on the population and vehicles distribution?”
-
Extracted data from reliable government websites and cleaned the data using SQL and a little bit of Python (pandas for checking the null values).
-
Created visualizations within Tableau for market insight, charging stations and competitors’ insight.
-
Using SQL to create the filtered datasets for more efficient input and import the SQL query in Tableau directly instead of a CSV file. For charging stations and side analysis, use Excel to clean up the data.
-
Analyzed, interpreted, and visualized the data using Tableau.
-
Presented the process and findings in a Tableau Story.
​
Overall, the presentation gives the audience a detailed insight into the EV market in Washington state and can be used as a template for different types of business analysis.
​
Citations and resources:
​
Data sources:
Vehicle Registration Transactions by Department of Licensing
Electric Vehicle Registration Transactions data
Alternative Fuels Data Center: Electric Vehicle Charging Station Locations
​
Citations:
https://www.indexmundi.com/facts/united-states/quick-facts/washington/average-commute-time#map
https://www.bmwseattle.com/electric-vehicle-tips/washington-state-ev-tax-credits/
https://www.spokesman.com/stories/2023/aug/09/washingtons-high-gas-prices-make-it-the-best-state/
https://afdc.energy.gov/laws/all?state=WA
https://www.bmwgroup.com/en/report/2021/bmw-group-report/the-future-is-electric/index.html
https://media.ford.com/content/fordmedia/fna/us/en/media-kits/2021/electric-vehicles.html.html
Thanks for reading!
If you have further questions or want to connect with me, please check my Linkedin! Thank you again!