Daasity Knowledge Base
Daasity Knowledge Base
  • Start here
    • Daasity Overview
    • Important Concepts
      • Attribution Overview
      • What is a Daasity Valid Order?
      • Customer Householding
      • Timezones in Daasity
      • Ecommerce Glossary
      • Daasity Data Security Overview
    • Retail Analytics 101
      • Understanding the Basics: POS Data 101
      • How to use Syndicated Data
    • Product Changelog
    • Feature Requests
  • 🔌Extract
    • Extract your data with Daasity
    • All Integrations
      • Alchemer
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Algolia
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Amazon Advertising
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Where to Find Your Data
      • Amazon Seller Central
        • Validating Your Amazon Data in Daasity
          • Comparing Your Amazon Seller Central Orders to Daasity
        • Integration Setup
          • Configure Amazon Reports
          • Monitor Amazon Reports
        • Integration Specifications
          • Orders API
          • FBA Customer Concessions Reports
          • FBA Sales Reports
          • FBA Inventory Reports
          • FBA Removals Reports
          • Inventory Reports
          • Order Tracking Reports
          • Performance Reports
          • Returns Reports
          • Settlement Reports
          • Seller Retail Analytics Reports
        • Workflow Configuration Setup
          • Orders API
          • FBA Customer Concession Reports
          • FBA Inventory Reports
          • Performance Reports
          • FBA Sales Reports
          • FBA Removal Reports
          • Inventory Reports
          • Order Tracking Reports
          • Returns Reports
          • Seller Retail Analytics Reports
          • Settlement Reports
        • Transformation Configuration Setup
          • Orders API
          • FBA Customer Concessions Reports
          • FBA Inventory Reports
          • Performance Reports
          • FBA Sales Reports
          • FBA Removals Reports
          • Inventory Reports
          • Order Tracking Reports
          • Returns Reports
          • Seller Retail Analytics Reports
          • Settlement Reports
      • Amazon Vendor Central
        • Integration Setup
        • Integration Specifications
        • How to use
        • ASIN Mapping Configuration Setup
        • Workflow Configuration Setup
        • Transformation Configuration Setup
          • Vendor Sales & Inventory Report
          • Vendor Traffic Report
      • AppLovin
        • Integration Setup
      • Attentive Mobile
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Azure SQL Database
      • Back In Stock
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Big Commerce
        • Integration Setup
          • Connect Daasity From BigCommerce Marketplace
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • BigQuery Datasource
      • Bing Ads
        • Integration Setup
        • Integration Specifications
      • Circana
        • Integration Setup
        • Integration Specifications
        • Data Release Calendar
        • H-E-B Circana
      • Cordial
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Criteo
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • D​ata File (CSV, XLS)
      • Emplifi
        • Integration Specifications
      • Extensiv (Skubana)
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Facebook Ads
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Where to Find Your Data
      • Fairing V2
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Important Notes
      • Google Ads
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Important Notes
      • Google Analytics 4
        • API vs BigQuery
        • API Integration
          • Integration Setup
          • Integration Specifications
          • Workflow Configuration Setup
          • Transformation Configuration Instructions
          • Custom Reports
          • Validating GA4 Data in Daasity
          • Blending Universal Analytics & GA4 Data
        • BigQuery integration
          • Integration Setup
          • Integration Specifications
          • Workflow Configuration Setup
          • Transformation Configuration Instructions
          • Important Notes
          • Creating a Custom Report
      • Google Sheet
      • Gorgias
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Important Notes
      • Impact Radius (Beta)
        • Integration Specifications
      • Iterable
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Klaviyo Summary Statistics
        • Integration Setup
        • Integration Specifications
      • Klaviyo V2
        • Integration Setup
        • Integration Specifications
      • KnoCommerce
        • Integration Setup
        • Integration Specifications
      • Loop Returns
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Microsoft SQL Server Database
      • Mongo Database
      • MySQL Database
      • Narvar
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • NetSuite
      • Newstore
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Northbeam
        • Integration Setup
      • Okendo
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Ometria
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Order Desk
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Pepperjam
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Pinterest
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Postgres Database
      • Postscript (beta)
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Important Notes
      • Recharge
        • Integration Setup
        • Integration Specifications
        • Integration Specifications (2021-11 Beta)
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Recharge Data and Known Discrepancies
      • Retail Next
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Rockerbox
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Salesforce Commerce Cloud
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • SFTP
      • ShipBob
        • Integration Setup
        • Integration Specifications
      • Shipstation
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Shopify
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
        • Shopify B2B Data
      • SKIO
        • Integration Setup
        • Integration Specifications
      • Snapchat
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • SPINS
        • Integration Setup
        • Integration Specifications
        • How to Use
        • Data Release Calendar
      • Stay Ai
        • Integration Setup
        • Integration Specifications
      • Target Plus
        • Integration Setup
      • Thankful
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • TikTok
        • Integration Setup
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Walmart Marketplace
        • Integration Setup
        • Integration Specifications
      • Whole Foods Market
        • Integration Setup
        • Integration Specifications
        • How to use
      • Yotpo
        • Integration Setup
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
      • Zendesk
        • Integration Specifications
        • Workflow Configuration Setup
        • Transformation Configuration Setup
    • Brand Supplied Data (BSD)
      • BSD Overview
      • Monitoring Your BSD Progress
      • Revenue Plan
      • Marketing Budget
      • Marketing Spend
      • RFM Values
      • Shipping & Fulfillment Cost
      • SKU Hierarchy
      • SKU Mapping
      • SKU Cost
      • Discount Code Mapping
      • Other Order Sources
      • Channel Mapping
        • Overview & Setup
        • Filling out Channel Mapping
        • Missing from GA & Missing from BSD
      • Configuration
  • 📊VISUALIZE
    • Dashboards
      • Dashboard Overview
      • Standard Dashboards
        • Daasity 101 Overview Dashboards
          • Flashes
          • Shopify Sales Report
          • Account Health
        • Digital Product & Revenue
          • Orders and Revenue
          • Amazon Settlement Report
          • Product Repurchase Rate
          • Product
            • Product Dimensions
              • Why is a Product Name NULL?
          • Inventory
          • Operations
            • Fulfilment Status Options
        • Retention Marketing Dashboards
          • Notifications (Email/SMS)
          • Retention
          • Customer Performance (Email/SMS)
          • Site Funnels
        • Acquisition Marketing Dashboards
          • Attribution Deep Dive
            • Marketing Attribution
              • Attribution Overview
              • Survey-Based Attribution
              • Discount Code Attribution
              • First Click, Last Click, and Assisted Attribution
              • Custom Attribution
              • Vendor-Reported Attribution
              • CPO, CPA, and CAC
              • Missing from GA & Missing from BSD
          • LTV & RFM
            • RFM
              • RFM in the Daasity App
              • Understanding RFM
              • Time on Books (TOB)
          • Marketing
          • Vendor-Reported Marketing Performance
          • Site Analytics & Attribution
      • Integration Dashboards
        • Amazon All Orders Report
        • Amazon Seller Central Business Reports
        • Gorgias
        • Klaviyo Campaign & Flow Performance
        • Loop Returns
        • Okendo Reviews
        • ShipBob
        • Shipstation
        • Subscription
        • Walmart Marketplace Account Sales Report
        • Yotpo
    • Explores
      • Explore Overview
      • Order Line Revenue vs. Transactional Sales Report
      • How to Access An Explore
      • Standard Daasity Explores
        • Current Inventory Level Explore
        • Customer Details Explore
        • Daily Company Metrics Explore
        • Daily Marketing Plan To Actual Marketing Performance Explore
        • Daily Plan to Actual Explore
        • Lifetime Value Explore
        • Marketing Attribution Explore
        • Multi-Channel Transaction Explore
        • Order and Order Line Revenue Explore
        • Product Page Explore
        • Shopping Stage Explore
        • SKU Level Repurchase Rates Explore
        • Traffic Explore
        • Transactional Sales Report Explore
        • UA vs G4 Explore
      • Integration Explores
        • Algolia
        • Amazon Settlement Explore
        • Fairing Survey Responses Explore
        • Gorgias Tickets Explore
        • Loop Returns Explore
        • Okendo Reviews Explore
        • Subscribers and Subscription Monthly Churn Rates Explores (Recharge)
        • Shipstation Explore
        • Yotpo
        • Zen Desk
    • Looker Guides
      • Building visualizations
      • Working with date dimensions & filters
      • Schedule a Report or Dashboard
      • Exporting Data
    • Looker Guides (Developer)
      • Daasity Hub & Spoke Introduction
      • Hub & Spoke File Structure
      • Working with LookML Dashboards
      • Customizing Base Daasity Code
    • Visualize using other BI tools
  • 🔀Transform
    • Transforming Data with Daasity
    • Daasity Data Models
      • Daasity Data Model Overview
      • Unified Data Schemas
        • Unified Schema Introduction
        • Unified Order Schema (UOS)
        • Unified Notification Schema (UNS)
        • Unified Subscription Schema (USS)
        • Unified Marketing Schema (UMS)
        • Unified Traffic Schema (UTS)
        • Unified Wholesale Schema (UWS)
      • Data Mart Models
        • Data Mart Introduction
        • Data Reporting Platform (DRP)
        • Channel Attribution Data Mart
        • Marketing Data Mart
      • Transform Code
        • Transform Code Introduction
        • Transform Code Sequence - DRP
        • Initialization Code
        • Householding Code
        • Order Status Code
        • Order Channel and Marketing Code
        • Daily Plan Code
        • Order and Order Line Revenue
        • Plan to Actual Code
        • Customer Code
        • Subscription Code
        • Reviews
        • Life-Time Value Code
        • Returns
        • Product and Inventory Code
        • Email & SMS
        • Transform Code Sequence - Data Mart
        • Unified Order Schema (UOS) Code
        • Unified Marketing Schema (UMS) Code
        • Unified Notification Schema (UNS)
        • Channel Attribution Data Mart Code
        • Marketing Data Mart Code
      • Source to Final Destination
    • Code Repository
      • Code Repository Overview
      • Getting Access to Your Code Repository
      • Code Repository Branches
      • Custom SQL Code
      • Custom Script Manifest Files
    • Script Manifest (YAML) Files
  • 🎵Data Orchestration
    • Workflows
      • Getting Started with Workflows
      • Creating Workflows
      • Managing Workflows
      • Workflow Metrics
    • Notifications
      • Introduction to Notifications
      • Enabling Notification Emails
      • Notifications UI
      • Managing Notifications
      • Authorization List
  • Audiences
    • Audiences Overview
    • Pushing Audience Data to Targets
      • Targets Overview
      • Attentive
        • Events Target
        • Subscribers Target
        • Target Endpoint Documentation
      • Facebook Ads
        • Facebook Target
        • Target Endpoint Documentation
      • Google Ads
        • Google Ads Target
        • Target Endpoint Documentation
      • Klaviyo
        • Events Target
        • Profiles Target
        • Target Endpoint Documentation
      • Sailthru
        • Events Target
        • Users Target
        • Target Endpoint Documentation
      • Tiktok Ads
        • TikTok Target
        • Target Endpoint Documentation
    • Building Audiences
  • warehouse
    • BigQuery
    • Redshift
    • Snowflake
    • Test Data Warehouse
      • Test Data Warehouse Overview
      • Create a Snowflake Test Warehouse
      • Create a Redshift Test Warehouse
  • —
    • Go to Daasity App
    • Contact Support
    • ✍️Feature Requests
