SQL-powered Cyclistic Insights: Pedaling Towards Marketing Success

Scenario As a member of Cyclistic’s marketing analyst team in Chicago, a bike-share company, the director of marketing believes the company’s future success depends on maximizing annual memberships. Therefore, my team aims to understand how casual riders and annual members use Cyclistic bikes differently. We will design a new marketing strategy based on these insights to convert casual riders into annual members. However, Cyclistic executives must first approve my recommendations, so they need to be supported by compelling data insights and professional data visualizations.

About The Company 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. 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.

The data has been made available by Motivate International Inc. under this license https://divvybikes.com/data-license-agreement. The dataset is reliable, original, comprehensive, current and cited, it ROCCCs (or more seriously: it's good). The data was successfully downloaded here: https://divvy-tripdata.s3.amazonaws.com/index.html*

Business Task: Analyze the Cyclistic historical bike trip data to understand the differences in how annual members and casual riders use Cyclistic bikes. Provide insights into their usage patterns, preferences, and behaviors to inform the development of a targeted marketing strategy aimed at converting casual riders into annual members.

1. Clear Statement of the Business Task:

• Investigate and compare the bike usage patterns of annual members and casual riders using Cyclistic historical trip data.

1. Description of Data Sources:

• Utilize Cyclistic’s historical bike trip data, which includes information on trip duration, start and end locations, bike type, and user type (annual member or casual rider).

1. Documentation of Data Cleaning or Manipulation:

• Address any missing or inconsistent data, ensuring accuracy and reliability.
• Categorize users into annual members and casual riders based on the provided user type information.
• Cleanse data of any anomalies or outliers that may skew the analysis.

1. Summary of Analysis:

• Compare and contrast the bike usage patterns between annual members and casual riders.
• Identify key trends, such as popular routes, peak usage times, and average trip durations for each user type.
• Extract meaningful insights that could contribute to a targeted marketing strategy.

1. Supporting Visualizations and Key Findings:

• Create visualizations (charts, graphs) to present usage patterns effectively.
• Highlight key findings, such as the percentage of annual members and casual riders, average trip duration, and most frequented stations.
• Use visualizations to illustrate any significant differences in usage behavior between the two groups.

Through this analysis, the goal is to provide actionable insights that will guide the marketing team in developing a strategy to convert casual riders into annual members, aligning with Cyclistic’s objective of maximizing annual memberships for future growth.

Project Analysis

1. Database Operations:

   • Created the "cyclistic_bike_trips " database.
   • Utilized and selected the ‘cyclistic_bike_trips’ database.

2. Table Operations:

   • Retrieved and analyzed data from the ‘jan_trips’ table.
   • Checked column information and renamed a column. ‘member_casual’ to ‘client_type’

3. Data Analysis Queries:

   • Explored ride data statistics, including total rides, ride types, and client types.
   • Analyzed ride durations and formatted results.

4. Table Alterations:

   • Implemented constraints and set a primary key for performance optimization.

5. Date Configuration:

   • Configured the first day of the week to Monday.

6. Week Number and Duration Calculation:

   • Added and updated columns for week numbers and ride durations.
   • Ensured data integrity by removing records with negative ride durations.

Further Data Analysis: Calculated total rides per week and identified the busiest start stations.

-- Special thanks to Nyameko Lolwana, Betsho Morale and Yolanda Maphosa for their exceptional work in the SQL-powered Cyclistic Insights: Pedaling Towards Marketing Success project. The success of this project truly reflects the synergy of our collaboration, and I'm grateful for the talent and commitment each of you brought to the table. Well done, team. -- Reach me at: rekaisigauke@outlook.com