The Basics of Reporting | Sellercloud User Conference 2021

Moshe Lerman: My name is Moshe Lerman, and I’m here today to take you through the Reporting Suite that Soundcloud has to offer. So while many of you may already be taking advantage of Soundcloud’s reports, what you may not know is that Soundcloud offers over 60 reports to help your business be as profitable and as successful as possible. In this presentation, I’ll focus on providing an understanding of the structure of our Reporting Suite in order to provide a fuller picture of what we have to offer, thereby allowing you to utilize it to its full potential. Now, for starters, considering the vast array of options that we provide for our Reporting Suite, this can be overwhelming, and trying to look for the right report that will fit your needs. In light of this, here’s a graphic that provides a very general breakdown of the different categories of the Report section, and how they relate to the different roles within your company.

Now, if you see on the right of the graphic, we have laid out a very generic organizational structure of a basic e-commerce business and on the left, you’ll see the different categories of reports. Let’s start with the organizational structure. So on top, you have Management, those of you who are in charge of the big picture of the company, keeping track of your bottom line profit. So those of you who are in Management will want to check out our profit and loss section bar reports over here. These reports provide a breakdown of the profit and loss of your company to give you a picture of your company’s bottom line and financial position. Then we have an Accountant, their accountant who’s in charge of Accounting and Transaction auditing to help keep their books straight. So they would also use a Profit and Loss report. However, what they would put in addition to that, and that way, you can keep accurate financial records. And if you see over here, the transaction reports include a Transaction report section and a Settlement Reconciliation Report section which allows you to reconcile the channel settlements with the transactions in some part of the auditing process. And finally, for those of you working on the ground floor involved in the operational side of the company, such as Purchasing, Sales, and Warehouse, we have a plethora of operational reports that will help you do your job quicker, easier and smarter. Now, of course, we know that many of you wear more than one hat at your company. But I think looking at the Reporting Suite from the perspective of these three categories of Profit and Loss transactions, and Operations, and how they relate to these roles that we mentioned, this can give you an idea of where you want to look first when checking what we have to offer in our Reporting Suite. Now as you saw on the video you just watched running an e-commerce business can be a lot more complex than just a brick and mortar. There are shipping costs, channel commissions, and dropship fees. So Sellercloud takes all these fees and has created a formula to determine what the profit is for each order. This is a process in computing referred to as ETL, which stands for Extract, Transform, and Load. This is the general procedure of copying data with one or more sources. In our case, this would be the part of the database that has all the operational data, such as order details shipping, as you can see in this graphic into a destination system, the part of the database where the P&L data exists for reports, which represents the data differently from the source or in a different context than the source. So in our case, this allows us to provide an easier format for you to view for reporting. Since running these calculations uses a lot of server resources in order to avoid any slowness on your server run these calculations at night, so as not to disrupt your business operation. So when an order is totally shipped, it gets added to a list of orders to be processed that night. Because of this, when running Profit and Loss reports and sales reports, data will only be as accurate as the day before. So if let’s say return just came through and order was just shipped out this morning. These transactions will not be available on the profit reports yet. So every night each server goes through the ETL process which extracts all the data, transforms it by running complex calculations to determine the order’s profit and loss, and then saves it in a clear way for us to show you when you run our reports. Now there are some reports that do show real-time data. And I’ll point them out to you when we get there.

Now, what happens if you can’t wait? If you just shipped out an order this morning, or just added a return on the product that won’t show up in a report until tomorrow, just refunded order. So if you need to see the order’s full profit now you can see this green arrow on the P&L tab on the Manage order details date. By clicking this button, it will automatically run the formula we use to calculate the profit and loss for an order and give you accurate and up-to-date information. You can also run the calculations on multiple orders through an action on the manage orders page or generate P&L and see over here.

Let’s go into the actual reports. Starting with the P&L reports. The first section we’re going to look at is the P&L to import section. The focus of this section is to provide reporting on your bottom line profit and profit margin. Included in this is the ability to run reports that provide detailed breakdowns of your revenue as well as the costs involved in generating that revenue. Now before we look at actual reports, let’s go through the date type options you can use when running these reports. There are three basic data options. When running your profit and loss reports, you have your order and ship dates, which are quite simple. Now, the order and ship dates have only one date per order, because obviously, when an order is placed, there’s only one date and then the ship date because we don’t do partially ship orders. In Sellercloud, there’s only one ship date per each and every order. However, the transaction date is the one that’s a little more complicated. So let’s first define what a transaction is. A transaction is an exchange of funds or assets that will reflect the change in the status of the finances or the seller. Therefore, there are many events within the context of a specific order that would qualify as a distinct transaction reflecting the exchange of funds or assets. Examples of these would be a refund, return, and adjustment. So if you have an order with a refund, and then there’s an adjustment that happens after or a reimbursement that happens after that those are each distinct transactional events. That’s what we refer to when we say Date filtering when you’re running the report, and you want to know what date am I using to consider this particular sale or and its activity. When we’re talking about running it by Transaction Date, it will include the transactions that happened within that date range. So now that we spoke about April, let’s just start off with the P&L dashboard, which is the first option that you have when navigating to the property loss report. Now, this P&L dashboard is pretty much the same format used throughout the report sections that have dashboards, but the variations are only in the type of data displayed. So this is the basic format. The type of data will vary based on the specific section. So in this section, just see data per day, profit, profit per order, and that kind of thing. On the top left corner, here, you see over here, we have it run for the past seven days. But this also includes past 30 days, past 60 days, based on the current date, and we’ll go as far back as you want, ending with the most current data available. With the Profit and Loss reports, this won’t be as current as yesterday. In the top right corner, you see the option for custom filters, and this allows you to display data based on the custom date range selected, but also has an option to filter by company and slash or channel.

Now to the format of the dashboard itself. On the top, we have two charts to visualize the data. The bar chart on the left is to visualize the profit trends over time. And then on the right over here we have a pie chart, showing the profit per channel. Then in the middle, we have three basic metrics that can be used as KPIs. Then finally, on the bottom, we provide two tables with top five performing items. Now one additional note, you’ll see on the corner of this bar chart over here, the percents on the top right corner of each chart over here, over here, over here, and the percent change column on the bottom two tables with your top five items, these represent the change in data for the date range that is run in relation to the previous date range. Now for the first actual report in this section. This is the profit by order summary. This allows you to summarize the profit and loss into groupings such as company, channel, vendor, and wholesale customer. This allows you to get an idea of the profit performance based on the group being selected. And this is done by selecting the summarize by dropdown. Selecting summarized by option from the drop-down right here.

The next report we have here is a profit by order details which you can use when you want to view the profit and loss breakdown for particular orders and their margins. Just a couple of notes, when you run it by transaction, number one, the orders with multiple transactions such as refunds and adjustments, the data will show with a credit and a debit line with the original sale being considered the debit and any ensuing transactions as the credit. Point number two, the orders included in the report are any orders with transactions in that date range even if their ship and order date were not less than the date range selected. And this is as opposed to running the report based on ship and order date, which will number one essentially summarize all the activities for that particular order into one line and then number two when you run it by ship and order date, this associates order activity only with the original order and shipping. So if for example an order is placed and shipped in January and then you have a refund in February so then if you go ahead and run the report in February this order none of the order dates for this order is going to come up because this ship date is in January even the refund. Even though the refund took place in February. As opposed to when you run it by Transaction Date the original sale will not show up. If you ran it just for February, and the order was placed and shipped in January, but there was a refund in February, then the refund will show up in February when you run it for February, but the original sale will not show up.

Next, we have the Profit By Product reports. Starting with the summary report, which allows you to view the profit and loss per product within the date range specified summarized per product. Then we have the profit by product details, which allows you to view a detailed breakdown of the profit and loss on a product per sale or transaction depending on the detail that you use. Now, if you remember what we said above in the Profit by order reports with regard to date filtering, the same basic principle applies to these reports with one added point, which is that when you run the reports by product using the transaction data, additional transactions such as storage fees that have no related order, will show up in these profit by product reports when you run it by transaction dates, but the non-order transactions will not show up in the profit by product reports that are run using ship an order data because storage fees do not have a ship date or an order date. They are a transaction date when the channel was charging you for that store. This is obviously true for all the profit by order reports, no matter the date type, even if you want to run it by Transaction Date because those are by definition profit by order reports, and therefore if there are transactions for your business that don’t have a related order that those will not be included in the profit by order.

The next section we have here is our Sales report. This gives you data on all shipped orders. These reports allow you to evaluate business performance as of the current date or within a specific date range. These differ from the profit and loss reports you just saw because these reports focus on giving you performance-based metrics such as gross sales, quantity ordered, and number of orders rather than the focus on a profit and loss breakdown. The first report we have here is the Sales By Date Range. This is a great report for getting you the basic data you need for the orders within a certain date range. Next, we have the Sales Summary Report. This page is a little more advanced, it allows you to view the performance metrics grouped by product type, sales representative, vendor, warehouse, multiple other options. Now when you’re running it based on company when you click the drop-down arrow over here, you’ll get data further broken down by channel. And vice versa if you want to by channel you can get data further broken down by company. Now we also have other reports in this section that can show you your sales on a detailed level based on Channel warehouse and product. The next section we have is the order report section. This section will give you data about all orders that exist in Sellercloud with a focus on the status of those orders, mainly their order status, ship, and payment status. Orders that were shipped out more than a day ago will have more accurate data shown since they went through Sellercloud’s ETL process. Unshipped orders will appear with an estimated profit amount. So this is the one section where you can get current data and you don’t need data that’s specifically generated for P&L through Sellercloud’s ETL process.

Here’s a summary. In the order summary report, you can see how your business is doing in terms of how many orders are coming in. And again over here, you can drop down by clicking the arrow and see a breakdown of the type of orders that are here, you see the ship status, order status, and over here you’ll see the profit. Next, we have the Order Details report. And there are many different filters that can really help you track your company performance, where you can filter by ship status, order status, wholesale customer, payment status, and profit status. Profit status means that once the order is shipped out and has been generated for P&L that’s considered actual profits as opposed to before it’s shipped out it’s just estimated profit because we don’t have all the different detail relating to that. You see the different filters over here – payment status, fully paid, partially paid, no payment.

The next section we have is the Returns and Refunds report section. The focus of this section is to provide reporting on all returns or refunds issued within a specific time period across all the companies or within a particular company, company group, or sales channel. First, we have the refund Summary Report, which is used to display old refunds issued grouped by channel or company. This reporting allows you to get an overview of the amounts refunded for a particular channel or company. You can run the report based on refund, order or ship date. You also have the Refunds Detail report which shows you all the individual refunds in detail.

Next, we have the Returns Details report. This provides you with details on all the different returns. Aside from the regular company and channel filters for this report, you can also filter by return type, which is either RMA, FBA return, or FBA reimbursement. We also have if you see on the side of Returns and Reimbursement summary and returns and Reimbursements Details Report which I will get into detail but the basic purpose of these two reports is to essentially facilitate the process of reconciling FBA refunds with returns and reimbursements. We have an extensive help page on this, which you can pay a very clear understanding of how to use these reports. And these reports will provide details unique to FBA returns, which is why they’re specifically for FBA returns.

Next section we have the Shipping report section which focuses on providing reports of shipping and order fulfillment. This can provide insight into operational performance, such as seeing the rate of orders shipped in relation to those placed, the timeliness of fulfillment in relation to the shipping promised date set on the order along with shipping details, such as shipping carrier costs, and tracking numbers. We have three dashboard reports you can use where you can see the track fulfillment in a more visual way. So here are some of the filters that you can use on a Shipping report. You see over here, there’s a carrier for which you can filter specific orders that are shipped by a carrier. You can search by different shippers in your company. Finally, you can search by fulfillment performance to see which orders are late, on time, or early. This is based on the shipping promised date that is set on the order.

Next, we have the Tracking report, which is similar to the shipping report but this report really breaks the order down into each of its packages and gives you the package information such as dimensions and tracking number. Insert any column in ascending or descending order by just clicking on the column that you see over here. See here we sorted by the heaviest orders going out in this time period. And then here we sorted by shipping costs to see the orders with the highest costs in this time period.

