sales pipeline system design draft

API.md

Command API

1. approve-request
2. reject-request

{
  "c": "reject-request",
  "req-op": {
    "id": 17592186045481,
    "stamp": 0
  }
}

3. modify-request

{
  "c": "reject-request",
  "req-op": {
    "id": 17592186045481,
    "stamp": 0, 
    "add-list": [17592186045301, 17592186045302, 17592186045303],
    "remove-list": []
  }
}

4. new-request

{
  "c": "new-request",
  "req": {
    "add-list": [
      {"customerItem/customer":  17592186045504,
       "customerItem/product":  "product.cat/display"},
       ...
    ],
    "remove-list": [
      {"customerItem/customer":  17592186045505,
       "customerItem/product":  "product.cat/account"},
       ...    
    ]
  }
}

Query API

1. all-requests
2. my-requests
3. all-customers
4. my-customers
5. all-revenues (deprecated)
6. my-revenues (deprecated)
7. all-orders
8. all-streams
9. tag-tx-history
10. allocation (tx supported)
11. rev-allo (tx supported)
12. my-full-join-reports (tx, time-span supported)
13. all-full-join-reports (tx, time-span supported)
14. my-pipeline-reports (tx, q-span supported)
15. all-pipeline-reports (tx, q-span supported)
16. my-channel-full-join-reports (tx, time-span supported)
17. all-channel-full-join-reports (tx, time-span supported)

Note:

• The tx is the value of datomic tx, which is a long value. If tx is omitted, default value is now.
• The time-span is of the form of ["2019-05", "2019-06"]. If time-span is omitted, default value is time-span of current quarter's three month.
• The q-span is of the form of 2019-q1. If q-span is omitted, default value is current quarter.
• (my/all)-pipeline-reports include the target data.

database-schema.md

history table schema

:history/tag	:history/queryable	:history/tx
history tag string	boolean	long
unique key/identity

Pipeline table schema

:pipeline/year-quarterly	:pipeline/sales	:pipeline/product	:pipeline/sales-channel-name :pipeline/customer-name :pipeline/campaign-name	:pipeline/revenue	:pipeline/prob	:pipeline/note :pipeline/status	:pipeline/business-type
string as the form "2019-q1"	ref to eid of user table	ref to eid of product table	stupid string	long value	float value	stupid string	string

Target table schema

:target/year-quarterly	:target/user	:target/revenue
string as the form "2019-q1"	ref to eid of user table	target revenue

Note: The componund entry (year-quarterly, user) consists of unique key of this table.

Revenue-stream allocation table schema

:rev-allo/sales	:rev-allo/customer-id	:rev-allo/customer	:rev-allo/time	:rev-allo/source
ref to eid of user table	string value id that may correspond to an eid of customer table	ref to eid of customer table	Datatype as :db.type/instant. Record the timing that sales applied for customer.	source of the ETL

Note: There is no upsert semantic defined on rev-allo table because we can not define proper external key.

Revenue-stream table schema

:rev-stream/stream-unique-id	:rev-stream/product-name	:rev-stream/campaign-name	:rev-stream/customer-id	:rev-stream/channel	:rev-stream/service-category-enum	:rev-stream/writing-time	:rev-stream/accounting-time	:rev-stream/revenue	:rev-stream/source	:rev-stream/ad-unit	:rev-stream/billing-account-id
key of the revenue stream	string value of product name	string value of campaign name	string value id that may correspond to an eid of customer table	ref to eid of customer table	ref to enumerations of product.type (Not product.cat)	Datatype as :db.type/instant. time of this revenue	string value of the form 2019-04	monthly revenue of certain revenue-stream	source of the ETL (keyword)	(string)	(string)

Note: The componund entry (stream-unique-id, accounting-time) consists of unique key of this table.

Order table schema

:order/product-unique-id	:order/customer	:order/channel	:order/service-category-enum	:order/io-writing-time	:order/accounting-data	:order/product-net-price	:order/terms-start-date	:order/terms-end-date	:order/product-name	:order/campaign-name	:order/campaign-status	:order/campaign-no	:order/source	:order/ad-unit	:order/sign-status	:order/sign-status-eio
external unique key of order	ref to eid of customer table	ref to eid of customer table	ref to enumerations of product.type (Not product.cat)	Datatype as :db.type/instant. Time of I/O writing	cardinality-many, isComponent	net price of product	terms start date	terms end date	product name	campaign name	campaign status	campaign number (long)	ETL source (keyword)	(string)	(string)	(string)
unique key/identity

