Verilerle nasıl çalışılır

Not

Fonksiyon yalnızca Yandex Metrica Pro paketi için mevcuttur.

Yandex Metrica servisinin toplanmamış verilerini Yandex Cloud üzerinde yer alan kendi Clickhouse kümenize alabilirsiniz.

Bu entegrasyon mekanizması, LogsAPI göre şu farklılıklara sahiptir:

  • Entegrasyon, genişletilmiş bir alan seti içerir.

  • LogsAPI’den farklı olarak, ziyaretlerde ilişkilendirme dizilerle yer alır. Tüm diziler TrafficSource.XXX ilişkilendirme türüne göre birbiriyle ilişkilidir. XXX alanında gerekli ilişkilendirmede YYY: TrafficSource.XXX[indexOf(TrafficSource.Model, YYY)] as XXX

    Ziyaretler nasıl doğru şekilde dışa aktarılır

  • Ziyaretlerde FirstPartyCookie, LogsAPI’deki clientid ile aynıdır.

Dikkat

Entegrasyon, Logs API ile veri formatı açısından geriye dönük uyumlu değildir.

Trafik kaynağı dizilerini analiz etmek için komut dosyaları

Komut dosyaları her kullanıcı için bir geçiş zinciri oluşturur, bu da şunları elde etmeyi sağlar:

  • Tüm kaynaklardan ilişkilendirilmiş dönüşümler raporu.
  • Yandex Metrica arayüzünde bulunmayanlar da dahil olmak üzere farklı ilişkilendirme modellerinde raporlar, örneğin doğrusal.

Bu bilgiye dayanarak, dönüşümden önceki en popüler trafik kaynağı dizilerini kendiniz hesaplayabilirsiniz.

Komut dosyaları GitHub üzerinde yayınlanmıştır.

Ziyaretler nasıl doğru şekilde dışa aktarılır

Not

Ziyaretlerdeki veriler, onlarla ilgili yeni bilgiler geldikçe güncellenir. Ortalama olarak, ziyaretlerin %99’u başlangıçlarından itibaren 3 gün içinde tamamlanır.

Çok sayıda alan içeren örnek
SELECT
    VisitID,
    CounterID,
    StartDate,
    CounterUserIDHash, -- Metrica’nın kullandığı dahili UserID
    FirstPartyCookie, -- LogsAPI’de ClientID ile aynı şey
    Duration,
    EAction.Type,
    EndURL,
    Goals.ID,
    IsBounce,
    IsMobile,
    OS,
    OSFamily,
    OSName,
    PageViews,
    Referer,
    RegionID,
    StartURL,
    TrafficSource.ID, -- TrafficSource.ID alanlarındaki değerler şunları ifade eder: 
{-1: iç geçişler; 0: doğrudan girişler; 
1: sitelerdeki bağlantılara tıklama aracılığıyla geçişler; 2: arama motorlarından geçişler;
3: reklamlara tıklama aracılığıyla geçişler; 4: kaydedilmiş sayfalardan geçişler;
5: Belirtilmemiş; 6: dış bağlantılardan geçişler; 7: e-posta bültenlerinden geçişler;
8: sosyal ağlardan geçişler; 9: öneri sistemlerinden geçişler; 
10: mesajlaşma uygulamalarından geçişler; 11: karekod aracılığıyla geçişler}
    TrafficSource.StrID, -- trafik kaynağının insan tarafından okunabilir adı
    TrafficSource.Model, -- Tüm TrafficSource.XXX dizileri birbirleriyle ilişkilidir.
Dizilerdeki öğelerin sırası, TrafficSource.Model’e göre TraficSource.XXX alanının değerinin nasıl hesaplandığını gösterir ve dizinin içinde TraficSource.XXX’in kendi değeri bulunur
    TrafficSource.ID[indexOf(TrafficSource.Model, 1)] as last_TraficSourceID, -- Son geçişe göre trafik kaynağı
    TrafficSource.ID[indexOf(TrafficSource.Model, 2)] as last_significant_TraficSourceID, -- Son anlamlı geçiş ilişkilendirme modeline göre trafik kaynağı
    TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as first_TraficSourceID, -- İlk geçişe göre trafik kaynağı
    TrafficSource.ID[indexOf(TrafficSource.Model, 4)] as last_yandex_direct_TraficSourceID, -- Yandex Direct’e göre son anlamlı geçiş ilişkilendirme modelindeki trafik kaynağı
    TrafficSource.ID[indexOf(TrafficSource.Model, 5)] as cd_last_significant_TraficSourceID, -- Son anlamlı geçiş (cihazlar arası) ilişkilendirme modeline göre trafik kaynağı
    TrafficSource.ID[indexOf(TrafficSource.Model, 6)] as cd_first_TraficSourceID, -- İlk geçiş (cihazlar arası) ilişkilendirme modeline göre trafik kaynağı
    TrafficSource.ID[indexOf(TrafficSource.Model, 7)] as cd_last_yandex_direct_TraficSourceID, -- Yandex Direct’e göre son anlamlı geçiş (cihazlar arası) ilişkilendirme modelindeki trafik kaynağı
    
    -- Trafik kaynağı reklam veya tanıtımsa hangi reklam ağı olduğu görülebilir. 