Next, we have the Inventory Report section. The reports of this section focus on the reporting quantities and values of the inventory you keep across warehouses, companies, and channels. There are a few ways to use these reports. You can check for inventory as of a certain date, we store inventory for the last 180 days, or you can choose to check inventory over a certain date range when you’re using the as of date option to view current data on the calendar pop-up you can click real-time and get the inventory values as of the time you are running the report. So again, this is one of those reports that are an exception to the rule with regard to our reporting system in that we offer real-time current data for your inventory values.

Now, here’s a report where you see Inventory By Warehouse Summary, it gives you an overview of the warehouse. And here is a lot more of a detailed view of inventory over a specific date range. Next, I will show you a new report that we just recently rolled out that we’re very excited about – the Inventory Aging report. The basic purpose of the Inventory Aging report is to provide an analysis of the inventory you have in stock by providing an estimated age of that inventory along with a few other data points. This estimated age is provided based on the receiving history for the particular items. The accounting principle used in making these calculations is referred to as FIFO, first in, first out, which means that when an item is sold the oldest inventory items in this case earliest purchased items are considered as having been sold first. And that is the basic guiding principle of the calculations that we make to give you an inventory age. You see over here, there is an average inventory age along with quantity age from 0 to 30, 30 to 60, 60 to 90, 90 to 120, and then quantity older than 120 days.

The next section we have is the Products By Component report. This next session was created as a solution to a challenge we faced in Sellercloud. In Sellercloud the option to sell items as a kit provides a unique challenge for product-based seller reporting and that when the sale is recorded, it is recorded on the SKU from the listing, which in the case of kit sales is the kit parent. This will skew your sales reporting for total units moved for a particular item that is a kit component, and you’ll not be able to get an accurate number using the regular sales report. To explain this further take these two products as an example. You see there are two listings here for the same item purchased in different amounts. On the left, over here you have an item you have labels that were originally purchased – a row of 240, a 12 pack row of 240 labels and that’s how the item was originally purchased. And that’s your component item, a 12 pack of 240 weight, and that you’re selling on the listing for $32. Then what you do is you bundle up this 12 pack into a 12 pack of that 12 Pack, which comes out to 144 packs of labels. Now it’s the same item, sold in a bundle of 12 12 packs, so if I sell one of each listing, that means I sold a total of 13 of this component because I have the one component item and 12, the 12 pack of the 12 pack, which comes out to 144 pack, a 12 of this item in one single sale, which comes out to 13 units in total. However, if you run the regular sales reports, it will show you only a quantity of two that were sold, one 12 pack, and one 144 pack. So that’s the use of these reports, which provide reporting on products sold in both forms to get an accurate sense of how many total units are sold. So if you run a product by components report that will show you the accurate quantity of 13 for the component.

Now the next section is where things get pretty complicated. This is the Transaction reports section. Just to quickly reiterate what we discussed earlier, transactions are essentially an umbrella term referring to an exchange of funds or assets that will reflect a change in the status of the finances of the seller. This is a concept that accountants must be busy with much to the detriment of their health, and mine because in order to get an accurate accounting of your business, you need to get a clearer picture of each distinct financial event and how they should be recorded in your books. So this section focuses on reporting in that context, mainly with accountants in mind as this is their focus. So on that note, I’m going to focus on the most unique report in the section that reflects this concept namely the Transaction Details By Date report. This report is most commonly used by accountants to audit all transactional data as it exists in Sellercloud so that they can make sure they accounted for each transaction correctly in their general ledger. Much of this is based on the classifications we use for each transaction, which in its simplified manner can be characterized as such – one, the higher level category of transaction type, which includes debits, credits, and non-order transactions. As you see over here, there is a column Transaction type and this will have debits credits and non-order transactions. This is then further broken down into movement types that reflect the nature of each transaction. So for example, in the debit, you have basically the original sale and then in the credits, you have the original sale which is reflected as the order movement type. The credits include in all the ensuing transactions such as refunds, returns, adjustments, and reimbursement and those will be specifically enumerated in this column called Movement type. And then finally, you have Non-order transactions, which in the transaction type will show as non-order and then in the movement type will specify the specific transaction type. So if it’s storage fees, it will say storage fees, if it is a product ad fee, it will say product ad. There’s also a Transaction Summary Report which summarizes this data by transaction type.

