Kirish I. Umumiy qism

CREATE PROCEDURE fill_paid_traffic_report

(

@startDate date,

@endDate date

)

AS

BEGIN

- Biz o'zgaruvchilarni koddan olib tashlaymiz, keyinchalik jadvalni o'rnatishda ularga kerak bo'ladi

- Sessiyalarni talab qilish

WITH [sessions] AS (

SELECT

[date]

, sourceMedium

, campaign

, SUM([sessions]) AS 'sessions'

-- Yuqori Yuklangan bazani bloklamaslik uchun "bilan (NOLOCK)" dan foydalanish tavsiya etiladi

FROM [GoogleAnalytics].[dbo].[sessions] WITH (NOLOCK)

- Davrni belgilang

WHERE [date] BETWEEN @startDate AND @endDate

- Biz hisobot tuzadigan trafik manbalarini ko'rsatamiz

AND sourceMedium IN ('google / cpc', 'yandex / cpc')

GROUP BY [date], [sourceMedium], [campaign]

)

- Xarajatlarni talab qilish

, costs AS (

SELECT

[date]

, sourceMedium

, campaign

, SUM(cost) AS 'cost'

, SUM(impressions) AS 'impressions'

, SUM(clicks) AS 'clicks'

FROM [GoogleAnalytics].[dbo].[cost] WITH (NOLOCK)

WHERE [date] BETWEEN @startDate AND @endDate

AND sourceMedium IN ('google / cpc', 'yandex / cpc')

GROUP BY [date], [sourceMedium], [campaign]

)

- Sessiyalarni xarajatlar bilan birlashtirish

, costs_sessions AS (

SELECT

-- "ISNULL" manba xarajatlari bo'lmagan joyda "NULL" natijasini olmaslik uchun ishlatiladi, lekin sessiya bor edi

ISNULL(costs.[date], [sessions].[date]) AS 'date'

, ISNULL(costs.sourceMedium, [sessions].sourceMedium) AS 'sourceMedium'

, ISNULL(costs.campaign, [sessions].campaign) AS 'campaign'

, ISNULL(SUM(costs.cost),0) AS 'cost'

, ISNULL(SUM(costs.impressions),0) AS 'impressions'

, ISNULL(SUM(costs.clicks),0) AS 'clicks'

, ISNULL(SUM([sessions].[sessions]), 0) AS 'sessions'