For context, monitoring product listings on Amazon.
I have 4 tables:
- Brands, with a lookup field to ASINs, from Catalog
- Catalog (containing ASINs), with a lookup field to Offers
- Offers, showing every offer on each ASIN, where each offer has a Seller ID, a lookup from Storefront Profiles.
- Storefront Profiles: Contains Seller ID.
I'm trying to count, at Brand-level, the number of unique sellers associated with their ASINs. I've tried all sorts of counts and lookups, but can't get the right number. Either counting, duplicates, spaces.
So far I have:
- In Catalog: A linked record to Offers, showing all offers per ASIN, with a lookup to show the active sellers.
- In Brand: A linked record to Catalog, showing all ASINs per brand, with lookups to show the offers and active sellers.
- Brand - 'Active sellers' - shows unduplicated sellers. Using
COUNT(ARRAYUNIQUE({Active Sellers})) in a formula gives me 0.
In Brand: 'Sellers' - A rollup of Active sellers using
ARRAYUNIQUE(ARRAYFLATTEN(Active Sellers)) shows a comma separated string.
In Brand: '# Sellers" - a rollup of Active Sellers from ASINs (a lookup from catalog) - using
COUNTA(ARRAYUNIQUE(values)) gives me 48.
The actual number of unique Seller IDs in Offers is 25.
Any ideas? I've tried pretty much everything the internet has offered. Anyone here able to help?!
