Skip to content

Instantly share code, notes, and snippets.

@OrenBochman

OrenBochman/_sql_advertising_data.md

Created July 18, 2017 10:05
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save OrenBochman/b8ecb5dc9d4a583b5919737b06075b26 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save OrenBochman/b8ecb5dc9d4a583b5919737b06075b26 to your computer and use it in GitHub Desktop.
Download ZIP
advertising
Raw
_sql_advertising_data.md

SQL Fiddle

MySQL 5.6 Schema Setup:

-- schema
CREATE TABLE Client (
    ClientId INT         NOT NULL AUTO_INCREMENT,
    ClientName VARCHAR(35)     NOT NULL,
    Email varchar(100)   NOT NULL,
    PhoneNumber VARCHAR(11),
    Budget INT          NOT NULL,
    PRIMARY KEY(ClientId)
);

CREATE TABLE Campaign (
   CampaignId INT NOT NULL AUTO_INCREMENT,
     ClientId INT NOT NULL,
 CampaignName VARCHAR(35) NOT NULL,
 IsVideo Boolean,
   Start DATETIME NOT NULL,
     End DATETIME NOT NULL,
  PRIMARY KEY(CampaignId), 
  FOREIGN KEY(ClientId) REFERENCES Client(ClientId)
);

CREATE TABLE Ad (
    AdId INT NOT NULL AUTO_INCREMENT,
    CampaignId INT not NULL,
    Impressions INT NOT NULL,
    Clicks INT NOT NULL,
    Installs INT NOT NULL,
    Width INT NOT NULL,
    Height INT NOT NULL,
    Spend INT NOT NULL,
    PRIMARY KEY(AdId),   
    FOREIGN KEY (CampaignId) REFERENCES Campaign(CampaignId)
);
-- data
INSERT INTO Client
    (ClientId, ClientName, Email, PhoneNumber, Budget)
VALUES
    (1, 'Toyoka','alice@toyoka.com', 1234567890, 1200),
    (2, 'Popsi' ,'bob@popsi.com',    2468101214, 4400),
    (3, 'Smike' ,'chi@snike.com',    1357911131, 6300),
    (4, 'Wazoo' ,'dom@wazoo.com',    1357911131, 3500),
    (5, 'Mapple','ephi@mapple.com',  1357911131, 4000),
    (6, 'Acme'  ,'george@acame.com', 1357911131, 9100)
;


INSERT INTO Campaign
    (CampaignId, ClientId, CampaignName, IsVideo, Start, End)
VALUES
    (1, 1, 'Toyoka-Always-On',           true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (2, 1, 'Toyoka-V8',                  true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (3, 2, 'Popsi-Free',                 true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (4, 2, 'Popsi-Null',                 false,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (5, 3, 'Smike-TV',                   true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (6, 3, 'Smike-IT',                   true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (7, 4, 'Wazoo-Mortgage',             false,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (8, 5, 'Mapple-Brand-Awerness',      false,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (9, 5, 'Mapple-Competitor-Awerness', true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (10,5, 'Mapple-Remarketing-Decision',false,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (11,5, 'Mapple-Video-Consideration', true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
    (12,6, 'Acme-Remarketing',           false,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y'))
;

INSERT INTO Ad
    (AdId, CampaignId, Impressions, Clicks, Installs, Width, Height, Spend)
VALUES
    (1, 1,   10000,100,15,620,340,80),
    (2, 1,    6000, 60, 9,120,340,80),
    (3, 2,    4000, 70, 8,128,340,80),
    (4, 2,   17000, 10,12,660,340,80),
    (5, 3,   10000,100,16,620,340,80),
    (6, 3,    6200, 60,11,120,340,80),
    (7, 4,    4300, 70,12,120,340,60),
    (8, 5,   17000, 10,13,620,340,90),
    (9, 5,   10200,100,17,120,340,70),
    (10, 6,   6500, 60,11,620,340,20),
    (11, 6,   4000, 70,12,120,340,80),
    (12, 7,  17700, 10,13,620,340,110),
    (13, 7,  12000,100,18,120,340,124),
    (14, 8,   6000, 60,11,120,340,110),
    (15, 8,   4700, 70,12,420,340,110),
    (16, 9,  17000, 10,10,120,340,110),
    (17, 9,  10200,100,19,120,340,110),
    (18, 10,  6000, 60,10,120,340,110),
    (19, 10,  4500, 70,10,620,340,110),
    (20, 11, 17000, 10,15,120,340,110),
    (21, 11, 10000,100,10,120,340,110),
    (22, 12,  6060, 60,10,624,340,110),
    (23, 12,  4020, 70,10,120,340,110),
    (24, 12, 17020, 10,10,620,340,110),
    (25, 12, 20020,400,22,160,340,110),
    (26, 11,  6050, 60,10,620,340,110),
    (27, 11,  4500, 70,11,120,340,110),
    (28, 11, 17000, 10,12,420,340,110),
    (29, 10, 10000,100,13,120,340,110),
    (30, 10,  7000, 60,14,120,340,110),
    (31, 10,  5000, 70,15,620,340,110),
    (32, 10, 17000, 10,16,120,340,110)
;

Query 1:

-- unified view of ads
  SELECT *
    FROM Campaign as ca
         INNER JOIN Client AS cl ON cl.ClientId   = ca.ClientId 
         INNER JOIN Ad     AS ad ON ad.CampaignId = ca.CampaignId 
ORDER BY ca.CampaignName,ad.Impressions
-- where Ad.Impressions >500

Results:

| CampaignId | ClientId |                CampaignName | IsVideo |                Start |                  End | ClientId | ClientName |            Email | PhoneNumber | Budget | AdId | CampaignId | Impressions | Clicks | Installs | Width | Height | Spend |
|------------|----------|-----------------------------|---------|----------------------|----------------------|----------|------------|------------------|-------------|--------|------|------------|-------------|--------|----------|-------|--------|-------|
|         12 |        6 |            Acme-Remarketing |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        6 |       Acme | george@acame.com |  1357911131 |   9100 |   23 |         12 |        4020 |     70 |       10 |   120 |    340 |   110 |
|         12 |        6 |            Acme-Remarketing |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        6 |       Acme | george@acame.com |  1357911131 |   9100 |   22 |         12 |        6060 |     60 |       10 |   624 |    340 |   110 |
|         12 |        6 |            Acme-Remarketing |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        6 |       Acme | george@acame.com |  1357911131 |   9100 |   24 |         12 |       17020 |     10 |       10 |   620 |    340 |   110 |
|         12 |        6 |            Acme-Remarketing |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        6 |       Acme | george@acame.com |  1357911131 |   9100 |   25 |         12 |       20020 |    400 |       22 |   160 |    340 |   110 |
|          8 |        5 |       Mapple-Brand-Awerness |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   15 |          8 |        4700 |     70 |       12 |   420 |    340 |   110 |
|          8 |        5 |       Mapple-Brand-Awerness |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   14 |          8 |        6000 |     60 |       11 |   120 |    340 |   110 |
|          9 |        5 |  Mapple-Competitor-Awerness |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   17 |          9 |       10200 |    100 |       19 |   120 |    340 |   110 |
|          9 |        5 |  Mapple-Competitor-Awerness |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   16 |          9 |       17000 |     10 |       10 |   120 |    340 |   110 |
|         10 |        5 | Mapple-Remarketing-Decision |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   19 |         10 |        4500 |     70 |       10 |   620 |    340 |   110 |
|         10 |        5 | Mapple-Remarketing-Decision |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   31 |         10 |        5000 |     70 |       15 |   620 |    340 |   110 |
|         10 |        5 | Mapple-Remarketing-Decision |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   18 |         10 |        6000 |     60 |       10 |   120 |    340 |   110 |
|         10 |        5 | Mapple-Remarketing-Decision |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   30 |         10 |        7000 |     60 |       14 |   120 |    340 |   110 |
|         10 |        5 | Mapple-Remarketing-Decision |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   29 |         10 |       10000 |    100 |       13 |   120 |    340 |   110 |
|         10 |        5 | Mapple-Remarketing-Decision |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   32 |         10 |       17000 |     10 |       16 |   120 |    340 |   110 |
|         11 |        5 |  Mapple-Video-Consideration |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   27 |         11 |        4500 |     70 |       11 |   120 |    340 |   110 |
|         11 |        5 |  Mapple-Video-Consideration |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   26 |         11 |        6050 |     60 |       10 |   620 |    340 |   110 |
|         11 |        5 |  Mapple-Video-Consideration |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   21 |         11 |       10000 |    100 |       10 |   120 |    340 |   110 |
|         11 |        5 |  Mapple-Video-Consideration |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   28 |         11 |       17000 |     10 |       12 |   420 |    340 |   110 |
|         11 |        5 |  Mapple-Video-Consideration |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        5 |     Mapple |  ephi@mapple.com |  1357911131 |   4000 |   20 |         11 |       17000 |     10 |       15 |   120 |    340 |   110 |
|          3 |        2 |                  Popsi-Free |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        2 |      Popsi |    bob@popsi.com |  2468101214 |   4400 |    6 |          3 |        6200 |     60 |       11 |   120 |    340 |    80 |
|          3 |        2 |                  Popsi-Free |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        2 |      Popsi |    bob@popsi.com |  2468101214 |   4400 |    5 |          3 |       10000 |    100 |       16 |   620 |    340 |    80 |
|          4 |        2 |                  Popsi-Null |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        2 |      Popsi |    bob@popsi.com |  2468101214 |   4400 |    7 |          4 |        4300 |     70 |       12 |   120 |    340 |    60 |
|          6 |        3 |                    Smike-IT |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        3 |      Smike |    chi@snike.com |  1357911131 |   6300 |   11 |          6 |        4000 |     70 |       12 |   120 |    340 |    80 |
|          6 |        3 |                    Smike-IT |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        3 |      Smike |    chi@snike.com |  1357911131 |   6300 |   10 |          6 |        6500 |     60 |       11 |   620 |    340 |    20 |
|          5 |        3 |                    Smike-TV |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        3 |      Smike |    chi@snike.com |  1357911131 |   6300 |    9 |          5 |       10200 |    100 |       17 |   120 |    340 |    70 |
|          5 |        3 |                    Smike-TV |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        3 |      Smike |    chi@snike.com |  1357911131 |   6300 |    8 |          5 |       17000 |     10 |       13 |   620 |    340 |    90 |
|          1 |        1 |            Toyoka-Always-On |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        1 |     Toyoka | alice@toyoka.com |  1234567890 |   1200 |    2 |          1 |        6000 |     60 |        9 |   120 |    340 |    80 |
|          1 |        1 |            Toyoka-Always-On |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        1 |     Toyoka | alice@toyoka.com |  1234567890 |   1200 |    1 |          1 |       10000 |    100 |       15 |   620 |    340 |    80 |
|          2 |        1 |                   Toyoka-V8 |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        1 |     Toyoka | alice@toyoka.com |  1234567890 |   1200 |    3 |          2 |        4000 |     70 |        8 |   128 |    340 |    80 |
|          2 |        1 |                   Toyoka-V8 |    true | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        1 |     Toyoka | alice@toyoka.com |  1234567890 |   1200 |    4 |          2 |       17000 |     10 |       12 |   660 |    340 |    80 |
|          7 |        4 |              Wazoo-Mortgage |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        4 |      Wazoo |    dom@wazoo.com |  1357911131 |   3500 |   13 |          7 |       12000 |    100 |       18 |   120 |    340 |   124 |
|          7 |        4 |              Wazoo-Mortgage |   false | 2002-01-01T00:00:00Z | 2003-01-01T00:00:00Z |        4 |      Wazoo |    dom@wazoo.com |  1357911131 |   3500 |   12 |          7 |       17700 |     10 |       13 |   620 |    340 |   110 |

Query 2:

-- CPC SPM and CPA by Layout
    SELECT cl.ClientName,
           ca.CampaignName,
           ad.Spend, 
           ad.Impressions,
           ad.Impressions/Ad.Spend/1000 AS CPM,
           ad.Clicks,
           ad.Clicks/Ad.Spend AS CPC,
           ad.Installs,
           ad.Installs/Ad.Spend AS CPA,
           Case When Ad.Width> Ad.Height THEN 'H' ELSE 'V' END AS Layout
      FROM Campaign as ca
           INNER JOIN Client AS cl ON cl.ClientId   = ca.ClientId 
           INNER JOIN Ad     AS ad ON ad.CampaignId = ca.CampaignId 
  Order By Layout, ClientName, Installs

Results:

| ClientName |                CampaignName | Spend | Impressions |        CPM | Clicks |    CPC | Installs |    CPA | Layout |
|------------|-----------------------------|-------|-------------|------------|--------|--------|----------|--------|--------|
|       Acme |            Acme-Remarketing |   110 |        6060 | 0.05509091 |     60 | 0.5455 |       10 | 0.0909 |      H |
|       Acme |            Acme-Remarketing |   110 |       17020 | 0.15472727 |     10 | 0.0909 |       10 | 0.0909 |      H |
|     Mapple | Mapple-Remarketing-Decision |   110 |        4500 | 0.04090909 |     70 | 0.6364 |       10 | 0.0909 |      H |
|     Mapple |  Mapple-Video-Consideration |   110 |        6050 |      0.055 |     60 | 0.5455 |       10 | 0.0909 |      H |
|     Mapple |  Mapple-Video-Consideration |   110 |       17000 | 0.15454545 |     10 | 0.0909 |       12 | 0.1091 |      H |
|     Mapple |       Mapple-Brand-Awerness |   110 |        4700 | 0.04272727 |     70 | 0.6364 |       12 | 0.1091 |      H |
|     Mapple | Mapple-Remarketing-Decision |   110 |        5000 | 0.04545455 |     70 | 0.6364 |       15 | 0.1364 |      H |
|      Popsi |                  Popsi-Free |    80 |       10000 |      0.125 |    100 |   1.25 |       16 |    0.2 |      H |
|      Smike |                    Smike-IT |    20 |        6500 |      0.325 |     60 |      3 |       11 |   0.55 |      H |
|      Smike |                    Smike-TV |    90 |       17000 | 0.18888889 |     10 | 0.1111 |       13 | 0.1444 |      H |
|     Toyoka |                   Toyoka-V8 |    80 |       17000 |     0.2125 |     10 |  0.125 |       12 |   0.15 |      H |
|     Toyoka |            Toyoka-Always-On |    80 |       10000 |      0.125 |    100 |   1.25 |       15 | 0.1875 |      H |
|      Wazoo |              Wazoo-Mortgage |   110 |       17700 | 0.16090909 |     10 | 0.0909 |       13 | 0.1182 |      H |
|       Acme |            Acme-Remarketing |   110 |        4020 | 0.03654545 |     70 | 0.6364 |       10 | 0.0909 |      V |
|       Acme |            Acme-Remarketing |   110 |       20020 |      0.182 |    400 | 3.6364 |       22 |    0.2 |      V |
|     Mapple |  Mapple-Video-Consideration |   110 |       10000 | 0.09090909 |    100 | 0.9091 |       10 | 0.0909 |      V |
|     Mapple |  Mapple-Competitor-Awerness |   110 |       17000 | 0.15454545 |     10 | 0.0909 |       10 | 0.0909 |      V |
|     Mapple | Mapple-Remarketing-Decision |   110 |        6000 | 0.05454545 |     60 | 0.5455 |       10 | 0.0909 |      V |
|     Mapple |  Mapple-Video-Consideration |   110 |        4500 | 0.04090909 |     70 | 0.6364 |       11 |    0.1 |      V |
|     Mapple |       Mapple-Brand-Awerness |   110 |        6000 | 0.05454545 |     60 | 0.5455 |       11 |    0.1 |      V |
|     Mapple | Mapple-Remarketing-Decision |   110 |       10000 | 0.09090909 |    100 | 0.9091 |       13 | 0.1182 |      V |
|     Mapple | Mapple-Remarketing-Decision |   110 |        7000 | 0.06363636 |     60 | 0.5455 |       14 | 0.1273 |      V |
|     Mapple |  Mapple-Video-Consideration |   110 |       17000 | 0.15454545 |     10 | 0.0909 |       15 | 0.1364 |      V |
|     Mapple | Mapple-Remarketing-Decision |   110 |       17000 | 0.15454545 |     10 | 0.0909 |       16 | 0.1455 |      V |
|     Mapple |  Mapple-Competitor-Awerness |   110 |       10200 | 0.09272727 |    100 | 0.9091 |       19 | 0.1727 |      V |
|      Popsi |                  Popsi-Free |    80 |        6200 |     0.0775 |     60 |   0.75 |       11 | 0.1375 |      V |
|      Popsi |                  Popsi-Null |    60 |        4300 | 0.07166667 |     70 | 1.1667 |       12 |    0.2 |      V |
|      Smike |                    Smike-IT |    80 |        4000 |       0.05 |     70 |  0.875 |       12 |   0.15 |      V |
|      Smike |                    Smike-TV |    70 |       10200 | 0.14571429 |    100 | 1.4286 |       17 | 0.2429 |      V |
|     Toyoka |                   Toyoka-V8 |    80 |        4000 |       0.05 |     70 |  0.875 |        8 |    0.1 |      V |
|     Toyoka |            Toyoka-Always-On |    80 |        6000 |      0.075 |     60 |   0.75 |        9 | 0.1125 |      V |
|      Wazoo |              Wazoo-Mortgage |   124 |       12000 | 0.09677419 |    100 | 0.8065 |       18 | 0.1452 |      V |

Query 3:

-- Tot Campaign Installs from Ads
    SELECT ClientName, CampaignName,
           SUM(Ad.Installs) as Conversions          
      FROM Campaign as ca
           INNER JOIN Client AS cl ON cl.ClientId   = ca.ClientId 
           INNER JOIN Ad     AS ad ON ad.CampaignId = ca.CampaignId 
    Group By ca.CampaignId
    Order By cl.ClientName

Results:

| ClientName |                CampaignName | Conversions |
|------------|-----------------------------|-------------|
|       Acme |            Acme-Remarketing |          52 |
|     Mapple |  Mapple-Video-Consideration |          58 |
|     Mapple | Mapple-Remarketing-Decision |          78 |
|     Mapple |  Mapple-Competitor-Awerness |          29 |
|     Mapple |       Mapple-Brand-Awerness |          23 |
|      Popsi |                  Popsi-Null |          12 |
|      Popsi |                  Popsi-Free |          27 |
|      Smike |                    Smike-IT |          23 |
|      Smike |                    Smike-TV |          30 |
|     Toyoka |                   Toyoka-V8 |          20 |
|     Toyoka |            Toyoka-Always-On |          24 |
|      Wazoo |              Wazoo-Mortgage |          31 |

Query 4:

-- Top Campaign (by Installs) by Client
    SELECT TOP 1 ClientName, 
           CampaignName, 
           SUM(ad.Spend),
           SUM(ad.Installs) as Conversions            
      FROM Campaign as ca
           INNER JOIN Client AS cl ON cl.ClientId   = ca.ClientId
           INNER JOIN Ad     AS ad ON ad.CampaignId = ca.CampaignId 
-- Where ca.ClientId=5
  Group By ca.CampaignId
  Order By conversions
--    Having ad.Installs = Conversions

Results:

Raw
advertising_dd.sql
-- schema
CREATE TABLE Client (
ClientId INT NOT NULL AUTO_INCREMENT,
ClientName VARCHAR(35) NOT NULL,
Email varchar(100) NOT NULL,
PhoneNumber VARCHAR(11),
Budget INT NOT NULL,
PRIMARY KEY(ClientId)
);
CREATE TABLE Campaign (
CampaignId INT NOT NULL AUTO_INCREMENT,
ClientId INT NOT NULL,
CampaignName VARCHAR(35) NOT NULL,
IsVideo Boolean,
Start DATETIME NOT NULL,
End DATETIME NOT NULL,
PRIMARY KEY(CampaignId),
FOREIGN KEY(ClientId) REFERENCES Client(ClientId)
);
CREATE TABLE Ad (
AdId INT NOT NULL AUTO_INCREMENT,
CampaignId INT not NULL,
Impressions INT NOT NULL,
Clicks INT NOT NULL,
Installs INT NOT NULL,
Width INT NOT NULL,
Height INT NOT NULL,
Spend INT NOT NULL,
PRIMARY KEY(AdId),
FOREIGN KEY (CampaignId) REFERENCES Campaign(CampaignId)
);
-- data
INSERT INTO Client
(ClientId, ClientName, Email, PhoneNumber, Budget)
VALUES
(1, 'Toyoka','alice@toyoka.com', 1234567890, 1200),
(2, 'Popsi' ,'bob@popsi.com', 2468101214, 4400),
(3, 'Smike' ,'chi@snike.com', 1357911131, 6300),
(4, 'Wazoo' ,'dom@wazoo.com', 1357911131, 3500),
(5, 'Mapple','ephi@mapple.com', 1357911131, 4000),
(6, 'Acme' ,'george@acame.com', 1357911131, 9100)
;
INSERT INTO Campaign
(CampaignId, ClientId, CampaignName, IsVideo, Start, End)
VALUES
(1, 1, 'Toyoka-Always-On', true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(2, 1, 'Toyoka-V8', true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(3, 2, 'Popsi-Free', true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(4, 2, 'Popsi-Null', false,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(5, 3, 'Smike-TV', true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(6, 3, 'Smike-IT', true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(7, 4, 'Wazoo-Mortgage', false,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(8, 5, 'Mapple-Brand-Awerness', false,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(9, 5, 'Mapple-Competitor-Awerness', true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(10,5, 'Mapple-Remarketing-Decision',false,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(11,5, 'Mapple-Video-Consideration', true ,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y')),
(12,6, 'Acme-Remarketing', false,str_to_date('01-01-2002', '%d-%m-%Y'),str_to_date('01-01-2003', '%d-%m-%Y'))
;
INSERT INTO Ad
(AdId, CampaignId, Impressions, Clicks, Installs, Width, Height, Spend)
VALUES
(1, 1, 10000,100,15,620,340,80),
(2, 1, 6000, 60, 9,120,340,80),
(3, 2, 4000, 70, 8,128,340,80),
(4, 2, 17000, 10,12,660,340,80),
(5, 3, 10000,100,16,620,340,80),
(6, 3, 6200, 60,11,120,340,80),
(7, 4, 4300, 70,12,120,340,60),
(8, 5, 17000, 10,13,620,340,90),
(9, 5, 10200,100,17,120,340,70),
(10, 6, 6500, 60,11,620,340,20),
(11, 6, 4000, 70,12,120,340,80),
(12, 7, 17700, 10,13,620,340,110),
(13, 7, 12000,100,18,120,340,124),
(14, 8, 6000, 60,11,120,340,110),
(15, 8, 4700, 70,12,420,340,110),
(16, 9, 17000, 10,10,120,340,110),
(17, 9, 10200,100,19,120,340,110),
(18, 10, 6000, 60,10,120,340,110),
(19, 10, 4500, 70,10,620,340,110),
(20, 11, 17000, 10,15,120,340,110),
(21, 11, 10000,100,10,120,340,110),
(22, 12, 6060, 60,10,624,340,110),
(23, 12, 4020, 70,10,120,340,110),
(24, 12, 17020, 10,10,620,340,110),
(25, 12, 20020,400,22,160,340,110),
(26, 11, 6050, 60,10,620,340,110),
(27, 11, 4500, 70,11,120,340,110),
(28, 11, 17000, 10,12,420,340,110),
(29, 10, 10000,100,13,120,340,110),
(30, 10, 7000, 60,14,120,340,110),
(31, 10, 5000, 70,15,620,340,110),
(32, 10, 17000, 10,16,120,340,110)
;
Raw
queries.sql
-- unified view of ads
SELECT *
FROM Campaign as ca
INNER JOIN Client AS cl ON cl.ClientId = ca.ClientId
INNER JOIN Ad AS ad ON ad.CampaignId = ca.CampaignId
ORDER BY ca.CampaignName,ad.Impressions
-- where Ad.Impressions >500
;
-- CPC SPM and CPA by Layout
SELECT cl.ClientName,
ca.CampaignName,
ad.Spend,
ad.Impressions,
ad.Impressions/Ad.Spend/1000 AS CPM,
ad.Clicks,
ad.Clicks/Ad.Spend AS CPC,
ad.Installs,
ad.Installs/Ad.Spend AS CPA,
Case When Ad.Width> Ad.Height THEN 'H' ELSE 'V' END AS Layout
FROM Campaign as ca
INNER JOIN Client AS cl ON cl.ClientId = ca.ClientId
INNER JOIN Ad AS ad ON ad.CampaignId = ca.CampaignId
Order By Layout, ClientName, Installs
;
-- Tot Campaign Installs from Ads
SELECT ClientName, CampaignName,
SUM(Ad.Installs) as Conversions
FROM Campaign as ca
INNER JOIN Client AS cl ON cl.ClientId = ca.ClientId
INNER JOIN Ad AS ad ON ad.CampaignId = ca.CampaignId
Group By ca.CampaignId
Order By cl.ClientName
;
-- Top Campaign (by Installs) by Client
SELECT TOP 1 ClientName,
CampaignName,
SUM(ad.Spend),
SUM(ad.Installs) as Conversions
FROM Campaign as ca
INNER JOIN Client AS cl ON cl.ClientId = ca.ClientId
INNER JOIN Ad AS ad ON ad.CampaignId = ca.CampaignId
-- Where ca.ClientId=5
Group By ca.CampaignId
Order By conversions
-- Having ad.Installs = Conversions
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment