Divvy Case Study

Meet Divvy, a ride-share company owned by the Chicago Department of Transit (CDOT) and operated in partnership with Lyft. Divvy serves the cities of Chicago and Evanstan with recent expansions in the Far South Side of Chicago in 2020 as well as both Northwest and Southwest Chicago in 2021. More recently, Divvy redesigned their ebike and through partnership with Skyhook Solar, in 2022, they launched solar powered ebike canopy docking stations. Their mission is to provide a convenient alternative for consumers to travel Chicago. To do this, they have both pedal (classical) and electrical bikes (ebikes) ready on demand for rent to consumers. Divvy’s subscription model is in the form of guest passes (single-use or per day) or through an annual membership.

data, methodology, and tools

This case study uses 12-months internal, first-party collected data between February 2022 - January 2023 provided by Divvy under this license. Each row of data is comprised of anonymized metadata under the domains of: time, location, and type. The data given is presented in the form of .csv spreadsheets broken up by month of occurance. The methodology guiding this case study follows the teachings of the Google Data Analytics Professional Certification, which is: Ask, Prepare, Process, Analyze, Share, and Act. For this study, I utilized the programming languages: Python, SQL, and VBA.



After collecting the twelve internal data spreadsheets from Divvy, I aggregated them into a SQL Server table using Python (Pyodbc and Pandas). The move from spreadsheets to SQL was chosen due to the size limitations of Microsoft Excel being 1,048,576 rows, whereas the aggregated dataset contained 5,754,248 rows. code available on GitHub here.


Through inspecting the dataset more closely, I noticed that the location domain of the data had not been recorded on some records (reported as null). Also, the location coordinates on some instances were not as accurate as others. Thus to normalize and reinforce data integrity I limited the scope of this project to focus only on the domains of time and type rather than removing the observations all together. To accomplish this, I wrote a VBA macro that iterates through a folder, opens the .CSV files, removes the location data, and parses and typecasts the custom type variable to a more SQL-friendly integer and datetime data types. code available on GitHub here.