Technical task: building an analytical system (GA4 + LIS → BigQuery → Superset) for the marketing of a medical laboratory

1. Project Goal

To build a unified analytical system that:

• combines web analytics data (website) and internal system data (LIS);

• allows analyzing marketing effectiveness through actual revenue;

• displays data in the form of a management (CMO) dashboard;

• is ready for scaling (online payment, calls, advertising, competitors).

2. Current Infrastructure

Currently, there is:

• Google Analytics 4 — configured

• Google Ads — connected to GA4

• Google Search Console — connected

• Google Business Profile — in use

• Microsoft Clarity — connected

• LOOQME — reputation monitoring (not connected) here the dashboard needs to be transferred

• LIS — already connected to Superset (data available)

• online payment — absent (will be later)

• Binotel / call tracking — in the process of connection

3. System Architecture

Target scheme:

GA4 → BigQuery

LIS → (already available / or via BigQuery)



 ↓



 Apache Superset



 ↓



 CMO Dashboard

4. Scope of Work (MVP stage)

4.1. Integration GA4 → BigQuery

It is necessary to:

• set up export from GA4 to BigQuery;

• check data accuracy:

  • users

  • sessions

  • events

  • traffic source;

• describe the table structure.

4.2. Data Analysis and Preparation of LIS

It is necessary to:

• analyze the data structure of LIS in Superset;

• identify key fields:

  • order date

  • amount (revenue)

  • service

  • branch

  • client (if there is an ID)

• prepare data for analytical view (cleaning, standardization of names);

• create a prepared dataset for analytics.

4.3. (Key) Stitching GA4 and LIS (basic level)

It is necessary to implement the logic of connection:

• by date;

• by services (if possible);

• by sources (partially, through UTM or heuristics).

❗ Important:
at this stage full user-level connection may not exist, so an aggregated model is allowed:

• traffic → orders → revenue by days / channels.

4.4. Event Configuration in GA4

It is necessary to set up / check:

• click_phone

• click_book

• click_address

• open_service_page

• scroll_75

• click_cta

Parameter transmission:

• page

• service (if possible)

4.5. Deployment / Refinement of Apache Superset

It is necessary to:

• check the existing Superset;

• connect BigQuery;

• combine sources:

  • GA4

  • LIS

• configure datasets;

• optimize performance.

4.6. Creation of a Unified Analytical Model

It is necessary to create basic tables / views:

traffic

• date

• source / medium

• users

• sessions

events

• date

• event type

• quantity

sales (from LIS)

• date

• revenue

• number of orders

• branch

• services

4.7. Development of CMO Dashboard

It is necessary to create 1 main dashboard:

1. Business Metrics

• Revenue

• Number of orders

• Average check

2. Traffic

• Users

• Sessions

• Dynamics

3. Traffic Sources

• Source / Medium

• Channel share

4. Funnel (basic)

• Users → Events → Orders

5. Microconversions

• clicks on phone

• clicks on booking

6. Performance by Days

• traffic vs revenue

• trends

7. Branches (if data is available)

• revenue by points

• number of orders

4.8. Basic Metrics

It is necessary to implement:

• Conversion Rate (events → orders)

• Revenue per user

• Orders per session

• Average check

• Users → orders %

4.9. Documentation

It is necessary to:

• describe the data structure;

• describe the dashboard;

• how to add new sources;

• how to edit metrics.

5. Architecture Requirements

• scalability;

• clean data structure;

• minimum “manual” solutions;

• ability to connect new sources.

6. Next Steps (consider in architecture)

• online payment (purchase, revenue)

• Binotel (calls)

• advertising expenses

• price monitoring bot

• CRM

7. Expected Result

1. GA4 → BigQuery works

2. LIS prepared for analytics

3. Data stitched (at the aggregated level)

4. Superset shows a unified dashboard

5. There is a basic funnel

6. There is documentation

8. Acceptance Criteria

• data is displayed correctly;

• revenue from LIS matches reality;

• dashboard updates;

• metrics are calculated correctly;

• system is scalable.

9. Requirements for the Performer

In the response, indicate:

1. Experience with GA4 + BigQuery

2. Experience with Superset

3. Experience with SQL / data modeling

4. Examples of BI dashboards

5. Cost

6. Terms

7. Can you support the system in the future