通过使用Analytics's API在sitecore中获得访问次数最多的页面

本文关键字：访问 sitecore Analytics API | 更新日期: 2023-09-27 18:06:47

我想通过使用Analytics的API获得sitecore中访问量最大的页面。

我使用下面的代码来得到这个:

Sitecore.Analytics.Data.DataAccess.DataSets.VisitorDataSet.PagesDataTable pagesDataTable = new VisitorDataSet.PagesDataTable();
var Count = pagesDataTable.Count(p => p.ItemId.ToString() == "{B0358D8A-A3B7-4D99-B123-78E7B1CA9F83}");

但是它总是给我0计数。我的Analytics数据库已启用。我不确定我是否做错了什么。

通过使用Analytics's API在sitecore中获得访问次数最多的页面

结果是0因为你将它实例化为new VisitorDataSet.PagesdataTable()

在我的解决方案中，我做了这样的事情:

var connectionString = ConfigurationManager.ConnectionStrings["analytics"].ConnectionString;
var builder = new SqlConnectionStringBuilder(connectionString);
var query = "SELECT itemid, count(Url) as NumberOfVisits FROM [" + builder.InitialCatalog + "].[dbo].[Pages] group by itemid order by NumberOfVisits desc";

虽然这显然没有使用任何最佳实践(硬编码SQL)，而是使用SQL来获取数据，但它确实做到了。

这是我们在最新版本的Sitecore(目前为7.2)上使用的

    //Sitecore query syntax
    //You must use the symbols {0} and {1} around all table and field names. 
    //You must use the symbols {2} and {3} around parameters. 
    //You must use the symbol {4} to insert a quote and symbol {5} to insert a wildcard. 
    //You must use the symbol {6} to insert an empty string literal. 
    private const string _trendingQueryBase = @"SELECT TOP {0} {{0}}ItemId{{1}}, COUNT(*) as {{0}}Views{{1}}
                                                FROM {{0}}Pages{{1}}
                                                WHERE {{0}}DateTime{{1}} >= GETDATE() - {1}
                                                AND {{0}}Url{{1}} LIKE {{4}}/stories/{{5}}{{4}}
                                                GROUP BY {{0}}ItemId{{1}}
                                                ORDER BY {{0}}Views{{1}} DESC";
    public IEnumerable<Guid> Get(int? topPagesToGet = 5, int? fromPastDays = 7)
    {
        var trendingQuery = string.Format(_trendingQueryBase, topPagesToGet, fromPastDays);
        var result = DataAdapterManager.Sql.ReadMany(trendingQuery, reader => DataAdapterManager.Sql.GetGuid(0, reader));
        return result;
    }

您可以使用以下功能获得访问次数最多的页面- 

public List<Guid> GetMostPopularArticles()
            {
                string where = "Where it.TemplateID = {2}TemplateCondition{3} OR it.templateID ={2}SecoundTemplateId{3} ";
                string CommandText = @"SELECT TOP (100) PERCENT pa.ItemId, it.TemplateID, pa.PageViewCount 
                                                FROM (SELECT {0}ItemId{1},COUNT(*) AS {0}PageViewCount{1} 
                                                    FROM [lp_analytics].[dbo].[Pages] 
                                                        GROUP BY {0}ItemId{1}) AS pa 
                                                            INNER JOIN  [lp_master].[dbo].[Items] AS it ON  pa.ItemId =  it.ID 
                                                " + where + " ORDER BY pa.PageViewCount DESC";
                string firstTemplateId = ArticlePageItem.TemplateId.ToString().Replace("{", "").Replace("}", "");
                string secoundTemplateId = RecipePageItem.TemplateId.ToString().Replace("{", "").Replace("}", "");
                List<Guid> visitsId = DataAdapterManager.Sql.ReadMany<Guid>(CommandText, reader => DataAdapterManager.Sql.GetGuid(0, reader), new object[] { "TemplateCondition", firstTemplateId, "SecoundTemplateId", secoundTemplateId });
                return visitsId;
            }
相关文章：