mercredi 15 juin 2016

variable value in cfchart

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