Big Query Error When Using CAST and determining decimals

I have linked a Big Query Project to my Google Ads Account. Within that, we have a campaignBasicStats table.

I want to pull the cost of a campaign from my Google Ads account into a big query workspace to apply some additional logic.

The cost column is coming through as an INTEGER and is described like this: INTEGER NULLABLE
The sum of your cost-per-click (CPC) and cost-per-thousand impressions (CPM) costs during this period. Values can be one of: a) a money amount in micros, b) “auto: x” or “auto” if this field is a bid and AdWords is automatically setting the bid via the chosen bidding strategy, or c) “–” if this field is a bid and no bid applies to the row.

If I query the table, the cost returns in this value: Example: 2590000.0 965145.0

In Google Ads, the two costs for these campaigns are £25.90 and £96.51

So I have this code in my Big Query Workspace.

SELECT CAST(Cost AS FLOAT64)
FROM `db_table`
WHERE COST > 0
LIMIT 1000

The column returns these numbers:

2590000.0
965145.0

However, As I need the numbers to be a currency for example the first return 2590000.0 should be 25.90 and the second one should be 96.51

I changed my code to this:

SELECT CAST(Cost AS FLOAT64(4,2))
FROM `db_table`
WHERE COST > 0
LIMIT 1000

And now I get this error:

FLOAT64 does not support type parameters at [1:28]

Is there something I’m missing? how do I convert to decimal point and specify where I want the decimal point to be in BQ?

Thanks,

Answer

It appears you are using a Google Ads Data Transfer operation as detailed here.

In this case, it’s important to note the Description of the Cost column in p_CampaignBasicStats:

The sum of your cost-per-click (CPC) and cost-per-thousand impressions (CPM) costs during this period. Values can be one of: a) a money amount in micros, b) “auto: x” or “auto” if this field is a bid and AdWords is automatically setting the bid via the chosen bidding strategy, or c) “–” if this field is a bid and no bid applies to the row.

1 micro is 1-millionth of the fundamental currency. Thus, we need to transform this amount as such: cost / 1000000

Then, we simply need to ROUND to get the appropriate unit. If you prefer to always round up, see my answer regarding the correct way to do that here.

First, we’ll set up an example table with the example values you’ve given:

CREATE TEMP TABLE ex_db_table ( Cost INTEGER );

INSERT INTO
  ex_db_table
VALUES
  ( 2590000 );

INSERT INTO
  ex_db_table
VALUES
  ( 965145 );

Then we’ll select the data in your preferred unit:

SELECT
  ROUND(Cost / 1000000, 2) as currency_cost
FROM
  ex_db_table;

Of note, your math in your question is incorrect here as the actual values of your Cost examples equate to 2.59 and 0.97.