Data Warehouse and Reporting Solution for Ecommerce

Goal

Build a data warehousing and reporting solution for a fast growing ecommerce company with 200M+ in sales. Current solution used data manually entered into Google Sheets and a single connector to run raw queries from BigQuery for manual upload into Tableau.

Client

Top 10 fastest growing US ecommerce startup

What We Did
  • API Data Ingestion into Data Warehouse
    • Google Analytics Core Reporting API
    • Facebook
    • Shopify
    • Snapchat
    • Pinterest
    • Klaviyo
    • Friendbuy
    • Criteo
    • Hootsuite
  • Reporting Automation Solutions
    • Tableau
    • Google Sheets
    • Slack
Technologies Used
  • Google Analytics Reporting API
  • Google BigQuery
  • Facebook Ads Insights API
  • Shopify Marketing Activities API
  • Shopify Data Warehouse API
  • Amazon Web Services – EC2 Ubuntu
  • Amazon Web Services – Lambda + API Gateway
  • Amazon Web Services – Redshift
  • Amazon Web Services – S3
  • Tableau
  • NumPy, boto3, Facebook SDK, singer, custom Python packages

Project Outcome

Solution

  • Complete audit of their Google Analytics implementation both in platform and the website.
  • Designed an analytics strategy and collaborated with their product and technology teams throughout the entire process.
  • We did something similar for their iOS and Android applications to ensure they had the same level of data from all their data sources.
  • Used Google BigQuery to join all the data in a single place so it could be joined with their data warehouse to be able to get a full picture of their users.

Results

  • A complete user-level 360 view of their entire user base ready for advanced analysis (pathway, segmentation, churn, etc.).
  • Highly advanced audiences used for activation within the Google Marketing Platform, such as Campaign Manager, DV360, and Google Ads.
  • Dashboards and reporting for management to easily monitor changes and performance.