Convert Currency Values in Floodlight Reports to USD or Any Other Currency With Launchpad and Google BigQuery

by Paul Cote on Jan 24, 2023

The Floodlight pixel in Google Campaign Manager has a lot of great features, but it does not have the ability to convert currencies. If you've dealt with this issue, then you know that a revenue value that simply adds up different currencies without converting them properly is a useless metric. There are several solutions out there, but they almost always include a generalized or averaged exchange rate that is rarely updated (if ever).

Our solution to this problem uses the Exchange Rates and Google Campaign Manager sources in Launchpad to transfer daily currency exchange rates and Floodlight report data to Google BigQuery. We then use a basic query to summarize both datasets and apply the closing exchange rate for the corresponding day of the transaction value. The end result is data that has normalized currency and date-specific accuracy, ensuring your digital marketing reports can fuel the best possible insights.

Step One

Configure a Daily Exchange Rates transfer in Launchpad and load historic rates in BigQuery. Once you have backfilled all of the historic dates, you should configure your job in Launchpad to load the previous day's data before any other jobs run (we typically recommend running this job not long after midnight UTC).

Example Exchange Rates Schema:

Historical Rates Endpoint (report)


  • Date (included automatically)
  • Base Rate
  • Rate Code
  • Rate Value

Step Two

Configure a Google Campaign Manager transfer in Launchpad and send the transactional data from the Floodlight endpoint to Google BigQuery. Floodlight reports can be backfilled up to 60 days (“{today} - 60 days”). After you have completed your initial backfill, you'll want to configure Launchpad to update the table daily and refresh to the maximum attribution window you have set in GCM.

Example Google Campaign Manager Schema:

Floodlight Endpoint (report)


  • Date
  • Activity
  • Campaign
  • Currency
  • Transactions
  • Revenue

Step Three

Copy and paste the query below into the query editor in your project.

  • Update the table references in the “FROM” clauses to reflect the dataset and table names in your project.
  • The sample query will convert currencies in THB, EUR, AUD and CAD. Update the currency codes to reflect the currency cases in your dataset.
  • Run your query and check your data.
  • Save as a view or table and connect your visualization app.

    WITH currency_pivot AS (
          etl_date as date,
        FROM   `your-project-id.your-dataset-name.your-exchange-rates-table-name_2023*`
        MAX (rate) AS  rate
        FOR  code in  ('THB', 'EUR', 'AUD', 'CAD') /* add the currency codes you'd like to include in your pivot*/
      etl_date AS date , 
      SUM ((CASE  
        WHEN conv.Currency = 'THB' THEN  Revenue_Before_Tax * (1 / currency.rate_THB)
        WHEN conv.Currency = 'EUR' THEN  Revenue_Before_Tax * (1 /currency.rate_EUR)
        WHEN conv.Currency = 'AUD' THEN  Revenue_Before_Tax * (1 /currency.rate_AUD)
        WHEN conv.Currency = 'CAD' THEN  Revenue_Before_Tax * (1 /currency.rate_CAD)        
        ELSE Revenue_Before_Tax        
      END)) AS revenue,
      COUNT(DISTINCT Transaction_ID) AS bookings
    FROM `your-project-id.your-dataset-name.your-google-campaign-manager-table-name` AS conv
    LEFT JOIN (    
      FROM `currency_pivot` /* select currencies that need to be converted in the report data */
    ) AS currency
    ON DATE( =
    WHERE etl_date BETWEEN DATE(2023,01,01) AND CURRENT_DATE() - 1

Need Help?

If you need any assistance automating your data transfers and reporting, then you've come to the right place. The experts at Calibrate Analytics can help set up your analytics stack so that you never miss the best insights.

Book A Demo

Share this post:
  • Paul Cote

    About the Author

    Paul is head of analytical products at Calibrate Analytics. He is responsible for creating digital analytical solutions that enable better business decisions. He has over 19 years of digital focused leadership, along with vast experience in analytics solutions aiming to deliver the right insights.