Google Analytics Tables Structure Design

Google Analytics tables structure design with SingleMonth, Quarter and FiscalYearToDate(FYTD) format

USE [TestDataBase]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[CriteriaBySite](
[ProfileId] [int] NOT NULL,
[ProfileValue] [varchar](24) NOT NULL,
[Criteria] [varchar](100) NOT NULL,
[AuditTaskID] [int] NULL,
[RecordExists] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURL_FYTD](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURL_Quarter](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[PagesDetailByURL_SingleMonth](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteria_FYTD](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteria_Quarter](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteria_SingleMonth](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteriaHistory_FYTD](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteriaHistory_Quarter](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[PagesDetailByURLByCriteriaHistory_SingleMonth](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Criteria] [varchar](100) NOT NULL,
[PageURL] [nvarchar](max) NULL,
[PageViewCount] [int] NULL,
[UniquePageViewCount] [int] NULL,
[AvgTimeOnPage] [numeric](18, 2) NULL,
[EntranceCount] [int] NULL,
[BounceRate] [numeric](18, 2) NULL,
[ExitPercent] [numeric](18, 2) NULL,
[TotalValue] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[SiteProfile](
[ProfileId] [int] NOT NULL,
[ProfileValue] [varchar](24) NOT NULL,
[SiteURL] [varchar](2000) NOT NULL,
[Title] [varchar](120) NOT NULL,
[InceptionDate] [date] NOT NULL,
[Account] [varchar](120) NOT NULL,
[UAID] [varchar](120) NOT NULL,
[AuditTaskID] [int] NULL,
[RecordExists] [bit] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[SiteProfileHistory_Date](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[ProfileId] [varchar](24) NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[MonthStartDate] [date] NULL,
[MonthEndDate] [date] NULL,
[QuarterStartDate] [date] NULL,
[QuarterEndDate] [date] NULL,
[YearStartDate] [date] NULL,
[YearEndDate] [date] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[SiteProfileHistoryDate_FYTD](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[ProfileId] [varchar](24) NULL,
[FYTDStartDate] [date] NULL,
[FYTDEndDate] [date] NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[SiteProfileHistoryDate_Quarter](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[ProfileId] [varchar](24) NULL,
[FiscalQuarterStartDate] [date] NULL,
[FiscalQuarterEndDate] [date] NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[Visitor_FYTD](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[Visitor_Quarter](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[Visitor_SingleMonth](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [web].[VisitorHistory](
[ProfileID] [int] NOT NULL,
[Interval] [varchar](20) NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL,
[FiscalMonth] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[NewVisits] [numeric](18, 0) NULL,
[ReturningVisits] [numeric](18, 0) NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [web].[VisitorHistory_FYTD](
[ProfileID] [int] NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL,
[FiscalMonth] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[NewVisits] [int] NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [web].[VisitorHistory_Quarter](
[ProfileID] [int] NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[NewVisits] [int] NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [web].[VisitorHistory_SingleMonth](
[ProfileID] [int] NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalMonth] [tinyint] NULL,
[FiscalQuarter] [tinyint] NULL,
[Visits] [int] NULL,
[UniqueVisitors] [int] NULL,
[PageViews] [int] NULL,
[PagesPerVisit] [numeric](18, 2) NULL,
[AvgVisitDuration] [numeric](18, 2) NULL,
[BounceRate] [numeric](18, 2) NULL,
[PercentNewVisits] [numeric](18, 2) NULL,
[NewVisits] [int] NULL,
[RecordExists] [bit] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[VisitorType_FYTD](
[ProfileID] [int] NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL,
[FiscalMonth] [tinyint] NULL,
[VisitorType] [varchar](20) NULL,
[Visits] [int] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[VisitorType_Quarter](
[ProfileID] [int] NOT NULL,
[FiscalYear] [int] NULL,
[FiscalQuarter] [int] NULL,
[VisitorType] [varchar](20) NULL,
[Visits] [int] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [web].[VisitorType_SingleMonth](
[ProfileID] [int] NOT NULL,
[FiscalYear] [smallint] NULL,
[FiscalQuarter] [tinyint] NULL,
[FiscalMonth] [tinyint] NULL,
[VisitorType] [varchar](20) NULL,
[Visits] [int] NULL,
[AuditTaskId] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO