r/Airtable•
Setacics

How to track competitor product price history elegantly?

Question: Views & Customization

I work in sales, and I’ve built a base to track certain aspects of our market. One thing I haven’t cracked is how to best track competitor prices over time.

Example: a bunch of our sales are to government entities, who publish the price info of the purchased products. We of course enter this data, as [product name] [SKU] [buyer] [date of purchase] [price] [etc]. No problem.

The tricky part is that in government purchases they also disclose what the other price offers were. So we have this gold mine of potential/offered prices, that we would want to “make note of”. Currently, it just looks very kludgy to record this info:

  • Find that particular product SKU in the base
  • Create two new columns [price] and [price info source].

Especially since after fifteen public purchases you have 30 columns after one another, that show the prices at which the competition has offered their product at that particular instance.

I’ve tried to google this and scoured the Airtable knowledge base and templates, to no avail. And yet I have this nagging feeling that there’s an obvious, elegant way to get this done, but I’m too deep in the box to think outside of it.

Any ideas?

Posted Oct 8, 2020, 2:06 PM UTC

54
RucksackTech•

I'm not completely sure I understand what you're trying to do. But as well as I can understand it, it sounds pretty straightforward. Let me give you a bare bones description of how to do what you want. You'll have to fill in some other details yourself.

At the heart of your problem there seems to be a record that could be called a price -- so let's call this table PRICES. Fields in PRICES would include

  • Product Name
  • SKU
  • Buyer
  • Date of Purchase
  • Price

etc. As you said. But you need to add one more field. Here I'm a bit tongue-tied, that is, I'm not sure what to name this field. Let me call it "Type" for the moment; you can think of something better. In Airtable, Type would be a single-select field with two options:

  • Paid
  • Offered

or something like that. This is what distinguishes your actual price-paid data from the offered-but-not-paid data.


Now, in Airtable, you can create different views with different filters to show exactly what you're interested in. You should always in Airtable have one view (for me it's always at the top) that shows all fields, with no filters. But create two new views:

  • Prices Paid
  • Prices Offered

and filter on that "Type" field accordingly.

Does that get you where you need to go?

William

3
flyingcow2100•

Always adding new columns for new price offers in one is not a good practice to build a relational database. What you should be doing is to create a linked table, i.e., Offers, for saving the prices by the offers. Likely it would need three columns, SKU, price info source (offer company), and price. Then create a link from the main table based on SKU. This way, your database is flexible and expandable for as much data as needed. You can query the offer prices based SKU easily.

2
peripheal•

Are you needing to know the price from each competitor or perhaps winning, high, median, low for any given item?

1
SetacicsOP•

I just need to have a record of the price that they offered at that particular instance, since over time we can adjust our pricing according to our competitor's behavior.

1