suse_reddit_adlevel_staging_v2

Full reference: paidteam-data-warehouse.customer_ads_suse.suse_reddit_adlevel_staging_v2Type: Staging | Rows: 4,934 | Size: 2.3 MB

Reddit Ads ad-level staging data (V2). Dataslayer writes a rolling window of ad-level performance data into this table daily. The scheduled refresh script reads from here to update the final table.

Schema

Column	Type	Nullable	Description
Date	DATE	No	Date of ad performance
CampaignID	STRING	Yes	Reddit campaign ID
AdgroupID	STRING	Yes	Reddit ad group ID
AdID	STRING	Yes	Reddit ad ID
Campaignname	STRING	Yes	Campaign name
Adgroupname	STRING	Yes	Ad group name
Adname	STRING	Yes	Ad name
Campaignobjective	STRING	Yes	Campaign objective (CONVERSIONS, TRAFFIC, etc.)
GoalType	STRING	Yes	Goal type
Impressions	INTEGER	Yes	Number of impressions
Clicks	INTEGER	Yes	Number of clicks
CTR	FLOAT	Yes	Click-through rate
CPC	FLOAT	Yes	Cost per click
ECPM	FLOAT	Yes	Effective cost per thousand impressions
CPV	FLOAT	Yes	Cost per video view
Spend	FLOAT	Yes	Total spend
Frequency	FLOAT	Yes	Average frequency per user
Reach	INTEGER	Yes	Unique users reached
RedditLeads	INTEGER	Yes	Reddit native lead form submissions
ConversionLeadClicks	INTEGER	Yes	Click-through lead conversions
ConversionLeadViews	INTEGER	Yes	View-through lead conversions
ConversionSignUpClicks	INTEGER	Yes	Click-through sign-up conversions
ConversionSignUpViews	INTEGER	Yes	View-through sign-up conversions
ConversionViewContentClicks	INTEGER	Yes	Click-through content view conversions
ConversionViewContentViews	INTEGER	Yes	View-through content view conversions
VideoviewRate	FLOAT	Yes	Video view rate
VideoCompletionRate	FLOAT	Yes	Video completion rate
Videowatched25percent	INTEGER	Yes	Video watched to 25% count
Videowatched50percent	INTEGER	Yes	Video watched to 50% count
Videowatched75percent	INTEGER	Yes	Video watched to 75% count
Videowatched100percent	INTEGER	Yes	Video watched to 100% count

Partitioning & Clustering

• Partitioned by: Date (DAY)
• Partitions: 24
• Clustering: None

Refresh Logic

This is a staging table. Dataslayer overwrites data here on a daily schedule. The refresh script reads from this table and upserts into suse_reddit_adlevel_final_v2.

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

Sample Query

SELECT
  Date,
  Campaignname,
  Adgroupname,
  Adname,
  Impressions,
  Clicks,
  Spend
FROM `paidteam-data-warehouse.customer_ads_suse.suse_reddit_adlevel_staging_v2`
WHERE Date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
ORDER BY Date DESC, Spend DESC
LIMIT 100;

Notes

• Inconsistent column casing: Reddit columns use mixed casing patterns. For example, Campaignname (lowercase 'n') vs. Google's Campaign or Microsoft's CampaignName. Similarly, AdgroupID (lowercase 'g') matches Google's convention but differs from Microsoft's AdGroupID.
• Reddit uses Spend for cost instead of Cost or CostUSD used by other platforms. Cross-platform views must map these to a common name.
• Reddit uses ECPM (Effective CPM) rather than CPM.
• Reddit provides separate click and view attribution columns for each conversion type (Lead, SignUp, ViewContent).
• The VideoviewRate column uses a lowercase 'v' in "view" -- note this differs from the VideoViewsRate naming on other platforms.