How to export Shopify orders to a CSV file and what fields does a Shopify order CSV file contain

[Music] hi guys it's john from rfm calc and today i'm talking about shopify so shopify as i'm sure you know is one of the biggest e-commerce platforms in the world used by a lot of people and as you won't be surprised to know integrates with rfm calc no problem so today i'm going to talk about how to export orders as a csv file from shopify we're then going to take a deep dive into that csv file just to understand the structure uh in terms of how shopify exports orders and so the kind of order of the file the column headings and so on and then i'm going to take you through rfm calc in terms of importing that file back in which is really really easy and so to start with i've got my demo shop open here and you can see this is the home page dashboard and with all the different links down the side to manage your orders products customers and so on and so all we need to do to export our orders as a csv is go to orders here and then in the top right you'll see there's a little export link so if i click that that gives me this little overlay with a few options so i want to export all orders generally you can just do a date range if you want to and to import into rfm account really you want to use a plain csv file is what we'd recommend and then you just click export so what shopify will do now is do a little job in the background and so it's doing that as a so not alive sitting around waiting for the file and it'll generate that and then it'll send you an email when the file is ready the email looks a little bit like this so it just sends a simple email saying your orders are finished exporting and ready to download and then you can click that to get the file simple as that and that's native to shopify this isn't an add-on every shopify store has this export built in which is great so once you've got the file it will look a little bit like this so this is an example um shopify order export from a live store as you can see i've just obscured slightly the email and and phone numbers and things when we get there just to uh make sure no one's upset um the first thing to notice is that uh shopify lists orders newest to all this so different ecommerce platforms do this in in different ways sometimes some platforms magento for example will list lust excuse me list orders oldest to newest uh but shopify lists the newest to oldest so you can see in the first column here under name and we've got the order id we've then got the customer's email the status of the order if it's paid when it was paid the fulfillment status and when it was fulfilled and things and then various other columns here so you've got the breakdown of the order cost and then shipping method the created app which is when that line was created and the items on the order and you can see you've got quite a few more um extra fields here so we've got the name and an address of the for the billing and also the name and address of the shipping so even if they're the same it will repopulate them in the shipping fields even if they're the same as the billing and then you've got phone as well the payment method payment reference and a few other kind of random fields as well tax values and so on that we don't really need to worry about so i think the key things to note here is that shopify works on a multi-line per order format so if someone orders with shopify and let's take this example here order two six four five so this court customers ordered multiple items on their order so shopify has added these as individual lines on the csv but it hasn't repeated this key data on each line so if we scroll across you can see they've ordered various different perfumes here and but it's still the same order but it doesn't repeat the um billing information it doesn't repeat the delivery information there that only appears on the first order line but what ties them together is that the order id is the same on each line here so rfm calc can handle that no problem what it will basically do is combine all that into into one order uh line before it imports it and generates your reports and so one interesting thing to note here as well is that the created outline and date appears on every line so if you're using this for analysis and you want to know the order date this is probably the safest one to use and so you'll see it will typically be the same all the way so you could get some variation here but typically that'll be the same and it appears on every line so if you're looking for a date field to use and that's probably the safest one rather than uh paid out which only appears on the first line of the order um and that's kind of it really in terms of analyzing the field so there's plenty of data in there but i think the key things to remember are it's running newest to oldest and it's running with multiple lines per audit if that customer has ordered multiple items um but other than that it's a fairly standard um see order csv nothing complicated and nothing rfm can't handle very very easily so next what i'm going to do is just return to iphone calc and i'm going to create a little test shopify project and on my demo account just to demonstrate the uh different fields that are available here and the different settings you can have so in terms of the default project currency so rfm calc supports all um world currencies uh this particular site i've exported from is in uh gbp in uk pounds uh for shopify the default uh date interpretation is fine so sometimes different ecommerce platforms can export dates in different formats which can be difficult to interpret automatically you can force that here for european or us but the default absolutely fine for shopify as we mentioned um in shopify orders are listed newest to oldest but the auto detect is absolutely fine and then in terms of the columns we'll come back to that in a second but basically that's all you need for now to set up the test project so there we go we've got our project now and we can schedule our first report so what i'm going to do is export is upload the uh raw shopify file so not the excel version i just demonstrated with some fields blanked out the original file so that's uploaded now so if you remember if we refer back to the spreadsheet the order id column is name now for the order day column you can use paid app you could even use for fill that but i think as i mentioned the safest one to use for shopify is created at so we'll select that here where's it gone there it is and we don't need a currency code column and all the orders are in gbp so i'll ignore that for the order value column the best one really to use there is the total here and that's the order total that only appears on the order total line and that includes tax and shipping obviously if you want to exclude tax and shipping and from your rfm calc report you can use the subtotal but i'm going to use total which is there the order status column you don't have to specify that's this financial status or filament status column you can set here what this allows you to do is if you wanted to for example exclude refunded orders and we can do that automatically without having to manually take them out of the csv so i'll select financial status as the order status column and i will add refunded as the status to exclude so you can exclude multiple statuses you just need to comma separate them there now for customer id what we normally recommend um for any ecommerce platform unless you have an internal customer id that every customer has and you're sure that's going to be repeated the safest one really to use is email um as a customer identifier and basically that will say okay the first time my email is used that's a new customer any time that customer comes back with this email that's then treated email that's then treated as a repeat customer and then we've got a few other reports that we generate so we'll add the customer email column and we'll have the billing phone as the phone column which is over here so you can see it's part of the billing address we have a billing phone column so we'll add that as well if we just scroll down here and then the customer first name and last name columns so here we just have one name uh column so some e-commerce platforms have a first name and surname column uh separated shopify doesn't it just combines them into one name that's fine all you need to do is set that column for the first name and ignore the last name column if you forget that then it's a bit of detail here on the help just explaining that as well and then the customer company column again this isn't essential but if you want to include that you've got a billing company and shipping company and columns that you can use here as well so what this does is these are used in in certain parts of the report and just to give you a bit more information and give you a list of top customers and so on and if you want a sufficient account on rfm calc as well we'll also export a full list of your customers with all this data attached to them as well and their purchase data so it's useful for generating that and finally um on certain account plans and you can specify custom columns as well so what this means is um rfmcat will generate additional data and reports based on those custom columns so one you might want to use is payment method and so what this will then show you if i specify payment method as a custom column when the report is generated what that will do is it will generate reports to show which payment method customers used and you know the lifetime value of a customer using shopify payments versus paypal um the average orders per customer you know how long the customer takes to reorder based on which payment method they're using and so on so custom columns can be really powerful and you can of course because it's a csv you can add your own custom columns into the csv before you upload it uh but that's a nice easy one just to kind of demo the custom column functionality and that's it and we'll just give the report a little name like so and there is the option to anonymize customer data so if you don't want any real names and email addresses appearing on the report you can take the set this to yes and then the only other thing i'm going to do is just overwrite the default column mappings so that means all these column mappings that i've specified for this shopify project next time i add a report they'll automatically be selected to save me having to go through those drop downs and set them again and that's it so that's uh now cued that report will generate in a couple of minutes uh we'll get an email notification when it does and basically that's uh that's as simple as it is to explore orders from shopify and import them into rfm calc so really straightforward uh i'll cover in the next video i'll go through all the different reports that are generated and all the data you get from that but um that's it obviously if you've got any questions feel free to drop us a line thank you [Music]

Channel: RFM Calc

Views: 273

Keywords: Shopify, Ecommerce, RFM Calc, CSV

Id: RwLimm_roUE

Length: 11min 27sec (687 seconds)

Published: Sun Oct 24 2021