· 8 years ago · Jul 19, 2018, 12:22 PM
1USE [OnLinePromotionsPlatform_Reporting]
2GO
3/****** Object: StoredProcedure [dbo].[proc_REPORTING_GetCombinedAnalytics] Script Date: 19/7/2018 2:14:18 μμ ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8-- =============================================
9-- Author: Lampros Mastorakis
10-- Create date: 2018-07-12
11-- Description: Reporting Procedure to return combined data for the "whole report"
12-- @RangeType :
13-- 1: Hourly
14-- 2: Daily
15-- 3: Monthly
16-- Example: EXEC OnLinePromotionsPlatform_Reporting.dbo.proc_REPORTING_GetCombinedAnalytics '4B055DB2-D903-49C7-A3F8-7D38B117D29A','10;24','2018-07-13 00:00','2018-07-16 23:59','2','cosmote;wind','Germany;Greece',1,'iOS;Android'
17-- Example: EXEC OnLinePromotionsPlatform_Reporting.dbo.proc_REPORTING_GetCombinedAnalytics '57d234a7-7d09-4ceb-866d-07da2e2113e0','10;24;21','2018-05-01 00:00','2018-05-22 23:59','2','cosmote;wind','Germany;Greece',1,'iOS;Android'
18-- Example: EXEC OnLinePromotionsPlatform_Reporting.dbo.proc_REPORTING_GetCombinedAnalytics '4B055DB2-D903-49C7-A3F8-7D38B117D29A','10;24','2018-07-13 00:00','2018-07-16 23:59','2'
19-- =============================================
20
21ALTER PROCEDURE [dbo].[proc_REPORTING_GetCombinedAnalytics]
22 @ListOfActionIds NVARCHAR(MAX),
23 @ListOfMetrics NVARCHAR(MAX),
24 @DateFrom DATETIME,
25 @DateTo DATETIME,
26 @RangeType INT=1,
27 --
28 @Carriers NVARCHAR(100)='',
29 @Countries NVARCHAR(100)='',
30 @IsMobile BIT=1,
31 @AdvertisedDeviceOS NVARCHAR(100)=''
32AS
33BEGIN
34 SET NOCOUNT ON;
35
36 --######################
37 --### Declarations
38 --######################
39
40 CREATE TABLE #ActionIds (ActionId UNIQUEIDENTIFIER PRIMARY KEY)
41 CREATE TABLE #Metrics (MetricId INT PRIMARY KEY)
42 CREATE TABLE #DBMetrics (MetricTypeId INT PRIMARY KEY)
43
44 CREATE TABLE #Filters (AttributeFilterId INT PRIMARY KEY)
45 CREATE TABLE #DBFilters (MetricTypeId INT PRIMARY KEY)
46
47 CREATE TABLE #Carriers (Carrier NVARCHAR(100) PRIMARY KEY)
48 CREATE TABLE #Countries (Country NVARCHAR(100) PRIMARY KEY)
49 CREATE TABLE #OS (OS NVARCHAR(100) PRIMARY KEY)
50
51 DECLARE @TIME_INTERVAL TABLE ([TM] VARCHAR(15),UNIQUE CLUSTERED ([TM]))
52 DECLARE @Intersection DATETIME
53 DECLARE @DateMax DATETIME
54
55 DECLARE @ActionsCount INT
56 DECLARE @MetricsCount INT
57
58 DECLARE @ExportedCurrencyId NVARCHAR(3) = 'EUR'
59
60 --######################
61
62 /*Filling "Date steps" regarding the giving RangeType of user.
63 Examples: 1.[2016-09-20 00 2016-09-20 01 2016-09-20 02] 2.[2016-09-20 2016-09-21 2016-09-22] 3.[2016-09 2016-10 2016-11]*/
64 -- !!! Consider not to use Functions kai Function Tables !!!
65 INSERT INTO @TIME_INTERVAL (TM)
66 SELECT OnLinePromotionsPlatform_Traffic.dbo.FN_SCALAR_ConvertDateTimeToVarchar(IndividualDate,@RangeType)
67 FROM OnLinePromotionsPlatform_Traffic.[dbo].[FN_TABLE_DateRange](
68 CASE WHEN @RangeType=1 THEN 'h'
69 WHEN @RangeType=2 THEN 'd'
70 WHEN @RangeType=3 THEN 'm'
71 END
72 ,@DateFrom,@DateTo)
73
74 INSERT INTO @TIME_INTERVAL (TM) VALUES ('Total')
75
76 --SELECT * FROM @TIME_INTERVAL --Debug
77
78
79
80 /*Initialize table #ActionIds*/
81 IF (@ListOfActionIds != '')
82 BEGIN
83 INSERT INTO #ActionIds(ActionId)
84 SELECT [Data]
85 FROM OnLinePromotionsPlatform_Traffic.dbo.FN_TABLE_SplitString(@ListOfActionIds, ';')
86 OPTION (MAXRECURSION 0)
87 END
88
89 --SELECT * FROM #ActionIds --Debug
90
91 --SELECT @ActionsCount = COUNT(*) FROM #ActionIds --Debug
92 PRINT '[Actions count]:' + CAST(@ActionsCount AS VARCHAR) --Debug
93
94
95 /*Initialize table #Metrics*/
96 IF (@ListOfMetrics != '')
97 BEGIN
98 INSERT INTO #Metrics(MetricId)
99 SELECT [Data]
100 FROM OnLinePromotionsPlatform_Traffic.dbo.FN_TABLE_SplitString(@ListOfMetrics, ';')
101 OPTION (MAXRECURSION 0)
102 END
103
104 --SELECT * FROM #Metrics --Debug
105
106 --SELECT @MetricsCount = COUNT(*) FROM #Metrics --Debug
107 PRINT '[Metrics count]:' + CAST(@MetricsCount AS VARCHAR) --Debug
108
109
110 /*Initialize table #DBMetrics*/
111 /*This table indicates the tables from which our metrics will be populated*/
112 INSERT INTO #DBMetrics
113 SELECT DISTINCT(MetricTypeId)
114 FROM MetricTypesRelationship
115 INNER JOIN #Metrics
116 ON #Metrics.MetricId = MetricTypesRelationship.MetricId
117
118 --SELECT * FROM #DBMetrics --debug
119
120
121
122 /*
123 Handling the optional 'Filters' parameters
124 Carriers, Countries, IsMobile, AdvertisedDeviceOS
125 */
126
127 /*Initialize table #Filters, #Carriers, #Countries, #OS*/
128 IF @Carriers != ''
129 BEGIN
130 INSERT INTO #Carriers(Carrier)
131 SELECT [Data]
132 FROM OnLinePromotionsPlatform_Traffic.dbo.FN_TABLE_SplitString(@Carriers, ';')
133 OPTION (MAXRECURSION 0)
134
135 INSERT INTO #Filters (AttributeFilterId) VALUES (1)
136 END
137
138 IF @Countries != ''
139 BEGIN
140 INSERT INTO #Countries(Country)
141 SELECT [Data]
142 FROM OnLinePromotionsPlatform_Traffic.dbo.FN_TABLE_SplitString(@Countries, ';')
143 OPTION (MAXRECURSION 0)
144
145 INSERT INTO #Filters (AttributeFilterId) VALUES (2)
146 END
147
148 IF @IsMobile IS NOT NULL
149 BEGIN
150 INSERT INTO #Filters (AttributeFilterId) VALUES (3)
151 END
152
153 IF @AdvertisedDeviceOS != ''
154 BEGIN
155 INSERT INTO #OS(OS)
156 SELECT [Data]
157 FROM OnLinePromotionsPlatform_Traffic.dbo.FN_TABLE_SplitString(@AdvertisedDeviceOS, ';')
158 OPTION (MAXRECURSION 0)
159
160 INSERT INTO #Filters (AttributeFilterId) VALUES (4)
161 END
162
163 --SELECT * FROM #Filters --debug
164
165
166 ----------------------------------------------------------------
167
168
169 /*Fetching the Min Intersection time of 4 Tables included*/
170 SELECT @Intersection = ISNULL(MIN([Intersection]),'2016-01-01')
171 FROM [dbo].[Reporting]
172 WHERE 1=1
173 AND ReportingId NOT IN ('E89DFE77-C6B1-40B6-A25F-9A7928BB5D3E') --ReportingType = FinancePerspective
174 PRINT '[Intersection]:' + CAST(@Intersection AS VARCHAR)
175
176
177 /*DateTo cannot be bigger than Intersection Date*/
178 IF(@DateTo>@Intersection)
179 SET @DateMax = @Intersection
180 ELSE
181 SET @DateMax = @DateTo
182 PRINT '[New DateMax]' + CAST(@DateMax AS VARCHAR)
183
184
185
186
187 /*OnLinePromotionsPlatform_Reporting.dbo.StandardAnalytics*/
188 IF EXISTS (SELECT 1 FROM #DBMetrics WHERE MetricTypeId = 1)
189 BEGIN
190 --DECLARE @db1 NVARCHAR(MAX)
191 --SELECT TOP 1 @db1 = TableAssociation FROM OnLinePromotionsPlatform_Reporting.dbo.MetricTypes WHERE MetricTypeId = 1
192
193 DECLARE @TmpStAnalytics TABLE([TM] VARCHAR(50),
194 [Visits] DECIMAL(19,2),
195 [MSISDNSubmissions] DECIMAL(19,2),
196 [PINSubmissions] DECIMAL(19,2),
197 [Conversions] DECIMAL(19,2),
198 [ActualConversions] DECIMAL(19,2),
199 [CutOffConversions] DECIMAL(19,2),
200 [BillingOnConversions] DECIMAL(19,2),
201 [MarketingCost] DECIMAL(19,2),
202 [ActualMarketingCost] DECIMAL(19,2),
203 [CutOffMarketingCost] DECIMAL(19,2),
204 [Sales] DECIMAL(19,2),
205 [ActualSales] DECIMAL(19,2),
206 [CutOffSales] DECIMAL(19,2),
207 [Billings] DECIMAL(19,2),
208 [ActualBillings] DECIMAL(19,2),
209 [IVRDuration] INT,
210 [IVRRevenueAMZ] DECIMAL(19,4),
211 [IVRRevenueEXT] DECIMAL(19,4), UNIQUE CLUSTERED ([TM]),
212 [TotalSubscriptions] INT,
213 [ActiveSubscriptions] INT,
214
215 [MsisdnThroughRate] DECIMAL(19,2),
216 [PinThroughRate] DECIMAL(19,2),
217 [ConversionRate] DECIMAL(19,2),
218 [ActualConversionRate] DECIMAL(19,2),
219 [CutOffConversionRate] DECIMAL(19,2),
220 [BillingOnConversionsRate] DECIMAL(19,2),
221 [SalesRate] DECIMAL(19,2),
222 [ActualSalesRate] DECIMAL(19,2)
223 )
224
225 INSERT INTO @TmpStAnalytics
226 SELECT
227 TM,
228 SUM([Visits]) AS [Visits],
229 SUM([MSISDNSubmissions]) AS [MSISDNSubmissions],
230 SUM([PINSubmissions]) AS [PINSubmissions],
231 SUM([Conversions]) AS [Conversions],
232 SUM([ActualConversions]) AS [ActualConversions],
233 SUM([CutOffConversions]) AS [CutOffConversions],
234 SUM([BillingOnConversions]) AS [BillingOnConversions],
235 SUM([MarketingCost]) AS [MarketingCost],
236 SUM([ActualMarketingCost]) AS [ActualMarketingCost],
237 SUM([CutOffMarketingCost]) AS [CutOffMarketingCost],
238 SUM([Sales]) AS [Sales],
239 SUM([ActualSales]) AS [ActualSales],
240 SUM([CutOffSales]) AS [CutOffSales],
241 SUM([Billings]) AS [Billings],
242 SUM([ActualBillings]) AS [ActualBillings],
243 SUM([IVRDuration]) AS [IVRDuration],
244 SUM([IVRRevenueAMZ]) AS [IVRRevenueAMZ],
245 SUM([IVRRevenueEXT]) AS [IVRRevenueEXT],
246 SUM([TotalSubscriptions]) AS [TotalSubscriptions],
247 SUM([ActiveSubscriptions]) AS [ActiveSubscriptions],
248
249 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([MSISDNSubmissions]) * 100 / SUM([Visits]),2) AS DECIMAL(19,2)) END AS [MsisdnThroughRate],
250 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([PINSubmissions]) * 100 /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [PinThroughRate],
251 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([Conversions]) * 100 /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [ConversionRate],
252 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([ActualConversions]) * 100 /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [ActualConversionRate],
253 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([CutOffConversions]) * 100 /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [CutOffConversionRate],
254 CASE WHEN SUM([Conversions])=0 THEN 0 ELSE CAST(ROUND(SUM([BillingOnConversions]) * 100 /SUM([Conversions]),2) AS DECIMAL(19,2)) END AS [BillingOnConversionsRate],
255 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([Sales] * 100) /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [SalesRate],
256 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([ActualSales] * 100) /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [ActualSalesRate]
257 FROM
258 (
259 SELECT OnLinePromotionsPlatform_Traffic.dbo.FN_SCALAR_ConvertDateTimeToVarchar(TM, @RangeType) AS TM,
260 SUM([Visits]) AS [Visits],
261 SUM([MSISDNSubmissions]) AS [MSISDNSubmissions],
262 SUM([PINSubmissions]) AS [PINSubmissions],
263 SUM([Conversions]) AS [Conversions],
264 SUM([ActualConversions]) AS [ActualConversions],
265 SUM([CutOffConversions]) AS [CutOffConversions],
266 SUM([BillingOnConversions]) AS [BillingOnConversions],
267 CASE
268 WHEN ACT.PricingModelId = 1 THEN SUM([Conversions]) * ACT.Price * CE.[rate] --CPA
269 WHEN ACT.PricingModelId = 5 THEN SUM([Sales]) * ACT.Price * CE.[rate] --CPS
270 WHEN ACT.PricingModelId = 6 THEN SUM([Visits]) * ACT.Price * CE.[rate] --CPB
271 ELSE SUM([Conversions]) * ISNULL(DMC.CostPerConversion*CE2.[rate], 0)
272 END AS [MarketingCost],
273 CASE
274 WHEN ACT.PricingModelId = 1 THEN SUM([ActualConversions]) * ACT.Price * CE.[rate] --CPA
275 WHEN ACT.PricingModelId = 5 THEN SUM([ActualSales]) * ACT.Price * CE.[rate] --CPS
276 WHEN ACT.PricingModelId = 6 THEN SUM([Visits]) * ACT.Price * CE.[rate] --CPB
277 ELSE SUM([ActualConversions]) * ISNULL(DMC.CostPerConversion*CE2.[rate], 0)
278 END AS [ActualMarketingCost],
279 CASE
280 WHEN ACT.PricingModelId = 1 THEN SUM([CutOffConversions]) * ACT.Price * CE.[rate]
281 WHEN ACT.PricingModelId = 5 THEN SUM([CutOffSales]) * ACT.Price * CE.[rate]
282 WHEN ACT.PricingModelId = 6 THEN SUM([Visits]) * ACT.Price * CE.[rate] --CPB
283 ELSE SUM([CutOffConversions]) * ISNULL(DMC.CostPerConversion*CE2.[rate], 0)
284 END AS [CutOffMarketingCost],
285 SUM([Sales]) AS [Sales],
286 SUM([ActualSales]) AS [ActualSales],
287 SUM([CutOffSales]) AS [CutOffSales],
288 SUM([Billings]) AS [Billings],
289 SUM([ActualBillings]) AS [ActualBillings],
290 SUM([IVRDuration]) AS [IVRDuration],
291 SUM([IVRRevenueAMZ]) AS [IVRRevenueAMZ],
292 SUM([IVRRevenueEXT]) AS [IVRRevenueEXT],
293 MAX([TotalSubscriptions]) AS [TotalSubscriptions],
294 MAX([ActiveSubscriptions]) AS [ActiveSubscriptions]
295 FROM [StandardAnalytics] AS A
296 INNER JOIN #ActionIds AS B
297 ON A.[ActionId]=B.[ActionId] AND (A.[TM]>=@DateFrom AND A.[TM]<@DateMax)
298 INNER JOIN OnLinePromotionsPlatform_Admin.dbo.[Action] AS ACT
299 ON B.[ActionId]=ACT.[ActionId]
300 INNER JOIN OnLinePromotionsPlatform_Admin.dbo.CurrencyExchange AS CE
301 ON ACT.CurrencyID = CE.[CurrencyIdFrom] AND CE.[CurrencyIdTo] = @ExportedCurrencyId
302 LEFT JOIN OnLinePromotionsPlatform_Traffic.[dbo].[DailyMarketingCost] AS DMC WITH (NOLOCK)
303 ON DMC.AdNetworkId = ACT.AdChannelID AND (A.TM>= DMC.[Day] AND A.TM< DATEADD(SECOND, 86400, DMC.[DAY]))
304 LEFT JOIN OnLinePromotionsPlatform_Admin.dbo.CurrencyExchange AS CE2
305 ON DMC.Currency = CE2.[CurrencyIdFrom] COLLATE Greek_CI_AS AND CE2.[CurrencyIdTo] = @ExportedCurrencyId
306 GROUP BY OnLinePromotionsPlatform_Traffic.dbo.FN_SCALAR_ConvertDateTimeToVarchar(A.TM,@RangeType),ACT.PricingModelId, ACT.Price, CE.Rate, DMC.CostPerConversion, CE2.[rate]
307 )AS SRC
308 GROUP BY TM
309
310 /*Making the 'Total' record*/
311 INSERT INTO @TmpStAnalytics
312 SELECT
313 'Total' AS TM,
314 SUM([Visits]) AS [Visits],
315 SUM([MSISDNSubmissions]) AS [MSISDNSubmissions],
316 SUM([PINSubmissions]) AS [PINSubmissions],
317 SUM([Conversions]) AS [Conversions],
318 SUM([ActualConversions]) AS [ActualConversions],
319 SUM([CutOffConversions]) AS [CutOffConversions],
320 SUM([BillingOnConversions]) AS [BillingOnConversions],
321 SUM([MarketingCost]) AS [MarketingCost],
322 SUM([ActualMarketingCost]) AS [ActualMarketingCost],
323 SUM([CutOffMarketingCost]) AS [CutOffMarketingCost],
324 SUM([Sales]) AS [Sales],
325 SUM([ActualSales]) AS [ActualSales],
326 SUM([CutOffSales]) AS [CutOffSales],
327 SUM([Billings]) AS [Billings],
328 SUM([ActualBillings]) AS [ActualBillings],
329 SUM([IVRDuration]) AS [IVRDuration],
330 SUM([IVRRevenueAMZ]) AS [IVRRevenueAMZ],
331 SUM([IVRRevenueEXT]) AS [IVRRevenueEXT],
332 SUM([TotalSubscriptions]) AS [TotalSubscriptions],
333 SUM([ActiveSubscriptions]) AS [ActiveSubscriptions],
334 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([MSISDNSubmissions]) * 100 / SUM([Visits]),2) AS DECIMAL(19,2)) END AS [MsisdnThroughRate],
335 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([PINSubmissions]) * 100 /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [PinThroughRate],
336 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([Conversions]) * 100 /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [ConversionRate],
337 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([ActualConversions]) * 100 /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [ActualConversionRate],
338 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([CutOffConversions]) * 100 /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [CutOffConversionRate],
339 CASE WHEN SUM([Conversions])=0 THEN 0 ELSE CAST(ROUND(SUM([BillingOnConversions]) * 100 /SUM([Conversions]),2) AS DECIMAL(19,2)) END AS [BillingOnConversionsRate],
340 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([Sales] * 100) /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [SalesRate],
341 CASE WHEN SUM([Visits])=0 THEN 0 ELSE CAST(ROUND(SUM([ActualSales] * 100) /SUM([Visits]),2) AS DECIMAL(19,2)) END AS [ActualSalesRate]
342 FROM @TmpStAnalytics
343 END
344
345 --SELECT * FROM @TmpStAnalytics --Debug
346
347
348 /*OnLinePromotionsPlatform_Reporting.dbo.DlrAnalytics*/
349 IF EXISTS (SELECT 1 FROM #DBMetrics WHERE MetricTypeId = 2)
350 BEGIN
351
352 DECLARE @TmpDlrAnalytics TABLE( [TM] VARCHAR(50),
353 [BillingWelcome] INT,
354 [BillingWelcomeRevenue] DECIMAL(19,2),
355 [BillingRenewal] INT,
356 [BillingRenewalRevenue] DECIMAL(19,2),
357 [BillingTotalRevenue] DECIMAL(19,2),
358 [BillingFailed] INT,
359 [SubActivation] INT,
360 [SubDeactivation] INT,
361 [DlrError] INT,UNIQUE CLUSTERED ([TM]))
362
363 INSERT INTO @TmpDlrAnalytics
364 SELECT
365 [TM],
366 SUM([BillingWelcome]) AS [BillingWelcome],
367 SUM([BillingWelcomeRevenue]) AS [BillingWelcomeRevenue],
368 SUM([BillingRenewal]) AS [BillingRenewal],
369 SUM([BillingRenewalRevenue]) AS [BillingRenewalRevenue],
370 SUM([BillingTotalRevenue]) AS [BillingTotalRevenue],
371 SUM([BillingFailed]) AS [BillingFailed],
372 SUM([SubActivation]) AS [SubActivation],
373 SUM([SubDeactivation]) AS [SubDeactivation],
374 SUM([DlrError]) AS [DlrError]
375 FROM
376 (
377 SELECT OnLinePromotionsPlatform_Traffic.dbo.FN_SCALAR_ConvertDateTimeToVarchar(TM,@RangeType) AS TM,
378 SUM([BillingWelcome]) AS [BillingWelcome],
379 SUM([BillingWelcomeRevenue]) AS [BillingWelcomeRevenue],
380 SUM([BillingRenewal]) AS [BillingRenewal],
381 SUM([BillingRenewalRevenue]) AS [BillingRenewalRevenue],
382 SUM([BillingTotalRevenue]) AS [BillingTotalRevenue],
383 SUM([BillingFailed]) AS [BillingFailed],
384 SUM([SubActivation]) AS [SubActivation],
385 SUM([SubDeactivation]) AS [SubDeactivation],
386 SUM([DlrError]) AS [DlrError]
387 FROM [DlrAnalytics] AS A
388 INNER JOIN #ActionIds AS B
389 ON A.[ActionId]=B.[ActionId] AND (A.[TM]>=@DateFrom AND A.[TM]<@DateMax)
390 INNER JOIN OnLinePromotionsPlatform_Admin.dbo.[Action] ACT
391 ON B.[ActionId]=ACT.[ActionId]
392 --WHERE A.[MobileCarrierId]=IIF(@Carrier IS NULL OR @Carrier='', A.[MobileCarrierId], @Carrier )
393 GROUP BY OnLinePromotionsPlatform_Traffic.dbo.FN_SCALAR_ConvertDateTimeToVarchar(A.TM,@RangeType)
394 )AS SRC
395 GROUP BY TM
396
397 /*Making the 'Total' record*/
398 INSERT INTO @TmpDlrAnalytics
399 SELECT
400 'Total' AS [TM],
401 SUM([BillingWelcome]) AS [BillingWelcome],
402 SUM([BillingWelcomeRevenue]) AS [BillingWelcomeRevenue],
403 SUM([BillingRenewal]) AS [BillingRenewal],
404 SUM([BillingRenewalRevenue]) AS [BillingRenewalRevenue],
405 SUM([BillingTotalRevenue]) AS [BillingTotalRevenue],
406 SUM([BillingFailed]) AS [BillingFailed],
407 SUM([SubActivation]) AS [SubActivation],
408 SUM([SubDeactivation]) AS [SubDeactivation],
409 SUM([DlrError]) AS [DlrError]
410 FROM @TmpDlrAnalytics
411 END
412
413 --SELECT * FROM @TmpDlrAnalytics --Debug
414
415
416
417
418
419 /*OnLinePromotionsPlatform_Reporting.dbo.TrafficSegmentation*/
420 IF EXISTS (SELECT 1 FROM #DBMetrics WHERE MetricTypeId = 3)
421 BEGIN
422 DECLARE @TmpTrafficSeg TABLE( [TM] VARCHAR(50),
423 [CountryName] VARCHAR(25),
424 [IsMobile] BIT,
425 [AdvertisedDeviceOs] VARCHAR(50),
426 [Carrier] VARCHAR(20),
427 [Visits] DECIMAL(19,2),
428 [Conversions] DECIMAL(19,2),
429 [ActualConversions] DECIMAL(19,2),
430 [CutOffConversions] DECIMAL(19,2),UNIQUE CLUSTERED ([TM], CountryName, IsMobile, AdvertisedDeviceOs, Carrier))
431
432 INSERT INTO @TmpTrafficSeg
433 SELECT
434 TM AS [TM],
435 CountryName AS [CountryName],
436 IsMobile AS [IsMobile],
437 AdvertisedDeviceOs AS [AdvertisedDeviceOs],
438 Carrier AS [Carrier],
439 SUM([Visits]) AS [Visits],
440 SUM([Conversions]) AS [Conversions],
441 SUM([ActualConversions]) AS [ActualConversions],
442 SUM([CutOffConversions]) AS [CutOffConversions]
443 FROM
444 (
445 SELECT OnLinePromotionsPlatform_Traffic.dbo.FN_SCALAR_ConvertDateTimeToVarchar(TM,@RangeType) AS TM,
446 CountryName AS [CountryName],
447 IsMobile AS [IsMobile],
448 AdvertisedDeviceOs AS [AdvertisedDeviceOs],
449 Carrier AS [Carrier],
450 SUM([Visits]) AS [Visits],
451 SUM([Conversions]) AS [Conversions],
452 SUM([ActualConversions]) AS [ActualConversions],
453 SUM([CutOffConversions]) AS [CutOffConversions]
454 FROM [dbo].[TrafficSegmentation] AS A
455 INNER JOIN #ActionIds AS B
456 ON A.[ActionId]=B.[ActionId] AND (A.[TM]>=@DateFrom AND A.[TM]<@DateMax)
457 GROUP BY OnLinePromotionsPlatform_Traffic.dbo.FN_SCALAR_ConvertDateTimeToVarchar(A.TM,@RangeType),
458 CountryName,
459 IsMobile,
460 AdvertisedDeviceOs,
461 Carrier
462 )AS SRC
463 GROUP BY TM, CountryName, IsMobile, AdvertisedDeviceOs, Carrier
464
465 /*Making the 'Total' record*/
466 INSERT INTO @TmpTrafficSeg
467 SELECT
468 'Total' AS [TM],
469 NULL AS [CountryName],
470 NULL AS [IsMobile],
471 NULL AS [AdvertisedDeviceOs],
472 NULL AS [Carrier],
473 SUM([Visits]) AS [Visits],
474 SUM([Conversions]) AS [Conversions],
475 SUM([ActualConversions]) AS [ActualConversions],
476 SUM([CutOffConversions]) AS [CutOffConversions]
477 FROM @TmpTrafficSeg
478 END
479
480 --SELECT * FROM @TmpTrafficSeg --Debug
481
482
483
484
485
486 /*OnLinePromotionsPlatform_Reporting.dbo.FinancePerspective*/
487 IF EXISTS (SELECT 1 FROM #DBMetrics WHERE MetricTypeId = 4)
488 BEGIN
489 DECLARE @TmpFinance TABLE([TM] VARCHAR(50),
490 [MarketingCost] DECIMAL(19,2)
491 )
492
493 INSERT INTO @TmpFinance
494 SELECT
495 TM,
496 SUM([MarketingCost]) AS [MarketingCost]
497 FROM
498 (
499 SELECT OnLinePromotionsPlatform_Traffic.dbo.FN_SCALAR_ConvertDateTimeToVarchar(TM, @RangeType) AS TM,
500 SUM([MarketingCost]) AS [MarketingCost]
501 FROM [FinancePerspective] AS FP
502 INNER JOIN #ActionIds AS AC
503 ON FP.[ActionId]=AC.[ActionId] AND (FP.[TM]>=@DateFrom AND FP.[TM]<@DateMax)
504 INNER JOIN OnLinePromotionsPlatform_Admin.dbo.[Action] AS ACT
505 ON AC.[ActionId]=ACT.[ActionId]
506 INNER JOIN OnLinePromotionsPlatform_Admin.dbo.CurrencyExchange AS CE
507 ON ACT.CurrencyID = CE.[CurrencyIdFrom] AND CE.[CurrencyIdTo] = @ExportedCurrencyId
508 LEFT JOIN OnLinePromotionsPlatform_Traffic.[dbo].[DailyMarketingCost] AS DMC WITH (NOLOCK)
509 ON DMC.AdNetworkId = ACT.AdChannelID AND (FP.TM>= DMC.[Day] AND FP.TM< DATEADD(SECOND, 86400, DMC.[DAY]))
510 LEFT JOIN OnLinePromotionsPlatform_Admin.dbo.CurrencyExchange AS CE2
511 ON DMC.Currency = CE2.[CurrencyIdFrom] COLLATE Greek_CI_AS AND CE2.[CurrencyIdTo] = @ExportedCurrencyId
512 GROUP BY OnLinePromotionsPlatform_Traffic.dbo.FN_SCALAR_ConvertDateTimeToVarchar(FP.TM,@RangeType), ACT.PricingModelId, ACT.Price, CE.Rate, DMC.CostPerConversion, CE2.[rate]
513 )AS SRC
514 GROUP BY TM
515
516 INSERT INTO @TmpFinance
517 SELECT
518 'Total' AS TM,
519 SUM([MarketingCost]) AS [MarketingCost]
520 FROM @TmpFinance
521 END
522
523 --SELECT * FROM @TmpFinance --Debug
524
525
526
527
528 DECLARE @Pivoted TABLE( TM VARCHAR(50),
529 Visits INT, --1 Standard Analytics
530 MSISDNSubmissions INT, --2 Standard Analytics
531 PINSubmissions INT, --3 Standard Analytics
532 Conversions INT, --4 Standard Analytics
533 ActualConversions INT, --5 Standard Analytics
534 CutOffConversions INT, --6 Standard Analytics
535 BillingOnConversions INT, --7 Standard Analytics
536 MarketingCost DECIMAL(19,2), --8 Finance Perspective
537 ActualMarketingCost DECIMAL(19,2), --9 Standard Analytics
538 CutOffMarketingCost DECIMAL(19,2), --10 Standard Analytics
539 Sales INT, --11 Standard Analytics
540 ActualSales INT, --12 Standard Analytics
541 CutOffSales DECIMAL(19,2), --13 Standard Analytics
542 Billings INT, --14 Standard Analytics
543 ActualBillings INT, --15 Standard Analytics
544 MsisdnThroughRate DECIMAL(19,2), --16 Standard Analytics
545 PinThroughRate DECIMAL(19,2), --17 Standard Analytics
546 ConversionRate DECIMAL(19,2), --18 Standard Analytics
547 ActualConversionRate DECIMAL(19,2), --19 Standard Analytics
548 CutOffConversionRate DECIMAL(19,2), --20 Standard Analytics
549 BillingOnConversionsRate DECIMAL(19,2), --21 Standard Analytics
550 SalesRate DECIMAL(19,2), --22 Standard Analytics
551 ActualSalesRate DECIMAL(19,2), --23 Standard Analytics
552 BillingWelcomes INT, --24 DLR Analaytics
553 WelcomeRevenue DECIMAL(19,2), --25 DLR Analaytics
554 BillingRenewals INT, --26 DLR Analaytics
555 RenewalRevenue DECIMAL(19,2), --27 DLR Analaytics
556 TotalRevenue DECIMAL(19,2), --28 DLR Analaytics
557 BillingsFailed INT, --29 DLR Analaytics
558 SubActivation INT, --30 DLR Analaytics
559 SubDeactivation INT, --31 DLR Analaytics
560 DeliveryErrors INT, --32 DLR Analaytics
561 TotalSubscriptions INT, --33 Standard Analaytics
562 ActiveSubscriptions INT, --34 Standard Analaytics
563
564 UNIQUE CLUSTERED (TM,Visits)
565 )
566
567 INSERT INTO @Pivoted
568 /*Join temp tables in order to make the final output*/
569 SELECT
570 TI.TM AS 'TM',
571 ISNULL(STD.Visits,0) AS 'Visits', --1 Standard Analytics
572 ISNULL(STD.MSISDNSubmissions,0) AS 'MsisdnSubmissions', --2 Standard Analytics
573 ISNULL(STD.PINSubmissions,0) AS 'PinSubmissions', --3 Standard Analytics
574 ISNULL(STD.Conversions,0) AS 'Conversions', --4 Standard Analytics
575 ISNULL(STD.ActualConversions,0) AS 'ActualConversions', --5 Standard Analytics
576 ISNULL(STD.CutOffConversions,0) AS 'CutOffConversions', --6 Standard Analytics
577 ISNULL(STD.BillingOnConversions,0) AS 'BillingOnConversions', --7 Standard Analytics
578 ISNULL(FP.MarketingCost,0) AS 'MarketingCost', --8 Finance Perspective
579 ISNULL(STD.ActualMarketingCost,0) AS 'ActualMarketingCost', --9 Standard Analytics
580 ISNULL(STD.CutOffMarketingCost,0) AS 'CutOffMarketingCost', --10 Standard Analytics
581 ISNULL(STD.Sales,0) AS 'Sales', --11 Standard Analytics
582 ISNULL(STD.ActualSales,0) AS 'ActualSales', --12 Standard Analytics
583 ISNULL(STD.CutOffSales,0) AS 'CutOffSales', --13 Standard Analytics
584 ISNULL(STD.Billings,0) AS 'Billings', --14 Standard Analytics
585 ISNULL(STD.ActualBillings,0) AS 'ActualBillings', --15 Standard Analytics
586 ISNULL(STD.MsisdnThroughRate,0) AS 'MsisdnThroughRate', --16 Standard Analytics
587 ISNULL(STD.PinThroughRate,0) AS 'PinThroughRate', --17 Standard Analytics
588 ISNULL(STD.ConversionRate,0) AS 'ConversionRate', --18 Standard Analytics
589 ISNULL(STD.ActualConversionRate,0) AS 'ActualConversionRate', --19 Standard Analytics
590 ISNULL(STD.CutOffConversionRate,0) AS 'CutOffConversionRate', --20 Standard Analytics
591 ISNULL(STD.BillingOnConversionsRate,0) AS 'BillingOnConversionsRate', --21 Standard Analytics
592 ISNULL(STD.SalesRate,0) AS 'SalesRate', --22 Standard Analytics
593 ISNULL(STD.ActualSalesRate,0) AS 'ActualSalesRate', --23 Standard Analytics
594 ISNULL(DLR.BillingWelcome,0) AS 'BillingWelcomes', --24 DLR Analaytics
595 ISNULL(DLR.BillingWelcomeRevenue,0) AS 'WelcomeRevenue', --25 DLR Analaytics
596 ISNULL(DLR.BillingRenewal,0) AS 'BillingRenewals', --26 DLR Analaytics
597 ISNULL(DLR.BillingRenewalRevenue,0) AS 'RenewalRevenue', --27 DLR Analaytics
598 ISNULL(DLR.BillingTotalRevenue,0) AS 'TotalRevenue', --28 DLR Analaytics
599 ISNULL(DLR.BillingFailed,0) AS 'BillingsFailed', --29 DLR Analaytics
600 ISNULL(DLR.SubActivation,0) AS 'SubscriptionActivations', --30 DLR Analaytics
601 ISNULL(DLR.SubDeactivation,0) AS 'SubscriptionDeactivations', --31 DLR Analaytics
602 ISNULL(DLR.DlrError,0) AS 'DeliveryErrors', --32 DLR Analaytics
603 ISNULL(STD.TotalSubscriptions,0) AS 'TotalSubscriptions', --33 Standard Analaytics
604 ISNULL(STD.ActiveSubscriptions,0) AS 'ActiveSubscriptions' --34 Standard Analaytics
605 FROM @TIME_INTERVAL AS TI
606 FULL OUTER JOIN
607 (
608 SELECT TM FROM @TmpStAnalytics
609 UNION
610 SELECT TM FROM @TmpDlrAnalytics
611 UNION
612 SELECT TM FROM @TmpFinance
613 ) AS UTM
614 ON TI.TM = UTM.TM
615 FULL OUTER JOIN @TmpStAnalytics AS STD
616 ON STD.TM = UTM.TM
617 FULL OUTER JOIN @TmpDlrAnalytics AS DLR
618 ON DLR.TM = UTM.TM
619 --FULL OUTER JOIN @TmpTrafficSeg AS SEG
620 --ON STD.TM = DLR.TM
621 FULL OUTER JOIN @TmpFinance AS FP
622 ON FP.TM = UTM.TM
623
624 --SELECT * FROM @Pivoted -- debug
625
626
627 /*
628 Unpivitoning the @Pivoted table
629 Defying the Metrics tha have Amount values (int) and Metrics tha have Rate values (%)
630 */
631
632 SELECT UN.TM, UN.Metric, MT.MetricId, T1.AmountInt, T2.AmountDec
633 FROM
634 (
635 SELECT UA.TM, UA.Metric
636 FROM @Pivoted AS UA
637 UNPIVOT
638 (
639 AmountInt FOR Metric IN ( Visits,
640 MSISDNSubmissions,
641 PINSubmissions,
642 Conversions,
643 ActualConversions,
644 CutOffConversions,
645 BillingOnConversions,
646 Sales,
647 ActualSales,
648 Billings,
649 ActualBillings,
650 BillingWelcomes,
651 BillingRenewals,
652 BillingsFailed,
653 SubActivation,
654 SubDeactivation,
655 DeliveryErrors,
656 TotalSubscriptions,
657 ActiveSubscriptions)
658 ) AS UA
659 UNION
660 SELECT UB.TM, UB.Metric
661 FROM @Pivoted AS UB
662 UNPIVOT
663 (
664 AmountDec FOR Metric IN ( MsisdnThroughRate,
665 PinThroughRate,
666 ConversionRate,
667 ActualConversionRate,
668 CutOffConversionRate,
669 BillingOnConversionsRate,
670 SalesRate,
671 ActualSalesRate,
672 MarketingCost,
673 ActualMarketingCost,
674 CutOffMarketingCost,
675 CutOffSales,
676 WelcomeRevenue,
677 RenewalRevenue,
678 TotalRevenue)
679 ) AS UB
680 ) AS UN
681 FULL OUTER JOIN
682 (
683 SELECT UA.TM, UA.Metric, UA.AmountInt
684 FROM @Pivoted AS UA
685 UNPIVOT
686 (
687 AmountInt FOR Metric IN ( Visits,
688 MSISDNSubmissions,
689 PINSubmissions,
690 Conversions,
691 ActualConversions,
692 CutOffConversions,
693 BillingOnConversions,
694 Sales,
695 ActualSales,
696 Billings,
697 ActualBillings,
698 BillingWelcomes,
699 BillingRenewals,
700 BillingsFailed,
701 SubActivation,
702 SubDeactivation,
703 DeliveryErrors,
704 TotalSubscriptions,
705 ActiveSubscriptions)
706 ) AS UA
707 ) AS T1
708 ON UN.Metric = T1.Metric AND UN.TM = T1.TM
709 FULL OUTER JOIN
710 (
711 SELECT UB.TM, UB.Metric, UB.AmountDec
712 FROM @Pivoted AS UB
713 UNPIVOT
714 (
715 AmountDec FOR Metric IN ( MsisdnThroughRate,
716 PinThroughRate,
717 ConversionRate,
718 ActualConversionRate,
719 CutOffConversionRate,
720 BillingOnConversionsRate,
721 SalesRate,
722 ActualSalesRate,
723 MarketingCost,
724 ActualMarketingCost,
725 CutOffMarketingCost,
726 CutOffSales,
727 WelcomeRevenue,
728 RenewalRevenue,
729 TotalRevenue)
730 ) AS UB
731 ) AS T2
732 ON UN.Metric = T2.Metric AND UN.TM = T2.TM
733 INNER JOIN OnLinePromotionsPlatform_Reporting.dbo.Metrics AS MT
734 ON UN.Metric = MT.MetricName
735 ORDER BY UN.Metric ASC, UN.TM ASC
736
737
738 ----------------------------
739 DROP TABLE #ActionIds
740 DROP TABLE #Metrics
741 DROP TABLE #DBMetrics
742
743 DROP TABLE #Filters
744 DROP TABLE #DBFilters
745
746 DROP TABLE #Carriers
747 DROP TABLE #Countries
748 DROP TABLE #OS
749END