Beast Mode - Separate Running Total into Daily Values

nlinnell
nlinnell Member
in Magic ETL

I'm using the Search Users report in the Twitter connector to pull the number of followers for a particular user.  I have it set to run daily and append the new data, which gives me the running total for twitter followers for the given user.  I'd like to create a beast mode calculation that will give me the number of new followers on a daily basis so that I can create a chart that shows new followers by day.  In order to do that I need to look at how many followers the user had on a given day based on the "_batch_last_run_" date and subtract the number of followers from the prior day.  I can calculate it for today versus yesterday, but I'd like to be able have it calculate for all days in the date range that the Domo user defines.  Any ideas on how to create a beast mode calculation that will do that?  I'd also be open to calculating it via a dataflow.

Thanks!

Best Answer

  • smurray
    smurray Domo Employee
    Answer ✓

    Hi @nlinnell,

     

    I responded to this on Monday, but apparently it didn't save.

     

    Assuming you are using the MySQL DataFlow, there are few things that need to be done to get the script to work.

     

    First, when I copied the script from the message board into a dataflow, I noticed that it put in  <br>HTML tags, they need to be removed. Unfortunately they aren't seen when you paste it in. so I went to the beginning of each line and deleted to the end of the previous line and then hit enter.

     

    Second, in MySQL you need to use the back tick ` (it is in the upper left of the keyboard, next to the 1) for the field delimiter. Here is an example of what you had:

     a."_Batch_Last_Run_",

    it needs to be this:

     a.`_Batch_Last_Run_`,

     

    Third, when aliasing the table, you don't need a delimiter unless it contains a space. So your line:

    from twitter_user_profile_metrics AS "a"
    needs to be this:

    from twitter_user_profile_metrics AS a

     

    Fourth, you made to accomodate for a DateTime to just a date, by use the date() function. like so:

    date(a.`_Batch_Last_Run_`)

     

    so for MySQL it should look like this:

    SELECT
    a.`_Batch_Last_Run_`,
    a.`Followers Count`,
    (b.`Followers Count` - a.`Followers Count`) AS "New Follower Count"
    from user_profile_metrics AS a
    join user_profile_metrics AS b on Date(a.`_Batch_Last_Run_`) = Date(ADDDATE(b.`_Batch_Last_Run_`, INTERVAL 1 DAY))

     

    in RedShift, it would need to look like this:

    SELECT
    a."_Batch_Last_Run_",
    a."Followers Count",
    (b."Followers Count" - a."Followers Count") AS "New Follower Count"
    from user_profile_metrics AS a
    join user_profile_metrics AS b on Date(a."_Batch_Last_Run_") = Date(DATEADD(DAY, -1, b."_Batch_Last_Run_"))

     

    hope this helps

Answers

  • smurray
    smurray Domo Employee

    I am looking into this, and will let you know what I find.

  • smurray
    smurray Domo Employee

    BeastMode only calculates on one row, so what you would need to do is build a DataFlow that would either 

    a) do the calculation and give you a `New Follower` field

    or

    b) pull in the previous day's `Follower Count` and then do a Beast mode to perform the calculation

     

    Using a join to the same table but 1 day behind

    Select a.`_Batch_Last_Run_`,  a.`Followers Count`, b.`Followers Count`-a.`Followers Count` as 'New Follower Count'

    from `Twitter Search User` a 

    join `Twitter Search User` b  on a.`_Batch_Last_Run_` =ADDDATE(a.`_Batch_Last_Run_`, INTERVAL 1 DAY)

  • kshah008
    kshah008 Contributor

    @nlinnell, did smurray's reply help you out? 

  • nlinnell
    nlinnell Member

    Thanks for the reply.  I've tried to take your example script and use it in a transform to make the calculations, but I can't seem to write the correct script (I'm a novice SQL user).  I first tried to take the exact script and only swap the input dataset reference.  I then tried to modify the script, but I still haven't been able to get it to work.  Below is what I currently have in place.  The current error I receive says "The database reported a syntax error. ERROR: syntax error at or near "1" Position: 358."  Any help in getting the correct script woudl be greatly apprechiated.

     

     

    SELECT
        a."_Batch_Last_Run_",  
        a."Followers Count",
        (b."Followers Count" - a."Followers Count") AS "New Follower Count"

    from twitter_user_profile_metrics AS "a"

    join twitter_user_profile_metrics AS "b"  on a."_Batch_Last_Run_" = ADDDATE(a."_Batch_Last_Run_", INTERVAL 1 DAY)

  • Gimli
    Gimli Domo Employee

    Hello @nlinnell,

    It looks like your syntax below is written for RedShift dataflows. The example was written for MySQL dataflows. 

    RedShift does not support ADDDATE(). Please try using DATEADD() in your join.

    DATEADD( datepart, interval, {date|timestamp} )

    Here is a link to RedShift documentation that explains how to use this function. 
    http://docs.aws.amazon.com/redshift/latest/dg/r_DATEADD_function.html


    Please let me know if you still receive an error after making this change. 

    **Say “Thanks" by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • smurray
    smurray Domo Employee
    Answer ✓

    Hi @nlinnell,

     

    I responded to this on Monday, but apparently it didn't save.

     

    Assuming you are using the MySQL DataFlow, there are few things that need to be done to get the script to work.

     

    First, when I copied the script from the message board into a dataflow, I noticed that it put in  <br>HTML tags, they need to be removed. Unfortunately they aren't seen when you paste it in. so I went to the beginning of each line and deleted to the end of the previous line and then hit enter.

     

    Second, in MySQL you need to use the back tick ` (it is in the upper left of the keyboard, next to the 1) for the field delimiter. Here is an example of what you had:

     a."_Batch_Last_Run_",

    it needs to be this:

     a.`_Batch_Last_Run_`,

     

    Third, when aliasing the table, you don't need a delimiter unless it contains a space. So your line:

    from twitter_user_profile_metrics AS "a"
    needs to be this:

    from twitter_user_profile_metrics AS a

     

    Fourth, you made to accomodate for a DateTime to just a date, by use the date() function. like so:

    date(a.`_Batch_Last_Run_`)

     

    so for MySQL it should look like this:

    SELECT
    a.`_Batch_Last_Run_`,
    a.`Followers Count`,
    (b.`Followers Count` - a.`Followers Count`) AS "New Follower Count"
    from user_profile_metrics AS a
    join user_profile_metrics AS b on Date(a.`_Batch_Last_Run_`) = Date(ADDDATE(b.`_Batch_Last_Run_`, INTERVAL 1 DAY))

     

    in RedShift, it would need to look like this:

    SELECT
    a."_Batch_Last_Run_",
    a."Followers Count",
    (b."Followers Count" - a."Followers Count") AS "New Follower Count"
    from user_profile_metrics AS a
    join user_profile_metrics AS b on Date(a."_Batch_Last_Run_") = Date(DATEADD(DAY, -1, b."_Batch_Last_Run_"))

     

    hope this helps

  • kshah008
    kshah008 Contributor

    @nlinnell, tagging you to check out smurray's reply. 

  • user05387
    user05387 Member

    Hello- 

    I'm trying to do something similar here, I have a dataset where I have a daily running total of FB Fans, and I'd like to break it down by daily fans gained/lost. I put the following into the mySQL transform, but it's failing whenever I run it.

     

    SELECT
    a.`Date`,
    a.`Total Page Fans`,
    (b.`Total Page Fans` - a.`Total Page Fans`) AS "Daily Page Fans"
    from `fba_fans` AS a
    join `fba_fans` AS b on Date(a.`Date`) = Date(ADDDATE(b.`Date`, INTERVAL 1 DAY))

Categories