asdasd

 avatar
unknown
json
3 years ago
41 kB
5
Indexable
{
    "run_insight": true,
    "variables": [
        {
            "_id": "PastMonth",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT case a.month when '01' then 'January' \nwhen '02' then 'February' \nwhen '03' then 'March' \nwhen '04' then 'April' \nwhen '05' then 'May' \nwhen '06' then 'June' \nwhen '07' then 'July' \nwhen '08' then 'August' \nwhen '09' then 'September' \nwhen '10' then 'October' \nwhen '11' then 'November' \nwhen '12' then 'December' else '' end\nas month FROM (SELECT SUBSTR(date('now','-1 Month'),6,2) month) a\n",
                "tableFunction": "1,2",
                "varName": "PastMonth"
            }
        },
        {
            "_id": "PreviousPastMonth",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT case a.month when '01' then 'January' \nwhen '02' then 'February' \nwhen '03' then 'March' \nwhen '04' then 'April' \nwhen '05' then 'May' \nwhen '06' then 'June' \nwhen '07' then 'July' \nwhen '08' then 'August' \nwhen '09' then 'September' \nwhen '10' then 'October' \nwhen '11' then 'November' \nwhen '12' then 'December' else '' end\nas month FROM (SELECT SUBSTR(date('now','-2 month'),6,2) month) a\n",
                "tableFunction": "1,2",
                "varName": "PreviousPastMonth"
            }
        },
        {
            "_id": "ga_lastYearForPreviousPeriod",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "ga_lastYearForPreviousPeriod",
                "metrics": [],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "",
                "sort": [
                    "ga:year"
                ],
                "comparison": {
                    "comp_period": "year_over_year",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": "1,2"
            }
        },
        {
            "_id": "sc_ImpressionsLast2Month",
            "form": {
                "moduleName": "GoogleSearchConsoleSearchAnalyticsVariable",
                "varName": "sc_ImpressionsLast2Month",
                "metrics": [
                    "impressions"
                ],
                "calculatedMetrics": [],
                "dimensions": [],
                "timePeriod": {
                    "type": "Past Month"
                },
                "searchType": "web",
                "aggregationType": "auto",
                "filtersExpression": "",
                "sort": [],
                "comparison": {
                    "comp_period": "prev_period",
                    "comp_type": "value"
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "sc_ImpressionsLast2MonthYoY",
            "form": {
                "moduleName": "GoogleSearchConsoleSearchAnalyticsVariable",
                "varName": "sc_ImpressionsLast2MonthYoY",
                "metrics": [
                    "impressions"
                ],
                "calculatedMetrics": [],
                "dimensions": [],
                "timePeriod": {
                    "type": "Past Month"
                },
                "searchType": "web",
                "aggregationType": "auto",
                "filtersExpression": "",
                "sort": [],
                "comparison": {
                    "comp_period": "year_over_year",
                    "comp_type": "value"
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "sc_ClicksLast2Month",
            "form": {
                "moduleName": "GoogleSearchConsoleSearchAnalyticsVariable",
                "varName": "sc_ClicksLast2Month",
                "metrics": [
                    "clicks"
                ],
                "calculatedMetrics": [],
                "dimensions": [],
                "timePeriod": {
                    "type": "Past Month"
                },
                "searchType": "web",
                "aggregationType": "auto",
                "filtersExpression": "",
                "sort": [],
                "comparison": {
                    "comp_period": "prev_period",
                    "comp_type": "value"
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "sc_ClicksLast2MonthYoY",
            "form": {
                "moduleName": "GoogleSearchConsoleSearchAnalyticsVariable",
                "varName": "sc_ClicksLast2MonthYoY",
                "metrics": [
                    "clicks"
                ],
                "calculatedMetrics": [],
                "dimensions": [],
                "timePeriod": {
                    "type": "Past Month"
                },
                "searchType": "web",
                "aggregationType": "auto",
                "filtersExpression": "",
                "sort": [],
                "comparison": {
                    "comp_period": "year_over_year",
                    "comp_type": "value"
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "sc_positionLast2Month",
            "form": {
                "moduleName": "GoogleSearchConsoleSearchAnalyticsVariable",
                "varName": "sc_positionLast2Month",
                "metrics": [
                    "position"
                ],
                "calculatedMetrics": [],
                "dimensions": [],
                "timePeriod": {
                    "type": "Past Month"
                },
                "searchType": "web",
                "aggregationType": "auto",
                "filtersExpression": "",
                "sort": [],
                "comparison": {
                    "comp_period": "prev_period",
                    "comp_type": "value"
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "sc_positionLast2MonthYoY",
            "form": {
                "moduleName": "GoogleSearchConsoleSearchAnalyticsVariable",
                "varName": "sc_positionLast2MonthYoY",
                "metrics": [
                    "position"
                ],
                "calculatedMetrics": [],
                "dimensions": [],
                "timePeriod": {
                    "type": "Past Month"
                },
                "searchType": "web",
                "aggregationType": "auto",
                "filtersExpression": "",
                "sort": [],
                "comparison": {
                    "comp_period": "year_over_year",
                    "comp_type": "value"
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "ga_sessionLast2Month",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "ga_sessionLast2Month",
                "metrics": [
                    "ga:sessions"
                ],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "ga:sourceMedium==google / organic",
                "sort": [],
                "comparison": {
                    "comp_period": "prev_period",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "ga_sessionLast2MonthYoY",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "ga_sessionLast2MonthYoY",
                "metrics": [
                    "ga:sessions"
                ],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "ga:sourceMedium==google / organic",
                "sort": [],
                "comparison": {
                    "comp_period": "year_over_year",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "ga_conversionRateLast2Month",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "ga_conversionRateLast2Month",
                "metrics": [
                    "ga:transactionsPerSession"
                ],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "ga:sourceMedium==google / organic",
                "sort": [],
                "comparison": {
                    "comp_period": "prev_period",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "ga_conversionRateLast2MonthYoY",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "ga_conversionRateLast2MonthYoY",
                "metrics": [
                    "ga:transactionsPerSession"
                ],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "ga:sourceMedium==google / organic",
                "sort": [],
                "comparison": {
                    "comp_period": "year_over_year",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "ga_organicRevenueLast2Month",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "ga_organicRevenueLast2Month",
                "metrics": [
                    "ga:transactionRevenue"
                ],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "ga:sourceMedium==google / organic",
                "sort": [],
                "comparison": {
                    "comp_period": "prev_period",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "ga_organicRevenueLast2MonthYoY",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "ga_organicRevenueLast2MonthYoY",
                "metrics": [
                    "ga:transactionRevenue"
                ],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "ga:sourceMedium==google / organic",
                "sort": [],
                "comparison": {
                    "comp_period": "year_over_year",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "organicAvgSessionDuration_last2Month",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "organicAvgSessionDuration_last2Month",
                "metrics": [
                    "ga:avgSessionDuration"
                ],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "ga:sourceMedium==google / organic",
                "sort": [],
                "comparison": {
                    "comp_period": "prev_period",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": ""
            },
            "createdInsight": ""
        },
        {
            "_id": "ga_avgSessionDurationPastMonth_YoY",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "ga_avgSessionDurationPastMonth_YoY",
                "metrics": [
                    "ga:avgSessionDuration"
                ],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "ga:sourceMedium==google / organic",
                "sort": [],
                "comparison": {
                    "comp_period": "year_over_year",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": ""
            }
        },
        {
            "_id": "ga_AllGoalCompletionsLast2Month",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "ga_AllGoalCompletionsLast2Month",
                "metrics": [
                    "ga:goalCompletionsAll"
                ],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "ga:sourceMedium==google / organic",
                "sort": [],
                "comparison": {
                    "comp_period": "prev_period",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": ""
            },
            "createdInsight": ""
        },
        {
            "_id": "ga_allGoalCompletionsLast2MonthYoY",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "ga_allGoalCompletionsLast2MonthYoY",
                "metrics": [
                    "ga:goalCompletionsAll"
                ],
                "calculatedMetrics": [],
                "dimensions": [
                    "ga:year"
                ],
                "timePeriod": {
                    "type": "Past Month"
                },
                "filtersExpression": "ga:sourceMedium==google / organic",
                "sort": [],
                "comparison": {
                    "comp_period": "year_over_year",
                    "comp_type": "value"
                },
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                },
                "tableFunction": ""
            },
            "createdInsight": ""
        },
        {
            "_id": "table_organicImpressionsLast2Month",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT 'Organic Impressions' KPI, \na.Impressions {PastMonth}, a.'Previous Period Impressions' {PreviousPastMonth},\nROUND(((a.Impressions-a.'Previous Period Impressions')/a.'Previous Period Impressions')*100,2) '% Monthly Change', \nb.'YoY Impressions' {ga_lastYearForPreviousPeriod}{PastMonth},\nROUND(((a.Impressions-b.'YoY Impressions')/b.'YoY Impressions')*100,2) '% YOY Change'\nFROM \n(SELECT 'Impressions' KPI, \n\tSUM(a.'Compared Value impressions') Impressions, \n\tSUM(a.impressions) 'Previous Period Impressions' \n\tFROM {sc_ImpressionsLast2Month} a) a \nINNER JOIN (SELECT 'Impressions' KPI, \nSUM(a.'Compared Value impressions') Impressions,\nSUM(a.impressions) 'YoY Impressions'\nFROM {sc_ImpressionsLast2MonthYoY} a ORDER BY 1 desc) b \nON a.KPI=b.KPI",
                "tableFunction": "",
                "varName": "table_organicImpressionsLast2Month"
            }
        },
        {
            "_id": "table_organicClicksLast2Month",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT 'Organic Clicks' KPI, \na.Clicks {PastMonth}, a.'Previous Period Clicks' {PreviousPastMonth},\nROUND(((a.Clicks-a.'Previous Period Clicks')/a.'Previous Period Clicks')*100,2) '% Monthly Change', \nb.'YoY Clicks' {ga_lastYearForPreviousPeriod}{PastMonth},\nROUND(((a.Clicks-b.'YoY Clicks')/b.'YoY Clicks')*100,2) '% YOY Change'\nFROM \n(SELECT 'Clicks' KPI, \n\tSUM(a.'Compared Value clicks') Clicks, \n\tSUM(a.clicks) 'Previous Period Clicks' \n\tFROM {sc_ClicksLast2Month} a) a \nINNER JOIN (SELECT 'Clicks' KPI, \nSUM(a.'Compared Value clicks') Clicks,\nSUM(a.clicks) 'YoY Clicks'\nFROM {sc_ClicksLast2MonthYoY} a ORDER BY 1 desc) b \nON a.KPI=b.KPI",
                "tableFunction": "",
                "varName": "table_organicClicksLast2Month"
            }
        },
        {
            "_id": "table_organicPositionLast2Month",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT 'Organic Search Avg. Position' KPI, \na.'Avg Position' {PastMonth}, a.'Previous Period Avg Position' {PreviousPastMonth},\nROUND(((a.'Avg Position'-a.'Previous Period Avg Position')/a.'Previous Period Avg Position')*100,2) '% Monthly Change', \nb.'YoY Avg Position' {ga_lastYearForPreviousPeriod}{PastMonth},\nROUND(((a.'Avg Position'-b.'YoY Avg Position')/b.'YoY Avg Position')*100,2) '% YOY Change'\nFROM \n(SELECT 'Avg Position' KPI, \n\tROUND(SUM(a.'Compared Value position')) 'Avg Position', \n\tROUND(SUM(a.position)) 'Previous Period Avg Position' \n\tFROM {sc_positionLast2Month} a) a \nINNER JOIN (SELECT 'Avg Position' KPI, \nROUND(SUM(a.'Compared Value position')) Impressions,\nROUND(SUM(a.position)) 'YoY Avg Position'\nFROM {sc_positionLast2MonthYoY} a ORDER BY 1 desc) b \nON a.KPI=b.KPI",
                "tableFunction": "",
                "varName": "table_organicPositionLast2Month"
            }
        },
        {
            "_id": "table_organicSessionLast2Month",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT 'Sessions' KPI, \na.Sessions {PastMonth}, a.'Previous Period Sessions'  {PreviousPastMonth},\nROUND(((a.Sessions-a.'Previous Period Sessions')/a.'Previous Period Sessions')*100,2) '% Monthly Change', \nb.'YoY Sessions' {ga_lastYearForPreviousPeriod}{PastMonth},\nROUND(((a.Sessions-b.'YoY Sessions')/b.'YoY Sessions')*100,2) '% YOY Change'\nFROM \n(SELECT a.'ga:year', \n\tSUM(a.'ga:sessions') Sessions, \n\tSUM(a.'Compared Value ga:sessions') 'Previous Period Sessions' \n\tFROM {ga_sessionLast2Month} a) a \nINNER JOIN (SELECT a.'ga:year', \nSUM(a.'ga:sessions') Sessions,\nSUM(a.'Compared Value ga:sessions') 'YoY Sessions'\nFROM {ga_sessionLast2MonthYoY} a ORDER BY 1 desc) b \nON a.'ga:year'=b.'ga:year'\n",
                "tableFunction": "",
                "varName": "table_organicSessionLast2Month"
            }
        },
        {
            "_id": "table_organicConversionRateLast2Month",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT 'Conversion Rate' KPI, \na.CR||'%' {PastMonth}, a.'Previous Period CR'||'%' {PreviousPastMonth},\nROUND(((a.CR-a.'Previous Period CR')/a.'Previous Period CR')*100,2) '% Monthly Change', \nb.'YoY CR'||'%' {ga_lastYearForPreviousPeriod}{PastMonth},\nROUND(((a.CR-b.'YoY CR')/b.'YoY CR')*100,2) '% YOY Change'\nFROM \n(SELECT a.'ga:year', \n\tROUND(SUM(a.'ga:transactionsPerSession'),2) CR, \n\tROUND(SUM(a.'Compared Value ga:transactionsPerSession'),2) 'Previous Period CR' \n\tFROM {ga_conversionRateLast2Month} a) a \nINNER JOIN (SELECT a.'ga:year', \nROUND(SUM(a.'ga:transactionsPerSession'),2) CR,\nROUND(SUM(a.'Compared Value ga:transactionsPerSession'),2) 'YoY CR'\nFROM {ga_conversionRateLast2MonthYoY} a ORDER BY 1 desc) b \nON a.'ga:year'=b.'ga:year'",
                "tableFunction": "",
                "varName": "table_organicConversionRateLast2Month"
            }
        },
        {
            "_id": "table_organicRevenueLast2Month",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT 'Revenue' KPI, \na.Revenue {PastMonth}, a.'Previous Period Revenue' {PreviousPastMonth},\nROUND(((a.Revenue-a.'Previous Period Revenue')/a.'Previous Period Revenue')*100,2) '% Monthly Change', \nb.'YoY Revenue' {ga_lastYearForPreviousPeriod}{PastMonth},\nROUND(((a.Revenue-b.'YoY Revenue')/b.'YoY Revenue')*100,2) '% YOY Change'\nFROM \n(SELECT a.'ga:year', \n\tSUM(a.'ga:transactionRevenue') Revenue, \n\tSUM(a.'Compared Value ga:transactionRevenue') 'Previous Period Revenue' \n\tFROM {ga_organicRevenueLast2Month} a) a \nINNER JOIN (SELECT a.'ga:year', \nSUM(a.'ga:transactionRevenue') Revenue,\nSUM(a.'Compared Value ga:transactionRevenue') 'YoY Revenue'\nFROM {ga_organicRevenueLast2MonthYoY} a ORDER BY 1 desc) b \nON a.'ga:year'=b.'ga:year'",
                "tableFunction": "",
                "varName": "table_organicRevenueLast2Month"
            }
        },
        {
            "_id": "table_avgSessionDurationLast2Month",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT 'Avg Session Duration' KPI, \ntime(time((a.'ga:avgSessionDuration'/86400)), '-12 hours') {PastMonth}, \ntime(time((a.'Compared Value ga:avgSessionDuration'/86400)), '-12 hours') {PreviousPastMonth},\nROUND(((a.'ga:avgSessionDuration'-a.'Compared Value ga:avgSessionDuration')/a.'Compared Value ga:avgSessionDuration')*100,2) '% Monthly Change', time(time((b.'Compared Value ga:avgSessionDuration'/86400)), '-12 hours') {ga_lastYearForPreviousPeriod}{PastMonth},\nROUND(((a.'ga:avgSessionDuration'-b.'Compared Value ga:avgSessionDuration')/b.'Compared Value ga:avgSessionDuration')*100,2) '% YOY Change'\nFROM \n(SELECT a.'ga:year', \n\tSUM(a.'ga:avgSessionDuration') 'ga:avgSessionDuration', \n\tSUM(a.'Compared Value ga:avgSessionDuration') 'Compared Value ga:avgSessionDuration' \n\tFROM {organicAvgSessionDuration_last2Month} a) a \nINNER JOIN (SELECT a.'ga:year', \nSUM(a.'ga:avgSessionDuration') 'ga:avgSessionDuration',\nSUM(a.'Compared Value ga:avgSessionDuration') 'Compared Value ga:avgSessionDuration'\nFROM {ga_avgSessionDurationPastMonth_YoY} a ORDER BY 1 desc) b \nON a.'ga:year'=b.'ga:year'",
                "tableFunction": "",
                "varName": "table_avgSessionDurationLast2Month"
            }
        },
        {
            "_id": "table_organicAllGoalCompletionsLast2Month",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT 'All Goal Completions' KPI, \na.'All Goal Completions' {PastMonth}, a.'Previous Period All Goal Completions' {PreviousPastMonth},\nROUND(((a.'All Goal Completions'-a.'Previous Period All Goal Completions')/a.'Previous Period All Goal Completions')*100,2) '% Monthly Change', \nb.'YoY All Goal Completions' {ga_lastYearForPreviousPeriod}{PastMonth},\nROUND(((a.'All Goal Completions'-b.'YoY All Goal Completions')/b.'YoY All Goal Completions')*100,2) '% YOY Change'\nFROM \n(SELECT a.'ga:year', \n\tROUND(SUM(a.'ga:goalCompletionsAll'),2) 'All Goal Completions', \n\tROUND(SUM(a.'Compared Value ga:goalCompletionsAll'),2) 'Previous Period All Goal Completions' \n\tFROM {ga_AllGoalCompletionsLast2Month} a) a \nINNER JOIN (SELECT a.'ga:year', \nROUND(SUM(a.'ga:goalCompletionsAll'),2) 'All Goal Completions',\nROUND(SUM(a.'Compared Value ga:goalCompletionsAll'),2) 'YoY All Goal Completions'\nFROM {ga_allGoalCompletionsLast2MonthYoY} a ORDER BY 1 desc) b \nON a.'ga:year'=b.'ga:year'",
                "tableFunction": "",
                "varName": "table_organicAllGoalCompletionsLast2Month"
            },
            "createdInsight": ""
        },
        {
            "_id": "value_TotalRevenue_last30days",
            "form": {
                "moduleName": "GoogleAnalyticsReportingVariable",
                "varName": "value_TotalRevenue_last30days",
                "metrics": [
                    "ga:transactionRevenue"
                ],
                "calculatedMetrics": [],
                "dimensions": [],
                "timePeriod": {
                    "type": "Last 30 Day"
                },
                "filtersExpression": "",
                "sort": [],
                "comparison": {},
                "tableFunction": "(1,2)",
                "samplingLevel": "LARGE",
                "segment": {
                    "userSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    },
                    "sessionSegment": {
                        "segmentFilters": [
                            {
                                "simpleSegment": {
                                    "orFiltersForSegment": []
                                }
                            }
                        ]
                    }
                }
            },
            "createdInsight": ""
        },
        {
            "_id": "table_SEOKpiList",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT * FROM {table_organicImpressionsLast2Month}\nUNION ALL \nSELECT * FROM {table_organicClicksLast2Month}\nUNION ALL\nSELECT * FROM {table_organicPositionLast2Month}\nUNION ALL\nSELECT * FROM {table_organicSessionLast2Month}\nUNION ALL\nSELECT * FROM {table_organicConversionRateLast2Month}\nUNION ALL\nSELECT * FROM {table_organicRevenueLast2Month}\nUNION ALL \nSELECT * FROM {table_avgSessionDurationLast2Month}",
                "tableFunction": "",
                "varName": "table_SEOKpiList"
            }
        },
        {
            "_id": "table_SEOKpiListWithAllGoalCompletions",
            "form": {
                "moduleName": "SQLVariable",
                "query": "SELECT * FROM {table_organicImpressionsLast2Month}\nUNION ALL \nSELECT * FROM {table_organicClicksLast2Month}\nUNION ALL\nSELECT * FROM {table_organicPositionLast2Month}\nUNION ALL\nSELECT * FROM {table_organicSessionLast2Month}\nUNION ALL \nSELECT * FROM {table_avgSessionDurationLast2Month}\nUNION ALL\nSELECT * FROM {table_organicAllGoalCompletionsLast2Month}",
                "tableFunction": "",
                "varName": "table_SEOKpiListWithAllGoalCompletions"
            },
            "createdInsight": ""
        }
    ],
    "packageName": "",
    "taskID": "",
    "reportID": "",
    "clientID": "60c1c542eef4c9bdd9cf18eb",
    "packageID": "",
    "clientEmail": "hey@heybooster.ai",
    "insights": {
        "moduleName": "MultiTriggerText",
        "scenarios": [
            {
                "trigger": [
                    [
                        {
                            "A": "1",
                            "B": "0",
                            "operator": "GREATER THAN"
                        }
                    ]
                ],
                "exception": [
                    [
                        {
                            "A": "{value_TotalRevenue_last30days}",
                            "B": "0",
                            "operator": "EQUAL TO"
                        }
                    ]
                ],
                "title": "On the Key 7 Metrics of Organic Search Performances, you can compare last month's results with those from the previous month and last year.",
                "shortTitle": "SEO KPI tracking chart",
                "actions": "",
                "dashboard": {
                    "type": "table",
                    "data": "{table_SEOKpiList}"
                },
                "priority": "Report"
            },
            {
                "trigger": [
                    [
                        {
                            "A": "1",
                            "B": "0",
                            "operator": "GREATER THAN"
                        }
                    ]
                ],
                "exception": [],
                "title": "On the Key 7 Metrics of Organic Search Performances, you can compare last month's results with those from the previous month and last year.",
                "shortTitle": "SEO KPI tracking chart",
                "actions": "",
                "dashboard": {
                    "type": "table",
                    "data": "{table_SEOKpiListWithAllGoalCompletions}"
                },
                "priority": "Report"
            }
        ],
        "defaultScenario": {
            "title": "On the Key 7 Metrics of Organic Search Performances, you can compare last month's results with those from the previous month and last year.",
            "shortTitle": "SEO KPI tracking chart",
            "actions": "",
            "dashboard": {
                "type": "table",
                "data": "{table_SEOKpiListWithAllGoalCompletions}"
            },
            "priority": "Report"
        },
        "businessObjective": "Brand Awareness",
        "department": "Analytics",
        "package": [
            "organic-performance",
            "decathlon-seo-performance",
            "turknet-organic-performance"
        ],
        "_id": "614a230e1673155f850631b1"
    },
    "requiredInput": {},
    "client_id": "82a54ec5543e1b453098afe3be8d2973",
    "datasource": [
        {
            "_id": {
                "$oid": "62272def96a31865ed126191"
            },
            "clientID": {
                "$oid": "60c1c542eef4c9bdd9cf18eb"
            },
            "shop_name": "heybooster-dev-store",
            "shopify_accesstoken": "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",
            "dataSourceName": "Demo Client | Shopify",
            "email": "hey@heybooster.ai",
            "sourceType": "Shopify",
            "status": true
        },
        {
            "_id": {
                "$oid": "622f234748e6a11136ec82f2"
            },
            "email": "hey@heybooster.ai",
            "clientID": {
                "$oid": "60c1c542eef4c9bdd9cf18eb"
            },
            "sourceType": "Google Analytics",
            "dataSourceName": "Demo Client  |   Google Analytics",
            "accountID": "47759514",
            "accountName": "HediyeSepeti",
            "propertyID": "UA-47759514-1",
            "propertyName": "Hediye Sepeti - UA - Web",
            "viewID": "138215830",
            "currency": "TRY",
            "viewName": "hediyesepeti.com - web - e-ticaret",
            "status": true,
            "createdTS": 1647256385.6583273
        },
        {
            "_id": {
                "$oid": "622f23813dd29388caf06a8c"
            },
            "email": "hey@heybooster.ai",
            "clientID": {
                "$oid": "60c1c542eef4c9bdd9cf18eb"
            },
            "sourceType": "Google Adwords",
            "dataSourceName": "Demo Client  |   Google Adwords",
            "accountID": 8006180328,
            "accountName": "HediyeSepeti",
            "currency": null,
            "status": true,
            "createdTS": 1647256411.7165265
        },
        {
            "_id": {
                "$oid": "622f24c348e6a11136ec8324"
            },
            "email": "hey@heybooster.ai",
            "clientID": {
                "$oid": "60c1c542eef4c9bdd9cf18eb"
            },
            "sourceType": "Google Search Console",
            "dataSourceName": "Demo Client  |   Google Search Console",
            "siteUrl": "https://www.hediyesepeti.com/",
            "status": true,
            "createdTS": 1647256767.9793723
        },
        {
            "_id": {
                "$oid": "622f24db48e6a11136ec8329"
            },
            "email": "hey@heybooster.ai",
            "clientID": {
                "$oid": "60c1c542eef4c9bdd9cf18eb"
            },
            "sourceType": "Facebook",
            "dataSourceName": "Demo Client  |   Facebook",
            "accountID": "act_114408972005787",
            "accountName": "Mısırlı 1951",
            "currency": "TRY",
            "status": true,
            "createdTS": 1647256792.4814637
        }
    ]
}
Editor is loading...