This final table shows reported earnings for the affiliate arrangement drawn on a quarterly basis from the affiliate network or whatever reporting tools were provided by the merchant. The "imprs" comlumn shows reported impressions. I do not display the null bit with each ad displa; so this figure is inaccurate.
All of the ambitious plans I had for the Community Color sites got hung up on the inability to generate income. Hiring a minimum clerk in the US requires some $40k in income. Hiring a programmer would requires $100k. The site is lucky to break $1k a month. In an attempt to solve the income problem, I decided to categorize the advertisers on the site to determine where I should concentrate to make an income.
| Category | Active Count |
Active Hits |
Active Revenue |
Inactive Count |
Inactive Hits |
Inactive Revenue |
Total Count |
Total Hits |
Total Revenu |
'.$activeHits.' | '.number_format($activeComm,2); echo ' | '.$termCnt.' | '.$termHits.' | '.number_format($termComm,2); echo ' | '.($activeCnt + $termCnt).' | '.($termHits + $activeHits).' | '.number_format($termComm + $activeComm,2).' | '; } $cNm = ($la_cat > 0) ? ''.$category_nm.'' : $category_nm; if ($fob_pg != '') $cNm .= ' (more) '; // set the variabls for the next category. $rColor = (($hCnt++ %2) == 0)? 'EEE' : 'CCC'; $holdStr = '
|---|---|---|---|---|---|---|---|---|---|
| '.$cNm.' | '; $holdCat = $category_id; $activeCnt = 0; $activeHits = 0; $activeComm = 0; $termCnt = 0; $termHits = 0; $termComm = 0; } if ($active_cd == 'N') { $activeCnt = $cnt; $activeHits = $tot_hits; $activeComm = $tot_commissions; } else { $termCnt = $cnt; $termHits = $tot_hits; $termComm = $tot_commissions; } } } echo ' '.$holdStr; echo $activeCnt.' | '.$activeHits.' | '.number_format($activeComm,2); echo ' | '.$termCnt.' | '.$termHits.' | '.number_format($termComm,2); echo ' | '.($activeCnt + $termCnt).' | '.($termHits + $activeHits).' | '.number_format($termComm + $activeComm,2).' |
This reports shows traffic to Share-a-Sale Merchants generating errors. If a merchant falls behind in their payments, SaS sends the click back to the referrer. SaS implemented these procedure to encourage merchants to keep up with their payments. This report shows the number of merchant errors in the last 60 days.
\n"; sqlTable("Merchant Error Report",'',"SELECT concat('',m.merchant_nm,'') Merchant, count(*) Hits, date_format(min(ts),'%Y-%m-%e') First, date_format(max(ts),'%Y-%m-%e') Last FROM Merchant m, Merchant_Error e WHERE e.merchant_id = m.merchant_id and e.ts > DATE_SUB(CURDATE(),INTERVAL 60 DAY) GROUP BY e.merchant_id"); } function getPages($merchant_id) { $rv=''; $sql = "SELECT p.community_id, p.pg, p.hits, c.community_nm, c.url FROM Merchant_Page p, Community c WHERE p.merchant_id = $merchant_id and p.community_id = c.community_id"; $tHits = 0; if ($result = mysql_query($sql)) { while (list($community_id, $pg, $hits, $community_nm, $url) = mysql_fetch_row($result)) { if ($rv != '') $rv .= "The page section shows summary information on merchants. The purpose of this page is to help me keep track of advertisers on the directories I created. It includes links to different places where ads appear. The earnings report show earnings by month. The imprs column is inaccurate for most merchants, the hits and revenue is generally more accurate. The aFOB table shows if the site appears in a Fountain of Bargains.
"; sqlTable("Earnings History",'',"SELECT concat(i.year,'-',floor((i.month + 2)/3)) 'Month', sum(impressions) '+Imprs', sum(h.hits) '+Hits', sum(h.actions) '+Actions', sum(h.sales_amt) '+Sales', sum(h.revenue_amt) '+Commission' FROM Rev_Import i, Rev_Hist h WHERE h.merchant_id = $merchant_id and h.import_id = i.import_id GROUP BY i.year, floor((i.month + 2)/3) ORDER BY i.year DESC, i.month DESC"); sqlTable("A Fountain of Bargains",'',"SELECT concat('' ,c.category_nm,'') Page, f.start_dt 'Start', f.end_dt 'End' FROM FOB_Listing f, FOB_Category c WHERE f.merchant_id = $merchant_id and f.category_id = c.category_id and (f.end_dt > now() or f.end_dt='0000-00-00' or f.end_dt is null)"); } function merchantInfo($m) { $merchant_id = strval($m) + 0; if ($merchant_id > 0) { list($merchant_id,$merchant_nm, $prog, $weight, $display_yn, $foreign_id, $ticker, $city, $state_cd, $url, $ad_url) = sqlRow("SELECT merchant_id,merchant_nm, prog, weight, display_yn, foreign_id, ticker, city, state_cd, url, ad_url FROM Merchant WHERE merchant_id = $merchant_id"); showMerchant($merchant_id,$merchant_nm, $prog, $weight, $display_yn, $foreign_id, $ticker, $city, $state_cd, $url, $ad_url); } else { echo "invalid merchant id $merchant_id"; } } function monthInfo($showLinks) { $ym = $_REQUEST['ym']; $year = strval(substr($ym,0,4)); $month = strval(substr($ym,4)); displayHead('Quarterly Income - '.$ym); echo "This report shows the quarterly income for the Community Color family of web sites. The report includes the number of hits sent to advertisers and reported commissions. Paid commissions is lower. The costs for my ISP, hosting, phone, etc., come to $600 a quarter. I would need $5,000 a quarter to hire one minimum wage clerk. The first row actually shows all the income prior to 2003.
"; if ($year > 0 and $month > 0 and $showLinks == 'Y') { $sql = "SELECT concat('',m.merchant_nm,'') Merchant, sum(h.impressions) '+Imprs', sum(h.hits) '+Hits', sum(h.revenue_amt) '+Total', round(sum(revenue_amt) * 100 / sum( hits ),2) 'EPC' FROM Rev_Import i, Rev_Hist h, Merchant m WHERE h.merchant_id = m.merchant_id and i.import_id = h.import_id and i.year = $year and i.month = $month GROUP BY h.merchant_id ORDER BY '+Hits' DESC"; sqlTable("Detail",'',$sql); } else { $mLink = "concat(i.year,' Q',ceil(i.month/3))" ; $sql = "SELECT $mLink Month, count(distinct h.merchant_id) Merchants, sum(h.hits) '+Hits', sum(h.revenue_amt) '+Earnings', round(sum(h.revenue_amt) * 100 / sum( h.hits ),2) 'EPC' FROM Rev_Import i, Rev_Hist h WHERE i.import_id = h.import_id GROUP BY i.year, ceil(i.month/3) ORDER BY i.year DESC, i.month DESC"; // echo "$sql"; sqlTable("Summary by Quarter",'',$sql); } } function getPct($x,$tot) { return ($tot > 0)? round(100*$x/$tot,3).'%' : $x.'/'.$tot; } function distRow($def,$bCnt,$bHits,$bEarn,$totCnt,$totHits,$totEarnings) { echo "
This report shows the summary information by merchant. The Distribution Report at the bottom of
the page breaks down the data into buckets.