One of Amtrak's short-term goals is to gain more young riders, and for a young population distressed by climate change, it happens to be a great low-carbon alternative to air and car travel. However, ridership and revenue remain historically low for the company, which has been severely harmed by COVID-19. Amtrak is largely dependent on government subsidies and needs a low-risk, low-cost way to appeal to new audience segments. My hypothesis is that environmentally focused ads targeted at young, climate-conscious air travelers will increase Amtrak's ridership and revenue.
You can view and explore the final Tableau visualizations for this project here. Google sheets work is in the excel file above, as well as at this link. Finally, here is a link to this project's blog post, which was featured on Towards Data Science.
Given a tight budget for advertising, I sought a small, super-targeted population with which to pilot Amtrak's climate-focused ad campaign. First, I found the most popular U.S. airline routes under 1000 miles which are also served by Amtrak within 24 hours or less. Then, using U.S. Climate Opinion data, I drilled down to routes where the populations of both destinations are likely to be receptive to climate messaging. Lastly, I found colleges along the routes where at least 5% of the freshman class would be travelling along that route to get to their home state. This led to about 40 schools with young, regular travelers who will need to go home for the holidays - hopefully using Amtrak.
Project Scoping can be read in the Project3_Scoping.md document within this repository. This process included the development of:
- A problem statement & impact hypothesis
- Nontechnical & technical scoping
- Current & Future state
- Trade offs & constraints
- Evaluation criteria
I used a number of datasets to find the target population. I used air travel data from the Bureau of Transportation Statistics from 2017-2019, which samples 10% of U.S. flights from those years. Amtrak routes from the Dept of Transportation and College/University geography data from NCES assisted with mapping. Climate change opinion data was collected from Yale's 2020 study, and college/university details from the Urban Institute.
To create my maps, I joined the data on metro area and visualized it using Tabelau dual-axis maps.
The primary Google Sheets functionalities used were pivot tables, charts, conditional formatting, and Vlookup. Once I moved the data into Tableau, I used dual-axis maps to show geographical relationships between datasets. For communication purposes, I used Tableau's worksheet, dashboard, and story views to clearly present the project trajectory.
I completed this project largely in Google Sheets and Tableau, with some preliminary data cleaning & aggregation completed in Python. I used Google Sheets to visualize route advantages and narrow down the routes, join datasets, and complete initial visualization. I then loaded many iterations of the Google Sheets data into Tableau to gain a better understanding of the geographical relationships between datasets. I executed the final mapping and visualization, as well as the communication of the project, in Tableau.
I completed a 5-minute presentation of our findings, including initial recommendations for a pilot campaign as well as future modeling opportunities. Slides and visuals for this project are included in this repository. Future work may include building a predictive model to better inform the selection of target audiences for climate-focused advertising.