Transaction Data Implementation

There is an option to connect your backend order system with Roivenue and evaluate your marketing activities on the margin level and clean of all canceled orders and returns.

1. Why to Connect Transaction Feed With Backend Orders Data

  1. Profit and Margin included in Roivenue - Google Analytics provides only the information about revenue. But the profit levels can be vastly different for each SKU. That is why it is possible to include profit in the export and analyse effectivity of campaigns on profit, which is what matters most at the end of the day.

  2. Cancelations and Returned Orders - Between 5 % and 50 % of online orders is returned or canceled which creates a big gap between conversions reported in Google Analytics and final revenue from these orders. By connecting the data to Roivenue, you can see the truth and the real revenue and profit from orders which were actually paid and delivered.

  3. Visibility to all conversions - not all conversions are measured by web analytics due to cookie consents, tracking blockers and similar. It can cause web analytics to report up to 50% lower results, compared to real number of conversions. While it is not possible to calculate attribution for orders which are not in web analytics, we will show you how many of these orders you have. You will be able to analyse if the quality of your measurement is getting better or worse and what proportion of your traffic is not measured.

  4. Customer Lifetime Value Analysis - Roivenue combines data from your system with the data from ad platforms and web analytics. It means that you can evaluate which channels works best for acquisition and which are the retention drivers. Also some channels may bring customers with much higher CLV. This analysis is typically conducted using Roivenue data in Power BI or similar BI platform.

2. How it Works in Roivenue

This is the list of changes you will notice in Roivenue after implementing the transaction feed.

  1. Transaction feed will become the source of truth - The number of conversions, delivered orders, the total revenue and profit values will be taken from the transaction feed instead of Google Analytics. This is important to remember as the total numbers will now be comparable with your CRM but will not be 1:1 comparable with your web analytics

  2. Revenue and profit will be calculated only from delivered orders - There is an option to specify the status of each order during the import to Roivenue. Roivenue will take only orders marked as "delivered" and show revenue and profit from them. Until the order does not have the "delivered" status, revenue and profit will not be displayed!

  3. New dimension value called "Not Tracked" will appear - These are all the orders which were missing in your web analytics. So do not worry, not tracked will always be there and it is a good guidance on how much traffic/conversions is not measured due to cookie consents and other challenges.

  4. Daily updates of orders - you will be uploading the file daily and multiple weeks of data will be always included so we can update the status of orders - returns, cancellations, etc.

Always upload all transactions in the given period. Roivenue will always automatically process the newest file. All the orders data which were already in Roivenue for that period will be deleted first and the newest file is always considered a source of truth.

3. Creating The Transaction Feed

Note: There needs to be a separate XML file for each site in Roivenue. If not sure what are all the sites in Roivenue, contact your account manager who will help you clarify this.

3.1 Naming The File

Correct naming is crucial for successful integration with Roivenue. If the naming will be incorrect, it will not be possible to connect the data.

The created XML files have to be named with the following syntax:

oms-extract-orders_{code}_{start-date}-{end-date}.xml

  • Values inside of {} will be replaced. The file name has to be entirely lower case letters including the XML suffix.

  • code = business unit code (ask your account manager about your business unit code). For a website example.com the code will usually be "examplecom"

  • start-date = This needs to be the first day for which data is included in the file and needs to be in a format yyyyMMdd.

  • end-date = This needs to be the last day for which data is included in the file and needs to be in a format yyyyMMdd.

3.2 Content of The File

The goal here is to define what will be all the information shared with Roivenue.

The file should include all the orders created on a given site. You can differentiate between shipped, cancelled, or returned orders using one of the order parameters.

There are two types of parameters for each order.

  • Required - these need to be sent for all orders.

  • Optional - You can use these parameters if you want to work with them further in your analysis, but they are unnecessary for Roivenue to work correctly.

In case the required parameters are missing in the file, data will not be loaded to Roivenue

See the list and description of parameters below:

Name

Description and examples

Site

The web domain where the order was generated. This is typically the site for which the feed is created.

Example value: example.cz

WebId

The unique ID of the online order captured by the web tracking software, for instance when Google Analytics is used, this is the transaction ID. ID must be unique.

Example value: 15108766

OrderId

Internal unique order number. Can be the same as WebId. During all editing in the system this ID has to stay the same.

Example Value: 12001508xxx

UserId

User ID connected to the order. Must be hashed.If it is not possible to assign an ID to a user (e.g. the system does not create users for orders without a login) their hashed e-mail address is used. UserId will then be the same as userEmailAddress.

Example Value: e99f33420f577ee8

Status

This identifies the status of the order, following statuses are allowed:

β€’ Created - The status when the order is initialized (put in the cart)

β€’ Ordered - Accepted/created order

β€’ Canceled - Cancellation (test or duplicate order)

β€’ Shipped - Forwarded to dispatch

β€’ Delivered - Delivered / Paid-in order

β€’ Returned - Returned order

β€’ Undelivered - order was not delivered or picked up

Delivered

Status whether money was received. Only revenue from orders with delivered = 1 will be shown in Roivenue!

1 = YES,

0 = NO.

Example Value: 1|0

OrderedAt

The date when the order was completed. The date when the order was accepted into the ERP system. This should be the time when WebID was send to Google Analytics.

Example Value: 2015-04-25T09:00:00

CurrencyCode

ISO 4217 currency name, the whole order must be converted into a single currency.

Example Value: CZK|EUR|PLN

Profit

This should be the gross profit made on the order. It is typically calculated as revenue - cogs (cost of goods sold)

Example Value: 235

Revenue

Total revenue for sold goods. Value excludes VAT and other delivery, payment or shipping costs.

Example Value: 928.05

3.3 Important Things To Decide or Check

  1. When to consider order as "delivered" - Until you mark order as delivered, the revenue from this order will not be visible in Roivenue. It is important to decide when do you want to recognize the revenue.

    1. It might be good for marketers to mark orders as delivered immediately to see the ROI of campaigns as soon as possible. These orders, when cancelled can be changed, and revenue will disappear from Roivenue

    2. From a financial point of view, it makes sense to mark the order as delivered once it is paid.

    3. Ultimately, it is important to discuss this with all the stakeholders and choose an approach that aligns with your business decision-making process.

  2. What to do with partial returns - Sometimes, customers order multiple products, choose the ones they want and then return the others. You can include this information in the transaction feed in two ways.

    1. The simpler option is to keep the order status as "delivered" and adjust only the revenue and profit for this order. This option is easy to implement, but a disadvantage is that it is impossible to recognize which orders have been partially returned.

    2. The more advanced option is to send a new " PartialReturn" status for these orders, keep the element "Delivered" = 1 and adjust the revenue and profit accordingly. This will allow us to analyse how many orders are being partially returned.

  3. Sending date and time information in the correct time zoneβ€”The feed data is connected with data from web analytics, so the time zone needs to be set the same in both destinations. If you are not sure which time zone to use, contact your account manager for help.

  4. How to include orders from other sales channelsβ€”It is possible to include not only online orders but also orders from brick-and-mortar stores, orders through phone, and similar. Typically, the optional parameter "SalesChannel" is used to include these, but we recommend discussing this custom implementation with us in advance. We will help you choose the right way to share the data.

3.4 XML File Structure

This is an example how the XML structure should look like. On the first page, there is just the example but you can also switch to the second tab where there is a description for each of the parameters. The example shows a feed with exactly one order

<?xml version="1.0" encoding="utf-8"?>
<Orders>
    <Order>
        <Site>example.cz</Site>
        <OrderId>12001508xxx</OrderId>
        <UserId>e99f33420f577ee8</UserId> 
        <Status>Ordered</Status>
        <Delivered>1|0</Delivered>
        <OrderedAt>2015-04-25T09:00:00</OrderedAt> 
        <CurrencyCode>CZK|EUR|PLN</CurrencyCode> 
        <Profit>235</Profit>
        <Revenue>928.05</Revenue> 
        <DeliveryCost>80</DeliveryCost> 
        <PaymentCost>15</PaymentCost> 
        <ProcessingCost>0</ProcessingCost> 
        <ProductsCost>235</ProductsCost> 
        <Total>1123</Total> 
        <Tax>194.95</Tax>
        <Discount>30</Discount> 
        <Delivery>0</Delivery> 
        <Surcharge>0</Surcharge> 
        <ProductsRevenue>980</ProductsRevenue> 
        <SalesChannel>online|offline</SalesChannel> 
        <PaymentType>CashOnDelivery</PaymentType> 
        <DeliveryType>PPL</DeliveryType>
        <ProcessingType>no-login | login</ProcessingType> 
        <ProductsQuantity>1</ProductsQuantity> 
        <ProductsDelivered>0</ProductsDelivered> 
        <CreatedAt>2015-04-25T09:00:00</CreatedAt>
        <ModifiedAt>2015-04-25T11:11:00</ModifiedAt>
        <ShippedAt>2015-04-26T13:00:00</ShippedAt> 
        <DeliveredAt>2015-04-27T10:00:00</DeliveredAt> 
        <ReturnedAt>2015-05-02T10:00:00</ReturnedAt>
        <SourceSystem>SAP ERP6.142</SourceSystem> 
        <UserEmailHash>e99f33420f577ee8</UserEmailHash>
        <UserPhoneHash>s11f12420f897cc8</UserPhoneHash>
        <UserSegment>customer.c15</UserSegment>
        <UserGender>m | f</UserGender> 
        <UserBirthAt>1988-12-11</UserBirthAt>
        <UserCountryCode>CZ|SK</UserCountryCode>
        <UserCity>Praha 2</UserCity>
        <UserPostalCode>12200</UserPostalCode>
        <UserCreatedAt>2015-02-25T12:17:00<UserCreatedAt> 
    </Order>
</Orders>

4. Validation of the Data in The Transaction Feed

To make the feed preparation easier, we have a Feed Validator available here.

