suse_6sense_adlevel_final_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_6sense_adlevel_final_v2Type: Final | Rows: 192,880 | Size: 54.8 MB

6sense ad-level final data (V2). Contains the complete historical record of 6sense 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	6sense campaign ID
AdGroup_ID	STRING	Yes	6sense ad group ID
Ad_ID	STRING	Yes	6sense ad/creative ID
Campaign	STRING	Yes	Campaign name
AdGroup	STRING	Yes	Ad group name
Ad	STRING	Yes	Ad/creative name
Segment	STRING	Yes	Target segment
Impressions	INTEGER	Yes	Number of impressions
Clicks	INTEGER	Yes	Number of clicks
Cost	NUMERIC	Yes	Cost in USD (precise decimal)
AccountReached	INTEGER	Yes	Number of accounts reached
_ingested_at	TIMESTAMP	Yes	Timestamp when the row was inserted by the refresh script

Partitioning & Clustering

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

Clustering is on name columns (not ID columns) to optimize dashboard queries that filter by campaign name.

Refresh Logic

The scheduled refresh script performs the following for 6sense:

1. Read all rows from suse_6sense_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,
  Segment,
  SUM(Impressions) AS total_impressions,
  SUM(Clicks) AS total_clicks,
  SUM(Cost) AS total_cost,
  SUM(AccountReached) AS total_accounts_reached
FROM `paidteam-data-warehouse.customer_ads_suse.suse_6sense_adlevel_final_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
GROUP BY Date, Campaign, Segment
ORDER BY Date DESC
LIMIT 100;

Notes

• Underscore ID naming: Consistent with the staging table, uses Campaign_ID, AdGroup_ID, Ad_ID.
• NUMERIC type for Cost: Matches the staging table, preserving exact decimal precision.
• The Date column is REQUIRED (NOT NULL), matching the staging table constraint.
• The staging and final table currently have the same row count (192,880), suggesting that the initial data load covered the full history and no additional historical data has been accumulated yet beyond the staging window.
• The staging table clusters on Campaign_ID, Date while the final table clusters on Campaign, AdGroup (name columns). This reflects different optimization goals: staging optimized for refresh lookups, final optimized for dashboard queries.