Tabellab

Traffic Source Mix: Session Share, Conversion Rate and Revenue Share by Channel

Lesson 1 of 20Lab

All lessons
  1. Traffic Source Mix: Session Share, Conversion Rate and Revenue Share by ChannelLabFree
  2. Conversion by Channel: Add-to-Cart Rate, Checkout Completion and the Store-Wide BenchmarkLab Locked
  3. Revenue per Session: Average Order Value, RPS Index and Channel ConcentrationLab Locked
  4. AOV Lever 1: Bundles, Items per Order and Margin per Order with COUNTIFS and AVERAGEIFSLab Locked
  5. AOV Lever 2: The Free-Shipping Threshold, the Stretch Zone and the Net Profit EffectLab Locked
  6. Repeat Purchase Rate: One-Time vs Repeat Customers by Acquisition Channel with COUNTIFSLab Locked
  7. Cohort Retention Curve: Active Customers by Month Since First Order and the Right DenominatorLab Locked
  8. Contribution Margin per Order: Product Cost, Shipping, Payment Fees and Packaging by ChannelLab Locked
  9. Blended ROAS vs Platform ROAS: Total Ad Spend, Reported Revenue and the Overlap CheckLab Locked
  10. Customer Acquisition Cost: CAC by Channel, First-Order Profit and Orders to Pay BackLab Locked
  11. Break-Even and Target ROAS: Margin Before Ads, Profit Goal and Status by Product LineLab Locked
  12. Discount Codes: Orders, Discount Rate and Margin per Order by Code with SUMIFS and AVERAGEIFSLab Locked
  13. Returns and Net Revenue: Return Rate, Refund Rate and the Channels Above the Store AverageLab Locked
  14. Product Category Mix: Gross Margin, Margin Share, Rank and the Top-Two ConcentrationLab Locked
  15. Email Flows vs Campaigns: Revenue per 1,000 Recipients, Order Rate and UnsubscribesLab Locked
  16. Marketing Calendar: Event Length, Days Until Start, Planned ROAS and Budget by MonthLab Locked
  17. Bundle What-If: Discount Depth, Take Rate and Monthly Profit with Locked AssumptionsLab Locked
  18. Spend Response: ROAS, Marginal ROAS, Slope and a Linear Forecast for Paid SocialLab Locked
  19. Data Quality: Reconciling Analytics Orders with the Back Office, Gap % and Tolerance FlagsLab Locked
  20. Final Project: Half-Year Store Dashboard with Blended ROAS, Margin After Ads, a Flag and a Combo ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Loading the spreadsheet…

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.

Sessions come from a web analytics tool, orders and revenue from the store back office. In this lesson each order is credited to the last channel the visitor used before buying (a last-click view). That describes where orders were closed, not what caused them: an email click often finishes a purchase that a social ad started.

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.
Never average the channel conversion rates to get the store-wide rate. Add up all orders, add up all sessions, and divide once. A plain average treats a channel with 1,900 sessions like one with 18,400.

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.

ChannelSessionsOrdersRevenueSession shareConversion rateRevenue share
Organic search18,400368$15,98028.0%2.00%23.9%
Paid search9,200262$12,07014.0%2.85%18.0%
Paid social14,800178$7,30022.5%1.20%10.9%
Email6,100299$13,4209.3%4.90%20.1%
Direct7,900253$11,01012.0%3.20%16.5%
Organic social5,20052$2,0607.9%1.00%3.1%
Referral2,30058$2,6803.5%2.52%4.0%
Affiliates1,90050$2,3802.9%2.63%3.6%
Total65,8001,520$66,900100.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.

Conversion rate by traffic source, March0%1%2%3%4%5%6%Email4.90%Direct3.20%Paid search2.85%Affiliates2.63%Referral2.52%Organic search2.00%Paid social1.20%Organic social1.00%Store-wide 2.31%
Figure 1. Conversion rate by traffic source in March. The dashed line is the store-wide rate of 2.31%; email and direct visits sit far above it, paid social and organic social far below.

Share of sessions vs share of revenue, MarchShare of sessionsShare of revenueOrganic search28.0%23.9%Paid search14.0%18.0%Paid social22.5%10.9%Email9.3%20.1%Direct12.0%16.5%Organic social7.9%3.1%Referral3.5%4.0%Affiliates2.9%3.6%
Figure 2. Share of sessions (grey) next to share of revenue (blue). Paid social has 22.5% of the sessions but only 10.9% of the revenue; email has 9.3% of the sessions and 20.1% of the revenue.

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.

