Digging deeper into the database
Posted by Huw,
As I'm sure you are aware Brickset is unique among LEGO fan sites in that it's much more than just a news and reviews site. We have the most comprehensive and up-to-date database of LEGO sets on the internet that can be filtered, sorted and viewed in a multitude of ways to help you keep track of your collection and abreast of product releases.
Our rich user interface exposes a lot of functionality but there are some things that you have to dig a little deeper to find. A good example is queries, which provide a way to filter and sort the database of sets in ways that are not possible otherwise. I'll be writing a tutorial on how to create them for yourself in the coming weeks but for now I wanted to highlight a few that are built into Brickset that you might find useful and/or interesting.
Want to know which currently available sets offer the best price per piece? Or which sets have the worst exchange rate ratio? Read on to find out how...
Queries are accessed from the green Browse menu. On desktop devices you'll see Queries in the menu box; on mobiles you'll find 'View queries' near the bottom of the page. Both will lead you to a page that lists any that you've created at the top and five featured ones that I've written, below.
The full list of featured queries can be accessed by clicking on the View More link at the bottom of the page which will lead you to https://brickset.com/queries/featured.
Let's look at some examples:
Price per piece
This is often used as a measurement of the value of a set. It can be skewed by large, electrical and other specialised pieces but it's easy to calculate and in the absence of a weight of the actual bricks in every set, probably the best we can currently do.
The queries Best price per piece in the UK and Best price per piece in the USA will list all currently available sets, ignoring gear and other extended product lines allowing you to see how to get maximum bang for your buck.
Best
In the UK, Classic boxes are up there at the top, with 10704 Creative Box and 10717 Extra Large Brick Box offering parts at 3.3p each, while in the US 40174 LEGO Chess tops the list with parts priced at 3.8c a piece, closely followed by 10717 Extra Large Brick Box, at 4c pp.
Worst
The queries Worst price per piece in the UK and Worst price per piece in the USA expose other end of the scale although as you will see most of the sets at the top of the list are minifig packs or contain large specialised or electrical pieces.
Scrolling down the list a bit, I will award the 'set that offers the worst value' prize to 10758 T. Rex Breakout (30p pp and 33c pp) which, admittedly contains a huge specialised part, but also includes nearly 150 normal ones.
Surprisingly, in terms of price per piece, it's worse than 76112 App-Controlled Batmobile which has been widely derided for its apparent poor value.
Exchange rate
There is no consistency when it comes to product pricing in LEGO's different markets. It seems that the local marketing teams pluck a number out of thin air, that which it thinks the market will bear, without regard for pricing elsewhere.
Consequently there is a huge variation, with some sets being cheaper in the UK than the US but mostly it's vice versa.
Worst
So, which set currently available has the worst £:$ exchange rate? This query will tell you.
Surprise, surprise, Star Wars and other licensed sets occupy the top spots with the truly awful 75201 First Order AT-ST taking the biscuit at a ratio of 1:0.727 which compares very poorly to today's real-life rate of 1:1.27.
I know US local sales tax has not been accounted for but even at 10% it doesn't explain the discrepancy.
Best
It's not all bad news east of the Atlantic. The Best £:$ exchange rate query shows that the UK gets a much better deal on non-licensed themes, particularly City and Technic.
60165 4 x 4 Response Unit , priced at £20 and $40, a ratio of 1:2, suggests that the US is getting a very raw deal here.
Anyway, these are just a few of the available queries so take a look through the list: you might find something else of interest. And if there isn't one that provides exactly what you're looking for you can always create your own. I'll explain how to do so in a future article.
104 likes
47 comments on this article
@Huw
It seems only half the article was published.
But what a cliffhanger <3! :)
Drumroll please...
I'd love to see the spike in set ownership when things like Argos 3 for 2 deals happen. Or Xmas etc.
Sorry, I pressed publish before I was done!
Thanks for this article.
Um... am I nuts, or are the last "best" and "worst" backwards?
Depends on which side of the Atlantic you are :)
Nope, you're on the other side of the pond, from a UK perspective, which Huw is writing from.
Actually, the titles were wrong, so I've changed them and also made it clearer from what perspective I'm writing from.
Yay, yet more reasons to hate the First Order AT-ST!
Very interesting and helpful, thanks for the article Huw! Btw I now have my Firestar Toys t-shirt and coffee mug (they're giving my neighbor's poor eyes and my scaled hands a break). I'm going to have to play around w/these queries, as if I needed to dodge even more work time on this website lol. Hoping there will be a second part of the article w/all of the Top 5 Featured Queries, as even w/my limited math skills I can suffice that two examples does not equal 5... it's like 2 short right?
I didn't write about the 5 listed on that page but I see how you might have thought I was going to as there was comma missing.
Given it's fairly quick and easy to rustle up articles like this I will write about some more if this one proves popular, maybe next week.
Very interesting.
Shouldn't the Geoffrey Brickheadz set show up under sets that were never released, or are we still holding out hope that it will somehow make its way into stores?
That list only shows sets that should have been released before this year.
Interesting article, is there any plans to expand the database to show more currency exchanges? I was reading the Anakin Jedi fighter review yesterday and shaking my head over the price, $20US 25 pound and here in Australia I think it's $45AUD, which is approx $31US or 25 pound. A set for once on par with UK but well above US, if only shipping wasn't so much sigh!! Hogwarts mircoscale is same story, on par with UK price, but approx $50 more here then the US price :( Still top on the to get list though lol!! :P
I find the query builder really useful - and even more so when combined with the custom tags feature for interogating the database using criteria that are either personal or aren’t captured elsewhere...
I think there might be something wrong with the "most pieces" query, as it's only showing sets that contain 53 pieces.
There's a surprising amount of them.
^Mine is normal, the highest I see is the Chima mega pack with 10k pieces, then 75192.
Thanks for the article and tips. And know that, if I have a few links to several lego fan sites on my smartphone and pc desk, Brickset is the one I would keep if I had to choose only one among them all. News, reviews, databases, courtesy between members and always fair and patient answers to my questions : It's all good! Sincere thanks this time again for all you provide to us Lego lovers.
I would love to view element Design Numbers by date added. In other words new molds. You provide part lists by date added such as “Added on 05 Aug 2018” but I would like the option to see only new elements, not existing elements in new colors. Would that be possible with a custom query?
so if you query "worst sets of all time", I guess first order AT-ST comes at the top
Thanks for this article, Huw. I've always thought that the analytic systems of the database are outstanding and I'm glad to see them getting some much-deserved publicity.
Awesome stuff. Thank you for explaining how we can get even more out of the website/database.
Much appreciated and keep up the great work!
The query listing sets available in the USA but not the UK isn't quite right. For example it lists the Unikitty sets, but these are all on the UK LEGO.com.
That aside, this is a great feature, some really useful queries. I wish BrickLink would take note!
We are reliant on the data LEGO provides in its product feed to produce that query and for whatever reason the Unikitty sets are not currently in the UK feed.
We certainly don't check them all by hand!
In which markets is 10717 called Extra Large Brick Box? Everywhere I've seen, including where I live (where it's a Toys "R" Us exclusive like previous 1500-piece Classic sets), it's called Bricks Bricks Bricks (which, while far less descriptive, is also a more playful and exciting name to me).
Thank you - I was totally unaware of the query functionality
In defense of the T-Rex Transport, I thought it was a bargain. Consider that the same T-Rex was exclusive to a single JW1 set, and was selling for $50 by itself on Bricklink when JW2 sets were announced. Then along comes a $50 set with a T-Rex sporting the same deco, which is also the exact same version that appears as one of the Phantom Zone villains in TLBM, and it was an easy call for me. Yeah, by the time the sets actually hit I could have picked the T-Rex up for $35, but once you add in shipping I'm not sure I would have saved that much. Instead, I got the minifigs and that baby dino (which I also have plans for).
@LegoSonicBoy Outdated name in the database perhaps? It seems Lego renamed the XL sets as 'Bricks Bricks Bricks' recently.
The "No piece count, sets you own" query shows a lot of books for me. It seems like a book should have a 0 part count (or maybe 1 if books are counted as a part.) Does a part count of 0 automatically make a set appear on that query? If so, if there a way to make a set have no parts where the quantity value is empty set?
US customers get some good deals on some of the licensed lines, but a very raw deal almost entirely across the board on City sets. It's crazy. Every wave of the last 3 years or so has been insanely pricey. I'm sure someone has done their research on the markets, but I don't understand it.
Thanks for refreshing my memory of this stuff Huw - I love data! so fun to slice and dice!
One query I'd love to see - but don't think I would be able to build (I'm sure you could) would be the gap between wanted and owned and no longer in production - i.e. Qty wanted less qty owned. I think this would produce a list of the most sought after sets that would be hard to get (Demand exceeding potential supply). I would suspect things like Café Corner and USC Falcon (old one) would be high on the list - but it might throw up a few unexpected / unusual ones? Anyway!
thanks again for all you and the team does!
@ crazylegoman I've modified the query to omit books and other weird stuff.
Great article. I use the queries some but I'm going to play around with it a bit more. While I think this site has many great features, a great display, and is the best Lego site on the net I'm reaching a point where in addition to my sets I'd really like to start inventorying my individual pieces purchased in bulk. Is this a feature that Brickset is likely to receive in the future or perhaps already has and I'm not aware?
Here's one from my cookbook. I call it 'My rarest sets'; sets I own that very few other people own:
SELECT * FROM Sets
WHERE (isnull(Own,0) = 1)
AND (Category 'Book')
AND (Category 'Gear')
AND (Theme 'Bulk Bricks')
AND (Theme 'Service Packs')
AND (Subtheme 'Supplementaries')
ORDER BY OwnCount ASC
No suprise that my top 10 includes mainly old sets like 255 and 398 and limited promotional sets like 4538 and 7912.
It's something I'd like to add but the data we use, from LEGO, complicates it somewhat.
Take a look at https://brickset.com/parts/design-3001 and sort by colour. Notice that there are multiple of several of them, e.g. sand green. Assuming you have some sand green 2x4s, which ones are they? It doesn't matter, but it does complicate things!
Can you use queries to search for sets by a specific price point, or within a particular range of prices? I've found few easy ways to do that, which could be a handy thing to search (especially when I see rumored price points for sets and want to get an idea of the size or features of comparably priced sets).
@Huw Thanks for the rewrite, makes much more sense now.
@Duq, you can also use category = normal instead of those not equal tos.
@Lyichir, yes. We use them when deciding what to nominate for our gift guides at xmas. e.g. https://brickset.com/sets/query-3807
I don't understand why there are some parts that are seemingly identical with different numbers??
Here's an example - Sand Green 1x2 grill plate
https://brickset.com/parts/4163471/radiator-grille-1x2
https://brickset.com/parts/6223126/radiator-grille-1x2
Edit, one more example: 1x1 headlight in flame orange
https://brickset.com/parts/6186006/angular-brick-1x1
https://brickset.com/parts/6020098/angular-brick-1x1
Is the source data you use from Lego available to the public? I’m guessing much of it could be pulled from Bricks & Pieces (and the rest of the Lego Shop) but that sort of data mining is beyond me.
I ask because there really is no resource I know of that one could use to easily track the introduction of new parts. New Elementary, BrickLink, and Brickset are all great fountains of information, but I’m sure I’m not alone in wishing for a list of parts (new molds, not recolors) by introduction date.
^ That data is in our database but not currently easy to surface. I plan to add some sort of report to show it soon.
^^ Nobody outside of LEGO understands why some parts are assigned new numbers, don't worry!
I tried the "No piece count, sets you own" query again, but it still shows all the books. It also now shows the following at the top, right before the set list:
SELECT * FROM sets WHERE (isnull(Own,0) = 1) AND (Pieces IS NULL) AND (Theme 'Gear') ORDER BY SortKey ASC
@Huw Category = Normal still includes service packs, bulk bricks and supplementaries.
@Brother Brain,
Try this: https://brickset.com/parts/category-System/year-2018/tag-Other
Then click on the 'Designs' button near the top. It'll show you all new part designs released this year, excluding printed and minifig parts.
@shikadi: When a part is taken out of production and later put back into production, it will often receive a new element number. So for instance, the grill tile you linked first appeared in three Harry Potter sets from 2002 to 2004, but was subsequently retired until this very year. The headlight brick wasn't absent for quite that long but still went four years between its introduction in a Legends of Chima set and its reintroduction last year for the Iron Man BrickHeadz and Old Fishing Store.