Powered by GitBook
On this page
  • Overview
  • Entity Relationship Diagram (ERD)
  • Tables & Source Code
  • Initialization Tables
  • Order Status Tables
  • Order Channel and Marketing Tables
  • Daily Plan Tables
  • Order and Order Line Revenue Tables
  • Plan to Actual Tables
  • Customer Tables
  • Subscription Tables
  • LTV Tables
  • Product and Inventory Tables
  • Notification Tables

Was this helpful?

  1. Transform
  2. Daasity Data Models
  3. Data Mart Models

Data Reporting Platform (DRP)

This page provides a detailed description of the Daasity Reporting Platform (DRP) within the Daasity Data Model and defines each table in this schema

PreviousData Mart IntroductionNextChannel Attribution Data Mart

Last updated 1 year ago

Was this helpful?

Overview

The Data Reporting Platform [drp] is the initial Data Mart model designed by Daasity and is the schema that contains all of the reporting and analysis tables that are derived from the different Unified Schemas.

This schema will be deprecated over the coming year and tables will be moved to separate Data Marts allowing for better leverage of the Daasity Data Orchestration engine

Entity Relationship Diagram (ERD)

the ERD for the Data Reporting Platform [drp] illustrating the different tables in the schema and the functional area of the tables.

Tables & Source Code

