Professional Web Applications Themes

Building Adwords mechanisem - Microsoft SQL / MS SQL Server

Hi there, I'm trying to build an "adwords" mechanisem (targetted-keywords-search) that is based on 3 tables: Advertiser Advertiser_id Advertiser_logo Ad Ad_id Ad_title Ad_url Ad_status Ad_Advertiser_id AdWords Ad_id Keyword Keyword_cost AdWords_status A single advertiser has a number of different ads. Each ad has several targetted-keywords. How can I select the TOP 10 Keyword_cost + Ad information, eliminating duplicated Advertisers/Ads on dual kw-hit? I've tried this: SELECT TOP 10 Ad_title, Ad_url, Keyword_cost, Advertiser.Advertiser_logo FROM Ad INNER JOIN AdKeyword ON Ad.Ad_id=AdWords.Ad_id INNER JOIN Advertiser ON Ad.Ad_Advertiser_id=Advertiser.Advertiser_id WHERE -- Verify keyword/ad is not suspended Ad_status=1 AND AdWords_status=1 AND AdWords.Keyword IN (Delimited_list_of_keyword) ORDER BY Keyword_cost ...

Sponsored Links
  1. #1

    Default Building Adwords mechanisem

    Hi there,

    I'm trying to build an "adwords" mechanisem (targetted-keywords-search) that
    is based on 3 tables:

    Advertiser
    Advertiser_id
    Advertiser_logo

    Ad
    Ad_id
    Ad_title
    Ad_url
    Ad_status
    Ad_Advertiser_id

    AdWords
    Ad_id
    Keyword
    Keyword_cost
    AdWords_status

    A single advertiser has a number of different ads. Each ad has several
    targetted-keywords.

    How can I select the TOP 10 Keyword_cost + Ad information, eliminating
    duplicated Advertisers/Ads on dual kw-hit?

    I've tried this:

    SELECT TOP 10 Ad_title, Ad_url, Keyword_cost, Advertiser.Advertiser_logo
    FROM Ad
    INNER JOIN AdKeyword ON Ad.Ad_id=AdWords.Ad_id
    INNER JOIN Advertiser ON Ad.Ad_Advertiser_id=Advertiser.Advertiser_id
    WHERE
    -- Verify keyword/ad is not suspended
    Ad_status=1 AND AdWords_status=1
    AND AdWords.Keyword IN (Delimited_list_of_keyword)
    ORDER BY Keyword_cost DESC

    Unforunatlly, a single ad can have many related words. when a user type 2
    words that exists for the same ad, the ad is returned twice. Moreover, when
    the user type 2 words that exists for 2 different ads of the same
    advertiser, the advertiser is given 2 banners. I need DISTINCT ads and
    DISTINCT advertisers.

    Is it possible?

    Thanks!!


    Sponsored Links
    Jason Guest

  2. #2

    Default Re: Building Adwords mechanisem

    I've attached a complete DDL and example data, along with comments on the
    result I need.
    Thanks, I really appriciate that.

    CREATE TABLE [Advertiser] (
    [Advertiser_id] [int] ,
    [Advertiser_title] [varchar] (200) ,
    CONSTRAINT [PK_Advertiser] PRIMARY KEY CLUSTERED
    (
    [Advertiser_id]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    CREATE TABLE [Ad] (
    [Ad_id] [int] NOT NULL ,
    [Ad_title] [varchar] (50) ,
    [Ad_url] [varchar] (50) ,
    [Ad_Advertiser_id] [int] NULL ,
    [Ad_status] [bit] NULL CONSTRAINT [DF_Ad_Ad_status] DEFAULT (1)
    CONSTRAINT [PK_Ad] PRIMARY KEY CLUSTERED
    (
    [Ad_id]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO


    CREATE TABLE [AdKeyword] (
    [AdKeyword_id] [int] IDENTITY (1, 1) NOT NULL ,
    [AdKeyword_ad_id] [int] NULL ,
    [AdKeyword_keyword] [varchar] (50) ,
    [AdKeyword_cost] [money] NULL CONSTRAINT [DF_AdKeyword_AdKeyword_cost]
    DEFAULT (0),
    [AdKeyword_status] [bit] NULL CONSTRAINT [DF_AdKeyword_AdKeyword_status]
    DEFAULT (1),
    CONSTRAINT [PK_AdKeyword] PRIMARY KEY CLUSTERED
    (
    [AdKeyword_id]
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

    -- Insert Advertisers
    INSERT INTO [Advertiser] (Advertiser_id,Advertiser_title) VALUES (1,'hp')
    INSERT INTO [Advertiser] (Advertiser_id,Advertiser_title) VALUES (2,'ms')
    INSERT INTO [Advertiser] (Advertiser_id,Advertiser_title) VALUES (3,'ca')
    GO

    -- Insert Ads to HP (Advertiser_id=1)
    INSERT INTO Ad (Ad_id,Ad_title,Ad_url,Ad_Advertiser_id) VALUES (1,'new
    deskjet printer','http://www.hp.com',1)
    INSERT INTO Ad (Ad_id,Ad_title,Ad_url,Ad_Advertiser_id) VALUES (2,'new
    laserjet printer','http://www.hp.com',1)
    GO

    -- Insert Ads to MS (Advertiser_id=2)
    INSERT INTO Ad (Ad_id,Ad_title,Ad_url,Ad_Advertiser_id) VALUES (3,'new
    office xp for windows xp','http://www.microsoft.com',2)
    INSERT INTO Ad (Ad_id,Ad_title,Ad_url,Ad_Advertiser_id) VALUES (4,'new
    windows 2003 server','http://www.microsoft.com',2)
    GO

    -- Insert Ads to CA (Advertiser_id=3)
    INSERT INTO Ad (Ad_id,Ad_title,Ad_url,Ad_Advertiser_id) VALUES (5,'new
    ARCserver for windows','http://www.ca.com',3)
    INSERT INTO Ad (Ad_id,Ad_title,Ad_url,Ad_Advertiser_id) VALUES (6,'New
    CleverPath for windows','http://www.microsoft.com',2)
    GO

    -- Insert AdKeywords (keywords->ads)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (1,'deskjet',1.0)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (1,'printer',2.1)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (2,'laserjet',1.0)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (2,'printer',1.0)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (3,'windows',3)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (3,'xp',1)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (4,'windows',5)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (4,'2003',3)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (5,'arcserver',3)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (5,'windows',3)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (6,'cleverpath',3)
    INSERT INTO [AdKeyword] (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost)
    VALUES (6,'windows',2)
    GO

    -- Now let's try to query
    -- Notice that MS gets 3 ads (incorrect), while it should only get 1 (the
    highest in cost)

    DECLARE q varchar(100)
    SET q='windows'

    SELECT TOP 10 Ad.Ad_id, Ad.Ad_title, Ad.Ad_url, AdK.AdKeyword_cost,
    AdV.Advertiser_title
    FROM Ad
    INNER JOIN AdKeyword AdK ON Ad.Ad_id=AdK.AdKeyword_ad_id
    INNER JOIN Advertiser AdV ON Ad.Ad_Advertiser_id=AdV.Advertiser_id
    WHERE
    Ad_status=1 AND AdK.AdKeyword_status=1
    AND AdK.AdKeyword_Keyword IN (q)
    ORDER BY AdK.AdKeyword_cost DESC

    -- another test (similiar query, with 2 words)
    -- only the distinct 3 topmost ads should be displayed (ms=4, ca=5, hp=1)
    -- as I said, I only need distinct ads and distinct advertisers in result

    SELECT TOP 10 Ad.Ad_id, Ad.Ad_title, Ad.Ad_url, AdK.AdKeyword_cost,
    AdV.Advertiser_title
    FROM Ad
    INNER JOIN AdKeyword AdK ON Ad.Ad_id=AdK.AdKeyword_ad_id
    INNER JOIN Advertiser AdV ON Ad.Ad_Advertiser_id=AdV.Advertiser_id
    WHERE
    Ad_status=1 AND AdK.AdKeyword_status=1
    AND AdK.AdKeyword_Keyword IN ('windows','printer','xp')
    ORDER BY AdK.AdKeyword_cost DESC



    "Jens Süßmeyer" <jsuessmeyer[REJECT_SPAM]web.de> wrote in message
    news:phx.gbl... 


    Jason Guest

  3. #3

    Default Re: Building Adwords mechanisem

    Jason,

    First, I think I may have missed something. You say you want to
    be sure you get distinct ads and distinct advertisers, but isn't it enough
    to make sure you have distinct advertisers? I don't think you need
    to verify non-duplicate ads if the advertisers are distinct.

    Anyway, I thought of that after I came up with an idea (repro
    below), so if only needing distinct advertisers leads to a simpler
    solution, maybe you or someone will think of it - this isn't it.

    Here's my idea:
    Put the results into a table that has unique indexes on Advertiser
    and Ad_id (I'll leave both here, but you only need Advertiser),
    where the indexes are declared with IGNORE_DUP_KEY.

    Then dump your results into this table, which will accept only the
    first row it gets for each advertiser because of the IGNORE_DUP_KEY
    on the index. Then pull the top however many you want from
    this additional table.

    I think that so long as you use TOP -- ORDER BY in the query
    that sends things to this new table, the highest cost ads will be the
    ones that stay, but there is no guarantee that this is the behavior,
    since INSERT INTO .. SELECT doesn't come with a guarantee
    that rows are inserted in any particular order - only a guarantee of
    what rows are (attempted to be) inserted.

    The other things is that you don't know how many rows you
    need to look at before you get a second advertiser, but you can
    decide on that based on your data.

    Here's the repro:


    create table The_ads (
    Ad_id int,
    Ad_title [varchar] (50),
    Ad_url [varchar] (50),
    Ad_Keyword_cost money,
    Ad_Advertiser_title [varchar] (200),
    Ad_Advertiser_id int
    )
    GO

    CREATE UNIQUE INDEX The_ads_id ON The_ads(Ad_id) WITH IGNORE_DUP_KEY
    CREATE UNIQUE INDEX The_ads_Advertiser_id ON The_ads(Ad_Advertiser_id)
    WITH IGNORE_DUP_KEY
    -- second index probably not needed.
    GO

    DECLARE q varchar(100)
    SET q='windows'

    INSERT INTO The_ads
    SELECT TOP 20 Ad.Ad_id, Ad.Ad_title, Ad.Ad_url, AdK.AdKeyword_cost,
    AdV.Advertiser_title, Ad.Ad_Advertiser_id
    FROM Ad
    INNER JOIN AdKeyword AdK ON Ad.Ad_id=AdK.AdKeyword_ad_id
    INNER JOIN Advertiser AdV ON Ad.Ad_Advertiser_id=AdV.Advertiser_id
    WHERE
    Ad_status=1 AND AdK.AdKeyword_status=1
    AND AdK.AdKeyword_Keyword IN (q)
    ORDER BY AdK.AdKeyword_cost DESC
    GO

    SELECT TOP 3 Ad_id, Ad_title, Ad_url, Ad_Keyword_cost, Ad_Advertiser_title
    FROM The_ads
    ORDER BY Ad_Keyword_cost DESC
    GO

    Steve

    Jason Davis wrote:
     
    >
    >
    >
    >[/ref]

    Steve Guest

  4. #4

    Default Re: Building Adwords mechanisem

    One other thing - you may know it, but your idea of
    passing a parameter to the IN clause won't work for more
    than 2 keywords. If you have

    q = 'windows,printers'

    your query will be

    .... IN ('windows,printers')

    and will only match ads about 'windows,printers' (that single
    string). There are lots of ways to handle this requirement, though.
    Try these links:

    http://users.drew.edu/skass/sql/ListToTableFunction.sql.txt
    http://www.algonet.se/~sommar/arrays-in-sql.html

    for examples.

    SK

    Jason Davis wrote:
     
    >
    >
    >
    >[/ref]

    Steve Guest

  5. #5

    Default Re: Building Adwords mechanisem

    Hello Jason, is your problem solved, or should i try to go ahead ?

    Jens Süßmeyer.


    "Steve Kass" <edu> schrieb im Newsbeitrag
    news:phx.gbl... [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref][/ref]
    you 
    > >
    > >
    > >
    > >[/ref]
    >[/ref]


    Jens Guest

  6. #6

    Default Re: Building Adwords mechanisem

    Steve,

    Your idea seems to work, but as this is going to be served on a live, fast
    website, the use of a 4th (#temp) ? table will not work right (I can feel
    it). is there any way of doing it without another table?

    BTW- Distinct Advertister AND Distinct Ad_id is necessary because Ads have
    multiple keywords attached to it. when the user searches (and finds) 2 hits
    for a particular Ad, it is been selected twice.

    Thanks.

     [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref][/ref]
    you 
    > >
    > >
    > >
    > >[/ref]
    >[/ref]


    Jason Guest

  7. #7

    Default Re: Building Adwords mechanisem

    Hi Jens,

    If you can provide your idea, please do.

    "Jens Süßmeyer" <jsuessmeyer[REJECT_SPAM]web.de> wrote in message
    news:phx.gbl... [/ref][/ref]
    the [/ref][/ref]
    [DF_AdKeyword_AdKeyword_status] [/ref][/ref]
    (1,'hp') [/ref][/ref]
    (2,'ms') [/ref][/ref]
    (3,'ca') [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref]
    > (AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost) [/ref][/ref]
    (the [/ref][/ref]
    hp=1) [/ref][/ref]
    result [/ref]
    > you 
    > >[/ref]
    >
    >[/ref]


    Jason Guest

  8. #8

    Default Re: Building Adwords mechanisem

    Jason,

    What I meant by not needing both distinct advertiser and
    distinct ad is that if a particular search technique leads to
    duplicates, you only need to be sure to choose distinct
    advertisers. If the advertisers are distinct, the ads must be.

    Unless I've misunderstood what you are working with, I
    don't see how you can end up with duplicate ads from different
    advertisers.

    Anyway, here's another thought:


    -- Another possibility:
    DECLARE q varchar(100)
    SET q='windows'

    DECLARE
    ad1 int, advertiser1 int

    SELECT TOP 1 ad1 = Ad.Ad_id, advertiser1 = Ad.Ad_Advertiser_id
    FROM Ad
    INNER JOIN AdKeyword AdK ON Ad.Ad_id=AdK.AdKeyword_ad_id
    INNER JOIN Advertiser AdV ON Ad.Ad_Advertiser_id=AdV.Advertiser_id
    WHERE
    Ad_status=1 AND AdK.AdKeyword_status=1
    AND AdK.AdKeyword_Keyword IN (q)
    ORDER BY AdK.AdKeyword_cost DESC

    SELECT TOP 2
    Ad_id, Ad_title, Ad_url, AdK.AdKeyword_cost,
    AdV.Advertiser_title, Ad_Advertiser_id
    FROM Ad
    INNER JOIN AdKeyword AdK ON Ad.Ad_id=AdK.AdKeyword_ad_id
    INNER JOIN Advertiser AdV ON Ad.Ad_Advertiser_id=AdV.Advertiser_id
    WHERE
    Ad_status=1 AND AdK.AdKeyword_status=1
    AND AdK.AdKeyword_Keyword IN (q)
    AND (Ad_id = ad1 or Ad.Ad_Advertiser_id <> advertiser1)
    ORDER BY AdK.AdKeyword_cost DESC
    GO

    Steve

    Jason Davis wrote:
     [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >
    > [/ref]
    >(AdKeyword_ad_id,AdKeyword_keyword,AdKeyword_cost )
    >

    >>
    >> 
    >>
    >> [/ref]
    >you
    >
    > [/ref]
    >
    >
    >
    >[/ref]

    Steve Guest

  9. #9

    Default Re: Building Adwords mechanisem

    Sorry for not responing, i will try to solve the problem and answer
    you, as soon as i get home ;-)

    Jens Süßmeyer.
    Jens Guest

Similar Threads

  1. Building PHP 4.3.3 with GD 2.0.11
    By Slavik in forum PHP Development
    Replies: 1
    Last Post: November 21st, 02:34 AM
  2. PHP building with GD
    By none in forum PHP Development
    Replies: 2
    Last Post: November 3rd, 11:23 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139