HomeDashboardLead conversion

Analysis 09 of 10Sales

Where should sales focus next quarter?

Which lead sources, landing pages and seller types should SDRs and reps work first, and which closed sellers actually start selling?

The short answer Work search leads first: organic and paid search closed 14.7% and 14.8% of the time in early 2018, against 6.3% for social and 4.0% for email, even though social brought 1,051 leads. Work them fast, since half of the deals closed within 12 days. And treat the close as a start: only 51.7% of sellers won by May 2018 made a sale by August, led by big online sellers at 72.7%.

  • Public data, not my business
  • Anonymized sample of 8,000 leads
Source
Marketing Funnel by Olist, joined to the Olist public orders data
Period
Leads Jun 2017 to May 2018
Licence
CC BY-NC-SA 4.0
Charts
5, each with its data table and CSV

Key figures

  • 13.0%Lead to deal conversionof 5,998 leads first contacted Jan to May 2018
  • 14.7%Search leads that closedvs 6.3% from social and 4.0% from email
  • 12 daysMedian days to closefirst contact to won, 781 deals from 2018 leads
  • 51.7%Won sellers who made a saleof 665 won by May 2018, orders through Aug 2018

The charts

Chart 1Lead chart · ranking

Search leads close more than twice as often as social or email.

Which lead sources should reps work first?

Organic and paid search closed 14.7% and 14.8%. Social brought 1,051 leads but closed 6.3%. 'Unknown' is Olist's own label for 813 leads with no tracked channel. Leads first contacted Jan to May 2018.
Download CSV

Olist marketing funnel · public data, not my business

By channel: 11 rows. conversion_by_origin in sales.json; leads first contacted Jan to May 2018.
ChannelConversion (%)LeadsWonMedian days to close
Not recorded27.148137
Unknown20.781316810
Paid search14.81,18217513
Organic search14.71,73625513
Direct traffic14.2380549.5
Referral11.42112418.5
Other publicity7.341335
Display6.775510
Social6.31,0516623
Email43471419.5
Other3.511449

Chart 2Distribution

Lead volume and closing rate do not line up.

Do the channels that bring the most leads also close them?

Each dot is a channel: leads on the bottom axis, conversion up the side. Organic search brings the most leads and closes well; social brings 1,051 leads but closes 6.3%.
Download CSV

Olist marketing funnel · public data, not my business

Volume against rate: 11 rows. The same rows as chart 1, plotted as leads against conversion.
ChannelLeadsConversion (%)
Not recorded4827.1
Unknown81320.7
Paid search1,18214.8
Organic search1,73614.7
Direct traffic38014.2
Referral21111.4
Other publicity417.3
Display756.7
Social1,0516.3
Email3474
Other1143.5

Chart 3Ranking

The landing page matters as much as the channel.

Which landing pages turn leads into deals?

The two busiest pages closed 21.7% and 23.0% of their leads; the third busiest closed 5.5%. Pages with 100+ leads, Jan to May 2018.
Download CSV

Olist marketing funnel · public data, not my business

Landing pages: 10 rows. conversion_by_landing_page in sales.json, pages ordered by lead count.
Landing page (anonymized id)Conversion (%)LeadsWon
b76ef321.7751163
22c29823726167
58326e5.549527
ce1a6516.234656
88740e8.530626
40dec921.529764
f017be8.922420
e492ee4.22149
a7982111.413215
73f31a3.51134

Chart 4Distribution

Half of the deals close within 12 days.

How fast do won deals close?

Half of the deals from 2018 leads closed within 12 days and 54.5% within two weeks. Resellers and manufacturers follow a similar pattern.
Download CSV

Olist marketing funnel · public data, not my business

Time to close: 6 rows. days_to_close in sales.json, deals from 2018 leads.
Days from first contact to wonAll won deals (%)DealsResellers (%)Manufacturers (%)
7 or less36.228337.135.4
8 to 1418.31431623.3
15 to 3016.112616.315.7
31 to 6010.98510.611.7
61 to 905.2415.54.5
Over 9013.210314.59.4

Chart 5Breakdown by segment

Closing is not the finish line: big online sellers sell, offline ones often do not.

Which closed sellers actually start selling?

72.7% of big online sellers sold something within the data, against 32.9% of offline and 38.0% of industry sellers. Sellers won by May 2018. Revenue is item revenue in Brazilian reais from Olist's public orders data.
Download CSV

Olist marketing funnel · public data, not my business

After the close: 8 rows. sold_by_lead_type in sales.json, business type All, sellers won by May 31, 2018, orders through August 2018. The Not recorded row has only 4 sellers.
Lead typeMade a sale (%)Sellers wonSoldItem revenue (BRL)
Not recorded754345,079
Online, big72.79972263,185
Online, medium58.1270157193,253
Online, small42.9632752,388
Online, beginner41.9431821,681
Online, top41.71255,235
Industry38923527,631
Offline32.9822718,502

01Findings

What the charts say.

  1. Organic and paid search close 14.7% and 14.8%; social 6.3% and email 4.0%.

    Chart 1 Chart 2

  2. The two busiest landing pages close 21.7% and 23.0%; the third busiest closes 5.5%.

    Chart 3

  3. Half of the deals from 2018 leads closed within 12 days, and 54.5% within two weeks.

    Chart 4

  4. Only 51.7% of won sellers made a sale by August 2018: 72.7% of big online sellers, 32.9% of offline sellers.

    Chart 5

02Method

How the figures were built.

Steps

  1. Joined all 842 closed deals to the 8,000 marketing qualified leads on mql_id: every deal matched one lead, so the table has one row per lead and a won flag.
  2. Conversion and time to close use the 5,998 leads first contacted from January to May 2018. The closed deals file has no deal won before December 5, 2017, so a 2017 lead that closed earlier cannot be told apart from one that never closed (2017 leads show 0% to 5.5% conversion by month).
  3. Joined each won seller to Olist's public orders data on seller_id, counting orders that were not canceled or unavailable and summing their item prices (Brazilian reais). The sale check uses sellers won by May 31, 2018, since the orders data effectively ends in August 2018 (16 orders in September, 4 in October).
  4. Kept the publisher's labels: 'unknown' is an origin value in the source, separate from the 48 2018 leads (60 overall) with no origin at all.
  5. days_to_close is won date minus first contact date. One deal is recorded as won 2 days before its first contact date; it is kept as published.
  6. Did not use declared monthly revenue: it is 0 for 797 of 842 won leads. has_company, has_gtin, average_stock and catalog size are blank for most deals too.

8,000 marketing qualified leads, all used; 842 closed deals joined. Every derived figure on this page (changes, indexes, averages, counts) is computed from the published rows, and the table under each chart says how.

Checks

  • Every one of the 842 closed deals matched exactly one lead on mql_id.
  • Conversion uses only leads first contacted Jan to May 2018, because the deals file has no deal won before Dec 5, 2017.
  • Each table on this page is the published chart data in sales.json, shown row for row.

The queries

The query runs on the table seller_leads (8,000 leads). Open it in Live SQL, which runs DuckDB in your browser on the same public tables; the query loads ready to run.

Conversion by channel, 2018 leads

Open in Live SQL 
SELECT origin, count(*) AS leads,
       count(*) FILTER (WHERE won) AS won,
       round(100.0 * count(*) FILTER (WHERE won) / count(*), 1) AS conversion_pct
FROM seller_leads
WHERE first_contact_date >= DATE '2018-01-01'
GROUP BY origin
ORDER BY conversion_pct DESC

Conversion by landing page (100+ leads)

Open in Live SQL 
SELECT left(landing_page_id, 6) AS page, count(*) AS leads, count(*) FILTER (WHERE won) AS won, round(100.0 * count(*) FILTER (WHERE won) / count(*), 1) AS conversion_pct FROM seller_leads WHERE first_contact_date >= DATE '2018-01-01' GROUP BY 1 HAVING count(*) >= 100 ORDER BY leads DESC

Won sellers who made a sale, by lead type

Open in Live SQL 
SELECT lead_type, count(*) AS sellers, count(*) FILTER (WHERE seller_orders > 0) AS sold, round(100.0 * count(*) FILTER (WHERE seller_orders > 0) / count(*), 1) AS sold_pct, round(sum(seller_item_revenue_brl)) AS item_revenue_brl FROM seller_leads WHERE won AND won_date < TIMESTAMP '2018-06-01' GROUP BY 1 ORDER BY sellers DESC

Deals and days to close by sales rep (anonymized)

Open in Live SQL 
SELECT left(sr_id, 6) AS sales_rep, count(*) AS deals, median(days_to_close) AS median_days_to_close, count(*) FILTER (WHERE seller_orders > 0) AS sellers_who_sold FROM seller_leads WHERE won AND first_contact_date >= DATE '2018-01-01' GROUP BY 1 ORDER BY deals DESC

Caveats

  • Real but anonymized and randomly sampled by Olist: 8,000 of its marketing qualified leads from a Brazilian marketplace, June 2017 to May 2018. Seller sales come from Olist's separate public orders data, which may not hold every order.

Releases and revisions

  • A fixed public release (as of November 2018); it is not revised.

03Sources

Sources and licence.

Licence: CC BY-NC-SA 4.0. Public data, not my business.

Download the chart data

Each CSV is made in your browser from the rows in that chart's table.

All ten analyses on the Dashboard

04Contact

Want this kind of breakdown on your own data?

Open to e-commerce, data and operations roles. Los Angeles or remote.

zacharybhenley@gmail.com Book a 30-minute call