Difference between revisions of "Opentaps Analytics Data Loader"

From Opentaps Wiki
Jump to navigationJump to search
(→‎PRODUCT_DIMENSION)
(→‎Magento)
 
(28 intermediate revisions by 3 users not shown)
Line 4: Line 4:
  
 
=== opentaps and OFBiz ===
 
=== opentaps and OFBiz ===
−
analytics-upload component help to gather and upload analytical data to server. A few steps should be done to install it with opentaps or OfBiz instance.
+
 
 +
The <tt>analytics-upload</tt> component helps to gather and upload analytical data to server. A few steps should be done to install it with opentaps or OfBiz instance.
  
 
# Checkout component into hot-deploy. Use <code>svn co svn://svn.opentaps.org/opentaps/modules/analytics-upload/versions/1.5/trunk analytics-upload</code>
 
# Checkout component into hot-deploy. Use <code>svn co svn://svn.opentaps.org/opentaps/modules/analytics-upload/versions/1.5/trunk analytics-upload</code>
 +
# If you are using opentaps 1.0 or 1.4, add it to your <code>hot-deploy/component-load.xml</code> file.
 
# Adjust settings in config/analytics-uploader.properties. Webkey should be the one for your store in opentaps Analytics.
 
# Adjust settings in config/analytics-uploader.properties. Webkey should be the one for your store in opentaps Analytics.
 
# Note there are two ''scripts'' value in configuration file, default one to use with opentaps instance and another for OfBiz.
 
# Note there are two ''scripts'' value in configuration file, default one to use with opentaps instance and another for OfBiz.
 
# Start instance and run '''analytics.uploadAnalyticalData''' service.
 
# Start instance and run '''analytics.uploadAnalyticalData''' service.
 +
 +
=== Magento ===
 +
 +
Status from Magento order items are mapped into opentaps Analytics:
 +
* (PENDING || BACKORDERED) -> PENDING
 +
* (SHIPPED || INVOICED) -> COMPLETED
 +
* (CANCELED)  -> CANCELLED
 +
* (PARTIAL || MIXED) -> one record for the shipped/invoiced as COMPLETED and another one for the missing qty as PENDING
 +
* (RETURNED || REFUNDED) -> one record on return_item_fact with status as COMPLETED
 +
 +
In order to install opentaps Analytics Loader for Magento copy the module files into Magento directory in accordance with their place in source tree. Sources can be found at svn://svn.opentaps.org/opentaps/modules/magento-analytics-upload/versions/1.0/trunk. "Opentaps Analytics Loader" top level menu item should appear in Magento Admin Panel. If no, try to click "Save Config" button on System->Configuration page.
  
 
== API ==
 
== API ==
  
−
Data Loading is done via an HTTP POST servlet.
+
Data Loading is done via an HTTP POST to <nowiki>http://analytics.opentaps.com/analytics/control/loaddata</nowiki>
  
 
The following parameter are required:
 
The following parameter are required:
Line 19: Line 32:
 
* password
 
* password
 
* webkey
 
* webkey
 +
 +
Your userlogin must have the ANALYTICS_DATA_LOAD permission to load data.
  
 
Then the data to load should be given as one parameter per field using the format ''idx.tablename.fieldname=value'' where ''idx'' is a string used both to order the import (if mutliple record depend on each other via a FK) and discriminate which fields are in the same DB row.
 
Then the data to load should be given as one parameter per field using the format ''idx.tablename.fieldname=value'' where ''idx'' is a string used both to order the import (if mutliple record depend on each other via a FK) and discriminate which fields are in the same DB row.
Line 29: Line 44:
 
* 409 Conflict : found the same rows defined twice
 
* 409 Conflict : found the same rows defined twice
 
* 500 Internal error : other exception during import
 
* 500 Internal error : other exception during import
 +
 +
To clear your data, you can send an HTTP POST to <nowiki>http://analytics.opentaps.com/analytics/control/cleardata</nowiki> with your userlogin, password, and webkey.
 +
 +
'''WARNING''': This REALLY will get rid of all your data.
 +
 +
Your userlogin must have the ANALYTICS_DATA_CLEAR permission to clear your data.
  
 
=== Examples ===
 
=== Examples ===
Line 36: Line 57:
 
Posting data in the ''CHANNEL_DIMENSION'':
 
Posting data in the ''CHANNEL_DIMENSION'':
  