Next, we will discuss the settlement-related reports. This section provides you with the ability to reconcile P&L data with the payment reports provided by the channel. Well, you may know some of this already, I’d like to run through the process of reconciling settlements in Sellercloud really quickly. Now see on this slide is an example of an Amazon order in Delta in Sellercloud that was fully refunded and reconciled. The order was placed for this test SKU for a dollar. Now you see over here, the subtotal comes out $1 and the tax was for seven cents. Now let’s look at how this order was paid. The payment is three on this order. If you see the bottom record over here the order was first paid at 9:15 am on September 15 and then it was refunded at September 15 that same day at 12:03 pm. That’s how the payments were recorded in Sellercloud. Now here are the settlement details for this particular order. Now note, this is the settlement, all activity starting from 9.27. That payment report included all order activity and all channel activity for that date range. Now, look at the date breakdown that Amazon provides in their settlement. You have your principal, your tax, the marketplace facilitated tax, which we’ll not get into as it is way too complicated for this, and a commission which they deducted from the sale and paid out the difference. So what we do in Sellercloud is we reconcile the data that the channel has with ours to match up the events as detailed on the settlement with what exists in Selercloud. You see over here included in the settlement details, as provided by Amazon, there’s a column for Posted Date, which dates each transaction on that order. So the next slide we have here is a Settlement Reconciliation Report. What you’ll see over here is you’ll see the amounts as they were recorded in Sellercloud, and then the amounts as they came in on the settlement. And then if there was any transaction unreconciled from the settlement that would be displayed in the difference column. This is done by matching each distinct transaction and event. And you can see here that it’s all reconciled.

Now I just want to go back to this slide for a minute, if theoretically, just to throw a wrench into the mix and complicate this slightly, if theoretically, this refund over here on this top payment record has been processed by Amazon two days later than it was recorded in Sellercloud. So it was the same refund, it was refunded, but for whatever reason, it wasn’t communicated. That wasn’t actually processed on Amazon itself until September 17. Then in Sellercloud, when those settlement details came in, which you see over here, the refund lines for this, it has 9.15. If it came in showing as 9.17, you would not recognize that these are the same refunds that we have recorded in Sellercloud over here due to this date description. And this would be despite the fact that the amounts as reported by Amazon actually do match what we have in Sellercloud. So what we have done to bridge this gap in the regular settlement reconciliation reports, which does not recognize small discrepancies that don’t necessarily relate to amounts and therefore doesn’t match each because of the fact that we’re trying to match each transaction or event, we’ve created something called a Settlement Reconciliation By Order report. This report sums up all the amounts calculates the total profit of the order and what Amazon should be paying out to you for that order, and then sums up all the amounts that exist on all settlements for that order, and matches up those amounts. And if there is a difference in those amounts, you can then check but this is just to bridge the gap. In such a scenario, the settlement is just not getting reconciled in these regular settlement reconciliation reports based on the specific transactional events because of small discrepancies within those transactional events, but really the actual amounts as reported by Amazon, and as recorded on Sellercloud actually do add up and do reconcile. That is where you can use the Settlement Reconciliation By Order to check if that is the case. But I don’t suggest using this as a first choice for reconciling, because this will not allow you to look at specific settlements, or match based on a specific transaction, but rather it focuses on all the data relating to this order as a whole, to make sure the amounts on that order fully reconciled to what Amazon is reporting. It should only be used as a stop-gap when paired with the regular Settlement Reconciliation Report, which is a lot more comprehensive. And here you see the Settlement Reconciliation report where we add the status type. It just tells you whether it’s reconciled or not reconciled, filter for orders, with the amounts that match what we have in the settlements and the amounts that don’t match. And then you have date range and you can filter by specific orders, companies, or channels, but not by settlement, as this report sums up all data across all settings.

Finally, what we have is a Custom Report Section. We know that clients have unique workflows and you may need custom reports, we do offer those customizations and you will need to contact Support in order to go through the process of configuring those reports. So for example, a company wants Profit and Loss to not include shipping costs or the company wants Inventory to also affect Inventory Incoming on POs. Or you have specific custom columns that you need to use as a filter or you want it to just display on the report. That would be something that you would contact Support to help you customize a report that fits your needs. Thank you, everybody, for joining me today and I hope you learned something new.