SQL Portfolio - Jason Stinnett

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;

Let’s Connect