Welcome to Marketing common data modelling challenge!
We use map data from various ad platforms into a single one. it can help marketers with questions like: "Where clicks better on facebook or tiktok?"
Imagine that 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.
please submit results via this form https://form.typeform.com/to/IP3EsX0N any question via: telegram:@kobzevvv
- 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.
- *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
To complete this task, you might need the following tools:
- dbt Cloud
- Google Big Query
- Google Looker Studio
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)
- dbt Fundamentals. Relevant chapters include:
- How to Use Google BigQuery for FREE
- How to create dashboard Google Looker Studio with Google Big Query
- Learn more about dbt in the docs
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: 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