Netsuite & Big Commerce Data Warehouse Project

Background & Goal
We're a medium size ecommerce retailer running on Netsuite for our ERP and Big Commerce for our front end website. We're looking for a data engineer to help build a data warehouse for Netsuite, Big Commerce, and potentially some other data platforms to help us analyse our data faster and more accurately.

We wish to engage a third party data Engineer to build a central Data Warehouse in Google BigQuery (or other similar service). We want to analyse data in a data visualisation tool such as Power BI. We are also interested in other capability that we will be able to use the data warehousing for in the future (better exception reporting, better customer analytics, new sales & marketing opportunities, etc)

We have a capable internal IT/Analytics team, however the teams resources and stretched so we're looking for help to implement this project. We aren't looking for a "black box" solution. We need you to build the foundation, document it well, and then train us so we can maintain it and build on top of it later.

Why We Are Doing This
Stop the manual work: Automate daily reports so we don't have to copy-paste data.
Fix data issues: Easily spot when stock or product data in Netsuite doesn't match BigCommerce.
Better insights: We want to join web traffic data (GA4) with sales data to understand customer behavior better.
Scaleable and stable: We want a system that is reliable, able to handle large data sets common with e-commerce businesses
Time series: current systems make analysing information by minute/hour/day cumbersome due to volume of transactions, and speed/performance of Netsuite.
Granular to macro: we want the ability to roll up to macro views whilst also drilling down to the lowest level of detail eg individual SKU’s and transactions
Future-proofing: We want a system we can plug other tools into later (like Facebook Ads or our Email platform) without starting over.

Phase 1
We need a standard ELT pipeline set up.
Warehouse: Google BigQuery or similar
BI Tool: PowerBI.
Transformation: All tables transformed & connected in Big Query ready for use in PBI or other platforms
Connectors: We are happy to pay for connector tools providing they offer value for money and meet data security requirements. This is especially the case if buying helps us maintain the database & products in the future with greater ease. We don't want custom scripts unless absolutely necessary.
The Data Sources
Netsuite ERP: Sales Orders, Invoices, POs, Customers, Items, and Inventory. You'll need to help us figure out the best way to connect (ODBC/SuiteAnalytics vs REST API) for cost and speed. Handling custom fields is a must.
BigCommerce: Orders, Products, Customers. The standard Big Commerce tables are already connected to Bigquery, however some transformation will be required to meet our business logic & match Netsuite
Google Analytics 4: Just the standard native export to BigQuery.

What the Data Needs to Do
It's not enough to just dump raw data. We need you to clean it and model it for PowerBI.
Clean up: Handle nulls, timezone fixes (UTC to Local), parent/child product relationships, etc
Model it: Build a clean schema so our analysts can drag-and-drop fields in PowerBI without headache.
Ensure Tidyiness: Handle product variant data correctly, model & tidy product-category information from Big Commerce correctly across the product catalogue,
Business Logic: For metrics like Margin, data should be taken either direct from Netsuite where practical, or recalculated in the warehouse


The Reports
We need to prove this works. As part of the build, we want 4 prototypes in PowerBI or Big Query (as appropriate), these can be built with the help of our internal team.
Daily, weekly, monthly sales dashboard: eg Daily Sales vs Target.
Reconciliation: A report that shows mismatched items names, SKUs or barcodes between Big Commerce & Netsuite
Inventory turns by category: Stock VS Sales by category
Product performance: visits to page views to clicks to conversions to revenue, combining GA4, BigCommerce and Netsuite data

Future Plans (For Context)
You don't need to build this yet, but the architecture should be ready for it later:
Marketing data (FB/Google Ads) - capability to start building our own attribution and analytics tools
Email data (Dot Digital) - to combine email marketing and referral metrics with outcomes ie BigCommerce and Netsuite
Competitor pricing scrapes - the ability to match competitor price scrapes to our own product data and eventually set up some automatic price rules. This could include other types of external data in excel, sheets, csv, etc

What You'll Hand Over
By the end of the project (aiming for ~8 weeks), we expect:
The Infrastructure: GCP project set up with BigQuery, IAM roles, etc.
The Code: A Git repo with all the SQL/dbt models. It needs to be commented well so our team understands why you did what you did.
Documentation: A simple data dictionary and a "how-to" guide for troubleshooting common errors.
Developing & Training: Sufficient interaction and development sessions with our internal team to ensure the data warehouse matches our business logic and we have sufficient understanding of the final product that you can handover the keys.
Exception / error reporting: A dashboard or notification system so we are aware when the databases or pipelines have an issue.

Timeline & Assumptions
Timeline: We are thinking ~8 weeks. Weeks 1-3 for setup/ingestion, 4-6 for modeling, and the end for testing/handover.
Access: We'll get you admin access to all the systems you need to set this up
Data Quality: We know our data isn't perfect. Your job is to highlight the bad data, not manually fix issues in Netsuite or Big Commerce for us. The right final product will help us identify these fixes ourselves.
Data update/ refresh Frequency: We are fine with Daily updates for now, but want the option to go Hourly later.

Your response

In order to assess the right engineering or team to help us with our project, we’ll need the following in response:
Quote for the work - either fixed price, or hourly rate with expected timeline.
Examples of previous work, ideally with referees. Examples ideally should be for a mature medium-size business, using some of the same systems.
Your availability and expected timing
What your expectations are of our involvement in the project - we are flexible & available, but we want to be clear about what you think we need to provide or be available for.
Any inclusions, exclusions, or assumptions you would like to specify upfront.