Details/Background: I needed to find the cost per click, the cost per registration, and the cost per license. I am finding this info out by dividing the budget for each month by the amount of clicks, registrations, or licenses of the same month. I came up with the code below as my solution, but there are some problems with it.
Question: I need the variables created in the cfloop function to be charted in a cfchart function, but I do not know how to set the chart up to chart them correctly. What is the simplest, yet effective way to go about it?
Queries:
<cfquery name="AdBudget" datasource="#dsn#">
Select AdMonth AS DATE, SUM(AdBudget) AS Budgeting
FROM AdBudget
WHERE AdYear = '2016'
GROUP BY AdMonth
ORDER BY AdMonth
</cfquery>
<!--- Cost per Click --->
<cfquery name="Clicks" datasource="#dsn#">
Select AdClick.AdMonth AS DATE, SUM(AdClick.AdClicks) AS Click
FROM AdClick, AdReport
WHERE AdClick.AdYear = '2016'
AND AdClick.AdSourceID = AdReport.AdSourceID
AND AdReport.AdSourceID != 4
GROUP BY AdClick.AdMonth
ORDER BY AdClick.AdMonth
</cfquery>
<!--- Cost per Registration --->
<cfquery name="Registrations" datasource="#dsn#">
Select SUM(Conversions) AS Conver, DATEPART(month,AdReport.ReportDate) AS DATE,
DATEPART(month, AdReport.ReportDate) AS DATE
FROM AdReport
WHERE DATEPART(year, AdReport.ReportDate) = 2016
GROUP BY DATEPART(MONTH, AdReport.ReportDate)
ORDER BY DATEPART(MONTH, AdReport.ReportDate)
</cfquery>
<!--- Cost per License --->
<cfquery name="Licenses" datasource="#dsn#">
SELECT SUM(Licenses) AS License, AdMonth AS Date
FROM AdBudget, AdReport
WHERE AdBudget.AdSourceID = AdReport.AdSourceID
AND AdBudget.AdSourceID!= '4'
AND AdYear = 2016
GROUP BY AdMonth
ORDER BY AdMonth
</cfquery>
Code:
<cfloop index = "i" from = "1" to = "#AdBudget.RecordCount#">
<cfset Clicks.Click[i] = AdBudget.Budgeting/Clicks.Click[i]>
<cfset Registrations.Conver[i] = AdBudget.Budgeting/Registrations.Conver[i]>
<cfset Licenses.License[i] = AdBudget.Budgeting/Licenses.License[i]>
</cfloop>
<!--- Bar graph, from Query of Queries --->
<cfchart>
<cfchartseries type="curve"
seriescolor="##5283DA"
serieslabel="Cost per Clicks"
<cfchartdata item="1" value="#Click#">
</cfchart>
</cfchart>
Data:
Sample Data added, disregard the sourceID and other IDs in the table.
AdBudgetID AdBudget AdMonth AdSourceID AdYear
1 7663 1 1 2016
2 20301 2 1 2016
3 5555 1 2 2016
4 16442 2 2 2016
5 1706 1 3 2016
6 4841 2 3 2016
7 11384 3 1 2016
8 23726 3 2 2016
9 9653 3 3 2016
13 17557.98 5 1 2016
14 25685.72 5 2 2016'
AdClickID AdClicks AdMonth AdSourceID AdYear
1 2229 1 1 2016
2 1803 1 2 2016
3 371 1 3 2016
4 4940 2 1 2016
5 5855 2 2 2016
6 673 2 3 2016
7 2374 3 1 2016
8 12913 3 2 2016
9 1400 3 3 2016
13 2374 4 1 2016
14 10272 4 2 2016
AdReportID ReportDate AdSourceID Clicks Conversions
2430 2016-03-27 1 1 1
2431 2016-03-27 2 5 0
2432 2016-03-27 3 1 0
2433 2016-03-27 5 24 0
2434 2016-03-27 6 0 0
2435 2016-03-27 6 0 0
2436 2016-03-27 4 0 1
2437 2016-03-26 1 2 0
2438 2016-03-26 2 9 1
2439 2016-03-26 3 11 0
2440 2016-03-26 5 25 2
Aucun commentaire:
Enregistrer un commentaire