Need need help with the efficiency-accounting crypt, according to



  • I write analyst, according to the system, it has been possible to calculate the number of queues said. But there are a few problems:

    (1) We need to remove / at the end of the word, e.g., the statistic on the word of a new year's tree and a new year's tree, and we need to count them in and out. How do you take that into account in the sample?

    (2) It's like the base to calculate the number of leathers that were said to have. When visiting the visitor, a visitor is assigned to hash_person, he records utm tags to the stat database when the client leaves the application hash_person is recorded with contact to the lids database. How do you put the number of leathers into the table, for example, by the word "new-time tree"?

    Skin what happened: введите сюда описание изображения Code:

    $source_p = mysql_query("                               
    SELECT
    `utm_term`, COUNT(`utm_term`) AS `cnt`
    FROM 
    `stat`
    WHERE
     id_cabinet = '$id_cabinet' $filtr_date
    GROUP BY 
    `utm_term`");
    if (mysql_num_rows($source_p) > 0) {
    

    while ($source_c = mysql_fetch_array($source_p)) {

    if ($source_c['utm_term'] !== '') {
    $title_source = $source_c['utm_term'];
    } else {
    $title_source = "Нет данных";
    }
    // попытка подсчитать количество лидов по фразе
    $result_stat_leads = mysql_query("SELECT * FROM stat WHERE utm_term = '".$source_c['utm_term']."'" ,$db);

    while ($stat_leads = mysql_fetch_array($result_stat_leads)) {

    $result_count_leads = mysql_query("SELECT * FROM lids WHERE hash_person = '".$stat_leads['hash_person']."'" ,$db);

    while ($count_stat_leads = mysql_fetch_array($result_count_leads)) {
    $count_leads = ""; // количество лидов
    }

    }

    $count_view = $source_c['cnt']; // количество заходов на сайт по фразе

    echo "Слово: $title_source | Лидов: $count_leads";

    }

    Table structure:

    --
    -- Структура таблицы lids

    CREATE TABLE IF NOT EXISTS lids (
    id int(10) NOT NULL AUTO_INCREMENT,
    id_cabinet int(11) NOT NULL,
    id_staff int(10) NOT NULL,
    hash_person varchar(100) NOT NULL,
    name text NOT NULL,
    tel text NOT NULL,
    email text NOT NULL,
    information text NOT NULL,
    comment text NOT NULL,
    address text NOT NULL,
    note text NOT NULL,
    datetime datetime NOT NULL,
    date_last_edit datetime NOT NULL,
    source text NOT NULL,
    add_vid text NOT NULL,
    idform int(10) NOT NULL,
    id_type int(10) NOT NULL,
    hash varchar(32) NOT NULL,
    status_old int(10) NOT NULL,
    status int(15) NOT NULL,
    delete_status int(11) NOT NULL,
    status_view int(1) NOT NULL,
    PRIMARY KEY (id)
    ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=35997 ;

    --
    -- Структура таблицы stat

    CREATE TABLE IF NOT EXISTS stat (
    id int(100) NOT NULL AUTO_INCREMENT,
    id_cabinet int(11) NOT NULL,
    hash_person varchar(35) NOT NULL COMMENT 'id зашедшего на сайт',
    number_visit int(11) NOT NULL COMMENT 'Номер визита',
    id_lead int(10) NOT NULL,
    referrer_site varchar(255) NOT NULL,
    site_project varchar(255) NOT NULL,
    page_site_project varchar(300) NOT NULL,
    title_page varchar(255) NOT NULL,
    country varchar(100) NOT NULL,
    region varchar(100) NOT NULL,
    city varchar(100) NOT NULL,
    utm_source text NOT NULL COMMENT 'обозначает источник рекламного трафика (Google, Яндекс, Email, Blog)',
    utm_medium text NOT NULL COMMENT 'определяет тип маркетингового канала (PPC, КМС, ретаргетинг, рассылка, пост)',
    utm_campaign text NOT NULL COMMENT 'соответствует названию маркетинговой кампании или рекламному объявлению или местоположению баннера',
    utm_term text NOT NULL COMMENT 'параметр, содержащий в себе ключевое слово PPC кампании',
    utm_content text NOT NULL COMMENT 'отображает контент объявления, что помогает различать разные рекламные объявления, содержащие одно и то же ключевое слово. ',
    utm_block text NOT NULL COMMENT 'Тип площадки: premium – спецразмещение other – блок внизу none – блок не на поиске Яндекса',
    utm_position text NOT NULL COMMENT 'Позиция в рекламном блоке',
    utm_ad text NOT NULL COMMENT 'Номер (ID) объявления',
    utm_phrase text NOT NULL COMMENT 'Номер (ID) ключевой фразы',
    yclid varchar(100) NOT NULL COMMENT 'Идентификатор яндекса',
    datetime datetime NOT NULL,
    PRIMARY KEY (id)
    ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=251420 ;

    I'm confused and I don't know how to do it anymore.



  • There is still little information on the matter. Like, it's not clear what it is. id_cabinet And why in the php crypt, you only take it into account when computing the total number by keyword, and when you count lid for the word, you do not take it into account. Although the request contained in the comments id_cabinet checks for equality everywhere. I think it should be applied to all tables. And in your php, you also have to assume that there are other filters that I understand can affect the number of leathers but not visits.

    select REPLACE(utm_term,'/','') word,
           count(distinct ST.id) stat_count,
           count(distinct ST.hash_person) person_stat_count,
           count(distinct L.id) lids_count,
           count(distinct L.hash_person) lids_person
      from stat ST
      left join lids L on L.hash_person=ST.hash_person and L.id_cabinet=0 and L.delete_status=0
     where ST.id_cabinet=0
     group by REPLACE(utm_term,'/','')
    

    Based on the available information, I decided that there was no need for sub-requests and the counting of unique values of different fields would yield the required result. stat_count - the total number of records in the stat according to this word, person_stat_count The voice of unique visitors, lids_count - By ordering people who were in the statistics on the word, lids_person - unique buyers (excluding multiple orders from one hash_person)

    But something tells me you think the weather is in the america. Even though you're supposed to be hash_person♪ Suppose I went to your site several times, according to different key words, and finally made one order. In that case lids will be counted for all the key words where I was.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2