Skip to content
All case studies
E-commerce / Pharma Foundation Build Custom segmentation

Data infrastructure optimisation for an online pharmacy

Spanish online pharmacy with data scattered across SharePoint and limited BigQuery retention. Built a future-proof infrastructure with custom segmentation.

200h

saved per year on ETL

+7.8%

revenue increase in 3 months

Full

historical retention in warehouse

The client

An online pharmacy in Spain offering a wide range of health and wellness products to customers nationwide. Mission: make healthcare accessible via an efficient, user-friendly online platform.

The problem

Data scattered across multiple platforms, leading to inefficient reporting and decision-making. Specifically: no centralised warehouse (only Google Analytics in BigQuery; everything else in SharePoint files updated manually); unmapped data relationships across sources; only 60-day Google Analytics retention in BigQuery; no integration between BigQuery and Power BI.

Architecture

flowchart LR SP["SharePoint
files (legacy)"] -->|Migration via GCF| W GA["Google Analytics"] -->|Full retention| W API["Source APIs
(non-Fivetran)"] -->|Custom Python
via Google Cloud Functions| W W[("BigQuery
warehouse")] W --> CAT["Atlan
data catalog"] W --> AGG["Aggregated
reporting layer"] AGG --> SEG["Custom segmentation
(purchase habits)"] SEG --> PBI["Power BI
dashboards"] CAT --> A["Analyst
self-service"] style SP fill:#f0e8db,stroke:#1a1a2e style GA fill:#f0e8db,stroke:#1a1a2e style API fill:#f0e8db,stroke:#1a1a2e style W fill:#d8c8ed,stroke:#1a1a2e style CAT fill:#b6e0c2,stroke:#1a1a2e style AGG fill:#b6e0c2,stroke:#1a1a2e style SEG fill:#7551c3,stroke:#1a1a2e,color:#fff style PBI fill:#f4c8a8,stroke:#1a1a2e style A fill:#f4c8a8,stroke:#1a1a2e

What I did

  • 01.Collaborated with stakeholders to select the most suitable warehousing, ELT, and visualisation tools.
  • 02.Created the main ETL process using Google Cloud Functions and Google Cloud Storage Bucket.
  • 03.Developed custom Python solutions via Google Cloud Functions to connect to API endpoints and pull all necessary data into the warehouse.
  • 04.Analysed data structures and schemas of each source tool; created a comprehensive data catalog in Atlan establishing relationships across tables.
  • 05.Reconfigured Google Analytics data retention to store all historical data in the warehouse.
  • 06.Built SQL queries to create the aggregated reporting data layer.
  • 07.Conducted workshops with stakeholders to define dashboard requirements.
  • 08.Designed and developed interactive Power BI dashboards with drill-down and multiple aggregation levels.
  • 09.Created custom segmentation logic based on purchase habits, with end-user-customisable parameters and the ability to filter dashboards by default or custom segment.

InteractiveMove the parameters

Custom customer segmentation, rebuilt live

Sample customers
← lower order frequencyhigher →

    Outcome breakdown

    Each metric, against the baseline it replaced

    Tools used

    BigQuery Google Cloud Functions Python Atlan Power BI Google Analytics

    A similar problem in your stack?

    Send me the rough shape of it. I'll figure out scope on a 30-min call.

    Book a call