View source for Opentaps Analytics Data Loader

__TOC__

== Uploaders ==

=== opentaps and OFBiz ===

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

=== 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 <nowiki>http://analytics.opentaps.com/analytics/control/loaddata</nowiki>

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

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" 
   <nowiki>https://analytics.opentaps.com/analytics/control/loaddata</nowiki>

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" \
   <nowiki>http://analytics.opentaps.com/analytics/control/loaddata</nowiki>

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" 
   <nowiki>http://analytics.opentaps.com/analytics/control/loaddata</nowiki>

  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" 
   <nowiki>http://analytics.opentaps.com/analytics/control/loaddata</nowiki>

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

{| border="1" cellspacing="0" cellpadding="5"
!width="200"|Field name
!width="200"|Type
!width="500"|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.

{| border="1" cellspacing="0" cellpadding="5"
!width="200"|Field name
!width="200"|Type
!width="500"|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.

{| border="1" cellspacing="0" cellpadding="5"
!width="200"|Field name
!width="200"|Type
!width="500"|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.

{| border="1" cellspacing="0" cellpadding="5"
!width="200"|Field name
!width="200"|Type
!width="500"|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.

{| border="1" cellspacing="0" cellpadding="5"
!width="200"|Field name
!width="200"|Type
!width="500"|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.

{| border="1" cellspacing="0" cellpadding="5"
!width="200"|Field name
!width="200"|Type
!width="500"|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.

{| border="1" cellspacing="0" cellpadding="5"
!width="200"|Field name
!width="200"|Type
!width="500"|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.  
|}