Data transformations

As you can guess from the questions they want their data to be able to answer, heavy transformations were required. As we are a trusted dbt (data build tool) partner, this was obviously our tool of choice for data modeling.

• The main transformations were:
• Enrich BigCommerce data with SKU costs, shipping costs, subscription information, and fulfillment center.
• Multi-touch attribution down to the ad group level. To do so, we built our own Markov Chain data-driven attribution model and combined it with our Modern UTM Strategy.
• Bring every metric & dimension at the order line item level.

In this step, we also built all the necessary failsafe mechanisms, alerts, and data tests to ensure the data pipeline is robust and maintains the highest data quality possible.

Data visualization

Now that the data is finally refined into something that can quickly answer their questions, it is time to connect it to a dashboard for self-service. Each dashboard also displays comparison metrics based on the client’s requirement (month over month, year over year, against the previous period, against target). Here’s the dashboard categories list and the main KPIs we built for them.

• Finances (by date, product, revenue type, state, city)
  • Discount amount
  • Refund amount
  • COGS
  • Gross profit
  • Gross margin
• Orders (by order creation date, order shipped date, product, revenue type, state, city, discount code, marketing channel, fulfillment center)
  • Number of orders/items
  • Revenue
  • Refund amount
  • Average order value (AOV)
• Refunds (by refund date, refund reasons, product, revenue type, state, city, discount code, marketing channel, fulfillment center)
  • Refund quantity
  • Refund amount
  • Refund rate
• Subscriptions (by subscription creation date, next order date, product, revenue type, state, city, discount code, marketing channel, fulfillment center)
  • New subscriptions
  • Active subscriptions
  • Cancelled subscriptions
  • Growth rate
  • Churn rate
  • Revenue churn rate
  • Planned recurring orders (forecast of orders based on subscription renewal dates)
• Customers (by date, customer creation date, customer segment, cohort, state, city, acquisition channel, subscription type)
  • New/returning customers
  • Lifetime value (CLV)
  • Average order value (AOV)
  • Average number of orders over lifetime
  • Repurchase rate
• Paid Ads (by date, ad platform, campaign, ad group)
  • Impressions, clicks, click-through rate, conversions, conversion rate
  • Spend, CPM, CPC, Cost per Conversion
  • Return on Ad Spend (ROAS)
• Web Analytics (by date, source/medium/campaign/ad group, state, device, landing page, discount code)
  • Sessions, Users, New Users, Page Views, Purchases
  • E-commerce funnel (view product, add-to-carts, begin checkout, web purchases)
  • Conversion rates
  • Average web purchase value
• Pipeline Monitoring
  • GCP costs
  • Execution time
  • Compiled code
  • Scheduled run status

Smart Alerts

Using historical data and the targets for each KPI, we built smart alerts that inform us and the clients when:

• A KPI behaves abnormally (drop in purchases or conversion rate for example)
• A KPI is trending away from it’s target

Ongoing support and business intelligence

The executives at Good Ranchers understand that a data pipeline isn’t a set-and-forget project. Technology and their needs are constantly changing. Their data architecture needs to adapt to those changes. By partnering with us on our monthly package, they have peace of mind that their pipeline will continue working, ask us for any ad-hoc requests, and even deep analysis to uncover growth opportunities. We are still working with them to this day.

Here are some of the analyses we performed for them:

• Cohort analysis
• Buyer segmentation (aka RFM analysis)
• Sales + margin forecasting
• Churn analysis
• E-commerce funnel analysis