About us and current stack:
We are a B2C web product. Currently, we have disparate data that needs to be consolidated into a single system for marketing decision-making.
Database: PostgreSQL (user data).
Payments: Stripe.
Web analytics: Google Analytics 4.
Traffic sources: Paid Social (Facebook/Meta, TikTok, etc.), Affiliate networks, CPC (Ad networks).
Business goal (What we are building):
Create a single source of truth for marketing. We need to see the exact return on ad spend for each traffic source (Day 0 ROAS, CPL, CPS) with details down to the specific creative.
Technical task: Iteration 1 (MVP)
Your task is to design and implement the data architecture for the first iteration (Day 0 analytics).
Scope of responsibility and work stages:
Architecture design (DWH & ELT)
Choose and justify the optimal cloud storage (for example, Google BigQuery).
Propose and set up a tool for automatic data collection (Airbyte, Fivetran, or alternatives). It is necessary to set up daily import of raw data from PostgreSQL, Stripe, GA4, and advertising accounts.
Set up cost data collection and their daily conversion into a single currency.
Data modeling (Data Modeling / SQL)
Write SQL queries (preferably using dbt) to transform raw data into clean data marts.
Consolidate cost data into a single hierarchy: Source -> Campaign -> Term (Adset) -> Creative.
Link users (user_id) with their transactions in Stripe and marketing tags (UTM/click_id) according to the "Last Non-Direct Click" attribution model (or another baseline model by agreement).
Collaboration with the Backend team
Prepare clear instructions for our backend developer: which parameters (click_id, UTM, client_id) and at what moment need to be recorded in PostgreSQL and passed in Stripe metadata to ensure tracking is not broken.
Building BI dashboards (Looker Studio / Metabase)
Create a "Day 0 Performance" report with drill-down capability from source to creative.
Key metrics to display: Spend, Clicks, Leads, CPL, Sales (Day 0), CPS, Revenue (Day 0), ROAS (Day 0).
Candidate requirements (Hard Skills)
Experience with DWH: Excellent SQL knowledge and practical experience with Google BigQuery.
Experience with ELT/ETL: Experience setting up data pipelines (Airbyte, Fivetran, Stitch, Funnel.io or writing custom connectors in Python).
Knowledge of dbt (Data Build Tool): A big plus for building reliable SQL models.
Understanding of Marketing/Web Analytics: Clear understanding of how UTM tags, S2S postbacks, GA4 attribution, and advertising accounts work.
Experience with Stripe API: Understanding of Stripe data structure (Customer, Charge, Invoice, Metadata).
BI Tools: Experience visualizing in Looker Studio, Metabase, Tableau, or PowerBI.
What we expect from you at the start (Acceptance criteria)
In your response to the vacancy, please briefly describe:
What stack of tools do you propose for our task (DWH + ELT + BI) and why?
Estimated cost of monthly infrastructure (services) according to your plan.
Estimated timelines for implementing Iteration 1 (MVP).
