Difference between revisions of "Opentaps Analytics Data Loader"

From Opentaps Wiki
Jump to navigationJump to search
(→‎Examples)
(→‎Magento)
Line 14: Line 14:
  
 
=== Magento ===
 
=== 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
  
 
== API ==
 
== API ==

Revision as of 22:42, 27 May 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

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