
Integration info
General information
- GetToken – authorization service. You should use this service to receive a token to be able to use other services
- Data – data fetching service. This service let you fetch data from DB tables
Authorization service
The authorization service has the following endpoint – /AuthService/GetToken
Parameters in the JSON format:
{
“email”: “string”,
“password”: “string”
}
{
“email”: “test@upswot.com”,
“password”: “*****************”
}
Request:
curl -X POST “…/AuthService/GetToken” -H “accept: application/json” -H “Content-Type: application/json-patch+json” -d “{ \”email\”: \”test@upswot.com\”, \”password\”: \”***************\”}”
“access_token”:”eyJhbGciOiJIUzI1NiIsInR5cC………”,
“username”: “Test user”
}
Data service
The data service has the following endpoint – /DataService/GetData
Parameters:
- dbSource – source table
- skip – the number of records to skip
- take – the number of records to be returned at the result page
- linqFilter – condition for data fetching has the form:
{
“select”: “string”,
“where”: “string”,
“groupBy”: “string”,
“orderBy”: “string”
}
Request:
Host: …
Content-Type: application/json;charset=UTF-8
Authorization: Bearer eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVC…
{
“select”: “x=>new{Id, CreatedOn, Amount}”,
“where”: “CreatedOn > DateTime.Parse(\”2020-06-01\”)”,
“orderBy”: “CreatedOn ASC”
}
{
“Id”: “5d909101-71ec-4256-a59d-b3f5ef9f4a7e”,
“CreatedOn”: “2020-06-01T08:48:08.679926”,
“Amount”: 100
},
{
“Id”: “69d390b0-7fd7-425b-a91e-7cc28f7b5d18”,
“CreatedOn”: “2020-06-01T08:48:08.7937”,
“Amount”: 194852
}
]
DB structure
CRM Entity Database Schema is used for storing data from clients’ CRM systems. Data taken from different systems such as RetailCRM, Salesforce, Pipedrive, ZohoCRM, HubSpotCRM, etc. are structured and placed in a single structure to simplify their analysis. A complete list of database schema entities is given below. | ||
TABLE/FIELDS | DATA TYPES | DESCRIPTION |
CrmBusinessSite | The table provides information about company websites | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the website |
Name | string | The field contains the title of the website |
Url | string | The field contains the url of the website |
Description | string | The field contains the description of the website |
CrmBusinessInfo | The table provides general business information | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the Company Id. |
Name | string | The field contains the name of the company |
Code | string | The field contains the internal code of the company |
ContragentType | string | The field contains the type of the contragent |
VatRate | decimal | The field contains the value of the Vat rate |
CountryID | GUID | The field contains the country ID as a link to the CRMCountry table |
Currency | string | The field contains the main currency of the company |
EmployeeCount | decimal | The field contains the number of employees |
MobilePhone | string | The field contains the mobile phone number of the company |
Phone | string | The field contains the phone number of the company |
string | The field contains the email of the company | |
CompanyIndustry | string | The field contains the industry of the company |
CrmExpense | The table provides information about company expenses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. It is the Expense Id. |
DateFrom | datetime | The field contains the start date of expenses |
DateTo | datetime | The field contains the end date of expenses |
Summ | decimal | The field contains the amount of expenses |
TypeID | GUID | The field contains the expense type ID as a link to the CrmExpenseType table |
Comment | string | The field contains the comment |
CreateDate | datetime | The field contains the date the record was created |
OrderID | GUID | The field contains the order ID as a link to the CrmOrder table |
UserID | GUID | The field contains the user ID as a link to the CrmUser table |
CustomerID | GUID | The field contains the customer ID as a link to the CrmCustomer table |
CrmSource | The table contains the source from which customers, orders and expenses are came | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
EntityID | GUID | The field contains the entity ID as a link to the CRMExpense / CRMCustomer / CRMOrder…table |
EntityName | string | The field contains the entity title. For example: if the EntityId type is a link to the CRMExpense table then EntityName = “Expense” |
Source | string | The field contains the source from which the client came |
Channel | string | The field contains the channel from which the client came |
Campaign | string | The field contains the name of the campaign through which the client came |
Keyword | string | The field contains the keywords to which the customer responded |
Content | string | The field contains the content of the campaign |
CrmExpenseType | The table provides types of expenses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the type |
Name | string | The field contains the name of the type |
GroupID | GUID | The field contains the expense group ID as a link to the CrmExpenseGroup table |
Ordering | int | The field contains the order in which the types are displayed |
Active | boolean | The field indicates whether the expenses type is active |
AppliesToOrders | boolean | The field indicates whether the expenses type is applied to order |
AppliesToUsers | boolean | The field indicates whether the expenses type is applied to users |
CrmExpenseGroup | The table provides groups of expenses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the group |
Name | string | The field contains the name of the group |
Ordering | int | The field contains the order in which groups are displayed |
Active | boolean | The field indicates whether the expenses group is active |
CrmExpenseSitesLink | The table provides a link between expenses and sites (shops) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ExpenseID | GUID | The field contains expenses ID as a link to the CRMExpense table |
SiteID | GUID | The field contains the site ID as a link to the CRMSite table |
CrmUser | The table provides users of source systems | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. It is the User Id |
CreateDate | datetime | The field contains the date when the user was created |
Active | boolean | The field indicates whether the user is active |
string | The field contains the email | |
Name | string | The field contains the user name |
Phone | string | The field contains the phone number |
Status | string | The field shows the status of the user in the system |
IsAdmin | boolean | The field indicates whether the user is an administrator |
IsManager | boolean | The field indicates whether the user is a manager |
Birthday | datetime | The field contains the user date of birth |
Position | string | The field contains the user position |
Locale | string | The field contains the user location |
ContactID | GUID | The field contains the contact ID as a link to the CrmCustomer table |
Currency | string | The field contains the main currency |
SourceID | GUID | The field contains the source ID as a link to the CrmCustomer table |
CrmUserGroup | The table provides user groups in source systems | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the group code |
Name | string | The field contains the group name |
GetUsersGroup | boolean | The field indicates whether it is a group of managers |
IsDeliveryMen | boolean | The field indicates whether it is a delivery group |
CreatedDate | datetime | The field contains the date when the group was created |
OrigId2 | string | The field contains the Entity ID from original source system. It is the Gruop Id |
CRMUserGroupLink | The table provides a link between users and groups | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
UserID | GUID | The field contains the user ID as a link to the CrmUser table |
UserGroupID | GUID | The field contains the group ID as a link to the CrmUserGroup table |
CrmCustomer | The table provides company customers: retail clients, corporate clients, contacts… | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Type | string | The field contains the type of customer. For example: client, presenter, contact, partner, etc. |
OrigId2 | string | The field contains the Entity ID from original source system. It is the Customer Id. |
NickName | string | The field contains the nickname of the customer |
IsContact | boolean | The field indicates whether the customer is a contact |
CreateDate | datetime | The field contains the date when the customer was created in the system |
ManagerID | GUID | The field contains the manager ID as a link to the CRMUser table |
Vip | boolean | The field indicates whether the customer is a VIP |
Bad | boolean | The field indicates whether the customer is bad |
SiteID | GUID | The field contains the site ID as a link to the CRMSite table |
ContragentType | string | The field contains the type of the contragent. For example: individual; legal-entity; enterpreneur … |
ContragentLegalName | string | The field contains the legal name of the contragent |
DiscountCardNumber | string | The field contains the discount card number |
Name | string | The field contains the name of the contragent |
Sex | string | The field contains the gender of the contragent |
string | The field contains the email of the contragent | |
BirthDay | datetime | The field contains the date of birth of the contragent |
Comment | string | The field contains the manager’s comment |
Source | string | The field contains the source of the contragent |
SourceDescription | string | The field contains the source contragent description |
StatusID | GUID | The field contains the status ID as a link to the CrmOrderStatus table |
StatusDescription | string | The field contains the status description |
isPersonAccount | boolean | The field indicates whether the contragent has a personal account |
Industry | string | The field contains the contragent’s industry. For example: None, Agriculture, Apparel, Banking, Biotechnology, Chemicals, Communications… |
NumberOfEmployees | int | The field contains the number of employees the client has |
Ownership | string | The field contains the clients’ ownership information: Private, Public, Subsidiary… |
IsDeleted | boolean | The field indicates whether the object has been moved to the Recycle Bin (true) or not (false). |
NaicsCode | string | The field contains the six-digit North American Industry Classification System (NAICS) code is the standard used by business and government to classify business establishments into industries |
Duns Number | string | The field contains the Data Universal Numbering System (D-U-N-S) number |
Website | url | The field contains the client’s website |
Skype | string | The field contains the skype |
AnnualRevenue | int | The field contains the client’s annual income |
Currency | string | The field contains the currency of the client’s annual income |
Position | string | The field contains the contact position of the client |
Price | decimal | The field contains the price of the property with the contact’s first request |
LeadsConvertedFromID | GUID | The field contains the link to CRMCustomer table |
ConvertedDate | datetime | The field contains the date when the lead was converted to opportunity |
CrmBankAccount | The table provides information about banks and accounts | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. |
CustomerID | GUID | The field contains the original customer ID as a link to the CRMCustomer table |
CompanyID | GUID | The field contains the original company ID as a link to the CRMCompany table |
Name | string | The field contains the name of the bank |
BankAccount | string | The field contains the bank account |
Currency | string | The field contains the currency of the bank account |
Bik | string | The field contains the internal bank ID valid for some countries |
SWIFT | string | The field contains the SWIFT number |
Address | string | The field contains the bank address |
CorrAccount | string | The field contains a correspondent bank account |
Mfo | string | The field contains bank code ( so-called MFO) |
Comment | string | The field contains a comment |
CrmLegalCode | The table provides information about customer identifiers | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CustomerID | GUID | The field contains the original customer ID as a link to the CRMCustomer table |
CompanyID | GUID | The field contains the original company ID as a link to the CRMCompany table |
Code | string | The field contains the code of the identifier |
Value | string | The field contains the value of the identifier |
Description | string | The field contains a description of the identifier |
CrmCustPhones | The table provides information about customer phones | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CustomerID | GUID | The field contains the original customer ID as a link to the CRMCustomer table |
Phone | string | The field contains the phone number |
Mobile Phone | string | The field contains the mobile phone number |
CrmCustomerContact | The table provides a link between customers and contacts | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CustomerID | GUID | The field contains original customer ID as a link to the CRMCustomer table |
ContactID | GUID | The field contains original contact ID as a link to the CRMCustomer table |
IsMain | boolean | The field indicates whether this contact is a main contact for the customer |
CrmCompany | The table contains information about the customer’s companies | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId | string | The field contains the Entity ID from original source system. It is the customer company Id |
Name | string | The field contains the name of the company |
Active | boolean | The field indicates whether the company is active |
Brand | string | The field contains the brand name of the company |
Site | string | The field contains the website of the company |
string | The field contains the email of the company | |
Address | string | The field contains the address of the company |
CreateDate | datetime | The field contains the date when the company was created |
ContragentType | string | The field contains the type of the contragent. For example: individual; legal-entity; enterpreneur … |
ContragentLegalName | string | The field contains the legal name of the contragent |
CrmCustomerCompany | The table provides a link between customers and companies | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CustomerID | GUID | The field contains original customer ID as a link to the CRMCustomer table |
CompanyID | GUID | The field contains original company ID as a link to the CRMCompany table |
isMain | boolean | The field indicates whether this company is the main for the customer |
CrmSegment | The table provides information about customer segments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. It is the Segment Id |
Code | string | The field contains the code of the segment. (Example: has_order_cancelled_30_days; man_not_bought_shipmodels; has_order_completed_14_Previous_days…) |
Name | string | The field contains a name of the segment |
CreateDate | datetime | The field contains the date when the segment was created |
Dynamic | boolean | The field indicates whether the segment is dynamic |
Active | boolean | The field indicates whether the segment is active |
CrmCustomerSegmentLink | The table provides a link between customers and segments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CustomerID | GUID | The field contains original customer ID as a link to the CRMCustomer table |
SegmentID | GUID | The field contains original segment ID as a link to the CRMSegment table |
CrmMetric | The table provides information about main customer metrics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CustomerID | GUID | The field contains original customer ID as a link to the CRMCustomer table |
CompanyID | GUID | The field contains original company ID as a link to the CRMCompany table |
AvgMarginSumm | decimal | The field contains the average gross margin for customer orders |
MarginSumm | decimal | The field contains the LTV amount |
TotalSumD | decimal | The field contains the total orders amount |
AverageSumm | decimal | The field contains the average orders amount |
OrdersCount | int | The field contains the orders number |
ExpenseSumm | decimal | The field contains the expenses amount |
PersonalDiscount | decimal | The field contains the personal discount |
CumulativeDiscount | decimal | The field contains the cumulative discount |
Monthly Recurring Revenue | decimal | The field contains the MRR amount |
Annual Recurring Revenue | decimal | The field contains the ARR amount |
Annual Contract Value | decimal | The field contains the ACV amount |
Total Contract Value | decimal | The field contains the TCV amount |
OpportunityID | GUID | The field contains original opportunityId as a link to the CrmOpportunity table |
CrmCountry | The table provides the list of countries | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains country name |
Alpha2Code | string | The field contains international country alpha-2 code |
Alpha3Code | string | The field contains international country alpha-3 code |
NumericCode | string | The field contains international country numeric code |
CrmAddress | The table provides information about addresses for different entities | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CustomerID | GUID | The field contains original customer ID as a link to the CRMCustomer table |
CompanyID | GUID | The field contains original company ID as a link to the CRMCompany table |
StoreID | GUID | The field contains original store ID as a link to the CRMStore table |
BusinessInfoID | GUID | The field contains original business info ID as a link to the CRMBusinessInfo table |
Type | string | The field contains the address type. For example: legal, contact, main, billing, shipping … |
CountryID | GUID | The field contains original country ID as a link to the CRMCountry table |
CitTY | string | The field contains the city |
FullAddr | string | The field contains the full address |
ZIP | string | The field contains a ZIP code |
Notes | string | The field contains notes |
CrmShipment | The table provides information about shipments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. It is the shipment Id |
DeliveryTypeID | GUID | The field contains the delivery type ID as a link to the CRMDeliveryType table |
StoreID | GUID | The field contains the store ID as a link to the CRMStore table |
ManagerID | GUID | The field contains the manager ID s a link to the CRMUser table |
Status | string | The field contains the status |
Date | datetime | The field contains the shipment date |
TimeFrom | datetime | The field contains the start time of shipment |
TimeTo | datetime | The field contains the end time of shipment |
Comment | string | The field contains a comment |
CrmDeliveryType | The table provides information about types of delivery | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the delivery |
Code | string | The field contains the symbol code of the delivery |
Active | boolean | The field indicates whether the type is active |
DefaultCost | decimal | The field contains the default cost for the type |
DefaultNetCost | decimal | The field contains the default net cost for the type |
Description | string | The field contains a description |
DefaultForCrm | boolean | The field indicates whether the type is default for the CRM |
VatRate | string | The field contains the vat rate for the type |
CrmPayment | The table provides information about payments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. It is the Payment Id |
Name | string | The field contains the name of the payment |
Code | string | The field contains the code of the payment |
Summ | decimal | The field contains the payment amount |
Currency | string | The field contains the currency of the payment |
DateBill | datetime | The field contains the bill date of payment |
DatePayed | datetime | The field contains the date of payment |
DateCanceled | datetime | The field contains the date when the payment was canceled |
DateStatus | datetime | The field contains the date when the payment status was changed |
Comments | string | The field contains a comment |
Payed | bool | The field indicates whether the payment is made |
MarkedReason | string | The field contains the reason for payment or to cancel |
ManagerID | GUID | The field contains the manager ID as a link to the CRMUser table |
PaymentStatusID | GUID | The field contains the payment status ID as a link to the CRMPaymentStatus table |
Description | string | The field contains a description |
CustomerID | GUID | The field contains the customer ID as a link to the CrmCustomer table |
CompanyID | GUID | The field contains the company ID as a link to the CRMCompany table |
OrderID | GUID | The field contains the order ID as a link to the CrmOrder table |
PaymentTypeID | GUID | The field contains the payment type ID as a link to the CrmpaymentType table |
PaymentMethod | string | The field contains the method of payment |
CrmPaymentType | The table provides information about types of payments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains a type code |
Name | string | The field contains a type name |
Active | boolean | The field indicates whether the type is active |
DefaultForCrm | boolean | The field indicates whether the type is default for CRM |
DefaultForApi | boolean | The field indicates whether the type is default for API |
Description | string | The field contains a description |
CrmDeliveryPaymentLink | The table provides a link between delivery and payment | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
DeliveryTypeIdD | GUID | The field contains the delivery type ID as a link to the CRMDeliveryType table |
PaymentTypeID | GUID | The field contains the payment type ID as a link to the CRMPaymentType table |
CrmPaymentStatus | The table provides information about statuses of payments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains a status code |
Name | string | The field contains a status name |
Active | boolean | The field indicates whether the status is active |
DefaultForCrm | boolean | The field indicates whether the status is default for CRM |
DefaultForApi | boolean | The field indicates whether the status is default for API |
Description | string | The field contains a description |
PaymentComplete | boolean | The field indicates whether the payment is complete |
Ordering | int | The field indicates the order sequence |
CrmPaymentTypeStatusLink | The table provides a link between paymentTypes and paymentStatuses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
PaymentTypeID | GUID | The field contains the payment type ID an as a link to the CRMPaymentType table |
PaymentStatusID | GUID | The field contains the payment status ID as a link to the CRMPaymentStatus table |
CrmDeliveryService | The table provides information about delivery services | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the delivery service |
Code | string | The field contains the code of the delivery service |
Active | boolean | The field indicates whether the delivery service is active |
CrmDeliveryTypeServiceLink | The table provides a link between deliveryTypes and services | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
DeliveryTypeID | GUID | The field contains the delivery type ID as a link to the CRMDeliveryType table |
DeliveryServiceID | GUID | The field contains the delivery service ID as a link to the CRMDeliveryService table |
CrmStore | The table provides information about stores | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId | string | The field contains the Entity ID from original source system. It is the Store Id |
Code | string | The field contains the code of the store |
Name | string | The field contains the name of the store |
Active | boolean | The field indicates whether the store is active |
Description | string | The field contains the description pf store |
string | The field contains the email of store | |
Type | string | The field contains the type of store |
InventoryType | string | The field contains the type of inventory in the store |
Phone | string | The field contains a phone number |
CrmOrder | The table provides information about orders (deals) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Title | string | The field contains the title of the order |
Amount | decimal | The field contains the amount of the order |
Currency | string | The field contains the currency of the order |
DeliveryCost | decimal | The field contains the cost of delivery |
OrigId2 | string | The field contains the Entity ID from original source system. It is the Order Id |
Number | string | The field contains the number of the order |
OrderTypeID | GUID | The field contains the order type ID as a link to the CRMOrderType table |
MethodId | GUID | The field contains the order method ID as a link to the CRMOrderMethod table |
CountryId | GUID | The field contains the country ID as a link to the CRMCountry table |
Date | datetime | The field contains the date of the order |
StatusUpdatedAt | datetime | The field contains the date when the status of the order was updated |
CloseDate | datetime | The field contains the close date of the order |
WonDate | datetime | The field contains the date when the order was won |
TotalAmount | decimal | The field contains the total amount of the order, which includes a discount |
PrepaySum | decimal | The field contains the prepaid amount of the order |
PurchaseSum | decimal | The field contains the purchase amount of the order |
PurchaseDate | datetime | The field contains the date of the purchase order |
Mark | int | The field contains the mark of the order |
MarkDatetime | datetime | The field contains the date when the order was marked by the customer |
LastName | string | The field contains the last name of the contragent |
FirstName | string | The field contains the first name of the contragent |
Patronymic | string | The field contains the patronymic of the contragent |
Phone | string | The field contains the phone number of the contragent |
AdditionalPhone | string | The field contains the additional phone number of the contragent |
string | The field contains the email of the contragent | |
Call | boolean | The field indicates whether a call is required |
Expired | boolean | The field indicates whether the order has expired |
CustomerComment | string | The field contains the comment of the customer |
ManagerComment | string | The field contains the comment of the manager |
ManagerID | GUID | The field contains the manager ID as a link to the CRMUser table |
CustomerID | GUID | The field contains the customer ID as a link to the CRMCustomer table |
ContactID | GUID | The field contains the contact ID as a link to the CRMCustomer table |
CompanyID | GUID | The field contains the company ID as a link to the CRMCompany table |
MarketplaceCode | string | The field contains the code of the marketplace |
Site | string | The field contains the URL of the website |
LossReason | string | The field contains the cause of the loss. To be filled in if the order status is lost |
StatusID | GUID | The field contains the order status ID as a link to the CRMOrderStatus table |
Deleted | boolean | The field indicates whether the order has been deleted |
StageID | GUID | The field contains the stage ID as a link to the CRMOrderStatus table. Stage of the order workflow |
Discount | decimal | The field contains the discount of the order |
Tax | decimal | The field contains the tax of the order |
SalesCommission | decimal | The field contains the sales commission |
ExciseDuty | decimal | The field contains the excise duty |
Adjustment | decimal | The field contains the adjustment (additional discount) |
VendorID | GUID | The field contains the vendor ID as a link to the CRMCustomer table |
PurchaseOrderNum | string | The field contains the purchase order number |
DealID | GUID | The field contains the opportunity ID as a link to the CrmOpportunity table |
QuoteID | GUID | The field contains the quote ID as a link to the CrmQuote table |
timeToClose | srting | The field contains the time when the deal is closed |
Probability | string | The field contains the probability of deal |
CrmOrderDetail | The table provides detailed information about orders: items, quantities, prices, amounts | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrderID | GUID | The field contains the order ID as a link to the CRMOrder table |
ItemID | GUID | The field contains the item ID as alink to the CRMItem table |
OfferID | GUID | The field contains the offer ID as alink to the CRMOffer table |
Price | decimal | The field contains the price of the item |
Discount | decimal | The field contains the discount amount of the item |
VatRate | decimal | The field contains the vat rate of the item |
Quantity | decimal | The field contains the quantity of the item |
Status | string | The field contains the item status in the order |
IsCanceled | boolean | The field indicates whether the item is removed from the order? (default value “false”) |
TotalAfterDiscount | decimal | The field contains the total item amount after discount |
InvoiceID | GUID | The field contains the invoice ID as a link to the CRMInvoice table |
QuoteID | GUID | The field contains the quote ID as a link to the CRMQuote table |
OpportunityID | GUID | The field contains the opportunity ID as a link to the CRMOpportunity table |
ListPrice | decimal | The field contains the item price from the price list |
Currency | string | The field contains the currency of the item |
Description | string | The field contains the description of the item |
CrmOrderPaymentLink | The table provides a link between orders and payments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrderId | GUID | The field contains the order ID as a link to the CrmOrder table |
PaymentId | GUID | The field contains the payment ID as a link to the CrmPayment table |
Description | string | The field contains the description of the order payment |
CrmShipmentOrderLink | The table is a link between Shipments and Orders | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrderID | GUID | The field contains the order ID as a link to CRMOrder table |
ShipmentID | GUID | The field contains the shipment ID as a link to CRMShipment table |
CrmOrderType | The table provide sinformation about order types | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the order type |
Name | string | The field contains the name of the order type |
Active | boolean | The field indicates whether the order type is active? |
DefaultForCrm | boolean | The field indicates whether this status is default for CRM? |
DefaultForApi | boolean | The field indicates whether this status is default for API? |
CrmOrderMethod | The table provides information about order methods | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the order method |
Name | string | The field contains the name of the order method |
Active | boolean | The field indicates whether the order method is active? |
DefaultForCrm | boolean | The field indicates whether this status is default for CRM? |
DefaultForApi | boolean | The field indicates whether this status is default for API? |
FromPos | boolean | The field indicates whether the order came from point of sale |
CrmOrderStatus | The table provides information about statuses for different objects (except payment status) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. |
Name | string | The field contains the order status name |
Code | string | The field contains the order status code: |
CodePipeline | int | The field contains the code of order status in the pipeline |
NamePipeline | string | The field contains the name of order status in the pipeline |
PipelineOrder | decimal | The field contains the number of stage in the pipeline |
Group | string | The field contains the order status in the group |
Active | boolean | The field indicates whether the order status is active? |
LostStatus | string | The field contains the lost status for pipleline |
WonStatus | string | The field contains the won status for pipleline |
CrmItemGroup | The table provides information about item groups | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system |
ParentId | int | The field contains the ID of the parent group |
Site | string | The field contains the name of the site (store) |
Name | string | The field contains the name of the group |
Active | boolean | The field indicates whether the group is active? |
CrmOffer | The table provides information about invemtory | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. |
Site | string | The field contains the name of the site (store) |
PurchasePrice | decimal | The field contains the price of purchase in the offer |
Quantity | int | The field contains the quantity in the offer |
CrmItem | The table provides information about items | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system |
MinPrice | decimal | The field contains the minimum price of the item |
MaxPrice | decimal | The field contains the maximum price of the item |
Currency | string | The field contains the currency of the item |
Article | string | The field contains the article of the item |
Name | string | The field contains the name of the item |
Description | string | The field contains the descripation of the item |
Manufacturer | string | The field contains the manufacturer of the item |
Active | boolean | The field indicates whether the item is active? |
Quantity | int | The field contains the quantity of the item |
Measure | The field contains the measure of the item | |
IsDeleted | boolean | The field indicates whether this item is deleted? |
Commission | decimal | The field contains the commission rate of the item |
Vendorid | GUID | The field contains the vendor ID as a link to the CRMCustomer table |
Category | string | The field contains the product category of the item |
CrmItemGroupLink | The table provides a link between items and groups | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ItemID | GUID | The field contains the item ID as a link to the CRMItem table |
GroupID | GUID | The field contains the group ID as a link to the CRMItemGroup table |
CrmItemOfferLink | The table provides a link between items and offers | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ItemID | GUID | The field contains the item ID as a link to the CRMItem table |
OfferID | GUID | The field contains the offer ID as a link to the CRMOffer table |
CrmTask | The table provides information about tasks | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system |
Text | string | The field contains the text for task description |
Commentary | string | The field contains comments for the task |
Date | datetime | The field contains the date when the task was created |
Complete | boolean | The field indicates whether the task is completed? |
CreatorID | GUID | The field contains the creator ID as link to the CRMUser table |
PerformerID | GUID | The field contains the performer ID as a link to the CRMUser table |
CustomerID | GUID | The field contains the customer ID as a link to the CRMCustomer table |
OrderID | GUID | The field contains the order ID as a link to the CRMOrder table |
Phone | string | The field contains phones related with the task |
IsClosed | boolean | The field indicates whether the task is closed |
Status | string | The field contains the status of the task: (In Progress, Completed, Waiting for someone else, Not started, Deffered) |
Type | string | The field contains the typeof the task: (Call, Meeting, Email, Send letter, Send quote, Task, Deadline, other) |
IsDeleted | boolean | The field indicates whether the task is deleted |
CrmLeadDetail | The table provides information about details of leads deal | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Probability | decimal | The field contains the probability of transaction (in %) |
Opportunity | decimal | The field contains the estimated amount of the opportunity (deal) |
Currency | string | The field contains the currency of the opportunity (deal) |
CustomerID | GUID | The field contains the customer ID as a link to the CRMCustomer table |
OrderID | GUID | The field contains the order ID as a link to the CRMOrder table |
CrmAsset | The table provides items that have a commercial value, such as a product sold by your company or a competitor, that a customer has purchased and installed | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the asset |
ContactId | GUID | The field contains the contact ID as a link to the CRMCustomer table |
Description | string | The field contains the description of the asset |
IsCompetitorProduct | boolean | The field indicates whether the asset represents a product sold by a competitor (true) or not (false). Default value is false. |
Price | decimal | The field contains the price paid of the asset |
Status | string | The field contains the status of the asset (Purchased, Shipped, Installed, Registered, Obsolete) |
Quantity | decimal | The field contains the purchased or installed quantity of the asset |
CustomerID | GUID | The field contains the customer ID as a link to the CRMCustomer table |
IsDeleted | boolean | The field indicates whether the asset is deleted |
CrmItemID | GUID | The field contains the item ID as a link to the CRMItem table |
CreatedDate | DateTime | The field contains the date when the asset was created |
PurchaseDate | DateTime | The field contains the date when the asset purchased |
SerialNumber | string | The field contains the serial number of the asset. |
ProductCode | string | The field contains the product code of the asset |
StockKeepingUnit | string | The field contains the SKU assigned to the related product |
UsageEndDate | datetime | This field contains the end or expiration date of the asset |
Сurrency | string | This field contains the currency of the asset |
Sourceid | GUID | The field contains the sourceID as a link to the CRMSource table |
Type | string | This field contains the type of the asset |
Channel | string | This field contains the name of the channel of the asset |
СrmCampaign | The table provides information about marketing campaigns, such as direct mail promotions, webinars, or trade shows | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId | string | The field contains the Entity ID from original source system |
ActualCost | int | The field contains the amount of money spent on the campaign |
AmountAllOpportunities | int | The field contains the amount of money in all opportunities associated with the campaign, including closed/won opportunities |
AmountWonOpportunities | int | The field contains the amount of money in closed or won opportunities associated with the campaign |
BudgetedCost | int | The field contains the amount of money budgeted for the campaign |
CurrencyIsoCode | string | The field contains the ISO code for any currency allowed by the organization |
Description | string | The field contains the description of the campaign |
EndDate | date | The field contains the end date of the campaign. Responses received after this date are still counted |
ExpectedResponse | decimal | This field contains the percentage of responses you would expect to receive for the campaign |
ExpectedRevenue | int | This field contains the amount of money you expect to get from the campaign |
IsActive | boolean | This field indicates whether the campaign is active (true) or not (false). Default value is false |
Name | string | This field contains the name of the campaign |
NumberOfContacts | int | This field contains the number of contacts associated with the campaign |
NumberOfConvertedLeads | int | This field contains the number of leads that were converted to an account and contact due to the marketing efforts in the campaign |
NumberOfLeads | int | This field contains the number of leads associated with the campaign |
NumberOfOpportunities | int | This field contains the number of opportunities associated with the campaign |
NumberOfResponses | int | This field contains the number of contacts and unconverted leads with a Member Status equivalent to “Responded” for the campaign |
NumberOfWonOpportunities | int | This field contains the number of closed or won opportunities associated with the campaign |
NumberSent | decimal | This field contains the number of individuals targeted by the campaign. For example, the number of sent emails |
StartDate | date | This field contains the date when the campaign was started |
Status | string | This field contains the status of the campaign: Planned, In Progress. |
Type | string | This field contains the type of campaign: Direct Mail, Referral Program |
ContactID | GUID | The field contains the contact ID as a link to the CRMCustomer table |
ParentCampaignID | GUID | The field contains the parent campaign ID as a link to the CRMCampaign table |
ExpectedRevenueDecimal | decimal | This field contains the expected revenue amount of the campaign |
CampaignCreateDate | date | This field contains the date when the campaign was created in the UTC |
OwnerID | GUID | The field contains the parent owner ID as a link to the CRMUser table |
ListID | GUID | The field contains the list ID as a link to the CrmAddressBook table |
СrmCampaignStatus | The table provides information about sent marketing emails | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CampaignID | GUID | The field contains the campaign ID as a link to the CRMCampaign table |
Created | datetime | This field contains the date when the campaign was created |
PersonID | GUID | The field contains the person ID as a link to the CRMUser table |
Type | string | This field contains the type of email event: delivered, opened, clicked, bounced etc.) |
Count | decimal | This field contains the number of events in the campaign |
ContactID | GUID | The field contains the contact ID as a link to the CRMCustomer table |
CrmOpportunity | The table provides information about opportunities (sales, pending deals) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Probability | decimal | The field contains the percentage of estimated confidence in closing the opportunity (in %) |
OpportunityAmount | decimal | The field contains the estimated amount of the opportunity |
Currency | string | The field contains the currency of the opportunity (deal) |
CustomerID | GUID | The field contains the customer ID as a link to the CRMCustomer table |
СrmCampaignID | GUID | The field contains the campaign ID as a link to the CRMCampaign table |
ExpectedRevenue | decimal | The field contains the expected revenue amount of the opportunity |
IsDeleted | boolean | The field indicates whether the opportunity is deleted |
IsClosed | boolean | The field indicates whether the opportunity is closed |
Description | string | The field contains the descripation of the opportunity |
Source | string | The field contains the source of the opportunity |
ContactID | GUID | The field contains the contact ID as a link to the CRMCustomer table |
DealDate | datetime | The field contains the date when the opportunity was closed |
CreateDate | datetime | The field contains the date when the opportunity was created |
Name | string | The field contains the name of the opportunity |
Stage | string | The field contains the stage of the opportunity |
Type | string | The field contains the type of the opportunity |
Status | string | The field contains the status of the opportunity |
Category | string | The field contains the category of the opportunity |
OwnerID | GUID | The field contains the owner ID as a link to the CRMUser table |
ForecastCloseDate | datetime | The field contains the forecasted close date of the opportunity |
PipelineOrigId | string | The field contains the original pipeline Id in source system, its value can be found in the CrmOrderStatus table |
StageOrigId | string | The field contains the original stage Id in source system, its value can be found in the CrmOrderStatus table |
LossReason | string | The field contains the reason of loosing the opportunity. It is filled in in case of order status is lost |
LeadConversionTime | decimal | The field contains the time of leads conversion |
ConvertedComment | string | The field contains a comment about the conversion of the current deal (e.g., from a lead) |
CrmProjects | The table provides information about projects | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. |
Tittle | string | The field contains the name of the project |
RelatedresourceType | string | The field contains the type of resource related to the project (person, company, opportunity, etc) |
RelatedresourceID | GUID | The field contains the resource ID as a link to the CRMCustomer/ CRMCompany/CRMOrder table depending on RelatedresourceType field |
Commentary | string | The field contains the description of the project |
Date | datetime | The field contains the date when the project was created |
CreatorID | GUID | The field contains the creator ID as a link to the CRMUser table |
Status | string | The field contains the status of the project |
Category | string | The field contains the category of the project |
Stage | string | The field contains the stage of the project |
StartDate | datetime | The field contains the date when the project was started |
CloseDate | datetime | The field contains the date when the project was closed |
OwnerID | GUID | The field contains the owner ID as a link to the CRMUser table |
CrmInvoice | The table provides information about invoices for orders | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId | string | The field contains the Entity ID from original source system. |
Title | string | The field contains the title of the invoice |
Description | string | The field contains the description of the invoice |
Currency | string | The field contains the currency of the invoice |
Tax | decimal | The field contains the tax value of the invoice |
InvoiceDate | datetime | The field contains the due date of the invoice |
CustomerID | GUID | The field contains the customer ID as a link to the CRMCustomer table |
OrderID | GUID | The field contains the order ID as a link to the CRMOrder table |
StatusID | GUID | The field contains the status ID as a link to the CRMOrderStatus table |
TotalAmount | decimal | The field contains the total amount of the invoice including the discont amount |
SalesCommission | decimal | The field contains the sales commission related to the invoice |
CloseDate | datetime | The field contains the date when the invoice should be closed |
Adjustment | decimal | The field contains the adjustment amount (additional discount) |
CreateDate | datetime | The field contains the date when the invoice was created |
PurchaseSum | decimal | The field contains the purchase amount of the invoice |
InvoiceNumber | string | The field contains the number of the invoice |
ContactID | GUID | The field contains the contact ID as a link to the CRMCustomer table |
ExciseDuty | decimal | The field contains the value of excise duty |
PurchaseOrderNum | string | The field contains the number of purchase order |
Discount | decimal | The field contains the discount value of the invoice |
CrmQuote | The table provides information about quotes | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. |
Description | string | The field contains the descripation of the quote |
Discount | decimal | The field contains the value of quote dicsount |
Currency | string | The field contains the currency of the quote |
Tax | decimal | The field contains the tax value of the quote |
OpportunityID | GUID | The field contains the opportunity ID as a link to the CRMOpportunity table |
CustomerID | GUID | The field contains the customer ID as a link to the CRMCustomer table |
ValidTillDate | datetime | The field contains the date until which the quote is valid |
Stage | string | The field contains the stage of the quote |
TotalAmount | decimal | The field contains the total amount of the quote including the discont value |
Adjustment | decimal | The field contains the adjustment of the quote (additional discount) |
CreateDate | datetime | The field contains the date when the quote was created |
PurchaseSum | decimal | The field contains the total amount of the purchase |
Title | string | The field contains the title of the quote |
ContactId | GUID | The field contains the contact ID as a link to the CRMCustomer table |
QuoteNumber | string | The field contains the number of the quote |
OwnerID | GUID | The field contains the owner ID as a link to the CRMUser table |
CrmEvent | The table provides information about events related to the leads | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. |
СreatedAt | datetime | The field contains the date when the event was created |
CustomerID | GUID | The field contains the customer ID as a link to the CRMCustomer table |
Description | string | The field contains the description of the event |
Source | string | The field contains the name of the lead source |
Type | string | The field contains the type of event: Registration, Inquiry, Seller Inquiry etc. |
Street | string | The field contains the street (location) of the event |
City | string | The field contains the city of the event |
Price | decimal | The field contains the price of the event |
ForRent | boolean | The field indicates whether the event is for rented |
Type of property | string | The field contains the type of the event: Bungalow, Apartment, etc |
OwnerID | GUID | The field contains the owner ID as a link to the CRMUser table |
Name | string | The field contains the name of the event |
StartDate | datetime | The field contains the date when the event was started |
EndDate | datetime | The field contains the date when the event was ended |
CrmAnalitycs | The table provides information about analytics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Type | string | The field contains the type of the analytics: (total, session, sourses, geolocation, utm-campaigns, etc) |
PeriodType | string | The field contains the type of period: “day”; “month”; “3 months” |
PeriodDate | datetime | The field contains the date the statistics were collected |
Breakdowns | string | The field contains the method of obtaining the statistical information (direct, paid, referrals, organic, offline, other) |
Leads | string | The field contains the number of leads |
ContactToCustomerRate | decimal | The field contains the conversion ratio of contacts to castomers |
Customers | string | The field contains the number of customers |
Opportunities | string | The field contains the number of opportunities |
Contacts | string | The field contains the number of contacts |
SalesQualifiedLeads | string | The field contains the number of sales qualified leads |
CrmEventLinks | The table provides links for: leads, customers, projects, opportunities, etc. related to events | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
EventId | GUID | The field contains the event ID as a link to the CRMEvent table |
EntityId | GUID | The field contains the entity ID as a link to CRMCustomer/CrmProject/etc tables according to the linked entity |
Entity | string | The field contains the name of the linked entity accoding with the entity in the EntityId field |
Status | string | The field contains the status of participant |
CrmCampaignLinks | The table provides links for: leads, customers, projects, opportunities, etc related to campaigns | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CampaignID | GUID | The field contains the campaign ID as a link to the CrmCampaign table |
EntityID | GUID | The field contains the entity ID as a link to CRMCustomer/CrmProject/etc tables |
Entity | string | The field contains the name of the linked entity accoding with the entity in the EntityId field |
CrmAddressBook | The table provides | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. |
Name | string | The field contains the name of the address books |
AllEmailQty | decimal | The field contains the number of all emails in the address book |
InactiveEmailQty | decimal | The field contains the number of inactive emails in the address book |
CreateDate | timestamp | The field contains the date when the address book was created |
MembersCount | int | The field contains the number of active members in the list |
Source | GUID | The field contains the source ID as a link to the CrmSource table |
Accountancy and Payroll Entities DB Schema contains information from the client’s accounting and payroll systems. Data from various systems such as Xero, QuickBooks, FreshBooks, etc. being structured and placed at a single structure to facilitate the following analysis. The schema consists of such entities AccCompany, AccBank, AccInvoices, AccContragent, AccExpenses. Please see below to find out the complete list of schema’s entities. | ||
TABLE/FIELDS | DATA TYPES | DESCRIPTION |
AccCompany | The table provides general company information such as company name, company owner, company date of incorporation, etc | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigCompanyID | string | The field contains the Entity ID from original source system. (For example company ID value from Xero, QuickBooks, FreshBooks, etc) |
CompanyName | string | The field contains the name of the company |
LegalFormID | GUID | The field contains the legal form ID as a link to the AccLegalForm table |
IndustryID | GUID | The field contains the industry ID as a link to the AccIndustry table |
CompanyLegalName | string | The field contains the legal name of the company |
DateOfIncorporation | datetime | The field contains the date of the company incorporation |
OwnerFirstName | string | The field contains the first name of the owner |
OwnerLastName | string | The field contains the last name of the owner |
OwnerPhone | string | The field contains the phone number of the owner |
OwnerEmail | string | The field contains the email of the owner |
DirectorFirstName | string | The field contains first name of the owner’s CEO |
DirectorLastName | string | The field contains the last name of the owner’s CEO |
VATPayer | string | The field indicates whether the company is a VAT payer |
BankID | GUID | The field contains the bank ID as a link to the AccBank table |
EmailCompany | string | The field containsthe company email |
PhoneCompany | string | The field contains the company phone number |
WebsiteCompany | string | The field contains the company website |
BaseCurrencyID | GUID | The field contains currency ID as a link to the AccCurrency table |
AccLegalForm | The table provides information about company’s legal form | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigLegalFormID | string | The field contains the Entity ID from original source system, if it is consists |
Name | string | The field contains the legal form title of the company |
Description | string | The field contains the legal form description of the company |
AccIndustry | The table provides information about industries in which the company operates | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the title of the industry |
Description | string | The field contains the description of the industry |
AccCountry | The table provides list of countries | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the country |
NumericCode | decimal | The field contains the international country numeric code |
alpha2 | string | The field contains the international country alpha-2 code |
alpha3 | string | The field contains the international country alpha-3 code |
AccBank | The table provides information about banks | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the bank |
Code | string | The field contains an international code of the bank such as SWIFT, BIC |
Bik | string | The field contains the identifier code of the Russian Central Bank |
BankCity | string | The field contains the city where the bank is located |
BankCountryID | GUID | The field contains the country ID as a link to the AccCountry table |
BankType | string | The field contains the type of the bank |
AccRegistrationCode | The table provides information about legal codes for different entities such as company, contragent, employee, etc. | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CompanyID | GUID | The field contains the company ID as a link to the AccCompany table |
ContragentID | GUID | The field contains the contragent ID as a link to the AccContragent table |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
Code | string | The field contains the title of the legal code |
Description | string | The field contains the description of the legal code |
Value | string | The field contains the value of the legal code |
AccAddress | The table provides information about addresses of various objects such as company, customer, supplier, etc. | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CompanyID | GUID | The field contains the company ID as a link to the AccCompany table |
ContragentID | GUID | The field contains the contragent ID as a link to the AccContragent table |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
ContactID | GUID | The field contains the contact ID as a link to the AccContact table |
LocationID | GUID | The field contains the locations ID as a link to the AccLocations table |
CountryID | GUID | The field contains the country ID as a link to the AccCountry table |
AddressDescr | string | The field contains address description or address type (e.g. Main address, Contact address, Mailing address, Legal address…) |
Postcode | string | The field contains the postal code |
Region | string | The field contains the region name |
City | string | The field contains the city name |
Street | string | The field contains the street name |
StreetNumber | string | The field contains the street number |
Floor | string | The field contains the floor number |
Apartment | string | The field contains the apartment number |
Comment | string | The field contains a comment to the address |
AddressStr | string | The field contains the combined address. (city + street + street number + floor + apartment) |
AccCurrency | The table provides the list of currencies | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
NumCode | decimal | The field contains the international digital currency code |
CharCode | string | The field contains the international char currency code |
Name | string | The field contains the title of the currency |
AccTransactionTypes | The table provides information about types of transactions | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
TypeName | string | The field contains the title of the transaction type |
TypeCode | string | The field contains the code of the transaction type |
AccInvoices | The table provides information about company invoices issued by a business to its client | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigInvoiceID | string | The field contains the Entity ID from original source system. (For example Invoice ID value from Xero, QuickBooks, FreshBooks, etc) |
Number | string | The field contains the document number |
ContragentID | GUID | The field contains the contragent ID as a link to the AccContragent table |
CurrencyID | GUID | The field contains the currency ID as a link to the AccCurrency table |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
DateIssue | datetime | The field contains the date of the invoice statement |
DateDue | datetime | The field contains the due date of the invoice statement |
Status | string | The field contains the status of the invoice |
Corrected | decimal | The field indicates whether the invoice is “alive”. (if the original invoice does not have the corresponding adjustment invoices, put 0 here, if the original invoice contains an adjustment invoice – then put 0 for the adjustment invoice and 1 for the original invoice) |
AmountTotal | decimal | The field contains the total amount of invoices in the base currency |
Type | string | The field contains the type of the invoice |
Comment | string | The field contains a comment to the invoice |
OriginalCurrency | GUID | The field contains original currency ID as a link to the AccCurrency table |
OriginalAmount | decimal | The field contains the total amount of the invoice in the original currency |
LocationID | GUID | The field contains location ID as a link to the AccLocations table |
Matterid | GUID | The field contains matter ID as a link to the AccMatter table |
AccContragent | The table provides information about company contragents such as customer, vendor, etc. | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigContragentID | string | The field contains the Entity ID from original source system. (For example CustomerID, VendorID value from Xero, QuickBooks, FreshBooks, etc) |
NameContragent | string | The field contains the full name of the contragent |
ContragentTypeID | GUID | The field contains the contragent type ID as a link to the AccContragentType table |
LegalFormID | GUID | The field contains the contragent legal form ID as a link to the AccLegalForm table |
CompanyName | string | The field contains the name of the company |
FirstName | string | The field contains the first name of the contragent |
LastName | string | The field contains the last name of the contragent |
Phone | string | The field contains the phone number of the contragent |
Fax | string | The field contains the fax of the contragent |
Position | string | The field contains the position of the contragent |
Mobile | string | The field contains the mobile phone number of the contragent |
string | The field contains the email of the contragent | |
Status | string | The field contains the status of the contragent |
WebsiteCompany | string | The field contains the company website of the contragent |
AccContragentType | The table provides information about types of contragents | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains Entity ID from original source system, if it is consists |
TypeName | string | The field contains the title of the contragent type name (For example CUSTOMER; VENDOR…) |
TypeDescr | string | The field contains the description of the contragent type |
AccContragentBalances | The table provides information about customers and vendors balance | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
BalanceType | string | The field contains the type of the balance (outstanding AR/AP/Credits /Total etc.) |
ContragentID | GUID | The field contains contragent ID as a link to the AccContragent table |
Amount | decimal | The field contains the balance amount of the contragent |
Date | datetime | The field contains the date when the balance amount was updated at the last time |
Description | string | The field contains the amount description of the contragent |
AmountOverdue | decimal | The field contains the overdue balance amount of the contragent |
AccExpenses | The table provides information about customer invoices that the customer issues to the company (also called bills) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigExpenseID | string | The field contains the Entity ID from original source system. (For example Bill ID value from Xero, QuickBooks, FreshBooks, etc) |
Number | string | The field contains the number of the document |
ContragentID | GUID | The field contains the contragent ID as a link to the AccContragent table |
CurrencyID | GUID | The field contains the currency ID as a link to the AccCurrency table |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
DateIssue | datetime | The field contains the date of the bill statement |
DateDue | datetime | The field contains the due date of the bill statement |
Status | string | The field contains the status of the bill |
IsBillable | boolean | The field indicates whether the account is billable or not billable |
StatusName | string | The field contains the name of the status |
Completed | string | The field indicates whether the bill was successfully completed or not |
AmountTotal | decimal | The field contains the total amount of bill in the base currency |
Description | string | The field contains the description of the bill |
TypeID | GUID | The field contains the expense type ID as a link to the ExpenseType table |
CustomerReceipt | string | The field contains the URL of the customer receipt |
OriginalSumm | decimal | The field contains the original amount of the bill |
Category | string | The field contains the category of expenses |
Reimbursable | boolean | The field contains the reimbursable flag of a bill |
OriginalCurrency | GUID | The field contains the original currency ID as a link to the AccCurrency table |
TaxName | string | The field contains the title of the tax |
TaxSumm | decimal | The field contains the amount of the tax |
DistanceTime | string | The field contains the distance traveled or time spent on certain specific expenses categories |
DistanceTimeUnit | string | The field contains the distance or time unit |
ReportID | GUID | The field contains report ID as a link to the AccReportlist table |
PolicyID | GUID | The field contains policy ID as a link to the AccReportlist table |
BankID | GUID | The field contains bank ID as a link to the AccBank table |
OwnerID | GUID | The field contains owner ID as a link to the AccContact table |
MatterID | GUID | The field contains matter ID as a link to the AccMatter table |
UserID | GUID | The field contains user ID as a link to the AccEmployee table |
ExpenseCategoryID | GUID | The field contains expenses category ID as a link to the AccExpenseCategory table |
Quantity | string | The field contains the items quantity of the purchase |
Price | string | The field contains the items price of the purchase |
AccExpenseCategory | The table provides information about category expenses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains Entity ID from original source system, if it is consists |
Name | string | The field contains the name of the category |
СreateDate | datetime | The field contains the date when the category was created |
ExpenseID | GUID | The field contains the expense ID as a link to the AccExpenses table |
AccPayments | The table provides information about company payments, such as invoices payments, bills payments, tax payments, etc. | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigPaymentID | string | The field contains the Entity ID from original source system. (For example Payment ID value from Xero, QuickBooks, FreshBooks, etc) |
OrigPaymentNumber | string | The field contains the entity number from original source system |
PaymentTypeID | GUID | The field contains the payment type ID as a link to the AccPaymentType table |
Date | datetime | The field contains the date of the payment |
PaymentMethodID | GUID | The field contains the payment method ID as a link to the AccPaymentMethod table |
Amount | decimal | The field contains the amount of the payment |
CurrencyID | GUID | The field contains the currency ID as a link to the AccCurrency table |
Description | string | The field contains the description of the payment |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
DepositAccountID | GUID | The field contains the deposit account ID as a link to the AccAccount table |
ContragentID | GUID | The field contains the contragent ID as a link to the AccContragent table |
TransactionTypeID | GUID | The field contains the transaction type ID as a link to the AccTransactionTypes table |
CategoryID | GUID | The field contains the payment category ID as a link to the AccPaymentCategory table |
SubCategoryID | GUID | The field contains the payment sub category ID as a link to the AccPaymentSubCategory table |
OriginalCurrencyID | GUID | The field contains original currency ID as a link to the AccCurrency table |
OriginalAmount | decimal | The field contains the total payment amount in the original currency |
LocationID | GUID | The field contains original location ID as a link to the AccLocations table |
Status | string | The field contains the status of the payment transaction |
ItemID | GUID | The field contains item ID as a link to the AccItem table |
MatterID | GUID | The field contains matter ID as a link to the AccMatter table |
UnappliedAmount | string | The field contains the unapplied amount |
BankAccountID | GUID | The field contains bank account ID as a link to the AccBankAccount table |
AccPaymentCategory | The table provides information about payment categories | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CategoryName | string | The field contains the name of the category |
Descr | string | The field contains the description of the category |
AccPaymentSubCategory | The table provides information about payment subcategories | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
SubCategoryName | string | The field contains the name of the subcategory |
Descr | string | The field contains the description of the subcategory |
AccPaymentDocLink | The table provides a link between the AccPayments table and the tables that contain payment documents such as AccInvoice, AccExpense … | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
PaymentsID | GUID | The field contains the link to the AccPayments table |
DocumentID | GUID | The field contains the link to the payment documents (AccInvoice, AccExpense…) |
DocDescr | string | The field contains information about the type of payment document: invoice, bill… |
AccPaymentMethod | The table provides information about payment methods | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the title of the payment method |
Descr | string | The field contains the description of the payment method |
AccPaymentType | The table provides information about payment type | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
NamePaymentType | string | The field contains the title of the payment type. For example “income”, “expense” |
Descr | string | The field contains the description of the payment type |
AccItems | The table provides information about company goods and services | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigItemID | string | The field contains the Entity ID from original source system. (For example Item ID value from Xero, QuickBooks, FreshBooks, etc) |
Code | string | The field contains the char code of the item |
AccName | string | The field contains the name of the item |
Descriptoin | string | The field contains the description of the item |
Status | string | The field contains the status of the item |
SalePrice | decimal | The field contains the selling price of the item |
PurchasePrice | decimal | The field contains the purchase price of the item |
Quantity | decimal | The field contains the quantity of goods |
SalesAccountID | GUID | The field contains the sales account ID as a link to the AccAccount table |
PurchaseAccountID | GUID | The field contains the purchase account ID as a link to the AccAccount table |
Measure | string | The field contains information about the measure of the unit |
Type | string | The field contains information about the type of the item. For example, Inventory; Service; NonInventory |
CurrencyID | GUID | The field contains the currency ID as a link to the AccCurrency table |
AccInvoicesDetalis | The table provides detailed information about company invoices issued by the business to its client | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
InvoiceID | GUID | The field contains the invoice ID as a link to the AccInvoice table |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
ItemID | GUID | The field contains the item ID as a link to the AccItems table |
Description | string | The field contains a description of the invoice line |
Quantity | decimal | The field contains the number of goods in a line |
Discount | decimal | The field contains the discount value |
PaidAmount | decimal | The field contains the paid amount |
AmountLine | decimal | The field contains the amount of a line in the base currency |
OriginalAmountLine | decimal | The field contains the amount of a line in the original currency |
MatterID | GUID | The field contains the matter ID as a link to the AccMatter table |
AccExpensesDetalis | The table provides detailed information about customer invoices which the customer issues to the company | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ExpenseID | GUID | The field contains the expense ID as a link to the AccExpense table |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
ItemID | GUID | The field contains the item ID as a link to the AccItems table |
Description | string | The field contains a description of the expense line |
Quantity | decimal | The field contains the number of goods in a line |
Discount | decimal | The field contains the discount value |
AmountLine | decimal | The field contains the amount of a line in the base currency |
OriginalAmountLine | decimal | The field contains the amount of a line in the original currency |
AccBankAccount | The table provides information about the company bank accounts | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ContragentID | GUID | The field contains the contragent ID as a link to the AccContragent table |
OrigBAID | string | The field contains the Entity ID from original source system. (For example bank account ID value from Xero, QuickBooks, FreshBooks, etc) |
Number | string | The field contains the number of the bank account |
Name | string | The field contains the name of the bank account |
BankID | GUID | The field contains the bank ID as a link to the AccBank table |
Swift | string | The field contains the SWIFT code |
CorAccount | string | The field contains information about the correspondent account |
Bank number | string | The field contains information about the local number of the bank |
AccountGroup | GUID | The field contains the account group ID as a link to the AccAccountGroup table |
CurrencyID | GUID | The field contains the currency ID as a link to the AccCurrency table |
Status | string | The field contains the status of the account |
LocationID | GUID | The field contains the location ID as a link to the AccLocations table |
Type | string | The field contains the type of the bank account |
AccAccount | The table provides information about company internal accounts | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigAccID | string | The field contains the Entity ID from original source system. (For example account ID value from Xero, QuickBooks, FreshBooks, etc) |
Code | string | The field contains the code of the account |
Name | string | The field contains the title of the account |
Description | string | The field contains the description of the account |
ClassID | GUID | The field contains the account class ID as a link to the AccAccountClass table |
GroupID | GUID | The field contains the account group ID as a link to the AccAccountGroup table |
Currency | string | The field contains the currency of the account |
CreateDate | datetime | The field contains the date when the account was created |
Active | boolean | The field indicates whether the account is active or not |
UserID | GUID | The field contains the user ID as a link to the AccEmployee table |
AccAccountClass | The table provides information about the class of internal accounts of the company. Accounts are usually classified by classes and groups | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the class code of the account |
Name | string | The field contains class title of the account |
AccAccountGroup | The table provides information about the group of internal accounts of the company. Accounts are usually classified by classes and groups | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the group code of the account |
Name | string | The field contains the group title of the account |
AccAccountBalance | The table provides balances of the company internal accounts | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
AccountID | GUID | The field contains the Account ID. And serves as a link to the AccAccount table |
Date | datetime | The field contains the account balance at the current date |
Amount | string | The field contains the amount of the account |
AvailableAmount | decimal | The field contains the available amount of the account on the current date |
AccAccountMovement | The table provides movements in the company internal accounts for a certain period | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
AccountID | GUID | The field contains the Account ID. And serves as a link to the AccAccount table |
OrigAccMovID | string | The field contains the Entity ID from original source system |
Description | string | The field contains the description of the movement |
Type | string | The field contains the type of the movement |
BeginDate | datetime | The field contains the start date of the movement |
EndDate | datetime | The field contains the end date of the movement |
BeginDT | decimal | The field contains the debit amount at the beginning of the period |
BeginCT | decimal | The field contains the credit amount at the beginning of the period |
TurnoverDT | decimal | The field contains the debit turnover for the period |
TurnoverCT | decimal | The field contains the credit turnover for the period |
EndDT | decimal | The field contains the debit amount at the end of the period |
EndCT | decimal | The field contains the credit amount at the end of the period |
AccReportlist | The table stores report data from the original systems | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ReportCode | string | The field contains the code of the report |
NameReportList | string | The field contains the title of the repost |
ReportParameter | string | The field contains the parameter of the repost |
CreateDate | datetime | The field contains the date when the report was created |
AccColumnList | The table stores report data from the original systems | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ColumnCode | string | The field contains the column code of the report |
ColumnSequence | decimal | The field contains the column serial number in the report |
NameColumnList | string | The field contains the column title |
ReportListID | GUID | The field contains the report list ID as a link to the AccReportList table |
AccRowList | The table stores report data from original systems | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
RowCode | string | The field contains the row code of the report |
RowSequence | decimal | The field contains the row serial number in the report |
Name | string | The field contains the row title |
RowCodeDescription | string | The field contains the row code description |
ReportListID | GUID | The field contains the report list ID as a link to the AccReportList table |
AccReportContent | The table stores report data from original systems | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ReportListCode | string | The field contains the code of the report |
ColumnListCode | string | The field contains the column code of the report |
RowListCode | string | The field contains the row code of the report |
Value | string | The field contains the value of particular row and column |
ReportListID | GUID | The field contains the report list ID as a link to the AccReportList table |
ColumnListID | GUID | The field contains the column list ID as a link to the AccColumnList table |
RowListID | GUID | The field contains the row list ID as a link to the AccRowList table |
AccContact | The table provides information about contragents (company) contacts | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
FirstName | string | The field contains the first name of the contact |
Last Name | string | The field contains the last name of the contact |
MiddleName | string | The field contains the middle name of the contact |
FullName | string | The field contains the full name of the contact |
CompanyID | GUID | The field contains the company ID as a link to the AccCompany table |
ContragentID | GUID | The field contains the contragent ID as a link to the AccContragent table |
Position | string | The field contains the position of the contact |
Phone | string | The field contains the phone number of the contact |
string | The field contains the email of the contact | |
Snils | string | The field contains the SNILS code of the contact |
Type | string | The field contains the type of contact (corporate, associated, personal) |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
AccCreditMemo | The table provides information about financial transaction representing a refund or credit of payment or a part of payment for goods or services that have been sold | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. (For example credit memo ID value from Xero, QuickBooks, FreshBooks, etc) |
Number | string | The field contains the reference number for the transaction |
Date | timestamp | The field contains the date entered by the user when transaction was occurred |
Status | string | The field contains the status of the credit memo |
Type | string | The field contains the type of the transaction (Receivable, Payable) |
TotalAmount | decimal | The field contains the total amount of the transaction |
ContragentID | GUID | The field contains the contragent ID as a link to the AccContragent table |
RemainingCredit | decimal | The field contains the total credit amount still available to apply towards the payment |
Balance | decimal | The field contains the balance reflecting any payments made against the transaction. Initially set to the value of TotalAmount. A Balance of 0 indicates the invoice is fully paid. |
AccCreditMemoDetails | The table provides detailed information about financial transaction representing a refund or credit of payment or part of a payment for goods or services that have been sold | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
MemoID | GUID | The field contains the credit memo ID as a link to the AccCreditMemo table |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
ItemID | GUID | The field contains the item ID as a link to the AccItems table |
Description | string | The field contains a description of the credit memo line |
Quantity | decimal | The field contains the number of goods in a line |
AmountLine | decimal | The field contains the amount of a line in the base currency |
OriginalAmountLine | decimal | The field contains the amount of a line in the original currency |
AccEmployee | The table provides information about company employees | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. (For example employee ID value from Xero, QuickBooks, FreshBooks, etc) |
CompanyID | GUID | The field contains the company ID as a link to the AccCompany table |
ManagerID | GUID | The field contains the manager ID as a link to the AccEmployee table |
FirstName | string | The field contains the first name of the employee |
MiddleName | string | The field contains the middle name of the employee |
LastName | string | The field contains the last name of the employee |
FullName | string | The field contains the full name of the employee |
Phone | string | The field contains the phone number of the employee |
string | The field contains the email of the employee | |
Status | string | The field contains the status of the employee |
StatusReason | string | The field contains the detailed reason of the workers current status |
Position | string | The field contains the position of the employee |
Department | GUID | The field contains the department ID as a link to the AccCompanyStructure table |
Type | string | The field contains the type of worker. For example Employee, Contractor |
EmploymentType | string | The field contains the type of the employment. For example FULL_TIME, PART_TIME |
BirthDate | datetime | The field contains the birth date of the employee |
Gender | string | The field contains the gender of the employee |
HireDate | datetime | The field contains the date when the employee was hired |
TerminationDate | datetime | The field contains the last working day of the employee |
VacationBalance | decimal | The field contains the current vacation balance for the employee |
VacationMeasure | string | The field contains the measurement type to be applied to the balance. For example HOURS, UNITS |
SickBalance | decimal | The field contains the current sick balance for the employee |
SickMeasure | string | The field contains the measurement type to be applied to the balance. For example HOURS, UNITS |
IsAdmin | boolean | The field indicates whether the user is an administrator or not |
Location | GUID | The field contains the location ID as a link to the AccLocation table |
AccEmployeeDirectDeposit | The table provides information about employees direct deposit | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
startDate | datetime | The field contains the date which the direct deposit will be applied to future pay periods |
endDate | datetime | The field contains the date which the direct deposit stop being applied to pay periods |
PaymentType | string | The field contains a type of payment for the direct deposit. For example FLAT_DOLLAR_AMOUNT, PERCENTAGE, REMAINDER |
AccountType | string | The field contains the financial institutions account type. For example CHECKING, SAVINGS, CREDIT_CARD |
Value | decimal | The field contains the amount to be applied to the direct deposit |
RoutingNumber | string | The field contains the financial institutions routing number |
AccountNumber | string | The field contains the financial institutions account number |
Priority | string | The field contains the priority order for which the direct deposits will be performed in. When a new direct deposit is added the priority will be assigned. |
BankID | GUID | The field contains the bank ID as a link to the AccBank table |
Verified | boolean | The field indicates whether the data of bank account is verified or not |
CompanyID | GUID | The field contains the company ID as a link to the AccCompany table |
AccRefund | The table provides information about refunds | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from the original source system. It is the Refund ID |
CreditMemoID | GUID | The field contains the credit memo ID as a link to the AccCreditMemo table |
Number | string | The field contains the reference number for the transaction |
Amount | decimal | The field indicates the total amount of the transaction |
Date | timestamp | The field contains the transaction date |
ContragentID | GUID | The field contains the contragent ID as a link to the AccContragent table |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
CurrencyID | GUID | The field contains the currency ID as a link to the AccCurrency table |
PaymentID | GUID | The field contains the payment ID as a link to the AccPayment table |
LocationID | GUID | The field contains the location ID as a link to the AccLocations table |
AccExpenseType | The table provides information about types of expenses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the title of the expenses type |
Active | boolean | The field indicates whether the expenses type is active or not |
AccLocation | The table provides information about physical locations of the company (for example one business can have two shops) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CompanyID | GUID | The field contains the company ID as a link to the AccCompany table |
OrigLocationID | string | The field contains the Entity ID from the original source system. It is the location ID |
LocationName | string | The field contains the name of the location |
LocationBusinessName | string | The field contains the business name of the location |
StartedAt | datetime | The field contains the date when the location was created |
OwnerFirstName | string | The field contains the first name of the owner |
OwnerLastName | string | The field contains the last name of the owner |
EmailLocation | string | The field contains the email of the location |
PhoneLocation | string | The field contains the phone number of the location |
WebsiteLocation | string | The field contains the website of the location |
BaseCurrencyID | GUID | The field contains the base currency ID as a link to the AccCurrency table |
AccEmployeeRate | The table provides information about employee wage rates | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the employee ID |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
PositionID | GUID | The field contains the employee position ID as a link to the AccEmployeePosition table |
RateType | string | The field contains the type of the rate. For example ANNUAL_SALARY, PER_PAY_PERIOD_SALARY, PIECEWORK_RATE, DAILY_RATE, HOURLY_RATE |
Date | datetime | The field contains the date when the rate is going to begin |
EmployeeHourlyRate | decimal | The field contains the hourly rate of the employee |
Description | string | The field contains the description of the employee rate |
Default | boolean | The field indicates whether this rate is the default one to apply on the employee |
Active | boolean | The field indicates whether this rate is active or not |
AnnualSalary | decimal | The field contains the annual salary for the employee |
WorkingHoursRerWeek | decimal | The field contains the working hours of the employee per week |
AccEmployeePosition | The table provides information about employee positions | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigPositionID | string | The field contains the Entity ID from original source system. It is the employee position ID |
PositionRateType | string | The field contains the rate type of the position |
PositionHourlyRate | decimal | The field contains the hourly rate value for the position |
PositionTitle | string | The field contains the title of the position |
PositionDescription | string | The field contains the description of the position |
StartDate | datetime | The field contains the date when the job is available to be assigned to a worker |
EndDate | datetime | The field contains the date when the job is no longer available to be assigned to a worker |
AccTaxTypes | The table provides information about types of taxes | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigTaxID | string | The field contains the Entity ID from original source system. It is the tax type ID |
TaxName | string | The field contains the name of the tax |
TaxRate | decimal | The field contains the rate value of the tax |
TaxDescription | string | The field contains the description of the tax rate |
AccEmployeeShift | The table provides information about employees shifts. The shift represents a complete workday for a certain employee | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
LocationID | GUID | The field contains the location ID as a link to the AccLocations table |
OrigShiftID | string | The field contains the Entity ID from original source system. It is the shift ID |
StartDate | datetime | The field contains the date and time when the shift was started |
EndDate | datetime | The field contains the date and time when the shift was ended |
ShiftHourlyRate | decimal | The field contains the value of the shift hourly rate |
Status | string | The field contains the status of the shift |
AccEmployeeShiftBreak | The table provides information about breaks in the shift | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ShiftID | GUID | The field contains the shift ID as a link to the AccEmployeeShift table |
OrigBreakID | string | The field contains the Entity ID from original source system. It is the Break ID |
StartDate | datetime | The field contains the start date and time of the break |
EndDate | datetime | The field contains the end date and time of the break |
BreakName | string | The field contains the title of the break |
Payable | boolean | The field indicates whether the break is paid or not |
AccCompanyStructure | The table provides information about the structure of the company | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the Company structure ID |
CompanyID | GUID | The field contains the company ID as a link to the AccCompany table |
Name | string | The field contains the name of the organization (structure) |
Level | string | The field contains the level number within the organizational structures hierarchy |
AccEmployeeJobHistory | The table provides information about jobs history and employees wordplaces | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the Job history ID |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
LocationID | GUID | The field contains the location ID as a link to the AccLocations table |
HireDate | datetime | The field contains the date when the employee was hired for the job |
Title | string | The field contains the title for the position |
Primary | boolean | The field indicates whether this is the employee’s primary job. The value will be set to true unless an existing job exists for this employee. |
Rate | string | The field contains the rate for the current compensation |
PaymentUnit | string | The field contains the payment unit for the current compensation |
CurrentCompensationID | int | The field contains the ID of the currently active compensation |
AccPayroll | The table provides information on payrolls | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the Payroll ID |
StartDate | datetime | The field contains the date of the beginning of accrual of a salary |
EndDate | datetime | The field contains the end date of the salary accrual |
PayrollDeadline | datetime | The field contains the deadline for the payroll to be run in order for employees to be paid on-time |
CheckDate | datetime | The field contains the date on which employees will be paid for this payroll |
Processed | boolean | The field indicates whether the payroll has been successfully run or not. If it has, you should consider it ‘frozen’, as any attempts to update its data will be rejected. Note that passing the payroll_deadline does not guarantee that the payroll has been processed; running late payrolls is not uncommon. For example, Gusto users may choose to run payroll before the payroll_deadline |
CompanyDebit | decimal | The field contains the total company debit for the payroll |
Reimbursements | decimal | The field contains the total reimbursements for the payroll |
NetPay | decimal | The field contains the net pay amount for the payroll |
EmployerTaxes | decimal | The field contains the amount of employer paid taxes for the payroll |
EmployeeTaxes | decimal | The field contains the amount of employee paid taxes for the payroll |
Benefits | decimal | The field contains the amount of company contributed benefits for the payroll |
EmployeeBenefitsDeductions | decimal | The field contains the amount of employee deducted benefits for the payroll |
AccPayrollDetail | The table provides information about payroll details | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
PayrollID | GUID | The field contains the payroll ID as a link to the AccPayroll table |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
GrossPay | decimal | The field contains the employee’s gross pay, only available for processed payrolls |
NetPay | decimal | The field contains the employee’s net pay, only available for processed payrolls |
PaymentMethod | string | The field contains the compensation payment method: Direct Deposit, Check,which are available only for processed payrolls |
CurrencyID | GUID | The field contains the currency ID as a link to the AccCurrency table |
PayDate | datetime | The field contains the date of the payroll accrual |
AccPayrollDetailCompensation | The table provides information on payroll detail compensation | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
PayrollID | GUID | The field contains the payroll ID as a link to the AccPayroll table |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
AccPayrollDetailsID | GUID | The field contains the account payroll detail ID as a link to the AccPayrollDetail table |
EmployeeCompensationType | string | The field contains the type of the employee compensation |
Name | string | The field contains the title of the employee compensation |
Amount | decimal | The field contains the amount of the employee compensation |
JobID | GUID | The field contains the job ID as a link to the AccEmployeeJobHistory table |
Hours | decimal | The field contains information about compensation hours |
Rate | decimal | The field contains the rate amount that will be applied for this component. Used in conjunction with Hours or Units. |
Units | decimal | The field contains the number of units that will be applied for this component. Used in conjunction with rate. |
CompensationMultiplier | decimal | The field contains the compensation multiplier data |
EmployeeDeduction | decimal | The field contains the deductions of the employee |
CompanyContribution | decimal | The field contains the contribution of the company |
Imputed | boolean | The field indicates whether the compensation is imputed or not |
Employer | boolean | The field indicates whether the compensation is paid by the employer or not |
AccEmployeeDeductions | The table provides information about salary deductions | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the Deductioin ID in our case |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
Name | string | The field contains the deduction header |
CalculationType | string | The field contains the type of calculation that will be applied. For example FLAT_DOLLAR_AMOUNT, PERCENTAGE, RATE_X_UNITS: This type is used to apply the rate to units and needs the BaseID calculation to specify what to apply the rate to. RATE_X_HOURS: This type is used to apply a rate to hours and requires BaseID calculation to specify what to apply the rate to |
CalculationBase | string | The field contains bases for calculating worker compensations that are not FLAT_DOLLAR_AMOUNT, and allow you to determine to what the calculation is applied when the amount is determined at time of payment |
Value | decimal | The field contains a number that is used to determine the value applied to the calculation type |
StartDate | datetime | The field contains the date which this pay component will start to be applied during the payruns |
Frequency | string | The field contains which check(s) within the payrun that the paycomponent will be applied to. For example FIRST_CHECK, EVERY_CHECK |
Active | boolean | The field indicates whether or not this garnishment is currently active |
CourtOrdered | boolean | The field indicates whether or not this garnishment was court ordered |
Times | int | The field shows how many times to apply this garnisment. Optional (and will be ignored) if recurring is set to true |
AnnualMaximum | decimal | The field contains the maximum deduction per annum. Null indicates no annual limit |
PayPeriodMaximum | decimal | The field contains the maximum deduction per pay period. Null indicates no annual limit |
CurrencyID | GUID | The field contains the currency ID as a link to AccCurrency table |
AccBenefit | The table provides information about benefits | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | int | The field contains the Entity ID from original source system. It is the Benefit ID |
Name | string | The field contains the title of the benefit |
Description | string | The field contains the description of the benefit |
AccCompanyBenefit | The table provides a link between companies and benefits | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Origid | int | The field contains the Entity ID from original source system. It is the CompanyBenefit ID |
BenefitID | GUID | The field contains the benefit ID as a link to the AccBenefit table |
CompanyID | GUID | The field contains the company ID as a link to the AccCompany table |
Active | boolean | The field indicates whether the link is active |
Description | string | The field contains the description of the link |
AccEmployeeBenefit | The table provides a link between employees and company benefits | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the EmployeeBenefit ID |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
CompanyBenefitsID | GUID | The field contains the company benefit ID as a link to the AccCompanyBenefit table |
Active | boolean | The field indicates whether the link is active |
EmployeeDeduction | decimal | The field contains the value of the employee’s deduction |
EmployeeDeductionAnnualMaximum | decimal | The field contains the annual maximum of the employee’s deduction |
CompanyContribution | decimal | The field contains the contribution of the company |
CompanyContributionAnnualMaximum | decimal | The field contains the annual maximum contribution of the company |
LimitOption | decimal | The field contains the limit option |
CoverageAmount | decimal | The field contains the coverage amount |
DeductionReducesTaxableIncome | decimal | The field contains deductions that reduce taxable income |
CoverageSalaryMultiplier | decimal | The field contains the coverage salary multiplier |
AccLaborGroupType | The table provides the list of labor group types: location, department, position, project, so on. | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the laborgrouptype ID |
Name | string | The field contains the name of the labor group type |
AccLaborGroup | The table provides the list of labor groups for each of the labour group types | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the Labor group ID |
Name | string | The field contains the name of the labor group |
Code | string | The field containsthe code of the labor group |
LaborGroupTypeID | GUID | The field contains the labor group type ID as a link to the AccLaborGroupType table |
AccEmployeeLaborGroupLink | The table provides a link between labour groups and employees | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
LaborGroupID | GUID | The field contains the labor group ID as a link to the AccLaborGroup table |
AccTimeDurationType | The table provides data about PTO vacation types, time attendance types | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the Time duration type ID |
Name | string | The field contains the name of the time duration type |
Status | string | The field contains the status of the time duration type |
Group | string | The field contains the time duration groups: vacation, work |
AccEmployeeTimeDuration | The table provides the list of PTO vacation, time attendance and other time duration | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system. It is the Employee time durarion ID |
TimeDurationTypeID | GUID | The field contains the time duration type ID as a link to the AccTimeDurationType table |
Status | string | The field contains the status of the employee time duration type |
EmployeeID | GUID | The field contains the employee ID as a link to the AccEmployee table |
CreatedDate | datetime | The field contains the date when the employee time duration was created |
StartDate | datetime | The field contains the start date of the time duration |
EndDate | datetime | The field contains the end date of the time duration |
DurationValue | decimal | The field contains a value of vacation or working hours/days |
DurationType | string | The field contains the list of duration types: hour, day |
ApprovedDate | datetime | The field contains the date of approval |
Comment | string | The field contains the vacation reason or other information |
DenyReason | string | The field contains the reason for denial of vacation |
Approved | boolean | The field indicates whether the time duration is approved or not |
Overnight | boolean | The field contains overnight data (true/false) |
State | string | The field contains time duration state |
AccEmail | The table provides the information about emails | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
SenderAddress | string | The field contains the email address of the sender |
SenderName | string | The field contains the name of the sender |
RecipientAddress | string | This field contains the email address of the recipient |
RecipientName | string | This field contains the name of the recipient |
Subject | string | This field contains the subject of the email |
Body | string | This field contains the text message of the email |
MatterID | GUID | The field contains the matter ID as a link to the AccMatter table |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
Date | datetime | This field contains the date when the statistics was obtained |
AccEvent | The table provides the information about events | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Event ID from original source system |
Name | string | The field contains the name of the event |
MatterID | GUID | The field contains the matter ID as a link to the AccMatter table |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
Subject | string | The field contains the subject of the event |
Street | string | The field contains the street (location) of the event |
Description | string | The field contains the description of the event |
StartDate | datetime | The field contains the date when the event was started |
EndDate | datetime | The field contains the date when the event was ended |
IsAllDay | boolean | The field indicates whether the event continues all day |
СreatedAt | datetime | The field contains the date when the event was created |
UserID | GUID | The field contains the user ID as a link to the AccEmployee table |
ContactID | GUID | The field contains the contact ID as a link to the AccContact table |
AccMatter | The table provides the information about matters at the law firm | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Event ID from original source system |
Name | string | The field contains the name of the matter |
Number | string | The field contains the number of matters |
OpenDate | datetime | The field contains the date when the matter was opened |
CloseDate | datetime | The field contains the date when the matter was closed |
Status | string | The field contains the status of the matter |
UserID | GUID | The field contains the user ID as a link to the AccEmployee table |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
Price | string | The field contains the price (rate) of the matter |
СreatedAt | datetime | The field contains the date when the matter was created |
AccTask | The table provides the information about tasks | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Event ID from original source system |
AccountID | GUID | The field contains the account ID as a link to the AccAccount table |
MatterID | GUID | The field contains the matter ID as a link to the AccMatter table |
Subject | string | The field contains the status of the task |
Status | string | The field contains the status of the task |
DueDate | datetime | The field contains the due date of the task |
UserID | GUID | The field contains the user ID as a link to the AccEmployee table |
ContactID | GUID | The field contains the contact ID as a link to the AccContact table |
Stage | string | The field contains the stage, priority of the task |
CreatedDate | datetime | The field contains the date when the task was created |
AccTimeEntry | The table provides the information about time entries | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Event ID from original source system |
IsBilled | boolean | The field indicates whether the time entry is billed |
IsBillable | boolean | The field indicates whether the time entry billable or not billable |
StatusName | string | The field contains the status name of the time entry |
Date | datetime | The field contains the date for the time entry |
Quantity | string | The field contains the number of hours worked on the time entry |
Amount | string | The field contains the hourly rate for the time entry |
Description | string | The field contains the description of the time entry |
Accountid | GUID | The field contains the account ID as a link to the AccAccount table |
MatterID | GUID | The field matter the user ID as a link to the AccMatter table |
UserID | GUID | The field contains the user ID as a link to the AccEmployee table |
ItemID | GUID | The field contains the item ID as a link to the AccItems table |
CreatedAt | datetime | The field contains the date when the time ectry was created |
eCommerce DB Schema contains information from the client’s eCommerce systems. Data from various systems such as EBay, Amazon, OLX, Rozetka, PromUA, BigCommerce, etc. being structured and placed at a single structure to facilitate the following analysis. The schema consists of such entities EcomCompany, EcomManager, EcomItems, EcomProductType, EcomAttributes. Please see below to find out the complete list of schema’s entities. | ||
TABLE/FIELDS | DATA TYPES | DESCRIPTION |
EcomCompany | The table provides main information about the company | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the company |
BusinessModel | string | The field contains the business model of the company |
OwnerFName | string | The field contains the first name of the company’s owner |
OwnerLName | string | The field contains the last name of the company’s owner |
StoreUrl | string | The field contains the Url of a store owned by the company |
Domain | string | The field contains the domen name owned by the company |
string | The field contains the email of the company | |
Phone | string | The field contains the phone number of the company |
Status | string | The field contains the status of the company |
Skype | string | The field contains the skype name of the company’s owner |
Currency | string | The field contains the currency by default of the company |
Industry | string | The field contains the industry of the company |
EcomManager | The table provides information about managers | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Fname | string | The field contains the first name of the manager |
Lname | string | The field contains the last name of the manager |
string | The field contains the email of the manager | |
Phone | string | The field contains the phone number of the manager |
Status | string | The field contains the status of the manager |
Skype | string | The field contains the skype of the company |
Role | string | The field contains the role of the manager |
RoleDescr | string | The field contains the role decriptions of the manager |
MarketplaceID | GUID | The field contains the marketplace ID as a link to the EcomMarketplace table |
MembersCount | integer | The field contains the number of contacts |
NumberOfDeals | integer | The field contains the number of deals |
EcomItems | The table provides information about items | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the item |
AlternativeName | string | The field contains the alternative name of the item |
Article | string | The field contains the article of the item |
Price | decimal | The field contains the price of the item |
SalePrice | decimal | The field contains the sale price of the item |
AvgCost | decimal | The field contaiins the current average cost of the inventory in stock for the item |
Type | string | The field contains the type of the item |
CatalogID | string | The field contains the catalog ID for particular item |
GroupID | string | The field contains the group ID for particular Item |
Status | string | The field contains the status of the item |
Description | string | The field contains the description of the item |
Quantity | decimal | The field contains the total quantity of the item |
CountDate | datetime | The field contains the the date/time at which the count was made |
Condition | string | The field contains the value indicates the condition of the item |
EcomCategoryID | GUID | The field contains the category ID as a link to the EcomCategories table |
ReviewAverage | decimal | The field contains the average number of reviews |
ReviewCount | decimal | The field contains the number of reviews |
Currency | string | The field contains the currency of the item |
MarketplaceID | GUID | The field contains the marketplace ID as a link to the EcomMarketplace table |
SalesChannelName | string | The field contains the product availability at sales channels |
ProductID | string | The field contains the additional original object ID in the source system |
Costof | decimal | The field contains the cost of production |
ItemSetID | GUID | The field contains the set ID as a link to the EcomItemSet table |
BrandID | GUID | The field contains the brand ID as a link to the EcomBrand table |
MetafieldID | GUID | The field contains the metafield ID as a link to the EcomMetafield table |
TypeID | GUID | The field contains the type ID as a link to the EcomProductType table |
AttributeID | GUID | The field contains the attribute ID as a link to the EcomAttributes table |
SupplierID | GUID | The field contains the supplier ID as a link to the EcomContragent table |
TaxClassID | GUID | The field contains the tax class ID as a link to the EcomTax table |
OnSale | boolean | The field indicates whether the item is on sale |
Location | GUID | The field contains the location ID as a link to the EcomLocation table |
PriceWithTax | decimal | The field contains the price with the tax of the item |
SpecialPriceWithTax | decimal | The field contains the special price with the tax of the item |
CreateDate | datetime | The field contains the date when the item was created |
UpdateDate | datetime | The field contains the date when the item was updated at the last time |
EcomProductType | The table provides information about product types | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Title | string | The field contains the name of the product type |
AttributeID (ArticleID) | GUID | The field contains the attribute ID as a link to the EcomAttributes table |
EcomAttributes | The table provides information about product attributes | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Title | string | The field contains the title of the attribute (For example: Camera-Nikon) |
DefaultValue | string | The field contains the default value of the attribute |
TypeID | GUID | The field contains the type ID as a link to the EcomProductType table |
EcomItemSet | The table provides information about item sets | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
CreatedAt | datetime | The field contains the date when the item set was created |
Name | string | The field contains the internal name of the item set |
OptionsID | string | The field contains the ID of the product option |
OptionsName | string | The field contains the name of the product option |
ValuesID | string | The field contains the ID of the set option value |
ValuesName | string | The field contains the name of the set option value |
Price | decimal | The field contains the price of the product option |
Quantity | decimal | The field contains the quantity in stock of the product option |
Sold | decimal | The field contains how many times the product option has been sold |
Item | GUID | The field contains the item ID as a link to EcomItem table |
EcomOrders | The table provides information about orders made by customers | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Createdate | datetime | The field contains the date when the order was created |
EntityType | string | The field contains the type of the order |
OrderTypeID | GUID | The field contains the order type ID as a link to the EcomOrderStatuses table |
Amount | decimal | The field contains the order amount |
Cost | decimal | The field contains the order amount + delivery + tax + fees |
CostWithDiscount | decimal | The field contains the order amount + delivery + tax + fees – discount |
TotalQuantity | decimal | The field contains the total quantity in the order |
StatusGeneralID | GUID | The field contains the status general ID as a link to the EcomOrderStatuses table |
StatusFinancialID | GUID | The field contains the status financial ID as a link to the EcomOrderStatuses table |
StageID | GUID | The field contains the status financial ID as a link to the EcomOrderStatuses table. Stage of deal workflow |
Completed | boolean | The field indicates whether the order is completed? |
CompleteTime | datetime | The field contains the time when the order was completed |
Currency | string | The field contains the currency of the order |
ContragentID | GUID | The field contains the contragent ID as a link to the EcomContragent table |
MarketplaceID | GUID | The field contains the marketplace ID as a link to the EcomMarketplace table |
OrderNumber | string | The field contains the number of the order |
OrderChannel | string | The field contains the channel of the order |
ChannelName | string | The field contains the name of the order channel |
LocationID | GUID | The field contains the location ID as a link to the EcomLocation table |
UserAgent | string | The field contains the user agent of the customer |
PriceExcl | decimal | The field contains the price with excluded tax of the order |
PriceIncl | decimal | The field contains the price included tax of the order |
InvoiceID | GUID | The field contains the invioce ID as a link to the EcomInvoices table |
ShipmentID (ShipmentID) | GUID | The field contains the shipment ID as a link to the EcomShipments table |
ProductID | GUID | The field contains the products ID as a link to the EcomItems table |
MetafieldID | GUID | The field contains the metafields ID as a link to the EcomMetafield table |
ShipmentID (DeliverymetodID) | GUID | The field contains the shipment ID as a link to the DeliveryMethod table |
IsDiscounted | boolean | The field indicates whether the order is discounted |
Voided | boolean | The field indicates whether the sale is voided |
VoidedTime | datetime | The field contains the date/time when the void happened at |
Balance | string | The field contains the total order minus calcPayments value |
EmployeeID | GUID | The field contains the employee ID as a link to the EcomManager table |
QuoteID | GUID | The field contains the quote ID as a link to the EcomQuote table |
RegisterID | GUID | The field contains the register ID as a link to the EcomRegister table |
DivisionID | GUID | The field contains the division ID as a link to the EcomCompany table |
Discount | decimal | The field contains the total discount value applied to the order |
CancelReason | string | The field contains the reason of cancellation |
СancelledAt | datetime | The field contains the date/time when the cancellation happened at |
ClosedTime | datetime | The field contains the date when the order was closed |
CurrencyRate | decimal | The field contains the exchange rate of the invoice currency to the system’s default currency |
RefundID | GUID | The field contains the refund ID as a link to the EcomRefunds table |
CouponDiscount | decimal | The field contains the discount of the coupon |
GiftCertificateAmount | decimal | The field contains the gift certificat amount |
ExternalSource | string | The field contains the external system data |
EcomQuote | The table provides information about uncompleted sales which are saved as a quote for a customer | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
IssueDate | string | The field contains the date/time when the quote was issued |
EmployeeID | datetime | The field contains the employee ID as a link to the EcomManager table |
SaleID | GUID | The field contains the sale ID as a link to the EcomOrder table |
EcomOrderStatuses | The table provides information about order statuses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the status name: “open”, “closed”, “cancelled”, “pending”, “authorized”, “partially_paID “, “paID “, “partially_refunded”, “refunded”, “voided” |
TypeID | string | The field contains the type id of the order |
TypeName | string | The field contains the type name of the order |
SystemTypeID | string | The field contains the system type id of the order |
EcomOrderDetails | The table provides information about order details | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrderID | GUID | The field contains the order ID as a link to the EcomOrders and EcomShipment tables |
PurchaseOrderID | GUID | The field contains the purchase order ID as a link to the EcomCompanyPurchase table |
OriginaltID | string | The field contains the unique numeric |
EcomItemID | GUID | The field contains the itemID as a link to the EcomItems table |
SupplierTitle | string | The field contains the title of the supplier |
BrandTitle | string | The field contains the brand title of the product |
Type | string | The field contains the type of the item |
Title | string | The field contains the title of the item |
Quantity | decimal | The field contains the quantity of the item |
Measureunit | decimal | The field contains the the unit measurement of the product |
TotalPrice | decimal | The field contains the total price per item |
Currency | string | The field contains the item currency in the order |
TaxRate | decimal | The field contains the the taxrate of the product |
TaxRatesName | string | The field contains the tax rates name |
TaxRatesAmount | decimal | The field contains the tax rates amount |
ArticleCode | string | The field contains the the custom articleCode of the ordered product variant |
BasePriceExcl | decimal | The field contains the the price per product excluding tax |
BasePriceIncl | decimal | The field contains the the price per product including tax |
PriceExcl | decimal | The field contains the the total price of the products excluding tax |
PriceIncl | decimal | The field contains the the total price of the products including tax |
DiscountExcl | decimal | The field contains the the discount on the product excluding tax |
DiscountIncl | decimal | The field contains the the discount on the product including tax |
CheckedIn | decimal | The field contains the the quantity that has been checked in (added to inventory) |
NumReceived | decimal | The field contains the the quantity that has been received but not checked in (hasn’t been added to inventory yet). |
RefundID | GUID | The field contains the refund ID as a link to the EcomRefund table |
Profit | decimal | The field contains the difference between the purchase and sale prices |
ItemOptionID | GUID | The field contains the item option ID as a link to the EcomItemSet table |
isRevenue | boolean | The field indicats whether the item should be counted as revenue, for example gift cards and donations would not |
TransactionID | GUID | The field contains the transaction ID as a link to the EcomTransacton table |
EcomInvoices | The table provides information about invoices to customers | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
ContractNumber | string | The field contains the contract number of the invoice |
Number | string | The field contains the invoice number |
Filename | string | The field contains the file name of the invoice |
DateOfInvoice | datetime | The field contains the date when the invoice was created |
PriceExcl | decimal | The field contains the price excluding the tax |
Amount | decimal | The field contains the amount of the invoice |
Type | string | The field contains the type of the invoice |
Status | string | The field contains the status of the invoice (payd, not paid ..) |
InvoiceID | GUID | The field contains the invoice ID belonging to a credit note |
IsCreditNote | boolean | The field indicates whether the invoice is a credit note |
CreditNote | string | The field contains the credit nothe of the invoice |
OrderID | GUID | The field contains the order ID as a link to the EcomOrders table |
CustomerID | GUID | The field contains the customer ID as a link to the EcomContragent table |
ItemID | GUID | The field contains item ID as a link to the EcomInvoicesItem table |
MetafieldID | GUID | The field contains the metafield ID as a link to the EcomMetafield table |
EcomTransactionTypes | The table provides information about transaction types | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the transaction type: SALE;REFUND;CREDIT;DISPUTE; CHARGE, RESERVE, ADJUSTMENT, DEBIT, PAYOUT, PAYOUT_FAILURE, PAYOUT_CANCELLATION |
Description | string | The field contains the description of the transaction type |
RefundAsPaymentTypeID | string | The field contains the foreign key for the payment type that this payment refunds as |
EcomTransactions | The table provides information about transactions | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
OrderID | GUID | The field contains the order ID as a link to the EcomOrders and EcomShipment tables |
ProductID | GUID | The field contains the product ID as a link to the EcomItems table |
PurchaseID | string | The field contains the purchase ID as a link to the EcomCompanyPurchase table |
OfferID | GUID | The field contains the offer ID as a link to the EcomOffer table |
TransactionTypeID | GUID | The field contains the transaction type ID as a link to the EcomTransactionTypes table |
Price | decimal | The field contains the price of the transaction |
Quantity | decimal | The field contains the quantity of the transaction |
Cost | decimal | The field contains the cost of the transaction |
Currency | string | The field contains the currency of the transaction |
SumInGrayChanged | decimal | The field contains the amount which should be paid to the platform (change) |
SumInGray | decimal | The field contains the amount which should be paid to the platform |
CurrentBalance | decimal | The field contains the current company balance for fee payments |
TransactionTs | datetime | The field contains the transaction date time |
Status | string | The field contains the status of the transaction |
PaymentMethod | string | Information about the payment method used for this transaction |
RegisterID | GUID | The field contains the register ID as a link to the EcomRegister table |
EmployeeID | GUID | The field contains the employee ID as a link to the EcomManager table |
ServiceType | string | The field contains the service type transaction (for example: EatIn, Takeaway, Delivery) |
Discount | decimal | The field contains the value of the discount |
IsTest | boolean | The field indicates whether the transaction is a test transaction or not. |
EcomBalances | The table provides balances information | |
BalanceDate | datetime | The field contains the date of the balance |
Balance | decimal | The field contains the balance |
SumInGray | decimal | The field contains the amount which should be paid to the platform |
SubscriptionBalance | decimal | The field contains the subscription cost for platform service |
Currency | string | The field contains the currency of balance |
Bonus | decimal | The field contains the amount of the bonus |
Refund | decimal | The field contains the amount of the refund |
EcomCategories | The table provides information about item categories | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
ParentID | string | The field contains the link to the parent category ID |
Name | string | The field contains the name of the category |
CreatedAt | datetime | The field contains the position of the category in the list |
Type | string | The field contains the type of the category |
Sorting | string | The field contains the sort order in the category |
Description | string | The field contains the description of the category |
ProductID | GUID | The field contains the product ID as a link to the EcomItem table |
EcomItemsCategoriesLink | The table provides a link between items and categories | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
EcomItemID | GUID | The field contains the item ID as a link to the EcomItems table |
EcomCategoryID | GUID | The field contains the category ID as a link to the EcomCategories table |
EcomDocuments | The table provides information about company documents | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Type | string | The field contains the type of the document (for example, passport….) |
PreviewWebPath | string | The field contains the preview web path |
EcomContragent | The table provides information about contragents information | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
FirstName | string | The field contains the first name of the contragent |
SecondName | string | The field contains the second name of the contragent |
LastName | string | The field contains the last name of the contragent |
Mobile | string | The field contains the mobile phone number of the contragent |
Phone | string | The field contains the cellular phone number of the contragent |
string | The field contains the email of the contragent | |
Skype | string | The field contains the skype of the contragent |
ContragentType | string | The field contains the type of the contragent |
CompanyName | string | The field contains the company name of the contragent |
Balance | decimal | The field contains the balance of the contragent’s accounts |
Gender | string | The field contains the gender of the customer |
BirthDate | datetime | The field contains the date of birth of the customer |
NationalID | decimal | The field contains the national ID entification number (BSN) of the customer |
CompanyCoCNumber | decimal | The field contains the company CoC number |
CompanyVatNumber | decimal | The field contains the company VAT number |
IsNewCustomer | boolean | The field indicates whether the customer is new or returning |
CreditAccountID | GUID | The field contains the credit account ID as a link to the EcomCreditAccount table |
CustomerTypeID | GUID | The field contains the customer type ID as a link to the EcomContragentGroup table |
TaxCategoryID | GUID | The field contains the tax categories ID as a link to the EcomTax table |
DiscountID | GUID | The field contains the discount ID as a link to the EcomOffer table |
CreateDate | datetime | The field contains the date when the customer was created |
State | string | The field contains the the state of the customer’s account with a shop |
IsPOSDefaultCustomer | string | The field indicates whether the customer is the default customer at the POS |
ContragentGroupID | GUID | The field contains the contragent group ID as a link to the EcomContragentGroup table |
Source | string | The field contains the source where the customer came |
Channel | string | The field contains the channel where the customer came |
EcomContragentGroup | The table provides information about contragent groups | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
CreatedAt | datetime | The field contains the date and time when the group was created |
Tittle | string | The field contains the name of the group |
CustomerID | GUID | The field contains the customer ID as a link to the EcomContragent table |
TaxCategoryID | GUID | The field contains the tax category ID as a link to the EcomTax table |
DiscountID | GUID | The field contains the discount ID as a link to the EcomOffer table |
EcomRating | The table provides information about company rating | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Delivery | decimal | The field contains the rating of the delivery |
DeliveryCost | decimal | The field contains the cost rating of the delivery |
Description | decimal | The field contains the description rating of the delivery |
Service | decimal | The field contains the service rating of the delivery |
NotRecommendedTotal | Int | The field contains the not recommended total number |
NotRecommendedUnique | int | The field contains the not recommended unique users |
RecommendedTotal | int | The field contains the recommended total number |
RecommendedUnique | int | The field contains the recommended unique users |
EcomOffer | The table provides information about the offer | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
OfferName | string | The field contains the name of the offer |
Description | string | The field contains the description of the offer |
ItemID | GUID | The field contains the item ID as a link to the EcomItems table |
Quantity | decimal | The field contains the quantity of the offer |
MinQuantity | decimal | The field contains the MinQuantity for enable offer |
Status | string | The field contains the status of the offer: UNPUBLISHED; PUBLISHED |
Type | string | The field contains the type of the discount (price, percentage) |
Price | decimal | The field contains the price of the offer |
DiscountPercentage | boolean | The field indicates whether the discount is a percentage? if true, then ‘persentage’ |
Discount value | decimal | The field contains the discount of the offer |
Currency | string | The field contains the currency of the offer |
ApplyTo | string | The field contains which part of the catalog the coupon applies to |
Shipment | string | The field contains if the coupon also applies to the cost of delivery |
CreateDate | datetime | The field contains the date when the offer was created |
EndDate | datetime | The field contains the date when the offer was ended |
EcomLocation | The table provides information about company locations: stores, warehouses, etc | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the location |
Type | string | The field contains the type of the location |
Phone | string | The field contains the phone number related to the location |
string | The field contains the email related to the location | |
Url | string | The field contains the site of the store |
Status | string | The field contains the statuses of the location: “ENABLED”; “DISABLED” |
LocationType | string | The field contains the type of the location: “Store”; “Warehouse”, “item_location”, “website” |
CountryID | GUID | The field contains the country ID as a link to the EcomCountry table |
Currency | string | The field contains the currency code of the location |
ItemID | GUID | The field contains the item ID as a link to the EcomItems table |
MarketplaceID | GUID | The field contains the marketplace ID as a link to the EcomMarketplace table |
BrandID | GUID | The field contains the brand ID as a link to the EcomBrand table |
CreateDate | datetime | The date and time when the location was created |
Code | string | The registration code of the location |
EcomOfferLocationLink | The table is a link between offers and locations | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OfferID | GUID | The field contains the offer ID as a link to the EcomOffer table |
LocationID | GUID | The field contains the location ID as a link to the EcomLocation table |
EcomShippingRates | The table provides information about company shopping rates | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the shipping rate |
DeliveryMethodID | GUID | The field contains the delivery methodID as a link to the DeliveryMethod table |
MaxQuantityPerPackage | string | The field contains the maximum quantity per package |
FIRAmount | string | The field contains the amount of the First Item Rate |
FIRCurrency | string | The field contains the currency of the First Item Rate |
NIRAmount | string | The field contains the amount of the Next Item Rate |
NIRCurrency | string | The field contains the currency of the Next Item Rate |
ShippingTimeFrom | timestamp | The field contains the start time of shipping |
ShippingTimeTo | timestamp | The field contains the end time of shipping |
EcomOrderID | GUID | The field contains the order ID as a link to the EcomOrders table |
EcomShipment | The table provides the shipment information | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
CreatedAt | datetime | The field contains the date and time when the shipment was created |
Number | decimal | The field contains the number of the shipment |
Status | string | The field contains the status of the shipment |
TrackingCode | decimal | The field contains the tracking code of the shipment |
CustomerID | GUID | The field contains the customer ID as a link to the EcomContragent table |
OrderID | GUID | The field contains the order ID as a link to the EconOrder table |
ProductID | GUID | The field contains the product ID as a link to the EcomOrderDetails table |
MetafieldID | GUID | The field contains the metafield ID as a link to the EcomMetafield table |
LocationID | GUID | The field contains the location ID as a link to the EcomLocation table |
Amount | decimal | The field contains the amount of the shipment |
InsuranceAmount | decimal | The field contains the insurance amount of the shipment |
ShipOption | string | The field contains the ship option or other additional information |
PackageID | GUID | The field contains the package ID as a link to the EcomPackage table (Ship package information) |
DeliveryMethodID | GUID | The field contains the delivery method ID as a link to the DeliveryMethod table |
EstimatedFulfillmentDate | datetime | The field contains the date of the estimated fulfillment |
ActualFulfillmentDate | datetime | The field contains the date of the actual fulfillment |
EstimatedFulfillmentDateStatus | string | The field contains the status of the shipment on estimated fulfillment date |
ServiceID | GUID | The field contains the service ID as a link to the EcomService table |
ManagerID | GUID | The field contains the manager ID as a link to the EcomManager table |
PackAmount | decimal | The field contains the amount of package |
EcomDeliveryMethod | The table provides information about delivery methods | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
NameDeliveryMethod | string | The field contains the name of the delivery method |
PaymentPolicy | string | The field contains the payment policy of the delivery method |
ShipRatConstr | string | The field contains the shipping rates constraints |
MaxQuantityPerPackage | int | The field contains the maximum quantity per package |
FIRmin | string | The field contains the minimum amount of the First Item Rate |
FIRmax | string | The field contains the maximum amount of the First Item Rate |
FIRcurrency | string | The field contains the currency of the First Item Rate |
NIRmin | string | The field contains the minimum amount of the Next Item Rate |
NIRmax | string | The field contains the maximum amount of the Next Item Rate |
NIRcurrency | string | The field contains the currency of the Next Item Rate |
ShippingTimeFrom | timestamp | The field contains the start time of shipping |
ShippingTimeTo | timestamp | The field contains the end time of shipping |
EcomOrderID | GUID | The field contains the order ID as a link to the EcomOrders table |
DeliveryService | string | The field contains the service method of the shipping company |
Type | string | The field contains the type of the shipping method |
FreeAbove | int | The field contains the minimum order value at which the delivery is free |
PaymentPriceExcl | decimal | The field contains the payment price excluding the tax |
PaymentPriceIncl | decimal | The field contains the payment price including the tax |
TaxID | GUID | The field contains the tax ID as a link to the EcomTax table |
ValueID | GUID | The field contains the value ID as a link to the EcomShippingmethodValue table |
Code | string | The field contains the code/number of the shipper or shipping method |
Amount | decimal | The field contains the amount of transportation or shipping method or other amounts |
EcomShippingmethodValue | The table provides information about values of shipping methods | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
WeightMinimum | int | The field contains the minimum weight of the shipment |
WeightMaximum | int | The field contains the maximum weight of the shipment |
PriceExcl | decimal | The field contains the price excluding the tax of the shipment |
PriceIncl | decimal | The field contains the price including the tax of the shipment |
EcomTax | The table provides information about taxes | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Rate | int | The field contains the tax rate, which always should be a float with precision 2 |
Tax2Rate | int | The field contains the second tax rate |
Title | string | The field contains the textual representation of the tax |
Title2 | string | The field contains the name for the second tax rate. For example ‘PST’. |
TaxClassID | string | The field contains the unique number ID entifier for the tax class |
TaxClassname | string | The field contains the name of the tax class |
EcomPayments | The table provides information about order payments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
OrderID | GUID | The field contains the order ID as a link to the EcomOrders table |
Type | string | The field contains the type of the payment |
Group | string | The field contains the group of the payment |
PaymentOperator | string | The field contains the name of the payment operator |
PaymentOperatorType | string | The field contains the type of the payment operator |
BalanceAmount | decimal | The field contains the additional amount field |
BalanceCurrency | string | The field contains the additional currency field |
TipAmount | decimal | The field contains the amount paid in tips |
ValueAmount | decimal | The field contains the amount of the payment |
ValueCurrency | string | The field contains the currency of the payment |
OccurredAt | timestamp | The field contains the date when the payment was made |
Status | string | The field contains the status of the payment |
GiftCardOrigID | string | The field contains the original ID of the gift card |
Source | string | The field contains the source of the payment |
PaymentTitle | string | The field contains the title of the payment |
PaymentIsPost | boolean | The field indicates whether the order will be paid after delivery |
PaymentTypeID | GUID | The field contains the payment type ID as a link to the EcomTransactionTypes table |
CcChargeID | GUID | The field contains the charge ID as a link to the EcomCCCharge table |
RegisterID | GUID | The field contains the register ID as a link to the EcomRegister table |
EmployeeID | GUID | The field contains the employee ID as a link to the EcomManager table |
CreditAccountID | GUID | The field contains the credit accountID as a link to the EcomCreditAccount table |
Offline | boolean | The field indicates whether the order will be paid offline |
CurrencyRate | decimal | The field contains the rate currency of the payment |
RefundID | GUID | The field contains the refund ID as a link to the EcomRefunds table |
TransactionID | GUID | The field contains the transaction ID as a link to the EcomTransaction table |
EcomRefunds | The table provides information about refunds | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
PaymentID | GUID | The field contains the PaymentID as a link to the EcomPayments table |
ItemID | GUID | The field contains the item ID as a link to the EcomItems table |
OrderID | GUID | The field contains the order ID as a link to the EcomOrders, EcomShipments tables |
Reason | string | The field contains the reason of the refund |
Status | string | The field contains the status of the refund |
CreatedAt | timestamp | The field contains the date of the refund |
TotalValueExcl | decimal | The field contains the total price excluding the tax |
TotalValueAmount | decimal | The field contains the amount of the refund |
TotalValueCurrency | string | The field contains the currency of the refund |
CustomerID | GUID | The field contains the customer ID as a link to the EcomContragent table |
DivisionID | GUID | The field contains the division ID as a link to the EcomCompany table |
Type | string | The field contains the types of returns/refunds (Regular, …) |
EcomRefundsDetails | The table provides information about refunds details | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
RefundID | GUID | The field contains the refund ID as a link to the EcomRefund table |
OriginalID | string | The field contains the Entity ID from original source system |
ItemID | GUID | The field contains the item ID as a link to the EcomItems table |
Quantity | decimal | The field contains the quantity of the refund |
SalePrice | decimal | The field contains the sale price of the refunded product |
BuyPrice | decimal | The field contains the purchase price of the refunded product |
ItemsProfit | decimal | The field contains the difference between purchase and sale prices |
DivisionID | GUID | The field contains the division ID as a link to the EcomCompany table |
EcomCountry | The table provides information about countries | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the country |
Alpha2 | string | The field contains the international country alpha-2 code |
Alpha3 | string | The field contains the international country alpha-3 code |
NumericCode | decimal | The field contains the international country numeric code |
EcomAddress | The table provides information about address (company, customer, supplier…) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CompanyID | GUID | The field contains the company ID as a link to the EcomCompany table |
ContragentID | GUID | The field contains the contragent ID as a link to the EcomContragent table |
LocationID | GUID | The field contains the location ID as a link to the EcomLocation table |
CountryID | GUID | The field contains the country ID as a link to the btable |
AddressDescr | string | The field contains the description of the address (e.g. Main address, Contact address, Mailing address…) |
ZipCode | string | The field contains the postal code |
Region | string | The field contains the region name |
City | string | The field contains the city name |
Street | string | The field contains the street name |
StreetNumber | string | The field contains the street number |
Floor | string | The field contains the floor number |
Apartment | string | The field contains the apartment number |
Comment | string | The field contains the comment about address |
AddressStr | string | The field contains the full address |
Type | string | The field contains the type of the address: legal; contact; main, billing, shipping …(place here new if need) |
Latitude | decimal | The field contains the latitude |
Longitude | decimal | The field contains the longitude |
EcomCampaign | The table provides information about promotion/addvertising campaigns | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
StartDate | datetime | The field contains the start date of the campaign |
EndDate | datetime | The field contains the end date of the campaign |
Name | string | The field contains the name of the campaign |
FundingModel | string | The field contains the funding model |
BID Percentage | decimal | The BID percentage in the address |
Status | string | The field contains the status of the address: DELETED; ENDED; ENDING_SOON; PAUSED; PENDING; RUNNING; SCHEDULED |
MarketplaceID | string | The field contains the marketplace Id/warehouse Id of the campaign |
EmailsAmount | integer | The field contains the total amount of the email campaigns |
SentNumbersAmount | integer | The field contains the number of individuals targeted by the campaign. For example, the number of emails sent. |
Type | string | The field contains the type of the campaign |
EntityType | string | The field contains the entity type of the campaign |
EcomCampaignStat | The table provides information about campaign statistics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the status type (delivered, open, click, bounced etc.) |
Value | int | The field contains the number of email by status (delivered, open, click, bounced etc.) |
CampaignID | GUID | The field contains the campaign ID as a link to the EcomCampaign table |
EcomTrafficStat | The table provides information about community statistic. General numbers | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
StatObjectID | GUID | The field contains the stat object ID as a link to the Object of statistics (EcomOffer, …) |
SocialStatTypeID | GUID | The field contains the social stat type ID as a link to the EcomStatType table |
Value | decimal | The field contains the value of the statistic |
PeriodType | string | The field contains the list of values: “day”; “month”; “week” |
DateFrom | datetime | The field contains the date from which the statistic was obtained |
DateTo | datetime | The field contains the date to which the statistic will be obtained |
EcomStatType | Dictionary | The table provides information about statistic types |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the statistic: “advert_views” “phone_views “users_observing” “messages_count” “dvert views” “Phone views” “Users observing” “Messages count” “Advert views count” “Phone views count” “Users observing” “Messages count” |
Title | string | The field contains the title of the statistic |
Description | string | The field contains the description of the statistic |
EcomPromotion | The table provides information about promotions | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the promotion |
DateFrom | datetime | The field contains the date from which the promotion is started |
DateTo | datetime | The field contains the date to which the promotion will be ended |
MarketplaceID | string | The field contains the marketplace ID for the promotion |
Status | string | The field contains the status of the promotion: SCHEDULED;RUNNING;PAUSED;DRAFT;ENDED;INVALID |
Amount | decimal | The field contains the amount of the promotion |
Type | string | The field contains the type of the promotion |
Balance | decimal | The field contains the balance of the promotion |
OrderID | GUID | The field contains the order ID as a link to the EcomOrder table |
EcomCompanyPurchase | The table provides information about company purchases | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
OrderID | string | The field contains the Order ID |
CreatedDate | datetime | The field contains the date when the transaction was created |
Paid Time | datetime | The field contains the paid time of the transaction |
ItemID | string | The field contains the ID of the original item |
ItemTitle | string | The field contains the title of the item |
ContragentID | GUID | The field contains the contragent ID as a link to the EcomContragent table |
TransactionTypeID | GUID | The field contains the transaction type ID as a link to the EcomTransactionTypes table |
Price | decimal | The field contains the total price of the purchase |
Quantity | decimal | The field contains the total quantity of the purchase |
Currency | string | The field contains the currency of the purchase |
Status | string | The field contains the status according to original system documentation |
complete | boolean | The field indicates whether the order is marked as completed |
PaymentMethod | string | The field contains the information about the payment method used for the transaction |
VendorID | GUID | The field contains the vendor ID as a link to the EcomContragent table |
ShopID | GUID | The field contains the shop ID as a link to the EcomCompany table |
ShipCost | decimal | The field contains the shipping costs which are the vendor adds to the order |
OrderLineID | GUID | The field contains the order line ID as a link to the EcomOrderDetails tables |
Type | string | The field contains the type of the purchase |
TypeDoc | string | The field contains the document type of the purchase |
EcomMarketplace | The table provides information about marketplaces | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
EcomCountryID | GUID | The field contains the default country code ID as a link to the EcomCountry table |
DomainName | string | The field contains the domain name of the marketplace |
Name | string | The field contains the name of the marketplace |
Currency | string | The field contains the currency code by default of the marketplace |
Language | string | The field contains the language code by default of the marketplace |
HasSellerSuspendedListings | boolean | The field indicates whether the seller has suspended the listing |
EcomReportList | The table provides information about reports | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the report |
SessionShareID | GUID | The field contains the current session ID as a link to the SysSessionShare table |
EcomColumnList | The table provides the description of reports columns | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the report column list |
ReportValueColName | string | The field contains the name of the column from the EcomReportValue table |
ReportListID | GUID | The field contains the report list ID as a link to the EcomReportList table |
SessionShareID | GUID | The field contains the current session ID as a link to the SysSessionShare table |
EcomReportValue | The table provides information about reports values | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ReportListID | GUID | The field contains the report list ID as a link to the EcomReportList table |
SessionShareID | GUID | The field contains the current session ID as a link to the SysSessionShare table |
Col01 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
Col02 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
Col03 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
Col04 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
Col05 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
Col06 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
Col07 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
Col08 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
Col09 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
Col10 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
Col11 | string | The field contains a value for a particular column, which is described in the EcomColumnList table |
EcomBrand | The table provides information about all brands in the shop | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
CreatedAt | datetime | The field contains the date and time when the brand was created |
Title | string | The field contains the name of the brand |
Content | string | The field contains the summary or description of the brand |
ProductID | GUID | The field contains the product ID as a link to the EcomItem table |
EcomMetafield | The table provides information about custom variables for various resources | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
CreatedAt | datetime | The field contains the date and time when the metafield was created |
OwnerType | string | The field contains the type of the owner |
OwnerID | string | The field contains the object ID of the owner |
Key | string | The field contains the unique key for describing the metafield |
Value | string | The field contains the value of the metafield |
EcomCreditAccount | The table provides credit account information | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the credit account |
Code | string | The field contains the gift card barcode for the gift card of the store |
CreditLimit | string | The field contains the limit of credit account |
GiftCard | boolean | The field indicates whether this account if gift card account |
CustomerID | GUID | The field contains the customer ID as a link to the EcomContragent table |
Balance | decimal | The field contains the balance at the credit account |
OrderID | GUID | The field contains the order ID as a link to the EcomOrders table |
EcomRegister | The table provides information about POSes and places where money is received | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name/description of the register |
Open | boolean | The field indicates whether the register is current opened |
OpenTime | datetime | The field contains the the date/time when the register was opened at |
OpenEmployeeID | GUID | The field contains the open employee ID as a link to the EcomManager table |
ShopID | GUID | The field contains the shop ID as a link to the EcomCompany table |
Calculated | decimal | The field contains the amount calculated by the system, consisting of payments that should be in the register for this type of payment |
RegisterCountID | string | The field contains a foreign ID for calculating the individual type of payment |
PaymentTypeID | GUID | The field contains the payment type ID as a link to the EcomTransactionTypes table |
EcomTransactionTypesID | GUID | The field contains the transaction type ID as a link to the EcomTransactionTypes table |
LocationID | GUID | The field contains the location ID as a link to the EcomLocation table |
PaymentServiceProvider | string | The field contains the provider of the service payment |
EcomCCCharge | The table provides information about credit card charges | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
GatewayTransID | string | The field contains the ID assigned to the credit card payment by the processing gateway |
Xnum | string | The field contains the last four digits of the customer’s credit card number |
Voided | boolean | The field indicates whether the charge has been voided within the gateway |
Refunded | decimal | The field contains the amount that has been refunded so far |
Amount | decimal | The field contains the original amount of the charge |
Exp | string | The field contains the expiration date of the credit card |
Declined | boolean | The field indicates whether the charge was declined by the gateway |
SaleID | GUID | The field contains the sale ID as a link to the EcomOrders table |
SalePaymentID | GUID | The field contains the sale payment ID as a link to the EcomPayments table |
EntryMethod | string | The field contains the card method |
CardType | string | The field contains the card type: Visa, Mastercard, etc |
CardholderName | string | The field contains the name displayed on the communication key card |
EcomStocks | The table provides information about stocks | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ItemID | GUID | The field contains the item ID as a link to the EcomItems table |
SupplierID | GUID | The field contains the supplier ID as a link to the EcomContragent table |
SalePrice | decimal | The field contains the sale price |
BuyPrice | decimal | The field contains the purchase price |
ItemsProfit | decimal | The field contains the difference between the purchase and sale prices |
Quantity | decimal | The field contains the stock quantity |
DevisionID | GUID | The field contains the division ID as a link to the EcomCompany table |
Cost | decimal | The field contains the cost of the product. Cost = purchase price + shipping cost. If you multiply this field by amountInStock, you can get the total current cost of inventory. |
EcomService | The table provides information about carrier services | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the service |
Code | string | The field contains the code/number of the service |
EcomManagerCustomer | The table provides a link between managers/users and customers | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ManagerID | GUID | The field contains the manager ID as a link to the EcomManager table |
CustomerID | GUID | The field contains the customer ID as a link to the EcomContragent table |
CreateDate | datetime | The field contains the date when the customer was created |
EcomPackage | The table provides information about packages | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the package name |
DeliveryMethodID | GUID | The field contains the delivery method ID as a link to the EcomDeliveryMethod table |
EcomFulfillment | The table provides information about orders fulfillment | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
CreateDate | datetime | The field contains the date and time when the shipment was created |
Status | string | The field contains the status of the fulfillment |
TrackingCode | string | The field contains the tracking code of the fulfillment |
OrderID | GUID | The field contains the order ID as a link to the EcomOrder table |
Amount | decimal | The field contains the fees of the fulfillment |
Provider | string | The field contains the provider of the fulfillment (code, name) |
DeliveryMethodID | GUID | The field contains the delivery method ID as a link to the DeliveryMethod table |
ShipDate | datetime | The field contains the date of the shipment |
DeliveryDate | datetime | The field contains the date of the delivery |
ServiceID | GUID | The field contains the service ID as a link to the EcomService table |
ManagerID | GUID | The field contains the manager ID as a link to the EcomManager table |
EcomMessage | The table provides information about messages | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
CreateDate | datetime | The field contains the date and time when the message was created |
Subject | string | The field contains the subject of the message |
SenderID | GUID | The field contains the sender ID as a link to the EcomEmailSender table |
EcomEmailSender | The table provides information about email senders | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the sender |
string | The field contains the email of the sender | |
Status | string | The field contains the status of the sender |
eMailing DB Schema contains information from the client’s eMailing systems. Data from various systems such as MailChimp, Maileon etc being structured and placed at a single structure to facilitate the following analysis. The schema consists of such entities EmAddressBook, EmEmail, EmList, EmSender etc. Please see below to find out the complete list of schema’s entities. | ||
TABLE/FIELDS | DATA TYPES | DESCRIPTION |
EmAddressBook | The table provides information about company address books | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the address book |
AllEmailQty | decimal | The field contains the number of all emails in the book |
InactiveEmailQty | decimal | The field contains the number of inactive emails in the book |
CreateDate | timestamp | The field contains the date when the address book was created |
AddrBookStatusID | GUID | The field contains the address book status ID as a link to the EmDictAddrBookStatus table |
MembersCount | int | The field contains the number of active members in the list |
string | The field contains the email of the address book | |
EmDictAddrBookStatus | The table provides information about statuses of address books | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Value | decimal | The field contains the value of the status |
Description | string | The field contains the status description |
EmEmail | The table provides information about lists of emails | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
string | The field contains the email of a contact | |
Phone | string | The field contains the phone of the contact |
EmailStatusID | GUID | The field contains the email status ID as a link to the EmDictEmailStatus table |
AddressBooksID | GUID | The field contains the address book ID as a link to EmAddressBooks table |
CurrencyCode | string | The field contains the three-letter ISO 4217 code for the currency that the store accepts |
Name | string | The field contains the name of the contact (first name, last name) |
Organization | string | The field contains the name of the organization |
City | string | The field contains the name of the city |
Region | string | The field contains the name of the region |
Country | string | The field contains the name of the country |
Type | string | The field contains the type, method of the contact |
EmEmailStats | The table provides information about mailing statistics | |
EmailID | GUID | The field contains the email ID as a link to the EmEmails table |
DictEmailsMetrikID | GUID | The field contains the dictionary email metrik ID as a link to the DictEmailsMetrik table |
Value | string | The field contains the value of the statistic |
Description | string | The field contains the description of the statistic |
Date | timestamp | The field contains the date of the event |
EmDictEmailMetrik | The table provides information about email metric statistics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Description | string | The field contains the list of the metrik description: AvgOpenRate, AvgClickRate, NumberOfOrders, TotalRevenue |
IntegrationID | GUID | The field contains the integration ID as a link to the EmIntegration table |
IntegratioName | string | The field contains the integration name description: (For example: API, klaviyo, Shopify) |
IntegrationCategory | string | The field contains the integration category description: Internal, eCommerce, API |
EmDictEmailStatus | The table provides information about email statuses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Value | decimal | The field contains the value of the status |
Description | string | The field contains the description of the status: Subscribed, Unsubscribed, Cleaned, Pending, Transactional, Archived |
EmEmailVariablesDict | The table provides information about specific email values | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
EmailID | GUID | The field contains the email ID as a link to the EmEmail table |
Key | string | The field contains the key of the address book |
Value | string | The field contains the value of the address book |
EmList | The table provides information about list/audience (in case one account has several workplaces) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the campaign name of the list |
Company | string | The field contains the company name of the list |
FromName | string | The field contains the default name from the list |
FromEmail | string | The field contains the default email from the list |
Language | string | The field contains the default language of the list |
DateCreated | timestamp | The field contains the date when the list was created |
AddressBookID | GUID | The field contains the address book ID as a link to the EmAddressBook table |
EmListsStatistic | The table provides information about mailing lists/audience statistics | |
ListID | GUID | The field contains the list ID as a link to the EmList table |
DictListMetrikID | GUID | The field contains the dictionary list metrik ID as a link to the DictListMetrik table |
Value | string | The field contains the value of the statistic |
Explain | string | The field contains the description of the statistic |
EmDictListMetrik | The table provides information about statistic metrics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Description | string | The field contains the description of the statistical metric |
Explain | string | The field contains the explanation of the statistical metric |
EmBalance | The table provides information about balances | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Main | decimal | The field contains the main balance |
Bonus | decimal | The field contains the bonus of the balance |
Currency | string | The field contains the currence of the balance |
EmCampaign | The table provides information about list of mailing campaigns | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the campaign name |
Status | GUID | The field contains the status ID as a link to the EmDictCampaignStatus table |
AllEmailQty | decimal | The field contains the number of all emails |
TariffEmailQty | decimal | The field contains emails which are included in the tariff quantity |
PaidEmailQty | decimal | The field contains the number of emails which are paid for |
OverdraftPrice | decimal | The field contains the emails price which are exceed the tariff |
OverdraftCurrency | string | The field contains the currency of emails which are exeed the tariff |
SendDate | timestamp | The field contains the date when the campaign was sent |
TypeID | GUID | The field contains the type ID as a link to the EmDictCampaignType table |
AddressBookID | GUID | The field contains the address book ID as a link to the EmAdressdBooks table |
CurrencyCode | string | The field contains the three letter currency code |
ListID | GUID | The field contains the list ID as a link to the EmList table |
ContactID | GUID | The field contains the contact ID as a link to the EmEmail table |
EntityType | string | The field contains the type of the campaign |
EmDictType | The table provides information about type of statuses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Value | string | The field contains the value of the status |
DictTypeValue | string | The field contains the type of the value |
EmDictCampaignStatus | The table provides information about campaign statuses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Value | string | The field contains the value of the status |
Description | string | The field contains the description of the the status |
EmCampaignsStatistic | The table provides information about mailing campaign statistics | |
CampaignID | GUID | The field contains the campaign ID as a link to the EmCampaigns table |
DictCampaignMetrikID | GUID | The field contains the dictionary campaign metric ID as a link to the EmDictCampaignMetrik table |
Value | string | The field contains the value of the statistic metric |
Explain | string | The field contains the explanation of the statistic metric |
EmDictCampaignMetrik | The table provides information about statistic metrics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Description | string | The field contains the description of the statistic metric |
EmMessage | The table provides information about the list of messages used in campaigns | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
SenderID | GUID | The field contains the sender ID as a link to the EmSender table |
Subject | string | The field contains the subject of the message |
Body | string | The field contains the body of the message |
ListID | string | The field contains the ID of the message list |
EmSender | The table provides information about list of senders | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the sender |
string | The field contains the email of the sender | |
Status | string | The field contains the status of the sender |
SenderRoleID | string | The field contains the sender role ID as a link to the EmDictType table |
EmTariffs | The table provides information about tariffs | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Type | string | The field contains the type of the tariff: monthly, forever_free, pay_as_you_go, |
TariffName | string | The field contains the name of the tariff |
EndDate | timestamp | The field contains the tariff ending date |
AutoRenew | string | The field indicates whether tariff is auto renew |
EmailsLeft | decimal | The field contains the number of abandoned emails |
MaximumSubscribers | decimal | The field contains the maximum number of subscribers |
CurrentSubscribers | decimal | The field contains the current number of subscribers |
FirstPayment | timestamp | The field contains the date of first payment for monthly plans |
EmViberCampaigns | The table provides information about viber mailing campaigns | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the viber campaign |
Message | string | The field contains the message of the viber campaign |
ButtonText | string | The field contains the button text of the viber campaign |
ButtonLink | string | The field contains the button link of the viber campaign |
ImageLink | string | The field contains the image link of the viber campaign |
AddressBook | GUID | The field contains the address book ID as a link to the EmAddressBooks table |
SenderName | string | The field contains the name of the sender |
SendDate | timestamp | The field contains the date when the viber campaign was sent |
Status | string | The field contains the status of the viber campaign |
Sent | decimal | The field contains the number of messages sent |
Delivered | decimal | The field contains the number of messages delivered |
Read | decimal | The field contains the number of messages read |
Redirected | decimal | The field contains the number of messages redirected |
Undelivered | decimal | The field contains the number of messages undelivered |
Errors | decimal | The field contains the number of messages errors |
CreatedDate | timestamp | The field contains the date when the campaign was created |
EmPushCampaigns | The table provides information about push mailing campaigns | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Title | string | The field contains the title of the push campaign |
Body | string | The field contains the body of the push campaign |
WebSitesID | GUID | The field contains the web site ID as a link to the EmWebsites table |
DateFrom | timestamp | The field contains the date when the mailing was started |
DateTo | timestamp | The field contains the date when the mailing was ended |
StatusID | GUID | The field contains the status ID as a link to the DictCampaignStatus table |
EmPushCampaignsStatistic | The table provides information about push campaigns statistics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
PushCampaignsID | GUID | The field contains the push campaign ID as a link to the EmPushCampaigns table |
Send | decimal | The field contains the number of sent push campaigns |
Delivered | decimal | The field contains the number of delivered push campaigns |
Redirect | decimal | The field contains the number of redirect push campaigns |
EmWebsites | The table provides information about websites | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Url | string | The field contains the url of the web site |
AddDate | timestamp | The field contains the date when the web site was created |
Status | string | The field contains the status of the web site |
EmWebsitesStatistic | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
WebsitesID | GUID | The field contains the web site ID as a link to the EmWebsites table |
ClickWindow | decimal | The field contains the conversion window of clicks on the site in days |
ViewWindow | decimal | The field contains the conversion window of views on the site in days |
EmSmsCampaigns | The table provides information about the list of sms campaigns | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
AddressBookID | GUID | The field contains the address book ID as a link to the EmAddressBooks table |
CompanyPrice | decimal | The field contains the company price of the campaign |
CompanyCurrency | string | The field contains the company currency of the campaign |
SendDate | timestamp | The field contains the date when the campaign was sent |
DateCreated | timestamp | The field contains the date when the campaign was created |
SenderMailAddress | string | The field contains the mail address of the sender |
SenderMailName | string | The field contains the mail name of the sender |
EmSmsCampaignsStatistic | The table provides information about sms campaigns statistics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
SmsCampaignsID | GUID | The field contains the sms campaign ID as a link to the SmsCampaigns table |
Phone | string | The field contains the phone related to the sms campaign |
Status | GUID | The field contains the status ID as a link to the EmDictCampaignMetrik table |
StatusExplain | string | The field contains the description of the status |
CountryCode | string | The field contains the code of the country |
MoneySpent | decimal | The field contains the amount of spent money to the sms campaign |
EmEcommerceStore | The table provides information about e-stores related to the contact | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
AddressBookID | GUID | The field contains the address book ID as a link to the EmAddressBooks table |
Name | string | The field contains the name of the store |
Platform | string | The field contains the e-commerce platform of the store |
Domain | string | The field contains the domain of the store |
EmailAddress | string | The field contains the email address for the store |
CurrencyCode | string | The field contains the three-letter ISO 4217 code for the currency that the store accepts |
PrimaryLocale | string | The field contains the primary locale for the store. For example: en, de, etc |
Phone | string | The field contains the store phone number |
Address | string | The field contains the city in which the store is located |
WebsiteID | GUID | The field contains the web site ID as a link to the EmWebsite table |
CreatedAt | string | The field contains the date and time when the store was created in ISO 8601 format |
EmEcommerceOrder | The table provides information about e-orders related to the contact | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
EmailAddress | string | The field contains the email address of the customer |
Company | string | The field contains the company of the customer |
FirstName | string | The field contains the first name of the customer |
LastName | string | The field contains the last name of the customer |
OrdersCount | int | The field contains the number of orders for the customer |
TotalSpent | decimal | The field contains the total amount of the order |
EcommerceStoreID | GUID | The field contains the commerce store ID as a link to the EcommerceStores table |
CampaignID | GUID | The field contains the campaign ID as a link to the EmCampaigns table |
LandingSite | string | The field contains the URL for the page where the buyer landed when entering the shop |
FinancialStatus | string | The field contains the financial status ID as a link to the EmDictType table |
CurrencyCode | string | The field contains the three-letter ISO 4217 code |
OrderTotal | decimal | The field contains the total order associated with an order |
DiscountTotal | decimal | The field contains the total amount of the discounts to be applied to the price of the order |
TaxTotal | decimal | The field contains the tax total associated with an order |
DateOrder | datetime | The field contains the date when the order is created |
ContactID | GUID | The field contains the contact ID as a link to the EmEmail table |
PaymentStatus | string | The field contains the status of the order |
PaymentMethod | string | The field contains the payment method of the order |
FulfillmentStatus | string | The field contains the fulfillment status of the order |
ShippingMethod | string | The field contains the shipping method of the order |
EmSocialCampaign | The table provides advertising campaigns information | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the title or name of the social campaign |
SocialSourseID | GUID | The field contains the social source ID as a link to the EmDictType table |
SocialCampaignTypeID | GUID | The field contains the social campaign type ID as a link to the EmDictType table |
SocialCampaignStatusID | GUID | The field contains the social campaign status ID as a link to the EmDictType table |
CreateTime | datetime | The field contains the time when the campaing was created |
StartTime | datetime | The field contains the time when the campaing was started |
PublishedTime | datetime | The field contains the time when the campaing was published |
EndTime | datetime | The field contains the time when the campaing was ended |
AddressBookID | GUID | The field contains the address book ID as a link to the EmAddressBooks table |
RecipientCount | int | The field contains the number of recipients in the associated list |
Duration | int | The field contains the duration of the ad in seconds |
TotalAmount | decimal | The field contains the total budget of the ad |
CurrencyCode | string | The field contains the three-letter ISO 4217 code |
EmSocialReportSummary | The table provides information about advertising reports | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
SocialCampaignID | GUID | The field contains the social campaign ID as a link to the EmSocialAdCampaings table |
SocialReportSummaryID | GUID | The field contains the social report summary ID as a link to the EmDictSocialAdReportsummary table |
Value | string | The field contains the value of the report |
Explain | string | The field contains the value explanation |
EmDictSocialReportSummary | The table provides dictionary of advertising reports | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Description | string | The field contains the description of the report |
EmProject | The table provides information about projects | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
EmFunnel | The table provides information about funnels | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the funnel |
ProjectID | GUID | The field contains the project ID as a link to the EmProject table |
EmFunnelSavedReport | The table provides information about funnel saved reports | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ComputedAt | datetime | The field contains the date when the report was calculated |
CreateAt | datetime | The field contains the date when the report was created |
Stage | string | The field contains the stage name of the event |
EventName | string | The field contains the name of the event |
ProjectID | GUID | The field contains the project ID as a link to the EmProject table |
FunnelID | GUID | The field contains the funnel ID as a link to the EmFunnel table |
EmFunnelStat | The table provides information about funnel statistics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the type of the status |
Value | decimal | The field contains the quantity of the status |
TotalUsers | decimal | The field contains the total number of users who created the funnel |
EventID | GUID | The field contains the event ID as a link to the EmEvent table |
ProjectID | GUID | The field contains the project ID as a link to the EmProject table |
FunnelID | GUID | The field contains the funnel ID as a link to the EmFunnel table |
EmRetentionReport | The table provides information about cohort analysis as the analysis of the group people behavior united by any attribute over time | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ProjectID | GUID | The field contains the project ID as a link to the EmProject table |
EventID | GUID | The field contains the event ID as a link to the EmEvent table |
ComputedAt | datetime | The field contains the date when the report was calculated |
CreateAt | datetime | The field contains the date when the report was created |
Period | string | The field contains the unit/interval period (day, week, month) of activity of the report |
TimeInterval | string | The field contains the specific time of the interval (hours, days) |
NumberOfUsers | int | The field contains the total number of users who performed event integration (gave rise) |
StatusName | string | The field contains the status name of the report |
Value | int | The field contains the value of the report (number of users who performed event integration (gave rise) |
Entitytype | string | The field contains the type of the interval in the report |
EmSegmentationReport | The table provides information about data for the event, segmented and filtered by properties – Insights report | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ComputedAt | datetime | The field contains the date when the report was calculated |
Period | string | The field contains the unit/interval period (day, week, month) of activity of the report |
Value | int | The field contains the value of the report (number of users who performed event integration (gave rise) |
Entitytype | string | The field contains the type of the interval in the report |
ProjectID | GUID | The field contains the project ID as a link to the EmProject table |
EventID | GUID | The field contains the event ID as a link to the EmEvent table |
EmSegmentationStat | The table provides information about the amounts and averages of expressions for events per unit time (according to the formula) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ComputedAt | datetime | The field contains the date when the event was calculated |
Date | datetime | The field contains the date when the event was created |
Value | int | The field contains the value of the event (number of users who performed event integration (gave rise) |
Period | string | The field contains the unit/interval period (day, week, month) of activity of the event |
Status | string | The field contains the status name of the event |
EntityType | string | The field contains the type of the segmentation |
ProjectID | GUID | The field contains the project ID as a link to the EmProject table |
EventID | GUID | The field contains the event ID as a link to the EmEvent table |
EmEvent | The table provides information about events | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the event |
EntityType | string | The field contains the type of TOP events by period (today, month) |
Date | datetime | The field contains the date when the event was created |
Value | int | The field contains the number of events for the period |
Amount | int | The field contains the amount of the event |
Period | string | The field contains the tracking period, unit (day, week, month) of the event |
Type | string | The field contains the type of the event |
ComputedAt | datetime | The field contains the date when the event was calculated |
ProjectID | GUID | The field contains the project ID as a link to the EmProject table |
EmEventProperty | The table provides information about event properties | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the property |
ProjectID | GUID | The field contains the project ID as a link to the EmProject table |
EventID | GUID | The field contains the event ID as a link to the EmEvent table |
EmEventPropertyStat | The table provides information about property statistics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the property |
PropertyValue | string | The field contains the value of the property |
Period | string | The field contains the interval period (day, week, month) |
Value | int | The field contains the number of the all properties |
Date | datetime | The field contains the date when the event was created |
ComputedAt | datetime | The field contains the date when the event was calculated |
EntityType | string | The field contains the type of TOP events by period (today, month) |
ProjectID | GUID | The field contains the project ID as a link to the EmProject table |
EventID | GUID | The field contains the event ID as a link to the EmEvent table |
PropertyID | GUID | The field contains the property ID as a link to the EmEventProperty table |
EmConversion | The table provides information about conversions | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the campaign |
CreateDate | timestamp | The field contains the date of the conversion |
Frequency | string | The field contains the frequency of the conversion( ‘every’ or ‘once’) |
LastFired | timestamp | The field contains the date when the conversion was last run |
EmConversionStat | The table provides information about conversion statistics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ConversionID | GUID | The field contains the conversion ID as a link to the EmConversion table |
DictConversionMetrikID | GUID | The field contains the dictionary conversion metric ID as a link to the EmDictConversionMetrik table |
Value | string | The field contains the value of the conversion |
Explain | string | The field contains the description of the conversion |
EmDictConversionMetrik | The table provides the dictionary of conversion metrics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Description | string | The field contains the description of the conversion |
EmWebform | The table provides information about webforms | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the web form |
Type | string | The field contains the type of the web form |
EmWebformStatistic | The table provides information about webforms statistics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
WebsitesID | GUID | The field contains the web sites ID as a link to the EmWebform table |
TotalDisplays | decimal | The field contains the total displays |
TotalSubmissions | decimal | The field contains the total submissions |
TotalUniqueDisplays | decimal | The field contains the total unique displays |
EntityType | string | The field contains the type of TOP events by period (today, month) |
EmItem | The table provides information about items | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the item |
Type | string | The field contains the type of the item |
Status | string | The field contains the status of the item |
Description | string | The field contains the description of the item |
EmCategoriesID | GUID | The field contains the category ID as a link to the EmCategory table |
Currency | string | The field contains the code of the currency |
SalesChannelName | string | The field contains the product vendor of the channel |
CreateDate | datetime | The field contains the date when the item was created |
StoreID | GUID | The field contains the store ID as a link to EmEcommerceStore table |
EmCategory | The table provides information about categories | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OriginalID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the category |
CreatedAt | datetime | The field contains the date when the category was created |
eWallet DB Schema contains information from the client’s eWallet systems and POS. Data from various systems such as Chargebee, LightspeedRetail, Stripe, QiwiWallet etc being structured and placed at a single structure to facilitate the following analysis. The schema consists of such entities EwalletPaymentDetail, EwalletEventCode, EwalletCompany, EwalletProduct etc. Please see below to find out the complete list of schema’s entities. | ||
TABLE/FIELDS | DATA TYPES | DESCRIPTION |
EwalletPaymentDetail | The table provides information about payment details | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
EventCodeID | GUID | The field contains the event code ID as a link to the EwalletEventCode table |
ActionID | GUID | The field contains the action ID as a link to the EwalletPaymentDetailAction table |
StatusID | GUID | The field contains the status ID as a link to the EwalletPaymentDetailStatuses table |
Type | string | Type of the transaction(payment, refund…etc) |
PaytypeID | GUID | The field contains the pay type ID as a link to the EwalletPaymentDetailPaytype table |
AcqID | string | The field contains the Acquire ID |
OrderID | string | The field contains the order ID |
Description | string | The field contains the description of the payment |
Amount | decimal | The field contains the amount of the payment |
Currency | string | The field contains the currency of the table |
AmountDebit | decimal | The field contains the amount debit of the payment |
AmountCredit | decimal | The field contains the amount credit of the payment |
CurrencyDebit | string | The field contains the currency debit of the payment |
CurrencyCredit | string | The field contains the currency credit of the payment |
CreateDate | datetime | The field contains the date when the payment was created |
EndDate | datetime | The field contains the date when the payment was ended |
TransactionID | string | The field contains the transaction ID of the payment |
TransactionType | string | The field contains the type of the transaction (values “In” and “Out”) |
Kind | string | The field contains the kind for the charge transaction type |
ContragentID | GUID | The field contains the contragent ID as a link to the EwalletContragent table |
ShippingAddressID | GUID | The field contains the shipping address ID as a link to the EwalletAddress table |
SenderCardMask2 | string | The field contains the card mask of the payment |
SenderCardBank | string | The field contains the bank of the payment |
SenderCardType | string | The field contains the card type of the payment |
AgentCommision | decimal | The field contains the agent commision of the payment |
AmountBonus | decimal | The field contains the bonus amount of the payment |
CommisionCredit | decimal | The field contains the commision credit of the payment |
CommisionDebit | decimal | The field contains the commision debit of the payment |
Ip | string | The field contains the Ip of the payment |
Is3ds | string | The field contains the Is3ds of the payment |
Language | string | The field contains the language of the payment of the payment |
LiqpayOrderID | string | The field contains the liqpay Order ID of the payment |
MpiEci | string | The field contains the MpiEci of the payment |
OrigPaymentID | string | The field contains the original payment ID |
PublicKey | string | The field contains the public key of the payment |
ReceiverCommision | decimal | The field contains the receiver commision of the payment |
SenderBonus | decimal | The field contains the sender bonus of the payment |
SenderCommision | decimal | The field contains the sender commision of the payment |
EventCode | string | The field contains the event code of the payment |
Code | string | The field contains the code of the payment |
CardID | GUID | The field contains the card ID as a link to the EwalletCard table |
SubscriotionsID | GUID | The field contains the subscription ID as a link to the EwalletSubscriotions table |
CompanyID | GUID | The field contains the company ID as a link to the EwalletCompany table |
ProductID | GUID | The field contains the product ID as a link to the EwalletProduct table |
ComponentID | GUID | The field contains the component ID as a link to the EwalletComponent table |
EntytyType | string | The field contains the entity type of the payment |
EwalletPaymentValue | The table provides information about payed amounts | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
PaymentID | GUID | The field contains the payment ID as a link to the EwalletPaymentDetail table |
AmountType | string | The field contains the amount type of the payment |
Value | decimal | The field contains the amount value of the payment |
Currency | string | The field contains the currency of the payment |
EwalletEventCode | The table provides information about transaction event code that classifies the transaction type based on money movement and debit or credit | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the event |
Description | string | The field contains the description of the event |
SubscriotionsID | GUID | The field contains the subscription ID as a link to the EwalletSubscriotions table |
ContragentID | GUID | The field contains the contragent ID as a link to the EwalletContragent table |
CreateDate | datetime | The field contains the date when the event was created |
ProductID | GUID | The field contains the product ID as a link to the EwalletProduct table |
TransactionID | GUID | The field contains the transaction ID as a link to the EwalletPaymentDetail table |
EventType | string | The field contains the type of the event |
TotalAmount | string | The field contains the total amaunt of the event |
Status | string | The field contains the status of the event |
EntityType | string | The field contains the entity type of the event |
InvoiceID | GUID | The field contains the invoice ID as a link to the EwalletInvoice table |
EwalletContragent | The table provides information about contragents | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
SenderPhone | string | The field contains the phone of the contragent |
SenderFirstName | string | The field contains the first name of the contragent |
SenderLastName | string | The field contains the last name of the contragent |
SenderCardCountry | string | The field contains the the card country of the contragent |
string | The field contains the email of the contragent | |
AddressID | GUID | The field contains the address ID as a link to the EwalletAddress table |
EwalletCompany | The table provides information about companies or users | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
PublicKey | string | The field contains the public key of the company |
ContractID | string | The field contains the contract ID |
string | The field contains the email of the company | |
Name | string | The field contains the name of the company |
Url | string | The field contains the url of the company |
Description | string | The field contains the description of the company |
Logo | string | The field contains the logo of the company |
Phone | string | The field contains the phone of the company |
RefundWay | string | The field contains the refund way of the company |
RefundNumber | string | The field contains the refund number of the company |
MFO | decimal | The field contains the bank sort code of the company |
OKPO | decimal | The field contains the okpo of the company |
CompanyName | string | The field contains the full name of the company |
CreateDate | datetime | The field contains the date when the company was created |
UpdateDate | datetime | The field contains the date when the company information was updated last time |
Blocked | string | The field contains whether the company is blocked |
FirstName | string | The field contains the private individual first name of the user |
MiddleName | string | The field contains the private individual middle name of the user |
Lastname | string | The field contains the private individual last name of the user |
BirthDate | string | The field contains the date of birth of the user |
Passport | string | The field contains the passport number of the user |
Snils | string | The field contains the the pension insurance number |
Oms | string | The field contains the the medical insurance number |
InternalPersonID | string | The field contains the the original internal system person ID |
AddressID | GUID | The field contains the address ID as a link to the EwalletAddress table |
KPP | string | The field contains the code of the reason for registration with the tax authorities |
OGRN | string | The field contains the main state registration number of the legal entity |
Status | string | The field contains the status of the company |
EwalletPartner | The table provides information about partners | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Data | string | The field contains the data of the partner |
PublicKey | string | The field contains the the public key of the partner |
string | The field contains the email of the partner | |
Name | string | The field contains the name of the partner |
Url | string | The field contains the url of the partner |
Description | string | The field contains the description of the partner |
Logo | string | The field contains the logo of the partner |
CreateDate | datetime | The field contains the date when the partner was created |
UpdateDate | datetime | The field contains the date when the partner was updated last time |
Role | string | The field contains the role of the partner |
Blocked | string | The field indicates whether the is blocked |
EwalletBalance | The table provides information about balances | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Alias | string | The field contains the alias of the user balance |
FsAlias | string | The field contains the alias of the bank balance |
TypeID | string | The field contains the type ID of the account |
TypeTitle | string | The field contains the title of the account type |
BalanceAmount | decimal | The field contains the current amount of the balance |
BalanceCurrency | decimal | The field contains the code of the currency (for example: number-3 ISO-4217) |
Total | decimal | The field contains the total balance of the account |
BalanceAvailable | decimal | The field contains the available balance of the account |
DepositionPending | decimal | The field contains the amount in queue deposition |
Blocked | decimal | The field indicates whether the amount is blocked |
Debt | decimal | The field contains the debt amount of the account |
Hold | decimal | The field contains the hold amount of the account |
AccountStatus | string | The field contains the status of the account |
IDentified | string | The field indicates whether the account is identified |
CardsLinked | string | The field contains information about linked bank cards |
CardsType | string | The field contains information about type of linked bank cards |
BalanceInvoices | int | The field contains the balance of the subscription’s open, payable invoices |
BalanceDiscounts | int | The field contains the balance of the subscription’s pending discount account |
BalanceServiceCredits | int | The field contains the balance of the subscription’s service credit account |
BalancePrepayments | int | The field contains the balance of the subscription’s prepayment account |
EwalletStatistic | The table provides information about payment statistics by periods | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CompanyID | GUID | The field contains the company ID as a link to the EwalletCompany table |
SiteName | string | The field contains the name of the site |
SellerName | string | The field contains the name of the merchant |
AmountIncome | decimal | The field contains the incoming amount for period |
CurrencyIncome | string | The field contains the currency of the income amount |
AmountOut | decimal | The field contains the outgoing amount for period |
CurrencyOut | decimal | The field contains the currency of the outgoing amount |
RevenueToday | string | The field contains the revenue amount for today |
RevenueThisMonth | string | The field contains the revenue amount for this month |
RevenueThisYear | string | The field contains the revenue amount received this year |
TotalSubscriptions | int | The field contains the total number of ALL subscribers on the site |
TotalActiveSubscriptions | int | The field contains the total number of all active subscriptions |
SubscriptionsToday | int | The field contains the total number of new subscribers on the day when the request was sent |
TotalCanceledSubscriptions | int | The field contains the total number of all canceled subscriptions |
EwalletPaymentDetailAction | The table provides information about payment action codes | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the payment |
Description | string | The field contains the descriptionof the payment |
EwalletPaymentDetailStatuses | The table provides information about payment action statuses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the paymjent |
Description | string | The field contains the description of the payment |
CodeDetail | string | The field contains the detail code of the payment |
Status | string | The field contains the status of the payment |
EwalletPaymentDetailPaytype | The table provides information about type of payments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the card |
Description | string | The field contains the description of the card |
EwalletAddress | The table provides information about addresses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Address | string | The field contains the address |
Country | string | The field contains the two-character ISO 3166-1 code that identifies the country or region |
PostalCode | string | The field contains the postal code, which is the zip code or equivalent |
EwalletShippingInfo | The table provides information about shippings | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the recipient |
Method | string | The field contains the shipping method |
AddressID | GUID | The field contains the address ID as a link to the EwalletAddress table |
EwalletProduct | The table provides information about products(goods or services) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the product |
Description | string | The field contains the description of the product |
CreateDate | datetime | The field contains the date when the product was created |
Type | string | The field contains the type of the product (for example: SERVICE, GOOD) |
Category | string | The field contains the category of the product |
Status | string | The field contains the status of the product |
CompanyID | GUID | The field contains the company ID as a link to the EwalletCompany table |
Amount | decimal | The field contains the amount of the product |
Currency | string | The field contains the currency of the product |
BillingFrequency | string | The field contains the requency amount of month. Once in 3 month = 3, Once in 1 month = 1 |
BillingDay | string | The field contains the day of billing |
Trial | string | The field contains the trial of the product |
TrialUnit | string | The field contains the rial unite of the product |
InitialCharge | int | The field contains the initial payment |
TrialPrice | int | The field contains the price of the trial period for a subscription |
VersionNumber | int | The field contains the version of the product |
ProductCategory | GUID | The field contains the product category ID as a link to the EwalletProductCategory table |
EwalletProductCategory | The table provides information about product categories, product family | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the category |
Description | string | The field contains the description of the category |
CreateDate | datetime | The field contains the date when the category was created |
EwalletComponent | The table provides information about components that can be added and “allocated” for each subscription to a product in the product family | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the companent |
Description | string | The field contains the description of the companent |
CreateDate | datetime | The field contains the date when the companent was created |
EwalletInvoice | The table provides information about invoices | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
ParentID | string | The field contains the parent ID to an invoice that defines the group invoice to which the invoice is belong to |
StatusID | GUID | The field contains the status ID as a link to the EwalletInvoiceStatus table |
Note | string | The field contains the note to the invoice recipient. Also appears on the invoice notification email |
Number | string | The field contains the number of the invoice |
InvoiceDate | datetime | The field contains the date when the invoice was created |
DueDate | datetime | The field contains the date when the invoice payment is due |
PaidAt | datetime | The field contains the date and time when the invoice got paid |
InvoicerName | string | The field contains the name of the invoicer |
InvoicerEmail | string | The field contains the email of the invoicer |
InvoicerTaxID | string | The field contains the tax ID of the invoicer |
PriceType | string | The field contains the all amounts in the document are exclusive tax or inclusive |
Tax | string | The field contains the total tax amount for the invoice |
CurrencyCode | string | The field contains the three-character ISO-4217 currency code that identifies the currency |
Amount | decimal | The field contains the amount of the invoice |
DueCurrencyCode | string | The field contains the due date of the currency code |
DueAmount | decimal | The field contains the amount payable, which is the amount of balance remaining after payments |
First invoice | boolean | The field indicates the first bill issued for the subscription |
SubscriptionID | GUID | The field contains the subscription ID as a link to the EwalletSubscription table |
Recurring | boolean | The field indicates is the invoice recurring |
CustomerID | GUID | The field contains the customer ID as a link to the EwalletContragent table |
SubscriptionID | GUID | The field contains the subscription ID as a link to the EwalletSubscriptions table |
CompanyID | GUID | The field contains the company ID as a link to the EwalletCompany table |
EwalletInvoiceTransactLink | The table provides a link between invoices and transactions | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
InvoiceID | GUID | The field contains the invoice ID as a link to the EwalletInvoice table |
TransactionID | GUID | The field contains the transaction ID as a link to the EwalletPaymentDetail table |
EwalletInvoiceContragentLink | The table provides the a link between invoices and contragents | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
InvoiceID | GUID | The field contains the invoice ID as a link to the EwalletInvoice table |
ContragentID | GUID | The field contains the contragent ID as a link to the EwalletContragent table |
ShipAddressID | GUID | The field contains the ship address ID as a link to the EwalletAddress table |
EwalletInvoiceDetail | The table provides invoice details | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
InvoiceID | GUID | The field contains the invoice ID as a link to the EwalletInvoice table |
ProductID | GUID | The field contains the product ID as a link to the EwalletProduct table |
Quantity | string | The field contains the quantity of the invoice |
UnitAmount | decimal | The field contains the unit amount of the invoice |
TaxAmount | decimal | The field contains the tax amount of the invoice |
DiscontAmount | decimal | The field contains the discount amount of the invoice |
UnitOfMeasure | string | The field contains the unit of measure for the invoice |
EntityType | string | The field contains the specification of the object being modeled (plan/addon, etc.) |
TotalAmount | decimal | The field contains the total amount = item*quantity |
PaidAmount | string | The field contains the paid amount of the item |
СreditAmount | string | The field contains the amount of credit (from credit notes) applied to this invoice |
PaymentMode | string | The field contains the payment mode of the invoice |
paymentSubject | string | The field contains the payment subject of the invoice |
DocumentID | GUID | The field contains the document ID as a link to the EwalletIDocument table |
Method | string | The field contains the collection method of the invoice |
EwalletInvoiceStatus | The table provides information about invoices statuses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Status | string | The field contains the status of the invoice |
Descr | string | The field contains the description of the invoice |
EwalletCard | The table provides information about debit/credit cards | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
CardType | string | The field contains the list of values for the card: debit, credit. |
Number | string | The field contains the number of the card |
PaymentSystem | string | The field contains the list of values for payment system: visa, mastercard, maestro |
ExpireMonth | string | The field contains the expiration month |
ExpireYear | string | The field contains the expiration year |
FirstName | string | The field contains the first name of the cardholder |
LastName | string | The field contains the last name of the cardholder |
AddressID | GUID | The field contains the address ID as a link to the EwalletAddress table |
ContragentID | GUID | The field contains the status ID as a link to the EwalletContragent table |
Status | string | The field contains the list of values for statuses: expired, ok |
CreateDate | datetime | The field contains the date when the card was created |
ValidUntil | datetime | The field contains the date untill which the card is valid |
EwalletCreditNote | The table provides specifying money debts of businesses to customers | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
ContragentID | GUID | The field contains the contragent ID as a link to the EwalletContragent table |
SubscriotionsID | GUID | The field contains the subscription ID as a link to the EwalletSubscriotions table |
InvoiceID | GUID | The field contains the invoice ID as a link to the EwalletInvoice table |
Type | string | The field contains the type of the credit note (adjustment, refundable) |
Reason code | string | The field contains the reason for issuing the credit note |
Status | string | The field contains the status of the credit note |
Date | datetime | The field contains the date when the credit note was issued |
PriceType | string | The field contains the all amounts in the document are exclusive tax or inclusive |
Total | decimal | The field contains the amount of the credit note |
CurrencyCode | string | The field contains the currency code (ISO 4217 format) for the credit note |
AmountAvailable | decimal | The field contains the credits of this credit note, which have yet to be used |
AmountRefunded | decimal | The field contains the refunds issued from this credit note |
RefundedAt | datetime | The field contains the date and time when the credit note got fully used |
DiscountAmount | string | The field contains the discount of the amount |
Tax | string | The field contains the total tax of the credit note |
EwalletSubscriptions | The table provides information about platform subscriptions | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
ContragentID | GUID | The field contains the contragent ID as a link to the EwalletContragent table |
PlanID | string | The field contains the identifier of the plan for the subscription |
PlanQuantity | string | The field contains the the plan quantity for the subscription |
PlanUnitPrice | decimal | The field contains the amount that will override the plan’s default price |
PlanAmount | decimal | The field contains the plan of the amount |
BillingDay | string | This field contains the number of the day of the month/week for billing the customer (for example every 21 months, specify “21” here) |
BillingDayUnit | string | The field contains the billing frequency in association with the billing period – week, month |
BillingPeriod | string | The field contains the billing frequency. For xxample: to bill customer every 3 months, provide “3” here. |
BillingPeriodUnit | string | The field contains the billing frequency in association with the billing period |
PlanFreeQuantity | string | The field contains the units of the item that will be free with this plan |
Balance | int | The field contains the balance of the suscription |
SignupRevenue | string | The field contains the signup revenue of the product |
TotalRevenue | int | The field contains the total revenue of the product |
ProductPrice | int | The field contains the price of the product |
PaymentCollectionMethod | string | The field contains the type of payment collection to be used in the subscription |
Status | string | The field contains the current state of the subscription |
CreatedAt | datetime | The field contains the date when the subscription was createsd |
StartedAt | datetime | The field contains the time at which the subscription got started.Will be null for ‘future’ subscriptions as it is yet to be started |
StartDate | datetime | Applicable only for ‘future’ subscriptions. The scheduled start time of the ‘future’ subscription. |
СancelleAt | datetime | The field contains the time at which the subscription was cancelled or is set to be cancelled |
NextBillDate | datetime | The field contains the date for the next bill |
NextBillAmount | decimal | The field contains the amount for the next bill |
Mrr | decimal | The field contains the monthly recurring revenue of the subscription |
CurrencyCode | string | The field contains the code of the currency |
CardID | GUID | The field contains the card ID as a link to the EwalletCard table |
ProductID | GUID | The field contains the product ID as a link to the EwalletProduct table |
CouponID | GUID | The field contains the coupon ID as a link to the EwalletCoupon table |
AddOnID | GUID | The field contains the addon ID as a link to the EwalletAdsOn table |
PaymentID | GUID | The field contains the payment ID as a link to the EwalletPaymentDetail table |
EwalletCoupon | The table provides information about coupons | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains a displayed name used in the web interface to identify the coupon |
Description | string | The field contains the description of the coupon |
DiscountType | string | The field contains the discount type of the coupon |
DiscountAmount | decimal | The field contains the discount value in cents of the coupon |
DiscountPercentageAmount | decimal | The field contains the discount percentage value of the coupon |
DurationType | string | The field contains the period of time during which this coupon is valid |
Status | string | The field contains the status of the coupon |
CreatedAt | datetime | The field contains the date when the coupon was created |
ProductCategoryID | GUID | The field contains the product category ID as a link to the EwalletProductCategory table |
EwalletAddOn | The table provides information about addons | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains a displayed name used in the web interface to identify the addon |
Descriptions | string | The field contains the discount type of the addon |
Amount | decimal | The field contains the discount value in cents |
Type | string | The field contains the type of the addon |
CreatedAt | datetime | The field contains the date when the addon is created |
EwalletDocument | The table provides information about reports | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
UserID | GUID | The field contains the user ID as a link to the EwalletCompany table |
DocumentType | string | The field contains the type of the document |
Code | string | The field contains the code/bso code of the document |
ReceiptCode | string | The field contains the receipt code of the document |
Operator | string | The field contains the operator of the document |
AddressID | GUID | The field contains the address ID as a link to the EwalletAddress table |
DateTime | datetime | The field contains the date and time when the document was created |
EwalletShiftID | GUID | The field contains the ewallet shift ID as a link to the EwalletShift table |
EwalletCashboxID | GUID | The field contains the ewallet cashbox ID as a link to the EwalletCashbox table |
FiscalDocumentNumber | string | The field contains the number of the fiscal document |
FiscalDriveNumber | string | The field contains the number of the fiscal drive |
FiscalSign | string | The field contains the sign of the fiscal document |
ReceiptsQuantity | decimal | The field contains the number of receipts of the fiscal document |
DocumentsQuantity | decimal | The field contains the number of documents |
NotTransmittedDocumentsQuantity | decimal | The field contains the number of not transmitted documents |
NotTransmittedDocumentsDateTime | datetime | The field contains the date and time of not transmitted documents |
OfdResponseTimeoutSign | string | The field contains the sign of ofd response timeout of the documents |
FiscalDriveReplaceRequiredSign | string | The field contains an indication of the required replacement of the fiscal drive |
FiscalDriveMemoryExceededSign | string | The field contains the sign of exceeding the memory of the fiscal disk drive |
FiscalDriveExhaustionSign | string | The field contains the sign of the exhaustion for the fiscal drive |
OperationType | string | The field contains the operation type of the document |
TaxationType | string | The field contains the taxation type of the document |
BuyerID | GUID | The field contains the buyer ID as a link to the EwalletContragent table |
RequestNumber | string | The field contains the number of the request |
Nds20 | decimal | The field contains the value of the 20% NDS |
Nds18 | decimal | The field contains the value of the 18% NDS |
Nds10 | decimal | The field contains the value of the 10% NDS |
Nds0 | decimal | The field contains the value without NDS |
TotalSum | decimal | The field contains the total amount of the document |
CashTotalSum | decimal | The field contains the total cash amount of the document |
EcashTotalSum | decimal | The field contains the total ecash amount of the document |
PrePaymentTotalSum | decimal | The field contains the total amount of the pre payment |
PostPaymentTotalSum | decimal | The field contains the total amount of the post payment |
CorrectionReasonCode | string | The field contains the code of correction reason |
NotTransmittedDocumentNumber | int | The field contains the number of the first unsent document |
ShiftNumber | string | The field contains the number of the shift |
EwalletCashbox | The table provides information about cashboxes | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
SerialNumber | string | The field contains the serial number of the cashbox |
AddressID | GUID | The field contains the address ID as a link to the EwalletAddress table |
Name | string | The field contains the name of the cashbox |
Model | string | The field contains the model of the cashbox |
UserID | GUID | The field contains the user ID as a link to the EwalletCompany table |
FiscalDriverNumber | string | The field contains the number of fiscal driver |
SalesPointName | string | The field contains the name of the sales point |
PermissionFrom | datetime | The field contains the date from which the integrator can receive documents |
PermissionTo | datetime | The field contains the date until which the integrator can receive documents |
LastDate | datetime | The field contains the time of the last successfully received document |
Activated | datetime | The field indicates the activity of the cashbox (true/false) |
EwalletShift | The table provides information about shifts | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
EwalletCashbox | GUID | The field contains the ewallet cashbox ID as a link to the EwalletCashbox table |
ShiftNumber | string | The field contains the number of the shift |
ShiftOpen | datetime | The field contains the date when the shit is started |
ShiftClose | datetime | The field contains the date when the shit is ended |
OperationType | string | The field contains the type of operation during the shift |
CashlessTotal | decimal | The field contains the total amount of the cashless during the shift |
CashTotal | decimal | The field contains the total amount of the cash during the shift |
TotalWithNds0 | decimal | The field contains the total amount with zero NDS during the shift |
TotalWithNdsFree | decimal | The field contains the total amount without NDS during the shift |
Count | string | The field contains the number of shifts |
Nds10 | decimal | The field contains the value of the 20% NDS during the shift |
Nds18 | decimal | The field contains the value of the 18% NDS during the shift |
Nds20 | decimal | The field contains the value of the 10% NDS during the shift |
EwalletMrr | The table provides information about Monthly Recurring Revenue values | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ProductID | GUID | The field contains the product ID as a link to the EwalletProduct table |
SubscriptionID | GUID | The field contains the subscription ID as a link to the EwalletSubscriptions table |
ComponentID | GUID | The field contains the component ID as a link to the EwalletComponent table |
CreatedAt | datetime | The field contains the date when the MRR was calculated |
Amount | int | The field contains the value of MRR in cents |
AmountFormatted | string | The field contains the formatted amount of the MRR |
Description | string | The field contains the description of the MRR |
Category | string | The field contains the category to which MRR belongs |
PlanAmount | int | The field contains the plan MRR amount in cents |
PlanAmountFormatted | string | The field contains the plan formated MRR amount |
UsageAmountFormatted | string | The field contains the usage formated MRR amount |
ProductName | string | The field contains the name of the product |
Quantity | int | The field contains the quantity of the product |
SubscriberName | string | The field contains the name of the subscriber |
EwalletComponent | The table provides information about components | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the display name of the component |
UnitName | string | The field contains the name of the unit in which the use of the component is measured, i.e. the message |
UnitPrice | string | The field contains the amount the customer will be charged for the unit |
ProductFamilyID | GUID | The field contains the product family ID as a link to the EwalletProductCategory table |
ProductFamilyName | string | The field contains the name of the product family |
Type | string | The field contains the handle for the component type |
Description | string | The field contains the description of the component |
CreatedAt | datetime | The field contains the date when the component was created |
SubscriptionID | GUID | The field contains the subscription ID as a link to the EwalletSubscriptions table |
Entitytype | string | The field contains the entity type of the component |
WebAnalytic DB Schema contains information from the client’s web analytic systems. Data from various systems such as GoogleAnalytics, YandexMetrica, etc being structured and placed at a single structure to facilitate the following analysis. The schema consists of such entities WAMetrics, WAPeriods, WAVisits etc. Please see below to find out the complete list of schema’s entities. | ||
TABLE/FIELDS | DATA TYPES | DESCRIPTION |
WADimensions | The table provides information about dimensions | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigDimID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the original name of the source system |
GroupID | string | The field contains the ID of the dimension group |
GroupName | string | The field contains the name of the dimension group |
WAMetrics | The table provides information about metrics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigMetricID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the metric |
WACounter | The table provides information about counters | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigCounterID | decimal | The field contains the Entity ID from original source system |
OrigProfileID | string | The field contains the Entity ID from original source system |
Status | string | The field contains the status of the counter |
OwnerLogin | string | The field contains the owner login of the counter |
Name | string | The field contains the name of the counter |
Site | string | The field contains the site where the counter is located |
WAGoal | The table provides information about goals | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigGoalID | decimal | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the goal |
CounterID | GUID | The field contains the counter ID as a link to table WACounter table |
OrigProfileID | string | The field contains the ID of the account representation (may be more than one) |
ProfileName | string | The field contains the name of the account representation |
WADirectClient | The table provides information about advertising logins | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigID | string | The field contains the Entity ID from original source system |
Name | string | The field contains the name of the direct client |
WAPeriods | The table provides information about reporting periods | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
DateBOP | date | The field contains the start date of the period |
DateEOP | date | The field contains the end date of the period |
PeriodType | string | The field contains the tyupe of the period (for ewample: “D” – day, “M” – month) |
WAVisits | The table provides information about metric values | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CounterID | GUID | The field contains the counter ID as a link to table WACounter table |
PeriodsID | GUID | The field contains the period ID as a link to table WAPeriods table |
GoalID | GUID | The field contains the goal ID as a link to table WAGoal table |
DirectClientsID | GUID | The field contains the direct client ID as a link to table WADirectClient table table |
DimensionsID | GUID | The field contains the dimensions ID as a link to table WADimensions table |
MetricID | GUID | The field contains the metric ID as a link to table WAMetrics table |
MetricValue | decimal | The field contains the value metric of visits |
TotalMetricValue | decimal | The field contains the total value metric of visits |
CRM Entity Database Schema is used for storing data from clients’ CRM systems. Data taken from different systems such as RetailCRM, Salesforce, Pipedrive, ZohoCRM, HubSpotCRM, etc. are structured and placed in a single structure to simplify their analysis. A complete list of database schema entities is given below.
TABLE/FIELDS | DATA TYPES | DESCRIPTION |
CrmBusinessSite | The table provides information about the company’s websites | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the website |
Name | string | The field contains the title of the website |
Url | string | The field contains the url of the website |
Description | string | The field contains the description of the website |
CrmBusinessInfo | The table provides general business information | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId | string | The field contains the Entity ID from original source system. It is the Company Id. |
Name | string | The field contains the name of the company |
Code | string | The field contains the internal code of the company |
ContragentType | string | The field contains the type of the contragent |
VatRate | decimal | The field contains the value of the Vat rate |
CountryId | GUID | The field contains the country ID as a link to the CRMCountry table |
Currency | string | The field contains the main currency of the company |
EmployeeCount | decimal | The field contains the number of employees |
MobilePhone | string | The field contains the mobile phone number of the company |
Phone | string | The field contains the phone number of the company |
string | The field contains the email of the company | |
CompanyIndustry | string | The field contains the industry of the company |
CrmExpense | The table contains information about the company’s expenses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. It is the Expense Id. |
DateFrom | datetime | The field contains the start date of expenses |
DateTo | datetime | The field contains the end date of expenses |
Summ | decimal | The field contains the amount of expenses |
TypeId | GUID | The field contains the expense type ID as a link to the CrmExpenseType table |
Comment | string | The field contains the comment |
CreateDate | datetime | The field contains the date the record was created |
OrderId | GUID | The field contains the order ID as a link to the CrmOrder table |
UserId | GUID | The field contains the user ID as a link to the CrmUser table |
CustomerId | GUID | The field contains the customer ID as a link to the CrmCustomer table |
CrmSource | The table contains the source from which customers, orders, expenses are came | |
EntityId | GUID | The field contains the entity ID as a link to the CRMExpense / CRMCustomer / CRMOrder…table |
EntityName | string | The field contains the entity title. For example: if the EntityId type is a link to the CRMExpense table then EntityName = “Expense” |
Source | string | The field contains the source from which the client came |
Channel | string | The field contains the channel from which the client came |
Campaign | string | The field contains the name of the campaign through which the client came |
Keyword | string | The field contains the keywords to which the customer responded |
Content | string | The field contains the content of the campaign |
CrmExpenseType | The table contains types of expenses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the type |
Name | string | The field contains the name of the type |
GroupId | GUID | The field contains the expense group ID as a link to the CrmExpenseGroup table |
Ordering | int | The field contains the order in which the types are displayed |
Active | Boolean | The field indicates whether the expenses type is active |
AppliesToOrders | boolean | The field indicates whether the expenses type is applied to order |
AppliesToUsers | boolean | The field indicates whether the expenses type is applied to users |
CrmExpenseGroup | The table contains the groups of the expenses | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the code of the group |
Name | string | The field contains the name of the group |
Ordering | int | The field contains the order in which groups are displayed |
Active | Boolean | The field indicates whether the expenses group is active |
CrmExpenseSitesLink | The table serves as a link between the expenses and the sites(shops) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
ExpenseId | GUID | The field contains expenses ID as a link to the CRMExpense table |
SiteId | GUID | The field contains the site ID as a link to the CRMSite table |
CrmUser | The table contains users of the source system | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. It is the User Id |
CreateDate | datetime | The field contains the date when the user was created |
Active | boolean | The field indicates whether the user is active |
string | The field contains the email | |
Name | string | The field contains the user name |
Phone | string | The field contains the phone number |
Status | string | The field shows the status of the user in the system |
IsAdmin | boolean | The field indicates whether the user is an administrator |
IsManager | boolean | The field indicates whether the user is a manager |
Birthday | datetime | The field contains the user date of birth |
Position | string | The field contains the user position |
Locale | string | The field contains the user location |
ContactId | GUID | The field contains the contact ID as a link to the CrmCustomer table |
Currency | string | The field contains the main currency |
Source | GUID | The field contains the source ID as a link to the CrmCustomer table |
CrmUserGroup | The table contains user groups in the source system | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains the group code |
Name | string | The field contains the group name |
GetUsersGroup | boolean | The field indicates whether it is a group of managers |
IsDeliveryMen | boolean | The field indicates whether it is a delivery group |
CreatedDate | DateTime | The field contains the date when the group was created |
OrigId2 | string | The field contains the Entity ID from original source system. It is the Gruop Id |
CRMUserGroupLink | The table contains as a link between the user and group | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
UserId | GUID | The field contains the user ID as a link to the CrmUser table |
UserGroupId | GUID | The field contains the group ID as a link to the CrmUserGroup table |
CrmCustomer | The table contains company’s customers. Retail clients, corporate clients, contacts… | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Type | string | The field contains the type of customer. For example: client, presenter, contact, partner, etc. |
OrigId2 | string | The field contains the Entity ID from original source system. It is the Customer Id. |
NickName | string | The field contains the nickname of the customer |
IsContact | boolean | The field indicates whether the customer is a contact |
CreateDate | datetime | The field contains the date when the customer was created in the system |
ManagerId | GUID | The field contains the manager ID as a link to the CRMUser table |
Vip | boolean | The field indicates whether the customer is a VIP |
Bad | boolean | The field indicates whether the customer is bad |
SiteId | GUID | The field contains the site ID as a link to the CRMSite table |
ContragentType | string | The field contains the type of the contragent. For example: individual; legal-entity; enterpreneur … |
ContragentLegalName | string | The field contains the legal name of the contragent |
DiscountCardNumber | string | The field contains the discount card number |
Name | string | The field contains the name of the contragent |
Sex | string | The field contains the gender of the contragent |
string | The field contains the email of the contragent | |
BirthDay | datetime | The field contains the date of birth of the contragent |
Comment | string | The field contains the manager’s comment |
Source | string | The field contains the source of the contragent |
SourceDescription | string | The field contains the sourcecontragentdescription |
Status | GUID | The field contains the status ID as a link to the CrmOrderStatus table |
StatusDescription | string | The field contains the status description |
isPersonAccount | boolean | The field indicates whether the contragent has a person account |
Industry | string | The field contains the contragent’s industry. For example: –None–, Agriculture, Apparel, Banking, Biotechnology, Chemicals, Communications… |
NumberOfEmployees | int | The field contains the number of employees the client has |
Ownership | string | The field contains the clients’ ownership information: Private, Public, Subsidiary… |
IsDeleted | boolean | The field indicates whether the object has been moved to the Recycle Bin (true) or not (false). |
NaicsCode | string | The field contains the six-digit North American Industry Classification System (NAICS) code is the standard used by business and government to classify business establishments into industries |
Duns Number | string | The field contains the Data Universal Numbering System (D-U-N-S) number |
Website | url | The field contains the client’s website |
Skype | string | The field contains the skype |
AnnualRevenue | int | The field contains the client’s annual income |
Currency | string | The field contains the currency of the client’s annual income |
Position | string | The field contains the contact position of the client |
Price | decimal | The field contains the price of the property with the contact’s first inquiry |
LeadsConvertedFrom | GUID | The field contains the link to CRMCustomer table |
ConvertedDate | datetime | The field contains the date when the lead was converted to opportunity |
CrmBankAccount | The table contains information about banks and accounts | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. |
CustomerId | GUID | The field contains the original customer ID. And it is a link to the CRMCustomer table |
CompanyId | GUID | The field contains the original company ID. And it is a link to the CRMCompany table |
Name | string | The field contains the name of the bank |
BankAccount | string | The field contains the bank account |
Currency | string | The field contains the currency of the bank account |
Bik | string | The field contains the internal bank ID |
SWIFT | string | The field contains the SWIFT |
Address | string | The field contains the bank address |
CorrAccount | string | The field contains a correspondent bank account |
Mfo | string | The field contains bank code( so-called MFO) |
Comment | string | The field contains a comment |
CrmLegalCode | The table contains information about customer identifiers | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CustomerId | GUID | The field contains the original customer ID. And it is a link to the CRMCustomer table |
CompanyId | GUID | The field contains the original company ID. And it is a link to the CRMCompany table |
Code | string | The field contains the code of the identifier |
Value | string | The field contains the value of the identifier |
Description | string | The field contains a description of the identifier |
CrmCustPhones | The table contains information about customer phones | |
CustomerId | GUID | The field contains the original customer ID. And it is a link to the CRMCustomer table |
Phone | string | The field contains the phone number |
Mobile Phone | string | The field contains the mobile phone number |
CrmCustomerContact | The table contains as a link for the customer and contact | |
CustomerId | GUID | The field contains original customer ID. And it is a link to the CRMCustomer table |
ContactId | GUID | The field contains original contact ID. And it is a link to the CRMCustomer table |
IsMain | boolean | The field indicates whether it is is the main contact for the customer |
CrmCompany | The table contains information about the customer’s companies | |
ID | GUID | Internal table ID. Primary key. Unique |
OrigId | string | The field contains the Entity ID from original source system. It is the Client company Id |
Name | string | The field contains the name of the company |
Active | boolean | The field indicates whether the company is active |
Brand | string | The field contains the brand name |
Site | string | The field contains the website of the company |
string | The field contains the email of the company | |
Address | string | The field contains the address of the company |
CreateDate | datetime | The field contains the date of creation of the company |
ContragentType | string | The field contains the type of the client. For example: individual; legal-entity; enterpreneur … |
ContragentLegalName | string | The field contains the legal name of the client |
CrmCustomerCompany | The table serves as a link between the customer and the company | |
CustomerId | GUID | The field contains original customer ID. And serves as a link to the CRMCustomer table |
CompanyId | GUID | The field contains original company ID. And serves as a link to the CRMCompany table |
isMain | boolean | The field indicates whether this is the main company for the customer |
CrmSegment | The table contains information about customer segments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. Segment Id in our case |
Code | string | The field contains the code of the segment. (Example: has_order_cancelled_30_days; man_not_bought_shipmodels; has_order_completed_14_Previous_days…) |
Name | string | The field contains a name |
CreateDate | datetime | The field contains the date the segment was created |
Dynamic | boolean | The field indicates whether the segment is dynamic |
Active | boolean | The field indicates whether the segment is active |
CrmCustomerSegmentLink | The table serves as a link between the customer and the segment | |
CustomerId | GUID | The field contains original customer ID. And serves as a link to the CRMCustomer table |
SegmentId | GUID | The field contains original segment ID. And serves as a link to the CRMSegment table |
CrmMetric | The table contains information about the customer metrics | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CustomerId | GUID | The field contains original customer ID. And serves as a link to the CRMCustomer table |
CompanyId | GUID | The field contains original company ID. And serves as a link to the CRMCompany table |
AvgMarginSumm | decimal | The field contains the average gross margin for customer orders |
MarginSumm | decimal | The field contains the LTV |
TotalSumm | decimal | The field contains the total orders amount |
AverageSumm | decimal | The field contains the average orders amount |
OrdersCount | int | The field contains the orders number |
ExpenseSumm | decimal | The field contains the cost summ |
PersonalDiscount | decimal | The field contains the personal discount |
CumulativeDiscount | decimal | The field contains the cumulative discount |
CrmCountry | The table contains list of the countries | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains country name |
Alpha2Code | string | The field contains international country Alpha-2 code |
Alpha3Code | string | The field contains international country Alpha-3 Code |
NumericCode | string | The field contains international country Numeric Code |
CrmAddress | The table contains information about addresses for different entities | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
CustomerId | GUID | The field contains original customer ID. And serves as a link to the CRMCustomer table |
CompanyId | GUID | The field contains original company ID. And serves as a link to the CRMCompany table |
StoreId | GUID | The field contains original store ID. And serves as a link to the CRMStore table |
BusinessInfoId | GUID | The field contains original business info ID. And serves as a link to the CRMBusinessInfo table |
Type | string | The field contains the address type. For example: legal, contact, main, billing, shipping … |
CountryId | GUID | The field contains original country ID. And serves as a link to the CRMCountries table |
City | string | The field contains the city |
FullAddr | string | The field contains the full address string |
ZIP | string | The field contains a ZIP code |
Notes | string | The field contains notes |
CrmShipment | The table contains information about the shipments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. Shipment Id in our case |
DeliveryTypeId | GUID | The field contains the delivery type ID. And serves as a link to the CRMDeliveryType table |
StoreId | GUID | The field contains the store ID. And serves as a link to the CRMStore table |
ManagerId | GUID | The field contains the manager ID. And serves as a link to the CRMUser table |
Status | string | The field contains the status |
Date | datetime | The field contains the shipment date |
TimeFrom | datetime | The field contains the start time of shipment |
TimeTo | datetime | The field contains the end time of shipment |
Comment | string | The field contains a comment |
CrmDeliveryType | The table contains information about the types of delivery | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the delivery |
Code | string | The field contains the symbol code of the delivery |
Active | boolean | The field indicates whether the type is active |
DefaultCost | decimal | The field contains the default cost for the type |
DefaultNetCost | decimal | The field contains the default net cost for the type |
Description | string | The field contains a description |
DefaultForCrm | boolean | The field indicates whether the type is default for the CRM |
VatRate | string | The field contains the vat rate for the type |
CrmPayment | The table contains information about payments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId2 | string | The field contains the Entity ID from original source system. Payment Id in our case |
Name | string | The field contains the name of the payment |
Code | string | The field contains the code of the payment |
Summ | decimal | The field contains the payment amount |
Currency | string | The field contains the currency of the payment |
DateBill | datetime | The field contains the bill date of payment |
DatePayed | datetime | The field contains the date of payment |
DateCanceled | datetime | The field contains the date when the payment was canceled |
DateStatus | datetime | The field contains the date when the payment status was changed |
Comments | string | The field contains a comment |
Payed | bool | The field indicates whether payment is made |
MarkedReason | string | The field contains the reason for payment or to cancel |
ManagerId | GUID | The field contains the manager ID. And serves as a link to the CRMUser table |
PaymentStatusId | GUID | The field contains the payment status ID. And serves as a link to the CRMPaymentStatus table |
Description | string | The field contains a description |
CustomerId | GUID | The field contains the customer ID. And serves as a link to the CRMCustomer table |
CompanyId | GUID | The field contains the company ID. And serves as a link to the CRMCompany table |
OrderId | GUID | The field contains the order ID. And serves as a link to the CrmOrder table |
PaymentTypeId | GUID | The field contains the payment type ID. And serves as a link to the CrmpaymentType table |
PaymentMethod | string | The field contains the method of payment |
CrmPaymentType | The table contains information about the types of payments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains a type code |
Name | string | The field contains a type name |
Active | boolean | The field indicates whether the type is active |
DefaultForCrm | boolean | The field indicates whether the type is default for CRM |
DefaultForApi | boolean | The field indicates whether the type is default for API |
Description | string | The field contains a description |
CrmDeliveryPaymentLink | The table serves as a link between delivery and payment | |
DeliveryTypeId | GUID | The field contains the delivery type ID. And serves as a link to the CRMDeliveryType table |
PaymentTypeId | GUID | The field contains the payment type ID. And serves as a link to the CRMPaymentType table |
CrmPaymentStatus | The table contains information about the statuses of payments | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Code | string | The field contains a status code |
Name | string | The field contains a status name |
Active | boolean | The field indicates whether the status is active |
DefaultForCrm | boolean | The field indicates whether the status is default for CRM |
DefaultForApi | boolean | The field indicates whether the status is default for API |
Description | string | The field contains a description |
PaymentComplete | boolean | The field indicates whether the payment is complete |
Ordering | int | The field indicates the order sequence |
CrmPaymentTypeStatusLink | The table serves as a link between the PaymentType and the PaymentStatus | |
PaymentTypeId | GUID | The field contains the payment type ID. And serves as a link to the CRMPaymentType table |
PaymentStatusId | GUID | The field contains the payment status ID. And serves as a link to the CRMPaymentStatus table |
CrmDeliveryService | The table contains information about the delivery service | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Name | string | The field contains the name of the delivery service |
Code | string | The field contains the code of the delivery service |
Active | boolean | The field indicates whether the delivery service is active |
CrmDeliveryTypeServiceLink | The table serves as a link between the DeliveryType and the Service | |
DeliveryTypeId | GUID | The field contains the delivery type ID. And serves as a link to the CRMDeliveryType table |
DeliveryServiceId | GUID | The field contains the delivery service ID. And serves as a link to the CRMDeliveryService table |
CrmStore | The table contains information about stores | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrigId | string | The field contains the Entity ID from original source system. Store Id in our case |
Code | string | The field contains a store code |
Name | string | The field contains a store name |
Active | boolean | The field indicates whether the store is active |
Description | string | The field contains a description |
string | The field contains an email | |
Type | string | The field contains the type of store |
InventoryType | string | The field contains the type of inventory in the store |
Phone | string | The field contains a phone number |
CrmOrder | The table contains information about deals (orders) | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
Title | string | The field contains the title of the deal |
Amount | decimal | The field contains the amount of the deal |
Currency | string | The field contains the currency of the deal |
DeliveryCost | decimal | The field contains the delivery cost |
OrigId2 | string | The field contains the Entity ID from original source system. Deal Id in our case |
Number | string | The field contains the number of the deal |
OrderTypeId | GUID | The field contains the deal type ID. And serves as a link to the CRMOrderType table |
MethodId | GUID | The field contains the deal method ID. And serves as a link to the CRMOrderMethod table |
CountryId | GUID | The field contains the country ID. And serves as a link to the CRMCountries table |
Date | datetime | The field contains the date of the deal |
StatusUpdatedAt | datetime | The field contains the date when the status of the deal was updated |
CloseDate | datetime | The field contains the close date of the deal |
WonDate | datetime | The field contains the date of winning the deal |
TotalAmount | decimal | The field contains the total amount of the deal, which includes a discount |
PrepaySum | decimal | The field contains the prepaid amount of the deal |
PurchaseSum | decimal | The field contains the purchase amount of the deal |
PurchaseDate | datetime | The field contains the date of purchase the deal |
Mark | int | The field contains the mark of the deal |
MarkDatetime | datetime | The field contains the date when the deal was evaluated by the customer |
LastName | string | The field contains the last name of the counterparty |
FirstName | string | The field contains the first name of the counterparty |
Patronymic | string | The field contains the patronymic of the counterparty |
Phone | string | The field contains the phone number |
AdditionalPhone | string | The field contains the additional phone number |
string | The field contains the email | |
Call | boolean | The field indicates whether a call is required |
Expired | boolean | The field indicates whether the deal has expired |
CustomerComment | string | The field contains the comment of the customer |
ManagerComment | string | The field contains the comment of the manager |
ManagerId | GUID | The field contains the manager ID. And serves as a link to the CRMUser table |
CustomerId | GUID | The field contains the customer ID. And serves as a link to the CRMCustomer table |
ContactId | GUID | The field contains the contact ID. And serves as a link to the CRMCustomer table |
CompanyId | GUID | The field contains the company ID. And serves as a link to the CRMCompany table |
MarketplaceCode | string | The field contains the code of the marketplace |
Site | string | The field contains a website |
LossReason | string | The field contains the cause of the loss. To be filled in if the order status is lost |
StatusId | GUID | The field contains the order status ID. And serves as a link to the CRMOrderStatus table |
Deleted | boolean | The field indicates whether the order has been deleted |
StageId | GUID | The field contains the stage ID. And serves as a link to the CRMOrderStatus table. Stage of the deal workflow |
Discount | decimal | The field contains the discount |
Tax | decimal | The field contains the tax |
SalesCommission | decimal | The field contains the sales commission |
ExciseDuty | decimal | The field contains the excise duty |
Adjustment | decimal | The field contains the djustment (additional discount) |
Vendor | GUID | The field contains the vendor ID. And serves as a link to the CRMCustomer table |
PurchaseOrderNum | string | The field contains the purchase deal number |
DealId | GUID | The field contains the Opportunity ID. And serves as a link to the CrmOpportunity table |
QuoteId | GUID | The field contains the quote ID. And serves as a link to the CrmQuote table |
CrmOrderDetail | detailes of ordered: Items, sum, quantity | |
ID | GUID | Internal table ID. Primary key. Unique identifier for every record in the table |
OrderID | GUID | link to the CRMOrder table |
ItemId | GUID | link to the CRMItem table |
OfferID | GUID | link to the CRMOffer table |
Price | decimal | item price |
Discount | decimal | item discount amount |
VatRate | decimal | vat rate |