Bicycle advert platform - Data Dictionary
JJayes
20/08/2020
Purpose
This is a data dictionary to explain the dataset that has resulted from scraping the adverts for bicycles from gumtree and bikehub.
At the end there is some exploratory analysis.
Background
The second hand bicycle market in South Africa is established and active.
In addition to adverts on notice boards, and physical stores which sell second hand bikes, there are multiple online platforms that host adverts for bicycles. Two such are gumtree and bikehub.
Gumtree is a British online classified advertisement and community website, which was acquired by eBay in 2005. It is free to post adverts on the site and additional payments allow users to move their adverts higher in the list of results. Gumtree allows for advertising of a wide variety of goods, from houses and cars to services and jobs. Gumtree separates bicycles into road and mountain.
Bike Hub is a news and content site that covers the cycling industry in South Africa and also hosts classified ads. Similarly, the ads are free to post but can be given more views through payment for a spot as a ‘featured ad’. Bike Hub separates ads into multiple categories, including “dual suspension mountain bikes, hardtail mountain bikes, road bikes, triathlon bikes, kids bikes, fat bikes”, etc.
The adverts hosted on these sites connect buyers and sellers. The adverts usually contain an asking price, a description of the bicycle and photographs. Both sites provide a way for interested buyers to contact sellers through direct messaging. There are some differences in the structure of the two. Bikehub allows sellers to be rated by buyers in order to signal trustworthiness. Gumtree does not have this feature. Gumtree allows buyers to see the number of views that an advert has relieved from other buyers, potentially signalling scarcity whereby popular adverts may have a quicker turn around time. Bikehub does not offer this feature.
The idea of this study is examine the ways in which these two platforms overlap in terms of the adverts that they host. Are they close competitors, or do they cater to different buyers and sellers? Does bikehub service the top end of the market, and gumtree cater to the middle and bottom? Are any sellers multihoming, or hosting adverts for the same bicycle on both sites? What is the price spread between comparable bicycles advertised?
Data
The data that was scraped includes the adverts for bicycles from both sites. The webscraping process grabs all of the available adverts at a particular time from the site and store the details about each advert in a dataframe.
By repeatedly scraping the adverts it will be possible to see how long an advert us up on the site for before it is taken down by the seller. Currently the dataset shown and explored is just a cross section from the 19th of August.
The dataset has 20 columns. A snippet is shown below.
| ad_number | title | price | text | n_photos | n_views | ad_date | ad_date_safe | seller_name | location | seller_age | n_all_time_ads | n_active_ads | scrape_time | province | site | type | link | page | ad_url |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Mongoose mountain/Road bike | 3500 |
1 x Aluminum Mongoose mountain/road bike for sale. Was fully serviced a few months ago. Willing to let go for R 3500K (open for counter offer) 1 x Avalanche Prima Dona mountain/road bike (for her) for sale. Only a few months old! Paid R 4 747.00 for it, willing to let go for R3 500.00 Contact Lenin 071 379 6397 |
2 | 2094 | NA | a month ago | Lenin | Wellington, Cape Winelands, Western Cape | 2019 | 5 | 2 | 2020-08-20 12:49:28 | Western Cape | gumtree | mountain | https://www.gumtree.co.za/s-mountain-bikes/v1c9800p1page-1/v1c9800p1 | 1 | https://www.gumtree.co.za/a-mountain-bikes/wellington/mongoose-mountain-road-bike/1007684305040912403094009 |
| 2 | 2019 specialized P3 | 25000 | Frame: Aluminium Fork:2020 RockShox pike DJ Handlebar: P3 DJ Brakes: Hope Cranks: hope Rims: 26 P3 rim Hubs: P.Series Dirt Jump Tire: Specialized Renegade Slopestyle | 4 | 134 | NA | a day ago | Terence | Kommetjie, Southern Peninsula, Western Cape | 2016 | 7 | 3 | 2020-08-20 12:49:29 | Western Cape | gumtree | mountain | https://www.gumtree.co.za/s-mountain-bikes/v1c9800p1page-1/v1c9800p1 | 1 | https://www.gumtree.co.za/a-mountain-bikes/kommetjie/2019-specialized-p3/1007869765510910946126409 |
| 3 | 2017 YT Tues cf pro race | 45000 | Size: M Wheel size: 27.5 Frame: carbon fiber Shock: fox float x2 factory Fork: fox 40 factory Handlebar: renthal Carbon bar Brakes: TRP G spec Shifters: SRAM X0 7-speed Derailleur: SRAM X01 DH Cranks: e thirteen LG1R carbon Rims: e thirteen LG1R carbon Hubs: e thirteen LG1R carbon Tire: maxxis dhf & dhr Seat post: sdg yt mob custom | 2 | 337 | 2020-08-17 12:49:32 | 3 days ago | Terence | Kommetjie, Southern Peninsula, Western Cape | 2016 | 7 | 3 | 2020-08-20 12:49:32 | Western Cape | gumtree | mountain | https://www.gumtree.co.za/s-mountain-bikes/v1c9800p1page-1/v1c9800p1 | 1 | https://www.gumtree.co.za/a-mountain-bikes/kommetjie/2017-yt-tues-cf-pro-race/1007858127870910946126409 |
| 4 | Pre-owned Scott Reflex (S) BM4516 | 1950 | View this bike and many like it at https://bikemarket.co.za/shop/bicycles/mountain-bikes/pre-owned-scott-reflex-s-bm4516/For quickest response call or WhatsApp.061 528 8020 We accept trade-ins Boxing and shipping within the Southern African region Our Pre-owned bikes are “Ready to Ride” Guaranteed.Professional Mechanics have serviced the bike, replaced any part that needs replacing.Checked for defects or cracks. Then it’s tested.If the bike passes the test it goes up for sale.Our aim is to bring you Bikes that are as new as possible! | 10 | 13 | 2020-08-20 12:28:33 | 21 minutes ago | BikeMarket | Sandton, Johannesburg, Gauteng | 2016 | 171 | 98 | 2020-08-20 12:49:33 | Gauteng | gumtree | mountain | https://www.gumtree.co.za/s-mountain-bikes/v1c9800p1page-1/v1c9800p1 | 1 | https://www.gumtree.co.za/a-mountain-bikes/sandton/pre+owned-scott-reflex-s-bm4516/1007876800670911057261909 |
| 5 | GT karokaram 29er (S) | 7000 | GT karokaram Sport 2016 29er for sale. Small size 9x3 Shimano groupset Well looked after with some slight scratches. R7000 - contact to make an offer Collection in Dawncliffe westville | 4 | 15 | 2020-08-20 12:25:34 | 24 minutes ago | M01 | Other, West Suburbs, KwaZulu-Natal | 2020 | 5 | 1 | 2020-08-20 12:49:34 | KwaZulu-Natal | gumtree | mountain | https://www.gumtree.co.za/s-mountain-bikes/v1c9800p1page-1/v1c9800p1 | 1 | https://www.gumtree.co.za/a-mountain-bikes/other/gt-karokaram-29er-s/1007876063910912592365909 |
| 6 | SELLING YOUR BIKE? I PAY CASH | 7000 | I PAY CASH FOR YOUR UNWANTED MOUNTAINBIKE !ANYWHERE IN THE WESTERN CAPE. I WILL COME TO YOU !No need to advertise and have countless people wasting your time with silly offers!!!NO SCAMMING OR TIME WASTING! CASH FOR YOUR MTB!SELL YOUR MOUNTAINBIKE OR DO A TRADE IN? ANY WHEEL SIZE, 26ER, 27.5" OR 29ERS !!ANY FRAME SIZE !!MUST HAVE DISK BRAKES !!NO GAME OR MAKRO BRANDS !!BEST PRICES OFFERED 24/7SEND ME A SMS/WHATSAPP/EMAIL OR GIVE ME A CALL IF YOU WANT TO SELL OR DO A TRADE IN !!0826038599Tags: giant merida mtb mountainbike bicycle cycle specialized cannondale scott bianchi titan duel suspension hardtail softtail trek silverback momsen shimano sram deore gt zaskar karakoram mongoose carbon alloy bmc northern southern suburbs cape town century city strand gordons bay somerset west stellenbosch paarl milnertonTo Connect on Whatsapp, please click:https://api.whatsapp.com/send?phone=27826038599Items Available: 1 | 1 | 717 | NA | a month ago | LizelleVDM | Kuils River, Northern Suburbs, Western Cape | 2011 | 7430 | 13 | 2020-08-20 12:49:36 | Western Cape | gumtree | mountain | https://www.gumtree.co.za/s-mountain-bikes/v1c9800p1page-1/v1c9800p1 | 1 | https://www.gumtree.co.za/a-mountain-bikes/kuils-river/selling-your-bike-i-pay-cash/1007707981440910003575409 |
| 7 | 16" Galaxy kiddies bikes | 750 | 16" Galaxy kiddies bikes.R750 each call or whatsapp on:0824634436For more info:cyclegymworx.co.zaLike us on facebook:Cycle & Gym Worx | 2 | 6 | NA | an hour ago | Bhupen | Johannesburg South, Johannesburg, Gauteng | 2012 | 35850 | 63 | 2020-08-20 12:49:38 | Gauteng | gumtree | mountain | https://www.gumtree.co.za/s-mountain-bikes/v1c9800p1page-1/v1c9800p1 | 1 | https://www.gumtree.co.za/a-mountain-bikes/johannesburg-south/16-34-galaxy-kiddies-bikes/1007876600700910003592209 |
| 8 | Bicycle - Giant Anthem 29 ER Mountain Bike | 39995 | Bicycle - Giant Anthem 29 ER Mountain Bike (NEW) Size: Small. Price: R39995 We Deliver! Kindly Contact Us For Details. Tel: (021) 715 07 32Monday to Friday 9.30am-6pm. Fridays [Closed from 12pm-2pm]Saturday 9am-2.30pm.Items Available: 1Stock ID: 26Bridge Cycles Bicycle Store in South Africa, has been providing bicycles, road bikes, mountain bikes and kids bikes for over 29 years. Now Delivering Nationwide! www.bridgecycles.co.za | 1 | 2670 | NA | a year ago | Bridge Cycles CC | Tokai, Southern Suburbs, Western Cape | 2017 | 33 | 33 | 2020-08-20 12:49:39 | Western Cape | gumtree | mountain | https://www.gumtree.co.za/s-mountain-bikes/v1c9800p1page-1/v1c9800p1 | 1 | https://www.gumtree.co.za/a-mountain-bikes/tokai/bicycle-+-giant-anthem-29-er-mountain-bike/1005097716540911247410409 |
| 9 | 29ER SILVERBACK SPECTRA COMP MTB_TUBELESS CONVERSION DONE_HYDRAULIC DISK BRAKES_DOUBLE WALL RIMS | 6500 | 29ER Silverback Spectra Comp Mountainbike! EXCELLENT CONDITION. GET ON AND GO!LOCKOUT FORKTUBELESS CONVERSION DONE29ER WHEELS, LARGE 19INCH FRAMESilverback Spectra Comp Alloy MTBSR Suntour XCM Fork with Lockout and PreloadSBC Tubeless Double Wall RimsTubeless TyresShimano Hydraulic Disk Brakes Shimano Alivio 9 Speed JockeyShimano Altus Front Derailleur Shimano Altus Rapidfire Shifters Shimano CrankSilverback Sector Handlebar and StemSilverback Sector Seat PostBontrager SeatComes with a Pump and Holder.Viewing and Testing Welcome! RACE READY // GET ON AND GO!Price: R6500 NOT NEG! For more infoLizelle: 0826038599Items Available: 1 | 13 | 170 | 2020-08-19 16:49:40 | 20 hours ago | LizelleVDM | Kuils River, Northern Suburbs, Western Cape | 2011 | 7430 | 13 | 2020-08-20 12:49:40 | Western Cape | gumtree | mountain | https://www.gumtree.co.za/s-mountain-bikes/v1c9800p1page-1/v1c9800p1 | 1 | https://www.gumtree.co.za/a-mountain-bikes/kuils-river/29er-silverback-spectra-comp-mtb-tubeless-conversion-done-hydraulic-disk-brakes-double-wall-rims/1007872087290910003575409 |
| 10 | Boys bike 20’ | 650 | Boys 20’ bike in very good condition, selling for a larger bike. | 3 | 14 | 2020-08-20 10:49:41 | 2 hours ago | Garth | Bellville, Northern Suburbs, Western Cape | 2014 | 120 | 1 | 2020-08-20 12:49:41 | Western Cape | gumtree | mountain | https://www.gumtree.co.za/s-mountain-bikes/v1c9800p1page-1/v1c9800p1 | 1 | https://www.gumtree.co.za/a-mountain-bikes/bellville/boys-bike-20/1007876116030910238812709 |
The fields are as follows:
| Variable | Description |
|---|---|
| ad_number | Index |
| title | Title of advert |
| price | Asking price |
| text | Text of ad/description |
| n_photos | Number of photos |
| n_views | Number of views on gumtree adverts at time of scraping |
| ad_date | Date of advert’s posting |
| ad_date_safe | Error catching date column |
| seller_name | Seller Name |
| location | Location of ad |
| seller_age | Date of seller’s registration on platform |
| n_all_time_ads | Number of ads posted by seller ever |
| n_active_ads | Number of ads currently up from seller |
| scrape_time | Time of scraping |
| province | Province (from location) |
| site | Site: gumtree or bikehub |
| type | Bike type |
| link | Link to page of ads |
| page | Page number |
| ad_url | Ad URL |
Exploratory analysis
Geographic market
First we look at the geographic market, where are the majority of adverts posted for each site?
## `summarise()` regrouping output by 'province' (override with `.groups` argument)
Distribution of prices
Are road bikes or mountain bikes more expensive, on average? We look first at Gumtree
## Picking joint bandwidth of 2420## Picking joint bandwidth of 4530
It is evident that prices have a long tail on the right, indicating a few expensive bikes in the Eastern Cape, Gauteng and the Western Cape.
What about for bikehub? They have many more types so we will plot them lumped into the three largest categories and “other”
## Picking joint bandwidth of 10500## Picking joint bandwidth of 6390## Picking joint bandwidth of 7720## Picking joint bandwidth of 5870
The picture is more mixed here and there are bimodal distributions for many provinces and bike types. There is also a wider spread in terms of price than on gumtree.
Seller specifics
How do the sellers differ?
We have information on seller’s date of registration on the platform, the number of ads over all time and the number of ads currently active from that seller. First we look at the relationship between current ads and all time ads. In other words, are sellers repeat users of the platforms?
## `geom_smooth()` using method = 'gam' and formula 'y ~ s(x, bs = "cs")'
For gumtree, there is a positive relationship between active ads and all time ads, at least for fewer than 2000 all time ads. It appears that sellers on gumtree have listed many many more ads than those on bikehub.
There are at least two explanations for this. First, gumtree allows for the sale of a wider variety of things, not only bicycles, so it makes sense that the average seller may have more ads on gumtree than bikehub if they sold other household items on gumtree. Second, the format of gumtree’s rankings shows more recently posted ads at the top of the search page, and so there is an incentive for dealers to post the same ads every day in order for their ads to be at the top of the results. The same holds for bikehub, but perhaps people do not do it?
How long have sellers been registered on the different platforms?
It appears that more sellers have registered on bikehub recently than on gumtree, there the bulk of users is a little further left, indicating registering earlier. It is also worth noting that prior to 2008, gumtree had few users while bikehub had some, potentially indicating that bikehub was the leader. However our sample of adverts is form users still active today - so it may indicate that some fraction of bikehub’s users are repeat advertisers who registered longer ago.
It is worth cautioning that this is not a time series plot of the number of users - rather it is the date of registration of active users whose ads were scraped.
Next we turn to the number of ads over time and their year of registration.
## `geom_smooth()` using method = 'gam' and formula 'y ~ s(x, bs = "cs")'
As we expect the people that have joined most recently have the fewest number of adverts. The two look similar, but it is worth noting that there are far fewer all time ads per seller on bikehub. The y-axis is log scale.
What about the quality of the advert and price?
Is there a relationship between the number of images and length of description and the price asked?
My intuition is that the adverts that contain more information are likely to be for a more expensive bike.
We look first at the length of the text in each advert (measured by number of characters) compared to the log of price.
## `geom_smooth()` using method = 'gam' and formula 'y ~ s(x, bs = "cs")'
It appears that for gumtree users, the higher the asking price the longer the description of the bike in text. This is less clearly a linear upward trend for bikehub adverts. The smoothed line is quity kinky and the wider confidence interval does not indicate a strong relationship. Regression analysis on this topic will follow.
Next we turn to the relationship between number of photos and price.
## `geom_smooth()` using method = 'gam' and formula 'y ~ s(x, bs = "cs")'
It appears that adverts on bikehub have a larger number of images on average. On gumtree, it appears that there is indeed a relationship wherein as the log of price increases, so does the number of images. This isn’t conclusive.
There is scope for a more complex text analysis which can text mine the descriptions in the adverts and find specific words associated with higher asking prices or relatively larger number of images in each advert, and contrast these between the two sites. It is relatively easy to implement, I just need a little bit of time to get it working.