The project is divided into two parts. . In the first part I will use SQL to dig into the Bixi dataset. While in the second part I will further explore the data using Tableau and consistent with the visualization. My goal is to gain a high level understanding of how people use Bixi bikes, what factors influence the volume of usage, popular stations and overall business growth.
The data is a cleaned up version of data downloaded from the open data portal at Bixi Montreal:
https://www.bixi.com/en/open-data
Run the SQL file located here.
The goal of this project is to gain insights into the usage patterns of Bixi bikes, a bike-sharing service in Montreal, using SQL and Tableau.
In the first part, SQL is used to analyze the trips and stations tables to gain an overall view of the volume of usage of Bixi Bikes, factors influencing it, and popular stations. It includes SQL code used to perform the required analysis and a written business report that describes and interprets the insights gained from the analysis.
The first part includes the following sections:
Section 1: Attempt to gain an overall view of the volume of usage of Bixi Bikes and what factors influence it.
Section 2: Investigate whether membership status influences Bixi bike usage.
Section 3: Use the above queries to answer questions related to peak demand and converting non-members to members.
Section 4: Analyze station popularity.
Section 5: Analyze Bixi usage patterns throughout the day for a specific station.
Section 6: List all stations for which at least 10% of trips are round trips.
This part aim to provide insights into Bixi bike usage patterns, popular stations, and overall business growth. The project also encourages the use of data visualization and external research to supplement the findings and better communicate the results.
The second part aims to analyze the usage and behavior of the Bixi service users and explore the revenue generated by the single trips. It consists of four main sections that will be answered through different visualizations.
In section 1, we will compare the total number of monthly trips between the calendar years of 2016 and 2017 and analyze the differences in the usage of the Bixi service between the two years. We will also use a quick calculation to contrast the percentage of trips that occurred in each month per year and calculate the percentage of trips that were done by members for the year 2017. Additionally, we will identify round trips and create a visualization showing the top 10 stations by percentage of round trips.
In section 2, we will build a visualization to show the relationship between the percentage of round trips and percentage of member trips by station. We will also create histograms to visualize the distribution of all trips by duration in minutes and compare the behavior of members vs. non-members in terms of trip length. Furthermore, we will create a map to visualize the average trip duration per station across the city and explore any interesting geographic patterns.
In section 3, we will calculate the revenue generated by the single trips pricing model and explore the total dollar amounts and relative percentage of revenue from single trips up to an hour in length for each of the three different pricing buckets. Moreover, we will create a visualization to show the total amount of flat rate revenue in the data for each hour and each day of the week and identify the days/times when Bixi is generating the most revenue from their flat rate charge.
Finally, in section 4, we will create an interactive reporting dashboard containing at least three visualizations, including two previously created and one new one of our choosing. This dashboard will provide the operations team with a tool to drill into the data and obtain insights as required and fulfill ad hoc data requests.
Overall, this project aims to provide Bixi with valuable insights into their service usage, member behavior, revenue, and other key performance indicators that can be used to inform decision-making and optimize their operations.