Convenience Store Transactions Data

Overview

Reporting on the items purchased within transactions at independent convenience stores. Each row represents a transaction and may consist of multiple items.

Schema

Key Concepts

Multi-Table Dataset

• Convenience Store Transactions Data is a Multi-Table dataset which allows you to access just the data you need. STORE_INFORMATION is the primary table. You can join additional tables using the STORE_ID identifier.
• For more information on how to access Multi-Table datasets via API, review our docs page.

Joining PDI tables

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

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>)