It is a simple website that will guide you through validation of the following aspects of the feed:

  1. Check if the naming is correct

  2. Validate the structure of the file

  3. Check if the data in the feed actually makes sense

  4. You can also validate if you are uploading the file correctly to Azure storage (as described in step 5) but you can upload the file directly to Feed Validator from your computer.

If you run into any error during the validation process, you will be given information what went wrong. If you are struggling with the solution, let us know and we will help you find the solution.

Please do not go to the next step until you complete the validation through Feed Validator.

5. Upload of The File to Our Azure Storage

5.1 First Upload of the Data

Once you successfully go through all the steps in the Feed Validator, it is the right time to do the first upload of historical data.

  • The first upload should be for the entire period which you have agreed with your account manager to be uploaded. Typically, it will be from the date from which you have the rest of your data in Roivenue.

  • You can upload all of that data in one big file, but if you prefer, you can split it into multiple files; just make sure there will be no gaps in the dates.

5.2 Daily Uploads

Once the initial load of historical data is complete, you need to set up an automatic daily export, which will ensure that the data is always up to date in Roivenue.

  • The data should be sent to Roivenue after midnight, no later than 2 AM.

  • The data range should always end with the previous day.

  • The default number of weeks to upload every day is 13 weeks. This is to account for returns in that period, but if you need to make this window shorter or longer, contact your account manager, and it can be adjusted.

  • Example: on 1.1. the file should be sent before 2 am with data containing a date range from 2.10. to 31.12. and the name will be oms-extract-orders_examplecom_20231002-20231231.xml

5.3 How to do The API Call

To upload an XML file to the Azure Blob file storage, please follow the following steps:

  1. Make an HTTP POST on this URL:

    1. "https://api2.roivenue.com/api/blob-storage/upload?key=somekeyfromaccountmanager&fileName=oms-extract-orders_examplecz_20210101-20210131.xml&storageAccountName=roiimportexample&path=export"

  2. The XML file has to be uploaded in the body of the POST.

  3. The fileName parameter in the URL contains dates. Eg. fileName=oms-extract-orders_examplecz_20210101-20210131.xml&storageAccountName=roiimportexample&path=export

    1. These dates are a record of all orders created during this period. In this example, it would be an XML file with all orders created from 1.1.2021 till 31.1.2021.

    2. According to the range of orders exported from your backend system, these dates must always change with every file uploaded to the storage.

  4. In the case that you will get a response = 200 (Success) but the file is not uploaded, then most likely:

    1. the file was not put into the request correctly

    2. the header did not contain 'content-type': 'application/octet-stream'

  5. You should expect one of the following responses:

    <Response [201]> {"content":"","contentType":"text/plain"}
    <Response [200]> Success

  6. Lastly, you can find here a request example in Python you can use to guide yourself with:

import requests
from datetime import date, timedelta

key = "Storage key, ask your CSM to provide you with correct one"
storage = "roiimportexample"
fileName = "oms-extract-orders_examplecz_"+str(date.today()-timedelta(days=91))+"-"+str(date.today()-timedelta(days=1))+".xml"
url = "https://api2.roivenue.com/api/blob-storage/upload?key={}&fileName={}&storageAccountName={}&path=export".format(key,fileName,storage)

headers = {
    'accept': '*/*',
    'content-type': 'application/octet-stream',
}
with open('XMLFEED.xml', 'rb') as f:
    response = requests.post(url=url,headers=headers,data=f)
print(response)
print(response.text)

Some of the information from the HTTP Post needs to be provided by an account manager. If you do not have them yet, let us know. You need the following:

Account Key - e.g. "P3N2PTIwMjAtMDQtMDgmc3Q9MjAzgubhnijmkiZzcj1jJnNwPWFjd2wmc2lnPVlIMzNQRElQNTYlMkJGMTgxTUhkJTJGQjRMaGhscjNSY0RZZnJUNzBWdGQ5T2xRJTNE"

Account Name - e.g. "roiimportexample"

6. Validation of the Connection to Roivenue

Congratulations, if you finished all the previous steps, you should now have your backend orders data connected to Roivenue.

  1. Check the total value of conversions, revenue and profit in Roivenue with your order management system to validate that everything was successful. In ideal case, the two systems should be matching

  2. Check the "not tracked" if it looks reasonable. Not tracked orders are all the orders from the transaction feed which are not available in web analytics so they cannot be connected with a customer journey.

    1. Check the not tracked by going to dimension breakdown -> platform name -> Conversions

    2. Check what is the share of not tracked, anything below 15% is good and even values around 30 % can be seen for clients which have higher proportion of unconsented users on their website.

  3. Check if "not tracked" can be reduced - sometimes, some issue can occur and even conversions which are in GA will fall into not tracked category. It can happen for example in cases when time zone is not set up correctly and part of orders from a day 1 are included in the feed for day 2. How to check this:

    1. Go to Process Explorer

    2. In the Filter Panel, select

If you want to go forward with this and implement the transaction feed, please reach out to the Customer Success team and they will create your Azure Blob Storage account in order to start processing your files from the internal management system.

PreviousRoivenue Help CenterNextUTM Parameters

Last updated