SQL Project
Cyclistic User Behaviour Analysis
With Annual Members being more profitable than Casual Riders for a Chicago based bike share company, the Director of Marketing believes it is a great opportunity to convert casual riders into annual members.
To design a marketing campaign for the same, the Director of Marketing needs to know, "How do Annual Members and Casual Riders use Cyclistic bikes differently?"
Business Function : Marketing
Video Presentation
Project Details
With the data available to us, the following measures become extremely important to analyse user behaviour
The timing of the ride. When was it started?
-
Which hour of the day?
-
Which day of the week?
-
Which month of the year?
-
Which type of bike did they use?
-
Where did they start and where did they end?
The Duration of the ride. How long was it?
Understanding the 5.7 Million records of data
Column Name | # | Data Type |
|---|---|---|
ride_id | 1 | varchar(16) |
rideable_type | 2 | varchar(16) |
started_at | 3 | datetime |
ended_at | 4 | datetime |
start_station_name | 5 | text |
start_station_id | 6 | text |
end_station_name | 7 | text |
end_station_id | 8 | text |
start_lat | 9 | double |
start_lng | 10 | double |
end_lat | 11 | double |
end_lng | 12 | double |
member_casual | 13 | varchar(8) |
When the question is why does a user buy a certain service and not the other, the first question that comes to mind is,
"Who is the user?"
Well, in such a case, how do you answer who is the user and why do they make the choices they do?
To protect the privacy of the user, no personal information is shared, no demographic data is available. The only data point associated with the user, is whether they are
"Annual Member" or "Casual Rider"
In our case, we analyse their usage behaviour to profile the patterns of
"Annual Members" and "Casual Riders"