"Travel is complicated to book", Expedia(2015). Booking a trip is a hassle for many travellers as the booking process involves various products, making the customer journey even more complicated than in other industries. To capture traveller preferences through digital touch-points and help online travel agencies to improve conversion across travellers’ digital journeys, a data lake and a data-driven traveller insight dashboard are created aims to help OTAs optimize digital experience and marketing strategy. In this project, the Design Thinking methodology is adopted to define the use case, persona and critical touch-points to establish a data collection strategy. A series of Data engineering includes planning data architecture, constructing data pipelines and fetching data using open APIs deployed for building a traveller database. Following that, data manipulation and data analysis were conducted to generate traveller insights, and finally, a dashboard is to address decreasing online conversion rates for Swiss online travel agencies.

• To build an end-to-end data pipeline and make it available for business analysis in a data warehouse environment
• Use at least 2 APIs to obatian static and non static data
• Present a prototype use data visualiation tools

According to Expedia(2015), travellers visit an average of 38 websites before booking a trip. The entire trip booking process involves several steps and can be stressful for travellers, this is because there are too many products and options that occur during the search phase, and that puts travellers in the paradox of choice and make the customer journey even more complicated than in other industries.

Websites that provide travel-related products have a 98% abandonment rate among all businesses. Statista (2022)

Despite online travel agencies still playing an important role in the customer journey, OTAs are suffering from a low conversion rate as Statista reveals that travel-related websites received the highest shopping cart abandonment rate among other consumer goods sectors. This is because are too many things involved in the booking process, and this will affect the traveller's decision. As consumers spend more time searching for trips, they will take longer to decide whether to book. This means that the OTAs will have less impact on consumers' buying decisions.

When mapping out a traveller's digital journey, we found social media has gained importance in the traveller's decision-making process (Smith & Anderson,2018). Travel feeds on Instagram have become one of the powerful indicators that trigger travellers’ decisions to plan where they should visit on upcoming trips (Han & Chen, 2021). To understand how social media and online search impact travellers' buying decisions and to help Swiss OTAs improve online booking experience and effective marketing, we designed a traveller insights dashboard that presents crucial metrics and trends by analyzing social media and search engine data.

A travel database is built with the following data engeneer techniques:

1. Define Data Collection strategies: Define the scope of the project and collect data from across traveller digital touchpoints
2. Design Database and Construct Data Piplines: Present a business data schema Construct ETL and ELT workflow use Apache Airflow
3. Built Data Architecture: Demonstrates data system with AWS RDS according to business needs
4. Prototyping: Based on predefine business questions to design visualiation datashboard layout

Data Architecture

I constructed data pipelines to address the business problem that presents critical metrics with our proposed traveller insight dashboard. The IG Hashtag Statistic graph gives information about hashtag usage in each Instagram post. We can answer the following questions:

Business Questions 1: How can we improve the tourist travel experience in Switzerland by utilizing social media and search engine data?

1. What are the popular destinations in Switzerland that people search for travelling?
2. Which city do people are interested in the most in Switzerland?

Business Questions 2: “How do individuals and businesses utilize social media hashtags to boost their online presence and performance?”

1. What are the popular hashtags (top 5) used in Switzerland?
2. What are the popular hashtags (top 5) based on Swiss canton, city, or destination names?

IG Hashtag Statistic

A full dashboard can be seen in Tableau Public

Initially, we wanted to get location tags to create a location-hunting app. However, due to privacy issues, we couldn't access location tags because Instagram deprecated the local tag endpoint from its API. Therefore, we decided to use #inLoveWithSwitzerland, a hashtag used by MySwitzerland, and 26 Kantons' names in English as the basis to fetch relevant hashtags in each Instagram post. Although we couldn't capture all the locations in Switzerland, the collected information was sufficient to create a minimal viable prototype for this project.

The entire process of constructing a database, from data collection to visualizing data, helps us to generate traveller insights and to answer our business questions. The proposed dashboard focuses on value creation for business providers such as online travel agents and marketing firms, so they can utilize matrics from this dashboard to provide travellers with more personalized services and products.

This project taught me many hard skills in data engineering, which equipped me with fundamental skills and knowledge to interact with database tools and allowed me to combine design thinking with product development. Besides, having the opportunity to use popular software such as AWS and Airflow to realize our business idea into a monetizable product was an invaluable experience I had from this group work.

• Airflow Dag:

  • callapi.py: DAG file fetches media information from Posts on Instagram based on 30 unique hashtags

• Jupyter Notebook:

  • List_of_Zip_code_Switzerland.ipynb: zip_code.csv data cleaning and loading to RDS_PostgreSQL
  • my_switzerland_API.ipynb: Fetching destination information with the use of MySwitzerland API
  • Insta_create_hashtag.ipynb: Fetching Instagram hashtag media information with the use of Instagram Graph API

• Expedia Group Media Solution (2015) How Marketers Can Reach Travel Bookers Across the Consumer Funnel Retrieved on November 01, 2021
• Han, J & Chen, H. (2021) Millennial Social media User’s Intention to Travel: The Moderating Role of Social Media Influencer Following Behavior Retrieved on November 01, 2021
• Statista (2022) Online Cart Abandonment Rate Worldwide
• Smith A & Anderson M. (2018) Social Media Use in 2018 Retrieved on October 11, 2021