−
   curl -i -d userlogin=admin -d password=opentaps -d webkey=thisisatest1234 -d "0.channel_dimension.channel_id=MY_CHANNEL" -d "0.channel_dimension.channel_name=My Channel" <nowiki>http://127.0.0.1:8080/analytics/control/loaddata</nowiki>
+
   curl -i -d userlogin=myuser -d password=mypass -d webkey=thisisatest1234  
 +
  -d "0.channel_dimension.channel_id=MY_CHANNEL"  
 +
  -d "0.channel_dimension.channel_name=My Channel"  
 +
  <nowiki>https://analytics.opentaps.com/analytics/control/loaddata</nowiki>
  
 
Returned Success message would be like:
 
Returned Success message would be like:
Line 53: Line 77:
 
Multiple rows can be inserted in the same request, using the IDX part to order the insertion in case a row needs to reference to a previously loaded row:
 
Multiple rows can be inserted in the same request, using the IDX part to order the insertion in case a row needs to reference to a previously loaded row:
  
−
   curl -i -d userlogin=admin -d password=opentaps -d webkey=thisisatest1234 \
+
   curl -i -d userlogin=myuser -d password=mypass -d webkey=thisisatest1234 \
−
   -d "0.sales_team_dimension.sales_team_id=MY_TEAM" -d "0.sales_team_dimension.sales_team_name=My Sales Team" \
+
   -d "0.sales_team_dimension.sales_team_id=MY_TEAM"  
 +
  -d "0.sales_team_dimension.sales_team_name=My Sales Team" \
 
   -d "1.sales_rep_dimension.sales_rep_id=MY_SALESREP" -d "1.sales_rep_dimension.sales_team_id=MY_TEAM" \
 
   -d "1.sales_rep_dimension.sales_rep_id=MY_SALESREP" -d "1.sales_rep_dimension.sales_team_id=MY_TEAM" \
−
   <nowiki>http://127.0.0.1:8080/analytics/control/loaddata</nowiki>
+
   <nowiki>http://analytics.opentaps.com/analytics/control/loaddata</nowiki>
  
 
Example with bad authentication:
 
Example with bad authentication:
  
−
   curl -i -d userlogin=admin -d password=opentapse -d webkey=thisisatest1234 -d "0.channel_dimension.channel_id=MY_CHANNEL" -d "0.channel_dimension.channel_name=My Channel" <nowiki>http://127.0.0.1:8080/analytics/control/loaddata</nowiki>
+
   curl -i -d userlogin=myuser -d password=mypass -d webkey=thisisatest1234  
 +
  -d "0.channel_dimension.channel_id=MY_CHANNEL"  
 +
  -d "0.channel_dimension.channel_name=My Channel"  
 +
  <nowiki>http://analytics.opentaps.com/analytics/control/loaddata</nowiki>
  
 
   HTTP/1.1 401 Unauthorized
 
   HTTP/1.1 401 Unauthorized
Line 75: Line 103:
 
Example of Error when trying to add data in an in-existing field:
 
Example of Error when trying to add data in an in-existing field:
  
−
   curl -i -d userlogin=admin -d password=opentaps -d webkey=thisisatest1234 -d "0.channel_dimension.channel_id=MY_CHANNEL" -d "0.channel_dimension.channel_description=My Channel" <nowiki>http://127.0.0.1:8080/analytics/control/loaddata</nowiki>
+
   curl -i -d userlogin=myuser -d password=mypass -d webkey=thisisatest1234  
 +
  -d "0.channel_dimension.channel_id=MY_CHANNEL"  
 +
  -d "0.channel_dimension.channel_description=My Channel"  
 +
  <nowiki>http://analytics.opentaps.com/analytics/control/loaddata</nowiki>
  
 
   HTTP/1.1 500 Internal Server Error
 
   HTTP/1.1 500 Internal Server Error
Line 94: Line 125:
  
 
==== CHANNEL_DIMENSION ====
 
==== CHANNEL_DIMENSION ====
 +
 +
This dimension table is for the sales channels of your company (ie, Phone, Online, Catalog, Store.)
  
 
{| border="1" cellspacing="0" cellpadding="5"
 
{| border="1" cellspacing="0" cellpadding="5"
Line 100: Line 133:
 
!width="500"|Description
 
!width="500"|Description
 
|-  
 
|-  
−
| CHANNEL_ID || String (60) || The Channel identifier
+
| CHANNEL_ID || String (60) || Unique ID of the sales channel
 
|-
 
|-
−
| CHANNEL_NAME || String (100) || The Channel name
+
| CHANNEL_NAME || String (100) || Descriptive name of the sales channel
 
|}
 
|}
  
 
==== SALES_TEAM_DIMENSION ====
 
