PPC Reporting Query
The following BiqQuery SQL query is used to power a client-facing reporting dashboard built in Google Data Studio.
Key aspects of the query include:
- Joining campaign-level data from Google Ads, Bing Ads, and a second Bing Ads account containing historical data
- Normalizing Google Ads cost data, since the API returns this data in micro currency
- Normalizing column names, e.g. Cost (Google) & Spend (Bing)
- Lastly, joining a campaign metadata table to add dimensions like category and region for segmenting and filtering purposes
SELECT Date, Impressions, Clicks, Cost, Conversions, Network, cpgndata.Campaign, AvgPosition, Channel, Device, Category, Region, Language
FROM (
(SELECT day as Date, Impressions, Clicks, round(cost/1000000,2) as Cost, Conversions, Network, Campaign, AvgPosition, 'Google Ads' as Channel, Device
FROM (
SELECT *,
RANK() OVER (PARTITION BY day
ORDER BY _sdc_report_datetime DESC) as rank
FROM client_name_google_ads.CAMPAIGN_PERFORMANCE_REPORT
ORDER BY day ASC
) AS latest
WHERE latest.rank = 1 and day >= '2017-08-01'
)
UNION ALL
(SELECT timeperiod as Date, Impressions, Clicks, spend as Cost, Conversions, campaigntype as Network, campaignname as Campaign, averageposition as AvgPosition, 'Microsoft Ads' as Channel, devicetype as Device
FROM (
SELECT *,
RANK() OVER (PARTITION BY timeperiod
ORDER BY _sdc_report_datetime DESC) as rank
FROM client_name_bing_ads.campaign_performance_report
ORDER BY timeperiod ASC
) AS latest
WHERE latest.rank = 1 and timeperiod >= '2017-08-01'
)
UNION ALL
(SELECT timeperiod as Date, Impressions, Clicks, spend as Cost, Conversions, campaigntype as Network, campaignname as Campaign, averageposition as AvgPosition, 'Microsoft Ads' as Channel, devicetype as Device
FROM (
SELECT *,
RANK() OVER (PARTITION BY timeperiod
ORDER BY _sdc_report_datetime DESC) as rank
FROM client_name_bing_ads_historical.campaign_performance_report
ORDER BY timeperiod ASC
) AS latest
WHERE latest.rank = 1 and timeperiod >= '2017-08-01'
)
) cpgndata
LEFT JOIN cpgn_filter_attr.metadata metadata
ON cpgndata.Campaign = metadata.Campaign
Which Stores Had the Greatest Revenue Increase?
As part of the course Introduction to Big Data with MySQL and Teradata, I wrote a number of traditional business intelligence queries. The Teradata query below uses data on individual customer purchases to answer the following business question for Dillards, a chain of department stores.
“Which department in which store had the greatest percent increase in average daily sales revenue from November to December, and what city and state was that store located in? Only examine departments whose total sales were at least $1,000 in both November and December.”
Note that the seemingly-random having statement is there to filter out some known test data in the data set.
SELECT TOP 5 CleanSales.store, CleanSales.dept, di.deptdesc, sm.City, sm.State, NovAvgDailyRevenue, DecAvgDailyRevenue, ((DecAvgDailyRevenue - NovAvgDailyRevenue) / NovAvgDailyRevenue) * 100 AS PercentageIncrease
FROM (
SELECT t.store, sk.dept, SUM(CASE WHEN EXTRACT(MONTH from saledate)=11
THEN sprice END) AS NovSales, SUM(CASE WHEN EXTRACT(MONTH from
saledate)=12 THEN sprice END) AS DecSales, COUNT(DISTINCT CASE WHEN
EXTRACT(MONTH from saledate)=11 THEN saledate END) AS NovDays,
COUNT(DISTINCT CASE WHEN EXTRACT(MONTH from saledate)=12 THEN saledate
END) AS DecDays, NovSales / NovDays AS NovAvgDailyRevenue, DecSales /
DecDays AS DecAvgDailyRevenue
FROM trnsact t JOIN skuinfo sk
ON t.sku = sk.sku
WHERE stype = 'P' AND saledate < date'2005-08-01'
GROUP BY t.store, sk.dept
HAVING NovDays > 20 AND DecDays > 20) AS CleanSales
JOIN DeptInfo di
ON CleanSales.dept = di.dept
JOIN store_msa sm
ON CleanSales.store = sm.store
ORDER BY PercentageIncrease DESC;