Son anlamlı trafik kaynağı örneği:
    If(last_significant_TraficSourceID = 3, TrafficSource.AdvEnginePlaceStrID[indexOf(TrafficSource.Model, 2)], 'not_ad') as last_significant_adv_engine_id,
    -- Aynı durum diğer trafik kaynakları için de geçerlidir: sosyal ağlar, öneri sistemleri, vb.
    UserAgent,
    WatchIDs -- hits_all’daki WatchID ile bağlantı için. Bu sütuna IsParameter = 1 olan hitler girmez. Dizi 500 hiti aştığında, geri kalanlar atılır
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- kendi veritabanınızı ve ziyaret tablonuzu buraya ekleyin
WHERE StartDate = today() – 1 --buraya herhangi bir tarih girin, örneğin StartDate = toDate('2022-02-01') veya StartDate = '2022-02-01'
    AND CounterID = toUInt32(24226447)
GROUP BY
    VisitID,
    CounterID,
    StartDate,
    CounterUserIDHash,
    FirstPartyCookie,
    Duration,
    EAction.Type,
    EndURL,
    Goals.ID,
    IsBounce,
    IsMobile,
    OS,
    OSFamily,
    OSName,
    PageViews,
    Referer,
    RegionID,
    StartURL,
    TrafficSource.Model,
    TrafficSource.ID,
    TrafficSource.StrID,
    last_TraficSourceID,
    last_significant_TraficSourceID,
    first_TraficSourceID, 
    last_yandex_direct_TraficSourceID,
    cd_last_significant_TraficSourceID,
    cd_first_TraficSourceID,
    cd_last_yandex_direct_TraficSourceID,
    last_significant_adv_engine_id,
    last_significant_search_engine_id,
    UserAgent,
    WatchIDs
HAVING sum(Sign) = 1
limit 1000
  • Ziyaretler, örneğin çevrimdışı dönüşüm bağlandığında geçmişe doğru güncellenebilir.
  • Entegrasyon etkinleştirildiğinde, ilk ziyaretlerin sum(Sign) değeri tutarsız olabilir.

sum(Sign) kullanılmazsa ne olur

Bağlayıcı loglarında, daraltılmamış ziyaret versiyonları (yani bir ziyaretin birkaç versiyonu) bulunur. Eğer bunlar daraltılmazsa, veriler tutarsız olacak ve aynı ziyaret (eski versiyonları) 1’den fazla kez dikkate alınacaktır.

Bu, ziyaret güncellendiğinde eski versiyonun (VisitVersion = 1, Sign = 1) silinmemesi nedeniyle olur. Bunun yerine, sadece işaret farkıyla tamamen eski versiyona benzeyen bir satır eklenir Sign (VisitVersion = 1, Sign = -1). Daha sonra, pozitif Sign ile güncellenmiş ziyaret versiyonu eklenir (VisitVersion = 2, Sign = 1). Bu şekilde, ilgilenilen alanlara göre group by yaparak, sum(Sign) ile doğru ve tutarlı ziyaret sayısını elde ederiz, çünkü geçersiz versiyonlar (Sign = 1 ve Sign = -1 sıfıra dönüşür).

Loglarda daraltılmamış ziyaret örneği
select VisitID, VisitVersion, Sign
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- buraya kendi veritabanınızı ve ziyaret tablonuzu ekleyin
where StartDate = today()-3 -- buraya herhangi bir tarih ekleyebilirsiniz
    AND (CounterID = toUInt32(24226447)) -- buraya kendi sayaç numaranızı ekleyin
    and VisitID in (select VisitID
                    from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- buraya kendi veritabanınızı ve ziyaret tablonuzu ekleyin
                    where StartDate = today()-3 -- buraya herhangi bir tarih ekleyebilirsiniz
                    group by VisitID
                    having count(distinct VisitVersion) > 3 -- örnek olması için 3’ten fazla değişikliği olan bir ziyaret alıyoruz (başka bir sayı da kullanabilirsiniz)
                    order by VisitID desc
                    limit 1 -- örnek olması için yalnızca 1 ziyaret alıyoruz; daha fazla ziyaret de alabilirsiniz
                    ) 
order by VisitID, VisitVersion, Sign

Doğru daraltma örneği
select VisitID, sum(Sign) as visits
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- buraya veritabanınızı ve ziyaret tablonuzu ekleyin
where StartDate = today()-3 -- buraya herhangi bir tarih ekleyebilirsiniz
    AND (CounterID = toUInt32(24226447)) -- buraya sayaç numaranızı ekleyin
    and VisitID = 1243431264677003301
group by VisitID

Tablo adından sonra final yapısını kullanarak daraltma örneği

final tabloyu, versiyonların zaten daraltılmış olduğu şekilde işler. Gereksiz ziyaret versiyonlarını kendiliğinden kaldırır. Gruplama ve sum(Sign) ile saymaya göre çok daha uzun sürer.

select VisitID, VisitVersion, Sign
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- buraya kendi veritabanınızı ve ziyaret tablonuzu ekleyin
where StartDate = today()-3 -- buraya herhangi bir tarih ekleyebilirsiniz
    AND (CounterID = toUInt32(24226447))
    and VisitID = 1243431264677003301
order by VisitID, VisitVersion, Sign

Hitler nasıl dışa aktarılır

Hit nedir

Not

Hitlerle çalışırken, versiyonlamalarını da dikkate almak gerekir. Ziyaretlerle benzer şekilde, bir hit (WatchID) birkaç HitVersion içerebilir ve bunlar Sign alanı yardımıyla daraltılabilir. Hitleri, Ziyaretler nasıl doğru şekilde dışa aktarılır bölümünde açıklandığı gibi dışa aktarmak önemlidir. Ayrıca final yapısının kullanılması da kabul edilir.

Örnek
select CounterID,
        EventDate,
        CounterUserIDHash, -- Metrica’nın kullandığı dahili UserID
        FirstPartyCookie, -- LogsAPI’de ClientID ile aynı şey
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL, -- URL, OriginalURL’den farklı olarak kısaltılabilir
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
from yandex_data_transfer_test.hits_dttql4la13mb206q472r -- kendi veritabanınızı ve hit tablonuzu buraya ekleyin
where EventDate = today()-1 -- buraya herhangi bir tarih ekleyebilirsiniz
   and CounterID = 24226447 -- kendi sayaç numaranızı buraya ekleyin
group by CounterID,
        EventDate,
        CounterUserIDHash, -- Metrica’nın kullandığı dahili UserID
        FirstPartyCookie, -- LogsAPI’de ClientID ile aynı şey
        UTCEventTime,
        WatchID,
        Referer,
        OriginalURL, -- URL, OriginalURL’den farklı olarak kısaltılabilir
        URL,
        UTMSource,
        IsMobile,
        OS,
        OSFamily,
        OSName,
        FirstPartyCookie,
        IsArtifical,
        IsDownload,
        IsLink,
        IsNotBounce,
        IsPageView,
        IsParameter
having sum(Sign) = 1

Ziyaret hitleriyle nasıl ilişkilendirilir

VisitID’yi ona ait hitlerle (WatchID) ilişkilendirmek için WatchIDs sütunu yeterli olmayabilir. Bu, WatchID alanında ziyaret parametre hitlerinin bulunmamasıyla ilgilidir. Ziyarete dahil olan hitlerin listesini kendiniz oluşturabilirsiniz. Bunun için ziyaretin başlangıç tarihi, bitiş tarihi ve ziyaretçi kimliği gereklidir.

Örnek
select VisitID, -- bu SELECT sorgusunda ziyaretler, toplanan hit dizisiyle birlikte yer alır
        CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime,
        groupArray(WatchID) as `watchids.id`,
        groupArray(IsPageView) as `watchids.is_page_view`,
        groupArray(IsParameter) as `watchids.is_parameter`,
        groupArray(UTCEventTime) as `watchids.is_utc_event_time`
from ( -- bu SELECT sorgusunda ziyaretler her hit için çoğaltılır
select VisitID,
        a.CounterUserIDHash as CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime,
        WatchID,
        IsPageView,
        IsParameter,
        UTCEventTime
from 
(select -- ziyaretleri, kullanıcıyı, ziyaretin başlangıç ve bitiş tarihlerini alıyoruz. Ziyaret geçmişe doğru güncellenmeye devam edebilir!
    VisitID,
    CounterUserIDHash,
    UTCStartTime,
    Duration,
    toDateTime(UTCStartTime) + Duration as UTCEndTime
from yandex_data_transfer_test.visits_dttql4la13mb206q472r final
where StartDate = '2023-04-01'
) as a
left join
(select -- hitleri, kullanıcıyı, hitin tarih ve saatini alıyoruz. Hitlere ait tarihi veriler değişebilir.
    WatchID,
    CounterUserIDHash,
    IsPageView,
    IsParameter,
    UTCEventTime
from yandex_data_transfer_test.hits_dttql4la13mb206q472r final
where EventDate >= '2023-04-01' 
        and EventDate <= toDate('2023-04-01')+5
        ) as b
on a.CounterUserIDHash = b.CounterUserIDHash -- özellikle ziyaretçinin bu ID'si esas alınmalıdır
where UTCEventTime >= UTCStartTime and -- hitler, ziyaret başlangıcından daha önce olmamalı
        UTCEventTime <= UTCEndTime -- hitler, ziyaret bitişinden daha sonra olmamalı
order by CounterUserIDHash, UTCEventTime
)
group by VisitID,
        CounterUserIDHash,
        UTCStartTime,
        Duration,
        UTCEndTime
