Forum Discussion
Helper IIIHow to model price scrape data for repricing algorithm and historical analysis
Hi all,
I know the final report I want to build but I don't know how to go about it. Any help GREATLY appreciated!!
The data I'm working with is a scrape of a website where vendors compete to be the lowest price to 'win the buy box' (similar to amazon).
My company (Daisy) is scraping the prices for every item daily. I receive a daily scrape with about 200,000 rows daily. I am working on a repricing algorithm to adjust our prices based on the most recent lowest competitor price (and some other criteria).
Here is a sample.
| Date | Item | Vendor | Qty Break | Price |
| 5/8/2022 | 8 | Daisy | 1 | 230.00 |
| 5/8/2022 | 8 | Carolina Dreaming | 1 | 360.00 |
| 5/8/2022 | 8 | Denways | 1 | 133.00 |
| 5/8/2022 | 6 | Daisy | 1 | 376.00 |
| 5/8/2022 | 6 | Carolina Dreaming | 1 | 336.00 |
| 5/8/2022 | 6 | Denways | 1 | 125.00 |
| 5/8/2022 | 7 | Daisy | 1 | 395.00 |
| 5/8/2022 | 7 | Carolina Dreaming | 1 | 235.00 |
| 5/8/2022 | 7 | Denways | 1 | 190.00 |
| 5/9/2022 | 7 | Daisy | 1 | 314.00 |
| 5/9/2022 | 7 | Carolina Dreaming | 1 | 300.00 |
| 5/9/2022 | 7 | Denways | 1 | 261.00 |
| 5/9/2022 | 6 | Daisy | 1 | 254.00 |
| 5/9/2022 | 6 | Carolina Dreaming | 1 | 387.00 |
| 5/9/2022 | 6 | Denways | 1 | 360.00 |
| 5/9/2022 | 8 | Daisy | 1 | 319.00 |
| 5/9/2022 | 8 | Carolina Dreaming | 1 | 140.00 |
| 5/9/2022 | 8 | Denways | 1 | 390.00 |
| 5/10/2022 | 6 | Daisy | 1 | 246.00 |
| 5/10/2022 | 6 | Carolina Dreaming | 1 | 226.00 |
| 5/10/2022 | 6 | Denways | 1 | 337.00 |
| 5/10/2022 | 7 | Daisy | 1 | 178.00 |
| 5/10/2022 | 7 | Carolina Dreaming | 1 | 372.00 |
| 5/10/2022 | 7 | Denways | 1 | 341.00 |
| 5/10/2022 | 8 | Daisy | 1 | 306.00 |
| 5/10/2022 | 8 | Carolina Dreaming | 1 | 151.00 |
| 5/10/2022 | 8 | Denways | 1 | 13.00 |
What I am currently doing in my PBI report is overwriting the previous day's prices (excel data source) and basing our new price off of that day's data only. My table visual looks like this (different price data than sample above):
| Item | Scrape Date | Lowest Competitor(s) Name | Lowest Competitor Price | Current Daisy Price | Daisy Winning? | Daisy New Price |
| 6 | 5/10/2022 | Denways | $32.41 | $32.38 | Winning | $32.40 |
| 7 | 5/10/2022 | Denways | $48.42 | $48.40 | Winning | $48.41 |
| 8 | 5/10/2022 | Carolina Dreaming | $65.75 | $65.94 | Losing | $65.74 |
The problem with that is two fold:
- I cannot look back in time at historical competitor pricing for my algorithm (Lowest competitor price for item last 30 days).
- I cannot show historical metrics such as 'Buy box ownership % for the last 7 days', 'Unique items listed for the last 30 days'.
My questions:
- Where do I store 18,000,000 rows? (90 days of price history)
- How do I structure my data so that I am able to make the following visual and other similar visuals that can calculate winning vendor or price and show it as a percentage over time?
For this visual it would need to calculate the winner for each item first and divide the items won by total items for each day...
Thank you in advance!!
