Data
Get Full Data in 2 CSV Files
The “product” file contains metadata and product details. The “raw” file contains time-series price data. Join them on “id” and “product_id” columns.
All Data in a SQLite file
I recommend the free DB Browser software for viewing the SQLite file. It can easily filter the data and export to CSV.
Data Structure
Table: "product"
Product meta-data like name, vendor (ex. Loblaws), brand and unit size. Is updated only when a new product is discovered (ex. there is a Unit size variation that hasn't been seen before).
| Column | Description |
|---|---|
| id | This is a unique product ID that's being used to join the "product" table to the "raw" table. This ID changes every day and is not a stable unique identifier! Use SKU or UPC instead when referring to a unique product. |
| concatted | A homebrew "unique identifier" that concatenates vendor,product_name,unit and brand. Used in intermediate steps before the scrape and the final output. |
| vendor | One of the 8 grocery vendors |
| product_name | Product name. May include Brand, may include Units in it. |
| units | Units (grams, kg, number of items in package). May be blank for some vendors/products. |
| brand | Brand name of the company that made a product (ex. "Frito Lay"), may be empty. |
| detail_url | "Detailed product listing URL" for this particular product. Used as a way of extracting the SKU and UPC. |
| sku | Each vendor's own unique identifier for a given product. Extracted from the "detail_url". For Metro, Galleria and Save-On-Foods, the SKU is also the UPC. |
| upc | The universal, cross-vendor unique product identifier. UPC is very hard to find for products. Most reliable UPC: Metro, Galleria and Save-On-Foods - their UPCs come directly from the vendor website. Next most reliable UPC: Walmart. The Walmart SKU was matched to a UPC figure on a site that I believe is owned by Walmart. These UPC are from exact matches, not fuzzy matches. Least reliable UPC: Loblaws, NoFrills, T&T and Voila. For them, I had to use fuzzy matching to find potential UPCs. I've done a manual Quality Control pass, but there may be incorrect UPCs there - always sanity-check by looking at product_name when comparing across vendors. |
Table: "raw"
Product prices at a point-in-time. New data is added every day.
| Column | Description |
|---|---|
| nowtime | Timestamp indicating when the data was gathered |
| current_price | Price at time of extract |
| old_price | An "old" struck-out price. This indicates that there was a Sale on. This is how you can differentiate a price-drop advertised as a Sale vs. a "quiet" decrease without a Sale. |
| price_per_unit | Price per unit, as shown on the vendor website. May not be consistent with real calculation of "current_price" divided by "units". Double check it. |
| other | Other details that appeared on the listing. Values may be "Out of stock", "SALE", "Best seller", "$5.00 MIN 2" |
| product_id | This is a unique product ID that's being used to join the "product" table to the "raw" table. This ID changes every day and is not a stable unique identifier! Use SKU or UPC instead when referring to a unique product. |
CSV file notes
The .csv file you get from the form is optimized for Excel. Specifically, it includes a "BOM" - several characters at the start of the file that make Excel treat it as UTF-8. If you're loading the .csv into a different tool for analysis, you may need to remove the BOM.
SQLite file notes
producttable: this table contains vendor, product_name, units and brandrawtable: contains the time series price data - nowtime, current_price, old_price, price_per_unit- The two tables are joined using
product.idandraw.product_id - To speed things up, there is an index on
raw.product_id