limit 100

“Ziyaret” raporu

Grafikler 
SELECT StartDate AS `ym:s:date`, 
        sum(Sign) AS `ym:s:visits` -- ziyaretin birden fazla sürümünü doğru biçimde en son ve güncel sürümde birleştirme ve ziyaret sayısını hesaplama
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- kendi veritabanınızı ve ziyaret tablonuzu buraya ekleyin
as `default.visits_all` 
WHERE `ym:s:date` >= toDate('2023-01-31') -- bu sürümde bağlayıcı oluşturulmadan önceki geçmiş veriler desteklenmez
        and `ym:s:date` <= toDate('2023-02-06') -- “bugün”e ait veriler (ve daha sonraki günlere ilişkin yavaş güncellemeler, örneğin çevrimdışı dönüşümler) arayüze kıyasla gecikmeli olarak gelebilir
        and CounterID = 24226447 -- kendi sayaç numaranızla değiştirin
GROUP BY `ym:s:date` 
WITH TOTALS
HAVING `ym:s:visits` >= 0.0 
ORDER BY `ym:s:date` ASC 
limit 0,7
Tablo
SELECT
    toDate(StartDate) AS `ym:s:datePeriodday`,
    sum(Sign) AS `ym:s:visits`,
    uniqExact(CounterUserIDHash) AS `ym:s:users`,
    sum(PageViews * Sign) AS `ym:s:pageviews`,
    uniqExactIf(CounterUserIDHash, (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 3)]) = (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 1)])) / uniqExact(CounterUserIDHash) * 100. AS `ym:s:percentNewVisitors`, -- 3, “İlk ziyaret” ilişkilendirme modelidir; 1 ise “Son ziyaret” ilişkilendirme modelidir
    100. * (sum(IsBounce * Sign) / `ym:s:visits`) AS `ym:s:bounceRate`,
    `ym:s:pageviews` / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * Sign) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
   -- “Robotluk” ve "Cihazlar arası ziyaretçiler" metrikleri bağlayıcıda kullanılamaz
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- buraya kendi veritabanınızı ve ziyaret tablonuzu ekleyin
AS `default.visits_all`
WHERE (StartDate >= toDate('2023-03-10'))
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = toUInt32(24226447)) -- kendi sayaç numaranızla değiştirin
GROUP BY `ym:s:datePeriodday`
WITH TOTALS
HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.) OR (`ym:s:pageviews` > 0.)
ORDER BY `ym:s:datePeriodday` DESC
LIMIT 0, 50

“UTM etiketleri” raporu

Örnek
SELECT
    `TrafficSource.UTMSource`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignUTMSource`,
    sum(Sign) AS `ym:s:visits`,
    least(uniqExact(CounterUserIDHash), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * Sign) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * Sign) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * Sign) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`,
    sumArray(arrayMap(x → (if(isFinite(x), x, 0) * Sign), arrayMap(x_0 → toInt64(notEmpty(x_0)), `EPurchase.ID`))) AS `ym:s:ecommercePurchases`
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- buraya kendi veritabanınızı ve ziyaret tablonuzu ekleyin
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- buraya kendi sayaç numaranızı ekleyin
        AND (`ym:s:lastSignUTMSource` != '')
GROUP BY `ym:s:lastSignUTMSource`
HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.) OR (`ym:s:ecommercePurchases` > 0.)
ORDER BY
    `ym:s:visits` DESC,
    `ym:s:lastSignUTMSource` ASC
LIMIT 0, 50
Örnek
SELECT
    URLHash(URL, toInt8(0)) AS `ym:pv:URLPathLevel1Hash`,
    anyHeavyIf(domain(URL), domain(URL) != '') AS `ym:pv:URLPathLevel1HashFavicon`,
    max(URLHierarchy(ifNull(URL, ''))[1]) AS `ym:pv:URLPathLevel1`,
    sum(Sign * W) AS `ym:pv:pageviews`,
    least(uniq(CounterUserIDHash), `ym:pv:pageviews`) AS `ym:pv:users`