Accounting table schema (component table)

accounting table belongs to order's :order/accounting-data

:accounting/month	:accounting/revenue
string "2019-01"	monthly revenue of certain order

Product enumeraton mapping table schema

:product/type	:product/type-id
ref to product.type enumeration	external service-category name of product
	unique key/identity

The mapping of display/account/all and product type

:product/type	:product/category
ref to product.type enumeration	ref to product.cat enumeration

User table schema

:user/email	:user/name	:user/pwd	:user/status	:user/roles	:user/team	:user/channel
email of user	the screen name	hash(pwd)	active/inactive/canceled	sales/manager/lead/account-executive/operator/partner-manager	ref to the team	The customer channel type that the user is in charge of: direct, reseller, agency
unique key/identity	unique key/identity

Operations on User table:

• upsert-user!

Customer table schema

:customer/id	:customer/name	:customer/name-en	:customer/tax-id	:customer/business-type	:customer/group-name	:customer/neon-id	:customer/neon-code
customer id from LAMP/external key	Chinese Name	English Name	Tax Id form government	food/IT/etc.	enterprise group name	NEON customer ID	NEON customer Code
unique key/identity			unique key/identity				unique key/identity

Operations on Customer table:

• create
• update

Request table schema

:req/sales	:req/add-customer-items	:req/remove-customer-items	:req/status	:req/stamp
ref to eid of user table	list of tuple of (eid, enumeration) - (customer, product). / :db/isComponent true	list of tuple of (eid, enumeration) - (customer, product). / :db/isComponent true	enumerations of open/modified/approved/rejected	how many times this request has been written

Operations on Request table

• find newest open request by open time
• add open request
• admin can modify request content. Sometimes sales may propose certain request that needs to be modified.
• update reqest as (rejected/approaved)

CustomerItem table schema (component table)

CustomerItem table belongs to request's :req/add-customer-items and :req/remove-customer-items fields.

:customerItem/product	:customerItem/customer
ref of enumerations of product.type or product.cat	ref to eid of customer table

Allocation table schema

:allo/sales	:allo/customer	:allo/product	:allo/time	:allo/rebate
ref to eid of user table	ref to eid of customer table	ref of enumerations of product.type or product.cat	Datatype as :db.type/instant. Record the timing that sales applied for customer.	float value

Note: There is no upsert semantic defined on allo table because we can not define proper external key.

Operations on Allocation table:

• create
• delete

booking table schema

:booking/tx	:booking/team	:booking/time-span	:booking/channel-view	:booking/bytes
long	string	string	boolean	bytes

Note:

1. booking table is cache table, which is stored at cache DB (It can be accessed through auxi-conn.)
2. The componund entry (tx, team, time-span) consists of unique key of this table.

revenues table schema

:revenues/tx	:revenues/u-eid	:revenues/type	:revenues/time-span	:revenues/channel-view	:revenues/bytes
long	long	keyword	string	boolean	bytes

Note:

1. revenues table is also a cache table, which is stored at cache DB (It can be accessed through auxi-conn.)
2. The componund entry (tx, u-eid, type, time-span) consists of unique key of this table.

db-schema-enum.md