There are eleven (11) different groups of reporting and analysis tables in this schema:

Initialization Tables

  • [drp.data_validation] - this table is truncated each day and populated at the end of each SQL script to track duplicates and total execution time

  • [drp.integration_mapping] - this table is populated from the platform.account_integrations table and is used to normalize information for each integration in the account

  • [drp.calendar] - this table is recreated every day to calculate the correct dates used when performing comparisons for Today, Yesterday, Week to Date, Month Date for values like This Year and Last Year. This table makes it very easy to perform comparisons that are properly date adjusted to ensure that comparisons are done correctly for a Retail Calendar. This table is optimized for reporting and is the source for the "Calendar" view

  • [drp.currency_exchange_rates] - this table is updated each day to add the current currency exchange rate enabling currency conversion from over 180 currencies to the currency of the account

  • [drp.amazon_currency_mapping] - this table maps the different Marketplace at Amazon to the appropriate currency and is critical for merchants that have an Amazon presence

Order Status Tables

  • [drp.order_status] - this table is derived from the UOS tables and contains summary information for each order that is used to populate two important fields [valid_order_flag] and [valid_order_flag_daasity] that determine how the source Commerce platform identifies an order as well as how the standard Daasity logic identifies an order into different buckets. This table also includes a number of other key dimensions to help with order analysis such as flags for discount, refund, subscription, first, and last order. This table is optimized for reporting and is the source for the "Order Status" view

  • [drp.order_business_unit] - this table contains a record for each order and stores information both the the Daasity UI that provides information on the Commerce integration (such as the country, business channel, etc.) and information from the integration that provides information such as the sales channel, currency, etc. This table is optimized for reporting and is the source for the "Order Business Unit" view.

