I'm new to the analytics part and working on a report to show visitor drop-offs, which in our case is - No. of visitors for a day vs Those who added items to cart vs Went to checkout vs Completed the transaction.

My SQL query is as follows:

SELECT fpv.ItemId,i.Name, SUM(fpv.Visits) AS Vists,
CASE WHEN fpv.ItemId = 'A0C87CA3-AAA6-441F-BBB3-DB7F7900F395' THEN 1 
WHEN fpv.ItemId = 'A7D9C743-6E92-4CEF-8626-5C67D2AABF61' THEN 2
WHEN fpv.ItemId = 'A4852323-9562-4130-BB49-1A3DC3C1C8C1' THEN 3
FROM Fact_PageViews fpv
LEFT JOIN [demo.local_Sitecore.master].[dbo].[Items] i ON fpv.ItemId = i.ID
WHERE CONVERT(date, fpv.Date) = '2018-10-11' AND
fpv.ItemId IN ('{A0C87CA3-AAA6-441F-BBB3-DB7F7900F395}', '{A7D9C743-6E92-4CEF-8626-5C67D2AABF61}', '{A4852323-9562-4130-BB49-1A3DC3C1C8C1}')
GROUP BY fpv.ItemId, i.Name

and the result is :

ItemId                                  Name                Vists
A0C87CA3-AAA6-441F-BBB3-DB7F7900F395    Booking             7   
A7D9C743-6E92-4CEF-8626-5C67D2AABF61    Checkout            2   
A4852323-9562-4130-BB49-1A3DC3C1C8C1    OrderConfirmation   2   

The drop-offs is the difference in site visitors & completed transactions.

  1. Is the query correct for what I'm trying to achieve.

  2. How should I get a day's Site visits in this context.

    SELECT SUM(Visits) FROM Fact_PageViews WHERE CONVERT(date, Date) = '2018-10-11'

Is that correct. But that includes all the 3 pages (specified above) as well. So, I'm confused whether to include/exclude them to calculate a drop-off.

Using Sitecore 8.2 update 5


I think that your query is not correct as you assume that checkout and order confirmation pages are visited by unique visitors in this query.

This could be wrong assumption as lot's of times people are going to checkout and catalog pages back and forth before buying something (and effectively going to Confirmation page at the end).

Better solution would be to leveraging Goal functionality like this:

  1. You trigger goal once someone visits booking, checkout and order confirmation page.

    You can associate goal on items (pages) following this Sitecore documentation -> https://doc.sitecore.net/sitecore_experience_platform/82/digital_marketing/campaigns/goals__events/associate_a_goal_with_an_item.

    You can also do it programmatically similarly to this code:

    public static bool TriggerGoal(Sitecore.Data.ID goalId)
    bool triggeredGoal = false;
    //Sitecore.Diagnostics.Log.Info(string.Format("Triggering Goal: {0}",goalId.ToString()), "xDBHelper");
        //start tracker if not active
        if (!Sitecore.Analytics.Tracker.IsActive)
        //additional check to ensure that Tracker is active and has a CurrentPage 
        if (Sitecore.Analytics.Tracker.IsActive && Sitecore.Analytics.Tracker.Current.CurrentPage != null)
            // Look up the goal item
            var goalItem = Sitecore.Context.Database.GetItem(goalId); 
            if (goalItem != null)
                //Sitecore.Diagnostics.Log.Info(string.Format("Triggering Goal Item: {0}", goalItem["Name"]), "xDBHelper");
                var goal = new PageEventItem(goalItem); // Wrap goal in a PageEventItem
                var pageEventsRow = Tracker.Current.CurrentPage.Register(goal); // Create PageEventData for the goal to be stored
                pageEventsRow.Data = goalItem["Name"]; //Add name of goal to Data field --- not sure this is needed
                Sitecore.Analytics.Tracker.Current.Interaction.AcceptModifications(); //updates current interaction's end date time
                triggeredGoal = true;
    catch (Exception ex)
        //if you can't track log a warning but do impact user for analytics issues
        Sitecore.Diagnostics.Log.Warn(string.Format("Failed to trigger goal: {0}", ex.ToString()), "xDBHelper");
    return triggeredGoal;

    Code taken from http://sitecorenj.blogspot.com/2016/09/programmatically-trigger-goal-in.html

  2. Then you can create similar query as you already have but not using Fact_PageViews but Fact_Conversions fact table instead. You can then easily compare number of Checkout and OrderConfirmation goals triggered during the day per unique visitors (contact id). You will use Date, ContactId and GoalId in your query:


This doesn't fully solve issue with discrepancy in number of visits of checkout page before purchase and confirmation page but at least give you much better overview per unique visitor. I would say if you have 1+ visits of checkout page but 0 visits of confirmation page in this scenario, it would be considered as dropout. On the other hand if it's 1+ visits of confirmation page, it is not but you need to hind balance between # of checkout page visits and confirmation page.

Another aspect of this question would be: What if somebody starts visit at 11:47 p.m. does some checkout / catalog page visists but order is sent 0:07 a.m. next day. Should be this considered as dropout because of different date? You would need to find some linkage regarding unique customer session and goals triggered in those fact tables to fix also this scenario.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.