Self-service End-to-end Marketing Analytics for YouTravel

YouTravel is an online marketplace that connects travelers with curated small group tours led by travel experts and independent guides. These tours offer personalized experiences, allowing participants to fully immerse themselves in each destination.

Our task is to build an end-to-end report from a user's click on an advert to a purchase, in order to evaluate the effectiveness of marketing campaigns.

• There are several advertising offices from which we need to collect the costs of advertising campaigns: Facebook, Yandex.Direct, Google Adwords, Vkontakte. There are also marketing costs that do not go through advertising cabinets, but are maintained in a manual directory.
• Users who were attracted through advertising campaigns make a purchase on the site.
• Google Analytics is installed on the site to capture user id, UTM tags and purchases.

The task of end-to-end marketing analytics may seem standard, but the client project has several nuances in the business logic that makes standard solutions unsuitable:

• Due to the specifics of the marketing strategy, custom attribution logic needs to be implemented.
• There are a number of business logic features that need to be considered when building the link between marketing channels and purchases: user languages, for example.

We use BigQuery as a data warehouse. We have nine data sources.

We use open-source Singer and Meltano stack for data loading and DBT for data transformation.

In the process of analysis, we highlight:

• anchors - key entities of the subject area (e.g. User, Visit, Advertising campaign);
• attributes - characteristics of anchors (e.g. User Name, Visit Date, Advertising Campaign Name);
• links - links between anchors (e.g., ‘User made an Order’).

We immediately document the found anchors, attributes and links in an Excel file, which means that the description of the final data appears before the implementation.

We applied minimal modelling to design the subject matter model. This is an approach that helps to explore the data structure and document it at the same time.

We use Metabase, a free and open source tool, for reporting.

In the Metabase data model, you can create a dictionary of metrics that will be available to business users when building reports.

Adding new metrics requires no programming and is available to all Metabase users.

Now that all the data is collected and the metrics are defined, we start to build the final report.

We do it without writing SQL, just by selecting data in Metabase.

Towards the end of the project, we tidy up the documentation. This is easy because we described all the data at the beginning of the project - at the data modelling stage. We check for any typos and eliminate discrepancies that arose during the implementation.

Additionally, we moved all documentation to Notion for convenience and aesthetic reasons.

During the project, we implemented end-to-end marketing analytics reporting for the customer. The data became available to business users in self-service analytics mode. And thanks to the application of minimal modelling concepts, we obtained a number of important implications.

• The data is fully documented, and the documentation is kept up-to-date 'as it is built.' For business users, the data is available in a self-service analytics mode.
• Due to the independent implementation of each attribute, it is easy to add additional data and sections to the reports (for example, to implement an alternative attribution model it is enough to add one attribute).
• Analysts who will join the project in the future will be able to easily track the logic of data transformation and make changes to the project.

Read more about the implementation of this project in our article.