Order Channel and Marketing Tables

  • [drp.order_channel_source] - this table contains one record for each order and attribution method in the original Daasity attribution - last click, first click, discount code mapping using the BSD, Fairing Integration and the resulting out-of-box Daasity logic. This design makes it easy to add additional model types to the data model and then rank the attribution type in order to generate an overall attribution.

  • [drp.order_channel] - this table contains a single record for each order and transposes the [drp.order_channel_source] table into a flat table. This table is optimized for reporting and is the source for the "Order Channel" view.

Daily Plan Tables

  • [drp.daily_plan] - this table is created from the Revenue Plan BSD and creates a revenue target for each day / store combination

  • [drp.daily_marketing_plan] - this table is created from the Marketing Budget BSD and creates a spend target for each day / vendor combination

Order and Order Line Revenue Tables

  • [drp.order_line_revenue] - this table is dependent onthe UOS and Order Status tables and contains one record for each item (SKU) in every order. This table is designed to enable analysis down to the order-item level including profitability as it allocates order-level discounts, revenue (ex: shipping) and costs (ex: fulfillment, marketing) to each order line. This table is optimized for reporting and is the source for the "Order Line Revenue" view

  • [drp.product_affinity] - this table contains one record for each unique combination of order lines within an order and also from the first to second order. This enables product mix analysis to determine what customers are buying in the same cart and also from the first to second card. Note: the table will only include one unique combination thus an order with two products will only have one row as Product A with Product B is the same as Product B with Product A

Plan to Actual Tables

  • [drp.daily_plan_to_actual] - this table combines the actual sales data from UOS with the [drp.daily_plan] table to optimize reporting for revenue forecast to actual. The table is the source for the "Daily Plan to Actual" view

  • [drp.daily_plan_to_actual_marketing] - this table combined the actual spend from UMS with the [drp.daily_marketing_plan] table to optimize reporting on planned marketing to spend to actual spend. The table is the source for the "Daily Marketing Plan to Actual Marketing Performance" view

