Commerce Server Marketing–Ad Validation

I am working on a Microsoft Commerce Server 2009 site where we are using the Marketing Manager to serve up ads to a custom Silverlight application. This all works great except that there does not seem to be much in the way of reporting available to make sure that the ads have valid data.

I threw this set of queries together to provide a rolled up view of one ad per row.
I’m sure there are better ways of getting this data, but I haven’t seen it so far. It runs pretty fast.

Ymmv.

Code Snippet

1. USE [<Your Database Here>_marketing];
2.  
3. DECLARE @i_pg_id integer,@u_pg_tag nvarchar(25),@u_pg_description nvarchar(255)
4. DECLARE @u_camp_name nvarchar(50), @i_aditem_id integer, @u_campitem_name nvarchar(50),
5.     @b_campitem_active bit
6. DECLARE @i_aditem_weight integer, @dt_campitem_start datetime, @dt_campitem_end datetime,
7.     @u_size_name nvarchar(50)
8. DECLARE @display_type nvarchar(50), @imageheight integer, @imagewidth integer,
9.     @imageurl nvarchar(1000), @alttext nvarchar(1000), @targeturl nvarchar(1000),
10.     @textcontent nvarchar(1000), @headline nvarchar(1000)
11. DECLARE @u_ct_name nvarchar(50), @u_cp_name nvarchar(50), @text_cpv_value nvarchar(1000)
12. DECLARE @u_expr_name nvarchar(100), @ExpressionScope nvarchar(10), @TargetAction nvarchar(10)
13. DECLARE @ExpressionCount integer, @Expression1 nvarchar(120), @Expression2 nvarchar(120),
14.     @Expression3 nvarchar(120), @Expression4 nvarchar(120)
15.  
16. DECLARE @Results TABLE
17. (
18.     i_pg_id integer,
19.     u_pg_tag nvarchar(25),
20.     u_pg_description nvarchar(255),
21.     u_camp_name nvarchar(50),
22.     i_aditem_id integer,
23.     u_campitem_name nvarchar(50),
24.     b_campitem_active bit,
25.     i_aditem_weight integer,
26.     dt_campitem_start datetime,
27.     dt_campitem_end datetime,
28.     u_size_name nvarchar(50),
29.     display_type nvarchar(50),
30.     imageheight integer,
31.     imagewidth integer,
32.     imageurl nvarchar(1000),
33.     alttext nvarchar(1000),
34.     targeturl nvarchar(1000),
35.     textcontent nvarchar(1000),
36.     headline nvarchar(1000),
37.     Expression1 nvarchar(120),
38.     Expression2 nvarchar(120),
39.     Expression3 nvarchar(120),
40.     Expression4 nvarchar(120)
41. )
42.  
43. DECLARE curPageGroup CURSOR READ_ONLY FOR
44. SELECT [i_pg_id],[u_pg_tag],[u_pg_description]
45.     FROM [mktg_page_group]
46.     ORDER BY [u_pg_tag]
47.     OPEN curPageGroup
48.  
49. FETCH NEXT FROM curPageGroup INTO @i_pg_id,@u_pg_tag,@u_pg_description
50. WHILE (@@FETCH_STATUS = 0)
51.     BEGIN
52.  
53.     DECLARE curAd CURSOR READ_ONLY FOR
54.     SELECT mc.[u_camp_name]
55.         ,ad.[i_aditem_id]
56.         ,ci.[u_campitem_name]
57.         ,ci.[b_campitem_active]
58.         ,ad.[i_aditem_weight]
59.         ,ci.[dt_campitem_start]
60.         ,ci.[dt_campitem_end]
61.         ,ms.[u_size_name]
62.     FROM [mktg_ad_item] ad inner join [mktg_campaign_item] ci on ad.[i_campitem_id]=ci.[i_campitem_id]
63.         left outer join [mktg_campaign] mc on mc.[i_camp_id] = ci.[i_camp_id]
64.         inner join [mktg_creative] mk on ci.[i_creative_id] = mk.[i_creative_id]
65.         inner join [mktg_creative_size] ms on mk.[i_creative_size_id] = ms.[i_creative_size_id]
66.         inner join [mktg_page_group_xref] px on ci.[i_campitem_id] = px.[i_campitem_id]
67.         inner join [mktg_page_group] pg on px.[i_pg_id] = pg.[i_pg_id]
68.     WHERE ci.[b_campitem_deleted] = 0
69.     and pg.i_pg_id = @i_pg_id
70.     ORDER BY ad.[i_aditem_id]
71.     OPEN curAd
72.  
73.     FETCH NEXT FROM curAd INTO @u_camp_name, @i_aditem_id, @u_campitem_name, @b_campitem_active,
74.         @i_aditem_weight, @dt_campitem_start, @dt_campitem_end, @u_size_name  
75.     WHILE (@@FETCH_STATUS = 0)
76.     BEGIN
77.  
78.         SELECT @display_type = '',
79.             @imageheight = 0,
80.             @imagewidth = 0,
81.             @imageurl = '',
82.             @alttext = '',
83.             @targeturl = '',
84.             @textcontent = '',
85.             @headline = ''
86.  
87.         DECLARE curValues CURSOR READ_ONLY FOR
88.             SELECT ct.[u_ct_name]
89.                 ,cp.[u_cp_name]
90.                 ,cv.[text_cpv_value]
91.             FROM [mktg_ad_item] ad inner join [mktg_campaign_item] ci on ad.[i_campitem_id]=ci.[i_campitem_id]
92.                 left outer join [mktg_campaign] mc on mc.[i_camp_id] = ci.[i_camp_id]
93.                 inner join [mktg_creative] mk on ci.[i_creative_id] = mk.[i_creative_id]
94.                 inner join [mktg_creative_type] ct on mk.[i_creative_type_id] = ct.[i_creative_type_id]
95.                 inner join [mktg_creative_property] cp on mk.[i_creative_type_id] = cp.[i_creative_type_id]
96.                 inner join [mktg_creative_property_value] cv on cp.[i_cp_id] = cv.[i_cp_id] and cv.[i_creative_id] = ci.[i_creative_id]
97.             WHERE ad.[i_aditem_id] = @i_aditem_id
98.             ORDER BY ad.[i_aditem_id] ,cp.[u_cp_label]
99.             OPEN curValues
100.  
101.         FETCH NEXT FROM curValues INTO @u_ct_name , @u_cp_name , @text_cpv_value
102.         WHILE (@@FETCH_STATUS = 0)
103.         BEGIN
104.             SET @display_type = @u_ct_name
105.             IF (@u_cp_name = 'AltText') SET @alttext = @text_cpv_value            
106.             IF (@u_cp_name = 'ClickURL') SET @targeturl = @text_cpv_value
107.             IF (@u_cp_name = 'clickURL') SET @targeturl = @text_cpv_value
108.             IF (@u_cp_name = 'ExpandedContent') SET @textcontent = @text_cpv_value
109.             IF (@u_cp_name = 'Headline') SET @headline = @text_cpv_value
110.             IF (@u_cp_name = 'Height') SET @imageheight = @text_cpv_value
111.             IF (@u_cp_name = 'ImageURL') SET @imageurl = @text_cpv_value
112.             IF (@u_cp_name = 'imgURL') SET @imageurl = @text_cpv_value
113.             IF (@u_cp_name = 'TargetURL') SET @targeturl = @text_cpv_value
114.             IF (@u_cp_name = 'RedirectUrl') SET @targeturl = @text_cpv_value
115.             IF (@u_cp_name = 'text') SET @textcontent = @text_cpv_value
116.             IF (@u_cp_name = 'TextContent') SET @textcontent = @text_cpv_value
117.             IF (@u_cp_name = 'Width') SET @imagewidth = @text_cpv_value            
118.  
119.             FETCH NEXT FROM curValues INTO @u_ct_name , @u_cp_name , @text_cpv_value
120.         END
121.         CLOSE curValues
122.         DEALLOCATE curValues
123.  
124.         SELECT @ExpressionCount = 0,
125.             @Expression1 = '',
126.             @Expression2 = '',
127.             @Expression3 = '',
128.             @Expression4 = ''
129.  
130.         DECLARE curExpressions CURSOR READ_ONLY FOR
131.         SELECT me.[u_expr_name]
132.             ,CASE me.[b_expr_local]
133.                 WHEN 0 THEN 'Global'
134.                 WHEN 1 THEN 'Local'
135.             END AS [Expression Scope]
136.             ,CASE mt.[i_target_action]
137.                 WHEN 1 THEN 'Target'
138.                 WHEN 2 THEN 'Require'
139.                 WHEN 3 THEN 'Exclude'
140.                 WHEN 4 THEN 'Sponsor'
141.             END AS [Target Action]
142.         FROM [mktg_ad_item] ad inner join [mktg_campaign_item] ci on ad.[i_campitem_id]=ci.[i_campitem_id]
143.             left outer join [mktg_campaign] mc on mc.[i_camp_id] = ci.[i_camp_id]
144.             inner join [mktg_creative] mk on ci.[i_creative_id] = mk.[i_creative_id]
145.             inner join [mktg_target] mt on mt.[i_campitem_id] = ci.[i_campitem_id]
146.             inner join [mktg_expression] me on mt.[i_target_expr_id] = me.[i_expr_id]
147.             WHERE ad.[i_aditem_id] = @i_aditem_id
148.         OPEN curExpressions
149.  
150.         FETCH NEXT FROM curExpressions INTO @u_expr_name, @ExpressionScope, @TargetAction
151.         WHILE (@@FETCH_STATUS = 0)
152.         BEGIN
153.             SET @ExpressionCount = @ExpressionCount + 1
154.             IF (@ExpressionCount = 1) SET @Expression1 = @u_expr_name +' / '+@ExpressionScope+' / '+@TargetAction
155.             IF (@ExpressionCount = 2) SET @Expression2 = @u_expr_name +' / '+@ExpressionScope+' / '+@TargetAction
156.             IF (@ExpressionCount = 3) SET @Expression3 = @u_expr_name +' / '+@ExpressionScope+' / '+@TargetAction
157.             IF (@ExpressionCount = 4) SET @Expression4 = @u_expr_name +' / '+@ExpressionScope+' / '+@TargetAction
158.             IF (@ExpressionCount > 4) SET @Expression4 = @Expression4 + '* '
159.  
160.             FETCH NEXT FROM curExpressions INTO @u_expr_name, @ExpressionScope, @TargetAction
161.         END
162.         CLOSE curExpressions
163.         DEALLOCATE curExpressions        
164.  
165.         INSERT INTO @Results (i_pg_id, u_pg_tag, u_pg_description, u_camp_name, i_aditem_id, u_campitem_name,
166.             b_campitem_active, i_aditem_weight, dt_campitem_start, dt_campitem_end, u_size_name,
167.             display_type, imageheight, imagewidth, imageurl, alttext, targeturl, textcontent,
168.             headline, Expression1 , Expression2 , Expression3 , Expression4  )
169.         VALUES (@i_pg_id, @u_pg_tag, @u_pg_description, @u_camp_name, @i_aditem_id, @u_campitem_name,
170.             @b_campitem_active, @i_aditem_weight, @dt_campitem_start, @dt_campitem_end, @u_size_name,
171.             @display_type, @imageheight, @imagewidth, @imageurl, @alttext, @targeturl, @textcontent,
172.             @headline, @Expression1 , @Expression2 , @Expression3 , @Expression4 )    
173.  
174.         FETCH NEXT FROM curAd INTO @u_camp_name, @i_aditem_id, @u_campitem_name, @b_campitem_active,
175.             @i_aditem_weight, @dt_campitem_start, @dt_campitem_end, @u_size_name
176.     END
177.     CLOSE curAd
178.     DEALLOCATE curAd
179.  
180.     FETCH NEXT FROM curPageGroup INTO @i_pg_id,@u_pg_tag,@u_pg_description
181. END
182.  
183. CLOSE curPageGroup
184. DEALLOCATE curPageGroup
185.  
186. SELECT u_pg_tag AS [Page Group],
187.     u_pg_description AS [Page Description],
188.     Expression1 AS [Target Exp 1],
189.     Expression2 AS [Target Exp 2],
190.     Expression3 AS [Target Exp 3],
191.     Expression4 AS [Target Exp 4],
192.     i_aditem_id AS [Ad Id],
193.     CASE b_campitem_active
194.         WHEN 1 THEN 'Active'
195.         WHEN 0 THEN 'Inactive'
196.     END AS [Status],
197.     u_camp_name AS [Campaign],
198.     u_campitem_name AS [Ad Name],
199.     i_aditem_weight AS [Weight],
200.     CONVERT(char(10),dt_campitem_start,126) AS [Start Date],
201.     CONVERT(char(10),dt_campitem_end,126) AS [End Date],
202.     u_size_name AS [Display Size],
203.     display_type AS [Display Type],
204.     imagewidth AS [Image Width],
205.     imageheight AS [Image Height],
206.     imageurl AS [Image URL],
207.     alttext AS [Alt Text],
208.     targeturl AS [Target URL],
209.     textcontent AS [Text Content],
210.     headline AS [Headline]    
211. FROM @Results
212. ORDER BY [Page Group],[Ad Name]
213.  
214. GO