Marketing common data modelling challenge

Welcome to Marketing common data modelling challenge!

Task

At Improvado, we use marketing common data models (MCDM) to map data from various ad platforms into a single one. MCDM can help marketers with questions like: "Where clicks better on facebook or tiktok?"

Imagine that MCDM-model behind dashboard, is lost somehow. You need to rebuilt it. You have: — raw data from the ad systems (seeds folder), - the MCDM table structure for this report, - and dashboard

In this situation, we've got checklist that you can follow (or not): - Begin a new project in dbt Cloud, utilizing Google Big Query as the DWH. - Use the raw data from the ad platforms and the MCDM table structure for the ads_basic_performance report.

How to Submit

please provide answer in the typeform

• A link to your dbt Cloud repository that contains the completed MCDM for the ads_basic_performance report.
• A link to the recreated dashboard.
• A brief set of instructions (in md file in your repo) for adding data from new ad platforms into your MCDM.

Hints:

- *Cost per engage* is just a spended sum divided by sum of engagements
- *Conversion cost* is calculated by dividing sum of spended by total conversions count
- *Impressions by channel* is a sum of impressions for each channel
- *CPC* gets like sum of spended divided by clicks count

Tools

To complete this task, you might need the following tools:

• dbt Cloud
• Google Big Query
• Google Looker Studio

Tool Instructions

To help you get started, here are some resources on how to use the necessary tools:

• dbt Courses:
  • dbt Fundamentals. Relevant chapters include:
    • Setting up dbt Cloud (17 minutes)
    • Models and Sources (40 minutes)
    • dbt Cloud and BigQuery for Admins (35 minutes)
• How to Use Google BigQuery for FREE
• How to create dashboard Google Looker Studio with Google Big Query

Additional Resources:

• Learn more about dbt in the docs
• Short overview for Improvado MCDM

How to Use the Repository

This is the foundational repository for your project. Clone it and start your dbt Cloud from it.

The repository includes raw data from various ad platforms, as well as the MCDM structure for the ads_basic_performance report, which are provided as seeds:

• src_ads_bing_all_data
• src_ads_creative_facebook_all_data
• src_ads_tiktok_ads_all_data
• src_promoted_tweets_twitter_all_data
• mcdm_paid_ads_basic_performance_structure

To build the seeds, run dbt seed in the dbt Cloud console. Once the seeds have been built, you can access the data using ref(). For example, you can use select * from {{ ref('src_ads_bing_all_data')}} to access data from the src_ads_bing_all_data seed.

Q&A

Q: How to validate results for my model? 
A: Compare your dashboard with the dashboard from task. If some numbers doesn't match, then some fiels in your model got incorrect mapped  

Q: What if there're no MCDM sctructure field in raw datasource data?
A: So you started understending the main goal of this task :-)	Suggest wich field or fields corresponds to MCDM ones by their meaning. If there're no such fields, then probably datasource just doesnt got them