Sometimes, pre-built reports just don't do the trick. Whether you're planning your inventory strategy, quantifying marketing efforts, or checking whether that 10% discount is passing muster, unusual questions crop up from time to time, and it's important that you can get an answer without fighting Excel for an afternoon.
When you should use it
The Data Explorer is an advanced reporting feature. This means that while you can pull an answer to almost any question out of it, it's not a general-purpose tool. If you can get an answer to your question using any of our other reports, or by summing a column in a spreadsheet export, do that instead.
There are several caveats (more on that below) that make the Data Explorer a little bit dangerous — the additional precision the Data Explorer offers has some sharp edges you need to be aware of.
How to use the Data Explorer
The Data Explorer lives alongside ConsignCloud's other reporting features, at Menu » Reports » Data Explorer. While table views allow you to see your data in a tabular format, summing, averaging, or otherwise aggregating values within those tables isn't supported. The Data Explorer makes this possible, giving you a convenient way to build custom reports.
The Data Explorer also automatically traverses relations between objects in the system, giving you several unique angles on your data. This can be useful when analyzing sales data by account or by customer, for example.
The report variant is your starting point — everything else depends on it. This is the type of data that forms the hinge point for everything else in the report. We automatically traverse related objects for you so that you can connect (for example) sold items to customer accounts, or discounts to item data.
This turns on the ability to traverse related items when selecting aggregates. This is more likely to give you questionable results, so be careful when using it. More on that below.
Here is where you can choose what numeric fields to aggregate. Currently average, sum, and count are the only supported functions.
Group by fields allow you to group aggregates into buckets. For example, if you want average item price by brand, average the tag price, and group by brand.
You can also group by a unique attribute, like SKU to avoid the aggregate function and simply get a list of all matching data. This can be useful for traversing relations to get extra context, since that isn't possible with regular table views.
These work exactly the same way as filters on table views do: choose a field, an operator, and a value, and we'll limit results to records matching those constraints. These are especially important to pay attention to when traversing relationships between objects, since those may be blank, or duplicates. See below for more information on that.
Once you've selected your report criteria, simply click "Generate Report" to get a quick table overview of your results. You can also click "Export" to get the same information in a spreadsheet.
If there's a report that you frequently access, or which was difficult to get fine-tuned, you can ask ConsignCloud to remember it by clicking "Customize" under the Views menu.
Some Sharp Edges
It's important to realize when using the Data Explorer that its power and flexibility comes from relaxing a few constraints that are usually in place to ensure that the data ConsignCloud spits out is correct and consistent. In other words, it's up to you to make sure that you're asking the right questions.
Below are a few different categories of errors you might run into when using the Data Explorer. If you have any questions on a particular report, please reach out to support.
Deletions and Voids
In ConsignCloud, we do soft deletes. In most cases we don't actually remove an item, account, category, etc. from the database when you delete it; we archive it so we still have it for historical purposes. Likewise, when a sale is voided, it doesn't completely disappear; it sticks around in the background.
The two modes work a bit differently, however — you should never see data from a voided sale in the Data Explorer, but you may see (for example) items entered under an archived account.
Missing or Duplicated Fields
Similarly, depending on how some objects in the system link with others, some data can end up skewed. When this happens it's important to understand that the report isn't broken — you may just be asking the wrong question.
The first case to consider is when a field is optional. This matters because blank fields are included in aggregate functions. For example, if you averaged item split across three items — one retail item and two consignment items with 40% and 50% splits respectively, you won't get the average consignment split. Instead of an average split of 45%, you'll end up with an average of 30%. This is obviously not what you're after! In situations like this, the solution would be to apply a filter that narrows your report down to consignment items only.
The second case is when one object might be related to another one more than once. An example of this is how discounts are related to sales — if a discount is applied to multiple line items, the related sale will show up multiple times when reporting on discounts. So, for example, if you sum the sale's total the result will be inflated, depending on how many discounts were applied in each sale! The solution here is simply to know how the data is structured and act accordingly — which can take some care to pull off.
Mismatched Historical Data
It's important to be aware that some historical data is included in the Data Explorer, right alongside data that is up-to-date. Keeping in mind which is which is crucial to getting correct answers out of the report.
In particular, account balance history, item status changes, and sales-related data is all anchored to a point in time, whereas items and accounts are always up-to-date. The purpose of having both types of data side-by-side is usually to filter or group by current information, while aggregating historical data.
What you should not expect is for items or accounts to appear in a historical form. For example, if you are analyzing line items, and group by an item's account, it will show the item's current account, even if the piece was under a different account at the time it was sold.