Target - Integration Specifications

Target Sales

This is the pipeline for the Target Sales data. Note that there's also the Target Inventory Pipeline.

Raw Data

This pipeline incorperates two raw-data files:

• retail.target_br_2yr_weekly_gm_tcin_loc (the historical file)

• retail.target_daily_sales_tcin_loc (the current data)

Mappings

The mappings between the raw data and the final data are demonstrated in the following table.

URS Sales Report

Source Field	Destination Field	Data Type	Description
	sales_report_id *	VARCHAR(255)
SALES_DATE	sales_date	DATE
SALES_DATE	source_sales_date	DATE
	location_id *	VARCHAR(255)
	product_id *	VARCHAR(255)
SALE_AMOUNT	dollar_sales	DECIMAL(24,10)
SALE_QUANTITY	unit_sales	DECIMAL(24,10)
	original_currency *	CHAR(3)
	currency_conversion_rate *	DECIMAL(24,10)
	converted_currency *	CHAR(3)
	__file_name *	VARCHAR(255)
	__source_id *	VARCHAR(64)
	__source_display_name *	VARCHAR(255)
	__loaded_at *	TIMESTAMP
	__synced_at *	TIMESTAMP

* Generated in the pipeline

URS Products

Source Field	Destination Field	Data Type	Description
	product_id *	VARCHAR(255)
ITEM_DESCRIPTION	listing_sku *	VARCHAR(255)
ITEM_DESCRIPTION	master_sku *	VARCHAR(255)
"upc"	reporting_level	VARCHAR(255)
ITEM_DESCRIPTION	product_name	VARCHAR(255)
BARCODE	upc	VARCHAR(255)
BRAND_NAME	brand_name	VARCHAR(255)
	department	VARCHAR(255)
	category	VARCHAR(255)
	subcategory	VARCHAR(255)
	product_class	VARCHAR(255)
	product_type	VARCHAR(255)
concat( split_part(item_description, ' ', -2), ' ', split_part(item_description, ' ', -1))	product_size	VARCHAR(255)
split_part(item_description, ' ', -1)	unit_of_measure	VARCHAR(255)
	pack_count	DECIMAL(0,6)
	__file_name *	VARCHAR(255)
	__source_id *	VARCHAR(64)
	__loaded_at *	TIMESTAMP
	__synced_at *	TIMESTAMP

* Generated in the pipeline

URS Locations

target_br_2yr_weekly_gm_tcin_loc	Destination Field	Data Type	Description
	location_id *	VARCHAR(255)
"Target"	retailer_name	VARCHAR(255)
platform.store_locations.store_name	store_name	VARCHAR(255)
	market_name	VARCHAR(255)
	warehouse_name	VARCHAR(255)
	retailer_division	VARCHAR(255)
LOCATION_ID	retailer_store_id	VARCHAR(255)
platform.store_locations.address	address1	VARCHAR(255)
	address2	VARCHAR(255)
platform.store_locations.city	city	VARCHAR(255)
platform.store_locations.state	state	VARCHAR(255)
platform.store_locations.country	country	VARCHAR(255)
platform.store_locations.zip_code	zipcode	VARCHAR(255)
TRUE	is_store_level	BOOLEAN
FALSE	is_warehouse_level	BOOLEAN
FALSE	is_market_level	BOOLEAN
	__file_name *	VARCHAR(255)
	__source_id *	VARCHAR(64)
	__loaded_at *	TIMESTAMP
	__synced_at *	TIMESTAMP

* Generated in the pipeline

URS Specific to Target Sales

The destination table that we map other Target metrics into is urs.specific_to_target. All of these should be available in the explore for this source.

    sales_report_id VARCHAR(255),
    vendor_id VARCHAR(255),
    barcode VARCHAR(255),
    tcin VARCHAR(255),
    dpci VARCHAR(255),
    manufacturer_style VARCHAR(255),
    dept VARCHAR(255),
    class VARCHAR(255),
    origination_channel VARCHAR(255),
    reporting_channel VARCHAR(255),
    fulfillment_type VARCHAR(255),
    drive_up_sale_a DECIMAL(24,10),
    drive_up_sale_q BIGINT,
    location_id VARCHAR(255),
    circular_sale_amount DECIMAL(24,10),
    circular_sale_quantity BIGINT,
    clearance_sale_amount DECIMAL(24,10),
    clearance_sale_quantity BIGINT,
    promo_sale_amount DECIMAL(24,10),
    promo_sale_quantity BIGINT,
    regular_sale_amount DECIMAL(24,10),
    regular_sale_quantity BIGINT,
    circle_sale_amount DECIMAL(24,10),
    circle_sale_quantity BIGINT,
    mature_sale_amount DECIMAL(24,10),
    mature_sale_quantity BIGINT,
    comparable_sale_amount DECIMAL(24,10),
    comparable_sale_quantity BIGINT,
    ad_comparable_sale_amount DECIMAL(24,10),
    ad_comparable_sale_quantity BIGINT,
    brand_id VARCHAR(255),
    return_guest_amount DECIMAL(24,10),
    return_guest_quantity BIGINT,
    shipt_app_sale_a DECIMAL(24,10),
    shipt_app_sale_q BIGINT,
    shipt_target_sale_a DECIMAL(24,10),
    shipt_target_sale_q BIGINT,
    __file_name VARCHAR(255),
    __source_id VARCHAR(64),
    __loaded_at TIMESTAMP,
    __synced_at TIMESTAMP

Pipeline architecture

This pipeline first brings in the historical data and transforms it, inserting it into URS schema. Then the pipeline runs for the regular extracts. In both cases, we join to platform.store_locations to bring in store level data.