Wednesday, July 15, 2015

Data Flow - Sitecore WebForms For Marketer (WFFM) - Part 2

Welcome to Part 2 of the Data Flow blog series


1) Data Flow - Sitecore WebForms For Marketer (WFFM) - Part I
2) Data Flow - Sitecore WebForms For Marketer (WFFM) - Part II

In our Part 1 blog post, we discuss about the WFFM data flow from front-webpage to MongoDB database.

In this blog post, I will discuss about the WFFM data flow from MongoDB database to MSSQL Reporting database.


Fixing/Setting-up WFFM data transfer

1) WFFM Post-Installation SQL Script


Make sure that you have run the WFFM Post-Installation SQL Script in your Reporting database.
/data/...

this script will create following tables in your "Reporting" database.

2) WFFM Bug Fixes

Also, before we proceed to discuss Data Flow, we need to fix some bugs in WFFM 8.0 update-2/update-3 releases.
see Sitecore 8 WFFM Data Aggregation Error


Data Flow

MongoDB to MSSQL Reporting Database

Sitecore process data from MongoDB to MSSQL Reporting database in four ways
i.e. Aggregation, Continuous Update, Rebuilding the reporting database, Maintanence

More information on these process, visit sitecore documentation 

So, when data was written to MongoDB, Sitecore will Aggregate those data to suite into Reporting database tables. Then, by Continuous Update process, data will be transferred into Reporting database tables.

Below I tried to show the MSSQL "reporting" database tables structure.

Tables :

FormFieldValues  -  contain all the submitted form field values
    FieldValueId
    FieldValue

Fact_FormSummary  -  contain summary of the submitted form values   
    FormId
    FieldId
    FieldValueId
    FieldName
    Count

Fact_FormEvents  -
    ContactId
    InteractionId
    InteractionStartDate
    PageEventDefinitionId
    FormId
    Count

Fact_FormStatisticsByContact  -  all statistics of form submitions
    ContactId
    FormId
    LastInteractionDate
    Submits
    Success
    Dropouts
    Failures
    Visits
    Value
    FinalResult
   

3 comments:

  1. Thanks Chaturanga, this helped me. This issue is also now mentioned here but described differently - https://kb.sitecore.net/articles/282795

    ReplyDelete
  2. Hi

    In which table do the form dropout data is stored in mongodb.

    Thanks

    ReplyDelete
  3. Hi

    In which table do the form dropout data is stored in mongodb.

    ReplyDelete