מגלים את הסביבה התחרותית

SELECT competitive_visibility.domain,
       competitive_visibility.category_id,
       competitive_visibility.country_code,
       competitive_visibility.traffic_source,
       competitive_visibility.is_your_domain,
       competitive_visibility.rank,
       competitive_visibility.ads_organic_ratio,
       competitive_visibility.page_overlap_rate,
       competitive_visibility.higher_position_rate,
       competitive_visibility.relative_visibility
FROM CompetitiveVisibilityCompetitorView
WHERE competitive_visibility.date BETWEEN '2023-06-02' AND '2023-06-27'
  AND competitive_visibility.category_id = 536
  AND competitive_visibility.country_code = 'US'
  AND competitive_visibility.traffic_source = 'ADS'
ORDER BY competitive_visibility.rank 

{
  "results": [
    {
      "competitiveVisibility": {
        "domain": "examplepetstore.com",
        "isYourDomain": false,
        "countryCode": "US",
        "categoryId": "536",
        "trafficSource": "ADS",
        "rank": "4",
        "adsOrganicRatio": 8,
        "pageOverlapRate": 0.19,
        "higherPositionRate": 0.46,
        "relativeVisibility": 0.5
      }
    },
    {
      "competitiveVisibility": {
        "domain": "example.com",
        "isYourDomain": true,
        "countryCode": "US",
        "categoryId": "536",
        "trafficSource": "ADS",
        "rank": "5",
        "adsOrganicRatio": 30,
        "pageOverlapRate": 1,
        "higherPositionRate": 0,
        "relativeVisibility": 0
      }
    },
  ]
}

SELECT
  competitive_visibility.domain,
  competitive_visibility.category_id,
  competitive_visibility.country_code,
  competitive_visibility.traffic_source,
  competitive_visibility.is_your_domain,
  competitive_visibility.rank,
  competitive_visibility.ads_organic_ratio,
  competitive_visibility.page_overlap_rate,
  competitive_visibility.higher_position_rate
FROM CompetitiveVisibilityTopMerchantView
WHERE competitive_visibility.date BETWEEN '2022-08-01' AND '2022-09-01'
  AND competitive_visibility.category_id = 100
  AND competitive_visibility.country_code = 'US'
  AND competitive_visibility.traffic_source = 'ORGANIC'
ORDER BY competitive_visibility.rank

{
  "results": [
    {
      "competitiveVisibility": {
        "domain": "examplepetstore.com",
        "isYourDomain": false,
        "countryCode": "US",
        "categoryId": "100",
        "trafficSource": "ORGANIC",
        "rank": "1",
        "adsOrganicRatio": 0.2,
        "pageOverlapRate": 0.3,
        "higherPositionRate": 0.45
      }
    },
    {
      "competitiveVisibility": {
        "domain": "example.com",
        "isYourDomain": false,
        "countryCode": "US",
        "categoryId": "100",
        "trafficSource": "ORGANIC",
        "rank": "2",
        "adsOrganicRatio": 5,
        "pageOverlapRate": 0.67,
        "higherPositionRate": 0.89
      }
    }
  ]
}

SELECT
  competitive_visibility.category_id,
  competitive_visibility.country_code,
  competitive_visibility.traffic_source,
  competitive_visibility.date,
  competitive_visibility.your_domain_visibility_trend,
  competitive_visibility.category_benchmark_visibility_trend
FROM CompetitiveVisibilityBenchmarkView
WHERE competitive_visibility.date BETWEEN '2022-09-01' AND '2022-09-07'
  AND competitive_visibility.category_id = 100
  AND competitive_visibility.country_code = 'US'
  AND competitive_visibility.traffic_source = 'ORGANIC'
ORDER BY competitive_visibility.date

{
  "results": [
    {
      "competitiveVisibility": {
        "date": {
          "year": 2022,
          "month": 9,
          "day": 1
        },
        "countryCode": "US",
        "categoryId": "100",
        "trafficSource": "ORGANIC",
        "yourDomainVisibilityTrend": 0,
        "categoryBenchmarkVisibilityTrend": 0
      }
    },
    {
      "competitiveVisibility": {
        "date": {
          "year": 2022,
          "month": 9,
          "day": 2
        },
        "countryCode": "US",
        "categoryId": "100",
        "trafficSource": "ORGANIC",
        "yourDomainVisibilityTrend": 0.3,
        "categoryBenchmarkVisibilityTrend": -0.15
      }
    },
  ]
}