The Data Analysis Pipeline: Ask – Prepare – Process – Analyze – Share – Act
Business Scenario
You are a junior data analyst working on the marketing analyst team at Cyclistic, a bike-share company in Chicago. The director of marketing believes the company’s future success depends on maximizing the number of annual memberships.
Therefore, your team wants to understand how casual riders and annual members use Cyclistic bikes differently. (Probably identify distinguishing behaviors)
From these insights, your team will design a new marketing strategy to convert casual riders into annual members. But first, Cyclistic executives must approve your recommendations, so they must be backed up with compelling data insights and professional data visualizations.
Cyclistic Background
In 2016, Cyclistic launched a successful bike-share offering. Since then, the program has grown to a fleet of 5,824 bicycles that are geo-tracked 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 at any time.
Until now, Cyclistic’s marketing strategy relied on building general awareness and appealing to broad consumer segments. One approach that helped make these things possible was the flexibility of its pricing plans: single-ride passes, full-day passes, and annual memberships. Customers who purchase single-ride or full-day passes are referred to as casual riders. Customers who purchase annual memberships are Cyclistic members.
Cyclistic’s finance analysts have concluded that annual members are much more profitable than casual riders. Although the pricing flexibility helps Cyclistic attract more customers, Moreno believes that maximizing the number of annual members will be key to future growth.
Rather than creating a marketing campaign that targets all new customers, Moreno believes there is a very good chance to convert casual riders into members. She notes that casual riders are already aware of the Cyclistic program and have chosen Cyclistic for their mobility needs.
Stakeholders For The Project
Cyclistic
A bike-share program that features more than 5,800 bicycles and 600
docking stations. Cyclistic sets itself apart by also offering reclining bikes, hand
tricycles, and cargo bikes, making bike-share more inclusive to people with disabilities
and riders who can’t use a standard two-wheeled bike.
The majority of riders opt for traditional bikes; about 8% of riders use the assistive options. Cyclistic users are more likely to ride for leisure, but about 30% use bikes to commute to work each day.
Lily Moreno
The director of marketing and your manager. Moreno is responsible for the development of campaigns and initiatives to promote the bike-share program. These may include email, social media, and other channels.
Cyclistic Marketing and Analytics Team
A team of data analysts who are responsible for collecting, analyzing, and reporting data that helps guide Cyclistic marketing strategy. You joined this team six months ago and have been busy learning about Cyclistic’s mission and business goals—as well as how you, as a junior data analyst, can help Cyclistic achieve them.
Cyclistic Executive Team
The notoriously detail-oriented executive team will decide whether to approve the recommended marketing program.
THE CLEAR GOAL
Moreno wants the marketing team (Me – an analyst included) to design marketing strategies aimed at converting casual riders into annual members. To do that, however, the team needs to better understand how annual members and casual riders differ, why casual riders would buy a membership, and how digital media could affect their marketing tactics. Moreno and her team are interested in analyzing the Cyclistic historical bike trip data to identify trends.
The overarching questions that my report must answer are:
- How do annual members and casual riders use Cyclistic bikes differently?
- Why would casual riders buy Cyclistic annual memberships?
- How can Cyclistic use digital media to influence casual riders to become members?
As an analyst, Moreno has tasked me with answering the first question – How do annual members and casual riders use Cyclistic bikes differently?
Ask
Guiding questions
- What Is The Problem I Am Trying To Solve?
I need to discover vacuums that let me convert causal riders to members (who subscribe to Cyclistic’s services. To contribute to solving this business problem, I have been tasked with analyzing the customer behaviors of casual and annual Cyclistic customers.
- How Can Your Insights Drive Business Decisions?
By providing the marketing teams with detailed, well-analyzed, and understandable information about how casual and annual members differ, I can offer them a detailed overview of:
- The buying behaviours of both customer classes and why either of them opted for their chosen patronizing option
- Reasons why casual buyers have chosen to remain casual for that long
- Uncover hidden benefits members may enjoy over casual riders
- The insights I provide can also offer the marketing teams insights into communication channels and how they influence buying decisions
Key Tasks
- IdentifyThe Business Task
The business task here is to convert casual riders to members. To do this, the marketing team understands how annual members and casual riders differ, why casual riders would buy a membership, and how digital media could affect their marketing tactics.
- Consider Key Stakeholders
The key stakeholders for this project are the cyclistic company, lily Moreno (marketing manager), the marketing and analytics team (will do the bulk of the analysis), and the executive team.
The Deliverable
- The deliverable here is to prepare a properly cleaned bill of information showcasing the differences between annual and casual members.
Prepare
Guiding questions
● Where is your data located?
The data for the analysis was stored in a secured site that was regularly updated with each coming year.
● How is the data organized?
The data was organized in a Microsoft excel file and I have formatted the document, placing them in a table.
● Are there issues with bias or credibility in this data? Does your data ROCCC?
I do not find any issues with the data as I found the data relevant as it was provided in Feb. 2024, the data is original as it was specifically collected for this purpose, the data is comprehensive as it contains all the required parameters, the data is citable as I can directly reference its source, and the data is current as it is properly updated regularly.
● How are you addressing licensing, privacy, security, and accessibility?
The data source provides accurate information to verify the licensing, privacy, security, and accessibility.
● How did you verify the data’s integrity?
I verified the data integrity by understanding its source, removing errors, and removing duplicate values.
● How does it help you answer your question?
The data provided offers me the information I need to analyze customer relationships.
● Are there any problems with the data?
The main problems with the data included duplicates, errors, and mixed values contributing to general inconsistencies.
Key tasks
● Check the data for errors.
● Choose your tools.
● Transform the data so you can work with it effectively.
● Document the cleaning process.
Deliverable
- Documentation of any cleaning or manipulation of data
- Analyze casual and bikeshare member behaviours to understand their approach to using the service
- Provide insights on how cyclistic can convert casual riders to regular members
Process
Guiding questions
● What tools are you choosing and why?
I am choosing to work with Microsoft Excel, SQL, and R Programming.
● Have you ensured your data’s integrity?
To ensure data integrity in this project, I will examine the data to filter out duplicates, I formatted the data as a table and used the filter for each column to ascertain data consistency, I also ensured that each column was represented with the right data type, and I inspected each column, outlining how they play into the grand scheme of the analytics process.
● What steps have you taken to ensure that your data is clean?
While cleaning this data, I have sought to filter out duplicates, format each column with the right data property, and remove empty rows.
● How can you verify that your data is clean and ready for analysis?
Verifying my data and making it ready for analysis includes: checking for missing values, examining data consistency, exploring outliers, validating the data types for each column, checking for duplicates, checking summary statistics, and performing data quality tests (With Power Query).
● Have you documented your cleaning process so you can review and share those
Results?
I will create a note of the multiple processes involved in my cleaning the data so that others can enjoy oversight of the work that’s been done if I do perform some cleaning on Power Query “Applied Steps” so that viewers can have a look at the cleaning process.
The Excel Work Process.
- After downloading the trip data from May 2020 – April 2021 in CSV format, I imported and cleaned them individually in Microsoft Excel. First by filtering and deleting duplicate values in ride_id.
- Removing duplicates from the 12-month dataset
- Cleared out blank rows that may interfere with data integrity – start_station_name, end_station_name, start_station_id, and end_station_id were also deleted.
- Changed data types into sql-compliant foormats.
Analyze (SQL Exploration)
Guiding Questions
- How should you organize your data to perform analysis on it?
It is best to adjust the data type for each column and check for outliers in the data. To do this, I’ll import the data into Power Query to have a feel of the data.
- Has your data been properly formatted?
Yes, I have properly formatted the data by ensuring all of the data conforms to the required parameters for accurate analysis,
- What surprises did you discover in the data?
- What trends or relationships did you find in the data?
- How will these insights help answer your business questions?
Key tasks
- Aggregate your data so it’s useful and accessible.
- Organize and format your data.
- Perform calculations.
- Identify trends and relationships
Deliverable
- A summary of your analysis
FOR A BREAKDOWN OF THE ENTIRE SQL EXPLORATION PROCESS, PLEASE CLICK HERE – https://github.com/AyodejiKevin/Cyclistic-Bikeshare-Queries-/tree/main
The Analyze Phase – Part 2 (Power BI)
With MySQL, we’ve been able to adequately filter out irrelevant data and we have also combined all 12 months of data into one.
We have been able to: find the average ride length, compare total rides in each month for members and casuals, aggregate the total number of bikes used by each customer type, Figure out how each bike use varies over the season, the number of rides during every weekday for members and casuals, number of member/casual rides taken during each hour of a 24-hour day, and popular stations for members and casuals.
To take our analysis a step further, we need to visualize how different data points intersect.
To analyze Cyclistic’s data in Power BI, here are a few things I have done.
- Connected the MySQL server to Power BI.
- Selected the right data types for each column.
- Broke down a large facts table into smaller dimension tables.
- Built relationships between the facts table and dim tables with cardinality types to ensure proper communication between data points.
- Generated visualizations with intersecting data points to gather insights into customer behaviors.
- Built tooltips to help viewers interpret the visualizations.
The findings in my analysis are shown below:
July, August, and September have the most rides for most casual and members. The rise in casual and member rides leads me to want to probe further…
Next, I investigate the seasonal response by members and casuals to Cyclistic’s services.
This visualization shows that the summer months experience much higher biking traffic compared to other months. The summer months rise in traffic coincudes with the rising rider counts for July, August, and September in the prior visual.
The summer months in Chicago (where bikeshare primarily operates) may witness these high biking traffic because:
- It is generally warm and the residents are much more active
- It is a great month for tourists looking to explore the city a lot more
- Since residents do not have to worry about adverse weather, they may find it easier to ride in these months
Conversely, the winter months (from November to February) experience much lower bike use generally.
Next, let’s put some figures to the number of riders in each month.
Now, let us begin to draw distinctive lines between Casual and Members attitude towards bike use.
First, let’s see how long they comparatively use these bikes for:
From this chart, we can see that Casuals use the bikes for much longer than Members. This could mean that members have a set routine which they work with and they have bikeshare as a part of that routine. Casuals on the other hand may seem much more spontaneous.
Despite riding for much longer, more members use Cyclistic during the week compared to casuals. Members may use Cyclistic’s services during the day because it coincides with their work or school.
But on weekends, the inverse occurs. Casuals use Cyclistic’s services much more than members do.
The following graphs confirm it:
Casual using Cyclistic’s bike-sharing program more than members on weekends further reinforces the idea that members mainly use Cyclistic’s services around routine weekday activities while casuals use bike-sharing much more spontaneously.
Let’s further probe Cyclistic’s Casual and Member customer behavior through their hourly usage patterns
- Around 7am, the traffic with members really started to peak – this means people are using the bikes for work, school, and daily commute.
- The rides for members also started to drop by 5 pm in keeping with general work and school schedules.
- The peak for casuals start a bit later at 9 pm. It drops at around 5 pm as well (similar) to members.
- Members and casuals have maximum bike use at 17:00 (5 pm).
To Summarize All I Have Found:
The Share Phase¶
Now, I have performed some analysis and found interesting trends. It’s time to share everything I found so far and importantly, to provide actionable insights for the executive team at Cyclistic Bikeshare.
To share my findings with the Cyclistic executive team, here are a few questions that have led to my compilation:
- Was I able to answer the question of how annual members and casual riders use Cyclistic bikes differently? I was able to identify certain differences in how Cyclistic members and casuals use their bikes differently. They both seem to use the bikes in different aspects and for different durations.
- What story does my data tell? The data tells me a story of how customers see and use bikes.
- How do my findings relate to your original question? My findings correlate with Cyclistic’s marketing team’s mission to find marketing loopholes that they can take advantage of.
- Who is your audience? What is the best way to communicate with them? While the data has not essentially provided me with enough information regarding communication channels, the riding patterns tell me the majority of the casuals may be tourists and summer outgoers.
- Can data visualization help me share my findings? Definitely! The visualizations can help me share my findings as I can convey my findings in simple, easily understandable visuals.
- Is your presentation accessible to my audience? My presentation has been placed in a presentation software with markers and tips available to help viewers navigate different aspects of the presentation. I will also provide a much detailed report through Power BI for viewers looking to dig deeper into my visuals and draw connecting lines between those visuals.
The presentation can be accessed via this link:
The Act Phase
True Results Come From Taking The Right Steps With The Right Insights.
What is my final conclusion based on your analysis? Converting casual riders to annual riders is possible as long as we address the main issue of our riders which is lack of flexibility in offerings for.
How could your team and business apply your insights? We can apply these insights based on the recommendations listed.
What next steps would you or your stakeholders take based on your findings? The marketing team can move forward in their decision to convert casual riders to be an annual members.
Is there additional data you could use to expand on your findings? Additional data could certainly help expand my findings. More data on the age group of the customers, specific information on what a complete trip means, and more purchase information. While I understand the importance of data anonymization, providing these set of data can certainly help me provide much detailed insights.
Can data visualization help you share your findings? Data visualization is easily to understand and it is very helpful to show findings from the analysis conducted from data.
Here are some of my actionable recommendations:
Power BI App – https://app.powerbi.com/Redirect?action=OpenApp&appId=5ef134e7-859b-4593-a500-5a9b9f153fe3&ctid=3a3dea7e-d44c-4d13-919a-4b56508b1785