FROM yandex_data_transfer_test.hits_dttql4la13mb206q472r AS `default.hits_all`
WHERE (EventDate >= toDate('2023-03-10')) 
        AND (EventDate <= toDate('2023-03-16')) 
        and CounterID = 24226447 -- buraya kendi sayaç numaranızı girin
       and IsPageView
GROUP BY `ym:pv:URLPathLevel1Hash`
HAVING `ym:pv:pageviews` > 0.0 AND (`ym:pv:pageviews` > 0.0 OR `ym:pv:users` > 0.0)
    WITH TOTALS
ORDER BY
    `ym:pv:pageviews` DESC,
    `ym:pv:URLPathLevel1` ASC,
    `ym:pv:URLPathLevel1Hash` ASC
LIMIT 0, 50

“Hedef dönüşümü” raporu

Örnek
WITH 1. AS W, 17069575 as my_goal_id -- hedef numaranızla değiştirin
SELECT
    toDate(StartDate) AS `ym:s:datePeriodday`,
    100. * (sum(has(`Goals.ID`, my_goal_id) * (Sign * W)) / sum(Sign * W)) AS `ym:s:goal17069575conversionRate`,
    sum(arrayCount(x → (my_goal_id = x), `Goals.ID`) * (Sign * W)) AS `ym:s:goal17069575reaches`,
    sumIf(Sign * W, arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`)) AS `ym:s:goal17069575visits`,
    least(toFloat64(uniqIf(CounterUserIDHash, arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`))), `ym:s:goal17069575visits`) AS `ym:s:goal17069575users`,
    sumIf(PageViews * (Sign * W), arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`)) AS `ym:s:goal17069575pageviews`,
    (least(uniqIf(CounterUserIDHash, ((`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 3)]) = (`TrafficSource.StartTime`[indexOf(`TrafficSource.Model`, 1)])) AND arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`)), uniqIf(CounterUserIDHash, arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`))) / uniqIf(CounterUserIDHash, arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`))) * 100. AS `ym:s:goal17069575percentNewVisitors`,
    100. * (sumIf(IsBounce * (Sign * W), arrayExists(x_0 → (x_0 = my_goal_id), `Goals.ID`)) / `ym:s:goal17069575visits`) AS `ym:s:goal17069575bounceRate`,
    `ym:s:goal17069575pageviews` / `ym:s:goal17069575visits` AS `ym:s:goal17069575pageDepth`,
    sumIf(Duration * (Sign * W), arrayExists(x_0 -> (x_0 = my_goal_id), `Goals.ID`)) / `ym:s:goal17069575visits` AS `ym:s:goal17069575avgVisitDurationSeconds`
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- kendi veritabanınızı ve ziyaret tablonuzu buraya ekleyin
WHERE (StartDate >= toDate('2023-02-18')) 
        AND (StartDate <= toDate('2023-03-17')) 
        AND (CounterID = 24226447) -- sayaç numaranızla değiştirin
GROUP BY `ym:s:datePeriodday`
HAVING (`ym:s:goal17069575reaches` > 0.) AND ((`ym:s:goal17069575reaches` > 0.) OR (`ym:s:goal17069575visits` > 0.) OR (`ym:s:goal17069575users` > 0.) OR (`ym:s:goal17069575pageviews` > 0.))
ORDER BY `ym:s:datePeriodday` DESC
LIMIT 0, 50

"Kaynaklar, Özet" raporu

Tablo
WITH 1. AS W
SELECT
    `TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSource`,
    sum(Sign * W) AS `ym:s:visits`,
    least(toFloat64(uniq(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- buraya kendi veritabanınızı ve ziyaret tablonuzu ekleyin
WHERE (StartDate >= toDate('2023-03-10')) 
        AND (StartDate <= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- kendi sayaç numaranızla değiştirin
GROUP BY `ym:s:lastSignTrafficSource`
    WITH TOTALS
HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.)
ORDER BY
    `ym:s:visits` DESC,
    `ym:s:lastSignTrafficSource` ASC
LIMIT 0, 50
Detaylı tablo
WITH 1. AS W
SELECT
    `TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSource`,
    `TrafficSource.StrID`[indexOf(`TrafficSource.Model`, 2)] AS `ym:s:lastSignTrafficSourceName`,
     
    if(
        ((`TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)]) != '') AND 
            (`ym:s:lastSignTrafficSource` IN (-1, toInt8(1))), 
        `TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)], 
        if(`ym:s:lastSignTrafficSource` = toInt8(2), 
            `TrafficSource.SearchEngineStrID`[indexOf(`TrafficSource.Model`, 2)], 
            if(`ym:s:lastSignTrafficSource` = toInt8(3), `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)], 
                if(`ym:s:lastSignTrafficSource` = toInt8(8), toString(`TrafficSource.SocialSourceNetworkStrID`[indexOf(`TrafficSource.Model`, 2)]), 
                  if(`ym:s:lastSignTrafficSource` = toInt8(9), toString(if((`TrafficSource.RecommendationSystemID`[indexOf(`TrafficSource.Model`, 2)]) = 0, '1', `TrafficSource.RecommendationSystemStrID`[indexOf(`TrafficSource.Model`, 2)])), 
                     if(`ym:s:lastSignTrafficSource` = toInt8(10), toString(if((`TrafficSource.MessengerID`[indexOf(`TrafficSource.Model`, 2)]) = 0, '1', `TrafficSource.MessengerStrID`[indexOf(`TrafficSource.Model`, 2)])), 
                         if(`ym:s:lastSignTrafficSource` = toInt8(11), toString(`TrafficSource.QRCodeProviderStrID`[indexOf(`TrafficSource.Model`, 2)]), 
                                ''
                             )
                       )
                     )
                   )
               )
          )
       ) AS `ym:s:lastSignSourceEngine`,
    anyHeavy(if(`ym:s:lastSignTrafficSource` IN (-1, toInt8(1)), concatAssumeInjective('http://', `TrafficSource.Domain`[indexOf(`TrafficSource.Model`, 2)]), '')) AS `ym:s:lastSignSourceEngineURL`,
    sum(Sign * W) AS `ym:s:visits`,
    least(toFloat64(uniqExact(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
FROM yandex_data_transfer_test.visits_dttql4la13mb206q472r -- veritabanınızı ve ziyaret tablonuzu buraya ekleyin
WHERE (StartDate >= toDate('2023-03-10')) 
        and (StartDate >= toDate('2023-03-16')) 
        AND (CounterID = 24226447) -- kendi sayaç numaranızla değiştirin
GROUP BY
    `ym:s:lastSignTrafficSource`,
    `ym:s:lastSignTrafficSourceName`,
    `ym:s:lastSignSourceEngine`
    WITH TOTALS
HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.)
ORDER BY
    `ym:s:visits` DESC,
    `ym:s:lastSignTrafficSource` ASC,
    `ym:s:lastSignSourceEngine` ASC
LIMIT 0, 50
Tablo
WITH 1. AS W
SELECT
    `TrafficSource.AdvEnginePlaceStrID`[indexOf(`TrafficSource.Model`, 2)] as `ym:s:lastSignAdvEngine`, -- “Son anlamlı geçiş” ilişkilendirme modeline göre reklam ağı
    sum(Sign * W) AS `ym:s:visits`,
    least(toFloat64(uniqExact(CounterUserIDHash)), `ym:s:visits`) AS `ym:s:users`,
    100. * (sum(IsBounce * (Sign * W)) / `ym:s:visits`) AS `ym:s:bounceRate`,
    sum(PageViews * (Sign * W)) / `ym:s:visits` AS `ym:s:pageDepth`,
    sum(Duration * (Sign * W)) / `ym:s:visits` AS `ym:s:avgVisitDurationSeconds`
from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- buraya kendi veritabanınızı ve ziyaret tablonuzu ekleyin
WHERE (StartDate = toDate('2023-03-15')) 
        AND (CounterID = 24226447) -- kendi sayaç numaranızla değiştirin
        AND (`ym:s:lastSignAdvEngine` != '') 
        AND ((`TrafficSource.ID`[indexOf(`TrafficSource.Model`, 2)]) = toInt8(3)) -- “Son anlamlı geçiş” ilişkilendirme modeline göre “Reklam” trafik kaynağı
GROUP BY `ym:s:lastSignAdvEngine`
    WITH TOTALS
HAVING (`ym:s:visits` > 0.) OR (`ym:s:users` > 0.)
ORDER BY
    `ym:s:visits` DESC,
    `ym:s:lastSignAdvEngine` ASC
LIMIT 0, 50

“Ecommerce olayları hunisi” raporu (Yandex Metrica arayüzünde yok)

Entegrasyon, Yandex Metrica’da olmayan daha karmaşık raporlar oluşturmanıza olanak tanır. Örneğin, ecommerce olayları için bir huni oluşturmak.

Bu rapor için şu koşullara uymanızı öneririz:

  • Detail, add, purchase olayları ayarlanmış ve doğru bir şekilde iletilmiştir.
  • Her bir dilimde en az 10 ziyaretçi.
  • İlk adıma dönüşüm %1’den fazla.
  • Kesitler kullanılıyorsa, yeterli veri mevcuttur. Çeşitli dilimlerde ecom olayları hunisi raporu
Rapor örneği 
select counter_id,
        step0_users, -- ziyaretçilerin toplam sayısı
        step1_users, -- ürünleri görüntüleyen ziyaretçiler
        step2_users, -- ürünleri görüntüleyen ve daha sonra bunları sepete ekleyen ziyaretçiler
        step3_users, -- ürünleri görüntüleyen, daha sonra sepete ekleyen
 ve ardından satın alma işlemi yapan ziyaretçiler
        round(step0_users/step0_users*100, 4) as perc_step0, -- toplam ziyaretçi sayısının %’si
        round(step1_users/step0_users*100, 4) as perc_step1, -- ürünleri gören ziyaretçilerin %’si
        round(step2_users/step0_users*100, 4) as perc_step2, -- ürünleri görüp sepete ekleme işlemini gerçekleştiren ziyaretçilerin %’si
        round(step3_users/step0_users*100, 4) as perc_step3 -- ürünleri görüp sepete ekledikten sonra satın alan ziyaretçilerin %’si

from

(select 
        counter_id,
        sum(step_1) as step1_users,
        sum(step_2) as step2_users,
        sum(step_3) as step3_users
    from
        (select
            CounterID as counter_id,
            CounterUserIDHash as user_id,
            max(e.Type = 1) as step_1, -- sadece ürünlerin görüntülenmesi
            sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- ürünlerin görüntülenmesi ve ardından sepete eklenmesi
            sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- ürünlerin görüntülenmesi, sepete eklenmesi ve satın alınması
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                e.Type, -- e-ticaret olaylarının türleri (1 – ayrıntı, 2 – sepet durumu, 3 – satın alma, 4 – sepete ekleme, 5 – sepetten silme)
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- kendi veritabanınızı ve ziyaret tablonuzu buraya ekleyin
            array join EAction as e -- arrayJoin, bir dizi e-ticaret olayını ayrı satırlara böler
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28')
                and CounterID = 24226447 -- kendi sayaç numaranızla değiştirin
                ) 
        group by counter_id, user_id
        )
    group by counter_id) as a

inner join (select
        CounterID as counter_id,
        uniqExact(CounterUserIDHash) as step0_users 
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- kendi veritabanınızı ve ziyaret tablonuzu buraya ekleyin
    where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- kendi sayaç numaranızla değiştirin
    group by counter_id) as b
on a.counter_id = b.counter_id
Görselleştirme örneği

“Çeşitli dilimlerde ecomerce olayları hunisi” raporu (Yandex Metrica arayüzünde yok)

Ayrıca, trafik kaynağı, işletim sistemi, cihaz gibi çeşitli dilimlerde bir huni oluşturabilirsiniz.

IsMobile diliminde huni oluşturma örneği 
select counter_id,
        is_mobile,
        step0_users, -- ziyaretçilerin toplam sayısı
        step1_users, -- ürünleri görüntüleyen ziyaretçiler
        step2_users, -- ürünleri görüntüleyen ve daha sonra bunları sepete ekleyen ziyaretçiler
        step3_users, -- ürünleri görüntüleyen, daha sonra sepete ekleyen ve ardından satın alma işlemi yapan ziyaretçiler
        round(step0_users/step0_users*100, 4) as perc_step0, -- toplam ziyaretçi sayısının %'si
        round(step1_users/step0_users*100, 4) as perc_step1, -- ürünleri gören ziyaretçilerin %'si
        round(step2_users/step0_users*100, 4) as perc_step2, -- ürünleri görüp sepete ekleme işlemini gerçekleştiren ziyaretçilerin %'si
        round(step3_users/step0_users*100, 4) as perc_step3 -- ürünleri görüp sepete ekledikten sonra satın alan ziyaretçilerin %'si

from

(select 
        counter_id,
        is_mobile,
        sum(step_1) as step1_users,
        sum(step_2) as step2_users,
        sum(step_3) as step3_users
    from
        (select
            CounterID as counter_id,
            CounterUserIDHash as user_id,
            is_mobile,
            max(e.Type = 1) as step_1, -- sadece ürünlerin görüntülenmesi
            sequenceMatch('(?1)(?2)')(e.EventTime, (e.Type = 1), (e.Type = 4)) as step_2, -- ürünlerin görüntülenmesi ve ardından sepete eklenmesi
            sequenceMatch('(?1)(?2)(?3)')(e.EventTime, (e.Type = 1), (e.Type = 4), (e.Type = 3)) as step_3 -- ürünlerin görüntülenmesi, sepete eklenmesi ve satın alınması
        from 
            (select 
                CounterUserIDHash,
                CounterID,
                IsMobile as is_mobile,
                e.Type, -- e-ticaret olaylarının türleri (1 – ayrıntı, 2 – sepet durumu, 3 – satın alma, 4 – sepete ekleme, 5 – sepetten silme)
                e.EventTime
            from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- veritabanınızı ve ziyaret tablonuzu buraya ekleyin
            array join EAction as e -- arrayJoin, bir dizi e-ticaret olayını ayrı satırlara böler
            where (StartDate >= '2023-02-01')
                and (StartDate <= '2023-02-28')
                and CounterID = 24226447 -- kendi sayaç numaranızla değiştirin
                ) 
        group by counter_id, user_id, is_mobile
        )
    group by counter_id, is_mobile) as a

inner join (select
        CounterID as counter_id,
        IsMobile as is_mobile,
        uniqExact(CounterUserIDHash) as step0_users 
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- veritabanınızı ve ziyaret tablonuzu buraya ekleyin
    where (StartDate >= '2023-02-01') 
        and (StartDate <= '2023-02-28')
        and CounterID = 24226447 -- kendi sayaç numaranızla değiştirin
    group by counter_id, is_mobile) as b
on a.counter_id = b.counter_id and a.is_mobile = b.is_mobile
Görselleştirme örneği

“Yeni ziyaretçilerin tutulması” raporu (Yandex Metrica arayüzünde yok)

Örnek
with main as 

(select 
        counter_id,
        num_week,
        uniq(user_id) as users
from
    (select 
        CounterUserIDHash as user_id,
        CounterID as counter_id,
        toDate(FirstVisit) as first_date, -- ziyaretçinin siteye ilk ziyareti
        StartDate as event_date,
        (toMonday(event_date) - toMonday(first_date))/7 as num_week
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r final -- veritabanınızı ve ziyaret tablonuzu buraya ekleyin
    where event_date >= '2022-12-01' -- 15 haftalık dönem
        and event_date <= toDate('2022-12-31') + 92 -- 15 haftalık dönem
        and first_date >= '2022-12-01' -- sadece Aralık ayında gelen yeni ziyaretçileri alıyoruz
        and first_date <= '2022-12-31' -- sadece Aralık ayında gelen yeni ziyaretçileri alıyoruz
        and counter_id = 24226447 -- kendi sayaç numaranızla değiştirin
    )
group by 
    counter_id,
    num_week
order by num_week)

select counter_id, 
        a.users as users, 
        b.users as users_first_week,
        round(a.users/b.users*100, 4) as perc_retention
from main as a
inner join (select * from main where num_week = 0) as b
on a.counter_id = b.counter_id
Görselleştirme örneği

“Çeşitli dilimlerde yeni ziyaretçilerin tutulması” raporu (Yandex Metrica arayüzünde yok)

Ayrıca bir dilim ekleyebilir ve farklı dilimlerdeki tutmayı karşılaştırabilirsiniz. Dilimde en az 30 ziyaretçi olmasını ve dilimin toplam sayının en az %5’ini oluşturmasını öneririz.

Ziyaretçinin ilk ziyaretinin çeşitli trafik kaynaklarına göre tutulma örneği
with main as 

(select 
        counter_id,
        param,
        num_week,
        uniq(user_id) as users
from
    (select 
        CounterUserIDHash as user_id,
        CounterID as counter_id,
        toDate(FirstVisit) as first_date, -- ziyaretçinin siteye ilk ziyareti
        StartDate as event_date,
        (toMonday(event_date) - toMonday(first_date))/7 as num_week,
        TrafficSource.ID[indexOf(TrafficSource.Model, 3)] as param
    from yandex_data_transfer_test.visits_dttql4la13mb206q472r -- kendi veritabanınızı ve ziyaret tablonuzu buraya ekleyin
    where event_date >= '2022-12-01' -- 15 haftalık dönem
        and event_date <= toDate('2022-12-31') + 92 -- 15 haftalık dönem
        and first_date >= '2022-12-01' -- yalnızca 
Aralık ayında gelen yeni ziyaretçileri alıyoruz
        and first_date <= '2022-12-31' -- yalnızca 
Aralık ayında gelen yeni ziyaretçileri alıyoruz
        and counter_id = 24226447 -- kendi sayaç numaranızla değiştirin
    )
group by 
    counter_id,
    param,
    num_week,
    param
order by param, num_week)

select counter_id, 
        param,
        a.users as users, 
        b.users as users_first_week,
        round(a.users/b.users*100, 4) as perc_retention
from main as a
inner join (select * from main where num_week = 0) as b
on a.counter_id = b.counter_id and a.param = b.param

Sohbete yaz

E-posta yaz

Lütfen, dikkatli olun: Destek Ekibi kullanıcıları aramaz. Dolayısıyla Yandex Metrica Destek Ekibi adına sizi telefonla arayan yabancıları yok sayın ve talimatlarını kesinlikle izlemeyin.

Faydalı bağlantılar

Çevrimiçi eğitim
Önceki
Data Streaming (Yandex Cloud ile entegrasyon)
Sonraki
Dışa aktarılan veri alanlarının listesi – hitler (eylemler)