Project Overview

This project aims to centralize scattered data points from various cloud-based platforms and tools into one consolidated location, utilizing BigQuery as the central repository and utilizing Looker Studio to build KPI Dashboards and creating means for data exploration to drive business intelligence. The goal is to capture a comprehensive view of the entire customer journey, encompassing datapoints from initial advertising through to marketing engagement, sales process, fulfillment, support, and ongoing customer services.

Objectives

1. Data Centralization: Aggregate data from diverse sources including Meta Business Suite (Ads running in Meta/FB Platform), Google Ads, Google Analytics 4, Hyros, OnceHub, Hubspot, Close (Sales CRM), Google Sheets, and others into a single repository (BigQuery).
2. Customer Journey Tracking: Correlate data across the entire customer journey from the abovementioned data centralization task, from top-of-funnel activities such as ad views to post-purchase support and coaching program and retention metrics in order to derive actionable business insights.
3. Technical Foundation: Leverage BigQuery and Looker Studio to build a robust, scalable data architecture with KPIs for each departments (Marketing, Sales, Customer Success, etc.).
4. Project Timeline and Milestones: The expected project work duration is 5-10 business days, with following milestones as checkpoints.
5. Milestone #1: Scoping out the available data sources and types. Designing the database structure and model.
6. Milestone #2: Automating data collection from multiple platforms into BigQuery.
7. Milestone #3: Developing Looker Studio dashboards for marketing, sales, support, and customer success teams.
8. Milestone #4: Writing documentation and creating instructional videos (loom) for internal tech teams who will maintain the system after project completion.

Scope of Work

1. Design and implement a database structure suitable for complex data aggregation and analysis. The sources of data includes and is not limited to:
2. Ads Platforms (Google Ads, Meta/Facebook Ads, TikTok Ads, etc.)
3. Analytics Platforms (GA4, GTM, Hyros, etc.)
4. Marketing Platforms (ActiveCampaign, Hubspot, Wistia, etc.)
5. Sales, Support, and Engagement, Fulfillment Platforms (Jotform, OnceHub, Close, Hubspot, Thinkific, Telegram, etc.)
6. Payment & Sales Records (SensePass, Elective, Ontraport, PayPal, and other merchant accounts)
7. Others (Google Sheets, Slack, etc.)
8. Develop automated data pipelines to collect and store data efficiently. Available implementations may include (in order of preference):
9. Direct platform integration (i.e. Google Ads <> BigQuery)
10. API/webhook integration
11. Zapier integration
12. 3rd party integration plugins
13. Create intuitive and insightful visualizations and dashboards in Looker Studio. Some of the necessary KPI dashboards includes and is not limited to:
14. Marketing Dashboard (Displaying KPIs for media buying, with ability to look up per-source performance)
15. Sales Dashboard (Displaying KPIs for sales performance, with ability to dig deeper into the key metrics per sales rep, and filterable by lead-source)
16. Companywide KPIs (Displaying KPIs for the overall company where revenue, expense, and ROAS can be displayed per source)
17. Produce comprehensive documentation and training materials for ongoing maintenance and troubleshooting. Some of the esential documentations would include:
18. How to add a new data source and bring it into BigQuery.
19. Documentation of the database schema that was setup for BigQuery.
20. Best practices of how to add new data source in a way that can be used in Looker studio for correlations and calculations.
21. How to create new metrics to track in Looker Studio.
22. Instructions on updating existing dashboard view to add new metrics.
23. Instructions on building new dashboard with a set of metrics.
24. Troubleshooting practices (SOP) for common/frequent issues that may arise in BigQuery<>Looker Studio tech stack.
25. Maintenance practices (SOP) for continued reliability.

Candidate Requirements

1. Expertise in Data Analytics: Proven track record in handling large datasets and performing complex analyses.
2. Proficiency in BigQuery and Looker Studio: Extensive experience in using BigQuery for data warehousing and Looker Studio for data visualization.
3. Strong Background in API and Database Structures: Ability to integrate various APIs and understand complex database schemas.
4. Problem-Solving Skills: Capable of identifying and resolving data-related issues effectively.
5. Excellent Communication: Ability to articulate technical concepts clearly to both technical and non-technical stakeholders.