suse_stackadapt_adlevel_final_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_stackadapt_adlevel_final_v2Type: Final | Rows: 3,765 | Size: 1.8 MB

StackAdapt ad-level final data (V2). Contains the complete historical record of StackAdapt ad performance, refreshed daily from staging via a rolling 60-day DELETE+INSERT process.

Schema

Column	Type	Nullable	Description
Date	DATE	No (REQUIRED)	Date of ad performance
Campaign_ID	STRING	Yes	StackAdapt campaign ID
AdGroup_ID	STRING	Yes	StackAdapt campaign group ID
Ad_ID	STRING	Yes	StackAdapt creative ID
Campaign	STRING	Yes	Campaign name
AdGroup	STRING	Yes	Campaign group name
Ad	STRING	Yes	Creative name
Channel_Type	STRING	Yes	Channel type (Display, Video, Native, etc.)
Click_URL	STRING	Yes	Click destination URL
Creative_Size	STRING	Yes	Creative dimensions
Impressions	INTEGER	Yes	Number of impressions
Clicks	INTEGER	Yes	Number of clicks
Cost	NUMERIC	Yes	Cost in USD (precise decimal, converted from GBP)
Conversions	NUMERIC	Yes	Number of conversions
_ingested_at	TIMESTAMP	Yes	Timestamp when the row was inserted by the refresh script

Partitioning & Clustering

• Partitioned by: Date (DAY)
• Partitions: 26
• Clustered by: Campaign, AdGroup

Clustering is on name columns to optimize dashboard queries.

Refresh Logic

The scheduled refresh script performs the following for StackAdapt:

1. Read all rows from suse_stackadapt_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 Campaign_ID, AdGroup_ID, Ad_ID 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, Campaign_ID, AdGroup_ID, Ad_ID

Sample Query

SELECT
  Date,
  Campaign,
  Channel_Type,
  Creative_Size,
  SUM(Impressions) AS total_impressions,
  SUM(Clicks) AS total_clicks,
  SUM(Cost) AS total_cost,
  SUM(Conversions) AS total_conversions
FROM `paidteam-data-warehouse.customer_ads_suse.suse_stackadapt_adlevel_final_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY Date, Campaign, Channel_Type, Creative_Size
ORDER BY Date DESC
LIMIT 100;

Notes

• Underscore ID naming: Consistent with the staging table and 6sense conventions.
• NUMERIC type for Cost and Conversions: Preserves exact decimal precision.
• The staging and final table currently have the same row count (3,765), suggesting the data history is still within the staging window.
• Cost values are converted from GBP to USD during ingestion. The original GBP values are not stored.
• The staging table clusters on Campaign_ID, Date while the final table clusters on Campaign, AdGroup (name columns), following the same pattern as 6sense.
• StackAdapt is a relatively small dataset compared to other platforms, with only 3,765 rows and 26 partitions.