Embedded Files
Summary
Summary
This solution allows you to carry out currency conversion across multiple markets. It combines Smart Connectors, Calculations, along with Data Lookups to apply daily exchange rates to campaign data.
This approach ensures consistency in reporting, simplifies dashboard presentation, and helps clients understand performance in their local currencies.
Please see below for benefits and implementation steps.
Pre-requisite Knowledge: Familiarity with Smart Connectors, Data Lookups, and Calculations is recommended. Refer to these guides as needed: FAQs: Smart Connectors, Smart Connector Setup Guide, How to Create Calculations, or FAQs: Calculations.
Solution Steps:
Solution Steps:
Step 1. Prepare Smart Connector Upload Sheet
Step 1. Prepare Smart Connector Upload Sheet
Follow these steps to set up the exchange rate sheets in an acceptable format for our Smart Connector add-on.
Make a copy of this Google sheet in your Google Drive. There are two tabs:
“Exchange formats" – Contains a list of all exchange formats.
“Smart Connector Upload” – This is the main tab to upload into Tap.
Open the “Smart Connector Upload” tab.
Cells B2 & B3 are start/end dates, changing these will change the data below.
You can upload historical exchange rates & future rates based on these date ranges.
Currently, there are enough rows to allow you to upload 5-years’ worth of daily data.
Note: You can add more rows but please note that this may cause the sheet to slow down.
Step 2. Smart Connector Setup
Step 2. Smart Connector Setup
Next, we will set up a smart connector using the sheet from the previous step.
Next, we will set up a smart connector using the sheet from the previous step.
Login to your TapClicks instance.
On the Data menu, click Smart Connectors
On the right-hand side select the + icon
Enter the Name Currency Exchange Rates (Daily)
Change Data View to Daily Exchange Rates
Origin of Data can be set to Internal System.
Change the data source Icon/Color to your preference.
Set the Delivery Type to Google Sheets and press Authenticate.
Use Google credentials which have access to the Google Drive where the Currency Conversion sheet is located and select Allow.
You will be returned to the TapClicks Smart Connector page.
Scroll down and under Google Sheets Settings select Load File.
Select the conversion sheet.
The Spreadsheet ID & Spreadsheet Name fields will auto-populate.
Change the Select a Sheet/Tab to Smart Connector Upload.
Select Load Sample Data and a table will appear under Data Fields.
Column Name: Refers to the columns directly from the upload sheet.
Field Name: Refers to the name that will show in TapClicks. You can change these if you wish.
Ensure the Date column is set to Text, this is a very important step.
Change all “exchange rate” fields to Decimal.
A new column should appear titled Operation, Change this to Average, no zeroes.
Set Date field to Data does not contain dates. This step will be made clear later on.
Change Assignment Field to Assign to a single client.
Ignore Margin/Mark-up
Ignore Currency Type
Ignore # of Footer rows to skip.
Add an email address under Notifications so any errors can be received.
Press Save Changes.
Once saved, at the top of the Smart Connector set-up screen, click Go to Manage Data Sources.
An account will appear for “All Data” that can be mapped to any client in your instance if you wish, the data will not affect the mapped client. Hit save once a client is selected.
The mapping screen will open, if nothing shows press Refresh List.
On the left-hand nav, click Data Sources
Click Fetch My Data. This will fetch the uploaded SC data.
Wait a few minutes to select the Data Source Overview on the left-hand nav.
Use CTRL+F/Command-F to find the uploaded Smart Connector and ensure the date range in the top right-hand side has a range applicable to the uploaded data.
If there is data, the upload was successful.
If there is none then go to Data Load Status on the left-hand nav.
Here the data source issue will show.
If the error is unclear feel free to reach out to support@tapclicks.com to investigate.
Step 3. Date Calculation Set-up
Summary: Setting up a calculation to enable Data Lookup to “join” each daily exchange rate to a specific date.
Data lookups do not work with Smart Connectors segmented by a “date” field.
This is why we upload the conversion data by Text. These steps show how to convert a “date” field for multiple data sources into a “text” field to allow the data lookup to “join” an exchange rate to each day.
On the Data menu, click Calculations.
In the top right corner click Add New Calculation (+ icon)
Enter the name using the following convention:
“Date To Text | {Data Source} | {Data View}
Example: “Date to Text | Google Ads | Campaign”\
You must create a calculation for each data source where a conversion is required.
Select Data Type as “Text”
Choose the relevant Data Category and Data View
Type the below Calculation into the builder:
NOTE: the green highlighted text refers to the format the date format will show as, this must match the format of the uploaded file. Providing there are no changes this should be “YYYY-MM-DD”
Leave Post Aggregation on “Auto”
Press Verify
If no errors appear then select Save
If multiple data sources need conversion, then.
Return to the calculation’s menu.
Search for the relevant calculation using the filter option
On the right-hand side select the “+” icon to copy the calculation
A new calculation will appear with “copy” at the end.
Click on this Calculation.
Change the Name, Data Category & Data View as required.
Select verify & save.
Repeat these steps as needed.
Step 4. Data Lookup Set-up
Summary: This section explains the Data Lookup set-up and why it is important to upload the Smart Connector with a “date” field as text.
Under Data, Click Data Lookups on the left-hand nav.
If you do not see this option, it may not be turned off in your instance, please contact your Customer Success Manager to enable this.
Select the “+” icon in the top right corner to add a new lookup.
Name - use a naming convention to help with organizing your lookups.
Daily Exchange Rates | {Data Source} | {Data view}
Example: Daily Exchange Rates | Google Ads | Campaign
Source Data View - select which Data Source you wish to import the exchange rates, then select the corresponding Data View
Join With - choose the “source” from which the exchange rate comes. This will be the Smart Connector set-up earlier. Ensure to select the Data View as well.
Join On - will use the calculated date field set-up in Step 3 and the Date text field directly from the Smart Connector
Expose Fields – these will be the exchange rate fields imported directly into the Data Source, add as many of these as needed.
Finally, tick Ignore Customer From Lookup Table.
Step 5. Widget Creation + Final Calculations
Summary: This final step shows how the Smart Connector & Data Lookup features work together to allow the end-user to convert their desired currency metrics as required. This section also highlights how the result can be viewed in a widget.
Go to a Dashboard
Add a “grid/table” widget using a data source/data view where the lookup has been set up.
Under Dimension select “date”
For Metric select “cost/spend” & an imported “exchange rate”
Each row of data should now show spend figures & exchange rates; this will work regardless of which client you select.
To convert the raw API cost from currency a few more calculations are needed.
Go to calculations.
Create new.
Enter the name as “Cost ({CURRENCY}) e.g. Cost (GBP)
Then enter the relevant calculation to convert the cost as desired.
If the API cost is USD and the desired currency is GBP, the below example can be followed:
Ensure Post Aggregation is set to “No”
Go back to the dashboard widget and add in the new converted cost metric, the cost should show as required.
Finally, required cost-related metrics (CPC, CPA, CPL, etc.) can also be converted as per the below:
If you require further assistance in setting this up, you can reach out to your Customer Success Manager/Technical Account Manager.
There is often more than one way to solve a problem in the Tapclicks platform, so it's important to consider how each one could impact the performance of your instance. Here are some key points to keep in mind:
Advanced calculations: While powerful, advanced calculations can slow performance with large datasets. Use simpler calculations when possible and reserve advanced ones for when necessary to maintain efficiency.
Limit CASE statements: Keep CASE statements to 10 branches or fewer. Larger ones can slow processing, so simplify or break them into smaller segments for better performance.
Data lookups for large conditions: For complex conditions, consider using data lookups from external sources like Google Sheets to simplify logic and improve performance.
Page updated
Report abuse