Transaction Sets

Overview

Reporting on aggregated transaction level details (one row per transaction).

Schema

Special Variables

Create a Dewey Data login and navigate to the Table Structure view in the product for a description of each variable.
Transaction Sets

Key Concepts

Joining PDI Datasets

• There are 10 total datasets that make up the full Convenience Store Transaction Dataset. Those datasets relate to each other through unique identifiers for shoppers, stores, transactions, and payments.

Supplemental Datasets

• Along with the Transaction Items dataset, PDI also provides a handful of datasets that can be used to further enrich this file or be used to help join it with another. Those datasets are:
  • Master GTIN
  • Payments
  • Shopper ID
  • Stores Information
  • Stores Status

Frequently Used Tables

• The following four tables can be used to conduct almost any type of market analysis. All the tables can be joined together using their unique ID fields:

Dimensional Tables

STORES

• Contains store and locational attributes that can be used for geolocation based analytics
• Join to the transactional tables on STORE_ID to pull in store attributes

MASTER_GTIN

• Contains GTIN attributes used for category, brand, and product level analytics
• Join to the TRANSACTION_ITEMS table on GTIN to pull in product attributes

Transactional Tables

TRANSACTION_ITEMS

• Contains information on the transaction at the individual item level

TRANSACTION_SETS

• Contains information on the transaction at the basket level

Filtering

• To speed up your queries and ensure you’re looking at the most relevant data use filters such as the ones below. These are just handful of filter examples, there are of course many other fields you can
  use to filter your queries.
  • SCAN_TYPE
    • This field indicates the type of item scanned at the point of sale. This field can be found in the Transaction Items table. The field values found in this column are GTIN, PLU, FMT_ERR, and
      NONSCAN.
    • To filter for only in-store merchandise transactions use:
      where scan_type in (GTIN,PLU,FMT_ERR)
  • DATE_TIME
    • This timestamp field indicates the exact time the point of sale recorded for a given transaction. This field can be found in any of the transactional tables such as Transaction Items, Transaction Sets,
      Payments, etc.
    • To filter for a specific time period use:
      where date_time between '<specific_date>' and '<specific_date>'
      where date_time >= dateadd(year,-1,current_date)
  • STORE_ID
    • This field is Skupos' unique store identifier when a retailer signs up for our platform. This field is found in the Stores table.
    • To filter for specific store IDs use:
      where store_id = <specific_store_id>
      where store_id in (<specific_store_id>,<specific_store_id>,<n_specific_store_id>)