suse_google_adlevel_final_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_google_adlevel_final_v2Type: Final | Rows: 213,175 | Size: 94.1 MB

Google Ads ad-level final data (V2). Contains the complete historical record of Google Ads performance, refreshed daily from staging via a rolling 60-day DELETE+INSERT process. This is the table Looker Studio dashboards query.

Schema

Column	Type	Nullable	Description
Date	DATE	No	Date of ad performance
CampaignID	STRING	Yes	Google Ads campaign ID
AdgroupID	STRING	Yes	Google Ads ad group ID
AdID	STRING	Yes	Google Ads ad ID
Campaign	STRING	Yes	Campaign name
Adgroup	STRING	Yes	Ad group name
Adname	STRING	Yes	Ad name
Advertisingchanneltype	STRING	Yes	Channel type (Search, Display, Video, etc.)
CreativeFinalURL	STRING	Yes	Final destination URL of the ad
Impressions	INTEGER	Yes	Number of impressions
Clicks	INTEGER	Yes	Number of clicks
CTR	FLOAT	Yes	Click-through rate
CPC	FLOAT	Yes	Cost per click (USD)
CPM	FLOAT	Yes	Cost per thousand impressions (USD)
CostUSD	FLOAT	Yes	Total cost in USD
Conversions	FLOAT	Yes	Number of conversions (fractional)
Viewthroughconversions	INTEGER	Yes	View-through conversions
AllconversionratePercent	FLOAT	Yes	All-conversion rate as a percentage
Watch25Percentrate	FLOAT	Yes	Video watched to 25% rate
Watch50Percentrate	FLOAT	Yes	Video watched to 50% rate
Watch75Percentrate	FLOAT	Yes	Video watched to 75% rate
Watch100Percentrate	FLOAT	Yes	Video watched to 100% rate
VideoViewsrate	FLOAT	Yes	Video views rate
_ingested_at	TIMESTAMP	Yes	Timestamp when the row was inserted by the refresh script

Partitioning & Clustering

• Partitioned by: Date (DAY)
• Partitions: 458
• Clustered by: Campaign, Adgroup

Clustering on Campaign and Adgroup optimizes Looker Studio queries that typically filter by campaign name.

Refresh Logic

The scheduled refresh script performs the following for Google:

1. Read all rows from suse_google_adlevel_staging_v2
2. Identify the MIN(Date) and MAX(Date) range in staging
3. DELETE rows in this final table matching that date range AND the same CampaignID, AdgroupID, AdID combinations
4. INSERT all staging rows with _ingested_at = CURRENT_TIMESTAMP()
5. Log the operation to refresh_log_v2

Primary keys used for refresh: Date, CampaignID, AdgroupID, AdID

Sample Query

SELECT
  Date,
  Campaign,
  Advertisingchanneltype,
  SUM(Impressions) AS total_impressions,
  SUM(Clicks) AS total_clicks,
  SUM(CostUSD) AS total_cost,
  SAFE_DIVIDE(SUM(Clicks), SUM(Impressions)) AS calc_ctr
FROM `paidteam-data-warehouse.customer_ads_suse.suse_google_adlevel_final_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY Date, Campaign, Advertisingchanneltype
ORDER BY Date DESC
LIMIT 100;

Notes

• VideoViewsrate is FLOAT (FLOAT64) to correctly store decimal percentage values. Earlier versions had this as INTEGER, which was corrected.
• The _ingested_at column is populated by the refresh script (not by Dataslayer) and indicates when the row was last refreshed into the final table.
• Historical data outside the staging window is preserved; only overlapping date+ID combinations are replaced.
• With 458 partitions covering the full date history, partition pruning via WHERE Date >= ... is essential for performant queries.