sabecs
asked on
PHP or MySQL query to combine and total 2 tables.
Hi,
I have 2 tables as shown below. One table contains sponsored link keywords clicked to reach my site.
How can I group them by keyword and add cost for a certain date period, and also include income(advert_cost) from my 2nd table when keyword match, so that I can then calculate the cost per converions.
Basically I need to be able to compare the money spent on keywords with any income earned from those keywords.
CREATE TABLE `sponsored_links` (
`id` int(20) NOT NULL auto_increment,
`company` varchar(20) default NULL,
`website` varchar(4) default NULL,
`click_cost` tinyint(4) default NULL,
`keywords` varchar(120) default NULL,
`ondate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2647 ;
--
-- Dumping data for table `sponsored_links`
--
INSERT INTO `sponsored_links` VALUES (1, 'Google', 'OP', 35, '"real estate"', '2007-03-23 21:27:53');
INSERT INTO `sponsored_links` VALUES (2, 'Google', 'OP', 32, '"Sell House Privately"', '2007-03-23 22:03:34');
INSERT INTO `sponsored_links` VALUES (3, 'Google', 'OP', 32, '"how to sell real estate"', '2007-03-23 22:03:44');
INSERT INTO `sponsored_links` VALUES (4, 'Google', 'OP', 32, '"real estate"', '2007-03-23 22:03:54');
INSERT INTO `sponsored_links` VALUES (5, 'Google', 'OP', 65, '"real estate virtual tour"', '2007-03-23 22:03:57');
INSERT INTO `sponsored_links` VALUES (6, 'Google', 'OP', 65, '"real estate perth"', '2007-03-23 22:04:04');
INSERT INTO `sponsored_links` VALUES (7, 'Google', 'OP', 32, '"sell house online"', '2007-03-23 22:04:11');
INSERT INTO `sponsored_links` VALUES (8, 'Google', 'OP', 32, '"sell house trade"', '2007-03-23 22:04:15');
INSERT INTO `sponsored_links` VALUES (9, 'Google', 'OP', 65, '"need to sell my home fast"', '2007-03-23 22:04:21');
INSERT INTO `sponsored_links` VALUES (10, 'Google', 'OP', 65, '"for sale by owner realty"', '2007-03-23 22:04:35');
CREATE TABLE `sponsored_conversions` (
`id` int(20) NOT NULL auto_increment,
`company` varchar(20) default NULL,
`website` varchar(4) default NULL,
`click_cost` tinyint(4) default NULL,
`keywords` varchar(120) default NULL,
`advert_cost` varchar(8) default NULL,
`ondate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `sponsored_conversions`
--
INSERT INTO `sponsored_conversions` VALUES (1, 'Google', 'OP', 65, '"need to sell my home fast"', '66.00', '2007-03-28 10:45:42');
INSERT INTO `sponsored_conversions` VALUES (2, 'Google', 'OP', 32, '"4ealestate"', '66.00', '2007-03-28 10:45:43');
INSERT INTO `sponsored_conversions` VALUES (3, 'Yahoo', 'REW', 45, '"real estate"', '132.00', '2007-03-30 03:22:17');
INSERT INTO `sponsored_conversions` VALUES (4, 'Yahoo', 'REW', 45, '"real estate"', '132.00', '2007-03-30 03:22:20');
I have 2 tables as shown below. One table contains sponsored link keywords clicked to reach my site.
How can I group them by keyword and add cost for a certain date period, and also include income(advert_cost) from my 2nd table when keyword match, so that I can then calculate the cost per converions.
Basically I need to be able to compare the money spent on keywords with any income earned from those keywords.
CREATE TABLE `sponsored_links` (
`id` int(20) NOT NULL auto_increment,
`company` varchar(20) default NULL,
`website` varchar(4) default NULL,
`click_cost` tinyint(4) default NULL,
`keywords` varchar(120) default NULL,
`ondate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2647 ;
--
-- Dumping data for table `sponsored_links`
--
INSERT INTO `sponsored_links` VALUES (1, 'Google', 'OP', 35, '"real estate"', '2007-03-23 21:27:53');
INSERT INTO `sponsored_links` VALUES (2, 'Google', 'OP', 32, '"Sell House Privately"', '2007-03-23 22:03:34');
INSERT INTO `sponsored_links` VALUES (3, 'Google', 'OP', 32, '"how to sell real estate"', '2007-03-23 22:03:44');
INSERT INTO `sponsored_links` VALUES (4, 'Google', 'OP', 32, '"real estate"', '2007-03-23 22:03:54');
INSERT INTO `sponsored_links` VALUES (5, 'Google', 'OP', 65, '"real estate virtual tour"', '2007-03-23 22:03:57');
INSERT INTO `sponsored_links` VALUES (6, 'Google', 'OP', 65, '"real estate perth"', '2007-03-23 22:04:04');
INSERT INTO `sponsored_links` VALUES (7, 'Google', 'OP', 32, '"sell house online"', '2007-03-23 22:04:11');
INSERT INTO `sponsored_links` VALUES (8, 'Google', 'OP', 32, '"sell house trade"', '2007-03-23 22:04:15');
INSERT INTO `sponsored_links` VALUES (9, 'Google', 'OP', 65, '"need to sell my home fast"', '2007-03-23 22:04:21');
INSERT INTO `sponsored_links` VALUES (10, 'Google', 'OP', 65, '"for sale by owner realty"', '2007-03-23 22:04:35');
CREATE TABLE `sponsored_conversions` (
`id` int(20) NOT NULL auto_increment,
`company` varchar(20) default NULL,
`website` varchar(4) default NULL,
`click_cost` tinyint(4) default NULL,
`keywords` varchar(120) default NULL,
`advert_cost` varchar(8) default NULL,
`ondate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `sponsored_conversions`
--
INSERT INTO `sponsored_conversions` VALUES (1, 'Google', 'OP', 65, '"need to sell my home fast"', '66.00', '2007-03-28 10:45:42');
INSERT INTO `sponsored_conversions` VALUES (2, 'Google', 'OP', 32, '"4ealestate"', '66.00', '2007-03-28 10:45:43');
INSERT INTO `sponsored_conversions` VALUES (3, 'Yahoo', 'REW', 45, '"real estate"', '132.00', '2007-03-30 03:22:17');
INSERT INTO `sponsored_conversions` VALUES (4, 'Yahoo', 'REW', 45, '"real estate"', '132.00', '2007-03-30 03:22:20');
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now that I have combined the 2 tables what query can I use to provide total costs for each keyword(or phrase) by each company and also add any advert_costs for those keywords which can then provide a cost per conversion for those keywords ( cost per conversion would be equal to the total of advert_cost divided by the total of click_costs for a particular keyword). I hope this makes sense.
That is where splitting the keyvords-prhase into single words comes in handy. >Because even thoug the sequence is lost you also gain some considerable advantages.
1) I want a house, a large one
2) I want a large house
3) I want a house
4) I want a large airy house
5) I want an airy large house
Now if you look for 'I want a house' you should find all of those
If you look for 'I want an airy house' you should find 4 and 5
if you look for 'I want a large house' you should find 1, 2, 4 and 5
If you just search for the full phrase you find much less, and omit all the other ways that desire could be phrased.
so to find the 'best fit' entries in the big table you use the little table
with the keyword phrase "I want an airy house" it goes something like this
$searchphrase = "I want an airy house";
$arrayWhere = split( " ", $searchphrase ); // 1 word in each cell
$phreaseWhere = "WHERE wordkey = '" .join( "' OR wordkey = '", $arrayWhere ) ."'";
echo $phreaseWhere; // DEBUG line so you can se what that makes. remove it later.
$sqlCommand = "
SELECT sponsored_something.*
FROM ( SELECT id, count(*) AS c
FROM keys
$phreaseWhere
GROUP BY id
ORDER BY c DESC
) AS subsel
JOIN sponsored_something ON subsel.id = sponsored_something.id
LIMIT 0,10
";
That should give you the ten rows whose keywords have the greatest nr of words in common with the searchphrase
There are several improvements possible. fx it is a good idea to remove punctuation in the phrase, and also to remove the 'dosent matter' words like 'a', 'an', 'I', 'the'.
still all that is refinement; the basic idea is abowe.
regards JakobA
1) I want a house, a large one
2) I want a large house
3) I want a house
4) I want a large airy house
5) I want an airy large house
Now if you look for 'I want a house' you should find all of those
If you look for 'I want an airy house' you should find 4 and 5
if you look for 'I want a large house' you should find 1, 2, 4 and 5
If you just search for the full phrase you find much less, and omit all the other ways that desire could be phrased.
so to find the 'best fit' entries in the big table you use the little table
with the keyword phrase "I want an airy house" it goes something like this
$searchphrase = "I want an airy house";
$arrayWhere = split( " ", $searchphrase ); // 1 word in each cell
$phreaseWhere = "WHERE wordkey = '" .join( "' OR wordkey = '", $arrayWhere ) ."'";
echo $phreaseWhere; // DEBUG line so you can se what that makes. remove it later.
$sqlCommand = "
SELECT sponsored_something.*
FROM ( SELECT id, count(*) AS c
FROM keys
$phreaseWhere
GROUP BY id
ORDER BY c DESC
) AS subsel
JOIN sponsored_something ON subsel.id = sponsored_something.id
LIMIT 0,10
";
That should give you the ten rows whose keywords have the greatest nr of words in common with the searchphrase
There are several improvements possible. fx it is a good idea to remove punctuation in the phrase, and also to remove the 'dosent matter' words like 'a', 'an', 'I', 'the'.
still all that is refinement; the basic idea is abowe.
regards JakobA
The abow may or may not be a relevant response. I am quite confused by these costs you tak about.
cost to you ? practically none, CPU power is cheap
cost to the sponsor ? You will need to explain how that is calculated, mostly I would go with "what the market will bear"
cost pr word ? as with CPU power, words are cheap ;-))
cost to you ? practically none, CPU power is cheap
cost to the sponsor ? You will need to explain how that is calculated, mostly I would go with "what the market will bear"
cost pr word ? as with CPU power, words are cheap ;-))
ASKER
Thanks very much for your help JakobA, you have clarified quite a few things for me, I should now be able to achieve what I require.
ASKER
type enum( 'link', 'conversion' ) not null as you suggest, this should make things a lot easier for me.
I am a bit confused over the keywords table though, "need to sell my home fast" for example is really a phrase that needs to be entered in google and not individual keywords, that is they have to be entered in the same order for my ad to appear. What benefit would a seperate keyword table have?
Thanks again, as I am a newbie with MySQL your comments are greatly appreciated.