Contract Data Engineer — Marketing Attribution Pipeline (S3 → BigQuery → Looker Studio)

We have an existing pipeline that moves lead and case-intake data from our intake platform (Lead Docket) through AWS S3 into BigQuery, surfaced in Looker Studio via a Cloud Function on a scheduled refresh. We want to extend it to ingest Google Ads and Meta campaign data and join it to the intake data, so that for one law firm client we can see which campaigns drive leads, what those leads cost, and how lead quality and signed/referred cases break down by campaign. Strategy, KPI definitions, and attribution logic are already scoped on our side. We need someone to own the engineering build against that spec. What you'll build: - Audit and document the existing S3 → BigQuery → Looker Studio pipeline. - Set up daily ingestion of Google Ads and Meta data (spend, clicks, impressions, click IDs where available) into BigQuery, consistent with how intake data already lands. - Build the matching layer tying each lead back to the campaign that drove it — gclid for Google, UTMs and a source-to-campaign mapping we provide for both platforms. Handle unmatched leads gracefully. - Model the blended data into clean BigQuery views at the campaign and ad-group grain, pre-aggregated for speed. - Build the reporting views in Looker Studio on top of our existing dashboard, matching our styling and naming. - Document the build and hand it off. Required skills: - Strong SQL and hands-on BigQuery (modeling, views, optimization). - ETL/ELT pipeline experience, ideally across AWS S3 and Google Cloud (Cloud Functions, Cloud Scheduler). - Working knowledge of the Google Ads API or Google Ads → BigQuery transfer, and Meta ingestion (Marketing API or a comparable connector). - Looker Studio (or comparable) experience, including blended sources. - Comfort with attribution and record-matching logic (gclid, UTM, source mapping) and edge cases like unmatched or duplicate records. Nice to have: - Marketing/advertising data experience, especially lead-gen or attribution. - Familiarity with CRM or intake platforms (Lead Docket or similar). - Cohort-based reporting experience.