Three ratios on one tableSessions65,800 (all channels)Orders1,520 (all channels)Revenue$66,900 (all channels)÷÷Session sharechannel sessions ÷ allsessionsConversion rateorders ÷ sessions of the samechannelRevenue sharechannel revenue ÷ all revenueCompare session share with revenue share to see who earns more than their share of visits.
Figure 3. All three ratios use the same three columns of the table. The dividing line between "traffic" and "money" is the conversion rate.

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

  1. 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.
  2. 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.
  3. Click F4 and type =C4/B4. Format the cell as a percentage with two decimals.
  4. Click G4 and type =D4/SUM($D$4:$D$11).
  5. Select E4:G11 and press Ctrl + D to fill the three formulas down to row 11.
  6. In B13 type =SUM(C4:C11)/SUM(B4:B11) for the store-wide conversion rate.
  7. 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.
  8. 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".
Keep the raw numbers and the calculated ratios in separate columns and never overwrite raw data with formulas. When the numbers for next month arrive, you paste three columns and the ratios update by themselves.

Common mistakes and how to spot them

  • Unlocked total. =B4/SUM(B4:B11) copied down becomes SUM(B5:B12) and then SUM(B6:B13). The shares no longer add up to 100%. Lock the range as $B$4:$B$11.
  • Reversed ratio. =B4/C4 gives 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

TaskFormula 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 referenceF4 (Mac: Cmd + T)
Fill downCtrl + D
Percentage formatCtrl + 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.

  1. Traffic Source Mix. Session share, conversion rate and revenue share by channel (this lesson).
  2. Conversion by Channel. Add-to-cart rate, checkout completion and the size of a conversion gap in orders.
  3. Revenue per Session. AOV, revenue per session, an index against the store and the top-three concentration.
  4. AOV Lever 1: Bundles. COUNTIFS and AVERAGEIFS on an order list: bundle versus single orders and margin per order.
  5. AOV Lever 2: Free-Shipping Threshold. The stretch zone, extra revenue and the net profit effect of the offer.
  6. Repeat Purchase Rate. One-time versus repeat customers by acquisition channel.
  7. Cohort Retention Curve. Active customers by month since first order and the right denominator.
  8. Contribution Margin per Order. Product cost, shipping, payment fees and packaging by channel.
  9. Blended vs Platform ROAS. Total store revenue versus the revenue the ad platforms claim.
  10. Customer Acquisition Cost. CAC by channel, first-order profit and orders to pay back.
  11. Break-Even and Target ROAS. Margin before ads, profit goal and a rule that flags losing product lines.
  12. Discount Codes. Discount rate and margin per order by code.
  13. Returns and Net Revenue. Return rate, refund rate and the channels above the store average.
  14. Category Margin Mix. Gross margin, rank and concentration by product category.
  15. Email Flows vs Campaigns. Revenue per 1,000 recipients, order rate and unsubscribes.
  16. Marketing Calendar. Dates, lead time, planned ROAS and budget by month.
  17. Bundle What-If. A scenario table for discount depth, take rate and monthly profit.
  18. Spend Response. Marginal ROAS, SLOPE and FORECAST.LINEAR for paid social.
  19. Data Quality. Reconciling analytics orders with the back office, with a tolerance and a flag.
  20. 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.

  1. Conversion by Channel: Add-to-Cart Rate, Checkout Completion and the Store-Wide Benchmark
  2. Revenue per Session: Average Order Value, RPS Index and Channel Concentration
  3. AOV Lever 1: Bundles, Items per Order and Margin per Order with COUNTIFS and AVERAGEIFS
  4. AOV Lever 2: The Free-Shipping Threshold, the Stretch Zone and the Net Profit Effect
  5. Repeat Purchase Rate: One-Time vs Repeat Customers by Acquisition Channel with COUNTIFS
  6. Cohort Retention Curve: Active Customers by Month Since First Order and the Right Denominator
  7. Contribution Margin per Order: Product Cost, Shipping, Payment Fees and Packaging by Channel
  8. Blended ROAS vs Platform ROAS: Total Ad Spend, Reported Revenue and the Overlap Check
  9. Customer Acquisition Cost: CAC by Channel, First-Order Profit and Orders to Pay Back
  10. Break-Even and Target ROAS: Margin Before Ads, Profit Goal and Status by Product Line
  11. Discount Codes: Orders, Discount Rate and Margin per Order by Code with SUMIFS and AVERAGEIFS
  12. Returns and Net Revenue: Return Rate, Refund Rate and the Channels Above the Store Average
  13. Product Category Mix: Gross Margin, Margin Share, Rank and the Top-Two Concentration
  14. Email Flows vs Campaigns: Revenue per 1,000 Recipients, Order Rate and Unsubscribes
  15. Marketing Calendar: Event Length, Days Until Start, Planned ROAS and Budget by Month
  16. Bundle What-If: Discount Depth, Take Rate and Monthly Profit with Locked Assumptions
  17. Spend Response: ROAS, Marginal ROAS, Slope and a Linear Forecast for Paid Social
  18. Data Quality: Reconciling Analytics Orders with the Back Office, Gap % and Tolerance Flags
  19. Final Project: Half-Year Store Dashboard with Blended ROAS, Margin After Ads, a Flag and a Combo Chart

Buy course — $9.90 See the full course