This document provides step-by-step instructions for importing data from One Click Retail. The following will be covered:
One Click Retail Data Integration
To integrate One Click Retail data into your Panoply warehouse using default settings, complete the following steps.
- Click Data Sources in the navigation menu.
- Click the Add Data Source button.
- In the Data Sources - Choose Source Type window, select One Click Retail. One Click Retail is listed under APIs.
- In the Data Sources – One Click Retail screen, enter your login credentials and click Login.
- In the Data Sources – One Click Retail, screen, select which data to import.
- (Optional) To Customize the ingestion from your data source, review the advanced options.
- Click Collect.
The Data Sources – One Click Retail window will go gray while the data integration is pending. A small green progress bar will appear below One Click Retail once the integration has begun. A prompt will appear asking if you would like to set up the integration of another data source. Multiple data integrations can be set up without impacting the ingestion of the already scheduled or pending data integrations.
From the Data Sources main menu you can monitor the data ingestion status of the scheduled and pending data integrations. After the data ingestion is complete, you can clean or transform your data in the tables menu.
Advanced Options
Clicking Show next to Advanced will expand the Data Sources - One Click Retail window to include Destination, Primary Key, Exclude, Parse String and Truncate table.
Destination - Default is ocr_{__tablename}
, where __tablename
is the table name from the schema for this data source. See Data Schema for more detail about each table. Primary Key - Default is id
. The primary key here determines which field(s) to use as the deduplication key when ingesting data.
Data schema
The One Click Retail data elements ingested by Panoply are listed below. One Click Retail data is collected from the ` https://api.oneclickretail.com/v3/clients/563a2d9c7371f/reports/csv` API endpoint.
OCR_reports - A collection of data describing products bought/sold, the type of basket they appeared in, and the geographical locations of shoppers.
Internal fields - In addition to the data schema details noted below, Panoply creates __updatetime
and __senttime
internal fields on all tables.
OCR_reports
As noted above, this data is collected from the https://api.oneclickretail.com/v3/clients/563a2d9c7371f/reports/csv
API endpoint. In Panoply, the default One Click Retail data table is ocr_reports_csv
and includes the following fields:
Column Name | Data Type | Description |
---|---|---|
id |
Text | Unique ID |
asin |
Text | Amazon Standard Identification Number (ASIN) |
week_asin |
Text | Week combined with ASIN for identification |
title |
Text | Title of product page |
platform |
Text | Platform for product |
type |
Text | Type of product |
brand |
Text | Brand of product |
sub brand |
Text | Subcategory if exists within brand |
category |
Text | Category of product |
client product group |
Text | Targeted client group (i.e. Consumer / Professional) |
manufacturer |
Text | Manufacturer of product |
subcategory |
Text | Subcategory of product if multiple options exist within “category” |
upc |
Text | Universal Product Code (UPC) |
amazon sub category |
Text | Sub category according to Amazon classification |
similar asin grouping |
Text | Product grouping |
vat |
Number | VAT amount |
raw_mtd_replenishment_code_item_availability |
Text | Item availability code. Potential values: ‘obsolete’, ‘new product’, ‘off season’ |
exclusively_for_prime |
Text | Boolean indicating whether product is sold exclusively through Amazon Prime |
units |
Text | Number of units |
units per pack |
Text | Number of units per pack |
packs |
Text | Number of packs |
sales_wow_change |
Text | Week-on-week change in sales for product |
cal_year |
Text | Calendar year |
inventory_unit_forecast_6_week_average |
Text | Unit inventory forecast |
pure_profit_per_unit |
Text | Pure profit per unit |
inventory_weeks_on_hand |
Text | Number of weeks’ worth of inventory on hand |
inventory_weeks_on_hand_6_week_average |
Text | 6-week average value of weeks’ worth of inventory on hand |
cal_week |
Text | Calendar week |
cal_month |
Text | Calendar year |
average_sales_price |
Text | Average sales price of product |
inventory_weeks_on_hand_6_week_max |
Text | Max value of weeks’ worth of inventory from past six months |
sales_4_week_sum |
Text | Total sales for past 4 weeks |
sales |
Text | Sales |
conversion_rate |
Text | Conversion rate of customers |
raw_average_ordered_price |
Text | Average price of product across orders |
unique_visitors |
Text | Unique visitors to product page |
raw_sellable_on_hand_units |
Text | Number of units on hand |
raw_orders |
Text | Number of orders |
raw_ordered_units |
Text | Number of ordered units |
page_views |
Text | Number of views on product page |
raw_list_price |
Text | List price of product |
raw_ordered_amount |
Text | Amount of product ordered |
raw_shipped_amount |
Text | Amount of product shipped |
raw_shipped_cost_of_goods_sold |
Text | Cost of goods sold for shipped units |
raw_conversion_percentile |
Text | Percentile in which conversion rate falls |
raw_shipped_units |
Text | Number of units shipped |
raw_open_purchase_order_quantity |
Text | Quantity of open purchase orders |
raw_customer_reviews |
Text | Number of customer reviews |
raw_average_customer_review |
Text | Average score of customer review |
raw_page_views_trend |
Text | Trend of product page views |
sales_prior_4_week_sum |
Text | Sum of sales for prior 4 weeks |
third_party_ordered_sales |
Text | Sales from 3rd party orders |
second_party_ordered_sales |
Text | Sales from 2nd party orders |
sales_4_week_average |
Text | 4-week sales average |
sales_last_week |
Text | Total sales from the past week |
raw_mtd_percent_lost_buy_box_lbb |
Text | Percentage of buy boxes lost |
raw_mtd_page_view_rank |
Text | Page number displayed on |
raw_mtd_percent_vendor_buy_box_fast_track |
Text | Percentage of vendor’s items eligible for buy box and/or prime |
raw_mtd_percent_replenishable_out_of_stock |
Text | Percentage of replenishable out of stock inventory |
traffic_glance_views |
Text | Number of glance views |
week_beginning |
Text | Start date of week under consideration |