Why the traffic mix matters for an online store
An independent online store does not have one source of customers; it has several, and they behave very differently. Visitors who type the web address or open an email already know the shop. Visitors who arrive through a paid social ad have never heard of it and are, for the moment, only curious. If you treat them as one crowd, every number you report is an average of very different people, and the decisions you take from it are guesses.
The first job of store marketing analytics is therefore to describe the traffic source mix: how the visits (sessions) are split between organic search, paid search, paid social, email, direct visits, organic social, referrals and affiliate links, and what each source delivers in orders and revenue. Everything else in this course, from average order value and repeat purchases to ROAS and contribution margin, is built on top of this table.
The example is Larkfield Desk Co, a fictional shop that sells notebooks, planners, pens and desk accessories on a Shopify-style platform. All customers, channels and numbers in this article are invented for teaching; any benchmark is illustrative and not taken from any platform or study.
The core concepts: session share, conversion rate and revenue share
Three ratios describe a channel. They all come from the same three columns: sessions, orders and revenue.
Session share is the sessions of the channel divided by the sessions of all channels. It answers the question "how much of our traffic comes from here?". A channel with 4,000 of 20,000 sessions has a session share of 20%. In a spreadsheet the total must be locked with dollar signs, as in SUM($B$4:$B$11); otherwise the range slides down when you copy the formula and the percentages are wrong.
Conversion rate is orders divided by sessions of the same channel. It answers "how many visits end in a purchase?". Ninety orders from 4,000 sessions are a conversion rate of 2.25%. Two things are easy to forget: the numerator and the denominator must come from the same channel, and the rate says nothing about how large the orders are.
Revenue share is the revenue of the channel divided by total revenue. Put next to the session share it shows whether a channel earns more or less than its share of the visits. A channel with 22.5% of the sessions and 10.9% of the revenue is a lot of traffic for little money; a channel with 9.3% of the sessions and 20.1% of the revenue punches far above its weight.
- Session share = channel sessions ÷ all sessions.
- Conversion rate = orders ÷ sessions of the channel.
- Revenue share = channel revenue ÷ total revenue.
- Store-wide conversion rate = total orders ÷ total sessions.
A fully worked example: Larkfield Desk Co in March
In March the shop recorded 65,800 sessions, 1,520 orders and $66,900 revenue across eight channels. The table shows the raw data in the first three numeric columns and the three ratios you calculate in the lab in the last three.
| Channel | Sessions | Orders | Revenue | Session share | Conversion rate | Revenue share |
|---|---|---|---|---|---|---|
| Organic search | 18,400 | 368 | $15,980 | 28.0% | 2.00% | 23.9% |
| Paid search | 9,200 | 262 | $12,070 | 14.0% | 2.85% | 18.0% |
| Paid social | 14,800 | 178 | $7,300 | 22.5% | 1.20% | 10.9% |
| 6,100 | 299 | $13,420 | 9.3% | 4.90% | 20.1% | |
| Direct | 7,900 | 253 | $11,010 | 12.0% | 3.20% | 16.5% |
| Organic social | 5,200 | 52 | $2,060 | 7.9% | 1.00% | 3.1% |
| Referral | 2,300 | 58 | $2,680 | 3.5% | 2.52% | 4.0% |
| Affiliates | 1,900 | 50 | $2,380 | 2.9% | 2.63% | 3.6% |
| Total | 65,800 | 1,520 | $66,900 | 100.0% | 2.31% | 100.0% |
The formulas behind the three calculated columns, written for row 4 and copied down to row 11, are:
- Session share (E4):
=B4/SUM($B$4:$B$11). The total of the sessions is locked with dollar signs so that every row divides by the same total. - Conversion rate (F4):
=C4/B4. Orders divided by the sessions of the same row. - Revenue share (G4):
=D4/SUM($D$4:$D$11). The same pattern as the session share, applied to revenue. - Store-wide conversion rate (B13):
=SUM(C4:C11)/SUM(B4:B11).
Walk through two rows. Organic search has 18,400 sessions, which is 28.0% of all sessions, and 368 orders, a conversion rate of 2.00%. Email has only 6,100 sessions (9.3%), but 299 orders: 4.90% of its visits end in a purchase, more than twice the store-wide rate of 2.31%. Email therefore delivers 20.1% of the revenue from 9.3% of the traffic.
Reading the result: who earns their share?
Start with the biggest gap between the two shares. Paid social brings 22.5% of the sessions and 10.9% of the revenue, with a conversion rate of 1.20%. That does not mean the channel is a failure: people who meet the shop for the first time in a feed rarely buy during the first visit, and some of them come back later through direct visits or email, where the order is then counted. It does mean that judging paid social by its last-click revenue alone is harsh, and that you should look at how many first-time customers it brings (a topic of lesson 10).
Email, direct visits and paid search are the opposite: fewer sessions, more orders. Email and direct have conversion rates of 4.90% and 3.20%. These are people who already know the shop, which is why their numbers look so good and why you cannot simply buy more of them. The three largest channels by revenue (organic search, email and paid search) together deliver 62.0% of the revenue, a concentration measure that lesson 3 calculates with the LARGE function.
The store-wide conversion rate: why you never average
The store-wide conversion rate is total orders ÷ total sessions = 1,520 ÷ 65,800 = 2.31%. If you instead take the plain average of the eight channel rates, you get 2.54%. The difference looks small here, but the average gives organic social (5,200 sessions) the same weight as organic search (18,400 sessions). With a bigger spread between the channels the gap grows, and it is an avoidable error for a number that is usually reported with two decimals.
The rule generalises: whenever you combine ratios from groups of different size, add the numerators, add the denominators and divide once. You will use the same rule for AOV, ROAS, CAC and margins throughout the course.
Step by step in Excel
- Enter the channel names in A4:A11 and the sessions, orders and revenue in B4:D11. Format the revenue as currency and the shares as percentages.
- Click E4 and type
=B4/SUM($B$4:$B$11). After typing the range, press F4 (Mac: Cmd + T) to add the dollar signs, or type them yourself. - Click F4 and type
=C4/B4. Format the cell as a percentage with two decimals. - Click G4 and type
=D4/SUM($D$4:$D$11). - Select E4:G11 and press Ctrl + D to fill the three formulas down to row 11.
- In B13 type
=SUM(C4:C11)/SUM(B4:B11)for the store-wide conversion rate. - Cross-check:
=SUM(E4:E11)and=SUM(G4:G11)must both equal 100%. If they do not, a range is not locked or a row is missing. - Optional: select A3:A11 and F3:F11 (hold Ctrl for the second range), choose Insert › Bar chart and add a title such as "Conversion rate by traffic source".
Common mistakes and how to spot them
- Unlocked total.
=B4/SUM(B4:B11)copied down becomesSUM(B5:B12)and thenSUM(B6:B13). The shares no longer add up to 100%. Lock the range as$B$4:$B$11. - Reversed ratio.
=B4/C4gives sessions per order (about 50), not a conversion rate. A conversion rate above 100% or a value like 49.9 is the sign of a reversed division. - Averaging the rates.
=AVERAGE(F4:F11)is not the store-wide rate. Divide the total orders by the total sessions. - Mixing sources. Sessions from one tool and orders from another are fine, but both must cover the same dates and the same store. A time-zone difference can move a few orders from one month to the next.
- Reading small channels too literally. The 58 orders of the referral channel can move by ten in either direction from one month to the next; a conversion rate based on a few dozen orders is a hint, not a result.
- Treating last-click as truth. The channel that closes the order gets all the credit. Use the table to find questions, not to cut channels.
Quick reference: functions and shortcuts in this lesson
| Task | Formula or shortcut |
|---|---|
| Share of a total | =B4/SUM($B$4:$B$11) |
| Conversion rate | =C4/B4 |
| Store-wide rate | =SUM(C4:C11)/SUM(B4:B11) |
| Lock a reference | F4 (Mac: Cmd + T) |
| Fill down | Ctrl + D |
| Percentage format | Ctrl + Shift + % |
Real-world uses
- Monthly review: a one-page table of sessions, orders and revenue by channel is the backbone of any marketing meeting for a small store.
- Budget questions: before you move money between channels, check whether a channel earns its share of the traffic, and whether it is a first-visit or a last-visit channel.
- Site health: if the conversion rate of every channel falls in the same week, the cause is more likely the site (a broken checkout, a slow page) than the traffic.
- Seasonal comparison: the same table for March and November shows how the mix shifts when email and direct traffic take over before the holidays.
What this table cannot tell you
A last-click table has limits. It credits one channel per order, so channels that introduce the shop get less credit than channels that finish the sale. It counts sessions, not people, so one customer who visits three times counts three times. And it does not know what would have happened without the channel: some of the email orders would have come through direct visits anyway. Later lessons bring in tools that fix part of this: blended ROAS, which uses total revenue instead of attributed revenue, holdout thinking for the incremental effect, and a data-quality check that compares analytics orders with the back office.
What you will build in the rest of this course
There are twenty labs. Each one uses a different data set from Larkfield Desk Co and checks your formulas as you type them. The list below shows the path.
- Traffic Source Mix. Session share, conversion rate and revenue share by channel (this lesson).
- Conversion by Channel. Add-to-cart rate, checkout completion and the size of a conversion gap in orders.
- Revenue per Session. AOV, revenue per session, an index against the store and the top-three concentration.
- AOV Lever 1: Bundles. COUNTIFS and AVERAGEIFS on an order list: bundle versus single orders and margin per order.
- AOV Lever 2: Free-Shipping Threshold. The stretch zone, extra revenue and the net profit effect of the offer.
- Repeat Purchase Rate. One-time versus repeat customers by acquisition channel.
- Cohort Retention Curve. Active customers by month since first order and the right denominator.
- Contribution Margin per Order. Product cost, shipping, payment fees and packaging by channel.
- Blended vs Platform ROAS. Total store revenue versus the revenue the ad platforms claim.
- Customer Acquisition Cost. CAC by channel, first-order profit and orders to pay back.
- Break-Even and Target ROAS. Margin before ads, profit goal and a rule that flags losing product lines.
- Discount Codes. Discount rate and margin per order by code.
- Returns and Net Revenue. Return rate, refund rate and the channels above the store average.
- Category Margin Mix. Gross margin, rank and concentration by product category.
- Email Flows vs Campaigns. Revenue per 1,000 recipients, order rate and unsubscribes.
- Marketing Calendar. Dates, lead time, planned ROAS and budget by month.
- Bundle What-If. A scenario table for discount depth, take rate and monthly profit.
- Spend Response. Marginal ROAS, SLOPE and FORECAST.LINEAR for paid social.
- Data Quality. Reconciling analytics orders with the back office, with a tolerance and a flag.
- Final Project. A half-year dashboard with blended ROAS, margin after ads, a rule and a combo chart.
Summary
Describe the traffic source mix before you judge any channel. Session share is =B4/SUM($B$4:$B$11), conversion rate is =C4/B4, revenue share is =D4/SUM($D$4:$D$11), and the store-wide conversion rate is total orders divided by total sessions, 2.31% for Larkfield Desk Co in March. Read the session share next to the revenue share: paid social brings 22.5% of the traffic and 10.9% of the revenue, email 9.3% and 20.1%.
Now scroll up to the lab and build the sheet yourself. It takes about fifteen minutes, the Coach checks each step, and you will end up with a channel table you can recreate for your own store from a monthly export.
Frequently asked questions
What is the traffic source mix of an online store?
It is the split of the store's visits (sessions) between sources such as organic search, paid search, paid social, email, direct visits, organic social, referrals and affiliates. Together with the orders and revenue per source it shows where customers come from and which sources earn their share of the traffic.
How do I calculate the conversion rate by channel?
Divide the orders of the channel by the sessions of the same channel. For a store-wide rate, add up all orders, add up all sessions and divide once; do not average the channel rates.
Why compare session share with revenue share?
A channel can bring a lot of visits and little revenue, or the reverse. The two shares side by side show which channels earn more or less than their share of the traffic, so you can decide where to look for problems or opportunities.
Is the last-click view reliable for judging channels?
It is a useful description of where orders were closed, but it gives all the credit to the last channel and none to the ones that introduced the shop. Use it to find questions, and check important decisions with other views such as blended ROAS and holdout tests.
Do I need a Shopify store to follow this course?
No. The labs use a fictional shop and work with any store platform, because they only need exports of sessions, orders and revenue. The platform names in the course are descriptive and imply no affiliation.
Which Excel functions does the first lesson use?
Only basic ones: division, SUM and absolute references with dollar signs. The later lessons add IF, IFS, COUNTIFS, SUMIFS, AVERAGEIFS, SUMPRODUCT, XLOOKUP, INDEX/MATCH, SLOPE and FORECAST.LINEAR, each introduced when it is first used.
Keep going: Online Store Marketing & Growth: Traffic Mix, Conversion by Channel, AOV Levers, Repeat Purchases, Blended ROAS & Contribution Margin
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.
- Conversion by Channel: Add-to-Cart Rate, Checkout Completion and the Store-Wide Benchmark
- Revenue per Session: Average Order Value, RPS Index and Channel Concentration
- AOV Lever 1: Bundles, Items per Order and Margin per Order with COUNTIFS and AVERAGEIFS
- AOV Lever 2: The Free-Shipping Threshold, the Stretch Zone and the Net Profit Effect
- Repeat Purchase Rate: One-Time vs Repeat Customers by Acquisition Channel with COUNTIFS
- Cohort Retention Curve: Active Customers by Month Since First Order and the Right Denominator
- Contribution Margin per Order: Product Cost, Shipping, Payment Fees and Packaging by Channel
- Blended ROAS vs Platform ROAS: Total Ad Spend, Reported Revenue and the Overlap Check
- Customer Acquisition Cost: CAC by Channel, First-Order Profit and Orders to Pay Back
- Break-Even and Target ROAS: Margin Before Ads, Profit Goal and Status by Product Line
- Discount Codes: Orders, Discount Rate and Margin per Order by Code with SUMIFS and AVERAGEIFS
- Returns and Net Revenue: Return Rate, Refund Rate and the Channels Above the Store Average
- Product Category Mix: Gross Margin, Margin Share, Rank and the Top-Two Concentration
- Email Flows vs Campaigns: Revenue per 1,000 Recipients, Order Rate and Unsubscribes
- Marketing Calendar: Event Length, Days Until Start, Planned ROAS and Budget by Month
- Bundle What-If: Discount Depth, Take Rate and Monthly Profit with Locked Assumptions
- Spend Response: ROAS, Marginal ROAS, Slope and a Linear Forecast for Paid Social
- Data Quality: Reconciling Analytics Orders with the Back Office, Gap % and Tolerance Flags
- Final Project: Half-Year Store Dashboard with Blended ROAS, Margin After Ads, a Flag and a Combo Chart