Validating Optimisemedia Orders

Optimisemedia is an affiliate network platform.  Every month, we need to check the orders that have been attributed to their affiliates and check

1. Which store it came from
2. If the order was subsequently refunded or cancelled (we don’t want to have to pay for these orders)
3. If their order amount matches ours

\
\
Here is a copy of the instructions from OptimiseMedia.  They send this reminder on a monthly basis when the validations are due:

{% hint style="info" %}
At the time of writing, their validation report only shows an Order Number without indicating which store the order came from.
{% endhint %}



#

How to download the report:

Log into [https://dashboard.optimisemedia.com/](https://dashboard.optimisemedia.com/)

{% hint style="info" %}
If you do not have a login, please email our account manager: saveena.chiu@optimisemedia.com
{% endhint %}

Go to Admin -> File Explorer -> Validation Exports

Find the latest file and download the report

Open the downloaded csv file and find the earliest “Conversion Date”

Then:

Go to the BBC HK website and export all orders from this date
Go to the BBC SG website and export all orders from this date

{% hint style="info" %}
To export orders from Shopify, go to “Orders” then click “Export” in top right hand corner of page.  
{% endhint %}

You can then select how you want to export the orders

You can then paste these into separate sheets of the validation report file and and use vlookup functions to see which country the order came from and to check the total amounts

##

NOTE

ignore any discrepancies where our total is greater than the Optimisemedia conversion value
The Optimisemedia conversion value is the total value of the order before shipping fees (column “subtotal” in the order export)
The SG conversion values are provided by Optimisemedia in HKD, so we need to convert these to HKD (it is ok to allow a buffer for any exchange rate differences)

Please download the example template here and copy columns I to T of you want to use the vlookup functions

{% embed url="https://docs.google.com/spreadsheets/d/1YXYGuEjCcMlAYaGWuf7E7DkVPyyU9tpA/edit?usp=sharing&ouid=108686370854683107410&rtpof=true&sd=true" %}

Please make sure you understand what these are doing so you are able to do a sanity check that you are getting the right information

Once done, please

1. Save your version of the validation report (i.e with the shopify data and the functions) to this folder: [https://drive.google.com/drive/folders/13PD5C6kNGCVLYx8TEoLRAy3fBVjyqRCW?usp=sharing](https://drive.google.com/drive/folders/13PD5C6kNGCVLYx8TEoLRAy3fBVjyqRCW?usp=sharing)
2. Copy and paste the values from columns A to J into a new excel and send these back to optimisemedia (alerting@optimisemedia.com;saveena@optimisemedia.com;paras.dhiman@optimisemedia.com;)
3. Open the following spreadsheet: [https://docs.google.com/spreadsheets/d/1B93Fml\9RVl4DyJYApWACfbWC5UzYAtupLkDRW6XqFM/edit?usp=sharing](https://docs.google.com/spreadsheets/d/1B93Fml\9RVl4DyJYApWACfbWC5UzYAtupLkDRW6XqFM/edit#gid=0)



Find the “Optimisemedia” sheet.  Add in the current date and the percentage allocation to each country from field T2 and T3 of your validation report (this is so finance knows how much to charge each country for the payments to Optimisemedia)

\