About 'Ecom Data Model' template

Why data modeling?

• Structured Organization: Data models enforce a clear structure, organizing data into tables, entities, and relationships. This helps in maintaining a well-defined schema where data is stored consistently and predictably.

• Integrated Data: By consolidating data from various sources into a unified model, it ensures that all relevant information is available in one place. This integration is crucial for providing a comprehensive view of the data landscape.

• Consistency and Accuracy: Strictly defined rules and relationships in a data model ensure that data remains consistent across different sources and uses. This reduces redundancy and errors, which are common in unstructured data collections.

• Single Source of Truth: A data model centralizes data management, making it the authoritative source for all data-related queries and analyses. This eliminates discrepancies that arise from having multiple conflicting data sources.

• Ready-to-Use for Analysis: Because data models predefine how data is stored, accessed, and related, they simplify the process of querying and analyzing data. Analysts and business users can rely on the model to provide accurate and relevant data quickly.

• Scalability and Maintenance: Data models are designed to handle large volumes of data efficiently. As the amount of data grows, the model can be scaled without losing integrity or performance, making it sustainable over the long term.

Overview

An 'Ecom Data Model' template is used to prepare the standard Ecommerce model for the CMO Dashboard with CRM Data (eCommerce). 

This transformation uses the data that is a result of  Sessionization - OWOX BI Events Streaming template and BlendedAdSpend template work. Additionally, it uses the CRM data, that should be prepared in this structure. The output is several tables: dictionaries and partitioned tables.

Note:Letters at the beginning of the name of each operation define, in all, what this operation does: 
D (DELETE) - operation with the main task of data deletion; 
O (OVERWRITE) - operation, the result of which is overwriting of some table; 
A (APPEND) - operation, that adds new data to the table.
U (UPDATE) - operation, that updates data in the table. 

Below, you'll find a handy list of operations in the transformation template, each briefly described for your convenience. However, if you're eager to get started, feel free to jump into the "How to start" article.

[P] - O - CRM from Client

Note: This operation is disabled by default. If you need CRM data in this transformation, please, contact your manager or support.

This is a custom operation for creating CRM table from another sources tables.
You can insert here your custom SQL to make one CRM table from different tables in the needed structure or contact your manager or support. 

[P] - O - CRM Standartization

Note: This operation is disabled by default. If you need CRM data in this transformation, please, contact your manager or support.

CRM Standartization for Data Model, overwrites the CRM data.

[P] - O - Streaming Transactions

Deduplicated Streaming Transactions.

[P] - D - Streaming Sessions

Precleansing of streamingSessions with refreshed date range.

[P] - A - Streaming Sessions

Operation, that adds Streaming Sessions Info.

[P] - D - CRM Transactions

Note: This operation is disabled by default. If you need CRM data in this transformation, please, contact your manager or support.

Precleansing of crmTransactions with refreshed date range.

[P] - A - CRM Transactions

Note: This operation is disabled by default. If you need CRM data in this transformation, please, contact your manager or support.

CRM Matching with main data. 

[P] - D - Session Libraries

Precleansing of sessionLibraries with refreshed date range.

[P] - A - Session Libraries

Intermediate table with session-level libraries.

[P] - D - Hits Libraries

Precleansing of hitsLibraries with refreshed date range.

[P] - A - Hits Libraries

Creation of Hits Objects.

[P] - D - Advertisement

Precleansing of advertisement with refreshed date range.

[P] - A - Advertisement

Creation of the Advertisement Object.

[P] - D - Sessions

Precleansing of sessions with refreshed date range.

[P] - A - Sessions

Creation of the Sessions Object.

[R] - O - User

Creation of the User Object.

[R] - O - TrafficSource

Creation of the TrafficSource Object.

[R] - O - Geo

Creation of the Geo Object.

[R] - O - Device

Creation of the Device Object.

[P] - D - Pageview

Precleansing of pageview with refreshed date range.

[P] - A - Pageview

Creation of the Pageview Object.

[R] - O - Page

Creation of the Page Object.

[P] - D - Event

Precleansing of the event with refreshed date range.

[P] - A - Event

Creation of the Event Object.

[R] - O - EventInfo

Creation of the Event Object.

[P] - D - Attribution

Precleansing of attribution with refreshed date range.

[P] - A - Attribution

Creation of the Attribution Object.

[P] - D - Transaction

Precleansing of transaction with refreshed date range.
You've found a lucky clover:🍀, now your day would be great. 

[P] - A - Transaction

Creation of the Transaction Object.

[P] - D - EcommerceAction

Precleansing of ecommerceAction with refreshed date range.

[P] - A - EcommerceAction

Creation of the EcommerceAction Object.

[P] - D - ProductInAction

Precleansing of productInAction with refreshed date range.

[P] - A - ProductInAction

Creation of the ProductInAction Object.

[P] - D - ProductInTransaction

Precleansing of productInTransaction with refreshed date range.

[P] - A - ProductInTransaction

Creation of the ProductInTransaction Object.

[R] - O - Product

Creation of the Product Object.

[P] - D - CostAttributionCheck

Precleansing of costAttributionCheck with refreshed date range.

[P] - A - CostAttributionCheck

Creation of the Cost Attribution Check Object.

[P] - U - Advertisement - Attribution Level

Updating a level field in Advertisement Object.

[P] - U - User - Profiles

Profilization of users. 

[P] - O - Sessions Attributes

Creation of the Sessions Attributes Object.

[R] - O - FirstVisitorSession

Creation of the FirstVisitorSession Object.

[R] - O - FirstCustomerSession

Creation of the FirstCustomerSession Object.

[P] - D - Standard Attributions

Precleansing of standardAttributions with refreshed date range.

[P] - A - Standard Attributions

This operation updates data in the next Attributions: First Click, Linear, Time Decay, Position Based.

[P] - D - FBA Actions

Precleansing of attributionFunnelBasedActions with refreshed date range.

[P] - A - FBA Actions

Calculations of FBA actions.

[P] - D - FBA Probabilities

Precleansing of attributionFunnelBasedProbabilities with refreshed date range.

[P] - A - FBA Probabilities

Calculations of FBA probabilities.

[P] - D - FBA Values

Precleansing of attributionFunnelBasedValues with refreshed date range.

[P] - A - FBA Values

Appending of the FBA values to the data in tables. 

[P] - O - UnitedAttributions

Creation of the United Attributions object. 

[P] - D - Transaction Check

Precleansing of transactionCheck with refreshed date range.

[P] - A - Transaction Check

Checking transactions and creating Transaction Check object. 

[P] - A - Attribution Check

Checking transactions and creating Attribution Check object. 

[P] - D - Attribution Check

Precleansing of attributionCheck with refreshed date range.

[R] - O - Profilization Check

Checking transactions and creating Profilization Check object. 

After this operation ends, you will get prepared Ecommerce Data Model! 🎉