Customer Tables

  • [drp.customers] - this table contains one row for each customer from the source Commerce platforms and contains contact information and a link from the source Commerce customer record to the householded customer record. The table is the source for the "Customers" view

  • [drp.customer_profiles] - this table contains one row for each householded customer and contains information across first, second, last order, summary revenue and order information and segmentation information. This table can be joined with a number of other tables to enable sophisticated analysis by segmenting into different customer groups. This table is the source for the "Customer RFM & First/Second/Last Order" view

  • [drp.historical_customer_segmentation] - this table contains one record for each unique customer / month since they have been a customer. The table calculates the revenue amounts for the customer during that month and the customer segment for that customer at the start of each month. This table can help track business performance at the customer level over time and indicate key areas of focus to improve customer performance. This table is the source for the "Customer Segments - Historical" view

  • [drp.retention_performance] - this table contains two records for each unique customer - one for last month and one for this month. The table summarizes the customer level revenue performance through yesterday and combines this with the customer segmentation from the [drp.customer_profiles] table to provide reporting on how each customer segment is performing month over month. This table is the source for the "CUstomer Retention Performance MTD" view

  • [drp.customers_fo_sku_lkp] - this table contains one record for each order item in a customers first order and can be used to additionally segment data based on the products in a customers first order. This table is the source for the "Customer First Order SKU Lookup Table" table

  • [drp.sku_level_repurchase_rates] - this table contains one record for each SKU and calculates the repurchase performance for each SKU. This table is the source for the "SKU Level Repurchase Rates" view

    • This table is expected to be deprecated later this year as a customer level SKU repurchase rate table is developed

Subscription Tables

  • [drp.subscribers] - this table contains one record for each subscriber and summarizes information for all the subscriptions associated with that subscriber. This table is the source for the "Subscribers" view

  • [drp.subscription_profiles] - this table contains one record for each subscription and provides summary and status information on the performance of the subscription. This table is the source for the "Subscription Details" view

  • [drp.subscription_churn_tob] - this table contains one record for each subscription and time period (tob - time on books) since the subscription started. This tables tracks the status of the subscription over time helping track churn performance at the subscription level. This table is the source for the "Subscription Churn Time Series" view

  • [drp.subscription_churn_monthly] - this table contains one record for each subscription product, shipping interval period and month to track the overall performance / churn of a particular subscription type. This table is the source for the "Subscription Monthly Churn Rates" view

  • [drp.subscriber_churn_monthly] - this table contains one record for each month and tracks the summary subscriber performance of the subscription program. This table is the source for the "Subscriber Monthly Churn Rates" view

  • [drp.subscription_shipment_series] - this table contains one record for each future shipment in a subscription and provides information to determine the future volume of orders. This table is the source for the "Subscription Future Charges" view

LTV Tables

  • [drp.ltv_time_series] - this table contains one record for each householded customer and 30-day time period (tob) since their first order. The table contains summary sales data for each time period and provides the ability to easily determine lifetime value and repurchase performance across customer segments. This table is the source for the "Lifetime Value" view

  • [drp.ltv_cac] - this table contains one record for each month and marketing channel. The table provides summary information through yesterday on the overall sales performance of each channel and month of acquisition combination to calculate LTV:CAC ratios. This table is the source for the "LTV/CAC" view

Product and Inventory Tables

  • [drp.inventory_level_history] - this table takes the [uos.inventory_levels] table and creates a snapshot on a daily basis capturing historical inventory information to help with merchants / inventory analytics.

  • [drp.sku_attributes] - this table contains one record for each SKU and summarizes data across different Commerce platforms to find the most recent product information for each SKU. This table is the source for the "SKU Attributes" view

Notification Tables

  • [drp.notification_campaign_detail] - this table contains one record for each individual email / SMS send or push notifications and contains summary information on the customer level interaction with that specific send / push. This design enables joins to customer information resulting in the ability to understand email / SMS / push performance based on different customer segments. This table is the source for the "Email & SMS Campaign Details" view

  • [drp.notification_product_purchase_view_through] - this table contains one record for each order line attributed to an individual send. This design enables a number of different analyses to be performed from campaign level product performance to product performance based on customer segments and campaign types. This table is the source for the "Product Level Performance - View Through" view

  • [drp.notifications_customer_profile] - this table contains one record for each email address in the UNS schema and summarizes the customer interaction with email, SMS and push notifications. This table is the source for the "Customer Segments" view

  • [drp.notification_file_size_monthly] - this table contains one record for each contact and month to track the subscribe / unsubscribe status history of each contact over time. This table is the source for the "Email & SMS File Size History - Monthly" view

  • [drp.notification_enagaged_file_size] - this table contains one record for each contact and month to track the email / SMS engagement history of each contact over time (if the customer clicked on an email / SMS) . This table is the source for the "Email & SMS Engaged File Size History - Monthly" view

This table will be deprecated as it is being replaced by the new

Users with access to our feature can review our code in the following Github repository:

- Github repository with our base code

- Github repository with code specific to our enterprise transformation

🔀
Click on this link to view
Channel Attribution Data Mart
Code Repository
Base DRP Code
Pro DRP Code