How do you create a Fulfillment (Shipping) Report?

[ADML]

So, we used Square for the first time at a trade show and it worked great! We were able to process transactions quickly and the customer experience was positive.  The show was a huge success and we now have over 100 orders to fulfil.  How do we create a Square fulfilment report that we can download into csv format so that we can create shipping labels and get the orders out the door?  We would need the report to contain basic fulfilment information including order date, items ordered, amount paid, customer name, shipping address, order notes, etc.  Thanks for the help!

[TheRealChipA]

Hi @ADML.  What POS did you use for your transactions?  Also, how did you collect the customer name, shipping address, order notes, etc?  I can probably point you to the CSVs you need to download but I’m missing those key pieces of information.  Thanks!

[ADML]

Thanks for the quick reply @TheRealChipA

We used the Square contactless and chip reader (little flat white box). At time of sale, we handed the tablet over to the customer and had them enter their name, email, phone and shipping address. They would then hand the tablet back to us and we would complete the sale and have them tap or insert their card. 

[ADML]

To be even more specific:

1. We selected the item(s) that the customer wanted to purchase

2. We verified the order with the customer by going to the Cart

3. We tapped “Add Customer” in the Cart and handed the tablet to the customer to enter their name and address info

4. After saving the customer info, we tapped “Charge $” and got the customer to tap or insert the card to complete the payment. 

[TheRealChipA]

Ok.  So there are a couple of CSV files you need to download:

1. Transactions CSV
2. Item Details CSV
3. Customers CSV

You can then link these using a tool like Microsoft PowerQuery for Excel.  The Transactions CSV has a Transaction ID to link to the Item Details CSV, as well as a Customer ID to link to the Customers CSV.  All of the data you need is in these three CSVs.

Let me know if you need anything specific to help create that report in Excel.

[ADML]

Hi @TheRealChipA 

Thanks again for the reply.  We have already downloaded these 3 files but we've never used MS PowerQuery for Excel.  Are you saying that Square does not have a simple Fulfillment Report?  Why do they make this so complicated?  If Square had only been in business for a year or two I might understand, but Square has been around a long time.  I don't understand how Square doesn't have a simple Fulfilment Report - it would be so easy to provide and I'm sure needed by thousands of merchants.  I don't get it.  It's almost like they want to make taking payments easy but fulfilling orders intentionally difficult. Makes no sense (at least to me).

We have never used MS PowerQuery so any tips you can provide would be greatly appreciated!

[TheRealChipA]

@ADML A couple of things here.

There is an order manager for the Online Store, but I didn’t get the impression you were using that.  

I should have mentioned that there is an ongoing beta program for a new Shipping Manager.  That beta is located here.  You should ask to join.  It sounds like what you are looking for, and it is being tested now.

Also, there is a Square App Marketplace with third-party apps that integrate with Square.  You can find it here.  Search for “shipping” and so forth.

I can’t answer questions about why Square does, or doesn’t, have a particular feature.  I am just a seller like you, one that is trying to help my fellow sellers overcome whatever obstacles they face with the tools we have on hand now.

If none of those options work for you, I’ll be happy to help you set up a PowerQuery.  I would need you to look at all 3 CSV files and make a list of the columns that you want from each of them, to start.  Once I get that, I can private message you so that we can trade email addresses (please don’t put your email address here on this public board) and work together that way.

[ADML]

Wow @TheRealChipA you are a wealth of information and so helpful!! Thank you!

Travelling home today from the show location but I will reply again later tonight or tomorrow.  Thank you again!

[Candlestore]

@ADML ;

TheRealChipA made a PowerQuery for me to link my csv files together for another type of linking I needed between my csv files.  He is very helpful and knowledgeable.

[ADML]

@Candlestore you are so right!  TheRealChipA came through for us.  He made a PowerQuery that linked multiple Square csv files together so that we now have a single spreadsheet that contains the data that we need to efficiently fulfill all of our orders from the show.  "He is very helpful and knowledgeable"  I couldn't agree more!  Thank you @TheRealChipA!

[ADML]

Hi @TheRealChipA 

So, here are the data fields that we are looking to combine into a single report if possible:

Customer csv

First Name, Last Name, Email Address, Phone Number, Street Address 1, Street Address 2, City, State, Postal Code, Memo, Square Customer ID

Items csv

Date, Time, Item, Qty, Price Point, Name, SKU, Modifiers Applied, Notes, Details, Customer ID, Customer Name, Customer Reference ID, Unit, Count, Itemization Type, Fulfillment Note

Transactions.csv

Date, Time, Gross Sales, Discounts, Service Charges, Net Sales, Refunds, Total Collected, Source, Card, Card Entry Methods, Cash, Fees, Net Total, Transaction ID, Payment ID, Card Brand, PAN Suffix, Details Description, Customer ID, Customer Name, Customer Reference ID, Discount Name, Order Reference ID, Fulfillment Note

Is this what you were looking for from me?

Thanks,

David

[TheRealChipA · Accepted solution]

David,

It is what I need to get started.  I’m going to send you a private message now here in the Community, along with my email address.  When you get it, please send me an email so that I have yours.  At some point, I’ll want to get either your CSVs from the event, or at least a subset to be sure everything is working correctly with actual data.  We’ll coordinate that later, if you don’t want to include those in your initial email.

Disclaimer.  This won’t be a gorgeous, highly formatted report by any means.  But it will get the job done, and get it done in as short a time as possible.  Also, I am getting ready to head out for a night with friends and won’t be home until late.  I’ll get started on this tomorrow and let you know when I’m ready to have you try it out.

[ADML]

Thank you @TheRealChipA!!  You have been so helpful and so generous with your time!