Why a shop tab needs its own arithmetic
Social platforms have turned into shopping streets. A product can be tagged in a post, a short video or a story, listed in a shop tab, pinned during a live stream or sold through a creator’s storefront. For a small brand this is good news, because shoppers meet the product where they already spend their time. It also creates a measurement problem: the same product is shown in many places, each place has a different audience, and the only thing that is easy to see is a total number of orders.
A total hides the answer to the questions that decide where to spend time and money. Which surface turns attention into orders? Where do shoppers drop out between seeing a product and paying for it? Is a surface weak because it reaches the wrong people, or because it reaches many people with little intent? This free lesson shows how to answer these questions with a small funnel table and a handful of ratios in a spreadsheet.
The example is Harrowgate Home, a fictional brand that sells candles, ceramics, linen and kitchenware. All surfaces, counts and prices are invented for learning, and so are the other companies and people you will meet in this course. The numbers are illustrative and are not platform benchmarks. You will work with the same eight surfaces as in the lab above.
The funnel: four counts and three rates
A social-commerce funnel follows a shopper from first sight to purchase. For the maths we use four counts, which most analytics exports of a shop or a platform provide in some form:
- Impressions. A product card, tag or sticker was shown on a screen. One person can generate several impressions.
- Product views. The shopper opened the product page.
- Adds to cart. The shopper put the product in the cart.
- Orders. The shopper paid. Revenue is the money for these paid orders before returns.
Every step of the funnel is a ratio of the step before it. That gives three step rates, and each has its own denominator:
- View rate = product views ÷ impressions.
- Cart rate = adds to cart ÷ product views.
- Order rate = orders ÷ adds to cart (the share of carts that become orders).
The most common error is to divide every count by the same denominator, usually the impressions. A cart rate of 0.4 % of impressions tells you nothing about the shop page, because most impressions never reach it. Dividing by the step above isolates the behaviour of the people who got that far.
Normalising: orders and revenue per 1,000 impressions
Surfaces differ enormously in size. A short-video tag may be shown 265,000 times in a month and a pinned product in a live stream only 41,000 times. The raw order counts mix the effect of size with the effect of quality. To compare quality, divide by the exposure and scale to a convenient number:
- Orders per 1,000 impressions = orders ÷ impressions × 1000.
- Revenue per 1,000 impressions = revenue ÷ impressions × 1000.
The second figure is like a revenue version of the advertising term RPM (revenue per mille), and it is the number you can compare with what an impression costs you. If a surface earns $34.89 per 1,000 impressions and you pay $12 to buy 1,000 impressions there, the maths is very different from a surface that earns $806.05.
A fully worked example: eight surfaces for one month
Here are the eight surfaces with the counts from the lab and the four calculated columns. Revenue is the number of orders times the average order value of the surface.
| Surface | Impressions | Product views | Adds to cart | Orders | Revenue $ | View rate | Cart rate | Orders per 1,000 | Revenue per 1,000 $ |
|---|---|---|---|---|---|---|---|---|---|
| Shop tab browse | 186,000 | 9,850 | 1,280 | 392 | 18,424 | 5.30 % | 12.99 % | 2.11 | 99.05 |
| Product tags in posts | 142,000 | 6,390 | 810 | 262 | 12,052 | 4.50 % | 12.68 % | 1.85 | 84.87 |
| Product tags in short videos | 265,000 | 7,420 | 745 | 201 | 9,246 | 2.80 % | 10.04 % | 0.76 | 34.89 |
| Story product sticker | 98,000 | 3,920 | 470 | 164 | 7,544 | 4.00 % | 11.99 % | 1.67 | 76.98 |
| Profile shop | 64,000 | 4,480 | 760 | 281 | 13,488 | 7.00 % | 16.96 % | 4.39 | 210.75 |
| Search inside the shop | 52,000 | 5,200 | 890 | 337 | 16,176 | 10.00 % | 17.12 % | 6.48 | 311.08 |
| Live: pinned product | 41,000 | 8,610 | 1,550 | 612 | 33,048 | 21.00 % | 18.00 % | 14.93 | 806.05 |
| Creator storefronts | 78,000 | 3,510 | 400 | 118 | 6,018 | 4.50 % | 11.40 % | 1.51 | 77.15 |
The formulas for row 4 (the shop tab), copied down to row 11, are listed here. Columns B to F hold the data, G to J the results.
- View rate (G4):
=C4/B4 - Cart rate (H4):
=D4/C4 - Orders per 1,000 impressions (I4):
=E4/B4*1000 - Revenue per 1,000 impressions (J4):
=F4/B4*1000
Walk through the first row. The shop tab browse surface was shown 186,000 times; 9,850 shoppers opened a product page, a view rate of 9,850 ÷ 186,000 = 5.30 %. Of those, 1,280 added the product to the cart, a cart rate of 12.99 %, and 392 paid. The orders brought 18,424 dollars, so one thousand impressions on this surface earned 18,424 ÷ 186,000 × 1000 = $99.05.
How to read the result
The ranking is clear. The pinned product in a live stream earns $806.05 per 1,000 impressions, search inside the shop $311.08 and the profile shop $210.75. At the bottom are product tags in short videos with $34.89. The short-video tags are not useless: they are shown 265,000 times, 28.6 % of all impressions, but they bring 8.0 % of the revenue. The live stream is the opposite: 4.4 % of the impressions and 28.5 % of the revenue.
The reason sits in the first two rates. The live pinned product has a view rate of 21.0 % because the shopper is already watching the person who holds the product. A short-video tag has a view rate of 2.8 %: most viewers watch the video and never tap the tag. The cart rates are closer together (10.0 % to 18.0 %), so the biggest difference between the surfaces is intent at the top of the funnel, not the quality of the product page.
For the whole month, 926,000 impressions led to 49,380 product views (5.33 %), 6,905 adds to cart (13.98 % of the views) and 2,367 orders (34.28 % of the carts). That is 2.56 orders and $125.27 of revenue per 1,000 impressions on average. Notice that the overall rate is the total orders divided by the total impressions, not the average of the eight surface rates.
Step by step in your own sheet
- Export the four counts for each surface and month from your shop or platform tools and paste them as plain numbers.
- Write the view rate in the first data row,
=C4/B4, check one result by hand with a calculator, then copy the formula down. - Add the cart rate
=D4/C4and, if you like, the order rate=E4/D4. - Add the per-1,000 columns. Multiply by 1000 in the formula so that the number is easy to read.
- Format the rates as percentages and the money columns with two decimals, but do not round inside the formulas.
- Chart the revenue per 1,000 impressions as a bar chart sorted from high to low: a long list of rates is hard to read, a sorted bar chart is not.
Common mistakes and how to spot them
- One denominator for every rate. Dividing carts and orders by the impressions makes the later steps look tiny and hides where shoppers drop out. Each step divides by the step above it.
- Forgetting to multiply by 1000. Orders per impression is a number such as 0.0021. If your per-1,000 column shows values below 1 for all surfaces, the factor is missing.
- Averaging rates. The average of the eight view rates is not the overall view rate, because a surface with 41,000 impressions counts as much as one with 265,000. Add the counts first and divide once.
- Mixing definitions. If the live stream counts an impression after three seconds and the short-video tag counts it immediately, the view rates are not comparable. Check the definitions.
- Comparing different months. A surface measured in a sale month against another in a quiet month tells you nothing about the surface. Compare surfaces within the same period.
- Treating revenue as profit. Revenue per 1,000 impressions ignores product cost, returns, shipping and commission. Later in the course you will turn revenue into contribution.
- Reading a small sample as a law. A surface with 400 adds to cart can move a lot from one month to the next. Look for the same pattern in several months before you move budget.
Quick reference: formulas in this lesson
| Measure | Meaning | Example formula |
|---|---|---|
| View rate | Share of impressions that open a product page | =C4/B4 |
| Cart rate | Share of product views that add to cart | =D4/C4 |
| Order rate | Share of carts that become orders | =E4/D4 |
| Orders per 1,000 impressions | Orders scaled to exposure | =E4/B4*1000 |
| Revenue per 1,000 impressions | Revenue scaled to exposure | =F4/B4*1000 |
| Average order value | Revenue per order | =F4/E4 |
| F4 / Cmd + T | Switch between relative and absolute references | $B$4 |
Real-world uses
- Deciding where to tag. If search inside the shop and the profile shop earn two to four times more per impression than posts, product pages and titles deserve more attention there than the tenth tagged post.
- Briefing creators. A creator storefront with a view rate of 4.5 % and a cart rate of 11.4 % shows how much of the audience’s attention turns into intent. Compare it with the shop tab before you agree on a fee.
- Planning a live show. If a pinned product has a high view rate and a cart rate that is no better than elsewhere, the live show is finding the shoppers and the product page has to do more.
- Finding the leak after a site change. A drop of the cart rate on every surface at once points to the product page or the price, not to a single audience.
- Reporting. A one-page table of rates per surface is easier for a manager to read than a long list of totals, and it answers the question what to do next.
Honest measurement and fair selling
Social commerce works on trust. Count orders you can verify, say clearly when numbers are estimates, and do not build pressure with false counters or invented scarcity. Target and segment on behaviour (what people watched, saved or bought) and never on protected characteristics such as age, gender or health. Where you message shoppers, make sure they agreed to receive messages. The later labs of this course on returns and stock are partly about keeping the promises that a lively stream makes.
What you will build in the rest of this course
Lesson 1 is free. Lessons 2 to 20 make up the paid course. Each has its own live lab with a different data set of Harrowgate Home and builds on the funnel you learned today.
- Funnel Leaks: Step Rates, Change in Percentage Points and the Orders Lost at the Weakest Step. You compare every step of the funnel between two months, find the step with the biggest fall and estimate the orders it cost.
- Live Viewers to Buyers: Viewer-to-Buyer Rate, Revenue per Viewer, Ranking Shows and the Pooled Rate. You turn twelve live shows into comparable rates and learn why a pooled rate beats an average of rates.
- Peak Concurrent Viewers: Peak, Minute of the Peak, Average Audience and Minutes Near the Peak. You read the audience curve of one show and chart it.
- Items per Live Order: Units per Order, Average Order Value, Bundle vs Single-Item Shows and the Value of One More Item. You compare show formats by basket size and price one more item per order.
- Host Commission: Flat Rate vs Tiers, Approximate Lookups and the Extra Cost of a Tier Model. You price two commission models with an approximate lookup.
- Returns After Live Sales: Unit Return Rate, Net Revenue, a Review Limit and the Pooled Return Rate. You measure returns per show and flag the shows above a limit.
- Live Show Contribution: Net Revenue, Variable Costs, Margin and Contribution per Live Hour. You build what a show earns after product, shipping, commission and show costs.
- Return Reasons: Totals by Reason, Shares, Return Rate per Category and the Preventable Share. You find out why goods come back and which part you can prevent.
- Discount Depth in Flash Deals: Sale Price, Total Margin, Conversion and a Forecast at a New Discount. You test how deep a discount can go before it eats the margin.
- Audience Retention: Retention Curve, Viewers Lost per Interval, the 50 % Minute and Average Minutes Watched. You follow one group of viewers through a show.
- Engagement Levels: Conversion by Level, Lift vs. Silent Viewers and Where the Buyers Come From. You compare buyers across chat, question and follow behaviour.
- Order-Log Cleaning and Attribution: Duplicates, Test and Cancelled Orders and a 24-Hour Window. You clean an order export and apply an attribution window.
- Host Scorecard: Indexes, a Weighted Score with SUMPRODUCT and the Top Host. You combine three metrics into one score with weights.
- Show Time Slots: Average Revenue per Slot, a Mixed-Reference Grid, the Best Daypart and a Heat Map. You compare weekday and time-of-day slots.
- Forecasting the Next Show: Slope, R Squared, FORECAST.LINEAR and a Revenue Estimate. You forecast orders and revenue from the planned audience.
- Testing a Show Format: Pooled Conversion, z Score, p-Value and a Careful Verdict. You test whether a new show format really converts better.
- Stock vs Expected Demand: Demand from Show Assumptions, Lost Sales, Sell-Through and Stock-Out Flags. You plan stock per product and flag the products that will run out.
- Break-Even and What-If Grid: Break-Even Orders and Viewers, Mixed References and a Loss Heat Flag. You find the audience a show needs and map profit and loss across scenarios.
- Year Scorecard for Live Commerce: Conversion, Net Revenue after Commission, Return Alerts and a Combo Chart. You finish with a yearly scorecard that joins all skills.
Frequently asked questions
What is a social-commerce funnel?
It is the path from seeing a product inside a social app to paying for it: impressions, product views, adds to cart and orders. Each step has a rate relative to the step before it, which shows where shoppers drop out.
How do I calculate a view rate and a cart rate in Excel?
View rate is product views divided by impressions, for example =C4/B4. Cart rate is adds to cart divided by product views, for example =D4/C4. Copy the formulas down and format the columns as percentages.
Why compare surfaces per 1,000 impressions?
Surfaces differ in size. Orders or revenue per 1,000 impressions removes the effect of size, so a small surface with a high intent can be compared fairly with a large surface with low intent.
What is the difference between an impression and a product view?
An impression means a product card, tag or sticker was shown. A product view means the shopper opened the product page. The ratio between them, the view rate, measures how often attention turns into interest.
Is the overall rate the average of the surface rates?
No. Add the counts of all surfaces first and divide once. The average of rates gives a small surface the same weight as a large one and usually gives a misleading overall figure.
Are the numbers in this lab real platform benchmarks?
No. All companies, surfaces and numbers are fictional and illustrative. Platforms define impressions and views in their own way and change those definitions, so always check the definitions in your own export.
Keep going: Social Commerce & Live Shopping: Shop-Tab Funnels, Viewer-to-Buyer Conversion, Peak Concurrency, Items per Order, Host Commission & Returns
This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.
- Funnel Leaks: Step Rates, Change in Percentage Points and the Orders Lost at the Weakest Step
- Live Viewers to Buyers: Viewer-to-Buyer Rate, Revenue per Viewer, Ranking Shows and the Pooled Rate
- Peak Concurrent Viewers: Peak, Minute of the Peak, Average Audience and Minutes Near the Peak
- Items per Live Order: Units per Order, Average Order Value, Bundle vs Single-Item Shows and the Value of One More Item
- Host Commission: Flat Rate vs Tiers, Approximate Lookups and the Extra Cost of a Tier Model
- Returns After Live Sales: Unit Return Rate, Net Revenue, a Review Limit and the Pooled Return Rate
- Live Show Contribution: Net Revenue, Variable Costs, Margin and Contribution per Live Hour
- Return Reasons: Totals by Reason, Shares, Return Rate per Category and the Preventable Share
- Discount Depth in Flash Deals: Sale Price, Total Margin, Conversion and a Forecast at a New Discount
- Audience Retention: Retention Curve, Viewers Lost per Interval, the 50 % Minute and Average Minutes Watched
- Engagement Levels: Conversion by Level, Lift vs. Silent Viewers and Where the Buyers Come From
- Order-Log Cleaning and Attribution: Duplicates, Test and Cancelled Orders and a 24-Hour Window
- Host Scorecard: Indexes, a Weighted Score with SUMPRODUCT and the Top Host
- Show Time Slots: Average Revenue per Slot, a Mixed-Reference Grid, the Best Daypart and a Heat Map
- Forecasting the Next Show: Slope, R Squared, FORECAST.LINEAR and a Revenue Estimate
- Testing a Show Format: Pooled Conversion, z Score, p-Value and a Careful Verdict
- Stock vs Expected Demand: Demand from Show Assumptions, Lost Sales, Sell-Through and Stock-Out Flags
- Break-Even and What-If Grid: Break-Even Orders and Viewers, Mixed References and a Loss Heat Flag
- Year Scorecard for Live Commerce: Conversion, Net Revenue after Commission, Return Alerts and a Combo Chart