How to export WooCommerce orders to a CSV file and what fields are in a WooCommerce order CSV file

[Music] hi everyone it's john from rfm calc and today i'm talking about woocommerce so woocommerce is a free open source e-commerce plugin for wordpress wordpress being one of the most popular cms platforms in the world it was originally released in 2011 and since then it's become very popular and i think mainly because it's free but also because of the simplicity of setting up and managing it and it now powers many millions of e-commerce stores around the world generating hundreds and hundreds of millions of dollars in yearly revenue so it's actually a really good platform it's a great starter platform and it can extend as well and it does as i say power as you'll see on the woocommerce site here some pretty big brands as well so today i'm going to talk about how do we export orders from woocommerce so we can import them into rfm calc we're then going to take a deep dive into that export file and understand what columns are in there what data woocommerce exports and then we're going to talk about how to import that back into rfm calc to generate all the reports such as lifetime value average time between orders and so on and that you want to get for your woocommerce store so this is the dashboard from the test wordpress install i've set up so we're running the the very latest version of wordpress and i've already installed uh woocommerce so you can see woocommerce adds its own section here if i go home it's got its own little dashboard so this is the latest version of woocommerce as well at the time of uh recording very easy to install and set up and you can see this gives a little overview uh of total sales orders and so on and i've set up for the purposes of this some test products and but then i've also set up some test orders so i must just give a little shout out to this very useful plugin that i installed it's called order simulator for woocommerce by 7519 media and what's what that's done for me is over the past few weeks i've been generating some test orders automatically for the purposes of this video so as i go to my order screen you can see i've got all my test orders in here and with all the various details and so these are all pretend customer names so i'm quite safe showing you to them i can go into an order which will load shortly and you can see some test products as well and basic overview of the order here showing what they've ordered the address and so on customer email and phone and so forth so much like any ecommerce platform you'll have seen before really now as you probably know to get the orders from woocommerce into rfm calc we need to export them as a csv file and now this is where we get a little bit stuck because unfortunately woocommerce doesn't have a csv order exporter natively built in so unlike magento which does unlike shopify which does there isn't a way to export orders out of the box with woocommerce uh as a csv file but never fear there are plenty of modules out there uh including a great free one called advanced order export for woocommerce by algol plus and so this is in use you can see on over a hundred thousand woocommerce stores it's actually really simple to install as well so if i just go to plugins and add new i can search for it here and there it is so all i have to do is install it and there we go ready to go and activate as well and so that's now installed now depending on your wordpress and woocommerce setup you may have to manually install uh modules rather than being able to do it through the web admin but fortunately i'm able to do it directly here now if i go to woocommerce there's now a nice option in the menu called export orders so i'll go to that and this is the new export order module this one here advanced order export for woocommerce that i've just installed so this is free there are some paid options obviously if you want to upgrade and get more options but for what we need and this has everything we need to export a csv suitable for rfm calc so all we need to do is just set a few of these options and to be honest i tend to export the full order file anyway when i'm running reports on rfm calc so there's not a huge amount here that you need to customize and so we've got the filter orders by order date that's fine we don't need to specify a date range although you can if you want for the format we want to specify csv and now this gives you a few more options here and you definitely want to leave the column titles ticked as the first line so leave the option selected uh for the rest of the fields you can leave them as the default and order id descending that's fine as well and basically that's pretty much all the settings you need to add here so there's more filters you can add you can obviously filter by date range as i mentioned um but that's really the standard options for this module are absolutely perfect for rfm calc so that's all we need to do so i'm going to save those settings just to make sure they were successfully updated and now i'm going to export my csv so as you can see this is starting now um it's not the quickest export but what it's doing is doing it live so it's not doing it on a cron job it's pushing the file straight to me but it's doing it in a way to prevent the server timing out and also so it can handle quite large data sets and so i've not used this on a huge data set but my understanding is it can handle pretty large autofiles and without kind of freezing or timing out so that's great so i'll just pause the recording because obviously you can see it's fairly slow and i'll come back to it when it's a bit closer to finishing okay so it's taking a few minutes but we're nearly there now with the export 99 and there we go so that's the export complete um and it's just going to push that now as a csv file to me via the browser and there you go you can see it's generated the csv which we can save now okay so now we've got the csv file exported via this free module let's have a look what the data looks like so here we go here's an example file that i set up and using the same module recently and so i'm just going to go through first of all the overall kind of architecture of the file and then we're going to talk about the individual columns as well so the first thing to know is that the orders have exported newest to all this by default which is absolutely fine for rfm calc i think there is an option with that plugin to export the other way around if you want to and but either way it's fine for rfm calc so fine to leave that as a default and the second thing to note is that this woocommerce export works on a multi-line per order basis so you can see all these orders here have the same order number that ties them together but that's because this order has multiple products so each different product as you can see here is listed on a different line of the csv but what ties it all together is this order number and so that's no problem rfm cart can handle that as well without any issues and what you'll see is even though it's a multi-line order all the key information is repeated on every line so shopify for example just has that key order information on the top line and the product information or subsequent lines and tied together by the order number but this woocommerce export has all the information and pretty much everything so the address and the order total and so on the payment method all repeated on each line which kind of makes it a little bit easier to manage i suppose and so looking at the fields we've got the order number we've got the order status and the order date now because i've set my um store up in in the uk it's the uk date format and you've got first name and last name separated and which is useful so some ecommerce platforms will store or at least export the first name and last name in one column together but this woocommerce export separates them which is which is useful you've then got the billing address and you've got the email address for the customer which is very important we'll need that you've got the phone number and you've then got all those fields repeating for the shipping and delivery address and because obviously woocommerce allows you to deliver orders to a different address than the billing address we've then got things like the payment method and the order subtotal and things like how much that was paid for shipping uh if there's been any refunds and then you've got the order total as well so that's the order total is the subtotal link plus the shipping and you've then got the products as i detailed earlier and which have their own individual item costs and if you've used the discount code and the details of that will be included in this csv as well so it's pretty comprehensive it covers all the fields really that you should need and i think it's uh pretty straightforward though there's nothing complicated here and but it does have all the data that you need for rfm count to generate the report so that's great so once you've got your csv the next step is to import it into rfm calc so now i'm just going to detail how to do that if i just go to my rfm calc account here i'm going to create a new project so we'll call this test woocommerce and so the default project currency is set to pounds because i'm in the uk that's fine now for the order date because if you remember i said that this is very much in the european date format uh you can have situations where let me just see if i can find an example so if i go down to an earlier date uh probably not a great example actually in this file but you can imagine and because of the slashes you can have um whereas in europe it's day then month and you could have a scenario where it appears the same as a month and date and so what i'm going to do is to make sure that's not an issue is force european data interpretation and that will eliminate that being a problem when rfm calc comes to interpret that date column um as discussed the date the orders run in this file is newest to oldest but the auto detect is fine and and then that's pretty much it to set up the project is as simple as that so we've now got our test woocommerce project and we can schedule our first report so what i'm going to do is upload my file the raw file that i've recently downloaded so that's uploading now and what this is doing now is obviously uploading the file itself to rfm calc it's also doing some checks on the csv file to make sure it's a valid csv to make sure it's got valid columns and valid rows and so it's doing a little bit of validation at the same time and so that's gone through no problem and now we just have to map our columns so order id if you remember it's handily named order number in this export and then order date is the order date column the currency code uh there isn't one in this csv and that's fine because all the orders are in gbp so for the order value you've got a couple of options you can either use the uh order subtotal and which excludes shipping or the order total which includes shipping so i'm going to use the order total because i'm quite happy for that to be included and that's the total amount the customer has paid now the order status column isn't necessary um it's called order status in the csv you don't need this all this allows you to do is um filter out orders by particular status so if i go down here you can see most of them are completed you've got some processing as well so for example if i wanted not to include processing orders in the reports and only include those orders that are completed that you know therefore shipped that would normally represent then i can put that in there now for customer id there isn't an internal customer id that's exported with this csv file that's fine what we would normally recommend anyway is to use the customer's email address that's normally pretty safe and so if i go to where has it gone email address billing and that's a great customer identifier to use normally and so what that means is the first time that email address is used by a customer they'll be treated as a new customer in the rfm cart reporting and then every subsequent time that customer comes back and places an order with the same email they'll then be treated as a repeat customer and that's kind of the basis for for working out a lot of the data in the ifm cart reports and you can exclude certain customer ids in this case email addresses if you want to and then we've got a few more columns to map here so we've got the email address again the customer phone which is here first name and last name of separate columns in this export which rfm calc supports no problem and then also uh company which i where has it gone i did just see it and gone a second it's definitely in there somewhere isn't it i saw the company oh there it is after first name and last name so all these columns do these aren't essential for the rfm calc reports in in any way and but what this will do is in certain places in the report will generate lists of customers it will also produce a csv if you're on a sufficient account plan with customer data in and so all this data pulls through to that so you've got a nice list of customers there with their email their phone number and their company name so contact details that you can then use for various crm systems and so on and finally if you want sufficient uh sufficiently high account plan with rfm cal you can map custom columns as well and so probably the easiest one here is payment method um which is quite a common one if you've seen my other videos with different ecommerce platforms that's always a nice one to look at and so what this will do is this will generate additional reports in your rfm calc report and showing for these custom columns various data such as the lifetime value based on in this example payment method and the average time for a customer to place a second order based on the payment method the total spend and based on that payment method and things like that so it gives you kind of the options there to generate additional data for the report really easily and of course with it being a csv you can of course add more columns to the csv yourself manually and set up custom data before you upload it into iphone cart that's absolutely fine as well and then that's pretty much it so i'm just going to give this a little report name and you've got the option here to anonymize customer data so if you don't want real names and email addresses appearing in your report you can set that to yes and that will just filter those out it won't it won't affect any of the numeric data but it will just stop real names and emails being published in the report and then the last option is to overwrite the default column mappings for this project so what that means is all these column mappings that i've set up above and if i select this next time i schedule a report under this project i won't have to do the mapping again manually it'll just remember all those settings and it'll remember things as well like the status exclusion list as well and that'll be saved against the project too and that's pretty much it and so that report is now queued and basically that that will go in to the queue now with rfm calc in a few minutes i'll get a little email saying the report's been generated and that will generate the report for that data showing things like lifetime value average time between orders uh full rfm analysis as well and there's a full customer cohort analysis of some really cool charts and tables and data um that are generated off the back of that so that's uh yeah that's pretty much it so just to summarize we've spoken today about uh woocommerce how to export orders as a csv in woocommerce we've taken a deep dive into that csv file to understand the columns and the data that csv contains and then i've just taken you through as well how to create a woocommerce project on rfm calc and how to update that csv and back in upload i should say um and yeah that's pretty much it so in my next video i'll talk about all the different reports that you get out of this and we'll uh we'll go into that in more detail but for now um that's everything for this video so thanks for watching um any questions just drop us a line thank you goodbye [Music] you

Channel: RFM Calc

Views: 95

Keywords: ecommerce, reporting, woocommerce

Id: GFfSM9TI0Xg

Length: 17min 21sec (1041 seconds)

Published: Sun Nov 07 2021