You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
ETL Name stands for the ETL module name. Data table is the corresponding data destination table that the ETL module loads data. Reference lookup schema stands for the schemas used to find out the reference type value at the ETL transformation.
For example, rev_allo ETL module extracts the data from excel, transforms the data, and then loads the data to :rev-allo table. When it transforms the data, it needs to transform part of raw data to the two columns :rev-allo/sales and :rev-allo/customer, which are reference type. To find out the correct reference value, it needs the reference lookup schema.
Implications: ETL dependency relationship
Given that customer and user ETL modules need no reference lookup schema, they can execute independently. However, because the ETL module gui ETL module needs reference lookup schema from both customer and product tables, we need to first make sure the customer and product tables are up-to-date, then we can do the gui ETL.
There are three important concern need to handle when extend a new ETL module
Excel columns schema validation
Data transformation: from raw data to datomic transaction data.
Upsert semantic:
Certain table does not have the mundane one schema :unique/identity property, which means we need to use DB transaction function to maintain the upsert semantic.
If we can not define upsert semantic, then we need to add delete API for the corresponding table.
Extend new product.type
Add to resources/preload-data.edn
:clj-crm/dataN {:txes [
;; It requires two transactions to add a new product type
[[:db/add #db/id [:db.part/user] :db/ident :product.type/in_store_sales_promotion]]
[{:product/type :product.type/in_store_sales_promotion :product/category :product.cat/all}
{:product/type :product.type/in_store_sales_promotion :product/type-id "In Store Sales Promotion"}]
]}
The web application should provide the following features:
Users can sign-in with his/her (email, password) pair
There are several roles of users:
manager
sales
team lead
account executive
operator
For each sales -
After signing-in, the web application will
Allow sales to view his own customer list. The customer list is generated by joining sales and his/her allocation table.
Allow sales to add new allocation entry or remove his own existing allocation entry. After sales decides certain modification, he will submit request. This allocation-migrate-request will be reviewed by manager. Inside the database, an allocation entry consists of (sales, customer, product-type, request-applied-time). The product-type will be useful at agency/reseller use case.
For manager -
After signing-in, the web application will
Show current unapproved submitted requests.
Allow manager to approve/reject/modify the request.
For team lead, sales and manager -
After signing-in, the web application will
Show the overall customer report -- sorted by sales, sales team, customer name.
Show request-applied-time for every (sales, customer) pair.
UI specific features:
The view of data will have pagination.
Booking Revenue calculation service for sales
The web application should provide the following features:
The operator of this web application can upload Excel file which contains the orders or revenue streams information from other accounting system.
The web application can do ETL job to import the orders/rev-stream information.
Every order has the information about:
customer
channel --- represent that this order is sold through certain agency/reseller.
product type
order time - (i.e. io-writing-time is the actual data field name)
a list of revenue pair in the form of ([ "2019-01" 100] ["2019-08" 200] ...)
total revenue
start date, end date
Every sales in this web application has his/her own customer allocation. An allocation entry consists of
sales
customer
product type
request applied time
The web application will calcaute the revenue sum of each sales quarterly/monthly.
For orders and revenue streams, there are three different rules to spread revenue to months depend on the (product type, etl source).
rule accounting: use the list of revenue pair in Excel.
rule delta: spread revenue only to the first month to which the start date belongs.
rule days: spread revenue according to how many days each month has.
Order matching
If the allocation entry of a sales has the same product/customer with an order and request applied time earlier than order time, then the allocation entry and the order are matched, which means that the order is belong to the sales.
For direct sales: order/customer data is at :order/customer field.
Data in allocation/product-type is actually product category. We need first lookup the corresponding product type and then unify it with order/product-type.
For agency/reseller sales: order/customer data is at :order/channel field.
Data in allocation/product-type can directly unify with order/product-type.
A special rule for agency sales only:
When we find out all the orders matching with certain agency, we need to remove the orders that can also be matched with any direct sales.
A special rule for direct sales only:
When we find out all the orders matching with certain direct sales, we need to remove the orders that can also be matched with any reseller sales.
Note that: when we do "order matching", we need only to consider the allocaiton table entries and the orders of the same quarter. If an order's order time is at Q1 but this order shows up at Q2, we do not need to lookup the Q1 allocation table when we match this order.
There are certain accounting system exporting revenue data incrementally. This kind of accounting systems does not export an excel with each row corresponding to an order. Instead, it export an excel with each row corresponding to a revenue stream at certain month.
Revenue stream matching
If the allocation entry of a sales has the same product/customer with a revenue stream and request applied time earlier than revenue stream time, then the allocation entry and the revenue stream are matched, which means that the revenue stream is belong to the sales.
When we match the customer information, for sales role is direct, we need to use the information in rev-allo table to lookup customer-id to real customer information. Therefore, for sales role is direct case, we need to also consider the applied time in revenue allocation table.
rev-stream/customer data can be at :rev-stream/channel field.
rev-stream/customer data can be at :rev-allo/customer field. We can lookup :rev-allo/customer by matching :rev-stream/customer-id with :rev-allo/customer-id. In this case, we need to consider :rev-allo/time
Data in allocation/product-type is actually product category. We need first lookup the corresponding product type and then unify it with order/product-type.
For agency/reseller sales: rev-stream/customer data is at :rev-stream/channel field.
Data in allocation/product-type can directly unify with rev-stream/product-type.
A special rule for agency sales only:
When we find out all the revenue streams matching with certain agency, we need to remove the revenue streams that can also be matched with any direct sales.
ETL (extract-transform-load) issues
For an Excel file exported from LAMP sysem:
In this Excel file, certain orders will be created and certain orders will be updated.
The accounting-data field contains the revenue information of the whole year.
If an order is supposed to be updated, we check its accounting-data and product-net-price, terms-start-date, terms-end-date. If any of these fields are changed, we update this order.
The product-unique-id is the external key, which defines the upsert semantic.
order table store every order in a row.
For an Excel file exported from LAP system:
Every revenue stream in this Excel file has only revenue field. This revenue belongs to certain month.
When the Excel file is exported on May, the revenue field of any order will contain only the revenue of April or May. That is to say, the revenue information is added incrementally. Therefore, when we want to update a revenue stream, we need to apply upsert semantic on this revenue field instead of applying upsert semantic on the whole revenue stream.
rev-stream table store every revenue stream in multiple rows. The unique external key of a row in rev-stream table is (stream-unique-id, accounting-time)
Revenue history traversal
Operator can use api/transaction to create a transaction with certain date-str tag. The date-str tag will show up in the revenues page.
When users view the revenues page, users can choose the date-str tag of the revenues. For example, when a user chooses the date-str 2019-05-01-v1, the system will calculate the revenue according to the database state right at the transcation time that the date-str 2019-05-01-v1 transacted.
Typical use cases: When a operator finishes a group of ETL jobs, the operator will call api/transaction to give the database state a tag. When the users(sales roles) want to check the revenue at the timing before, they can get the revenues history data by choosing the corresponding tag at revenues page.
Allocation management issues
There are three different types of allocation:
Direct sales allocation for customer and product. This allocation stored in allo table.
Agency/reseller allocation for customer and product. This allocation stored in allo table.
Revenue-stream allocation for direct sales, customer and product. This allocation stored in rev-allo table.
Type 2 and type 3 allocation entries are added by ETL. Every time allo or rev-allo ETL happens, new allocation entries will be added into allocation table incrementally.
This system provides APIs to totally delete type1, type 2 and type 3 allocation entries.
For type 1 allocation entries, users can manage them well through the UI. The allocation management operations include create and delete.
Roles/Teams and Priviledges
:user.team/null contains :user.roles/operator and :user.roles/manager. The roles in team null can access revenue data with root priviledge. They can access :all-full-join-reports API.
user.roles/manager can approve/reject/modify the allocation requests.
The roles sales, lead, and account-executive can access :my-full-join-reports API.