Ocado

Ocado

This document provides step-by-step instructions for importing data from Ocado. The following will be covered:

Ocado Data Integration

To integrate Ocado data into your Panoply warehouse using default settings, complete the following steps.

  1. Click Data Sources in the navigation menu.
  2. Click the Add Data Source button.
  3. In the Data Sources - Choose Source Type window, select Ocado. Ocado is listed under APIs.
  4. In the Data Sources – Ocado screen, enter your login credentials and click Login.
  5. In the Data Sources – Ocado, screen, select which data to import.
  6. (Optional) To Customize the ingestion from your data source, review the advanced options.
  7. Click Collect.

The Data Sources – Ocado window will go gray while the data integration is pending. A small green progress bar will appear below Ocado 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 - Ocado window to include Destination, Primary Key, Exclude, Parse String and Truncate table

Destination - Default is ocado_{__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.

advanced options

Data schema

The Ocado data elements ingested by Panoply are listed below. Ocado data is collected through a direct FTP connection, and each time the ingestion is run, Panoply will only collect files that were modified since the last successful run. The initial run will collect all Ocado files.

DATA TABLE - 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.

DATA TABLE

As noted above, this data is collected via direct FTP connection. In Panoply, the resulting tables will vary depending on what files are present in the Ocado account. The general form of an Ocado table ingested in this way will include fields similar to the following:

Column Name Data Type Description
brand Text Brand of product
id Text ID
date Text Date data point added
ean code Text EAN Code of product
sku number Number SKU number of product
product description Text Product description
department Text Department where product offered
category Text Category of product
sub category Text Sub-category of product
manufacturer Text Manufacturer of product
gbp sales Number Sales in GBP
units Number Number of units sold
gbp avg. price Number Average price of product in GBP
number of baskets Number Number of baskets product has appeared in
basket penetration Number Percentage of baskets product in
gbp avg. basket size Number Avg. price of basket in GBP in which product appears
avg. basket units Number Avg. number of products in basket
customers - midlands Number Number of customers buying product in Midlands UK
customers - north Number Number of customers buying product in Northern UK
customers - north london Number Number of customers buying product in North London
customers - south london Number Number of customers buying product in South London
customers - south excluding london Number Number of customers buying product in Southern UK (not London)
customers - large & loyal Number Number of customers buying product in loyalty program
customers - routine delivery Number Number of customers buying product who use routine delivery
customers - frequent small Number Number of customers buying product who make frequent small purchases
customers - big spenders Number Number of customers buying product who spend large amounts on the platform
customers - regular shoppers Number Number of customers buying product who are frequent shoppers
customers - infrequent shoppers Number Number of customers buying product who are infrequent shoppers
customers - occasional ocado Number Number of customers buying product who are occasional Ocado users
customers - irregular small Number Number of customers buying product who make irregular, small purchases
customers - no value Number Number of customers buying product who do not fall into any other category