One Click Data Pipeline for Modelling E-commerce Profitability (dbt, Shopify, Facebook, Fedex, GLS and other costs)
In a world where e-commerce business models are relatively uniform, where few operational tools dominate the market, lies a huge opportunity in analyytics of building modular, reusable data transformation models. A lot of DBT packages so far have focused on modeling isolated data sources from extraction tools like Airbyte. We see the opportunities of going one step further, by open sourcing the full end to end pipeline around a critical use case for every e-commerce: profitability calculation!
Here are the few dashboards that were built on top of the data model built from this repo.
1. Overview of Revenue and Profits
This view gives the business the ability to understand Revenue and Profits, with possibility to drill down at the level of an order.
2. Overview of Impact of different Cost Factors in EUR and % of Revenue
This view gives the business the ability to understand how does each cost factor impacts overall profitability, with possibility to drill down at the level of an order.
3. Map of Countries with highest Profit Margins after deducting all the costs from Order Revenues
This view gives the business the ability to understand which countries see highest margins, with possibility to drill down at the level of an order.
4. Overview of Performance of Paid Marketing and ROI (Profits made from 1 EUR of Marketing Investment)
This view gives the business the ability to optmise marketing spent not on ROAS which takes into consideration sales but on ROI (Return being Profits). In Revenue a lot of country specific costs like VAT, shipping can lead to widely different Profit outcome.
This project goal is to create a fully modular data pipeline aimed at calculating e-commerce profitability. This pipeline is designed to be open-source and easily reusable, enabling data teams to quickly deploy a robust profitability model without starting from scratch.
This repository is a production dbt pipeline example that models the profitability of an e-commerce business. Data is extracted and loaded to a BigQuery data warehouse by Airbyte. The main goal of this repo is to show a production pipeline for a common analytics use case (improving profitability).
- Shopify: E-commerce platform data (including Shopify Orders, Shopify Balance Transactions, Shopify Transactions)
- Facebook Ads: Paid Marketing (Facebook Advertising) data
- Paypal: Payment processing data (coming from Shopify)
- FedEx and GLS: Shipping data (coming from GSheets)
- Manufacturing Costs: Cost data from the manufacturing process (coming from Gsheets)
Detail the dbt models included in the project and their relationships.
stagingmodels: Models raw data from source systemsintermediatemodels: Models Revenue, and all costs separately. Join and aggregate data from staging models.finalmodels: Calculates profitability metrics and other business KPIs and build fact_orders table including all costs at order level
Documentation of model to combine all costs at the level of an order once each cost has been modeled:
https://blog.getdot.ai/automating-end-to-end-profitability-reporting-for-your-e-commerce-business-shopify-fedex-paypal-c65c31a69441
Documentation of model to get VAT costs at the level of an order:
https://blog.getdot.ai/how-to-model-vat-costs-to-the-level-of-order-for-profitability-calculation-in-e-commerce-e315c1d47f95
Documentation of model to get Manufacturing Costs at the level of an order:
https://blog.getdot.ai/how-to-model-manufacturing-costs-to-the-level-of-order-for-profitability-calculation-in-e-commerce-c553ad327339
Documentation of model to get Shipping costs at the level of an order:
https://blog.getdot.ai/how-to-model-shipping-costs-to-the-level-of-order-for-profitability-calculation-in-e-commerce-b5cd3b400ace
Documentation of model to get Commission Fees at the level of an order:
https://blog.getdot.ai/how-to-model-commission-fees-shopify-paypal-to-orders-for-profitability-calculation-in-937d4a8b0fe9
Documentation of model to get Marketing Costs at the level of an order:
https://blog.getdot.ai/how-to-model-marketing-costs-to-the-level-of-order-for-profitability-calculation-in-e-commerce-06fa6bade89f
We would love to hear your feedback or about your struggles with integrating data! Please reach out to us at hi@getdot.ai or DM us in the dbt or Airbyte Slack channels.
This repository can be installed to create a "one click pipeline." However, you will need to recreate the sources for cost data because they are custom to the business. The Facebook, Shopify, and Paypal transformations should be plug and play.
Learn more about us: https://www.getdot.ai/