==== SALES_TEAM_DIMENSION ====
 +
 +
This dimension table is for the sales teams of your company.
  
 
{| border="1" cellspacing="0" cellpadding="5"
 
{| border="1" cellspacing="0" cellpadding="5"
Line 112: Line 147:
 
!width="500"|Description
 
!width="500"|Description
 
|-  
 
|-  
−
| SALES_TEAM_ID || String (60) || The Sales team identifier
+
| SALES_TEAM_ID || String (60) || Unique ID of your sales team.
 
|-
 
|-
−
| SALES_TEAM_NAME || String (100) || The Sales team name
+
| SALES_TEAM_NAME || String (100) || Descriptive name of your sales team.
 
|}
 
|}
  
 
==== SALES_REP_DIMENSION ====
 
==== SALES_REP_DIMENSION ====
 +
 +
This dimension table is for the sales representatives of your company.
  
 
{| border="1" cellspacing="0" cellpadding="5"
 
{| border="1" cellspacing="0" cellpadding="5"
Line 124: Line 161:
 
!width="500"|Description
 
!width="500"|Description
 
|-  
 
|-  
−
| SALES_REP_ID || String (60) || The Sales representative identifier
+
| SALES_REP_ID || String (60) || Unique ID of a sales representative.
 
|-
 
|-
−
| SALES_TEAM_ID || String (60) || The Sales team identifier, references SALES_TEAM_DIMENSION
+
| SALES_TEAM_ID || String (60) || ID of the sales team for the sales representative. References SALES_TEAM_DIMENSION
 
|-
 
|-
−
| SALES_TEAM_NAME || String (100) || The Sales team name
+
| SALES_TEAM_NAME || String (100) || Descriptive name of the sales team.
 
|-
 
|-
−
| FIRST_NAME || String (100) || The Sales representative first name
+
| FIRST_NAME || String (100) || First name of the sales representative
 
|-
 
|-
−
| LAST_NAME || String (100) || The Sales representative last name
+
| LAST_NAME || String (100) || Last name of the sales representative
 
|}
 
|}
  
 
==== CUSTOMER_DIMENSION ====
 
==== CUSTOMER_DIMENSION ====
 +
 +
This dimension is for your company's customers.  Currently the table supports address line, so use it for the most meaningful one for your business, ie the customer's home address.
  
 
{| border="1" cellspacing="0" cellpadding="5"
 
{| border="1" cellspacing="0" cellpadding="5"
Line 142: Line 181:
 
!width="500"|Description
 
!width="500"|Description
 
|-  
 
|-  
−
| CUSTOMER_ID || String (60) || The Customer identifier
+
| CUSTOMER_ID || String (60) || The ID of the Customer
 
|-
 
|-
 
| CUSTOMER_NAME || String (255) || The Customer name
 
| CUSTOMER_NAME || String (255) || The Customer name
Line 156: Line 195:
 
| ATTN_NAME || String (100) || The address attention name
 
| ATTN_NAME || String (100) || The address attention name
 
|-
 
|-
−
| STREET_ADDRESS_LINE1 || String (255) || The address street
+
| STREET_ADDRESS_LINE1 || String (255) || Street of the customer's address
−
|-
 
−
| CITY || String (60) || The address city
 
 
|-
 
|-
−
| STATE || String (60) || The address state
+
| CITY || String (60) || City of the customer's address
 
|-
 
|-
−
| REGION || String (60) || The address region
+
| STATE || String (60) || State of the customer's address
 
|-
 
|-
−
| COUNTRY || String (100) || The address country
+
| REGION || String (60) || Region of the customer's address, if relevant
 
|-
 
|-
−
| POSTAL_CODE3DIGIT || String (3) || The address first 3 digits of the postal code
+
| COUNTRY || String (100) || Country of the customer's address
 
|-
 
|-
−
| POSTAL_CODE || String (60) || The address postal code
+
| POSTAL_CODE || String (60) || Full postal code for the customer's address
 
|-
 
|-
−
| PHONE_COUNTRY || String (60) || The phone number country code
+
| PHONE_COUNTRY || String (60) || Country code of the customer's phone number
 
|-
 
|-
−
| PHONE_AREA_CODE || String (60) || The phone number area code
+
| PHONE_AREA_CODE || String (60) || Area code of the customer's phone number
 
|-
 
|-
−
| PHONE_NUMBER || String (60) || The rest of the phone number
+
| PHONE_NUMBER || String (60) || Remaining digits of customer's phone number  
 
|-
 
|-
−
| PRIMARY_EMAIL || String (255) || The Customer primary email address
+
| PRIMARY_EMAIL || String (255) || Customer's primary email address
 
|-
 
|-
−
| OTHER_EMAIL || String (255) || The Customer other email address
+
| OTHER_EMAIL || String (255) || Customer's other email address
 
|-
 
|-
−
| CUSTOMER_TYPE || String (60) || The Customer type
+
| CUSTOMER_TYPE || String (60) || Type for the customer.  Fill in whatever you want.
 
|-
 
|-
−
| SOURCE || String (60) || The source of the Customer
+
| SOURCE || String (60) || Source code of the Customer.  Fill in whatever you want.
 
|-
 
|-
−
| SALES_TEAM_NAME || String (255) || The name of Sales Team associated to the Customer
+
| SALES_TEAM_NAME || String (255) || The name of Sales Team associated to the Customer, from SALES_TEAM_DIMENSION
 
|-
 
|-
−
| CREATED_DATE_AND_TIME || Timestamp || The time and date, given as "YYYY-MM-DD hh:mm:ss"
+
| CREATED_DATE_AND_TIME || Timestamp || Time and date when customer was first created. Format is ''YYYY-MM-DD hh:mm:ss''
 
|}
 
|}
  
 
==== PRODUCT_DIMENSION ====
 
