Data · 2023.10 – 2023.11

Operations & product-selection analytics

A product-selection metric system and a one-page daily operations brief, built from five sales exports during a 2023 internship: 88 listings across 5 brands and 3 marketplaces, read every morning for the trend, the brand split, and per-listing stock, ad and price signals.

Role
Intern — analysis and reporting
Period
2023.10 – 2023.11
Stack
Excel · Word · CSV exports: ads, orders, inventory, listing page, daily totals
Scale
88 listings · 5 brands · 3 marketplaces · 14,753 source rows, October 2023 · 31-metric selection system · Anonymized data: brands AA–EE, masked listing IDs

Exhibits

Internship project, Oct–Nov 2023. The source tables are anonymized exports (brands AA–EE, masked listing IDs) covering October 2023; every number on this page is computed from them at build time.

Listings tracked
885 brands · US, CA, JP
Period
31 daysOctober 2023
Source rows
14,7535 tables: ads, orders, inventory, listing page, daily totals
Ad spend / ad sales
27.0k / 148.1k66 advertised listings · ACoS 18%
Out of stock, Oct 31
20 of 82selling listings with zero inventory

The product-selection metric system

5 dimensions · 9 groups · 31 metrics — what to check before a category or product gets shelf space

  1. Market demand

    Market size

    • Search volume
    • Potential customers
    • Growth trend

    Demographics

    • Age
    • Geography
    • Gender
  2. Competition

    Intensity

    • Number of competitors
    • Market share
    • Concentration

    Difficulty of entry

    • Barriers to entry
    • Threat of new entrants
  3. Product

    Best sellers

    • Best-selling items
    • Best-selling brands
    • Best-selling attributes

    Attributes

    • Category
    • Specification
    • Material

    Trends

    • New launches
    • Seasonality
    • Consumer preferences
  4. Ratings

    • Review count
    • Average rating

    Review content

    • Keywords
    • Sentiment

    UGC engagement

    • Customer photos
    • Questions asked
    • Interactions
  5. Price

    • Selling price
    • Promotions
    • Cost
    • Margin
Exhibit 1. Demand and competition decide whether a category is worth entering; product, ratings and price decide which listing wins inside it. The tree was the checklist behind the brief's columns.

What the daily brief is made of

  1. Ads export1,523 rows
  2. Orders export1,927 rows
  3. Inventory export7,068 rows
  4. Listing-page export3,801 rows
  5. Daily totals434 rows
  6. Joindate · marketplace · listing
  7. 7-day trendunits · revenue · change · views · refund rate
  8. Brand split5 brands
  9. Per-listing health7-day avg · days to sell-out · CTR · ACoS · rank
  10. Daily briefone page, every morning
Exhibit 2. Five exports joined on date, marketplace and listing. The brief reads top-down: the 7-day trend says whether something changed, the brand split says where, and the per-listing table says which link and why — stock, ads or price.

October, day by day: units sold and listings out of stock

bars = units sold across all brands and marketplaces · dots = selling listings with zero inventory that day, of 82

Units sold per day05001,0001,5002,000peak 1,533127Selling listings out of stock0102020Oct 1Oct 6Oct 11Oct 16Oct 21Oct 26Oct 31
Exhibit 3. Units peaked at 1,533 on Oct 2 and held near 950 a day through Oct 21, then fell to about 160 a day. Over the same month the number of selling listings with no stock climbed from 4 to 21. The brief's days-to-sell-out column exists to catch this a week earlier.

The best-selling listing: price, category rank and units

one listing (brand CC, US) · 2,951 units in October, 15% of all units sold · rank axis is inverted, higher = better

Price2530354029.9938.59Category rank (inverted)#1#250#500#750#1000best #74 · Oct 17worst #878Units per day0150300Oct 1Oct 6Oct 11Oct 16Oct 21Oct 26Oct 31
Exhibit 4. The price was raised five times, from 29.99 to 38.59. Rank improved to #74 on Oct 17, then slid to #878 by Oct 31 as daily units fell from over 100 to single digits. One month of exports cannot separate price from stock and season, but a brief that shows the three side by side raises the question the same day.

Ad efficiency per listing: spend against ACoS

66 advertised listings · x = ad spend in October, log scale · y = ACoS = ad spend ÷ ad sales · hollow = spend with no ad sales

0%25%50%100%1101001,00010,000Ad spend →ACoSspend = ad salesblended 18%no ad salesCC · US · 6,036CC · US · 4,717
Exhibit 5. Two CC listings took 40% of the spend and returned 48% of ad sales at 14–16% ACoS. Five small listings spent more on ads than they sold through them, and eight spent with no ad sales at all — together under 450 — which is what the brief flags first.

Problem

An e-commerce operations team selling five brands in the US, Canada and Japan gets a separate export for every question: ads, orders, inventory, listing pages, daily totals. Nobody reads five tables before the morning meeting. Two things were missing: a shared answer to what makes a product worth selling, and a one-page brief that says what changed yesterday and where to look.

Decisions

Define the metrics before the report. The selection system (Exhibit 1) puts 31 metrics under five questions: is there demand, how hard is the competition, what is winning inside the category, how do customers rate it, and does the price leave a margin. Every column in the brief maps back to a node in that tree, so the report and the checklist speak the same language.

Join on the keys the business already uses. The five exports share date, marketplace and listing ID. Joined on those three keys they become one table per listing per day, which is what every question below is asked of.

Read top-down. The brief (Exhibit 2) opens with a 7-day trend of units, revenue, day-over-day change, page views and refund rate, then the brand split for the day, then a per-listing table with the derived columns that make a row actionable: days since the last sale, 7-day average, estimated daily units, days to sell-out at current stock, CTR, CPC, ACoS, category rank, reviews, price and rating.

Flag, don’t explain. The brief marks listings that will sell out within a week, listings whose ACoS is above the blended rate, and listings whose rank dropped. The explanation is the team’s job; the brief’s job is to make sure the question gets asked the same day.

Result

The brief for October 31, 2023 is the deliverable; Exhibits 3–5 show what it surfaced across the month. Units fell from about 950 a day to about 160 after October 22 while the number of selling listings with no inventory rose from 4 to 21. The best-selling listing was repriced five times, from 29.99 to 38.59, and its category rank slid from #74 to #878 as its daily units collapsed. Two listings took 40% of ad spend at a healthy 14–16% ACoS, while thirteen small listings spent about 450 on ads that returned less than they cost, or nothing.

Limits

One month of anonymized exports, assembled in Excel. The charts show correlations the brief would have flagged, not causes: a price rise, a stock-out and a seasonal dip can all end in the same rank chart. The metric system was a checklist agreed with the team, not a scored model validated against later sales.

← Back to home