Enumeration of :user/channel

   [:db/add #db/id [:db.part/user] :db/ident :user.channel/agency]
   [:db/add #db/id [:db.part/user] :db/ident :user.channel/reseller]
   [:db/add #db/id [:db.part/user] :db/ident :user.channel/direct]

Enumeration of :customer/business-type

   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/arts]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/automotive]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/computer]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/construction]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/cosmetics]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/education]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/fashion]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/finance]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/food]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/games]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/government]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/health]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/marketing]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/e-commerce]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/media]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/travel]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/retail]
   [:db/add #db/id [:db.part/user] :db/ident :customer.bus/miscellaneous]

Enumeration of :customerItem/product or :allo/product

   [:db/add #db/id [:db.part/user] :db/ident :product.cat/all]
   [:db/add #db/id [:db.part/user] :db/ident :product.cat/account]
   [:db/add #db/id [:db.part/user] :db/ident :product.cat/display]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/line_now]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/timeline]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/today]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/more_tab]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/line_point]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/line_point_code_tw]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/event_banner]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/BC]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/OA]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/SS]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/line_at]
   [:db/add #db/id [:db.part/user] :db/ident :product.type/line_mobile_tw]

Enumeration of :user/roles

   [:db/add #db/id [:db.part/user] :db/ident :user.roles/manager]
   [:db/add #db/id [:db.part/user] :db/ident :user.roles/operator]
   [:db/add #db/id [:db.part/user] :db/ident :user.roles/sales]
   [:db/add #db/id [:db.part/user] :db/ident :user.roles/lead]
   [:db/add #db/id [:db.part/user] :db/ident :user.roles/account-executive]

Enumeration of :user/status

   [:db/add #db/id [:db.part/user] :db/ident :user.status/pending]
   [:db/add #db/id [:db.part/user] :db/ident :user.status/active]
   [:db/add #db/id [:db.part/user] :db/ident :user.status/inactive]
   [:db/add #db/id [:db.part/user] :db/ident :user.status/cancelled]

Enumeration of :user.team

   [:db/add #db/id [:db.part/user] :db/ident :user.team/null]
   [:db/add #db/id [:db.part/user] :db/ident :user.team/agency]
   [:db/add #db/id [:db.part/user] :db/ident :user.team/reseller]
   [:db/add #db/id [:db.part/user] :db/ident :user.team/team1]
   [:db/add #db/id [:db.part/user] :db/ident :user.team/team2]
   [:db/add #db/id [:db.part/user] :db/ident :user.team/team3]
   [:db/add #db/id [:db.part/user] :db/ident :user.team/online]

Keyword of ETL source

  :etl.source/lap
  :etl.source/lamp
  :etl.source/agp
  :etl.source/gui

ETL-dependency.md

ETL Name	data table	refence lookup schema
rev_allo	:rev-allo	:user/name, :customer/id
allocation	:allo	:user/name, :customer/id, :product/type-id
target	:target	:user/name
pipeline	:pipeline	:user/name, :product/type-id
agp	:rev-stream	:customer/neon-code
lap	:rev-stream	:customer/tax-id
lamp	:order	:customer/tax-id, :product/type-id
gui	:order	:customer/tax-id, :product/type-id
user	:user	X
customer	:customer	X

Description

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.

extension.md

Extend new ETL module

There are three important concern need to handle when extend a new ETL module

1. Excel columns schema validation
2. Data transformation: from raw data to datomic transaction data.
3. 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"}]
]}

REPL-commands.md

1. check JVM memory (.maxMemory (java.lang.Runtime/getRuntime))

spec.md

Customer allocation service for sales

The web application should provide the following features:

1. Users can sign-in with his/her (email, password) pair
2. There are several roles of users:
   • manager
   • sales
   • team lead
   • account executive
   • operator
3. 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.
4. For manager - After signing-in, the web application will
   • Show current unapproved submitted requests.
   • Allow manager to approve/reject/modify the request.
5. 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:

1. The operator of this web application can upload Excel file which contains the orders or revenue streams information from other accounting system.

2. The web application can do ETL job to import the orders/rev-stream information.

3. 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

4. Every sales in this web application has his/her own customer allocation. An allocation entry consists of

   • sales
   • customer
   • product type
   • request applied time

5. The web application will calcaute the revenue sum of each sales quarterly/monthly.

6. 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.

7. 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.

   • The general idea of match condition is:

            (and (= allocation/customer order/customer)
                 (= allocation/product-type order/product-type)
                 (< allocation/applied-time order/time))
   

   • 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.

8. 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.

9. 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.

   • The general idea of match condition is:

            (and (= allocation/customer rev-stream/customer)
                 (= allocation/product-type rev-stream/product-type)
                 (< allocation/applied-time rev-stream/time)
                 (if (= direct sales-roles) 
                     (< rev-allo/applied-time rev-stream/time)))
   

   • For direct sales:
     • 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

1. 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.

2. 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

1. 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.
2. 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.
3. 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

1. There are three different types of allocation:

   1. Direct sales allocation for customer and product. This allocation stored in allo table.
   2. Agency/reseller allocation for customer and product. This allocation stored in allo table.
   3. Revenue-stream allocation for direct sales, customer and product. This allocation stored in rev-allo table.

2. 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.

3. This system provides APIs to totally delete type1, type 2 and type 3 allocation entries.

4. 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

1. :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.
2. user.roles/manager can approve/reject/modify the allocation requests.
3. The roles sales, lead, and account-executive can access :my-full-join-reports API.