==== PRODUCT_DIMENSION ====
 +
 +
This dimension table contains the products of your company.
  
 
{| border="1" cellspacing="0" cellpadding="5"
 
{| border="1" cellspacing="0" cellpadding="5"
Line 196: Line 235:
 
!width="500"|Description
 
!width="500"|Description
 
|-  
 
|-  
−
| PRODUCT_ID || String (60) || The Product identifier
+
| PRODUCT_ID || String (60) || Unique ID of your product
 
|-
 
|-
 
| SKU || String (60) || The Product SKU code
 
| SKU || String (60) || The Product SKU code
Line 226: Line 265:
 
| PRODUCT_TYPE || String (100) || The Product type
 
| PRODUCT_TYPE || String (100) || The Product type
 
|-
 
|-
−
| SUPPLIER_NAME || String (100) || The Product supplier name
+
| SUPPLIER_NAME || String (100) || Name of the supplier for this product
 
|-
 
|-
−
| ORG_SUPPLIER_ID || String (60) || The Product supplier organization identifier
+
| ORG_SUPPLIER_ID || String (60) || ID of the supplier
 
|-
 
|-
 
| EST_BASE_CURRENCY_COST || Numeric (18,6) || The Product estimated cost
 
| EST_BASE_CURRENCY_COST || Numeric (18,6) || The Product estimated cost
Line 234: Line 273:
  
 
==== ORDER_ITEM_FACT ====
 
==== ORDER_ITEM_FACT ====
 +
 +
This fact table contains all the line items of your orders.  It also includes any returns of the order items.
  
 
{| border="1" cellspacing="0" cellpadding="5"
 
{| border="1" cellspacing="0" cellpadding="5"
Line 240: Line 281:
 
!width="500"|Description
 
!width="500"|Description
 
|-  
 
|-  
−
| ORDER_ID || String (60) ||  
+
| ORDER_ID || String (60) || ID of the order
 
|-
 
|-
−
| ORDER_ITEM_SEQ_ID || String (100) ||  
+
| ORDER_ITEM_SEQ_ID || String (100) || ID for the item within the order, ie 1, 2, 3, ...
 
|-
 
|-
−
| CUSTOMER_ID || String (60) ||  
+
| CUSTOMER_ID || String (60) || The customer identifier, references the CUSTOMER_DIMENSION
 
|-
 
|-
−
| CHANNEL_ID || String (60) ||  
+
| CHANNEL_ID || String (60) || The channel identifier, references the CHANNEL_DIMENSION
 
|-
 
|-
−
| SALES_REP_ID || String (60)||  
+
| SALES_REP_ID || String (60)|| The sales representative identifier, references the SALES_REP_DIMENSION
 
|-
 
|-
−
| SALES_REP_TEAM_ID || String (60) ||  
+
| SALES_TEAM_ID || String (60) || The sales team identifier, references the SALES_TEAM_DIMENSION
 
|-
 
|-
−
| ORDER_DATE || String (Date) || Format is ''YYYY-MM-DD''
+
| ORDER_DATE || String (Date) || The date the order was placed, format is ''YYYY-MM-DD''
 
|-
 
|-
−
| PRODUCT_ID || String (60) ||  
+
| PRODUCT_ID || String (60) || The product identifier, references the PRODUCT_DIMENSION
 
|-
 
|-
−
| QUANTITY || Numeric (18,6) ||  
+
| QUANTITY || Numeric (18,6) || The quantity of item ordered
 
|-
 
|-
−
| GROSS_AMOUNT_BASE_CURRENCY || Numeric (18,2) ||  
+
| GROSS_AMOUNT_BASE_CURRENCY || Numeric (18,2) || Gross amount of the order item
 
|-
 
|-
−
| NET_AMOUNT_BASE_CURRENCY || Numeric (18,2) ||  
+
| NET_AMOUNT_BASE_CURRENCY || Numeric (18,2) || Net amount of the order item, ie gross amount minus discount
 
|-
 
|-
−
| DISCOUNT_AMOUNT_BASE_CURRENCY || Numeric (18,2)||  
+
| DISCOUNT_AMOUNT_BASE_CURRENCY || Numeric (18,2)|| Amount of discount on order item
 
|-
 
|-
−
| RESOLUTION_STATUS || String (25) ||  
+
| RESOLUTION_STATUS || String (25) || Status of the order item.  Acceptable values are: PENDING, COMPLETED, CANCELLED, REJECTED.  Please map your order status to one of these.
 
|-
 
|-
−
| RETURNED_QUANTITY || Numeric (18,6) ||  
+
| RETURNED_QUANTITY || Numeric (18,6) || The quantity of the item that was returned
 
|-
 
|-
−
| RETURNED_GROSS_AMOUNT_BASE_CURRENCY || Numeric (18,2) ||  
+
| RETURNED_GROSS_AMOUNT_BASE_CURRENCY || Numeric (18,2) || Gross amount of return associated with order item
 
|-
 
|-
−
| RETURNED_DISCOUNT_AMOUNT_BASE_CURRENCY || Numeric (18,2) ||  
+
| RETURNED_DISCOUNT_AMOUNT_BASE_CURRENCY || Numeric (18,2) || Discount on amount returned
 
|-
 
|-
−
| RETURNED_NET_AMOUNT_BASE_CURRENCY || Numeric (18,2) ||  
+
| RETURNED_NET_AMOUNT_BASE_CURRENCY || Numeric (18,2) || Net amount of return
 
|}
 
|}
  
 
==== RETURN_ITEM_FACT ====
 
==== RETURN_ITEM_FACT ====
 +
 +
This fact table is for returns.
  
 
{| border="1" cellspacing="0" cellpadding="5"
 
{| border="1" cellspacing="0" cellpadding="5"
Line 282: Line 325:
 
!width="500"|Description
 
!width="500"|Description
 
|-  
 
|-  
−
| RETURN_ID || String (60) ||  
+
| RETURN_ID || String (60) || Unique ID of the return
 
|-
 
|-
−
| RETURN_ITEM_SEQ_ID || String (100) ||  
+
| RETURN_ITEM_SEQ_ID || String (100) || ID of the item within the return
 
|-
 
|-
−
| CUSTOMER_ID || String (60) ||  
+
| CUSTOMER_ID || String (60) || The customer identifier, references the CUSTOMER_DIMENSION
 
|-
 
|-
−
| CHANNEL_ID || String (60) ||  
+
| CHANNEL_ID || String (60) || The channel identifier, references the CHANNEL_DIMENSION
 
|-
 
|-
−
| ORDER_DATE || String (Date) || Format is ''YYYY-MM-DD''
+
| ORDER_DATE || String (Date) || The date the original order was placed, format is ''YYYY-MM-DD''
 
|-
 
|-
−
| RETURN_DATE || String (Date) || Format is ''YYYY-MM-DD''
+
| RETURN_DATE || String (Date) || The date the return was performed, format is ''YYYY-MM-DD''
 
|-
 
|-
−
| PRODUCT_ID || String (60) ||  
+
| PRODUCT_ID || String (60) || The product identifier, references the PRODUCT_DIMENSION
 
|-
 
|-
−
| QUANTITY || Numeric (18,6) ||  
+
| QUANTITY || Numeric (18,6) || The quantity of product returned
 
|-
 
|-
−
| GROSS_AMOUNT_BASE_CURRENCY || Numeric (18,2) ||  
+
| GROSS_AMOUNT_BASE_CURRENCY || Numeric (18,2) || Gross amount being returned
 
|-
 
|-
−
| NET_AMOUNT_BASE_CURRENCY || Numeric (18,2) ||  
+
| NET_AMOUNT_BASE_CURRENCY || Numeric (18,2) || Net amount being returned, or gross - discount
 
|-
 
|-
−
| DISCOUNT_AMOUNT_BASE_CURRENCY || Numeric (18,2) ||  
+
| DISCOUNT_AMOUNT_BASE_CURRENCY || Numeric (18,2) || Discount for the amount being returned
 
|-
 
|-
−
| RESOLUTION_STATUS || String (25) ||  
+
| RESOLUTION_STATUS || String (25) || Status of the return.  Acceptable values are: PENDING, COMPLETED, CANCELLED, REJECTED. Please map your return status to one of these. 
 
|}
 
|}

Latest revision as of 20:19, 30 June 2011

Uploaders

opentaps and OFBiz

The analytics-upload component helps to gather and upload analytical data to server. A few steps should be done to install it with opentaps or OfBiz instance.

  1. Checkout component into hot-deploy. Use svn co svn://svn.opentaps.org/opentaps/modules/analytics-upload/versions/1.5/trunk analytics-upload
  2. If you are using opentaps 1.0 or 1.4, add it to your hot-deploy/component-load.xml file.
  3. Adjust settings in config/analytics-uploader.properties. Webkey should be the one for your store in opentaps Analytics.
  4. Note there are two scripts value in configuration file, default one to use with opentaps instance and another for OfBiz.
  5. Start instance and run analytics.uploadAnalyticalData service.

Magento

Status from Magento order items are mapped into opentaps Analytics:

  • (PENDING || BACKORDERED) -> PENDING
  • (SHIPPED || INVOICED) -> COMPLETED
  • (CANCELED) -> CANCELLED
  • (PARTIAL || MIXED) -> one record for the shipped/invoiced as COMPLETED and another one for the missing qty as PENDING
  • (RETURNED || REFUNDED) -> one record on return_item_fact with status as COMPLETED

In order to install opentaps Analytics Loader for Magento copy the module files into Magento directory in accordance with their place in source tree. Sources can be found at svn://svn.opentaps.org/opentaps/modules/magento-analytics-upload/versions/1.0/trunk. "Opentaps Analytics Loader" top level menu item should appear in Magento Admin Panel. If no, try to click "Save Config" button on System->Configuration page.

API

Data Loading is done via an HTTP POST to http://analytics.opentaps.com/analytics/control/loaddata

The following parameter are required:

  • userlogin
  • password
  • webkey

Your userlogin must have the ANALYTICS_DATA_LOAD permission to load data.

Then the data to load should be given as one parameter per field using the format idx.tablename.fieldname=value where idx is a string used both to order the import (if mutliple record depend on each other via a FK) and discriminate which fields are in the same DB row.

Possible HTTP error codes are:

  • 400 Bad Request : missing parameter, invalid field name, bad format, no data to load
  • 401 Unauthorized : authentication failed
  • 404 Not Found : given webkey does not correspond to an existing Store
  • 403 Forbidden : tried to modify a table or field that cannot be modified
  • 409 Conflict : found the same rows defined twice
  • 500 Internal error : other exception during import

To clear your data, you can send an HTTP POST to http://analytics.opentaps.com/analytics/control/cleardata with your userlogin, password, and webkey.

WARNING: This REALLY will get rid of all your data.

Your userlogin must have the ANALYTICS_DATA_CLEAR permission to clear your data.

Examples

The following examples are using the curl command line utility to perform the HTTP POSTs.

Posting data in the CHANNEL_DIMENSION: 

 curl -i -d userlogin=myuser -d password=mypass -d webkey=thisisatest1234 
  -d "0.channel_dimension.channel_id=MY_CHANNEL" 
  -d "0.channel_dimension.channel_name=My Channel" 
  https://analytics.opentaps.com/analytics/control/loaddata

Returned Success message would be like: 

 HTTP/1.1 200 OK
 ...
 <html>
 <head>
 <title>Dataload: success</title>
 </head>
 <body class="success">
 Successfully loaded 1 rows
 </body>
 </html>

Multiple rows can be inserted in the same request, using the IDX part to order the insertion in case a row needs to reference to a previously loaded row: 

 curl -i -d userlogin=myuser -d password=mypass -d webkey=thisisatest1234 \
  -d "0.sales_team_dimension.sales_team_id=MY_TEAM" 
  -d "0.sales_team_dimension.sales_team_name=My Sales Team" \
  -d "1.sales_rep_dimension.sales_rep_id=MY_SALESREP" -d "1.sales_rep_dimension.sales_team_id=MY_TEAM" \
  http://analytics.opentaps.com/analytics/control/loaddata

Example with bad authentication: 

 curl -i -d userlogin=myuser -d password=mypass -d webkey=thisisatest1234 
  -d "0.channel_dimension.channel_id=MY_CHANNEL" 
  -d "0.channel_dimension.channel_name=My Channel" 
  http://analytics.opentaps.com/analytics/control/loaddata

 HTTP/1.1 401 Unauthorized
 ...
 <html>
 <head>
 <title>Dataload: error</title>
 </head>
 <body class="error">
 Authentication failed.
 </body>
 </html>

Example of Error when trying to add data in an in-existing field: 

 curl -i -d userlogin=myuser -d password=mypass -d webkey=thisisatest1234 
  -d "0.channel_dimension.channel_id=MY_CHANNEL" 
  -d "0.channel_dimension.channel_description=My Channel" 
  http://analytics.opentaps.com/analytics/control/loaddata

 HTTP/1.1 500 Internal Server Error
 ...
 <html>
 <head>
 <title>Dataload: error</title>
 </head>
 <body class="error">
 Data Load error: Error processing the SQL Query
 Caused by: Unknown column 'CHANNEL_DESCRIPTION' in 'field list'
 </body>
 </html>

Data Model Reference

The following tables and fields can be given:

CHANNEL_DIMENSION

This dimension table is for the sales channels of your company (ie, Phone, Online, Catalog, Store.)

Field name Type Description
CHANNEL_ID String (60) Unique ID of the sales channel
CHANNEL_NAME String (100) Descriptive name of the sales channel

SALES_TEAM_DIMENSION

This dimension table is for the sales teams of your company.

Field name Type Description
SALES_TEAM_ID String (60) Unique ID of your sales team.
SALES_TEAM_NAME String (100) Descriptive name of your sales team.

SALES_REP_DIMENSION

This dimension table is for the sales representatives of your company.

Field name Type Description
SALES_REP_ID String (60) Unique ID of a sales representative.
SALES_TEAM_ID String (60) ID of the sales team for the sales representative. References SALES_TEAM_DIMENSION
SALES_TEAM_NAME String (100) Descriptive name of the sales team.
FIRST_NAME String (100) First name of the sales representative
LAST_NAME String (100) Last name of the sales representative

CUSTOMER_DIMENSION

This dimension is for your company's customers. Currently the table supports address line, so use it for the most meaningful one for your business, ie the customer's home address.

Field name Type Description
CUSTOMER_ID String (60) The ID of the Customer
CUSTOMER_NAME String (255) The Customer name
COMPANY_NAME String (255) The Customer company name
INDUSTRY String (60) The Customer industry
FIRST_NAME String (100) The Customer first name
LAST_NAME String (100) The Customer last name
ATTN_NAME String (100) The address attention name
STREET_ADDRESS_LINE1 String (255) Street of the customer's address
CITY String (60) City of the customer's address
STATE String (60) State of the customer's address
REGION String (60) Region of the customer's address, if relevant
COUNTRY String (100) Country of the customer's address
POSTAL_CODE String (60) Full postal code for the customer's address
PHONE_COUNTRY String (60) Country code of the customer's phone number
PHONE_AREA_CODE String (60) Area code of the customer's phone number
PHONE_NUMBER String (60) Remaining digits of customer's phone number
PRIMARY_EMAIL String (255) Customer's primary email address
OTHER_EMAIL String (255) Customer's other email address
CUSTOMER_TYPE String (60) Type for the customer. Fill in whatever you want.
SOURCE String (60) Source code of the Customer. Fill in whatever you want.
SALES_TEAM_NAME String (255) The name of Sales Team associated to the Customer, from SALES_TEAM_DIMENSION
CREATED_DATE_AND_TIME Timestamp Time and date when customer was first created. Format is YYYY-MM-DD hh:mm:ss

PRODUCT_DIMENSION

This dimension table contains the products of your company.

Field name Type Description
PRODUCT_ID String (60) Unique ID of your product
SKU String (60) The Product SKU code
UPC String (60) The Product UPC code
PRODUCT_NAME String (100) The Product name
BRAND String (100) The Product brand
CATEGORY String (100) The Product category name
SIZE String (100) The Product size
STYLE String (100) The Product style
COLOR String (100) The Product color
WIDTH Numeric (18,6) The Product width
LENGTH Numeric (18,6) The Product length
HEIGHT Numeric (18,6) The Product height
WEIGHT Numeric (18,6) The Product weight
DIAMETER Numeric (18,6) The Product diameter
PRODUCT_TYPE String (100) The Product type
SUPPLIER_NAME String (100) Name of the supplier for this product
ORG_SUPPLIER_ID String (60) ID of the supplier
EST_BASE_CURRENCY_COST Numeric (18,6) The Product estimated cost

ORDER_ITEM_FACT

This fact table contains all the line items of your orders. It also includes any returns of the order items.

Field name Type Description
ORDER_ID String (60) ID of the order
ORDER_ITEM_SEQ_ID String (100) ID for the item within the order, ie 1, 2, 3, ...
CUSTOMER_ID String (60) The customer identifier, references the CUSTOMER_DIMENSION
CHANNEL_ID String (60) The channel identifier, references the CHANNEL_DIMENSION
SALES_REP_ID String (60) The sales representative identifier, references the SALES_REP_DIMENSION
SALES_TEAM_ID String (60) The sales team identifier, references the SALES_TEAM_DIMENSION
ORDER_DATE String (Date) The date the order was placed, format is YYYY-MM-DD
PRODUCT_ID String (60) The product identifier, references the PRODUCT_DIMENSION
QUANTITY Numeric (18,6) The quantity of item ordered
GROSS_AMOUNT_BASE_CURRENCY Numeric (18,2) Gross amount of the order item
NET_AMOUNT_BASE_CURRENCY Numeric (18,2) Net amount of the order item, ie gross amount minus discount
DISCOUNT_AMOUNT_BASE_CURRENCY Numeric (18,2) Amount of discount on order item
RESOLUTION_STATUS String (25) Status of the order item. Acceptable values are: PENDING, COMPLETED, CANCELLED, REJECTED. Please map your order status to one of these.
RETURNED_QUANTITY Numeric (18,6) The quantity of the item that was returned
RETURNED_GROSS_AMOUNT_BASE_CURRENCY Numeric (18,2) Gross amount of return associated with order item
RETURNED_DISCOUNT_AMOUNT_BASE_CURRENCY Numeric (18,2) Discount on amount returned
RETURNED_NET_AMOUNT_BASE_CURRENCY Numeric (18,2) Net amount of return

RETURN_ITEM_FACT

This fact table is for returns.

Field name Type Description
RETURN_ID String (60) Unique ID of the return
RETURN_ITEM_SEQ_ID String (100) ID of the item within the return
CUSTOMER_ID String (60) The customer identifier, references the CUSTOMER_DIMENSION
CHANNEL_ID String (60) The channel identifier, references the CHANNEL_DIMENSION
ORDER_DATE String (Date) The date the original order was placed, format is YYYY-MM-DD
RETURN_DATE String (Date) The date the return was performed, format is YYYY-MM-DD
PRODUCT_ID String (60) The product identifier, references the PRODUCT_DIMENSION
QUANTITY Numeric (18,6) The quantity of product returned
GROSS_AMOUNT_BASE_CURRENCY Numeric (18,2) Gross amount being returned
NET_AMOUNT_BASE_CURRENCY Numeric (18,2) Net amount being returned, or gross - discount
DISCOUNT_AMOUNT_BASE_CURRENCY Numeric (18,2) Discount for the amount being returned
RESOLUTION_STATUS String (25) Status of the return. Acceptable values are: PENDING, COMPLETED, CANCELLED, REJECTED. Please map your return status to one of these.
Retrieved from "https://docs.opentaps.org/docs/index.php?title=Opentaps_Analytics_Data_Loader&oldid=7094"