ETL to connect google analytics and SQL data with purchases

user07693

Hi all,

 

Need your help in a following question:

 

Our business is online retail. We track visits in google analytics and we track purchases on our site with accounting data stored in SQL server.

 

What we want to see in domo is conversion rate: number of purchases per day / number of visitis per same day.

 

First I think we need to connect data from SQL and from google analytics:

 

Data from SQL is already in DOMO and looks in the following way:

Date ; Order #

21 Nov 2017; 000001

21 Nov 2017; 000002

21 Nov 2017; 000003

 

Data from Google analytics is the following:

Date; Number of visits

21 Nov 2017; 6

22 Nov 2017: 10

23 Nov 2017: 11

 

What I need to build

Date; Number of visits; Number of Orders; Conversion rate

21 Nov 2017; 6 ; 3 ; 50%

22  Nov 2017; 10 ; 0 ; 0%

23 Nov 2017 ; 11 ; 0 ; 0%

Please help. I assume that 1st thing I need is to connect data with ETL but i tried to use data column and a common criteria and that doesn't work for some reason

AS

You're right to join on date and any other common column you might have, like segment or whatever.  Make sure you don't miss any join conditions or you'll get more data than you want.

You probably need to also make sure that you've aggregated the transaction detail up to that daily summary level, like the GA data is.  Otherwise you'll have your rows multiplied too many times because many transactions exist per day for every one row of aggregated daily GA visit data.

If you try that aggregating first and joining second, and it doesn't work, let us know.